use alembic to initialize the backend database

[pithos] / snf-pithos-backend / pithos / backends / lib / sqlalchemy / public.py

diff --git a/snf-pithos-backend/pithos/backends/lib/sqlalchemy/public.py b/snf-pithos-backend/pithos/backends/lib/sqlalchemy/public.py
index 9ebdae3..a132980 100644 (file)
--- a/snf-pithos-backend/pithos/backends/lib/sqlalchemy/public.py
+++ b/snf-pithos-backend/pithos/backends/lib/sqlalchemy/public.py
@@ -1,4 +1,4 @@
-# Copyright 2011 GRNET S.A. All rights reserved.
+# Copyright 2011-2012 GRNET S.A. All rights reserved.
 # 
 # Redistribution and use in source and binary forms, with or
 # without modification, are permitted provided that the following
@@ -32,43 +32,68 @@
 # or implied, of GRNET S.A.
 
 from dbworker import DBWorker
-from sqlalchemy import Table, Column, String, Integer, MetaData
-from sqlalchemy.sql import select
+from sqlalchemy import Table, Column, String, Integer, Boolean, MetaData
+from sqlalchemy.sql import and_, select
 from sqlalchemy.schema import Index
+from sqlalchemy.exc import NoSuchTableError
 
+def create_tables(engine):
+    metadata = MetaData()
+    columns=[]
+    columns.append(Column('public_id', Integer, primary_key=True))
+    columns.append(Column('path', String(2048), nullable=False))
+    columns.append(Column('active', Boolean, nullable=False, default=True))
+    public = Table('public', metadata, *columns, mysql_engine='InnoDB', sqlite_autoincrement=True)
+    # place an index on path
+    Index('idx_public_path', public.c.path, unique=True)
+    metadata.create_all(engine)
+    return metadata.sorted_tables
 
 class Public(DBWorker):
     """Paths can be marked as public."""
     
     def __init__(self, **params):
         DBWorker.__init__(self, **params)
-        metadata = MetaData()
-        columns=[]
-        columns.append(Column('public_id', Integer, primary_key=True))
-        columns.append(Column('path', String(2048)))
-        self.public = Table('public', metadata, *columns, mysql_engine='InnoDB', sqlite_autoincrement=True)
-        # place an index on path
-        Index('idx_public_path', self.public.c.path, unique=True)
-        metadata.create_all(self.engine)
+        try:
+            metadata = MetaData(self.engine)
+            self.public = Table('public', metadata, autoload=True)
+        except NoSuchTableError:
+            tables = create_tables(self.engine)
+            map(lambda t: self.__setattr__(t.name, t), tables)
     
     def public_set(self, path):
-        s = self.public.select()
+        s = select([self.public.c.public_id])
         s = s.where(self.public.c.path == path)
         r = self.conn.execute(s)
-        public = r.fetchall()
+        row = r.fetchone()
         r.close()
-        if len(public) == 0:
+        if row:
+            s = self.public.update().where(self.public.c.public_id == row[0])
+            s = s.values(active=True)
+        else:
             s = self.public.insert()
-            r = self.conn.execute(s, path = path)
-            r.close()
+            s = s.values(path=path, active=True)
+        r = self.conn.execute(s)
+        r.close()
     
     def public_unset(self, path):
-        s = self.public.delete().where(self.public.c.path == path)
+        s = self.public.update()
+        s = s.where(self.public.c.path == path)
+        s = s.values(active=False)
+        r = self.conn.execute(s)
+        r.close()
+    
+    def public_unset_bulk(self, paths):
+        s = self.public.update()
+        s = s.where(self.public.c.path.in_(paths))
+        s = s.values(active=False)
         r = self.conn.execute(s)
         r.close()
     
     def public_get(self, path):
-        s = select([self.public.c.public_id], self.public.c.path == path)
+        s = select([self.public.c.public_id])
+        s = s.where(and_(self.public.c.path == path,
+                         self.public.c.active == True))
         r = self.conn.execute(s)
         row = r.fetchone()
         r.close()
@@ -76,8 +101,19 @@ class Public(DBWorker):
             return row[0]
         return None
     
+    def public_list(self, prefix):
+        s = select([self.public.c.path, self.public.c.public_id])
+        s = s.where(self.public.c.path.like(self.escape_like(prefix) + '%', escape='\\'))
+        s = s.where(self.public.c.active == True)
+        r = self.conn.execute(s)
+        rows = r.fetchall()
+        r.close()
+        return rows
+    
     def public_path(self, public):
-        s = select([self.public.c.path], self.public.c.public_id == public)
+        s = select([self.public.c.path])
+        s = s.where(and_(self.public.c.public_id == public,
+                         self.public.c.active == True))
         r = self.conn.execute(s)
         row = r.fetchone()
         r.close()