[Zope-CVS] CVS: Products/Ape/lib/apelib/sql - __init__.py:1.1 classification.py:1.1 keygen.py:1.1 pg.py:1.1 properties.py:1.1 security.py:1.1 sqlbase.py:1.1 structure.py:1.1

Shane Hathaway shane@zope.com
Wed, 9 Apr 2003 23:09:57 -0400


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

Added Files:
	__init__.py classification.py keygen.py pg.py properties.py 
	security.py sqlbase.py structure.py 
Log Message:
Moved apelib into a "lib" subdirectory.  This simplified the
Python hacking required to make apelib a top-level package.  Sorry
about the flood of checkins, but CVS makes a move like this quite painful.


=== Added File Products/Ape/lib/apelib/sql/__init__.py ===
##############################################################################
#
# Copyright (c) 2002 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.
#
##############################################################################
"""RDBMS gateway package

$Id: __init__.py,v 1.1 2003/04/10 03:09:56 shane Exp $
"""


=== Added File Products/Ape/lib/apelib/sql/classification.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 classification gateway

$Id: classification.py,v 1.1 2003/04/10 03:09:56 shane Exp $
"""

from apelib.core.interfaces import IGateway
from apelib.core.schemas import FieldSchema
from apelib.core.exceptions import NoStateFoundError
from sqlbase import SQLGatewayBase


class SQLClassification (SQLGatewayBase):

    __implements__ = IGateway

    schema = FieldSchema('classification', 'classification')

    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

    def load(self, event):
        key = long(event.getKey())
        items = self.execute(self.read_sql, 1, key=key)
        classification = {}
        if items:
            rec = items[0]
            if rec[0]:
                classification['meta_type'] = rec[0]
            if rec[1]:
                classification['class_name'] = rec[1]
        else:
            raise NoStateFoundError(key)
        return classification, rec

    def store(self, event, classification):
        key = long(event.getKey())
        items = self.execute(self.read_sql, 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)
        else:
            # insert.
            self.execute(self.insert_sql, **kw)
        return (mt, cn)



=== Added File Products/Ape/lib/apelib/sql/keygen.py ===
##############################################################################
#
# Copyright (c) 2002 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 keychain generator

$Id: keygen.py,v 1.1 2003/04/10 03:09:56 shane Exp $
"""

from apelib.core.interfaces import IKeychainGenerator, ISDEvent
from apelib.core.schemas import FieldSchema
from sqlbase import SQLGatewayBase


class SQLKeychainGenerator (SQLGatewayBase):

    __implements__ = IKeychainGenerator

    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 makeKeychain(self, event, name, stored):
        if not stored:
            raise RuntimeError(
                'Keychains generated by SQLKeychain must be stored')
        if ISDEvent.isImplementedBy(event):
            # Request that the other side do the work (for ZEO)
            n = event.getKeyedObjectSystem().newKey()
        else:
            n = self.execute(self.read_sql, 1)[0][0]
        return event.getKeychain()[:-1] + (long(n),)



=== Added File Products/Ape/lib/apelib/sql/pg.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.
#
##############################################################################
"""Psycopg connectivity

$Id: pg.py,v 1.1 2003/04/10 03:09:56 shane Exp $
"""

import os
from time import time

import psycopg

from apelib.core.interfaces import ITPCConnection

PROFILE = os.environ.get('APE_PROFILE_PG')


class PsycopgConnection:

    __implements__ = ITPCConnection

    _final = 0
    db = None
    cursor = None

    def __init__(self, params='', prefix='zodb'):
        self.error = psycopg.DatabaseError
        self.Binary = psycopg.Binary
        self.params = params
        self.prefix = prefix
        self.connect_callbacks = []

    def isConnected(self):
        return (self.db is not None)

    def addConnectCallback(self, f):
        self.connect_callbacks.append(f)

    def connect(self):
        import psycopg
        self.db = psycopg.connect(self.params)
        self.cursor = self.db.cursor()
        for f in self.connect_callbacks:
            f()
        self.connect_callbacks = []

    def sortKey(self):
        return repr(self)

    def getName(self):
        return repr(self)

    def __repr__(self):
        return 'PsycopgConnection(%s, %s)' % (
            repr(self.params), repr(self.prefix))

    def execute(self, text, fetch=0, cursor=None, **kw):
        if cursor is None:
            cursor = self.cursor
            if cursor is None:
                raise RuntimeError('Not connected')
        if PROFILE:
            start = time()
            cursor.execute(text, kw)
            end = time()
            print 'PG: %0.6fs: %s' % (end - start, text)
        else:
            cursor.execute(text, kw)
        if fetch:
            return cursor.fetchall()
        return None

    def asBinary(self, data):
        return self.Binary(data)

    def begin(self):
        pass

    def vote(self):
        self._final = 1

    def reset(self):
        self._final = 0

    def abort(self):
        try:
            self.db.rollback()
        finally:
            self.reset()

    def finish(self):
        if self._final:
            try:
                self.db.commit()
            finally:
                self.reset()

    def close(self):
        if self.isConnected():
            self.cursor.close()
            self.cursor = None
            self.db.close()
            self.db = None



=== Added File Products/Ape/lib/apelib/sql/properties.py ===
##############################################################################
#
# Copyright (c) 2002 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 properties

$Id: properties.py,v 1.1 2003/04/10 03:09:56 shane Exp $
"""

from apelib.core.interfaces import IGateway
from apelib.core.schemas import RowSequenceSchema
from sqlbase import SQLGatewayBase


class SQLProperties (SQLGatewayBase):
    """SQL properties gateway
    """

    __implements__ = IGateway

    schema = RowSequenceSchema()
    schema.addField('id', 'string', 1)
    schema.addField('type', 'string')
    schema.addField('data', 'string')

    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

    def load(self, event):
        key = long(event.getKey())
        items = self.execute(self.read_sql, 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)
        state_dict = {}
        for row in state:
            id = row[0]
            state_dict[id] = row
        items_dict = {}
        for old_row in items:
            id = old_row[0]
            items_dict[id] = old_row
            state_row = state_dict.get(id)
            if state_row is None:
                # Remove a property
                self.execute(self.delete_sql, 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,
                             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,
                             key=key, id=id, type=t, data=data)
        state = list(state)
        state.sort()
        return tuple(state)



=== Added File Products/Ape/lib/apelib/sql/security.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 gateways for security information.

$Id: security.py,v 1.1 2003/04/10 03:09:56 shane Exp $
"""

from apelib.core.interfaces import IGateway
from apelib.core.schemas import RowSequenceSchema
from sqlbase import SQLGatewayBase


class SQLSecurityAttributes (SQLGatewayBase):
    """SQL security attribute storage"""

    __implements__ = IGateway

    schema = RowSequenceSchema()
    schema.addField('declaration_type', 'string')
    schema.addField('role', 'string')
    schema.addField('permission', 'string')
    schema.addField('username', 'string')

    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.sort()
        return items, tuple(items)

    def store(self, event, state):
        key = long(event.getKey())
        items = self.execute(self.read_sql, 1, key=key)
        items.sort()
        state = list(state)
        state.sort()
        if state != items:
            self.execute(self.delete_sql, key=key)
            for row in state:
                self.execute(
                    self.insert_sql, key=key, declaration_type=row[0],
                    role=row[1], permission=row[2], username=row[3])
        return tuple(state)



class SQLUserList (SQLGatewayBase):
    """Stores and retrieves all users for a folder at once."""

    __implements__ = IGateway

    schema = RowSequenceSchema()
    schema.addField('id', 'string', 1)
    schema.addField('password', 'string')
    schema.addField('roles', 'string:list')
    schema.addField('domains', 'string:list')

    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)'''

    clear_roles_sql = 'DELETE FROM %(roles_table)s'

    clear_domains_sql = 'DELETE FROM %(domains_table)s'


    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):
        conn = self.conn
        try:
            self.execute(self.checkexist_sql)
        except conn.error:
            conn.db.rollback()
            self.execute(self.create_sql)
            self.execute(self.create_roles_sql)
            self.execute(self.create_domains_sql)
            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 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


    def load(self, event):
        key = event.getKeychain()[-1]
        rows = self.execute(self.read_sql, 1, key=key)
        data = {}
        for id, password in rows:
            data[id] = (password, [], [])
        rows = self.execute(self.read_roles_sql, 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)
        for id, domain in rows:
            row = data.get(id)
            if row is not None:
                row[2].append(domain)
        records = []
        for id, (password, roles, domains) in data.items():
            records.append((id, password, tuple(roles), tuple(domains)))
        records.sort()
        return records, tuple(records)


    def store(self, event, state):
        keychain = event.getKeychain()
        keychain1 = keychain[:-1]
        key = long(keychain[-1])
        old_rows, old_serial = self.load(event)
        new_dict = {}
        for rec in state:
            new_dict[rec[0]] = rec
        old_dict = {}
        for rec in old_rows:
            old_dict[rec[0]] = rec

        # Compare / remove rows.
        for old_row in old_rows:
            id = old_row[0]
            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)
            elif new_row == old_row:
                # Don't need to update this row.
                del new_dict[id]

        # Insert / update rows.
        for new_row in new_dict.values():
            id, password, roles, domains = new_row
            old_row = old_dict.get(id)
            if old_row is not None:
                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,
                                 password=password)
            else:
                # Insert a new record.
                self.execute(self.insert_sql, 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)
                for role in roles:
                    assert role
                    self.execute(self.insert_role_sql, 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)
                for domain in domains:
                    assert domain
                    self.execute(self.insert_domain_sql, key=key, id=id,
                                 domain=domain)
                
        state = list(state)
        state.sort()
        return tuple(state)



=== Added File Products/Ape/lib/apelib/sql/sqlbase.py ===
##############################################################################
#
# Copyright (c) 2002 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.
#
##############################################################################
"""Abstract gateways

$Id: sqlbase.py,v 1.1 2003/04/10 03:09:56 shane Exp $
"""

class SQLGatewayBase:
    """SQL gateway base class"""

    table_base_name = '(override this)'
    checkexist_sql = '(override this)'
    create_sql = '(override this)'
    clear_sql = 'DELETE FROM %(table)s'

    def __init__(self, conn):
        self.conn = conn
        self.setupTableNames()
        if conn.isConnected():
            self.setupTables()
        else:
            conn.addConnectCallback(self.setupTables)

    def setupTableNames(self):
        self.table = self.conn.prefix + '_' + self.table_base_name

    def setupTables(self):
        conn = self.conn
        try:
            self.execute(self.checkexist_sql)
        except conn.error:
            conn.db.rollback()
            self.execute(self.create_sql)
            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 clear(self):
        self.execute(self.clear_sql)



=== Added File Products/Ape/lib/apelib/sql/structure.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 gateways for a filesystem-like tree structure.

$Id: structure.py,v 1.1 2003/04/10 03:09:56 shane Exp $
"""

from apelib.core.interfaces import IGateway
from apelib.core.schemas import FieldSchema, RowSequenceSchema
from sqlbase import SQLGatewayBase


class SQLObjectData (SQLGatewayBase):
    """SQL object data gateway"""

    __implements__ = IGateway

    schema = FieldSchema('data', 'string')

    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

    def load(self, event):
        key = long(event.getKey())
        items = self.execute(self.read_sql, 1, key=key)
        if items:
            state = items[0][0]
        else:
            state = ''
        return state, state

    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)}
        if items:
            # update.
            self.execute(self.update_sql, **kw)
        else:
            # insert.
            self.execute(self.insert_sql, **kw)
        return state


class SQLFolderItems (SQLGatewayBase):
    """SQL folder items gateway"""

    __implements__ = IGateway

    schema = RowSequenceSchema()
    schema.addField('id', 'string', 1)
    schema.addField('keychain', 'keychain')

    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

    def load(self, event):
        keychain = event.getKeychain()
        key = long(keychain[-1])
        prefix = keychain[:-1]
        rows = self.execute(self.read_sql, 1, key=key)
        rows.sort()
        res = [(row[0], prefix + (long(row[1]),)) for row in rows]
        return res, tuple(res)

    def store(self, event, state):
        keychain = event.getKeychain()
        keychain1 = keychain[:-1]
        key = long(keychain[-1])
        state_dict = {}
        for name, child_keychain in state:
            state_dict[name] = 1

        rows = self.execute(self.read_sql, 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)

        state = list(state)
        state.sort()
        for name, child_keychain in state:
            if child_keychain[:-1] != keychain1:
                raise RuntimeError(
                    "SQLFolderItems cannot store cross-domain references")
            child_key = long(child_keychain[-1])
            kw = {'key': key, 'name': name, 'child_key': child_key}
            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)
            else:
                # Add this item to the database.
                self.execute(self.insert_sql, **kw)
        return tuple(state)


class SQLItemId (SQLGatewayBase):
    """SQL item ID gateway.

    Piggybacks SQLFolderItems and makes the assumption that the item
    is stored in only one place.
    """

    __implements__ = IGateway

    schema = FieldSchema('id', 'string')

    table_base_name = 'folder_items'

    read_sql = 'SELECT name from %(table)s WHERE child_key = %(child_key)s'

    def setupTables(self):
        # No action necessary
        pass

    def getSchema(self):
        return self.schema

    def load(self, event):
        key = long(event.getKey())
        rows = self.execute(self.read_sql, 1, child_key=key)
        assert len(rows) >= 1
        name = rows[0][0]  # Other names will be ignored
        return name, None

    def store(self, event, state):
        # Assume that SQLFolderItems stored or will store the name.
        return None


class SQLRemainder (SQLObjectData):
    """SQL remainder pickle gateway"""

    __implements__ = IGateway

    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)'''


class SQLModTime (SQLGatewayBase):
    """SQL object mod time gateway"""

    __implements__ = IGateway

    schema = FieldSchema('mtime', 'int')  # second

    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

    def load(self, event):
        key = long(event.getKey())
        items = self.execute(self.read_sql, 1, key=key)
        if items:
            state = long(items[0][0])
        else:
            state = 0L
        return state, state

    def store(self, event, state):
        key = long(event.getKey())
        state = long(state)
        items = self.execute(self.read_sql, 1, key=key)
        kw = {'key': key, 'mtime': state}
        if items:
            # update.
            self.execute(self.update_sql, **kw)
        else:
            # insert.
            self.execute(self.insert_sql, **kw)
        return state