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
    Import data into VegBIEN: . bin/import_all
16
        Using column-based import: . bin/with_all 'import by_col=1'
17
    Stop all running imports: . bin/stop_imports
18
    Archive the last import: make schemas/rotate
19
    Remove the last import: make schemas/public/reinstall
20
        WARNING: This will delete the current public schema of your VegBIEN DB!
21
    Re-import data: make schemas/rotate; . bin/import_all
22
        Note: This will archive the last import.
23

    
24
Backups:
25
    After a new import:
26
        On vegbiendev:
27
        make schemas/rotate
28
        Rename the rotated schema using the date in the first datasource's log
29
            file name
30
        tail inputs/*/*/logs/<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
        Delete previous imports so they won't bloat the full DB backup:
35
            make backups/public.<datetime>.backup/remove
36
        make backups/<schema>.backup/test & make backups/vegbien.backup/all &
37
        On local machine:
38
            make inputs/download-logs
39
            make backups/download &
40
            If desired, record the import times in inputs/import.stats.xls
41
    Archived imports:
42
        Back up: make backups/public.<date>.backup &
43
            Note: To back up the last import, you must archive it first (above)
44
        Test: make backups/public.<date>.backup/test &
45
        Restore: make backups/public.<date>.backup/restore &
46
        Remove: make backups/public.<date>.backup/remove
47
        Download: make backups/download
48
    Full DB:
49
        Back up, test, and rotate: make backups/vegbien.backup/all &
50
        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
        Download: make backups/download
54
    Import logs:
55
        Download: make inputs/download-logs
56

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

    
128
Schema changes:
129
    Regenerate schema from installed DB: make schemas/remake
130
    Reinstall DB from schema: make schemas/reinstall
131
        WARNING: This will delete the current public schema of your VegBIEN DB!
132
    Reinstall errors tables: make inputs/install errors_table_only=1
133
    Reinstall staging tables: . bin/reinstall_all
134
    Sync ERD with vegbien.sql schema:
135
        Run make schemas/vegbien.my.sql
136
        Open schemas/vegbien.ERD.mwb in MySQLWorkbench
137
        Go to File > Export > Synchronize With SQL CREATE Script...
138
        For Input File, select schemas/vegbien.my.sql
139
        Click Continue
140
        Click in the changes list and press Ctrl+A or Apple+A to select all
141
        Click Update Model
142
        Click Continue
143
        Note: The generated SQL script will be empty because we are syncing in
144
            the opposite direction
145
        Click Execute
146
        Reposition any lines that have been reset
147
        Add any new tables by dragging them from the Catalog in the left sidebar
148
            to the diagram
149
        Remove any deleted tables by right-clicking the table's diagram element,
150
            selecting Delete '<table name>', and clicking Delete
151
        Save
152
        If desired, update the graphical ERD exports (see below)
153
    Update graphical ERD exports:
154
        Go to File > Export > Export as PNG...
155
        Select schemas/vegbien.ERD.png and click Save
156
        Go to File > Export > Export as SVG...
157
        Select schemas/vegbien.ERD.svg and click Save
158
        Go to File > Export > Export as Single Page PDF...
159
        Select schemas/vegbien.ERD.1_pg.pdf and click Save
160
        Go to File > Print...
161
        In the lower left corner, click PDF > Save as PDF...
162
        Set the Title and Author to ""
163
        Select schemas/vegbien.ERD.pdf and click Save
164

    
165
Testing:
166
    Mapping process: make test
167
    Map spreadsheet generation: make remake
168
    Missing mappings: make missing_mappings
169
    Everything (for most complete coverage): make test-all
170

    
171
WinMerge setup:
172
    Install WinMerge from <http://winmerge.org/>
173
    Open WinMerge
174
    Go to Edit > Options and click Compare in the left sidebar
175
    Enable "Moved block detection", as described at
176
        <http://manual.winmerge.org/Configuration.html#d0e5892>.
177
    Set Whitespace to Ignore change, as described at
178
        <http://manual.winmerge.org/Configuration.html#d0e5758>.
179

    
180
Documentation:
181
    To generate a Redmine-formatted list of steps for column-based import:
182
        make inputs/QMOR/specimens/logs/steps.by_col.log.sql
183

    
184
General:
185
    To see a program's description, read its top-of-file comment
186
    To see a program's usage, run it without arguments
187
    To remake a directory: make <dir>/remake
188
    To remake a file: make <file>-remake
(2-2/3)