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
    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
    Wait (overnight) for the import to finish
29
    ./bin/make_analytical_db &
30
    Wait for make_analytical_db to finish
31
    Archive the last import: make schemas/rotate
32
        Important: This must be done *after* running make_analytical_db
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/public.<datetime>.backup/test &
43
    make backups/vegbien.<datetime>.backup/test &
44
    On local machine:
45
        make inputs/download-logs
46
        make backups/download
47
    If desired, record the import times in inputs/import.stats.xls:
48
        Open inputs/import.stats.xls
49
        Insert a copy of the leftmost Column-based column group before it
50
        Update the import date in the upper-right corner
51
        ./bin/import_times inputs/*/*/logs/<date>-*.log.sql
52
        Paste the output over the # Rows/Time columns, making sure that the
53
            row counts match up with the previous import's row counts
54
        If the row counts do not match up, insert or reorder rows as needed
55
            until they do
56
        Commit: svn ci -m "inputs/import.stats.xls: Updated import times"
57

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

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

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

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

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

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

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