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
    VegCore data dictionary:
12
        Regularly, or whenever the VegCore data dictionary page
13
            (https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/VegCore)
14
            is changed, regenerate mappings/VegCore.csv:
15
            make mappings/VegCore.htm-remake; make mappings/
16
            When moving terms, check that no terms were lost: svn di
17
            svn ci -m "mappings/VegCore.htm: Regenerated from wiki"
18
    Important: Whenever you install a system update that affects PostgreSQL or
19
        any of its dependencies, such as libc, you should restart the PostgreSQL
20
        server. Otherwise, you may get strange errors like "the database system
21
        is in recovery mode" which go away upon reimport, or you may not be able
22
        to access the database as the postgres superuser. This applies to both
23
        Linux and Mac OS X.
24

    
25
Single datasource import:
26
    (Re)import and scrub: make inputs/<datasrc>/reimport_scrub
27
    (Re)import only: make inputs/<datasrc>/reimport
28
    (Re)scrub: make inputs/<datasrc>/rescrub
29
    Note that these commands also work if the datasource is not yet imported
30

    
31
Full database import:
32
    On local machine:
33
        make inputs/upload
34
        make test by_col=1
35
            See note under Testing below
36
    On vegbiendev:
37
    Ensure there are no local modifications: svn st
38
    svn up
39
    make inputs/download
40
    For each newly-uploaded datasource above: make inputs/<datasrc>/reinstall
41
    Update the auxiliary schemas: make schemas/reinstall
42
        The public schema will be installed separately by the import process
43
    Delete imports before the last so they won't bloat the full DB backup:
44
        make backups/vegbien.<version>.backup/remove
45
        To keep a previous import other than the public schema:
46
            export dump_opts='--exclude-schema=public --exclude-schema=<version>'
47
    Make sure there is at least 150GB of disk space on /: df -h
48
        The import schema is 100GB, and may use additional space for temp tables
49
        To free up space, remove backups that have been archived on jupiter:
50
            List backups/ to view older backups
51
            Check their MD5 sums using the steps under On jupiter below
52
            Remove these backups
53
    unset version
54
    Start column-based import: . bin/import_all by_col=1
55
        To use row-based import: . bin/import_all
56
        To stop all running imports: . bin/stop_imports
57
        WARNING: Do NOT run import_all in the background, or the jobs it creates
58
            won't be owned by your shell.
59
        Note that import_all will take up to an hour to import the NCBI backbone
60
            and other metadata before returning control to the shell.
61
    Wait (overnight) for the import to finish
62
    On local machine: make inputs/download-logs
63
    In PostgreSQL:
64
        Check that the provider_count and source tables contain entries for all
65
            inputs
66
        Check that unscrubbed_taxondetermination_view returns no rows
67
            (takes 90 s with LIMIT 1)
68
        Check that there are taxondeterminations whose source_id is
69
            source_by_shortname('TNRS')
70
    tail inputs/{.,}*/*/logs/$version.log.sql
71
    In the output, search for "Command exited with non-zero status"
72
    For inputs that have this, fix the associated bug(s)
73
    If many inputs have errors, discard the current (partial) import:
74
        make schemas/$version/uninstall
75
    Otherwise, continue
76
    make schemas/$version/publish
77
    unset version
78
    sudo backups/fix_perms
79
    make backups/upload
80
    On jupiter:
81
        cd /data/dev/aaronmk/bien/backups
82
        For each newly-archived backup:
83
            make -s <backup>.md5/test
84
            Check that "OK" is printed next to the filename
85
    On nimoy:
86
        cd /home/bien/svn/
87
        svn up
88
        export version=<version>
89
        make backups/analytical_aggregate.$version.csv/download
90
        make -s backups/analytical_aggregate.$version.csv.md5/test
91
        Check that "OK" is printed next to the filename
92
        In the bien_web DB:
93
            Create the analytical_aggregate_<version> table using its schema
94
                in schemas/vegbien.my.sql
95
        env table=analytical_aggregate_$version bin/publish_analytical_db \
96
            backups/analytical_aggregate.$version.csv
97
    If desired, record the import times in inputs/import.stats.xls:
98
        Open inputs/import.stats.xls
99
        Insert a copy of the leftmost "By column" column group before it
100
        export version=<version>
101
        bin/import_date inputs/{.,}*/*/logs/$version.log.sql
102
        Update the import date in the upper-right corner
103
        bin/import_times inputs/{.,}*/*/logs/$version.log.sql
104
        Paste the output over the # Rows/Time columns, making sure that the
105
            row counts match up with the previous import's row counts
106
        If the row counts do not match up, insert or reorder rows as needed
107
            until they do
108
        Commit: svn ci -m "inputs/import.stats.xls: Updated import times"
109
    To scrub unscrubbed taxondeterminations: make scrub by_col=1 &
110
        To view progress:
111
            tail -f inputs/.TNRS/public.unscrubbed_taxondetermination_view/logs/$version.log.sql
112
    To remake analytical DB: bin/make_analytical_db &
113
        To view progress:
114
            tail -f inputs/analytical_db/logs/make_analytical_db.log.sql
115

    
116
Backups:
117
    Archived imports:
118
        Back up: make backups/<version>.backup &
119
            Note: To back up the last import, you must archive it first:
120
                make schemas/rotate
121
        Test: make -s backups/<version>.backup/test &
122
        Restore: make backups/<version>.backup/restore &
123
        Remove: make backups/<version>.backup/remove
124
        Download: make backups/download
125
    TNRS cache:
126
        Back up: make backups/TNRS.backup-remake &
127
        Restore:
128
            yes|make inputs/.TNRS/uninstall
129
            make backups/TNRS.backup/restore &
130
            yes|make schemas/public/reinstall
131
                Must come after TNRS restore to recreate tnrs_input_name view
132
    Full DB:
133
        Back up: make backups/vegbien.<version>.backup &
134
        Test: make -s backups/vegbien.<version>.backup/test &
135
        Restore: make backups/vegbien.<version>.backup/restore &
136
        Download: make backups/download
137
    Import logs:
138
        Download: make inputs/download-logs
139

    
140
Datasource setup:
141
    Add a new datasource: make inputs/<datasrc>/add
142
        <datasrc> may not contain spaces, and should be abbreviated.
143
        If the datasource is a herbarium, <datasrc> should be the herbarium code
144
            as defined by the Index Herbariorum <http://sweetgum.nybg.org/ih/>
145
    For MySQL inputs (exports and live DB connections):
146
        For .sql exports:
147
            Place the original .sql file in _src/ (*not* in _MySQL/)
148
            Create a database for the MySQL export in phpMyAdmin
149
            bin/mysql_bien database <inputs/<datasrc>/_src/export.sql
150
        mkdir inputs/<datasrc>/_MySQL/
151
        cp -p lib/MySQL.{data,schema}.sql.make inputs/<datasrc>/_MySQL/
152
        Edit _MySQL/*.make for the DB connection
153
            For a .sql export, use your local MySQL DB
154
        Skip the Add input data for each table section
155
    For MS Access databases:
156
        Place the .mdb or .accdb file in _src/
157
        Download and install Access To PostgreSQL from
158
            http://www.bullzip.com/download.php
159
        Use Access To PostgreSQL to export the database:
160
            Export just the tables/indexes to inputs/<datasrc>/<file>.schema.sql
161
            Export just the data to inputs/<datasrc>/<file>.data.sql
162
        In <file>.schema.sql, make the following changes:
163
            Replace text "BOOLEAN" with "/*BOOLEAN*/INTEGER"
164
            Replace text "DOUBLE PRECISION NULL" with "DOUBLE PRECISION"
165
        Skip the Add input data for each table section
166
    Add input data for each table present in the datasource:
167
        For .sql exports, you must use the name of the table in the DB export
168
        For CSV files, you can use any name. It's recommended to use a table
169
            name from <https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/VegCSV#Suggested-table-names>
170
        Note that if this table will be joined together with another table, its
171
            name must end in ".src"
172
        make inputs/<datasrc>/<table>/add
173
            Important: DO NOT just create an empty directory named <table>!
174
                This command also creates necessary subdirs, such as logs/.
175
        If the table is in a .sql export: make inputs/<datasrc>/<table>/install
176
            Otherwise, place the CSV(s) for the table in
177
            inputs/<datasrc>/<table>/ OR place a query joining other tables
178
            together in inputs/<datasrc>/<table>/create.sql
179
        Important: When exporting relational databases to CSVs, you MUST ensure
180
            that embedded quotes are escaped by doubling them, *not* by
181
            preceding them with a "\" as is the default in phpMyAdmin
182
        If there are multiple part files for a table, and the header is repeated
183
            in each part, make sure each header is EXACTLY the same.
184
             (If the headers are not the same, the CSV concatenation script
185
             assumes the part files don't have individual headers and treats the
186
             subsequent headers as data rows.)
187
        Add <table> to inputs/<datasrc>/import_order.txt before other tables
188
            that depend on it
189
    Install the staging tables:
190
        make inputs/<datasrc>/reinstall quiet=1 &
191
        To view progress: tail -f inputs/<datasrc>/<table>/logs/install.log.sql
192
        View the logs: tail -n +1 inputs/<datasrc>/*/logs/install.log.sql
193
            tail provides a header line with the filename
194
            +1 starts at the first line, to show the whole file
195
        For every file with an error 'column "..." specified more than once':
196
            Add a header override file "+header.<ext>" in <table>/:
197
                Note: The leading "+" should sort it before the flat files.
198
                    "_" unfortunately sorts *after* capital letters in ASCII.
199
                Create a text file containing the header line of the flat files
200
                Add an ! at the beginning of the line
201
                    This signals cat_csv that this is a header override.
202
                For empty names, use their 0-based column # (by convention)
203
                For duplicate names, add a distinguishing suffix
204
                For long names that collided, rename them to <= 63 chars long
205
                Do NOT make readability changes in this step; that is what the
206
                    map spreadsheets (below) are for.
207
                Save
208
        If you made any changes, re-run the install command above
209
    Auto-create the map spreadsheets: make inputs/<datasrc>/
210
    Map each table's columns:
211
        In each <table>/ subdir, for each "via map" map.csv:
212
            Open the map in a spreadsheet editor
213
            Open the "core map" /mappings/Veg+-VegBIEN.csv
214
            In each row of the via map, set the right column to a value from the
215
                left column of the core map
216
            Save
217
        Regenerate the derived maps: make inputs/<datasrc>/
218
    Accept the test cases:
219
        make inputs/<datasrc>/test
220
            When prompted to "Accept new test output", enter y and press ENTER
221
            If you instead get errors, do one of the following for each one:
222
            -   If the error was due to a bug, fix it
223
            -   Add a SQL function that filters or transforms the invalid data
224
            -   Make an empty mapping for the columns that produced the error.
225
                Put something in the Comments column of the map spreadsheet to
226
                prevent the automatic mapper from auto-removing the mapping.
227
            When accepting tests, it's helpful to use WinMerge
228
                (see WinMerge setup below for configuration)
229
        make inputs/<datasrc>/test by_col=1
230
            If you get errors this time, this always indicates a bug, usually in
231
                the VegBIEN unique constraints or column-based import itself
232
    Add newly-created files: make inputs/<datasrc>/add
233
    Commit: svn ci -m "Added inputs/<datasrc>/" inputs/<datasrc>/
234
    Update vegbiendev:
235
        On vegbiendev: svn up
236
        On local machine: make inputs/upload
237
        On vegbiendev:
238
            Follow the steps under Install the staging tables above
239
            make inputs/<datasrc>/test
240

    
241
Datasource refreshing:
242
    VegBank:
243
        make inputs/VegBank/vegbank.sql-remake
244
        make inputs/VegBank/reinstall quiet=1 &
245

    
246
Schema changes:
247
    When changing the analytical views, run sync_analytical_..._to_view()
248
        to update the corresponding table
249
    Remember to update the following files with any renamings:
250
        schemas/filter_ERD.csv
251
        mappings/VegCore-VegBIEN.csv
252
        mappings/verify.*.sql
253
    Regenerate schema from installed DB: make schemas/remake
254
    Reinstall DB from schema: make schemas/public/reinstall schemas/reinstall
255
        WARNING: This will delete the current public schema of your VegBIEN DB!
256
    Reinstall staging tables: . bin/reinstall_all
257
    Sync ERD with vegbien.sql schema:
258
        Run make schemas/vegbien.my.sql
259
        Open schemas/vegbien.ERD.mwb in MySQLWorkbench
260
        Go to File > Export > Synchronize With SQL CREATE Script...
261
        For Input File, select schemas/vegbien.my.sql
262
        Click Continue
263
        In the changes list, select each table with an arrow next to it
264
        Click Update Model
265
        Click Continue
266
        Note: The generated SQL script will be empty because we are syncing in
267
            the opposite direction
268
        Click Execute
269
        Reposition any lines that have been reset
270
        Add any new tables by dragging them from the Catalog in the left sidebar
271
            to the diagram
272
        Remove any deleted tables by right-clicking the table's diagram element,
273
            selecting Delete '<table name>', and clicking Delete
274
        Save
275
        If desired, update the graphical ERD exports (see below)
276
    Update graphical ERD exports:
277
        Go to File > Export > Export as PNG...
278
        Select schemas/vegbien.ERD.png and click Save
279
        Go to File > Export > Export as SVG...
280
        Select schemas/vegbien.ERD.svg and click Save
281
        Go to File > Export > Export as Single Page PDF...
282
        Select schemas/vegbien.ERD.1_pg.pdf and click Save
283
        Go to File > Print...
284
        In the lower left corner, click PDF > Save as PDF...
285
        Set the Title and Author to ""
286
        Select schemas/vegbien.ERD.pdf and click Save
287
        Commit: svn ci -m "schemas/vegbien.ERD.mwb: Regenerated exports"
288
    Refactoring tips:
289
        To rename a table:
290
            In vegbien.sql, do the following:
291
                Replace regexp (?<=_|\b)<old>(?=_|\b) with <new>
292
                    This is necessary because the table name is *everywhere*
293
                Search for <new>
294
                Manually change back any replacements inside comments
295
        To rename a column:
296
            Rename the column: ALTER TABLE <table> RENAME <old> TO <new>;
297
            Recreate any foreign key for the column, removing CONSTRAINT <name>
298
                This resets the foreign key name using the new column name
299
    Creating a poster of the ERD:
300
        Determine the poster size:
301
            Measure the line height (from the bottom of one line to the bottom
302
                of another): 16.3cm/24 lines = 0.679cm
303
            Measure the height of the ERD: 35.4cm*2 = 70.8cm
304
            Zoom in as far as possible
305
            Measure the height of a capital letter: 3.5mm
306
            Measure the line height: 8.5mm
307
            Calculate the text's fraction of the line height: 3.5mm/8.5mm = 0.41
308
            Calculate the text height: 0.679cm*0.41 = 0.28cm
309
            Calculate the text height's fraction of the ERD height:
310
                0.28cm/70.8cm = 0.0040
311
            Measure the text height on the *VegBank* ERD poster: 5.5mm = 0.55cm
312
            Calculate the VegBIEN poster height to make the text the same size:
313
                0.55cm/0.0040 = 137.5cm H; *1in/2.54cm = 54.1in H
314
            The ERD aspect ratio is 11 in W x (2*8.5in H) = 11x17 portrait
315
            Calculate the VegBIEN poster width: 54.1in H*11W/17H = 35.0in W
316
            The minimum VegBIEN poster size is 35x54in portrait
317
        Determine the cost:
318
            The FedEx Kinkos near NCEAS (1030 State St, Santa Barbara, CA 93101)
319
                charges the following for posters:
320
                base: $7.25/sq ft
321
                lamination: $3/sq ft
322
                mounting on a board: $8/sq ft
323

    
324
Testing:
325
    On a development machine, you should put the following in your .profile:
326
        export log= n=2
327
    Mapping process: make test
328
        Including column-based import: make test by_col=1
329
            If the row-based and column-based imports produce different inserted
330
            row counts, this usually means that a table is underconstrained
331
            (the unique indexes don't cover all possible rows).
332
            This can occur if you didn't use COALESCE(field, null_value) around
333
            a nullable field in a unique index. See sql_gen.null_sentinels for
334
            the appropriate null value to use.
335
    Map spreadsheet generation: make remake
336
    Missing mappings: make missing_mappings
337
    Everything (for most complete coverage): make test-all
338

    
339
Debugging:
340
    "Binary chop" debugging:
341
        (This is primarily useful for regressions that occurred in a previous
342
        revision, which was committed without running all the tests)
343
        svn up -r <rev>; make inputs/.TNRS/reinstall; make schemas/public/reinstall; make <failed-test>.xml
344

    
345
WinMerge setup:
346
    Install WinMerge from <http://winmerge.org/>
347
    Open WinMerge
348
    Go to Edit > Options and click Compare in the left sidebar
349
    Enable "Moved block detection", as described at
350
        <http://manual.winmerge.org/Configuration.html#d0e5892>.
351
    Set Whitespace to Ignore change, as described at
352
        <http://manual.winmerge.org/Configuration.html#d0e5758>.
353

    
354
Documentation:
355
    To generate a Redmine-formatted list of steps for column-based import:
356
        make schemas/public/reinstall
357
        make inputs/ACAD/Specimen/logs/steps.by_col.log.sql
358
    To import and scrub just the test taxonomic names:
359
        inputs/test_taxonomic_names/test_scrub
360

    
361
General:
362
    To see a program's description, read its top-of-file comment
363
    To see a program's usage, run it without arguments
364
    To remake a directory: make <dir>/remake
365
    To remake a file: make <file>-remake
(2-2/6)