COMMENT ON SCHEMA py_util IS '

IMPORTANT: Functions must always return NULL in place of '''' (the empty string). This ensures that empty strings do not find their way into VegBIEN.

';

COMMENT ON SCHEMA util IS '

IMPORTANT: Functions must always return NULL in place of '''' (the empty string). This ensures that empty strings do not find their way into VegBIEN.

NOTE: IMMUTABLE SQL-language functions should never be declared STRICT, because this prevents them from being inlined. inlining can create a significant speed improvement (7x+), by avoiding function calls and enabling additional constant folding.

';

COMMENT ON FUNCTION _and("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean) IS '

_and() ignores NULL values, while AND combines them with the other values to potentially convert true to NULL. AND should be used with required fields, and _and() with optional fields.

';

COMMENT ON FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean) IS '

_or() ignores NULL values, while OR combines them with the other values to potentially convert false to NULL. OR should be used with required fields, and _or() with optional fields.

';

COMMENT ON FUNCTION added_cols(table_ regclass, names regclass) IS '

gets table_''s added columns (all the columns not in the original data)

';

COMMENT ON FUNCTION array_length("array" anyarray, dimension integer) IS '

returns 0 instead of NULL for empty arrays

';

COMMENT ON FUNCTION cluster_once(table_ regclass, index regclass) IS '

idempotent

';

COMMENT ON FUNCTION col_default_value(col col_ref, ret_type_null anyelement) IS '

ret_type_null: NULL::ret_type

';

COMMENT ON FUNCTION create_if_not_exists(sql text) IS '

idempotent

';

COMMENT ON FUNCTION derived_cols(table_ regclass, names regclass) IS '

gets table_''s derived columns (all the columns not in the names table)

';

COMMENT ON FUNCTION diff_cols(left_query text, right_query text, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) IS '

col_type_null (*required*): NULL::col_type

COMMENT ON FUNCTION diff_views(left_view regclass, right_view regclass, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) IS '

col_type_null (*required*): NULL::col_type

COMMENT ON FUNCTION do_optionally_ignore(sql text, ignore boolean) IS '

idempotent

';

COMMENT ON FUNCTION drop_column(col col_ref) IS '

idempotent

';

COMMENT ON FUNCTION drop_table(table_ text) IS '

idempotent

';

COMMENT ON FUNCTION drop_view(view_ text) IS '

idempotent

';

COMMENT ON FUNCTION empty_array(elem_type_null anyelement) IS '

constructs proper empty 1-dimensional array whose dimensions are not NULL ( ''{}''::text[] does not do this)

';

COMMENT ON FUNCTION eval2col_pair(sql text, col_type_null anyelement, OUT left_ anyelement, OUT right_ anyelement) IS '

col_type_null (*required*): NULL::col_type

';

COMMENT ON FUNCTION eval2set(sql text, ret_type_null anyelement) IS '

ret_type_null: NULL::ret_type

';

COMMENT ON FUNCTION eval2val(sql text, ret_type_null anyelement) IS '

ret_type_null: NULL::ret_type

';

COMMENT ON FUNCTION eval_expr(sql text, ret_type_null anyelement) IS '

ret_type_null: NULL::ret_type

';

COMMENT ON FUNCTION eval_expr_passthru(sql text, ret_type_null anyelement) IS '

sql: can be NULL, which will be passed through

ret_type_null: NULL::ret_type

';

COMMENT ON FUNCTION explain2table(sql text, table_ regclass) IS '

usage:

$$);

';

COMMENT ON FUNCTION fix_array("array" anyarray) IS '

ensures that an array will always have proper non-NULL dimensions

';

COMMENT ON FUNCTION force_recreate(cmd text, users text[]) IS '

idempotent

users: not necessary to provide this because it will be autopopulated

';

COMMENT ON FUNCTION force_update_view(view_ text, query text) IS '

idempotent

';

COMMENT ON FUNCTION hstore(keys text[], value text) IS '

avoids repeating the same value for each key

';

COMMENT ON FUNCTION ifnull(value anyelement, null_ anyelement) IS '

equivalent to MySQL''s IFNULL() (Postgres auto-lowercases the name)

';

COMMENT ON FUNCTION map_nulls(nulls text[], value anyelement) IS '

due to dynamic inlining[1], this is just as fast as util._map() which it wraps[2].

also, /inputs/REMIB/Specimen/postprocess.sql > country takes the same amount of time (56000 ms) to build with map_nulls() as with a literal hstore.

';

COMMENT ON FUNCTION materialize_query(table_ text, sql text) IS '

idempotent

';

COMMENT ON FUNCTION materialize_view(table_ text, view_ regclass) IS '

idempotent

';

COMMENT ON COLUMN $$||$1.table_||$$.$$||quote_ident($1.name)||$$ IS '

constant

';

COMMENT ON FUNCTION mk_const_col(col col_ref, value anyelement) IS '

idempotent

';

COMMENT ON FUNCTION mk_derived_col(col col_ref, expr text, overwrite boolean) IS '

idempotent

';

COMMENT ON FUNCTION mk_source_col(table_ regclass) IS '

idempotent

';

COMMENT ON FUNCTION mk_subset_by_row_num_no_sort_func(view_ regclass) IS '

creates subset function which turns off enable_sort

';

COMMENT ON FUNCTION nulls_map(nulls text[]) IS '

for use with _map()

';

COMMENT ON FUNCTION rename_cols(table_ regclass, renames anyelement) IS '

idempotent

';

COMMENT ON FUNCTION reset_col_names(table_ regclass, names regclass) IS '

idempotent.

alters the names table, so it will need to be repopulated after running this function.

';

COMMENT ON FUNCTION schema_bundle_get_schemas(schema_bundle text) IS '

a schema bundle is a group of schemas with a common prefix

';

COMMENT ON FUNCTION set_col_names(table_ regclass, names regclass) IS '

idempotent

';

COMMENT ON FUNCTION set_col_names_with_metadata(table_ regclass, names regclass) IS '

idempotent.

the metadata mappings must be *last* in the names table.

';

COMMENT ON FUNCTION set_col_types(table_ regclass, col_casts col_cast[]) IS '

idempotent

';

COMMENT ON FUNCTION table_flag__get(table_ regclass, flag text) IS '

gets whether a status flag is set by the presence of a table constraint

';

COMMENT ON FUNCTION table_flag__set(table_ regclass, flag text) IS '

stores a status flag by the presence of a table constraint.

idempotent.

';

COMMENT ON FUNCTION table_nulls_mapped__get(table_ regclass) IS '

gets whether a table''s NULL-equivalent strings have been replaced with NULL

';

COMMENT ON FUNCTION table_nulls_mapped__set(table_ regclass) IS '

sets that a table''s NULL-equivalent strings have been replaced with NULL.

idempotent.

';

COMMENT ON FUNCTION to_global_col_names(table_ regclass) IS '

idempotent

';

COMMENT ON FUNCTION "trim"(table_ regclass, names regclass) IS '

trims table_ to include only columns in the original data.

idempotent.

';

COMMENT ON FUNCTION truncate(table_ regclass) IS '

idempotent

';

COMMENT ON FUNCTION try_create(sql text) IS '

idempotent

';

COMMENT ON FUNCTION try_mk_derived_col(col col_ref, expr text) IS '

idempotent

';

COMMENT ON FUNCTION type_qual(value anyelement) IS '

a type''s NOT NULL qualifier

';

COMMENT ON FUNCTION type_qual_name(type regtype) IS '

a type''s schema-qualified name

';

COMMENT ON AGGREGATE all_same(anyelement) IS '

includes NULLs in comparison

';

COMMENT ON OPERATOR => (text[], text) IS '

usage: array[''key1'', ...]::text[] => ''value''

';

COMMENT ON OPERATOR ||% (text, text) IS '

% indicates an identifier, as in Perl hashes and one of the x86 assembler syntaxes for registers

';

COMMENT ON SCHEMA public_validations IS '

aggregating validations queries

';

COMMENT ON TYPE coordinatesource IS '

From <https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/VegCore_data_dictionary#coordinateSource>

';

COMMENT ON TYPE establishmentmeans_dwc IS '

See <http://rs.tdwg.org/dwc/terms/#establishmentMeans>

';

COMMENT ON TYPE placerank IS '

county = parish, canton

From <http://rs.tdwg.org/dwc/terms/#dcindex#dcterms:Location>, <http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1415&entity=dba_fielddescription&params=1415>

';

COMMENT ON TYPE taxonrank IS '

From <http://www.tdwg.org/standards/117/download/>

';

COMMENT ON TYPE role IS '

From <http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1331&entity=dba_fielddescription&params=1331>, <http://vegbank.org/vegbank/views/dba_tabledescription_detail.jsp?view=detail&wparam=projectcontributor&entity=dba_tabledescription&where=where_tablename#ROLE_ID>, and misc terms.

';

COMMENT ON TYPE sourcetype IS '

From <https://projects.nceas.ucsb.edu/nceas/attachments/download/428/bien_web_datasource_schema.sql>, <http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1300&entity=dba_fielddescription&params=1300>, <http://vegbiendev.nceas.ucsb.edu/phppgadmin/display.php?server=localhost%3A5432%3Aallow&database=vegbien&schema=VegBank&table=source&subject=table#sourcetype>

';

COMMENT ON TYPE taxonclass IS '

= growthform + establishmentmeans_dwc + some others

';

COMMENT ON TYPE taxonomic_status IS '

See <http://tnrs.iplantcollaborative.org/instructions.html#Status>

';

COMMENT ON FUNCTION _or("0" boolean, "1" boolean, "2" boolean, "3" boolean, "4" boolean) IS '

_or() ignores NULL values, while OR combines them with the other values to potentially convert false to NULL. OR should be used with required fields, and _or() with optional fields.

';

COMMENT ON FUNCTION datasource_publish(datasource text, schema_null anyelement) IS '

secure against renamings of the public schema.

COMMENT ON FUNCTION datasource_rename(old text, new text, schema_null anyelement) IS '

secure against renamings of the public schema.

COMMENT ON FUNCTION datasource_rm(datasource text, schema_null anyelement) IS '

secure against renamings of the public schema.

COMMENT ON FUNCTION datasource_unpublish(datasource text, schema_null anyelement) IS '

secure against renamings of the public schema.

COMMENT ON FUNCTION location__pull_forward_from_parent() IS '

see wiki.vegpath.org/Pull-forward

';

COMMENT ON FUNCTION location_set_top_plot() IS '

see wiki.vegpath.org/Pull-forward

';

COMMENT ON FUNCTION locationevent_pull_forward_from_parent() IS '

see wiki.vegpath.org/Pull-forward

';

COMMENT ON FUNCTION locationevent_pull_forward_from_parent_for_stratum() IS '

see wiki.vegpath.org/Pull-forward

';

COMMENT ON TABLE place IS '

The full path to a place. Can be either verbatim or accepted. For accepted names, points to the identified place.

To use a custom hierarchy of placenames with no explicit column, point to the lowest-rank placename in placename_id.

';

COMMENT ON COLUMN place.matched_place_id IS '

The closest match to this place. Places should be linked in a three-level hierarchy of datasource place -> verbatim place -> accepted place.

An accepted place should point to itself in this field. This will happen automatically by setting it to the special value 0.

';

COMMENT ON FUNCTION project_contributors(project_id integer) IS '

usage: array(SELECT project_contributors(...))

';

COMMENT ON FUNCTION publish(schema_null anyelement) IS '

publishes this schema.

COMMENT ON FUNCTION rm(schema_null anyelement) IS '

deletes this schema.

COMMENT ON TABLE taxonlabel IS '

A taxon label defined by an entity. Can be at any level in the taxonomic hierarchy. Can be either verbatim or accepted.

Equivalent to VegBank''s plantConcept and plantName tables, plus plantParent_ID and plantLevel from plantStatus.

';

COMMENT ON COLUMN taxonlabel.source_id IS '

The entity that created the taxon label.

';

COMMENT ON COLUMN taxonlabel.sourceaccessioncode IS '

The datasource''s identifier for the taxonlabel.

';

COMMENT ON COLUMN taxonlabel.canon_label_id IS '

The canonical taxonlabel for this taxonlabel.

';

COMMENT ON COLUMN taxonlabel.matched_label_id IS '

The taxonlabel containing the closest match to this taxonlabel. taxonlabels should be linked in a four-level hierarchy of datasource label -> parsed label -> matched label -> accepted label. A previously-accepted name''s label should be further linked to the synonym that has replaced it.

An accepted name should point to itself in this field. This will happen automatically by setting it to the special value 0.

';

COMMENT ON COLUMN taxonlabel.parent_id IS '

The parent taxonlabel. Note that while a taxon *name* may have multiple parents, a taxonlabel *instance* has only one, based on the creator''s opinion of where that taxonlabel goes in the taxonomic hierarchy.

';

COMMENT ON COLUMN taxonlabel.taxonepithet IS '

The epithet of the taxon within its parent taxon. This is the lowest-rank portion of this taxonlabel''s full taxonomic name, if it has one.

';

COMMENT ON COLUMN taxonlabel.rank IS '

The taxon''s level in the taxonomic hierarchy, standardized to a closed list. Even if you specify a custom verbatimrank in taxonverbatim, you should also specify a closest-match rank from the taxonrank closed list.

';

COMMENT ON COLUMN taxonlabel.taxonomicname IS '

The concatenated taxonomic name which uniquely identifies this taxon, including the family and author of that name. Must be unique within the datasource.

Equivalent to Darwin Core''s scientificName.

';

COMMENT ON TABLE aggregateoccurrence IS '

Equivalent to VegBank''s taxonimportance table.

';

COMMENT ON COLUMN aggregateoccurrence.linecover_m IS '

The distance in m along which this occurrence intercepts a line subplot.

';

COMMENT ON COLUMN aggregateoccurrence.occurrencestatus_dwc IS '

The extent to which the taxon is present. See <http://code.google.com/p/darwincore/wiki/Occurrence#occurrenceStatus>.

';

COMMENT ON COLUMN coordinates.footprintgeometry_dwc IS '

DwC''s footprintWKT field. "A Well-Known Text (WKT) representation of the shape (footprint, geometry) that defines the Location" (http://rs.tdwg.org/dwc/terms/#footprintWKT).

';

COMMENT ON COLUMN coordinates.coordsaccuracy_m IS '

Accuracy of latitude/longitude or footprint geometry, in meters. This should generally be at least 1 m, which is the accuracy of the best GPSes.

';

COMMENT ON TABLE cultivated_family_locations IS '

from sftp://nimoy.nceas.ucsb.edu/home/bien/bien2_scripts/geoscrub/cultivated/cult_by_taxon/flag_by_taxa.inc

';

COMMENT ON TABLE location IS '

Equivalent to VegBank''s plot table.

';

COMMENT ON COLUMN location.top_plot IS '

autopopulated

';

COMMENT ON TABLE locationevent IS '

Equivalent to VegBank''s observation table.

';

COMMENT ON TABLE method IS '

A method for sampling and aggregating plants. Replaces VegBank''s stratummethod and stratumtype tables.

Important: *All* length- or area-related measurements throughout VegBIEN must be converted to SI base units, e.g. cm -> m, ha -> m^2.**

';

COMMENT ON COLUMN method.source_id IS '

Use the source table (e.g. source.url) to store a link to the original plain text description.

';

COMMENT ON COLUMN method.name IS '

A short name for the set of methods used. Although there is no existing standard, many names are widely used, and could be useful for finding plots with similar methodology.

';

COMMENT ON COLUMN method.description IS '

Additional metadata helpful for understanding how the data were collected during the observation event.

';

COMMENT ON COLUMN method.diameterheight_m IS '

The height in m at which the diameter is measured. e.g. 1.37 m for breast height (DBH).

';

COMMENT ON COLUMN method.mindiameter_m IS '

Lower diameter limit in m for inclusion of a tree.

';

COMMENT ON COLUMN method.maxdiameter_m IS '

Upper diameter limit in m for inclusion of a tree.

';

COMMENT ON COLUMN method.minheight_m IS '

Lower height limit in m for inclusion of a tree.

';

COMMENT ON COLUMN method.maxheight_m IS '

Upper height limit in m for inclusion of a tree.

';

COMMENT ON COLUMN method.observationtype IS '

values: aggregate, individual, both

';

COMMENT ON COLUMN method.observationmeasure IS '

e.g. count, cover, presence, points-intercepted, distance-intercepted

';

COMMENT ON COLUMN method.samplingfactor IS '

Here, we could explicitly say that we are sampling a particular area by a different size representative sample area. Simply divide the number of plants connected to this record by this value to get the extrapolated (or interpolated) number of plants in the area in question. This explicitly notes a subsample or supersample.

';

COMMENT ON COLUMN method.coverbasis IS '

Were cover values for the total taxon list collected from one contiguous area or dispersed subplots?

COMMENT ON COLUMN method.stemsamplemethod IS '

The method used to obtain basal area or tree stem data (e.g., full census, point quarter, random pairs, Bitterlich, other).

Subsample census

';

COMMENT ON COLUMN method.shape IS '

e.g. square, rectangle, circle, line, point, other

';

COMMENT ON COLUMN method.samplearea_m2 IS '

The total surface area used for cover estimates and for which a complete species list is provided. If subplots were used, this would be the total area of the subplots without interstitial space.

';

COMMENT ON COLUMN method.subplotspacing_m IS '

Spacing in m between adjacent subplots, lines (line-intercept data), or points (point-intercept data).

';

COMMENT ON COLUMN method.subplotmethod_id IS '

Method to use for each subplot/line/point, which will specify subplot size, line length, etc.

';

COMMENT ON COLUMN method.pointsperline IS '

The number of points sampled on each line subplot for point-intercept data.

';

COMMENT ON TABLE plantobservation IS '

Equivalent to VegBank''s stemcount table.

';

COMMENT ON COLUMN plantobservation.authorplantcode IS '

The number of the organism within the data collection or event.

';

COMMENT ON TABLE specimenreplicate IS '

A herbarium''s replicate of a specimen. Contains Darwin Core specimen data.

';

COMMENT ON COLUMN specimenreplicate.institution_id IS '

The institution (such as a museum) that the specimenreplicate is from.

';

COMMENT ON COLUMN specimenreplicate.collectioncode_dwc IS '

The code for the collection that the specimenreplicate is from.

';

COMMENT ON TABLE stemobservation IS '

Equivalent to VegBank''s stemlocation table.

';

COMMENT ON COLUMN stemobservation.tags IS '

Stores all tags when multiple tags provided. Older tags go first.

';

COMMENT ON TABLE taxondetermination IS '

Equivalent to VegBank''s taxoninterpretation table.

';

COMMENT ON TABLE taxonoccurrence IS '

Equivalent to VegBank''s taxonobservation table.

';

COMMENT ON COLUMN taxonoccurrence.iscultivated IS '

cultivated or wild

';

COMMENT ON COLUMN taxonoccurrence.cultivatedbasis IS '

The reason why a taxonoccurrence was marked as cultivated (or not).

';

COMMENT ON COLUMN taxonoccurrence.isnative IS '

native or exotic

';

COMMENT ON TABLE taxonverbatim IS '

Component parts of the taxonlabel. Contains the datasource''s original taxonomic name components, as well as any parsed components produced by name resolution.

';