From 2ce9639124ec4eac47d3b785c8f711e9e40465ed Mon Sep 17 00:00:00 2001
From: Sofia Papagiannaki <papagian@gmail.com>
Date: Mon, 12 Sep 2011 13:22:46 +0300
Subject: [PATCH] implement insert or ignore database statements

Fixes  #1075
---
 pithos/backends/lib/sqlalchemy/dbwrapper.py |    6 +-----
 pithos/backends/lib/sqlalchemy/groups.py    |   19 +++++++++++++------
 pithos/backends/lib/sqlalchemy/public.py    |   10 ++++++++--
 pithos/backends/lib/sqlalchemy/xfeatures.py |   18 ++++++++++++------
 4 files changed, 34 insertions(+), 19 deletions(-)

diff --git a/pithos/backends/lib/sqlalchemy/dbwrapper.py b/pithos/backends/lib/sqlalchemy/dbwrapper.py
index 2ac0e35..a4465b2 100644
--- a/pithos/backends/lib/sqlalchemy/dbwrapper.py
+++ b/pithos/backends/lib/sqlalchemy/dbwrapper.py
@@ -32,16 +32,12 @@
 # or implied, of GRNET S.A.
 
 from sqlalchemy import create_engine
-from sqlalchemy.pool import NullPool
 
 class DBWrapper(object):
     """Database connection wrapper."""
     
     def __init__(self, db):
-        if db.startswith('sqlite://'):
-            self.engine = create_engine(db, connect_args={'check_same_thread': False}, poolclass=NullPool)
-        else:
-            self.engine = create_engine(db)
+        self.engine = create_engine(db)
         self.conn = self.engine.connect()
         self.trans = None
     
diff --git a/pithos/backends/lib/sqlalchemy/groups.py b/pithos/backends/lib/sqlalchemy/groups.py
index 49d81b8..f10f7d9 100644
--- a/pithos/backends/lib/sqlalchemy/groups.py
+++ b/pithos/backends/lib/sqlalchemy/groups.py
@@ -79,17 +79,24 @@ class Groups(DBWorker):
     def group_add(self, owner, group, member):
         """Add a member to a group."""
         
-        s = self.groups.insert()
-        r = self.conn.execute(s, owner=owner, name=group, member=member)
+        s = self.groups.select()
+        s = s.where(self.groups.c.owner == owner)
+        s = s.where(self.groups.c.name == group)
+        s = s.where(self.groups.c.member == member)
+        r = self.conn.execute(s)
+        groups = r.fetchall()
         r.close()
+        if len(groups) == 0:    
+            s = self.groups.insert()
+            r = self.conn.execute(s, owner=owner, name=group, member=member)
+            r.close()
     
     def group_addmany(self, owner, group, members):
         """Add members to a group."""
         
-        s = self.groups.insert()
-        values = [{'owner':owner, 'name':group, 'member':member} for member in members]
-        r = self.conn.execute(s, values)
-        r.close()
+        #TODO: more efficient way to do it
+        for member in members:
+            self.group_add(owner, group, member)
     
     def group_remove(self, owner, group, member):
         """Remove a member from a group."""
diff --git a/pithos/backends/lib/sqlalchemy/public.py b/pithos/backends/lib/sqlalchemy/public.py
index 14bd60a..7037e7a 100644
--- a/pithos/backends/lib/sqlalchemy/public.py
+++ b/pithos/backends/lib/sqlalchemy/public.py
@@ -48,9 +48,15 @@ class Public(DBWorker):
     
     
     def public_set(self, path):
-        s = self.public.insert()
-        r = self.conn.execute(s, path = path)
+        s = self.public.select()
+        s = s.where(self.xfeaturevals.c.path == path)
+        r = self.conn.execute(s)
+        public = r.fetchall()
         r.close()
+        if len(public) == 0:
+            s = self.public.insert()
+            r = self.conn.execute(s, path = path)
+            r.close()
     
     def public_unset(self, path):
         s = self.public.delete().where(self.public.c.path == path)
diff --git a/pithos/backends/lib/sqlalchemy/xfeatures.py b/pithos/backends/lib/sqlalchemy/xfeatures.py
index 41ac2bf..577b3aa 100644
--- a/pithos/backends/lib/sqlalchemy/xfeatures.py
+++ b/pithos/backends/lib/sqlalchemy/xfeatures.py
@@ -142,17 +142,23 @@ 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)
+        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."""
-- 
1.7.10.4