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/public.<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
    On local machine:
47
        make inputs/download-logs
48
        make backups/download
49
        Copy backups to jupiter in /data/dev/aaronmk/VegBIEN.backups/
50
    Upload backups/analytical_aggregate.public.<version>.csv to nimoy at
51
        /home/bien/svn/backups/analytical_aggregate.public.<version>.csv
52
    On nimoy:
53
        cd /home/bien/svn/
54
        svn up
55
        In the bien_web DB:
56
            Rename any existing analytical_aggregate table
57
            Create the analytical_aggregate table using its schema in
58
                schemas/vegbien.my.sql
59
        bin/publish_analytical_db backups/analytical_aggregate.public.<version>.csv
60
    If desired, record the import times in inputs/import.stats.xls:
61
        Open inputs/import.stats.xls
62
        Insert a copy of the leftmost Column-based column group before it
63
        Update the import date in the upper-right corner
64
        ./bin/import_times inputs/{.,}*/*/logs/*.r<revision>[.-]*log.sql
65
        Paste the output over the # Rows/Time columns, making sure that the
66
            row counts match up with the previous import's row counts
67
        If the row counts do not match up, insert or reorder rows as needed
68
            until they do
69
        Commit: svn ci -m "inputs/import.stats.xls: Updated import times"
70
    To remake analytical DB: env public=... bin/make_analytical_db &
71
        public should be set to the current import's schema name
72
        To view progress:
73
            tail -f inputs/analytical_db/logs/make_analytical_db.log.sql
74

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

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

    
189
Datasource refreshing:
190
    VegBank:
191
        make inputs/VegBank/vegbank.sql-remake
192
        make inputs/VegBank/reinstall quiet=1 &
193

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

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

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

    
291
Documentation:
292
    To generate a Redmine-formatted list of steps for column-based import:
293
        make inputs/ACAD/Specimen/logs/steps.by_col.log.sql
294
    To import and scrub just the test taxonomic names:
295
        inputs/test_taxonomic_names/test_scrub
296

    
297
General:
298
    To see a program's description, read its top-of-file comment
299
    To see a program's usage, run it without arguments
300
    To remake a directory: make <dir>/remake
301
    To remake a file: make <file>-remake
(2-2/5)