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
    Start column-based import: . bin/with_all 'import by_col=1'
27
        To use row-based import: . bin/import_all
28
        To stop all running imports: . bin/stop_imports
29
    Wait (overnight) for the import to finish
30
    ./bin/make_analytical_db &
31
    Wait for make_analytical_db to finish
32
    Archive the last import: make schemas/rotate
33
    Rename the rotated schema using the date in the first datasource's log
34
        file name
35
    tail inputs/*/*/logs/<date>-*.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
    Delete previous imports so they won't bloat the full DB backup:
41
        make backups/public.<datetime>.backup/remove
42
    make backups/<schema>.backup/test & make backups/vegbien.backup/all &
43
    On local machine:
44
        make inputs/download-logs
45
        make backups/download
46
        If desired, record the import times in inputs/import.stats.xls
47

    
48
Backups:
49
    Archived imports:
50
        Back up: make backups/public.<date>.backup &
51
            Note: To back up the last import, you must archive it first (above)
52
        Test: make backups/public.<date>.backup/test &
53
        Restore: make backups/public.<date>.backup/restore &
54
        Remove: make backups/public.<date>.backup/remove
55
        Download: make backups/download
56
    Full DB:
57
        Back up, test, and rotate: make backups/vegbien.backup/all &
58
        Back up and rotate: make backups/vegbien.backup/rotate &
59
        Test: make backups/vegbien.<date>.backup/test &
60
        Restore: make backups/vegbien.<date>.backup/restore &
61
        Download: make backups/download
62
    Import logs:
63
        Download: make inputs/download-logs
64

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

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

    
185
Testing:
186
    Mapping process: make test
187
        Including column-based import: make test by_col=1
188
    Map spreadsheet generation: make remake
189
    Missing mappings: make missing_mappings
190
    Everything (for most complete coverage): make test-all
191

    
192
WinMerge setup:
193
    Install WinMerge from <http://winmerge.org/>
194
    Open WinMerge
195
    Go to Edit > Options and click Compare in the left sidebar
196
    Enable "Moved block detection", as described at
197
        <http://manual.winmerge.org/Configuration.html#d0e5892>.
198
    Set Whitespace to Ignore change, as described at
199
        <http://manual.winmerge.org/Configuration.html#d0e5758>.
200

    
201
Documentation:
202
    To generate a Redmine-formatted list of steps for column-based import:
203
        make inputs/QMOR/Specimen/logs/steps.by_col.log.sql
204

    
205
General:
206
    To see a program's description, read its top-of-file comment
207
    To see a program's usage, run it without arguments
208
    To remake a directory: make <dir>/remake
209
    To remake a file: make <file>-remake
(2-2/5)