[Zope-CVS] CVS: Products/Ape/lib/apelib/sql - mysql.py:1.1.4.1
postgresql.py:1.1.2.1 dbapi.py:1.11.2.3 interfaces.py:1.4.2.3
oidgen.py:1.5.2.3 security.py:1.8.2.3 sqlbase.py:1.13.2.3
table.py:1.1.2.3
Shane Hathaway
shane at zope.com
Wed Jul 21 00:56:18 EDT 2004
Update of /cvs-repository/Products/Ape/lib/apelib/sql
In directory cvs.zope.org:/tmp/cvs-serv5818/sql
Modified Files:
Tag: sql-types-branch
dbapi.py interfaces.py oidgen.py security.py sqlbase.py
table.py
Added Files:
Tag: sql-types-branch
mysql.py postgresql.py
Log Message:
Another checkpoint:
- Implemented AbstractSQLConnection.define_table(), which now
coordinates translation to specific databases.
- Moved MySQL and PostgreSQL classes to their own modules.
- Laid a foundation for prepared queries.
- Fixed bugs in the new code.
=== Added File Products/Ape/lib/apelib/sql/mysql.py ===
##############################################################################
#
# 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: mysql.py,v 1.1.4.1 2004/07/21 04:55:47 shane Exp $
"""
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]
=== Added File Products/Ape/lib/apelib/sql/postgresql.py ===
##############################################################################
#
# 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: postgresql.py,v 1.1.2.1 2004/07/21 04:55:47 shane Exp $
"""
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/dbapi.py 1.11.2.2 => 1.11.2.3 ===
--- Products/Ape/lib/apelib/sql/dbapi.py:1.11.2.2 Tue Jul 20 22:25:58 2004
+++ Products/Ape/lib/apelib/sql/dbapi.py Wed Jul 21 00:55:47 2004
@@ -21,8 +21,9 @@
from time import time
from apelib.core.interfaces import ITPCConnection
-
+from apelib.core.schemas import ColumnSchema
from apelib.sql.interfaces import ISQLConnection
+from apelib.sql.table import SQLTable
name_style_re = re.compile(':[A-Za-z0-9_-]+')
@@ -34,20 +35,25 @@
__implements__ = ISQLConnection, ITPCConnection
- #column_type_translations = {} # { local type name -> db type name }
- #column_name_translations = {} # { local col name -> db col name }
+ # factories by column name take precedence over factories by column type.
+ column_factories_by_name = {} # { local col name -> column factory }
+ column_factories_by_type = {} # { local type 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
- tables = None
def __init__(self, module_name, connect_expression, prefix=''):
# 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._tables = {}
self._final = 0
def __repr__(self):
@@ -58,6 +64,30 @@
# IRDBMSConnection implementation.
#
+ def define_table(self, name, schema):
+ """Creates and returns an IRDBMSTable."""
+ t = 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 = self.column_factories_by_type.get(c.type, None)
+ if factory is None:
+ factory = RDBMSColumn
+ dbc = factory(c)
+ name = self.column_name_translations.get(c.name)
+ if name is not None:
+ dbc.name = name
+ type = self.column_name_translations.get(c.type)
+ if type is not None:
+ dbc.type = type
+ t.add_column(c.name, dbc)
+ self._tables[name] = t
+ return t
+
+ 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.
@@ -88,10 +118,17 @@
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:
@@ -155,179 +192,97 @@
-# 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.
+ def __init__(self, column):
+ self.name = column.name
+ self.type = column.type
+ self.unique = column.unique
- 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 to_db(self, connection, value):
+ return value
- def create_sequence(self, name, start=1):
- """Creates a sequence.
- """
- sql = "CREATE SEQUENCE %s START %d" % (self.prefix + name, start)
- self.execute(sql)
+ def from_db(self, connection, value):
+ return value
- 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 OIDColumn(RDBMSColumn):
+ """RDBMS column that stores string OIDs as integers."""
+ __implements__ = IRDBMSColumn
+ def to_db(self, connection, value):
+ return int(value)
+ def from_db(self, connection, value):
+ return str(value)
-class MySQLConnection (AbstractSQLConnection):
- column_type_translations = {
- 'long': 'bigint',
- 'string': 'character varying(255)',
- 'blob': 'longblob',
- 'boolean': 'tinyint(1)',
- }
+# Set up default column types.
+AbstractSQLConnection.column_factories_by_name['oid'] = OIDColumn
- 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]
+# Names imported for backward compatibility only.
+from apelib.sql.postgresql import PostgreSQLConnection
+from apelib.sql.mysql import MySQLConnection
=== Products/Ape/lib/apelib/sql/interfaces.py 1.4.2.2 => 1.4.2.3 ===
--- Products/Ape/lib/apelib/sql/interfaces.py:1.4.2.2 Tue Jul 20 22:25:58 2004
+++ Products/Ape/lib/apelib/sql/interfaces.py Wed Jul 21 00:55:47 2004
@@ -32,13 +32,6 @@
connector = Attribute("connector", "The shared DB-API connection")
- def exists(name, type_name):
- """Returns true if the specified database object exists.
-
- 'name' is the name of the object. 'type_name' is 'table' or
- 'sequence'.
- """
-
def define_table(name, schema):
"""Creates and returns an IRDBMSTable.
@@ -49,6 +42,13 @@
def get_table(name):
"""Returns a previously defined IRDBMSTable."""
+ def exists(name, type_name):
+ """Returns true if the specified database object exists.
+
+ 'name' is the name of the object. 'type_name' is 'table' or
+ 'sequence'.
+ """
+
def list_table_names():
"""Returns a list of existing table names."""
@@ -78,9 +78,6 @@
class IRDBMSTable (Interface):
"""A table in a database."""
-
- def exists():
- """Returns true if this table exists in the database."""
def select(result_col_names, **filter):
"""Selects rows from a table and returns column values for those rows.
=== Products/Ape/lib/apelib/sql/oidgen.py 1.5.2.2 => 1.5.2.3 ===
--- Products/Ape/lib/apelib/sql/oidgen.py:1.5.2.2 Tue Jul 20 22:25:58 2004
+++ Products/Ape/lib/apelib/sql/oidgen.py Wed Jul 21 00:55:47 2004
@@ -37,6 +37,7 @@
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_name)
=== Products/Ape/lib/apelib/sql/security.py 1.8.2.2 => 1.8.2.3 ===
--- Products/Ape/lib/apelib/sql/security.py:1.8.2.2 Tue Jul 20 22:25:58 2004
+++ Products/Ape/lib/apelib/sql/security.py Wed Jul 21 00:55:47 2004
@@ -80,7 +80,7 @@
for args in columns:
table_schema.add(*args)
table = conn.define_table(table_name, table_schema)
- if not table.exists():
+ if not conn.exists(table_name, 'table'):
table.create()
elif event.clear_all:
table.delete_rows()
=== Products/Ape/lib/apelib/sql/sqlbase.py 1.13.2.2 => 1.13.2.3 ===
--- Products/Ape/lib/apelib/sql/sqlbase.py:1.13.2.2 Tue Jul 20 22:25:58 2004
+++ Products/Ape/lib/apelib/sql/sqlbase.py Wed Jul 21 00:55:47 2004
@@ -56,7 +56,7 @@
assert IRDBMSConnection.isImplementedBy(conn)
all = RowSequenceSchema(self.oid_columns + self.table_schema.get_columns())
table = conn.define_table(self.table_name, all)
- if table.exists():
+ if conn.exists(self.table_name, 'table'):
if event.clear_all:
table.delete_rows()
else:
=== Products/Ape/lib/apelib/sql/table.py 1.1.2.2 => 1.1.2.3 ===
--- Products/Ape/lib/apelib/sql/table.py:1.1.2.2 Tue Jul 20 22:25:58 2004
+++ Products/Ape/lib/apelib/sql/table.py Wed Jul 21 00:55:47 2004
@@ -36,6 +36,11 @@
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]
@@ -50,10 +55,10 @@
return sql
def generate_insert(self, col_names):
- names = [self.columns[c].name for c in 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(names), ', '.join(colfmts))
+ self.name, ', '.join(db_names), ', '.join(colfmts))
def generate_update(self, key_col_names, other_col_names):
where = self.generate_conditions(key_col_names)
@@ -77,7 +82,7 @@
c = col_names[n]
res[c] = self.columns[c].to_db(data[n])
if oid is not None:
- res['oid'] = self.columns[c].to_db(oid)
+ res['oid'] = self.columns['oid'].to_db(oid)
return res
#
@@ -87,18 +92,18 @@
def select(self, result_col_names, **filter):
"""Selects rows from a table and returns column values for those rows.
"""
- sql = self.generate_select(filter.keys(), result_col_names)
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 result to standard types.
res = []
- from_db = []
+ dbcols = []
for n in range(len(result_col_names)):
- from_db.append((n, self.columns[result_col_names[n]].from_db))
+ dbcols.append((n, self.columns[result_col_names[n]].from_db))
for row in db_res:
- r = [from_db(row[n]) for (n, column) in dbcols]
+ r = [from_db(row[n]) for (n, from_db) in dbcols]
res.append(tuple(r))
return res
@@ -106,7 +111,7 @@
"""Inserts one row in the table.
"""
kw = self.prepare_for_db(col_names, row)
- sql = self.generate_insert(col_names)
+ sql = self.cache(self.generate_insert, col_names)
self.execute(sql, kw)
def set_one(self, oid, col_names, row, is_new):
@@ -118,10 +123,10 @@
"""
kw = self.prepare_for_db(col_names, row, oid)
if is_new:
- sql = self.generate_insert(('oid',) + tuple(col_names))
+ sql = self.cache(self.generate_insert, ('oid',) + tuple(col_names))
self.execute(sql, kw)
else:
- sql = self.generate_update(('oid',), col_names)
+ 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):
@@ -137,10 +142,11 @@
combo = tuple(key_col_names) + tuple(other_col_names)
if not key_col_names:
# Don't compare rows. Just delete and insert.
- sql = self.generate_delete(('oid',))
- self.execute(sql, {'oid': oid})
+ 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:
- sql = self.generate_insert(('oid',) + combo)
kw = self.prepare_for_db(combo, row, oid)
self.execute(sql, kw)
return
@@ -161,28 +167,29 @@
for key, value in existing.items():
if not now.has_key(key):
# Delete this row.
- sql = self.generate_delete(('oid',) + tuple(key_col_names))
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
- sql = self.generate_update(
- ('oid',) + tuple(key_col_names), other_col_names)
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.
- sql = self.generate_insert(('oid',) + combo)
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.generate_delete(filter.keys())
+ sql = self.cache(self.generate_delete, filter.keys())
self.execute(sql, filter)
def create(self):
@@ -207,4 +214,3 @@
"""
sql = "DROP TABLE %s" % self.name
self.execute(sql)
-
More information about the Zope-CVS
mailing list