1
|
Installation:
|
2
|
Install: make install
|
3
|
WARNING: This will delete the current public schema of your VegBIEN DB!
|
4
|
Uninstall: make uninstall
|
5
|
WARNING: This will delete your entire VegBIEN DB!
|
6
|
This includes all archived imports and staging tables.
|
7
|
|
8
|
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
|
Data import:
|
15
|
On local machine:
|
16
|
make test by_col=1
|
17
|
See note under Testing below
|
18
|
On vegbiendev:
|
19
|
svn up
|
20
|
Update the schemas: make schemas/reinstall
|
21
|
WARNING: This will delete the current public schema of your VegBIEN DB!
|
22
|
To save it: make schemas/rotate
|
23
|
Important: This must be done *after* running make_analytical_db on a
|
24
|
previous import
|
25
|
Import the TNRS names:
|
26
|
make inputs/.TNRS/cleanup; make inputs/.TNRS/import by_col=1 &
|
27
|
Wait for the TNRS import to finish: wait
|
28
|
Start column-based import: . bin/import_all by_col=1
|
29
|
To use row-based import: . bin/import_all
|
30
|
To stop all running imports: . bin/stop_imports
|
31
|
Wait (overnight) for the import to finish
|
32
|
./bin/make_analytical_db &
|
33
|
Wait for make_analytical_db to finish
|
34
|
Archive the last import: make schemas/rotate
|
35
|
Important: This must be done *after* running make_analytical_db
|
36
|
Rename the rotated schema using the date in the first datasource's log
|
37
|
file name
|
38
|
tail inputs/*/*/logs/<date>-*.log.sql
|
39
|
Check that every input's log ends in "Encountered 0 error(s)"
|
40
|
If many do not, fix the bug and discard the current (partial) import:
|
41
|
make schemas/public/reinstall
|
42
|
Otherwise, continue
|
43
|
Delete previous imports so they won't bloat the full DB backup:
|
44
|
make backups/public.<datetime>.backup/remove
|
45
|
make inputs/.TNRS/cleanup; make backups/TNRS.backup-remake &
|
46
|
make backups/public.<datetime>.backup/test &
|
47
|
make backups/vegbien.<datetime>.backup/test &
|
48
|
On local machine:
|
49
|
make inputs/download-logs
|
50
|
make backups/download
|
51
|
If desired, record the import times in inputs/import.stats.xls:
|
52
|
Open inputs/import.stats.xls
|
53
|
Insert a copy of the leftmost Column-based column group before it
|
54
|
Update the import date in the upper-right corner
|
55
|
./bin/import_times inputs/*/*/logs/<date>-*.log.sql
|
56
|
Paste the output over the # Rows/Time columns, making sure that the
|
57
|
row counts match up with the previous import's row counts
|
58
|
If the row counts do not match up, insert or reorder rows as needed
|
59
|
until they do
|
60
|
Commit: svn ci -m "inputs/import.stats.xls: Updated import times"
|
61
|
|
62
|
Backups:
|
63
|
Archived imports:
|
64
|
Back up: make backups/public.<date>.backup &
|
65
|
Note: To back up the last import, you must archive it first (above)
|
66
|
Test: make backups/public.<date>.backup/test &
|
67
|
Restore: make backups/public.<date>.backup/restore &
|
68
|
Remove: make backups/public.<date>.backup/remove
|
69
|
Download: make backups/download
|
70
|
Full DB:
|
71
|
Back up, test, and rotate: make backups/vegbien.backup/all &
|
72
|
Back up and rotate: make backups/vegbien.backup/rotate &
|
73
|
Test: make backups/vegbien.<date>.backup/test &
|
74
|
Restore: make backups/vegbien.<date>.backup/restore &
|
75
|
Download: make backups/download
|
76
|
Import logs:
|
77
|
Download: make inputs/download-logs
|
78
|
|
79
|
Datasource setup:
|
80
|
Add a new datasource: make inputs/<datasrc>/add
|
81
|
<datasrc> may not contain spaces, and should be abbreviated.
|
82
|
If the datasource is a herbarium, <datasrc> should be the herbarium code
|
83
|
as defined by the Index Herbariorum <http://sweetgum.nybg.org/ih/>
|
84
|
Install any MySQL export:
|
85
|
Create database in phpMyAdmin
|
86
|
mysql -p database <export.sql
|
87
|
Add input data for each table present in the datasource:
|
88
|
Choose a table name from <https://projects.nceas.ucsb.edu/nceas/projects
|
89
|
/bien/wiki/VegCSV#Suggested-table-names>, or use a custom name
|
90
|
Note that if this table will be joined together with another table, its
|
91
|
name must end in ".src"
|
92
|
make inputs/<datasrc>/<table>/add
|
93
|
Important: DO NOT just create an empty directory named <table>!
|
94
|
This command also creates necessary subdirs, such as logs/.
|
95
|
Place the CSV for the table in inputs/<datasrc>/<table>/
|
96
|
OR place a query joining other tables together in
|
97
|
inputs/<datasrc>/<table>/create.sql and svn add this file
|
98
|
Important: When exporting relational databases to CSVs, you MUST ensure
|
99
|
that embedded quotes are escaped by doubling them, *not* by
|
100
|
preceding them with a "\" as is the default in phpMyAdmin
|
101
|
If there are multiple part files for a table, and the header is repeated
|
102
|
in each part, make sure each header is EXACTLY the same.
|
103
|
(If the headers are not the same, the CSV concatenation script
|
104
|
assumes the part files don't have individual headers and treats the
|
105
|
subsequent headers as data rows.)
|
106
|
Add <table> to inputs/<datasrc>/import_order.txt before other tables
|
107
|
that depend on it
|
108
|
Auto-create the map spreadsheets:
|
109
|
make inputs/<datasrc>/
|
110
|
svn add inputs/<datasrc>/*/{,.}{header,map,*terms,VegBIEN}.csv{,.*}
|
111
|
Install the staging tables:
|
112
|
make inputs/<datasrc>/reinstall quiet=1 &
|
113
|
To view progress: tail -f inputs/<datasrc>/<table>/logs/install.log.sql
|
114
|
View the logs: tail -n +1 inputs/<datasrc>/*/logs/install.log.sql
|
115
|
tail provides a header line with the filename
|
116
|
+1 starts at the first line, to show the whole file
|
117
|
For every file with an error 'column "..." specified more than once':
|
118
|
Add a header override file "+header.<ext>" in <table>/:
|
119
|
Note: The leading "+" should sort it before the flat files.
|
120
|
"_" unfortunately sorts *after* capital letters in ASCII.
|
121
|
Create a text file containing the header line of the flat files
|
122
|
Add an ! at the beginning of the line
|
123
|
This signals cat_csv that this is a header override.
|
124
|
For empty names, use their 0-based column # (by convention)
|
125
|
For duplicate names, add a distinguishing suffix
|
126
|
For long names that collided, rename them to <= 63 chars long
|
127
|
Do NOT make readability changes in this step; that is what the
|
128
|
map spreadsheets (below) are for.
|
129
|
Save
|
130
|
svn add inputs/<datasrc>/<table>/<header_override>
|
131
|
If you made any changes, re-run the install command above
|
132
|
Map each table's columns:
|
133
|
In each <table>/ subdir, for each "via map" map.csv:
|
134
|
Open the map in a spreadsheet editor
|
135
|
Open the "core map" /mappings/Veg+-VegBIEN.csv
|
136
|
In each row of the via map, set the right column to a value from the
|
137
|
left column of the core map
|
138
|
Save
|
139
|
Regenerate the derived maps: make inputs/<datasrc>/
|
140
|
Accept the test cases:
|
141
|
make inputs/<datasrc>/test
|
142
|
When prompted to "Accept new test output", enter y and press ENTER
|
143
|
If you instead get errors, do one of the following for each one:
|
144
|
- If the error was due to a bug, fix it
|
145
|
- Add a SQL function that filters or transforms the invalid data
|
146
|
- Make an empty mapping for the columns that produced the error.
|
147
|
Put something in the Comments column of the map spreadsheet to
|
148
|
prevent the automatic mapper from auto-removing the mapping.
|
149
|
When accepting tests, it's helpful to use WinMerge
|
150
|
(see WinMerge setup below for configuration)
|
151
|
make inputs/<datasrc>/test by_col=1
|
152
|
If you get errors this time, this always indicates a bug, usually in
|
153
|
either the unique constraints or column-based import itself
|
154
|
svn add inputs/<datasrc>/*/test.xml.ref
|
155
|
Commit: svn ci -m "Added inputs/<datasrc>/" inputs/<datasrc>/
|
156
|
Update vegbiendev:
|
157
|
On vegbiendev: svn up
|
158
|
On local machine: make inputs/upload
|
159
|
On vegbiendev:
|
160
|
Follow the steps under Install the staging tables above
|
161
|
make inputs/<datasrc>/test
|
162
|
|
163
|
Schema changes:
|
164
|
Regenerate schema from installed DB: make schemas/remake
|
165
|
Reinstall DB from schema: make schemas/reinstall
|
166
|
WARNING: This will delete the current public schema of your VegBIEN DB!
|
167
|
Reinstall staging tables: . bin/reinstall_all
|
168
|
Sync ERD with vegbien.sql schema:
|
169
|
Run make schemas/vegbien.my.sql
|
170
|
Open schemas/vegbien.ERD.mwb in MySQLWorkbench
|
171
|
Go to File > Export > Synchronize With SQL CREATE Script...
|
172
|
For Input File, select schemas/vegbien.my.sql
|
173
|
Click Continue
|
174
|
Click in the changes list and press Ctrl+A or Apple+A to select all
|
175
|
Click Update Model
|
176
|
Click Continue
|
177
|
Note: The generated SQL script will be empty because we are syncing in
|
178
|
the opposite direction
|
179
|
Click Execute
|
180
|
Reposition any lines that have been reset
|
181
|
Add any new tables by dragging them from the Catalog in the left sidebar
|
182
|
to the diagram
|
183
|
Remove any deleted tables by right-clicking the table's diagram element,
|
184
|
selecting Delete '<table name>', and clicking Delete
|
185
|
Save
|
186
|
If desired, update the graphical ERD exports (see below)
|
187
|
Update graphical ERD exports:
|
188
|
Go to File > Export > Export as PNG...
|
189
|
Select schemas/vegbien.ERD.png and click Save
|
190
|
Go to File > Export > Export as SVG...
|
191
|
Select schemas/vegbien.ERD.svg and click Save
|
192
|
Go to File > Export > Export as Single Page PDF...
|
193
|
Select schemas/vegbien.ERD.1_pg.pdf and click Save
|
194
|
Go to File > Print...
|
195
|
In the lower left corner, click PDF > Save as PDF...
|
196
|
Set the Title and Author to ""
|
197
|
Select schemas/vegbien.ERD.pdf and click Save
|
198
|
|
199
|
Testing:
|
200
|
Mapping process: make test
|
201
|
Including column-based import: make test by_col=1
|
202
|
If the row-based and column-based imports produce different inserted
|
203
|
row counts, this usually means that a table is underconstrained
|
204
|
(the unique indexes don't cover all possible rows).
|
205
|
This can occur if you didn't use COALESCE(field, null_value) around
|
206
|
a nullable field in a unique index. See sql_gen.null_sentinels for
|
207
|
the appropriate null value to use.
|
208
|
Map spreadsheet generation: make remake
|
209
|
Missing mappings: make missing_mappings
|
210
|
Everything (for most complete coverage): make test-all
|
211
|
|
212
|
WinMerge setup:
|
213
|
Install WinMerge from <http://winmerge.org/>
|
214
|
Open WinMerge
|
215
|
Go to Edit > Options and click Compare in the left sidebar
|
216
|
Enable "Moved block detection", as described at
|
217
|
<http://manual.winmerge.org/Configuration.html#d0e5892>.
|
218
|
Set Whitespace to Ignore change, as described at
|
219
|
<http://manual.winmerge.org/Configuration.html#d0e5758>.
|
220
|
|
221
|
Documentation:
|
222
|
To generate a Redmine-formatted list of steps for column-based import:
|
223
|
make inputs/QMOR/Specimen/logs/steps.by_col.log.sql
|
224
|
To import and scrub just the test taxonomic names:
|
225
|
make inputs/.TNRS/reinstall
|
226
|
WARNING: This will delete the TNRS cache!
|
227
|
To save it: make backups/TNRS.backup-remake
|
228
|
make schemas/public/reinstall
|
229
|
WARNING: This will delete the public schema of your VegBIEN DB!
|
230
|
To save it: make schemas/rotate
|
231
|
make inputs/test_taxonomic_names/import log=
|
232
|
Do the following twice: (The 2nd time parses the accepted names.)
|
233
|
make inputs/.TNRS/tnrs/tnrs-remake log=
|
234
|
yes|make schemas/public/reinstall
|
235
|
make inputs/.TNRS/cleanup
|
236
|
make inputs/.TNRS/import log=
|
237
|
make inputs/test_taxonomic_names/import log=
|
238
|
|
239
|
General:
|
240
|
To see a program's description, read its top-of-file comment
|
241
|
To see a program's usage, run it without arguments
|
242
|
To remake a directory: make <dir>/remake
|
243
|
To remake a file: make <file>-remake
|