Skip to content

Commit 9452448

Browse files
SimonGoringSimonGoring
committed
Big pull from neotomadev to help fix the Tilia functions.
1 parent b404193 commit 9452448

File tree

171 files changed

+1022
-1178
lines changed

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

171 files changed

+1022
-1178
lines changed

‎connect_remote.py‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -70,7 +70,7 @@
7070
if args.isDev:
7171
data['database'] = data['database'] + 'dev'
7272

73-
print("Writing to the " + data['database'] + ' Neotoma server.')
73+
print("Using the " + data['database'] + ' Neotoma server.')
7474

7575
conn = psycopg2.connect(**data)
7676

‎function/ap/getpeople.sql‎

Lines changed: 15 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -1,19 +1,19 @@
11
CREATE OR REPLACE FUNCTION ap.getpeople()
22
RETURNS TABLE(contactid integer, contactname character varying)
33
LANGUAGE sql
4-
AS $function$
5-
WITH auth_pi AS (
6-
(SELECT DISTINCT dpi.contactid, c.contactname
7-
FROM ndb.datasetpis AS dpi INNER JOIN
8-
ndb.contacts AS c ON c.contactid = dpi.contactid)
9-
UNION
10-
(SELECT DISTINCT pa.contactid, c.contactname
11-
FROM ndb.publicationauthors AS pa INNER JOIN
12-
ndb.contacts AS c ON c.contactid = pa.contactid)
13-
), alia AS (
14-
SELECT c.contactid, c.contactname
15-
FROM ndb.contacts AS c
16-
WHERE c.contactid != c.aliasid AND c.aliasid > 0 AND c.aliasid IN (SELECT contactid FROM auth_pi)
17-
)
18-
SELECT * FROM (SELECT * FROM auth_pi) AS q UNION (SELECT * FROM alia);
4+
AS $function$
5+
WITH auth_pi AS (
6+
(SELECT DISTINCT dpi.contactid, c.contactname
7+
FROM ndb.datasetpis AS dpi INNER JOIN
8+
ndb.contacts AS c ON c.contactid = dpi.contactid)
9+
UNION
10+
(SELECT DISTINCT pa.contactid, c.contactname
11+
FROM ndb.publicationauthors AS pa INNER JOIN
12+
ndb.contacts AS c ON c.contactid = pa.contactid)
13+
), alia AS (
14+
SELECT c.contactid, c.contactname
15+
FROM ndb.contacts AS c
16+
WHERE c.contactid != c.aliasid AND c.aliasid > 0 AND c.aliasid IN (SELECT contactid FROM auth_pi)
17+
)
18+
SELECT * FROM (SELECT * FROM auth_pi) AS q UNION (SELECT * FROM alia);
1919
$function$

‎function/ndb/contacts_delete_fn.sql‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -12,7 +12,7 @@ AS $function$
1212

1313
update ndb.constituentdatabases
1414
set contactid = null
15-
where contactid = OLD.contactid;
15+
where contactid = OLD.contactid ;
1616

1717
delete from ndb.dataprocessors
1818
where contactid = OLD.contactid;

‎function/ndb/dssampdata.sql‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,9 +1,9 @@
11
CREATE OR REPLACE VIEW ndb.dssampdata AS
22
SELECT ds.datasetid,
33
count(DISTINCT smp.sampleid) AS samples,
4-
count(DISTINCT dt.dataid) AS observations
4+
count(DISTINCT dt.dataid) AS observations
55
FROM ndb.datasets AS ds
6-
JOIN ndb.samples AS smp ON smp.datasetid = ds.datasetid
7-
JOIN ndb.data AS dt ON dt.sampleid = smp.sampleid
6+
LEFT OUTER JOIN ndb.samples AS smp ON smp.datasetid = ds.datasetid
7+
LEFT OUTER JOIN ndb.data AS dt ON dt.sampleid = smp.sampleid
88
GROUP BY ds.datasetid;
99
GRANT SELECT ON ndb.dssampdata TO neotomawsreader;

‎function/ndb/rawbymonth.sql‎

Lines changed: 31 additions & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -4,35 +4,49 @@ RETURNS TABLE (datasets bigint,
44
sites bigint,
55
publications bigint,
66
authors bigint,
7-
observations bigint)
7+
countrygpid bigint,
8+
observations numeric)
89
AS
910
$function$
1011
WITH rsum AS (
1112
SELECT ds.datasetid,
12-
dsl.siteid,
13-
array_agg(DISTINCT dsp.publicationid) AS publications,
14-
array_agg(DISTINCT pua.contactid) AS authors,
15-
gpd.path[1] AS countrygpid,
16-
smp.observations
13+
dsl.siteid,
14+
array_agg(DISTINCT dsp.publicationid) AS publications,
15+
array_agg(DISTINCT pua.contactid) AS authors,
16+
gpd.path[1] AS countrygpid
1717
FROM ndb.datasets AS ds
1818
JOIN ndb.datasetsubmissions AS dss on dss.datasetid = ds.datasetid
1919
JOIN ndb.dslinks AS dsl ON dsl.datasetid = ds.datasetid
20-
JOIN ndb.dssampdata AS smp ON ds.datasetid = smp.datasetid
2120
LEFT OUTER JOIN ndb.datasetpublications AS dsp ON dsp.datasetid = ds.datasetid
2221
LEFT OUTER JOIN ndb.publicationauthors AS pua ON dsp.publicationid = pua.publicationid
2322
JOIN ndb.sitegeopolitical AS sgp ON dsl.siteid = sgp.siteid
2423
JOIN ndb.geopoldepth AS gpd ON gpd.geopoliticalid = sgp.geopoliticalid
2524
WHERE
2625
EXTRACT(year from AGE(NOW(), dss.submissiondate))*12 +
2726
EXTRACT(month from AGE(NOW(), dss.submissiondate)) BETWEEN startperiod and endperiod
28-
GROUP BY ds.datasetid, dsl.siteid, dsl.siteid, gpd.path[1], smp.observations)
29-
SELECT COUNT(DISTINCT datasetid) AS datasets,
30-
COUNT(DISTINCT siteid) AS sites,
31-
COUNT(DISTINCT unpublications) AS publications,
32-
COUNT(DISTINCT unauthors) AS authors,
33-
COUNT(DISTINCT countrygpid) AS countries,
34-
SUM(observations) AS observations
35-
FROM rsum,
36-
unnest(authors) AS unauthors,
37-
unnest(publications) AS unpublications
27+
GROUP BY ds.datasetid, dsl.siteid, gpd.path[1]),
28+
shortsum AS (
29+
SELECT COUNT(DISTINCT datasetid) AS datasets,
30+
COUNT(DISTINCT siteid) AS sites,
31+
COUNT(DISTINCT unpublications) AS publications,
32+
COUNT(DISTINCT unauthors) AS authors,
33+
COUNT(DISTINCT countrygpid) AS countries
34+
FROM rsum,
35+
unnest(authors) AS unauthors,
36+
unnest(publications) AS unpublications),
37+
obssum AS (
38+
SELECT ds.datasetid, MAX(dsm.observations) AS observations
39+
FROM ndb.datasets AS ds
40+
JOIN ndb.datasetsubmissions AS dss on dss.datasetid = ds.datasetid
41+
JOIN ndb.dssampdata AS dsm ON dsm.datasetid = ds.datasetid
42+
WHERE
43+
EXTRACT(year from AGE(NOW(), dss.submissiondate))*12 +
44+
EXTRACT(month from AGE(NOW(), dss.submissiondate))
45+
BETWEEN startperiod and endperiod
46+
GROUP BY ds.datasetid
47+
)
48+
SELECT *,
49+
(SELECT SUM(observations) FROM obssum) AS observations
50+
FROM shortsum
51+
3852
$function$ LANGUAGE SQL;

‎function/ti/getaliascontactnames.sql‎

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -1,9 +1,9 @@
11
CREATE OR REPLACE FUNCTION ti.getaliascontactnames()
22
RETURNS TABLE(aliascontactid integer, contactname character varying, currentcontactid integer, currentcontactname character varying)
33
LANGUAGE sql
4-
AS $function$
5-
SELECT contacts_1.contactid AS aliascontactid, ndb.contacts.contactname AS aliascontactname, ndb.contacts.contactid AS currentcontactid,
6-
contacts_1.contactname AS currentcontactname
7-
FROM ndb.contacts INNER JOIN ndb.contacts AS contacts_1 ON ndb.contacts.aliasid = contacts_1.contactid
8-
WHERE (ndb.contacts.aliasid <> ndb.contacts.contactid);
4+
AS $function$
5+
SELECT contacts_1.contactid AS aliascontactid, ndb.contacts.contactname AS aliascontactname, ndb.contacts.contactid AS currentcontactid,
6+
contacts_1.contactname AS currentcontactname
7+
FROM ndb.contacts INNER JOIN ndb.contacts AS contacts_1 ON ndb.contacts.aliasid = contacts_1.contactid
8+
WHERE (ndb.contacts.aliasid <> ndb.contacts.contactid);
99
$function$

‎function/ti/getbiochemdatasetbyid.sql‎

Lines changed: 24 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -1,28 +1,28 @@
11
CREATE OR REPLACE FUNCTION ti.getbiochemdatasetbyid(_datasetid integer)
22
RETURNS TABLE(sampleid integer, analysisunitname character varying, samplename character varying, sampledate character varying, taxonname character varying, variable character varying, variableelement character varying, variableunits character varying, value double precision)
33
LANGUAGE plpgsql
4-
AS $function$
5-
DECLARE
6-
dstypeid int := (SELECT datasettypeid FROM ndb.datasets WHERE datasetid = _datasetid);
7-
BEGIN
8-
IF dstypeid = 27
9-
THEN
10-
RETURN QUERY
11-
SELECT ndb.data.sampleid, ndb.analysisunits.analysisunitname, ndb.samples.samplename, ndb.samples.sampledate::varchar(10) as sampledate,
12-
ndb.taxa.taxonname, taxa_1.taxonname as variable, ndb.variableelements.variableelement, ndb.variableunits.variableunits, ndb.data.value
13-
FROM ndb.samples INNER JOIN
14-
ndb.taxa ON ndb.samples.taxonid = ndb.taxa.taxonid INNER JOIN
15-
ndb.analysisunits ON ndb.samples.analysisunitid = ndb.analysisunits.analysisunitid INNER JOIN
16-
ndb.data ON ndb.samples.sampleid = ndb.data.sampleid INNER JOIN
17-
ndb.variables ON ndb.data.variableid = ndb.variables.variableid INNER JOIN
18-
ndb.taxa AS taxa_1 ON ndb.variables.taxonid = taxa_1.taxonid LEFT OUTER JOIN
19-
ndb.variableunits ON ndb.variables.variableunitsid = ndb.variableunits.variableunitsid LEFT OUTER JOIN
20-
ndb.variableelements ON ndb.variables.variableelementid = ndb.variableelements.variableelementid
21-
WHERE ndb.samples.datasetid = _datasetid
22-
ORDER BY ndb.data.sampleid;
23-
ELSE
24-
RAISE NOTICE 'dataset is not biochemistry';
25-
END IF;
26-
27-
END;
4+
AS $function$
5+
DECLARE
6+
dstypeid int := (SELECT datasettypeid FROM ndb.datasets WHERE datasetid = _datasetid);
7+
BEGIN
8+
IF dstypeid = 27
9+
THEN
10+
RETURN QUERY
11+
SELECT ndb.data.sampleid, ndb.analysisunits.analysisunitname, ndb.samples.samplename, ndb.samples.sampledate::varchar(10) as sampledate,
12+
ndb.taxa.taxonname, taxa_1.taxonname as variable, ndb.variableelements.variableelement, ndb.variableunits.variableunits, ndb.data.value
13+
FROM ndb.samples INNER JOIN
14+
ndb.taxa ON ndb.samples.taxonid = ndb.taxa.taxonid INNER JOIN
15+
ndb.analysisunits ON ndb.samples.analysisunitid = ndb.analysisunits.analysisunitid INNER JOIN
16+
ndb.data ON ndb.samples.sampleid = ndb.data.sampleid INNER JOIN
17+
ndb.variables ON ndb.data.variableid = ndb.variables.variableid INNER JOIN
18+
ndb.taxa AS taxa_1 ON ndb.variables.taxonid = taxa_1.taxonid LEFT OUTER JOIN
19+
ndb.variableunits ON ndb.variables.variableunitsid = ndb.variableunits.variableunitsid LEFT OUTER JOIN
20+
ndb.variableelements ON ndb.variables.variableelementid = ndb.variableelements.variableelementid
21+
WHERE ndb.samples.datasetid = _datasetid
22+
ORDER BY ndb.data.sampleid;
23+
ELSE
24+
RAISE NOTICE 'dataset is not biochemistry';
25+
END IF;
26+
27+
END;
2828
$function$

‎function/ti/getchildtaxa.sql‎

Lines changed: 18 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -1,22 +1,22 @@
11
CREATE OR REPLACE FUNCTION ti.getchildtaxa(taxonname character varying)
22
RETURNS TABLE(taxonid integer, taxonname character varying, author character varying, highertaxonid integer, level integer)
33
LANGUAGE sql
4-
AS $function$
5-
WITH RECURSIVE taxacte
6-
AS
7-
(
8-
SELECT taxonid AS basetaxonid, taxonid, taxonname, author, highertaxonid, 0 AS level
9-
FROM ndb.taxa
10-
WHERE taxonname = $1
11-
12-
UNION ALL
13-
14-
SELECT parent.basetaxonid, child.taxonid, child.taxonname, child.author, child.highertaxonid, parent.level +1 AS level
15-
FROM taxacte AS parent
16-
INNER JOIN ndb.taxa AS child
17-
ON parent.taxonid = child.highertaxonid
18-
)
19-
SELECT taxonid, taxonname, author, highertaxonid, level
20-
FROM taxacte
21-
ORDER BY level
4+
AS $function$
5+
WITH RECURSIVE taxacte
6+
AS
7+
(
8+
SELECT taxonid AS basetaxonid, taxonid, taxonname, author, highertaxonid, 0 AS level
9+
FROM ndb.taxa
10+
WHERE taxonname = $1
11+
12+
UNION ALL
13+
14+
SELECT parent.basetaxonid, child.taxonid, child.taxonname, child.author, child.highertaxonid, parent.level +1 AS level
15+
FROM taxacte AS parent
16+
INNER JOIN ndb.taxa AS child
17+
ON parent.taxonid = child.highertaxonid
18+
)
19+
SELECT taxonid, taxonname, author, highertaxonid, level
20+
FROM taxacte
21+
ORDER BY level
2222
$function$

‎function/ti/getchroncontroltypehighestid.sql‎

Lines changed: 14 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -1,18 +1,18 @@
11
CREATE OR REPLACE FUNCTION ti.getchroncontroltypehighestid(_chroncontroltypeid integer)
22
RETURNS TABLE(chroncontroltypeid integer, chroncontroltype character varying, higherchroncontroltypeid integer)
33
LANGUAGE plpgsql
4-
AS $function$
5-
DECLARE
6-
id int := _chroncontroltypeid;
7-
higherid int := (SELECT ndb.chroncontroltypes.higherchroncontroltypeid FROM ndb.chroncontroltypes WHERE ndb.chroncontroltypes.chroncontroltypeid = id);
8-
BEGIN
9-
WHILE id <> higherid LOOP
10-
id := higherid;
11-
higherid := (SELECT ndb.chroncontroltypes.higherchroncontroltypeid FROM ndb.chroncontroltypes WHERE ndb.chroncontroltypes.chroncontroltypeid = id);
12-
RETURN QUERY
13-
SELECT ndb.chroncontroltypes.chroncontroltypeid, ndb.chroncontroltypes.chroncontroltype, ndb.chroncontroltypes.higherchroncontroltypeid
14-
FROM ndb.chroncontroltypes
15-
WHERE ndb.chroncontroltypes.chroncontroltypeid = id;
16-
END LOOP;
17-
END;
4+
AS $function$
5+
DECLARE
6+
id int := _chroncontroltypeid;
7+
higherid int := (SELECT ndb.chroncontroltypes.higherchroncontroltypeid FROM ndb.chroncontroltypes WHERE ndb.chroncontroltypes.chroncontroltypeid = id);
8+
BEGIN
9+
WHILE id <> higherid LOOP
10+
id := higherid;
11+
higherid := (SELECT ndb.chroncontroltypes.higherchroncontroltypeid FROM ndb.chroncontroltypes WHERE ndb.chroncontroltypes.chroncontroltypeid = id);
12+
RETURN QUERY
13+
SELECT ndb.chroncontroltypes.chroncontroltypeid, ndb.chroncontroltypes.chroncontroltype, ndb.chroncontroltypes.higherchroncontroltypeid
14+
FROM ndb.chroncontroltypes
15+
WHERE ndb.chroncontroltypes.chroncontroltypeid = id;
16+
END LOOP;
17+
END;
1818
$function$

‎function/ti/getchronocontrolsbychronologyid.sql‎

Lines changed: 13 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -1,17 +1,17 @@
11
CREATE OR REPLACE FUNCTION ti.getchronocontrolsbychronologyid(cronid integer)
22
RETURNS TABLE(chroncontrolid integer, chroncontroltypeid integer, chroncontroltype character varying, depth double precision, thickness double precision, analysisunitid integer, analysisunitname character varying, age double precision, agelimityounger double precision, agelimitolder double precision, notes text, calibrationcurve character varying, calibrationprogram character varying, version character varying)
33
LANGUAGE sql
4-
AS $function$
5-
SELECT ndb.chroncontrols.chroncontrolid, ndb.chroncontrols.chroncontroltypeid, ndb.chroncontroltypes.chroncontroltype, ndb.chroncontrols.depth,
6-
ndb.chroncontrols.thickness, ndb.chroncontrols.analysisunitid, ndb.analysisunits.analysisunitname, ndb.chroncontrols.age,
7-
ndb.chroncontrols.agelimityounger, ndb.chroncontrols.agelimitolder, ndb.chroncontrols.notes, ndb.calibrationcurves.calibrationcurve,
8-
ndb.calibrationprograms.calibrationprogram, ndb.calibrationprograms.version
9-
FROM ndb.calibrationprograms INNER JOIN
10-
ndb.chroncontrolscal14c ON ndb.calibrationprograms.calibrationprogramid = ndb.chroncontrolscal14c.calibrationprogramid INNER JOIN
11-
ndb.calibrationcurves ON ndb.chroncontrolscal14c.calibrationcurveid = ndb.calibrationcurves.calibrationcurveid RIGHT OUTER JOIN
12-
ndb.chroncontrols INNER JOIN
13-
ndb.chroncontroltypes ON ndb.chroncontrols.chroncontroltypeid = ndb.chroncontroltypes.chroncontroltypeid ON
14-
ndb.chroncontrolscal14c.chroncontrolid = ndb.chroncontrols.chroncontrolid LEFT OUTER JOIN
15-
ndb.analysisunits ON ndb.chroncontrols.analysisunitid = ndb.analysisunits.analysisunitid
16-
WHERE ndb.chroncontrols.chronologyid = cronid;
4+
AS $function$
5+
SELECT ndb.chroncontrols.chroncontrolid, ndb.chroncontrols.chroncontroltypeid, ndb.chroncontroltypes.chroncontroltype, ndb.chroncontrols.depth,
6+
ndb.chroncontrols.thickness, ndb.chroncontrols.analysisunitid, ndb.analysisunits.analysisunitname, ndb.chroncontrols.age,
7+
ndb.chroncontrols.agelimityounger, ndb.chroncontrols.agelimitolder, ndb.chroncontrols.notes, ndb.calibrationcurves.calibrationcurve,
8+
ndb.calibrationprograms.calibrationprogram, ndb.calibrationprograms.version
9+
FROM ndb.calibrationprograms INNER JOIN
10+
ndb.chroncontrolscal14c ON ndb.calibrationprograms.calibrationprogramid = ndb.chroncontrolscal14c.calibrationprogramid INNER JOIN
11+
ndb.calibrationcurves ON ndb.chroncontrolscal14c.calibrationcurveid = ndb.calibrationcurves.calibrationcurveid RIGHT OUTER JOIN
12+
ndb.chroncontrols INNER JOIN
13+
ndb.chroncontroltypes ON ndb.chroncontrols.chroncontroltypeid = ndb.chroncontroltypes.chroncontroltypeid ON
14+
ndb.chroncontrolscal14c.chroncontrolid = ndb.chroncontrols.chroncontrolid LEFT OUTER JOIN
15+
ndb.analysisunits ON ndb.chroncontrols.analysisunitid = ndb.analysisunits.analysisunitid
16+
WHERE ndb.chroncontrols.chronologyid = cronid;
1717
$function$

0 commit comments

Comments
 (0)