Setting tag plcapi-5.4-2

[plcapi.git] / PLC / PostgreSQL.py

diff --git a/PLC/PostgreSQL.py b/PLC/PostgreSQL.py
index 5c12257..e04b02b 100644 (file)
--- a/PLC/PostgreSQL.py
+++ b/PLC/PostgreSQL.py
@@ -1,12 +1,10 @@
 #
 #

-# PostgreSQL database interface. Sort of like DBI(3) (Database
-# independent interface for Perl).
+# PostgreSQL database interface. 
+# Sort of like DBI(3) (Database independent interface for Perl).

 #
 # Mark Huang <mlhuang@cs.princeton.edu>
 # Copyright (C) 2006 The Trustees of Princeton University
 #
 #
 # Mark Huang <mlhuang@cs.princeton.edu>
 # Copyright (C) 2006 The Trustees of Princeton University
 #

-# $Id$
-#

 
 import psycopg2
 import psycopg2.extensions
 
 import psycopg2
 import psycopg2.extensions


@@ -14,68 +12,41 @@ psycopg2.extensions.register_type(psycopg2.extensions.UNICODE)
 # UNICODEARRAY not exported yet
 psycopg2.extensions.register_type(psycopg2._psycopg.UNICODEARRAY)
 
 # UNICODEARRAY not exported yet
 psycopg2.extensions.register_type(psycopg2._psycopg.UNICODEARRAY)
 

-import pgdb
+import types

 from types import StringTypes, NoneType
 import traceback
 import commands
 import re
 from pprint import pformat
 
 from types import StringTypes, NoneType
 import traceback
 import commands
 import re
 from pprint import pformat
 

-from PLC.Debug import profile, log
+from PLC.Logger import logger
+from PLC.Debug import profile

 from PLC.Faults import *
 from PLC.Faults import *

-
-if not psycopg2:
-    is8bit = re.compile("[\x80-\xff]").search
-
-    def unicast(typecast):
-        """
-        pgdb returns raw UTF-8 strings. This function casts strings that
-        appear to contain non-ASCII characters to unicode objects.
-        """
-    
-        def wrapper(*args, **kwds):
-            value = typecast(*args, **kwds)
-
-            # pgdb always encodes unicode objects as UTF-8 regardless of
-            # the DB encoding (and gives you no option for overriding
-            # the encoding), so always decode 8-bit objects as UTF-8.
-            if isinstance(value, str) and is8bit(value):
-                value = unicode(value, "utf-8")
-
-            return value
-
-        return wrapper
-
-    pgdb.pgdbTypeCache.typecast = unicast(pgdb.pgdbTypeCache.typecast)
+from datetime import datetime as DateTimeType

 
 class PostgreSQL:
     def __init__(self, api):
         self.api = api
         self.debug = False
 
 class PostgreSQL:
     def __init__(self, api):
         self.api = api
         self.debug = False

+#        self.debug = True

         self.connection = None
 
     def cursor(self):
         if self.connection is None:
             # (Re)initialize database connection
         self.connection = None
 
     def cursor(self):
         if self.connection is None:
             # (Re)initialize database connection

-            if psycopg2:
-                try:
-                    # Try UNIX socket first
-                    self.connection = psycopg2.connect(user = self.api.config.PLC_DB_USER,
-                                                       password = self.api.config.PLC_DB_PASSWORD,
-                                                       database = self.api.config.PLC_DB_NAME)
-                except psycopg2.OperationalError:
-                    # Fall back on TCP
-                    self.connection = psycopg2.connect(user = self.api.config.PLC_DB_USER,
-                                                       password = self.api.config.PLC_DB_PASSWORD,
-                                                       database = self.api.config.PLC_DB_NAME,
-                                                       host = self.api.config.PLC_DB_HOST,
-                                                       port = self.api.config.PLC_DB_PORT)
-                self.connection.set_client_encoding("UNICODE")
-            else:
-                self.connection = pgdb.connect(user = self.api.config.PLC_DB_USER,
-                                               password = self.api.config.PLC_DB_PASSWORD,
-                                               host = "%s:%d" % (api.config.PLC_DB_HOST, api.config.PLC_DB_PORT),
-                                               database = self.api.config.PLC_DB_NAME)
+            try:
+                # Try UNIX socket first
+                self.connection = psycopg2.connect(user = self.api.config.PLC_DB_USER,
+                                                   password = self.api.config.PLC_DB_PASSWORD,
+                                                   database = self.api.config.PLC_DB_NAME)
+            except psycopg2.OperationalError:
+                # Fall back on TCP
+                self.connection = psycopg2.connect(user = self.api.config.PLC_DB_USER,
+                                                   password = self.api.config.PLC_DB_PASSWORD,
+                                                   database = self.api.config.PLC_DB_NAME,
+                                                   host = self.api.config.PLC_DB_HOST,
+                                                   port = self.api.config.PLC_DB_PORT)
+            self.connection.set_client_encoding("UNICODE")

 
         (self.rowcount, self.description, self.lastrowid) = \
                         (None, None, None)
 
         (self.rowcount, self.description, self.lastrowid) = \
                         (None, None, None)


@@ -87,20 +58,52 @@ class PostgreSQL:
             self.connection.close()
             self.connection = None
 
             self.connection.close()
             self.connection = None
 

+    @staticmethod
+    # From pgdb, and simplify code
+    def _quote(x):
+        if isinstance(x, DateTimeType):
+            x = str(x)
+        elif isinstance(x, unicode):
+            x = x.encode( 'utf-8' )
+    
+        if isinstance(x, types.StringType):
+            x = "'%s'" % str(x).replace("\\", "\\\\").replace("'", "''")
+        elif isinstance(x, (types.IntType, types.LongType, types.FloatType)):
+            pass
+        elif x is None:
+            x = 'NULL'
+        elif isinstance(x, (types.ListType, types.TupleType, set)):
+            x = 'ARRAY[%s]' % ', '.join(map(lambda x: str(_quote(x)), x))
+        elif hasattr(x, '__pg_repr__'):
+            x = x.__pg_repr__()
+        else:
+            raise PLCDBError, 'Cannot quote type %s' % type(x)
+        return x
+
+

     def quote(self, value):
         """
         Returns quoted version of the specified value.
         """
     def quote(self, value):
         """
         Returns quoted version of the specified value.
         """

-
-        # The pgdb._quote function is good enough for general SQL
-        # quoting, except for array types.
-        if isinstance(value, (list, tuple, set)):
-            return "ARRAY[%s]" % ", ".join(map, self.quote, value)
-        else:
-            return pgdb._quote(value)
-
-    quote = classmethod(quote)
-
+        return PostgreSQL._quote (value)
+
+# following is an unsuccessful attempt to re-use lib code as much as possible
+#    def quote(self, value):
+#        # The pgdb._quote function is good enough for general SQL
+#        # quoting, except for array types.
+#        if isinstance (value, (types.ListType, types.TupleType, set)):
+#            'ARRAY[%s]' % ', '.join( [ str(self.quote(x)) for x in value ] )
+#        else:
+#            try:
+#                # up to PyGreSQL-3.x, function was pgdb._quote
+#                import pgdb
+#                return pgdb._quote(value)
+#            except:
+#                # with PyGreSQL-4.x, use psycopg2's adapt
+#                from psycopg2.extensions import adapt
+#                return adapt (value)
+
+    @classmethod

     def param(self, name, value):
         # None is converted to the unquoted string NULL
         if isinstance(value, NoneType):
     def param(self, name, value):
         # None is converted to the unquoted string NULL
         if isinstance(value, NoneType):


@@ -117,8 +120,6 @@ class PostgreSQL:
 
         return '%(' + name + ')' + conversion
 
 
         return '%(' + name + ')' + conversion
 

-    param = classmethod(param)
-

     def begin_work(self):
         # Implicit in pgdb.connect()
         pass
     def begin_work(self):
         # Implicit in pgdb.connect()
         pass


@@ -135,13 +136,13 @@ class PostgreSQL:
         return self.rowcount
 
     def next_id(self, table_name, primary_key):
         return self.rowcount
 
     def next_id(self, table_name, primary_key):

-       sequence = "%(table_name)s_%(primary_key)s_seq" % locals()      
-       sql = "SELECT nextval('%(sequence)s')" % locals()
-       rows = self.selectall(sql, hashref = False)
-       if rows: 
-           return rows[0][0]
-               
-       return None 
+        sequence = "%(table_name)s_%(primary_key)s_seq" % locals()
+        sql = "SELECT nextval('%(sequence)s')" % locals()
+        rows = self.selectall(sql, hashref = False)
+        if rows:
+            return rows[0][0]
+
+        return None

 
     def last_insert_id(self, table_name, primary_key):
         if isinstance(self.lastrowid, int):
 
     def last_insert_id(self, table_name, primary_key):
         if isinstance(self.lastrowid, int):


@@ -153,25 +154,45 @@ class PostgreSQL:
 
         return None
 
 
         return None
 

+    # modified for psycopg2-2.0.7
+    # executemany is undefined for SELECT's
+    # see http://www.python.org/dev/peps/pep-0249/
+    # accepts either None, a single dict, a tuple of single dict - in which case it execute's
+    # or a tuple of several dicts, in which case it executemany's

     def execute(self, query, params = None):
     def execute(self, query, params = None):

-        return self.execute_array(query, (params,))

 
 

-    def execute_array(self, query, param_seq):

         cursor = self.cursor()
         try:
         cursor = self.cursor()
         try:

-            if self.debug:
-                for params in param_seq:
-                    if params:
-                        print >> log, query % params
-                    else:
-                        print >> log, query

 
             # psycopg2 requires %()s format for all parameters,
             # regardless of type.
 
             # psycopg2 requires %()s format for all parameters,
             # regardless of type.

+            # this needs to be done carefully though as with pattern-based filters
+            # we might have percents embedded in the query
+            # so e.g. GetPersons({'email':'*fake*'}) was resulting in .. LIKE '%sake%'

             if psycopg2:
                 query = re.sub(r'(%\([^)]*\)|%)[df]', r'\1s', query)
             if psycopg2:
                 query = re.sub(r'(%\([^)]*\)|%)[df]', r'\1s', query)

-
-            cursor.executemany(query, param_seq)
+            # rewrite wildcards set by Filter.py as '***' into '%'
+            query = query.replace ('***','%')
+
+            if not params:
+                if self.debug:
+                    logger.debug('execute0: {}'.format(query))
+                cursor.execute(query)
+            elif isinstance(params, dict):
+                if self.debug:
+                    logger.debug('execute-dict: params {} query {}'
+                                 .format(params, query%params))
+                cursor.execute(query, params)
+            elif isinstance(params,tuple) and len(params)==1:
+                if self.debug:
+                    logger.debug('execute-tuple {}'.format(query%params[0]))
+                cursor.execute(query,params[0])
+            else:
+                param_seq=(params,)
+                if self.debug:
+                    for params in param_seq:
+                        logger.debug('executemany {}'.format(query%params))
+                cursor.executemany(query, param_seq)

             (self.rowcount, self.description, self.lastrowid) = \
                             (cursor.rowcount, cursor.description, cursor.lastrowid)
         except Exception, e:
             (self.rowcount, self.description, self.lastrowid) = \
                             (cursor.rowcount, cursor.description, cursor.lastrowid)
         except Exception, e:


@@ -180,12 +201,8 @@ class PostgreSQL:
             except:
                 pass
             uuid = commands.getoutput("uuidgen")
             except:
                 pass
             uuid = commands.getoutput("uuidgen")

-            print >> log, "Database error %s:" % uuid
-            print >> log, e
-            print >> log, "Query:"
-            print >> log, query
-            print >> log, "Params:"
-            print >> log, pformat(param_seq[0])
+            message = "Database error {}: - Query {} - Params {}".format(uuid, query, pformat(params))
+            logger.exception(message)

             raise PLCDBError("Please contact " + \
                              self.api.config.PLC_NAME + " Support " + \
                              "<" + self.api.config.PLC_MAIL_SUPPORT_ADDRESS + ">" + \
             raise PLCDBError("Please contact " + \
                              self.api.config.PLC_NAME + " Support " + \
                              "<" + self.api.config.PLC_MAIL_SUPPORT_ADDRESS + ">" + \


@@ -207,7 +224,7 @@ class PostgreSQL:
         cursor = self.execute(query, params)
         rows = cursor.fetchall()
         cursor.close()
         cursor = self.execute(query, params)
         rows = cursor.fetchall()
         cursor.close()

-
+        self.commit()

         if hashref or key_field is not None:
             # Return each row as a dictionary keyed on field name
             # (like DBI selectrow_hashref()).
         if hashref or key_field is not None:
             # Return each row as a dictionary keyed on field name
             # (like DBI selectrow_hashref()).