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
    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_r<revision> table using its schema
58
                in schemas/vegbien.my.sql
59
        env table=analytical_aggregate_r<revision> bin/publish_analytical_db \
60
            backups/analytical_aggregate.public.<version>.csv
61
    If desired, record the import times in inputs/import.stats.xls:
62
        Open inputs/import.stats.xls
63
        Insert a copy of the leftmost Column-based column group before it
64
        Update the import date in the upper-right corner
65
        ./bin/import_times inputs/{.,}*/*/logs/*.r<revision>[.-]*log.sql
66
        Paste the output over the # Rows/Time columns, making sure that the
67
            row counts match up with the previous import's row counts
68
        If the row counts do not match up, insert or reorder rows as needed
69
            until they do
70
        Commit: svn ci -m "inputs/import.stats.xls: Updated import times"
71
    To remake analytical DB: env public=... bin/make_analytical_db &
72
        public should be set to the current import's schema name
73
        To view progress:
74
            tail -f inputs/analytical_db/logs/make_analytical_db.log.sql
75

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

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

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

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

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

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

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

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