Project

General

Profile

1 702 aaronmk
Installation:
2
    Install: make install
3 3370 aaronmk
        WARNING: This will delete the current public schema of your VegBIEN DB!
4 702 aaronmk
    Uninstall: make uninstall
5 3370 aaronmk
        WARNING: This will delete your entire VegBIEN DB!
6 3374 aaronmk
        This includes all archived imports and staging tables.
7 554 aaronmk
8 3674 aaronmk
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 702 aaronmk
Data import:
15 4487 aaronmk
    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 4482 aaronmk
    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 4481 aaronmk
    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 2976 aaronmk
    Archive the last import: make schemas/rotate
33 4481 aaronmk
    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 3381 aaronmk
48 3545 aaronmk
Backups:
49 3408 aaronmk
    Archived imports:
50
        Back up: make backups/public.<date>.backup &
51 3546 aaronmk
            Note: To back up the last import, you must archive it first (above)
52 3410 aaronmk
        Test: make backups/public.<date>.backup/test &
53 3408 aaronmk
        Restore: make backups/public.<date>.backup/restore &
54
        Remove: make backups/public.<date>.backup/remove
55 3701 aaronmk
        Download: make backups/download
56 3408 aaronmk
    Full DB:
57 3546 aaronmk
        Back up, test, and rotate: make backups/vegbien.backup/all &
58 3439 aaronmk
        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 3701 aaronmk
        Download: make backups/download
62 3698 aaronmk
    Import logs:
63
        Download: make inputs/download-logs
64 554 aaronmk
65 1773 aaronmk
Datasource setup:
66 4219 aaronmk
    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 4360 aaronmk
    Install any MySQL export:
71
        Create database in phpMyAdmin
72
        mysql -p database <export.sql
73 4218 aaronmk
    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 4264 aaronmk
        Note that if this table will be joined together with another table, its
77
            name must end in ".src"
78 4219 aaronmk
        make inputs/<datasrc>/<table>/add
79 4342 aaronmk
            Important: DO NOT just create an empty directory named <table>!
80
                This command also creates necessary subdirs, such as logs/.
81 4219 aaronmk
        Place the CSV for the table in inputs/<datasrc>/<table>/
82 4264 aaronmk
            OR place a query joining other tables together in
83
            inputs/<datasrc>/<table>/create.sql and svn add this file
84 4212 aaronmk
        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 3612 aaronmk
        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 4255 aaronmk
        Add <table> to inputs/<datasrc>/import_order.txt before other tables
93 4220 aaronmk
            that depend on it
94 3593 aaronmk
    Auto-create the map spreadsheets:
95 4219 aaronmk
        make inputs/<datasrc>/; make inputs/<datasrc>/
96 3593 aaronmk
            Note: Must be run twice to properly bootstrap all maps.
97 4594 aaronmk
        svn add inputs/<datasrc>/*/{,.}{header,src,map*,*terms,VegBIEN}.csv{,.*}
98 3611 aaronmk
    Install the staging tables:
99 4219 aaronmk
        make inputs/<datasrc>/reinstall quiet=1 &
100
        To view progress: tail -f inputs/<datasrc>/<table>/logs/install.log.sql
101
        View the logs: tail -n +1 inputs/<datasrc>/*/logs/install.log.sql
102 3611 aaronmk
            tail provides a header line with the filename
103
            +1 starts at the first line, to show the whole file
104
        For every file with an error 'column "..." specified more than once':
105 4182 aaronmk
            Add a header override file "+header.<ext>" in <table>/:
106 3611 aaronmk
                Note: The leading "+" should sort it before the flat files.
107
                    "_" unfortunately sorts *after* capital letters in ASCII.
108
                Create a text file containing the header line of the flat files
109
                Add an ! at the beginning of the line
110
                    This signals cat_csv that this is a header override.
111
                For empty names, use their 0-based column # (by convention)
112
                For duplicate names, add a distinguishing suffix
113
                For long names that collided, rename them to <= 63 chars long
114
                Do NOT make readability changes in this step; that is what the
115
                    map spreadsheets (below) are for.
116
                Save
117 4219 aaronmk
                svn add inputs/<datasrc>/<table>/<header_override>
118 3611 aaronmk
        If you made any changes, re-run the install command above
119 3576 aaronmk
    Map each table's columns:
120 4125 aaronmk
        In each <table>/ subdir, for each "via map" map.csv:
121 3576 aaronmk
            Open the map in a spreadsheet editor
122 4125 aaronmk
            Open the "core map" /mappings/Veg+-VegBIEN.csv
123 3576 aaronmk
            In each row of the via map, set the right column to a value from the
124
                left column of the core map
125
            Save
126 4219 aaronmk
        Regenerate the derived maps: make inputs/<datasrc>/
127 3593 aaronmk
    Accept the test cases:
128 4219 aaronmk
        make inputs/<datasrc>/test
129 3593 aaronmk
            When prompted to "Accept new test output", enter y and press ENTER
130 3690 aaronmk
            If you instead get errors, do one of the following for each one:
131
            -   If the error was due to a bug, fix it
132
            -   Add a SQL function that filters or transforms the invalid data
133
            -   Make an empty mapping for the columns that produced the error.
134
                Put something in the Comments column of the map spreadsheet to
135
                prevent the automatic mapper from auto-removing the mapping.
136 3783 aaronmk
            When accepting tests, it's helpful to use WinMerge
137
                (see WinMerge setup below for configuration)
138 4476 aaronmk
        make inputs/<datasrc>/test by_col=1
139
            If you get errors this time, this always indicates a bug, usually in
140
                either the unique constraints or column-based import itself
141 4219 aaronmk
        svn add inputs/<datasrc>/*/test.xml.ref
142
    Commit: svn ci -m "Added inputs/<datasrc>/" inputs/<datasrc>/
143 3585 aaronmk
    Update vegbiendev:
144
        On vegbiendev: svn up
145
        On local machine: make inputs/upload
146 4291 aaronmk
        On vegbiendev:
147
            Follow the steps under Install the staging tables above
148
            make inputs/<datasrc>/test
149 1773 aaronmk
150 702 aaronmk
Schema changes:
151
    Regenerate schema from installed DB: make schemas/remake
152 1967 aaronmk
    Reinstall DB from schema: make schemas/reinstall
153 3370 aaronmk
        WARNING: This will delete the current public schema of your VegBIEN DB!
154 3589 aaronmk
    Reinstall staging tables: . bin/reinstall_all
155 702 aaronmk
    Sync ERD with vegbien.sql schema:
156
        Run make schemas/vegbien.my.sql
157
        Open schemas/vegbien.ERD.mwb in MySQLWorkbench
158
        Go to File > Export > Synchronize With SQL CREATE Script...
159
        For Input File, select schemas/vegbien.my.sql
160
        Click Continue
161
        Click in the changes list and press Ctrl+A or Apple+A to select all
162
        Click Update Model
163
        Click Continue
164
        Note: The generated SQL script will be empty because we are syncing in
165
            the opposite direction
166
        Click Execute
167
        Reposition any lines that have been reset
168
        Add any new tables by dragging them from the Catalog in the left sidebar
169
            to the diagram
170
        Remove any deleted tables by right-clicking the table's diagram element,
171
            selecting Delete '<table name>', and clicking Delete
172
        Save
173 1774 aaronmk
        If desired, update the graphical ERD exports (see below)
174
    Update graphical ERD exports:
175 702 aaronmk
        Go to File > Export > Export as PNG...
176 1774 aaronmk
        Select schemas/vegbien.ERD.png and click Save
177 702 aaronmk
        Go to File > Export > Export as SVG...
178 1774 aaronmk
        Select schemas/vegbien.ERD.svg and click Save
179 702 aaronmk
        Go to File > Export > Export as Single Page PDF...
180 4087 aaronmk
        Select schemas/vegbien.ERD.1_pg.pdf and click Save
181 1774 aaronmk
        Go to File > Print...
182
        In the lower left corner, click PDF > Save as PDF...
183
        Set the Title and Author to ""
184 4087 aaronmk
        Select schemas/vegbien.ERD.pdf and click Save
185 203 aaronmk
186 1459 aaronmk
Testing:
187
    Mapping process: make test
188 4292 aaronmk
        Including column-based import: make test by_col=1
189 1459 aaronmk
    Map spreadsheet generation: make remake
190 1744 aaronmk
    Missing mappings: make missing_mappings
191 1459 aaronmk
    Everything (for most complete coverage): make test-all
192 702 aaronmk
193 3783 aaronmk
WinMerge setup:
194
    Install WinMerge from <http://winmerge.org/>
195 3785 aaronmk
    Open WinMerge
196
    Go to Edit > Options and click Compare in the left sidebar
197 3783 aaronmk
    Enable "Moved block detection", as described at
198
        <http://manual.winmerge.org/Configuration.html#d0e5892>.
199 3784 aaronmk
    Set Whitespace to Ignore change, as described at
200
        <http://manual.winmerge.org/Configuration.html#d0e5758>.
201 3783 aaronmk
202 3133 aaronmk
Documentation:
203
    To generate a Redmine-formatted list of steps for column-based import:
204 4486 aaronmk
        make inputs/QMOR/Specimen/logs/steps.by_col.log.sql
205 3133 aaronmk
206 702 aaronmk
General:
207
    To see a program's description, read its top-of-file comment
208
    To see a program's usage, run it without arguments
209 3389 aaronmk
    To remake a directory: make <dir>/remake
210
    To remake a file: make <file>-remake