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
    Import the TNRS names:
26
        make inputs/.TNRS/cleanup; make inputs/.TNRS/import by_col=1 &
27
    Wait for the TNRS import to finish: wait
28
    Start column-based import: . bin/import_all by_col=1
29
        To use row-based import: . bin/import_all
30
        To stop all running imports: . bin/stop_imports
31
    Wait (overnight) for the import to finish
32
    ./bin/make_analytical_db &
33
    Wait for make_analytical_db to finish
34
    Archive the last import: make schemas/rotate
35
        Important: This must be done *after* running make_analytical_db
36
    Rename the rotated schema using the date in the first datasource's log
37
        file name
38
    tail inputs/*/*/logs/<date>-*.log.sql
39
    Check that every input's log ends in "Encountered 0 error(s)"
40
    If many do not, fix the bug and discard the current (partial) import:
41
        make schemas/public/reinstall
42
    Otherwise, continue
43
    Delete previous imports so they won't bloat the full DB backup:
44
        make backups/public.<datetime>.backup/remove
45
    make inputs/.TNRS/cleanup; make backups/TNRS.backup-remake &
46
    make backups/public.<datetime>.backup/test &
47
    make backups/vegbien.<datetime>.backup/test &
48
    On local machine:
49
        make inputs/download-logs
50
        make backups/download
51
    If desired, record the import times in inputs/import.stats.xls:
52
        Open inputs/import.stats.xls
53
        Insert a copy of the leftmost Column-based column group before it
54
        Update the import date in the upper-right corner
55
        ./bin/import_times inputs/*/*/logs/<date>-*.log.sql
56
        Paste the output over the # Rows/Time columns, making sure that the
57
            row counts match up with the previous import's row counts
58
        If the row counts do not match up, insert or reorder rows as needed
59
            until they do
60
        Commit: svn ci -m "inputs/import.stats.xls: Updated import times"
61

    
62
Backups:
63
    Archived imports:
64
        Back up: make backups/public.<date>.backup &
65
            Note: To back up the last import, you must archive it first (above)
66
        Test: make backups/public.<date>.backup/test &
67
        Restore: make backups/public.<date>.backup/restore &
68
        Remove: make backups/public.<date>.backup/remove
69
        Download: make backups/download
70
    Full DB:
71
        Back up, test, and rotate: make backups/vegbien.backup/all &
72
        Back up and rotate: make backups/vegbien.backup/rotate &
73
        Test: make backups/vegbien.<date>.backup/test &
74
        Restore: make backups/vegbien.<date>.backup/restore &
75
        Download: make backups/download
76
    Import logs:
77
        Download: make inputs/download-logs
78

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

    
163
Schema changes:
164
    Regenerate schema from installed DB: make schemas/remake
165
    Reinstall DB from schema: make schemas/reinstall
166
        WARNING: This will delete the current public schema of your VegBIEN DB!
167
    Reinstall staging tables: . bin/reinstall_all
168
    Sync ERD with vegbien.sql schema:
169
        Run make schemas/vegbien.my.sql
170
        Open schemas/vegbien.ERD.mwb in MySQLWorkbench
171
        Go to File > Export > Synchronize With SQL CREATE Script...
172
        For Input File, select schemas/vegbien.my.sql
173
        Click Continue
174
        Click in the changes list and press Ctrl+A or Apple+A to select all
175
        Click Update Model
176
        Click Continue
177
        Note: The generated SQL script will be empty because we are syncing in
178
            the opposite direction
179
        Click Execute
180
        Reposition any lines that have been reset
181
        Add any new tables by dragging them from the Catalog in the left sidebar
182
            to the diagram
183
        Remove any deleted tables by right-clicking the table's diagram element,
184
            selecting Delete '<table name>', and clicking Delete
185
        Save
186
        If desired, update the graphical ERD exports (see below)
187
    Update graphical ERD exports:
188
        Go to File > Export > Export as PNG...
189
        Select schemas/vegbien.ERD.png and click Save
190
        Go to File > Export > Export as SVG...
191
        Select schemas/vegbien.ERD.svg and click Save
192
        Go to File > Export > Export as Single Page PDF...
193
        Select schemas/vegbien.ERD.1_pg.pdf and click Save
194
        Go to File > Print...
195
        In the lower left corner, click PDF > Save as PDF...
196
        Set the Title and Author to ""
197
        Select schemas/vegbien.ERD.pdf and click Save
198

    
199
Testing:
200
    Mapping process: make test
201
        Including column-based import: make test by_col=1
202
            If the row-based and column-based imports produce different inserted
203
            row counts, this usually means that a table is underconstrained
204
            (the unique indexes don't cover all possible rows).
205
            This can occur if you didn't use COALESCE(field, null_value) around
206
            a nullable field in a unique index. See sql_gen.null_sentinels for
207
            the appropriate null value to use.
208
    Map spreadsheet generation: make remake
209
    Missing mappings: make missing_mappings
210
    Everything (for most complete coverage): make test-all
211

    
212
WinMerge setup:
213
    Install WinMerge from <http://winmerge.org/>
214
    Open WinMerge
215
    Go to Edit > Options and click Compare in the left sidebar
216
    Enable "Moved block detection", as described at
217
        <http://manual.winmerge.org/Configuration.html#d0e5892>.
218
    Set Whitespace to Ignore change, as described at
219
        <http://manual.winmerge.org/Configuration.html#d0e5758>.
220

    
221
Documentation:
222
    To generate a Redmine-formatted list of steps for column-based import:
223
        make inputs/QMOR/Specimen/logs/steps.by_col.log.sql
224
    To import and scrub just the test taxonomic names:
225
        make inputs/.TNRS/reinstall
226
            WARNING: This will delete the TNRS cache! 
227
            To save it: make backups/TNRS.backup-remake
228
        make schemas/public/reinstall
229
            WARNING: This will delete the public schema of your VegBIEN DB! 
230
            To save it: make schemas/rotate
231
        make inputs/test_taxonomic_names/import log=
232
        Do the following twice: (The 2nd time parses the accepted names.)
233
            make inputs/.TNRS/tnrs/tnrs-remake log=
234
            yes|make schemas/public/reinstall
235
            make inputs/.TNRS/cleanup
236
            make inputs/.TNRS/import log=
237
            make inputs/test_taxonomic_names/import log=
238

    
239
General:
240
    To see a program's description, read its top-of-file comment
241
    To see a program's usage, run it without arguments
242
    To remake a directory: make <dir>/remake
243
    To remake a file: make <file>-remake
(2-2/5)