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

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

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

    
228
Datasource refreshing:
229
    VegBank:
230
        make inputs/VegBank/vegbank.sql-remake
231
        make inputs/VegBank/reinstall quiet=1 &
232

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

    
311
Testing:
312
    On a development machine, you should put the following in your .profile:
313
        export log= n=2
314
    Mapping process: make test
315
        Including column-based import: make test by_col=1
316
            If the row-based and column-based imports produce different inserted
317
            row counts, this usually means that a table is underconstrained
318
            (the unique indexes don't cover all possible rows).
319
            This can occur if you didn't use COALESCE(field, null_value) around
320
            a nullable field in a unique index. See sql_gen.null_sentinels for
321
            the appropriate null value to use.
322
    Map spreadsheet generation: make remake
323
    Missing mappings: make missing_mappings
324
    Everything (for most complete coverage): make test-all
325

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

    
332
WinMerge setup:
333
    Install WinMerge from <http://winmerge.org/>
334
    Open WinMerge
335
    Go to Edit > Options and click Compare in the left sidebar
336
    Enable "Moved block detection", as described at
337
        <http://manual.winmerge.org/Configuration.html#d0e5892>.
338
    Set Whitespace to Ignore change, as described at
339
        <http://manual.winmerge.org/Configuration.html#d0e5758>.
340

    
341
Documentation:
342
    To generate a Redmine-formatted list of steps for column-based import:
343
        make schemas/public/reinstall
344
        make inputs/ACAD/Specimen/logs/steps.by_col.log.sql
345
    To import and scrub just the test taxonomic names:
346
        inputs/test_taxonomic_names/test_scrub
347

    
348
General:
349
    To see a program's description, read its top-of-file comment
350
    To see a program's usage, run it without arguments
351
    To remake a directory: make <dir>/remake
352
    To remake a file: make <file>-remake
(2-2/5)