[Zope-CVS] CVS: Products/Ape/lib/apelib/sql - mysql.py:1.2 postgresql.py:1.2 table.py:1.2 classification.py:1.9 dbapi.py:1.12 interfaces.py:1.5 oidgen.py:1.6 properties.py:1.12 security.py:1.9 sqlbase.py:1.14 structure.py:1.12

Shane Hathaway shane at zope.com
Wed Jul 21 02:38:35 EDT 2004


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

Modified Files:
	classification.py dbapi.py interfaces.py oidgen.py 
	properties.py security.py sqlbase.py structure.py 
Added Files:
	mysql.py postgresql.py table.py 
Log Message:
Merged sql-types-branch.


=== Products/Ape/lib/apelib/sql/mysql.py 1.1 => 1.2 ===
--- /dev/null	Wed Jul 21 02:38:35 2004
+++ Products/Ape/lib/apelib/sql/mysql.py	Wed Jul 21 02:38:05 2004
@@ -0,0 +1,79 @@
+##############################################################################
+#
+# Copyright (c) 2004 Zope Corporation and Contributors.
+# All Rights Reserved.
+#
+# This software is subject to the provisions of the Zope Public License,
+# Version 2.0 (ZPL).  A copy of the ZPL should accompany this distribution.
+# THIS SOFTWARE IS PROVIDED "AS IS" AND ANY AND ALL EXPRESS OR IMPLIED
+# WARRANTIES ARE DISCLAIMED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
+# WARRANTIES OF TITLE, MERCHANTABILITY, AGAINST INFRINGEMENT, AND FITNESS
+# FOR A PARTICULAR PURPOSE.
+#
+##############################################################################
+"""MySQL-specific database connection.
+
+$Id$
+"""
+
+from apelib.sql.dbapi import AbstractSQLConnection
+
+class MySQLConnection (AbstractSQLConnection):
+
+    column_type_translations = {
+        'long':   'bigint',
+        'string': 'character varying(255)',
+        'blob':   'longblob',
+        'boolean': 'tinyint(1)',
+        }
+
+    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'
+        """
+        table_name = self.prefix + name
+        if type_name not in ('table', 'sequence'):
+            raise ValueError(type_name)
+        sql = 'SHOW TABLES LIKE :name'
+        rows = self.execute(sql, {'name': table_name}, fetch=1)
+        return len(rows)
+
+    def list_table_names(self):
+        """Returns a list of existing table names.
+        """
+        sql = 'SHOW TABLES'
+        rows = self.execute(sql, {}, fetch=1)
+        res = []
+        for (name,) in rows:
+            if not self.prefix or name.startswith(self.prefix):
+                res.append(name[len(self.prefix):])
+        return res
+
+    def create_sequence(self, name, start=1):
+        """Creates a sequence.
+        """
+        table_name = self.prefix + name
+        self.execute("CREATE TABLE %s (last_value int)" % table_name)
+        self.execute("INSERT INTO %s VALUES (%d)" % (table_name, start))
+
+    def reset_sequence(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/postgresql.py 1.1 => 1.2 ===
--- /dev/null	Wed Jul 21 02:38:35 2004
+++ Products/Ape/lib/apelib/sql/postgresql.py	Wed Jul 21 02:38:05 2004
@@ -0,0 +1,80 @@
+##############################################################################
+#
+# Copyright (c) 2004 Zope Corporation and Contributors.
+# All Rights Reserved.
+#
+# This software is subject to the provisions of the Zope Public License,
+# Version 2.0 (ZPL).  A copy of the ZPL should accompany this distribution.
+# THIS SOFTWARE IS PROVIDED "AS IS" AND ANY AND ALL EXPRESS OR IMPLIED
+# WARRANTIES ARE DISCLAIMED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
+# WARRANTIES OF TITLE, MERCHANTABILITY, AGAINST INFRINGEMENT, AND FITNESS
+# FOR A PARTICULAR PURPOSE.
+#
+##############################################################################
+"""PostgreSQL-specific database connection.
+
+$Id$
+"""
+
+from apelib.sql.dbapi import AbstractSQLConnection
+
+
+class PostgreSQLConnection (AbstractSQLConnection):
+
+    column_type_translations = {
+        'long':   'bigint',
+        'string': 'character varying(255)',
+        'blob':   'bytea',
+        'datetime': 'timestamp',
+        }
+
+    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'
+        """
+        table_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': table_name.lower()}, fetch=1)
+        return len(rows)
+
+    def list_table_names(self):
+        """Returns a list of existing table names.
+        """
+        sql = 'SELECT tablename FROM pg_tables'
+        rows = self.execute(sql, {}, fetch=1)
+        res = []
+        for (name,) in rows:
+            if not self.prefix or name.startswith(self.prefix):
+                res.append(name[len(self.prefix):])
+        return res
+
+    def create_sequence(self, name, start=1):
+        """Creates a sequence.
+        """
+        sql = "CREATE SEQUENCE %s START %d" % (self.prefix + name, start)
+        self.execute(sql)
+
+    def reset_sequence(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]


=== Products/Ape/lib/apelib/sql/table.py 1.1 => 1.2 ===
--- /dev/null	Wed Jul 21 02:38:35 2004
+++ Products/Ape/lib/apelib/sql/table.py	Wed Jul 21 02:38:05 2004
@@ -0,0 +1,226 @@
+##############################################################################
+#
+# Copyright (c) 2004 Zope Corporation and Contributors.
+# All Rights Reserved.
+#
+# This software is subject to the provisions of the Zope Public License,
+# Version 2.0 (ZPL).  A copy of the ZPL should accompany this distribution.
+# THIS SOFTWARE IS PROVIDED "AS IS" AND ANY AND ALL EXPRESS OR IMPLIED
+# WARRANTIES ARE DISCLAIMED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
+# WARRANTIES OF TITLE, MERCHANTABILITY, AGAINST INFRINGEMENT, AND FITNESS
+# FOR A PARTICULAR PURPOSE.
+#
+##############################################################################
+"""SQL implementation of IRDBMSTable.
+
+$Id$
+"""
+
+from apelib.sql.interfaces import IRDBMSTable, IRDBMSColumn
+
+
+class SQLTable:
+    """Talks to a table via SQL."""
+
+    __implements__ = IRDBMSTable
+
+    def __init__(self, connection, name):
+        self.name = name
+        self.execute = connection.execute
+        self.columns = {}
+        self.column_order = []
+
+    def add_column(self, name, column):
+        assert not self.columns.has_key(name)
+        assert IRDBMSColumn.isImplementedBy(column)
+        self.columns[name] = column
+        self.column_order.append(name)
+
+    def cache(self, m, *params):
+        # In the future, this will integrate with AbstractSQLConnection
+        # to provide a clean way to cache and prepare database queries.
+        return m(*params)
+
+    def generate_conditions(self, col_names):
+        clauses = [
+            "%s = :%s" % (self.columns[c].name, c) for c in col_names]
+        return ' AND '.join(clauses)
+
+    def generate_select(self, filter_col_names, result_col_names):
+        result_names = [self.columns[col].name for col in result_col_names]
+        sql = 'SELECT %s FROM %s' % (', '.join(result_names), self.name)
+        where = self.generate_conditions(filter_col_names)
+        if where:
+            sql += ' WHERE %s' % where
+        return sql
+
+    def generate_insert(self, col_names):
+        db_names = [self.columns[c].name for c in col_names]
+        colfmts = [':%s' % c for c in col_names]
+        return 'INSERT INTO %s (%s) VALUES (%s)' % (
+            self.name, ', '.join(db_names), ', '.join(colfmts))
+
+    def generate_update(self, key_col_names, other_col_names):
+        where = self.generate_conditions(key_col_names)
+        to_set = [
+            ("%s = :%s" % (self.columns[c].name, c))
+            for c in other_col_names]
+        return 'UPDATE %s SET %s WHERE %s' % (
+            self.name, ', '.join(to_set), where)
+
+    def generate_delete(self, col_names):
+        where = self.generate_conditions(col_names)
+        sql = 'DELETE FROM %s' % self.name
+        if where:
+            sql += ' WHERE %s' % where
+        return sql
+
+    def prepare_for_db(self, col_names, data, oid=None):
+        """Prepares one row for writing to the database."""
+        res = {}
+        for n in range(len(col_names)):
+            c = col_names[n]
+            res[c] = self.columns[c].to_db(data[n])
+        if oid is not None:
+            res['oid'] = self.columns['oid'].to_db(oid)
+        return res
+
+    #
+    # IRDBMSTable implementation.
+    #
+
+    def select(self, result_col_names, **filter):
+        """Selects rows from a table and returns column values for those rows.
+        """
+        f = {}
+        for col_name, value in filter.items():
+            f[col_name] = self.columns[col_name].to_db(value)
+        sql = self.cache(self.generate_select, filter.keys(), result_col_names)
+        db_res = self.execute(sql, f, fetch=1)
+        # Convert the results to standard types.
+        conversions = []
+        for n in range(len(result_col_names)):
+            col = self.columns[result_col_names[n]]
+            if col.use_conversion:
+                conversions.append((n, col.from_db))
+        if conversions:
+            # Convert specific columns.
+            res = []
+            for row in db_res:
+                r = list(row)
+                for n, from_db in conversions:
+                    r[n] = from_db(r[n])
+                res.append(tuple(r))
+        else:
+            # No conversion needed.
+            res = db_res
+        return res
+
+    def insert(self, col_names, row):
+        """Inserts one row in the table.
+        """
+        kw = self.prepare_for_db(col_names, row)
+        sql = self.cache(self.generate_insert, col_names)
+        self.execute(sql, kw)
+
+    def set_one(self, oid, col_names, 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.
+        """
+        kw = self.prepare_for_db(col_names, row, oid)
+        if is_new:
+            sql = self.cache(self.generate_insert, ('oid',) + tuple(col_names))
+            self.execute(sql, kw)
+        else:
+            sql = self.cache(self.generate_update, ('oid',), col_names)
+            self.execute(sql, kw)
+
+    def set_many(self, oid, key_col_names, other_col_names, 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.
+        """
+        combo = tuple(key_col_names) + tuple(other_col_names)
+        if not key_col_names:
+            # Don't compare rows.  Just delete and insert.
+            kw = self.prepare_for_db((), (), oid)
+            sql = self.cache(self.generate_delete, ('oid',))
+            self.execute(sql, kw)
+            sql = self.cache(self.generate_insert, ('oid',) + combo)
+            for row in rows:
+                kw = self.prepare_for_db(combo, row, oid)
+                self.execute(sql, kw)
+            return
+        # Edit the table.
+        exist_rows = self.select(combo, oid=oid)
+        count = len(key_col_names)
+        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.
+                kw = self.prepare_for_db(key_col_names, key, oid)
+                sql = self.cache(
+                    self.generate_delete, ('oid',) + tuple(key_col_names))
+                self.execute(sql, kw)
+            elif now[key] != value:
+                # Update this row.
+                #print 'DIFFERENT:', now[key], value
+                kw = self.prepare_for_db(combo, key + now[key], oid)
+                cols = ('oid',) + tuple(key_col_names)
+                sql = self.cache(self.generate_update, cols, other_col_names)
+                self.execute(sql, kw)
+        for key, value in now.items():
+            if not existing.has_key(key):
+                # Insert this row.
+                kw = self.prepare_for_db(combo, key + value, oid)
+                sql = self.cache(self.generate_insert, ('oid',) + combo)
+                self.execute(sql, kw)
+        return
+
+    def delete_rows(self, **filter):
+        """Deletes rows from the table.
+        """
+        sql = self.cache(self.generate_delete, filter.keys())
+        self.execute(sql, filter)
+
+    def create(self):
+        """Creates the table.
+        """
+        pkeys = []
+        col_decls = []
+        for c in self.column_order:
+            col = self.columns[c]
+            constraints = ''
+            if col.unique:
+                constraints = ' NOT NULL'
+                pkeys.append(col.name)
+            col_decls.append(
+                "%s %s%s" % (col.name, col.type, constraints))
+        if pkeys:
+            col_decls.append('PRIMARY KEY (%s)' % ', '.join(pkeys))
+        sql = "CREATE TABLE %s (%s)" % (self.name, ', '.join(col_decls))
+        self.execute(sql)
+
+    def drop(self):
+        """Drops the table.
+        """
+        sql = "DROP TABLE %s" % self.name
+        self.execute(sql)


=== Products/Ape/lib/apelib/sql/classification.py 1.8 => 1.9 ===
--- Products/Ape/lib/apelib/sql/classification.py:1.8	Sat Mar 20 01:34:23 2004
+++ Products/Ape/lib/apelib/sql/classification.py	Wed Jul 21 02:38:05 2004
@@ -16,7 +16,7 @@
 $Id$
 """
 
-from apelib.core.schemas import FieldSchema
+from apelib.core.schemas import ColumnSchema, RowSequenceSchema
 from apelib.core.interfaces import OIDConflictError
 from sqlbase import SQLGatewayBase
 
@@ -25,16 +25,15 @@
 
     __implements__ = SQLGatewayBase.__implements__
 
-    schema = FieldSchema('classification', 'classification')
-    table = 'classification'
-    column_defs = (
-        ('class_name', 'string', 0),
-        ('mapper_name', 'string', 0),
-        )
+    schema = ColumnSchema('classification', 'classification')
+    table_name = 'classification'
+    table_schema = RowSequenceSchema()
+    table_schema.add('class_name', 'string', 0)
+    table_schema.add('mapper_name', 'string', 0)
 
     def load(self, event):
-        conn = self.get_connection(event)
-        rows = conn.select(self.table, self.columns, oid=event.oid)
+        table = self.get_table(event)
+        rows = table.select(self.column_names, oid=event.oid)
         classification = {}
         if rows:
             rec = rows[0]
@@ -48,10 +47,11 @@
 
     def store(self, event, classification):
         conn = self.get_connection(event)
+        table = self.get_table(event)
         row = (classification.get('class_name', ''),
                classification.get('mapper_name', ''))
         try:
-            conn.set_one(self.table, event.oid, self.columns, row, event.is_new)
-        except conn.module.IntegrityError:
+            table.set_one(event.oid, self.column_names, row, event.is_new)
+        except conn.module.DatabaseError:
             raise OIDConflictError(event.oid)
         return row


=== Products/Ape/lib/apelib/sql/dbapi.py 1.11 => 1.12 ===
--- Products/Ape/lib/apelib/sql/dbapi.py:1.11	Thu Apr 15 19:15:42 2004
+++ Products/Ape/lib/apelib/sql/dbapi.py	Wed Jul 21 02:38:05 2004
@@ -21,8 +21,9 @@
 from time import time
 
 from apelib.core.interfaces import ITPCConnection
-
-from apelib.sql.interfaces import IRDBMSConnection
+from apelib.core.schemas import ColumnSchema
+from apelib.sql.interfaces import ISQLConnection, IRDBMSColumn
+from apelib.sql.table import SQLTable
 
 name_style_re = re.compile(':[A-Za-z0-9_-]+')
 
@@ -32,10 +33,12 @@
 
 class AbstractSQLConnection:
 
-    __implements__ = IRDBMSConnection, ITPCConnection
+    __implements__ = ISQLConnection, ITPCConnection
 
-    column_type_translations = {}  # { local type name -> db type name }
+    # factories by column name take precedence over factories by column type.
+    column_factories_by_name = {}  # { local col name -> column factory }
     column_name_translations = {}  # { local col name -> db col name }
+    column_type_translations = {}  # { local type name -> db type name }
     module = None
     connector = None
 
@@ -43,167 +46,44 @@
         # connect_expression is a Python expression.
         self.module_name = module_name
         self.module = __import__(module_name, {}, {}, ('__doc__',))
+        if not hasattr(self.module, "connect"):
+            raise ImportError(
+                "Module '%s' does not have a 'connect' method." % module_name)
         self.connect_expression = connect_expression
         self.prefix = prefix
         self.connector = None
+        self._tables = {}
         self._final = 0
 
     def __repr__(self):
         return '<%s(module_name=%s)>' % (
             self.__class__.__name__, repr(self.module_name))
 
-    def translate_name(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 translate_type(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 generate_conditions(self, keys):
-        conditions = []
-        for key in keys:
-            clause = "%s = :%s" % (self.translate_name(key), key)
-            conditions.append(clause)
-        return ' AND '.join(conditions)
-
-    def generate_insert(self, table_name, columns):
-        dbcols = [self.translate_name(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 generate_update(self, table_name, key_columns, other_columns):
-        where = self.generate_conditions(key_columns)
-        to_set = [
-            ("%s = :%s" % (self.translate_name(col), col))
-            for col in other_columns]
-        return 'UPDATE %s SET %s WHERE %s' % (
-            table_name, ', '.join(to_set), where)
-
-    def generate_delete(self, table_name, keys):
-        where = self.generate_conditions(keys)
-        sql = 'DELETE FROM %s' % table_name
-        if where:
-            sql += ' WHERE %s' % where
-        return sql
-
-    def make_dict(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.translate_name(col) for col in result_columns]
-        where = self.generate_conditions(filter.keys())
-        sql = 'SELECT %s FROM %s' % (', '.join(dbcols), table_name)
-        if where:
-            sql += ' WHERE %s' % where
-        return self.execute(sql, filter, fetch=1)
-
-    def insert(self, table, columns, row):
-        """Inserts one row in the table.
-        """
-        table_name = self.prefix + table
-        kw = self.make_dict(columns, row)
-        sql = self.generate_insert(table_name, columns)
-        self.execute(sql, kw)
-
-    def set_one(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.make_dict(columns, row, oid)
-        if is_new:
-            sql = self.generate_insert(table_name, ('oid',) + tuple(columns))
-            self.execute(sql, kw)
-        else:
-            sql = self.generate_update(table_name, ('oid',), columns)
-            self.execute(sql, kw)
-            
-    def set_many(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.generate_delete(table_name, ('oid',))
-            self.execute(sql, {'oid': oid})
-            for row in rows:
-                sql = self.generate_insert(table_name, ('oid',) + combo)
-                kw = self.make_dict(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.generate_delete(
-                    table_name, ('oid',) + tuple(key_columns))
-                kw = self.make_dict(key_columns, key, oid)
-                self.execute(sql, kw)
-            elif now[key] != value:
-                # Update this row.
-                #print 'DIFFERENT:', now[key], value
-                sql = self.generate_update(
-                    table_name, ('oid',) + tuple(key_columns), other_columns)
-                kw = self.make_dict(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.generate_insert(table_name, ('oid',) + combo)
-                kw = self.make_dict(combo, key + value, oid)
-                self.execute(sql, kw)
-        return
-
-    def delete_from(self, table, **filter):
-        """Deletes rows from a table.
-        """
-        table_name = self.prefix + table
-        sql = self.generate_delete(table_name, filter.keys())
-        self.execute(sql, filter)
+    def define_table(self, name, schema):
+        """Creates and returns an IRDBMSTable."""
+        table = SQLTable(self, self.prefix + name)
+        for c in schema.get_columns():
+            factory = self.column_factories_by_name.get(c.name, None)
+            if factory is None:
+                factory = RDBMSColumn
+            dbc = factory(self, c)
+            n = self.column_name_translations.get(c.name)
+            if n is not None:
+                dbc.name = n
+            t = self.column_type_translations.get(c.type)
+            if t is not None:
+                dbc.type = t
+            table.add_column(c.name, dbc)
+        self._tables[name] = table
+        return table
+
+    def get_table(self, name):
+        """Returns a previously defined IRDBMSTable."""
+        return self._tables[name]
 
     def exists(self, name, type_name):
         """Returns true if the specified database object exists.
@@ -217,32 +97,6 @@
         """
         raise NotImplementedError("Abstract Method")
 
-    def create_table(self, table, column_defs):
-        """Creates a table.
-        """
-        table_name = self.prefix + table
-        cols = []
-        pkeys = []
-        for name, typ, unique in column_defs:
-            col = self.translate_name(name)
-            db_type = self.translate_type(typ)
-            constraints = ''
-            if unique:
-                constraints = ' NOT NULL'
-                pkeys.append(col)
-            cols.append("%s %s%s" % (col, db_type, constraints))
-        if pkeys:
-            cols.append('PRIMARY KEY (%s)' % ', '.join(pkeys))
-        sql = "CREATE TABLE %s (%s)" % (table_name, ', '.join(cols))
-        self.execute(sql)
-
-    def drop_table(self, table):
-        """Drops a table.
-        """
-        table_name = self.prefix + table
-        sql = "DROP TABLE %s" % table_name
-        self.execute(sql)
-
     def create_sequence(self, name, start=1):
         """Creates a sequence.
         """
@@ -258,13 +112,25 @@
         """
         raise NotImplementedError("Abstract Method")
 
-    def execute(self, sql, kw=None, fetch=0):
+    def clear_table(self, name):
+        """Removes all rows from a table.
+        """
+        self.execute('DELETE FROM %s' % (self.prefix + name))
+
+    def execute(self, sql, kw=None, fetch=False):
         if self.connector is None:
             raise RuntimeError('Not connected')
-        if kw is None:
-            kw = {}
         converter = style_converters[self.module.paramstyle]
-        sql, params = converter(sql, kw)
+        sql, param_names = converter(sql)
+        if param_names is None:
+            # The query expects keyword parameters.
+            params = kw or {}
+        else:
+            # The query expects positional parameters.
+            if not param_names:
+                params = ()
+            else:
+                params = tuple([kw[n] for n in param_names])
         cursor = self.connector.cursor()
         try:
             if DEBUG or PROFILE:
@@ -328,179 +194,96 @@
 
 
 
-# Converters for all parameter styles defined by DB-API 2.0
+# Converters for all parameter styles defined by DB-API 2.0.
+# Each converter returns translated SQL and a list of positional parameters.
+# The list of positional parameters may be None, indicating that a dictionary
+# should be supplied rather than a tuple.
 
 style_converters = {}
 
-def convert_to_qmark(sql, kw):
+def convert_to_qmark(sql):
     # '?' format
     params = []
-    def replace(match, params=params, kw=kw):
+    def replace(match, params=params):
         name = match.group()[1:]
-        params.append(kw[name])
+        params.append(name)
         return '?'
     sql = name_style_re.sub(replace, sql)
-    return sql, tuple(params)
+    return sql, params
 style_converters['qmark'] = convert_to_qmark
 
-def convert_to_numeric(sql, kw):
+def convert_to_numeric(sql):
     # ':1' format
     params = []
-    def replace(match, params=params, kw=kw):
+    def replace(match, params=params):
         name = match.group()[1:]
         index = len(params)
-        params.append(kw[name])
+        params.append(name)
         return ':%d' % index
     sql = name_style_re.sub(replace, sql)
-    return sql, tuple(params)
+    return sql, params
 style_converters['numeric'] = convert_to_numeric
 
-def convert_to_named(sql, kw):
+def convert_to_named(sql):
     # ':name' format
     # The input format is the same as the output format.
-    return sql, kw
+    return sql, None
 style_converters['named'] = convert_to_named
 
-def convert_to_format(sql, kw):
+def convert_to_format(sql):
     # '%s' format
     params = []
-    def replace(match, params=params, kw=kw):
+    def replace(match, params=params):
         name = match.group()[1:]
-        params.append(kw[name])
+        params.append(name)
         return '%s'
     sql = name_style_re.sub(replace, sql)
-    return sql, tuple(params)
+    return sql, params
 style_converters['format'] = convert_to_format
 
-def convert_to_pyformat(sql, kw):
+def convert_to_pyformat(sql):
     # '%(name)s' format
-    def replace(match, kw=kw):
+    def replace(match):
         name = match.group()[1:]
         return '%%(%s)s' % name
     sql = name_style_re.sub(replace, sql)
-    return sql, kw
+    return sql, None
 style_converters['pyformat'] = convert_to_pyformat
 
 
-# Database-specific implementations of IRDBMSConnection.
 
-class PostgreSQLConnection (AbstractSQLConnection):
+# RDBMS column implementations.
 
-    column_type_translations = {
-        'long':   'bigint',
-        'string': 'character varying(255)',
-        'blob':   'bytea',
-        'datetime': 'timestamp',
-        }
-
-    column_name_translations = {
-        'oid': 'objoid',
-        }
+class RDBMSColumn(ColumnSchema):
+    """Basic RDBMS column.  Does no type translation."""
+    __implements__ = IRDBMSColumn
 
-    def exists(self, name, type_name):
-        """Returns true if the specified database object exists.
+    use_conversion = False
 
-        type_name is 'table' or 'sequence'
-        """
-        table_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': table_name.lower()}, fetch=1)
-        return len(rows)
+    def __init__(self, connection, column):
+        self.name = column.name
+        self.type = column.type
+        self.unique = column.unique
 
-    def list_table_names(self):
-        """Returns a list of existing table names.
-        """
-        sql = 'SELECT tablename FROM pg_tables'
-        rows = self.execute(sql, {}, fetch=1)
-        res = []
-        for (name,) in rows:
-            if not self.prefix or name.startswith(self.prefix):
-                res.append(name[len(self.prefix):])
-        return res
-
-    def create_sequence(self, name, start=1):
-        """Creates a sequence.
-        """
-        sql = "CREATE SEQUENCE %s START %d" % (self.prefix + name, start)
-        self.execute(sql)
-
-    def reset_sequence(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):
+    def to_db(self, value):
+        return value
 
-    column_type_translations = {
-        'long':   'bigint',
-        'string': 'character varying(255)',
-        'blob':   'longblob',
-        'boolean': 'tinyint(1)',
-        }
+    def from_db(self, value):
+        return value
 
-    column_name_translations = {
-        'oid': 'objoid',
-        }
 
-    def exists(self, name, type_name):
-        """Returns true if the specified database object exists.
+class OIDColumn(RDBMSColumn):
+    """RDBMS column that stores string OIDs as integers."""
+    __implements__ = IRDBMSColumn
 
-        type_name is 'table' or 'sequence'
-        """
-        table_name = self.prefix + name
-        if type_name not in ('table', 'sequence'):
-            raise ValueError(type_name)
-        sql = 'SHOW TABLES LIKE :name'
-        rows = self.execute(sql, {'name': table_name}, fetch=1)
-        return len(rows)
+    use_conversion = True
 
-    def list_table_names(self):
-        """Returns a list of existing table names.
-        """
-        sql = 'SHOW TABLES'
-        rows = self.execute(sql, {}, fetch=1)
-        res = []
-        for (name,) in rows:
-            if not self.prefix or name.startswith(self.prefix):
-                res.append(name[len(self.prefix):])
-        return res
+    def to_db(self, value):
+        return int(value)
 
-    def create_sequence(self, name, start=1):
-        """Creates a sequence.
-        """
-        table_name = self.prefix + name
-        self.execute("CREATE TABLE %s (last_value int)" % table_name)
-        self.execute("INSERT INTO %s VALUES (%d)" % (table_name, start))
+    def from_db(self, value):
+        return str(value)
 
-    def reset_sequence(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]
+# Set up default column types.
+AbstractSQLConnection.column_factories_by_name['oid'] = OIDColumn


=== Products/Ape/lib/apelib/sql/interfaces.py 1.4 => 1.5 ===
--- Products/Ape/lib/apelib/sql/interfaces.py:1.4	Wed Mar 24 22:17:08 2004
+++ Products/Ape/lib/apelib/sql/interfaces.py	Wed Jul 21 02:38:05 2004
@@ -18,6 +18,8 @@
 
 from Interface import Interface
 from Interface.Attribute import Attribute
+from apelib.core.interfaces import IColumnSchema
+
 
 class IRDBMSConnection (Interface):
     """Interface of basic RDBMS connections.
@@ -30,35 +32,15 @@
 
     connector = Attribute("connector", "The shared DB-API connection")
 
-    def select(table, result_columns, **filter):
-        """Selects rows from a table and returns column values for those rows.
-        """
+    def define_table(name, schema):
+        """Creates and returns an IRDBMSTable.
 
-    def insert(table, columns, row):
-        """Inserts one row in a table.
-        """
-
-    def set_one(table, oid, columns, row, is_new):
-        """Sets one row in a table.
-
-        Executes either an update or insert operation, depending
-        on the is_new argument and configured policies.
+        Does not create the table in the database.  table.create()
+        creates the table.
         """
 
-    def set_many(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.
-        """
-
-    def delete_from(table, **filter):
-        """Deletes rows from a table.
-        """
+    def get_table(name):
+        """Returns a previously defined IRDBMSTable."""
 
     def exists(name, type_name):
         """Returns true if the specified database object exists.
@@ -68,29 +50,87 @@
         """
 
     def list_table_names():
-        """Returns a list of existing table names.
+        """Returns a list of existing table names."""
+
+    def create_sequence(name, start=1):
+        """Creates a sequence."""
+
+    def reset_sequence(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.
         """
 
-    def create_table(name, column_defs):
-        """Creates a table.
+    def clear_table(name):
+        """Removes all rows from a table.
 
-        column_defs is [(name, type, is_unique)].
+        This is not a method of IRDBMSTable because it is not
+        always possible to construct an IRDBMSTable while resetting
+        tables.
         """
 
-    def drop_table(table):
-        """Drops a table.
+
+class ISQLConnection (IRDBMSConnection):
+    
+    def execute(sql, kw=None, fetch=False):
+        """Executes a SQL query.
+
+        If kw is provided, parameters in the SQL are substituted for
+        parameter values.  If fetch is true, the rows from the results
+        are returned.  No type conversion happens in execute().
         """
 
-    def create_sequence(name, start=1):
-        """Creates a sequence.
+
+class IRDBMSTable (Interface):
+    """A table in a database."""
+
+    def select(result_col_names, **filter):
+        """Selects rows from a table and returns column values for those rows.
         """
 
-    def reset_sequence(name, start=1):
-        """Resets a sequence to a starting value.
+    def insert(col_names, row):
+        """Inserts one row in the table."""
+
+    def set_one(oid, col_names, row, is_new):
+        """Sets one row in the table.
+
+        Executes either an update or insert operation, depending
+        on the is_new argument and configured policies.
         """
 
-    def increment(name):
-        """Increments a sequence and returns the value.
+    def set_many(oid, key_col_names, other_col_names, rows):
+        """Sets multiple rows in the table.
 
-        Whether the value is before or after the increment is not specified.
+        'rows' is a sequence of tuples containing values for the
+        key_col_names as well as the other_col_names.
+
+        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 delete_rows(**filter):
+        """Deletes rows from the table."""
+
+    def create():
+        """Creates the table."""
+
+    def drop():
+        """Drops the table."""
+
+
+class IRDBMSColumn (IColumnSchema):
+    """A column associated with a specific database."""
+
+    use_conversion = Attribute(
+        "use_conversion", "True if this column needs to convert values.")
+
+    def to_db(value):
+        """Converts a generic value to a database-specific value."""
+
+    def from_db(value):
+        """Converts a database-specific value to a generic value."""
+


=== Products/Ape/lib/apelib/sql/oidgen.py 1.5 => 1.6 ===
--- Products/Ape/lib/apelib/sql/oidgen.py:1.5	Fri Mar 26 10:52:49 2004
+++ Products/Ape/lib/apelib/sql/oidgen.py	Wed Jul 21 02:38:05 2004
@@ -26,19 +26,19 @@
     __implements__ = (interfaces.IOIDGenerator,
                       interfaces.IDatabaseInitializer)
 
-    table = 'oid_seq'
-    column_defs = ()
+    table_name = 'oid_seq'
     root_oid = "0"
 
     def init(self, event):
         conn = self.get_connection(event)
-        if not conn.exists(self.table, 'sequence'):
-            conn.create_sequence(self.table, start=1)
+        if not conn.exists(self.table_name, 'sequence'):
+            conn.create_sequence(self.table_name, start=1)
         elif event.clear_all:
-            conn.reset_sequence(self.table, start=1)
+            conn.reset_sequence(self.table_name, start=1)
 
     def new_oid(self, event):
+        """Returns a new OID.  Must return a string."""
         assert interfaces.IGatewayEvent.isImplementedBy(event)
         conn = self.get_connection(event)
-        n = conn.increment(self.table)
+        n = conn.increment(self.table_name)
         return str(n)


=== Products/Ape/lib/apelib/sql/properties.py 1.11 => 1.12 ===
--- Products/Ape/lib/apelib/sql/properties.py:1.11	Thu Mar 25 22:31:52 2004
+++ Products/Ape/lib/apelib/sql/properties.py	Wed Jul 21 02:38:05 2004
@@ -48,24 +48,22 @@
     schema.add('id', 'string', 1)
     schema.add('type', 'string')
     schema.add('data', 'string')
-
-    table = 'properties'
-    column_defs = (
-        ('id', 'string', 1),
-        ('type', 'string', 0),
-        ('data', 'blob', 0),
-        )
+    table_name = 'properties'
+    table_schema = RowSequenceSchema()
+    table_schema.add('id', 'string', 1)
+    table_schema.add('type', 'string', 0)
+    table_schema.add('data', 'blob', 0)
 
     def load(self, event):
-        conn = self.get_connection(event)
-        rows = conn.select(self.table, self.columns, oid=event.oid)
+        table = self.get_table(event)
+        rows = table.select(self.column_names, oid=event.oid)
         rows.sort()
         return rows, tuple(rows)
 
     def store(self, event, state):
-        conn = self.get_connection(event)
+        table = self.get_table(event)
         rows = [(id, t, data) for id, t, data in state]
-        conn.set_many(self.table, event.oid, ('id',), ('type', 'data'), rows)
+        table.set_many(event.oid, ('id',), ('type', 'data'), rows)
         state = list(state)
         state.sort()
         return tuple(state)
@@ -75,42 +73,47 @@
     """SQL fixed-schema properties gateway.
     """
 
-    def __init__(self, conn_name, table_name, cols):
-        self.table = table_name
-        self.column_defs = cols
-        self.columns =  [name for (name, t, u) in cols]
-        self.schema = None
+    def __init__(self, conn_name, table_name, schema):
+        self.table_name = table_name
+        self.schema = schema
         SQLGatewayBase.__init__(self, conn_name)
+        self.columns = schema.get_columns()
 
-
-    def load(self, event):
+    def init(self, event):
         conn = self.get_connection(event)
-        recs = conn.select(self.table, self.columns, oid=event.oid)
+        all = RowSequenceSchema(
+            self.oid_columns + self.table_schema.get_columns())
+        table = conn.define_table(self.table_name, all)
+        if not conn.exists(self.table_name, 'table'):
+            table.create()
+        
+    def load(self, event):
+        table = self.get_table(event)
+        recs = table.select(self.column_names, oid=event.oid)
         if not recs:
             return (), ()
         if len(recs) > 1:
             raise ValueError("Multiple records where only one expected")
         record = [str(value) for value in recs[0]]
         items = []
-        cols = self.column_defs
+        cols = self.columns
         for n in range(len(cols)):
-            name, typ, unique = cols[n]
+            name = cols[n].name
             if name.startswith('_'):
                 prop_name = name[1:]
             else:
                 prop_name = name
-            items.append((prop_name, typ, record[n]))
+            items.append((prop_name, cols[n].type, record[n]))
         return items, tuple(record)
 
-
     def store(self, event, state, leftover=None):
-        cols = self.column_defs
+        cols = self.columns
         statedict = {}  # prop name -> (type, value)
         for name, typ, value in state:
             statedict[name] = (typ, value)
         record = []
         for col in cols:
-            name = col[0]
+            name = col.name
             if name.startswith('_'):
                 prop_name = name[1:]
             else:
@@ -129,8 +132,8 @@
                 raise ValueError(
                     "Extra properties provided for fixed schema: %s"
                     % statedict.keys())
-        conn = self.get_connection(event)
-        conn.set_one(self.table, event.oid, self.columns, record, event.is_new)
+        table = self.get_table(event)
+        table.set_one(event.oid, self.column_names, record, event.is_new)
         return tuple(record)
 
 
@@ -143,12 +146,11 @@
 
     schema = SQLProperties.schema
 
-    table = 'property_tables'
-    column_defs = (
-        ('class_name', 'string', 1),
-        ('table_name', 'string', 0),
-        )
-    oid_column_def = ()  # No OID column
+    table_name = 'property_tables'
+    table_schema = RowSequenceSchema()
+    table_schema.add('class_name', 'string', 1)
+    table_schema.add('table_name', 'string', 0)
+    oid_columns = []  # No OID column
 
     def __init__(self, conn_name='db'):
         self.var_props = SQLProperties(conn_name=conn_name)
@@ -160,18 +162,19 @@
 
     def init(self, event):
         conn = self.get_connection(event)
-        if not conn.exists(self.table, 'table'):
-            self.create(event)
+        table = conn.define_table(self.table_name, self.table_schema)
+        if not conn.exists(self.table_name, 'table'):
+            table.create()
         self.var_props.init(event)
         if event.clear_all:
             # Clear the fixed property tables.
-            recs = conn.select(self.table, ('table_name',))
+            recs = table.select(('table_name',))
             for (name,) in recs:
-                conn.delete_from(name)
+                conn.clear_table(name)
             self.fixed_props = {}
 
 
-    def get_columns_for_class(self, module_name, class_name):
+    def get_schema_for_class(self, module_name, class_name):
         """Returns the class-defined property schema.
 
         This Zope2-ism should be made pluggable later on.
@@ -179,7 +182,7 @@
         d = {}
         m = __import__(module_name, d, d, ('__doc__',))
         klass = getattr(m, class_name)
-        cols = []
+        schema = RowSequenceSchema()
         props = getattr(klass, '_properties', ())
         if not props:
             return None
@@ -193,8 +196,8 @@
                 name = '_oid'
             else:
                 name = prop_name
-            cols.append((name, p['type'], 0))
-        return tuple(cols)
+            schema.add(name, p['type'], 0)
+        return schema
 
 
     def get_fixed_props(self, event):
@@ -215,15 +218,16 @@
             raise ValueError, "Not a qualified class name: %s" % repr(cn)
         module_name = cn[:pos]
         class_name = cn[pos + 1:]
-        cols = self.get_columns_for_class(module_name, class_name)
-        if not cols:
+        schema = self.get_schema_for_class(module_name, class_name)
+        if schema is None or not schema.get_columns():
             # No fixed properties exist for this class.
             self.fixed_props[cn] = None
             return None
 
         # Allocate a table name
         conn = self.get_connection(event)
-        rows = conn.select(self.table, ('table_name',), class_name=cn)
+        table = self.get_table(event)
+        rows = table.select(('table_name',), class_name=cn)
         if rows:
             table_name = rows[0][0]
         else:
@@ -237,13 +241,11 @@
                 if not conn.exists(table_name, 'table'):
                     break
                 attempt += 1
-            conn.insert(
-                self.table, ('class_name', 'table_name'), (cn, table_name))
+            table.insert(('class_name', 'table_name'), (cn, table_name))
 
         # Create the fixed properties and table
-        fp = SQLFixedProperties(self.conn_name, table_name, cols)
-        if not conn.exists(table_name, 'table'):
-            fp.create(event)
+        fp = SQLFixedProperties(self.conn_name, table_name, schema)
+        fp.init(event)
         # XXX If the transaction gets aborted, the table creation will
         # be undone, but self.fixed_props won't see the change.
         # Perhaps we need to reset self.fixed_props on abort.
@@ -252,6 +254,7 @@
 
 
     def load(self, event):
+        """Returns a combination of states from two tables."""
         var_state, var_hash = self.var_props.load(event)
         fp = self.get_fixed_props(event)
         if fp is None:
@@ -276,6 +279,7 @@
 
 
     def store(self, event, state):
+        """Stores state in two tables."""
         fp = self.get_fixed_props(event)
         if fp is None:
             return self.var_props.store(event, state)


=== Products/Ape/lib/apelib/sql/security.py 1.8 => 1.9 ===
--- Products/Ape/lib/apelib/sql/security.py:1.8	Wed Mar 24 22:17:08 2004
+++ Products/Ape/lib/apelib/sql/security.py	Wed Jul 21 02:38:05 2004
@@ -16,7 +16,7 @@
 $Id$
 """
 
-from apelib.core.schemas import RowSequenceSchema
+from apelib.core.schemas import RowSequenceSchema, ColumnSchema
 from sqlbase import SQLGatewayBase
 
 
@@ -31,18 +31,18 @@
     schema.add('permission', 'string')
     schema.add('username', 'string')
 
-    table = 'security'
-    oid_column_def = (('oid', 'int', 0),)  # Don't create a primary key
+    table_name = 'security'
+    oid_columns = [ColumnSchema('oid', 'int', 0)]  # Don't create a primary key
 
     def load(self, event):
-        conn = self.get_connection(event)
-        items = conn.select(self.table, self.columns, oid=event.oid)
+        table = self.get_table(event)
+        items = table.select(self.column_names, oid=event.oid)
         items.sort()
         return items, tuple(items)
 
     def store(self, event, state):
-        conn = self.get_connection(event)
-        conn.set_many(self.table, event.oid, (), self.columns, state)
+        table = self.get_table(event)
+        table.set_many(event.oid, (), self.column_names, state)
         state = list(state)
         state.sort()
         return tuple(state)
@@ -61,39 +61,46 @@
     schema.add('domains', 'string:list')
 
     table_defs = {
-        'users':        (('oid', 'int', 1),
+        'users':        [('oid', 'int', 1),
                          ('id', 'string', 1),
-                         ('password', 'string', 0),),
-        'user_roles':   (('oid', 'int', 0),
+                         ('password', 'string', 0)],
+        'user_roles':   [('oid', 'int', 0),
                          ('id', 'string', 0),
-                         ('role', 'string', 0),),
-        'user_domains': (('oid', 'int', 0),
+                         ('role', 'string', 0)],
+        'user_domains': [('oid', 'int', 0),
                          ('id', 'string', 0),
-                         ('domain', 'string', 0),),
+                         ('domain', 'string', 0)],
         }
 
 
     def init(self, event):
         conn = self.get_connection(event)
-        for table, col_defs in self.table_defs.items():
-            if not conn.exists(table, 'table'):
-                conn.create_table(table, col_defs)
+        for table_name, columns in self.table_defs.items():
+            table_schema = RowSequenceSchema()
+            for args in columns:
+                table_schema.add(*args)
+            table = conn.define_table(table_name, table_schema)
+            if not conn.exists(table_name, 'table'):
+                table.create()
             elif event.clear_all:
-                conn.delete_from(table)
+                table.delete_rows()
 
 
     def load(self, event):
         conn = self.get_connection(event)
-        rows = conn.select('users', ('id', 'password'), oid=event.oid)
+        rows = conn.get_table('users').select(
+            ('id', 'password'), oid=event.oid)
         data = {}
         for id, password in rows:
             data[id] = (password, [], [])
-        rows = conn.select('user_roles', ('id', 'role'), oid=event.oid)
+        rows = conn.get_table('user_roles').select(
+            ('id', 'role'), oid=event.oid)
         for id, role in rows:
             row = data.get(id)
             if row is not None:
                 row[1].append(role)
-        rows = conn.select('user_domains', ('id', 'domain'), oid=event.oid)
+        rows = conn.get_table('user_domains').select(
+            ('id', 'domain'), oid=event.oid)
         for id, domain in rows:
             row = data.get(id)
             if row is not None:
@@ -109,7 +116,8 @@
         oid = event.oid
         conn = self.get_connection(event)
         rows = [(id, pw) for id, pw, roles, domains in state]
-        conn.set_many('users', event.oid, (), ('id', 'password',), rows)
+        conn.get_table('users').set_many(
+            event.oid, (), ('id', 'password',), rows)
         roles_d = {}
         domains_d = {}
         for id, pw, roles, domains in state:
@@ -117,10 +125,10 @@
                 roles_d[(id, role)] = 1
             for domain in domains:
                 domains_d[(id, domain)] = 1
-        conn.set_many(
-            'user_roles', event.oid, (), ('id', 'role',), roles_d.keys())
-        conn.set_many(
-            'user_domains', event.oid, (), ('id', 'domain',), domains_d.keys())
+        conn.get_table('user_roles').set_many(
+            event.oid, (), ('id', 'role',), roles_d.keys())
+        conn.get_table('user_domains').set_many(
+            event.oid, (), ('id', 'domain',), domains_d.keys())
         state = list(state)
         state.sort()
         return tuple(state)


=== Products/Ape/lib/apelib/sql/sqlbase.py 1.13 => 1.14 ===
--- Products/Ape/lib/apelib/sql/sqlbase.py:1.13	Wed Mar 24 22:17:08 2004
+++ Products/Ape/lib/apelib/sql/sqlbase.py	Wed Jul 21 02:38:05 2004
@@ -16,7 +16,9 @@
 $Id$
 """
 
-from apelib.core.interfaces import IGateway, IDatabaseInitializer
+from apelib.core.interfaces \
+     import IGateway, IDatabaseInitializer, IDatabaseInitEvent
+from apelib.core.schemas import ColumnSchema, RowSequenceSchema
 from interfaces import IRDBMSConnection
 
 
@@ -25,33 +27,42 @@
 
     __implements__ = IGateway, IDatabaseInitializer
 
-    table = '(override this)'
-    schema = None       # override
-    column_defs = None  # optional override
-    oid_column_def = (('oid', 'int', 1),)
+    # override these in subclasses
+    table_name = None
+    schema = None
+    table_schema = None
+    oid_columns = [ColumnSchema('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.get_column_defs())
-        self.columns = tuple([name for name, t, u in self.column_defs])
+        if self.table_schema is None:
+            if self.schema is not None:
+                self.table_schema = self.schema
+            else:
+                self.table_schema = RowSequenceSchema()
+        self.column_names = [f.name for f in self.table_schema.get_columns()]
 
     def get_connection(self, event):
         return event.connections[self.conn_name]
 
+    def get_table(self, event):
+        c = event.connections[self.conn_name]
+        return c.get_table(self.table_name)
+
     def create(self, event):
-        conn = self.get_connection(event)
-        defs = self.oid_column_def + self.column_defs
-        conn.create_table(self.table, defs)
+        self.get_table(event).create()
 
     def init(self, event):
         conn = self.get_connection(event)
         assert IRDBMSConnection.isImplementedBy(conn)
-        if conn.exists(self.table, 'table'):
-            if event.clear_all:
-                conn.delete_from(self.table)
+        all = RowSequenceSchema(
+            self.oid_columns + self.table_schema.get_columns())
+        table = conn.define_table(self.table_name, all)
+        if conn.exists(self.table_name, 'table'):
+            if IDatabaseInitEvent.isImplementedBy(event) and event.clear_all:
+                table.delete_rows()
         else:
-            self.create(event)
+            table.create()
 
     def load(self, event):
         raise NotImplementedError, "abstract method"


=== Products/Ape/lib/apelib/sql/structure.py 1.11 => 1.12 ===
--- Products/Ape/lib/apelib/sql/structure.py:1.11	Sat Mar 20 01:34:23 2004
+++ Products/Ape/lib/apelib/sql/structure.py	Wed Jul 21 02:38:05 2004
@@ -16,7 +16,7 @@
 $Id$
 """
 
-from apelib.core.schemas import FieldSchema, RowSequenceSchema
+from apelib.core.schemas import ColumnSchema, RowSequenceSchema
 from sqlbase import SQLGatewayBase
 
 
@@ -25,27 +25,27 @@
 
     __implements__ = SQLGatewayBase.__implements__
 
-    schema = FieldSchema('data', 'string')
-    table = 'object_data'
-    column_defs = (
-        ('data', 'blob', 0),
-        )
+    schema = ColumnSchema('data', 'string')
+    table_name = 'object_data'
+    table_schema = RowSequenceSchema()
+    table_schema.add('data', 'blob', 0)
 
     def load(self, event):
-        conn = self.get_connection(event)
-        firstcol = self.columns[:1]
-        items = conn.select(self.table, firstcol, oid=event.oid)
+        table = self.get_table(event)
+        firstcol = self.column_names[:1]
+        items = table.select(firstcol, oid=event.oid)
         if items:
-            state = items[0][0]
+            state = str(items[0][0])
         else:
             state = ''
         return state, state
 
     def store(self, event, state):
         conn = self.get_connection(event)
-        firstcol = (self.column_defs[0][0],)
+        table = self.get_table(event)
+        firstcol = self.column_names[:1]
         data = (conn.module.Binary(state),)
-        conn.set_one(self.table, event.oid, firstcol, data, event.is_new)
+        table.set_one(event.oid, firstcol, data, event.is_new)
         return state
 
 
@@ -58,16 +58,14 @@
     schema.add('key', 'string', 1)
     schema.add('oid', 'string')
     schema.add('classification', 'classification')
-
-    table = 'folder_items'
-    column_defs = (
-        ('name', 'string', 1),
-        ('child_oid', 'int', 0),
-        )
+    table_name = 'folder_items'
+    table_schema = RowSequenceSchema()
+    table_schema.add('name', 'string', 1)
+    table_schema.add('child_oid', 'int', 0)
 
     def load(self, event):
-        conn = self.get_connection(event)
-        rows = conn.select(self.table, self.columns, oid=event.oid)
+        table = self.get_table(event)
+        rows = table.select(self.column_names, oid=event.oid)
         res = []
         h = []
         for name, child_oid in rows:
@@ -79,12 +77,12 @@
         return res, tuple(h)
 
     def store(self, event, state):
-        conn = self.get_connection(event)
+        table = self.get_table(event)
         rows = [(name, long(child_oid)) for (name, child_oid, cls) in state]
         rows.sort()
         # Note that set_many() requires the child_oid column to match
         # its database type.
-        conn.set_many(self.table, event.oid, ('name',), ('child_oid',), rows)
+        table.set_many(event.oid, ('name',), ('child_oid',), rows)
         return tuple(rows)
 
 
@@ -97,20 +95,18 @@
 
     __implements__ = SQLGatewayBase.__implements__
 
-    schema = FieldSchema('id', 'string')
-    table = 'folder_items'
-
-    column_defs = (
-        ('child_oid', 'int', 1),
-        ('name', 'string', 0),
-        )
+    schema = ColumnSchema('id', 'string')
+    table_name = 'folder_items'
+    table_schema = RowSequenceSchema()
+    table_schema.add('child_oid', 'int', 1)
+    table_schema.add('name', 'string', 0)
 
     def init(self, event):
         pass
 
     def load(self, event):
-        conn = self.get_connection(event)
-        rows = conn.select(self.table, ('name',), child_oid=event.oid)
+        table = self.get_table(event)
+        rows = table.select(('name',), child_oid=event.oid)
         if len(rows) >= 1:
             name = rows[0][0]  # Accept only the first result
         else:
@@ -127,10 +123,9 @@
 
     __implements__ = SQLGatewayBase.__implements__
 
-    table = 'remainder'
-    column_defs = (
-        ('pickle', 'blob', 0),
-        )
+    table_name = 'remainder'
+    table_schema = RowSequenceSchema()
+    table_schema.add('pickle', 'blob', 0)
 
 
 class SQLModTime (SQLGatewayBase):
@@ -138,15 +133,14 @@
 
     __implements__ = SQLGatewayBase.__implements__
 
-    schema = FieldSchema('mtime', 'int')  # second
-    table = 'mtime'
-    column_defs = (
-        ('mtime', 'long', 0),
-        )
+    schema = ColumnSchema('mtime', 'int')  # second
+    table_name = 'mtime'
+    table_schema = RowSequenceSchema()
+    table_schema.add('mtime', 'long', 0)
 
     def load(self, event):
-        conn = self.get_connection(event)
-        items = conn.select(self.table, self.columns, oid=event.oid)
+        table = self.get_table(event)
+        items = table.select(self.column_names, oid=event.oid)
         if items:
             state = long(items[0][0])
         else:
@@ -155,9 +149,9 @@
 
     def store(self, event, state):
         state = long(state)
-        conn = self.get_connection(event)
+        table = self.get_table(event)
         data = (state,)
-        conn.set_one(self.table, event.oid, self.columns, data, event.is_new)
+        table.set_one(event.oid, self.column_names, data, event.is_new)
         return state
 
 



More information about the Zope-CVS mailing list