| 1 |
702
|
aaronmk
|
Installation:
|
| 2 |
|
|
Install: make install
|
| 3 |
3370
|
aaronmk
|
WARNING: This will delete the current public schema of your VegBIEN DB!
|
| 4 |
702
|
aaronmk
|
Uninstall: make uninstall
|
| 5 |
3370
|
aaronmk
|
WARNING: This will delete your entire VegBIEN DB!
|
| 6 |
3374
|
aaronmk
|
This includes all archived imports and staging tables.
|
| 7 |
554
|
aaronmk
|
|
| 8 |
3674
|
aaronmk
|
Maintenance:
|
| 9 |
|
|
Important: Whenever you install a system update that affects PostgreSQL or
|
| 10 |
|
|
any of its dependencies, such as libc, you should restart the PostgreSQL
|
| 11 |
|
|
server. Otherwise, you may get strange errors like "the database system
|
| 12 |
|
|
is in recovery mode" which go away upon reimport.
|
| 13 |
|
|
|
| 14 |
702
|
aaronmk
|
Data import:
|
| 15 |
1550
|
aaronmk
|
Import data into VegBIEN: . bin/import_all
|
| 16 |
3205
|
aaronmk
|
Using column-based import: . bin/with_all 'import by_col=1'
|
| 17 |
1556
|
aaronmk
|
Stop all running imports: . bin/stop_imports
|
| 18 |
2976
|
aaronmk
|
Archive the last import: make schemas/rotate
|
| 19 |
3381
|
aaronmk
|
Remove the last import: make schemas/public/reinstall
|
| 20 |
|
|
WARNING: This will delete the current public schema of your VegBIEN DB!
|
| 21 |
2976
|
aaronmk
|
Re-import data: make schemas/rotate; . bin/import_all
|
| 22 |
|
|
Note: This will archive the last import.
|
| 23 |
3381
|
aaronmk
|
|
| 24 |
3545
|
aaronmk
|
Backups:
|
| 25 |
3547
|
aaronmk
|
After a new import:
|
| 26 |
3698
|
aaronmk
|
On vegbiendev:
|
| 27 |
3547
|
aaronmk
|
make schemas/rotate
|
| 28 |
|
|
Rename the rotated schema using the date in the first datasource's log
|
| 29 |
|
|
file name
|
| 30 |
4182
|
aaronmk
|
tail inputs/*/*/logs/<date>-*.log.sql
|
| 31 |
3845
|
aaronmk
|
Check that every input's log ends in "Encountered 0 error(s)"
|
| 32 |
|
|
If many do not, fix the bug and discard the current (partial) import
|
| 33 |
|
|
Otherwise, continue
|
| 34 |
3548
|
aaronmk
|
Delete previous imports so they won't bloat the full DB backup:
|
| 35 |
3845
|
aaronmk
|
make backups/public.<datetime>.backup/remove
|
| 36 |
3547
|
aaronmk
|
make backups/<schema>.backup/test & make backups/vegbien.backup/all &
|
| 37 |
3698
|
aaronmk
|
On local machine:
|
| 38 |
|
|
make inputs/download-logs
|
| 39 |
3701
|
aaronmk
|
make backups/download &
|
| 40 |
3845
|
aaronmk
|
If desired, record the import times in inputs/import.stats.xls
|
| 41 |
3408
|
aaronmk
|
Archived imports:
|
| 42 |
|
|
Back up: make backups/public.<date>.backup &
|
| 43 |
3546
|
aaronmk
|
Note: To back up the last import, you must archive it first (above)
|
| 44 |
3410
|
aaronmk
|
Test: make backups/public.<date>.backup/test &
|
| 45 |
3408
|
aaronmk
|
Restore: make backups/public.<date>.backup/restore &
|
| 46 |
|
|
Remove: make backups/public.<date>.backup/remove
|
| 47 |
3701
|
aaronmk
|
Download: make backups/download
|
| 48 |
3408
|
aaronmk
|
Full DB:
|
| 49 |
3546
|
aaronmk
|
Back up, test, and rotate: make backups/vegbien.backup/all &
|
| 50 |
3439
|
aaronmk
|
Back up and rotate: make backups/vegbien.backup/rotate &
|
| 51 |
|
|
Test: make backups/vegbien.<date>.backup/test &
|
| 52 |
|
|
Restore: make backups/vegbien.<date>.backup/restore &
|
| 53 |
3701
|
aaronmk
|
Download: make backups/download
|
| 54 |
3698
|
aaronmk
|
Import logs:
|
| 55 |
|
|
Download: make inputs/download-logs
|
| 56 |
554
|
aaronmk
|
|
| 57 |
1773
|
aaronmk
|
Datasource setup:
|
| 58 |
4219
|
aaronmk
|
Add a new datasource: make inputs/<datasrc>/add
|
| 59 |
|
|
<datasrc> may not contain spaces, and should be abbreviated.
|
| 60 |
|
|
If the datasource is a herbarium, <datasrc> should be the herbarium code
|
| 61 |
|
|
as defined by the Index Herbariorum <http://sweetgum.nybg.org/ih/>
|
| 62 |
4218
|
aaronmk
|
Add input data for each table present in the datasource:
|
| 63 |
|
|
Choose a table name from <https://projects.nceas.ucsb.edu/nceas/projects
|
| 64 |
|
|
/bien/wiki/VegCSV#Suggested-table-names>, or use a custom name
|
| 65 |
4264
|
aaronmk
|
Note that if this table will be joined together with another table, its
|
| 66 |
|
|
name must end in ".src"
|
| 67 |
4219
|
aaronmk
|
make inputs/<datasrc>/<table>/add
|
| 68 |
|
|
Place the CSV for the table in inputs/<datasrc>/<table>/
|
| 69 |
4264
|
aaronmk
|
OR place a query joining other tables together in
|
| 70 |
|
|
inputs/<datasrc>/<table>/create.sql and svn add this file
|
| 71 |
4212
|
aaronmk
|
Important: When exporting relational databases to CSVs, you MUST ensure
|
| 72 |
|
|
that embedded quotes are escaped by doubling them, *not* by
|
| 73 |
|
|
preceding them with a "\" as is the default in phpMyAdmin
|
| 74 |
3612
|
aaronmk
|
If there are multiple part files for a table, and the header is repeated
|
| 75 |
|
|
in each part, make sure each header is EXACTLY the same.
|
| 76 |
|
|
(If the headers are not the same, the CSV concatenation script
|
| 77 |
|
|
assumes the part files don't have individual headers and treats the
|
| 78 |
|
|
subsequent headers as data rows.)
|
| 79 |
4255
|
aaronmk
|
Add <table> to inputs/<datasrc>/import_order.txt before other tables
|
| 80 |
4220
|
aaronmk
|
that depend on it
|
| 81 |
3593
|
aaronmk
|
Auto-create the map spreadsheets:
|
| 82 |
4219
|
aaronmk
|
make inputs/<datasrc>/; make inputs/<datasrc>/
|
| 83 |
3593
|
aaronmk
|
Note: Must be run twice to properly bootstrap all maps.
|
| 84 |
4265
|
aaronmk
|
svn add inputs/<datasrc>/*/{,.}{header,src,map*,VegBIEN}.csv{,.*}
|
| 85 |
3611
|
aaronmk
|
Install the staging tables:
|
| 86 |
4219
|
aaronmk
|
make inputs/<datasrc>/reinstall quiet=1 &
|
| 87 |
|
|
To view progress: tail -f inputs/<datasrc>/<table>/logs/install.log.sql
|
| 88 |
|
|
View the logs: tail -n +1 inputs/<datasrc>/*/logs/install.log.sql
|
| 89 |
3611
|
aaronmk
|
tail provides a header line with the filename
|
| 90 |
|
|
+1 starts at the first line, to show the whole file
|
| 91 |
|
|
For every file with an error 'column "..." specified more than once':
|
| 92 |
4182
|
aaronmk
|
Add a header override file "+header.<ext>" in <table>/:
|
| 93 |
3611
|
aaronmk
|
Note: The leading "+" should sort it before the flat files.
|
| 94 |
|
|
"_" unfortunately sorts *after* capital letters in ASCII.
|
| 95 |
|
|
Create a text file containing the header line of the flat files
|
| 96 |
|
|
Add an ! at the beginning of the line
|
| 97 |
|
|
This signals cat_csv that this is a header override.
|
| 98 |
|
|
For empty names, use their 0-based column # (by convention)
|
| 99 |
|
|
For duplicate names, add a distinguishing suffix
|
| 100 |
|
|
For long names that collided, rename them to <= 63 chars long
|
| 101 |
|
|
Do NOT make readability changes in this step; that is what the
|
| 102 |
|
|
map spreadsheets (below) are for.
|
| 103 |
|
|
Save
|
| 104 |
4219
|
aaronmk
|
svn add inputs/<datasrc>/<table>/<header_override>
|
| 105 |
3611
|
aaronmk
|
If you made any changes, re-run the install command above
|
| 106 |
3576
|
aaronmk
|
Map each table's columns:
|
| 107 |
4125
|
aaronmk
|
In each <table>/ subdir, for each "via map" map.csv:
|
| 108 |
3576
|
aaronmk
|
Open the map in a spreadsheet editor
|
| 109 |
4125
|
aaronmk
|
Open the "core map" /mappings/Veg+-VegBIEN.csv
|
| 110 |
3576
|
aaronmk
|
In each row of the via map, set the right column to a value from the
|
| 111 |
|
|
left column of the core map
|
| 112 |
|
|
Save
|
| 113 |
4219
|
aaronmk
|
Regenerate the derived maps: make inputs/<datasrc>/
|
| 114 |
3593
|
aaronmk
|
Accept the test cases:
|
| 115 |
4219
|
aaronmk
|
make inputs/<datasrc>/test
|
| 116 |
3593
|
aaronmk
|
When prompted to "Accept new test output", enter y and press ENTER
|
| 117 |
3690
|
aaronmk
|
If you instead get errors, do one of the following for each one:
|
| 118 |
|
|
- If the error was due to a bug, fix it
|
| 119 |
|
|
- Add a SQL function that filters or transforms the invalid data
|
| 120 |
|
|
- Make an empty mapping for the columns that produced the error.
|
| 121 |
|
|
Put something in the Comments column of the map spreadsheet to
|
| 122 |
|
|
prevent the automatic mapper from auto-removing the mapping.
|
| 123 |
3783
|
aaronmk
|
When accepting tests, it's helpful to use WinMerge
|
| 124 |
|
|
(see WinMerge setup below for configuration)
|
| 125 |
4219
|
aaronmk
|
svn add inputs/<datasrc>/*/test.xml.ref
|
| 126 |
|
|
Commit: svn ci -m "Added inputs/<datasrc>/" inputs/<datasrc>/
|
| 127 |
3585
|
aaronmk
|
Update vegbiendev:
|
| 128 |
|
|
On vegbiendev: svn up
|
| 129 |
|
|
On local machine: make inputs/upload
|
| 130 |
3588
|
aaronmk
|
On vegbiendev: Follow the steps under Install the staging tables above
|
| 131 |
1773
|
aaronmk
|
|
| 132 |
702
|
aaronmk
|
Schema changes:
|
| 133 |
|
|
Regenerate schema from installed DB: make schemas/remake
|
| 134 |
1967
|
aaronmk
|
Reinstall DB from schema: make schemas/reinstall
|
| 135 |
3370
|
aaronmk
|
WARNING: This will delete the current public schema of your VegBIEN DB!
|
| 136 |
3441
|
aaronmk
|
Reinstall errors tables: make inputs/install errors_table_only=1
|
| 137 |
3589
|
aaronmk
|
Reinstall staging tables: . bin/reinstall_all
|
| 138 |
702
|
aaronmk
|
Sync ERD with vegbien.sql schema:
|
| 139 |
|
|
Run make schemas/vegbien.my.sql
|
| 140 |
|
|
Open schemas/vegbien.ERD.mwb in MySQLWorkbench
|
| 141 |
|
|
Go to File > Export > Synchronize With SQL CREATE Script...
|
| 142 |
|
|
For Input File, select schemas/vegbien.my.sql
|
| 143 |
|
|
Click Continue
|
| 144 |
|
|
Click in the changes list and press Ctrl+A or Apple+A to select all
|
| 145 |
|
|
Click Update Model
|
| 146 |
|
|
Click Continue
|
| 147 |
|
|
Note: The generated SQL script will be empty because we are syncing in
|
| 148 |
|
|
the opposite direction
|
| 149 |
|
|
Click Execute
|
| 150 |
|
|
Reposition any lines that have been reset
|
| 151 |
|
|
Add any new tables by dragging them from the Catalog in the left sidebar
|
| 152 |
|
|
to the diagram
|
| 153 |
|
|
Remove any deleted tables by right-clicking the table's diagram element,
|
| 154 |
|
|
selecting Delete '<table name>', and clicking Delete
|
| 155 |
|
|
Save
|
| 156 |
1774
|
aaronmk
|
If desired, update the graphical ERD exports (see below)
|
| 157 |
|
|
Update graphical ERD exports:
|
| 158 |
702
|
aaronmk
|
Go to File > Export > Export as PNG...
|
| 159 |
1774
|
aaronmk
|
Select schemas/vegbien.ERD.png and click Save
|
| 160 |
702
|
aaronmk
|
Go to File > Export > Export as SVG...
|
| 161 |
1774
|
aaronmk
|
Select schemas/vegbien.ERD.svg and click Save
|
| 162 |
702
|
aaronmk
|
Go to File > Export > Export as Single Page PDF...
|
| 163 |
4087
|
aaronmk
|
Select schemas/vegbien.ERD.1_pg.pdf and click Save
|
| 164 |
1774
|
aaronmk
|
Go to File > Print...
|
| 165 |
|
|
In the lower left corner, click PDF > Save as PDF...
|
| 166 |
|
|
Set the Title and Author to ""
|
| 167 |
4087
|
aaronmk
|
Select schemas/vegbien.ERD.pdf and click Save
|
| 168 |
203
|
aaronmk
|
|
| 169 |
1459
|
aaronmk
|
Testing:
|
| 170 |
|
|
Mapping process: make test
|
| 171 |
|
|
Map spreadsheet generation: make remake
|
| 172 |
1744
|
aaronmk
|
Missing mappings: make missing_mappings
|
| 173 |
1459
|
aaronmk
|
Everything (for most complete coverage): make test-all
|
| 174 |
702
|
aaronmk
|
|
| 175 |
3783
|
aaronmk
|
WinMerge setup:
|
| 176 |
|
|
Install WinMerge from <http://winmerge.org/>
|
| 177 |
3785
|
aaronmk
|
Open WinMerge
|
| 178 |
|
|
Go to Edit > Options and click Compare in the left sidebar
|
| 179 |
3783
|
aaronmk
|
Enable "Moved block detection", as described at
|
| 180 |
|
|
<http://manual.winmerge.org/Configuration.html#d0e5892>.
|
| 181 |
3784
|
aaronmk
|
Set Whitespace to Ignore change, as described at
|
| 182 |
|
|
<http://manual.winmerge.org/Configuration.html#d0e5758>.
|
| 183 |
3783
|
aaronmk
|
|
| 184 |
3133
|
aaronmk
|
Documentation:
|
| 185 |
|
|
To generate a Redmine-formatted list of steps for column-based import:
|
| 186 |
4182
|
aaronmk
|
make inputs/QMOR/specimens/logs/steps.by_col.log.sql
|
| 187 |
3133
|
aaronmk
|
|
| 188 |
702
|
aaronmk
|
General:
|
| 189 |
|
|
To see a program's description, read its top-of-file comment
|
| 190 |
|
|
To see a program's usage, run it without arguments
|
| 191 |
3389
|
aaronmk
|
To remake a directory: make <dir>/remake
|
| 192 |
|
|
To remake a file: make <file>-remake
|
