Project

General

Profile

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

    
11
Connecting to vegbiendev:
12
	ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
13
	cd /home/bien # should happen automatically at login
14

    
15
Single datasource refresh:
16
	ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
17
	# -> Maintenance > to back up the vegbiendev databases
18
	# place updated extract in inputs/ACAD/_src/
19
	# place extracted flat file(s) in the appropriate table subdirs 
20
	rm=1 inputs/<datasrc>/run # reload staging tables
21
	make inputs/<datasrc>/reimport_scrub by_col=1 &
22
		# this works whether or not datasource is already imported
23
	tail -150 inputs/<datasrc>/*/logs/public.log.sql # view progress
24
	# -> Full database import > To re-run geoscrubbing
25
	# -> Full database import > To remake analytical DB
26
	# -> Maintenance > to back up the vegbiendev databases
27

    
28
Notes on system stability:
29
	**WARNING**: when shutting down the VM, always first stop Postgres:
30
		sudo service postgresql stop
31
		this prevents the OS from SIGKILLing Postgres, which sometimes causes
32
		database corruption 
33

    
34
Notes on running programs:
35
	**WARNING**: always start with a clean shell, to avoid spurious bugs. the
36
		shell should not have changes to the env vars. (there have been bugs
37
		that went away after closing and reopening the terminal window.) note
38
		that running `exec bash` is not sufficient to *reset* the env vars.
39

    
40
Notes on editing files:
41
	**WARNING**: shell scripts should always be read-only, so that editing them
42
		while an import is in progress will not crash the import (see
43
		http://vegpath.org/links/#**%20modifying%20a%20running%20shell%20script)
44

    
45
Full database import:
46
	**WARNING**: You must perform *every single* step listed below, to avoid
47
		breaking column-based import
48
	**WARNING**: always start with a clean shell, as described above under
49
		"Notes on running programs"
50
	**IMPORTANT**: the beginning of the import should be scheduled at a time
51
		when the DB will not be needed for other uses. this is necessary because
52
		vegbiendev will be slow for the first few hours of the import, due to
53
		the import using all the available cores.
54
	do steps under Maintenance > "to synchronize vegbiendev, jupiter, and
55
		your local machine"
56
	On local machine:
57
		make inputs/upload
58
		make inputs/upload live=1
59
		make test by_col=1 # runtime: 1 h ("53m7.383s") @starscream
60
			if you encounter errors, they are most likely related to the
61
				PostgreSQL error parsing in /lib/sql.py parse_exception()
62
			See note under Testing below
63
	ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
64
	Ensure there are no local modifications: svn st
65
	up
66
	make inputs/download
67
	make inputs/download live=1
68
	For each newly-uploaded datasource above: make inputs/<datasrc>/reinstall
69
	Update the auxiliary schemas: make schemas/reinstall
70
		**WARNING**: requires sudo access!
71
		The public schema will be installed separately by the import process
72
	Delete imports before the last so they won't bloat the full DB backup:
73
		make backups/vegbien.<version>.backup/remove
74
		To keep a previous import other than the public schema:
75
			export dump_opts='--exclude-schema=public --exclude-schema=<version>'
76
			# env var will be inherited by `screen` shell
77
	restart Postgres to free up any disk space used by temp tables from the last
78
		import (this is apparently not automatically reclaimed):
79
		make postgres_restart
80
	Make sure there is at least 1 TB of disk space on /: df -h
81
		although the import schema itself is only 315 GB, Postgres uses
82
			significant temporary space at the beginning of the import.
83
			the total disk usage oscillates between 1.2 TB and the entire disk
84
			for the first day (for import started @12:55:09, high-water marks of
85
			1.7 TB @14:00:25, 1.8 TB @15:38:32; then next day w/ 2 datasources
86
			running: entire disk for 4 min @05:35:44, 1.8 TB @11:15:05).
87
		To free up space, remove backups that have been archived on jupiter:
88
			List backups/ to view older backups
89
			Check their MD5 sums using the steps under On jupiter below
90
			Remove these backups
91
	for full import:
92
		screen
93
		Press ENTER
94
	$0 # nested shell to prevent errexit from closing the window
95
	the following must happen within screen to avoid affecting the outer shell:
96
	unset TMOUT # TMOUT causes shell to exit even with background processes
97
	set -o ignoreeof # prevent Ctrl+D from exiting shell to keep attached jobs
98
	on local machine:
99
		unset n # clear any limit set in .profile (unless desired)
100
		unset log # allow logging output to go to log files
101
	unset version # clear any version from last import, etc.
102
	if no commits have been made since the last import (eg. if retrying an
103
		import), set a custom version that differs from the auto-assigned one
104
		(would otherwise cause a collision with the last import):
105
		svn info
106
		extract the svn revision after "Revision:"
107
		export version=r[revision]_2 # +suffix to distinguish from last import
108
			# env var will be inherited by `screen` shell
109
	to import just a subset of the datasources:
110
		declare -ax inputs; inputs=(inputs/{src,...}/) # no () in declare on Mac
111
			# array vars *not* inherited by `screen` shell
112
		export version=custom_import_name
113
	Start column-based import: . bin/import_all
114
		To use row-based import: . bin/import_all by_col=
115
		To stop all running imports: . bin/stop_imports
116
		**WARNING**: Do NOT run import_all in the background, or the jobs it
117
			creates won't be owned by your shell.
118
		Note that import_all will take up to an hour to import the NCBI backbone
119
			and other metadata before returning control to the shell.
120
		To view progress:
121
			tail inputs/{.,}??*/*/logs/$version.log.sql
122
	note: at the beginning of the import, the system may send out CPU load
123
		warning e-mails. these can safely be ignored. (they happen because the
124
		parallel imports use all the available cores.)
125
	for test import, turn off DB backup (also turns off analytical DB creation):
126
		kill % # cancel after_import()
127
	Wait (4 days) for the import to finish
128
	**WARNING**: do *not* run backups/pg_snapshot while the import is running,
129
		due to continuously-changing files
130
	**WARNING**: do *not* run backups/pg_snapshot until the previous import has
131
		been replaced, to avoid running into disk space limits
132
	To recover from a closed terminal window: screen -r
133
	To restart an aborted import for a specific table:
134
		export version=<version>
135
		(set -o errexit; make inputs/<datasrc>/<table>/import_scrub by_col=1 continue=1; make inputs/<datasrc>/publish) &
136
		bin/after_import $! & # $! can also be obtained from `jobs -l`
137
	Get $version: echo $version
138
	Set $version in all vegbiendev terminals: export version=<version>
139
	When there are no more running jobs, exit `screen`: exit # not Ctrl+D
140
	upload logs: make inputs/upload live=1
141
	On local machine: make inputs/download-logs live=1
142
	check for disk space errors:
143
		grep --files-with-matches -F 'No space left on device' inputs/{.,}??*/*/logs/$version.log.sql
144
		if there are any matches:
145
			manually reimport these datasources using the steps under
146
				Single datasource import
147
			bin/after_import &
148
			wait for the import to finish
149
	tail inputs/{.,}??*/*/logs/$version.log.sql
150
	In the output, search for "Command exited with non-zero status"
151
	For inputs that have this, fix the associated bug(s)
152
	If many inputs have errors, discard the current (partial) import:
153
		make schemas/$version/uninstall
154
	Otherwise, continue
155
	In PostgreSQL:
156
		Go to wiki.vegpath.org/VegBIEN_contents
157
		Get the # observations
158
		Get the # datasources
159
		Get the # datasources with observations
160
		in the r# schema:
161
		Check that analytical_stem contains [# observations] rows
162
		Check that source contains [# datasources] rows up through XAL. If this
163
			is not the case, manually check the entries in source against the
164
			datasources list on the wiki page (some datasources may be near the
165
			end depending on import order).
166
		Check that provider_count contains [# datasources with observations]
167
			rows with dataset="(total)" (at the top when the table is unsorted)
168
	Check that TNRS ran successfully:
169
		tail -100 inputs/.TNRS/tnrs/logs/tnrs.make.log.sql
170
		If the log ends in an AssertionError
171
			"assert sql.table_col_names(db, table) == header":
172
			Figure out which TNRS CSV columns have changed
173
			On local machine:
174
				Make the changes in the DB's TNRS and public schemas
175
				rm=1 inputs/.TNRS/schema.sql.run export_
176
				make schemas/remake
177
				inputs/test_taxonomic_names/test_scrub # re-run TNRS
178
				rm=1 inputs/.TNRS/data.sql.run export_
179
				Commit
180
			ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
181
				If dropping a column, save the dependent views
182
				Make the same changes in the live TNRS.tnrs table on vegbiendev
183
				If dropping a column, recreate the dependent views
184
				Restart the TNRS client: make scrub by_col=1 &
185
	Publish the new import:
186
		**WARNING**: Before proceeding, be sure you have done *every single*
187
			verification step listed above. Otherwise, a previous valid import
188
			could incorrectly be overwritten with a broken one.
189
		make schemas/$version/publish # runtime: 1 min ("real 1m10.451s")
190
	unset version
191
	make backups/upload live=1
192
	on local machine:
193
		make backups/vegbien.$version.backup/download live=1
194
			# download backup to local machine
195
	ssh aaronmk@jupiter.nceas.ucsb.edu
196
		cd /data/dev/aaronmk/bien/backups
197
		For each newly-archived backup:
198
			make -s <backup>.md5/test
199
			Check that "OK" is printed next to the filename
200
	If desired, record the import times in inputs/import.stats.xls:
201
		On local machine:
202
		Open inputs/import.stats.xls
203
		If the rightmost import is within 5 columns of column IV:
204
			Copy the current tab to <leftmost-date>~<rightmost-date>
205
			Remove the previous imports from the current tab because they are
206
				now in the copied tab instead
207
		Insert a copy of the leftmost "By column" column group before it
208
		export version=<version>
209
		bin/import_date inputs/{.,}??*/*/logs/$version.log.sql
210
		Update the import date in the upper-right corner
211
		bin/import_times inputs/{.,}??*/*/logs/$version.log.sql
212
		Paste the output over the # Rows/Time columns, making sure that the
213
			row counts match up with the previous import's row counts
214
		If the row counts do not match up, insert or reorder rows as needed
215
			until they do. Get the datasource names from the log file footers:
216
			tail inputs/{.,}??*/*/logs/$version.log.sql
217
		Commit: svn ci -m 'inputs/import.stats.xls: updated import times'
218
	Running individual steps separately:
219
	To run TNRS:
220
		To use an import other than public: export version=<version>
221
		to rescrub all names:
222
			make inputs/.TNRS/reinstall
223
			re-create public-schema views that were cascadingly deleted
224
		make scrub &
225
		To view progress:
226
			tail -100 inputs/.TNRS/tnrs/logs/tnrs.make.log.sql
227
	To re-run geoscrubbing:
228
		$ screen
229
		# press Enter
230
		# to use an import other than public: $ export version=<version>
231
		$ bin/psql_verbose_vegbien <<<'SELECT geoscrub_input_view_modify();' &
232
			# runtime: 8 min ("7:40.54") @r14089 @vegbiendev
233
		# wait until done
234
		$ rm=1 exports/geoscrub_input.csv.run
235
			# runtime: 25 s ("0m24.936s") @r14089 @vegbiendev
236
		$ rm=1 inputs/.geoscrub/geoscrub_output/geoscrub.csv.run &
237
			# runtime: 2.5 h
238
		# wait until done
239
		$ rm=1 inputs/.geoscrub/run &
240
			# runtime: 15 min ("16m34.052s") @r14089 @vegbiendev
241
		# wait until done
242
		# re-create public-schema views that were cascadingly deleted
243
		# press Ctrl+D
244
		# remake the analytical DB (below)
245
	To remake analytical DB:
246
		To use an import other than public: export version=<version>
247
		bin/make_analytical_db & # runtime: 13 h ("12:43:57elapsed")
248
		To view progress:
249
			tail -150 inputs/analytical_db/logs/make_analytical_db.log.sql
250
	To back up DB (staging tables and last import):
251
		To use an import *other than public*: export version=<version>
252
		make backups/TNRS.backup-remake &
253
		dump_opts=--exclude-schema=public make backups/vegbien.$version.backup/test &
254
			If after renaming to public, instead set dump_opts='' and replace
255
			$version with the appropriate revision
256
		make backups/upload live=1
257

    
258
Datasource setup:
259
	On local machine:
260
	Example steps for a datasource: wiki.vegpath.org/Import_process_for_Madidi
261
	umask ug=rwx,o= # prevent files from becoming web-accessible
262
	Add a new datasource: make inputs/<datasrc>/add
263
		<datasrc> may not contain spaces, and should be abbreviated.
264
		If the datasource is a herbarium, <datasrc> should be the herbarium code
265
			as defined by the Index Herbariorum <http://sweetgum.nybg.org/ih/>
266
	For a new-style datasource (one containing a ./run runscript):
267
		"cp" -f inputs/.NCBI/{Makefile,run,table.run} inputs/<datasrc>/
268
	For MySQL inputs (exports and live DB connections):
269
		For .sql exports:
270
			Place the original .sql file in _src/ (*not* in _MySQL/)
271
			Follow the steps starting with Install the staging tables below.
272
				This is for an initial sync to get the file onto vegbiendev.
273
			ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
274
				Create a database for the MySQL export in phpMyAdmin
275
				Give the bien user all database-specific privileges *except*
276
					UPDATE, DELETE, ALTER, DROP. This prevents bugs in the
277
					import scripts from accidentally deleting data.
278
				bin/mysql_bien database <inputs/<datasrc>/_src/export.sql &
279
		mkdir inputs/<datasrc>/_MySQL/
280
		cp -p lib/MySQL.{data,schema}.sql.make inputs/<datasrc>/_MySQL/
281
		Edit _MySQL/*.make for the DB connection
282
			For a .sql export, use server=vegbiendev and --user=bien
283
		Skip the Add input data for each table section
284
	For MS Access databases:
285
		Place the .mdb or .accdb file in _src/
286
		Download and install Bullzip's MS Access to PostgreSQL from
287
			http://bullzip.com/download.php > Access To PostgreSQL > Download
288
		Use Access To PostgreSQL to export the database:
289
			Export just the tables/indexes to inputs/<datasrc>/<file>.schema.sql
290
				using the settings in the associated .ini file where available
291
			Export just the data to inputs/<datasrc>/<file>.data.sql using the
292
				settings in the associated .ini file where available
293
		In <file>.schema.sql, make the following changes:
294
			Replace text "BOOLEAN" with "/*BOOLEAN*/INTEGER"
295
			Replace text "DOUBLE PRECISION NULL" with "DOUBLE PRECISION"
296
		Skip the Add input data for each table section
297
	Add input data for each table present in the datasource:
298
		For .sql exports, you must use the name of the table in the DB export
299
		For CSV files, you can use any name. It's recommended to use a table
300
			name from <https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/VegCSV#Suggested-table-names>
301
		Note that if this table will be joined together with another table, its
302
			name must end in ".src"
303
		make inputs/<datasrc>/<table>/add
304
			Important: DO NOT just create an empty directory named <table>!
305
				This command also creates necessary subdirs, such as logs/.
306
		If the table is in a .sql export: make inputs/<datasrc>/<table>/install
307
			Otherwise, place the CSV(s) for the table in
308
			inputs/<datasrc>/<table>/ OR place a query joining other tables
309
			together in inputs/<datasrc>/<table>/create.sql
310
		Important: When exporting relational databases to CSVs, you MUST ensure
311
			that embedded quotes are escaped by doubling them, *not* by
312
			preceding them with a "\" as is the default in phpMyAdmin
313
		If there are multiple part files for a table, and the header is repeated
314
			in each part, make sure each header is EXACTLY the same.
315
			(If the headers are not the same, the CSV concatenation script
316
			assumes the part files don't have individual headers and treats the
317
			subsequent headers as data rows.)
318
		Add <table> to inputs/<datasrc>/import_order.txt before other tables
319
			that depend on it
320
		For a new-style datasource:
321
			"cp" -f inputs/.NCBI/nodes/run inputs/<datasrc>/<table>/
322
			inputs/<datasrc>/<table>/run
323
	Install the staging tables:
324
		make inputs/<datasrc>/reinstall quiet=1 &
325
		For a MySQL .sql export:
326
			At prompt "[you]@vegbiendev's password:", enter your password
327
			At prompt "Enter password:", enter the value in config/bien_password
328
		To view progress: tail -f inputs/<datasrc>/<table>/logs/install.log.sql
329
		View the logs: tail -n +1 inputs/<datasrc>/*/logs/install.log.sql
330
			tail provides a header line with the filename
331
			+1 starts at the first line, to show the whole file
332
		For every file with an error 'column "..." specified more than once':
333
			Add a header override file "+header.<ext>" in <table>/:
334
				Note: The leading "+" should sort it before the flat files.
335
					"_" unfortunately sorts *after* capital letters in ASCII.
336
				Create a text file containing the header line of the flat files
337
				Add an ! at the beginning of the line
338
					This signals cat_csv that this is a header override.
339
				For empty names, use their 0-based column # (by convention)
340
				For duplicate names, add a distinguishing suffix
341
				For long names that collided, rename them to <= 63 chars long
342
				Do NOT make readability changes in this step; that is what the
343
					map spreadsheets (below) are for.
344
				Save
345
		If you made any changes, re-run the install command above
346
	Auto-create the map spreadsheets: make inputs/<datasrc>/
347
	Map each table's columns:
348
		In each <table>/ subdir, for each "via map" map.csv:
349
			Open the map in a spreadsheet editor
350
			Open the "core map" /mappings/Veg+-VegBIEN.csv
351
			In each row of the via map, set the right column to a value from the
352
				left column of the core map
353
			Save
354
		Regenerate the derived maps: make inputs/<datasrc>/
355
	Accept the test cases:
356
		For a new-style datasource:
357
			inputs/<datasrc>/run
358
			svn di inputs/<datasrc>/*/test.xml.ref
359
			If you get errors, follow the steps for old-style datasources below
360
		For an old-style datasource:
361
			make inputs/<datasrc>/test
362
			When prompted to "Accept new test output", enter y and press ENTER
363
			If you instead get errors, do one of the following for each one:
364
			-	If the error was due to a bug, fix it
365
			-	Add a SQL function that filters or transforms the invalid data
366
			-	Make an empty mapping for the columns that produced the error.
367
				Put something in the Comments column of the map spreadsheet to
368
				prevent the automatic mapper from auto-removing the mapping.
369
			When accepting tests, it's helpful to use WinMerge
370
				(see WinMerge setup below for configuration)
371
		make inputs/<datasrc>/test by_col=1
372
			If you get errors this time, this always indicates a bug, usually in
373
				the VegBIEN unique constraints or column-based import itself
374
	Add newly-created files: make inputs/<datasrc>/add
375
	Commit: svn ci -m "Added inputs/<datasrc>/" inputs/<datasrc>/
376
	Update vegbiendev:
377
		ssh aaronmk@jupiter.nceas.ucsb.edu
378
			up
379
		On local machine:
380
			./fix_perms
381
			make inputs/upload
382
			make inputs/upload live=1
383
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
384
			up
385
			make inputs/download
386
			make inputs/download live=1
387
			Follow the steps under Install the staging tables above
388

    
389
Maintenance:
390
	on a live machine, you should put the following in your .profile:
391
--
392
# make svn files web-accessible. this does not affect unversioned files, because
393
# these get the right permissions on the local machine instead.
394
umask ug=rwx,o=rx
395

    
396
unset TMOUT # TMOUT causes screen to exit even with background processes
397
--
398
	if http://vegbiendev.nceas.ucsb.edu/phppgadmin/ goes down:
399
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
400
			make phppgadmin-Linux
401
	regularly, re-run full-database import so that bugs in it don't pile up.
402
		it needs to be kept in working order so that it works when it's needed.
403
	to back up the vegbiendev databases:
404
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
405
		back up MySQL: # usually few changes, so do this first
406
			backups/mysql_snapshot
407
			l=1        overwrite=1 inplace=1 local_dir=/ remote_url="$USER@jupiter:/data/dev/aaronmk/Documents/BIEN/" subpath=/var/lib/mysql.bak/ sudo -E env PATH="$PATH" bin/sync_upload
408
			on local machine:
409
			l=1 swap=1 overwrite=1 inplace=1 local_dir=~ sync_remote_subdir=                          subpath=~/Documents/BIEN/var/lib/mysql.bak/                          bin/sync_upload
410
		back up Postgres:
411
			backups/pg_snapshot
412
	to synchronize vegbiendev, jupiter, and your local machine:
413
		**WARNING**: pay careful attention to all files that will be deleted or
414
			overwritten!
415
		install put if needed:
416
			download https://uutils.googlecode.com/svn/trunk/bin/put to ~/bin/ and `chmod +x` it
417
		when changes are made on vegbiendev:
418
			avoid extraneous diffs when rsyncing:
419
				on local machine:
420
					up; ./fix_perms
421
				ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
422
					up; ./fix_perms
423
				ssh aaronmk@jupiter.nceas.ucsb.edu
424
					up; ./fix_perms
425
			ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
426
				upload:
427
				overwrite=1 bin/sync_upload --size-only
428
					then review diff, and rerun with `l=1` prepended
429
			on your machine:
430
				download:
431
				overwrite=1 swap=1 src=. dest='aaronmk@jupiter.nceas.ucsb.edu:~/bien' put --exclude=.svn web/BIEN3/TWiki
432
					then review diff, and rerun with `l=1` prepended
433
				swap=1 bin/sync_upload backups/TNRS.backup
434
					then review diff, and rerun with `l=1` prepended
435
				overwrite=1 swap=1 bin/sync_upload --size-only
436
					then review diff, and rerun with `l=1` prepended
437
				overwrite=1 sync_remote_url=~/Dropbox/svn/ bin/sync_upload --existing --size-only # just update mtimes/perms
438
					then review diff, and rerun with `l=1` prepended
439
	to back up e-mails:
440
		on local machine:
441
		/Applications/gmvault-v1.8.1-beta/bin/gmvault sync --multiple-db-owner --type quick aaronmk.nceas@gmail.com
442
		open Thunderbird
443
		click the All Mail folder for each account and wait for it to download the e-mails in it
444
	to back up the version history:
445
		# back up first on the local machine, because often only the svnsync
446
			command gets run, and that way it will get backed up immediately to
447
			Dropbox (and hourly to Time Machine), while vegbiendev only gets
448
			backed up daily to tape
449
		on local machine:
450
		svnsync sync file://"$HOME"/Dropbox/docs/BIEN/svn_repo/ # initial runtime: 1.5 h ("08:21:38" - "06:45:26") @vegbiendev
451
		(cd ~/Dropbox/docs/BIEN/git/; git svn fetch)
452
		# use absolute path for vegbiendev commands because the Ubuntu 14.04
453
			version of rsync doesn't expand ~ properly
454
		overwrite=1        src=~ dest='aaronmk@jupiter.nceas.ucsb.edu:/data/dev/aaronmk/' put Dropbox/docs/BIEN/svn_repo/ # runtime: 1 min ("1:05.08")
455
			then review diff, and rerun with `l=1` prepended
456
		overwrite=1        src=~ dest='aaronmk@jupiter.nceas.ucsb.edu:/data/dev/aaronmk/' put Dropbox/docs/BIEN/git/
457
			then review diff, and rerun with `l=1` prepended
458
	to back up vegbiendev:
459
		do steps under Maintenance > "to synchronize vegbiendev, jupiter, and
460
			your local machine"
461
		on local machine:
462
			l=1 overwrite=1 inplace=1 src=root@vegbiendev.nceas.ucsb.edu:/ dest=~/Documents/BIEN/vegbiendev/ sudo -E put --exclude=/var/lib/mysql.bak --exclude=/var/lib/postgresql.bak --exclude='/var/lib/postgresql/9.3/main/*/' --exclude=/home/aaronmk/bien
463
			# enable --link-dest to work:
464
				chmod -R o+r ~/bien/.svn/; find ~/bien/.svn -type d -exec chmod o+rx {} \; # match perms
465
				l=1 overwrite=1 del= src='aaronmk@vegbiendev.nceas.ucsb.edu:~/bien/' dest=~/bien/ put --existing --size-only .svn/pristine/ # match times and perms
466
			l=1 overwrite=1 inplace=1 src=aaronmk@vegbiendev.nceas.ucsb.edu:/ dest=~/Documents/BIEN/vegbiendev/ sudo -E put --link-dest="$HOME"/Documents/BIEN/svn/ --no-owner --no-group home/aaronmk/bien/
467
				# --no-owner --no-group: needed to allow --link-dest to work
468
				# --link-dest: relative to dest, not currdir, so need abs path
469
	to back up the local machine's settings:
470
		do step when changes are made on vegbiendev > on your machine, download
471
		ssh aaronmk@jupiter.nceas.ucsb.edu
472
			(cd ~/Dropbox/svn/; up)
473
		on your machine:
474
			sudo find / -name .DS_Store -print -delete
475
			rm ~/'Library/Thunderbird/Profiles/9oo8rcyn.default/ImapMail/imap.googlemail.com/[Gmail].sbd/Spam'
476
				# remove the downloaded Spam folder, because spam e-mails often contain viruses that would trigger clamscan
477
			overwrite=1           sync_local_dir=~/Dropbox/svn/ sync_remote_subdir=Dropbox/svn/ bin/sync_upload --size-only # just update mtimes
478
				then review diff, and rerun with `l=1` prepended
479
			overwrite=1 inplace=1 sync_local_dir=~/             sync_remote_subdir=                          bin/sync_upload ~/"VirtualBox VMs/**" # need inplace=1 because they are very large files
480
				then review diff, and rerun with `l=1` prepended
481
			overwrite=1           sync_local_dir=~/             sync_remote_subdir= sudo -E env USER=aaronmk bin/sync_upload --exclude="/Library/Saved Application State/" --exclude="/.Trash/" --exclude="/bin/" --exclude="/bin/pg_ctl" --exclude="/bin/unzip" --exclude="/Dropbox/home/" --exclude="/.profile" --exclude="/.shrc" --exclude="/.bashrc" --exclude="/software/**/.svn/"
482
				# sudo -E: needed for Documents/BIEN/vegbiendev*/
483
				then review diff, and rerun with `l=1` prepended
484
			pause Dropbox: system tray > Dropbox icon > gear icon > Pause Syncing
485
				this prevents Dropbox from trying to capture filesystem
486
				events while syncing
487
			overwrite=1           sync_local_dir=~/             sync_remote_url=~/Dropbox/home/              bin/sync_upload --exclude="/Library/Saved Application State/" --exclude="/.Trash/" --exclude="/.dropbox/" --exclude="/Documents/BIEN/" --exclude="/Dropbox/" --exclude=/gmvault-db/ --exclude="/software/" --exclude="/VirtualBox VMs/**.sav" --exclude="/VirtualBox VMs/**.vdi" --exclude="/VirtualBox VMs/**.vmdk"
488
				then review diff, and rerun with `l=1` prepended
489
			resume Dropbox: system tray > Dropbox icon > gear icon > Resume Syncing
490
	to backup files not in Time Machine:
491
		**IMPORTANT**: need to use 2 TB external hard drive instead of Time
492
			Machine drive because Time Machine drive does not have
493
			~/Documents/BIEN/ in a location where it can be hardlinked against
494
		On local machine:
495
		on first run, create parent dirs:
496
			sudo mkdir -p '/Volumes/BIEN3.**SAVE**/Users/aaronmk/Documents/BIEN/'
497
			sudo mkdir -p '/Volumes/BIEN3.**SAVE**/usr/local/var/postgres/'
498
			l=1 src=/ dest='/Volumes/BIEN3.**SAVE**/' sudo -E put --existing
499
		l=1 overwrite=1 src=/ dest='/Volumes/BIEN3.**SAVE**/' sudo -E put --include='/vegbiendev**' --exclude='**' Users/aaronmk/Documents/BIEN/
500
			# this cannot be backed up by Time Machine because it dereferences hard links:
501
			#  `sudo find /Volumes/Time\ Machine\ Backups/Backups.backupdb/ ! -type d -links +1`
502
			#  returns no files when there is a single timestamped backup, but
503
			#  `sudo find / ! -type d -links +1` does
504
		l=1 overwrite=1 src=/ dest='/Volumes/BIEN3.**SAVE**/' sudo -E put usr/local/var/postgres/
505
			# this cannot be backed up by Time Machine because it prevents the backup process from ending
506
		launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist # stop the PostgreSQL server
507
		l=1 overwrite=1 src=/ dest='/Volumes/BIEN3.**SAVE**/' sudo -E put usr/local/var/postgres/
508
		launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist # start the PostgreSQL server
509
	to back up the local machine's hard drive:
510
		turn on and connect the 2 TB external hard drive
511
		screen
512
		# --exclude='/\**': exclude *-files indicating the (differing) retention
513
		#  statuses of the partitions involved
514
		l=1 overwrite=1 src=/ dest='/Volumes/BIEN3.**SAVE**/' sudo -E put --exclude='/\**' --exclude=/.fseventsd/ --exclude=/private/var/vm/ --exclude=/usr/local/var/postgres/ --exclude=/Users/aaronmk/.dropbox/
515
			# no --extended-attributes: rsync has to visit every file for this
516
			# runtime: 10 min (~600); initial runtime: 4-13 h ("2422.84"+"12379.91" .. "45813.19"+"747.96")
517
		# repeat until only minimal changes
518
		l=1 overwrite=1 src=/ dest='/Volumes/BIEN3.**SAVE**/' sudo -E put --exclude='/\**' --exclude=/.fseventsd/ --exclude=/private/var/vm/
519
		pause Dropbox: system tray > Dropbox icon > gear icon > Pause Syncing
520
			otherwise, the backup of ~/.dropbox will be corrupted
521
		launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist # stop the PostgreSQL server
522
		l=1 overwrite=1 src=/ dest='/Volumes/BIEN3.**SAVE**/' sudo -E put --exclude='/\**' --exclude=/.fseventsd/ --exclude=/private/var/vm/
523
		launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist # start the PostgreSQL server
524
		resume Dropbox: system tray > Dropbox icon > gear icon > Resume Syncing
525
	to restore from Time Machine:
526
		# restart holding Alt
527
		# select Time Machine Backups
528
		# restore the last Time Machine backup to Macintosh HD
529
		# restart holding Alt
530
		# select Macintosh HD
531
		$ screen
532
		$ l=1 swap=1 src=/ dest=/Volumes/Time\ Machine\ Backups/ sudo -E put usr/local/var/postgres/ # runtime: 1 h ("4020.61")
533
		$ make postgres_restart
534
	VegCore data dictionary:
535
		Regularly, or whenever the VegCore data dictionary page
536
			(https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/VegCore)
537
			is changed, regenerate mappings/VegCore.csv:
538
			On local machine:
539
			make mappings/VegCore.htm-remake; make mappings/
540
			apply new data dict mappings to datasource mappings/staging tables:
541
				inputs/run postprocess # runtime: see inputs/run
542
				time yes|make inputs/{NVS,SALVIAS,TEAM}/test # old-style import; runtime: 1 min ("0m59.692s") @starscream
543
			svn di mappings/VegCore.tables.redmine
544
			If there are changes, update the data dictionary's Tables section
545
			When moving terms, check that no terms were lost: svn di
546
			svn ci -m 'mappings/VegCore.htm: regenerated from wiki'
547
			ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
548
				perform the steps under "apply new data dict mappings to
549
					datasource mappings/staging tables" above
550
	Important: Whenever you install a system update that affects PostgreSQL or
551
		any of its dependencies, such as libc, you should restart the PostgreSQL
552
		server. Otherwise, you may get strange errors like "the database system
553
		is in recovery mode" which go away upon reimport, or you may not be able
554
		to access the database as the postgres superuser. This applies to both
555
		Linux and Mac OS X.
556

    
557
Backups:
558
	Archived imports:
559
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
560
		Back up: make backups/<version>.backup &
561
			Note: To back up the last import, you must archive it first:
562
				make schemas/rotate
563
		Test: make -s backups/<version>.backup/test &
564
		Restore: make backups/<version>.backup/restore &
565
		Remove: make backups/<version>.backup/remove
566
		Download: make backups/<version>.backup/download
567
	TNRS cache:
568
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
569
		Back up: make backups/TNRS.backup-remake &
570
			runtime: 3 min ("real 2m48.859s")
571
		Restore:
572
			yes|make inputs/.TNRS/uninstall
573
			make backups/TNRS.backup/restore &
574
				runtime: 5.5 min ("real 5m35.829s")
575
			yes|make schemas/public/reinstall
576
				Must come after TNRS restore to recreate tnrs_input_name view
577
	Full DB:
578
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
579
		Back up: make backups/vegbien.<version>.backup &
580
		Test: make -s backups/vegbien.<version>.backup/test &
581
		Restore: make backups/vegbien.<version>.backup/restore &
582
		Download: make backups/vegbien.<version>.backup/download
583
	Import logs:
584
		On local machine:
585
		Download: make inputs/download-logs live=1
586

    
587
Datasource refreshing:
588
	VegBank:
589
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
590
		make inputs/VegBank/vegbank.sql-remake
591
		make inputs/VegBank/reinstall quiet=1 &
592

    
593
Schema changes:
594
	On local machine:
595
	When changing the analytical views, run sync_analytical_..._to_view()
596
		to update the corresponding table
597
	Remember to update the following files with any renamings:
598
		schemas/filter_ERD.csv
599
		mappings/VegCore-VegBIEN.csv
600
		mappings/verify.*.sql
601
	Regenerate schema from installed DB: make schemas/remake
602
	Reinstall DB from schema: make schemas/public/reinstall schemas/reinstall
603
		**WARNING**: This will delete the public schema of your VegBIEN DB!
604
	If needed, reinstall staging tables:
605
		On local machine:
606
			sudo -E -u postgres psql <<<'ALTER DATABASE vegbien RENAME TO vegbien_prev'
607
			make db
608
			. bin/reinstall_all
609
			Fix any bugs and retry until no errors
610
			make schemas/public/install
611
				This must be run *after* the datasources are installed, because
612
				views in public depend on some of the datasources
613
			sudo -E -u postgres psql <<<'DROP DATABASE vegbien_prev'
614
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
615
			repeat the above steps
616
			**WARNING**: Do not run this until reinstall_all runs successfully
617
			on the local machine, or the live DB may be unrestorable!
618
	update mappings and staging table column names:
619
		on local machine:
620
			inputs/run postprocess # runtime: see inputs/run
621
			time yes|make inputs/{NVS,SALVIAS,TEAM}/test # old-style import; runtime: 1 min ("0m59.692s") @starscream
622
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
623
			manually apply schema changes to the live public schema
624
			do steps under "on local machine" above
625
	Sync ERD with vegbien.sql schema:
626
		Run make schemas/vegbien.my.sql
627
		Open schemas/vegbien.ERD.mwb in MySQLWorkbench
628
		Go to File > Export > Synchronize With SQL CREATE Script...
629
		For Input File, select schemas/vegbien.my.sql
630
		Click Continue
631
		In the changes list, select each table with an arrow next to it
632
		Click Update Model
633
		Click Continue
634
		Note: The generated SQL script will be empty because we are syncing in
635
			the opposite direction
636
		Click Execute
637
		Reposition any lines that have been reset
638
		Add any new tables by dragging them from the Catalog in the left sidebar
639
			to the diagram
640
		Remove any deleted tables by right-clicking the table's diagram element,
641
			selecting Delete '<table name>', and clicking Delete
642
		Save
643
		If desired, update the graphical ERD exports (see below)
644
	Update graphical ERD exports:
645
		Go to File > Export > Export as PNG...
646
		Select schemas/vegbien.ERD.png and click Save
647
		Go to File > Export > Export as SVG...
648
		Select schemas/vegbien.ERD.svg and click Save
649
		Go to File > Export > Export as Single Page PDF...
650
		Select schemas/vegbien.ERD.1_pg.pdf and click Save
651
		Go to File > Print...
652
		In the lower left corner, click PDF > Save as PDF...
653
		Set the Title and Author to ""
654
		Select schemas/vegbien.ERD.pdf and click Save
655
		Commit: svn ci -m "schemas/vegbien.ERD.mwb: Regenerated exports"
656
	Refactoring tips:
657
		To rename a table:
658
			In vegbien.sql, do the following:
659
				Replace regexp (?<=_|\b)<old>(?=_|\b) with <new>
660
					This is necessary because the table name is *everywhere*
661
				Search for <new>
662
				Manually change back any replacements inside comments
663
		To rename a column:
664
			Rename the column: ALTER TABLE <table> RENAME <old> TO <new>;
665
			Recreate any foreign key for the column, removing CONSTRAINT <name>
666
				This resets the foreign key name using the new column name
667
	Creating a poster of the ERD:
668
		Determine the poster size:
669
			Measure the line height (from the bottom of one line to the bottom
670
				of another): 16.3cm/24 lines = 0.679cm
671
			Measure the height of the ERD: 35.4cm*2 = 70.8cm
672
			Zoom in as far as possible
673
			Measure the height of a capital letter: 3.5mm
674
			Measure the line height: 8.5mm
675
			Calculate the text's fraction of the line height: 3.5mm/8.5mm = 0.41
676
			Calculate the text height: 0.679cm*0.41 = 0.28cm
677
			Calculate the text height's fraction of the ERD height:
678
				0.28cm/70.8cm = 0.0040
679
			Measure the text height on the *VegBank* ERD poster: 5.5mm = 0.55cm
680
			Calculate the VegBIEN poster height to make the text the same size:
681
				0.55cm/0.0040 = 137.5cm H; *1in/2.54cm = 54.1in H
682
			The ERD aspect ratio is 11 in W x (2*8.5in H) = 11x17 portrait
683
			Calculate the VegBIEN poster width: 54.1in H*11W/17H = 35.0in W
684
			The minimum VegBIEN poster size is 35x54in portrait
685
		Determine the cost:
686
			The FedEx Kinkos near NCEAS (1030 State St, Santa Barbara, CA 93101)
687
				charges the following for posters:
688
				base: $7.25/sq ft
689
				lamination: $3/sq ft
690
				mounting on a board: $8/sq ft
691

    
692
Testing:
693
	On a development machine, you should put the following in your .profile:
694
		umask ug=rwx,o= # prevent files from becoming web-accessible
695
		export log= n=2
696
	For development machine specs, see /planning/resources/dev_machine.specs/
697
	On local machine:
698
	Mapping process: make test
699
		Including column-based import: make test by_col=1
700
			If the row-based and column-based imports produce different inserted
701
			row counts, this usually means that a table is underconstrained
702
			(the unique indexes don't cover all possible rows).
703
			This can occur if you didn't use COALESCE(field, null_value) around
704
			a nullable field in a unique index. See sql_gen.null_sentinels for
705
			the appropriate null value to use.
706
	Map spreadsheet generation: make remake
707
	Missing mappings: make missing_mappings
708
	Everything (for most complete coverage): make test-all
709

    
710
Debugging:
711
	"Binary chop" debugging:
712
		(This is primarily useful for regressions that occurred in a previous
713
		revision, which was committed without running all the tests)
714
		up -r <rev>; make inputs/.TNRS/reinstall; make schemas/public/reinstall; make <failed-test>.xml
715
	.htaccess:
716
		mod_rewrite:
717
			**IMPORTANT**: whenever you change the DirectorySlash setting for a
718
				directory, you *must* clear your browser's cache to ensure that
719
				a cached redirect is not used. this is because RewriteRule
720
				redirects are (by default) temporary, but DirectorySlash
721
				redirects are permanent.
722
				for Firefox:
723
					press Cmd+Shift+Delete
724
					check only Cache
725
					press Enter or click Clear Now
726

    
727
WinMerge setup:
728
	In a Windows VM:
729
	Install WinMerge from <http://winmerge.org/>
730
	Open WinMerge
731
	Go to Edit > Options and click Compare in the left sidebar
732
	Enable "Moved block detection", as described at
733
		<http://manual.winmerge.org/Configuration.html#d0e5892>.
734
	Set Whitespace to Ignore change, as described at
735
		<http://manual.winmerge.org/Configuration.html#d0e5758>.
736

    
737
Documentation:
738
	To generate a Redmine-formatted list of steps for column-based import:
739
		On local machine:
740
		make schemas/public/reinstall
741
		make inputs/ACAD/Specimen/logs/steps.by_col.log.sql
742
	To import and scrub just the test taxonomic names:
743
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i
744
		inputs/test_taxonomic_names/test_scrub
745

    
746
General:
747
	To see a program's description, read its top-of-file comment
748
	To see a program's usage, run it without arguments
749
	To remake a directory: make <dir>/remake
750
	To remake a file: make <file>-remake
(6-6/11)