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
		WARNING: This will delete the current public schema of your VegBIEN DB!
6
	Uninstall: make uninstall
7
		WARNING: This will delete your entire VegBIEN DB!
8
		This includes all archived imports and staging tables.
9 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 7287 aaronmk
Single datasource import:
16 11516 aaronmk
	Follow the steps under Connecting to vegbiendev above
17 10804 aaronmk
	(Re)import and scrub: make inputs/<datasrc>/reimport_scrub by_col=1 &
18
	(Re)import only: make inputs/<datasrc>/reimport by_col=1 &
19 8458 aaronmk
	Note that these commands also work if the datasource is not yet imported
20 11263 aaronmk
	Remake analytical DB: see Full database import > To remake analytical DB
21 7287 aaronmk
22
Full database import:
23 9499 aaronmk
	WARNING: You must perform *every single* step listed below, to avoid
24
		breaking column-based import
25 11915 aaronmk
	WARNING: always start with a clean shell, which does not have changes to the
26
		env vars. (there have been bugs that went away after closing and
27
		reopening the terminal window.) note that running `exec bash` is not
28
		sufficient to *reset* the env vars.
29 11516 aaronmk
	Follow the steps under Connecting to vegbiendev above
30
	Follow the steps under Connecting to vegbiendev above, using jupiter instead
31 9883 aaronmk
	On jupiter: svn up --force
32 8458 aaronmk
	On local machine:
33 9887 aaronmk
		do steps under Maintenance > "to synchronize vegbiendev, jupiter, and
34 11258 aaronmk
			your local machine"
35 8458 aaronmk
		make inputs/upload
36 10025 aaronmk
		make inputs/upload live=1
37 8458 aaronmk
		make test by_col=1
38 10549 aaronmk
			if you encounter errors, they are most likely related to the
39
				PostgreSQL error parsing in /lib/sql.py parse_exception()
40 8458 aaronmk
			See note under Testing below
41
	On vegbiendev:
42
	Ensure there are no local modifications: svn st
43
	svn up
44
	make inputs/download
45 10025 aaronmk
	make inputs/download live=1
46 8458 aaronmk
	For each newly-uploaded datasource above: make inputs/<datasrc>/reinstall
47
	Update the auxiliary schemas: make schemas/reinstall
48 11787 aaronmk
		WARNING: requires sudo access!
49 8458 aaronmk
		The public schema will be installed separately by the import process
50
	Delete imports before the last so they won't bloat the full DB backup:
51
		make backups/vegbien.<version>.backup/remove
52
		To keep a previous import other than the public schema:
53
			export dump_opts='--exclude-schema=public --exclude-schema=<version>'
54 9970 aaronmk
	Make sure there is at least 300GB of disk space on /: df -h
55 10603 aaronmk
		The import schema is 230GB, and may use additional space for temp tables
56 8458 aaronmk
		To free up space, remove backups that have been archived on jupiter:
57
			List backups/ to view older backups
58
			Check their MD5 sums using the steps under On jupiter below
59
			Remove these backups
60
	screen
61
	Press ENTER
62 9531 aaronmk
	set -o ignoreeof #prevent Ctrl+D from exiting `screen` to keep attached jobs
63 9532 aaronmk
	unset TMOUT # TMOUT causes screen to exit even with background processes
64
	unset version
65 10579 aaronmk
	Start column-based import: . bin/import_all
66
		To use row-based import: . bin/import_all by_col=
67 8458 aaronmk
		To stop all running imports: . bin/stop_imports
68
		WARNING: Do NOT run import_all in the background, or the jobs it creates
69
			won't be owned by your shell.
70
		Note that import_all will take up to an hour to import the NCBI backbone
71
			and other metadata before returning control to the shell.
72 10850 aaronmk
	Wait (4 days) for the import to finish
73 8458 aaronmk
	To recover from a closed terminal window: screen -r
74 10583 aaronmk
	To restart an aborted import for a specific table:
75
		export version=<version>
76 11800 aaronmk
		(set -o errexit; make inputs/<datasrc>/<table>/import_scrub by_col=1 continue=1; make inputs/<datasrc>/publish) &
77 10588 aaronmk
		bin/after_import $! & # $! can also be obtained from `jobs -l`
78 8458 aaronmk
	Get $version: echo $version
79
	Set $version in all vegbiendev terminals: export version=<version>
80 10557 aaronmk
	When there are no more running jobs, exit the screen
81 10025 aaronmk
	Upload logs (run on vegbiendev): make inputs/upload live=1
82
	On local machine: make inputs/download-logs live=1
83 8458 aaronmk
	In PostgreSQL:
84 11568 aaronmk
		Go to wiki.vegpath.org/VegBIEN_contents
85 11728 aaronmk
		Get the # observations
86
		Get the # datasources
87
		Get the # datasources with observations
88 11892 aaronmk
		in the r# schema:
89 11569 aaronmk
		Check that analytical_stem contains [# observations] rows
90 11568 aaronmk
		Check that source contains [# datasources] rows up through XAL
91
		Check that provider_count contains [# datasources with observations]
92
			rows with dataset="(total)" (at the top when the table is unsorted)
93 9492 aaronmk
	Check that TNRS ran successfully:
94
		tail -100 inputs/.TNRS/tnrs/logs/tnrs.make.log.sql
95
		If the log ends in an AssertionError
96
			"assert sql.table_col_names(db, table) == header":
97
			Figure out which TNRS CSV columns have changed
98
			On local machine:
99 10784 aaronmk
				Make the changes in the DB's TNRS and public schemas
100
				rm=1 inputs/.TNRS/schema.sql.run export_
101 9492 aaronmk
				make schemas/remake
102 10785 aaronmk
				inputs/test_taxonomic_names/test_scrub # re-run TNRS
103 10784 aaronmk
				rm=1 inputs/.TNRS/data.sql.run export_
104 9492 aaronmk
				Commit
105
			On vegbiendev:
106
				If dropping a column, save the dependent views
107
				Make the same changes in the live TNRS.tnrs table on vegbiendev
108
				If dropping a column, recreate the dependent views
109
				Restart the TNRS client: make scrub by_col=1 &
110 8458 aaronmk
	tail inputs/{.,}*/*/logs/$version.log.sql
111
	In the output, search for "Command exited with non-zero status"
112
	For inputs that have this, fix the associated bug(s)
113
	If many inputs have errors, discard the current (partial) import:
114
		make schemas/$version/uninstall
115
	Otherwise, continue
116 9498 aaronmk
	Publish the new import:
117
		WARNING: Before proceeding, be sure you have done *every single*
118
			verification step listed above. Otherwise, a previous valid import
119
			could incorrectly be overwritten with a broken one.
120 10864 aaronmk
		make schemas/$version/publish # runtime: 1 min ("real 1m10.451s")
121 8458 aaronmk
	unset version
122 10027 aaronmk
	make backups/upload live=1
123 11897 aaronmk
	on local machine:
124
		make backups/vegbien.$version.backup/download live=1
125
			# download backup to local machine
126 8458 aaronmk
	On jupiter:
127
		cd /data/dev/aaronmk/bien/backups
128
		For each newly-archived backup:
129
			make -s <backup>.md5/test
130
			Check that "OK" is printed next to the filename
131
	If desired, record the import times in inputs/import.stats.xls:
132 11573 aaronmk
		On local machine:
133 8458 aaronmk
		Open inputs/import.stats.xls
134
		If the rightmost import is within 5 columns of column IV:
135
			Copy the current tab to <leftmost-date>~<rightmost-date>
136
			Remove the previous imports from the current tab because they are
137
				now in the copied tab instead
138
		Insert a copy of the leftmost "By column" column group before it
139
		export version=<version>
140
		bin/import_date inputs/{.,}*/*/logs/$version.log.sql
141
		Update the import date in the upper-right corner
142
		bin/import_times inputs/{.,}*/*/logs/$version.log.sql
143
		Paste the output over the # Rows/Time columns, making sure that the
144
			row counts match up with the previous import's row counts
145
		If the row counts do not match up, insert or reorder rows as needed
146
			until they do. Get the datasource names from the log file footers:
147
			tail inputs/{.,}*/*/logs/$version.log.sql
148 11573 aaronmk
		Commit: svn ci -m 'inputs/import.stats.xls: updated import times'
149 10885 aaronmk
	Running individual steps separately:
150 9497 aaronmk
	To run TNRS:
151 9996 aaronmk
		To use an import other than public: export version=<version>
152 9995 aaronmk
		make scrub &
153 8458 aaronmk
		To view progress:
154
			tail -100 inputs/.TNRS/tnrs/logs/tnrs.make.log.sql
155 9497 aaronmk
	To remake analytical DB:
156 9996 aaronmk
		To use an import other than public: export version=<version>
157 11089 aaronmk
		bin/make_analytical_db & # runtime: 13 h ("12:43:57elapsed")
158 8458 aaronmk
		To view progress:
159 10600 aaronmk
			tail -150 inputs/analytical_db/logs/make_analytical_db.log.sql
160 8458 aaronmk
	To back up DB (staging tables and last import):
161 10578 aaronmk
		To use an import *other than public*: export version=<version>
162 10743 aaronmk
		make backups/TNRS.backup-remake &
163 10577 aaronmk
		dump_opts=--exclude-schema=public make backups/vegbien.$version.backup/test &
164 10578 aaronmk
			If after renaming to public, instead set dump_opts='' and replace
165
			$version with the appropriate revision
166 10744 aaronmk
		make backups/upload live=1
167 3381 aaronmk
168 1773 aaronmk
Datasource setup:
169 11516 aaronmk
	On local machine:
170 11090 aaronmk
	Example steps for a datasource: wiki.vegpath.org/Import_process_for_Madidi
171 8469 aaronmk
	umask ug=rwx,o= # prevent files from becoming web-accessible
172 8458 aaronmk
	Add a new datasource: make inputs/<datasrc>/add
173
		<datasrc> may not contain spaces, and should be abbreviated.
174
		If the datasource is a herbarium, <datasrc> should be the herbarium code
175
			as defined by the Index Herbariorum <http://sweetgum.nybg.org/ih/>
176 11018 aaronmk
	For a new-style datasource (one containing a ./run runscript):
177 11019 aaronmk
		"cp" -f inputs/.NCBI/{Makefile,run,table.run} inputs/<datasrc>/
178 8458 aaronmk
	For MySQL inputs (exports and live DB connections):
179
		For .sql exports:
180
			Place the original .sql file in _src/ (*not* in _MySQL/)
181
			Follow the steps starting with Install the staging tables below.
182
				This is for an initial sync to get the file onto vegbiendev.
183
			On vegbiendev:
184
				Create a database for the MySQL export in phpMyAdmin
185 9494 aaronmk
				Give the bien user all database-specific privileges *except*
186
					UPDATE, DELETE, ALTER, DROP. This prevents bugs in the
187
					import scripts from accidentally deleting data.
188 8458 aaronmk
				bin/mysql_bien database <inputs/<datasrc>/_src/export.sql &
189
		mkdir inputs/<datasrc>/_MySQL/
190
		cp -p lib/MySQL.{data,schema}.sql.make inputs/<datasrc>/_MySQL/
191
		Edit _MySQL/*.make for the DB connection
192
			For a .sql export, use server=vegbiendev and --user=bien
193
		Skip the Add input data for each table section
194
	For MS Access databases:
195
		Place the .mdb or .accdb file in _src/
196
		Download and install Access To PostgreSQL from
197
			http://www.bullzip.com/download.php
198
		Use Access To PostgreSQL to export the database:
199
			Export just the tables/indexes to inputs/<datasrc>/<file>.schema.sql
200
			Export just the data to inputs/<datasrc>/<file>.data.sql
201
		In <file>.schema.sql, make the following changes:
202
			Replace text "BOOLEAN" with "/*BOOLEAN*/INTEGER"
203
			Replace text "DOUBLE PRECISION NULL" with "DOUBLE PRECISION"
204
		Skip the Add input data for each table section
205
	Add input data for each table present in the datasource:
206
		For .sql exports, you must use the name of the table in the DB export
207
		For CSV files, you can use any name. It's recommended to use a table
208
			name from <https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/VegCSV#Suggested-table-names>
209
		Note that if this table will be joined together with another table, its
210
			name must end in ".src"
211
		make inputs/<datasrc>/<table>/add
212
			Important: DO NOT just create an empty directory named <table>!
213
				This command also creates necessary subdirs, such as logs/.
214
		If the table is in a .sql export: make inputs/<datasrc>/<table>/install
215
			Otherwise, place the CSV(s) for the table in
216
			inputs/<datasrc>/<table>/ OR place a query joining other tables
217
			together in inputs/<datasrc>/<table>/create.sql
218
		Important: When exporting relational databases to CSVs, you MUST ensure
219
			that embedded quotes are escaped by doubling them, *not* by
220
			preceding them with a "\" as is the default in phpMyAdmin
221
		If there are multiple part files for a table, and the header is repeated
222
			in each part, make sure each header is EXACTLY the same.
223 8466 aaronmk
			(If the headers are not the same, the CSV concatenation script
224
			assumes the part files don't have individual headers and treats the
225
			subsequent headers as data rows.)
226 8458 aaronmk
		Add <table> to inputs/<datasrc>/import_order.txt before other tables
227
			that depend on it
228 11018 aaronmk
		For a new-style datasource:
229
			"cp" -f inputs/.NCBI/nodes/run inputs/<datasrc>/<table>/
230
			inputs/<datasrc>/<table>/run
231 8458 aaronmk
	Install the staging tables:
232
		make inputs/<datasrc>/reinstall quiet=1 &
233
		For a MySQL .sql export:
234
			At prompt "[you]@vegbiendev's password:", enter your password
235
			At prompt "Enter password:", enter the value in config/bien_password
236
		To view progress: tail -f inputs/<datasrc>/<table>/logs/install.log.sql
237
		View the logs: tail -n +1 inputs/<datasrc>/*/logs/install.log.sql
238
			tail provides a header line with the filename
239
			+1 starts at the first line, to show the whole file
240
		For every file with an error 'column "..." specified more than once':
241
			Add a header override file "+header.<ext>" in <table>/:
242
				Note: The leading "+" should sort it before the flat files.
243
					"_" unfortunately sorts *after* capital letters in ASCII.
244
				Create a text file containing the header line of the flat files
245
				Add an ! at the beginning of the line
246
					This signals cat_csv that this is a header override.
247
				For empty names, use their 0-based column # (by convention)
248
				For duplicate names, add a distinguishing suffix
249
				For long names that collided, rename them to <= 63 chars long
250
				Do NOT make readability changes in this step; that is what the
251
					map spreadsheets (below) are for.
252
				Save
253
		If you made any changes, re-run the install command above
254
	Auto-create the map spreadsheets: make inputs/<datasrc>/
255
	Map each table's columns:
256
		In each <table>/ subdir, for each "via map" map.csv:
257
			Open the map in a spreadsheet editor
258
			Open the "core map" /mappings/Veg+-VegBIEN.csv
259
			In each row of the via map, set the right column to a value from the
260
				left column of the core map
261
			Save
262
		Regenerate the derived maps: make inputs/<datasrc>/
263
	Accept the test cases:
264 11018 aaronmk
		For a new-style datasource:
265
			inputs/<datasrc>/run
266
			svn di inputs/<datasrc>/*/test.xml.ref
267
			If you get errors, follow the steps for old-style datasources below
268
		For an old-style datasource:
269
			make inputs/<datasrc>/test
270 8458 aaronmk
			When prompted to "Accept new test output", enter y and press ENTER
271
			If you instead get errors, do one of the following for each one:
272
			-	If the error was due to a bug, fix it
273
			-	Add a SQL function that filters or transforms the invalid data
274
			-	Make an empty mapping for the columns that produced the error.
275
				Put something in the Comments column of the map spreadsheet to
276
				prevent the automatic mapper from auto-removing the mapping.
277
			When accepting tests, it's helpful to use WinMerge
278
				(see WinMerge setup below for configuration)
279
		make inputs/<datasrc>/test by_col=1
280
			If you get errors this time, this always indicates a bug, usually in
281
				the VegBIEN unique constraints or column-based import itself
282
	Add newly-created files: make inputs/<datasrc>/add
283
	Commit: svn ci -m "Added inputs/<datasrc>/" inputs/<datasrc>/
284
	Update vegbiendev:
285
		On jupiter: svn up
286
		On local machine:
287
			./fix_perms
288
			make inputs/upload
289 10025 aaronmk
			make inputs/upload live=1
290 8458 aaronmk
		On vegbiendev:
291
			svn up
292
			make inputs/download
293 10025 aaronmk
			make inputs/download live=1
294 8458 aaronmk
			Follow the steps under Install the staging tables above
295 1773 aaronmk
296 10884 aaronmk
Maintenance:
297
	on a live machine, you should put the following in your .profile:
298
--
299
# make svn files web-accessible. this does not affect unversioned files, because
300
# these get the right permissions on the local machine instead.
301
umask ug=rwx,o=rx
302
303
unset TMOUT # TMOUT causes screen to exit even with background processes
304
--
305
	if http://vegbiendev.nceas.ucsb.edu/phppgadmin/ goes down:
306
		on vegbiendev: make postgres-Linux
307
	to synchronize vegbiendev, jupiter, and your local machine:
308
		WARNING: pay careful attention to all files that will be deleted or
309
			overwritten!
310
		install put if needed:
311
			download https://uutils.googlecode.com/svn/trunk/bin/put to ~/bin/ and `chmod +x` it
312
		when changes are made on vegbiendev:
313
			on all machines: ./fix_perms
314
			on vegbiendev, upload:
315 11259 aaronmk
				overwrite=1 bin/sync_upload --size-only
316 10884 aaronmk
					then rerun with l=1 ...
317
			on your machine, download:
318
				overwrite=1 swap=1 src=. dest='aaronmk@jupiter:~/bien' put --exclude=.svn inputs/VegBIEN/TWiki
319
					then rerun with l=1 ...
320 11259 aaronmk
				overwrite=1 swap=1 bin/sync_upload --size-only
321 10884 aaronmk
					then rerun with l=1 ...
322
				make backups/TNRS.backup/download live=1
323
				overwrite=1 sync_remote_url=~/Dropbox/svn/ bin/sync_upload --existing --size-only # just update mtimes/perms
324
					then rerun with l=1 ...
325
	to synchronize a Mac's settings with my testing machine's:
326
		download:
327
			WARNING: this will overwrite all your user's settings!
328 11516 aaronmk
			on your machine:
329 10884 aaronmk
			overwrite=1 swap=1 sync_local_dir=~/Library/ sync_remote_subdir=Library/ bin/sync_upload --exclude="/Saved Application State"
330
				then rerun with l=1 ...
331
		upload:
332
			do step when changes are made on vegbiendev > on your machine, download
333
			on jupiter: (cd ~/Dropbox/svn/; up)
334
			on your machine:
335 11940 aaronmk
				rm ~/'Library/Thunderbird/Profiles/9oo8rcyn.default/ImapMail/imap.googlemail.com/[Gmail].sbd/Spam'
336
					# remove the downloaded Spam folder, because spam e-mails often contain viruses that would trigger clamscan
337 10884 aaronmk
				overwrite=1 del= sync_local_dir=~/Dropbox/svn/ sync_remote_subdir=Dropbox/svn/ bin/sync_upload --size-only # just update mtimes
338
					then rerun with l=1 ...
339
				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"
340
					then rerun with l=1 ...
341
				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"
342
					then rerun with l=1 ...
343
		upload just the VirtualBox VMs:
344
			on your machine:
345
				overwrite=1      sync_local_dir=~              sync_remote_subdir=             bin/sync_upload ~/"VirtualBox VMs/**"
346
					then rerun with l=1 ...
347
	to backup files not in Time Machine:
348 11516 aaronmk
		On local machine:
349 11686 aaronmk
		overwrite=1 src=/ dest=/Volumes/Time\ Machine\ Backups/ sudo -E put Library/PostgreSQL/9.3/data/
350 11684 aaronmk
			then rerun with l=1 ...
351 10884 aaronmk
		pg_ctl. stop # stop the PostgreSQL server
352 11686 aaronmk
		overwrite=1 src=/ dest=/Volumes/Time\ Machine\ Backups/ sudo -E put Library/PostgreSQL/9.3/data/
353 11685 aaronmk
			then rerun with l=1 ...
354 10884 aaronmk
		pg_ctl. start # start the PostgreSQL server
355 11866 aaronmk
	to backup e-mails:
356
		on local machine:
357
		/Applications/gmvault-v1.8.1-beta/bin/gmvault sync --multiple-db-owner --type quick aaronmk.nceas@gmail.com
358
		/Applications/gmvault-v1.8.1-beta/bin/gmvault sync --multiple-db-owner --type quick aaronmk@nceas.ucsb.edu
359
		open Thunderbird
360
		click the All Mail folder for each account and wait for it to download the e-mails in it
361 10884 aaronmk
	VegCore data dictionary:
362
		Regularly, or whenever the VegCore data dictionary page
363
			(https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/VegCore)
364
			is changed, regenerate mappings/VegCore.csv:
365 11516 aaronmk
			On local machine:
366 10884 aaronmk
			make mappings/VegCore.htm-remake; make mappings/
367
			svn di mappings/VegCore.tables.redmine
368
			If there are changes, update the data dictionary's Tables section
369
			When moving terms, check that no terms were lost: svn di
370
			svn ci -m 'mappings/VegCore.htm: regenerated from wiki'
371
	Important: Whenever you install a system update that affects PostgreSQL or
372
		any of its dependencies, such as libc, you should restart the PostgreSQL
373
		server. Otherwise, you may get strange errors like "the database system
374
		is in recovery mode" which go away upon reimport, or you may not be able
375
		to access the database as the postgres superuser. This applies to both
376
		Linux and Mac OS X.
377
378
Backups:
379
	Archived imports:
380 11516 aaronmk
		Follow the steps under Connecting to vegbiendev above
381 10884 aaronmk
		Back up: make backups/<version>.backup &
382
			Note: To back up the last import, you must archive it first:
383
				make schemas/rotate
384
		Test: make -s backups/<version>.backup/test &
385
		Restore: make backups/<version>.backup/restore &
386
		Remove: make backups/<version>.backup/remove
387
		Download: make backups/<version>.backup/download
388
	TNRS cache:
389 11516 aaronmk
		Follow the steps under Connecting to vegbiendev above
390 10884 aaronmk
		Back up: make backups/TNRS.backup-remake &
391
			runtime: 3 min ("real 2m48.859s")
392
		Restore:
393
			yes|make inputs/.TNRS/uninstall
394
			make backups/TNRS.backup/restore &
395
				runtime: 5.5 min ("real 5m35.829s")
396
			yes|make schemas/public/reinstall
397
				Must come after TNRS restore to recreate tnrs_input_name view
398
	Full DB:
399 11516 aaronmk
		Follow the steps under Connecting to vegbiendev above
400 10884 aaronmk
		Back up: make backups/vegbien.<version>.backup &
401
		Test: make -s backups/vegbien.<version>.backup/test &
402
		Restore: make backups/vegbien.<version>.backup/restore &
403
		Download: make backups/vegbien.<version>.backup/download
404
	Import logs:
405 11516 aaronmk
		On local machine:
406 10884 aaronmk
		Download: make inputs/download-logs live=1
407
408 6484 aaronmk
Datasource refreshing:
409 8458 aaronmk
	VegBank:
410 11516 aaronmk
		Follow the steps under Connecting to vegbiendev above
411 8458 aaronmk
		make inputs/VegBank/vegbank.sql-remake
412
		make inputs/VegBank/reinstall quiet=1 &
413 6484 aaronmk
414 702 aaronmk
Schema changes:
415 11516 aaronmk
	On local machine:
416 8458 aaronmk
	When changing the analytical views, run sync_analytical_..._to_view()
417
		to update the corresponding table
418
	Remember to update the following files with any renamings:
419
		schemas/filter_ERD.csv
420
		mappings/VegCore-VegBIEN.csv
421
		mappings/verify.*.sql
422
	Regenerate schema from installed DB: make schemas/remake
423
	Reinstall DB from schema: make schemas/public/reinstall schemas/reinstall
424
		WARNING: This will delete the current public schema of your VegBIEN DB!
425 8837 aaronmk
	Reinstall staging tables:
426
		On local machine:
427 8840 aaronmk
			sudo -E -u postgres psql <<<'ALTER DATABASE vegbien RENAME TO vegbien_prev'
428 8845 aaronmk
			make db
429 8837 aaronmk
			. bin/reinstall_all
430
			Fix any bugs and retry until no errors
431 8846 aaronmk
			make schemas/public/install
432
				This must be run *after* the datasources are installed, because
433
				views in public depend on some of the datasources
434 8842 aaronmk
			sudo -E -u postgres psql <<<'DROP DATABASE vegbien_prev'
435 8841 aaronmk
		On vegbiendev: repeat the above steps
436 8837 aaronmk
			WARNING: Do not run this until reinstall_all runs successfully on
437
			the local machine, or the live DB may be unrestorable!
438 8458 aaronmk
	Sync ERD with vegbien.sql schema:
439
		Run make schemas/vegbien.my.sql
440
		Open schemas/vegbien.ERD.mwb in MySQLWorkbench
441
		Go to File > Export > Synchronize With SQL CREATE Script...
442
		For Input File, select schemas/vegbien.my.sql
443
		Click Continue
444
		In the changes list, select each table with an arrow next to it
445
		Click Update Model
446
		Click Continue
447
		Note: The generated SQL script will be empty because we are syncing in
448
			the opposite direction
449
		Click Execute
450
		Reposition any lines that have been reset
451
		Add any new tables by dragging them from the Catalog in the left sidebar
452
			to the diagram
453
		Remove any deleted tables by right-clicking the table's diagram element,
454
			selecting Delete '<table name>', and clicking Delete
455
		Save
456
		If desired, update the graphical ERD exports (see below)
457
	Update graphical ERD exports:
458
		Go to File > Export > Export as PNG...
459
		Select schemas/vegbien.ERD.png and click Save
460
		Go to File > Export > Export as SVG...
461
		Select schemas/vegbien.ERD.svg and click Save
462
		Go to File > Export > Export as Single Page PDF...
463
		Select schemas/vegbien.ERD.1_pg.pdf and click Save
464
		Go to File > Print...
465
		In the lower left corner, click PDF > Save as PDF...
466
		Set the Title and Author to ""
467
		Select schemas/vegbien.ERD.pdf and click Save
468
		Commit: svn ci -m "schemas/vegbien.ERD.mwb: Regenerated exports"
469
	Refactoring tips:
470
		To rename a table:
471
			In vegbien.sql, do the following:
472
				Replace regexp (?<=_|\b)<old>(?=_|\b) with <new>
473
					This is necessary because the table name is *everywhere*
474
				Search for <new>
475
				Manually change back any replacements inside comments
476
		To rename a column:
477
			Rename the column: ALTER TABLE <table> RENAME <old> TO <new>;
478
			Recreate any foreign key for the column, removing CONSTRAINT <name>
479
				This resets the foreign key name using the new column name
480
	Creating a poster of the ERD:
481
		Determine the poster size:
482
			Measure the line height (from the bottom of one line to the bottom
483
				of another): 16.3cm/24 lines = 0.679cm
484
			Measure the height of the ERD: 35.4cm*2 = 70.8cm
485
			Zoom in as far as possible
486
			Measure the height of a capital letter: 3.5mm
487
			Measure the line height: 8.5mm
488
			Calculate the text's fraction of the line height: 3.5mm/8.5mm = 0.41
489
			Calculate the text height: 0.679cm*0.41 = 0.28cm
490
			Calculate the text height's fraction of the ERD height:
491
				0.28cm/70.8cm = 0.0040
492
			Measure the text height on the *VegBank* ERD poster: 5.5mm = 0.55cm
493
			Calculate the VegBIEN poster height to make the text the same size:
494
				0.55cm/0.0040 = 137.5cm H; *1in/2.54cm = 54.1in H
495
			The ERD aspect ratio is 11 in W x (2*8.5in H) = 11x17 portrait
496
			Calculate the VegBIEN poster width: 54.1in H*11W/17H = 35.0in W
497
			The minimum VegBIEN poster size is 35x54in portrait
498
		Determine the cost:
499
			The FedEx Kinkos near NCEAS (1030 State St, Santa Barbara, CA 93101)
500
				charges the following for posters:
501
				base: $7.25/sq ft
502
				lamination: $3/sq ft
503
				mounting on a board: $8/sq ft
504 203 aaronmk
505 1459 aaronmk
Testing:
506 8458 aaronmk
	On a development machine, you should put the following in your .profile:
507 8469 aaronmk
		umask ug=rwx,o= # prevent files from becoming web-accessible
508 8458 aaronmk
		export log= n=2
509 11516 aaronmk
	On local machine:
510 8458 aaronmk
	Mapping process: make test
511
		Including column-based import: make test by_col=1
512
			If the row-based and column-based imports produce different inserted
513
			row counts, this usually means that a table is underconstrained
514
			(the unique indexes don't cover all possible rows).
515
			This can occur if you didn't use COALESCE(field, null_value) around
516
			a nullable field in a unique index. See sql_gen.null_sentinels for
517
			the appropriate null value to use.
518
	Map spreadsheet generation: make remake
519
	Missing mappings: make missing_mappings
520
	Everything (for most complete coverage): make test-all
521 702 aaronmk
522 7183 aaronmk
Debugging:
523 8458 aaronmk
	"Binary chop" debugging:
524
		(This is primarily useful for regressions that occurred in a previous
525
		revision, which was committed without running all the tests)
526
		svn up -r <rev>; make inputs/.TNRS/reinstall; make schemas/public/reinstall; make <failed-test>.xml
527 8470 aaronmk
	.htaccess:
528
		mod_rewrite:
529 8471 aaronmk
			IMPORTANT: whenever you change the DirectorySlash setting for a
530
				directory, you *must* clear your browser's cache to ensure that
531
				a cached redirect is not used. this is because RewriteRule
532
				redirects are (by default) temporary, but DirectorySlash
533
				redirects are permanent.
534 8470 aaronmk
				for Firefox:
535
					press Cmd+Shift+Delete
536
					check only Cache
537
					press Enter or click Clear Now
538 7183 aaronmk
539 3783 aaronmk
WinMerge setup:
540 11516 aaronmk
	In a Windows VM:
541 8458 aaronmk
	Install WinMerge from <http://winmerge.org/>
542
	Open WinMerge
543
	Go to Edit > Options and click Compare in the left sidebar
544
	Enable "Moved block detection", as described at
545
		<http://manual.winmerge.org/Configuration.html#d0e5892>.
546
	Set Whitespace to Ignore change, as described at
547
		<http://manual.winmerge.org/Configuration.html#d0e5758>.
548 3783 aaronmk
549 3133 aaronmk
Documentation:
550 8458 aaronmk
	To generate a Redmine-formatted list of steps for column-based import:
551 11516 aaronmk
		On local machine:
552 8458 aaronmk
		make schemas/public/reinstall
553
		make inputs/ACAD/Specimen/logs/steps.by_col.log.sql
554
	To import and scrub just the test taxonomic names:
555 11516 aaronmk
		Follow the steps under Connecting to vegbiendev above
556 8458 aaronmk
		inputs/test_taxonomic_names/test_scrub
557 3133 aaronmk
558 702 aaronmk
General:
559 8458 aaronmk
	To see a program's description, read its top-of-file comment
560
	To see a program's usage, run it without arguments
561
	To remake a directory: make <dir>/remake
562
	To remake a file: make <file>-remake