[Zope-CVS] CVS: Products/Ape/lib/apelib/sql - dbapi.py:1.13
ingres.py:1.2 interfaces.py:1.6 properties.py:1.13
Shane Hathaway
shane at zope.com
Mon Sep 6 16:30:39 EDT 2004
Update of /cvs-repository/Products/Ape/lib/apelib/sql
In directory cvs.zope.org:/tmp/cvs-serv725/lib/apelib/sql
Modified Files:
dbapi.py ingres.py interfaces.py properties.py
Log Message:
Integrated changes for Ingres column types and queries.
Ingres is very sensitive about quoting within SQL queries. The Python
adapter also doesn't handle empty parameters.
=== Products/Ape/lib/apelib/sql/dbapi.py 1.12 => 1.13 ===
--- Products/Ape/lib/apelib/sql/dbapi.py:1.12 Wed Jul 21 02:38:05 2004
+++ Products/Ape/lib/apelib/sql/dbapi.py Mon Sep 6 16:30:39 2004
@@ -37,6 +37,7 @@
# 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
@@ -67,7 +68,9 @@
"""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)
+ factory = self.column_factories_by_name.get(c.name)
+ if factory is None:
+ factory = self.column_factories_by_type.get(c.type)
if factory is None:
factory = RDBMSColumn
dbc = factory(self, c)
@@ -141,7 +144,10 @@
end = time()
print 'SQL time: %0.6fs' % (end - start)
else:
- cursor.execute(sql, params)
+ if not params:
+ cursor.execute(sql)
+ else:
+ cursor.execute(sql, params)
if fetch:
res = list(cursor.fetchall())
if DEBUG:
@@ -272,8 +278,8 @@
return value
-class OIDColumn(RDBMSColumn):
- """RDBMS column that stores string OIDs as integers."""
+class IntColumn(RDBMSColumn):
+ """RDBMS column that stores as integers."""
__implements__ = IRDBMSColumn
use_conversion = True
@@ -285,5 +291,18 @@
return str(value)
+class LongColumn(RDBMSColumn):
+ """RDBMS column that stores as long integers."""
+ __implements__ = IRDBMSColumn
+
+ use_conversion = True
+
+ def to_db(self, value):
+ return long(value)
+
+ def from_db(self, value):
+ return str(value)
+
+
# Set up default column types.
-AbstractSQLConnection.column_factories_by_name['oid'] = OIDColumn
+AbstractSQLConnection.column_factories_by_name['oid'] = IntColumn
=== Products/Ape/lib/apelib/sql/ingres.py 1.1 => 1.2 ===
--- Products/Ape/lib/apelib/sql/ingres.py:1.1 Wed Jul 21 03:06:20 2004
+++ Products/Ape/lib/apelib/sql/ingres.py Mon Sep 6 16:30:39 2004
@@ -16,24 +16,63 @@
$Id$
"""
-from apelib.sql.dbapi import AbstractSQLConnection
+from apelib.sql import dbapi
+
+class IngresConnection (dbapi.AbstractSQLConnection):
+ """
+ Name : IngresConnection - class for Ingres
+
+ Description
+ sub-classing of all methods needed to support the Ingres
+ relational database management system.
+
+ Inputs :
+
+ Output :
+
+ Exceptions :
+
+ History:
+ 1-Jul-2004 - (emma.mcgrattan at ca.com)
+ created
+ 14-Jul-2004 - (grant.croker at ca.com)
+ modified IngresConnection.exists() to work with
+ paramstyle=qmark
+ 14-Jul-2004 - (grant.croker at ca.com)
+ subclassed create_table into IngresConnection to make use
+ of Performance features of Ingres.
+ 15-Jul-2004 - (grant.croker at ca.com)
+ Corrected Syntax of "MODIFY TABLE ... "
+ Corrected parameter passing (Changed '?' to 'table_name')
+ 18-Jul-2004 - (srisu02 at ca.com)
+ Corrected Syntax for sequence increments
+ 18-Jul-2004 - (srisu02 at ca.com)
+ Corrected Syntax for sequence fetch() i.e added fetch=1 as a parameter
+ 22-Jul-2004 - (srisu02 at ca.com)
+ Integrated changes from dbapi.py
+ Made change for cache size while creating sequences
+ """
-class IngresConnection (AbstractSQLConnection):
-
column_type_translations = {
'long': 'bigint',
'string': 'varchar(255)',
'datetime': 'time',
'boolean': 'tinyint',
}
-
+
column_name_translations = {
'oid': 'objoid',
}
-
+
+ column_factories_by_name = (
+ dbapi.AbstractSQLConnection.column_factories_by_name.copy())
+
+ column_factories_by_type = (
+ dbapi.AbstractSQLConnection.column_factories_by_type.copy())
+
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
@@ -41,41 +80,103 @@
sql = ('SELECT table_name FROM iitables '
'WHERE table_name = :name')
elif type_name == 'sequence':
- sql = ("SELECT seq_name FROM ii_sequences "
+ sql = ("SELECT seq_name FROM iisequences "
"WHERE seq_name = :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 table_name FROM ii_tables'
+ sql = 'SELECT table_name FROM iitables'
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 WITH %d" % (
+ sql = "CREATE SEQUENCE %s START WITH %d CACHE 500" % (
self.prefix + name, start)
self.execute(sql)
-
+
def reset_sequence(self, name, start=1):
"""Resets a sequence.
"""
- sql = "ALTER SEQUENCE '%s' RESTART WITH %d" % (
+ sql = "ALTER SEQUENCE %s RESTART WITH %d" % (
self.prefix + name, start)
self.execute(sql)
-
+
def increment(self, name):
"""Increments a sequence.
"""
- sql = "SELECT NEXT VALUE FOR '%s'" % (self.prefix + name)
+ sql = "SELECT NEXT VALUE FOR %s" % (self.prefix + name)
rows = self.execute(sql, fetch=1)
return rows[0][0]
+
+ def create_table(self, table, column_defs):
+ """
+ Name : IngresConnection - class for Ingres
+
+ Description
+ sub-classing of all methods needed to support the Ingres
+ relational database management system.
+
+ Inputs :
+
+ Output :
+
+ Exceptions :
+
+ History:
+ 14-Jul-2004 - (grant.croker at ca.com)
+ Created - based on AbstractSQLConnection
+
+ NOTES
+ -----
+ Ingres supports 4 table structures. Depending on the key
+ some are more preferrable than others. HEAP and ISAM are
+ being ruled out on performance and maintenance grounds.
+ BTREE is normally the best catch all solution but
+ suffers when the key is sequentially increasing. HASH is good
+ for one hit lookups but can require a more-frequent maintenance
+ routine.
+
+ The page size of the tables created is controlled by the
+ ingres_page_size variable. Valid values are: 2048, 4096,
+ 8192, 16384, 32768 and 65536.
+ """
+ ingres_page_size = 8192
+ ingres_table_structure = "BTREE"
+ table_name = self.prefix + table
+ cols = []
+ indexes = []
+ for name, typ, unique in column_defs:
+ col = self.translate_name(name)
+ db_type = self.translate_type(typ)
+ constraints = ''
+ if unique:
+ constraints = ' NOT NULL'
+ indexes.append(col)
+ cols.append("%s %s%s" % (col, db_type, constraints))
+ sql = "CREATE TABLE %s (%s)" % (table_name, ', '.join(cols))
+ self.execute(sql)
+ if indexes:
+ sql = "MODIFY %s TO %s UNIQUE ON %s WITH PAGE_SIZE=%d" % (
+ table_name, ingres_table_structure, ', '.join(indexes),
+ ingres_page_size)
+ self.execute(sql)
+ else:
+ sql = "MODIFY %s TO %s WITH PAGE_SIZE=%d" % (
+ table_name, ingres_table_structure, ingres_page_size)
+ traceback.print_stack()
+ self.execute(sql)
+
+IngresConnection.column_factories_by_type['boolean'] = dbapi.IntColumn
+IngresConnection.column_factories_by_type['int'] = dbapi.IntColumn
+IngresConnection.column_factories_by_type['long'] = dbapi.LongColumn
=== Products/Ape/lib/apelib/sql/interfaces.py 1.5 => 1.6 ===
--- Products/Ape/lib/apelib/sql/interfaces.py:1.5 Wed Jul 21 02:38:05 2004
+++ Products/Ape/lib/apelib/sql/interfaces.py Mon Sep 6 16:30:39 2004
@@ -74,7 +74,7 @@
class ISQLConnection (IRDBMSConnection):
-
+
def execute(sql, kw=None, fetch=False):
"""Executes a SQL query.
=== Products/Ape/lib/apelib/sql/properties.py 1.12 => 1.13 ===
--- Products/Ape/lib/apelib/sql/properties.py:1.12 Wed Jul 21 02:38:05 2004
+++ Products/Ape/lib/apelib/sql/properties.py Mon Sep 6 16:30:39 2004
@@ -86,7 +86,7 @@
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)
@@ -234,10 +234,9 @@
attempt = 0
while 1:
# Find an available table name.
- if not attempt:
- table_name = '%s_properties' % class_name
- else:
- table_name = '%s_%d_properties' % (class_name, attempt)
+ table_name = '%s_properties' % (class_name[:16])
+ if attempt:
+ table_name += '_%02d' % attempt
if not conn.exists(table_name, 'table'):
break
attempt += 1
More information about the Zope-CVS
mailing list