Project

General

Profile

1 702 aaronmk
Installation:
2 8458 aaronmk
	Check out svn: svn co https://code.nceas.ucsb.edu/code/projects/bien
3
	cd bien/
4
	Install: make install
5 12226 aaronmk
		**WARNING**: This will delete the public schema of your VegBIEN DB!
6 8458 aaronmk
	Uninstall: make uninstall
7 12226 aaronmk
		**WARNING**: This will delete your entire VegBIEN DB!
8 8458 aaronmk
		This includes all archived imports and staging tables.
9 554 aaronmk
10 11515 aaronmk
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 12011 aaronmk
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 11967 aaronmk
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 7287 aaronmk
Single datasource import:
27 11516 aaronmk
	Follow the steps under Connecting to vegbiendev above
28 10804 aaronmk
	(Re)import and scrub: make inputs/<datasrc>/reimport_scrub by_col=1 &
29
	(Re)import only: make inputs/<datasrc>/reimport by_col=1 &
30 8458 aaronmk
	Note that these commands also work if the datasource is not yet imported
31 11263 aaronmk
	Remake analytical DB: see Full database import > To remake analytical DB
32 7287 aaronmk
33
Full database import:
34 12226 aaronmk
	**WARNING**: You must perform *every single* step listed below, to avoid
35 9499 aaronmk
		breaking column-based import
36 12011 aaronmk
	**WARNING**: always start with a clean shell, as described above under
37
		"Notes on running programs"
38 11516 aaronmk
	Follow the steps under Connecting to vegbiendev above
39
	Follow the steps under Connecting to vegbiendev above, using jupiter instead
40 9883 aaronmk
	On jupiter: svn up --force
41 8458 aaronmk
	On local machine:
42 9887 aaronmk
		do steps under Maintenance > "to synchronize vegbiendev, jupiter, and
43 11258 aaronmk
			your local machine"
44 8458 aaronmk
		make inputs/upload
45 10025 aaronmk
		make inputs/upload live=1
46 12027 aaronmk
		make test by_col=1 # runtime: 20 min ("4m46.108s" + ("21:50:43" - "21:37:09")) @starscream
47 10549 aaronmk
			if you encounter errors, they are most likely related to the
48
				PostgreSQL error parsing in /lib/sql.py parse_exception()
49 8458 aaronmk
			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 10025 aaronmk
	make inputs/download live=1
55 8458 aaronmk
	For each newly-uploaded datasource above: make inputs/<datasrc>/reinstall
56
	Update the auxiliary schemas: make schemas/reinstall
57 12226 aaronmk
		**WARNING**: requires sudo access!
58 8458 aaronmk
		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 9970 aaronmk
	Make sure there is at least 300GB of disk space on /: df -h
64 10603 aaronmk
		The import schema is 230GB, and may use additional space for temp tables
65 8458 aaronmk
		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 9531 aaronmk
	set -o ignoreeof #prevent Ctrl+D from exiting `screen` to keep attached jobs
72 9532 aaronmk
	unset TMOUT # TMOUT causes screen to exit even with background processes
73
	unset version
74 10579 aaronmk
	Start column-based import: . bin/import_all
75
		To use row-based import: . bin/import_all by_col=
76 8458 aaronmk
		To stop all running imports: . bin/stop_imports
77 12226 aaronmk
		**WARNING**: Do NOT run import_all in the background, or the jobs it
78
			creates won't be owned by your shell.
79 8458 aaronmk
		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 12026 aaronmk
		To view progress:
82
			tail inputs/{.,}*/*/logs/$version.log.sql
83 10850 aaronmk
	Wait (4 days) for the import to finish
84 8458 aaronmk
	To recover from a closed terminal window: screen -r
85 10583 aaronmk
	To restart an aborted import for a specific table:
86
		export version=<version>
87 11800 aaronmk
		(set -o errexit; make inputs/<datasrc>/<table>/import_scrub by_col=1 continue=1; make inputs/<datasrc>/publish) &
88 10588 aaronmk
		bin/after_import $! & # $! can also be obtained from `jobs -l`
89 8458 aaronmk
	Get $version: echo $version
90
	Set $version in all vegbiendev terminals: export version=<version>
91 10557 aaronmk
	When there are no more running jobs, exit the screen
92 10025 aaronmk
	Upload logs (run on vegbiendev): make inputs/upload live=1
93
	On local machine: make inputs/download-logs live=1
94 8458 aaronmk
	In PostgreSQL:
95 11568 aaronmk
		Go to wiki.vegpath.org/VegBIEN_contents
96 11728 aaronmk
		Get the # observations
97
		Get the # datasources
98
		Get the # datasources with observations
99 11892 aaronmk
		in the r# schema:
100 11569 aaronmk
		Check that analytical_stem contains [# observations] rows
101 12148 aaronmk
		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 11568 aaronmk
		Check that provider_count contains [# datasources with observations]
106
			rows with dataset="(total)" (at the top when the table is unsorted)
107 9492 aaronmk
	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 10784 aaronmk
				Make the changes in the DB's TNRS and public schemas
114
				rm=1 inputs/.TNRS/schema.sql.run export_
115 9492 aaronmk
				make schemas/remake
116 10785 aaronmk
				inputs/test_taxonomic_names/test_scrub # re-run TNRS
117 10784 aaronmk
				rm=1 inputs/.TNRS/data.sql.run export_
118 9492 aaronmk
				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 8458 aaronmk
	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 9498 aaronmk
	Publish the new import:
131 12226 aaronmk
		**WARNING**: Before proceeding, be sure you have done *every single*
132 9498 aaronmk
			verification step listed above. Otherwise, a previous valid import
133
			could incorrectly be overwritten with a broken one.
134 10864 aaronmk
		make schemas/$version/publish # runtime: 1 min ("real 1m10.451s")
135 8458 aaronmk
	unset version
136 10027 aaronmk
	make backups/upload live=1
137 11897 aaronmk
	on local machine:
138
		make backups/vegbien.$version.backup/download live=1
139
			# download backup to local machine
140 8458 aaronmk
	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 11573 aaronmk
		On local machine:
147 8458 aaronmk
		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 11573 aaronmk
		Commit: svn ci -m 'inputs/import.stats.xls: updated import times'
163 10885 aaronmk
	Running individual steps separately:
164 9497 aaronmk
	To run TNRS:
165 9996 aaronmk
		To use an import other than public: export version=<version>
166 9995 aaronmk
		make scrub &
167 8458 aaronmk
		To view progress:
168
			tail -100 inputs/.TNRS/tnrs/logs/tnrs.make.log.sql
169 9497 aaronmk
	To remake analytical DB:
170 9996 aaronmk
		To use an import other than public: export version=<version>
171 11089 aaronmk
		bin/make_analytical_db & # runtime: 13 h ("12:43:57elapsed")
172 8458 aaronmk
		To view progress:
173 10600 aaronmk
			tail -150 inputs/analytical_db/logs/make_analytical_db.log.sql
174 8458 aaronmk
	To back up DB (staging tables and last import):
175 10578 aaronmk
		To use an import *other than public*: export version=<version>
176 10743 aaronmk
		make backups/TNRS.backup-remake &
177 10577 aaronmk
		dump_opts=--exclude-schema=public make backups/vegbien.$version.backup/test &
178 10578 aaronmk
			If after renaming to public, instead set dump_opts='' and replace
179
			$version with the appropriate revision
180 10744 aaronmk
		make backups/upload live=1
181 3381 aaronmk
182 1773 aaronmk
Datasource setup:
183 11516 aaronmk
	On local machine:
184 11090 aaronmk
	Example steps for a datasource: wiki.vegpath.org/Import_process_for_Madidi
185 8469 aaronmk
	umask ug=rwx,o= # prevent files from becoming web-accessible
186 8458 aaronmk
	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 11018 aaronmk
	For a new-style datasource (one containing a ./run runscript):
191 11019 aaronmk
		"cp" -f inputs/.NCBI/{Makefile,run,table.run} inputs/<datasrc>/
192 8458 aaronmk
	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 9494 aaronmk
				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 8458 aaronmk
				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 8466 aaronmk
			(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 8458 aaronmk
		Add <table> to inputs/<datasrc>/import_order.txt before other tables
241
			that depend on it
242 11018 aaronmk
		For a new-style datasource:
243
			"cp" -f inputs/.NCBI/nodes/run inputs/<datasrc>/<table>/
244
			inputs/<datasrc>/<table>/run
245 8458 aaronmk
	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 11018 aaronmk
		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 8458 aaronmk
			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 10025 aaronmk
			make inputs/upload live=1
304 8458 aaronmk
		On vegbiendev:
305
			svn up
306
			make inputs/download
307 10025 aaronmk
			make inputs/download live=1
308 8458 aaronmk
			Follow the steps under Install the staging tables above
309 1773 aaronmk
310 10884 aaronmk
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 12226 aaronmk
		**WARNING**: pay careful attention to all files that will be deleted or
323 10884 aaronmk
			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 11259 aaronmk
				overwrite=1 bin/sync_upload --size-only
330 10884 aaronmk
					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 11259 aaronmk
				overwrite=1 swap=1 bin/sync_upload --size-only
335 10884 aaronmk
					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 12226 aaronmk
			**WARNING**: this will overwrite all your user's settings!
342 11516 aaronmk
			on your machine:
343 10884 aaronmk
			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 11940 aaronmk
				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 10884 aaronmk
				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 11516 aaronmk
		On local machine:
363 11686 aaronmk
		overwrite=1 src=/ dest=/Volumes/Time\ Machine\ Backups/ sudo -E put Library/PostgreSQL/9.3/data/
364 11684 aaronmk
			then rerun with l=1 ...
365 10884 aaronmk
		pg_ctl. stop # stop the PostgreSQL server
366 11686 aaronmk
		overwrite=1 src=/ dest=/Volumes/Time\ Machine\ Backups/ sudo -E put Library/PostgreSQL/9.3/data/
367 11685 aaronmk
			then rerun with l=1 ...
368 10884 aaronmk
		pg_ctl. start # start the PostgreSQL server
369 11866 aaronmk
	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 12022 aaronmk
	to back up the version history:
376
		on vegbiendev:
377 12127 aaronmk
		svnsync sync file:///home/aaronmk/Dropbox/docs/BIEN/svn_repo/ # initial runtime: 1.5 h ("08:21:38" - "06:45:26") @vegbiendev
378 12128 aaronmk
		(cd ~/Dropbox/docs/BIEN/git/; git svn fetch)
379 12127 aaronmk
		overwrite=1        src=~ dest='aaronmk@jupiter:~' put Dropbox/docs/BIEN/svn_repo/ # runtime: 1 min ("1:05.08")
380 12023 aaronmk
			then rerun with l=1 ...
381
		on local machine:
382 12127 aaronmk
		overwrite=1 swap=1 src=~ dest='aaronmk@jupiter:~' put Dropbox/docs/BIEN/svn_repo/ # runtime: 30 s ("36.19")
383 12023 aaronmk
			then rerun with l=1 ...
384 10884 aaronmk
	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 11516 aaronmk
			On local machine:
389 10884 aaronmk
			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 11516 aaronmk
		Follow the steps under Connecting to vegbiendev above
404 10884 aaronmk
		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 11516 aaronmk
		Follow the steps under Connecting to vegbiendev above
413 10884 aaronmk
		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 11516 aaronmk
		Follow the steps under Connecting to vegbiendev above
423 10884 aaronmk
		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 11516 aaronmk
		On local machine:
429 10884 aaronmk
		Download: make inputs/download-logs live=1
430
431 6484 aaronmk
Datasource refreshing:
432 8458 aaronmk
	VegBank:
433 11516 aaronmk
		Follow the steps under Connecting to vegbiendev above
434 8458 aaronmk
		make inputs/VegBank/vegbank.sql-remake
435
		make inputs/VegBank/reinstall quiet=1 &
436 6484 aaronmk
437 702 aaronmk
Schema changes:
438 11516 aaronmk
	On local machine:
439 8458 aaronmk
	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 12226 aaronmk
		**WARNING**: This will delete the public schema of your VegBIEN DB!
448 12227 aaronmk
	If needed, reinstall staging tables:
449 8837 aaronmk
		On local machine:
450 8840 aaronmk
			sudo -E -u postgres psql <<<'ALTER DATABASE vegbien RENAME TO vegbien_prev'
451 8845 aaronmk
			make db
452 8837 aaronmk
			. bin/reinstall_all
453
			Fix any bugs and retry until no errors
454 8846 aaronmk
			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 8842 aaronmk
			sudo -E -u postgres psql <<<'DROP DATABASE vegbien_prev'
458 8841 aaronmk
		On vegbiendev: repeat the above steps
459 12226 aaronmk
			**WARNING**: Do not run this until reinstall_all runs successfully
460
			on the local machine, or the live DB may be unrestorable!
461 8458 aaronmk
	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 203 aaronmk
528 1459 aaronmk
Testing:
529 8458 aaronmk
	On a development machine, you should put the following in your .profile:
530 8469 aaronmk
		umask ug=rwx,o= # prevent files from becoming web-accessible
531 8458 aaronmk
		export log= n=2
532 11985 aaronmk
	For development machine specs, see /planning/resources/dev_machine.specs/
533 11516 aaronmk
	On local machine:
534 8458 aaronmk
	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 702 aaronmk
546 7183 aaronmk
Debugging:
547 8458 aaronmk
	"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 8470 aaronmk
	.htaccess:
552
		mod_rewrite:
553 12226 aaronmk
			**IMPORTANT**: whenever you change the DirectorySlash setting for a
554 8471 aaronmk
				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 8470 aaronmk
				for Firefox:
559
					press Cmd+Shift+Delete
560
					check only Cache
561
					press Enter or click Clear Now
562 7183 aaronmk
563 3783 aaronmk
WinMerge setup:
564 11516 aaronmk
	In a Windows VM:
565 8458 aaronmk
	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 3783 aaronmk
573 3133 aaronmk
Documentation:
574 8458 aaronmk
	To generate a Redmine-formatted list of steps for column-based import:
575 11516 aaronmk
		On local machine:
576 8458 aaronmk
		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 11516 aaronmk
		Follow the steps under Connecting to vegbiendev above
580 8458 aaronmk
		inputs/test_taxonomic_names/test_scrub
581 3133 aaronmk
582 702 aaronmk
General:
583 8458 aaronmk
	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