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 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
Connecting to vegbiendev:
11
	ssh -t vegbiendev.nceas.ucsb.edu exec sudo su - aaronmk
12
	cd /home/bien/svn # should happen automatically at login
13

    
14
Notes on running programs:
15
	**WARNING**: always start with a clean shell, to avoid spurious bugs. the
16
		shell should not have changes to the env vars. (there have been bugs
17
		that went away after closing and reopening the terminal window.) note
18
		that running `exec bash` is not sufficient to *reset* the env vars.
19

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

    
25
Single datasource import:
26
	ssh -t vegbiendev.nceas.ucsb.edu exec sudo su - aaronmk
27
	(Re)import and scrub: make inputs/<datasrc>/reimport_scrub by_col=1 &
28
	(Re)import only: make inputs/<datasrc>/reimport by_col=1 &
29
	Note that these commands also work if the datasource is not yet imported
30
	Remake analytical DB: see Full database import > To remake analytical DB
31

    
32
Full database import:
33
	**WARNING**: You must perform *every single* step listed below, to avoid
34
		breaking column-based import
35
	**WARNING**: always start with a clean shell, as described above under
36
		"Notes on running programs"
37
	ssh aaronmk@jupiter.nceas.ucsb.edu
38
		svn up --force
39
	On local machine:
40
		do steps under Maintenance > "to synchronize vegbiendev, jupiter, and
41
			your local machine"
42
		make inputs/upload
43
		make inputs/upload live=1
44
		make test by_col=1 # runtime: 20 min ("4m46.108s" + ("21:50:43" - "21:37:09")) @starscream
45
			if you encounter errors, they are most likely related to the
46
				PostgreSQL error parsing in /lib/sql.py parse_exception()
47
			See note under Testing below
48
	ssh -t vegbiendev.nceas.ucsb.edu exec sudo su - aaronmk
49
	Ensure there are no local modifications: svn st
50
	svn up
51
	make inputs/download
52
	make inputs/download live=1
53
	For each newly-uploaded datasource above: make inputs/<datasrc>/reinstall
54
	Update the auxiliary schemas: make schemas/reinstall
55
		**WARNING**: requires sudo access!
56
		The public schema will be installed separately by the import process
57
	Delete imports before the last so they won't bloat the full DB backup:
58
		make backups/vegbien.<version>.backup/remove
59
		To keep a previous import other than the public schema:
60
			export dump_opts='--exclude-schema=public --exclude-schema=<version>'
61
	Make sure there is at least 300GB of disk space on /: df -h
62
		The import schema is 230GB, and may use additional space for temp tables
63
		To free up space, remove backups that have been archived on jupiter:
64
			List backups/ to view older backups
65
			Check their MD5 sums using the steps under On jupiter below
66
			Remove these backups
67
	screen
68
	Press ENTER
69
	unset TMOUT # TMOUT causes screen to exit even with background processes
70
	set -o ignoreeof #prevent Ctrl+D from exiting `screen` to keep attached jobs
71
	unset version
72
	Start column-based import: . bin/import_all
73
		To use row-based import: . bin/import_all by_col=
74
		To stop all running imports: . bin/stop_imports
75
		**WARNING**: Do NOT run import_all in the background, or the jobs it
76
			creates won't be owned by your shell.
77
		Note that import_all will take up to an hour to import the NCBI backbone
78
			and other metadata before returning control to the shell.
79
		To view progress:
80
			tail inputs/{.,}*/*/logs/$version.log.sql
81
	Wait (4 days) for the import to finish
82
	To recover from a closed terminal window: screen -r
83
	To restart an aborted import for a specific table:
84
		export version=<version>
85
		(set -o errexit; make inputs/<datasrc>/<table>/import_scrub by_col=1 continue=1; make inputs/<datasrc>/publish) &
86
		bin/after_import $! & # $! can also be obtained from `jobs -l`
87
	Get $version: echo $version
88
	Set $version in all vegbiendev terminals: export version=<version>
89
	When there are no more running jobs, exit the screen
90
	ssh -t vegbiendev.nceas.ucsb.edu exec sudo su - aaronmk
91
		Upload logs: make inputs/upload live=1
92
	On local machine: make inputs/download-logs live=1
93
	In PostgreSQL:
94
		Go to wiki.vegpath.org/VegBIEN_contents
95
		Get the # observations
96
		Get the # datasources
97
		Get the # datasources with observations
98
		in the r# schema:
99
		Check that analytical_stem contains [# observations] rows
100
		Check that source contains [# datasources] rows up through XAL. If this
101
			is not the case, manually check the entries in source against the
102
			datasources list on the wiki page (some datasources may be near the
103
			end depending on import order).
104
		Check that provider_count contains [# datasources with observations]
105
			rows with dataset="(total)" (at the top when the table is unsorted)
106
	Check that TNRS ran successfully:
107
		tail -100 inputs/.TNRS/tnrs/logs/tnrs.make.log.sql
108
		If the log ends in an AssertionError
109
			"assert sql.table_col_names(db, table) == header":
110
			Figure out which TNRS CSV columns have changed
111
			On local machine:
112
				Make the changes in the DB's TNRS and public schemas
113
				rm=1 inputs/.TNRS/schema.sql.run export_
114
				make schemas/remake
115
				inputs/test_taxonomic_names/test_scrub # re-run TNRS
116
				rm=1 inputs/.TNRS/data.sql.run export_
117
				Commit
118
			ssh -t vegbiendev.nceas.ucsb.edu exec sudo su - aaronmk
119
				If dropping a column, save the dependent views
120
				Make the same changes in the live TNRS.tnrs table on vegbiendev
121
				If dropping a column, recreate the dependent views
122
				Restart the TNRS client: make scrub by_col=1 &
123
	tail inputs/{.,}*/*/logs/$version.log.sql
124
	In the output, search for "Command exited with non-zero status"
125
	For inputs that have this, fix the associated bug(s)
126
	If many inputs have errors, discard the current (partial) import:
127
		make schemas/$version/uninstall
128
	Otherwise, continue
129
	Publish the new import:
130
		**WARNING**: Before proceeding, be sure you have done *every single*
131
			verification step listed above. Otherwise, a previous valid import
132
			could incorrectly be overwritten with a broken one.
133
		make schemas/$version/publish # runtime: 1 min ("real 1m10.451s")
134
	unset version
135
	make backups/upload live=1
136
	on local machine:
137
		make backups/vegbien.$version.backup/download live=1
138
			# download backup to local machine
139
	ssh aaronmk@jupiter.nceas.ucsb.edu
140
		cd /data/dev/aaronmk/bien/backups
141
		For each newly-archived backup:
142
			make -s <backup>.md5/test
143
			Check that "OK" is printed next to the filename
144
	If desired, record the import times in inputs/import.stats.xls:
145
		On local machine:
146
		Open inputs/import.stats.xls
147
		If the rightmost import is within 5 columns of column IV:
148
			Copy the current tab to <leftmost-date>~<rightmost-date>
149
			Remove the previous imports from the current tab because they are
150
				now in the copied tab instead
151
		Insert a copy of the leftmost "By column" column group before it
152
		export version=<version>
153
		bin/import_date inputs/{.,}*/*/logs/$version.log.sql
154
		Update the import date in the upper-right corner
155
		bin/import_times inputs/{.,}*/*/logs/$version.log.sql
156
		Paste the output over the # Rows/Time columns, making sure that the
157
			row counts match up with the previous import's row counts
158
		If the row counts do not match up, insert or reorder rows as needed
159
			until they do. Get the datasource names from the log file footers:
160
			tail inputs/{.,}*/*/logs/$version.log.sql
161
		Commit: svn ci -m 'inputs/import.stats.xls: updated import times'
162
	Running individual steps separately:
163
	To run TNRS:
164
		To use an import other than public: export version=<version>
165
		make scrub &
166
		To view progress:
167
			tail -100 inputs/.TNRS/tnrs/logs/tnrs.make.log.sql
168
	To remake analytical DB:
169
		To use an import other than public: export version=<version>
170
		bin/make_analytical_db & # runtime: 13 h ("12:43:57elapsed")
171
		To view progress:
172
			tail -150 inputs/analytical_db/logs/make_analytical_db.log.sql
173
	To back up DB (staging tables and last import):
174
		To use an import *other than public*: export version=<version>
175
		make backups/TNRS.backup-remake &
176
		dump_opts=--exclude-schema=public make backups/vegbien.$version.backup/test &
177
			If after renaming to public, instead set dump_opts='' and replace
178
			$version with the appropriate revision
179
		make backups/upload live=1
180

    
181
Datasource setup:
182
	On local machine:
183
	Example steps for a datasource: wiki.vegpath.org/Import_process_for_Madidi
184
	umask ug=rwx,o= # prevent files from becoming web-accessible
185
	Add a new datasource: make inputs/<datasrc>/add
186
		<datasrc> may not contain spaces, and should be abbreviated.
187
		If the datasource is a herbarium, <datasrc> should be the herbarium code
188
			as defined by the Index Herbariorum <http://sweetgum.nybg.org/ih/>
189
	For a new-style datasource (one containing a ./run runscript):
190
		"cp" -f inputs/.NCBI/{Makefile,run,table.run} inputs/<datasrc>/
191
	For MySQL inputs (exports and live DB connections):
192
		For .sql exports:
193
			Place the original .sql file in _src/ (*not* in _MySQL/)
194
			Follow the steps starting with Install the staging tables below.
195
				This is for an initial sync to get the file onto vegbiendev.
196
			ssh -t vegbiendev.nceas.ucsb.edu exec sudo su - aaronmk
197
				Create a database for the MySQL export in phpMyAdmin
198
				Give the bien user all database-specific privileges *except*
199
					UPDATE, DELETE, ALTER, DROP. This prevents bugs in the
200
					import scripts from accidentally deleting data.
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
		For a new-style datasource:
242
			"cp" -f inputs/.NCBI/nodes/run inputs/<datasrc>/<table>/
243
			inputs/<datasrc>/<table>/run
244
	Install the staging tables:
245
		make inputs/<datasrc>/reinstall quiet=1 &
246
		For a MySQL .sql export:
247
			At prompt "[you]@vegbiendev's password:", enter your password
248
			At prompt "Enter password:", enter the value in config/bien_password
249
		To view progress: tail -f inputs/<datasrc>/<table>/logs/install.log.sql
250
		View the logs: tail -n +1 inputs/<datasrc>/*/logs/install.log.sql
251
			tail provides a header line with the filename
252
			+1 starts at the first line, to show the whole file
253
		For every file with an error 'column "..." specified more than once':
254
			Add a header override file "+header.<ext>" in <table>/:
255
				Note: The leading "+" should sort it before the flat files.
256
					"_" unfortunately sorts *after* capital letters in ASCII.
257
				Create a text file containing the header line of the flat files
258
				Add an ! at the beginning of the line
259
					This signals cat_csv that this is a header override.
260
				For empty names, use their 0-based column # (by convention)
261
				For duplicate names, add a distinguishing suffix
262
				For long names that collided, rename them to <= 63 chars long
263
				Do NOT make readability changes in this step; that is what the
264
					map spreadsheets (below) are for.
265
				Save
266
		If you made any changes, re-run the install command above
267
	Auto-create the map spreadsheets: make inputs/<datasrc>/
268
	Map each table's columns:
269
		In each <table>/ subdir, for each "via map" map.csv:
270
			Open the map in a spreadsheet editor
271
			Open the "core map" /mappings/Veg+-VegBIEN.csv
272
			In each row of the via map, set the right column to a value from the
273
				left column of the core map
274
			Save
275
		Regenerate the derived maps: make inputs/<datasrc>/
276
	Accept the test cases:
277
		For a new-style datasource:
278
			inputs/<datasrc>/run
279
			svn di inputs/<datasrc>/*/test.xml.ref
280
			If you get errors, follow the steps for old-style datasources below
281
		For an old-style datasource:
282
			make inputs/<datasrc>/test
283
			When prompted to "Accept new test output", enter y and press ENTER
284
			If you instead get errors, do one of the following for each one:
285
			-	If the error was due to a bug, fix it
286
			-	Add a SQL function that filters or transforms the invalid data
287
			-	Make an empty mapping for the columns that produced the error.
288
				Put something in the Comments column of the map spreadsheet to
289
				prevent the automatic mapper from auto-removing the mapping.
290
			When accepting tests, it's helpful to use WinMerge
291
				(see WinMerge setup below for configuration)
292
		make inputs/<datasrc>/test by_col=1
293
			If you get errors this time, this always indicates a bug, usually in
294
				the VegBIEN unique constraints or column-based import itself
295
	Add newly-created files: make inputs/<datasrc>/add
296
	Commit: svn ci -m "Added inputs/<datasrc>/" inputs/<datasrc>/
297
	Update vegbiendev:
298
		ssh aaronmk@jupiter.nceas.ucsb.edu
299
			svn up
300
		On local machine:
301
			./fix_perms
302
			make inputs/upload
303
			make inputs/upload live=1
304
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo su - aaronmk
305
			svn up
306
			make inputs/download
307
			make inputs/download live=1
308
			Follow the steps under Install the staging tables above
309

    
310
Maintenance:
311
	on a live machine, you should put the following in your .profile:
312
--
313
# make svn files web-accessible. this does not affect unversioned files, because
314
# these get the right permissions on the local machine instead.
315
umask ug=rwx,o=rx
316

    
317
unset TMOUT # TMOUT causes screen to exit even with background processes
318
--
319
	if http://vegbiendev.nceas.ucsb.edu/phppgadmin/ goes down:
320
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo su - aaronmk
321
			make phppgadmin-Linux
322
	to synchronize vegbiendev, jupiter, and your local machine:
323
		**WARNING**: pay careful attention to all files that will be deleted or
324
			overwritten!
325
		install put if needed:
326
			download https://uutils.googlecode.com/svn/trunk/bin/put to ~/bin/ and `chmod +x` it
327
		when changes are made on vegbiendev:
328
			avoid extraneous diffs when rsyncing:
329
				on all machines:
330
				up
331
				./fix_perms
332
			ssh -t vegbiendev.nceas.ucsb.edu exec sudo su - aaronmk
333
				upload:
334
				overwrite=1 bin/sync_upload --size-only
335
					then rerun with l=1 ...
336
			on your machine:
337
				download:
338
				overwrite=1 swap=1 src=. dest='aaronmk@jupiter.nceas.ucsb.edu:~/bien' put --exclude=.svn inputs/VegBIEN/TWiki
339
					then rerun with l=1 ...
340
				swap=1 bin/sync_upload backups/TNRS.backup
341
					then rerun with l=1 ...
342
				overwrite=1 swap=1 bin/sync_upload --size-only
343
					then rerun with l=1 ...
344
				overwrite=1 sync_remote_url=~/Dropbox/svn/ bin/sync_upload --existing --size-only # just update mtimes/perms
345
					then rerun with l=1 ...
346
	to back up e-mails:
347
		on local machine:
348
		/Applications/gmvault-v1.8.1-beta/bin/gmvault sync --multiple-db-owner --type quick aaronmk.nceas@gmail.com
349
		/Applications/gmvault-v1.8.1-beta/bin/gmvault sync --multiple-db-owner --type quick aaronmk@nceas.ucsb.edu
350
		open Thunderbird
351
		click the All Mail folder for each account and wait for it to download the e-mails in it
352
	to back up the version history:
353
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo su - aaronmk
354
		svnsync sync file:///home/aaronmk/Dropbox/docs/BIEN/svn_repo/ # initial runtime: 1.5 h ("08:21:38" - "06:45:26") @vegbiendev
355
		(cd ~/Dropbox/docs/BIEN/git/; git svn fetch)
356
		overwrite=1        src=~ dest='aaronmk@jupiter.nceas.ucsb.edu:~' put Dropbox/docs/BIEN/svn_repo/ # runtime: 1 min ("1:05.08")
357
			then rerun with l=1 ...
358
		overwrite=1        src=~ dest='aaronmk@jupiter.nceas.ucsb.edu:~' put Dropbox/docs/BIEN/git/
359
			then rerun with l=1 ...
360
		on local machine:
361
		overwrite=1 swap=1 src=~ dest='aaronmk@jupiter.nceas.ucsb.edu:~' put Dropbox/docs/BIEN/svn_repo/ # runtime: 30 s ("36.19")
362
			then rerun with l=1 ...
363
		overwrite=1 swap=1 src=~ dest='aaronmk@jupiter.nceas.ucsb.edu:~' put Dropbox/docs/BIEN/git/
364
			then rerun with l=1 ...
365
	to synchronize a Mac's settings with my testing machine's:
366
		download:
367
			**WARNING**: this will overwrite all your user's settings!
368
			on your machine:
369
			overwrite=1 swap=1 sync_local_dir=~/Library/ sync_remote_subdir=Library/ bin/sync_upload --exclude="/Saved Application State"
370
				then rerun with l=1 ...
371
		upload:
372
			do step when changes are made on vegbiendev > on your machine, download
373
			ssh aaronmk@jupiter.nceas.ucsb.edu
374
				(cd ~/Dropbox/svn/; up)
375
			on your machine:
376
				rm ~/'Library/Thunderbird/Profiles/9oo8rcyn.default/ImapMail/imap.googlemail.com/[Gmail].sbd/Spam'
377
					# remove the downloaded Spam folder, because spam e-mails often contain viruses that would trigger clamscan
378
				overwrite=1 del= sync_local_dir=~/Dropbox/svn/ sync_remote_subdir=Dropbox/svn/ bin/sync_upload --size-only # just update mtimes
379
					then rerun with l=1 ...
380
				overwrite=1      sync_local_dir=~              sync_remote_subdir=             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"
381
					then rerun with l=1 ...
382
				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="/software" --exclude="/VirtualBox VMs/**.sav" --exclude="/VirtualBox VMs/**.vdi" --exclude="/VirtualBox VMs/**.vmdk"
383
					then rerun with l=1 ...
384
		upload just the VirtualBox VMs:
385
			on your machine:
386
				overwrite=1      sync_local_dir=~              sync_remote_subdir=             bin/sync_upload ~/"VirtualBox VMs/**"
387
					then rerun with l=1 ...
388
	to backup files not in Time Machine:
389
		On local machine:
390
		overwrite=1 src=/ dest=/Volumes/Time\ Machine\ Backups/ sudo -E put Library/PostgreSQL/9.3/data/
391
			then rerun with l=1 ...
392
		pg_ctl. stop # stop the PostgreSQL server
393
		overwrite=1 src=/ dest=/Volumes/Time\ Machine\ Backups/ sudo -E put Library/PostgreSQL/9.3/data/
394
			then rerun with l=1 ...
395
		pg_ctl. start # start the PostgreSQL server
396
	VegCore data dictionary:
397
		Regularly, or whenever the VegCore data dictionary page
398
			(https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/VegCore)
399
			is changed, regenerate mappings/VegCore.csv:
400
			On local machine:
401
			make mappings/VegCore.htm-remake; make mappings/
402
			apply new data dict mappings to datasource mappings/staging tables:
403
				inputs/run postprocess # runtime: see inputs/run
404
				time yes|make inputs/{NVS,SALVIAS,TEAM}/test # old-style import; runtime: 1 min ("0m59.692s") @starscream
405
			svn di mappings/VegCore.tables.redmine
406
			If there are changes, update the data dictionary's Tables section
407
			When moving terms, check that no terms were lost: svn di
408
			svn ci -m 'mappings/VegCore.htm: regenerated from wiki'
409
			ssh -t vegbiendev.nceas.ucsb.edu exec sudo su - aaronmk
410
				perform the steps under "apply new data dict mappings to
411
					datasource mappings/staging tables" above
412
	Important: Whenever you install a system update that affects PostgreSQL or
413
		any of its dependencies, such as libc, you should restart the PostgreSQL
414
		server. Otherwise, you may get strange errors like "the database system
415
		is in recovery mode" which go away upon reimport, or you may not be able
416
		to access the database as the postgres superuser. This applies to both
417
		Linux and Mac OS X.
418

    
419
Backups:
420
	Archived imports:
421
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo su - aaronmk
422
		Back up: make backups/<version>.backup &
423
			Note: To back up the last import, you must archive it first:
424
				make schemas/rotate
425
		Test: make -s backups/<version>.backup/test &
426
		Restore: make backups/<version>.backup/restore &
427
		Remove: make backups/<version>.backup/remove
428
		Download: make backups/<version>.backup/download
429
	TNRS cache:
430
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo su - aaronmk
431
		Back up: make backups/TNRS.backup-remake &
432
			runtime: 3 min ("real 2m48.859s")
433
		Restore:
434
			yes|make inputs/.TNRS/uninstall
435
			make backups/TNRS.backup/restore &
436
				runtime: 5.5 min ("real 5m35.829s")
437
			yes|make schemas/public/reinstall
438
				Must come after TNRS restore to recreate tnrs_input_name view
439
	Full DB:
440
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo su - aaronmk
441
		Back up: make backups/vegbien.<version>.backup &
442
		Test: make -s backups/vegbien.<version>.backup/test &
443
		Restore: make backups/vegbien.<version>.backup/restore &
444
		Download: make backups/vegbien.<version>.backup/download
445
	Import logs:
446
		On local machine:
447
		Download: make inputs/download-logs live=1
448

    
449
Datasource refreshing:
450
	VegBank:
451
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo su - aaronmk
452
		make inputs/VegBank/vegbank.sql-remake
453
		make inputs/VegBank/reinstall quiet=1 &
454

    
455
Schema changes:
456
	On local machine:
457
	When changing the analytical views, run sync_analytical_..._to_view()
458
		to update the corresponding table
459
	Remember to update the following files with any renamings:
460
		schemas/filter_ERD.csv
461
		mappings/VegCore-VegBIEN.csv
462
		mappings/verify.*.sql
463
	Regenerate schema from installed DB: make schemas/remake
464
	Reinstall DB from schema: make schemas/public/reinstall schemas/reinstall
465
		**WARNING**: This will delete the public schema of your VegBIEN DB!
466
	If needed, reinstall staging tables:
467
		On local machine:
468
			sudo -E -u postgres psql <<<'ALTER DATABASE vegbien RENAME TO vegbien_prev'
469
			make db
470
			. bin/reinstall_all
471
			Fix any bugs and retry until no errors
472
			make schemas/public/install
473
				This must be run *after* the datasources are installed, because
474
				views in public depend on some of the datasources
475
			sudo -E -u postgres psql <<<'DROP DATABASE vegbien_prev'
476
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo su - aaronmk
477
			repeat the above steps
478
			**WARNING**: Do not run this until reinstall_all runs successfully
479
			on the local machine, or the live DB may be unrestorable!
480
	update mappings and staging table column names:
481
		on local machine:
482
			inputs/run postprocess # runtime: see inputs/run
483
			time yes|make inputs/{NVS,SALVIAS,TEAM}/test # old-style import; runtime: 1 min ("0m59.692s") @starscream
484
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo su - aaronmk
485
			manually apply schema changes to the live public schema
486
			do steps under "on local machine" above
487
	Sync ERD with vegbien.sql schema:
488
		Run make schemas/vegbien.my.sql
489
		Open schemas/vegbien.ERD.mwb in MySQLWorkbench
490
		Go to File > Export > Synchronize With SQL CREATE Script...
491
		For Input File, select schemas/vegbien.my.sql
492
		Click Continue
493
		In the changes list, select each table with an arrow next to it
494
		Click Update Model
495
		Click Continue
496
		Note: The generated SQL script will be empty because we are syncing in
497
			the opposite direction
498
		Click Execute
499
		Reposition any lines that have been reset
500
		Add any new tables by dragging them from the Catalog in the left sidebar
501
			to the diagram
502
		Remove any deleted tables by right-clicking the table's diagram element,
503
			selecting Delete '<table name>', and clicking Delete
504
		Save
505
		If desired, update the graphical ERD exports (see below)
506
	Update graphical ERD exports:
507
		Go to File > Export > Export as PNG...
508
		Select schemas/vegbien.ERD.png and click Save
509
		Go to File > Export > Export as SVG...
510
		Select schemas/vegbien.ERD.svg and click Save
511
		Go to File > Export > Export as Single Page PDF...
512
		Select schemas/vegbien.ERD.1_pg.pdf and click Save
513
		Go to File > Print...
514
		In the lower left corner, click PDF > Save as PDF...
515
		Set the Title and Author to ""
516
		Select schemas/vegbien.ERD.pdf and click Save
517
		Commit: svn ci -m "schemas/vegbien.ERD.mwb: Regenerated exports"
518
	Refactoring tips:
519
		To rename a table:
520
			In vegbien.sql, do the following:
521
				Replace regexp (?<=_|\b)<old>(?=_|\b) with <new>
522
					This is necessary because the table name is *everywhere*
523
				Search for <new>
524
				Manually change back any replacements inside comments
525
		To rename a column:
526
			Rename the column: ALTER TABLE <table> RENAME <old> TO <new>;
527
			Recreate any foreign key for the column, removing CONSTRAINT <name>
528
				This resets the foreign key name using the new column name
529
	Creating a poster of the ERD:
530
		Determine the poster size:
531
			Measure the line height (from the bottom of one line to the bottom
532
				of another): 16.3cm/24 lines = 0.679cm
533
			Measure the height of the ERD: 35.4cm*2 = 70.8cm
534
			Zoom in as far as possible
535
			Measure the height of a capital letter: 3.5mm
536
			Measure the line height: 8.5mm
537
			Calculate the text's fraction of the line height: 3.5mm/8.5mm = 0.41
538
			Calculate the text height: 0.679cm*0.41 = 0.28cm
539
			Calculate the text height's fraction of the ERD height:
540
				0.28cm/70.8cm = 0.0040
541
			Measure the text height on the *VegBank* ERD poster: 5.5mm = 0.55cm
542
			Calculate the VegBIEN poster height to make the text the same size:
543
				0.55cm/0.0040 = 137.5cm H; *1in/2.54cm = 54.1in H
544
			The ERD aspect ratio is 11 in W x (2*8.5in H) = 11x17 portrait
545
			Calculate the VegBIEN poster width: 54.1in H*11W/17H = 35.0in W
546
			The minimum VegBIEN poster size is 35x54in portrait
547
		Determine the cost:
548
			The FedEx Kinkos near NCEAS (1030 State St, Santa Barbara, CA 93101)
549
				charges the following for posters:
550
				base: $7.25/sq ft
551
				lamination: $3/sq ft
552
				mounting on a board: $8/sq ft
553

    
554
Testing:
555
	On a development machine, you should put the following in your .profile:
556
		umask ug=rwx,o= # prevent files from becoming web-accessible
557
		export log= n=2
558
	For development machine specs, see /planning/resources/dev_machine.specs/
559
	On local machine:
560
	Mapping process: make test
561
		Including column-based import: make test by_col=1
562
			If the row-based and column-based imports produce different inserted
563
			row counts, this usually means that a table is underconstrained
564
			(the unique indexes don't cover all possible rows).
565
			This can occur if you didn't use COALESCE(field, null_value) around
566
			a nullable field in a unique index. See sql_gen.null_sentinels for
567
			the appropriate null value to use.
568
	Map spreadsheet generation: make remake
569
	Missing mappings: make missing_mappings
570
	Everything (for most complete coverage): make test-all
571

    
572
Debugging:
573
	"Binary chop" debugging:
574
		(This is primarily useful for regressions that occurred in a previous
575
		revision, which was committed without running all the tests)
576
		svn up -r <rev>; make inputs/.TNRS/reinstall; make schemas/public/reinstall; make <failed-test>.xml
577
	.htaccess:
578
		mod_rewrite:
579
			**IMPORTANT**: whenever you change the DirectorySlash setting for a
580
				directory, you *must* clear your browser's cache to ensure that
581
				a cached redirect is not used. this is because RewriteRule
582
				redirects are (by default) temporary, but DirectorySlash
583
				redirects are permanent.
584
				for Firefox:
585
					press Cmd+Shift+Delete
586
					check only Cache
587
					press Enter or click Clear Now
588

    
589
WinMerge setup:
590
	In a Windows VM:
591
	Install WinMerge from <http://winmerge.org/>
592
	Open WinMerge
593
	Go to Edit > Options and click Compare in the left sidebar
594
	Enable "Moved block detection", as described at
595
		<http://manual.winmerge.org/Configuration.html#d0e5892>.
596
	Set Whitespace to Ignore change, as described at
597
		<http://manual.winmerge.org/Configuration.html#d0e5758>.
598

    
599
Documentation:
600
	To generate a Redmine-formatted list of steps for column-based import:
601
		On local machine:
602
		make schemas/public/reinstall
603
		make inputs/ACAD/Specimen/logs/steps.by_col.log.sql
604
	To import and scrub just the test taxonomic names:
605
		ssh -t vegbiendev.nceas.ucsb.edu exec sudo su - aaronmk
606
		inputs/test_taxonomic_names/test_scrub
607

    
608
General:
609
	To see a program's description, read its top-of-file comment
610
	To see a program's usage, run it without arguments
611
	To remake a directory: make <dir>/remake
612
	To remake a file: make <file>-remake
(6-6/11)