Project

General

Profile

1
Installation:
2
	Check out svn: svn co https://code.nceas.ucsb.edu/code/projects/bien
3
	cd bien/
4
	Install: make install
5
		WARNING: This will delete the current public schema of your VegBIEN DB!
6
	Uninstall: make uninstall
7
		WARNING: This will delete your entire VegBIEN DB!
8
		This includes all archived imports and staging tables.
9

    
10
Maintenance:
11
	to synchronize vegbiendev, jupiter, and your local machine:
12
		install put if needed:
13
			download https://uutils.googlecode.com/svn/trunk/bin/put to ~/bin/ and `chmod +x` it
14
		when changes are made on vegbiendev:
15
			on vegbiendev, upload:
16
				env overwrite=1             src=. dest='aaronmk@jupiter:~/bien' put --no-group --exclude=.svn --exclude=install.log.sql --exclude='*.backup' --exclude='/backups/analytical_aggregate.*.csv' --exclude='inputs/GBIF/**.data.sql' --exclude='bin/dotlockfile'
17
					then rerun with env l=1 ...
18
				env overwrite=1 del=        src=. dest='aaronmk@jupiter:~/bien' put --no-group --exclude=.svn --exclude=install.log.sql --exclude='inputs/GBIF/**.data.sql'
19
					then rerun with env l=1 ...
20
			on your machine, download:
21
				env overwrite=1 del= swap=1 src=. dest='aaronmk@jupiter:~/bien' put            --exclude=.svn --exclude=install.log.sql --exclude='*.backup' --exclude='/backups/analytical_aggregate.*.csv' --exclude='inputs/GBIF/**.data.sql' --exclude='bin/dotlockfile'
22
					then rerun with env l=1 ...
23
	to synchronize a Mac's settings with my testing machine's:
24
		download:
25
			WARNING: this will overwrite all your user's settings!
26
			env overwrite=1 swap=1 src=~/Library/ dest='aaronmk@jupiter:~/Library/' put --exclude="/Saved Application State"
27
				then rerun with env l=1 ...
28
		upload:
29
			do step when changes are made on vegbiendev > on your machine, download
30
			env overwrite=1        src=~ dest='aaronmk@jupiter:~' put --exclude="/Library/Saved Application State" --exclude="/.Trash" --exclude="/bin" --exclude="/bin/pg_ctl" --exclude="/bin/unzip" --exclude="/Documents/BIEN/large_files" --exclude="/Documents/BIEN/svn/backups/*.backup" --exclude="/Documents/BIEN/svn/backups/analytical_aggregate.*.csv" --exclude="/Dropbox/home"
31
				then rerun with env l=1 ...
32
			env overwrite=1        src=~ dest=~/Dropbox/home      put --exclude="/Library/Saved Application State" --exclude="/.Trash" --exclude="/.dropbox" --exclude="/Documents/BIEN" --exclude="/Dropbox" --exclude="/software" --exclude="/VirtualBox VMs/**.sav" --exclude="/VirtualBox VMs/**.vdi" --exclude="/VirtualBox VMs/**.vmdk"
33
				then rerun with env l=1 ...
34
	to backup files not in Time Machine:
35
		env inplace=1 src=/ dest=/Volumes/Time\ Machine\ Backups/ put Users/aaronmk/VirtualBox\ VMs/
36
			then rerun with env l=1 ...
37
		stop the PostgreSQL server
38
		sudo env src=/ dest=/Volumes/Time\ Machine\ Backups/ put Library/PostgreSQL/9.1/data/
39
			then rerun with env l=1 ...
40
		start the PostgreSQL server
41
	VegCore data dictionary:
42
		Regularly, or whenever the VegCore data dictionary page
43
			(https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/VegCore)
44
			is changed, regenerate mappings/VegCore.csv:
45
			make mappings/VegCore.htm-remake; make mappings/
46
			svn di mappings/VegCore.tables.redmine
47
			If there are changes, update the data dictionary's Tables section
48
			When moving terms, check that no terms were lost: svn di
49
			svn ci -m "mappings/VegCore.htm: Regenerated from wiki"
50
	Important: Whenever you install a system update that affects PostgreSQL or
51
		any of its dependencies, such as libc, you should restart the PostgreSQL
52
		server. Otherwise, you may get strange errors like "the database system
53
		is in recovery mode" which go away upon reimport, or you may not be able
54
		to access the database as the postgres superuser. This applies to both
55
		Linux and Mac OS X.
56

    
57
Single datasource import:
58
	(Re)import and scrub: make inputs/<datasrc>/reimport_scrub by_col=1
59
	(Re)import only: make inputs/<datasrc>/reimport by_col=1
60
	(Re)scrub: make inputs/<datasrc>/rescrub by_col=1
61
	Note that these commands also work if the datasource is not yet imported
62

    
63
Full database import:
64
	On jupiter: svn up
65
	On local machine:
66
		./fix_perms
67
		make inputs/upload
68
		make test by_col=1
69
			See note under Testing below
70
	On vegbiendev:
71
	Ensure there are no local modifications: svn st
72
	svn up
73
	make inputs/download
74
	For each newly-uploaded datasource above: make inputs/<datasrc>/reinstall
75
	Update the auxiliary schemas: make schemas/reinstall
76
		The public schema will be installed separately by the import process
77
	Delete imports before the last so they won't bloat the full DB backup:
78
		make backups/vegbien.<version>.backup/remove
79
		To keep a previous import other than the public schema:
80
			export dump_opts='--exclude-schema=public --exclude-schema=<version>'
81
	Make sure there is at least 150GB of disk space on /: df -h
82
		The import schema is 100GB, and may use additional space for temp tables
83
		To free up space, remove backups that have been archived on jupiter:
84
			List backups/ to view older backups
85
			Check their MD5 sums using the steps under On jupiter below
86
			Remove these backups
87
	unset version
88
	screen
89
	Press ENTER
90
	Start column-based import: . bin/import_all by_col=1
91
		To use row-based import: . bin/import_all
92
		To stop all running imports: . bin/stop_imports
93
		WARNING: Do NOT run import_all in the background, or the jobs it creates
94
			won't be owned by your shell.
95
		Note that import_all will take up to an hour to import the NCBI backbone
96
			and other metadata before returning control to the shell.
97
	Wait (overnight) for the import to finish
98
	To recover from a closed terminal window: screen -r
99
	When there are no more running jobs, exit the screen
100
	Get $version: echo $version
101
	Set $version in all vegbiendev terminals: export version=<version>
102
	Upload logs (run on vegbiendev): make inputs/upload
103
	On local machine: make inputs/download-logs
104
	In PostgreSQL:
105
		Check that the provider_count and source tables contain entries for all
106
			inputs
107
	tail inputs/{.,}*/*/logs/$version.log.sql
108
	In the output, search for "Command exited with non-zero status"
109
	For inputs that have this, fix the associated bug(s)
110
	If many inputs have errors, discard the current (partial) import:
111
		make schemas/$version/uninstall
112
	Otherwise, continue
113
	make schemas/$version/publish
114
	unset version
115
	backups/fix_perms
116
	make backups/upload
117
	On jupiter:
118
		cd /data/dev/aaronmk/bien/backups
119
		For each newly-archived backup:
120
			make -s <backup>.md5/test
121
			Check that "OK" is printed next to the filename
122
	On nimoy:
123
		cd /home/bien/svn/
124
		svn up
125
		export version=<version>
126
		make backups/analytical_stem.$version.csv/download
127
		In the bien_web DB:
128
			Create the analytical_stem_<version> table using its schema
129
				in schemas/vegbien.my.sql
130
		make -s backups/analytical_stem.$version.csv.md5/test
131
		Check that "OK" is printed next to the filename
132
		env table=analytical_stem_$version bin/publish_analytical_db \
133
			backups/analytical_stem.$version.csv
134
	If desired, record the import times in inputs/import.stats.xls:
135
		Open inputs/import.stats.xls
136
		If the rightmost import is within 5 columns of column IV:
137
			Copy the current tab to <leftmost-date>~<rightmost-date>
138
			Remove the previous imports from the current tab because they are
139
				now in the copied tab instead
140
		Insert a copy of the leftmost "By column" column group before it
141
		export version=<version>
142
		bin/import_date inputs/{.,}*/*/logs/$version.log.sql
143
		Update the import date in the upper-right corner
144
		bin/import_times inputs/{.,}*/*/logs/$version.log.sql
145
		Paste the output over the # Rows/Time columns, making sure that the
146
			row counts match up with the previous import's row counts
147
		If the row counts do not match up, insert or reorder rows as needed
148
			until they do. Get the datasource names from the log file footers:
149
			tail inputs/{.,}*/*/logs/$version.log.sql
150
		Commit: svn ci -m "inputs/import.stats.xls: Updated import times"
151
	To run TNRS: make scrub by_col=1 &
152
		export version=<version>
153
		To view progress:
154
			tail -100 inputs/.TNRS/tnrs/logs/tnrs.make.log.sql
155
	To remake analytical DB: bin/make_analytical_db &
156
		export version=<version>
157
		To view progress:
158
			tail -100 inputs/analytical_db/logs/make_analytical_db.log.sql
159
	To back up DB (staging tables and last import):
160
		export version=<version>
161
		If before renaming to public: export dump_opts=--exclude-schema=public
162
		make backups/vegbien.$version.backup/test &
163

    
164
Backups:
165
	Archived imports:
166
		Back up: make backups/<version>.backup &
167
			Note: To back up the last import, you must archive it first:
168
				make schemas/rotate
169
		Test: make -s backups/<version>.backup/test &
170
		Restore: make backups/<version>.backup/restore &
171
		Remove: make backups/<version>.backup/remove
172
		Download: make backups/download
173
	TNRS cache:
174
		Back up: make backups/TNRS.backup-remake &
175
		Restore:
176
			yes|make inputs/.TNRS/uninstall
177
			make backups/TNRS.backup/restore &
178
			yes|make schemas/public/reinstall
179
				Must come after TNRS restore to recreate tnrs_input_name view
180
	Full DB:
181
		Back up: make backups/vegbien.<version>.backup &
182
		Test: make -s backups/vegbien.<version>.backup/test &
183
		Restore: make backups/vegbien.<version>.backup/restore &
184
		Download: make backups/download
185
	Import logs:
186
		Download: make inputs/download-logs
187

    
188
Datasource setup:
189
	umask ug=rwx,o= # prevent files from becoming web-accessible
190
	Add a new datasource: make inputs/<datasrc>/add
191
		<datasrc> may not contain spaces, and should be abbreviated.
192
		If the datasource is a herbarium, <datasrc> should be the herbarium code
193
			as defined by the Index Herbariorum <http://sweetgum.nybg.org/ih/>
194
	For MySQL inputs (exports and live DB connections):
195
		For .sql exports:
196
			Place the original .sql file in _src/ (*not* in _MySQL/)
197
			Follow the steps starting with Install the staging tables below.
198
				This is for an initial sync to get the file onto vegbiendev.
199
			On vegbiendev:
200
				Create a database for the MySQL export in phpMyAdmin
201
				bin/mysql_bien database <inputs/<datasrc>/_src/export.sql &
202
		mkdir inputs/<datasrc>/_MySQL/
203
		cp -p lib/MySQL.{data,schema}.sql.make inputs/<datasrc>/_MySQL/
204
		Edit _MySQL/*.make for the DB connection
205
			For a .sql export, use server=vegbiendev and --user=bien
206
		Skip the Add input data for each table section
207
	For MS Access databases:
208
		Place the .mdb or .accdb file in _src/
209
		Download and install Access To PostgreSQL from
210
			http://www.bullzip.com/download.php
211
		Use Access To PostgreSQL to export the database:
212
			Export just the tables/indexes to inputs/<datasrc>/<file>.schema.sql
213
			Export just the data to inputs/<datasrc>/<file>.data.sql
214
		In <file>.schema.sql, make the following changes:
215
			Replace text "BOOLEAN" with "/*BOOLEAN*/INTEGER"
216
			Replace text "DOUBLE PRECISION NULL" with "DOUBLE PRECISION"
217
		Skip the Add input data for each table section
218
	Add input data for each table present in the datasource:
219
		For .sql exports, you must use the name of the table in the DB export
220
		For CSV files, you can use any name. It's recommended to use a table
221
			name from <https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/VegCSV#Suggested-table-names>
222
		Note that if this table will be joined together with another table, its
223
			name must end in ".src"
224
		make inputs/<datasrc>/<table>/add
225
			Important: DO NOT just create an empty directory named <table>!
226
				This command also creates necessary subdirs, such as logs/.
227
		If the table is in a .sql export: make inputs/<datasrc>/<table>/install
228
			Otherwise, place the CSV(s) for the table in
229
			inputs/<datasrc>/<table>/ OR place a query joining other tables
230
			together in inputs/<datasrc>/<table>/create.sql
231
		Important: When exporting relational databases to CSVs, you MUST ensure
232
			that embedded quotes are escaped by doubling them, *not* by
233
			preceding them with a "\" as is the default in phpMyAdmin
234
		If there are multiple part files for a table, and the header is repeated
235
			in each part, make sure each header is EXACTLY the same.
236
			(If the headers are not the same, the CSV concatenation script
237
			assumes the part files don't have individual headers and treats the
238
			subsequent headers as data rows.)
239
		Add <table> to inputs/<datasrc>/import_order.txt before other tables
240
			that depend on it
241
	Install the staging tables:
242
		make inputs/<datasrc>/reinstall quiet=1 &
243
		For a MySQL .sql export:
244
			At prompt "[you]@vegbiendev's password:", enter your password
245
			At prompt "Enter password:", enter the value in config/bien_password
246
		To view progress: tail -f inputs/<datasrc>/<table>/logs/install.log.sql
247
		View the logs: tail -n +1 inputs/<datasrc>/*/logs/install.log.sql
248
			tail provides a header line with the filename
249
			+1 starts at the first line, to show the whole file
250
		For every file with an error 'column "..." specified more than once':
251
			Add a header override file "+header.<ext>" in <table>/:
252
				Note: The leading "+" should sort it before the flat files.
253
					"_" unfortunately sorts *after* capital letters in ASCII.
254
				Create a text file containing the header line of the flat files
255
				Add an ! at the beginning of the line
256
					This signals cat_csv that this is a header override.
257
				For empty names, use their 0-based column # (by convention)
258
				For duplicate names, add a distinguishing suffix
259
				For long names that collided, rename them to <= 63 chars long
260
				Do NOT make readability changes in this step; that is what the
261
					map spreadsheets (below) are for.
262
				Save
263
		If you made any changes, re-run the install command above
264
	Auto-create the map spreadsheets: make inputs/<datasrc>/
265
	Map each table's columns:
266
		In each <table>/ subdir, for each "via map" map.csv:
267
			Open the map in a spreadsheet editor
268
			Open the "core map" /mappings/Veg+-VegBIEN.csv
269
			In each row of the via map, set the right column to a value from the
270
				left column of the core map
271
			Save
272
		Regenerate the derived maps: make inputs/<datasrc>/
273
	Accept the test cases:
274
		make inputs/<datasrc>/test
275
			When prompted to "Accept new test output", enter y and press ENTER
276
			If you instead get errors, do one of the following for each one:
277
			-	If the error was due to a bug, fix it
278
			-	Add a SQL function that filters or transforms the invalid data
279
			-	Make an empty mapping for the columns that produced the error.
280
				Put something in the Comments column of the map spreadsheet to
281
				prevent the automatic mapper from auto-removing the mapping.
282
			When accepting tests, it's helpful to use WinMerge
283
				(see WinMerge setup below for configuration)
284
		make inputs/<datasrc>/test by_col=1
285
			If you get errors this time, this always indicates a bug, usually in
286
				the VegBIEN unique constraints or column-based import itself
287
	Add newly-created files: make inputs/<datasrc>/add
288
	Commit: svn ci -m "Added inputs/<datasrc>/" inputs/<datasrc>/
289
	Update vegbiendev:
290
		On jupiter: svn up
291
		On local machine:
292
			./fix_perms
293
			make inputs/upload
294
		On vegbiendev:
295
			svn up
296
			make inputs/download
297
			Follow the steps under Install the staging tables above
298

    
299
Datasource refreshing:
300
	VegBank:
301
		make inputs/VegBank/vegbank.sql-remake
302
		make inputs/VegBank/reinstall quiet=1 &
303

    
304
Schema changes:
305
	When changing the analytical views, run sync_analytical_..._to_view()
306
		to update the corresponding table
307
	Remember to update the following files with any renamings:
308
		schemas/filter_ERD.csv
309
		mappings/VegCore-VegBIEN.csv
310
		mappings/verify.*.sql
311
	Regenerate schema from installed DB: make schemas/remake
312
	Reinstall DB from schema: make schemas/public/reinstall schemas/reinstall
313
		WARNING: This will delete the current public schema of your VegBIEN DB!
314
	Reinstall staging tables:
315
		On local machine:
316
			sudo -E -u postgres psql <<<'ALTER DATABASE vegbien RENAME TO vegbien_prev'
317
			make db
318
			. bin/reinstall_all
319
			Fix any bugs and retry until no errors
320
			make schemas/public/install
321
				This must be run *after* the datasources are installed, because
322
				views in public depend on some of the datasources
323
			sudo -E -u postgres psql <<<'DROP DATABASE vegbien_prev'
324
		On vegbiendev: repeat the above steps
325
			WARNING: Do not run this until reinstall_all runs successfully on
326
			the local machine, or the live DB may be unrestorable!
327
	Sync ERD with vegbien.sql schema:
328
		Run make schemas/vegbien.my.sql
329
		Open schemas/vegbien.ERD.mwb in MySQLWorkbench
330
		Go to File > Export > Synchronize With SQL CREATE Script...
331
		For Input File, select schemas/vegbien.my.sql
332
		Click Continue
333
		In the changes list, select each table with an arrow next to it
334
		Click Update Model
335
		Click Continue
336
		Note: The generated SQL script will be empty because we are syncing in
337
			the opposite direction
338
		Click Execute
339
		Reposition any lines that have been reset
340
		Add any new tables by dragging them from the Catalog in the left sidebar
341
			to the diagram
342
		Remove any deleted tables by right-clicking the table's diagram element,
343
			selecting Delete '<table name>', and clicking Delete
344
		Save
345
		If desired, update the graphical ERD exports (see below)
346
	Update graphical ERD exports:
347
		Go to File > Export > Export as PNG...
348
		Select schemas/vegbien.ERD.png and click Save
349
		Go to File > Export > Export as SVG...
350
		Select schemas/vegbien.ERD.svg and click Save
351
		Go to File > Export > Export as Single Page PDF...
352
		Select schemas/vegbien.ERD.1_pg.pdf and click Save
353
		Go to File > Print...
354
		In the lower left corner, click PDF > Save as PDF...
355
		Set the Title and Author to ""
356
		Select schemas/vegbien.ERD.pdf and click Save
357
		Commit: svn ci -m "schemas/vegbien.ERD.mwb: Regenerated exports"
358
	Refactoring tips:
359
		To rename a table:
360
			In vegbien.sql, do the following:
361
				Replace regexp (?<=_|\b)<old>(?=_|\b) with <new>
362
					This is necessary because the table name is *everywhere*
363
				Search for <new>
364
				Manually change back any replacements inside comments
365
		To rename a column:
366
			Rename the column: ALTER TABLE <table> RENAME <old> TO <new>;
367
			Recreate any foreign key for the column, removing CONSTRAINT <name>
368
				This resets the foreign key name using the new column name
369
	Creating a poster of the ERD:
370
		Determine the poster size:
371
			Measure the line height (from the bottom of one line to the bottom
372
				of another): 16.3cm/24 lines = 0.679cm
373
			Measure the height of the ERD: 35.4cm*2 = 70.8cm
374
			Zoom in as far as possible
375
			Measure the height of a capital letter: 3.5mm
376
			Measure the line height: 8.5mm
377
			Calculate the text's fraction of the line height: 3.5mm/8.5mm = 0.41
378
			Calculate the text height: 0.679cm*0.41 = 0.28cm
379
			Calculate the text height's fraction of the ERD height:
380
				0.28cm/70.8cm = 0.0040
381
			Measure the text height on the *VegBank* ERD poster: 5.5mm = 0.55cm
382
			Calculate the VegBIEN poster height to make the text the same size:
383
				0.55cm/0.0040 = 137.5cm H; *1in/2.54cm = 54.1in H
384
			The ERD aspect ratio is 11 in W x (2*8.5in H) = 11x17 portrait
385
			Calculate the VegBIEN poster width: 54.1in H*11W/17H = 35.0in W
386
			The minimum VegBIEN poster size is 35x54in portrait
387
		Determine the cost:
388
			The FedEx Kinkos near NCEAS (1030 State St, Santa Barbara, CA 93101)
389
				charges the following for posters:
390
				base: $7.25/sq ft
391
				lamination: $3/sq ft
392
				mounting on a board: $8/sq ft
393

    
394
Testing:
395
	On a development machine, you should put the following in your .profile:
396
		umask ug=rwx,o= # prevent files from becoming web-accessible
397
		export log= n=2
398
	Mapping process: make test
399
		Including column-based import: make test by_col=1
400
			If the row-based and column-based imports produce different inserted
401
			row counts, this usually means that a table is underconstrained
402
			(the unique indexes don't cover all possible rows).
403
			This can occur if you didn't use COALESCE(field, null_value) around
404
			a nullable field in a unique index. See sql_gen.null_sentinels for
405
			the appropriate null value to use.
406
	Map spreadsheet generation: make remake
407
	Missing mappings: make missing_mappings
408
	Everything (for most complete coverage): make test-all
409

    
410
Debugging:
411
	"Binary chop" debugging:
412
		(This is primarily useful for regressions that occurred in a previous
413
		revision, which was committed without running all the tests)
414
		svn up -r <rev>; make inputs/.TNRS/reinstall; make schemas/public/reinstall; make <failed-test>.xml
415
	.htaccess:
416
		mod_rewrite:
417
			IMPORTANT: whenever you change the DirectorySlash setting for a
418
				directory, you *must* clear your browser's cache to ensure that
419
				a cached redirect is not used. this is because RewriteRule
420
				redirects are (by default) temporary, but DirectorySlash
421
				redirects are permanent.
422
				for Firefox:
423
					press Cmd+Shift+Delete
424
					check only Cache
425
					press Enter or click Clear Now
426

    
427
WinMerge setup:
428
	Install WinMerge from <http://winmerge.org/>
429
	Open WinMerge
430
	Go to Edit > Options and click Compare in the left sidebar
431
	Enable "Moved block detection", as described at
432
		<http://manual.winmerge.org/Configuration.html#d0e5892>.
433
	Set Whitespace to Ignore change, as described at
434
		<http://manual.winmerge.org/Configuration.html#d0e5758>.
435

    
436
Documentation:
437
	To generate a Redmine-formatted list of steps for column-based import:
438
		make schemas/public/reinstall
439
		make inputs/ACAD/Specimen/logs/steps.by_col.log.sql
440
	To import and scrub just the test taxonomic names:
441
		inputs/test_taxonomic_names/test_scrub
442

    
443
General:
444
	To see a program's description, read its top-of-file comment
445
	To see a program's usage, run it without arguments
446
	To remake a directory: make <dir>/remake
447
	To remake a file: make <file>-remake
(3-3/8)