Project

General

Profile

Activity

From 08/25/2013 to 09/23/2013

09/22/2013

08:25 PM Revision 11071: mappings/VegCore.htm: regenerated from wiki. added Stratum table.
Aaron Marcuse-Kubitza
07:57 PM Revision 11070: schemas/vegbien.ERD.mwb: updated to PostgreSQL schema
Aaron Marcuse-Kubitza
06:25 PM Revision 11069: schemas/VegCore/VegCore.ERD.mwb: model options: template for new fkey names: use the PostgreSQL naming convention so that new fkeys will sync properly with equivalent fkeys in the Postgres schema that have had their names reset
Aaron Marcuse-Kubitza
06:18 PM Revision 11068: schemas/VegCore/VegCore.ERD.mwb: regenerated exports and udpated image map
Aaron Marcuse-Kubitza
06:13 PM Revision 11067: schemas/VegCore/VegCore.ERD.mwb: source: made name optional because some sources (e.g. records) don't have a name, and are instead identified differently. instead require name in applicable subclasses. source subclasses: added unique constraints on parent/name since the name is presumed to be identifying within the parent entity.
Aaron Marcuse-Kubitza
05:55 PM Revision 11066: schemas/VegCore/VegCore.ERD.mwb: party: added separate person subclass, which has the organization pointer. organization instead has a recursive parent pointer, to avoid mutual recursion between party and organization.
Aaron Marcuse-Kubitza
09:00 AM Revision 11065: schemas/VegCore/VegCore.ERD.mwb: dataset.parent: updated comment to refer to data_source, not traceable.source (which is now used for a different purpose)
Aaron Marcuse-Kubitza
08:56 AM Revision 11064: schemas/VegCore/VegCore.ERD.mwb: regenerated exports and udpated image map
Aaron Marcuse-Kubitza
08:49 AM Revision 11063: schemas/VegCore/ERD/VegCore.ERD.mwb: fixed lines
Aaron Marcuse-Kubitza
08:46 AM Revision 11062: schemas/VegCore/ERD/VegCore.ERD.mwb: removed unnecessary hstore extenders that duplicate an extender of the same name in a superclass
Aaron Marcuse-Kubitza
08:25 AM Revision 11061: schemas/VegCore/ERD/VegCore.ERD.mwb: dataset: replaced metadata_source with data_source, since the source field inherited from traceable is actually for where the information in the dataset record itself came from
Aaron Marcuse-Kubitza
07:57 AM Revision 11060: schemas/VegCore/ERD/VegCore.ERD.mwb: dataset.parent: documented that this provides a containment relationship, which differs from the attribution relationship provided by source (inherited from traceable). e.g. the GBIF.ARIZ dataset is contained within the GBIF dataset (the parent), but was obtained from ARIZ (the source).
Aaron Marcuse-Kubitza
07:37 AM Revision 11059: schemas/VegCore/ERD/VegCore.ERD.mwb: source: moved parent pointer to dataset, since the containment relationship is intended for datasets (there may be other types of sources for which this makes sense, but not ones that are used in our data)
Aaron Marcuse-Kubitza
07:07 AM Revision 11058: schemas/VegCore/ERD/VegCore.ERD.mwb: traceable.table_fragment: renamed to id_within_source, since this can also identify e.g. a dataset within the source that it came from
Aaron Marcuse-Kubitza
06:47 AM Revision 11057: schemas/VegCore/ERD/VegCore.ERD.mwb: dataset: added metadata_source, which stores where the information *about* the dataset came from (as opposed to where the dataset came from)
Aaron Marcuse-Kubitza
06:31 AM Revision 11056: schemas/VegCore/ERD/VegCore.ERD.mwb: party: inherit from source because a person (or organization) can directly be a source of data. it should not be necessary to link them to a dataset in order to attribute rows to them.
Aaron Marcuse-Kubitza
06:25 AM Revision 11055: schemas/VegCore/ERD/VegCore.ERD.mwb: traceable.table_fragment: made this optional, since some traceables may have a source but are not *uniquely identifiable* within that source. the ID would in this case need to be populated by a subclass table, since traceable can't generate a unique ID without the fragment.
Aaron Marcuse-Kubitza
04:33 AM Revision 11054: schemas/VegCore/ERD/VegCore.ERD.mwb: record: clarified that "an actual data record" means "a row in a table". dataset: made definition more specific ("a collection of records from the same place, with the same attribution requirements").
Aaron Marcuse-Kubitza
04:08 AM Revision 11053: schemas/VegCore/ERD/VegCore.ERD.mwb: record: added definition (a collection of data relating to the same entity). note that this doesn't have to be an actual data record, it can also be a paragraph of text relating to the same thing (e.g. a method description in a PDF).
Aaron Marcuse-Kubitza
02:52 AM Revision 11052: schemas/VegCore/ERD/VegCore.ERD.mwb: method: added optional name field
Aaron Marcuse-Kubitza
02:49 AM Revision 11051: schemas/VegCore/ERD/VegCore.ERD.mwb: stratum: added definition (a non-horizontal way of subdividing a plot)
Aaron Marcuse-Kubitza
02:32 AM Revision 11050: schemas/VegCore/ERD/: svn:ignore *.bak
Aaron Marcuse-Kubitza
02:14 AM Revision 11049: schemas/VegCore/ERD/VegCore.ERD.mwb: stratum, stratum_event: added VegX synonyms
Aaron Marcuse-Kubitza
02:00 AM Revision 11048: schemas/VegCore/ERD/VegCore.ERD.mwb: synonyms with other exchange schemas: put the exchange schema name first, so that the first word in the URL is the source of that synonym (which is most important), while the schema-specific synonym name (which is less important) comes after it. this matches the VegCore data dictionary convention where the text of the URL (i.e. the most important part) is the exchange schema name.
Aaron Marcuse-Kubitza
01:53 AM Revision 11047: schemas/VegCore/ERD/VegCore.ERD.mwb: synonyms with other exchange schemas: put the exchange schema name first, so that the first word in the URL is the source of that synonym (which is most important), while the schema-specific synonym name (which is less important) comes after it. this matches the VegCore data dictionary convention where the text of the URL (i.e. the most important part) is the exchange schema name.
Aaron Marcuse-Kubitza
01:09 AM Revision 11046: schemas/VegCore/ERD/VegCore.ERD.mwb: individual_count.taxon: renamed to taxon_presence to clarify that this is an fkey to taxon_presence
Aaron Marcuse-Kubitza

09/21/2013

11:01 PM Revision 11045: schemas/VegCore/: moved ERD-related files to ERD/ subdir
Aaron Marcuse-Kubitza
10:58 PM Revision 11044: added schemas/VegCore/ERD/SQL symlink to the dir with the VegCore SQL schemas
Aaron Marcuse-Kubitza
10:52 PM Revision 11043: schemas/VegCore/: moved ERD-related files to ERD/ subdir
Aaron Marcuse-Kubitza
10:45 PM Revision 11042: schemas/VegCore/: moved ERD-related files to ERD/ subdir
Aaron Marcuse-Kubitza
10:44 PM Revision 11041: schemas/VegCore/: moved ERD-related files to ERD/ subdir
Aaron Marcuse-Kubitza
10:27 PM Revision 11040: bugfix: schemas/VegCore/ERD/.htaccess: mod_autoindex: clear the IndexHeadInsert message "some listed files are not web-accessible" so that it is not displayed above the ERD image, which looks strange because the files are below it (this message can be removed for this dir because all files in it are web-accessible)
Aaron Marcuse-Kubitza
10:17 PM Revision 11039: web/.htaccess: mod_autoindex: IndexHeadInsert: use IndexStyleSheet for stylesheet link instead
Aaron Marcuse-Kubitza
10:12 PM Revision 11038: schemas/VegCore/ERD/VegCore.ERD.png.map.htm: VegCore logo: hyperlink this to VegCore.vegpath.org as well, instead of to the data dictionary
Aaron Marcuse-Kubitza
10:10 PM Revision 11037: schemas/VegCore/VegCore.ERD.mwb: regenerated exports and udpated image map
Aaron Marcuse-Kubitza
10:00 PM Revision 11036: schemas/VegCore/VegCore.ERD.mwb: moved VegCore.vegpath.org link next to logo so it can be found more easily
Aaron Marcuse-Kubitza
09:45 PM Revision 11035: schemas/VegCore/ERD/.htaccess: display the index page (containing the ERD) as part of the mod_autoindex directory listing, so that the directory listing is included when you go to the VegCore.vegpath.org/ERD/ URL. this will allow the ERD-related files to be moved to the ERD/ subdir, and still be visible in the directory listing.
Aaron Marcuse-Kubitza
09:42 PM Revision 11034: bugfix: web/.htaccess: mod_autoindex: IndexOptions: use +HTMLTable to ensure that the contents are always displayed in an HTML table, even when a header file is provided with HeaderName (which turns off the default HTML table for some reason)
Aaron Marcuse-Kubitza
09:01 PM Revision 11033: lib/sql_io.py: put_table(): added link to new INSERT ON DUPLICATE SELECT wiki page, which now contains the explanation in the doc comment
Aaron Marcuse-Kubitza
07:29 PM Revision 11032: schemas/VegCore/VegCore.ERD.mwb: ERD link: changed to VegCore.vegpath.org so that the user can access *all* additional files for VegCore, not just the PDF version of the ERD
Aaron Marcuse-Kubitza
07:24 PM Revision 11031: schemas/VegCore/VegCore.ERD.mwb: source.name: require this field (for records, populate it from id_within_dataset)
Aaron Marcuse-Kubitza
07:01 PM Revision 11030: schemas/VegCore/VegCore.ERD.mwb: record.dataset_record_id: renamed to id_within_dataset for clarity
Aaron Marcuse-Kubitza
06:59 PM Revision 11029: schemas/VegCore/VegCore.ERD.mwb: traceable: added table_fragment field, which is the portion of the source record that this traceable refers to. (denormalized source data often contains data for many VegCore tables in the same row, and the traceables for each of these table entries must be distinguished *from each other* since they share the same source. this is usually just the VegCore table name, sometimes with a distinguishing prefix (e.g. collector.party/identified_by.party; current_observation.taxon_determination/orig_observation.taxon_determination).) this field is needed *in addition to the source record* to form a unique ID for the traceable.
Aaron Marcuse-Kubitza
06:24 PM Revision 11028: schemas/VegCore/VegCore.ERD.mwb: regenerated exports and udpated image map
Aaron Marcuse-Kubitza
06:22 PM Revision 11027: schemas/VegCore/VegCore.ERD.mwb: dataset: inherit from traceable because the information *about* the dataset (data_owners, contacts, etc.) might have been obtained from an external source, such as IH, a web page, or even an e-mail conversation
Aaron Marcuse-Kubitza
05:47 PM Revision 11026: schemas/VegCore/VegCore.ERD.mwb: regenerated exports and udpated image map
Aaron Marcuse-Kubitza
05:45 PM Revision 11025: schemas/VegCore/VegCore.ERD.mwb: taxon_observation: draw inheritance connector to event to clarify what table this inherits from. reobservable.original_observation: renamed to orig_observation to match the naming convention of specimen.orig_collection. table relationships legend: updated "inherits from record" label to refer to the traceable table, which now serves this purpose.
Aaron Marcuse-Kubitza

09/19/2013

06:49 PM Revision 11024: bugfix: inputs/VegBank/import_order.txt: added stratum
Aaron Marcuse-Kubitza
06:48 PM Revision 11023: inputs/VegBank/taxon_observation.**/postprocess.sql: added stratum, stratumtype to the left-join
Aaron Marcuse-Kubitza
06:46 PM Revision 11022: inputs/VegBank/stemcount_/map.csv: stratum_id: removed table prefix so it can be used as a join column
Aaron Marcuse-Kubitza
06:45 PM Revision 11021: inputs/VegBank/: mapped stratum
Aaron Marcuse-Kubitza
06:39 PM Revision 11020: inputs/VegBank/: mapped stratumtype
Aaron Marcuse-Kubitza
06:27 PM Revision 11019: /README.TXT: Datasource setup: additional steps for new-style datasources: added steps not present in http://wiki.vegpath.org/Adding_new-style_import_to_a_datasource because they were performed all at once for all datasources
Aaron Marcuse-Kubitza
06:24 PM Revision 11018: /README.TXT: Datasource setup: added additional steps for new-style datasources, from http://wiki.vegpath.org/Adding_new-style_import_to_a_datasource
Aaron Marcuse-Kubitza
04:18 PM Revision 11017: bugfix: schemas/vegbien.sql: taxondetermination_set_iscurrent(): is_datasource_current: accept any determinationtype other than the TNRS ones (accepted, matched), so that datasources that provide a custom value for this field (such as VegBank) don't have their taxondeterminations incorrectly treated as non-datasource. this fixes the VegBank bug where datasource taxondeterminations were not being joined on in analytical_stem_view, because the join included a filter for only datasource taxondeterminations, but these taxondeterminations were not being properly treated as such. **this should fix the missing taxonomic information in the VegBank validation extract.**
Aaron Marcuse-Kubitza
11:31 AM Revision 11016: inputs/VegBank/taxonobservation_/map.csv: taxonomic ranks not in VegCore: removed table prefix so they will be automapped (they are globally unique)
Aaron Marcuse-Kubitza
10:33 AM Revision 11015: planning/timeline/timeline.2013.xls: updated for progress
Aaron Marcuse-Kubitza
10:31 AM Revision 11014: planning/timeline/timeline.2013.xls: updated as decided in the conference call
Aaron Marcuse-Kubitza
02:55 AM Revision 11013: inputs//: don't import joined tables, because they are now imported in the taxon_observation.** left-join instead
Aaron Marcuse-Kubitza
01:22 AM Revision 11012: inputs/VegBank/taxon_observation.**/postprocess.sql: run mk_subset_by_row_num_func() to add a subset function that uses sort_col. this is used by column-based import, and also provides a common subsetting/sorting API for all the left-joined views. test.xml.ref: the inserted row count most likely changes because the sort order changes.
Aaron Marcuse-Kubitza
01:05 AM Revision 11011: schemas/util.sql: added mk_subset_by_row_num_func(regclass), which uses a sort_col instead of a row_num column
Aaron Marcuse-Kubitza
12:10 AM Revision 11010: schemas/util.sql: mk_subset_by_row_num_func(): factored creation of no-sort subset function into mk_subset_by_row_num_no_sort_func() so it can be used by other functions
Aaron Marcuse-Kubitza

09/18/2013

11:53 PM Revision 11009: bugfix: inputs/VegBank/stemlocation_/map.csv: remapped stemcount-related fields to OMIT, so that these don't collide with fields of the same name in stemcount_ when they are left-joined together in taxon_observation.** . having the same name causes these to be incorrectly interpreted as shared fkey columns in the NATURAL JOIN (and without the NATURAL JOIN, they would instead be collision errors).
Aaron Marcuse-Kubitza
10:35 PM Revision 11008: bugfix: inputs/VegBank/stemlocation_/postprocess.sql: added missing index on aggregateOrganismObservationID, needed for the 1:many portion of the taxon_observation.** left-join
Aaron Marcuse-Kubitza
10:28 PM Revision 11007: inputs/VegBank/stemcount_/postprocess.sql: moved stemcount___parent index before the derived columns section because it does not depend on them
Aaron Marcuse-Kubitza
10:26 PM Revision 11006: bugfix: inputs/VegBank/stemcount_/postprocess.sql: added missing index on taxonOccurrenceID, needed for the 1:many portion of the taxon_observation.** left-join
Aaron Marcuse-Kubitza
10:14 PM Revision 11005: schemas/util.sql: added ||% operator to append to escaped strings (the % indicates an identifier, as in Perl hashes and one of the x86 assembler syntaxes for registers
Aaron Marcuse-Kubitza
03:50 PM Revision 11004: bugfix: inputs/VegBank/taxon_observation.**/postprocess.sql: added sort_col (=identificationID) at beginning because column-based import will always sort a view by the first column, which may lead to slow query plans if the first column is not a joined table's pkey
Aaron Marcuse-Kubitza
02:04 PM Revision 11003: inputs/VegBank/taxon_observation.**/postprocess.sql: documented that there is no row_num because left-join to stemcount_, stemlocation_ adds rows to each taxonobservation_
Aaron Marcuse-Kubitza
02:03 PM Revision 11002: bugfix: inputs/VegBank/taxon_observation.**/postprocess.sql: removed row_num (=identificationID), because there is actually more than one row per VegBank taxonobservation_, so this does not properly enumerate the view rows. this is because there is a 1:many left-join to stemcount_, stemlocation_ which adds rows to each taxonobservation_. since the row_num is gone, any row-subsetting of the view using OFFSET will always need to materialize the entire view up to the OFFSET value. this works for smaller datasources like VegBank that fit almost entirely into one column-based import chunk (1 million rows), but not for larger datasources like FIA where it would be much slower to materialize all preceding 16 million rows on the last chunk (which is what OFFSET normally does with left-joins).
Aaron Marcuse-Kubitza
01:51 PM Revision 11001: bugfix: inputs/VegBank/taxon_observation.**/: generated header.csv and related files, which were previously not generated because the error in `rm header.csv` aborted the runscript
Aaron Marcuse-Kubitza

09/17/2013

10:05 PM Revision 11000: bugfix: lib/runscripts/*: calls to rm: use `rm -f` instead to avoid an error (which aborts the program) if the file does not yet exist
Aaron Marcuse-Kubitza

09/16/2013

07:51 AM Revision 10999: inputs/VegBank/: added taxon_observation.** left-join of the tables, using the steps at http://wiki.vegpath.org/Left-joining_a_datasource
Aaron Marcuse-Kubitza
07:48 AM Revision 10998: inputs/VegBank/taxonobservation_/create.sql: join starting with taxoninterpretation so that we can use the taxoninterpretation_id as the row_num (text strings, formed from concatenated #s cannot be used as a row_num). there is only 1 taxonobservation without a taxoninterpretation, so we can just include one row for each taxoninterpretation.
Aaron Marcuse-Kubitza
02:32 AM Revision 10997: bugfix: inputs/VegBank/taxonobservation_/test.xml.ref: updated after reloading staging table. this fixed a bug where observationGranularity apparently either did not exist or was not the right type of constant column to be properly inlined the last time the tester was run. the inlining is important for using metadata switches to generate the correct XML import script.
Aaron Marcuse-Kubitza
12:13 AM Revision 10996: bugfix: lib/sh/make.sh: don't allow rm to override remake if an invoked script uses this file. this fixes a bug in `rm=1 inputs/.../.../run` where the remake action would be invoked on the map_table command even though it had been suppressed, because it was run externally (i.e. make.sh was reloaded) and the rm=1 flag was still active
Aaron Marcuse-Kubitza

09/15/2013

10:50 PM Revision 10995: schemas/VegCore/ERD/VegCore.ERD.png.map.htm: reordered in dependency order: moved traceable before party because party is a subclass of it
Aaron Marcuse-Kubitza
10:02 PM Revision 10994: bugfix: inputs/input.Makefile: `%/install: %/create.sql`: don't include %/header.csv as a target, so that it won't get deleted if the install fails (especially on a step that happens *after* the header is exported)
Aaron Marcuse-Kubitza
09:07 PM Revision 10993: inputs/VegBank/taxonobservation_/postprocess.sql: added primary key. note that the inserted row count changes, most likely because the rows are now in sorted order.
Aaron Marcuse-Kubitza
01:54 PM Revision 10992: inputs/VegBank/stemcount_/postprocess.sql: added primary key. note that the inserted row count changes, most likely because the rows are now in sorted order.
Aaron Marcuse-Kubitza
01:27 PM Revision 10991: bugfix: schemas/util.sql: mk_subset_by_row_num_func(): need explicit ORDER BY on the row_num_col, to prevent PostgreSQL from sorting the rows in reverse (they will be sorted because of the index scan, but the direction is otherwise indeterminate)
Aaron Marcuse-Kubitza
01:20 PM Revision 10990: schemas/util.sql: mk_subset_by_row_num_func(): support tables whose pkeys don't start with 1 (such as the VegBank tables), by calculating the smallest row_num from the table
Aaron Marcuse-Kubitza
01:03 PM Revision 10989: schemas/util.sql: offset2row_num(), limit2row_num(): added optional min_row_num, for tables whose serial pkeys start from a value other than 1
Aaron Marcuse-Kubitza
12:36 PM Revision 10988: schemas/util.sql: added qual_name(regclass)
Aaron Marcuse-Kubitza
08:36 AM Revision 10987: schemas/util.sql: added esc_name__append()
Aaron Marcuse-Kubitza
08:09 AM Revision 10986: schemas/util.sql: added col__min()
Aaron Marcuse-Kubitza
07:34 AM Revision 10985: schemas/util.sql: added limit2row_num() and use it in mk_subset_by_row_num_func() for clarity
Aaron Marcuse-Kubitza
07:26 AM Revision 10984: schemas/util.sql: added offset2row_num() and use it in mk_subset_by_row_num_func() for clarity
Aaron Marcuse-Kubitza
07:25 AM Revision 10983: bugfix: schemas/Makefile: `%/install: vegbien.sql`: when replacing public with the specified schema name, only perform the replacement if the schema is named something other than public. this prevents text like "public schema" inside comments from being "-escaped.
Aaron Marcuse-Kubitza
06:50 AM Revision 10982: planning/timeline/timeline.2013.xls: updated for progress
Aaron Marcuse-Kubitza
06:25 AM Revision 10981: bugfix: /README.TXT: to backup files not in Time Machine: need to use -E option to sudo to preserve env, after installing the latest system update
Aaron Marcuse-Kubitza
05:47 AM Revision 10980: schemas/VegCore/ERD/VegCore.ERD.png.map.htm: reordered in dependency order
Aaron Marcuse-Kubitza
05:40 AM Revision 10979: schemas/VegCore/VegCore.ERD.mwb: regenerated exports and udpated image map
Aaron Marcuse-Kubitza
05:17 AM Revision 10978: schemas/VegCore/VegCore.ERD.mwb: party: added required name field
Aaron Marcuse-Kubitza
05:12 AM Revision 10977: schemas/VegCore/VegCore.ERD.mwb: fixed table positions
Aaron Marcuse-Kubitza
05:09 AM Revision 10976: schemas/VegCore/VegCore.ERD.mwb: event: added event_unique_within_parent, event_unique_within_place unique indexes
Aaron Marcuse-Kubitza
04:57 AM Revision 10975: schemas/VegCore/VegCore.ERD.mwb: source: split into source and dataset tables, since many of the source fields (first_publisher, data_owners, contacts) were only applicable to datasets. note that a dataset is specifically something *requiring attribution*, while a source just indicates where something came from. non-dataset sources are useful e.g. as the taxon_concept.according_to.
Aaron Marcuse-Kubitza
03:48 AM Revision 10974: schemas/VegCore/VegCore.ERD.mwb: source.owners: renamed to data_owners for clarity. documented that this is the parties who must be given attribution, such as copyrightholders.
Aaron Marcuse-Kubitza
03:39 AM Revision 10973: schemas/VegCore/VegCore.ERD.mwb: source: allow multiple contacts
Aaron Marcuse-Kubitza
03:34 AM Revision 10972: schemas/VegCore/VegCore.ERD.mwb: source: added contact (different from owners)
Aaron Marcuse-Kubitza
03:23 AM Revision 10971: schemas/VegCore/VegCore.ERD.mwb: model options: templates for new column names, etc.: configured to our conventions (pkey is "id"; columns are unique within the table rather than being prefixed with the table name)
Aaron Marcuse-Kubitza
03:08 AM Revision 10970: schemas/VegCore/VegCore.ERD.mwb: source: allow multiple data owners
Aaron Marcuse-Kubitza
03:04 AM Revision 10969: schemas/VegCore/VegCore.ERD.mwb: documented that record is the record in the *source* data, and traceable is a row that can have associated source information
Aaron Marcuse-Kubitza
03:03 AM Revision 10968: schemas/VegCore/VegCore.ERD.mwb: removed relationship table, because this is too general, and custom relationships between entities are better indicated in the applicable hstore extender field for the table in question. record: split into record and traceable tables, where record is instead the record in the *source* data, and traceable indicates that a row has associated source information.
Aaron Marcuse-Kubitza
02:36 AM Revision 10967: bugfix: schemas/VegCore/VegCore.ERD.mwb: party_list_entry: use just a single id field as the primary key, because the pkey must be a single text string in order to uniquely identify the row by a global ID
Aaron Marcuse-Kubitza
02:25 AM Revision 10966: schemas/VegCore/VegCore.ERD.mwb: party_list_entry: added role field
Aaron Marcuse-Kubitza
02:01 AM Revision 10965: schemas/VegCore/VegCore.ERD.mwb: regenerated exports and udpated image map
Aaron Marcuse-Kubitza
01:53 AM Revision 10964: schemas/VegCore/VegCore.ERD.mwb: taxa_sampling_event: link directly to project, so that you don't have to search the parent event hierarchy to find the associated project
Aaron Marcuse-Kubitza
01:50 AM Revision 10963: schemas/VegCore/VegCore.ERD.mwb: fixed table positions
Aaron Marcuse-Kubitza
01:48 AM Revision 10962: schemas/VegCore/VegCore.ERD.mwb: spaced tables out more
Aaron Marcuse-Kubitza
01:36 AM Revision 10961: schemas/VegCore/VegCore.ERD.mwb: taxon_occurrence: require all taxon_occurrences to have an associated within_place (which may be set to Earth if the coordinates/placename is unknown)
Aaron Marcuse-Kubitza
01:30 AM Revision 10960: schemas/VegCore/VegCore.ERD.mwb: moved the plot table next the the section label for the plots half of the page
Aaron Marcuse-Kubitza
01:19 AM Revision 10959: schemas/VegCore/VegCore.ERD.mwb: taxon_observation: allow any taxon_observation to link directly to a taxa_sampling_event, not just aggregate_observation. this allows any taxon_observation's methodology information to be found directly by joining to taxa_sampling_event. previously, one had to join to event.parent and then taxa_sampling_event, and it was not clear that event.parent would even *be* a taxa_sampling_event.
Aaron Marcuse-Kubitza
01:07 AM Revision 10958: schemas/VegCore/VegCore.ERD.mwb: aggregate_observation: link to rather than being a taxa_sampling_event, because taxa_sampling_event was actually intended to be the container event in which various types of taxon_observations occur, rather than a type of taxon_observation itself. rearranged tables to avoid them covering any lines.
Aaron Marcuse-Kubitza

09/14/2013

11:53 PM Revision 10957: schemas/VegCore/VegCore.ERD.mwb: renamed GNRS & geovalidation steps to just geoscrubbing steps for clarity
Aaron Marcuse-Kubitza
11:48 PM Revision 10956: schemas/VegCore/VegCore.ERD.mwb: taxon_occurrence: added within_place to allow easily locating all the taxon_occurrences in a plot. (some taxon_occurrences require within_place, but there was no common superclass field to allow joining to just a single table.)
Aaron Marcuse-Kubitza
11:24 PM Revision 10955: schemas/VegCore/ERD/VegCore.ERD.png.map.htm: moved reobservable tables (individual, specimen, etc.) before aggregate_observation tables because these are less abstract
Aaron Marcuse-Kubitza
11:21 PM Revision 10954: schemas/VegCore/VegCore.ERD.mwb: regenerated exports and udpated image map
Aaron Marcuse-Kubitza
11:15 PM Revision 10953: schemas/VegCore/VegCore.ERD.mwb: individual_count: inherit from taxon_presence because this is also a type of taxon_observation (and specifically, a taxon_presence), in addition to the general taxon_presence it's associated with. it is possible e.g. for different diameter classes to be counted by different collectors, and thus to need separate taxon_observations to store the different collectors.
Aaron Marcuse-Kubitza
11:07 PM Revision 10952: schemas/VegCore/VegCore.ERD.mwb: added taxon_occurrence to store the underlying occurrence for each taxon_observation. we cannot use reobservable for this because not all taxon_observations are reobservable, but the one-time observations still have an associated occurrence. moved reobservable.current_determination to taxon_occurrence and renamed it to current_observation (this can be used to find the latest observation for each occurrence).
Aaron Marcuse-Kubitza
09:47 PM Revision 10951: planning/timeline/timeline.2013.xls: added spacing between later column and weeks to make clear that dots in the later column do not correspond to a week
Aaron Marcuse-Kubitza
09:26 PM Revision 10950: inputs/FIA/occurrence_all/: renamed to taxon_observation.** to clarify what an occurrence is. taxon_observation has the same meaning as in VegCore, where *each taxon_determination is considered a separate taxon_observation* of the associated specimen or vouchered plant (vegpath.org/VegCore/ERD/). (note that having multiple taxon_determinations only makes sense when there is something to reobserve.) the .** is SQL dotpath syntax (wiki.vegpath.org/SQL_dotpaths) for the recursive expansion of all tables to which taxon_observation has forward fkeys (i.e. "the left-join").
Aaron Marcuse-Kubitza
09:01 PM Revision 10949: inputs/FIA/occurrence_all/postprocess.sql: use :table_str var instead of hardcoding the table name as occurrence_all, to avoid needing to change this file when renaming the view
Aaron Marcuse-Kubitza
05:38 PM Revision 10948: inputs/FIA/occurrence_all/postprocess.sql: renamed occurrence_all--row_num to just row_num because this is for the entire view, rather than a specific table in it, and thus does not need a disambiguating table prefix. this also avoids embedding the view name in its own columns.
Aaron Marcuse-Kubitza
04:34 PM Revision 10947: inputs/FIA/*/map.csv: use -- to separate the table and column name instead of - , to conform with the u-name format (wiki.vegpath.org/u-name#format), which works even when only one of _- can be used in the name. -s are needed in this case to linewrap the column on a separate line as the table in phpPgAdmin.
Aaron Marcuse-Kubitza
04:20 PM Revision 10946: lib/runscripts/datasrc_dir.run: added postprocess target to run postprocess in just the table subdirs, skipping any additional subdirs that don't have this target
Aaron Marcuse-Kubitza
04:19 PM Revision 10945: lib/runscripts/datasrc_dir.run: @subdirs: moved import_order.txt subdirs into separate @table_subdirs, which provides access to just the table subdirs when the user adds other dirs to @subdirs
Aaron Marcuse-Kubitza

09/12/2013

06:43 PM Revision 10944: inputs/VegBank/: prepended the table name to each column name to prevent column collisions, using the steps at http://wiki.vegpath.org/Left-joining_a_datasource
Aaron Marcuse-Kubitza
06:17 PM Revision 10943: inputs/VegBank/: switched to new-style import, using the steps at http://wiki.vegpath.org/Adding_new-style_import_to_a_datasource
Aaron Marcuse-Kubitza
06:13 PM Revision 10942: bugfix: inputs/VegBank/stemlocation_/map.csv: put columns in table order, which is needed by new-style import
Aaron Marcuse-Kubitza
05:57 PM Revision 10941: inputs/VegBank/stemlocation_/: translated one-to-many mappings to postprocessing derived columns, using the steps at http://wiki.vegpath.org/Adding_new-style_import_to_a_datasource#Translating-filters-to-postprocessing-derived-columns
Aaron Marcuse-Kubitza
05:49 PM Revision 10940: bugfix: inputs/VegBank/taxonobservation_/map.csv: put columns in table order, which is needed by new-style import
Aaron Marcuse-Kubitza
05:26 PM Revision 10939: bugfix: inputs/VegBank/plot_/postprocess.sql: coordinateUncertaintyInMeters: need to use GREATEST() instead of _alt() to handle cases where the coordinate uncertainty is > than the fuzzing uncertainty, where you wouldn't want to just use the smaller fuzzing uncertainty
Aaron Marcuse-Kubitza
05:20 PM Revision 10938: inputs/VegBank/plot_/: translated multi-column filters to postprocessing derived columns, using the steps at http://wiki.vegpath.org/Adding_new-style_import_to_a_datasource#Translating-filters-to-postprocessing-derived-columns
Aaron Marcuse-Kubitza
05:11 PM Revision 10937: inputs/VegBank/plot_/postprocess.sql: map_*() derived cols: updated runtime
Aaron Marcuse-Kubitza
05:10 PM Revision 10936: inputs/VegBank/plot_/: translated single-column filters to postprocessing derived columns, using the steps at http://wiki.vegpath.org/Adding_new-style_import_to_a_datasource#Translating-filters-to-postprocessing-derived-columns
Aaron Marcuse-Kubitza
04:36 PM Revision 10935: inputs/VegBank/stemcount_/: translated multi-column filters to postprocessing derived columns, using the steps at http://wiki.vegpath.org/Adding_new-style_import_to_a_datasource#Translating-filters-to-postprocessing-derived-columns
Aaron Marcuse-Kubitza
04:31 PM Revision 10934: inputs/VegBank/stemlocation_/: translated multi-column filters to postprocessing derived columns, using the steps at http://wiki.vegpath.org/Adding_new-style_import_to_a_datasource#Translating-filters-to-postprocessing-derived-columns
Aaron Marcuse-Kubitza
04:30 PM Revision 10933: inputs/VegBank/taxonobservation_/postprocess.sql: scientificName: recorded runtime (15 s)
Aaron Marcuse-Kubitza
04:15 PM Revision 10932: inputs/VegBank/taxonobservation_/: translated multi-column filters to postprocessing derived columns, using the steps at http://wiki.vegpath.org/Adding_new-style_import_to_a_datasource#Translating-filters-to-postprocessing-derived-columns
Aaron Marcuse-Kubitza
04:14 PM Revision 10931: inputs/VegBank/taxonobservation_/: translated multi-column filters to postprocessing derived columns, using the steps at http://wiki.vegpath.org/Adding_new-style_import_to_a_datasource#Translating-filters-to-postprocessing-derived-columns
Aaron Marcuse-Kubitza
03:37 PM Revision 10930: inputs/FIA/occurrence_all/postprocess.sql: use much simpler LEFT JOINs instead of nested RIGHT JOINs, which required lots of () to get them to happen in the right order. note that the columns are now provided in reverse instead of forwards path order, but this is still much clearer than the nested mess of RIGHT JOINs. this approach can also be used to simplify VegBank's joins.
Aaron Marcuse-Kubitza
03:34 PM Revision 10929: bugfix: lib/runscripts/view.run: remake_VegBIEN_mappings(): also need to remake header.csv, not just map.csv as for tables, because view columns may change when the view is regenerated
Aaron Marcuse-Kubitza
02:42 PM Revision 10928: schemas/VegCore/VegCore.ERD.mwb: specimen: changed definition to "something collected from a plant" rather than just "a physical part of a plant", to support using this table for identifying pictures and descriptions of a plant (as DwC does)
Aaron Marcuse-Kubitza
02:28 PM Revision 10927: schemas/VegCore/VegCore.ERD.mwb: regenerated exports and udpated image map
Aaron Marcuse-Kubitza
02:24 PM Revision 10926: schemas/VegCore/VegCore.ERD.mwb: reobservable_presence: allow it to be vouchered by any reobservable element (including a tagged individual), not just a specimen
Aaron Marcuse-Kubitza
02:01 PM Revision 10925: schemas/VegCore/VegCore.ERD.mwb: specimen.defining_data: clarified that the observations in this are actually a subset of individual_observation.traits (specifically, the subset that can be used to make a taxonomic redetermination). information in this field should therefore always also be stored in individual_observation.traits.
Aaron Marcuse-Kubitza
01:54 PM Revision 10924: schemas/VegCore/VegCore.ERD.mwb: specimen: added specimen_unique_in_individual_observation unique constraint, analogous to specimen_unique_in_individual
Aaron Marcuse-Kubitza
01:34 PM Revision 10923: schemas/VegCore/VegCore.ERD.mwb: regenerated exports and udpated image map
Aaron Marcuse-Kubitza
01:29 PM Revision 10922: schemas/VegCore/VegCore.ERD.mwb: specimen: added defining_data, which for a digital-only specimen, stores the information that comprises the specimen. note that a taxon_presence without a physical voucher can still qualify as reobservable if a detailed description of it is provided in this field, to make taxonomic redeterminations on. for datasources like VegBank, which incorrectly allow multiple taxon_determinations for *any* type of taxon_observation, their taxonomic redeterminations would actually be considered invalid if made on a purely taxon_presence observation (i.e. just a taxon name) without a detailed description that could be used to make a redetermination. this is different than the scrubbing of a taxon name, which relates a taxon name to another taxon name, rather than a taxon_observation to a completely different taxon name.
Aaron Marcuse-Kubitza
12:35 PM Revision 10921: bugfix: lib/sh/util.sh: set_fds(): don't add surrounding quotes to empty redirect dest
Aaron Marcuse-Kubitza
12:31 PM Revision 10920: bugfix: lib/sh/util.sh: set_fds(): need to check if redirect is empty *before* escaping it with `printf %q`, which may add surrounding quotes to an empty string
Aaron Marcuse-Kubitza
11:40 AM Revision 10919: planning/timeline/timeline.2013.xls: attribution and conditions of use: documented that Brad/Brian/Bob should work on this, as decided in the conference call (wiki.vegpath.org/2013-09-12_conference_call#data-provider-metadata)
Aaron Marcuse-Kubitza
05:47 AM Revision 10918: planning/timeline/timeline.2013.xls: reformatted to fit all rows and all per-week columns on one page
Aaron Marcuse-Kubitza
05:30 AM Revision 10917: planning/timeline/timeline.2013.xls: streamline process of mapping and adding a new datasource: added subtask to create interactive scripts for each import step
Aaron Marcuse-Kubitza
05:15 AM Revision 10916: planning/timeline/timeline.2013.xls: improve and complete data provider metadata: moved to end because this can also been added manually to the source table, and does not have to be in place before running column-based import
Aaron Marcuse-Kubitza
05:09 AM Revision 10915: planning/timeline/timeline.2013.xls: flatten the datasources to a common schema: added subtask to left-join unvalidated datasources since they need the flattening in order to validate them properly
Aaron Marcuse-Kubitza
04:21 AM Revision 10914: planning/timeline/timeline.2013.xls: rebalanced dots
Aaron Marcuse-Kubitza
04:15 AM Revision 10913: planning/timeline/timeline.2013.xls: moved items marked later to separate section at bottom
Aaron Marcuse-Kubitza
04:13 AM Revision 10912: planning/timeline/timeline.2013.xls: moved revisions to schema under datasource validations because schema changes are largely driven by validations problems uncovered
Aaron Marcuse-Kubitza
04:12 AM Revision 10911: planning/timeline/timeline.2013.xls: split tasks into weeks
Aaron Marcuse-Kubitza
03:47 AM Revision 10910: planning/timeline/timeline.2013.xls: updated for progress
Aaron Marcuse-Kubitza
03:35 AM Revision 10909: planning/timeline/timeline.2013.xls: split months into (currently identical) weeks
Aaron Marcuse-Kubitza
03:19 AM Revision 10908: planning/timeline/timeline.2013.xls: added During month of label above months
Aaron Marcuse-Kubitza
03:09 AM Revision 10907: planning/timeline/timeline.2013.xls: switched to portrait mode to better fit the new format, which hides columns for past months
Aaron Marcuse-Kubitza
03:05 AM Revision 10906: planning/timeline/timeline.2013.xls: hid crossed out rows to show just the remaining tasks
Aaron Marcuse-Kubitza
03:03 AM Revision 10905: planning/timeline/timeline.2013.xls: crossed out avoid DB restructuring when ingesting a new datasource, because FIA (which is flattened before import) *does* properly support optional subplots and diamond linking of subplots to parent plot events, which were necessary to ingest an arbitrary flattened plots datasource
Aaron Marcuse-Kubitza
02:55 AM Revision 10904: planning/timeline/timeline.2013.xls: crossed out fully-completed tasks. rebalanced dots.
Aaron Marcuse-Kubitza
02:46 AM Revision 10903: planning/timeline/timeline.2013.xls: moved switching to new-style import to top of streamline process of mapping and adding a new datasource because this puts all the datasource adding steps (except filling in the mappings) into one rerunnable script
Aaron Marcuse-Kubitza
02:36 AM Revision 10902: planning/timeline/timeline.2013.xls: hid columns for past months so that the current and future months are right next to each task
Aaron Marcuse-Kubitza
02:31 AM Revision 10901: planning/timeline/timeline.2013.xls: moved streamline process of mapping and adding a new datasource before documentation testing because this will assist the documentation tester in running the import process
Aaron Marcuse-Kubitza
02:26 AM Revision 10900: planning/timeline/timeline.2013.xls: moved geoscrubbing re-run under add any missing columns because this is needed to fully populate the geoscrubbing columns
Aaron Marcuse-Kubitza
02:20 AM Revision 10899: planning/timeline/timeline.2013.xls: added documentation testing, usability testing priority tasks (wiki.vegpath.org/Priority_tasks). lowercased tasks for consistency with the wiki and to avoid needing to sentence case new subtasks.
Aaron Marcuse-Kubitza
01:53 AM Revision 10898: planning/timeline/timeline.2013.xls: moved Flatten the datasources to a common schema under Datasource validations because the query left-joining the tables is needed for validation, and it is much easier to validate datasources when there is only one input table to validate
Aaron Marcuse-Kubitza

09/11/2013

02:52 PM Revision 10897: added derived/biengeo/Geovalidation_and_geoscrubbing_update.presentation.url
Aaron Marcuse-Kubitza

09/09/2013

06:12 PM Revision 10896: added BIEN2/traits_observation_counts.xls
Aaron Marcuse-Kubitza
05:44 PM Revision 10895: /README.TXT: Single datasource import: removed rescrub step because this is not needed by the current TNRS process
Aaron Marcuse-Kubitza
02:04 PM Revision 10894: web/links/index.htm: updated to Firefox bookmarks. MySQL: added steps to add a user if you are not root but have sudo access.
Aaron Marcuse-Kubitza

09/07/2013

08:19 PM Revision 10893: BIEN2/country_species/: svn:ignore the .tsv exports
Aaron Marcuse-Kubitza
08:19 PM Revision 10892: BIEN2/country_species/run: documented runtime (1 min)
Aaron Marcuse-Kubitza
08:15 PM Revision 10891: added BIEN2/country_species/run, which exports each BIEN2 country's species list
Aaron Marcuse-Kubitza
08:14 PM Revision 10890: bugfix: lib/sh/util.sh: set_fds(): need to escape redirect destinations which are files, because they may contain special shell characters
Aaron Marcuse-Kubitza
08:10 PM Revision 10889: lib/sh/util.sh: added rm_prefix()
Aaron Marcuse-Kubitza
07:11 PM Revision 10888: lib/sh/db.sh: mysql_cmd(): added caller usage with connection/login opts
Aaron Marcuse-Kubitza
07:08 PM Revision 10887: lib/sh/db.sh: mysql(), mysql_export(): usage: added database=...
Aaron Marcuse-Kubitza
12:30 AM Revision 10886: planning/timeline/timeline.2013.xls: Data provider validations: renamed to Datasource validations to clarify that this is a validation *of the datasources*, but not necessarily *by the data providers*
Aaron Marcuse-Kubitza

09/05/2013

07:19 PM Revision 10885: /README.TXT: Full database import: added Running individual steps separately label for the section that is not part of the main import, but is useful if the import is aborted part of the way through
Aaron Marcuse-Kubitza
05:02 PM Revision 10884: /README.TXT: moved Single datasource import, Datasource setup to top since these are the most important howtos
Aaron Marcuse-Kubitza
04:14 PM Revision 10883: bugfix: schemas/Makefile: enclose schema names in "" so that they won't be lowercased
Aaron Marcuse-Kubitza
03:56 PM Revision 10882: bugfix: schemas/Makefile, lib/common.Makefile: enclose schema names in "" so that they won't be lowercased
Aaron Marcuse-Kubitza
03:26 PM Revision 10881: /run: geoscrub_input/make(): updated runtime (20 s)
Aaron Marcuse-Kubitza
01:31 PM Revision 10880: planning/timeline/timeline.2013.xls: Data provider validations (spot-checking): moved ahead of Individual datasource refresh as decided in conference call
Aaron Marcuse-Kubitza
01:29 PM Revision 10879: schemas/vegbien.sql: analytical_plot: added aggregateOrganismObservationID from analytical_stem
Aaron Marcuse-Kubitza
08:38 AM Revision 10878: planning/timeline/timeline.2013.xls: updated for progress
Aaron Marcuse-Kubitza
08:37 AM Revision 10877: planning/timeline/timeline.2013.xls: Data provider validations: added subtask for Aggregated validations (counts)
Aaron Marcuse-Kubitza
01:17 AM Revision 10876: inputs/import.stats.xls: analytical DB: updated rowcount
Aaron Marcuse-Kubitza
01:14 AM Revision 10875: inputs/import.stats.xls: updated import times
Aaron Marcuse-Kubitza
01:01 AM Revision 10874: inputs/input.Makefile: reimport: don't remove the existing import first, because it will instead be removed by the publish step. this ensures there is always one complete copy of the datasource in the DB.
Aaron Marcuse-Kubitza
01:00 AM Revision 10873: added backups/vegbien.r10848.backup.md5
Aaron Marcuse-Kubitza
12:59 AM Revision 10872: backups/TNRS.backup.md5: updated
Aaron Marcuse-Kubitza
12:11 AM Revision 10871: bugfix: bin/import_all: use reimport_scrub instead of import_scrub so that the temp suffix of the datasource name is removed
Aaron Marcuse-Kubitza
12:02 AM Revision 10870: inputs/input.Makefile: reimport: use import_publish instead of import so that the reimport replaces the previous import
Aaron Marcuse-Kubitza

09/04/2013

11:59 PM Revision 10869: inputs/input.Makefile: added import_publish, which removes the temp suffix when the import is done
Aaron Marcuse-Kubitza
11:48 PM Revision 10868: bugfix: bin/after_import: run backups/fix_perms right after the backup files are created to make them private
Aaron Marcuse-Kubitza
11:32 PM Revision 10867: bugfix: backups/fix_perms: just make the backups themselves private, since the other files are in svn, and their permissions should match their accessibility through Redmine
Aaron Marcuse-Kubitza
11:06 PM Revision 10866: inputs/*/*/test.xml.ref: updated source.shortname for new datasource name, which now starts out with .new suffix
Aaron Marcuse-Kubitza
05:27 PM Revision 10865: bugfix: bin/make_analytical_db: `/run export_`: don't take input from the terminal, because this causes rm to prompt the user (from a background task) about overwriting the previous export
Aaron Marcuse-Kubitza
05:26 PM Revision 10864: /README.TXT: Full database import: Publish the new import: added runtime (1 min)
Aaron Marcuse-Kubitza
03:00 PM Revision 10863: inputs/input.Makefile: $(map2db): import to datasrc.new instead of plain datasrc, so that the current import of the datasrc is not overwritten
Aaron Marcuse-Kubitza
02:59 PM Revision 10862: inputs/input.Makefile: added publish (`make inputs/src/publish`)
Aaron Marcuse-Kubitza
02:55 PM Revision 10861: bugfix: schemas/vegbien.sql: source: removed testing row that had gotten in during `make schemas/remake`
Aaron Marcuse-Kubitza
02:43 PM Revision 10860: inputs/input.Makefile: added %/publish (`make inputs/src/src.version/publish`)
Aaron Marcuse-Kubitza
02:32 PM Revision 10859: bugfix: schemas/vegbien.sql: datasource_publish(): need to remove the *current* live datasource instead of the datasource to publish. note that datasource_rename() does not currently generate an error if the specified datasource doesn't exist.
Aaron Marcuse-Kubitza
02:27 PM Revision 10858: bugfix: schemas/vegbien.sql: datasource_publish(): run it in a nested transaction so that there is always one published copy of the datasource. (note that a nested transaction is not automatically created for each function, http://stackoverflow.com/questions/6274457/set-isolation-level-for-postgresql-stored-procedures?In_PG_your_procedures_aren%27t_separate_transactions#answer-6283201 .)
Aaron Marcuse-Kubitza
01:57 PM Revision 10857: schemas/vegbien.sql: added datasource_publish()
Aaron Marcuse-Kubitza
01:53 PM Revision 10856: schemas/vegbien.sql: added datasource_rename()
Aaron Marcuse-Kubitza
01:51 PM Revision 10855: schemas/vegbien.sql: added rm_version_suffix()
Aaron Marcuse-Kubitza
01:28 PM Revision 10854: bin/map: allow user to override the source env var, which is used as the source.shortname value in the DB
Aaron Marcuse-Kubitza
09:43 AM Revision 10853: exports/: svn:ignore *.zip
Aaron Marcuse-Kubitza
09:42 AM Revision 10852: inputs/WIN/Specimen/unmapped_terms.csv: updated
Aaron Marcuse-Kubitza
09:37 AM Revision 10851: inputs/import.stats.xls: updated import times
Aaron Marcuse-Kubitza

08/31/2013

07:47 PM Revision 10850: /README.TXT: Full database import: time to wait for the import to finish: updated to time in inputs/import.stats.xls
Aaron Marcuse-Kubitza
07:44 PM Revision 10849: bugfix: bin/import_all: `rm inputs/.TNRS/tnrs/tnrs.make.lock`: need to use `"rm"` instead of `rm` so that we don't use any rm alias the user might have in their shell (import_all is run in the calling shell so that the jobs are owned by the calling shell)
Aaron Marcuse-Kubitza
07:36 PM Revision 10848: bugfix: mappings/VegCore-VegBIEN.csv: don't map datasetURL to source.url for taxa-only data (this mapping should only occur for Source tables)
Aaron Marcuse-Kubitza
07:27 PM Revision 10847: bin/import_all: added step to remove any leftover TNRS lockfile (previously done manually)
Aaron Marcuse-Kubitza
06:46 PM Revision 10846: planning/timeline/timeline.2013.xls: updated for progress
Aaron Marcuse-Kubitza
06:32 PM Revision 10845: bugfix: lib/sql_io.py: put_table(): Getting output table pkeys of existing/inserted rows: need to include the index cond in the join condition here, too (using var join_custom_cond), so that an index scan can be used instead of a much slower full-table sort
Aaron Marcuse-Kubitza
06:01 PM Revision 10844: bugfix: schemas/vegbien.sql: locationevent: locationevent_unique_within_location unique index: added COALESCE(...) expression around location_id since it is nullable, and this is needed for the left and right sides of the join to exactly match up to use an index scan
Aaron Marcuse-Kubitza
05:52 PM Revision 10843: bugfix: lib/sql_io.py: put_table(): DuplicateKeyException: need to include any index cond in the join condition, so that an index scan can be used instead of a much slower full-table sort (otherwise the query planner will not know that it can restrict results to rows satisfying the index cond)
Aaron Marcuse-Kubitza
05:48 PM Revision 10842: lib/sql_gen.py: Join: added custom_cond param that can be used to add to the JOIN condition
Aaron Marcuse-Kubitza
01:02 AM Revision 10841: lib/sql.py: distinct_table(): support custom filters on the distincting query
Aaron Marcuse-Kubitza
01:01 AM Revision 10840: lib/sql_gen.py: ColValueCond: support conds that are just plain SQL (without separate left and right sides) using special custom_cond flag value
Aaron Marcuse-Kubitza

08/30/2013

11:18 PM Revision 10839: bugfix: inputs/input.Makefile: %/test: in by_col mode, also need to run %/test.by_col.xml
Aaron Marcuse-Kubitza
10:38 PM Revision 10838: lib/sql_io.py: ensure_cond(): documented meaning of passed, failed params (at least one row passed/failed the constraint)
Aaron Marcuse-Kubitza
06:28 PM Revision 10837: fix: web/links/index.htm: PostgreSQL: vacuuming: moved info about autovacuum process being aborted to correct bookmark
Aaron Marcuse-Kubitza
06:25 PM Revision 10836: web/links/index.htm: updated to Firefox bookmarks. PostgreSQL: added info on vacuuming and analyzing.
Aaron Marcuse-Kubitza
06:06 PM Revision 10835: lib/runscripts/util.run: usage: documented that this usage also applies to all files that include this file
Aaron Marcuse-Kubitza
06:06 PM Revision 10834: lib/runscripts/util.run: usage: clarified that the cmd to run is a function
Aaron Marcuse-Kubitza
06:03 PM Revision 10833: added schemas/postgresql*.conf.run, which installs these config files and takes care of restarting the server
Aaron Marcuse-Kubitza
06:02 PM Revision 10832: added lib/runscripts/pg.conf.run, which installs PostgreSQL config files
Aaron Marcuse-Kubitza
06:01 PM Revision 10831: added lib/runscripts/install.run, analogous to import.run
Aaron Marcuse-Kubitza
04:39 PM Revision 10830: fix: schemas/postgresql*.conf: turn on autovacuum logging (log_autovacuum_min_duration = 0) so we can verify if autovacuum is happening
Aaron Marcuse-Kubitza
04:35 PM Revision 10829: bugfix: lib/db_xml.py: put_table(): turned off db.autoanalyze, since forcing an ANALYZE after every bulk insert is inefficient for small datasources. the default autovacuum settings in schemas/postgresql.conf should be fine; however, the frequency and/or threshold may need to be increased if autovacuum does not ANALYZE frequently enough to replace db.autoanalyze.
Aaron Marcuse-Kubitza
03:37 PM Revision 10828: /run: taxon_trait/make(): order by scientificName, measurementType as described in the taxon_trait table comment
Aaron Marcuse-Kubitza
03:36 PM Revision 10827: lib/sh/db.sh: mk_select(): added support for ORDER BY
Aaron Marcuse-Kubitza
03:30 PM Revision 10826: /run: added taxon_trait/make()
Aaron Marcuse-Kubitza
03:28 PM Revision 10825: lib/sh/db.sh: added pg_export_table_to_dir(), analogous to pg_export_table_to_dir_no_header()
Aaron Marcuse-Kubitza
03:03 PM Revision 10824: schemas/vegbien.sql: taxon_trait: added query to use to export
Aaron Marcuse-Kubitza
02:52 PM Revision 10823: inputs/NVS/*/map.csv: Taxon Growth Form: mapped to VegBIEN.growthform enum, using http://www.fgdc.gov/standards/projects/FGDC-standards-projects/vegetation/NVCS_V2_FINAL_2008-02.pdf#page=83&section.page=76 . documented values used by each table.
Aaron Marcuse-Kubitza
02:18 PM Revision 10822: lib/sh/util.sh: is_array(): handle unset vars (=false). this fixes a bug in pg_export_table_no_header, which produced the error "lib/sh/util.sh: line 290: declare: cols: not found".
Aaron Marcuse-Kubitza
02:06 PM Revision 10821: fix: lib/sh/util.sh: join(): documented that delim must be a single char
Aaron Marcuse-Kubitza
07:15 AM Revision 10820: bugfix: /README.TXT: on a live machine, you should put the following in your .profile: need to make svn files web-accessible, because these are used by fs.vegpath.org links (such as to the ERD, etc.). note that this does not affect unversioned files, because these get the right permissions on the local machine instead (see Testing > On a development machine, you should put the following in your .profile).
Aaron Marcuse-Kubitza
07:07 AM Revision 10819: /README.TXT: to backup files not in Time Machine: added command to start the PostgreSQL server
Aaron Marcuse-Kubitza
06:58 AM Revision 10818: bugfix: /README.TXT: to synchronize a Mac's settings with my testing machine's: don't upload ~/.profile, etc. to jupiter because these files are different on each machine. they can instead be synced manually.
Aaron Marcuse-Kubitza
06:52 AM Revision 10817: /README.TXT: to backup files not in Time Machine: added command to stop the PostgreSQL server
Aaron Marcuse-Kubitza
06:49 AM Revision 10816: /README.TXT: to synchronize vegbiendev, jupiter, and your local machine: noted that ./fix_perms should be run on all machines
Aaron Marcuse-Kubitza
06:36 AM Revision 10815: removed unused dir analysis/ (originally requested by Jim)
Aaron Marcuse-Kubitza
06:31 AM Revision 10814: bugfix: /README.TXT: to synchronize vegbiendev, jupiter, and your local machine:: added step to run `make backups/TNRS.backup/download live=1`, because bin/sync_upload does not sync this due to filters in backups/.rsync_filter.download
Aaron Marcuse-Kubitza
06:11 AM Revision 10813: /README.TXT: Maintenance: to synchronize vegbiendev, jupiter, and your local machine: added step to run ./fix_perms so that there are fewer permissions diffs to review
Aaron Marcuse-Kubitza
06:07 AM Revision 10812: bugfix: /README.TXT: to synchronize a Mac's settings with my testing machine's: upload: `(cd ~/Dropbox/svn/; svn up)`: use `up` instead so that the needed --force option is applied
Aaron Marcuse-Kubitza
05:37 AM Revision 10811: inputs/VegBank/*/postprocess.sql: added primary keys to derived tables
Aaron Marcuse-Kubitza
05:15 AM Revision 10810: schemas/VegCore/VegCore.ERD.mwb: regenerated exports and udpated image map
Aaron Marcuse-Kubitza
05:11 AM Revision 10809: schemas/VegCore/VegCore.ERD.mwb: individual_observation.individual: documented that it is optional because an individual_observation cannot have an associated individual unless the individual is traceable to a specific plant
Aaron Marcuse-Kubitza
05:09 AM Revision 10808: schemas/VegCore/VegCore.ERD.mwb: individual_observation.place_observed_at: made it optional because some individual_observations (e.g. of the plant a specimen was collected from) may be missing location information. however, an individual_observation cannot have an associated individual unless the individual is traceable to a specific plant.
Aaron Marcuse-Kubitza
05:03 AM Revision 10807: schemas/VegCore/VegCore.ERD.mwb: specimen: added individual_observation, which stores observations about the plant the specimen was collected from. (some specimens may not be traceable to a reobservable individual, but will still have these plant observations.) specimen_observation: adjusted position to fully display the HAS-A connector to specimen.
Aaron Marcuse-Kubitza
03:44 AM Revision 10806: planning/timeline/timeline.2013.xls: updated for progress. rebalanced dots.
Aaron Marcuse-Kubitza
01:39 AM Revision 10805: planning/timeline/timeline.2013.xls: added separate task for Individual datasource refresh (separate from Individual datasource *removal*), because we also need to optimize the reload of datasources. the reload is most likely slow because rows are being added to very large tables.
Aaron Marcuse-Kubitza
01:21 AM Revision 10804: /README.TXT: Single datasource import: run commands in the background, since these are long-running commands
Aaron Marcuse-Kubitza
12:57 AM Revision 10803: planning/timeline/timeline.2013.xls: moved Attribution and conditions of use before Flatten the datasources as suggested in meeting with Mark
Aaron Marcuse-Kubitza
12:42 AM Revision 10802: schemas/vegbien.sql: datasource_rm(): runtime: added runtime of MO (55 min, 0.85 ms/row), which has a much larger # of rows than ACAD (4 million instead of 45,000). updated GBIF runtime estimate (~13 h) with more accurate ms/row from MO.
Aaron Marcuse-Kubitza

08/29/2013

11:19 PM Revision 10801: schemas/vegbien.sql: datasource_rm(): estimated runtime for GBIF (~10 h). note that this is still significantly shorter than the import time (3.4 days).
Aaron Marcuse-Kubitza
11:11 PM Revision 10800: schemas/vegbien.sql: datasource_rm(): documented how to calculate runtime
Aaron Marcuse-Kubitza
11:04 PM Revision 10799: schemas/vegbien.sql: datasource_rm(): documented runtime for ACAD: 30 s; 0.61 ms/row
Aaron Marcuse-Kubitza
05:09 PM Revision 10798: inputs/input.Makefile: rm: use new datasource_rm(), which encapsulates the schema-specific aspects of removing a datasource
Aaron Marcuse-Kubitza
04:59 PM Revision 10797: bugfix: schemas/vegbien.sql: datasource_rm(): set_config(): don't name the is_local param because it is not a named parameter
Aaron Marcuse-Kubitza
04:50 PM Revision 10796: schemas/vegbien.sql: added datasource_rm(). this uses an internal schema-scoping parameter to ensure that the function always operates on tables in the schema it was *defined* in, rather than tables in the search_path. this ensures that when the public schema is renamed (e.g. from an imported version), the function will continue to operate on its own schema rather than whichever schema happens to be called public. this avoids any surprises if you are trying to remove a datasource in one schema, and don't want it to unintentionally be removed in another schema instead.
Aaron Marcuse-Kubitza
04:20 PM Revision 10795: schemas/util.sql: added schema_ident()
Aaron Marcuse-Kubitza
03:20 PM Revision 10794: schemas/util.sql: added schema(regtype), schema(anyelement)
Aaron Marcuse-Kubitza
02:07 PM Revision 10793: inputs/.TNRS/schema.sql: added covering indexes on foreign keys where needed. this enables rows to be cascadingly deleted without a full table scan.
Aaron Marcuse-Kubitza
01:58 PM Revision 10792: schemas/vegbien.sql: added covering indexes on foreign keys where needed. this enables rows to be cascadingly deleted without a full table scan.
Aaron Marcuse-Kubitza

08/27/2013

11:11 PM Revision 10791: planning/timeline/timeline.2013.xls: increased font size for better readability at 100% (which is also the printed size). note that the timeline is normally zoomed in, so you don't see the actual font size.
Aaron Marcuse-Kubitza
10:52 PM Revision 10790: inputs/.TNRS/schema.sql: tnrs: instructions for when changing this table's schema: updated to use new `inputs/.TNRS/data.sql.run refresh`
Aaron Marcuse-Kubitza
10:50 PM Revision 10789: inputs/.TNRS/data.sql.run: added refresh() target which runs inputs/test_taxonomic_names/test_scrub
Aaron Marcuse-Kubitza
10:34 PM Revision 10788: inputs/test_taxonomic_names/test_scrub: added step to update inputs/.TNRS/data.sql to the now-refreshed TNRS sample data (this updating step is now automated)
Aaron Marcuse-Kubitza
10:32 PM Revision 10787: inputs/.TNRS/schema.sql: tnrs: updated steps to run when changing this table's schema, to use new TNRS editing workflow
Aaron Marcuse-Kubitza
10:14 PM Revision 10786: inputs/.TNRS/data.sql: re-ran TNRS using `inputs/test_taxonomic_names/test_scrub; rm=1 inputs/.TNRS/data.sql.run export_`
Aaron Marcuse-Kubitza
10:13 PM Revision 10785: /README.TXT: Full database import: fixing TNRS errors: noted that inputs/test_taxonomic_names/test_scrub re-runs TNRS
Aaron Marcuse-Kubitza
10:12 PM Revision 10784: /README.TXT: Full database import: fixing TNRS errors: updated instructions for new TNRS schema editing workflow
Aaron Marcuse-Kubitza
09:53 PM Revision 10783: inputs/.TNRS/data.sql: generate from the DB using `rm=1 inputs/.TNRS/data.sql.run export_` instead of being a hand-edited file
Aaron Marcuse-Kubitza
09:50 PM Revision 10782: added inputs/.TNRS/data.sql.run for syncing data.sql directly with the DB without needing to use inputs/test_taxonomic_names/test_scrub just to export the sample data. (however, when modifying the tnrs table, it may still be easier to generate new sample data using test_scrub rather than refactoring the table in place.)
Aaron Marcuse-Kubitza
09:35 PM Revision 10781: added lib/runscripts/data.pg.sql.run (analogous to schema.pg.sql.run for data-only SQL scripts)
Aaron Marcuse-Kubitza
09:32 PM Revision 10780: added lib/runscripts/file.pg.sql.run and use it in schema.pg.sql.run
Aaron Marcuse-Kubitza
09:25 PM Revision 10779: added lib/runscripts/schema.pg.sql.run and use it in inputs/.TNRS/schema.sql.run
Aaron Marcuse-Kubitza
09:18 PM Revision 10778: inputs/.TNRS/schema.sql: generate from the DB using `rm=1 inputs/.TNRS/schema.sql.run export_` instead of being a hand-edited file. this makes it much easier to edit the (now frequently-changing) TNRS schema directly in pgAdmin (which is graphical), rather than having to manually copy SQL changes from pgAdmin to the file.
Aaron Marcuse-Kubitza
09:15 PM Revision 10777: inputs/.TNRS/schema.sql.run: export_(): added usage
Aaron Marcuse-Kubitza
09:12 PM Revision 10776: added inputs/.TNRS/schema.sql.run, which syncs schema.sql with the DB
Aaron Marcuse-Kubitza
09:07 PM Revision 10775: bugfix: lib/sh/db.sh: pg_dump(): don't default $struct flag to on, because both structure and data should be printed by default
Aaron Marcuse-Kubitza
09:02 PM Revision 10774: lib/sh/db.sh: pg_dump(): added create_schema= flag to remove CREATE SCHEMA statements (useful if the schema already exists)
Aaron Marcuse-Kubitza
08:59 PM Revision 10773: bugfix: lib/sh/util.sh: set_fds(): remove empty redirects resulting from using `redirs= cmd...` to clear the redirs and then using $redirs as an array
Aaron Marcuse-Kubitza
08:47 PM Revision 10772: fix: lib/sh/util.sh: set_fds(): documented that it does not currently support redirecting an fd to itself (due to bash bugs that require the dest fd to be closed before it can be reopened)
Aaron Marcuse-Kubitza
08:44 PM Revision 10771: bugfix: lib/sh/util.sh: stdout2fd(): don't add >&$fd redirect if the fd is 1, because redir does not currently support redirecting an fd to itself (due to bash bugs that require the dest fd to be closed before it can be reopened)
Aaron Marcuse-Kubitza
08:40 PM Revision 10770: lib/sh/util.sh: filter_fd(): factored out >() subshell command into stdout2fd() for clarity
Aaron Marcuse-Kubitza
08:33 PM Revision 10769: bugfix: lib/sh/util.sh: redir(): unset redirs so that you don't redirect again in the invoked command
Aaron Marcuse-Kubitza
08:29 PM Revision 10768: fix: lib/sh/util.sh: filter_fd(): documented that ${redirs[@]} must not be set to an outer value
Aaron Marcuse-Kubitza
07:41 PM Revision 10767: fix: inputs/ARIZ/omoccurrences/map.csv: occurrenceID: remapped to EQUIV#to:occid instead of DUPLICATE#of:occid since these are not exact duplicates
Aaron Marcuse-Kubitza
07:30 PM Revision 10766: lib/runscripts/util.run: added to_top_file alias for use with $top_file
Aaron Marcuse-Kubitza
07:10 PM Revision 10765: lib/sh/local.sh: added pg_dump_local()
Aaron Marcuse-Kubitza
07:09 PM Revision 10764: lib/sh/db.sh: added pg_dump(), using the code in bin/pg_dump_vegbien with clarity improvements
Aaron Marcuse-Kubitza
07:06 PM Revision 10763: lib/sh/db.sh: added pg_cmd() (analogous to mysql_cmd() for PostgreSQL), and use it in psql(), so that other PostgreSQL operations can use this to set the PG* connection/login vars
Aaron Marcuse-Kubitza
05:36 PM Revision 10762: planning/timeline/timeline.2013.xls: updated dots for new priority order
Aaron Marcuse-Kubitza
05:33 PM Revision 10761: planning/timeline/timeline.2013.xls: moved optimization of individual datasource removal before flattening the datasources to a common schema as suggested in meeting with Mark
Aaron Marcuse-Kubitza
04:00 PM Revision 10760: lib/runscripts/datasrc_dir.run: include of import.run: use .rel instead of `. "$(dirname "${BASH_SOURCE[0]}")"/...`
Aaron Marcuse-Kubitza
03:59 PM Revision 10759: lib/runscripts/datasrc_dir.run: moved commands related to any runscript in the datasrc dir to new in_datasrc_dir.run
Aaron Marcuse-Kubitza
03:57 PM Revision 10758: inputs/*/Specimen/test.xml.ref with eventDate->dateCollected mappings: updated test outputs to match mapping
Aaron Marcuse-Kubitza
03:52 PM Revision 10757: some inputs/*/*/unmapped_terms.csv: updated now that datasetURL is mapped (this does not affect the mappings because it is only mapped for Source tables)
Aaron Marcuse-Kubitza
03:43 PM Revision 10756: bugfix: inputs/ARIZ/omoccurrences/map.csv: fixed one-to-many mapping for modified (created by the automapper?)
Aaron Marcuse-Kubitza
02:38 PM Revision 10755: lib/sh/db.sh: pg_export(): added usage
Aaron Marcuse-Kubitza
01:54 PM Revision 10754: inputs/.TNRS/schema.sql: moved source code comments to in-schema COMMENT ON comments so all the info in schema.sql is in the DB
Aaron Marcuse-Kubitza
01:47 PM Revision 10753: inputs/.TNRS/schema.sql: views that use * as the column list: added comments to indicate that this is the case, so that the views can be updated in place rather than only by reinstalling the TNRS schema
Aaron Marcuse-Kubitza
01:20 PM Revision 10752: updated backups/TNRS.backup.md5
Aaron Marcuse-Kubitza
01:19 PM Revision 10751: planning/timeline/timeline.2013.xls: clarified note about the purpose of the dots
Aaron Marcuse-Kubitza
01:03 PM Revision 10750: added backups/vegbien.r10548.backup.md5
Aaron Marcuse-Kubitza
01:03 PM Revision 10749: bugfix: backups/: svn:ignore: removed *.md5, which should be under version control
Aaron Marcuse-Kubitza
12:55 PM Revision 10748: inputs/input.Makefile: scrub: documented that using & (background process) ignores TNRS errors, so that TNRS bugs do not prevent the remaining tables from being imported even if TNRS can't be run
Aaron Marcuse-Kubitza
12:49 PM Revision 10747: inputs/.TNRS/schema.sql: tnrs: util.set_col_types() runtime: updated for most recent ALTER COLUMN TYPE command (9 min)
Aaron Marcuse-Kubitza
12:25 PM Revision 10746: inputs/.TNRS/schema.sql: tnrs.Time_submitted: renamed to batch and added fkey to batch.id. this requires including the batch table in inputs/.TNRS/data.sql, so that the fkey is satisfied (batch entries are already added by bin/tnrs_db.
Aaron Marcuse-Kubitza
11:42 AM Revision 10745: updated backups/TNRS.backup
Aaron Marcuse-Kubitza
11:38 AM Revision 10744: /README.TXT: Full database import: To back up DB (staging tables and last import) separately: added step to upload backups to jupiter
Aaron Marcuse-Kubitza
11:30 AM Revision 10743: /README.TXT: Full database import: To back up DB (staging tables and last import) separately: added step to remake backups/TNRS.backup
Aaron Marcuse-Kubitza

08/26/2013

08:45 PM Revision 10742: bin/tnrs_db: add entry to new batch table
Aaron Marcuse-Kubitza
07:48 PM Revision 10741: inputs/.TNRS/schema.sql: batch: reset name of id_by_time unique constraint since this field is now in the batch table
Aaron Marcuse-Kubitza
07:46 PM Revision 10740: inputs/.TNRS/schema.sql: download_settings: renamed to batch_download_settings because this table is actually specific to the batch, and it does not make sense to have a download settings file without a batch
Aaron Marcuse-Kubitza
07:32 PM Revision 10739: inputs/.TNRS/schema.sql: download_settings.id: added fkey to batch.id to create a 1:1 relationship with optional participation by download_settings. note that this relationship happens to be the same as SQL inheritance, as used in VegCore, but in this case, the 1:1 relationship is not related to inheritance.
Aaron Marcuse-Kubitza
06:30 PM Revision 10738: inputs/.TNRS/schema.sql: client_version: added table, column comments with info on how to retrieve each value
Aaron Marcuse-Kubitza
06:28 PM Revision 10737: inputs/.TNRS/schema.sql: added client_version table for svn revisions, with fkey from batch
Aaron Marcuse-Kubitza
06:23 PM Revision 10736: inputs/.TNRS/schema.sql: added batch table and moved download_settings.time_submitted, id_by_time to it since these are not related to the download_settings file
Aaron Marcuse-Kubitza
05:04 PM Revision 10735: fix: planning/timeline/timeline.2013.xls: Switching to new-style import: updated hyperlink
Aaron Marcuse-Kubitza
05:02 PM Revision 10734: planning/timeline/timeline.2013.xls: moved Individual datasource removal under Streamline process of mapping and adding a new datasource
Aaron Marcuse-Kubitza
04:49 PM Revision 10733: planning/timeline/timeline.2013.xls: added note that the purpose of the dots is to show what tasks should be worked on. in some cases, they are also proportional to the complexity of the task, but this may not be the case if e.g. a task was given different priorities in different months, or worked on in different amounts.
Aaron Marcuse-Kubitza
04:38 PM Revision 10732: fix: planning/timeline/timeline.2013.xls: matched supertask status to subtask status
Aaron Marcuse-Kubitza
04:35 PM Revision 10731: planning/timeline/timeline.2013.xls: made Switching to new-style import a subtask of Streamline process of mapping and adding a new datasource because new-style import automates many of the datasource-mapping tasks that previously needed to be done by hand
Aaron Marcuse-Kubitza
04:33 PM Revision 10730: planning/timeline/timeline.2013.xls: reordered for priorities and to-do assignments from last conference call (wiki.vegpath.org/2013-08-22_conference_call#Decisions-made)
Aaron Marcuse-Kubitza
04:32 PM Revision 10729: planning/timeline/timeline.2013.xls: updated for August progress and recently-added tasks
Aaron Marcuse-Kubitza
01:49 PM Revision 10728: inputs/.TNRS/schema.sql: added VegCore-style id column as the primary key, instead of using time_submitted directly. this enables always using the same name for the pkey. the pkey is now autopopulated from time_submitted in a trigger, using helper column id_by_time. the user is now also able to specify their own globally-unique ID that is not based on the time_submitted.
Aaron Marcuse-Kubitza

08/25/2013

11:22 PM Revision 10727: inputs/.TNRS/schema.sql: download_settings comment: changed name of button to Download settings, which had gotten auto-replaced to download_settings
Aaron Marcuse-Kubitza
11:08 PM Revision 10726: inputs/.TNRS/schema.sql: Download settings table: renamed to download_settings because although Download settings is the verbatim name of the button that this info comes from, it is not necessary to name the table a particular way in order to match up data to it correctly, so we can just use the standard naming convention (wiki.vegpath.org/u-name#format) and avoid the need to enclose the name in ""
Aaron Marcuse-Kubitza
 

Also available in: Atom