[Zope-CVS] CVS: Products/Ape/lib/apelib/sql - querygen.py:1.1 classification.py:1.2 keygen.py:1.2 pg.py:1.2 properties.py:1.2 security.py:1.2 sqlbase.py:1.2 structure.py:1.2
Shane Hathaway
shane@zope.com
Fri, 11 Apr 2003 02:17:50 -0400
Update of /cvs-repository/Products/Ape/lib/apelib/sql
In directory cvs.zope.org:/tmp/cvs-serv2565/lib/apelib/sql
Modified Files:
classification.py keygen.py pg.py properties.py security.py
sqlbase.py structure.py
Added Files:
querygen.py
Log Message:
- Added a SQL query generator and converted all SQL gateways to use
it. The current generator is designed for PostgreSQL, but it should
be simple to adapt for other databases. This also involved
backward-incompatible changes to sqlbase.py--I hope no one minds.
- Added the IRelationalSchema interface, which declares the
getColumnDefs() method. getColumnDefs() makes it easier to generate
SQL. The basic schema types now support this interface.
=== Added File Products/Ape/lib/apelib/sql/querygen.py ===
##############################################################################
#
# Copyright (c) 2003 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 generator.
$Id: querygen.py,v 1.1 2003/04/11 06:17:49 shane Exp $
"""
class QueryGenerator:
db_column_types = {
'int': 'int',
'long': 'bigint',
'string': 'character varying(255)',
'blob': 'bytea',
}
db_column_names = {}
key_column = ('key', 'int', 1)
def __init__(self, table_name, column_defs):
# column_defs is a sequence of (column_name, column_type, unique)
self.table_name = table_name
self.column_defs = (self.key_column,) + tuple(column_defs)
def translateName(self, column_name):
"""Returns a column name for a variable name.
Defaults to no translation.
"""
return self.db_column_names.get(column_name, column_name)
def translateType(self, column_type):
"""Returns a database type for a variable type.
If the type is unknown, raises KeyError.
"""
return self.db_column_types[column_type]
def generateAll(self):
res = {
'check': self.generateCheck(),
'create': self.generateCreate(),
'read': self.generateRead(),
'update': self.generateUpdate(),
'insert': self.generateInsert(),
'delete': self.generateDelete(),
'clear': self.generateClear(),
}
return res
def generateCheck(self):
kc = self.translateName(self.key_column[0])
# This is meant to raise an error if the table is missing.
# Returns nothing if the table exists.
return 'SELECT %s FROM %s WHERE 0 = 1' % (kc, self.table_name)
def generateCreate(self):
cols = []
for name, typ, u in self.column_defs:
trans_name = self.translateName(name)
db_type = self.translateType(typ)
cols.append("%s %s" % (trans_name, db_type))
return "CREATE TABLE %s (%s)" % (self.table_name, ', '.join(cols))
def generateRead(self, key_column_name=None):
cols = []
for name, typ, u in self.column_defs[1:]:
trans_name = self.translateName(name)
cols.append(trans_name)
if key_column_name is None:
key_column_name = self.key_column[0]
trans_kc = self.translateName(key_column_name)
return 'SELECT %s FROM %s WHERE %s = %%(%s)s' % (
', '.join(cols), self.table_name, trans_kc, key_column_name)
def generateUpdate(self):
update_cols = []
where_clauses = []
for name, typ, unique in self.column_defs:
trans_name = self.translateName(name)
clause = "%s = %%(%s)s" % (trans_name, name)
if unique:
# use this column to choose a record
where_clauses.append(clause)
else:
# update this column
update_cols.append(clause)
return 'UPDATE %s SET %s WHERE %s' % (
self.table_name, ', '.join(update_cols),
' and '.join(where_clauses))
def generateInsert(self):
colnames = []
colfmts = []
for name, typ, unique in self.column_defs:
trans_name = self.translateName(name)
colnames.append(trans_name)
colfmts.append('%%(%s)s' % name)
return 'INSERT INTO %s (%s) VALUES (%s)' % (
self.table_name, ', '.join(colnames), ', '.join(colfmts))
def generateDelete(self):
where_clauses = []
for name, typ, unique in self.column_defs:
if unique:
trans_name = self.translateName(name)
clause = "%s = %%(%s)s" % (trans_name, name)
where_clauses.append(clause)
return 'DELETE FROM %s WHERE %s' % (
self.table_name, ' and '.join(where_clauses))
def generateClear(self):
return 'DELETE FROM %s' % self.table_name
def generateAllForSequence(self):
table_name = self.table_name
res = {
'check': "SELECT last_value FROM %s" % table_name,
'create': "CREATE SEQUENCE %s" % table_name,
'read': "SELECT nextval('%s')" % table_name,
'clear': "SELECT setval('%s', 1)" % table_name,
}
return res
=== Products/Ape/lib/apelib/sql/classification.py 1.1 => 1.2 ===
--- Products/Ape/lib/apelib/sql/classification.py:1.1 Wed Apr 9 23:09:56 2003
+++ Products/Ape/lib/apelib/sql/classification.py Fri Apr 11 02:17:49 2003
@@ -30,32 +30,14 @@
table_base_name = 'classification'
- checkexist_sql = '''SELECT key FROM %(table)s WHERE key = 0'''
-
- create_sql = '''CREATE TABLE %(table)s (
- key int PRIMARY KEY,
- meta_type character varying(255),
- class_name character varying(255)
- )'''
-
- read_sql = '''SELECT meta_type, class_name from %(table)s
- WHERE key = %(key)s'''
-
- update_sql = '''UPDATE %(table)s
- SET meta_type=%(meta_type)s, class_name=%(class_name)s
- WHERE key = %(key)s'''
-
- insert_sql = '''INSERT INTO %(table)s
- (key, meta_type, class_name)
- VALUES (%(key)s, %(meta_type)s, %(class_name)s)'''
-
-
- def getSchema(self):
- return self.schema
+ column_defs = (
+ ('meta_type', 'string', 0),
+ ('class_name', 'string', 0),
+ )
def load(self, event):
key = long(event.getKey())
- items = self.execute(self.read_sql, 1, key=key)
+ items = self.execute('read', 1, key=key)
classification = {}
if items:
rec = items[0]
@@ -69,15 +51,15 @@
def store(self, event, classification):
key = long(event.getKey())
- items = self.execute(self.read_sql, 1, key=key)
+ items = self.execute('read', 1, key=key)
mt = classification.get('meta_type', '')
cn = classification.get('class_name', '')
kw = {'key': key, 'meta_type': mt, 'class_name': cn}
if items:
# update.
- self.execute(self.update_sql, **kw)
+ self.execute('update', **kw)
else:
# insert.
- self.execute(self.insert_sql, **kw)
+ self.execute('insert', **kw)
return (mt, cn)
=== Products/Ape/lib/apelib/sql/keygen.py 1.1 => 1.2 ===
--- Products/Ape/lib/apelib/sql/keygen.py:1.1 Wed Apr 9 23:09:56 2003
+++ Products/Ape/lib/apelib/sql/keygen.py Fri Apr 11 02:17:49 2003
@@ -27,14 +27,9 @@
table_base_name = 'key_seq'
- checkexist_sql = "SELECT last_value FROM %(table)s"
-
- create_sql = "CREATE SEQUENCE %(table)s"
-
- read_sql = "SELECT nextval('%(table)s')"
-
- clear_sql = "SELECT setval('%(table)s', 1)"
-
+ def setUpQueries(self):
+ gen = self.conn.makeQueryGenerator(self.table, ())
+ self.queries = gen.generateAllForSequence()
def makeKeychain(self, event, name, stored):
if not stored:
@@ -44,6 +39,6 @@
# Request that the other side do the work (for ZEO)
n = event.getKeyedObjectSystem().newKey()
else:
- n = self.execute(self.read_sql, 1)[0][0]
+ n = self.execute('read', 1)[0][0]
return event.getKeychain()[:-1] + (long(n),)
=== Products/Ape/lib/apelib/sql/pg.py 1.1 => 1.2 ===
--- Products/Ape/lib/apelib/sql/pg.py:1.1 Wed Apr 9 23:09:56 2003
+++ Products/Ape/lib/apelib/sql/pg.py Fri Apr 11 02:17:49 2003
@@ -18,10 +18,10 @@
import os
from time import time
-
import psycopg
from apelib.core.interfaces import ITPCConnection
+from querygen import QueryGenerator
PROFILE = os.environ.get('APE_PROFILE_PG')
@@ -40,6 +40,9 @@
self.params = params
self.prefix = prefix
self.connect_callbacks = []
+
+ def makeQueryGenerator(self, table_name, column_defs):
+ return QueryGenerator(table_name, column_defs)
def isConnected(self):
return (self.db is not None)
=== Products/Ape/lib/apelib/sql/properties.py 1.1 => 1.2 ===
--- Products/Ape/lib/apelib/sql/properties.py:1.1 Wed Apr 9 23:09:56 2003
+++ Products/Ape/lib/apelib/sql/properties.py Fri Apr 11 02:17:49 2003
@@ -34,42 +34,21 @@
table_base_name = 'properties'
- checkexist_sql = '''SELECT key FROM %(table)s WHERE key = 0'''
-
- create_sql = '''CREATE TABLE %(table)s (
- key int,
- id character varying(255),
- type character varying(255),
- data bytea
- )'''
-
- read_sql = '''SELECT id, type, data from %(table)s
- WHERE key = %(key)s'''
-
- update_sql = '''UPDATE %(table)s
- SET type = %(type)s, data = %(data)s
- WHERE key = %(key)s and id = %(id)s'''
-
- insert_sql = '''INSERT INTO %(table)s
- (key, id, type, data)
- VALUES (%(key)s, %(id)s, %(type)s, %(data)s)'''
-
- delete_sql = '''DELETE FROM %(table)s
- WHERE key = %(key)s and id = %(id)s'''
-
-
- def getSchema(self):
- return self.schema
+ column_defs = (
+ ('id', 'string', 1),
+ ('type', 'string', 0),
+ ('data', 'blob', 0),
+ )
def load(self, event):
key = long(event.getKey())
- items = self.execute(self.read_sql, 1, key=key)
+ items = self.execute('read', 1, key=key)
items.sort()
return items, tuple(items)
def store(self, event, state):
key = long(event.getKey())
- items = self.execute(self.read_sql, 1, key=key)
+ items = self.execute('read', 1, key=key)
state_dict = {}
for row in state:
id = row[0]
@@ -81,19 +60,19 @@
state_row = state_dict.get(id)
if state_row is None:
# Remove a property
- self.execute(self.delete_sql, key=key, id=id)
+ self.execute('delete', key=key, id=id)
elif old_row != state_row:
# Update a property
id, t, v = state_row
data = self.conn.asBinary(v)
- self.execute(self.update_sql,
+ self.execute('update',
key=key, id=id, type=t, data=data)
for row in state:
if not items_dict.has_key(row[0]):
# Add a property
id, t, v = row
data = self.conn.asBinary(v)
- self.execute(self.insert_sql,
+ self.execute('insert',
key=key, id=id, type=t, data=data)
state = list(state)
state.sort()
=== Products/Ape/lib/apelib/sql/security.py 1.1 => 1.2 ===
--- Products/Ape/lib/apelib/sql/security.py:1.1 Wed Apr 9 23:09:56 2003
+++ Products/Ape/lib/apelib/sql/security.py Fri Apr 11 02:17:49 2003
@@ -34,48 +34,23 @@
table_base_name = 'security'
- checkexist_sql = '''SELECT key FROM %(table)s WHERE key = 0'''
-
- create_sql = '''CREATE TABLE %(table)s (
- key int,
- declaration_type character varying(255),
- role character varying(255),
- permission character varying(255),
- username character varying(255)
- )'''
-
- read_sql = '''SELECT declaration_type, role, permission, username
- from %(table)s WHERE key = %(key)s'''
-
- insert_sql = '''INSERT INTO %(table)s
- (key, declaration_type, role, permission, username)
- VALUES (%(key)s, %(declaration_type)s, %(role)s,
- %(permission)s, %(username)s)'''
-
- delete_sql = '''DELETE FROM %(table)s
- WHERE key = %(key)s'''
-
-
- def getSchema(self):
- return self.schema
-
def load(self, event):
key = long(event.getKey())
- items = self.execute(self.read_sql, 1, key=key)
+ items = self.execute('read', 1, key=key)
items.sort()
return items, tuple(items)
def store(self, event, state):
key = long(event.getKey())
- items = self.execute(self.read_sql, 1, key=key)
+ items = self.execute('read', 1, key=key)
items.sort()
state = list(state)
state.sort()
if state != items:
- self.execute(self.delete_sql, key=key)
+ self.execute('delete', key=key)
for row in state:
self.execute(
- self.insert_sql, key=key, declaration_type=row[0],
+ 'insert', key=key, declaration_type=row[0],
role=row[1], permission=row[2], username=row[3])
return tuple(state)
@@ -94,108 +69,64 @@
table_base_name = 'users'
- checkexist_sql = '''SELECT key FROM %(table)s WHERE key = 0'''
-
- create_sql = '''CREATE TABLE %(table)s (
- key int,
- id character varying(255),
- password character varying(255)
- )'''
-
- create_roles_sql = '''CREATE TABLE %(roles_table)s (
- key int,
- id character varying(255),
- role character varying(255)
- )'''
-
- create_domains_sql = '''CREATE TABLE %(domains_table)s (
- key int,
- id character varying(255),
- domain character varying(255)
- )'''
-
- read_sql = '''SELECT id, password FROM %(table)s
- WHERE key = %(key)s'''
-
- read_roles_sql = '''SELECT id, role FROM %(roles_table)s
- WHERE key = %(key)s'''
-
- read_domains_sql = '''SELECT id, domain FROM %(domains_table)s
- WHERE key = %(key)s'''
-
- update_sql = '''UPDATE %(table)s
- SET password = %(password)s
- WHERE key = %(key)s AND id = %(id)s'''
-
- insert_sql = '''INSERT INTO %(table)s
- (key, id, password)
- VALUES (%(key)s, %(id)s, %(password)s)'''
-
- delete_sql = '''DELETE FROM %(table)s
- WHERE key = %(key)s and id = %(id)s'''
-
- delete_roles_sql = '''DELETE FROM %(roles_table)s
- WHERE key = %(key)s and id = %(id)s'''
-
- delete_domains_sql = '''DELETE FROM %(domains_table)s
- WHERE key = %(key)s and id = %(id)s'''
-
- insert_role_sql = '''INSERT INTO %(roles_table)s
- (key, id, role)
- VALUES (%(key)s, %(id)s, %(role)s)'''
-
- insert_domain_sql = '''INSERT INTO %(domains_table)s
- (key, id, domain)
- VALUES (%(key)s, %(id)s, %(domain)s)'''
+ def setUpTableNames(self):
+ SQLGatewayBase.setUpTableNames(self)
+ self.roles_table = self.conn.prefix + '_user_roles'
+ self.domains_table = self.conn.prefix + '_user_domains'
- clear_roles_sql = 'DELETE FROM %(roles_table)s'
- clear_domains_sql = 'DELETE FROM %(domains_table)s'
+ def setUpQueries(self):
+ query_groups = {}
+ column_defs = [('id', 'string', 1),
+ ('password', 'string', 0),]
+ gen = self.conn.makeQueryGenerator(self.table, column_defs)
+ query_groups['users'] = gen.generateAll()
+
+ column_defs = [('id', 'string', 1),
+ ('role', 'string', 0),]
+ gen = self.conn.makeQueryGenerator(self.roles_table, column_defs)
+ query_groups['roles'] = gen.generateAll()
+
+ column_defs = [('id', 'string', 1),
+ ('domain', 'string', 0),]
+ gen = self.conn.makeQueryGenerator(self.domains_table, column_defs)
+ query_groups['domains'] = gen.generateAll()
+ self.query_groups = query_groups
- def setupTableNames(self):
- SQLGatewayBase.setupTableNames(self)
- self.roles_table = self.conn.prefix + '_user_roles'
- self.domains_table = self.conn.prefix + '_user_domains'
- def setupTables(self):
+ def setUpTables(self):
conn = self.conn
try:
- self.execute(self.checkexist_sql)
+ self.execute('users', 'check')
except conn.error:
conn.db.rollback()
- self.execute(self.create_sql)
- self.execute(self.create_roles_sql)
- self.execute(self.create_domains_sql)
+ self.execute('users', 'create')
+ self.execute('roles', 'create')
+ self.execute('domains', 'create')
conn.db.commit()
- def execute(self, text, *args, **kw):
- text = text.replace('%(table)s', self.table)
- text = text.replace('%(roles_table)s', self.roles_table)
- text = text.replace('%(domains_table)s', self.domains_table)
- return self.conn.execute(text, *args, **kw)
+ def execute(self, table, query_name, *args, **kw):
+ query = self.query_groups[table][query_name]
+ return self.conn.execute(query, *args, **kw)
def clear(self):
- self.execute(self.clear_sql)
- self.execute(self.clear_roles_sql)
- self.execute(self.clear_domains_sql)
-
- def getSchema(self):
- return self.schema
-
+ self.execute('users', 'clear')
+ self.execute('roles', 'clear')
+ self.execute('domains', 'clear')
def load(self, event):
key = event.getKeychain()[-1]
- rows = self.execute(self.read_sql, 1, key=key)
+ rows = self.execute('users', 'read', 1, key=key)
data = {}
for id, password in rows:
data[id] = (password, [], [])
- rows = self.execute(self.read_roles_sql, 1, key=key)
+ rows = self.execute('roles', 'read', 1, key=key)
for id, role in rows:
row = data.get(id)
if row is not None:
row[1].append(role)
- rows = self.execute(self.read_domains_sql, 1, key=key)
+ rows = self.execute('domains', 'read', 1, key=key)
for id, domain in rows:
row = data.get(id)
if row is not None:
@@ -225,9 +156,9 @@
new_row = new_dict.get(id)
if new_row is None:
# Remove this row.
- self.execute(self.delete_sql, key=key, id=id)
- self.execute(self.delete_roles_sql, key=key, id=id)
- self.execute(self.delete_domains_sql, key=key, id=id)
+ self.execute('users', 'delete', key=key, id=id)
+ self.execute('roles', 'delete', key=key, id=id)
+ self.execute('domains', 'delete', key=key, id=id)
elif new_row == old_row:
# Don't need to update this row.
del new_dict[id]
@@ -240,29 +171,29 @@
old_id, old_password, old_roles, old_domains = old_row
if old_password != password:
# Update the password.
- self.execute(self.update_sql, key=key, id=id,
+ self.execute('users', 'update', key=key, id=id,
password=password)
else:
# Insert a new record.
- self.execute(self.insert_sql, key=key, id=id,
+ self.execute('users', 'insert', key=key, id=id,
password=password)
old_roles = ()
old_domains = ()
# Update the role list.
if tuple(roles) != tuple(old_roles):
- self.execute(self.delete_roles_sql, key=key, id=id)
+ self.execute('roles', 'delete', key=key, id=id)
for role in roles:
assert role
- self.execute(self.insert_role_sql, key=key, id=id,
+ self.execute('roles', 'insert', key=key, id=id,
role=role)
# Update the domain list.
if tuple(domains) != tuple(old_domains):
- self.execute(self.delete_domains_sql, key=key, id=id)
+ self.execute('domains', 'delete', key=key, id=id)
for domain in domains:
assert domain
- self.execute(self.insert_domain_sql, key=key, id=id,
+ self.execute('domains', 'insert', key=key, id=id,
domain=domain)
state = list(state)
=== Products/Ape/lib/apelib/sql/sqlbase.py 1.1 => 1.2 ===
--- Products/Ape/lib/apelib/sql/sqlbase.py:1.1 Wed Apr 9 23:09:56 2003
+++ Products/Ape/lib/apelib/sql/sqlbase.py Fri Apr 11 02:17:49 2003
@@ -20,34 +20,48 @@
"""SQL gateway base class"""
table_base_name = '(override this)'
- checkexist_sql = '(override this)'
- create_sql = '(override this)'
- clear_sql = 'DELETE FROM %(table)s'
+ schema = None # override
+ column_defs = None # optional override
def __init__(self, conn):
self.conn = conn
- self.setupTableNames()
+ self.setUpTableNames()
+ self.setUpQueries()
if conn.isConnected():
- self.setupTables()
+ self.setUpTables()
else:
- conn.addConnectCallback(self.setupTables)
+ conn.addConnectCallback(self.setUpTables)
- def setupTableNames(self):
+ def getSchema(self):
+ return self.schema
+
+ def getColumnDefs(self):
+ defs = self.column_defs
+ if defs is None:
+ defs = self.getSchema().getColumnDefs()
+ return defs
+
+ def setUpTableNames(self):
self.table = self.conn.prefix + '_' + self.table_base_name
- def setupTables(self):
+ def setUpQueries(self):
+ column_defs = self.getColumnDefs()
+ gen = self.conn.makeQueryGenerator(self.table, column_defs)
+ self.queries = gen.generateAll()
+
+ def setUpTables(self):
conn = self.conn
try:
- self.execute(self.checkexist_sql)
+ self.execute('check')
except conn.error:
conn.db.rollback()
- self.execute(self.create_sql)
+ self.execute('create')
conn.db.commit()
- def execute(self, text, *args, **kw):
- text = text.replace('%(table)s', self.table) # XXX workaround
- return self.conn.execute(text, *args, **kw)
+ def execute(self, query_name, *args, **kw):
+ query = self.queries[query_name]
+ return self.conn.execute(query, *args, **kw)
def clear(self):
- self.execute(self.clear_sql)
+ self.execute('clear')
=== Products/Ape/lib/apelib/sql/structure.py 1.1 => 1.2 ===
--- Products/Ape/lib/apelib/sql/structure.py:1.1 Wed Apr 9 23:09:56 2003
+++ Products/Ape/lib/apelib/sql/structure.py Fri Apr 11 02:17:49 2003
@@ -30,31 +30,13 @@
table_base_name = 'object_data'
- checkexist_sql = '''SELECT key FROM %(table)s WHERE key = 0'''
-
- create_sql = '''CREATE TABLE %(table)s (
- key int PRIMARY KEY,
- data bytea
- )'''
-
- read_sql = '''SELECT data from %(table)s
- WHERE key = %(key)s'''
-
- update_sql = '''UPDATE %(table)s
- SET data = %(data)s
- WHERE key = %(key)s'''
-
- insert_sql = '''INSERT INTO %(table)s
- (key, data)
- VALUES (%(key)s, %(data)s)'''
-
-
- def getSchema(self):
- return self.schema
+ column_defs = (
+ ('data', 'blob', 0),
+ )
def load(self, event):
key = long(event.getKey())
- items = self.execute(self.read_sql, 1, key=key)
+ items = self.execute('read', 1, key=key)
if items:
state = items[0][0]
else:
@@ -63,14 +45,15 @@
def store(self, event, state):
key = long(event.getKey())
- items = self.execute(self.read_sql, 1, key=key)
- kw = {'key': key, 'data': self.conn.asBinary(state)}
+ items = self.execute('read', 1, key=key)
+ col_name = self.column_defs[0][0]
+ kw = {'key': key, col_name: self.conn.asBinary(state)}
if items:
# update.
- self.execute(self.update_sql, **kw)
+ self.execute('update', **kw)
else:
# insert.
- self.execute(self.insert_sql, **kw)
+ self.execute('insert', **kw)
return state
@@ -85,37 +68,16 @@
table_base_name = 'folder_items'
- checkexist_sql = '''SELECT key FROM %(table)s WHERE key = 0'''
-
- create_sql = '''CREATE TABLE %(table)s (
- key int,
- name character varying(255),
- child_key int
- )'''
-
- read_sql = '''SELECT name, child_key FROM %(table)s
- WHERE key = %(key)s'''
-
- update_sql = '''UPDATE %(table)s
- SET child_key=%(child_key)s
- WHERE key = %(key)s and name = %(name)s'''
-
- insert_sql = '''INSERT INTO %(table)s
- (key, name, child_key)
- VALUES (%(key)s, %(name)s, %(child_key)s)'''
-
- delete_sql = '''DELETE FROM %(table)s
- WHERE key = %(key)s and name = %(name)s'''
-
-
- def getSchema(self):
- return self.schema
+ column_defs = (
+ ('name', 'string', 1),
+ ('child_key', 'int', 0),
+ )
def load(self, event):
keychain = event.getKeychain()
key = long(keychain[-1])
prefix = keychain[:-1]
- rows = self.execute(self.read_sql, 1, key=key)
+ rows = self.execute('read', 1, key=key)
rows.sort()
res = [(row[0], prefix + (long(row[1]),)) for row in rows]
return res, tuple(res)
@@ -128,14 +90,14 @@
for name, child_keychain in state:
state_dict[name] = 1
- rows = self.execute(self.read_sql, 1, key=key)
+ rows = self.execute('read', 1, key=key)
db_dict = {}
for name, child_key in rows:
if state_dict.has_key(name):
db_dict[name] = child_key
else:
# Remove this item from the database.
- self.execute(self.delete_sql, key=key, name=name)
+ self.execute('delete', key=key, name=name)
state = list(state)
state.sort()
@@ -148,10 +110,10 @@
if db_dict.has_key(name):
if db_dict[name] != child_key:
# Change this item to point to a different OID.
- self.execute(self.update_sql, **kw)
+ self.execute('update', **kw)
else:
# Add this item to the database.
- self.execute(self.insert_sql, **kw)
+ self.execute('insert', **kw)
return tuple(state)
@@ -168,18 +130,24 @@
table_base_name = 'folder_items'
- read_sql = 'SELECT name from %(table)s WHERE child_key = %(child_key)s'
+ def setUpQueries(self):
+ column_defs = (('name', 'string', 0),)
+ gen = self.conn.makeQueryGenerator(self.table, column_defs)
+ self.queries = {
+ 'read': gen.generateRead(key_column_name='child_key'),
+ }
- def setupTables(self):
+ def setUpTables(self):
# No action necessary
pass
- def getSchema(self):
- return self.schema
+ def clear(self):
+ # Don't clear anything
+ pass
def load(self, event):
key = long(event.getKey())
- rows = self.execute(self.read_sql, 1, child_key=key)
+ rows = self.execute('read', 1, child_key=key)
assert len(rows) >= 1
name = rows[0][0] # Other names will be ignored
return name, None
@@ -196,21 +164,9 @@
table_base_name = 'remainder'
- create_sql = '''CREATE TABLE %(table)s (
- key int PRIMARY KEY,
- pickle bytea
- )'''
-
- read_sql = '''SELECT pickle from %(table)s
- WHERE key = %(key)s'''
-
- update_sql = '''UPDATE %(table)s
- SET pickle = %(data)s
- WHERE key = %(key)s'''
-
- insert_sql = '''INSERT INTO %(table)s
- (key, pickle)
- VALUES (%(key)s, %(data)s)'''
+ column_defs = (
+ ('pickle', 'blob', 0),
+ )
class SQLModTime (SQLGatewayBase):
@@ -222,31 +178,13 @@
table_base_name = 'mtime'
- checkexist_sql = '''SELECT key FROM %(table)s WHERE key = 0'''
-
- create_sql = '''CREATE TABLE %(table)s (
- key int PRIMARY KEY,
- mtime bigint
- )'''
-
- read_sql = '''SELECT mtime from %(table)s
- WHERE key = %(key)s'''
-
- update_sql = '''UPDATE %(table)s
- SET mtime = %(mtime)d
- WHERE key = %(key)s'''
-
- insert_sql = '''INSERT INTO %(table)s
- (key, mtime)
- VALUES (%(key)s, %(mtime)d)'''
-
-
- def getSchema(self):
- return self.schema
+ column_defs = (
+ ('mtime', 'long', 0),
+ )
def load(self, event):
key = long(event.getKey())
- items = self.execute(self.read_sql, 1, key=key)
+ items = self.execute('read', 1, key=key)
if items:
state = long(items[0][0])
else:
@@ -256,13 +194,13 @@
def store(self, event, state):
key = long(event.getKey())
state = long(state)
- items = self.execute(self.read_sql, 1, key=key)
+ items = self.execute('read', 1, key=key)
kw = {'key': key, 'mtime': state}
if items:
# update.
- self.execute(self.update_sql, **kw)
+ self.execute('update', **kw)
else:
# insert.
- self.execute(self.insert_sql, **kw)
+ self.execute('insert', **kw)
return state