schemas/VegCore/VegCore.ERD.mwb: VegCore DB: changed collation to utf8_bin to match table collation
schemas/VegCore/VegCore.ERD.mwb: saved settings for publishing schema to vegbiendev phpMyAdmin (vegbiendev.nceas.ucsb.edu/phpmyadmin/index.php?db=VegCore). this adds `DROP TABLE IF EXISTS` before each CREATE TABLE statement, renames the default DB to VegCore, and adds a USE statement to select the VegCore DB.
schemas/VegCore/connections.xml: vegbiendev.nceas.ucsb.edu: set schema (DB) to VegCore
added schemas/VegCore/connections.xml, which should be installed as the connections.xml settings file
schemas/VegCore/VegCore.ERD.mwb: imported database connections from the connections.xml settings file into document.mwb.xml in the .mwb zip file. these connections are not used directly by MySQL Workbench, but instead need to be copied into the connections.xml settings file.
schemas/VegCore/VegCore.ERD.mwb: use Database > Forward Engineer (Ctrl+G) instead of File > Export > Forward Engineer SQL CREATE Script (Ctrl+Shift+G), so that comments will use the max comment length for the server's MySQL version rather than MySQL Workbench's internal MySQL version, which has a max comment length of only 60 chars for tables. this commit stores the default export settings for Forward Engineer, which are saved each time it's run.
bugfix: root Makefile: $(postgresReload-Linux): fixed the "shmat(id=...) failed: Cannot allocate memory" error by telling the user to restart PostgreSQL manually from the command line
root Makefile: $(postgresReload-Linux): don't restart Postgres twice, because this does not fix the "shmat(id=...) failed: Cannot allocate memory" error. this error is instead produced whenever PostgreSQL is restarted from within a Makefile, but not when it's run directly from the command line or /bin/sh.
root Makefile: $(postgresReload-Linux): restart Postgres twice, because the first time after the postgresql.conf has been udpated, it always produces a "shmat(id=...) failed: Cannot allocate memory" error
schemas/VegCore/VegCore.ERD.mwb: re-synced with VegCore.my.sql: step 2: `DEFAULT NULL` added on all nullable columns
schemas/VegCore/VegCore.ERD.mwb: re-synced with VegCore.my.sql: step 1: table order changed
schemas/VegCore/VegCore.ERD.mwb: relationship: renamed the two fk_relationship_record1 fkeys so their names wouldn't collide
schemas/VegCore/VegCore.ERD.mwb: changed encoding back to utf8/utf8_bin now that latin1 isn't needed to make the key columns satisfy the index length limits. note that the connection encoding must be utf8 on import so that the correct byte sequence is stored in the VARBINARY columns.
schemas/VegCore/VegCore.ERD.mwb: changed VARCHAR BINARY columns to VARBINARY because VARCHAR BINARY is actually only an alias for the *_bin collation of the table's encoding, but still uses multibyte characters if the encoding does. note that VARBINARY will still be displayed as text (in the connection encoding), rather than hex values.
schemas/VegCore/VegCore.ERD.mwb: changed the type of TEXT columns used in indexes to VARCHAR BINARY to satisfy prefix length limits ("a prefix can be up to [...] 767 bytes for InnoDB tables" <http://dev.mysql.com/doc/refman/5.5/en/create-index.html>). the BINARY flag was added to allow storing Unicode values (from UTF-8), which are not representable in the latin1 encoding.
schemas/VegCore/VegCore.ERD.mwb: changed encoding to latin1 to fit more characters in length-limited indexes (indexes treat utf8 as having 3 bytes/character (http://stackoverflow.com/questions/1814532/1071-specified-key-was-too-long-max-key-length-is-767-bytes#comment-18031137), which significantly reduces the max length of an indexed column)
schemas/VegCore/VegCore.ERD.mwb: changed collation to utf8_bin to avoid case-*in*sensitive comparison of case-sensitive strings. (utf8_bin is also much faster than encoding-aware collations because it uses the raw bytes.)
schemas/VegCore/VegCore.ERD.mwb: set DefaultPkColumnType to TEXT to match the type of current pkey columns
schemas/VegCore/VegCore.ERD.mwb: set all tables to use utf8/utf8_unicode_ci rather than latin1/latin1_swedish_ci because our DB is in UTF-8. utf8_unicode_ci is better than utf8_general_ci because it supports international characters better (http://forums.mysql.com/read.php?103,187048,188748)
schemas/VegCore/VegCore.ERD.mwb: TNRS taxonomic scrubbing steps labels: removed fkey columns to the next step since these are not needed and were causing problems with round-trip sync to the .my.sql file
bugfix: schemas/VegCore/VegCore.ERD.mwb: fixed foreign keys without a referenced columns list
schemas/VegCore/VegCore.ERD.mwb: upgraded for MySQL Workbench 5.2.47 (MySQL 5.5), which requires indexes to have different names than fkeys
schemas/postgresql.conf: shared_buffers: reduced again for Ubuntu VM
bugfix: schemas/postgresql.conf: shared_buffers: removed invalid Unicode character U+FEFF
schemas/postgresql.conf: shared_buffers: reduced to work in Ubuntu VM
README.TXT: Maintenance: to synchronize vegbiendev, jupiter, and your local machine: add * after analytical_aggregate.*.csv to match *.md5 files as well
inputs/VegBIEN/TWiki/templates/login.tmpl: increased font size further using <span> style. changed text to fit on two lines with the new size.
inputs/VegBIEN/TWiki/templates/login.tmpl: added large text with instructions to register (the TWiki instructions are small and easily missed)
added inputs/VegBIEN/TWiki/templates/login.tmpl
inputs/VegBIEN/TWiki/twiki.conf: main TWiki dir: changed redirect to go directly to the Main TWiki web, so that we don't need to worry about creating different "webs" (subsites) for top-level wiki pages
inputs/VegBIEN/TWiki/twiki.conf: main TWiki dir: redirect shortened wiki page URLs of the form vegpath.org/twiki/Path to do/view/ for use with VegPath
added web/.twiki symlink to datasources/VegBIEN/TWiki/
added inputs/VegBIEN/TWiki/twiki.conf
schemas/VegCore/VegCore.ERD.mwb: coordinates: added latitude_deg, longitude_deg. geovalidation: added lat_long_in_ranks hstore.
schemas/VegCore/VegCore.ERD.mwb: realigned event/occurrence divider. moved method to avoid overlapping the halves.
schemas/VegCore/VegCore.ERD.mwb: specimen: replaced specimen_holder_institutions with specimen_holder_institution many:many table. added fkeys for the various institution fields. collection: removed name because this is already a required field in source. event_participant: removed *_id suffixes from fields.
schemas/VegCore/VegCore.ERD.mwb: replaced party_list with event_participant many:many table, since the nature of each type of party_list (ordered, not, etc.) may differ between tables that HAVE-A party_list, and because it is not possible to create an fkey to a column that is not unique in the referenced table (as is the case for party_list.list_id)
schemas/VegCore/VegCore.ERD.mwb: fixed lines
schemas/VegCore/VegCore.ERD.mwb: made first_publisher a field of source (fkey to party) instead of a subclass of it, because a source can be both an aggregator for some datasets and a first publisher for others and which one it is depends on the dataset
schemas/VegCore/VegCore.ERD.mwb: added organization
schemas/VegCore/VegCore.ERD.mwb: added party_list
schemas/VegCore/VegCore.ERD.mwb: regenerated exports
schemas/VegCore/VegCore.ERD.mwb: taxon_presence, aggregate_observation: added required taxon field to identify (distinguish) the observation itself. this is expected to be the same as the first taxon_determination applied to the observation.
schemas/VegCore/VegCore.ERD.mwb: collection: renamed code to name because the collection code is often the full name of the collection, rather than just an abbreviation. made name required.
schemas/VegCore/VegCore.ERD.mwb: source: added name
schemas/VegCore/VegCore.ERD.mwb: set page size to fit the ERD contents. this cannot be done through the GUI, so instead you need to use the following steps:
$ unzip ERD.mwb document.mwb.xmlopen document.mwb.xml and search for "this ERD" units are mm$ zip -u ERD.mwb document.mwb.xml...
schemas/VegCore/VegCore.ERD.mwb: fixed split connector
schemas/VegCore/VegCore.ERD.mwb: fixed inheritance lines
schemas/VegCore/VegCore.ERD.mwb: source: added contact field (fkey to party)
schemas/VegCore/VegCore.ERD.mwb: party: inherit from record instead of source, because a party is defined by and scoped by a datasource but isn't a datasource itself (however, a named datasource can have an associated admin party)
schemas/VegCore/VegCore.ERD.mwb: moved tables to make more room between specimen and taxon tables
schemas/VegCore/VegCore.ERD.mwb: split taxon_occurrence into taxon_occurrence and taxon_observation, because a taxon_*occurrence* is actually the thing on which a taxon_*observation* is made, not the observation itself. rearranged tables significantly to put observations next to the things they observe, and to make the ERD less wide so it will fit on a small screen without as much scaling (allowing a bigger font size when the ERD is shrunk to fit the screen).
schemas/VegCore/VegCore.ERD.mwb: added primary columns. changed VARCHAR columns to TEXT and also set default type in ERD so future columns won't need to be changed.
schemas/VegCore/VegCore.ERD.mwb: added primary columns, including catch-all hstores for custom columns. made stem inherit from individual and stem_observation from individual_observation because any stem can have individual-level traits and identifying info.
schemas/VegCore/VegCore.ERD.mwb: replaced trait table with traits hstore on taxon_occurrence, since the trait table is basically just a way of allowing the user to add arbitrary measurement columns to the taxon_occurrence. units can be indicated by storing a record with the quantity and units, converted to a string, and parsing it back out as a record upon retrieval.
schemas/VegCore/VegCore.ERD.mwb: renamed taxon_observation to taxon_presence for clarity and to distinguish it better from taxon_occurrence
schemas/VegCore/VegCore.ERD.mwb: taxon_determination: added identified_date and unique index on identifying columns
schemas/VegCore/VegCore.ERD.mwb: taxon_determination: added identified_by. rearranged taxon tables to visually differentiate between inheritance relationships (1:1) and directional HAS-A arrows
schemas/VegCore/VegCore.ERD.mwb: taxon tables: added catch-all hstores for custom columns
schemas/VegCore/VegCore.ERD.mwb: split taxon_concept into taxon_name (containing the components of a name) and taxon_concept (containing the taxon's formal specification and relationships to other taxon_concepts). this avoids requiring a taxon_concept entry for names that are not true taxon concepts. taxon_name: added taxon name/author-related components. TNRS taxonomic scrubbing steps: added dummy id column to avoid sync problems with empty tables.
schemas/VegCore/VegCore.ERD.mwb: taxon tables: added nonidentifying columns
schemas/VegCore/VegCore.ERD.mwb: categories: updated box sizes
schemas/VegCore/VegCore.ERD.mwb: validatable_place: added unique index on identifying columns
schemas/VegCore/VegCore.ERD.mwb: made place_path and coordinates globally-scoped rather than per-datasource, so that there is only one geovalidation row for each combination of them rather than one per datasource place. added globally-scoped validatable_place table that stores combinations of place_path and coordinates, and made geovalidation extend it (since geovalidation provides optional fields and is 1:1 with it).
schemas/VegCore/VegCore.ERD.mwb: made geovalidation extend place_path instead of being pointed to by it, because it provides a set of optional fields a (geovalidated) place_path may have
schemas/VegCore/VegCore.ERD.mwb: made place_path extend place instead of place having a place_path, because place_path now provides an optional set of fields that a place may provide, rather than being a completely separate entity
schemas/VegCore/VegCore.ERD.mwb: renamed named_place to place_path since place now serves the purpose that named_place used to (i.e. representing geographical areas)
schemas/VegCore/VegCore.ERD.mwb: removed invisible U+FEFF Unicode character
schemas/VegCore/VegCore.ERD.mwb: renamed location to place because any place can be a location, not just a plot or point (e.g. Barro Colorado Island is a CTFS "plot" but definitely much larger than the typical plot)
schemas/VegCore/VegCore.ERD.mwb: labeled TNRS taxonomic scrubbing steps
schemas/VegCore/VegCore.ERD.mwb: geovalidation: put named_place before coordinates so they would be in path order (one named_place can have many coordinates in it)
schemas/VegCore/VegCore.ERD.mwb: taxon_assertion: added fkey to taxon_concept/taxon_path to store any parsed taxonomic fields provided by the data provider
schemas/VegCore/VegCore.ERD.mwb: linked taxon_concept directly to taxon_string rather than to child table taxon_name, because it creates unnecessary confusion to have two tables storing taxonomic strings. a taxon_string is anything that can be used as input to TNRS, and may or may not include a cf_aff.
schemas/VegCore/VegCore.ERD.mwb: link each taxon_string to a parsed_taxon_assertion. removed verbatim_taxon_assertion because that function is now performed by taxon_string.
schemas/VegCore/VegCore.ERD.mwb: added taxon_name, which is used by taxon_concept
schemas/VegCore/VegCore.ERD.mwb: added taxon_string, which is used by taxon_assertion
schemas/VegCore/VegCore.ERD.mwb: added unique indexes on identifying columns where possible. unique indexes differ from primary keys in that they are generally on multiple columns, while pkeys are on a text field which is the concatenation of the identifying columns.
schemas/VegCore/VegCore.ERD.mwb: hierarchical tables: made parent pointer NOT NULL so that it can be an identifying column. this requires the root(s) of the hierarchy to be indicated with a self-referential parent pointer rather than a NULL value. this also allows materializing parent elements to arbitrary levels without worrying about hitting the root, because the root's ancestors will simlpy be itself for as many levels as are needed (i.e. materializing several levels up the tree is guaranteed to produce a non-NULL entry for all levels).
schemas/VegCore/VegCore.ERD.mwb: taxon_assertion: removed fkey to taxon_concept because only parsed_taxon_assertions have a corresponding taxon_concept
schemas/VegCore/VegCore.ERD.mwb: fixed table positions
schemas/VegCore/VegCore.ERD.mwb: lined up tables
schemas/VegCore/VegCore.ERD.mwb: parsed_taxon_assertion.matched_taxon: changed it to be an fkey to taxon_path, because the parsing must also populate the denormalized path fields in addition to defining a taxon_concept with the concatenated scientific name
schemas/VegCore/VegCore.ERD.mwb: added taxon_path
schemas/VegCore/VegCore.ERD.mwb: geological_context: made it the same color as community because it is a type of community for the geological aspects of the location
schemas/VegCore/VegCore.ERD.mwb: renamed fkeys to match renamed tables
schemas/VegCore/VegCore.ERD.mwb: merged synonym_taxon, accepted_taxon into taxon_concept because any taxon_concept can have an accepted taxon which indicates the canonical name for that taxon, not just TNRS match results
schemas/VegCore/VegCore.ERD.mwb: merged taxon_concept and taxon, because every taxon is according to someone (whether that's Weakley, Tropicos, or the person identifying a taxon_occurrence). note that the according_to is usually different from the source (which is the datasource that owns the record), but if there is no specific according_to, the according_to is the datasource itself.
schemas/VegCore/VegCore.ERD.mwb: lightened table colors so dark text is easier to see against them. used colors from VegBank ERD (http://vegpath.org/fs/schemas/VegBank/vegbank.ERD.pdf) where possible, to have a familiar look-and-feel for those who have seen the VegBank ERD.
schemas/VegCore/VegCore.ERD.mwb: categories: added stratum
schemas/VegCore/VegCore.ERD.mwb: categories: put categories on the same halves of the legend as they are in the ERD
