Issue 395 - Filter hits statistics backend

sitescripts/filterhits/db.py

 # coding: utf-8
 
 # This file is part of the Adblock Plus web scripts,
-# Copyright (C) 2006-2014 Eyeo GmbH
+# Copyright (C) 2006-2015 Eyeo GmbH
 #
 # Adblock Plus is free software: you can redistribute it and/or modify
 # it under the terms of the GNU General Public License version 3 as
 # published by the Free Software Foundation.
 #
 # Adblock Plus is distributed in the hope that it will be useful,
 # but WITHOUT ANY WARRANTY; without even the implied warranty of
 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 # GNU General Public License for more details.
 #
 # You should have received a copy of the GNU General Public License
 # along with Adblock Plus.  If not, see <http://www.gnu.org/licenses/>.
 
-import MySQLdb
-
-db = None
+import itertools, MySQLdb
 
 def connect(user, password, database):
-  global db
-  if not db:
-    db = MySQLdb.connect(
-      user=user,
-      passwd=password,
-      db=database,
-      use_unicode=True, charset="utf8"
-    )
-  return db
+  return MySQLdb.connect(
+    user=user,
+    passwd=password,
+    db=database,
+    use_unicode=True, charset="utf8"
+  )
 
-def disconnect():
-  global db
-  if db:
-    db.close()
-    db = None
+def query(db, sql, *params, **kwargs):
+  """
+  Executes the query given by the provided SQL and returns the results.
+  If dict_result keyword argument is provided + True the results will be
+  returned as a tuple of dictionaries, otherwise a tuple of tuples.
+  """
+  dict_result=kwargs.pop('dict_result', False)
 
-def escape(s):
-  return MySQLdb.escape_string(s)
-
-def query(sql, dict_result=False):
-  """
-   Executes the query given by the provided SQL and returns the results.
-   If dict_result keyword argument is provided + True the results will be
-   returned as a tuple of dictionaries, otherwise a tuple of tuples.
-  """
-  global db
   try:
     if dict_result:
       cursor = db.cursor(MySQLdb.cursors.DictCursor)
     else:
       cursor = db.cursor()
-    cursor.execute(sql)
+    cursor.execute(sql, params)
     results = cursor.fetchall()
   finally:
     if cursor:
       cursor.close()
   return results
 
-def write(sql):
+def write(db, queries):
   """
-    This writes a given SQL string or iterator of SQL strings to the database.
-    All given SQL will be run as one transaction and rolled back on error.
+  This writes a given SQL string or iterator of tuples containing SQL

[Sebastian Noack, 2015/02/17 14:59:17]

Iterators (in Python) are objects that implement the iterator protocol, i.e. the
next() method. When using for-loops, Python internally calls iter(obj) to create
an iterator, however the object passed to this function isn't an iterator
itself, it's merely an iterable object.

[kzar, 2015/02/24 18:05:11]

Done.

+  strings and any required parameters to the database. All queries will
+  be run as one transaction and rolled back on error.
   """
-  global db
-
-  if isinstance(sql, str):
-    sql = [sql]
+  if isinstance(queries, str):
+    queries = ((queries,),)
 
   try:
-    # Commit the changes
     cursor = db.cursor()
-    for query in sql:
-      if query:
-        [cursor.execute(s) for s in query.split(";") if s]
-    db.commit()
+    try:
+      for query in queries:
+        sql, params = query[0], query[1:]

[Sebastian Noack, 2015/02/17 14:59:17]

I'd prefer to use a two-dimensional tuple here and everywhere else like (sql,
params) rather than prepanding the sql to the params. This makes it easier to
understand the data structure, is more efficient, and reduces complexity.

[kzar, 2015/02/24 18:05:11]

I like doing it this way for db.query and db.write personally as it reduces the
boilerplate when using the functions with zero or one parameters. I originally
did it in a way similar to how you suggested but found lots of calls where I was
passing empty or 1 item tuples - it seemed kind of ugly.

[Sebastian Noack, 2015/02/26 16:39:25]

I don't agree. So I am leaving it up to Wladimir.

+        cursor.execute(sql, params)
+      db.commit()
+    finally:
+      cursor.close()
   except MySQLdb.Error:
-    # On error roll them back
     if db:
       db.rollback()
     raise
-  finally:
-    if cursor:
-      cursor.close()