Skip to content

Commit c856209

Browse files
SimonGoringSimonGoring
committed
Resolved merge conflicts.
2 parents 1d64b69 + c15fe4d commit c856209

10 files changed

+230
-0
lines changed

‎function/longgeoname_view.SQL‎

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,24 @@
1+
CREATE MATERIALIZED VIEW ti.longgeonames AS
2+
WITH RECURSIVE gpid AS (
3+
SELECT
4+
gpu.geopoliticalid AS child,
5+
gpu.highergeopoliticalid AS parent,
6+
gpu.geopoliticalname,
7+
CONCAT(gpu.geopoliticalname, '') AS place,
8+
1 AS depth_level
9+
FROM ndb.geopoliticalunits AS gpu
10+
UNION ALL
11+
SELECT
12+
gpu.geopoliticalid AS child,
13+
gpu.highergeopoliticalid AS parent,
14+
gpu.geopoliticalname,
15+
CONCAT(gpu.geopoliticalname, ', ', hgpu.place) AS place,
16+
hgpu.depth_level + 1
17+
FROM gpid AS hgpu
18+
INNER JOIN ndb.geopoliticalunits AS gpu ON hgpu.child = gpu.highergeopoliticalid)
19+
20+
SELECT child AS geopoliticalid, geopoliticalname, place AS long_name
21+
FROM gpid AS main
22+
WHERE main.depth_level = (SELECT max(ml.depth_level) FROM gpid AS ml WHERE ml.child = main.child);
23+
24+
WITH data

‎helpers/add_relations.sql‎

Lines changed: 64 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,64 @@
1+
DELETE FROM ndb.elementdatasettaxagroups AS edtg
2+
WHERE edtg.taxagroupid NOT IN (SELECT taxagroupid FROM ndb.taxagrouptypes);
3+
ALTER TABLE ndb.elementdatasettaxagroups
4+
ADD CONSTRAINT edt_group FOREIGN KEY (taxagroupid) REFERENCES taxagrouptypes(taxagroupid);
5+
6+
DELETE FROM ndb.elementdatasettaxagroups AS edtg
7+
WHERE edtg.elementtypeid NOT IN (SELECT elementtypeid FROM ndb.elementtypes);
8+
ALTER TABLE ndb.elementdatasettaxagroups
9+
ADD CONSTRAINT edt_types FOREIGN KEY (elementtypeid) REFERENCES elementtypes (elementtypeid);
10+
11+
DELETE FROM ndb.eventchronology AS ec
12+
WHERE ec.analysisunitid NOT IN (SELECT analysisunitid FROM ndb.analysisunits);
13+
ALTER TABLE ndb.eventchronology
14+
ADD CONSTRAINT evc_alu FOREIGN KEY (analysisunitid) REFERENCES analysisunits (analysisunitid);
15+
16+
DELETE FROM ndb.eventchronology AS ec
17+
WHERE ec.eventid NOT IN (SELECT eventid FROM ndb.events);
18+
ALTER TABLE ndb.eventchronology
19+
ADD CONSTRAINT evc_evt FOREIGN KEY (eventid) REFERENCES events (eventid);
20+
21+
DELETE FROM ndb.eventchronology AS ech
22+
WHERE ech.chroncontrolid NOT IN (SELECT chroncontrolid FROM ndb.chroncontrols);
23+
ALTER TABLE ndb.eventchronology
24+
ADD CONSTRAINT evc_ccid FOREIGN KEY (chroncontrolid) REFERENCES chroncontrols (chroncontrolid);
25+
26+
DELETE FROM ndb.eventtypes AS ety
27+
WHERE ety.chroncontroltypeid NOT IN (SELECT chroncontroltypeid FROM ndb.chroncontroltypes);
28+
ALTER TABLE ndb.eventtypes
29+
ADD CONSTRAINT evt_cct FOREIGN KEY (chroncontroltypeid) REFERENCES chroncontroltypes (chroncontroltypeid);
30+
31+
DELETE FROM ndb.publications AS pub
32+
WHERE pub.pubtypeid NOT IN (SELECT pubtypeid FROM ndb.publicationtypes);
33+
ALTER TABLE ndb.publications
34+
ADD CONSTRAINT pub_pty FOREIGN KEY (pubtypeid) REFERENCES publicationtypes (pubtypeid);
35+
36+
DELETE FROM ndb.relativechronology AS rc
37+
WHERE rc.chroncontrolid NOT IN (SELECT chroncontrolid FROM ndb.chroncontrols);
38+
ALTER TABLE ndb.relativechronology
39+
ADD CONSTRAINT rc_ccid FOREIGN KEY (chroncontrolid) REFERENCES chroncontrols (chroncontrolid);
40+
41+
DELETE FROM ndb.specimendates AS sd
42+
WHERE sd.geochronid NOT IN (SELECT geochronid FROM ndb.geochronology);
43+
ALTER TABLE ndb.specimendates
44+
ADD CONSTRAINT sd_gcid FOREIGN KEY (geochronid) REFERENCES geochronology (geochronid);
45+
46+
DELETE FROM ndb.specimendates AS sd
47+
WHERE sd.taxonid NOT IN (SELECT taxonid FROM ndb.taxa);
48+
ALTER TABLE ndb.specimendates
49+
ADD CONSTRAINT sd_txid FOREIGN KEY (taxonid) REFERENCES taxa (taxonid);
50+
51+
DELETE FROM ndb.specimendates AS sd
52+
WHERE sd.fractionid NOT IN (SELECT fractionid FROM ndb.fractiondated);
53+
ALTER TABLE ndb.specimendates
54+
ADD CONSTRAINT sd_fcid FOREIGN KEY (fractionid) REFERENCES fractiondated (fractionid);
55+
56+
DELETE FROM ndb.specimendates AS sd
57+
WHERE sd.sampleid NOT IN (SELECT sampleid FROM ndb.samples);
58+
ALTER TABLE ndb.specimendates
59+
ADD CONSTRAINT sd_smpid FOREIGN KEY (sampleid) REFERENCES samples (sampleid);
60+
61+
DELETE FROM ndb.specimendates AS sd
62+
WHERE sd.elementtypeid NOT IN (SELECT elementtypeid FROM ndb.elementtypes);
63+
ALTER TABLE ndb.specimendates
64+
ADD CONSTRAINT sd_etyid FOREIGN KEY (elementtypeid) REFERENCES elementtypes (elementtypeid);

‎helpers/fullcontactnames.sql‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1 @@
1+
CREATE MATERIALIZED VIEW mymatview AS SELECT * FROM mytab;

‎helpers/geopol_ancestors.sql‎

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,21 @@
1+
CREATE TABLE ndb.geopaths (
2+
geoout INTEGER[] NOT NULL,
3+
geoin BIGINT NOT NULL,
4+
PRIMARY KEY (geoout, geoin),
5+
FOREIGN KEY (geoin) REFERENCES ndb.geopoliticalunits(geopoliticalid)
6+
);
7+
8+
INSERT INTO ndb.geopaths(geoout, geoin)
9+
10+
WITH RECURSIVE geopol AS (
11+
SELECT gp.geopoliticalid, ARRAY[]::integer[] AS ancestors
12+
FROM ndb.geopoliticalunits AS gp
13+
WHERE gp.highergeopoliticalid = 0
14+
UNION ALL
15+
SELECT gp.geopoliticalid, geopol.ancestors || gp.highergeopoliticalid
16+
FROM ndb.geopoliticalunits AS gp, geopol
17+
WHERE gp.highergeopoliticalid = geopol.geopoliticalid)
18+
19+
SELECT geo.ancestors AS geoout,
20+
geo.geopoliticalid AS geoin
21+
FROM geopol AS geo

‎helpers/getallforeignkeys.SQL‎

Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,31 @@
1+
COPY
2+
(SELECT fk_nsp.nspname || '.' || fk_table AS fk_table,
3+
array_agg(fk_att.attname ORDER BY fk_att.attnum) AS fk_columns,
4+
tar_nsp.nspname || '.' || target_table AS target_table,
5+
array_agg(tar_att.attname ORDER BY fk_att.attnum) AS target_columns
6+
FROM (
7+
SELECT
8+
fk.oid AS fk_table_id,
9+
fk.relnamespace AS fk_schema_id,
10+
fk.relname AS fk_table,
11+
unnest(con.conkey) as fk_column_id,
12+
13+
tar.oid AS target_table_id,
14+
tar.relnamespace AS target_schema_id,
15+
tar.relname AS target_table,
16+
unnest(con.confkey) as target_column_id,
17+
18+
con.connamespace AS constraint_nsp,
19+
con.conname AS constraint_name
20+
21+
FROM pg_constraint con
22+
JOIN pg_class fk ON con.conrelid = fk.oid
23+
JOIN pg_class tar ON con.confrelid = tar.oid
24+
WHERE con.contype = 'f'
25+
) sub
26+
JOIN pg_attribute fk_att ON fk_att.attrelid = fk_table_id AND fk_att.attnum = fk_column_id
27+
JOIN pg_attribute tar_att ON tar_att.attrelid = target_table_id AND tar_att.attnum = target_column_id
28+
JOIN pg_namespace fk_nsp ON fk_schema_id = fk_nsp.oid
29+
JOIN pg_namespace tar_nsp ON target_schema_id = tar_nsp.oid
30+
GROUP BY 1, 3, sub.constraint_nsp, sub.constraint_name)
31+
TO '/tmp/selfref.csv' CSV HEADER;

‎helpers/neofulldump.sh‎

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,5 @@
11
#!/bin/bash
2+
<<<<<<< HEAD
23
# Create local file dumps of the Neotoma Database.
34
# Created June 7, 2021 by Simon Goring
45
#
@@ -46,3 +47,21 @@ pg_dump -Fc -O -h db5.cei.psu.edu -U $1 -n ndb -v -d neotoma > ./dumps${now}/neo
4647
tar -cvf ./archives/neotoma_ndb_full_${now}.tar -C ./dumps${now} neotoma_ndb_full_${now}.sql
4748
rm ./dumps${now}/*
4849
rmdir ./dumps${now}
50+
=======
51+
if [ $# -eq 0 ]; then
52+
echo "Please provide your username, e.g.:"
53+
echo "> bash neofulldump.sh postgres"
54+
exit 1
55+
fi
56+
now=`date +"%Y-%m-%d"`
57+
mkdir -p dumps
58+
mkdir -p archives
59+
pg_dump -Fc -O -v -h db5.cei.psu.edu -U $1 -s neotoma > ./dumps/neotoma_ndb_schema_${now}.sql
60+
pg_dump -Fc -O -v -h db5.cei.psu.edu -U $1 -N gen -N tmp -W -v -d neotoma > ./dumps/neotoma_dump_full_${now}.sql
61+
tar -cvf ./archives/neotoma_dump_full_${now}.tar -C ./dumps neotoma_dump_full_${now}.sql
62+
rm ./dumps/neotoma_dump_full_${now}.sql
63+
pg_dump -Fc -O -h db5.cei.psu.edu -U $1 -N gen -n ndb -W -v -d neotoma > ./dumps/neotoma_dump_ndb_${now}.sql
64+
tar -cvf ./archives/neotoma_dump_ndb_${now}.tar -C ./dumps neotoma_dump_ndb_${now}.sql
65+
rm ./dumps/neotoma_dump_ndb_${now}.sql
66+
rmdir ./dumps
67+
>>>>>>> 9e2ea20b36954d3a88d2b6f48da67cecb4c72804

‎helpers/reset_sequences.sql‎

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,15 @@
1+
/* Code from here: */
2+
SELECT 'select '
3+
|| trim(trailing ')'
4+
from replace(pg_get_expr(d.adbin, d.adrelid),
5+
'nextval', 'setval'))
6+
|| ', (select max( ' || a.attname || ') from only '
7+
|| nspname || '.' || relname || '));'
8+
FROM pg_class c
9+
JOIN pg_namespace n ON n.oid = c.relnamespace
10+
JOIN pg_attribute a ON a.attrelid = c.oid
11+
JOIN pg_attrdef d ON d.adrelid = a.attrelid
12+
AND d.adnum = a.attnum
13+
AND a.atthasdef
14+
WHERE relkind = 'r' and a.attnum > 0
15+
AND pg_get_expr(d.adbin, d.adrelid) ~ '^nextval';

‎helpers/siteswithMultipleLithologies.sql‎

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,16 @@
1+
WITH lithos AS (
2+
SELECT st.siteid,
3+
cu.collectionunitid,
4+
COUNT(lt.lithologyid)
5+
FROM ndb.lithology AS lt
6+
INNER JOIN ndb.collectionunits AS cu ON cu.collectionunitid = lt.collectionunitid
7+
INNER JOIN ndb.sites AS st ON st.siteid = cu.siteid
8+
GROUP BY st.siteid, cu.collectionunitid)
9+
SELECT li.siteid,
10+
dsl.datasetid,
11+
COUNT(*) FROM lithos AS li
12+
INNER JOIN ndb.dslinks AS dsl ON dsl.siteid = li.siteid
13+
INNER JOIN ndb.datasetdatabases AS dsdb ON dsdb.datasetid = dsl.datasetid
14+
WHERE dsdb.databaseid = 3
15+
GROUP BY siteid
16+
HAVING COUNT(*) > 1

‎helpers/taxon_ancestors.sql‎

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,21 @@
1+
CREATE TABLE ndb.taxonpaths (
2+
taxonout INTEGER[] NOT NULL,
3+
taxonid BIGINT NOT NULL,
4+
PRIMARY KEY (taxonout, taxonid),
5+
FOREIGN KEY (taxonid) REFERENCES ndb.taxa(taxonid)
6+
);
7+
8+
INSERT INTO ndb.taxonpaths(taxonout, taxonid)
9+
10+
WITH RECURSIVE taxon AS (
11+
SELECT tx.taxonid, ARRAY[]::integer[] AS ancestors
12+
FROM ndb.taxa AS tx
13+
WHERE tx.highertaxonid = tx.taxonid
14+
UNION ALL
15+
SELECT tx.taxonid, taxon.ancestors || gp.highertaxonid
16+
FROM ndb.taxa AS tx, taxon
17+
WHERE tx.highertaxonid = taxon.taxonid)
18+
19+
SELECT taxon.ancestors AS taxonout,
20+
taxon.taxonid AS taxonin
21+
FROM taxon AS taxon;

‎helpers/tephra_query.sql‎

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,18 @@
1+
SELECT ds.datasetid,
2+
dst.datasettype,
3+
st.sitename,
4+
ST_asText(st.geog),
5+
ds.notes AS datasetnotes,
6+
au.analysisunitid,
7+
au.analysisunitname,
8+
au.notes AS analysisunitnotes,
9+
ev.eventname,
10+
tph.notes AS tephranotes
11+
FROM ndb.tephras AS tph
12+
INNER JOIN ndb.analysisunits AS au ON au.analysisunitid = tph.analysisunitid
13+
INNER JOIN ndb.collectionunits AS cu ON au.collectionunitid = cu.collectionunitid
14+
INNER JOIN ndb.events AS ev ON ev.eventid = tph.eventid
15+
INNER JOIN ndb.datasets AS ds ON ds.collectionunitid = cu.collectionunitid
16+
INNER JOIN ndb.datasettypes AS dst ON ds.datasettypeid = dst.datasettypeid
17+
INNER JOIN ndb.sites AS st ON st.siteid = cu.siteid
18+
WHERE ev.eventname ILIKE'%tephra%'

0 commit comments

Comments
 (0)