use django DB connection for patchwork

This is a bit of a hack to get the DB class using the configured
django DB. This allows us to then use postgres to fix bug:

 https://bugs.linaro.org/show_bug.cgi?id=247

Additionally, I made this object accessible via a contextmanager
so that its easier/automagic to close the database connection.

Change-Id: I6a8488421e4a77df1e082d2a7b6ef5a12b6f994c

3 files changed, 45 insertions, 54 deletions

diff --git a/apps/patchwork/bin/parsemail.py b/apps/patchwork/bin/parsemail.py
index a7c7005..fe643d8 100755
--- a/apps/patchwork/bin/parsemail.py
+++ b/apps/patchwork/bin/parsemail.py
@@ -19,8 +19,6 @@
 # along with Patchwork; if not, write to the Free Software
 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 
-import _pythonpath
-import atexit
 import chardet
 import datetime
 import operator
@@ -503,11 +501,10 @@ def is_valid_patch(submitter, author):
                          settings.AUTH_CROWD_SERVER_REST_URI)
 
             cache_db = PatchworkDB(settings.CROWD_USERS_DB_FILE)
-            atexit.register(cache_db.close)
-
-            valid_patch |= is_linaro_user(submitter.email, cache_db, crwd)
-            if author is not None:
-                valid_patch |= is_linaro_user(author.email, cache_db, crwd)
+            with cache_db:
+                valid_patch |= is_linaro_user(submitter.email, cache_db, crwd)
+                if author is not None:
+                    valid_patch |= is_linaro_user(author.email, cache_db, crwd)
 
     return valid_patch
 
diff --git a/apps/patchwork/bin/update-committed-patches.py b/apps/patchwork/bin/update-committed-patches.py
index 86afa4d..c10aad3 100755
--- a/apps/patchwork/bin/update-committed-patches.py
+++ b/apps/patchwork/bin/update-committed-patches.py
@@ -9,8 +9,6 @@
 # accepted into sub-maintainer/custodian branches will only be marked as
 # accepted once they reach the master branch.
 
-import _pythonpath
-import atexit
 import sys
 
 from datetime import datetime
@@ -49,11 +47,14 @@ def main():
         sys.exit(1)
 
     cache_db = PatchworkDB(settings.CROWD_USERS_DB_FILE)
-    atexit.register(cache_db.close)
-
     projects = Project.objects.exclude(
         Q(source_tree__isnull=True) | Q(source_tree=''))
 
+    with cache_db:
+        _update(crwd, projects, cache_db)
+
+
+def _update(crwd, projects, cache_db):
     print "Total number of projects: {0}".format(len(projects))
 
     for project in projects:
diff --git a/apps/patchwork/db.py b/apps/patchwork/db.py
index 4e33304..e886ba5 100644
--- a/apps/patchwork/db.py
+++ b/apps/patchwork/db.py
@@ -17,7 +17,14 @@
 # along with Patchwork; if not, write to the Free Software
 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 
-import sqlite3
+import os
+import sys
+
+os.environ['DJANGO_SETTINGS_MODULE'] = 'settings'
+here = os.path.abspath(os.path.dirname(__file__))
+sys.path.append(os.path.join(here, '..'))
+import django.db
+from django.utils.importlib import import_module
 
 
 class PatchworkDB(object):
@@ -33,54 +40,34 @@ class PatchworkDB(object):
     There is no user logic here, it is just used as a cache backend.
     """
 
-    def __init__(self, db_file):
-        self.db_file = db_file
-        self._connection = None
-        self._cursor = None
+    def __init__(self):
+        self._db_wrapper = None
+        self.cursor = None
 
-        self._init_db()
+    def __enter__(self):
+        self._db_wrapper = django.db.connections[django.db.DEFAULT_DB_ALIAS]
+        self.cursor = self._db_wrapper.cursor()  # triggers a "connection"
+        self.connection = self._db_wrapper.connection
 
-    def _init_db(self):
         if not self.table_exists('users'):
             self.cursor.execute('''CREATE TABLE users
                 (id TEXT PRIMARY KEY, valid INTEGER, ts TIMESTAMP)''')
 
-    def close(self):
+    def __exit__(self, type, value, tb):
         """Closes the DB connection."""
         # Force a commit, even if we are in autocommit mode.
-        self.save()
-        self.connection.close()
-
-    def save(self):
-        """Performs a commit on the DB."""
         self.connection.commit()
-
-    @property
-    def connection(self):
-        """The connection to the DB."""
-        if self._connection is None:
-            self._connection = sqlite3.connect(self.db_file)
-        return self._connection
-
-    @property
-    def cursor(self):
-        """The connection cursor."""
-        if self._cursor is None:
-            self._cursor = self.connection.cursor()
-        return self._cursor
+        self.connection.close()
 
     def table_exists(self, table):
         """Checks if a table exists in the DB.
 
         :param table: The name of the table to check.
         """
-        self.cursor.execute('''SELECT name FROM sqlite_master WHERE
-            type='table' AND name=?''', [table])
-
-        exists = False
-        if self.cursor.fetchone():
-            exists = True
-        return exists
+        base = self._db_wrapper.__module__.replace('.base', '')
+        module = import_module('.introspection', base)
+        introspection = module.DatabaseIntrospection(self.connection)
+        return table in introspection.get_table_list(self.cursor)
 
     def insert_user(self, email, valid, timestamp):
         """Inserts a user in the DB.
@@ -90,11 +77,11 @@ class PatchworkDB(object):
         :param timestamp: When the user was added in the cache.
         """
         self.cursor.execute('''INSERT INTO users(id, valid, ts)
-            VALUES (?, ?, ?)''', (email, valid, timestamp))
+            VALUES (%s, %s, %s)''', (email, valid, timestamp))
 
     def update_user(self, email, valid, timestamp):
         """Updates a user in the DB."""
-        self.cursor.execute('''UPDATE users SET valid=?, ts=? WHERE id=?''',
+        self.cursor.execute('''UPDATE users SET valid=%s, ts=%s WHERE id=%s''',
                             (valid, timestamp, email))
 
     def get_user(self, email):
@@ -103,7 +90,7 @@ class PatchworkDB(object):
         :param email: The user email.
         :return A tuple with the data found, None otherwise.
         """
-        self.cursor.execute('''SELECT * FROM users where id=?''', [email])
+        self.cursor.execute('''SELECT * FROM users where id=%s''', [email])
         return self.cursor.fetchone()
 
     def user_exists(self, email):
@@ -112,9 +99,15 @@ class PatchworkDB(object):
         :param email: The user email.
         :return True or False.
         """
-        result = self.get_user(email)
-
-        exists = False
-        if result:
-            exists = True
-        return exists
+        return self.get_user(email) is not None
+
+if __name__ == '__main__':
+    import datetime
+    db = PatchworkDB()
+    with db:
+        if not db.user_exists('andy'):
+            print "creating user"
+            db.insert_user('andy', 1, datetime.datetime.now())
+        else:
+            print "user exists, updating"
+            db.update_user('andy', 0, datetime.datetime.now())