git-interface: Add database abstraction layer

Add a new class that connects to the database specified in the
configuration file and provides an interface to execute SQL queries.
Prepared statements with qmark ("?") placeholders are supported.

Replace all direct database accesses with calls to the new abstraction
layer.

Signed-off-by: Lukas Fleischer <lfleischer@archlinux.org>

1 files changed, 37 insertions, 55 deletions

diff --git a/git-interface/git-serve.py b/git-interface/git-serve.py
index e0ebd0ec..ab612f08 100755
--- a/git-interface/git-serve.py
+++ b/git-interface/git-serve.py
@@ -1,21 +1,16 @@
 #!/usr/bin/python3
 
 import configparser
-import mysql.connector
 import os
 import re
 import shlex
 import sys
 
+import db
+
 config = configparser.RawConfigParser()
 config.read(os.path.dirname(os.path.realpath(__file__)) + "/../conf/config")
 
-aur_db_host = config.get('database', 'host')
-aur_db_name = config.get('database', 'name')
-aur_db_user = config.get('database', 'user')
-aur_db_pass = config.get('database', 'password')
-aur_db_socket = config.get('database', 'socket')
-
 repo_path = config.get('serve', 'repo-path')
 repo_regex = config.get('serve', 'repo-regex')
 git_shell_cmd = config.get('serve', 'git-shell-cmd')
@@ -27,12 +22,8 @@ maintenance_exc = config.get('options', 'maintenance-exceptions').split()
 
 
 def pkgbase_from_name(pkgbase):
-    db = mysql.connector.connect(host=aur_db_host, user=aur_db_user,
-                                 passwd=aur_db_pass, db=aur_db_name,
-                                 unix_socket=aur_db_socket)
-    cur = db.cursor()
-    cur.execute("SELECT ID FROM PackageBases WHERE Name = %s", [pkgbase])
-    db.close()
+    conn = db.Connection()
+    cur = conn.execute("SELECT ID FROM PackageBases WHERE Name = ?", [pkgbase])
 
     row = cur.fetchone()
     return row[0] if row else None
@@ -43,21 +34,18 @@ def pkgbase_exists(pkgbase):
 
 
 def list_repos(user):
-    db = mysql.connector.connect(host=aur_db_host, user=aur_db_user,
-                                 passwd=aur_db_pass, db=aur_db_name,
-                                 unix_socket=aur_db_socket)
-    cur = db.cursor()
+    conn = db.Connection()
 
-    cur.execute("SELECT ID FROM Users WHERE Username = %s ", [user])
+    cur = conn.execute("SELECT ID FROM Users WHERE Username = ?", [user])
     userid = cur.fetchone()[0]
     if userid == 0:
         die('{:s}: unknown user: {:s}'.format(action, user))
 
-    cur.execute("SELECT Name, PackagerUID FROM PackageBases " +
-                "WHERE MaintainerUID = %s ", [userid])
+    cur = conn.execute("SELECT Name, PackagerUID FROM PackageBases " +
+                       "WHERE MaintainerUID = ?", [userid])
     for row in cur:
         print((' ' if row[1] else '*') + row[0])
-    db.close()
+    conn.close()
 
 
 def create_pkgbase(pkgbase, user):
@@ -66,26 +54,25 @@ def create_pkgbase(pkgbase, user):
     if pkgbase_exists(pkgbase):
         die('{:s}: package base already exists: {:s}'.format(action, pkgbase))
 
-    db = mysql.connector.connect(host=aur_db_host, user=aur_db_user,
-                                 passwd=aur_db_pass, db=aur_db_name,
-                                 unix_socket=aur_db_socket)
-    cur = db.cursor()
+    conn = db.Connection()
 
-    cur.execute("SELECT ID FROM Users WHERE Username = %s ", [user])
+    cur = conn.execute("SELECT ID FROM Users WHERE Username = ?", [user])
     userid = cur.fetchone()[0]
     if userid == 0:
         die('{:s}: unknown user: {:s}'.format(action, user))
 
-    cur.execute("INSERT INTO PackageBases (Name, SubmittedTS, ModifiedTS, " +
-                "SubmitterUID, MaintainerUID) VALUES (%s, UNIX_TIMESTAMP(), " +
-                "UNIX_TIMESTAMP(), %s, %s)", [pkgbase, userid, userid])
+    cur = conn.execute("INSERT INTO PackageBases (Name, SubmittedTS, " +
+                       "ModifiedTS, SubmitterUID, MaintainerUID) VALUES " +
+                       "(?, UNIX_TIMESTAMP(), UNIX_TIMESTAMP(), ?, ?)",
+                       [pkgbase, userid, userid])
     pkgbase_id = cur.lastrowid
 
-    cur.execute("INSERT INTO PackageNotifications (PackageBaseID, UserID) " +
-                "VALUES (%s, %s)", [pkgbase_id, userid])
+    cur = conn.execute("INSERT INTO PackageNotifications " +
+                       "(PackageBaseID, UserID) VALUES (?, ?)",
+                       [pkgbase_id, userid])
 
-    db.commit()
-    db.close()
+    conn.commit()
+    conn.close()
 
 
 def pkgbase_set_keywords(pkgbase, keywords):
@@ -93,34 +80,29 @@ def pkgbase_set_keywords(pkgbase, keywords):
     if not pkgbase_id:
         die('{:s}: package base not found: {:s}'.format(action, pkgbase))
 
-    db = mysql.connector.connect(host=aur_db_host, user=aur_db_user,
-                                 passwd=aur_db_pass, db=aur_db_name,
-                                 unix_socket=aur_db_socket)
-    cur = db.cursor()
+    conn = db.Connection()
 
-    cur.execute("DELETE FROM PackageKeywords WHERE PackageBaseID = %s",
-                [pkgbase_id])
+    conn.execute("DELETE FROM PackageKeywords WHERE PackageBaseID = ?",
+                 [pkgbase_id])
     for keyword in keywords:
-        cur.execute("INSERT INTO PackageKeywords (PackageBaseID, Keyword) "
-                    "VALUES (%s, %s)", [pkgbase_id, keyword])
+        conn.execute("INSERT INTO PackageKeywords (PackageBaseID, Keyword) " +
+                     "VALUES (?, ?)", [pkgbase_id, keyword])
 
-    db.commit()
-    db.close()
+    conn.commit()
+    conn.close()
 
 
 def pkgbase_has_write_access(pkgbase, user):
-    db = mysql.connector.connect(host=aur_db_host, user=aur_db_user,
-                                 passwd=aur_db_pass, db=aur_db_name,
-                                 unix_socket=aur_db_socket, buffered=True)
-    cur = db.cursor()
-
-    cur.execute("SELECT COUNT(*) FROM PackageBases " +
-                "LEFT JOIN PackageComaintainers " +
-                "ON PackageComaintainers.PackageBaseID = PackageBases.ID " +
-                "INNER JOIN Users ON Users.ID = PackageBases.MaintainerUID " +
-                "OR PackageBases.MaintainerUID IS NULL " +
-                "OR Users.ID = PackageComaintainers.UsersID " +
-                "WHERE Name = %s AND Username = %s", [pkgbase, user])
+    conn = db.Connection()
+
+    cur = conn.execute("SELECT COUNT(*) FROM PackageBases " +
+                       "LEFT JOIN PackageComaintainers " +
+                       "ON PackageComaintainers.PackageBaseID = PackageBases.ID " +
+                       "INNER JOIN Users " +
+                       "ON Users.ID = PackageBases.MaintainerUID " +
+                       "OR PackageBases.MaintainerUID IS NULL " +
+                       "OR Users.ID = PackageComaintainers.UsersID " +
+                       "WHERE Name = ? AND Username = ?", [pkgbase, user])
     return cur.fetchone()[0] > 0