Project

General

Profile

1 702 aaronmk
Installation:
2 6886 aaronmk
    Check out svn: svn co https://code.nceas.ucsb.edu/code/projects/bien
3 6887 aaronmk
    cd bien/
4 702 aaronmk
    Install: make install
5 3370 aaronmk
        WARNING: This will delete the current public schema of your VegBIEN DB!
6 702 aaronmk
    Uninstall: make uninstall
7 3370 aaronmk
        WARNING: This will delete your entire VegBIEN DB!
8 3374 aaronmk
        This includes all archived imports and staging tables.
9 554 aaronmk
10 3674 aaronmk
Maintenance:
11 6846 aaronmk
    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 6856 aaronmk
            make mappings/VegCore.htm-remake; make mappings/
16 6987 aaronmk
            svn ci -m "mappings/VegCore.csv: Regenerated from wiki"
17 3674 aaronmk
    Important: Whenever you install a system update that affects PostgreSQL or
18
        any of its dependencies, such as libc, you should restart the PostgreSQL
19
        server. Otherwise, you may get strange errors like "the database system
20 6820 aaronmk
        is in recovery mode" which go away upon reimport, or you may not be able
21 6821 aaronmk
        to access the database as the postgres superuser. This applies to both
22
        Linux and Mac OS X.
23 3674 aaronmk
24 7287 aaronmk
Single datasource import:
25
    (Re)import and scrub: make inputs/<datasrc>/reimport_scrub
26
    (Re)import only: make inputs/<datasrc>/reimport
27
    (Re)scrub: make inputs/<datasrc>/rescrub
28
    Note that these commands also work if the datasource is not yet imported
29
30
Full database import:
31 4487 aaronmk
    On local machine:
32 7114 aaronmk
        make inputs/upload
33 4487 aaronmk
        make test by_col=1
34 5016 aaronmk
            See note under Testing below
35 4487 aaronmk
    On vegbiendev:
36 7032 aaronmk
    Ensure there are no local modifications: svn st
37 4482 aaronmk
    svn up
38 7114 aaronmk
    For each newly-uploaded datasource above: make inputs/<datasrc>/reinstall
39 6952 aaronmk
    Update the auxiliary schemas: make schemas/reinstall
40
        The public schema will be installed separately by the import process
41 6962 aaronmk
    Delete imports before the last so they won't bloat the full DB backup:
42
        make backups/vegbien.<version>.backup/remove
43 7205 aaronmk
        To keep a previous import other than the public schema:
44
            export dump_opts='--exclude-schema=public --exclude-schema=<version>'
45 7224 aaronmk
    Make sure there is at least 150GB of disk space on /: df -h
46 7300 aaronmk
        The import schema is 100GB, and may use additional space for temp tables
47 6804 aaronmk
        To free up space, remove backups that have been archived on jupiter:
48
            List backups/ to view older backups
49
            Check their MD5 sums using the steps under On jupiter below
50
            Remove these backups
51 6995 aaronmk
    unset version
52 5206 aaronmk
    Start column-based import: . bin/import_all by_col=1
53
        To use row-based import: . bin/import_all
54 4481 aaronmk
        To stop all running imports: . bin/stop_imports
55 5318 aaronmk
        WARNING: Do NOT run import_all in the background, or the jobs it creates
56
            won't be owned by your shell.
57 6797 aaronmk
        Note that import_all will take several hours to import the NCBI backbone
58
            and TNRS names before returning control to the shell.
59 4481 aaronmk
    Wait (overnight) for the import to finish
60 6796 aaronmk
    On local machine: make inputs/download-logs
61 7318 aaronmk
    In PostgreSQL:
62
        Check that the provider_count and source tables contain entries for all
63
            inputs
64
        Check that unscrubbed_taxondetermination_view returns no rows
65 7319 aaronmk
        Check that there are taxondeterminations whose source_id is
66
            source_by_shortname('TNRS')
67 6964 aaronmk
    tail inputs/{.,}*/*/logs/$version.log.sql
68 6799 aaronmk
    In the output, search for "Command exited with non-zero status"
69
    For inputs that have this, fix the associated bug(s)
70
    If many inputs have errors, discard the current (partial) import:
71 6953 aaronmk
        make schemas/$version/uninstall
72 6103 aaronmk
    Otherwise, continue
73 6959 aaronmk
    make schemas/$version/publish
74 7299 aaronmk
    unset version
75 6848 aaronmk
    sudo backups/fix_perms
76 6666 aaronmk
    make backups/upload
77 6719 aaronmk
    On jupiter:
78 6888 aaronmk
        cd /data/dev/aaronmk/bien/backups
79 6719 aaronmk
        For each newly-archived backup:
80 7326 aaronmk
            make -s <backup>.md5/test
81 6719 aaronmk
            Check that "OK" is printed next to the filename
82 6494 aaronmk
    On nimoy:
83
        cd /home/bien/svn/
84
        svn up
85 6993 aaronmk
        export version=<version>
86 6948 aaronmk
        make backups/analytical_aggregate.$version.csv/download
87 7326 aaronmk
        make -s backups/analytical_aggregate.$version.csv.md5/test
88 6723 aaronmk
        Check that "OK" is printed next to the filename
89 6494 aaronmk
        In the bien_web DB:
90 6994 aaronmk
            Create the analytical_aggregate_<version> table using its schema
91 6652 aaronmk
                in schemas/vegbien.my.sql
92 6948 aaronmk
        env table=analytical_aggregate_$version bin/publish_analytical_db \
93
            backups/analytical_aggregate.$version.csv
94 4677 aaronmk
    If desired, record the import times in inputs/import.stats.xls:
95
        Open inputs/import.stats.xls
96 7027 aaronmk
        Insert a copy of the leftmost "By column" column group before it
97 7344 aaronmk
        export version=<version>
98 6996 aaronmk
        bin/import_date inputs/{.,}*/*/logs/$version.log.sql
99 4677 aaronmk
        Update the import date in the upper-right corner
100 6996 aaronmk
        bin/import_times inputs/{.,}*/*/logs/$version.log.sql
101 4677 aaronmk
        Paste the output over the # Rows/Time columns, making sure that the
102
            row counts match up with the previous import's row counts
103
        If the row counts do not match up, insert or reorder rows as needed
104
            until they do
105 4777 aaronmk
        Commit: svn ci -m "inputs/import.stats.xls: Updated import times"
106 7325 aaronmk
    To scrub unscrubbed taxondeterminations: make scrub by_col=1 &
107
        To view progress:
108
            tail -f inputs/.TNRS/public.unscrubbed_taxondetermination_view/logs/$version.log.sql
109 6956 aaronmk
    To remake analytical DB: bin/make_analytical_db &
110 6212 aaronmk
        To view progress:
111
            tail -f inputs/analytical_db/logs/make_analytical_db.log.sql
112 3381 aaronmk
113 3545 aaronmk
Backups:
114 3408 aaronmk
    Archived imports:
115 6942 aaronmk
        Back up: make backups/<version>.backup &
116 6516 aaronmk
            Note: To back up the last import, you must archive it first:
117
                make schemas/rotate
118 7326 aaronmk
        Test: make -s backups/<version>.backup/test &
119 6942 aaronmk
        Restore: make backups/<version>.backup/restore &
120
        Remove: make backups/<version>.backup/remove
121 3701 aaronmk
        Download: make backups/download
122 5801 aaronmk
    TNRS cache:
123
        Back up: make backups/TNRS.backup-remake &
124
        Restore:
125
            yes|make inputs/.TNRS/uninstall
126
            make backups/TNRS.backup/restore &
127
            yes|make schemas/public/reinstall
128
                Must come after TNRS restore to recreate tnrs_input_name view
129 3408 aaronmk
    Full DB:
130 6942 aaronmk
        Back up: make backups/vegbien.<version>.backup &
131 7326 aaronmk
        Test: make -s backups/vegbien.<version>.backup/test &
132 6942 aaronmk
        Restore: make backups/vegbien.<version>.backup/restore &
133 3701 aaronmk
        Download: make backups/download
134 3698 aaronmk
    Import logs:
135
        Download: make inputs/download-logs
136 554 aaronmk
137 1773 aaronmk
Datasource setup:
138 4219 aaronmk
    Add a new datasource: make inputs/<datasrc>/add
139
        <datasrc> may not contain spaces, and should be abbreviated.
140
        If the datasource is a herbarium, <datasrc> should be the herbarium code
141
            as defined by the Index Herbariorum <http://sweetgum.nybg.org/ih/>
142 6366 aaronmk
    For MySQL inputs (exports and live DB connections):
143
        For .sql exports:
144
            Place the original .sql file in _src/ (*not* in _MySQL/)
145
            Create a database for the MySQL export in phpMyAdmin
146
            mysql -p database <inputs/<datasrc>/_src/export.sql
147
        mkdir inputs/<datasrc>/_MySQL/
148
        cp -p lib/MySQL.{data,schema}.sql.make inputs/<datasrc>/_MySQL/
149
        Edit _MySQL/*.make for the DB connection
150
            For a .sql export, use your local MySQL DB
151 6372 aaronmk
        Install the export according to Install the staging tables below
152 4218 aaronmk
    Add input data for each table present in the datasource:
153 6365 aaronmk
        For .sql exports, you must use the name of the table in the DB export
154
        For CSV files, you can use any name. It's recommended to use a table
155
            name from <https://projects.nceas.ucsb.edu/nceas/projects/bien/wiki/VegCSV#Suggested-table-names>
156 4264 aaronmk
        Note that if this table will be joined together with another table, its
157
            name must end in ".src"
158 4219 aaronmk
        make inputs/<datasrc>/<table>/add
159 4342 aaronmk
            Important: DO NOT just create an empty directory named <table>!
160
                This command also creates necessary subdirs, such as logs/.
161 6373 aaronmk
        If the table is in a .sql export: make inputs/<datasrc>/<table>/install
162
            Otherwise, place the CSV(s) for the table in
163 6370 aaronmk
            inputs/<datasrc>/<table>/ OR place a query joining other tables
164
            together in inputs/<datasrc>/<table>/create.sql
165 4212 aaronmk
        Important: When exporting relational databases to CSVs, you MUST ensure
166
            that embedded quotes are escaped by doubling them, *not* by
167
            preceding them with a "\" as is the default in phpMyAdmin
168 3612 aaronmk
        If there are multiple part files for a table, and the header is repeated
169
            in each part, make sure each header is EXACTLY the same.
170
             (If the headers are not the same, the CSV concatenation script
171
             assumes the part files don't have individual headers and treats the
172
             subsequent headers as data rows.)
173 4255 aaronmk
        Add <table> to inputs/<datasrc>/import_order.txt before other tables
174 4220 aaronmk
            that depend on it
175 3611 aaronmk
    Install the staging tables:
176 4219 aaronmk
        make inputs/<datasrc>/reinstall quiet=1 &
177
        To view progress: tail -f inputs/<datasrc>/<table>/logs/install.log.sql
178
        View the logs: tail -n +1 inputs/<datasrc>/*/logs/install.log.sql
179 3611 aaronmk
            tail provides a header line with the filename
180
            +1 starts at the first line, to show the whole file
181
        For every file with an error 'column "..." specified more than once':
182 4182 aaronmk
            Add a header override file "+header.<ext>" in <table>/:
183 3611 aaronmk
                Note: The leading "+" should sort it before the flat files.
184
                    "_" unfortunately sorts *after* capital letters in ASCII.
185
                Create a text file containing the header line of the flat files
186
                Add an ! at the beginning of the line
187
                    This signals cat_csv that this is a header override.
188
                For empty names, use their 0-based column # (by convention)
189
                For duplicate names, add a distinguishing suffix
190
                For long names that collided, rename them to <= 63 chars long
191
                Do NOT make readability changes in this step; that is what the
192
                    map spreadsheets (below) are for.
193
                Save
194
        If you made any changes, re-run the install command above
195 6015 aaronmk
    Auto-create the map spreadsheets: make inputs/<datasrc>/
196 3576 aaronmk
    Map each table's columns:
197 4125 aaronmk
        In each <table>/ subdir, for each "via map" map.csv:
198 3576 aaronmk
            Open the map in a spreadsheet editor
199 4125 aaronmk
            Open the "core map" /mappings/Veg+-VegBIEN.csv
200 3576 aaronmk
            In each row of the via map, set the right column to a value from the
201
                left column of the core map
202
            Save
203 4219 aaronmk
        Regenerate the derived maps: make inputs/<datasrc>/
204 3593 aaronmk
    Accept the test cases:
205 4219 aaronmk
        make inputs/<datasrc>/test
206 3593 aaronmk
            When prompted to "Accept new test output", enter y and press ENTER
207 3690 aaronmk
            If you instead get errors, do one of the following for each one:
208
            -   If the error was due to a bug, fix it
209
            -   Add a SQL function that filters or transforms the invalid data
210
            -   Make an empty mapping for the columns that produced the error.
211
                Put something in the Comments column of the map spreadsheet to
212
                prevent the automatic mapper from auto-removing the mapping.
213 3783 aaronmk
            When accepting tests, it's helpful to use WinMerge
214
                (see WinMerge setup below for configuration)
215 4476 aaronmk
        make inputs/<datasrc>/test by_col=1
216
            If you get errors this time, this always indicates a bug, usually in
217 6368 aaronmk
                the VegBIEN unique constraints or column-based import itself
218 5881 aaronmk
    Add newly-created files: make inputs/<datasrc>/add
219 4219 aaronmk
    Commit: svn ci -m "Added inputs/<datasrc>/" inputs/<datasrc>/
220 3585 aaronmk
    Update vegbiendev:
221
        On vegbiendev: svn up
222
        On local machine: make inputs/upload
223 4291 aaronmk
        On vegbiendev:
224
            Follow the steps under Install the staging tables above
225
            make inputs/<datasrc>/test
226 1773 aaronmk
227 6484 aaronmk
Datasource refreshing:
228
    VegBank:
229
        make inputs/VegBank/vegbank.sql-remake
230
        make inputs/VegBank/reinstall quiet=1 &
231
232 702 aaronmk
Schema changes:
233 7347 aaronmk
    When changing the analytical views, run sync_analytical_..._to_view()
234
        to update the corresponding table
235 5227 aaronmk
    Remember to update the following files with any renamings:
236
        schemas/filter_ERD.csv
237
        mappings/VegCore-VegBIEN.csv
238 6058 aaronmk
        mappings/verify.*.sql
239 702 aaronmk
    Regenerate schema from installed DB: make schemas/remake
240 6951 aaronmk
    Reinstall DB from schema: make schemas/public/reinstall schemas/reinstall
241 3370 aaronmk
        WARNING: This will delete the current public schema of your VegBIEN DB!
242 3589 aaronmk
    Reinstall staging tables: . bin/reinstall_all
243 702 aaronmk
    Sync ERD with vegbien.sql schema:
244
        Run make schemas/vegbien.my.sql
245
        Open schemas/vegbien.ERD.mwb in MySQLWorkbench
246
        Go to File > Export > Synchronize With SQL CREATE Script...
247
        For Input File, select schemas/vegbien.my.sql
248
        Click Continue
249 6369 aaronmk
        In the changes list, select each table with an arrow next to it
250 702 aaronmk
        Click Update Model
251
        Click Continue
252
        Note: The generated SQL script will be empty because we are syncing in
253
            the opposite direction
254
        Click Execute
255
        Reposition any lines that have been reset
256
        Add any new tables by dragging them from the Catalog in the left sidebar
257
            to the diagram
258
        Remove any deleted tables by right-clicking the table's diagram element,
259
            selecting Delete '<table name>', and clicking Delete
260
        Save
261 1774 aaronmk
        If desired, update the graphical ERD exports (see below)
262
    Update graphical ERD exports:
263 702 aaronmk
        Go to File > Export > Export as PNG...
264 1774 aaronmk
        Select schemas/vegbien.ERD.png and click Save
265 702 aaronmk
        Go to File > Export > Export as SVG...
266 1774 aaronmk
        Select schemas/vegbien.ERD.svg and click Save
267 702 aaronmk
        Go to File > Export > Export as Single Page PDF...
268 4087 aaronmk
        Select schemas/vegbien.ERD.1_pg.pdf and click Save
269 1774 aaronmk
        Go to File > Print...
270
        In the lower left corner, click PDF > Save as PDF...
271
        Set the Title and Author to ""
272 4087 aaronmk
        Select schemas/vegbien.ERD.pdf and click Save
273 7003 aaronmk
        Commit: svn ci -m "schemas/vegbien.ERD.mwb: Regenerated exports"
274 5226 aaronmk
    Refactoring tips:
275
        To rename a table:
276
            In vegbien.sql, do the following:
277
                Replace regexp (?<=_|\b)<old>(?=_|\b) with <new>
278
                    This is necessary because the table name is *everywhere*
279
                Search for <new>
280
                Manually change back any replacements inside comments
281
        To rename a column:
282
            Rename the column: ALTER TABLE <table> RENAME <old> TO <new>;
283
            Recreate any foreign key for the column, removing CONSTRAINT <name>
284
                This resets the foreign key name using the new column name
285 6320 aaronmk
    Creating a poster of the ERD:
286 6326 aaronmk
        Determine the poster size:
287 6320 aaronmk
            Measure the line height (from the bottom of one line to the bottom
288
                of another): 16.3cm/24 lines = 0.679cm
289
            Measure the height of the ERD: 35.4cm*2 = 70.8cm
290
            Zoom in as far as possible
291
            Measure the height of a capital letter: 3.5mm
292
            Measure the line height: 8.5mm
293
            Calculate the text's fraction of the line height: 3.5mm/8.5mm = 0.41
294
            Calculate the text height: 0.679cm*0.41 = 0.28cm
295
            Calculate the text height's fraction of the ERD height:
296
                0.28cm/70.8cm = 0.0040
297
            Measure the text height on the *VegBank* ERD poster: 5.5mm = 0.55cm
298
            Calculate the VegBIEN poster height to make the text the same size:
299
                0.55cm/0.0040 = 137.5cm H; *1in/2.54cm = 54.1in H
300
            The ERD aspect ratio is 11 in W x (2*8.5in H) = 11x17 portrait
301
            Calculate the VegBIEN poster width: 54.1in H*11W/17H = 35.0in W
302
            The minimum VegBIEN poster size is 35x54in portrait
303 6327 aaronmk
        Determine the cost:
304
            The FedEx Kinkos near NCEAS (1030 State St, Santa Barbara, CA 93101)
305
                charges the following for posters:
306
                base: $7.25/sq ft
307
                lamination: $3/sq ft
308
                mounting on a board: $8/sq ft
309 203 aaronmk
310 1459 aaronmk
Testing:
311 7090 aaronmk
    On a development machine, you should put the following in your .profile:
312 7122 aaronmk
        export log= n=2
313 1459 aaronmk
    Mapping process: make test
314 4292 aaronmk
        Including column-based import: make test by_col=1
315 4985 aaronmk
            If the row-based and column-based imports produce different inserted
316
            row counts, this usually means that a table is underconstrained
317
            (the unique indexes don't cover all possible rows).
318
            This can occur if you didn't use COALESCE(field, null_value) around
319
            a nullable field in a unique index. See sql_gen.null_sentinels for
320
            the appropriate null value to use.
321 1459 aaronmk
    Map spreadsheet generation: make remake
322 1744 aaronmk
    Missing mappings: make missing_mappings
323 1459 aaronmk
    Everything (for most complete coverage): make test-all
324 702 aaronmk
325 7183 aaronmk
Debugging:
326
    "Binary chop" debugging:
327
        (This is primarily useful for regressions that occurred in a previous
328
        revision, which was committed without running all the tests)
329
        svn up -r <rev>; make inputs/.TNRS/reinstall; make schemas/public/reinstall; make <failed-test>.xml
330
331 3783 aaronmk
WinMerge setup:
332
    Install WinMerge from <http://winmerge.org/>
333 3785 aaronmk
    Open WinMerge
334
    Go to Edit > Options and click Compare in the left sidebar
335 3783 aaronmk
    Enable "Moved block detection", as described at
336
        <http://manual.winmerge.org/Configuration.html#d0e5892>.
337 3784 aaronmk
    Set Whitespace to Ignore change, as described at
338
        <http://manual.winmerge.org/Configuration.html#d0e5758>.
339 3783 aaronmk
340 3133 aaronmk
Documentation:
341
    To generate a Redmine-formatted list of steps for column-based import:
342 7178 aaronmk
        make schemas/public/reinstall
343 6291 aaronmk
        make inputs/ACAD/Specimen/logs/steps.by_col.log.sql
344 5210 aaronmk
    To import and scrub just the test taxonomic names:
345 5415 aaronmk
        inputs/test_taxonomic_names/test_scrub
346 3133 aaronmk
347 702 aaronmk
General:
348
    To see a program's description, read its top-of-file comment
349
    To see a program's usage, run it without arguments
350 3389 aaronmk
    To remake a directory: make <dir>/remake
351
    To remake a file: make <file>-remake