[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