update for new id column added to journals (to aid making journal rows unique in the face of duplicate activity)

4 files changed, 10 insertions, 7 deletions

diff --git a/pkgbase_schema.sql b/pkgbase_schema.sql
index abad7a0..f5c9db4 100644
--- a/pkgbase_schema.sql
+++ b/pkgbase_schema.sql
@@ -64,6 +64,7 @@ CREATE INDEX rego_otk_otk_idx ON rego_otk(otk);
 
 -- Table structure for table: journals
 CREATE TABLE journals (
+   id SERIAL,
    name TEXT,
    version TEXT,
    action TEXT,
diff --git a/store.py b/store.py
index d8768e6..d83498e 100644
--- a/store.py
+++ b/store.py
@@ -657,7 +657,7 @@ class Store:
         cursor.execute('select name from packages order by name')
         return [p[0] for p in cursor.fetchall()]
 
-    _Journal = FastResultRow('action submitted_date! submitted_by submitted_from')
+    _Journal = FastResultRow('id action submitted_date! submitted_by submitted_from')
     def get_journal(self, name, version):
         ''' Retrieve info about the package from the database.
 
@@ -667,8 +667,8 @@ class Store:
         '''
         cursor = self.get_cursor()
         # get the generic stuff or the stuff specific to the version
-        sql = '''select action, submitted_date, submitted_by, submitted_from
-            from journals where name=%s and (version=%s or
+        sql = '''select id, action, submitted_date, submitted_by,
+            submitted_from from journals where name=%s and (version=%s or
            version is NULL) order by submitted_date'''
         safe_execute(cursor, sql, (name, version))
         return Result(None, cursor.fetchall(), self._Journal)
@@ -921,16 +921,16 @@ class Store:
         return Result(None, self.get_unique(cursor.fetchall()),
                 self._Updated_Releases)
 
-    _Changelog = FastResultRow('name version submitted_date! action')
+    _Changelog = FastResultRow('id name version submitted_date! action')
     def changelog(self, since):
-        '''Fetch (name, version, submitted_date, action) since 'since' argument.
+        '''Fetch (id, name, version, submitted_date, action) since 'since'
+        argument.
         '''
-
         assert isinstance(since, int)
 
         cursor = self.get_cursor()
         safe_execute(cursor, '''
-            select name,version,submitted_date,action
+            select id, name, version, submitted_date, action
             from journals j
             where j.submitted_date > %s
         ''', (time.strftime('%Y-%m-%d %H:%M:%S +0000', time.gmtime(since)),))
diff --git a/tools/sql-migrate-20130114.sql b/tools/sql-migrate-20130114.sql
new file mode 100644
index 0000000..2ebcbc6
--- /dev/null
+++ b/tools/sql-migrate-20130114.sql
@@ -0,0 +1 @@
+alter table journals add column id serial;
diff --git a/tools/sqlite_create.py b/tools/sqlite_create.py
index e04cb2d..b30b0c2 100644
--- a/tools/sqlite_create.py
+++ b/tools/sqlite_create.py
@@ -46,6 +46,7 @@ cursor.execute('''
     )''')
 cursor.execute('''
     create table journals (
+        id integer primary key autoincrement,
         name varchar,
         version varchar,
         action varchar,