Aggregating validations refactoring¶
- Table of contents
- Aggregating validations refactoring
translate to Postgres¶
in validation/aggregating/*/{,*/}*.sql
(the VegCore-input and VegBIEN-output queries):
- open in jEdit
- remove trailing whitespace: replace text
- replace text
REGEXP
with~
- for VegCore input queries:
- translate column names to VegCore:
bin/in_place validation/aggregating/.../__.VegCore.sql env text=1 bin/repl inputs/__/__/map.csv
- translate column names to VegCore:
- enclose mixed-case identifiers in
""
: replace regexp^(?!--)(.*)(?<![:.] )(?<!^ *)(?<!")\b([a-z]*[A-Z][a-z]\w*)\b(?!")
with$1"$2"
repeatedly, until no more replacements are made - reformat comments for diffing with
schemas/vegbien.sql
:- for each
--
commented section before a query, move just the notes in it (not the query name) to after the query and enclose them in/* ... */
- if applicable, move
Check
comments all at once: replace regexp^-- (Check:.*) ((?s).*?;$)
with$2 /* $1 */
- if applicable, move
- for each
prepend CREATE VIEW
¶
replace vars below: <observation_type>
(plots, specimens, traits)
in validation/aggregating/*/{,*/}*.sql
(the VegCore-input and VegBIEN-output queries):
- open in jEdit
- replace regexp
-- (\d+\..*)((?s).*?)(?=(?i) SELECT| )
with BeanShell snippet_0+"\CREATE VIEW _<observation_type>_0"+_1.toLowerCase().replaceAll("\\W+","_")+" AS"
- in all queries except the first, replace text
_01
with_1
- abbreviate view names longer than 63 chars to prevent them from being arbitrarily truncated
- remove no longer needed
--
comments containing the query name: in everything after the header comments, replace regexp--.*
with nothing - change
/* */
comments toCOMMENT ON
comments- replace regexp
^(CREATE VIEW (.*?) AS (?s:.*?);) /\*
with$1 COMMENT ON VIEW $2 IS '
- replace text
*/
with';
- escape any
'
inside'...'
by doubling them
- replace regexp
prep for pipeline¶
these changes do not get committed, but instead get performed right before adding finished statement(s) to the pipeline
in validation/aggregating/*/*.sql
(the VegBIEN-output queries):
- open in jEdit
- replace text
:datasource
with"current_schema"()::text
remove DDL statements¶
after importing the finished statements to the DB, this is done as a final step so that running the query file does not alter the database
in validation/aggregating/*/{,*/}*.sql
(the VegCore-input and VegBIEN-output queries):
- open in jEdit
- replace regexp
^CREATE VIEW (.*?) AS$
with-- $1
- replace regexp
^COMMENT ON VIEW .*? IS '$
with/*
- replace text
';
with*/
- replace text
public.
with nothing