Installation:

		svn co https://code.nceas.ucsb.edu/code/projects/bien/trunk bien

	Install: make install

		sudo service postgresql stop

		this prevents the OS from SIGKILLing Postgres, which sometimes causes

		database corruption

		import, causing errors such as disk space overruns. for this reason, it

		is recommended to maintain a snapshot copy of the VM as it was at the

		last successful import, for fallback use if a system upgrade breaks

		anything. system upgrades on the snapshot VM should be disabled

		completely, and because this will also disable security fixes, the

		snapshot VM should be disconnected from the internet and all networking

		interfaces. (this is an unfortunate consequence of modern OSes being

		written in non-memory-safe languages such as C and C++.)

	**WARNING**: always start with a clean shell, to avoid spurious bugs. the

		shell should not have changes to the env vars. (there have been bugs

		that went away after closing and reopening the terminal window.) note

		that running `exec bash` is not sufficient to *reset* the env vars.

	**WARNING**: shell scripts should always be read-only, so that editing them

		while an import is in progress will not crash the import (see

		http://vegpath.org/links/#**%20modifying%20a%20running%20shell%20script)

	(Re)import only: make inputs/<datasrc>/reimport by_col=1 &

Full database import:

		"Notes on running programs"

		when the DB will not be needed for other uses. this is necessary because

		vegbiendev will be slow for the first few hours of the import, due to

		the import using all the available cores.

		your local machine"

		make inputs/upload

				PostgreSQL error parsing in /lib/sql.py parse_exception()

	Update the auxiliary schemas: make schemas/reinstall

	Delete imports before the last so they won't bloat the full DB backup:

		make backups/vegbien.<version>.backup/remove

		To keep a previous import other than the public schema:

			export dump_opts='--exclude-schema=public --exclude-schema=<version>'

		import (this is apparently not automatically reclaimed):

		make postgres_restart

		although the import schema itself is only 315 GB, Postgres uses

			for the first day (for import started @12:55:09, high-water marks of

			running: entire disk for 4 min @05:35:44, 1.8 TB @11:15:05).

			List backups/ to view older backups

			Check their MD5 sums using the steps under On jupiter below

			Remove these backups

		screen

		Press ENTER

	set -o ignoreeof # prevent Ctrl+D from exiting shell to keep attached jobs

		unset n # clear any limit set in .profile (unless desired)

		unset log # allow logging output to go to log files

	if no commits have been made since the last import (eg. if retrying an

		import), set a custom version that differs from the auto-assigned one

		(would otherwise cause a collision with the last import):

		svn info

		extract the svn revision after "Revision:"

		export version=r[revision]_2 # +suffix to distinguish from last import

			# env var will be inherited by `screen` shell

		export version=custom_import_name

		To use row-based import: . bin/import_all by_col=

			creates won't be owned by your shell.

			and other metadata before returning control to the shell.

		warning e-mails. these can safely be ignored. (they happen because the

		parallel imports use all the available cores.)

		due to continuously-changing files

	**WARNING**: do *not* run backups/pg_snapshot until the previous import has

		been replaced, to avoid running into disk space limits

		export version=<version>

	Set $version in all vegbiendev terminals: export version=<version>

			manually reimport these datasources using the steps under

				Single datasource import

			bin/after_import &

			wait for the import to finish

	For inputs that have this, fix the associated bug(s)

	If many inputs have errors, discard the current (partial) import:

		make schemas/$version/uninstall

	Otherwise, continue

		Get the # datasources

		Get the # datasources with observations

			is not the case, manually check the entries in source against the

			datasources list on the wiki page (some datasources may be near the

			end depending on import order).

			rows with dataset="(total)" (at the top when the table is unsorted)

		tail -100 inputs/.TNRS/tnrs/logs/tnrs.make.log.sql

		If the log ends in an AssertionError

			"assert sql.table_col_names(db, table) == header":

			Figure out which TNRS CSV columns have changed

			On local machine:

				rm=1 inputs/.TNRS/schema.sql.run export_

				Make the same changes in the live TNRS.tnrs table on vegbiendev

				If dropping a column, recreate the dependent views

				Restart the TNRS client: make scrub by_col=1 &

			could incorrectly be overwritten with a broken one.

		make backups/vegbien.$version.backup/download live=1

			# download backup to local machine

		For each newly-archived backup:

			make -s <backup>.md5/test

			Check that "OK" is printed next to the filename

	If desired, record the import times in inputs/import.stats.xls:

		If the rightmost import is within 5 columns of column IV:

			Copy the current tab to <leftmost-date>~<rightmost-date>

			Remove the previous imports from the current tab because they are

				now in the copied tab instead

		Insert a copy of the leftmost "By column" column group before it

		export version=<version>

			row counts match up with the previous import's row counts

		If the row counts do not match up, insert or reorder rows as needed

			until they do. Get the datasource names from the log file footers:

			make inputs/.TNRS/reinstall

			re-create public-schema views that were cascadingly deleted

			tail -100 inputs/.TNRS/tnrs/logs/tnrs.make.log.sql

		$ screen

		# press Enter

		$ bin/psql_verbose_vegbien <<<'SELECT geoscrub_input_view_modify();' &

			# runtime: 25 s ("0m24.936s") @r14089 @vegbiendev

			# runtime: 2.5 h

		# wait until done

		$ rm=1 inputs/.geoscrub/run &

		# re-create public-schema views that were cascadingly deleted

		# press Ctrl+D

		# remake the analytical DB (below)

			$version with the appropriate revision

		<datasrc> may not contain spaces, and should be abbreviated.

		If the datasource is a herbarium, <datasrc> should be the herbarium code

			as defined by the Index Herbariorum <http://sweetgum.nybg.org/ih/>

		For .sql exports:

			Place the original .sql file in _src/ (*not* in _MySQL/)

			Follow the steps starting with Install the staging tables below.

				This is for an initial sync to get the file onto vegbiendev.

					UPDATE, DELETE, ALTER, DROP. This prevents bugs in the

					import scripts from accidentally deleting data.

		mkdir inputs/<datasrc>/_MySQL/

		cp -p lib/MySQL.{data,schema}.sql.make inputs/<datasrc>/_MySQL/

		Edit _MySQL/*.make for the DB connection

			For a .sql export, use server=vegbiendev and --user=bien

		Skip the Add input data for each table section

	For MS Access databases:

		Place the .mdb or .accdb file in _src/

		Download and install Access To PostgreSQL from

			http://www.bullzip.com/download.php

		Use Access To PostgreSQL to export the database:

			Export just the tables/indexes to inputs/<datasrc>/<file>.schema.sql

			Export just the data to inputs/<datasrc>/<file>.data.sql

		In <file>.schema.sql, make the following changes:

			Replace text "BOOLEAN" with "/*BOOLEAN*/INTEGER"

			Replace text "DOUBLE PRECISION NULL" with "DOUBLE PRECISION"

		Skip the Add input data for each table section

	Add input data for each table present in the datasource:

		For .sql exports, you must use the name of the table in the DB export

		For CSV files, you can use any name. It's recommended to use a table

			name from <https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/VegCSV#Suggested-table-names>

		Note that if this table will be joined together with another table, its

			name must end in ".src"

		make inputs/<datasrc>/<table>/add

			Important: DO NOT just create an empty directory named <table>!

				This command also creates necessary subdirs, such as logs/.

		If the table is in a .sql export: make inputs/<datasrc>/<table>/install

			Otherwise, place the CSV(s) for the table in

			inputs/<datasrc>/<table>/ OR place a query joining other tables

			together in inputs/<datasrc>/<table>/create.sql

		Important: When exporting relational databases to CSVs, you MUST ensure

			that embedded quotes are escaped by doubling them, *not* by

			preceding them with a "\" as is the default in phpMyAdmin

		If there are multiple part files for a table, and the header is repeated

			in each part, make sure each header is EXACTLY the same.

			assumes the part files don't have individual headers and treats the

			subsequent headers as data rows.)

			that depend on it

			"cp" -f inputs/.NCBI/nodes/run inputs/<datasrc>/<table>/

			inputs/<datasrc>/<table>/run

		make inputs/<datasrc>/reinstall quiet=1 &

		For a MySQL .sql export:

			At prompt "[you]@vegbiendev's password:", enter your password

			At prompt "Enter password:", enter the value in config/bien_password

		To view progress: tail -f inputs/<datasrc>/<table>/logs/install.log.sql

		View the logs: tail -n +1 inputs/<datasrc>/*/logs/install.log.sql

			tail provides a header line with the filename

			+1 starts at the first line, to show the whole file

		For every file with an error 'column "..." specified more than once':

			Add a header override file "+header.<ext>" in <table>/:

				Note: The leading "+" should sort it before the flat files.

					"_" unfortunately sorts *after* capital letters in ASCII.

				Create a text file containing the header line of the flat files

				Add an ! at the beginning of the line

					This signals cat_csv that this is a header override.

				For empty names, use their 0-based column # (by convention)

				For duplicate names, add a distinguishing suffix

				For long names that collided, rename them to <= 63 chars long

				Do NOT make readability changes in this step; that is what the

					map spreadsheets (below) are for.

				Save

		If you made any changes, re-run the install command above

	Auto-create the map spreadsheets: make inputs/<datasrc>/

	Map each table's columns:

		In each <table>/ subdir, for each "via map" map.csv:

			Open the map in a spreadsheet editor

			Open the "core map" /mappings/Veg+-VegBIEN.csv

			In each row of the via map, set the right column to a value from the

				left column of the core map

			Save

		Regenerate the derived maps: make inputs/<datasrc>/

	Accept the test cases:

			inputs/<datasrc>/run

			svn di inputs/<datasrc>/*/test.xml.ref

			If you get errors, follow the steps for old-style datasources below

		For an old-style datasource:

			make inputs/<datasrc>/test

			If you instead get errors, do one of the following for each one:

			-	If the error was due to a bug, fix it

			-	Add a SQL function that filters or transforms the invalid data

			-	Make an empty mapping for the columns that produced the error.

				Put something in the Comments column of the map spreadsheet to

				prevent the automatic mapper from auto-removing the mapping.

			When accepting tests, it's helpful to use WinMerge

				(see WinMerge setup below for configuration)

		make inputs/<datasrc>/test by_col=1

			If you get errors this time, this always indicates a bug, usually in

				the VegBIEN unique constraints or column-based import itself

	Add newly-created files: make inputs/<datasrc>/add

	Commit: svn ci -m "Added inputs/<datasrc>/" inputs/<datasrc>/

	Update vegbiendev:

			./fix_perms

			make inputs/upload

	on a live machine, you should put the following in your .profile:

--

# make svn files web-accessible. this does not affect unversioned files, because

# these get the right permissions on the local machine instead.

umask ug=rwx,o=rx

unset TMOUT # TMOUT causes screen to exit even with background processes

--

	if http://vegbiendev.nceas.ucsb.edu/phppgadmin/ goes down:

		it needs to be kept in working order so that it works when it's needed.

		ssh -t vegbiendev.nceas.ucsb.edu exec sudo -u aaronmk -i

		back up MySQL: # usually few changes, so do this first

				then review diff, and rerun without `live=`

			on local machine:

			l=1 swap=1 overwrite=1 inplace=1 local_dir=~ sync_remote_subdir=                          subpath=~/Documents/BIEN/var/lib/mysql.bak/                          bin/sync_upload

		back up Postgres:

				then review diff, and rerun without `live=`

		install put if needed:

			download https://uutils.googlecode.com/svn/trunk/bin/put to ~/bin/ and `chmod +x` it

		when changes are made on vegbiendev:

				on all machines:

				up

				./fix_perms

				download:

		on local machine:

		/Applications/gmvault-v1.8.1-beta/bin/gmvault sync --multiple-db-owner --type quick aaronmk.nceas@gmail.com

		/Applications/gmvault-v1.8.1-beta/bin/gmvault sync --multiple-db-owner --type quick aaronmk@nceas.ucsb.edu

		open Thunderbird

		click the All Mail folder for each account and wait for it to download the e-mails in it

	to back up the version history:

			command gets run, and that way it will get backed up immediately to

			Dropbox (and hourly to Time Machine), while vegbiendev only gets

			backed up daily to tape

		on local machine:

		# use absolute path for vegbiendev commands because the Ubuntu 14.04

			version of rsync doesn't expand ~ properly

		overwrite=1 swap=1 src=~ dest='aaronmk@jupiter.nceas.ucsb.edu:/data/dev/aaronmk/' put Dropbox/docs/BIEN/svn_repo/ # runtime: 30 s ("36.19")

		do step when changes are made on vegbiendev > on your machine, download

		ssh aaronmk@jupiter.nceas.ucsb.edu

			(cd ~/Dropbox/svn/; up)

		on your machine:

			rm ~/'Library/Thunderbird/Profiles/9oo8rcyn.default/ImapMail/imap.googlemail.com/[Gmail].sbd/Spam'

				# remove the downloaded Spam folder, because spam e-mails often contain viruses that would trigger clamscan

			overwrite=1 del=      sync_local_dir=~/Dropbox/svn/ sync_remote_subdir=Dropbox/svn/ bin/sync_upload --size-only # just update mtimes

				then review diff, and rerun with `l=1` prepended

			overwrite=1 inplace=1 sync_local_dir=~              sync_remote_subdir=             bin/sync_upload ~/"VirtualBox VMs/**" # need inplace=1 because they are very large files

				then review diff, and rerun with `l=1` prepended

			overwrite=1           sync_local_dir=~              sync_remote_subdir=             bin/sync_upload --exclude="/Library/Saved Application State" --exclude="/Library/Thunderbird/Profiles/9oo8rcyn.default/global-messages-db.sqlite" --exclude="/.Trash" --exclude="/bin" --exclude="/bin/pg_ctl" --exclude="/bin/unzip" --exclude="/Dropbox/home" --exclude="/.profile" --exclude="/.shrc" --exclude="/.bashrc" --exclude="/software/**/.svn"

				then review diff, and rerun with `l=1` prepended

			stop Dropbox: system tray > Dropbox icon > gear icon > Quit Dropbox

				this prevents Dropbox from trying to capture filesystem

				events while syncing

			overwrite=1           sync_local_dir=~              sync_remote_url=~/Dropbox/home  bin/sync_upload --exclude="/Library/Saved Application State" --exclude="/Library/Thunderbird/Profiles/9oo8rcyn.default/global-messages-db.sqlite" --exclude="/.Trash" --exclude="/.dropbox" --exclude="/Documents/BIEN" --exclude="/Dropbox" --exclude="/software" --exclude="/VirtualBox VMs/**.sav" --exclude="/VirtualBox VMs/**.vdi" --exclude="/VirtualBox VMs/**.vmdk"

				then review diff, and rerun with `l=1` prepended

			start Dropbox: /Applications > double-click Dropbox.app

		overwrite=1 src=/ dest=/Volumes/Time\ Machine\ Backups/ sudo -E put usr/local/var/postgres

		turn on and connect the 2 TB external hard drive

		screen

		l=1 overwrite=1 src=/ dest=/Volumes/Macintosh\ HD\ backup/ sudo -E put

			# runtime: 10 min (~600); initial runtime: 4 h ("2422.84"+"12379.91")

		# restart holding Alt

		# select Time Machine Backups

		# restore the last Time Machine backup to Macintosh HD

		# restart holding Alt

		# select Macintosh HD

		$ screen

		$ l=1 swap=1 src=/ dest=/Volumes/Time\ Machine\ Backups/ sudo -E put usr/local/var/postgres # runtime: 1 h ("4020.61")

		$ make postgres_restart

		Regularly, or whenever the VegCore data dictionary page

			(https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/VegCore)

			is changed, regenerate mappings/VegCore.csv:

			If there are changes, update the data dictionary's Tables section

			When moving terms, check that no terms were lost: svn di

			svn ci -m 'mappings/VegCore.htm: regenerated from wiki'

					datasource mappings/staging tables" above

		any of its dependencies, such as libc, you should restart the PostgreSQL

		server. Otherwise, you may get strange errors like "the database system

		is in recovery mode" which go away upon reimport, or you may not be able

		to access the database as the postgres superuser. This applies to both

		Linux and Mac OS X.

Backups:

	Archived imports:

			Note: To back up the last import, you must archive it first:

				make schemas/rotate

		Test: make -s backups/<version>.backup/test &

		Restore: make backups/<version>.backup/restore &

		Remove: make backups/<version>.backup/remove

		Download: make backups/<version>.backup/download

	TNRS cache:

			runtime: 3 min ("real 2m48.859s")

		Restore:

			yes|make inputs/.TNRS/uninstall

			make backups/TNRS.backup/restore &

				runtime: 5.5 min ("real 5m35.829s")

			yes|make schemas/public/reinstall

				Must come after TNRS restore to recreate tnrs_input_name view

	Full DB:

		Test: make -s backups/vegbien.<version>.backup/test &

		Restore: make backups/vegbien.<version>.backup/restore &

		Download: make backups/vegbien.<version>.backup/download

	Import logs:

		make inputs/VegBank/reinstall quiet=1 &

		to update the corresponding table

	Remember to update the following files with any renamings:

		schemas/filter_ERD.csv

		mappings/VegCore-VegBIEN.csv

		mappings/verify.*.sql

	Regenerate schema from installed DB: make schemas/remake

	Reinstall DB from schema: make schemas/public/reinstall schemas/reinstall

			Fix any bugs and retry until no errors

				This must be run *after* the datasources are installed, because

				views in public depend on some of the datasources

			on the local machine, or the live DB may be unrestorable!

		Run make schemas/vegbien.my.sql

		Open schemas/vegbien.ERD.mwb in MySQLWorkbench

		Go to File > Export > Synchronize With SQL CREATE Script...

		For Input File, select schemas/vegbien.my.sql

		Click Continue

		In the changes list, select each table with an arrow next to it

		Click Update Model

		Click Continue

		Note: The generated SQL script will be empty because we are syncing in

			the opposite direction

		Click Execute

		Reposition any lines that have been reset

		Add any new tables by dragging them from the Catalog in the left sidebar

			to the diagram

		Remove any deleted tables by right-clicking the table's diagram element,

			selecting Delete '<table name>', and clicking Delete

		Save

		If desired, update the graphical ERD exports (see below)

	Update graphical ERD exports:

		Go to File > Export > Export as PNG...

		Select schemas/vegbien.ERD.png and click Save

		Go to File > Export > Export as SVG...

		Select schemas/vegbien.ERD.svg and click Save

		Go to File > Export > Export as Single Page PDF...

		Select schemas/vegbien.ERD.1_pg.pdf and click Save

		Go to File > Print...

		In the lower left corner, click PDF > Save as PDF...

		Set the Title and Author to ""

		Select schemas/vegbien.ERD.pdf and click Save

		Commit: svn ci -m "schemas/vegbien.ERD.mwb: Regenerated exports"

	Refactoring tips:

		To rename a table:

			In vegbien.sql, do the following:

				Replace regexp (?<=_|\b)<old>(?=_|\b) with <new>

					This is necessary because the table name is *everywhere*

				Search for <new>

				Manually change back any replacements inside comments

		To rename a column:

			Rename the column: ALTER TABLE <table> RENAME <old> TO <new>;

			Recreate any foreign key for the column, removing CONSTRAINT <name>

				This resets the foreign key name using the new column name

	Creating a poster of the ERD:

		Determine the poster size:

			Measure the line height (from the bottom of one line to the bottom

				of another): 16.3cm/24 lines = 0.679cm

			Measure the height of the ERD: 35.4cm*2 = 70.8cm

			Zoom in as far as possible

			Measure the height of a capital letter: 3.5mm

			Measure the line height: 8.5mm

			Calculate the text's fraction of the line height: 3.5mm/8.5mm = 0.41

			Calculate the text height: 0.679cm*0.41 = 0.28cm

			Calculate the text height's fraction of the ERD height:

				0.28cm/70.8cm = 0.0040

			Measure the text height on the *VegBank* ERD poster: 5.5mm = 0.55cm

			Calculate the VegBIEN poster height to make the text the same size:

				0.55cm/0.0040 = 137.5cm H; *1in/2.54cm = 54.1in H

			The ERD aspect ratio is 11 in W x (2*8.5in H) = 11x17 portrait

			Calculate the VegBIEN poster width: 54.1in H*11W/17H = 35.0in W

			The minimum VegBIEN poster size is 35x54in portrait

		Determine the cost:

			The FedEx Kinkos near NCEAS (1030 State St, Santa Barbara, CA 93101)

				charges the following for posters:

				base: $7.25/sq ft

				lamination: $3/sq ft

				mounting on a board: $8/sq ft

		Including column-based import: make test by_col=1

			If the row-based and column-based imports produce different inserted

			row counts, this usually means that a table is underconstrained

			(the unique indexes don't cover all possible rows).

			This can occur if you didn't use COALESCE(field, null_value) around

			a nullable field in a unique index. See sql_gen.null_sentinels for

			the appropriate null value to use.

	Map spreadsheet generation: make remake

	Missing mappings: make missing_mappings

	Everything (for most complete coverage): make test-all

		(This is primarily useful for regressions that occurred in a previous

		revision, which was committed without running all the tests)

		mod_rewrite:

				a cached redirect is not used. this is because RewriteRule

				redirects are (by default) temporary, but DirectorySlash

				redirects are permanent.

					press Cmd+Shift+Delete

					check only Cache

					press Enter or click Clear Now

	Open WinMerge

	Go to Edit > Options and click Compare in the left sidebar

	Enable "Moved block detection", as described at

		<http://manual.winmerge.org/Configuration.html#d0e5892>.

	Set Whitespace to Ignore change, as described at

		<http://manual.winmerge.org/Configuration.html#d0e5758>.

		make inputs/ACAD/Specimen/logs/steps.by_col.log.sql

	To import and scrub just the test taxonomic names:

	To see a program's usage, run it without arguments

	To remake a directory: make <dir>/remake

	To remake a file: make <file>-remake