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
            If you get errors (including skipped errors), this always indicates
18
            a bug, usually in either the unique constraints or column-based
19
            import itself
20
            Note that inserted row count diffs of a few rows are benign
21
    On vegbiendev:
22
    svn up
23
    Update the schemas: make schemas/reinstall
24
        WARNING: This will delete the current public schema of your VegBIEN DB!
25
        To save it: make schemas/rotate
26
        Important: This must be done *after* running make_analytical_db on a
27
            previous import
28
    Start column-based import: . bin/with_all 'import 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 backups/public.<datetime>.backup/test &
46
    make backups/vegbien.<datetime>.backup/test &
47
    On local machine:
48
        make inputs/download-logs
49
        make backups/download
50
    If desired, record the import times in inputs/import.stats.xls:
51
        Open inputs/import.stats.xls
52
        Insert a copy of the leftmost Column-based column group before it
53
        Update the import date in the upper-right corner
54
        ./bin/import_times inputs/*/*/logs/<date>-*.log.sql
55
        Paste the output over the # Rows/Time columns, making sure that the
56
            row counts match up with the previous import's row counts
57
        If the row counts do not match up, insert or reorder rows as needed
58
            until they do
59
        Commit: svn ci -m "inputs/import.stats.xls: Updated import times"
60

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

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

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

    
198
Testing:
199
    Mapping process: make test
200
        Including column-based import: make test by_col=1
201
    Map spreadsheet generation: make remake
202
    Missing mappings: make missing_mappings
203
    Everything (for most complete coverage): make test-all
204

    
205
WinMerge setup:
206
    Install WinMerge from <http://winmerge.org/>
207
    Open WinMerge
208
    Go to Edit > Options and click Compare in the left sidebar
209
    Enable "Moved block detection", as described at
210
        <http://manual.winmerge.org/Configuration.html#d0e5892>.
211
    Set Whitespace to Ignore change, as described at
212
        <http://manual.winmerge.org/Configuration.html#d0e5758>.
213

    
214
Documentation:
215
    To generate a Redmine-formatted list of steps for column-based import:
216
        make inputs/QMOR/Specimen/logs/steps.by_col.log.sql
217

    
218
General:
219
    To see a program's description, read its top-of-file comment
220
    To see a program's usage, run it without arguments
221
    To remake a directory: make <dir>/remake
222
    To remake a file: make <file>-remake
(2-2/5)