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
    Important: Whenever you install a system update that affects PostgreSQL or
17
        any of its dependencies, such as libc, you should restart the PostgreSQL
18
        server. Otherwise, you may get strange errors like "the database system
19
        is in recovery mode" which go away upon reimport, or you may not be able
20
        to access the database as the postgres superuser. This applies to both
21
        Linux and Mac OS X.
22

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

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

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

    
211
Datasource refreshing:
212
    VegBank:
213
        make inputs/VegBank/vegbank.sql-remake
214
        make inputs/VegBank/reinstall quiet=1 &
215

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

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

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

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

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