Project

General

Profile

Home » Database development » VegBIEN » VegBIEN schema »

VegBIEN schema refactoring

data dictionary

apply data dictionary renamings to database

in the VegBIEN data dictionary spreadsheet:

  1. click the divider between `approved by` and `column` to display `actual column`
  2. copy `actual column` and `column` to jEdit
  3. delete the line containing the column headers
  4. replace text
    "
    with
    ""
  5. replace regexp
    ^(.*?)\t(.*)$
    with
    /* try_create(): ignore errors if view does not include column, or if renaming
column to same name */
SELECT util.try_create(\$\$ALTER TABLE "plot.**"                            RENAME COLUMN "$1" TO "$2";\$\$);
SELECT util.try_create(\$\$ALTER TABLE view_full_occurrence_individual_view RENAME COLUMN "$1" TO "$2";\$\$);
SELECT util.try_create(\$\$ALTER TABLE view_full_occurrence_individual      RENAME COLUMN "$1" TO "$2";\$\$);
SELECT util.try_create(\$\$ALTER TABLE analytical_stem                      RENAME COLUMN "$1" TO "$2";\$\$);
  6. append the following:
    SELECT "plot.**.modify"(); -- ensure that view_full_occurrence_individual_view cols properly renamed
-- *not* view_full_occurrence_individual_view_modify(), which will be done separately below
SELECT analytical_stem_modify(); -- propagate changes to analytical_plot, analytical_specimen, etc
  7. run the resulting queries on both vegbiendev and the local machine
  8. edit the column names in view_full_occurrence_individual_view_modify(), and update it on vegbiendev and the local machine
  9. on the local machine only (not vegbiendev, or this will delete view_full_occurrence_individual):
    1. run:
      SELECT view_full_occurrence_individual_view_modify();
    2. if there is an error, make the necessary renamings in the view_full_occurrence_individual_view_modify() definition
    3. repeat until no errors
  10. copy `column` to `actual column` since the database columns are now renamed
  11. right-click `actual column` and click Hide column

copy data dictionary definitions to column comments

in the VegBIEN data dictionary spreadsheet:

  1. copy `column` and `formula/definition/comments` to jEdit
  2. delete the line containing the column headers
  3. replace text
    '
    with
    ''
  4. replace regexp
    ^(.*?)\t(?:(?!")(.*)|"((?s:.*?))")$
    with
    -- try_create(): ignore errors if view does not include column
SELECT util.try_create(\$\$COMMENT ON COLUMN "plot.**"                           ."$1" IS '$2$3';\$\$);
SELECT util.try_create(\$\$COMMENT ON COLUMN view_full_occurrence_individual_view."$1" IS '$2$3';\$\$);
SELECT util.try_create(\$\$COMMENT ON COLUMN view_full_occurrence_individual     ."$1" IS '$2$3';\$\$);
SELECT util.try_create(\$\$COMMENT ON COLUMN analytical_stem                     ."$1" IS '$2$3';\$\$);
SELECT util.try_create(\$\$COMMENT ON COLUMN analytical_plot                     ."$1" IS '$2$3';\$\$);
SELECT util.try_create(\$\$COMMENT ON COLUMN analytical_specimen                 ."$1" IS '$2$3';\$\$);
SELECT util.try_create(\$\$COMMENT ON COLUMN collected_dates                     ."$1" IS '$2$3';\$\$);
  5. replace text
    ""
    with
    "
  6. replace text
    ':"
    with
    '"
  7. run the resulting queries on both vegbiendev and the local machine
  8. on the local machine only (not vegbiendev, or this will delete view_full_occurrence_individual), run:
    SELECT view_full_occurrence_individual_view_modify();

merge a column into the comments column

in the VegBIEN data dictionary spreadsheet:

  1. create a blank an Excel spreadsheet (this is needed because Google spreadsheet doesn't support multi-column select)
  2. copy the column to add into the Excel spreadsheet
  3. copy the comments column into the Excel spreadsheet
  4. copy the Excel spreadsheet columns to jEdit
  5. delete the line containing the column headers
  6. replace regexp
    (.*)\t(")(?=.*)
    with
    $2$1;
  7. replace regexp
    (.*)\t:?(.*)
    with BeanShell snippet
    "\""+_1.replaceAll("\"","\"\"")+";\n"+_2.replaceAll("\"","\"\"")+"\""
  8. copy the jEdit text back to the comments column
  9. delete the column to add

done:

populate links from unrenamed term names

in analytical_stem_view's COMMENT ON COLUMN statements:

  1. copy to jEdit
  2. replace regexp
    (\."?(.*?)"? IS ')from (\S+)(?=';)
    with
    $1$2@$3@vegbiendev.nceas.ucsb.edu

rename terms to link URLs

in analytical_stem_view's COMMENT ON COLUMN statements:

  1. copy to jEdit
  2. replace regexp
    ^(COMMENT ON COLUMN (analytical_stem_view)\.(.*?) IS ')(\S+@vegpath\.org)(?:; )?(.*)$
    with
    $1$5
ALTER TABLE $2 RENAME COLUMN $3 TO "$4";
  3. it is not necessary to save the rename statements, because the derived views can be automatically updated by looking for AS statements and updating the output column to match the input column

prepend http://

this enables links to work in Chrome, and causes them to be auto-hyperlinked in Redmine

in the VegBIEN data dictionary spreadsheet:

  1. copy `column` to jEdit
  2. replace regexp
    ^(?=.*?@)
    with
    http://
  3. copy back

add padding to visually separate components of URL

in the VegBIEN data dictionary spreadsheet:

  1. copy `column` to jEdit
  2. replace regexp
    ^(http://)([^@]*)(@[^@.]*)
    with
    $1__$2__$3__
  3. copy back

prepend http:// to URL-names

in the VegBIEN data dictionary spreadsheet:

  1. copy the comments column to jEdit
  2. delete the line containing the column header
  3. replace regexp
    (?<=")[^/@"]*@
    with
    http://
  4. manually fix URLs that contain "
  5. copy the jEdit text back to the comments column

generate Google spreadsheet hyperlinks

no longer needed now that the columns are just the term names

in the VegBIEN data dictionary spreadsheet:

  1. copy `column` to jEdit (which strips the existing hyperlinks)
  2. replace regexp
    ^.*@.*$
    with
    =HYPERLINK("http://$0", "$0")
  3. copy back