Skip to content

Commit 127d0f8

Browse files
SimonGoringSimonGoring
committed
Here's a set of commits
These reflect edits to the Neotoma SQL.
1 parent 2996185 commit 127d0f8

19 files changed

+112
-106
lines changed

‎function/da/chronologybyid.sql‎

Lines changed: 1 addition & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -1,15 +1,5 @@
11
CREATE OR REPLACE FUNCTION da.chronologybyid(_chronid integer)
2-
RETURNS TABLE(controls json,
3-
"Default" boolean,
4-
ChronologyName character varying,
5-
AgeType character varying,
6-
AgeModel character varying,
7-
AgeOlder integer,
8-
ChronologyID integer,
9-
AgeYounger integer,
10-
datasets json,
11-
notes character varying,
12-
dateprepared date)
2+
RETURNS TABLE(controls json, "Default" boolean, chronologyname character varying, agetype character varying, agemodel character varying, ageolder integer, chronologyid integer, ageyounger integer, datasets json, notes character varying, dateprepared date)
133
LANGUAGE sql
144
AS $function$
155

‎function/doi/datasetinfo.sql‎

Lines changed: 20 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -1,37 +1,42 @@
1-
CREATE OR REPLACE FUNCTION doi.datasetinfo(dsid integer)
1+
CREATE OR REPLACE FUNCTION doi.datasetinfo(dsid integer[])
22
RETURNS TABLE(datasetid integer, dataset json)
33
LANGUAGE sql
44
AS $function$
55

66
SELECT dts.datasetid,
77
json_build_object('site', json_build_object('siteid', sts.siteid,
88
'sitename', sts.sitename,
9+
'depositionalenvironment', dvt.depenvt,
910
'sitedescription', sts.sitedescription,
1011
'sitenotes', sts.notes,
1112
'geography', ST_AsGeoJSON(sts.geog,5,2),
1213
'altitude', sts.altitude,
13-
'collectionunitid', clu.collectionunitid,
14+
'collectionunit', json_build_object('collectionunitid', clu.collectionunitid,
1415
'collectionunit', clu.collunitname,
15-
'handle', clu.handle,
16-
'unittype', cts.colltype),
16+
'handle', clu.handle,
17+
'collunittype', cts.colltype,
18+
'colldate', clu.colldate,
19+
'notes', clu.notes,
20+
'location', clu.location,
1721
'dataset', json_build_object( 'datasetid', dts.datasetid,
1822
'datasettype', dst.datasettype,
1923
'datasetnotes', dts.notes,
2024
'database', cstdb.databasename,
2125
'doi', doi.dois,
2226
'datasetpi', dsau.authors,
23-
'agerange', agerange.ages))
27+
'agerange', agerange.ages))))
2428
FROM
25-
ndb.datasets AS dts LEFT OUTER JOIN
26-
ndb.collectionunits AS clu ON clu.collectionunitid = dts.collectionunitid LEFT OUTER JOIN
27-
ndb.sites AS sts ON sts.siteid = clu.siteid LEFT OUTER JOIN
28-
ndb.datasettypes AS dst ON dst.datasettypeid = dts.datasettypeid LEFT OUTER JOIN
29-
ndb.collectiontypes as cts ON clu.colltypeid = cts.colltypeid LEFT OUTER JOIN
29+
ndb.datasets AS dts LEFT OUTER JOIN
30+
ndb.collectionunits AS clu ON clu.collectionunitid = dts.collectionunitid LEFT OUTER JOIN
31+
ndb.depenvttypes AS dvt ON dvt.depenvtid = clu.depenvtid LEFT OUTER JOIN
32+
ndb.sites AS sts ON sts.siteid = clu.siteid LEFT OUTER JOIN
33+
ndb.datasettypes AS dst ON dst.datasettypeid = dts.datasettypeid LEFT OUTER JOIN
34+
ndb.collectiontypes AS cts ON clu.colltypeid = cts.colltypeid LEFT OUTER JOIN
3035
ndb.datasetdatabases AS dsdb ON dsdb.datasetid = dts.datasetid LEFT OUTER JOIN
31-
(SELECT * FROM doi.agerange(dsid)) AS agerange ON agerange.datasetid = dts.datasetid LEFT OUTER JOIN
32-
ndb.constituentdatabases AS cstdb ON dsdb.databaseid = cstdb.databaseid LEFT OUTER JOIN
33-
(SELECT * FROM doi.doireturn(dsid)) AS doi ON doi.datasetid = dts.datasetid LEFT OUTER JOIN
34-
(SELECT * FROM doi.datasetauthors(dsid)) AS dsau ON dsau.datasetid = dts.datasetid
35-
WHERE dts.datasetid = dsid
36+
(SELECT * FROM doi.agerange(dsid)) AS agerange ON agerange.datasetid = dts.datasetid LEFT OUTER JOIN
37+
ndb.constituentdatabases AS cstdb ON dsdb.databaseid = cstdb.databaseid LEFT OUTER JOIN
38+
(SELECT * FROM doi.doireturn(dsid)) AS doi ON doi.datasetid = dts.datasetid LEFT OUTER JOIN
39+
(SELECT * FROM doi.datasetauthors(dsid)) AS dsau ON dsau.datasetid = dts.datasetid
40+
WHERE dts.datasetid = ANY(dsid)
3641

3742
$function$

‎function/doi/doireturn.sql‎

Lines changed: 6 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1,9 +1,9 @@
1-
CREATE OR REPLACE FUNCTION doi.doireturn(dsid integer)
1+
CREATE OR REPLACE FUNCTION doi.doireturn(dsid integer[])
22
RETURNS TABLE(datasetid integer, dois json)
33
LANGUAGE sql
4-
AS $function$
5-
SELECT doi.datasetid, json_agg(doi.doi)
6-
FROM ndb.datasetdoi AS doi
7-
WHERE doi.datasetid = dsid
8-
GROUP BY doi.datasetid;
4+
AS $function$
5+
SELECT doi.datasetid, json_agg(doi.doi)
6+
FROM ndb.datasetdoi AS doi
7+
WHERE doi.datasetid = ANY(dsid)
8+
GROUP BY doi.datasetid;
99
$function$

‎function/doi/ndbdata.sql‎

Lines changed: 12 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -9,10 +9,13 @@ WITH dssamples AS (
99
'depth', anu.depth,
1010
'datum', jsonb_agg(DISTINCT jsonb_strip_nulls(jsonb_build_object('value', dt.value,
1111
'variablename', tx.taxonname,
12-
'element', ve.variableelement,
13-
'elementtype', vt.elementtype,
14-
'symmetry', vs.symmetry,
15-
'context', vc.variablecontext,
12+
'taxonid', tx.taxonid,
13+
'taxongroup', txg.taxagroup,
14+
'ecologicalgroup', ecg.ecolgroupid,
15+
'element', ve.variableelement,
16+
'elementtype', vt.elementtype,
17+
'symmetry', vs.symmetry,
18+
'context', vc.variablecontext,
1619
'units', vru.variableunits))),
1720
'sampleanalyst', json_agg(DISTINCT jsonb_strip_nulls(jsonb_build_object('contactid', cnt.contactid,
1821
'contactname', cnt.contactname,
@@ -32,6 +35,8 @@ WITH dssamples AS (
3235
LEFT OUTER JOIN ndb.data AS dt ON dt.dataid = dsd.dataid
3336
LEFT OUTER JOIN ndb.variables as var ON var.variableid = dsd.variableid
3437
LEFT OUTER JOIN ndb.taxa AS tx ON tx.taxonid = var.taxonid
38+
LEFT OUTER JOIN ndb.taxagrouptypes AS txg ON txg.taxagroupid = tx.taxagroupid
39+
LEFT OUTER JOIN ndb.ecolgroups AS ecg ON ecg.taxonid = tx.taxonid
3540
LEFT OUTER JOIN ndb.variableunits AS vru ON vru.variableunitsid = var.variableunitsid
3641
LEFT OUTER JOIN ndb.samples AS smp ON smp.sampleid = dsd.sampleid
3742
LEFT OUTER JOIN ndb.sampleanalysts AS san ON san.sampleid = smp.sampleid
@@ -54,11 +59,12 @@ WITH dssamples AS (
5459
ds.datasetid,
5560
jsonb_build_object('dataset', dsinfo.dataset,
5661
'samples', json_agg(dss.sampledata)) AS data
57-
5862
FROM
5963
ndb.datasets AS ds
6064
JOIN dssamples AS dss ON ds.datasetid = dss.datasetid
61-
JOIN (SELECT datasetid, dataset::jsonb FROM doi.datasetinfo(dsid)) AS dsinfo ON dsinfo.datasetid = ds.datasetid
65+
JOIN (SELECT datasetid,
66+
dataset::jsonb
67+
FROM doi.datasetinfo(dsid)) AS dsinfo ON dsinfo.datasetid = ds.datasetid
6268
WHERE ds.datasetid = dsid
6369
GROUP BY ds.datasetid, dsinfo.dataset
6470

‎function/ndb/datasetconstitdb.sql‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6,6 +6,7 @@ AS $function$
66
FROM ndb.datasets AS ds
77
JOIN ndb.datasetsubmissions AS dss on dss.datasetid = ds.datasetid
88
JOIN ndb.constituentdatabases AS cdb ON cdb.databaseid = dss.databaseid
9-
WHERE EXTRACT(month from AGE(NOW(), dss.submissiondate)) BETWEEN startperiod and endperiod
9+
WHERE EXTRACT(year from AGE(NOW(), dss.submissiondate))*12 +
10+
EXTRACT(month from AGE(NOW(), dss.submissiondate)) BETWEEN startperiod and endperiod
1011
GROUP BY cdb.databasename
1112
$function$

‎function/ndb/stewardcontrib.sql‎

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -6,6 +6,7 @@ AS $function$
66
FROM ndb.datasets AS ds
77
JOIN ndb.datasetsubmissions AS dss on dss.datasetid = ds.datasetid
88
JOIN ndb.constituentdatabases AS cdb ON cdb.databaseid = dss.databaseid
9-
WHERE EXTRACT(month from AGE(NOW(), dss.submissiondate)) BETWEEN startperiod and endperiod
9+
WHERE EXTRACT(year from AGE(NOW(), dss.submissiondate))*12 +
10+
EXTRACT(month from AGE(NOW(), dss.submissiondate)) BETWEEN startperiod and endperiod
1011
GROUP BY cdb.databasename
1112
$function$

‎function/ti/getaggregatechronbydatasetid.sql‎

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,7 @@
1-
CREATE OR REPLACE FUNCTION ti.getaggregatechronbydatasetid(_aggregatedatasetid integer) RETURNS TABLE(aggregatechronid integer, aggregatedatasetid integer, agetypeid integer, isdefault boolean, chronologyname CHARACTER varying, ageboundyounger integer, ageboundolder integer, notes CHARACTER varying) LANGUAGE SQL AS $function$
1+
CREATE OR REPLACE FUNCTION ti.getaggregatechronbydatasetid(_aggregatedatasetid integer)
2+
RETURNS TABLE(aggregatechronid integer, aggregatedatasetid integer, agetypeid integer, isdefault boolean, chronologyname character varying, ageboundyounger integer, ageboundolder integer, notes character varying)
3+
LANGUAGE sql
4+
AS $function$
25

36
SELECT aggregatechronid,
47
aggregatedatasetid,

‎function/ti/getdatasetidfordatasettypeidforgeochronanalunit.sql‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
1-
CREATE OR REPLACE FUNCTION ti.getdatasetidfordatasettypeidforgeochronanalunit(_geochronid int, _datasettypeid int)
2-
RETURNS TABLE(datasetid INTEGER)
3-
LANGUAGE sql
1+
CREATE OR REPLACE FUNCTION ti.getdatasetidfordatasettypeidforgeochronanalunit(_geochronid integer, _datasettypeid integer)
2+
RETURNS TABLE(datasetid integer)
3+
LANGUAGE sql
44
AS $function$
55

66
SELECT ds.datasetid

‎function/ti/site_bounding.sql‎

Lines changed: 5 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,7 @@
1-
CREATE OR REPLACE FUNCTION ti.site_bounding() RETURNS TRIGGER
2-
AS $$
1+
CREATE OR REPLACE FUNCTION ti.site_bounding()
2+
RETURNS trigger
3+
LANGUAGE plpgsql
4+
AS $function$
35
BEGIN
46
IF NEW.latitudenorth IS NULL AND NEW.geog IS NOT NULL THEN
57
UPDATE ndb.sites
@@ -12,4 +14,4 @@ BEGIN
1214
RETURN NEW;
1315
END;
1416

15-
$$ LANGUAGE plpgsql;
17+
$function$

‎function/ts/insertsteward.sql‎

Lines changed: 1 addition & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,8 +1,4 @@
1-
CREATE OR REPLACE FUNCTION ts.insertsteward(_contactid integer,
2-
_username character varying,
3-
_password character varying,
4-
_taxonomyexpert boolean,
5-
_databaseid integer)
1+
CREATE OR REPLACE FUNCTION ts.insertsteward(_contactid integer, _username character varying, _password character varying, _taxonomyexpert boolean, _databaseid integer)
62
RETURNS integer
73
LANGUAGE sql
84
AS $function$

0 commit comments

Comments
 (0)