1212
SQL Formatting. · NeotomaDB/Neotoma_SQL@dbf9e79 · GitHub
Skip to content

Commit dbf9e79

Browse files
SimonGoringSimonGoring
committed
SQL Formatting.
1 parent 683ec1d commit dbf9e79

File tree

76 files changed

+325
-480
lines changed

Some content is hidden

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

76 files changed

+325
-480
lines changed

‎connect_remote.py‎

Lines changed: 64 additions & 28 deletions
Original file line numberDiff line numberDiff line change
@@ -35,12 +35,21 @@
3535
import git
3636
import sys
3737

38-
parser = argparse.ArgumentParser(description='Check Neotoma SQL functions against functions in the online database servers (`neotoma` and `neotomadev`).')
39-
40-
parser.add_argument('-dev', dest='isDev', default = False, help = 'Use the `dev` database? (`False` without the flag)', action = 'store_true')
41-
parser.add_argument('-push', dest='isPush', default = False, help = 'Assume that SQL functions in the repository are newer, push to the db server.', action = 'store_true')
42-
parser.add_argument('-g', dest='pullGit', nargs = '?', type = str, default = None, help = 'Pull from the remote git server before running?.')
43-
parser.add_argument('-tilia', dest='isTilia', default = False, help = 'Use the `dev` database? (`False` without the flag)', action = 'store_true')
38+
parser = argparse.ArgumentParser(
39+
description='Check Neotoma SQL functions against functions in the '
40+
+ 'online database servers (`neotoma` and `neotomadev`).')
41+
42+
parser.add_argument('-dev', dest='isDev', default=False,
43+
help='Use the `dev` database? (`False` without the flag)',
44+
action='store_true')
45+
parser.add_argument('-push', dest='isPush', default=False,
46+
help='Assume that SQL functions in the repository are '
47+
+ 'newer, push to the db server.', action='store_true')
48+
parser.add_argument('-g', dest='pullGit', nargs='?', type=str, default=None,
49+
help='Pull from the remote git server before running?.')
50+
parser.add_argument('-tilia', dest='isTilia', default=False,
51+
help='Use the `dev` database? (`False` without the flag)',
52+
action='store_true')
4453

4554
args = parser.parse_args()
4655

@@ -54,7 +63,8 @@
5463
break
5564

5665
if good is False:
57-
print("The connect_remote.json file is not in your .gitignore file. Please add it!")
66+
print("The connect_remote.json file is not in your .gitignore file. "
67+
+ "Please add it!")
5868

5969
with open('connect_remote.json') as f:
6070
data = json.load(f)
@@ -64,7 +74,8 @@
6474
try:
6575
repo.heads[args.pullGit].checkout()
6676
except git.exc.GitCommandError:
67-
sys.exit("Stash or commit changes in the current branch before switching to " + args.pullGit + ".")
77+
sys.exit("Stash or commit changes in the current branch before "
78+
+ "switching to " + args.pullGit + ".")
6879

6980
repo.remotes.origin.pull()
7081

@@ -76,7 +87,7 @@
7687

7788
print("Using the " + data['database'] + ' Neotoma server.')
7889

79-
conn = psycopg2.connect(**data)
90+
conn = psycopg2.connect(**data, connect_timeout=5)
8091

8192
cur = conn.cursor()
8293

@@ -91,7 +102,8 @@
91102
FROM pg_catalog.pg_proc AS f
92103
INNER JOIN pg_catalog.pg_namespace AS n ON f.pronamespace = n.oid
93104
WHERE
94-
n.nspname IN ('ti','ndb','ts', 'mca', 'ecg', 'ap', 'da', 'emb', 'gen', 'doi')
105+
n.nspname IN ('ti','ndb','ts', 'mca', 'ecg', 'ap',
106+
'da', 'emb', 'gen', 'doi')
95107
ORDER BY n.nspname, proname""")
96108

97109
# For each sql function in the named namespaces go in and write out the actual
@@ -105,6 +117,7 @@
105117
z = 0
106118

107119
for record in cur:
120+
print(record[1])
108121
# This checks each function in the database and then tests whether there
109122
# is a file associated with it.
110123
newFile = "./function/" + record[0] + "/" + record[1] + ".sql"
@@ -124,38 +137,55 @@
124137
file = open(newFile)
125138
textCheck = copy.deepcopy(file.read())
126139
serverFun = copy.deepcopy(record[3])
127-
textCheck = re.sub('[\s+\t+\n+\r+]','', textCheck)
128-
serverFun = re.sub('[\s+\t+\n+\r+]','', serverFun)
140+
textCheck = re.sub('[\s+\t+\n+\r+]', '', textCheck)
141+
serverFun = re.sub('[\s+\t+\n+\r+]', '', serverFun)
129142
match = serverFun == textCheck
130-
# Pushing (to the db) and pulling (from the db) are defined by the user.
131-
if match == False:
132-
if args.isPush == False:
133-
print('The function ' + record[0] + '.' + record[1] + ' differs between the database and your local copy.\n *' + newFile + ' will be written locally.')
143+
# Pushing (to the db) and pulling (from the db) are defined by the user
144+
if match is False:
145+
if args.isPush is False:
146+
print('The function ' + record[0] + '.' + record[1]
147+
+ ' differs between the database and your local copy.\n*'
148+
+ newFile + ' will be written locally.')
134149
file = open(newFile, 'w')
135150
file.write(record[3])
136151
file.close()
137-
print('The file for ' + record[0] + '.' + record[1] + ' has been updated in the repository.')
152+
print('The file for ' + record[0] + '.' + record[1]
153+
+ ' has been updated in the repository.')
138154
else:
139155
cur2 = conn.cursor()
140156
try:
141-
cur2.execute("DROP FUNCTION " + record[0] + "." + record[1] + "(" + record[2] + ");")
157+
cur2.execute("DROP FUNCTION " + record[0] + "." + record[1]
158+
+ "(" + record[2] + ");")
142159
conn.commit()
143-
except:
160+
print("Dropped function.")
161+
except Exception as e:
162+
print(e)
144163
conn.rollback()
145164
print("Could not delete " + record[0] + "." + record[1])
146165
failed.add(record[0] + "." + record[1])
147166

148167
try:
149-
cur2.execute(open("./function/" + record[0] + "/" + record[1] + ".sql", "r").read())
168+
print("trying to execute")
169+
cur2 = conn.cursor()
170+
cur2.execute(
171+
open("./function/" + record[0] + "/" + record[1]
172+
+ ".sql", "r").read())
150173
conn.commit()
151-
print('The function for ' + record[0] + '.' + record[1] + ' has been updated in the `' + data['database'] + '` database.')
174+
print("executed")
152175
cur2.execute("REASSIGN OWNED BY sug335 TO functionwriter;")
153176
conn.commit()
177+
print("reassigned")
154178
rewrite.add(record[0] + "." + record[1])
179+
print('The function for ' + record[0] + '.' + record[1]
180+
+ ' has been updated in the `' + data['database']
181+
+ '` database.')
155182
z = z + 1
156-
except:
183+
except Exception as e:
157184
conn.rollback()
158-
print('The function for ' + record[0] + '.' + record[1] + ' has not been updated in the `' + data['database'] + '` database.')
185+
print(e)
186+
print('The function for ' + record[0] + '.' + record[1]
187+
+ ' has not been updated in the `' + data['database']
188+
+ '` database.')
159189
failed.add(record[0] + "." + record[1])
160190

161191
for schema in ['ti', 'ts', 'doi', 'ap', 'ndb']:
@@ -175,15 +205,17 @@
175205
print(data)
176206
try:
177207
cur.execute(SQL, data)
178-
except:
208+
except Exception as e:
179209
conn.rollback()
180210
print("Failed to run. " + schema)
211+
print(e)
181212

182213
if cur.rowcount == 0:
183214
# Execute the new script if there is one. Needs the commit.
184215
print("Executing " + schema + "." + functs.split(".")[0])
185216
try:
186-
cur.execute(open("./function/" + schema + "/" + functs, "r").read())
217+
cur.execute(open("./function/" + schema
218+
+ "/" + functs, "r").read())
187219
conn.commit()
188220
cur2.execute("REASSIGN OWNED BY sug335 TO functionwriter;")
189221
conn.commit()
@@ -196,9 +228,13 @@
196228
failed.add(schema + "." + functs.split(".")[0])
197229
z = z + 1
198230
if cur.rowcount > 1:
199-
# TODO: Need to add a script to check that the definitions are the same.
200-
print(schema + "." + functs.split(".")[0] + " has " +
201-
str(cur.rowcount) + " definitions.")
231+
# TODO: Need to add a script to check that the definitions are
232+
# the same.
233+
print(schema + "." + functs.split(".")[0] + " has "
234+
+ str(cur.rowcount) + " definitions.")
235+
236+
conn.close()
237+
202238

203239
print("The script has rewritten:")
204240

‎function/ap/explorersearch.sql‎

Lines changed: 4 additions & 33 deletions
Original file line numberDiff line numberDiff line change
@@ -1,35 +1,6 @@
1-
-- FUNCTION: ap.explorersearch(integer[], integer[], integer[], integer[], integer, integer, integer, character varying, integer, integer, integer, integer, integer, character varying, integer, integer, integer, boolean, boolean, date, boolean)
2-
3-
-- DROP FUNCTION ap.explorersearch(integer[], integer[], integer[], integer[], integer, integer, integer, character varying, integer, integer, integer, integer, integer, character varying, integer, integer, integer, boolean, boolean, date, boolean);
4-
5-
CREATE OR REPLACE FUNCTION ap.explorersearch(
6-
_taxonids integer[] DEFAULT NULL::integer[],
7-
_elemtypeids integer[] DEFAULT NULL::integer[],
8-
_taphtypeids integer[] DEFAULT NULL::integer[],
9-
_depenvids integer[] DEFAULT NULL::integer[],
10-
_abundpct integer DEFAULT NULL::integer,
11-
_datasettypeid integer DEFAULT NULL::integer,
12-
_keywordid integer DEFAULT NULL::integer,
13-
_coords character varying DEFAULT NULL::character varying,
14-
_gpid integer DEFAULT NULL::integer,
15-
_altmin integer DEFAULT NULL::integer,
16-
_altmax integer DEFAULT NULL::integer,
17-
_coltypeid integer DEFAULT NULL::integer,
18-
_dbid integer DEFAULT NULL::integer,
19-
_sitename character varying DEFAULT NULL::character varying,
20-
_contactid integer DEFAULT NULL::integer,
21-
_ageold integer DEFAULT NULL::integer,
22-
_ageyoung integer DEFAULT NULL::integer,
23-
_agedocontain boolean DEFAULT true,
24-
_agedirectdate boolean DEFAULT false,
25-
_subdate date DEFAULT NULL::date,
26-
_debug boolean DEFAULT false)
27-
RETURNS TABLE(datasetid integer, datasettype character varying, databasename character varying, minage integer, maxage integer, ageyoungest integer, ageoldest integer, siteid integer, sitename character varying, sitedescription text, notes text, collunithandle character varying, collunitname character varying, latitudenorth double precision, latitudesouth double precision, longitudeeast double precision, longitudewest double precision)
28-
LANGUAGE 'plpgsql'
29-
30-
COST 100
31-
VOLATILE
32-
ROWS 1000
1+
CREATE OR REPLACE FUNCTION ap.explorersearch(_taxonids integer[] DEFAULT NULL::integer[], _elemtypeids integer[] DEFAULT NULL::integer[], _taphtypeids integer[] DEFAULT NULL::integer[], _depenvids integer[] DEFAULT NULL::integer[], _abundpct integer DEFAULT NULL::integer, _datasettypeid integer DEFAULT NULL::integer, _keywordid integer DEFAULT NULL::integer, _coords character varying DEFAULT NULL::character varying, _gpid integer DEFAULT NULL::integer, _altmin integer DEFAULT NULL::integer, _altmax integer DEFAULT NULL::integer, _coltypeid integer DEFAULT NULL::integer, _dbid integer DEFAULT NULL::integer, _sitename character varying DEFAULT NULL::character varying, _contactid integer DEFAULT NULL::integer, _ageold integer DEFAULT NULL::integer, _ageyoung integer DEFAULT NULL::integer, _agedocontain boolean DEFAULT true, _agedirectdate boolean DEFAULT false, _subdate date DEFAULT NULL::date, _debug boolean DEFAULT false)
2+
RETURNS TABLE(datasetid integer, datasettype character varying, databasename character varying, minage integer, maxage integer, ageyoungest integer, ageoldest integer, siteid integer, sitename character varying, sitedescription text, notes text, collunithandle character varying, collunitname character varying, latitudenorth double precision, latitudesouth double precision, longitudeeast double precision, longitudewest double precision)
3+
LANGUAGE plpgsql
334
AS $function$
345

356
DECLARE thesql varchar;
@@ -492,4 +463,4 @@ RAISE NOTICE '%', thesql;
492463

493464
END;
494465

495-
$function$;
466+
$function$

‎function/doi/agerange.sql‎

Lines changed: 0 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -1,18 +1,3 @@
1-
CREATE OR REPLACE FUNCTION doi.agerange(dsid integer)
2-
RETURNS TABLE(datasetid integer, ages json)
3-
LANGUAGE sql
4-
AS $function$
5-
SELECT dts.datasetid, json_agg(jsonb_build_object('ageyoung', agerange.younger,
6-
'ageold', agerange.older,
7-
'units', agetypes.agetype)) AS agerange
8-
FROM
9-
ndb.datasets AS dts
10-
LEFT OUTER JOIN ndb.dsageranges AS agerange ON dts.datasetid = agerange.datasetid
11-
LEFT OUTER JOIN ndb.agetypes AS agetypes ON agetypes.agetypeid = agerange.agetypeid
12-
WHERE dts.datasetid = dsid
13-
GROUP BY dts.datasetid;
14-
$function$;
15-
161
CREATE OR REPLACE FUNCTION doi.agerange(dsid integer[])
172
RETURNS TABLE(datasetid integer, ages json)
183
LANGUAGE sql

‎function/doi/datasetauthors.sql‎

Lines changed: 1 addition & 17 deletions
Original file line numberDiff line numberDiff line change
@@ -1,20 +1,4 @@
11
CREATE OR REPLACE FUNCTION doi.datasetauthors(dsid integer)
2-
RETURNS TABLE(datasetid integer, authors jsonb)
3-
LANGUAGE sql
4-
AS $function$
5-
SELECT dst.datasetid, jsonb_agg(jsonb_build_object('contactid', cnt.contactid,
6-
'contactname', cnt.contactname,
7-
'familyname', cnt.familyname,
8-
'firstname', cnt.givennames,
9-
'initials', cnt.leadinginitials)) AS authors
10-
FROM ndb.datasets AS dst
11-
JOIN ndb.datasetpis AS dspi ON dspi.datasetid = dst.datasetid LEFT OUTER JOIN
12-
ndb.contacts AS cnt ON cnt.contactid = dspi.contactid
13-
WHERE dst.datasetid = dsid
14-
GROUP BY dst.datasetid;
15-
$function$;
16-
17-
CREATE OR REPLACE FUNCTION doi.datasetauthors(dsid integer[])
182
RETURNS TABLE(datasetid integer, authors jsonb)
193
LANGUAGE sql
204
AS $function$
@@ -26,6 +10,6 @@ AS $function$
2610
FROM ndb.datasets AS dst
2711
JOIN ndb.datasetpis AS dspi ON dspi.datasetid = dst.datasetid LEFT OUTER JOIN
2812
ndb.contacts AS cnt ON cnt.contactid = dspi.contactid
29-
WHERE dst.datasetid = ANY(dsid)
13+
WHERE dst.datasetid = dsid
3014
GROUP BY dst.datasetid;
3115
$function$

‎function/ndb/datasetsummary.sql‎

Lines changed: 5 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1,14 +1,12 @@
1-
CREATE OR REPLACE FUNCTION ndb.datasetsummary(startperiod integer default 0,
2-
endperiod integer default 1)
3-
RETURNS TABLE (databasename varchar,
4-
counts bigint)
5-
AS
6-
$function$
1+
CREATE OR REPLACE FUNCTION ndb.datasetsummary(startperiod integer DEFAULT 0, endperiod integer DEFAULT 1)
2+
RETURNS TABLE(databasename character varying, counts bigint)
3+
LANGUAGE sql
4+
AS $function$
75
SELECT dst.datasettype, count(*)
86
FROM ndb.datasets AS ds
97
JOIN ndb.datasettypes AS dst ON ds.datasettypeid = dst.datasettypeid
108
JOIN ndb.datasetsubmissions AS dss on dss.datasetid = ds.datasetid
119
JOIN ndb.constituentdatabases AS cdb ON cdb.databaseid = dss.databaseid
1210
WHERE EXTRACT(month from AGE(NOW(), dss.submissiondate)) BETWEEN 1 and 2
1311
GROUP BY cdb.databasename, dst.datasettype
14-
$function$ LANGUAGE SQL;
12+
$function$

‎function/ndb/stewardcontrib.sql‎

Lines changed: 5 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1,13 +1,11 @@
1-
CREATE OR REPLACE FUNCTION ndb.stewardcontrib(startperiod integer default 0,
2-
endperiod integer default 1)
3-
RETURNS TABLE (databasename varchar,
4-
counts bigint)
5-
AS
6-
$function$
1+
CREATE OR REPLACE FUNCTION ndb.stewardcontrib(startperiod integer DEFAULT 0, endperiod integer DEFAULT 1)
2+
RETURNS TABLE(databasename character varying, counts bigint)
3+
LANGUAGE sql
4+
AS $function$
75
SELECT cdb.databasename, count(*)
86
FROM ndb.datasets AS ds
97
JOIN ndb.datasetsubmissions AS dss on dss.datasetid = ds.datasetid
108
JOIN ndb.constituentdatabases AS cdb ON cdb.databaseid = dss.databaseid
119
WHERE EXTRACT(month from AGE(NOW(), dss.submissiondate)) BETWEEN startperiod and endperiod
1210
GROUP BY cdb.databasename
13-
$function$ LANGUAGE SQL;
11+
$function$

‎function/ti/getanalysisunitsamplecount.sql‎

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,8 @@
11
CREATE OR REPLACE FUNCTION ti.getanalysisunitsamplecount(_analunitid integer)
2-
RETURNS TABLE(count INTEGER)
3-
AS $$
2+
RETURNS TABLE(count integer)
3+
LANGUAGE sql
4+
AS $function$
45
SELECT count(analysisunitid)::integer AS count
56
FROM ndb.samples
67
WHERE analysisunitid = $1;
7-
$$ LANGUAGE SQL;
8+
$function$

‎function/ti/getchildtaxacount.sql‎

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,7 +1,8 @@
11
CREATE OR REPLACE FUNCTION ti.getchildtaxacount(_highertaxonid integer)
2-
RETURNS TABLE(count INTEGER)
3-
AS $$
2+
RETURNS TABLE(count integer)
3+
LANGUAGE sql
4+
AS $function$
45
SELECT COUNT(highertaxonid)::integer AS count
56
FROM ndb.taxa
67
WHERE (highertaxonid <> taxonid) AND (highertaxonid = $1);
7-
$$ LANGUAGE SQL;
8+
$function$

‎function/ti/getchroncontrolsbychroncontrolid.sql‎

Lines changed: 3 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1,9 +1,6 @@
11
CREATE OR REPLACE FUNCTION ti.getchroncontrolsbychroncontrolid(_chroncontrolid integer)
2-
RETURNS TABLE(chroncontrolid integer , chroncontroltypeid integer , chroncontroltype character varying(64), depth double precision,
3-
thickness double precision, analysisunitid integer, analysisunitname character varying(80), agetypeid integer, age double precision,
4-
agelimityounger double precision, agelimitolder double precision, notes text, calibrationcurve character varying(24),
5-
calibrationprogram character varying(24), version character varying(24))
6-
LANGUAGE sql
2+
RETURNS TABLE(chroncontrolid integer, chroncontroltypeid integer, chroncontroltype character varying, depth double precision, thickness double precision, analysisunitid integer, analysisunitname character varying, agetypeid integer, age double precision, agelimityounger double precision, agelimitolder double precision, notes text, calibrationcurve character varying, calibrationprogram character varying, version character varying)
3+
LANGUAGE sql
74
AS $function$
85
SELECT ndb.chroncontrols.chroncontrolid, ndb.chroncontrols.chroncontroltypeid, ndb.chroncontroltypes.chroncontroltype, ndb.chroncontrols.depth,
96
ndb.chroncontrols.thickness, ndb.chroncontrols.analysisunitid, ndb.analysisunits.analysisunitname, ndb.chroncontrols.agetypeid, ndb.chroncontrols.age,
@@ -17,4 +14,4 @@ FROM ndb.calibrationprograms INNER JOIN
1714
ndb.chroncontrolscal14c.chroncontrolid = ndb.chroncontrols.chroncontrolid LEFT OUTER JOIN
1815
ndb.analysisunits ON ndb.chroncontrols.analysisunitid = ndb.analysisunits.analysisunitid
1916
WHERE ndb.chroncontrols.chroncontrolid = $1;
20-
$function$
17+
$function$

0 commit comments

Comments
 (0)