Project

General

Profile

1
Installation:
2
    Install: make install
3
        WARNING: This will delete the current public schema of your VegBIEN DB!
4
    Uninstall: make uninstall
5
        WARNING: This will delete your entire VegBIEN DB!
6
        This includes all archived imports and staging tables.
7

    
8
Maintenance:
9
    Important: Whenever you install a system update that affects PostgreSQL or
10
        any of its dependencies, such as libc, you should restart the PostgreSQL
11
        server. Otherwise, you may get strange errors like "the database system
12
        is in recovery mode" which go away upon reimport.
13

    
14
Data import:
15
    On local machine:
16
        make test by_col=1
17
            See note under Testing below
18
    On vegbiendev:
19
    svn up
20
    make inputs/upload
21
    For each newly-uploaded datasource: make inputs/<datasrc>/reinstall
22
    Update the schemas: make schemas/reinstall
23
        WARNING: This will delete the current public schema of your VegBIEN DB!
24
        To save it: make schemas/rotate
25
    Start column-based import: . bin/import_all by_col=1
26
        To use row-based import: . bin/import_all
27
        To stop all running imports: . bin/stop_imports
28
        WARNING: Do NOT run import_all in the background, or the jobs it creates
29
            won't be owned by your shell.
30
        Note that import_all will several hours to import the NCBI backbone and
31
            TNRS names before returning control to the shell.
32
    Wait (overnight) for the import to finish
33
    tail inputs/{.,}*/*/logs/*.r<revision>[.-]*log.sql
34
    Check that every input's log ends in "Encountered 0 error(s)"
35
    If many do not, fix the bug and discard the current (partial) import:
36
        make schemas/public/reinstall
37
    Otherwise, continue
38
    Determine the import name:
39
        bin/import_name inputs/{.,}*/*/logs/*.r<revision>[.-]*log.sql
40
    Archive the last import: make schemas/rename/public.<import_name>
41
    Delete previous imports so they won't bloat the full DB backup:
42
        make backups/vegbien.<version>.backup/remove
43
    make backups/TNRS.backup-remake &
44
    make backups/vegbien.<version>.backup/test &
45
    env public=public.<version> bin/export_analytical_db &
46
    make backups/upload
47
    On jupiter:
48
        cd /data/dev/aaronmk/VegBIEN.backups
49
        For each newly-archived backup:
50
            make <backup>.md5/test
51
            Check that "OK" is printed next to the filename
52
    On local machine:
53
        make inputs/download-logs
54
    On nimoy:
55
        cd /home/bien/svn/
56
        svn up
57
        make backups/analytical_aggregate.public.<version>.csv/download
58
        make backups/analytical_aggregate.public.<version>.csv.md5/test
59
        Check that "OK" is printed next to the filename
60
        In the bien_web DB:
61
            Create the analytical_aggregate_r<revision> table using its schema
62
                in schemas/vegbien.my.sql
63
        env table=analytical_aggregate_r<revision> bin/publish_analytical_db \
64
            backups/analytical_aggregate.public.<version>.csv
65
    If desired, record the import times in inputs/import.stats.xls:
66
        Open inputs/import.stats.xls
67
        Insert a copy of the leftmost Column-based column group before it
68
        Update the import date in the upper-right corner
69
        ./bin/import_times inputs/{.,}*/*/logs/*.r<revision>[.-]*log.sql
70
        Paste the output over the # Rows/Time columns, making sure that the
71
            row counts match up with the previous import's row counts
72
        If the row counts do not match up, insert or reorder rows as needed
73
            until they do
74
        Commit: svn ci -m "inputs/import.stats.xls: Updated import times"
75
    To remake analytical DB: env public=... bin/make_analytical_db &
76
        public should be set to the current import's schema name
77
        To view progress:
78
            tail -f inputs/analytical_db/logs/make_analytical_db.log.sql
79

    
80
Backups:
81
    Archived imports:
82
        Back up: make backups/public.<date>.backup &
83
            Note: To back up the last import, you must archive it first:
84
                make schemas/rotate
85
        Test: make backups/public.<date>.backup/test &
86
        Restore: make backups/public.<date>.backup/restore &
87
        Remove: make backups/public.<date>.backup/remove
88
        Download: make backups/download
89
    TNRS cache:
90
        Back up: make backups/TNRS.backup-remake &
91
        Restore:
92
            yes|make inputs/.TNRS/uninstall
93
            make backups/TNRS.backup/restore &
94
            yes|make schemas/public/reinstall
95
                Must come after TNRS restore to recreate tnrs_input_name view
96
    Full DB:
97
        Back up: make backups/vegbien.<date>.backup &
98
        Test: make backups/vegbien.<date>.backup/test &
99
        Restore: make backups/vegbien.<date>.backup/restore &
100
        Download: make backups/download
101
    Import logs:
102
        Download: make inputs/download-logs
103

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

    
194
Datasource refreshing:
195
    VegBank:
196
        make inputs/VegBank/vegbank.sql-remake
197
        make inputs/VegBank/reinstall quiet=1 &
198

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

    
274
Testing:
275
    Mapping process: make test
276
        Including column-based import: make test by_col=1
277
            If the row-based and column-based imports produce different inserted
278
            row counts, this usually means that a table is underconstrained
279
            (the unique indexes don't cover all possible rows).
280
            This can occur if you didn't use COALESCE(field, null_value) around
281
            a nullable field in a unique index. See sql_gen.null_sentinels for
282
            the appropriate null value to use.
283
    Map spreadsheet generation: make remake
284
    Missing mappings: make missing_mappings
285
    Everything (for most complete coverage): make test-all
286

    
287
WinMerge setup:
288
    Install WinMerge from <http://winmerge.org/>
289
    Open WinMerge
290
    Go to Edit > Options and click Compare in the left sidebar
291
    Enable "Moved block detection", as described at
292
        <http://manual.winmerge.org/Configuration.html#d0e5892>.
293
    Set Whitespace to Ignore change, as described at
294
        <http://manual.winmerge.org/Configuration.html#d0e5758>.
295

    
296
Documentation:
297
    To generate a Redmine-formatted list of steps for column-based import:
298
        make inputs/ACAD/Specimen/logs/steps.by_col.log.sql
299
    To import and scrub just the test taxonomic names:
300
        inputs/test_taxonomic_names/test_scrub
301

    
302
General:
303
    To see a program's description, read its top-of-file comment
304
    To see a program's usage, run it without arguments
305
    To remake a directory: make <dir>/remake
306
    To remake a file: make <file>-remake
(2-2/5)