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
    Update the schemas: make schemas/reinstall
21
        WARNING: This will delete the current public schema of your VegBIEN DB!
22
        To save it: make schemas/rotate
23
        Important: This must be done *after* running make_analytical_db on a
24
            previous import
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
    ./bin/make_analytical_db &
34
    Wait for make_analytical_db to finish
35
    Archive the last import: make schemas/rotate
36
        Important: This must be done *after* running make_analytical_db
37
    Rename the rotated schema using the date in the first datasource's log
38
        file name
39
    tail inputs/{.,}*/*/logs/*.r<revision>[.-]*log.sql
40
    Check that every input's log ends in "Encountered 0 error(s)"
41
    If many do not, fix the bug and discard the current (partial) import:
42
        make schemas/public/reinstall
43
    Otherwise, continue
44
    Delete previous imports so they won't bloat the full DB backup:
45
        make backups/public.<version>.backup/remove
46
    make backups/TNRS.backup-remake &
47
    make backups/public.<version>.backup/test &
48
    make backups/vegbien.<version>.backup/test &
49
    On local machine:
50
        make inputs/download-logs
51
        make backups/download
52
    If desired, record the import times in inputs/import.stats.xls:
53
        Open inputs/import.stats.xls
54
        Insert a copy of the leftmost Column-based column group before it
55
        Update the import date in the upper-right corner
56
        ./bin/import_times inputs/{.,}*/*/logs/*.r<revision>[.-]*log.sql
57
        Paste the output over the # Rows/Time columns, making sure that the
58
            row counts match up with the previous import's row counts
59
        If the row counts do not match up, insert or reorder rows as needed
60
            until they do
61
        Commit: svn ci -m "inputs/import.stats.xls: Updated import times"
62

    
63
Backups:
64
    Archived imports:
65
        Back up: make backups/public.<date>.backup &
66
            Note: To back up the last import, you must archive it first (above)
67
        Test: make backups/public.<date>.backup/test &
68
        Restore: make backups/public.<date>.backup/restore &
69
        Remove: make backups/public.<date>.backup/remove
70
        Download: make backups/download
71
    TNRS cache:
72
        Back up: make backups/TNRS.backup-remake &
73
        Restore:
74
            yes|make inputs/.TNRS/uninstall
75
            make backups/TNRS.backup/restore &
76
            yes|make schemas/public/reinstall
77
                Must come after TNRS restore to recreate tnrs_input_name view
78
    Full DB:
79
        Back up, test, and rotate: make backups/vegbien.backup/all &
80
        Back up and rotate: make backups/vegbien.backup/rotate &
81
        Test: make backups/vegbien.<date>.backup/test &
82
        Restore: make backups/vegbien.<date>.backup/restore &
83
        Download: make backups/download
84
    Import logs:
85
        Download: make inputs/download-logs
86

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

    
168
Schema changes:
169
    Remember to update the following files with any renamings:
170
        schemas/filter_ERD.csv
171
        mappings/VegCore-VegBIEN.csv
172
    Regenerate schema from installed DB: make schemas/remake
173
    Reinstall DB from schema: make schemas/reinstall
174
        WARNING: This will delete the current public schema of your VegBIEN DB!
175
    Reinstall staging tables: . bin/reinstall_all
176
    Sync ERD with vegbien.sql schema:
177
        Run make schemas/vegbien.my.sql
178
        Open schemas/vegbien.ERD.mwb in MySQLWorkbench
179
        Go to File > Export > Synchronize With SQL CREATE Script...
180
        For Input File, select schemas/vegbien.my.sql
181
        Click Continue
182
        Click in the changes list and press Ctrl+A or Apple+A to select all
183
        Click Update Model
184
        Click Continue
185
        Note: The generated SQL script will be empty because we are syncing in
186
            the opposite direction
187
        Click Execute
188
        Reposition any lines that have been reset
189
        Add any new tables by dragging them from the Catalog in the left sidebar
190
            to the diagram
191
        Remove any deleted tables by right-clicking the table's diagram element,
192
            selecting Delete '<table name>', and clicking Delete
193
        Save
194
        If desired, update the graphical ERD exports (see below)
195
    Update graphical ERD exports:
196
        Go to File > Export > Export as PNG...
197
        Select schemas/vegbien.ERD.png and click Save
198
        Go to File > Export > Export as SVG...
199
        Select schemas/vegbien.ERD.svg and click Save
200
        Go to File > Export > Export as Single Page PDF...
201
        Select schemas/vegbien.ERD.1_pg.pdf and click Save
202
        Go to File > Print...
203
        In the lower left corner, click PDF > Save as PDF...
204
        Set the Title and Author to ""
205
        Select schemas/vegbien.ERD.pdf and click Save
206
    Refactoring tips:
207
        To rename a table:
208
            In vegbien.sql, do the following:
209
                Replace regexp (?<=_|\b)<old>(?=_|\b) with <new>
210
                    This is necessary because the table name is *everywhere*
211
                Search for <new>
212
                Manually change back any replacements inside comments
213
        To rename a column:
214
            Rename the column: ALTER TABLE <table> RENAME <old> TO <new>;
215
            Recreate any foreign key for the column, removing CONSTRAINT <name>
216
                This resets the foreign key name using the new column name
217

    
218
Testing:
219
    Mapping process: make test
220
        Including column-based import: make test by_col=1
221
            If the row-based and column-based imports produce different inserted
222
            row counts, this usually means that a table is underconstrained
223
            (the unique indexes don't cover all possible rows).
224
            This can occur if you didn't use COALESCE(field, null_value) around
225
            a nullable field in a unique index. See sql_gen.null_sentinels for
226
            the appropriate null value to use.
227
    Map spreadsheet generation: make remake
228
    Missing mappings: make missing_mappings
229
    Everything (for most complete coverage): make test-all
230

    
231
WinMerge setup:
232
    Install WinMerge from <http://winmerge.org/>
233
    Open WinMerge
234
    Go to Edit > Options and click Compare in the left sidebar
235
    Enable "Moved block detection", as described at
236
        <http://manual.winmerge.org/Configuration.html#d0e5892>.
237
    Set Whitespace to Ignore change, as described at
238
        <http://manual.winmerge.org/Configuration.html#d0e5758>.
239

    
240
Documentation:
241
    To generate a Redmine-formatted list of steps for column-based import:
242
        make inputs/QMOR/Specimen/logs/steps.by_col.log.sql
243
    To import and scrub just the test taxonomic names:
244
        inputs/test_taxonomic_names/test_scrub
245

    
246
General:
247
    To see a program's description, read its top-of-file comment
248
    To see a program's usage, run it without arguments
249
    To remake a directory: make <dir>/remake
250
    To remake a file: make <file>-remake
(2-2/5)