Changeset 212 for trunk

Timestamp:

12/21/11 17:49:46 (13 years ago)

Author:

Kris Deugau

Message:

/trunk

Begin setting up the barest sketch of what will eventually become
a complete automagical DB init/upgrade feature. See #34.

Location:

trunk

Files:

• DNSDB.pm (modified) (1 diff)
• dns.sql (modified) (2 diffs)

trunk/DNSDB.pm

@@ -262 +262 @@
     or return (undef, $DBI::errstr) if(!$dbh);
 
-# Return here if we can't select.  Note that this indicates a
-# problem executing the select.
-  my $sth = $dbh->prepare("select group_id from groups limit 1");
+##fixme:  initialize the DB if we can't find the table (since, by definition, there's
+# nothing there if we can't select from it...)
+  my $tblsth = $dbh->prepare("SELECT count(*) FROM pg_catalog.pg_class WHERE relkind='r' AND relname=?");
+  my ($tblcount) = $dbh->selectrow_array($tblsth, undef, ('misc'));
+  return (undef,$DBI::errstr) if $dbh->err;
+
+#if ($tblcount == 0) {
+#  # create tables one at a time, checking for each.
+#  return (undef, "check table misc missing");
+#}
+
+
+# Return here if we can't select.
+# This should retrieve the dbversion key.
+  my $sth = $dbh->prepare("SELECT key,value FROM misc WHERE misc_id=1");
   $sth->execute();
   return (undef,$DBI::errstr) if ($sth->err);
+
+##fixme:  do stuff to the DB on version mismatch
+# x.y series should upgrade on $DNSDB::VERSION > misc(key=>version)
+# DB should be downward-compatible;  column defaults should give sane (if possibly
+# useless-and-needs-help) values in columns an older software stack doesn't know about.
 
 # See if the select returned anything (or null data).  This should

trunk/dns.sql

@@ -5 +5 @@
 -- CREATE DATABASE dnsdb OWNED BY dnsdb;
 -- SET SESSION AUTHORIZATION 'dnsdb';
+
+-- need a handy place to put eg a DB version identifier - useful for auto-upgrading a DB
+CREATE TABLE misc (
+        misc_id serial NOT NULL,
+        key text DEFAULT '' NOT NULL,
+        value test DEFAULT '' NOT NULL
+);
+
+COPY misc (misc_id, key, value) FROM stdin;
+1       dbversion       1.0
+\.
 
 CREATE TABLE default_records (
@@ -253 +264 @@
 
 -- set starting sequence numbers, since we've inserted data before they're active
+SELECT pg_catalog.setval('misc_misc_id_seq', 1, true);
 SELECT pg_catalog.setval('default_records_record_id_seq', 8, true);
 SELECT pg_catalog.setval('domains_domain_id_seq', 1, false);