[Zope-CVS] CVS: Products/Ape/lib/apelib/sql - classification.py:1.7 dbapi.py:1.5 interfaces.py:1.2 oidgen.py:1.3 properties.py:1.7 security.py:1.6 sqlbase.py:1.11 structure.py:1.10 querygen.py:NONE

Shane Hathaway shane at zope.com
Thu Mar 11 00:59:10 EST 2004


Update of /cvs-repository/Products/Ape/lib/apelib/sql
In directory cvs.zope.org:/tmp/cvs-serv13608/sql

Modified Files:
	classification.py dbapi.py interfaces.py oidgen.py 
	properties.py security.py sqlbase.py structure.py 
Removed Files:
	querygen.py 
Log Message:
Ape's sql package now uses a smarter connection class and no query generator.

The query generator could not adapt very well to the different 
approaches databases take.  It separated the SQL generation too far from 
the execution, forcing every database to fit a common pattern.  This 
means that PostgreSQL support was easy, while MySQL was harder, and 
other databases might have been worse.

Now, the connection class implements a fairly simple interface for 
talking to the database.  The connection is now free to use multiple 
statements or shortcuts.  Also, the DB-API module registry is no longer 
needed.



=== Products/Ape/lib/apelib/sql/classification.py 1.6 => 1.7 ===
--- Products/Ape/lib/apelib/sql/classification.py:1.6	Mon Feb  2 10:07:21 2004
+++ Products/Ape/lib/apelib/sql/classification.py	Thu Mar 11 00:58:38 2004
@@ -26,19 +26,18 @@
     __implements__ = SQLGatewayBase.__implements__
 
     schema = FieldSchema('classification', 'classification')
-
-    table_base_name = 'classification'
-
+    table = 'classification'
     column_defs = (
         ('class_name', 'string', 0),
         ('mapper_name', 'string', 0),
         )
 
     def load(self, event):
-        items = self.execute(event, 'read', 1, oid=event.oid)
+        conn = self.getConnection(event)
+        rows = conn.select(self.table, self.columns, oid=event.oid)
         classification = {}
-        if items:
-            rec = items[0]
+        if rows:
+            rec = rows[0]
             if rec[0]:
                 classification['class_name'] = rec[0]
             if rec[1]:
@@ -48,16 +47,11 @@
         return classification, rec
 
     def store(self, event, classification):
-        items = self.execute(event, 'read', 1, oid=event.oid)
-        if items and event.is_new:
-            # Something is already stored at this OID.
+        conn = self.getConnection(event)
+        row = (classification.get('class_name', ''),
+               classification.get('mapper_name', ''))
+        try:
+            conn.setOne(self.table, event.oid, self.columns, row, event.is_new)
+        except conn.module.IntegrityError:
             raise OIDConflictError(event.oid)
-        cn = classification.get('class_name', '')
-        mn = classification.get('mapper_name', '')
-        kw = {'oid': event.oid, 'class_name': cn, 'mapper_name': mn}
-        if items:
-            self.execute(event, 'update', **kw)
-        else:
-            self.execute(event, 'insert', **kw)
-        return (cn, mn)
-
+        return row


=== Products/Ape/lib/apelib/sql/dbapi.py 1.4 => 1.5 ===
--- Products/Ape/lib/apelib/sql/dbapi.py:1.4	Mon Aug 11 13:51:38 2003
+++ Products/Ape/lib/apelib/sql/dbapi.py	Thu Mar 11 00:58:38 2004
@@ -1,6 +1,6 @@
 ##############################################################################
 #
-# Copyright (c) 2003 Zope Corporation and Contributors.
+# Copyright (c) 2004 Zope Corporation and Contributors.
 # All Rights Reserved.
 #
 # This software is subject to the provisions of the Zope Public License,
@@ -11,115 +11,263 @@
 # FOR A PARTICULAR PURPOSE.
 #
 ##############################################################################
-"""DB-API to Ape gateway connectivity
+"""SQL database connections via DB-API 2.0.
 
 $Id$
 """
 
 import os
+import re
 from time import time
-from types import StringType
 
 from apelib.core.interfaces import ITPCConnection
-from apelib.sql.interfaces import ISQLConnection
-from querygen import makeQueryGenerator
+
+from apelib.sql.interfaces import IRDBMSConnection
+
+name_style_re = re.compile(':[A-Za-z0-9_-]+')
 
 DEBUG = os.environ.get('APE_DEBUG_SQL')
 PROFILE = os.environ.get('APE_PROFILE_SQL')
 
 
-class DBAPIConnector:
+class AbstractSQLConnection:
 
-    __implements__ = ITPCConnection, ISQLConnection
+    __implements__ = IRDBMSConnection, ITPCConnection
 
-    _final = 0
-    db = None
-    cursor = None
+    column_type_translations = {}  # { local type name -> db type name }
+    column_name_translations = {}  # { local col name -> db col name }
 
-    def __init__(self, module_name, params=(), kwparams='', prefix='zodb'):
+    def __init__(self, module_name, connect_args, prefix='zodb_'):
+        # connect_args is a Python expression.
         self.module_name = module_name
         self.module = __import__(module_name, {}, {}, ('__doc__',))
-        self.error = self.module.DatabaseError
+        self.connect_args = connect_args
         self.prefix = prefix
-        self.connect_callbacks = []
-        if isinstance(kwparams, StringType):
-            # Convert the keyword string to a dictionary.
+        self.connector = None
+        self._final = 0
+
+    def __repr__(self):
+        return '<%s(%s(%s))>' % (
+            self.__class__.__name__, self.module_name, self.connect_args)
+
+    def translateName(self, column_name):
+        """Returns a column name for a variable name.
+
+        Defaults to no translation.
+        """
+        return self.column_name_translations.get(column_name, column_name)
+
+    def translateType(self, column_type):
+        """Returns a database type for a variable type.
+
+        If the type is unknown, raises KeyError.
+        """
+        return self.column_type_translations.get(column_type, column_type)
+
+    def generateConditions(self, keys):
+        conditions = []
+        for key in keys:
+            clause = "%s = :%s" % (self.translateName(key), key)
+            conditions.append(clause)
+        return ' AND '.join(conditions)
+
+    def generateInsert(self, table_name, columns):
+        dbcols = [self.translateName(col) for col in columns]
+        colfmts = [':%s' % col for col in columns]
+        return 'INSERT INTO %s (%s) VALUES (%s)' % (
+            table_name, ', '.join(dbcols), ', '.join(colfmts))
+
+    def generateUpdate(self, table_name, key_columns, other_columns):
+        where = self.generateConditions(key_columns)
+        to_set = [
+            ("%s = :%s" % (self.translateName(col), col))
+            for col in other_columns]
+        return 'UPDATE %s SET %s WHERE %s' % (
+            table_name, ', '.join(to_set), where)
+
+    def generateDelete(self, table_name, keys):
+        where = self.generateConditions(keys)
+        sql = 'DELETE FROM %s' % table_name
+        if where:
+            sql += ' WHERE %s' % where
+        return sql
+
+    def makeDict(self, columns, data, oid=None):
+        res = {}
+        for n in range(len(columns)):
+            res[columns[n]] = data[n]
+        if oid is not None:
+            res['oid'] = oid
+        return res
+
+    #
+    # IRDBMSConnection implementation.
+    #
+
+    def select(self, table, result_columns, **filter):
+        """Selects rows from a table and returns column values for those rows.
+        """
+        table_name = self.prefix + table
+        dbcols = [self.translateName(col) for col in result_columns]
+        where = self.generateConditions(filter.keys())
+        sql = 'SELECT %s FROM %s WHERE %s' % (
+            ', '.join(dbcols), table_name, where)
+        return self.execute(sql, filter, fetch=1)
+
+    def setOne(self, table, oid, columns, row, is_new):
+        """Sets one row in a table.
+
+        Requires the table to have only one value for each oid.
+        Executes either an update or insert operation, depending on
+        the is_new argument and configured policies.
+        """
+        table_name = self.prefix + table
+        kw = self.makeDict(columns, row, oid)
+        if is_new:
+            sql = self.generateInsert(table_name, ('oid',) + tuple(columns))
+            self.execute(sql, kw)
+        else:
+            sql = self.generateUpdate(table_name, ('oid',), columns)
+            self.execute(sql, kw)
+            
+    def setMany(self, table, oid, key_columns, other_columns, rows):
+        """Sets multiple rows in a table.
+
+        'rows' is a sequence of tuples containing values for the
+        key_columns as well as the other_columns.
+
+        Either deletes all rows for an oid and inserts new rows, or
+        examines the current state of the database and modifies it in
+        pieces.
+        """
+        table_name = self.prefix + table
+        combo = tuple(key_columns) + tuple(other_columns)
+        if not key_columns:
+            # Don't compare rows.  Just delete and insert.
+            sql = self.generateDelete(table_name, ('oid',))
+            self.execute(sql, {'oid': oid})
+            for row in rows:
+                sql = self.generateInsert(table_name, ('oid',) + combo)
+                kw = self.makeDict(combo, row, oid)
+                self.execute(sql, kw)
+            return
+        # Edit the table.
+        exist_rows = self.select(table, combo, oid=oid)
+        count = len(key_columns)
+        existing = {}
+        for record in exist_rows:
+            key = tuple(record[:count])
+            value = tuple(record[count:])
+            existing[key] = value
+        now = {}
+        for record in rows:
+            key = tuple(record[:count])
+            value = tuple(record[count:])
+            now[key] = value
+        # Delete and update rows.
+        for key, value in existing.items():
+            if not now.has_key(key):
+                # Delete this row.
+                sql = self.generateDelete(
+                    table_name, ('oid',) + tuple(key_columns))
+                kw = self.makeDict(key_columns, key, oid)
+                self.execute(sql, kw)
+            elif now[key] != value:
+                # Update this row.
+                #print 'DIFFERENT:', now[key], value
+                sql = self.generateUpdate(
+                    table_name, ('oid',) + tuple(key_columns), other_columns)
+                kw = self.makeDict(combo, key + now[key], oid)
+                self.execute(sql, kw)
+        for key, value in now.items():
+            if not existing.has_key(key):
+                # Insert this row.
+                sql = self.generateInsert(table_name, ('oid',) + combo)
+                kw = self.makeDict(combo, key + value, oid)
+                self.execute(sql, kw)
+        return
+
+    def deleteFrom(self, table, **filter):
+        """Deletes rows from a table.
+        """
+        table_name = self.prefix + table
+        sql = self.generateDelete(table_name, filter.keys())
+        self.execute(sql, filter)
+
+    def exists(self, name, type_name):
+        """Returns true if the specified database object exists.
+
+        type_name is 'table' or 'sequence'
+        """
+        raise NotImplementedError("Abstract Method")
+
+    def createTable(self, table, column_defs):
+        """Creates a table.
+        """
+        table_name = self.prefix + table
+        cols = []
+        pkeys = []
+        for name, typ, unique in column_defs:
+            col = self.translateName(name)
+            db_type = self.translateType(typ)
+            cols.append("%s %s" % (col, db_type))
+            if unique:
+                pkeys.append(col)
+        if pkeys:
+            cols.append('PRIMARY KEY (%s)' % ', '.join(pkeys))
+        sql = "CREATE TABLE %s (%s)" % (table_name, ', '.join(cols))
+        self.execute(sql)
+
+    def createSequence(self, name, start=1):
+        """Creates a sequence.
+        """
+        raise NotImplementedError("Abstract Method")
+
+    def resetSequence(self, name, start=1):
+        """Resets a sequence.
+        """
+        raise NotImplementedError("Abstract Method")
+
+    def increment(self, name):
+        """Increments a sequence.
+        """
+        raise NotImplementedError("Abstract Method")
+
+    def execute(self, sql, kw=None, fetch=0):
+        if self.connector is None:
+            raise RuntimeError('Not connected')
+        if kw is None:
             kw = {}
-            for p in kwparams.split():
-                k, v = p.split(':')
-                kw[k.strip()] = v.strip()
-            kwparams = kw
-        self.kwparams = kwparams
-        if isinstance(params, StringType):
-            params = (params,)
-        self.params = params
-        self.query_gens = {}      # { (table, columns) -> QueryGenerator }
-        self.query_cache = {}     # { (table, columns, operation) -> query }
-
-    def isConnected(self):
-        return self.db is not None
-
-    # ISQLConnection implementation
-
-    def getQuery(self, table_name, column_defs, operation):
-        query = self.query_cache.get((table_name, column_defs, operation))
-        if query is not None:
-            return query
-        gen = self.query_gens.get((table_name, column_defs))
-        if gen is None:
-            if self.prefix:
-                real_table_name = '%s_%s' % (self.prefix, table_name)
-            else:
-                real_table_name = table_name
-            gen = makeQueryGenerator(
-                self.module_name, real_table_name, column_defs)
-            self.query_gens[(table_name, column_defs)] = gen
-        query = gen.generate(operation)
-        self.query_cache[(table_name, column_defs, operation)] = query
-        return query
-
-    def execute(self, query, fetch=0, cursor=None, _data=None, **kw):
-        if cursor is None:
-            cursor = self.cursor
-            if cursor is None:
-                raise RuntimeError('Not connected')
-        if _data is not None:
-            # Callers can pass _data instead of keyword arguments
-            # to avoid argument name conflicts.
-            kw.update(_data)
-        if DEBUG or PROFILE:
-            if kw:
-                print 'Query: %s, %s' % (repr(query), kw)
+        converter = style_converters[self.module.paramstyle]
+        sql, params = converter(sql, kw)
+        cursor = self.connector.cursor()
+        try:
+            if DEBUG or PROFILE:
+                print 'SQL: %s, %s' % (repr(sql), params)
+            if PROFILE:
+                start = time()
+                cursor.execute(sql, params)
+                end = time()
+                print 'SQL time: %0.6fs' % (end - start)
             else:
-                print 'Query: %s' % repr(query)
-        if PROFILE:
-            start = time()
-            cursor.execute(query, kw)
-            end = time()
-            print 'Query time: %0.6fs' % (end - start)
-        else:
-            cursor.execute(query, kw)
-        if fetch:
-            res = list(cursor.fetchall())
-            if DEBUG:
-                print 'Query result: %s' % repr(res)
-            return res
-        return None
-
-    def asBinary(self, data):
-        return self.module.Binary(data)
+                cursor.execute(sql, params)
+            if fetch:
+                res = list(cursor.fetchall())
+                if DEBUG:
+                    print 'SQL result: %s' % repr(res)
+                return res
+        finally:
+            cursor.close()
 
-    # ITPCConnection implementation
+    #
+    # ITPCConnection implementation.
+    #
 
     def connect(self):
-        if self.kwparams:
-            self.db = self.module.connect(*self.params, **self.kwparams)
-        else:
-            self.db = self.module.connect(*self.params)
-        self.cursor = self.db.cursor()
-        for f in self.connect_callbacks:
-            f()
-        self.connect_callbacks = []
+        d = {'connect': self.module.connect}
+        code = "connector = connect(%s)" % self.connect_args
+        exec code in d
+        self.connector = d['connector']
 
     def sortKey(self):
         return repr(self)
@@ -138,21 +286,182 @@
 
     def abort(self):
         try:
-            self.db.rollback()
+            self.connector.rollback()
         finally:
             self.reset()
 
     def finish(self):
         if self._final:
             try:
-                self.db.commit()
+                self.connector.commit()
             finally:
                 self.reset()
 
     def close(self):
-        if self.isConnected():
-            self.cursor.close()
-            self.cursor = None
-            self.db.close()
-            self.db = None
+        c = self.connector
+        if c is not None:
+            self.connector = None
+            c.close()
+
+
+
+# Converters for all parameter styles defined by DB-API 2.0
+
+style_converters = {}
+
+def convert_to_qmark(sql, kw):
+    # '?' format
+    params = []
+    def replace(match, params=params, kw=kw):
+        name = match.group()[1:]
+        params.append(kw[name])
+        return '?'
+    sql = name_style_re.sub(replace, sql)
+    return sql, tuple(params)
+style_converters['qmark'] = convert_to_qmark
+
+def convert_to_numeric(sql, kw):
+    # ':1' format
+    params = []
+    def replace(match, params=params, kw=kw):
+        name = match.group()[1:]
+        index = len(params)
+        params.append(kw[name])
+        return ':%d' % index
+    sql = name_style_re.sub(replace, sql)
+    return sql, tuple(params)
+style_converters['numeric'] = convert_to_numeric
+
+def convert_to_named(sql, kw):
+    # ':name' format
+    # The input format is the same as the output format.
+    return sql, kw
+style_converters['named'] = convert_to_named
+
+def convert_to_format(sql, kw):
+    # '%s' format
+    params = []
+    def replace(match, params=params, kw=kw):
+        name = match.group()[1:]
+        params.append(kw[name])
+        return '%s'
+    sql = name_style_re.sub(replace, sql)
+    return sql, tuple(params)
+style_converters['format'] = convert_to_format
+
+def convert_to_pyformat(sql, kw):
+    # '%(name)s' format
+    def replace(match, kw=kw):
+        name = match.group()[1:]
+        return '%%(%s)s' % name
+    sql = name_style_re.sub(replace, sql)
+    return sql, kw
+style_converters['pyformat'] = convert_to_pyformat
+
+
+# Database-specific implementations of IRDBMSConnection.
+
+class PostgreSQLConnection (AbstractSQLConnection):
+
+    column_type_translations = {
+        'long':   'bigint',
+        'string': 'character varying(255)',
+        'blob':   'bytea',
+        'date_international': 'date',
+        }
+
+    column_name_translations = {
+        'oid': 'objoid',
+        }
+
+    def exists(self, name, type_name):
+        """Returns true if the specified database object exists.
+
+        type_name is 'table' or 'sequence'
+        """
+        name = self.prefix + name
+        if type_name == 'table':
+            sql = ('SELECT tablename FROM pg_tables '
+                   'WHERE tablename = :name')
+        elif type_name == 'sequence':
+            sql = ("SELECT relname FROM pg_class "
+                   "WHERE relkind = 'S' and relname = :name")
+        else:
+            raise ValueError(type_name)
+        rows = self.execute(sql, {'name': name}, fetch=1)
+        return len(rows)
+
+    def createSequence(self, name, start=1):
+        """Creates a sequence.
+        """
+        sql = "CREATE SEQUENCE %s START %d" % (self.prefix + name, start)
+        self.execute(sql)
+
+    def resetSequence(self, name, start=1):
+        """Resets a sequence.
+        """
+        sql = "SELECT setval('%s', %d)" % (self.prefix + name, start)
+        self.execute(sql)
+
+    def increment(self, name):
+        """Increments a sequence.
+        """
+        sql = "SELECT nextval('%s')" % (self.prefix + name)
+        rows = self.execute(sql, fetch=1)
+        return rows[0][0]
 
+
+
+class MySQLConnection (AbstractSQLConnection):
+
+    column_type_translations = {
+        'long':   'bigint',
+        'string': 'character varying(255)',
+        'blob':   'longblob',
+        'date_international': 'date',
+        }
+
+    column_name_translations = {
+        'oid': 'objoid',
+        }
+
+    def exists(self, name, type_name):
+        """Returns true if the specified database object exists.
+
+        type_name is 'table' or 'sequence'
+        """
+        # XXX TODO
+        name = self.prefix + name
+        if type_name == 'table':
+            sql = ('SELECT tablename FROM pg_tables '
+                   'WHERE tablename = :name')
+        elif type_name == 'sequence':
+            sql = ("SELECT relname FROM pg_class "
+                   "WHERE relkind = 'S' and relname = :name")
+        else:
+            raise ValueError(type_name)
+        rows = self.execute(sql, {'name': name}, fetch=1)
+        return len(rows)
+
+    def createSequence(self, name, start=1):
+        """Creates a sequence.
+        """
+        table_name = self.prefix + name
+        self.execute("CREATE TABLE %s (last_value int)" % self.table_name)
+        self.execute("INSERT INTO %s VALUES (%d)" % (self.table_name, start))
+
+    def resetSequence(self, name, start=1):
+        """Resets a sequence.
+        """
+        table_name = self.prefix + name
+        self.execute("UPDATE %s SET last_value=0" % table_name)
+
+    def increment(self, name):
+        """Increments a sequence.
+        """
+        table_name = self.prefix + name
+        self.execute(
+            "UPDATE %s SET last_value=LAST_INSERT_ID(last_value+1)" %
+            table_name)
+        rows = self.execute("SELECT LAST_INSERT_ID()", fetch=1)
+        return rows[0][0]


=== Products/Ape/lib/apelib/sql/interfaces.py 1.1 => 1.2 ===
--- Products/Ape/lib/apelib/sql/interfaces.py:1.1	Sun May 18 00:23:14 2003
+++ Products/Ape/lib/apelib/sql/interfaces.py	Thu Mar 11 00:58:38 2004
@@ -18,23 +18,66 @@
 
 from Interface import Interface
 
-class ISQLConnection (Interface):
-    """Interface of SQL database connectors
+class IRDBMSConnection (Interface):
+    """Interface of basic RDBMS connections.
+
+    This interface provides only relatively basic operations.  Create
+    subinterfaces for complex and vendor-specific extensions.
     """
 
-    def getQuery(table_name, column_defs, operation):
-        """Returns a query for invoking the specified operation.
+    # module = Attribute("The DB-API module")
+
+    # connector = Attribute("The shared DB-API connection")
+
+    def select(table, result_columns, **filter):
+        """Selects rows from a table and returns column values for those rows.
+        """
+
+    def setOne(table, oid, columns, row, is_new):
+        """Sets one row in a table.
 
-        The available operations depend on the query generator and the
-        database.
+        Executes either an update or insert operation, depending
+        on the is_new argument and configured policies.
         """
 
-    def execute(query, fetch=0, cursor=None, **kw):
-        """Executes a query.
+    def setMany(table, oid, key_columns, other_columns, rows):
+        """Sets multiple rows in a table.
 
-        If fetch is true, returns all of the rows from the query.
+        'rows' is a sequence of tuples containing values for the
+        key_columns as well as the other_columns.
+
+        Either deletes all rows for an oid and inserts new rows, or
+        examines the current state of the database and modifies it in
+        pieces.
+        """
+
+    def deleteFrom(table, **filter):
+        """Deletes rows from a table.
         """
 
-    def asBinary(data):
-        """Converts a string to the database's binary type."""
+    def exists(name, type_name):
+        """Returns true if the specified database object exists.
+
+        'name' is the name of the object.  'type_name' is 'table' or
+        'sequence'.
+        """
 
+    def createTable(name, column_defs):
+        """Creates a table.
+
+        column_defs is [(name, type, is_unique)].
+        """
+
+    def createSequence(name, start=1):
+        """Creates a sequence.
+        """
+
+    def resetSequence(name, start=1):
+        """Resets a sequence to a starting value.
+        """
+
+    def increment(name):
+        """Increments a sequence and returns the value.
+
+        Whether the value is before or after the increment is not specified.
+        """


=== Products/Ape/lib/apelib/sql/oidgen.py 1.2 => 1.3 ===
--- Products/Ape/lib/apelib/sql/oidgen.py:1.2	Mon Feb  2 10:07:21 2004
+++ Products/Ape/lib/apelib/sql/oidgen.py	Thu Mar 11 00:58:38 2004
@@ -26,27 +26,16 @@
     __implements__ = (interfaces.IOIDGenerator,
                       interfaces.IDatabaseInitializer)
 
-    table_base_name = 'oid_seq'
+    table = 'oid_seq'
     column_defs = ()
-
     root_oid = "0"
 
     def init(self, event):
-        conn = event.connections[self.conn_name]
-        first_time = 0
-        try:
-            rows = self.execute(event, 'sequence_check', 1)
-            if len(rows) == 0:
-                first_time = 1
-        except conn.error:
-            conn.db.rollback()
-            self.execute(event, 'sequence_create')
-            first_time = 1
-        if first_time:
-            self.execute(event, 'sequence_insert')
-        if event.clear_all:
-            self.execute(event, 'sequence_clear')
-        conn.db.commit()
+        conn = self.getConnection(event)
+        if not conn.exists(self.table, 'sequence'):
+            conn.createSequence(self.table, start=1)
+        elif event.clear_all:
+            conn.resetSequence(self.table, start=1)
 
     def new_oid(self, event, name, stored):
         if not stored:
@@ -56,7 +45,6 @@
             # Request that the other side do the work (for ZEO)
             n = event.obj_db.new_oid()
         else:
-            self.execute(event, 'sequence_update')
-            n = self.execute(event, 'sequence_read', 1)[0][0]
+            conn = self.getConnection(event)
+            n = conn.increment(self.table)
         return str(n)
-


=== Products/Ape/lib/apelib/sql/properties.py 1.6 => 1.7 ===
--- Products/Ape/lib/apelib/sql/properties.py:1.6	Mon Feb  2 10:07:21 2004
+++ Products/Ape/lib/apelib/sql/properties.py	Thu Mar 11 00:58:38 2004
@@ -32,8 +32,7 @@
     schema.addField('type', 'string')
     schema.addField('data', 'string')
 
-    table_base_name = 'properties'
-
+    table = 'properties'
     column_defs = (
         ('id', 'string', 1),
         ('type', 'string', 0),
@@ -41,38 +40,15 @@
         )
 
     def load(self, event):
-        items = self.execute(event, 'read', 1, oid=event.oid)
-        items.sort()
-        return items, tuple(items)
+        conn = self.getConnection(event)
+        rows = conn.select(self.table, self.columns, oid=event.oid)
+        rows.sort()
+        return rows, tuple(rows)
 
     def store(self, event, state):
-        items = self.execute(event, 'read', 1, oid=event.oid)
-        state_dict = {}
-        for row in state:
-            id = row[0]
-            state_dict[id] = row
-        items_dict = {}
-        conn = event.connections[self.conn_name]
-        for old_row in items:
-            id = old_row[0]
-            items_dict[id] = old_row
-            state_row = state_dict.get(id)
-            if state_row is None:
-                # Remove a property
-                self.execute(event, 'delete', oid=event.oid, id=id)
-            elif old_row != state_row:
-                # Update a property
-                id, t, v = state_row
-                data = conn.asBinary(v)
-                self.execute(event, 'update',
-                             oid=event.oid, id=id, type=t, data=data)
-        for row in state:
-            if not items_dict.has_key(row[0]):
-                # Add a property
-                id, t, v = row
-                data = conn.asBinary(v)
-                self.execute(event, 'insert',
-                             oid=event.oid, id=id, type=t, data=data)
+        conn = self.getConnection(event)
+        rows = [(id, t, data) for id, t, data in state]
+        conn.setMany(self.table, event.oid, ('id',), ('type', 'data'), rows)
         state = list(state)
         state.sort()
         return tuple(state)
@@ -91,8 +67,9 @@
     """
 
     def __init__(self, conn_name, table_name, cols):
-        self.table_base_name = table_name
+        self.table = table_name
         self.column_defs = cols
+        self.columns =  [name for (name, t, u) in cols]
         self.schema = None
         SQLGatewayBase.__init__(self, conn_name)
 
@@ -101,32 +78,19 @@
         """Creates the fixed property table without triggering an error.
         """
         # Note: event is any kind of IGatewayEvent.
-        conn = event.connections[self.conn_name]
-        if conn.prefix:
-            full_table_name = '%s_%s' % (conn.prefix, self.table_base_name)
-        else:
-            full_table_name = self.table_base_name
-        full_table_name = full_table_name.lower()
-        query = conn.getQuery('', (), 'table_names')
-        recs = conn.execute(query, fetch=1)
-        for rec in recs:
-            if rec[0].lower() == full_table_name:
-                # The table already exists.
-                # TODO: check the table schema.
-                break
-        else:
-            # Create the table.
-            query = conn.getQuery(self.table_base_name,
-                                  self.column_defs, 'create')
-            conn.execute(query)
+        conn = self.getConnection(event)
+        if not conn.exists(self.table, 'table'):
+            defs = (self.oid_column_def,) + self.column_defs
+            conn.createTable(self.table, defs)
 
 
     def load(self, event):
-        recs = self.execute(event, 'read', fetch=1, oid=event.oid)
+        conn = self.getConnection(event)
+        recs = conn.select(self.table, self.columns, oid=event.oid)
         if not recs:
             return (), ()
         if len(recs) > 1:
-            raise ValueError, "Multiple records where only one expected"
+            raise ValueError("Multiple records where only one expected")
         record = recs[0]
         items = []
         cols = self.column_defs
@@ -169,16 +133,15 @@
                 raise ValueError(
                     "Extra properties provided for fixed schema: %s"
                     % statedict.keys())
-        recs = self.execute(event, 'read', fetch=1, oid=event.oid)
-        if not recs:
-            self.execute(event, 'insert', oid=event.oid, _data=data)
-        else:
-            self.execute(event, 'update', oid=event.oid, _data=data)
+        conn = self.getConnection(event)
+        conn.setOne(self.table, event.oid, self.columns, record, event.is_new)
         return tuple(record)
 
 
 
 class SQLMultiTableProperties:
+    """Combines fixed and variable properties.
+    """
 
     __implements__ = IGateway, IDatabaseInitializer
 
@@ -197,23 +160,15 @@
 
     def init(self, event):
         self.var_props.init(event)
+        conn = self.getConnection(event)
         if event.clear_all:
             # Clear the fixed property tables by searching for tables
-            # with a special name.
-            conn = event.connections[self.conn_name]
-            if conn.prefix:
-                to_find = '%s_fp_' % conn.prefix
-            else:
-                to_find = 'fp_'
-            query = conn.getQuery('', (), 'table_names')
-            recs = conn.execute(query, fetch=1)
-            for (full_table_name,) in recs:
-                if full_table_name.startswith(to_find):
-                    table_name = 'fp_' + full_table_name[len(to_find):]
+            # with a special name, 'fp_*'.
+            names = conn.listTableNames()
+            for name in names:
+                if name.startswith('fp_'):
                     # This is a fixed property table.  Clear it.
-                    query = conn.getQuery(table_name, (), 'clear')
-                    conn.execute(query)
-                    conn.db.commit()
+                    conn.delete(self.table_name)
 
 
     def getColumnsForClass(self, module_name, class_name):
@@ -313,4 +268,3 @@
         else:
             var_hash = ()
         return (fixed_hash, var_hash)
-


=== Products/Ape/lib/apelib/sql/security.py 1.5 => 1.6 ===
--- Products/Ape/lib/apelib/sql/security.py:1.5	Mon Feb  2 10:07:21 2004
+++ Products/Ape/lib/apelib/sql/security.py	Thu Mar 11 00:58:38 2004
@@ -31,24 +31,20 @@
     schema.addField('permission', 'string')
     schema.addField('username', 'string')
 
-    table_base_name = 'security'
+    table = 'security'
+    oid_column_def = ('oid', 'int', 0)  # Don't create a primary key
 
     def load(self, event):
-        items = self.execute(event, 'read', 1, oid=event.oid)
+        conn = self.getConnection(event)
+        items = conn.select(self.table, self.columns, oid=event.oid)
         items.sort()
         return items, tuple(items)
 
     def store(self, event, state):
-        items = self.execute(event, 'read', 1, oid=event.oid)
-        items.sort()
+        conn = self.getConnection(event)
+        conn.setMany(self.table, event.oid, (), self.columns, state)
         state = list(state)
         state.sort()
-        if state != items:
-            self.execute(event, 'delete', oid=event.oid)
-            for row in state:
-                self.execute(
-                    event, 'insert', oid=event.oid, declaration_type=row[0],
-                    role=row[1], permission=row[2], username=row[3])
         return tuple(state)
 
 
@@ -65,53 +61,39 @@
     schema.addField('domains', 'string:list')
 
     table_defs = {
-        'users':        (('id', 'string', 1),
+        'users':        (('oid', 'int', 1),
+                         ('id', 'string', 1),
                          ('password', 'string', 0),),
-        'user_roles':   (('id', 'string', 1),
+        'user_roles':   (('oid', 'int', 0),
+                         ('id', 'string', 0),
                          ('role', 'string', 0),),
-        'user_domains': (('id', 'string', 1),
+        'user_domains': (('oid', 'int', 0),
+                         ('id', 'string', 0),
                          ('domain', 'string', 0),),
         }
 
 
     def init(self, event):
-        conn = event.connections[self.conn_name]
-        try:
-            if event.clear_all:
-                self.execute(event, 'users', 'clear')
-                self.execute(event, 'user_roles', 'clear')
-                self.execute(event, 'user_domains', 'clear')
-            else:
-                self.execute(event, 'users', 'check')
-        except conn.error:
-            conn.db.rollback()
-            self.execute(event, 'users', 'create')
-            self.execute(event, 'user_roles', 'create')
-            self.execute(event, 'user_domains', 'create')
-            conn.db.commit()
-
-
-    def execute(self, event, table, operation, *args, **kw):
-        conn = event.connections[self.conn_name]
-        query = conn.getQuery(
-            table, self.table_defs[table], operation)
-        if query == '':
-            # No query needed for this operation
-            return
-        return conn.execute(query, *args, **kw)
+        conn = self.getConnection(event)
+        for table, col_defs in self.table_defs.items():
+            if not conn.exists(table, 'table'):
+                conn.createTable(table, col_defs)
+            elif event.clear_all:
+                conn.deleteFrom(table)
 
 
     def load(self, event):
-        rows = self.execute(event, 'users', 'read', 1, oid=event.oid)
+        conn = self.getConnection(event)
+        rows = conn.select('users', ('id', 'password'), oid=event.oid)
         data = {}
         for id, password in rows:
             data[id] = (password, [], [])
-        rows = self.execute(event, 'user_roles', 'read', 1, oid=event.oid)
+        rows = conn.select('user_roles', ('id', 'role'), oid=event.oid)
         for id, role in rows:
             row = data.get(id)
             if row is not None:
                 row[1].append(role)
-        rows = self.execute(event, 'user_domains', 'read', 1, oid=event.oid)
+        rows = conn.select('user_domains', ('id', 'domain'), oid=event.oid)
         for id, domain in rows:
             row = data.get(id)
             if row is not None:
@@ -124,65 +106,21 @@
 
 
     def store(self, event, state):
-        old_rows, old_serial = self.load(event)
-        new_dict = {}
-        for rec in state:
-            new_dict[rec[0]] = rec
-        old_dict = {}
-        for rec in old_rows:
-            old_dict[rec[0]] = rec
-
-        # Compare / remove rows.
-        for old_row in old_rows:
-            id = old_row[0]
-            new_row = new_dict.get(id)
-            if new_row is None:
-                # Remove this row.
-                self.execute(event, 'users', 'delete', oid=event.oid, id=id)
-                self.execute(
-                    event, 'user_roles', 'delete', oid=event.oid, id=id)
-                self.execute(
-                    event, 'user_domains', 'delete', oid=event.oid, id=id)
-            elif new_row == old_row:
-                # Don't need to update this row.
-                del new_dict[id]
-
-        # Insert / update rows.
-        for new_row in new_dict.values():
-            id, password, roles, domains = new_row
-            old_row = old_dict.get(id)
-            if old_row is not None:
-                old_id, old_password, old_roles, old_domains = old_row
-                if old_password != password:
-                    # Update the password.
-                    self.execute(event, 'users', 'update',
-                                 oid=event.oid, id=id, password=password)
-            else:
-                # Insert a new record.
-                self.execute(event, 'users', 'insert',
-                             oid=event.oid, id=id, password=password)
-                old_roles = ()
-                old_domains = ()
-
-            # Update the role list.
-            if tuple(roles) != tuple(old_roles):
-                self.execute(
-                    event, 'user_roles', 'delete', oid=event.oid, id=id)
-                for role in roles:
-                    assert role
-                    self.execute(event, 'user_roles', 'insert',
-                                 oid=event.oid, id=id, role=role)
-
-            # Update the domain list.
-            if tuple(domains) != tuple(old_domains):
-                self.execute(
-                    event, 'user_domains', 'delete', oid=event.oid, id=id)
-                for domain in domains:
-                    assert domain
-                    self.execute(event, 'user_domains', 'insert',
-                                 oid=event.oid, id=id, domain=domain)
-                
+        oid = event.oid
+        conn = self.getConnection(event)
+        rows = [(id, pw) for id, pw, roles, domains in state]
+        conn.setMany('users', event.oid, (), ('id', 'password',), rows)
+        roles_d = {}
+        domains_d = {}
+        for id, pw, roles, domains in state:
+            for role in roles:
+                roles_d[(id, role)] = 1
+            for domain in domains:
+                domains_d[(id, domain)] = 1
+        conn.setMany(
+            'user_roles', event.oid, (), ('id', 'role',), roles_d.keys())
+        conn.setMany(
+            'user_domains', event.oid, (), ('id', 'domain',), domains_d.keys())
         state = list(state)
         state.sort()
         return tuple(state)
-


=== Products/Ape/lib/apelib/sql/sqlbase.py 1.10 => 1.11 ===
--- Products/Ape/lib/apelib/sql/sqlbase.py:1.10	Mon Feb  2 10:07:21 2004
+++ Products/Ape/lib/apelib/sql/sqlbase.py	Thu Mar 11 00:58:38 2004
@@ -17,7 +17,7 @@
 """
 
 from apelib.core.interfaces import IGateway, IDatabaseInitializer
-from interfaces import ISQLConnection
+from interfaces import IRDBMSConnection
 
 
 class SQLGatewayBase:
@@ -25,36 +25,29 @@
 
     __implements__ = IGateway, IDatabaseInitializer
 
-    table_base_name = '(override this)'
+    table = '(override this)'
     schema = None       # override
     column_defs = None  # optional override
+    oid_column_def = ('oid', 'int', 1)
 
     def __init__(self, conn_name='db'):
         self.conn_name = conn_name
         if self.column_defs is None and self.schema is not None:
             self.column_defs = tuple(self.schema.getColumnDefs())
+        self.columns = tuple([name for name, t, u in self.column_defs])
+
+    def getConnection(self, event):
+        return event.connections[self.conn_name]
 
     def init(self, event):
-        conn = event.connections[self.conn_name]
-        assert ISQLConnection.isImplementedBy(conn)
-        try:
+        conn = self.getConnection(event)
+        assert IRDBMSConnection.isImplementedBy(conn)
+        if conn.exists(self.table, 'table'):
             if event.clear_all:
-                self.execute(event, 'clear')
-            else:
-                self.execute(event, 'check')
-        except conn.error:
-            conn.db.rollback()
-            self.execute(event, 'create')
-            conn.db.commit()
-
-    def execute(self, event, operation, *args, **kw):
-        conn = event.connections[self.conn_name]
-        query = conn.getQuery(
-            self.table_base_name, self.column_defs, operation)
-        if query == '':
-            # No query needed for this operation
-            return
-        return conn.execute(query, *args, **kw)
+                conn.deleteFrom(self.table)
+        else:
+            defs = (self.oid_column_def,) + self.column_defs
+            conn.createTable(self.table, defs)
 
     def load(self, event):
         raise NotImplementedError, "abstract method"


=== Products/Ape/lib/apelib/sql/structure.py 1.9 => 1.10 ===
--- Products/Ape/lib/apelib/sql/structure.py:1.9	Sat Feb 28 15:06:27 2004
+++ Products/Ape/lib/apelib/sql/structure.py	Thu Mar 11 00:58:38 2004
@@ -26,15 +26,15 @@
     __implements__ = SQLGatewayBase.__implements__
 
     schema = FieldSchema('data', 'string')
-
-    table_base_name = 'object_data'
-
+    table = 'object_data'
     column_defs = (
         ('data', 'blob', 0),
         )
 
     def load(self, event):
-        items = self.execute(event, 'read', 1, oid=event.oid)
+        conn = self.getConnection(event)
+        firstcol = self.columns[:1]
+        items = conn.select(self.table, firstcol, oid=event.oid)
         if items:
             state = items[0][0]
         else:
@@ -42,16 +42,10 @@
         return state, state
 
     def store(self, event, state):
-        items = self.execute(event, 'read', 1, oid=event.oid)
-        col_name = self.column_defs[0][0]
-        conn = event.connections[self.conn_name]
-        kw = {'oid': event.oid, col_name: conn.asBinary(state)}
-        if items:
-            # update.
-            self.execute(event, 'update', **kw)
-        else:
-            # insert.
-            self.execute(event, 'insert', **kw)
+        conn = self.getConnection(event)
+        firstcol = (self.column_defs[0][0],)
+        data = (conn.module.Binary(state),)
+        conn.setOne(self.table, event.oid, firstcol, data, event.is_new)
         return state
 
 
@@ -65,53 +59,33 @@
     schema.addField('oid', 'string')
     schema.addField('classification', 'classification')
 
-    table_base_name = 'folder_items'
-
+    table = 'folder_items'
     column_defs = (
         ('name', 'string', 1),
         ('child_oid', 'int', 0),
         )
 
     def load(self, event):
-        rows = list(self.execute(event, 'read', 1, oid=event.oid))
+        conn = self.getConnection(event)
+        rows = conn.select(self.table, self.columns, oid=event.oid)
         res = []
         h = []
         for name, child_oid in rows:
-            # XXX This would be faster if we used a join.
-            child_oid = str(child_oid)
-            classification = event.classify(child_oid)
-            res.append((name, child_oid, classification))
-            h.append((name, child_oid))
+            s = str(child_oid)
+            classification = event.classify(s)
+            res.append((name, s, classification))
+            h.append((name, long(child_oid)))
         h.sort()
         return res, tuple(h)
 
     def store(self, event, state):
-        state_dict = {}
-        for name, child_oid, classification in state:
-            state_dict[name] = 1
-
-        rows = list(self.execute(event, 'read', 1, oid=event.oid))
-        db_dict = {}
-        for name, child_oid in rows:
-            if state_dict.has_key(name):
-                db_dict[name] = child_oid
-            else:
-                # Remove this item from the database.
-                self.execute(event, 'delete', oid=event.oid, name=name)
-
-        res = []
-        for name, child_oid, classification in state:
-            res.append((name, child_oid))
-            kw = {'oid': event.oid, 'name': name, 'child_oid': child_oid}
-            if db_dict.has_key(name):
-                if db_dict[name] != child_oid:
-                    # Change this item to point to a different OID.
-                    self.execute(event, 'update', **kw)
-            else:
-                # Add this item to the database.
-                self.execute(event, 'insert', **kw)
-        res.sort()
-        return tuple(res)
+        conn = self.getConnection(event)
+        rows = [(name, long(child_oid)) for (name, child_oid, cls) in state]
+        rows.sort()
+        # Note that setMany() requires the child_oid column to match
+        # its database type.
+        conn.setMany(self.table, event.oid, ('name',), ('child_oid',), rows)
+        return tuple(rows)
 
 
 class SQLItemId (SQLGatewayBase):
@@ -124,8 +98,7 @@
     __implements__ = SQLGatewayBase.__implements__
 
     schema = FieldSchema('id', 'string')
-
-    table_base_name = 'folder_items'
+    table = 'folder_items'
 
     column_defs = (
         ('child_oid', 'int', 1),
@@ -136,9 +109,12 @@
         pass
 
     def load(self, event):
-        rows = self.execute(event, 'simple_search', 1, child_oid=event.oid)
-        assert len(rows) >= 1
-        name = rows[0][1]  # Accept only the first result
+        conn = self.getConnection(event)
+        rows = conn.select(self.table, ('name',), child_oid=event.oid)
+        if len(rows) >= 1:
+            name = rows[0][0]  # Accept only the first result
+        else:
+            name = None
         # Disable conflict checking by returning None as the hash value.
         return name, None
 
@@ -151,8 +127,7 @@
 
     __implements__ = SQLGatewayBase.__implements__
 
-    table_base_name = 'remainder'
-
+    table = 'remainder'
     column_defs = (
         ('pickle', 'blob', 0),
         )
@@ -164,15 +139,14 @@
     __implements__ = SQLGatewayBase.__implements__
 
     schema = FieldSchema('mtime', 'int')  # second
-
-    table_base_name = 'mtime'
-
+    table = 'mtime'
     column_defs = (
         ('mtime', 'long', 0),
         )
 
     def load(self, event):
-        items = self.execute(event, 'read', 1, oid=event.oid)
+        conn = self.getConnection(event)
+        items = conn.select(self.table, self.columns, oid=event.oid)
         if items:
             state = long(items[0][0])
         else:
@@ -181,14 +155,9 @@
 
     def store(self, event, state):
         state = long(state)
-        items = self.execute(event, 'read', 1, oid=event.oid)
-        kw = {'oid': event.oid, 'mtime': state}
-        if items:
-            # update.
-            self.execute(event, 'update', **kw)
-        else:
-            # insert.
-            self.execute(event, 'insert', **kw)
+        conn = self.getConnection(event)
+        data = (state,)
+        conn.setOne(self.table, event.oid, self.columns, data, event.is_new)
         return state
 
 

=== Removed File Products/Ape/lib/apelib/sql/querygen.py ===




More information about the Zope-CVS mailing list