| 1 |
5491
|
aaronmk
|
SELECT *
|
| 2 |
5721
|
aaronmk
|
FROM "nodes.src"
|
| 3 |
11863
|
aaronmk
|
JOIN "names.src" USING ("taxonID")
|
| 4 |
|
|
WHERE "*name class" = 'scientific name'
|
| 5 |
5491
|
aaronmk
|
;
|
| 6 |
|
|
|
| 7 |
11863
|
aaronmk
|
ALTER TABLE :table ALTER COLUMN "taxonID" TYPE integer
|
| 8 |
|
|
USING "taxonID"::integer;
|
| 9 |
|
|
ALTER TABLE :table ALTER COLUMN "parentTaxonID" TYPE integer
|
| 10 |
|
|
USING "parentTaxonID"::integer;
|
| 11 |
5491
|
aaronmk
|
|
| 12 |
11863
|
aaronmk
|
ALTER TABLE :table ADD PRIMARY KEY ("taxonID");
|
| 13 |
5820
|
aaronmk
|
|
| 14 |
11863
|
aaronmk
|
CREATE INDEX ON :table ("parentTaxonID");
|
| 15 |
5820
|
aaronmk
|
|
| 16 |
11863
|
aaronmk
|
ALTER TABLE :table ADD FOREIGN KEY ("parentTaxonID") REFERENCES :table ("taxonID")
|
| 17 |
5820
|
aaronmk
|
ON UPDATE CASCADE ON DELETE CASCADE;
|
| 18 |
5821
|
aaronmk
|
|
| 19 |
11863
|
aaronmk
|
-- Make "taxonNameOrEpithet" (mostly) globally unique by removing other kingdoms
|
| 20 |
6032
|
aaronmk
|
-- Note that the delete will cascade to descendants
|
| 21 |
11863
|
aaronmk
|
DELETE FROM :table WHERE "taxonID" IN (
|
| 22 |
6033
|
aaronmk
|
10239 -- superkingdom Viruses
|
| 23 |
|
|
, 12884 -- superkingdom Viroids
|
| 24 |
|
|
, 12908 -- unclassified sequences
|
| 25 |
|
|
, 28384 -- other sequences
|
| 26 |
|
|
, 2 -- superkingdom Bacteria
|
| 27 |
|
|
, 2157 -- superkingdom Archaea
|
| 28 |
|
|
, 4751 -- kingdom Fungi
|
| 29 |
|
|
, 33208 -- kingdom Animalia/Metazoa
|
| 30 |
|
|
);
|
| 31 |
5835
|
aaronmk
|
|
| 32 |
11863
|
aaronmk
|
-- Make "taxonNameOrEpithet" completely globally unique by removing duplicates
|
| 33 |
5835
|
aaronmk
|
DELETE FROM :table
|
| 34 |
11863
|
aaronmk
|
WHERE "taxonNameOrEpithet" IN (
|
| 35 |
|
|
SELECT "taxonNameOrEpithet"
|
| 36 |
5835
|
aaronmk
|
FROM :table
|
| 37 |
11863
|
aaronmk
|
GROUP BY "taxonNameOrEpithet"
|
| 38 |
5835
|
aaronmk
|
HAVING count(*) > 1
|
| 39 |
|
|
)
|
| 40 |
|
|
;
|
