Revision 10754
Added by Aaron Marcuse-Kubitza over 11 years ago
| inputs/.TNRS/schema.sql | ||
|---|---|---|
| 107 | 107 |
LANGUAGE sql STABLE STRICT |
| 108 | 108 |
COST 100; |
| 109 | 109 |
|
| 110 |
/* IMPORTANT: when changing this table's schema, you must regenerate data.sql: |
|
| 111 |
$ <this_file>/../test_taxonomic_names/test_scrub |
|
| 112 |
you must also make the same changes on vegbiendev: e.g. |
|
| 113 |
$ make backups/TNRS.backup-remake # runtime: 3 min ("real 2m44.277s")
|
|
| 114 |
SELECT util.set_col_types('"TNRS".tnrs', ARRAY[
|
|
| 115 |
('col', 'new_type')
|
|
| 116 |
]::util.col_cast[]); |
|
| 117 |
runtime: 9 min ("531282 ms")
|
|
| 118 |
*/ |
|
| 119 | 110 |
CREATE TABLE tnrs |
| 120 | 111 |
( |
| 121 | 112 |
batch text NOT NULL DEFAULT now(), |
| ... | ... | |
| 168 | 159 |
WITH ( |
| 169 | 160 |
OIDS=FALSE |
| 170 | 161 |
); |
| 162 |
COMMENT ON TABLE tnrs |
|
| 163 |
IS 'IMPORTANT: when changing this table''s schema, you must regenerate data.sql: |
|
| 164 |
$ <this_file>/../test_taxonomic_names/test_scrub |
|
| 165 |
you must also make the same changes on vegbiendev: e.g. |
|
| 166 |
$ make backups/TNRS.backup-remake # runtime: 3 min ("real 2m44.277s")
|
|
| 167 |
SELECT util.set_col_types(''"TNRS".tnrs'', ARRAY[
|
|
| 168 |
(''col'', ''new_type'')
|
|
| 169 |
]::util.col_cast[]); |
|
| 170 |
runtime: 9 min ("531282 ms")';
|
|
| 171 | 171 |
|
| 172 | 172 |
CREATE UNIQUE INDEX tnrs_score_ok |
| 173 | 173 |
ON tnrs |
| ... | ... | |
| 175 | 175 |
("Name_submitted" )
|
| 176 | 176 |
WHERE score_ok("Max_score");
|
| 177 | 177 |
|
| 178 |
/* IMPORTANT: when changing this function, you must regenerate the derived cols: |
|
| 179 |
UPDATE "TNRS".tnrs SET "Name_submitted" = "Name_submitted" |
|
| 180 |
runtime: 25 min ("5221748 rows affected, 1524228 ms execution time")
|
|
| 181 |
VACUUM ANALYZE "TNRS".tnrs -- to remove previous rows |
|
| 182 |
runtime: 1 min ("62350 ms")
|
|
| 183 |
*/ |
|
| 184 | 178 |
CREATE OR REPLACE FUNCTION tnrs_populate_fields() |
| 185 | 179 |
RETURNS trigger AS |
| 186 | 180 |
$BODY$ |
| ... | ... | |
| 231 | 225 |
$BODY$ |
| 232 | 226 |
LANGUAGE plpgsql VOLATILE |
| 233 | 227 |
COST 100; |
| 228 |
COMMENT ON FUNCTION tnrs_populate_fields() IS 'IMPORTANT: when changing this function, you must regenerate the derived cols: |
|
| 229 |
UPDATE "TNRS".tnrs SET "Name_submitted" = "Name_submitted" |
|
| 230 |
runtime: 25 min ("5221748 rows affected, 1524228 ms execution time")
|
|
| 231 |
VACUUM ANALYZE "TNRS".tnrs -- to remove previous rows |
|
| 232 |
runtime: 1 min ("62350 ms")';
|
|
| 234 | 233 |
|
| 235 | 234 |
CREATE TRIGGER tnrs_populate_fields |
| 236 | 235 |
BEFORE INSERT OR UPDATE |
Also available in: Unified diff
inputs/.TNRS/schema.sql: moved source code comments to in-schema COMMENT ON comments so all the info in schema.sql is in the DB