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 1550 aaronmk
    Import data into VegBIEN: . bin/import_all
16 3205 aaronmk
        Using column-based import: . bin/with_all 'import by_col=1'
17 1556 aaronmk
    Stop all running imports: . bin/stop_imports
18 2976 aaronmk
    Archive the last import: make schemas/rotate
19 3381 aaronmk
    Remove the last import: make schemas/public/reinstall
20
        WARNING: This will delete the current public schema of your VegBIEN DB!
21 2976 aaronmk
    Re-import data: make schemas/rotate; . bin/import_all
22
        Note: This will archive the last import.
23 3381 aaronmk
24 3545 aaronmk
Backups:
25 3547 aaronmk
    After a new import:
26 3698 aaronmk
        On vegbiendev:
27 3547 aaronmk
        make schemas/rotate
28
        Rename the rotated schema using the date in the first datasource's log
29
            file name
30 3845 aaronmk
        tail inputs/*/import/*.<date>-*.log.sql
31
        Check that every input's log ends in "Encountered 0 error(s)"
32
        If many do not, fix the bug and discard the current (partial) import
33
        Otherwise, continue
34 3548 aaronmk
        Delete previous imports so they won't bloat the full DB backup:
35 3845 aaronmk
            make backups/public.<datetime>.backup/remove
36 3547 aaronmk
        make backups/<schema>.backup/test & make backups/vegbien.backup/all &
37 3698 aaronmk
        On local machine:
38
            make inputs/download-logs
39 3701 aaronmk
            make backups/download &
40 3845 aaronmk
            If desired, record the import times in inputs/import.stats.xls
41 3408 aaronmk
    Archived imports:
42
        Back up: make backups/public.<date>.backup &
43 3546 aaronmk
            Note: To back up the last import, you must archive it first (above)
44 3410 aaronmk
        Test: make backups/public.<date>.backup/test &
45 3408 aaronmk
        Restore: make backups/public.<date>.backup/restore &
46
        Remove: make backups/public.<date>.backup/remove
47 3701 aaronmk
        Download: make backups/download
48 3408 aaronmk
    Full DB:
49 3546 aaronmk
        Back up, test, and rotate: make backups/vegbien.backup/all &
50 3439 aaronmk
        Back up and rotate: make backups/vegbien.backup/rotate &
51
        Test: make backups/vegbien.<date>.backup/test &
52
        Restore: make backups/vegbien.<date>.backup/restore &
53 3701 aaronmk
        Download: make backups/download
54 3698 aaronmk
    Import logs:
55
        Download: make inputs/download-logs
56 554 aaronmk
57 1773 aaronmk
Datasource setup:
58 3586 aaronmk
    Add a new datasource: make inputs/<name>/add
59
        <name> may not contain spaces, and should be abbreviated.
60
        If the datasource is a herbarium, <name> should be the herbarium code as
61
        defined by the Index Herbariorum <http://sweetgum.nybg.org/ih/>
62 3557 aaronmk
    Populate the src/ subdir with input data:
63 3586 aaronmk
        Obtain/create CSVs for the table(s) present in the datasource:
64 4125 aaronmk
            specimens, 0.plots, 1.organisms, 2.stems
65 3612 aaronmk
        If there are multiple part files for a table, and the header is repeated
66
            in each part, make sure each header is EXACTLY the same.
67
             (If the headers are not the same, the CSV concatenation script
68
             assumes the part files don't have individual headers and treats the
69
             subsequent headers as data rows.)
70 4125 aaronmk
        Place each CSV in a subdir named according to one of the tables above
71 3593 aaronmk
    Auto-create the map spreadsheets:
72
        make inputs/<name>/; make inputs/<name>/
73
            Note: Must be run twice to properly bootstrap all maps.
74 4125 aaronmk
        svn add inputs/<name>/src/*/{{src,map*,VegBIEN}.csv,.*.last_cleanup}
75 3611 aaronmk
    Install the staging tables:
76
        make inputs/<name>/reinstall quiet=1 &
77 4125 aaronmk
        To view progress: tail -f inputs/<name>/src/<table>/logs/install.log.sql
78
        View the logs: tail -n +1 inputs/<name>/src/*/logs/install.log.sql
79 3611 aaronmk
            tail provides a header line with the filename
80
            +1 starts at the first line, to show the whole file
81
        For every file with an error 'column "..." specified more than once':
82 4125 aaronmk
            Add a header override file "+header.<ext>" in src/<table>/:
83 3611 aaronmk
                Note: The leading "+" should sort it before the flat files.
84
                    "_" unfortunately sorts *after* capital letters in ASCII.
85
                Create a text file containing the header line of the flat files
86
                Add an ! at the beginning of the line
87
                    This signals cat_csv that this is a header override.
88
                For empty names, use their 0-based column # (by convention)
89
                For duplicate names, add a distinguishing suffix
90
                For long names that collided, rename them to <= 63 chars long
91
                Do NOT make readability changes in this step; that is what the
92
                    map spreadsheets (below) are for.
93
                Save
94 4125 aaronmk
                svn add inputs/<name>/src/<table>/<header_override>
95 3611 aaronmk
        If you made any changes, re-run the install command above
96 3576 aaronmk
    Map each table's columns:
97 4125 aaronmk
        In each <table>/ subdir, for each "via map" map.csv:
98 3576 aaronmk
            Open the map in a spreadsheet editor
99 4125 aaronmk
            Open the "core map" /mappings/Veg+-VegBIEN.csv
100 3576 aaronmk
            In each row of the via map, set the right column to a value from the
101
                left column of the core map
102
            Save
103
        Regenerate the derived maps: make inputs/<name>/
104 3593 aaronmk
    Accept the test cases:
105 4125 aaronmk
        make inputs/<name>/test
106 3593 aaronmk
            When prompted to "Accept new test output", enter y and press ENTER
107 3690 aaronmk
            If you instead get errors, do one of the following for each one:
108
            -   If the error was due to a bug, fix it
109
            -   Add a SQL function that filters or transforms the invalid data
110
            -   Make an empty mapping for the columns that produced the error.
111
                Put something in the Comments column of the map spreadsheet to
112
                prevent the automatic mapper from auto-removing the mapping.
113 3783 aaronmk
            When accepting tests, it's helpful to use WinMerge
114
                (see WinMerge setup below for configuration)
115 4125 aaronmk
        svn add inputs/<name>/src/*/test.xml.ref
116 3583 aaronmk
    Commit: svn ci -m "Added inputs/<name>/" inputs/<name>/
117 3585 aaronmk
    Update vegbiendev:
118
        On vegbiendev: svn up
119
        On local machine: make inputs/upload
120 3588 aaronmk
        On vegbiendev: Follow the steps under Install the staging tables above
121 1773 aaronmk
122 702 aaronmk
Schema changes:
123
    Regenerate schema from installed DB: make schemas/remake
124 1967 aaronmk
    Reinstall DB from schema: make schemas/reinstall
125 3370 aaronmk
        WARNING: This will delete the current public schema of your VegBIEN DB!
126 3441 aaronmk
    Reinstall errors tables: make inputs/install errors_table_only=1
127 3589 aaronmk
    Reinstall staging tables: . bin/reinstall_all
128 702 aaronmk
    Sync ERD with vegbien.sql schema:
129
        Run make schemas/vegbien.my.sql
130
        Open schemas/vegbien.ERD.mwb in MySQLWorkbench
131
        Go to File > Export > Synchronize With SQL CREATE Script...
132
        For Input File, select schemas/vegbien.my.sql
133
        Click Continue
134
        Click in the changes list and press Ctrl+A or Apple+A to select all
135
        Click Update Model
136
        Click Continue
137
        Note: The generated SQL script will be empty because we are syncing in
138
            the opposite direction
139
        Click Execute
140
        Reposition any lines that have been reset
141
        Add any new tables by dragging them from the Catalog in the left sidebar
142
            to the diagram
143
        Remove any deleted tables by right-clicking the table's diagram element,
144
            selecting Delete '<table name>', and clicking Delete
145
        Save
146 1774 aaronmk
        If desired, update the graphical ERD exports (see below)
147
    Update graphical ERD exports:
148 702 aaronmk
        Go to File > Export > Export as PNG...
149 1774 aaronmk
        Select schemas/vegbien.ERD.png and click Save
150 702 aaronmk
        Go to File > Export > Export as SVG...
151 1774 aaronmk
        Select schemas/vegbien.ERD.svg and click Save
152 702 aaronmk
        Go to File > Export > Export as Single Page PDF...
153 4087 aaronmk
        Select schemas/vegbien.ERD.1_pg.pdf and click Save
154 1774 aaronmk
        Go to File > Print...
155
        In the lower left corner, click PDF > Save as PDF...
156
        Set the Title and Author to ""
157 4087 aaronmk
        Select schemas/vegbien.ERD.pdf and click Save
158 203 aaronmk
159 1459 aaronmk
Testing:
160
    Mapping process: make test
161
    Map spreadsheet generation: make remake
162 1744 aaronmk
    Missing mappings: make missing_mappings
163 1459 aaronmk
    Everything (for most complete coverage): make test-all
164 702 aaronmk
165 3783 aaronmk
WinMerge setup:
166
    Install WinMerge from <http://winmerge.org/>
167 3785 aaronmk
    Open WinMerge
168
    Go to Edit > Options and click Compare in the left sidebar
169 3783 aaronmk
    Enable "Moved block detection", as described at
170
        <http://manual.winmerge.org/Configuration.html#d0e5892>.
171 3784 aaronmk
    Set Whitespace to Ignore change, as described at
172
        <http://manual.winmerge.org/Configuration.html#d0e5758>.
173 3783 aaronmk
174 3133 aaronmk
Documentation:
175
    To generate a Redmine-formatted list of steps for column-based import:
176 4122 aaronmk
        make inputs/QMOR/src/specimens/logs/steps.by_col.log.sql
177 3133 aaronmk
178 702 aaronmk
General:
179
    To see a program's description, read its top-of-file comment
180
    To see a program's usage, run it without arguments
181 3389 aaronmk
    To remake a directory: make <dir>/remake
182
    To remake a file: make <file>-remake