1 |
702
|
aaronmk
|
Installation:
|
2 |
6886
|
aaronmk
|
Check out svn: svn co https://code.nceas.ucsb.edu/code/projects/bien
|
3 |
6887
|
aaronmk
|
cd bien/
|
4 |
702
|
aaronmk
|
Install: make install
|
5 |
3370
|
aaronmk
|
WARNING: This will delete the current public schema of your VegBIEN DB!
|
6 |
702
|
aaronmk
|
Uninstall: make uninstall
|
7 |
3370
|
aaronmk
|
WARNING: This will delete your entire VegBIEN DB!
|
8 |
3374
|
aaronmk
|
This includes all archived imports and staging tables.
|
9 |
554
|
aaronmk
|
|
10 |
3674
|
aaronmk
|
Maintenance:
|
11 |
6846
|
aaronmk
|
VegCore data dictionary:
|
12 |
|
|
Regularly, or whenever the VegCore data dictionary page
|
13 |
|
|
(https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/VegCore)
|
14 |
|
|
is changed, regenerate mappings/VegCore.csv:
|
15 |
6856
|
aaronmk
|
make mappings/VegCore.htm-remake; make mappings/
|
16 |
6987
|
aaronmk
|
svn ci -m "mappings/VegCore.csv: Regenerated from wiki"
|
17 |
3674
|
aaronmk
|
Important: Whenever you install a system update that affects PostgreSQL or
|
18 |
|
|
any of its dependencies, such as libc, you should restart the PostgreSQL
|
19 |
|
|
server. Otherwise, you may get strange errors like "the database system
|
20 |
6820
|
aaronmk
|
is in recovery mode" which go away upon reimport, or you may not be able
|
21 |
6821
|
aaronmk
|
to access the database as the postgres superuser. This applies to both
|
22 |
|
|
Linux and Mac OS X.
|
23 |
3674
|
aaronmk
|
|
24 |
702
|
aaronmk
|
Data import:
|
25 |
4487
|
aaronmk
|
On local machine:
|
26 |
|
|
make test by_col=1
|
27 |
5016
|
aaronmk
|
See note under Testing below
|
28 |
4487
|
aaronmk
|
On vegbiendev:
|
29 |
7032
|
aaronmk
|
Ensure there are no local modifications: svn st
|
30 |
4482
|
aaronmk
|
svn up
|
31 |
6059
|
aaronmk
|
make inputs/upload
|
32 |
|
|
For each newly-uploaded datasource: make inputs/<datasrc>/reinstall
|
33 |
6952
|
aaronmk
|
Update the auxiliary schemas: make schemas/reinstall
|
34 |
|
|
The public schema will be installed separately by the import process
|
35 |
6962
|
aaronmk
|
Delete imports before the last so they won't bloat the full DB backup:
|
36 |
|
|
make backups/vegbien.<version>.backup/remove
|
37 |
6802
|
aaronmk
|
Make sure there is at least 100GB of disk space on /: df -h
|
38 |
|
|
The import schema is 75GB, and may use additional space for temp tables
|
39 |
6804
|
aaronmk
|
To free up space, remove backups that have been archived on jupiter:
|
40 |
|
|
List backups/ to view older backups
|
41 |
|
|
Check their MD5 sums using the steps under On jupiter below
|
42 |
|
|
Remove these backups
|
43 |
6995
|
aaronmk
|
unset version
|
44 |
5206
|
aaronmk
|
Start column-based import: . bin/import_all by_col=1
|
45 |
|
|
To use row-based import: . bin/import_all
|
46 |
4481
|
aaronmk
|
To stop all running imports: . bin/stop_imports
|
47 |
5318
|
aaronmk
|
WARNING: Do NOT run import_all in the background, or the jobs it creates
|
48 |
|
|
won't be owned by your shell.
|
49 |
6797
|
aaronmk
|
Note that import_all will take several hours to import the NCBI backbone
|
50 |
|
|
and TNRS names before returning control to the shell.
|
51 |
4481
|
aaronmk
|
Wait (overnight) for the import to finish
|
52 |
6796
|
aaronmk
|
On local machine: make inputs/download-logs
|
53 |
7030
|
aaronmk
|
In PostgreSQL, check that the provider_count and source tables contain
|
54 |
|
|
entries for all inputs
|
55 |
6964
|
aaronmk
|
tail inputs/{.,}*/*/logs/$version.log.sql
|
56 |
6799
|
aaronmk
|
In the output, search for "Command exited with non-zero status"
|
57 |
|
|
For inputs that have this, fix the associated bug(s)
|
58 |
|
|
If many inputs have errors, discard the current (partial) import:
|
59 |
6953
|
aaronmk
|
make schemas/$version/uninstall
|
60 |
6103
|
aaronmk
|
Otherwise, continue
|
61 |
6959
|
aaronmk
|
make schemas/$version/publish
|
62 |
6848
|
aaronmk
|
sudo backups/fix_perms
|
63 |
6666
|
aaronmk
|
make backups/upload
|
64 |
6719
|
aaronmk
|
On jupiter:
|
65 |
6888
|
aaronmk
|
cd /data/dev/aaronmk/bien/backups
|
66 |
6719
|
aaronmk
|
For each newly-archived backup:
|
67 |
|
|
make <backup>.md5/test
|
68 |
|
|
Check that "OK" is printed next to the filename
|
69 |
6494
|
aaronmk
|
On nimoy:
|
70 |
|
|
cd /home/bien/svn/
|
71 |
|
|
svn up
|
72 |
6993
|
aaronmk
|
export version=<version>
|
73 |
6948
|
aaronmk
|
make backups/analytical_aggregate.$version.csv/download
|
74 |
|
|
make backups/analytical_aggregate.$version.csv.md5/test
|
75 |
6723
|
aaronmk
|
Check that "OK" is printed next to the filename
|
76 |
6494
|
aaronmk
|
In the bien_web DB:
|
77 |
6994
|
aaronmk
|
Create the analytical_aggregate_<version> table using its schema
|
78 |
6652
|
aaronmk
|
in schemas/vegbien.my.sql
|
79 |
6948
|
aaronmk
|
env table=analytical_aggregate_$version bin/publish_analytical_db \
|
80 |
|
|
backups/analytical_aggregate.$version.csv
|
81 |
4677
|
aaronmk
|
If desired, record the import times in inputs/import.stats.xls:
|
82 |
|
|
Open inputs/import.stats.xls
|
83 |
7027
|
aaronmk
|
Insert a copy of the leftmost "By column" column group before it
|
84 |
6996
|
aaronmk
|
bin/import_date inputs/{.,}*/*/logs/$version.log.sql
|
85 |
4677
|
aaronmk
|
Update the import date in the upper-right corner
|
86 |
6996
|
aaronmk
|
bin/import_times inputs/{.,}*/*/logs/$version.log.sql
|
87 |
4677
|
aaronmk
|
Paste the output over the # Rows/Time columns, making sure that the
|
88 |
|
|
row counts match up with the previous import's row counts
|
89 |
|
|
If the row counts do not match up, insert or reorder rows as needed
|
90 |
|
|
until they do
|
91 |
4777
|
aaronmk
|
Commit: svn ci -m "inputs/import.stats.xls: Updated import times"
|
92 |
6956
|
aaronmk
|
To remake analytical DB: bin/make_analytical_db &
|
93 |
6212
|
aaronmk
|
To view progress:
|
94 |
|
|
tail -f inputs/analytical_db/logs/make_analytical_db.log.sql
|
95 |
3381
|
aaronmk
|
|
96 |
3545
|
aaronmk
|
Backups:
|
97 |
3408
|
aaronmk
|
Archived imports:
|
98 |
6942
|
aaronmk
|
Back up: make backups/<version>.backup &
|
99 |
6516
|
aaronmk
|
Note: To back up the last import, you must archive it first:
|
100 |
|
|
make schemas/rotate
|
101 |
6942
|
aaronmk
|
Test: make backups/<version>.backup/test &
|
102 |
|
|
Restore: make backups/<version>.backup/restore &
|
103 |
|
|
Remove: make backups/<version>.backup/remove
|
104 |
3701
|
aaronmk
|
Download: make backups/download
|
105 |
5801
|
aaronmk
|
TNRS cache:
|
106 |
|
|
Back up: make backups/TNRS.backup-remake &
|
107 |
|
|
Restore:
|
108 |
|
|
yes|make inputs/.TNRS/uninstall
|
109 |
|
|
make backups/TNRS.backup/restore &
|
110 |
|
|
yes|make schemas/public/reinstall
|
111 |
|
|
Must come after TNRS restore to recreate tnrs_input_name view
|
112 |
3408
|
aaronmk
|
Full DB:
|
113 |
6942
|
aaronmk
|
Back up: make backups/vegbien.<version>.backup &
|
114 |
|
|
Test: make backups/vegbien.<version>.backup/test &
|
115 |
|
|
Restore: make backups/vegbien.<version>.backup/restore &
|
116 |
3701
|
aaronmk
|
Download: make backups/download
|
117 |
3698
|
aaronmk
|
Import logs:
|
118 |
|
|
Download: make inputs/download-logs
|
119 |
554
|
aaronmk
|
|
120 |
1773
|
aaronmk
|
Datasource setup:
|
121 |
4219
|
aaronmk
|
Add a new datasource: make inputs/<datasrc>/add
|
122 |
|
|
<datasrc> may not contain spaces, and should be abbreviated.
|
123 |
|
|
If the datasource is a herbarium, <datasrc> should be the herbarium code
|
124 |
|
|
as defined by the Index Herbariorum <http://sweetgum.nybg.org/ih/>
|
125 |
6366
|
aaronmk
|
For MySQL inputs (exports and live DB connections):
|
126 |
|
|
For .sql exports:
|
127 |
|
|
Place the original .sql file in _src/ (*not* in _MySQL/)
|
128 |
|
|
Create a database for the MySQL export in phpMyAdmin
|
129 |
|
|
mysql -p database <inputs/<datasrc>/_src/export.sql
|
130 |
|
|
mkdir inputs/<datasrc>/_MySQL/
|
131 |
|
|
cp -p lib/MySQL.{data,schema}.sql.make inputs/<datasrc>/_MySQL/
|
132 |
|
|
Edit _MySQL/*.make for the DB connection
|
133 |
|
|
For a .sql export, use your local MySQL DB
|
134 |
6372
|
aaronmk
|
Install the export according to Install the staging tables below
|
135 |
4218
|
aaronmk
|
Add input data for each table present in the datasource:
|
136 |
6365
|
aaronmk
|
For .sql exports, you must use the name of the table in the DB export
|
137 |
|
|
For CSV files, you can use any name. It's recommended to use a table
|
138 |
|
|
name from <https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/VegCSV#Suggested-table-names>
|
139 |
4264
|
aaronmk
|
Note that if this table will be joined together with another table, its
|
140 |
|
|
name must end in ".src"
|
141 |
4219
|
aaronmk
|
make inputs/<datasrc>/<table>/add
|
142 |
4342
|
aaronmk
|
Important: DO NOT just create an empty directory named <table>!
|
143 |
|
|
This command also creates necessary subdirs, such as logs/.
|
144 |
6373
|
aaronmk
|
If the table is in a .sql export: make inputs/<datasrc>/<table>/install
|
145 |
|
|
Otherwise, place the CSV(s) for the table in
|
146 |
6370
|
aaronmk
|
inputs/<datasrc>/<table>/ OR place a query joining other tables
|
147 |
|
|
together in inputs/<datasrc>/<table>/create.sql
|
148 |
4212
|
aaronmk
|
Important: When exporting relational databases to CSVs, you MUST ensure
|
149 |
|
|
that embedded quotes are escaped by doubling them, *not* by
|
150 |
|
|
preceding them with a "\" as is the default in phpMyAdmin
|
151 |
3612
|
aaronmk
|
If there are multiple part files for a table, and the header is repeated
|
152 |
|
|
in each part, make sure each header is EXACTLY the same.
|
153 |
|
|
(If the headers are not the same, the CSV concatenation script
|
154 |
|
|
assumes the part files don't have individual headers and treats the
|
155 |
|
|
subsequent headers as data rows.)
|
156 |
4255
|
aaronmk
|
Add <table> to inputs/<datasrc>/import_order.txt before other tables
|
157 |
4220
|
aaronmk
|
that depend on it
|
158 |
3611
|
aaronmk
|
Install the staging tables:
|
159 |
4219
|
aaronmk
|
make inputs/<datasrc>/reinstall quiet=1 &
|
160 |
|
|
To view progress: tail -f inputs/<datasrc>/<table>/logs/install.log.sql
|
161 |
|
|
View the logs: tail -n +1 inputs/<datasrc>/*/logs/install.log.sql
|
162 |
3611
|
aaronmk
|
tail provides a header line with the filename
|
163 |
|
|
+1 starts at the first line, to show the whole file
|
164 |
|
|
For every file with an error 'column "..." specified more than once':
|
165 |
4182
|
aaronmk
|
Add a header override file "+header.<ext>" in <table>/:
|
166 |
3611
|
aaronmk
|
Note: The leading "+" should sort it before the flat files.
|
167 |
|
|
"_" unfortunately sorts *after* capital letters in ASCII.
|
168 |
|
|
Create a text file containing the header line of the flat files
|
169 |
|
|
Add an ! at the beginning of the line
|
170 |
|
|
This signals cat_csv that this is a header override.
|
171 |
|
|
For empty names, use their 0-based column # (by convention)
|
172 |
|
|
For duplicate names, add a distinguishing suffix
|
173 |
|
|
For long names that collided, rename them to <= 63 chars long
|
174 |
|
|
Do NOT make readability changes in this step; that is what the
|
175 |
|
|
map spreadsheets (below) are for.
|
176 |
|
|
Save
|
177 |
|
|
If you made any changes, re-run the install command above
|
178 |
6015
|
aaronmk
|
Auto-create the map spreadsheets: make inputs/<datasrc>/
|
179 |
3576
|
aaronmk
|
Map each table's columns:
|
180 |
4125
|
aaronmk
|
In each <table>/ subdir, for each "via map" map.csv:
|
181 |
3576
|
aaronmk
|
Open the map in a spreadsheet editor
|
182 |
4125
|
aaronmk
|
Open the "core map" /mappings/Veg+-VegBIEN.csv
|
183 |
3576
|
aaronmk
|
In each row of the via map, set the right column to a value from the
|
184 |
|
|
left column of the core map
|
185 |
|
|
Save
|
186 |
4219
|
aaronmk
|
Regenerate the derived maps: make inputs/<datasrc>/
|
187 |
3593
|
aaronmk
|
Accept the test cases:
|
188 |
4219
|
aaronmk
|
make inputs/<datasrc>/test
|
189 |
3593
|
aaronmk
|
When prompted to "Accept new test output", enter y and press ENTER
|
190 |
3690
|
aaronmk
|
If you instead get errors, do one of the following for each one:
|
191 |
|
|
- If the error was due to a bug, fix it
|
192 |
|
|
- Add a SQL function that filters or transforms the invalid data
|
193 |
|
|
- Make an empty mapping for the columns that produced the error.
|
194 |
|
|
Put something in the Comments column of the map spreadsheet to
|
195 |
|
|
prevent the automatic mapper from auto-removing the mapping.
|
196 |
3783
|
aaronmk
|
When accepting tests, it's helpful to use WinMerge
|
197 |
|
|
(see WinMerge setup below for configuration)
|
198 |
4476
|
aaronmk
|
make inputs/<datasrc>/test by_col=1
|
199 |
|
|
If you get errors this time, this always indicates a bug, usually in
|
200 |
6368
|
aaronmk
|
the VegBIEN unique constraints or column-based import itself
|
201 |
5881
|
aaronmk
|
Add newly-created files: make inputs/<datasrc>/add
|
202 |
4219
|
aaronmk
|
Commit: svn ci -m "Added inputs/<datasrc>/" inputs/<datasrc>/
|
203 |
3585
|
aaronmk
|
Update vegbiendev:
|
204 |
|
|
On vegbiendev: svn up
|
205 |
|
|
On local machine: make inputs/upload
|
206 |
4291
|
aaronmk
|
On vegbiendev:
|
207 |
|
|
Follow the steps under Install the staging tables above
|
208 |
|
|
make inputs/<datasrc>/test
|
209 |
1773
|
aaronmk
|
|
210 |
6484
|
aaronmk
|
Datasource refreshing:
|
211 |
|
|
VegBank:
|
212 |
|
|
make inputs/VegBank/vegbank.sql-remake
|
213 |
|
|
make inputs/VegBank/reinstall quiet=1 &
|
214 |
|
|
|
215 |
702
|
aaronmk
|
Schema changes:
|
216 |
5227
|
aaronmk
|
Remember to update the following files with any renamings:
|
217 |
|
|
schemas/filter_ERD.csv
|
218 |
|
|
mappings/VegCore-VegBIEN.csv
|
219 |
6058
|
aaronmk
|
mappings/verify.*.sql
|
220 |
702
|
aaronmk
|
Regenerate schema from installed DB: make schemas/remake
|
221 |
6951
|
aaronmk
|
Reinstall DB from schema: make schemas/public/reinstall schemas/reinstall
|
222 |
3370
|
aaronmk
|
WARNING: This will delete the current public schema of your VegBIEN DB!
|
223 |
3589
|
aaronmk
|
Reinstall staging tables: . bin/reinstall_all
|
224 |
702
|
aaronmk
|
Sync ERD with vegbien.sql schema:
|
225 |
|
|
Run make schemas/vegbien.my.sql
|
226 |
|
|
Open schemas/vegbien.ERD.mwb in MySQLWorkbench
|
227 |
|
|
Go to File > Export > Synchronize With SQL CREATE Script...
|
228 |
|
|
For Input File, select schemas/vegbien.my.sql
|
229 |
|
|
Click Continue
|
230 |
6369
|
aaronmk
|
In the changes list, select each table with an arrow next to it
|
231 |
702
|
aaronmk
|
Click Update Model
|
232 |
|
|
Click Continue
|
233 |
|
|
Note: The generated SQL script will be empty because we are syncing in
|
234 |
|
|
the opposite direction
|
235 |
|
|
Click Execute
|
236 |
|
|
Reposition any lines that have been reset
|
237 |
|
|
Add any new tables by dragging them from the Catalog in the left sidebar
|
238 |
|
|
to the diagram
|
239 |
|
|
Remove any deleted tables by right-clicking the table's diagram element,
|
240 |
|
|
selecting Delete '<table name>', and clicking Delete
|
241 |
|
|
Save
|
242 |
1774
|
aaronmk
|
If desired, update the graphical ERD exports (see below)
|
243 |
|
|
Update graphical ERD exports:
|
244 |
702
|
aaronmk
|
Go to File > Export > Export as PNG...
|
245 |
1774
|
aaronmk
|
Select schemas/vegbien.ERD.png and click Save
|
246 |
702
|
aaronmk
|
Go to File > Export > Export as SVG...
|
247 |
1774
|
aaronmk
|
Select schemas/vegbien.ERD.svg and click Save
|
248 |
702
|
aaronmk
|
Go to File > Export > Export as Single Page PDF...
|
249 |
4087
|
aaronmk
|
Select schemas/vegbien.ERD.1_pg.pdf and click Save
|
250 |
1774
|
aaronmk
|
Go to File > Print...
|
251 |
|
|
In the lower left corner, click PDF > Save as PDF...
|
252 |
|
|
Set the Title and Author to ""
|
253 |
4087
|
aaronmk
|
Select schemas/vegbien.ERD.pdf and click Save
|
254 |
7003
|
aaronmk
|
Commit: svn ci -m "schemas/vegbien.ERD.mwb: Regenerated exports"
|
255 |
5226
|
aaronmk
|
Refactoring tips:
|
256 |
|
|
To rename a table:
|
257 |
|
|
In vegbien.sql, do the following:
|
258 |
|
|
Replace regexp (?<=_|\b)<old>(?=_|\b) with <new>
|
259 |
|
|
This is necessary because the table name is *everywhere*
|
260 |
|
|
Search for <new>
|
261 |
|
|
Manually change back any replacements inside comments
|
262 |
|
|
To rename a column:
|
263 |
|
|
Rename the column: ALTER TABLE <table> RENAME <old> TO <new>;
|
264 |
|
|
Recreate any foreign key for the column, removing CONSTRAINT <name>
|
265 |
|
|
This resets the foreign key name using the new column name
|
266 |
6320
|
aaronmk
|
Creating a poster of the ERD:
|
267 |
6326
|
aaronmk
|
Determine the poster size:
|
268 |
6320
|
aaronmk
|
Measure the line height (from the bottom of one line to the bottom
|
269 |
|
|
of another): 16.3cm/24 lines = 0.679cm
|
270 |
|
|
Measure the height of the ERD: 35.4cm*2 = 70.8cm
|
271 |
|
|
Zoom in as far as possible
|
272 |
|
|
Measure the height of a capital letter: 3.5mm
|
273 |
|
|
Measure the line height: 8.5mm
|
274 |
|
|
Calculate the text's fraction of the line height: 3.5mm/8.5mm = 0.41
|
275 |
|
|
Calculate the text height: 0.679cm*0.41 = 0.28cm
|
276 |
|
|
Calculate the text height's fraction of the ERD height:
|
277 |
|
|
0.28cm/70.8cm = 0.0040
|
278 |
|
|
Measure the text height on the *VegBank* ERD poster: 5.5mm = 0.55cm
|
279 |
|
|
Calculate the VegBIEN poster height to make the text the same size:
|
280 |
|
|
0.55cm/0.0040 = 137.5cm H; *1in/2.54cm = 54.1in H
|
281 |
|
|
The ERD aspect ratio is 11 in W x (2*8.5in H) = 11x17 portrait
|
282 |
|
|
Calculate the VegBIEN poster width: 54.1in H*11W/17H = 35.0in W
|
283 |
|
|
The minimum VegBIEN poster size is 35x54in portrait
|
284 |
6327
|
aaronmk
|
Determine the cost:
|
285 |
|
|
The FedEx Kinkos near NCEAS (1030 State St, Santa Barbara, CA 93101)
|
286 |
|
|
charges the following for posters:
|
287 |
|
|
base: $7.25/sq ft
|
288 |
|
|
lamination: $3/sq ft
|
289 |
|
|
mounting on a board: $8/sq ft
|
290 |
203
|
aaronmk
|
|
291 |
1459
|
aaronmk
|
Testing:
|
292 |
|
|
Mapping process: make test
|
293 |
4292
|
aaronmk
|
Including column-based import: make test by_col=1
|
294 |
4985
|
aaronmk
|
If the row-based and column-based imports produce different inserted
|
295 |
|
|
row counts, this usually means that a table is underconstrained
|
296 |
|
|
(the unique indexes don't cover all possible rows).
|
297 |
|
|
This can occur if you didn't use COALESCE(field, null_value) around
|
298 |
|
|
a nullable field in a unique index. See sql_gen.null_sentinels for
|
299 |
|
|
the appropriate null value to use.
|
300 |
1459
|
aaronmk
|
Map spreadsheet generation: make remake
|
301 |
1744
|
aaronmk
|
Missing mappings: make missing_mappings
|
302 |
1459
|
aaronmk
|
Everything (for most complete coverage): make test-all
|
303 |
702
|
aaronmk
|
|
304 |
3783
|
aaronmk
|
WinMerge setup:
|
305 |
|
|
Install WinMerge from <http://winmerge.org/>
|
306 |
3785
|
aaronmk
|
Open WinMerge
|
307 |
|
|
Go to Edit > Options and click Compare in the left sidebar
|
308 |
3783
|
aaronmk
|
Enable "Moved block detection", as described at
|
309 |
|
|
<http://manual.winmerge.org/Configuration.html#d0e5892>.
|
310 |
3784
|
aaronmk
|
Set Whitespace to Ignore change, as described at
|
311 |
|
|
<http://manual.winmerge.org/Configuration.html#d0e5758>.
|
312 |
3783
|
aaronmk
|
|
313 |
3133
|
aaronmk
|
Documentation:
|
314 |
|
|
To generate a Redmine-formatted list of steps for column-based import:
|
315 |
6291
|
aaronmk
|
make inputs/ACAD/Specimen/logs/steps.by_col.log.sql
|
316 |
5210
|
aaronmk
|
To import and scrub just the test taxonomic names:
|
317 |
5415
|
aaronmk
|
inputs/test_taxonomic_names/test_scrub
|
318 |
3133
|
aaronmk
|
|
319 |
702
|
aaronmk
|
General:
|
320 |
|
|
To see a program's description, read its top-of-file comment
|
321 |
|
|
To see a program's usage, run it without arguments
|
322 |
3389
|
aaronmk
|
To remake a directory: make <dir>/remake
|
323 |
|
|
To remake a file: make <file>-remake
|