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
            svn ci -m "mappings/VegCore.csv: Regenerated from wiki"
17
    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
        is in recovery mode" which go away upon reimport, or you may not be able
21
        to access the database as the postgres superuser. This applies to both
22
        Linux and Mac OS X.
23

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

    
94
Backups:
95
    Archived imports:
96
        Back up: make backups/<version>.backup &
97
            Note: To back up the last import, you must archive it first:
98
                make schemas/rotate
99
        Test: make backups/<version>.backup/test &
100
        Restore: make backups/<version>.backup/restore &
101
        Remove: make backups/<version>.backup/remove
102
        Download: make backups/download
103
    TNRS cache:
104
        Back up: make backups/TNRS.backup-remake &
105
        Restore:
106
            yes|make inputs/.TNRS/uninstall
107
            make backups/TNRS.backup/restore &
108
            yes|make schemas/public/reinstall
109
                Must come after TNRS restore to recreate tnrs_input_name view
110
    Full DB:
111
        Back up: make backups/vegbien.<version>.backup &
112
        Test: make backups/vegbien.<version>.backup/test &
113
        Restore: make backups/vegbien.<version>.backup/restore &
114
        Download: make backups/download
115
    Import logs:
116
        Download: make inputs/download-logs
117

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

    
208
Datasource refreshing:
209
    VegBank:
210
        make inputs/VegBank/vegbank.sql-remake
211
        make inputs/VegBank/reinstall quiet=1 &
212

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

    
288
Testing:
289
    Mapping process: make test
290
        Including column-based import: make test by_col=1
291
            If the row-based and column-based imports produce different inserted
292
            row counts, this usually means that a table is underconstrained
293
            (the unique indexes don't cover all possible rows).
294
            This can occur if you didn't use COALESCE(field, null_value) around
295
            a nullable field in a unique index. See sql_gen.null_sentinels for
296
            the appropriate null value to use.
297
    Map spreadsheet generation: make remake
298
    Missing mappings: make missing_mappings
299
    Everything (for most complete coverage): make test-all
300

    
301
WinMerge setup:
302
    Install WinMerge from <http://winmerge.org/>
303
    Open WinMerge
304
    Go to Edit > Options and click Compare in the left sidebar
305
    Enable "Moved block detection", as described at
306
        <http://manual.winmerge.org/Configuration.html#d0e5892>.
307
    Set Whitespace to Ignore change, as described at
308
        <http://manual.winmerge.org/Configuration.html#d0e5758>.
309

    
310
Documentation:
311
    To generate a Redmine-formatted list of steps for column-based import:
312
        make inputs/ACAD/Specimen/logs/steps.by_col.log.sql
313
    To import and scrub just the test taxonomic names:
314
        inputs/test_taxonomic_names/test_scrub
315

    
316
General:
317
    To see a program's description, read its top-of-file comment
318
    To see a program's usage, run it without arguments
319
    To remake a directory: make <dir>/remake
320
    To remake a file: make <file>-remake
(2-2/5)