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 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

    
10
Maintenance:
11
	to synchronize vegbiendev, jupiter, and your local machine:
12
		install put if needed:
13
			download https://uutils.googlecode.com/svn/trunk/bin/put to ~/bin/ and `chmod +x` it
14
		when changes are made on vegbiendev:
15
			on vegbiendev, upload:
16
				env overwrite=1             src=. dest='aaronmk@jupiter:~/bien' put --exclude=.svn --exclude=install.log.sql --exclude='*.backup*' --exclude='/backups/analytical_aggregate.*.csv*' --exclude='inputs/GBIF/**.data.sql' --exclude='bin/dotlockfile'
17
					then rerun with env l=1 ...
18
				env overwrite=1 del=        src=. dest='aaronmk@jupiter:~/bien' put --exclude=.svn --exclude=install.log.sql --exclude='inputs/GBIF/**.data.sql'
19
					then rerun with env l=1 ...
20
			on your machine, download:
21
				env overwrite=1 del= swap=1 src=. dest='aaronmk@jupiter:~/bien' put --exclude=.svn --exclude=install.log.sql --exclude='*.backup' --exclude='/backups/analytical_aggregate.*.csv*' --exclude='inputs/GBIF/**.data.sql' --exclude='bin/dotlockfile'
22
					then rerun with env l=1 ...
23
	to synchronize a Mac's settings with my testing machine's:
24
		download:
25
			WARNING: this will overwrite all your user's settings!
26
			env overwrite=1 swap=1 src=~/Library/ dest='aaronmk@jupiter:~/Library/' put --exclude="/Saved Application State/**"
27
				then rerun with env l=1 ...
28
		upload:
29
			env overwrite=1        src=~/Library/ dest='aaronmk@jupiter:~/Library/' put --exclude="/Saved Application State/**"
30
				then rerun with env l=1 ...
31
	to backup the Ubuntu VM:
32
		env src=/Users/aaronmk/VirtualBox\ VMs/Ubuntu dest=/Volumes/Macintosh\ HD\ backup/Ubuntu put --exclude=/Ubuntu.prev.vdi
33
			then rerun with env l=1 ...
34
	VegCore data dictionary:
35
		Regularly, or whenever the VegCore data dictionary page
36
			(https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/VegCore)
37
			is changed, regenerate mappings/VegCore.csv:
38
			make mappings/VegCore.htm-remake; make mappings/
39
			svn di mappings/VegCore.tables.redmine
40
			If there are changes, update the data dictionary's Tables section
41
			When moving terms, check that no terms were lost: svn di
42
			svn ci -m "mappings/VegCore.htm: Regenerated from wiki"
43
	Important: Whenever you install a system update that affects PostgreSQL or
44
		any of its dependencies, such as libc, you should restart the PostgreSQL
45
		server. Otherwise, you may get strange errors like "the database system
46
		is in recovery mode" which go away upon reimport, or you may not be able
47
		to access the database as the postgres superuser. This applies to both
48
		Linux and Mac OS X.
49

    
50
Single datasource import:
51
	(Re)import and scrub: make inputs/<datasrc>/reimport_scrub by_col=1
52
	(Re)import only: make inputs/<datasrc>/reimport by_col=1
53
	(Re)scrub: make inputs/<datasrc>/rescrub by_col=1
54
	Note that these commands also work if the datasource is not yet imported
55

    
56
Full database import:
57
	On jupiter: svn up
58
	On local machine:
59
		./fix_perms
60
		make inputs/upload
61
		make test by_col=1
62
			See note under Testing below
63
	On vegbiendev:
64
	Ensure there are no local modifications: svn st
65
	svn up
66
	make inputs/download
67
	For each newly-uploaded datasource above: make inputs/<datasrc>/reinstall
68
	Update the auxiliary schemas: make schemas/reinstall
69
		The public schema will be installed separately by the import process
70
	Delete imports before the last so they won't bloat the full DB backup:
71
		make backups/vegbien.<version>.backup/remove
72
		To keep a previous import other than the public schema:
73
			export dump_opts='--exclude-schema=public --exclude-schema=<version>'
74
	Make sure there is at least 150GB of disk space on /: df -h
75
		The import schema is 100GB, and may use additional space for temp tables
76
		To free up space, remove backups that have been archived on jupiter:
77
			List backups/ to view older backups
78
			Check their MD5 sums using the steps under On jupiter below
79
			Remove these backups
80
	unset version
81
	screen
82
	Press ENTER
83
	Start column-based import: . bin/import_all by_col=1
84
		To use row-based import: . bin/import_all
85
		To stop all running imports: . bin/stop_imports
86
		WARNING: Do NOT run import_all in the background, or the jobs it creates
87
			won't be owned by your shell.
88
		Note that import_all will take up to an hour to import the NCBI backbone
89
			and other metadata before returning control to the shell.
90
	Wait (overnight) for the import to finish
91
	To recover from a closed terminal window: screen -r
92
	When there are no more running jobs, exit the screen
93
	Get $version: echo $version
94
	Set $version in all vegbiendev terminals: export version=<version>
95
	Upload logs (run on vegbiendev): make inputs/upload
96
	On local machine: make inputs/download-logs
97
	In PostgreSQL:
98
		Check that the provider_count and source tables contain entries for all
99
			inputs
100
	tail inputs/{.,}*/*/logs/$version.log.sql
101
	In the output, search for "Command exited with non-zero status"
102
	For inputs that have this, fix the associated bug(s)
103
	If many inputs have errors, discard the current (partial) import:
104
		make schemas/$version/uninstall
105
	Otherwise, continue
106
	make schemas/$version/publish
107
	unset version
108
	backups/fix_perms
109
	make backups/upload
110
	On jupiter:
111
		cd /data/dev/aaronmk/bien/backups
112
		For each newly-archived backup:
113
			make -s <backup>.md5/test
114
			Check that "OK" is printed next to the filename
115
	On nimoy:
116
		cd /home/bien/svn/
117
		svn up
118
		export version=<version>
119
		make backups/analytical_stem.$version.csv/download
120
		In the bien_web DB:
121
			Create the analytical_stem_<version> table using its schema
122
				in schemas/vegbien.my.sql
123
		make -s backups/analytical_stem.$version.csv.md5/test
124
		Check that "OK" is printed next to the filename
125
		env table=analytical_stem_$version bin/publish_analytical_db \
126
			backups/analytical_stem.$version.csv
127
	If desired, record the import times in inputs/import.stats.xls:
128
		Open inputs/import.stats.xls
129
		If the rightmost import is within 5 columns of column IV:
130
			Copy the current tab to <leftmost-date>~<rightmost-date>
131
			Remove the previous imports from the current tab because they are
132
				now in the copied tab instead
133
		Insert a copy of the leftmost "By column" column group before it
134
		export version=<version>
135
		bin/import_date inputs/{.,}*/*/logs/$version.log.sql
136
		Update the import date in the upper-right corner
137
		bin/import_times inputs/{.,}*/*/logs/$version.log.sql
138
		Paste the output over the # Rows/Time columns, making sure that the
139
			row counts match up with the previous import's row counts
140
		If the row counts do not match up, insert or reorder rows as needed
141
			until they do. Get the datasource names from the log file footers:
142
			tail inputs/{.,}*/*/logs/$version.log.sql
143
		Commit: svn ci -m "inputs/import.stats.xls: Updated import times"
144
	To run TNRS: make scrub by_col=1 &
145
		export version=<version>
146
		To view progress:
147
			tail -100 inputs/.TNRS/tnrs/logs/tnrs.make.log.sql
148
	To remake analytical DB: bin/make_analytical_db &
149
		export version=<version>
150
		To view progress:
151
			tail -100 inputs/analytical_db/logs/make_analytical_db.log.sql
152
	To back up DB (staging tables and last import):
153
		export version=<version>
154
		If before renaming to public: export dump_opts=--exclude-schema=public
155
		make backups/vegbien.$version.backup/test &
156

    
157
Backups:
158
	Archived imports:
159
		Back up: make backups/<version>.backup &
160
			Note: To back up the last import, you must archive it first:
161
				make schemas/rotate
162
		Test: make -s backups/<version>.backup/test &
163
		Restore: make backups/<version>.backup/restore &
164
		Remove: make backups/<version>.backup/remove
165
		Download: make backups/download
166
	TNRS cache:
167
		Back up: make backups/TNRS.backup-remake &
168
		Restore:
169
			yes|make inputs/.TNRS/uninstall
170
			make backups/TNRS.backup/restore &
171
			yes|make schemas/public/reinstall
172
				Must come after TNRS restore to recreate tnrs_input_name view
173
	Full DB:
174
		Back up: make backups/vegbien.<version>.backup &
175
		Test: make -s backups/vegbien.<version>.backup/test &
176
		Restore: make backups/vegbien.<version>.backup/restore &
177
		Download: make backups/download
178
	Import logs:
179
		Download: make inputs/download-logs
180

    
181
Datasource setup:
182
	umask ug=rwx,o= # prevent files from becoming web-accessible
183
	Add a new datasource: make inputs/<datasrc>/add
184
		<datasrc> may not contain spaces, and should be abbreviated.
185
		If the datasource is a herbarium, <datasrc> should be the herbarium code
186
			as defined by the Index Herbariorum <http://sweetgum.nybg.org/ih/>
187
	For MySQL inputs (exports and live DB connections):
188
		For .sql exports:
189
			Place the original .sql file in _src/ (*not* in _MySQL/)
190
			Follow the steps starting with Install the staging tables below.
191
				This is for an initial sync to get the file onto vegbiendev.
192
			On vegbiendev:
193
				Create a database for the MySQL export in phpMyAdmin
194
				bin/mysql_bien database <inputs/<datasrc>/_src/export.sql &
195
		mkdir inputs/<datasrc>/_MySQL/
196
		cp -p lib/MySQL.{data,schema}.sql.make inputs/<datasrc>/_MySQL/
197
		Edit _MySQL/*.make for the DB connection
198
			For a .sql export, use server=vegbiendev and --user=bien
199
		Skip the Add input data for each table section
200
	For MS Access databases:
201
		Place the .mdb or .accdb file in _src/
202
		Download and install Access To PostgreSQL from
203
			http://www.bullzip.com/download.php
204
		Use Access To PostgreSQL to export the database:
205
			Export just the tables/indexes to inputs/<datasrc>/<file>.schema.sql
206
			Export just the data to inputs/<datasrc>/<file>.data.sql
207
		In <file>.schema.sql, make the following changes:
208
			Replace text "BOOLEAN" with "/*BOOLEAN*/INTEGER"
209
			Replace text "DOUBLE PRECISION NULL" with "DOUBLE PRECISION"
210
		Skip the Add input data for each table section
211
	Add input data for each table present in the datasource:
212
		For .sql exports, you must use the name of the table in the DB export
213
		For CSV files, you can use any name. It's recommended to use a table
214
			name from <https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/VegCSV#Suggested-table-names>
215
		Note that if this table will be joined together with another table, its
216
			name must end in ".src"
217
		make inputs/<datasrc>/<table>/add
218
			Important: DO NOT just create an empty directory named <table>!
219
				This command also creates necessary subdirs, such as logs/.
220
		If the table is in a .sql export: make inputs/<datasrc>/<table>/install
221
			Otherwise, place the CSV(s) for the table in
222
			inputs/<datasrc>/<table>/ OR place a query joining other tables
223
			together in inputs/<datasrc>/<table>/create.sql
224
		Important: When exporting relational databases to CSVs, you MUST ensure
225
			that embedded quotes are escaped by doubling them, *not* by
226
			preceding them with a "\" as is the default in phpMyAdmin
227
		If there are multiple part files for a table, and the header is repeated
228
			in each part, make sure each header is EXACTLY the same.
229
			(If the headers are not the same, the CSV concatenation script
230
			assumes the part files don't have individual headers and treats the
231
			subsequent headers as data rows.)
232
		Add <table> to inputs/<datasrc>/import_order.txt before other tables
233
			that depend on it
234
	Install the staging tables:
235
		make inputs/<datasrc>/reinstall quiet=1 &
236
		For a MySQL .sql export:
237
			At prompt "[you]@vegbiendev's password:", enter your password
238
			At prompt "Enter password:", enter the value in config/bien_password
239
		To view progress: tail -f inputs/<datasrc>/<table>/logs/install.log.sql
240
		View the logs: tail -n +1 inputs/<datasrc>/*/logs/install.log.sql
241
			tail provides a header line with the filename
242
			+1 starts at the first line, to show the whole file
243
		For every file with an error 'column "..." specified more than once':
244
			Add a header override file "+header.<ext>" in <table>/:
245
				Note: The leading "+" should sort it before the flat files.
246
					"_" unfortunately sorts *after* capital letters in ASCII.
247
				Create a text file containing the header line of the flat files
248
				Add an ! at the beginning of the line
249
					This signals cat_csv that this is a header override.
250
				For empty names, use their 0-based column # (by convention)
251
				For duplicate names, add a distinguishing suffix
252
				For long names that collided, rename them to <= 63 chars long
253
				Do NOT make readability changes in this step; that is what the
254
					map spreadsheets (below) are for.
255
				Save
256
		If you made any changes, re-run the install command above
257
	Auto-create the map spreadsheets: make inputs/<datasrc>/
258
	Map each table's columns:
259
		In each <table>/ subdir, for each "via map" map.csv:
260
			Open the map in a spreadsheet editor
261
			Open the "core map" /mappings/Veg+-VegBIEN.csv
262
			In each row of the via map, set the right column to a value from the
263
				left column of the core map
264
			Save
265
		Regenerate the derived maps: make inputs/<datasrc>/
266
	Accept the test cases:
267
		make inputs/<datasrc>/test
268
			When prompted to "Accept new test output", enter y and press ENTER
269
			If you instead get errors, do one of the following for each one:
270
			-	If the error was due to a bug, fix it
271
			-	Add a SQL function that filters or transforms the invalid data
272
			-	Make an empty mapping for the columns that produced the error.
273
				Put something in the Comments column of the map spreadsheet to
274
				prevent the automatic mapper from auto-removing the mapping.
275
			When accepting tests, it's helpful to use WinMerge
276
				(see WinMerge setup below for configuration)
277
		make inputs/<datasrc>/test by_col=1
278
			If you get errors this time, this always indicates a bug, usually in
279
				the VegBIEN unique constraints or column-based import itself
280
	Add newly-created files: make inputs/<datasrc>/add
281
	Commit: svn ci -m "Added inputs/<datasrc>/" inputs/<datasrc>/
282
	Update vegbiendev:
283
		On jupiter: svn up
284
		On local machine:
285
			./fix_perms
286
			make inputs/upload
287
		On vegbiendev:
288
			svn up
289
			make inputs/download
290
			Follow the steps under Install the staging tables above
291

    
292
Datasource refreshing:
293
	VegBank:
294
		make inputs/VegBank/vegbank.sql-remake
295
		make inputs/VegBank/reinstall quiet=1 &
296

    
297
Schema changes:
298
	When changing the analytical views, run sync_analytical_..._to_view()
299
		to update the corresponding table
300
	Remember to update the following files with any renamings:
301
		schemas/filter_ERD.csv
302
		mappings/VegCore-VegBIEN.csv
303
		mappings/verify.*.sql
304
	Regenerate schema from installed DB: make schemas/remake
305
	Reinstall DB from schema: make schemas/public/reinstall schemas/reinstall
306
		WARNING: This will delete the current public schema of your VegBIEN DB!
307
	Reinstall staging tables: . bin/reinstall_all
308
	Sync ERD with vegbien.sql schema:
309
		Run make schemas/vegbien.my.sql
310
		Open schemas/vegbien.ERD.mwb in MySQLWorkbench
311
		Go to File > Export > Synchronize With SQL CREATE Script...
312
		For Input File, select schemas/vegbien.my.sql
313
		Click Continue
314
		In the changes list, select each table with an arrow next to it
315
		Click Update Model
316
		Click Continue
317
		Note: The generated SQL script will be empty because we are syncing in
318
			the opposite direction
319
		Click Execute
320
		Reposition any lines that have been reset
321
		Add any new tables by dragging them from the Catalog in the left sidebar
322
			to the diagram
323
		Remove any deleted tables by right-clicking the table's diagram element,
324
			selecting Delete '<table name>', and clicking Delete
325
		Save
326
		If desired, update the graphical ERD exports (see below)
327
	Update graphical ERD exports:
328
		Go to File > Export > Export as PNG...
329
		Select schemas/vegbien.ERD.png and click Save
330
		Go to File > Export > Export as SVG...
331
		Select schemas/vegbien.ERD.svg and click Save
332
		Go to File > Export > Export as Single Page PDF...
333
		Select schemas/vegbien.ERD.1_pg.pdf and click Save
334
		Go to File > Print...
335
		In the lower left corner, click PDF > Save as PDF...
336
		Set the Title and Author to ""
337
		Select schemas/vegbien.ERD.pdf and click Save
338
		Commit: svn ci -m "schemas/vegbien.ERD.mwb: Regenerated exports"
339
	Refactoring tips:
340
		To rename a table:
341
			In vegbien.sql, do the following:
342
				Replace regexp (?<=_|\b)<old>(?=_|\b) with <new>
343
					This is necessary because the table name is *everywhere*
344
				Search for <new>
345
				Manually change back any replacements inside comments
346
		To rename a column:
347
			Rename the column: ALTER TABLE <table> RENAME <old> TO <new>;
348
			Recreate any foreign key for the column, removing CONSTRAINT <name>
349
				This resets the foreign key name using the new column name
350
	Creating a poster of the ERD:
351
		Determine the poster size:
352
			Measure the line height (from the bottom of one line to the bottom
353
				of another): 16.3cm/24 lines = 0.679cm
354
			Measure the height of the ERD: 35.4cm*2 = 70.8cm
355
			Zoom in as far as possible
356
			Measure the height of a capital letter: 3.5mm
357
			Measure the line height: 8.5mm
358
			Calculate the text's fraction of the line height: 3.5mm/8.5mm = 0.41
359
			Calculate the text height: 0.679cm*0.41 = 0.28cm
360
			Calculate the text height's fraction of the ERD height:
361
				0.28cm/70.8cm = 0.0040
362
			Measure the text height on the *VegBank* ERD poster: 5.5mm = 0.55cm
363
			Calculate the VegBIEN poster height to make the text the same size:
364
				0.55cm/0.0040 = 137.5cm H; *1in/2.54cm = 54.1in H
365
			The ERD aspect ratio is 11 in W x (2*8.5in H) = 11x17 portrait
366
			Calculate the VegBIEN poster width: 54.1in H*11W/17H = 35.0in W
367
			The minimum VegBIEN poster size is 35x54in portrait
368
		Determine the cost:
369
			The FedEx Kinkos near NCEAS (1030 State St, Santa Barbara, CA 93101)
370
				charges the following for posters:
371
				base: $7.25/sq ft
372
				lamination: $3/sq ft
373
				mounting on a board: $8/sq ft
374

    
375
Testing:
376
	On a development machine, you should put the following in your .profile:
377
		umask ug=rwx,o= # prevent files from becoming web-accessible
378
		export log= n=2
379
	Mapping process: make test
380
		Including column-based import: make test by_col=1
381
			If the row-based and column-based imports produce different inserted
382
			row counts, this usually means that a table is underconstrained
383
			(the unique indexes don't cover all possible rows).
384
			This can occur if you didn't use COALESCE(field, null_value) around
385
			a nullable field in a unique index. See sql_gen.null_sentinels for
386
			the appropriate null value to use.
387
	Map spreadsheet generation: make remake
388
	Missing mappings: make missing_mappings
389
	Everything (for most complete coverage): make test-all
390

    
391
Debugging:
392
	"Binary chop" debugging:
393
		(This is primarily useful for regressions that occurred in a previous
394
		revision, which was committed without running all the tests)
395
		svn up -r <rev>; make inputs/.TNRS/reinstall; make schemas/public/reinstall; make <failed-test>.xml
396
	.htaccess:
397
		mod_rewrite:
398
			IMPORTANT: whenever you change the DirectorySlash setting for a
399
				directory, you *must* clear your browser's cache to ensure that
400
				a cached redirect is not used. this is because RewriteRule
401
				redirects are (by default) temporary, but DirectorySlash
402
				redirects are permanent.
403
				for Firefox:
404
					press Cmd+Shift+Delete
405
					check only Cache
406
					press Enter or click Clear Now
407

    
408
WinMerge setup:
409
	Install WinMerge from <http://winmerge.org/>
410
	Open WinMerge
411
	Go to Edit > Options and click Compare in the left sidebar
412
	Enable "Moved block detection", as described at
413
		<http://manual.winmerge.org/Configuration.html#d0e5892>.
414
	Set Whitespace to Ignore change, as described at
415
		<http://manual.winmerge.org/Configuration.html#d0e5758>.
416

    
417
Documentation:
418
	To generate a Redmine-formatted list of steps for column-based import:
419
		make schemas/public/reinstall
420
		make inputs/ACAD/Specimen/logs/steps.by_col.log.sql
421
	To import and scrub just the test taxonomic names:
422
		inputs/test_taxonomic_names/test_scrub
423

    
424
General:
425
	To see a program's description, read its top-of-file comment
426
	To see a program's usage, run it without arguments
427
	To remake a directory: make <dir>/remake
428
	To remake a file: make <file>-remake
(2-2/6)