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
        Important: This must be done *after* running make_analytical_db on a
26
            previous import
27
    Start column-based import: . bin/import_all by_col=1
28
        To use row-based import: . bin/import_all
29
        To stop all running imports: . bin/stop_imports
30
        WARNING: Do NOT run import_all in the background, or the jobs it creates
31
            won't be owned by your shell.
32
        Note that import_all will several hours to import the NCBI backbone and
33
            TNRS names before returning control to the shell.
34
    Wait (overnight) for the import to finish
35
    tail inputs/{.,}*/*/logs/*.r<revision>[.-]*log.sql
36
    Check that every input's log ends in "Encountered 0 error(s)"
37
    If many do not, fix the bug and discard the current (partial) import:
38
        make schemas/public/reinstall
39
    Otherwise, continue
40
    Determine the import name:
41
        bin/import_name inputs/{.,}*/*/logs/*.r<revision>[.-]*log.sql
42
    Archive the last import: make schemas/rename/public.<import_name>
43
        Important: This must be done *after* running make_analytical_db
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
    To remake analytical DB: bin/make_analytical_db &
63
        To view progress:
64
            tail -f inputs/analytical_db/logs/make_analytical_db.log.sql
65

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

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

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

    
246
Testing:
247
    Mapping process: make test
248
        Including column-based import: make test by_col=1
249
            If the row-based and column-based imports produce different inserted
250
            row counts, this usually means that a table is underconstrained
251
            (the unique indexes don't cover all possible rows).
252
            This can occur if you didn't use COALESCE(field, null_value) around
253
            a nullable field in a unique index. See sql_gen.null_sentinels for
254
            the appropriate null value to use.
255
    Map spreadsheet generation: make remake
256
    Missing mappings: make missing_mappings
257
    Everything (for most complete coverage): make test-all
258

    
259
WinMerge setup:
260
    Install WinMerge from <http://winmerge.org/>
261
    Open WinMerge
262
    Go to Edit > Options and click Compare in the left sidebar
263
    Enable "Moved block detection", as described at
264
        <http://manual.winmerge.org/Configuration.html#d0e5892>.
265
    Set Whitespace to Ignore change, as described at
266
        <http://manual.winmerge.org/Configuration.html#d0e5758>.
267

    
268
Documentation:
269
    To generate a Redmine-formatted list of steps for column-based import:
270
        make inputs/ACAD/Specimen/logs/steps.by_col.log.sql
271
    To import and scrub just the test taxonomic names:
272
        inputs/test_taxonomic_names/test_scrub
273

    
274
General:
275
    To see a program's description, read its top-of-file comment
276
    To see a program's usage, run it without arguments
277
    To remake a directory: make <dir>/remake
278
    To remake a file: make <file>-remake
(2-2/5)