Installation:

    Check out svn: svn co https://code.nceas.ucsb.edu/code/projects/bien

    cd bien/

    Install: make install

        WARNING: This will delete the current public schema of your VegBIEN DB!

    Uninstall: make uninstall

        WARNING: This will delete your entire VegBIEN DB!

        This includes all archived imports and staging tables.

Maintenance:

    VegCore data dictionary:

        Regularly, or whenever the VegCore data dictionary page

            (https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/VegCore)

            is changed, regenerate mappings/VegCore.csv:

            make mappings/VegCore.htm-remake; make mappings/

    Important: Whenever you install a system update that affects PostgreSQL or

        any of its dependencies, such as libc, you should restart the PostgreSQL

        server. Otherwise, you may get strange errors like "the database system

        is in recovery mode" which go away upon reimport, or you may not be able

        to access the database as the postgres superuser. This applies to both

        Linux and Mac OS X.

Data import:

    On local machine:

        make test by_col=1

            See note under Testing below

    On vegbiendev:

    svn up

    make inputs/upload

    For each newly-uploaded datasource: make inputs/<datasrc>/reinstall

    Update the schemas: make schemas/reinstall

        WARNING: This will delete the current public schema of your VegBIEN DB!

        To save it: make schemas/rotate

    Make sure there is at least 100GB of disk space on /: df -h

        The import schema is 75GB, and may use additional space for temp tables

        To free up space, remove backups that have been archived on jupiter:

            List backups/ to view older backups

            Check their MD5 sums using the steps under On jupiter below

            Remove these backups

    Start column-based import: . bin/import_all by_col=1

        To use row-based import: . bin/import_all

        To stop all running imports: . bin/stop_imports

        WARNING: Do NOT run import_all in the background, or the jobs it creates

            won't be owned by your shell.

        Note that import_all will take several hours to import the NCBI backbone

            and TNRS names before returning control to the shell.

    Wait (overnight) for the import to finish

    On local machine: make inputs/download-logs

    In PostgreSQL, check that the source table contains entries for all inputs

    tail inputs/{.,}*/*/logs/*.<version>[.-]*log.sql

    In the output, search for "Command exited with non-zero status"

    For inputs that have this, fix the associated bug(s)

    If many inputs have errors, discard the current (partial) import:

        make schemas/public/reinstall

    Otherwise, continue

    Determine the import name:

        bin/import_name inputs/{.,}*/*/logs/*.<version>[.-]*log.sql

    Archive the last import: make schemas/rename/<import_name>

    Delete previous imports so they won't bloat the full DB backup:

        make backups/vegbien.<version>.backup/remove

    make backups/TNRS.backup-remake &

    make backups/vegbien.<version>.backup/test &

    env public=<version> bin/export_analytical_db &

    sudo backups/fix_perms

    make backups/upload

    On jupiter:

        cd /data/dev/aaronmk/bien/backups

        For each newly-archived backup:

            make <backup>.md5/test

            Check that "OK" is printed next to the filename

    On nimoy:

        cd /home/bien/svn/

        svn up

        make backups/analytical_aggregate.<version>.csv/download

        make backups/analytical_aggregate.<version>.csv.md5/test

        Check that "OK" is printed next to the filename

        In the bien_web DB:

            Create the analytical_aggregate_<version> table using its schema

                in schemas/vegbien.my.sql

        env table=analytical_aggregate_<version> bin/publish_analytical_db \

            backups/analytical_aggregate.<version>.csv

    If desired, record the import times in inputs/import.stats.xls:

        Open inputs/import.stats.xls

        Insert a copy of the leftmost Column-based column group before it

        Update the import date in the upper-right corner

        ./bin/import_times inputs/{.,}*/*/logs/*.<version>[.-]*log.sql

        Paste the output over the # Rows/Time columns, making sure that the

            row counts match up with the previous import's row counts

        If the row counts do not match up, insert or reorder rows as needed

            until they do

        Commit: svn ci -m "inputs/import.stats.xls: Updated import times"

    To remake analytical DB: env public=... bin/make_analytical_db &

        public should be set to the current import's schema name

        To view progress:

            tail -f inputs/analytical_db/logs/make_analytical_db.log.sql

Backups:

    Archived imports:

        Back up: make backups/<version>.backup &

            Note: To back up the last import, you must archive it first:

                make schemas/rotate

        Test: make backups/<version>.backup/test &

        Restore: make backups/<version>.backup/restore &

        Remove: make backups/<version>.backup/remove

        Download: make backups/download

    TNRS cache:

        Back up: make backups/TNRS.backup-remake &

        Restore:

            yes|make inputs/.TNRS/uninstall

            make backups/TNRS.backup/restore &

            yes|make schemas/public/reinstall

                Must come after TNRS restore to recreate tnrs_input_name view

    Full DB:

        Back up: make backups/vegbien.<version>.backup &

        Test: make backups/vegbien.<version>.backup/test &

        Restore: make backups/vegbien.<version>.backup/restore &

        Download: make backups/download

    Import logs:

        Download: make inputs/download-logs

Datasource setup:

    Add a new datasource: make inputs/<datasrc>/add

        <datasrc> may not contain spaces, and should be abbreviated.

        If the datasource is a herbarium, <datasrc> should be the herbarium code

            as defined by the Index Herbariorum <http://sweetgum.nybg.org/ih/>

    For MySQL inputs (exports and live DB connections):

        For .sql exports:

            Place the original .sql file in _src/ (*not* in _MySQL/)

            Create a database for the MySQL export in phpMyAdmin

            mysql -p database <inputs/<datasrc>/_src/export.sql

        mkdir inputs/<datasrc>/_MySQL/

        cp -p lib/MySQL.{data,schema}.sql.make inputs/<datasrc>/_MySQL/

        Edit _MySQL/*.make for the DB connection

            For a .sql export, use your local MySQL DB

        Install the export according to Install the staging tables below

    Add input data for each table present in the datasource:

        For .sql exports, you must use the name of the table in the DB export

        For CSV files, you can use any name. It's recommended to use a table

            name from <https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/VegCSV#Suggested-table-names>

        Note that if this table will be joined together with another table, its

            name must end in ".src"

        make inputs/<datasrc>/<table>/add

            Important: DO NOT just create an empty directory named <table>!

                This command also creates necessary subdirs, such as logs/.

        If the table is in a .sql export: make inputs/<datasrc>/<table>/install

            Otherwise, place the CSV(s) for the table in

            inputs/<datasrc>/<table>/ OR place a query joining other tables

            together in inputs/<datasrc>/<table>/create.sql

        Important: When exporting relational databases to CSVs, you MUST ensure

            that embedded quotes are escaped by doubling them, *not* by

            preceding them with a "\" as is the default in phpMyAdmin

        If there are multiple part files for a table, and the header is repeated

            in each part, make sure each header is EXACTLY the same.

             (If the headers are not the same, the CSV concatenation script

             assumes the part files don't have individual headers and treats the

             subsequent headers as data rows.)

        Add <table> to inputs/<datasrc>/import_order.txt before other tables

            that depend on it

    Install the staging tables:

        make inputs/<datasrc>/reinstall quiet=1 &

        To view progress: tail -f inputs/<datasrc>/<table>/logs/install.log.sql

        View the logs: tail -n +1 inputs/<datasrc>/*/logs/install.log.sql

            tail provides a header line with the filename

            +1 starts at the first line, to show the whole file

        For every file with an error 'column "..." specified more than once':

            Add a header override file "+header.<ext>" in <table>/:

                Note: The leading "+" should sort it before the flat files.

                    "_" unfortunately sorts *after* capital letters in ASCII.

                Create a text file containing the header line of the flat files

                Add an ! at the beginning of the line

                    This signals cat_csv that this is a header override.

                For empty names, use their 0-based column # (by convention)

                For duplicate names, add a distinguishing suffix

                For long names that collided, rename them to <= 63 chars long

                Do NOT make readability changes in this step; that is what the

                    map spreadsheets (below) are for.

                Save

        If you made any changes, re-run the install command above

    Auto-create the map spreadsheets: make inputs/<datasrc>/

    Map each table's columns:

        In each <table>/ subdir, for each "via map" map.csv:

            Open the map in a spreadsheet editor

            Open the "core map" /mappings/Veg+-VegBIEN.csv

            In each row of the via map, set the right column to a value from the

                left column of the core map

            Save

        Regenerate the derived maps: make inputs/<datasrc>/

    Accept the test cases:

        make inputs/<datasrc>/test

            When prompted to "Accept new test output", enter y and press ENTER

            If you instead get errors, do one of the following for each one:

            -   If the error was due to a bug, fix it

            -   Add a SQL function that filters or transforms the invalid data

            -   Make an empty mapping for the columns that produced the error.

                Put something in the Comments column of the map spreadsheet to

                prevent the automatic mapper from auto-removing the mapping.

            When accepting tests, it's helpful to use WinMerge

                (see WinMerge setup below for configuration)

        make inputs/<datasrc>/test by_col=1

            If you get errors this time, this always indicates a bug, usually in

                the VegBIEN unique constraints or column-based import itself

    Add newly-created files: make inputs/<datasrc>/add

    Commit: svn ci -m "Added inputs/<datasrc>/" inputs/<datasrc>/

    Update vegbiendev:

        On vegbiendev: svn up

        On local machine: make inputs/upload

        On vegbiendev:

            Follow the steps under Install the staging tables above

            make inputs/<datasrc>/test

Datasource refreshing:

    VegBank:

        make inputs/VegBank/vegbank.sql-remake

        make inputs/VegBank/reinstall quiet=1 &

Schema changes:

    Remember to update the following files with any renamings:

        schemas/filter_ERD.csv

        mappings/VegCore-VegBIEN.csv

        mappings/verify.*.sql

    Regenerate schema from installed DB: make schemas/remake

    Reinstall DB from schema: make schemas/reinstall

        WARNING: This will delete the current public schema of your VegBIEN DB!

    Reinstall staging tables: . bin/reinstall_all

    Sync ERD with vegbien.sql schema:

        Run make schemas/vegbien.my.sql

        Open schemas/vegbien.ERD.mwb in MySQLWorkbench

        Go to File > Export > Synchronize With SQL CREATE Script...

        For Input File, select schemas/vegbien.my.sql

        Click Continue

        In the changes list, select each table with an arrow next to it

        Click Update Model

        Click Continue

        Note: The generated SQL script will be empty because we are syncing in

            the opposite direction

        Click Execute

        Reposition any lines that have been reset

        Add any new tables by dragging them from the Catalog in the left sidebar

            to the diagram

        Remove any deleted tables by right-clicking the table's diagram element,

            selecting Delete '<table name>', and clicking Delete

        Save

        If desired, update the graphical ERD exports (see below)

    Update graphical ERD exports:

        Go to File > Export > Export as PNG...

        Select schemas/vegbien.ERD.png and click Save

        Go to File > Export > Export as SVG...

        Select schemas/vegbien.ERD.svg and click Save

        Go to File > Export > Export as Single Page PDF...

        Select schemas/vegbien.ERD.1_pg.pdf and click Save

        Go to File > Print...

        In the lower left corner, click PDF > Save as PDF...

        Set the Title and Author to ""

        Select schemas/vegbien.ERD.pdf and click Save

    Refactoring tips:

        To rename a table:

            In vegbien.sql, do the following:

                Replace regexp (?<=_|\b)<old>(?=_|\b) with <new>

                    This is necessary because the table name is *everywhere*

                Search for <new>

                Manually change back any replacements inside comments

        To rename a column:

            Rename the column: ALTER TABLE <table> RENAME <old> TO <new>;

            Recreate any foreign key for the column, removing CONSTRAINT <name>

                This resets the foreign key name using the new column name

    Creating a poster of the ERD:

        Determine the poster size:

            Measure the line height (from the bottom of one line to the bottom

                of another): 16.3cm/24 lines = 0.679cm

            Measure the height of the ERD: 35.4cm*2 = 70.8cm

            Zoom in as far as possible

            Measure the height of a capital letter: 3.5mm

            Measure the line height: 8.5mm

            Calculate the text's fraction of the line height: 3.5mm/8.5mm = 0.41

            Calculate the text height: 0.679cm*0.41 = 0.28cm

            Calculate the text height's fraction of the ERD height:

                0.28cm/70.8cm = 0.0040

            Measure the text height on the *VegBank* ERD poster: 5.5mm = 0.55cm

            Calculate the VegBIEN poster height to make the text the same size:

                0.55cm/0.0040 = 137.5cm H; *1in/2.54cm = 54.1in H

            The ERD aspect ratio is 11 in W x (2*8.5in H) = 11x17 portrait

            Calculate the VegBIEN poster width: 54.1in H*11W/17H = 35.0in W

            The minimum VegBIEN poster size is 35x54in portrait

        Determine the cost:

            The FedEx Kinkos near NCEAS (1030 State St, Santa Barbara, CA 93101)

                charges the following for posters:

                base: $7.25/sq ft

                lamination: $3/sq ft

                mounting on a board: $8/sq ft

Testing:

    Mapping process: make test

        Including column-based import: make test by_col=1

            If the row-based and column-based imports produce different inserted

            row counts, this usually means that a table is underconstrained

            (the unique indexes don't cover all possible rows).

            This can occur if you didn't use COALESCE(field, null_value) around

            a nullable field in a unique index. See sql_gen.null_sentinels for

            the appropriate null value to use.

    Map spreadsheet generation: make remake

    Missing mappings: make missing_mappings

    Everything (for most complete coverage): make test-all

WinMerge setup:

    Install WinMerge from <http://winmerge.org/>

    Open WinMerge

    Go to Edit > Options and click Compare in the left sidebar

    Enable "Moved block detection", as described at

        <http://manual.winmerge.org/Configuration.html#d0e5892>.

    Set Whitespace to Ignore change, as described at

        <http://manual.winmerge.org/Configuration.html#d0e5758>.

Documentation:

    To generate a Redmine-formatted list of steps for column-based import:

        make inputs/ACAD/Specimen/logs/steps.by_col.log.sql

    To import and scrub just the test taxonomic names:

        inputs/test_taxonomic_names/test_scrub

General:

    To see a program's description, read its top-of-file comment

    To see a program's usage, run it without arguments

    To remake a directory: make <dir>/remake

    To remake a file: make <file>-remake