Skip to content

Commit 7a43fdf

Browse files
SimonGoringSimonGoring
committed
Updating the DOI/API requests for downloads.
1 parent 7ad6d27 commit 7a43fdf

File tree

4 files changed

+39
-26
lines changed

4 files changed

+39
-26
lines changed

‎function/doi/01_datasetinfo.sql‎

Lines changed: 13 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -6,6 +6,7 @@ AS $function$
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),
@@ -22,16 +23,17 @@ SELECT dts.datasetid,
2223
'datasetpi', dsau.authors,
2324
'agerange', agerange.ages))
2425
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
26+
ndb.datasets AS dts LEFT OUTER JOIN
27+
ndb.collectionunits AS clu ON clu.collectionunitid = dts.collectionunitid LEFT OUTER JOIN
28+
ndb.depenvttypes AS dvt ON dvt.depenvtid = clu.depenvtid LEFT OUTER JOIN
29+
ndb.sites AS sts ON sts.siteid = clu.siteid LEFT OUTER JOIN
30+
ndb.datasettypes AS dst ON dst.datasettypeid = dts.datasettypeid LEFT OUTER JOIN
31+
ndb.collectiontypes AS cts ON clu.colltypeid = cts.colltypeid LEFT OUTER JOIN
3032
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
33+
(SELECT * FROM doi.agerange(dsid)) AS agerange ON agerange.datasetid = dts.datasetid LEFT OUTER JOIN
34+
ndb.constituentdatabases AS cstdb ON dsdb.databaseid = cstdb.databaseid LEFT OUTER JOIN
35+
(SELECT * FROM doi.doireturn(dsid)) AS doi ON doi.datasetid = dts.datasetid LEFT OUTER JOIN
36+
(SELECT * FROM doi.datasetauthors(dsid)) AS dsau ON dsau.datasetid = dts.datasetid
3537
WHERE dts.datasetid = ANY(dsid)
3638

3739
$function$;
@@ -45,6 +47,7 @@ AS $function$
4547
SELECT dts.datasetid,
4648
json_build_object('site', json_build_object('siteid', sts.siteid,
4749
'sitename', sts.sitename,
50+
'depositionalenvironment', dvt.depenvt,
4851
'sitedescription', sts.sitedescription,
4952
'sitenotes', sts.notes,
5053
'geography', ST_AsGeoJSON(sts.geog,5,2),
@@ -63,6 +66,7 @@ SELECT dts.datasetid,
6366
FROM
6467
ndb.datasets AS dts LEFT OUTER JOIN
6568
ndb.collectionunits AS clu ON clu.collectionunitid = dts.collectionunitid LEFT OUTER JOIN
69+
ndb.depenvttypes AS dvt ON dvt.depenvtid = clu.depenvtid LEFT OUTER JOIN
6670
ndb.sites AS sts ON sts.siteid = clu.siteid LEFT OUTER JOIN
6771
ndb.datasettypes AS dst ON dst.datasettypeid = dts.datasettypeid LEFT OUTER JOIN
6872
ndb.collectiontypes as cts ON clu.colltypeid = cts.colltypeid LEFT OUTER JOIN

‎function/doi/02_ndbdata.sql‎

Lines changed: 17 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -10,10 +10,13 @@ WITH dssamples AS (
1010
'depth', anu.depth,
1111
'datum', jsonb_agg(DISTINCT jsonb_strip_nulls(jsonb_build_object('value', dt.value,
1212
'variablename', tx.taxonname,
13-
'element', ve.variableelement,
14-
'elementtype', vt.elementtype,
15-
'symmetry', vs.symmetry,
16-
'context', vc.variablecontext,
13+
'taxonid', tx.taxonid,
14+
'taxongroup', txg.taxagroup,
15+
'ecologicalgroup', ecg.ecolgroupid,
16+
'element', ve.variableelement,
17+
'elementtype', vt.elementtype,
18+
'symmetry', vs.symmetry,
19+
'context', vc.variablecontext,
1720
'units', vru.variableunits))),
1821
'sampleanalyst', json_agg(DISTINCT jsonb_strip_nulls(jsonb_build_object('contactid', cnt.contactid,
1922
'contactname', cnt.contactname,
@@ -33,6 +36,8 @@ WITH dssamples AS (
3336
LEFT OUTER JOIN ndb.data AS dt ON dt.dataid = dsd.dataid
3437
LEFT OUTER JOIN ndb.variables as var ON var.variableid = dsd.variableid
3538
LEFT OUTER JOIN ndb.taxa AS tx ON tx.taxonid = var.taxonid
39+
LEFT OUTER JOIN ndb.taxagrouptypes AS txg ON txg.taxagroupid = tx.taxagroupid
40+
LEFT OUTER JOIN ndb.ecolgroups AS ecg ON ecg.taxonid = tx.taxonid
3641
LEFT OUTER JOIN ndb.variableunits AS vru ON vru.variableunitsid = var.variableunitsid
3742
LEFT OUTER JOIN ndb.samples AS smp ON smp.sampleid = dsd.sampleid
3843
LEFT OUTER JOIN ndb.sampleanalysts AS san ON san.sampleid = smp.sampleid
@@ -55,11 +60,12 @@ WITH dssamples AS (
5560
ds.datasetid,
5661
jsonb_build_object('dataset', dsinfo.dataset,
5762
'samples', json_agg(dss.sampledata)) AS data
58-
5963
FROM
6064
ndb.datasets AS ds
6165
JOIN dssamples AS dss ON ds.datasetid = dss.datasetid
62-
JOIN (SELECT datasetid, dataset::jsonb FROM doi.datasetinfo(dsid)) AS dsinfo ON dsinfo.datasetid = ds.datasetid
66+
JOIN (SELECT datasetid,
67+
dataset::jsonb
68+
FROM doi.datasetinfo(dsid)) AS dsinfo ON dsinfo.datasetid = ds.datasetid
6369
WHERE ds.datasetid = dsid
6470
GROUP BY ds.datasetid, dsinfo.dataset
6571

@@ -77,6 +83,9 @@ WITH dssamples AS (
7783
'depth', anu.depth,
7884
'datum', jsonb_agg(DISTINCT jsonb_strip_nulls(jsonb_build_object('value', dt.value,
7985
'variablename', tx.taxonname,
86+
'taxonid', tx.taxonid,
87+
'taxongroup', txg.taxagroup,
88+
'ecologicalgroup', ecg.ecolgroupid,
8089
'element', ve.variableelement,
8190
'elementtype', vt.elementtype,
8291
'symmetry', vs.symmetry,
@@ -100,6 +109,8 @@ WITH dssamples AS (
100109
LEFT OUTER JOIN ndb.data AS dt ON dt.dataid = dsd.dataid
101110
LEFT OUTER JOIN ndb.variables as var ON var.variableid = dsd.variableid
102111
LEFT OUTER JOIN ndb.taxa AS tx ON tx.taxonid = var.taxonid
112+
LEFT OUTER JOIN ndb.taxagrouptypes AS txg ON txg.taxagroupid = tx.taxagroupid
113+
LEFT OUTER JOIN ndb.ecolgroups AS ecg ON ecg.taxonid = tx.taxonid
103114
LEFT OUTER JOIN ndb.variableunits AS vru ON vru.variableunitsid = var.variableunitsid
104115
LEFT OUTER JOIN ndb.samples AS smp ON smp.sampleid = dsd.sampleid
105116
LEFT OUTER JOIN ndb.sampleanalysts AS san ON san.sampleid = smp.sampleid

‎function/doi/doifreeze.sql‎

Lines changed: 2 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -9,12 +9,11 @@ WITH chronmeta AS (
99
GROUP BY datasetid
1010
),
1111
ids AS (
12-
SELECT * FROM UNNEST(dsid) AS dsid
12+
SELECT * FROM UNNEST(dsid) AS dsid
1313
),
1414
datameta AS (
15-
SELECT * FROM doi.ndbdata(dsid)
15+
SELECT * FROM doi.ndbdata(dsid)
1616
)
17-
1817
SELECT ids.dsid AS datasetid,
1918
json_strip_nulls(json_build_object('chronologies', jsonb_build_object('chronologies', chr.chronologies),
2019
'data', dt.data)) AS record

‎function/indexes/addingIndices.sql‎

Lines changed: 7 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -2,20 +2,19 @@ CREATE INDEX IF NOT EXISTS taxonames_idx ON ndb.taxa USING GIN(taxonname g
22
CREATE INDEX IF NOT EXISTS sitenames_idx ON ndb.sites USING GIN(sitename gin_trgm_ops);
33
CREATE INDEX IF NOT EXISTS familynames_idx ON ndb.contacts USING GIN(familyname gin_trgm_ops);
44

5-
CREATE INDEX IF NOT EXISTS sitegeog_gix ON ndb.sites USING GIST (geog);
5+
CREATE INDEX IF NOT EXISTS sitegeog_gix ON ndb.sites USING GIST (geog);
66

77
CREATE INDEX IF NOT EXISTS chryoungage_idx ON ndb.chronologies USING btree(ageboundyounger);
8-
CREATE INDEX IF NOT EXISTS chroldage_idx ON ndb.chronologies USING btree(ageboundolder);
8+
CREATE INDEX IF NOT EXISTS chroldage_idx ON ndb.chronologies USING btree(ageboundolder);
99

1010
CREATE INDEX IF NOT EXISTS ccrchronage_idx ON ndb.chroncontrols USING btree(age);
11-
CREATE INDEX IF NOT EXISTS ccroldage_idx ON ndb.chroncontrols USING btree(agelimitolder);
11+
CREATE INDEX IF NOT EXISTS ccroldage_idx ON ndb.chroncontrols USING btree(agelimitolder);
1212
CREATE INDEX IF NOT EXISTS ccryoungage_idx ON ndb.chroncontrols USING btree(agelimityounger);
1313

14-
CREATE INDEX IF NOT EXISTS smpage_idx ON ndb.sampleages USING btree(age);
15-
CREATE INDEX IF NOT EXISTS smpageold_idx ON ndb.sampleages USING btree(ageolder);
14+
CREATE INDEX IF NOT EXISTS smpage_idx ON ndb.sampleages USING btree(age);
15+
CREATE INDEX IF NOT EXISTS smpageold_idx ON ndb.sampleages USING btree(ageolder);
1616
CREATE INDEX IF NOT EXISTS smpageyoung_idx ON ndb.sampleages USING btree(ageyounger);
1717

18-
CREATE INDEX IF NOT EXISTS geoage_idx ON ndb.geochronology USING btree(age);
18+
CREATE INDEX IF NOT EXISTS geoage_idx ON ndb.geochronology USING btree(age);
1919

20-
ALTER TABLE ndb.eventchronology DROP CONSTRAINT IF EXISTS uniqueeventset;
21-
ALTER TABLE ndb.eventchronology ADD CONSTRAINT uniqueeventset UNIQUE(analysisunitid,eventid,chroncontrolid);
20+
CREATE INDEX IF NOT EXISTS variableel ON ndb.variables USING btree(taxonid, variableelementid, variableunitsid);

0 commit comments

Comments
 (0)