Installation: 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: 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. 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//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 Important: This must be done *after* running make_analytical_db on a previous import 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 several hours to import the NCBI backbone and TNRS names before returning control to the shell. Wait (overnight) for the import to finish tail inputs/{.,}*/*/logs/*.r[.-]*log.sql Check that every input's log ends in "Encountered 0 error(s)" If many do not, fix the bug and discard the current (partial) import: make schemas/public/reinstall Otherwise, continue ./bin/make_analytical_db & To view progress: tail -f inputs/analytical_db/logs/make_analytical_db.log.sql Wait for make_analytical_db to finish Determine the import name: bin/import_name inputs/{.,}*/*/logs/*.r[.-]*log.sql Archive the last import: make schemas/rename/public. Important: This must be done *after* running make_analytical_db Delete previous imports so they won't bloat the full DB backup: make backups/public..backup/remove make backups/TNRS.backup-remake & make backups/public..backup/test & make backups/vegbien..backup/test & On local machine: make inputs/download-logs make backups/download 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/*.r[.-]*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" Backups: Archived imports: Back up: make backups/public..backup & Note: To back up the last import, you must archive it first (above) Test: make backups/public..backup/test & Restore: make backups/public..backup/restore & Remove: make backups/public..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, test, and rotate: make backups/vegbien.backup/all & Back up and rotate: make backups/vegbien.backup/rotate & Test: make backups/vegbien..backup/test & Restore: make backups/vegbien..backup/restore & Download: make backups/download Import logs: Download: make inputs/download-logs Datasource setup: Add a new datasource: make inputs//add may not contain spaces, and should be abbreviated. If the datasource is a herbarium, should be the herbarium code as defined by the Index Herbariorum Install any MySQL export: Create database in phpMyAdmin mysql -p database , or use a custom name Note that if this table will be joined together with another table, its name must end in ".src" make inputs///add Important: DO NOT just create an empty directory named
! This command also creates necessary subdirs, such as logs/. Place the CSV for the table in inputs//
/ OR place a query joining other tables together in inputs//
/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
to inputs//import_order.txt before other tables that depend on it Install the staging tables: make inputs//reinstall quiet=1 & To view progress: tail -f inputs//
/logs/install.log.sql View the logs: tail -n +1 inputs//*/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." in
/: 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// Map each table's columns: In each
/ 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// Accept the test cases: make inputs//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//test by_col=1 If you get errors this time, this always indicates a bug, usually in either the unique constraints or column-based import itself Add newly-created files: make inputs//add Commit: svn ci -m "Added inputs//" inputs// 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//test 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 Click in the changes list and press Ctrl+A or Apple+A to select all 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 '
', 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)(?=_|\b) with This is necessary because the table name is *everywhere* Search for Manually change back any replacements inside comments To rename a column: Rename the column: ALTER TABLE
RENAME TO ; Recreate any foreign key for the column, removing CONSTRAINT This resets the foreign key name using the new column name 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 Open WinMerge Go to Edit > Options and click Compare in the left sidebar Enable "Moved block detection", as described at . Set Whitespace to Ignore change, as described at . Documentation: To generate a Redmine-formatted list of steps for column-based import: make inputs/QMOR/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 /remake To remake a file: make -remake