Avoid reverse LIKE statements in SQL.

[pithos] / pithos / backends / lib / sqlalchemy / xfeatures.py

diff --git a/pithos/backends/lib/sqlalchemy/xfeatures.py b/pithos/backends/lib/sqlalchemy/xfeatures.py
index 41ac2bf..2d530c9 100644 (file)
--- a/pithos/backends/lib/sqlalchemy/xfeatures.py
+++ b/pithos/backends/lib/sqlalchemy/xfeatures.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
@@ -51,9 +51,9 @@ class XFeatures(DBWorker):
         columns=[]
         columns.append(Column('feature_id', Integer, primary_key=True))
         columns.append(Column('path', String(2048)))
-        self.xfeatures = Table('xfeatures', metadata, *columns)
+        self.xfeatures = Table('xfeatures', metadata, *columns, mysql_engine='InnoDB')
         # place an index on path
-        Index('idx_features_path', self.xfeatures.c.path)
+        Index('idx_features_path', self.xfeatures.c.path, unique=True)
         
         columns=[]
         columns.append(Column('feature_id', Integer,
@@ -63,57 +63,43 @@ class XFeatures(DBWorker):
         columns.append(Column('key', Integer, primary_key=True,
                               autoincrement=False))
         columns.append(Column('value', String(255), primary_key=True))
-        self.xfeaturevals = Table('xfeaturevals', metadata, *columns)
+        self.xfeaturevals = Table('xfeaturevals', metadata, *columns, mysql_engine='InnoDB')
         
         metadata.create_all(self.engine)
     
-    def xfeature_inherit(self, path):
-        """Return the (path, feature) inherited by the path, or None."""
-        
-        s = select([self.xfeatures.c.path, self.xfeatures.c.feature_id])
-        s = s.where(self.xfeatures.c.path <= path)
-        s = s.order_by(desc(self.xfeatures.c.path)).limit(1)
-        r = self.conn.execute(s)
-        row = r.fetchone()
-        r.close()
-        if row and path.startswith(row[0]):
-            return row
-        else:
-            return None
+#     def xfeature_inherit(self, path):
+#         """Return the (path, feature) inherited by the path, or None."""
+#         
+#         s = select([self.xfeatures.c.path, self.xfeatures.c.feature_id])
+#         s = s.where(self.xfeatures.c.path <= path)
+#         #s = s.where(self.xfeatures.c.path.like(self.escape_like(path) + '%', escape='\\')) # XXX: Implement reverse and escape like...
+#         s = s.order_by(desc(self.xfeatures.c.path))
+#         r = self.conn.execute(s)
+#         l = r.fetchall()
+#         r.close()
+#         return l
     
-    def xfeature_list(self, path):
-        """Return the list of the (prefix, feature) pairs matching path.
-           A prefix matches path if either the prefix includes the path,
-           or the path includes the prefix.
-        """
-        
-        inherited = self.xfeature_inherit(path)
-        if inherited:
-            return [inherited]
+    def xfeature_get(self, path):
+        """Return feature for path."""
         
-        s = select([self.xfeatures.c.path, self.xfeatures.c.feature_id])
-        s = s.where(and_(self.xfeatures.c.path.like(path + '%'),
-                     self.xfeatures.c.path != path))
+        s = select([self.xfeatures.c.feature_id])
+        s = s.where(self.xfeatures.c.path == path)
         s = s.order_by(self.xfeatures.c.path)
         r = self.conn.execute(s)
-        l = r.fetchall()
+        row = r.fetchone()
         r.close()
-        return l
+        if row:
+            return row[0]
+        return None
     
     def xfeature_create(self, path):
         """Create and return a feature for path.
-           If the path already inherits a feature or
-           bestows to paths already inheriting a feature,
-           create no feature and return None.
            If the path has a feature, return it.
         """
         
-        prefixes = self.xfeature_list(path)
-        pl = len(prefixes)
-        if (pl > 1) or (pl == 1 and prefixes[0][0] != path):
-            return None
-        if pl == 1 and prefixes[0][0] == path:
-            return prefixes[0][1]
+        feature = self.xfeature_get(path)
+        if feature is not None:
+            return feature
         s = self.xfeatures.insert()
         r = self.conn.execute(s, path=path)
         inserted_primary_key = r.inserted_primary_key[0]
@@ -142,17 +128,24 @@ class XFeatures(DBWorker):
     def feature_set(self, feature, key, value):
         """Associate a key, value pair with a feature."""
         
-        s = self.xfeaturevals.insert()
-        r = self.conn.execute(s, feature_id=feature, key=key, value=value)
+        s = self.xfeaturevals.select()
+        s = s.where(self.xfeaturevals.c.feature_id == feature)
+        s = s.where(self.xfeaturevals.c.key == key)
+        s = s.where(self.xfeaturevals.c.value == value)
+        r = self.conn.execute(s)
+        xfeaturevals = r.fetchall()
         r.close()
+        if len(xfeaturevals) == 0:
+            s = self.xfeaturevals.insert()
+            r = self.conn.execute(s, feature_id=feature, key=key, value=value)
+            r.close()
     
     def feature_setmany(self, feature, key, values):
         """Associate the given key, and values with a feature."""
         
-        s = self.xfeaturevals.insert()
-        values = [{'feature_id':feature, 'key':key, 'value':v} for v in values]
-        r = self.conn.execute(s, values)
-        r.close()
+        #TODO: more efficient way to do it
+        for v in values:
+            self.feature_set(feature, key, v)
     
     def feature_unset(self, feature, key, value):
         """Disassociate a key, value pair from a feature."""