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 vegbiendev.nceas.ucsb.edu
12
	sudo su - aaronmk
13
	cd /home/bien/svn # should happen automatically at login
14

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

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

    
26
Single datasource import:
27
	Follow the steps under Connecting to vegbiendev above
28
	(Re)import and scrub: make inputs/<datasrc>/reimport_scrub by_col=1 &
29
	(Re)import only: make inputs/<datasrc>/reimport by_col=1 &
30
	Note that these commands also work if the datasource is not yet imported
31
	Remake analytical DB: see Full database import > To remake analytical DB
32

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

    
182
Datasource setup:
183
	On local machine:
184
	Example steps for a datasource: wiki.vegpath.org/Import_process_for_Madidi
185
	umask ug=rwx,o= # prevent files from becoming web-accessible
186
	Add a new datasource: make inputs/<datasrc>/add
187
		<datasrc> may not contain spaces, and should be abbreviated.
188
		If the datasource is a herbarium, <datasrc> should be the herbarium code
189
			as defined by the Index Herbariorum <http://sweetgum.nybg.org/ih/>
190
	For a new-style datasource (one containing a ./run runscript):
191
		"cp" -f inputs/.NCBI/{Makefile,run,table.run} inputs/<datasrc>/
192
	For MySQL inputs (exports and live DB connections):
193
		For .sql exports:
194
			Place the original .sql file in _src/ (*not* in _MySQL/)
195
			Follow the steps starting with Install the staging tables below.
196
				This is for an initial sync to get the file onto vegbiendev.
197
			On vegbiendev:
198
				Create a database for the MySQL export in phpMyAdmin
199
				Give the bien user all database-specific privileges *except*
200
					UPDATE, DELETE, ALTER, DROP. This prevents bugs in the
201
					import scripts from accidentally deleting data.
202
				bin/mysql_bien database <inputs/<datasrc>/_src/export.sql &
203
		mkdir inputs/<datasrc>/_MySQL/
204
		cp -p lib/MySQL.{data,schema}.sql.make inputs/<datasrc>/_MySQL/
205
		Edit _MySQL/*.make for the DB connection
206
			For a .sql export, use server=vegbiendev and --user=bien
207
		Skip the Add input data for each table section
208
	For MS Access databases:
209
		Place the .mdb or .accdb file in _src/
210
		Download and install Access To PostgreSQL from
211
			http://www.bullzip.com/download.php
212
		Use Access To PostgreSQL to export the database:
213
			Export just the tables/indexes to inputs/<datasrc>/<file>.schema.sql
214
			Export just the data to inputs/<datasrc>/<file>.data.sql
215
		In <file>.schema.sql, make the following changes:
216
			Replace text "BOOLEAN" with "/*BOOLEAN*/INTEGER"
217
			Replace text "DOUBLE PRECISION NULL" with "DOUBLE PRECISION"
218
		Skip the Add input data for each table section
219
	Add input data for each table present in the datasource:
220
		For .sql exports, you must use the name of the table in the DB export
221
		For CSV files, you can use any name. It's recommended to use a table
222
			name from <https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/VegCSV#Suggested-table-names>
223
		Note that if this table will be joined together with another table, its
224
			name must end in ".src"
225
		make inputs/<datasrc>/<table>/add
226
			Important: DO NOT just create an empty directory named <table>!
227
				This command also creates necessary subdirs, such as logs/.
228
		If the table is in a .sql export: make inputs/<datasrc>/<table>/install
229
			Otherwise, place the CSV(s) for the table in
230
			inputs/<datasrc>/<table>/ OR place a query joining other tables
231
			together in inputs/<datasrc>/<table>/create.sql
232
		Important: When exporting relational databases to CSVs, you MUST ensure
233
			that embedded quotes are escaped by doubling them, *not* by
234
			preceding them with a "\" as is the default in phpMyAdmin
235
		If there are multiple part files for a table, and the header is repeated
236
			in each part, make sure each header is EXACTLY the same.
237
			(If the headers are not the same, the CSV concatenation script
238
			assumes the part files don't have individual headers and treats the
239
			subsequent headers as data rows.)
240
		Add <table> to inputs/<datasrc>/import_order.txt before other tables
241
			that depend on it
242
		For a new-style datasource:
243
			"cp" -f inputs/.NCBI/nodes/run inputs/<datasrc>/<table>/
244
			inputs/<datasrc>/<table>/run
245
	Install the staging tables:
246
		make inputs/<datasrc>/reinstall quiet=1 &
247
		For a MySQL .sql export:
248
			At prompt "[you]@vegbiendev's password:", enter your password
249
			At prompt "Enter password:", enter the value in config/bien_password
250
		To view progress: tail -f inputs/<datasrc>/<table>/logs/install.log.sql
251
		View the logs: tail -n +1 inputs/<datasrc>/*/logs/install.log.sql
252
			tail provides a header line with the filename
253
			+1 starts at the first line, to show the whole file
254
		For every file with an error 'column "..." specified more than once':
255
			Add a header override file "+header.<ext>" in <table>/:
256
				Note: The leading "+" should sort it before the flat files.
257
					"_" unfortunately sorts *after* capital letters in ASCII.
258
				Create a text file containing the header line of the flat files
259
				Add an ! at the beginning of the line
260
					This signals cat_csv that this is a header override.
261
				For empty names, use their 0-based column # (by convention)
262
				For duplicate names, add a distinguishing suffix
263
				For long names that collided, rename them to <= 63 chars long
264
				Do NOT make readability changes in this step; that is what the
265
					map spreadsheets (below) are for.
266
				Save
267
		If you made any changes, re-run the install command above
268
	Auto-create the map spreadsheets: make inputs/<datasrc>/
269
	Map each table's columns:
270
		In each <table>/ subdir, for each "via map" map.csv:
271
			Open the map in a spreadsheet editor
272
			Open the "core map" /mappings/Veg+-VegBIEN.csv
273
			In each row of the via map, set the right column to a value from the
274
				left column of the core map
275
			Save
276
		Regenerate the derived maps: make inputs/<datasrc>/
277
	Accept the test cases:
278
		For a new-style datasource:
279
			inputs/<datasrc>/run
280
			svn di inputs/<datasrc>/*/test.xml.ref
281
			If you get errors, follow the steps for old-style datasources below
282
		For an old-style datasource:
283
			make inputs/<datasrc>/test
284
			When prompted to "Accept new test output", enter y and press ENTER
285
			If you instead get errors, do one of the following for each one:
286
			-	If the error was due to a bug, fix it
287
			-	Add a SQL function that filters or transforms the invalid data
288
			-	Make an empty mapping for the columns that produced the error.
289
				Put something in the Comments column of the map spreadsheet to
290
				prevent the automatic mapper from auto-removing the mapping.
291
			When accepting tests, it's helpful to use WinMerge
292
				(see WinMerge setup below for configuration)
293
		make inputs/<datasrc>/test by_col=1
294
			If you get errors this time, this always indicates a bug, usually in
295
				the VegBIEN unique constraints or column-based import itself
296
	Add newly-created files: make inputs/<datasrc>/add
297
	Commit: svn ci -m "Added inputs/<datasrc>/" inputs/<datasrc>/
298
	Update vegbiendev:
299
		On jupiter: svn up
300
		On local machine:
301
			./fix_perms
302
			make inputs/upload
303
			make inputs/upload live=1
304
		On vegbiendev:
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
		on vegbiendev: make postgres-Linux
321
	to synchronize vegbiendev, jupiter, and your local machine:
322
		**WARNING**: pay careful attention to all files that will be deleted or
323
			overwritten!
324
		install put if needed:
325
			download https://uutils.googlecode.com/svn/trunk/bin/put to ~/bin/ and `chmod +x` it
326
		when changes are made on vegbiendev:
327
			on all machines: ./fix_perms
328
			on vegbiendev, upload:
329
				overwrite=1 bin/sync_upload --size-only
330
					then rerun with l=1 ...
331
			on your machine, download:
332
				overwrite=1 swap=1 src=. dest='aaronmk@jupiter:~/bien' put --exclude=.svn inputs/VegBIEN/TWiki
333
					then rerun with l=1 ...
334
				overwrite=1 swap=1 bin/sync_upload --size-only
335
					then rerun with l=1 ...
336
				make backups/TNRS.backup/download live=1
337
				overwrite=1 sync_remote_url=~/Dropbox/svn/ bin/sync_upload --existing --size-only # just update mtimes/perms
338
					then rerun with l=1 ...
339
	to synchronize a Mac's settings with my testing machine's:
340
		download:
341
			**WARNING**: this will overwrite all your user's settings!
342
			on your machine:
343
			overwrite=1 swap=1 sync_local_dir=~/Library/ sync_remote_subdir=Library/ bin/sync_upload --exclude="/Saved Application State"
344
				then rerun with l=1 ...
345
		upload:
346
			do step when changes are made on vegbiendev > on your machine, download
347
			on jupiter: (cd ~/Dropbox/svn/; up)
348
			on your machine:
349
				rm ~/'Library/Thunderbird/Profiles/9oo8rcyn.default/ImapMail/imap.googlemail.com/[Gmail].sbd/Spam'
350
					# remove the downloaded Spam folder, because spam e-mails often contain viruses that would trigger clamscan
351
				overwrite=1 del= sync_local_dir=~/Dropbox/svn/ sync_remote_subdir=Dropbox/svn/ bin/sync_upload --size-only # just update mtimes
352
					then rerun with l=1 ...
353
				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"
354
					then rerun with l=1 ...
355
				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"
356
					then rerun with l=1 ...
357
		upload just the VirtualBox VMs:
358
			on your machine:
359
				overwrite=1      sync_local_dir=~              sync_remote_subdir=             bin/sync_upload ~/"VirtualBox VMs/**"
360
					then rerun with l=1 ...
361
	to backup files not in Time Machine:
362
		On local machine:
363
		overwrite=1 src=/ dest=/Volumes/Time\ Machine\ Backups/ sudo -E put Library/PostgreSQL/9.3/data/
364
			then rerun with l=1 ...
365
		pg_ctl. stop # stop the PostgreSQL server
366
		overwrite=1 src=/ dest=/Volumes/Time\ Machine\ Backups/ sudo -E put Library/PostgreSQL/9.3/data/
367
			then rerun with l=1 ...
368
		pg_ctl. start # start the PostgreSQL server
369
	to backup e-mails:
370
		on local machine:
371
		/Applications/gmvault-v1.8.1-beta/bin/gmvault sync --multiple-db-owner --type quick aaronmk.nceas@gmail.com
372
		/Applications/gmvault-v1.8.1-beta/bin/gmvault sync --multiple-db-owner --type quick aaronmk@nceas.ucsb.edu
373
		open Thunderbird
374
		click the All Mail folder for each account and wait for it to download the e-mails in it
375
	to back up the version history:
376
		on vegbiendev:
377
		svnsync sync file:///home/aaronmk/Dropbox/docs/BIEN/svn_repo/ # initial runtime: 1.5 h ("08:21:38" - "06:45:26") @vegbiendev
378
		(cd ~/Dropbox/docs/BIEN/git/; git svn fetch)
379
		overwrite=1        src=~ dest='aaronmk@jupiter:~' put Dropbox/docs/BIEN/svn_repo/ # runtime: 1 min ("1:05.08")
380
			then rerun with l=1 ...
381
		on local machine:
382
		overwrite=1 swap=1 src=~ dest='aaronmk@jupiter:~' put Dropbox/docs/BIEN/svn_repo/ # runtime: 30 s ("36.19")
383
			then rerun with l=1 ...
384
	VegCore data dictionary:
385
		Regularly, or whenever the VegCore data dictionary page
386
			(https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/VegCore)
387
			is changed, regenerate mappings/VegCore.csv:
388
			On local machine:
389
			make mappings/VegCore.htm-remake; make mappings/
390
			svn di mappings/VegCore.tables.redmine
391
			If there are changes, update the data dictionary's Tables section
392
			When moving terms, check that no terms were lost: svn di
393
			svn ci -m 'mappings/VegCore.htm: regenerated from wiki'
394
	Important: Whenever you install a system update that affects PostgreSQL or
395
		any of its dependencies, such as libc, you should restart the PostgreSQL
396
		server. Otherwise, you may get strange errors like "the database system
397
		is in recovery mode" which go away upon reimport, or you may not be able
398
		to access the database as the postgres superuser. This applies to both
399
		Linux and Mac OS X.
400

    
401
Backups:
402
	Archived imports:
403
		Follow the steps under Connecting to vegbiendev above
404
		Back up: make backups/<version>.backup &
405
			Note: To back up the last import, you must archive it first:
406
				make schemas/rotate
407
		Test: make -s backups/<version>.backup/test &
408
		Restore: make backups/<version>.backup/restore &
409
		Remove: make backups/<version>.backup/remove
410
		Download: make backups/<version>.backup/download
411
	TNRS cache:
412
		Follow the steps under Connecting to vegbiendev above
413
		Back up: make backups/TNRS.backup-remake &
414
			runtime: 3 min ("real 2m48.859s")
415
		Restore:
416
			yes|make inputs/.TNRS/uninstall
417
			make backups/TNRS.backup/restore &
418
				runtime: 5.5 min ("real 5m35.829s")
419
			yes|make schemas/public/reinstall
420
				Must come after TNRS restore to recreate tnrs_input_name view
421
	Full DB:
422
		Follow the steps under Connecting to vegbiendev above
423
		Back up: make backups/vegbien.<version>.backup &
424
		Test: make -s backups/vegbien.<version>.backup/test &
425
		Restore: make backups/vegbien.<version>.backup/restore &
426
		Download: make backups/vegbien.<version>.backup/download
427
	Import logs:
428
		On local machine:
429
		Download: make inputs/download-logs live=1
430

    
431
Datasource refreshing:
432
	VegBank:
433
		Follow the steps under Connecting to vegbiendev above
434
		make inputs/VegBank/vegbank.sql-remake
435
		make inputs/VegBank/reinstall quiet=1 &
436

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

    
528
Testing:
529
	On a development machine, you should put the following in your .profile:
530
		umask ug=rwx,o= # prevent files from becoming web-accessible
531
		export log= n=2
532
	For development machine specs, see /planning/resources/dev_machine.specs/
533
	On local machine:
534
	Mapping process: make test
535
		Including column-based import: make test by_col=1
536
			If the row-based and column-based imports produce different inserted
537
			row counts, this usually means that a table is underconstrained
538
			(the unique indexes don't cover all possible rows).
539
			This can occur if you didn't use COALESCE(field, null_value) around
540
			a nullable field in a unique index. See sql_gen.null_sentinels for
541
			the appropriate null value to use.
542
	Map spreadsheet generation: make remake
543
	Missing mappings: make missing_mappings
544
	Everything (for most complete coverage): make test-all
545

    
546
Debugging:
547
	"Binary chop" debugging:
548
		(This is primarily useful for regressions that occurred in a previous
549
		revision, which was committed without running all the tests)
550
		svn up -r <rev>; make inputs/.TNRS/reinstall; make schemas/public/reinstall; make <failed-test>.xml
551
	.htaccess:
552
		mod_rewrite:
553
			**IMPORTANT**: whenever you change the DirectorySlash setting for a
554
				directory, you *must* clear your browser's cache to ensure that
555
				a cached redirect is not used. this is because RewriteRule
556
				redirects are (by default) temporary, but DirectorySlash
557
				redirects are permanent.
558
				for Firefox:
559
					press Cmd+Shift+Delete
560
					check only Cache
561
					press Enter or click Clear Now
562

    
563
WinMerge setup:
564
	In a Windows VM:
565
	Install WinMerge from <http://winmerge.org/>
566
	Open WinMerge
567
	Go to Edit > Options and click Compare in the left sidebar
568
	Enable "Moved block detection", as described at
569
		<http://manual.winmerge.org/Configuration.html#d0e5892>.
570
	Set Whitespace to Ignore change, as described at
571
		<http://manual.winmerge.org/Configuration.html#d0e5758>.
572

    
573
Documentation:
574
	To generate a Redmine-formatted list of steps for column-based import:
575
		On local machine:
576
		make schemas/public/reinstall
577
		make inputs/ACAD/Specimen/logs/steps.by_col.log.sql
578
	To import and scrub just the test taxonomic names:
579
		Follow the steps under Connecting to vegbiendev above
580
		inputs/test_taxonomic_names/test_scrub
581

    
582
General:
583
	To see a program's description, read its top-of-file comment
584
	To see a program's usage, run it without arguments
585
	To remake a directory: make <dir>/remake
586
	To remake a file: make <file>-remake
(6-6/11)