-
Notifications
You must be signed in to change notification settings - Fork 16
/
db.py
executable file
·891 lines (811 loc) · 55.8 KB
/
db.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
# -*- coding: utf-8 -*-
import os.path
import img
import json
#########################################################################
## This scaffolding model makes your app work on Google App Engine too
## File is released under public domain and you can use without limitations
#########################################################################
## if SSL/HTTPS is properly configured and you want all HTTP requests to
## be redirected to HTTPS, uncomment the line below:
# request.requires_https()
## app configuration made easy. Look inside private/appconfig.ini
from gluon.contrib.appconfig import AppConfig
from gluon import current
#########################################################################
## Useful global variables
#########################################################################
## once in production, set is_testing=False to gain optimizations
## this will also set migration=False for all tables, so that the DB table definitions are fixed
is_testing = True
## Read configuration
if (
is_testing and
request.env.cmd_options is not None and
len(request.env.cmd_options.args) > 1 and
os.path.isfile(request.env.cmd_options.args[-1])
):
# For unit testing, we might want to load a different appconfig.ini file, which can
# be passed in to the rocket server as the last arg on the command-line (on the main
# server, `request.env.cmd_options` is undefined, and we default back to appconfig.ini)
myconf = AppConfig(request.env.cmd_options.args[-1], reload=is_testing)
else:
# Reload config on each request when is_testing (i.e. not production)
myconf = AppConfig(reload=is_testing)
## Configure i18n
T.set_current_languages('en', 'en-en')
# Allow translators to add new languages e.g. on the test (beta) site, but not on prod
T.is_writable = is_testing
#ALL pages can set ?lang=XXX to override the browser default for translating strings
if request.vars.lang:
T.force(request.vars.lang)
try:
thumb_base_url = myconf.take('images.url_base')
if thumb_base_url.startswith("//"):
# Add http: or https: to make it possible to view pages even from file:// urls
thumb_base_url = request.env.wsgi_url_scheme + ":" + thumb_base_url
local_pic_path = None
except:
thumb_base_url = URL(*img.local_web2py, scheme=True, host=True, extension=False)+"/"
local_pic_path = lambda s, s_id: os.path.join(request.folder, img.local_path, img.thumb_path(s, s_id))
name_length_chars = 190 ##max length for a species name is 190 chars (allows indexing. NB: max OTT name is 108 chars)
name_rank_chars = 30 ##max length for a taxonomic rank, e.g. species, genus, ...
#########################################################################
## Set up database link
#########################################################################
## if NOT running on Google App Engine use SQLite or other DB
DALstring = myconf.take('db.uri')
doMigration = myconf.take('db.migrate') in ['true', '1', 't', 'y', 'yes', 'True']
if DALstring.startswith('mysql://'):
db = DAL(DALstring,
driver_args={'read_default_file':os.path.join(request.folder, 'private','my.cnf')},
pool_size=myconf.take('db.pool_size', cast=int),
check_reserved=['all'],
migrate=doMigration,
#fake_migrate_all=True, # uncomment to fix migration issues assuming correct DB fields exist
lazy_tables= not is_testing)
## allow mysql tinyint
from gluon.dal import SQLCustomType
boolean = SQLCustomType(
type ='boolean',
native='TINYINT(1)',
encoder = (lambda x: 1 if x else 0),
decoder = (lambda x: True if x else False)
)
db.placeholder = "%s"
else:
db = DAL(DALstring,
pool_size=myconf.take('db.pool_size', cast=int),
check_reserved=['all'],
migrate=doMigration,
lazy_tables=True)
db.placeholder = "?" #for sqlite
current.db = db
## by default give a view/generic.extension to all actions from localhost
## none otherwise. a pattern can be 'controller/function.extension'
response.generic_patterns = ['*'] if request.is_local else []
## choose a style for forms
response.formstyle = myconf.take('forms.formstyle') # or 'bootstrap3_stacked' or 'bootstrap2' or other
response.form_label_separator = myconf.take('forms.separator')
response.headers['X-Powered-By'] = 'n/a'
response.headers['Server'] = 'n/a'
## (optional) optimize handling of static files
# response.optimize_css = 'concat,minify,inline'
# response.optimize_js = 'concat,minify,inline'
## (optional) static assets folder versioning
# response.static_version = '0.0.0'
#########################################################################
## Here is sample code if you need for
## - email capabilities
## - authentication (registration, login, logout, ... )
## - authorization (role based authorization)
## - services (xml, csv, json, xmlrpc, jsonrpc, amf, rss)
## - old style crud actions
## (more options discussed in gluon/tools.py)
#########################################################################
from gluon.tools import Auth, Service, PluginManager
## Configure session handling: http://web2py.com/books/default/chapter/29/13/deployment-recipes#Sessions-in-database
session.connect(request, response, db)
auth = Auth(db)
service = Service()
plugins = PluginManager()
## after auth = Auth(db)
## auth.settings.extra_fields['auth_user']= [
## Field('twitter_name',type = 'text')]
## before auth.define_tables(username=True)
## James: I got rid of this because twitter is now added to sponsorship table itself
## create all tables needed by auth if not custom tables
auth.define_tables(username=True, signature=False)
## configure email
try:
mail = auth.settings.mailer
mail.settings.server = myconf.take('smtp.server')
mail.settings.sender = myconf.take('smtp.sender')
try:
mail.settings.login = myconf.take('smtp.login')
except:
pass
try:
mail.settings.tls = (myconf.take('smtp.tls').lower() == 'true')
except:
pass
except:
mail = None
## configure auth policy
auth.settings.registration_requires_verification = False
auth.settings.registration_requires_approval = False
auth.settings.reset_password_requires_verification = True
auth.settings.allow_basic_login = True
##restrict site to only logged in users
## https://groups.google.com/forum/#!topic/web2py/0j92-sPp4bc
##NB: useful url to add a guest user programmatically http://stackoverflow.com/questions/35504306/web2py-how-to-programmatically-register-users/35518991
#if not (request.url == auth.settings.login_url):
# auth.requires_login()(lambda: None)()
## guests can't change the p/w
if auth.user and auth.user.get('username') is not None and auth.user.get('username') == 'guest':
auth.settings.actions_disabled = ['reset_password', 'request_reset_password', 'change_password', 'profile']
#kill off silly messages
auth.messages.logged_in = None
auth.messages.logged_out = None
auth.settings.actions_disabled.append('register')
#########################################################################
## Define your tables below (or better in another model file) for example
##
## >>> db.define_table('mytable',Field('myfield','string'))
##
## Fields can be 'string','text','password','integer','double','boolean'
## 'date','time','datetime','blob','upload', 'reference TABLENAME'
## There is an implicit 'id integer autoincrement' field
## Consult manual for more options, validators, etc.
##
## More API examples for controllers:
##
## >>> db.mytable.insert(myfield='value')
## >>> rows=db(db.mytable.myfield=='value').select(db.mytable.ALL)
## >>> for row in rows: print row.id, row.myfield
#########################################################################
## after defining tables, uncomment below to enable auditing
# auth.enable_record_versioning(db)
# csv tables read in for all species that could be sponsored
# should correspond to line 294 of OTTmapper2csv.py which creates the data: "id","ott","name","popularity","eol","wikidata"]
# then keys of ('ncbi', 1172), ('if', 596), ('worms', 123), ('irmng', 1347), ('gbif', 800)
db.define_table('ordered_leaves',
Field('parent', type='integer', notnull=True), #index number into ordered_nodes
Field('real_parent', type='integer', notnull=True), #index number into ordered_nodes, negative if this is a polytomy
Field('name', type='string', length=name_length_chars), #this is not genus and species because not everything conforms to that
Field('extinction_date', type='double'), #in Ma
Field('ott', type='integer'),
Field('wikidata', type='integer'),
Field('wikipedia_lang_flag', type='integer'), #which language wikipedia articles exist (enwiki=1, dewiki=2, eswiki=4, etc etc: listed in wikiflags in _OZglobals.py)
Field('eol', type='integer'),
Field('iucn', type='text'), #this could contain multiple bar-separated numbers (|), if we have a conflict. Conflicts are resolved in the IUCN table
Field('raw_popularity', type='double'),
Field('popularity', type='double'),
Field('popularity_rank', type='integer'),
#the following 5 fields are sources listed by the OpenTree
Field('ncbi', type='integer'),
Field('ifung', type='integer'),
Field('worms', type='integer'),
Field('irmng', type='integer'),
Field('gbif', type='integer'),
Field('ipni', type='integer'),
#all the remaining fields are not filled out in the original data tables, but are filled out by later algorithms
Field('price', type='integer'), #price in pence. Should not use floating point type because we want to group by this at some
format = '%(ott)s')
# csv file for ordered nodes allowing sets of leaves to be recovered using a nested
# set notation. This should correspond to line 434 and 455 of CSV_base_table_creator.py which creates the data:
# "ott","lft","rgt","name","popularity","eol","wikidata"
db.define_table('ordered_nodes',
# Next 13 cols are indices into the ordered_nodes (first 3) and ordered_leaves tables (remaining 10)
# we might want to consider making these references rather than indexes, to enforce dependency
Field('parent', type='integer', notnull=True),
Field('real_parent', type='integer', notnull=True), # Real parent, before polytomy breaking, negative if this is a polytomy
Field('node_rgt', type='integer', notnull=True), # The highest node ID in the subtree defined by this node
Field('leaf_lft', type='integer', notnull=True),
Field('leaf_rgt', type='integer', notnull=True),
Field('name', type='string', length=name_length_chars), #scientific name
Field('age', type='double'), # in Ma, e.g. chimp/human = 6.0
Field('ott', type='integer'),
Field('wikidata', type='integer'),
Field('wikipedia_lang_flag', type='integer'), #
Field('eol', type='integer'),
Field('rnk', type='string', length=name_rank_chars), # The taxonomic rank, e.f. "genus"
Field('raw_popularity', type='double'),
Field('popularity', type='double'),
#the following 5 fields are sources listed by the OpenTree
Field('ncbi', type='integer'),
Field('ifung', type='integer'),
Field('worms', type='integer'),
Field('irmng', type='integer'),
Field('gbif', type='integer'),
Field('ipni', type='integer'),
#all the remaining fields are not filled out in the original data tables, but are filled out by later algorithms
#we might want to consider putting them in a different table, although they need recalculating every time the topology changes
Field('vern_synth', type='text'), # a synthetic version of the vernacular name, e.g. Hamsters, Gerbils, True Mice, Rats and more
Field('rep1', type='integer'), # rep1 - rep8 are the leaf otts for the 8 'representative' species pictured
Field('rep2', type='integer'), # these are chosen using some complex algorithm which depends on richness
Field('rep3', type='integer'), # and quality of picture. The algorithm is implemented in OZprivate/ServerScripts/Utilities/picProcess.py
Field('rep4', type='integer'), # and needs to be regularly rerun.
Field('rep5', type='integer'),
Field('rep6', type='integer'),
Field('rep7', type='integer'),
Field('rep8', type='integer'),
Field('rtr1', type='integer'), # rtr1 - rtr8 are the leaf otts for the 8 'representative' species pictured
Field('rtr2', type='integer'), # that are guaranteed to be 'trusted'
Field('rtr3', type='integer'), #
Field('rtr4', type='integer'), #
Field('rtr5', type='integer'),
Field('rtr6', type='integer'),
Field('rtr7', type='integer'),
Field('rtr8', type='integer'),
Field('rpd1', type='integer'), # rpd1 - rpd8 are the leaf otts for the 8 'representative' species pictured
Field('rpd2', type='integer'), # that are guaranteed to have a public domain image
Field('rpd3', type='integer'), #
Field('rpd4', type='integer'), #
Field('rpd5', type='integer'),
Field('rpd6', type='integer'),
Field('rpd7', type='integer'),
Field('rpd8', type='integer'),
Field('iucnNE', type='integer'), #the numbers of IUCN categories among the leaves of this node
Field('iucnDD', type='integer'),
Field('iucnLC', type='integer'),
Field('iucnNT', type='integer'),
Field('iucnVU', type='integer'),
Field('iucnEN', type='integer'),
Field('iucnCR', type='integer'),
Field('iucnEW', type='integer'),
Field('iucnEX', type='integer'),
Field('popleaf', type='integer'), #most popular leaf (index into ordered_leaves)
Field('popleaf_ott', type='integer'), #ott (if present) of most popular leaf above (to speed up lookups)
format = '%(ott)s')
# tables for common names - one name per line (allows multiple names for the same taxon)
# this could be obtained e.g. from EoL or IUCN. Most should be identified by ott, but
# some taxa have no ott id, so have to be identified (in another table) by name
db.define_table('vernacular_by_ott',
Field('ott', type='integer', notnull=True, requires=IS_NOT_EMPTY()), # the opentree id for this taxon
Field('vernacular', type='string', notnull=True, length=name_length_chars), #in mySQL, need to set this to charset utf8mb4 so that all the weird unicode characters are saved correctly
Field('lang_primary', type='string', notnull=True, length=3), #the 'primary' 2 or 3 letter 'lang' code for this name (e.g. 'en', 'cmn'). See http://www.w3.org/International/articles/language-tags/
# can be constructed from sql by
#sql> update vernacular_by_ott set lang_primary=substring_index(lang_full,'-',1);
Field('lang_full', type='string', notnull=True, length=20), #the longer 'lang' code for this name (e.g. 'en-gb, zh-hans, etc'), as lowecase. Should never be >20 chars.
Field('preferred', type=boolean, notnull=True), #if there are several names for a lang, use this
Field('src', type='integer', notnull=True), # The source (eol, bespoke, etc)
Field('src_id', type='integer'), # The sourceid, e.g the EoL page id.
#NB sourceid is mainly for traceability. For a proper matching of e.g. eol to OTT, see ordered_leaves
Field('updated', type='datetime', requires= IS_EMPTY_OR(IS_DATETIME())),
format = '%(ott)s_%(vernacular)s_%(lang)s')
db.define_table('vernacular_by_name',
Field('name', type='string', notnull=True, length=name_length_chars, requires=IS_NOT_EMPTY()),
Field('vernacular', type='string', notnull=True, length=name_length_chars), #in mySQL, need to set this to charset utf8mb4 so that all the weird unicode characters are saved correctly
Field('lang_primary', type='string', notnull=True, length=3), #the 'primary' 2 or 3 letter 'lang' code for this name (e.g. 'en', 'cmn'). See http://www.w3.org/International/articles/language-tags/
# can be constructed from sql by
#sql> update vernacular_by_ott set lang_primary=substring_index(lang_full,'-',1);
Field('lang_full', type='string', notnull=True, length=20), #the longer 'lang' code for this name (e.g. 'en-gb, zh-hans, etc'), as lowecase. Should never be >20 chars.
Field('preferred', type=boolean, notnull=True), #if there are several names for a lang, use this
Field('src', type='integer', notnull=True), # 1=OneZoom, 2=EoL, 8=onezoom_special (short name). Others could be reserved e.g. for iucn
Field('src_id', type='integer'), #the sourceid, e.g the EoL page id.
#NB sourceid is mainly for traceability. For a proper matching of e.g. eol to OTT, see ordered_leaves
Field('updated', type='datetime', requires= IS_EMPTY_OR(IS_DATETIME())),
format = '%(name)s_%(vernacular)s_%(lang)s')
# tables for image references. These are mostly intended for leaves
# on the tree. It is more robust to populate nodes by picking the
# highest-scoring child leaf, using ordered_nodes. This can be
# done every time a new tree is generated
# e.g. to the EOL pages batch API. Note that this saves the
# EOL data object ID, but not the image itself: this may need to be downloaded
# separately, if you want a local store.
db.define_table('images_by_ott',
Field('ott', type='integer', notnull=True, requires=IS_NOT_EMPTY()),
Field('src', type='integer', notnull=True),
# 1=eol, 2=arkive, etc.
Field('src_id', type='integer'),
# a unique identifier for this picture (e.g. the EoL data_object_id).
Field('url', type='text'),
# the source url for this picture (e.g. http://media.eol.org/content/2015/04/05/06/22039_orig.jpg).
Field('rating', type='integer'),
# an image rating. EOL 5 star ratings are mapped to unsigned 2 byte integers by multiplying by 10,000
# so that 1 star = 10,000 & 5 star = 50,000, which allows 0-10,000 for bespoke marking of terrible photos
# and 50,000-65,535 for bespoke marking of amazing photos.
Field('rating_confidence', type='bigint'),
# A measure of the confidence in the rating. This measure is dependent on the source. For EoL
# images, the lowest 5 bytes in the number store the number of votes for each star. and the
# higher bytes store the total number of votes. This rather specific algorithm allows us to keep the
# as a generic bigint, without having to specifically code 5 eol-specific
# numbers into the database. Since the total number of votes cast occupies the highest bits,
# sorting by confidence is equivalent to sorting by number of votes (but with ties broken by number
# of 5* votes, then 4*, etc etc. Hacky. But clever.
Field('rights', type='text'), #in mySQL, need to set this to charset utf8mb4 so that all the weird unicode characters are saved correctly
# e.g. "© Scott Loarie". Can be of long length as we don't need to index this
Field('licence', type='string', length=name_length_chars), #in mySQL, need to set this to charset utf8mb4 so that all the weird unicode characters are saved correctly
# e.g. "http://creativecommons.org/licenses/by/2.0/". We might need to index this
Field('updated', type='datetime', requires=IS_EMPTY_OR(IS_DATETIME())),
*[Field('{}best_{}'.format(prefix,suffix), type=boolean, notnull=True) for suffix in image_status_labels for prefix in ("","overall_")],
#define fields for each type in image_status_labels, i.e. best_any, best_verified, best_pd. If True, this image is the
# best to choose for this src (any=best regardless of type, verified=best that has been verified by an expert, pd=best public domain image
# also define overall_best_any, overall_best_verified, overall_best_pd, which indicate the best image regardless of src.
# Note that this means you should only have one 'True' value set in e.g. overall_best_any for all rows that share an ott id.
format = '%(ott)s')
db.define_table('images_by_name',
Field('name', type='string', length=name_length_chars, requires=IS_NOT_EMPTY()), #needs to be of same charset as 'name' in ordered_leaves & nodes
Field('src', type='integer', notnull=True),
Field('src_id', type='integer'),
Field('url', type='text'),
Field('rating', type='integer'),
Field('rating_confidence', type='bigint'),
Field('rights', type='text'), #in mySQL, need to set this to charset utf8mb4 so that all the weird unicode characters are saved correctly
Field('licence', type='string', length=name_length_chars), #in mySQL, need to set this to charset utf8mb4 so that all the weird unicode characters are saved correctly
Field('updated', type='datetime', requires=IS_EMPTY_OR(IS_DATETIME())),
*[Field('{}best_{}'.format(prefix,suffix), type=boolean, notnull=True) for suffix in image_status_labels for prefix in ("","overall_")],
format = '%(name)s')
#we want to keep track of last updated times for each ott, but can't store this in the
#image or vernacular section, because some otts will be checked and have no pictures / names
db.define_table('eol_updated',
Field('eol', type='integer', notnull=True, unique=True, requires=IS_NOT_EMPTY()),
Field('updated', type='datetime', notnull=True, requires= IS_DATETIME()),
#Some EoL pages may have moved to a different id, or have been permanently deleted (API calls return "unavailable page id").
#It is useful to keep track of these for monitoring purposes, e.g. so we can update our mapping in ordered_XX or correct on Wikidata
#if an eol ID has been deleted rather than moved, we should set this to None / NULL
Field('real_eol_id', type='integer'),
format = '%(eol)s', migrate=is_testing)
# table for IUCN status: this can get updated by a call to the IUCN API
# it is useful for populating the metadata on the tree. There is some semi-duplicated
# information here, because the ott->iucn mapping also exists (through wikidata or EoL).
# in ordered_leaves, although that table can have multiple IUCN ids separated by |
# Note that we don't need an 'updated' field for each row, as we update all these
# in a single batch from the IUCN API.
db.define_table('iucn',
Field('ott', type='integer', unique=True, requires=IS_NOT_EMPTY()),
Field('iucn', type='integer'), #can be empty if e.g. this is an extinct species not in IUCN
Field('status_code', type='string', length=10), #LC, VN, etc.
format = '%(iucn)s', migrate=is_testing)
# Table for availability of IPNIs in Kew's Plants of the World Online portal (PoWO):
# this contains IPNIs which have live pages of the form
# http://powo.science.kew.org/taxon/urn:lsid:ipni.org:names:<ipni_id>
# It should be filled out by importing the file sent from Kew
db.define_table('PoWO',
Field('ipni', type='string', length=20, unique=True, requires=IS_NOT_EMPTY()), #e.g. 391732-1
Field('ipni_int', type='integer', unique=True, requires=IS_NOT_EMPTY()), #same but with no hyphen => int
format = '%(ipni)s', migrate=is_testing)
# this table defines the OTTIDs that are banned from sponsorship
db.define_table('banned',
Field('ott', type='integer', notnull=True, requires=IS_NOT_EMPTY()),
Field('cname', type='string', length=name_length_chars),
#this is simply used to help remember what the OTT_IDs are
format = '%(ott)s_%(cname)s', migrate=is_testing)
# this table handles reservations, ledger and verified in a single table
# to remove any sensitive information (e.g. before sending to 3rd parties)
# you should make a local copy and either remove all rows of the reservations table (preferable) ordo e.g.
# UPDATE reservations SET user_id=NULL, username=NULL, e_mail=NULL, twitter_name=NULL, allow_contact=NULL, user_sponsor_lang=NULL, user_sponsor_kind=NUll, user_sponsor_name=NULL, user_donor_name=NULL, user_more_info=NULL, user_message_OZ=NULL, user_giftaid=NULL, user_paid=NULL, PP_transaction_code=NULL, PP_e_mail=NULL, PP_first_name=NULL, PP_second_name=NULL, PP_town=NULL, PP_country=NULL, PP_house_and_street=NULL, PP_postcode=NULL, sale_time=NULL, verified_paid=NULL, asking_price=NULL, admin_comment=NULL, sponsorship_story_level=NULL;
# also make sure not to export the expired_reservations and wiped_reservations tables.
db.define_table('reservations',
Field('OTT_ID', type='integer', unique=True, requires=IS_NOT_EMPTY()),
# this field is the open tree ID associated with page views and reservations
Field('name', type='text', writable=False),
# scientific name (not necessarily genus), species, since some leaves have extra info
# this is essentially redundant, since the OTT_ID should give us a name, but it is useful to know
# We set writeable=False because we shouldn't update it via autogenerated forms
Field('num_views', type='integer', requires=IS_NOT_EMPTY(), writable=False),
# these are general useful stats for a page
Field('last_view', type='datetime', requires= IS_EMPTY_OR(IS_DATETIME()), writable=False),
Field('reserve_time', type='datetime', requires= IS_EMPTY_OR(IS_DATETIME()), writable=False),
# When entry clicked on by user (and reserved for potential sponsorship). For renewals and expire-repurchase this is the original reserve_time of the previous entry.
Field('user_registration_id', type='text', writable=False),
# This is created by OZfunc/__make_user_code and used instead of a session cookie so that
# when refreshing e.g. a sponsor_leaf page we know that it is the same user trying to sponsor.
# Eventually we intend this to correspond to a number in the registration_id field of the
# auth_user table but for the moment we generate a new UUID for each treeview session
Field('basket_code', type='text', writable=False),
# A UUID created by OZfunc/__make_user_code identifying a group of items sent to paypal for
# purchase. Used to identify which OTTs have been purchased once confirmation of funds comes
# back from paypal, by being encoded in notify_url.
Field('prev_reservation_id', type='integer', writable=False, ),
# The ID of the previous sponsorship that this replaced (either with an extension or a renewal)
# NB: Ideally it'd be type='references expired_reservations.id', but we can't without a circular definition
# these handle auto reservation of pages
Field('user_id', type='reference auth_user' , requires=IS_EMPTY_OR(IS_IN_DB(db, 'auth_user.id','%(first_name)s %(last_name)s'))),
# points to user table - built in. Is usually NULL because doner need not have a OneZoom login
db.auth_user.username.clone(unique=False, requires=None),
# Create a "username" field of an identical type to that in auth_user.username.
# People can set a username in the reservations table at any point which has to be one
# that is not already present in reservations.username and if it is idential to one
# in auth_user.username they should be prompted to log in. If, later, they create
# a login, we will always use the reservations.username value.
# The reservations.username field is optional but needed if the user is to have a
# "public" page of all their sponsorships
# NB: Ideally the default of this field would be None, not "" - https://github.com/OneZoom/OZtree/issues/645
Field('e_mail', type='string', length=200, requires=IS_EMPTY_OR(IS_EMAIL())),
Field('twitter_name', type='text'),
Field('allow_contact', type=boolean, default=False),
# in case they don't want to log in to process this.
Field('user_sponsor_lang', type='string', length=30),
# in case they don't want to log in to process this.
Field('user_sponsor_kind', type='string', length=4, requires=IS_EMPTY_OR(IS_IN_SET(['by','for']))),
# self explanatory for sponsorship kind 'by' or 'for' a person. If the
Field('user_sponsor_name', type='string', length=40, requires=IS_EMPTY_OR(IS_LENGTH(minsize=1,maxsize=30))),
# name of person as appears on leaf. Needs to be verified
Field('user_donor_title', type='string', length=40, requires=IS_EMPTY_OR(IS_LENGTH(minsize=1,maxsize=30))),
# title of donor (Mr, Mrs, Dr, etc - needed for giftaid). .
Field('user_donor_name', type='string', length=40, requires=IS_EMPTY_OR(IS_LENGTH(minsize=1,maxsize=30))),
# name of donor (different to user_sponsor_name if sponsored for someone).
Field('user_addr_house', type='text', writable=True, length=40, requires=IS_EMPTY_OR(IS_LENGTH(minsize=1,maxsize=40))),
# house name or number, or full address for non-UK residents. Needed for giftaid
Field('user_addr_postcode', type='string', length=10, writable=True),
# Postcode, or NULL for non-UK residents. Needed for giftaid
Field('user_donor_hide', type=boolean, default=False),
# True if the user explicitly requested we hide acknowledgment on donors page / personal page
# False if they didn't check a box, NULL if they haven't been asked yet.
Field('user_more_info', type='string', length=40, requires=IS_EMPTY_OR(IS_LENGTH(maxsize=30))),
# optional extra sponsorship text which could be shown on the leaf if there's enough space
Field('user_nondefault_image', type='integer'),
#has the user chosen a non-default image? None if no img or the already downloaded OZ
# image was chosen, 0 if there was no OZ image but the default EoL image was chosen,
# or 1 if another image was chosen by the sponsor. Should prob be boolean type instead.
Field('user_preferred_image_src', type='integer', requires=IS_EMPTY_OR(IS_INT_IN_RANGE(0,1000))),
Field('user_preferred_image_src_id', type='integer', requires=IS_EMPTY_OR(IS_INT_IN_RANGE(-1e100,1e100))),
# an option for users to recommend an EOL ID as the best image. Should normally be filled
# for a sponsored image, even if the sponsor hasn't chosen a different image. If they
# do choose a non default image, user_nondefault_image is set to true, and the
# user_preferred_image_src should be set to src_flags['onezoom_via_eol'],
# on the assumption that this is a nicer image than the
# default OneZoom one. Otherwise the user_preferred_image_src and src_id values
# should be set to the src and src_id values passed in when viewing the sponsor page
Field('user_updated_time', type='datetime', requires=IS_EMPTY_OR(IS_DATETIME()), writable=False),
# need to know when it was last updated to check for user updates
Field('user_paid', type='double', requires=IS_EMPTY_OR(IS_DECIMAL_IN_RANGE(0,1e100))),
# The amount (in pounds) the user promised to pay for this OTTID
# Set by user before paypal trip, validated to be >= asking_price (See valid_spons).
Field('user_message_OZ', type='text', requires=(IS_LENGTH(maxsize=250))),
# message for OZ e.g. to show on funding website or to request converstaion about url etc.
Field('sponsorship_story', type='text', requires=(IS_LENGTH(maxsize=1000))),
# The story behind .
Field('sponsorship_story_level', type='double'),
# A hand-curated quality score roughly matching that in `sponsorship_text_level`
# (e.g. 3 = completely standard & acceptable, 4 = excellent, 5 = best) - NULL = not reviewed.
Field('user_giftaid', type=boolean),
# can we collect gift aid?
# paypal returned information
Field('PP_transaction_code', type='text', writable=False),
Field('PP_e_mail', type='string', length=200, writable=False),
# another e-mail just in case we need it for verification
Field('PP_first_name', type='text', writable=False),
Field('PP_second_name', type='text', writable=False),
# name to help with sponsorship text verification
Field('PP_town', type='text', writable=False),
Field('PP_country', type='text', writable=False),
# address to help with further info verification
Field('PP_house_and_street', type='text', writable=False),
Field('PP_postcode', type='text', writable=False),
# save the two above only if they have agreed to give us gift aid
Field('sale_time', type='text', writable=False),
# seems professional to know when they paid and wise to keep it separate from expiry date
# a verified copy of what's in the sponsor table.
Field('verified_kind', type='string', length=4, requires=IS_EMPTY_OR(IS_IN_SET(['by','for']))),
# matches 'user_sponsor_kind'
Field('verified_name', type='string', length=40, requires=IS_EMPTY_OR(IS_LENGTH(minsize=1,maxsize=30)), widget=SQLFORM.widgets.text.widget),
# matches 'user_sponsor_name'
Field('verified_donor_title', type='string', length=40, requires=IS_EMPTY_OR(IS_LENGTH(minsize=1,maxsize=30))),
# matches 'user_donor_title'
Field('verified_donor_name', type='string', length=40, requires=IS_EMPTY_OR(IS_LENGTH(minsize=1,maxsize=30))),
# matches 'user_donor_name'
Field('verified_more_info', type='string', length=40, requires=IS_EMPTY_OR(IS_LENGTH(maxsize=30)), widget=SQLFORM.widgets.text.widget),
# matches 'user_more_info'
Field('verified_preferred_image', type='integer', requires=IS_EMPTY_OR(IS_INT_IN_RANGE(-1e100,1e100))), #old, to be deleted
Field('verified_preferred_image_src', type='integer', requires=IS_EMPTY_OR(IS_INT_IN_RANGE(0,1000))),
Field('verified_preferred_image_src_id', type='integer', requires=IS_EMPTY_OR(IS_INT_IN_RANGE(-1e100,1e100))),
# match 'user_preferred_image_src', and 'user_preferred_image_src_id' or may be
# modified by hand by an admin. Can only be null if there was no image
# (not even an already-downloaded OneZoom one) when the user sponsored.
# If the user picked a non default image, then this
# field is still filled out, but user_nondefault_image should be 1.
Field('verified_time', type='datetime', requires=IS_EMPTY_OR(IS_DATETIME())),
# if verified_time = NULL then details haven't been verified
Field('verified_paid', type='text', writable=False),
# The amount paypal reported as being paid (as a stringified pounds/pence float) for this OTTID
# May be NULL after payment if, e.g. payment received outside paypal, see notes in get_reservation()
Field('verified_url', type='text'),
# url for those that agree to have one
Field('sponsorship_text_level', type='integer'),
# How generally acceptable is the text (3 = completely standard & acceptable) - NULL=not reviewed
Field('tweeted_re_sponsorship', type='datetime', requires=IS_EMPTY_OR(IS_DATETIME())),
# the time when we emailed them
Field('emailed_re_sponsorship', type='datetime', requires=IS_EMPTY_OR(IS_DATETIME())),
# the time when we emailed them
Field('emailed_re_renewal_initial', type='datetime', requires=IS_EMPTY_OR(IS_DATETIME())),
# the first time when we emailed about renewals
Field('emailed_re_renewal_final', type='datetime', requires=IS_EMPTY_OR(IS_DATETIME())),
# the final time when we emailed about renewals
Field('restrict_all_contact', type=boolean),
# Can we contact them about renewals etc, or only about the most urgent stuff
Field('admin_comment', type='text'),
# comments for any purpose edited by us
Field('sponsorship_duration_days',type='integer', writable=False),
# the length in days gained by the most recent donation
Field('sponsorship_ends',type='datetime', writable=False),
# the expiry date, set during validation. For new sponsorships, this will be the verified_time plus
# the sponsorship_duration_days. For renewed leaves, the difference between sponsorship_ends and
# verified_time can be 2 or 3 (or more) times the sponsorship_duration_days
Field('asking_price', type='double', requires=IS_EMPTY_OR(IS_DECIMAL_IN_RANGE(0,1e100)), writable=False),
# price in pounds - good idea to hang on to this for accounting purposes and verification the numbers add up later
# Set as (ordered_leaves.price / 100) at time of purchase (See valid_spons)
Field('partner_percentage', type='double', requires=IS_EMPTY_OR(IS_DECIMAL_IN_RANGE(0,1e100)), writable=False),
# percentage of this donation that is diverted to a OZ partner like Linn Soc (after paypal fees are deducted)
# If there are multiple partners, set to NaN. Refer to partner table to get percentage
Field('partner_name', type='string', length=40, writable=False),
# A comma-separated string of partner_identifier(s)
Field('partner_paid_on', type='datetime', requires=IS_EMPTY_OR(IS_DATETIME()), writable=False),
Field('giftaid_claimed_on', type='datetime', requires=IS_EMPTY_OR(IS_DATETIME()), writable=False),
Field('deactivated', type='text'),
# true if this row in the reservations table has been deliberately deactivated for any reason other than expiry e.g. complaint / species disappears etc.
format = '%(OTT_ID)s_%(name)s')
#a duplicate of the reservations table to store old reservations. This table need never be sent to 3rd parties
db.define_table('expired_reservations',
Field('OTT_ID', type='integer', unique=False, requires=IS_NOT_EMPTY()),
Field('was_renewed', type=boolean),
# useful to know if part of the sponsorship time in this reservation is captured in another reservation row
*[f.clone() for f in db.reservations if f.name != 'OTT_ID' and f.name!='id'],
format = '%(OTT_ID)s_%(name)s', migrate=is_testing)
# Reservations that we never show anywhere on the site, even historically, but still keep
# in the DB for our own (financial) records. Reservations can be moved into here by hand
# and deleted from the other reservations table if requested by a sponsor
db.define_table('wiped_reservations',
*[f.clone() for f in db.expired_reservations if f.name!='id'],
format = '%(OTT_ID)s_%(name)s', migrate=is_testing)
# Record donations via. paypal that can't be directly assigned to a node, e.g.
# * Remainder of transaction after all node costs are met
# * Remainder if transaction can only pay for OTTs x/y when the basket contains x/y/
db.define_table('uncategorised_donation',
Field('basket_code', type='text', writable=False),
# A UUID created by OZfunc/__make_user_code identifying a group of items sent to paypal for
# purchase. Used to identify which OTTs have been purchased once confirmation of funds comes
# back from paypal, by being encoded in notify_url.
Field('sale_time', type='text', writable=False),
# seems professional to know when they paid and wise to keep it separate from expiry date
Field('verified_paid', type='text', writable=False),
# The amount paypal reported as being paid (as a stringified pounds/pence float) for this OTTID
# May be NULL after payment if, e.g. payment received outside paypal, see notes in get_reservation()
Field('user_paid', type='double', requires=IS_EMPTY_OR(IS_DECIMAL_IN_RANGE(0,1e100))),
# The amount (in pounds) the user promised to pay for this OTTID
Field('user_giftaid', type=boolean),
# can we collect gift aid?
Field('e_mail', type='string', length=200, requires=IS_EMPTY_OR(IS_EMAIL())),
Field('allow_contact', type=boolean),
# in case they don't want to log in to process this.
# paypal returned information
Field('PP_transaction_code', type='text', writable=False),
Field('PP_e_mail', type='string', length=200, writable=False),
# another e-mail just in case we need it for verification
Field('PP_first_name', type='text', writable=False),
Field('PP_second_name', type='text', writable=False),
# name to help with sponsorship text verification
Field('PP_town', type='text', writable=False),
Field('PP_country', type='text', writable=False),
# address to help with further info verification
Field('PP_house_and_street', type='text', writable=False),
Field('PP_postcode', type='text', writable=False),
# save the two above only if they have agreed to give us gift aid
format = '%(sale_time)s_%(verified_paid)s', migrate=is_testing)
# this table defines the current pricing cutoff points
db.define_table('prices',
Field('price', type='integer', unique=True),
Field('perpetuity_price', type='integer'),
# Map the "normal" 4 year price to the in-perpetuity price
Field('quantile', type='double'),
Field('n_leaves', type='integer'),
Field('class_description', type='string', length=100),
Field('price_description', type='string', length=100),
# Two text descriptions used in e.g. museum displays, where the price may not be up-to-date
format = '%(price)s_%(n_leaves)s', migrate=is_testing)
# this table collects data for recently 'visited' nodes (i.e. requested through the API)
db.define_table('visit_count',
Field('ott', type='integer', notnull=True, unique=True),
Field('detail_fetch_count', type='integer'),
Field('search_count', type='integer'),
Field('leaf_click_count', type='integer'),
format = '%(ott)s', migrate=is_testing)
# This table buffers recently 'visited' EoL taxa (visited through the window popup or via the copyright link)
# taxa in this table are stored until at least 1 minute after the taxon is visited, and then read by the EOL update
# script (EoLQueryPicsNames.py) to check for updates to the crop location, ratings, etc. Once checked, the taxon
# is deleted from the table, so this should contain only those taxa that need checking. The EoLQueryPicsNames.py script
# can be given
# i: just a set of ott ids: in which case it looks up the corresponding eol ids from
# the ordered_leaves and ordered_nodes tables,
# ii: just a set of eol data object ids: in which case it updates the database rows for
# images (not names) where src is one of [src_flags[n] for n in eol_src_flag_names] and
# src_id corresponds to the eol id.
# iii: both ott and corresponding eol ids: in which case the images so gained are
# treated from the "onezoom_via_eol" src, rather than plain "eol" src, which
# should give them a priority (this is used when e.g. sponsors chose a non-default
# image for their species
#
# used to download images and populate tables
# in advance of a change to the OneZoom tree (when otts may not even exist in the
# ordered_leaves / nodes table).
db.define_table('eol_inspected',
Field('ott', type='integer'),
Field('name', type='string', length=name_length_chars), #only used if no OTT
Field('eol', type='integer'),
#usually null, otherwise an eol data_object id (if via==eol_inspect_via_flags['image'])
# or an eol page id (otherwise).
Field('via', type='integer', notnull=True),
Field('inspected', type='datetime', notnull=True, requires=IS_DATETIME()),
format = '%(ott)s_%(name)s', migrate=is_testing)
# this table contains info to provide a list of interesting places to sponsor.
db.define_table('sponsor_picks',
Field('identifier', type='string', unique=True, length=20, notnull=True, comment=(
"A unique identifier. If a number this refers to an OTT id, but otherwise a "
"bespoke list whose OTTs are listed in the 'otts field'")),
Field('display_order', type='integer', unique=True, comment=(
"What order to display them in on the page. If NULL, this is disabled")),
Field('display_flags', type='integer', comment=(
"Whether to display this pick in different contexts, with the contexts"
"as listed in the sponsor_suggestion_flags array. If NULL, alwys use")),
Field('name', type='text', notnull=True, comment=(
"The name that appears next to this pick on the webpage. May be translated")),
Field('subtext', type='text', comment=(
"optional subtext to explain the list. May be translated")),
Field('thumb_url', type='text', comment=(
"A URL to a thumbnail image. If NULL use the thumb_src & thumb_src_id fields")),
Field('thumb_src', type='integer', comment=(
"A number from src_flags, e.g. 1 for OneZoom, 2 for Eol, etc.")),
Field('thumb_src_id', type='integer', comment=(
"The identifier within src, e.g. the data_object id for EoL")),
Field('otts', type='text', comment=(
"A comma-separated list of ott ids. If NULL, generate a single ott by "
"converting the identifier field above to an integer and linking to that "
"sponsor_node page. Otherwise use the sponsor_handpick page")),
Field('vars', type='text', comment=(
"A JSON string used to add variables to the linked web page, e.g. {'n':12}"
"to increase the number of species in each price category, or "
"{'user_more_info':'sponsor info'} or {'partner':partner_identifier} "
"where partner_identifier matches that value from the partners table below.")),
format = '%(identifier)s_%(name)s', migrate=is_testing)
# this table lists potential OneZoom 'partners' with whom we might share profits
db.define_table('partners',
Field('partner_identifier', type='string', unique=True, length=20, notnull=True), #a unique alphanumeric identifier, e.g. LinnSoc
Field('name', type='text', notnull=True), #the name, e.g. 'the Linnean Society of London',as in "50% will go to {{name}}" - this may be translated
Field('url', type='text'), #a url to give people further info about the sponsorship scheme on the partner's web site
Field('general_url', type='text'), #a url to give people general info about the partner
Field('logo', type='text'), #a url to a logo, e.g. used on sponsorship page
Field('small_logo', type='text'), #a url to a small version of their logo (displayed at ~ 100x50 px), e.g. used on the main OneZoom viewer
Field('details', type='text'), #more info for sponsors, e.g. what the money goes towards. May be translated.
Field('percentage', type='double', notnull=True),
Field('giftaid', type=boolean, notnull=True), #can we collect gift aid?
Field('default_more_info', type='string', length=30), #what appears by default in the sponsorship text, e.g. "supporting the Linnean Society"
Field('popular_locations_json', type='text'), #a JSON string used to fill the popular species menu (the popular_locations variable passed to treeviewer/layout.html). This is an long array containing elements which are either strings (which give menu headers) or dicts as {"OTT":1234,"en":"English name",...}. No carriage returns or single quotes allowed.
Field('allow_own_site', type=boolean), #If True, allow a site like www.onezoom.org/life/partnername/
format = '%(name)s', migrate=is_testing)
# this table maps partners to node or leaf OTTs. When sponsorship windows
# are opened on the main OZ website we check whether the
db.define_table('partner_taxa',
Field('partner_identifier', type='string', length=20, notnull=True), #a unique alphanumeric identifier, e.g. LinnSoc
Field('ott', type='text'), #an ott: on the main OZ site, descendants of these IDs will have this partner set
Field('is_leaf', type=boolean, notnull=True), #is this a leaf? Helps us choose whether to look in leaf or node table
Field('deactived', type=boolean, notnull=True), #allows us to keep details in the DB but not to do sponsorship. However, it is more efficient to delete them from this table
format = '%(partner_identifier)s', migrate=is_testing)
# Root for all tours
db.define_table('tour',
Field('identifier', type='string', unique=True, length=20, notnull=True), # Identifier for tour, used in URLs, e.g.
Field('lang', type='string', notnull=True, length=3), #the 'primary' 2 or 3 letter 'lang' code for this name (e.g. 'en', 'cmn'). See http://www.w3.org/International/articles/language-tags/
Field('author', type='text', notnull=True, default=''), # Author of tour
Field('image_url', type='string'), # URL to image used when displaying in e.g. popular places
Field('title', type='text', notnull=True, default=''), # Title for tour for listings
Field('description', type='text', notnull=True, default=''), # Description for tour for listings
Field('keywords', type='list:string', default=[]), # Arbitary keywords, e.g. 'education', '11--16' (age)
Field('visible_in_search', 'boolean', notnull=True, default=True), # Available in text search results or nearby tours search
Field('created', 'datetime', default=request.now),
Field('updated', 'datetime', default=request.now, update=request.now),
Field('views', 'integer', notnull=True, default=0),
format='%(identifier)s', migrate=is_testing)
# Tourstops within a tour
db.define_table('tourstop',
Field('tour', db.tour),
Field('identifier', type='string', notnull=True), # Unique identifier for tourstop, for establishing symlinks
Field('ord', type='integer', notnull=True), # The position of this tourstop in the tour, starting with 1
Field('ott', type='integer', notnull=False), # The OTT this tourstop points at. NULL => return to start
Field('secondary_ott', type='integer', notnull=True, default=0), # A second OTT when targeting a common ancestor
Field('qs_opts', type='string', notnull=False, default=''), # QS-style options to apply to modify tourstop, e.g. into_node=true&initmark=...
Field('author', type='text', notnull=True, default=''), # Author of tourstop (doesn't necessarily match tour in case of remix)
Field('transition_in', type='string', notnull=True, requires=IS_IN_SET(('fly', 'leap', 'fly_straight')), default='fly'), # Transition to use when flying to stop
Field('fly_in_speed', type='double', notnull=False, default=1), # Speed relative to global_anim_speed
Field('transition_in_wait', type='integer', notnull=False), # How long to wait before entering into transition
Field('stop_wait', type='integer', notnull=False), # How long to wait at this stop (null => wait until "next" is pressed)
Field('stop_wait_after_backward', type='integer', notnull=False), # How long to wait if entered by going back
Field('template_data', type='text', notnull=True,
filter_in=lambda obj: json.dumps(obj),
filter_out=lambda txt: json.loads(txt)), # JSON template data for tourstop
Field('lang', type='string', notnull=True, length=3, default='en'), #the 'primary' 2 or 3 letter 'lang' code for this name (e.g. 'en', 'cmn'). See http://www.w3.org/International/articles/language-tags/
Field('checked_by', type='string'), # Who has checked the validity of this data?
Field('checked_updated', 'datetime', default=None), # When did they do it?
Field('visible_in_search', 'boolean', notnull=True, default=True), # Available in tourstop-search for remixing a tour?
Field('created', 'datetime', default=request.now),
Field('updated', 'datetime', default=request.now, update=request.now),
format='%(ott)d_%(identifier)s', migrate=is_testing)
# Symlinks to other tourstops in another tour, a full tour will combine both tours and tourstops
db.define_table('tourstop_symlink',
Field('tour', db.tour),
Field('tourstop', db.tourstop),
Field('ord', type='integer', notnull=True), # The position of this tourstop in the tour
Field('created', 'datetime', default=request.now),
Field('updated', 'datetime', default=request.now, update=request.now),
format='%(tour)d_%(ord)d', migrate=is_testing)
# These are popular places, tours or other things that a user can use to explore the tree
# in a more guided way. E.g. use as a first way into the tree or as suggestions of places
# to go / things to do once in.
db.define_table('tree_startpoints',
Field('category', type='string', length=20, comment=(
"A unique alphanumeric identifier which is used to categorise the startpoints "
"for example 'homepage_anim' for the list of points visited in the animation on "
"the home page, 'homepage_main' for the main pictures on the homepage carousel "
"'homepage_red' for the threatened or endangered species on the homepage. We "
"might also want to place the popular places menu items in here later, rather "
"than hardcoding them in the controllers")),
Field('partner_identifier', type='string', length=20, comment=(
"An alphanumeric category which we can use to distinguish partners (e.g."
"LinnSoc or OTOP) - this should match up with the 'partners' and 'partner_taxa'"
"tables")),
Field('ott', type='integer', comment=(
"The ott of the startpoint, used to get the picture and (if tour_identifier is "
"None) the starting location")),
Field('image_url', type='text', comment=(
"Used instead of the default image (allows e.g. non organism images for tours)")),
Field('tour_identifier', type='string', length=20, comment=(
"A string giving a tour name, which will override the ott as the startpoint")),
format = '%(category)s_%(partner_identifier)s', migrate=is_testing)
# Somewhere to simply store the html for news items
db.define_table('news',
Field('category', type='string', length=20), # e.g. 'milestone', 'event',
Field('news_date', type='datetime', notnull=True, requires=IS_DATETIME()), #the date of the news
Field('html_description', type='text'), # a description of the event
Field('html_summary', type='text'), # a short description of the event in HTML. If absent then the main HTML should be short enough.
Field('thumbnail_href', type='text'), # href to a thumbnail of the event 4:3 aspect ratio is best if absent the code will use the category field default jpeg instead.
Field('text_date', type='text'), # in case we want a bespoke text for the date, e.g. July 30th-31st 2016
format = '%(date)s_%(type)s', migrate=is_testing)
# Somewhere to simply store the endorsement quotes
db.define_table('quotes',
Field('category', type='string', length=30), # e.g. 'Scientists', 'Educators',
Field('html_quote', type='text'), # The endorsement quote (may also contain html)
Field('quality', type='double'), # A measure of how usable the quote is, e.g. well known person, good quote
Field('person', type='string', length=50), #the plain text person's name
Field('person_url', type='string', length=100), # a web url for this person
Field('html_affiliation', type='string', length=100), # E.g. University of Oxford
format = '%(person)s_%(category)s', migrate=is_testing)
#a list of API users, added by hand
db.define_table('API_users',
Field('APIkey', type='string', length=32, unique=True, notnull=True),
Field('API_user_name', type='text'), # who this API user is
Field('max_taxa_per_query', type='integer'), #set this to NULL or 0 to deactivate this API user
Field('max_returns_per_taxon', type='integer'),
format = '%(APIkey)s', migrate=is_testing)
#a record of the amounts of API use, by user
db.define_table('API_use',
Field('APIkey', type='string', unique=True, length=32), #which key was used
Field('API', type='string', length=100), #which API was used, e.g. popularity/list
Field('start_date', type='datetime', notnull=True, requires=IS_DATETIME()), #when we started recording this data
Field('end_date', type='datetime', requires=IS_DATETIME()), #when we stopped recording this data (if NULL, this is still recording)
Field('n_calls', type='bigint', default=1), # number of times API called
Field('n_taxa', type='bigint', default=0), # number of taxa requested (divide by n_calls to get av taxa per call)
Field('n_returns', type='bigint', default=0), # number of taxa returned (divide by n_calls to get av taxa per call)
format = '%(APIkey)s_%(API)s', migrate=is_testing)
# Record of embed keys and who uses them
db.define_table('embed_key',
Field('e_mail', type='string', length=200, unique=True, notnull=True, requires=IS_EMAIL()),
# E-mail address associated with uuid_key
Field('code', type='text', notnull=True),
# Key used publicly in embedkey querystring
Field('created', 'datetime', default=request.now),
)
# add extra indexes on OTT_ID etc in tables. Index name (ott_index) is arbitrary
# http://stackoverflow.com/questions/4601138/what-is-the-significance-of-the-index-name-when-creating-an-index-in-mysql
if db._uri.startswith("sqlite://"):
db.executesql('CREATE INDEX IF NOT EXISTS ott_index ON ordered_nodes (ott);')
db.executesql('CREATE INDEX IF NOT EXISTS ott_index ON ordered_leaves (ott);')
db.executesql('CREATE INDEX IF NOT EXISTS ott_index ON banned (ott);')
db.executesql('CREATE INDEX IF NOT EXISTS ott_index ON reservations (OTT_ID);')
db.executesql('CREATE INDEX IF NOT EXISTS eol_index ON ordered_nodes (eol);')
db.executesql('CREATE INDEX IF NOT EXISTS eol_index ON ordered_leaves (eol);')
db.executesql('CREATE INDEX IF NOT EXISTS name_index ON ordered_nodes (name);')
db.executesql('CREATE INDEX IF NOT EXISTS name_index ON ordered_leaves (name);')
#note mysql does not allow IF NOT EXISTS for index creation. Indexes may need to be added manually. See
# http://stackoverflow.com/questions/36602374/web2py-how-to-call-a-call-a-function-on-table-creation
# for mysql, try this one-off command
# to build indexes - the code has been moved to /OZprivate/Examples/SQL\ example\ scripts for safe keeping