# img - image database G.Ogle 9/21/99 # - modified by JG 11/19/03 for mysql # - last modified May 21, 2014 -- JG DROP TABLE img; CREATE TABLE img ( # fields for all photos seq_num INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, # the unique ID ( generated) genre char (9) not null, # Animal Fungi Landscape People Plant Protista disknum char(14) not null, # 1111 2222 3333 imgnum char(4) not null, # 0002 kwid char(19) not null, # 1111 2222 3333 0002 doi varchar(255), # Global Unique Identifier. For DOI, ARK, etc. Added March 2013 at request of MVZ/CLIR. # Nov 2015: Changed from guid to doi at request of MVZ. index_date date not null, # date added to database (generated) DateLastModified datetime, # added Nov 12, 2012 collectn char(16) not null, # Brousseau, Private, MVZ,CalAcademy,CalState, # AmphibiaWeb photographer varchar(255) not null, # name_full in img_photographer table or Unknown organization varchar(128), # if relevant ex: Department of Fish and Game contact varchar(128), # John Game email: copyright varchar(128) not null,# "1998 John Game" license varchar(128) not null,# CC BY (JG added July 2011) orient char(1) not null, # V or H or N (not used as of 2007) enlarge integer not null, # display enlargement? (generated) # 1=thumbnail only # 2=tnail+256x384 only # 3=any # 4=tilepic plus any enlarge # 5=MVZ tilepic # Location info captivity bool, # t=captivity f=wild # mysql: t=1; f=0 location varchar(255), # location of photo (wild) or location of origin (captive) # Landscape: this is the caption # for captive, these are "place of origin" # for wild, these are location of photo county char(3), # California county 'ALA' other_county varchar(64), # non-California county state char(2), # 2-letter US state 'CA' other_state varchar(64), # non-US state or province country char(2), # ISO 3166 country names 'US' continent varchar(64), # Africa, North America, etc. see table continent # Naming for Plant and Animal photos taxon varchar(128), # Plant, Animal, Landscape: # name as verified against CalFlora (Plant) or ITIS (Animal) # "Abies grandis" # or "Abies sp." # or "Unknown" # or name as reported by photographer # Person: name of person family varchar(64), # added 11/99 class varchar(64), # added 11/99 phylum varchar(64), # added 11/99 cname varchar(255), # common name(s) from CalFlora (or some other source) # multiple names separated by commas ph_taxon varchar(128), # taxon reported by photographer ph_cname varchar(255), # common name(s) reported by photographer synonyms varchar(255), # added Nov 12, 2012 synonyms, etc. namesoup text, # UPPER CASE concat of taxon,cname,ph names (generated) tsn integer, # ITIS Taxonomic Serial Number (generated) lifeform varchar(30), # [See table img_lifeform] # current as of June 12, 2007: # Animal: Amphibian Bird Fish Invertebrate:Insect Invertebrate:Other Invertebrate:Spider Mammal Reptile # Bacteria: bacteria # Fossil: Invertebrate Microfossil Plant Vertebrate # Fungi: fungi lichen mold # Landscape: fieldsite habitat # Misc.: nature other specimen_tag # People: culture historical photographer # Plant: annual/perennial fern mosses/etc shrub tree tree/shrub unavailable vine # Protista: algae amoeba slimemold batchid varchar(255), # For recording information about a batch load. Added May 5, 2016. Replaced no-longer-used calrecnum field. plant_comm varchar(255), # aka "Habitat" ex: Chaparral # About the photo photo_date date, # 1998-02-27 source_id_1 varchar(255), # source id number (64 > 255 GO 4/08) source_id_2 varchar(255), # source id number (64 > 255 GO 4/08) photo_info varchar(128), # 35mm, flash, camera, etc. photog_notes text, # photographer comments chg 8/31/00 GO dl_notes text, # DigLib notes # multiple entries are separated by ';;' blobs varchar(255), # (generated) ID_confirm integer, # 1=ID confirmed, 0=not confirmed inhabitants varchar(255), # for habitat photos: species in this habitat # example: Genus1 species1, Genus2 species2 url_id varchar(128), # ID for some other database url_type varchar(32), # name of database, such as MykoWeb # also used for ph_cname source (4/2003) taxon_source varchar(128), # (2/3/01 GO) outside sources where we found taxon # NOT_FOUND CF AW ITIS MYKO CABI MSW cap_loc varchar(255), # (8/14/01 GO) photo location for captive photos date_prec varchar(16), # (8/31/01 GO) photo_date precision # exactday,exactmonth,exactyear,nodate # integer for "yyyy - yyyy" (Yr1 + date_prec = Yr2)(1/31/03 GO) submittedby varchar(60), # person who submitted info keywords text, # as requested by CAS taxon2 varchar(255), # (1/9/2003) additional taxa depicted in the photo, comma separated specimen_no varchar(255), # (1/9/2003) for specimens, unique IDs for collectn # comma separated museum varchar(255), # (1/9/2003) MVZ, UCJEPS, UCMP 3/08: changed size from 32 island varchar(64), # (1/21/2003) "Make Island" island_group varchar(64), # (1/21/2003) "Seychelles Islands" cname_source varchar(128), # (3/19/2003) outside sources for cname(s) # NOT_FOUND CF AW ITIS MYKO CABI MSW ASW UCJEPS ready integer, # (3/23/2003) 1 (display on query) 0 (do not display) coll_year integer, # (4/9/2003) collected date for specimens coll_month integer, # (4/9/2003) collected date for specimens coll_day integer, # (4/9/2003) collected date for specimens ordr varchar(64), # (7/22/2003) required for insects project varchar(64), # (7/22/2003) new lat decimal(12,8), # (9/10/2003)36.332 lng decimal(12,8), # (9/10/2003)122.351 HorizontalDatum varchar(50), # added Nov 12, 2012 GeorefSource text, # added Nov 12, 2012 Lat_Long_Determined_By varchar(255), # added Nov 12, 2012 Lat_Long_Determined_Date date, # added Nov 12, 2012 GeorefRemarks text, # added Nov 12, 2012 any relevant remarks about the georeferencing MaximumUncertaintyInMeters decimal(11,5), # added Nov 12, 2012 VerificationStatus varchar(128), # added Nov 12, 2012 a controlled set of terms for Confirmed by collector, # or Verified by Curator , or Unverified (something to help # us flag the differing levels of confidence we can assign to the locality) subject varchar(128), # 1/12/04 AKA "caption" orig_filename varchar(128), # original filename when uploaded to calphotos higher_res tinyint, # 1 (yes higher res on server) or 0 (no higher resolution file) external_enlargement_uri text, # added Nov 12, 2012 URL/URI to external "enlargement" image (ie, 600x800 dpi image) external_highres_uri text, # location of high res image at external URL/URI # was external_uri before Nov 12, 2012 associatedMedia text, # Dec 2013: Now using img_related table for this. # ie, for VTM photo retakes. A list of URLs, comma separated. Added April 2013. associatedKwids varchar(255) # Dec 2013: Now using img_related table for this. # Was for this: ie, for VTM photo retakes. A list of CalPhotos kwids, comma separated. Added April 2013. ); #LOAD DATA LOCAL INFILE "img_dump" INTO TABLE img; #LOAD DATA LOCAL INFILE "out" INTO TABLE img; #LOAD DATA LOCAL INFILE "/data1/mysql_cron_dumps/mysql_backups_current/img" INTO TABLE img; #LOAD DATA LOCAL INFILE "/data1/mysql_cron_dumps/mysql_backups_current/img" INTO TABLE img_back; # drop index img_namesoup on img; create fulltext index img_keywords on img (keywords); create fulltext index img_loc_ft on img (location); create fulltext index img_namesoup on img (namesoup); create fulltext index img_photog_notes on img (photog_notes); create fulltext index img_taxon_fulltext on img (taxon); # added July 2 2021 create fulltext index img_family_fulltext on img (family); # added July 12 2021 create fulltext index img_ordr_fulltext on img (ordr); # added July 12 2021 create fulltext index img_class_fulltext on img (class); # added July 12 2021 create fulltext index img_phylum_fulltext on img (phylum); # added July 12 2021 create fulltext index img_taxon2_fulltext on img (taxon2); # added July 12 2021 create index img_c_source on img(cname_source); create index img_calrec on img (calrecnum); create index img_cname on img (cname); create index img_collectn on img (collectn); create index img_continent on img (continent); create index img_country on img (country); create index img_county on img (county); create index img_dnum on img (disknum); create index img_family on img (family); create index img_genre on img (genre); create index img_island on img(island); create index img_island_grp on img(island_group); create index img_kwid on img (kwid); create index img_lat on img (lat); create index img_lifeform on img (lifeform); create index img_location on img (location); create index img_long on img (lng); create index img_museum on img(museum); create index img_ordr on img(ordr); create index img_org on img (organization); create index img_phcname on img (ph_cname); create index img_photog on img (photographer); create index img_plant_comm on img (plant_comm); create index img_project on img (project); create index img_ready on img (ready); create index img_s1 on img (source_ID_1); create index img_s2 on img (source_ID_2); create index img_specimen_no on img(specimen_no); create index img_state on img (state); create index img_taxon on img (taxon); create index img_taxon2 on img(taxon2); create index img_tsn on img (tsn);