Updating the sequencing data and removing steward table updates.

SimonGoring · SimonGoring · commit befa88ba66c5 · 2020-09-28T13:13:29.000-07:00
diff --git a/connect_remote.py b/connect_remote.py
@@ -136,8 +136,8 @@
         file = open(newFile)
         textCheck = copy.deepcopy(file.read())
         serverFun = copy.deepcopy(record[3])
-        textCheck = re.sub('[\s+\t+\n+\r+]', '', textCheck)
-        serverFun = re.sub('[\s+\t+\n+\r+]', '', serverFun)
+        textCheck = re.sub(r'[\s+\t+\n+\r+]', '', textCheck)
+        serverFun = re.sub(r'[\s+\t+\n+\r+]', '', serverFun)
         match = serverFun == textCheck
         # Pushing (to the db) and pulling (from the db) are defined by the user
         if match is False:
@@ -228,8 +228,22 @@
             print(schema + "." + functs.split(".")[0] + " has "
                   + str(cur.rowcount) + " definitions.")
 
-conn.close()
+print('Running the indexes:')
+
+# This section makes sure that the sequences are reset properly.  We ran into
+# this issue unintentionally during a re-load of the Neotoma data.
+cur.execute(open('helpers/reset_sequences.sql', 'r').read())
+cur2 = conn.cursor()
 
+for res in cur.fetchall():
+    try:
+        cur2.execute(res[0])
+        conn.commit()
+    except Exception as e:
+        print('skipped: ' + res[0])
+        print(e)
+
+conn.close()
 
 print("The script has rewritten:")
 
diff --git a/function/ts/deletesynonymy.sql b/function/ts/deletesynonymy.sql
@@ -4,7 +4,4 @@ CREATE OR REPLACE FUNCTION ts.deletesynonymy(_synonymyid integer, _contactid int
 AS $function$
   DELETE FROM ndb.synonymy AS sy
   WHERE sy.synonymyid = _synonymyid;
-
-  INSERT INTO ti.stewardupdates(contactid, tablename, pk1, operation)
-  VALUES      (_contactid, n'synonymy', _synonymyid, n'delete')
 $function$
diff --git a/function/ts/insertsample.sql b/function/ts/insertsample.sql
@@ -1,4 +1,12 @@
-CREATE OR REPLACE FUNCTION ts.insertsample(_analysisunitid integer, _datasetid integer, _samplename character varying DEFAULT NULL::character varying, _sampledate character varying DEFAULT NULL::character varying, _analysisdate character varying DEFAULT NULL::character varying, _taxonid integer DEFAULT NULL::integer, _labnumber character varying DEFAULT NULL::character varying, _prepmethod character varying DEFAULT NULL::character varying, _notes character varying DEFAULT NULL::character varying)
+CREATE OR REPLACE FUNCTION ts.insertsample(_analysisunitid integer,
+  _datasetid integer,
+  _samplename character varying DEFAULT NULL::character varying,
+  _sampledate character varying DEFAULT NULL::character varying,
+  _analysisdate character varying DEFAULT NULL::character varying,
+  _taxonid integer DEFAULT NULL::integer,
+  _labnumber character varying DEFAULT NULL::character varying,
+  _prepmethod character varying DEFAULT NULL::character varying,
+  _notes character varying DEFAULT NULL::character varying)
  RETURNS integer
  LANGUAGE sql
 AS $function$
diff --git a/helpers/reset_sequences.sql b/helpers/reset_sequences.sql
@@ -1,14 +1,15 @@
+/* Code from here: */
 SELECT 'select '
         || trim(trailing ')'
            from replace(pg_get_expr(d.adbin, d.adrelid),
                         'nextval', 'setval'))
         || ', (select max( ' || a.attname || ') from only '
         || nspname || '.' || relname || '));'
   FROM pg_class c
-       JOIN pg_namespace n on n.oid = c.relnamespace
-       JOIN pg_attribute a on a.attrelid = c.oid
-       JOIN pg_attrdef d on d.adrelid = a.attrelid
-                          and d.adnum = a.attnum
-                          and a.atthasdef
- WHERE relkind = 'r' and a.attnum > 0
-       and pg_get_expr(d.adbin, d.adrelid) ~ '^nextval';
+       JOIN pg_namespace n ON n.oid = c.relnamespace
+       JOIN pg_attribute a ON a.attrelid = c.oid
+       JOIN pg_attrdef d ON d.adrelid = a.attrelid
+                          AND d.adnum = a.attnum
+                          AND a.atthasdef
+WHERE relkind = 'r' and a.attnum > 0
+  AND pg_get_expr(d.adbin, d.adrelid) ~ '^nextval';
