[mirrorbrain-commits] r8350 - in /trunk: mb/mb/conn.py sql/initialdata-postgresql.sql sql/schema-postgresql.sql

Author: poeml
Date: Thu Jan 30 02:05:48 2014
New Revision: 8350

URL: http://svn.mirrorbrain.org/viewvc/mirrorbrain?rev=8350&view=rev
Log:
mb:
- There's a "version" table now (created since 2.17.0), but it didn't contain
  an id column as primary key, so SQLObject couldn't work with it. So now we
  add an id column as primary key to the table (migrating an existing table by
  simply recreating it). 

Modified:
    trunk/mb/mb/conn.py
    trunk/sql/initialdata-postgresql.sql
    trunk/sql/schema-postgresql.sql

Modified: trunk/mb/mb/conn.py
URL: http://svn.mirrorbrain.org/viewvc/mirrorbrain/trunk/mb/mb/conn.py?rev=8350&r1=8349&r2=8350&view=diff
==============================================================================
--- trunk/mb/mb/conn.py	(original)
+++ trunk/mb/mb/conn.py	Thu Jan 30 02:05:48 2014
_at_@ -145,27 +145,49 @@
 
 
         # upgrade things in the database, if needed
+        self.Version = None
         try:
             class Version(SQLObject):
                 """version of the database schema"""
                 class sqlmeta:
                     fromDatabase = True
-        except psycopg2.ProgrammingError:
-            print 'Your database needs to be upgraded (2.17.0)...'
+            self.Version = Version
+            
+            # 2.17.0 shipped with an SQL schema where the version table didn't contain an "id" column
+            # sqlobject doesn't like that, so let's re-create the table properly...
+            try:
+                dbversion = self.Version.select("""component = 'mirrorbrain'""")[0]
+            except (dberrors.ProgrammingError, psycopg2.ProgrammingError):
+                query = "drop table version;"
+                SQLObject._connection.query(query)
+                raise
+
+
+        except (dberrors.ProgrammingError, psycopg2.ProgrammingError):
+            print 'Your database needs to be upgraded (to 2.18.0): creating "version" table...'
 
             query = """CREATE TABLE version ( 
-                           "component" text NOT NULL PRIMARY KEY,
+                           "id" serial NOT NULL PRIMARY KEY,
+                           "component" text NOT NULL,
                            "major" INTEGER NOT NULL,
                            "minor" INTEGER NOT NULL,
                            "patchlevel" INTEGER NOT NULL );
-                       INSERT INTO version VALUES ('mirrorbrain', 2, 17, 0);
+                       INSERT INTO version VALUES (1, 'mirrorbrain', 2, 18, 0);
                     """
             SQLObject._connection.query(query)
 
-            # the following modification comes with 2.17.0
-            print "migrating server table by adding ipv6_only column"
-            query = "ALTER TABLE server ADD COLUMN ipv6_only boolean NOT NULL default 'f';"
-            SQLObject._connection.query(query)
+            try:
+                # the following modification came with 2.17.0
+                print "checking server table if ipv6_only column exists...",
+                query = "ALTER TABLE server ADD COLUMN ipv6_only boolean NOT NULL default 'f';"
+                SQLObject._connection.query(query)
+                print "created."
+            except (dberrors.ProgrammingError, psycopg2.ProgrammingError):
+                print "already there."
+
+        if self.Version:
+            mbversion = self.Version.select("""component = 'mirrorbrain'""")[0]
+            #print mbversion.major, mbversion.minor
 
 
         class Server(SQLObject):

Modified: trunk/sql/initialdata-postgresql.sql
URL: http://svn.mirrorbrain.org/viewvc/mirrorbrain/trunk/sql/initialdata-postgresql.sql?rev=8350&r1=8349&r2=8350&view=diff
==============================================================================
--- trunk/sql/initialdata-postgresql.sql	(original)
+++ trunk/sql/initialdata-postgresql.sql	Thu Jan 30 02:05:48 2014
_at_@ -1,4 +1,4 @@
-INSERT INTO version VALUES ('mirrorbrain', 2, 18, 0);
+INSERT INTO version VALUES ('1', 'mirrorbrain', 2, 18, 0);
 
 INSERT INTO region VALUES (1,'af','Africa'),(2,'as','Asia'),(3,'eu','Europe'),(4,'na','North America'),(5,'sa','South America'),(6,'oc','Oceania');
 

Modified: trunk/sql/schema-postgresql.sql
URL: http://svn.mirrorbrain.org/viewvc/mirrorbrain/trunk/sql/schema-postgresql.sql?rev=8350&r1=8349&r2=8350&view=diff
==============================================================================
--- trunk/sql/schema-postgresql.sql	(original)
+++ trunk/sql/schema-postgresql.sql	Thu Jan 30 02:05:48 2014
_at_@ -14,7 +14,8 @@
 
 
 CREATE TABLE "version" (
-        "component" text NOT NULL PRIMARY KEY,
+        "id" serial NOT NULL PRIMARY KEY,
+        "component" text NOT NULL,
         "major" INTEGER NOT NULL,
         "minor" INTEGER NOT NULL,
         "patchlevel" INTEGER NOT NULL




_______________________________________________
mirrorbrain-commits mailing list
Archive: http://mirrorbrain.org/archive/mirrorbrain-commits/

Note: To remove yourself from this list, send a mail with the content
 	unsubscribe
to the address mirrorbrain-commits-request_at_mirrorbrain.org