Installation

Include cone.sql to install dependencies in your application’s setup.py.

Configure Database and WSGI

Adopt your application config ini file to define database location and hook up the related elements to the WSGI pipeline.

[app:my_app]
use = egg:cone.app#main

pyramid.includes =
    pyramid_retry
    pyramid_tm

tm.commit_veto = pyramid_tm.default_commit_veto

cone.plugins =
    cone.sql

sql.db.url = sqlite:///%(here)s/var/sqlite/my_db.db

[filter:remote_addr]
# for use behind nginx
use = egg:cone.app#remote_addr

[filter:session]
use = egg:cone.sql#session

[pipeline:main]
pipeline =
    remote_addr
    session
    my_app

Create Model and Nodes

Define the SQLAlchemy model.

from cone.sql import SQLBase
from cone.sql.model import GUID
from sqlalchemy import Column
from sqlalchemy import String

class MyRecord(SQLBase):
    __tablename__ = 'my_table'
    uid_key = Column(GUID, primary_key=True)
    field = Column(String)

Define an application node which represents the SQL row and uses the SQLAlchemy model. The class holds a reference to the related SQLAlchemy model.

from cone.sql.model import SQLRowNode

class MyNode(SQLRowNode):
    record_class = MyRecord

Define an application node which represents the table and acts as container for the SQL row nodes. The class holds a reference to the related SQLAlchemy model and the related SQLRowNode.

from cone.sql.model import SQLTableNode

class MyContainer(SQLTableNode):
    record_class = MyRecord
    child_factory = MyNode

Append-only versioning

cone.sql.versioning provides revision safe storage as an opt-in mixin. Nothing is ever updated or deleted: a change inserts a new row and marks the previous one superseded, a deletion inserts a grave row. SQLBase and existing tables are untouched.

from cone.sql import SQLBase
from cone.sql.versioning import VersionedMixin
from cone.sql.versioning import VersionRegistryMixin
from sqlalchemy import Column
from sqlalchemy import String

class MyRegistryRecord(VersionRegistryMixin, SQLBase):
    __tablename__ = 'my_registry'
    code = Column(String, unique=True)

class MyRecord(VersionedMixin, SQLBase):
    __tablename__ = 'my_table'
    registry_class = MyRegistryRecord
    field = Column(String)

The mixin contributes id (row identity), object_id (object identity, stable across versions), deleted, created and superseded, plus a partial unique index enforcing at most one current version per object.

The registry is an immutable table with object_id as primary key. It exists because object_id is not unique in the versioned table and therefore cannot serve as a foreign key target. Classes nobody references by object identity can omit it.

Four operations are the only write path:

record = MyRecord.create(session, registry_values=dict(code='a'), field='x')
record = MyRecord.new_version(session, record.object_id, field='y')
record = MyRecord.tombstone(session, record.object_id)
record = MyRecord.resurrect(session, record.object_id, field='z')

Data columns not passed to new_version are carried over from the predecessor. Queries use the filters, never a hand written condition:

session.query(MyRecord).filter(MyRecord.current())
session.query(MyRecord).filter(MyRecord.as_of(timestamp))
session.query(MyRecord).filter(MyRecord.gone())
MyRecord.history(session, object_id)

A before_flush guard rejects any UPDATE touching anything but superseded and any DELETE on versioned rows, which makes the pattern enforceable rather than a convention.

Two things the application owns: SQLite needs PRAGMA foreign_keys=ON for the registry foreign key to be more than declarative, and application specific columns such as a creator are stamped by handlers registered with version_metadata_handler.

For publishing versioned records there are node classes mirroring SQLTableNode and SQLRowNode:

from cone.sql.versioning import VersionedSQLRowNode
from cone.sql.versioning import VersionedSQLTableNode

class MyNode(VersionedSQLRowNode):
    record_class = MyRecord

class MyContainer(VersionedSQLTableNode):
    record_class = MyRecord
    child_factory = MyNode

The node name is the object identity, not the primary key. A name bound to the primary key would change on every edit and take every URL and every stored reference with it, which is the opposite of what the pattern exists for.

Usage follows the same contract as the non versioned nodes:

node = MyNode()
node.attrs['field'] = 'value'
container[str(object_id)] = node
node()

Attribute writes are buffered and turned into a version when the node is called - creating the first one, or superseding the current one. Calling a node without pending changes writes nothing. __delitem__ writes a tombstone instead of deleting, and the container lists and resolves current rows only. Cascading over dependent objects stays application logic, since only the application knows the aggregate.

Primary key handling

The node name maps to the primary key of the SQLAlchemy model (currenly no multiple primary keys are supported). Node names are converted to the primary key data type automatically. The conversion factories are defined at SQLTableNode.data_type_converters which can be extended by more data types if needed.

>>> SQLTableNode.data_type_converters
{<class 'sqlalchemy.sql.sqltypes.String'>: <type 'unicode'>,
<class 'cone.sql.model.GUID'>: <class 'uuid.UUID'>,
<class 'sqlalchemy.sql.sqltypes.Integer'>: <type 'int'>}

Integrate to the Application Model

In order to publish a SQL table node, the table node must be hooked up to the application model. To hook up the at root level, register it as entry.

import cone.app

cone.app.register_entry('container', MyContainer)

Session setup handlers

There exists a sql_session_setup decorator which can be used to perform session setup tasks like registering SQLAlchemy event listeners.

from cone.sql import sql_session_setup
from sqlalchemy import event

def after_flush(session, flush_context):
    """Do something after flush.
    """

@sql_session_setup
def bind_session_listener(session):
    """SQL session setup callback.
    """
    event.listen(session, 'after_flush', after_flush)

Query the database

Querying the database is done via SQLAlchemy. If you are in a request/response cycle, you should acquire the session from request via get_session and perform arbitrary operations on it. By reading the session from request we ensure the transaction manager to work properly if configured.

from cone.sql import get_session

session = get_session(request)
result = session.query(MyRecord).all()

If you need a session outside a request/response cycle you can create one by using the session_factory.

from cone.sql import session_factory

session = session_factory()
result = session.query(MyRecord).all()
session.close()

Principal ACL’s

SQL based Principal ACL’s are implemented in cone.sql.acl. The related table gets created as soon as you import from this module.

Using SQLPrincipalACL requires the model to implement node.interfaces.IUUID.

from cone.sql.acl import SQLPrincipalACL
from node.base import BaseNode
from node.interfaces import IUUID
from plumber import plumbing
from pyramid.security import Allow
from zope.interface import implementer
import uuid as uuid_module

@implementer(IUUID)
@plumbing(SQLPrincipalACL)
class SQLPrincipalACLNode(BaseNode):
    uuid = uuid_module.UUID('1a82fa87-08d6-4e48-8bc2-97ee5a52726d')

    @property
    def __acl__(self):
        return [
            (Allow, 'role:editor', ['edit']),
            (Allow, 'role:manager', ['manage']),
        ]

User and Group Management

cone.sql.ugm contains an implementation of the UGM contracts defined at node.ext.ugm.interfaces, using sql as backend storage:

                  +------------+
                  |  Principal |
                  |(data: JSON)|
                  +------------+
                        ^
                        |
   +-----------------------------------------+
   |                                         |
   |                                         |
+------+                                 +-------+
| User |                                 | Group |
+------+                                 +-------+
    1                                        1
    |                                        |
    |                                        |
    +-------------+            +-------------+
                  |            |
                  n            m
                  |            |
               +-----------------+
               | GroupAssignment |
               +-----------------+

Currently SQLite and PostgreSQL are supported and tested, other DBs must be evaluated concerning their JSON capabilities since users and groups store additional payload data in a JSON field which brings the flexibility to store arbitrary data as a dict in the JSON field.

To activate SQL based UGM backend, it needs to be configured via the application ini config file.:

ugm.backend = sql

sql.user_attrs = id, mail, fullname, portrait
sql.group_attrs = description
sql.binary_attrs = portrait
sql.log_auth = True
sql.user_expires_attr = expires

UGM users and groups are stored in the same database as defined at sql.db.url in the config file.

UGM dedicated config options:

• sql.user_attrs is a comma separated list of strings defining the available user attributes stored in the user JSON data field.

• sql.group_attrs is a comma separated list of strings defining the available group attributes stored in the group JSON data field.

• sql.binary_attrs is a comma separated list of strings defining the attributes which are considered binary and get stored base 64 encoded in the JSON data field of users and groups.

• sql.log_auth defaults to False. If set, the first login timestamp will be stored during the first authentication and latest login timestamp will be updated for each successful authentication.

• sql.user_expires_attr defaults to None. If set, user expiration is enabled and the value given is the attribute name of the JSON data field where the expiration timestamp gets stored.

Users and groups can be managed with cone.ugm. If activated, sql.user_attrs and sql.group_attrs can be omitted, relevant information gets extracted from the ugm.xml config file.

ugm.backend = sql
ugm.config = %(here)s/ugm.xml

sql.log_auth = True

cone.plugins =
    cone.ugm
    cone.sql

TODO

• Support multiple primary keys.

1.2.0 (2026-10-24)

• Remove cone.sql.model.UNICODE_TYPE. It was an alias for str left over from Python 2 support. Use str instead. [rnix]

• Modernise the code ruff flags as outdated: u'' prefixes, class X(object), .format() over f-strings. Behaviour unchanged. super(Class, self) is kept, see cone.app. [rnix]

• Add qa.ruff domain to Makefile and pin the ruff rule selection in pyproject.toml, make check runs ruff check. [rnix]

• testing.SQLLayer.make_app passes its settings to the application. It handed on only the keyword arguments, so the layer never configured sql as UGM backend and the cone.ugm integration tests ran against the file backend. [rnix]

• Add testing.use_transaction_manager test decorator. [rnix]

• cone.sql.testing imports cone.sql.ugm. SQLLayer configures sql as UGM backend, but created the tables before the UGM tables were known, so a downstream package using the layer failed with no such table: principal. [rnix]

• Fix signatures of Ugm.__iter__ and Ugm.__delitem__. Both took a superfluous argument, so iterating the UGM raised TypeError and deleting from it raised TypeError instead of NotImplementedError. [rnix]

• Remove UsersBehavior.passwd. It was never called, AuthenticationBehavior.passwd overrides it. [rnix]

• Add cone.sql.versioning, providing append-only versioning with tombstones as an opt-in mixin. A change inserts a new row and supersedes its predecessor, a deletion inserts a grave row; the four operations create, new_version, tombstone and resurrect are the only write path. Comes with a before_flush guard rejecting in place mutation and deletion of versioned rows, current()/as_of() filters, a registry mixin providing a foreign key target for object identity, and the partial unique index enforcing at most one current version per object. SQLBase and existing tables are untouched. Columns carry a version_ prefix (version_created, version_superseded, version_deleted), so they do not collide with the application meaning of created and deleted. [rnix]

• Add VersionedSQLTableNode and VersionedSQLRowNode for publishing versioned records. The node name is the object identity rather than the primary key, so it survives edits. Attribute writes are buffered and applied as a version when the node is called, __delitem__ writes a tombstone, and the container resolves and lists current rows only. [rnix]

• Add UTCDateTime column type. Timezone aware, normalized to UTC, naive values rejected. PostgreSQL gets TIMESTAMPTZ, SQLite fixed width ISO-8601 text - SQLite compares text byte by byte, so a variable fraction would make ORDER BY sort by punctuation rather than by time. [rnix]

• Drop the Python 2 branch behind UNICODE_TYPE, which the supported Python versions cannot reach. The name is kept, cone.sql.ugm and downstream packages import it. Remove two unused imports from cone.sql.testing. [rnix]

• Fix GUID.process_result_value on PostgreSQL. load_dialect_impl maps the type to a native uuid column there, so the driver already returns a uuid.UUID instance. Feeding it to uuid.UUID() raised AttributeError: 'UUID' object has no attribute 'replace' on every read. Values that already are uuid.UUID are now passed through unchanged, while the CHAR(32) hexstring path used by the other backends keeps working. [rnix]

1.1.0 (2026-02-03)

• Refactor package layout to use pyproject.toml and implicit namespace packages. [rnix]

• Setup Makefile. [lenadax]

• Run tests with pytest. [lenadax]

0.9 (2025-10-25)

• Pin upper versions of dependencies. [rnix]

• Setup Makefile. [lenadax]

• Run tests with pytest. [lenadax]

0.8 (2024-02-12)

• Initialize SQL before calling setUp of super class in SQLLayer.setUp, which itself calls make_app. This ensures sql.session_factory is properly set if used in a cone main_hook. [rnix]

0.7 (2022-12-05)

• Implement expires and expired on cone.sql.ugm.UserBehavior. Extend cone.sql.ugm.UgmBehavior by user_expires_attr which enables used expiration support. [rnix]

• Add TestSQLSessionFactory and set to cone.sql.session_factory in SQLLayer.init_sql if not present. [rnix, toalba]

0.6 (2022-10-06)

• Remove usage of Nodespaces behavior. [rnix]

• Replace deprecated use of IStorage by IMappingStorage. [rnix]

• Replace deprecated use of Nodify by MappingNode. [rnix]

• Replace deprecated use of Adopt by MappingAdopt. [rnix]

• Replace deprecated use of NodeChildValidate by MappingConstraints. [rnix]

• Replace deprecated use of allow_non_node_children by child_constraints. [rnix]

0.5 (2021-11-08)

• Rename deprecated SQLPrincipalRoles.allow_non_node_childs to allow_non_node_children [rnix]

• Add cache_ok to GUID type decorator to prevent warning with SQLAlchemy 1.4 [rnix]

0.4 (2020-11-12)

• Fix typo in SqlUGMFactory.__init__. [rnix]

0.3 (2020-07-09)

• SQL database URL setting key in ini file changed from cone.sql.db.url to sql.db.url. [rnix]

• Add SQL based UGM implementation. [zworkb, rnix]

• Patch maker on cone.sql.session_factory if present in cone.sql.testing.SQLLayer to ensure working session factory when running tests. [rnix]

0.2 (2020-05-30)

• Introduce cone.sql.SQLSessionFactory. Gets instanciated at application startup as singleton at cone.sql.session_factory. [rnix]

• SQL database URL setting key in ini file changed from cone.sql.dbinit.url to cone.sql.db.url. [rnix]

• SQL database URL definition is only required once in the app section of the ini file. sqlalchemy.url can be removed from session filter. [rnix]

• Add SQL based principal ACL support. [rnix]

• Python 3 compatibility. [rnix]

• Fix hex formatting in cone.sql.model.GUID.process_bind_param. [rnix]

• Register SQL session to transaction manager with zope.sqlalchemy.register. [rnix]

• Use pyramid_tm instead of repoze.tm2. Disabled by default, must be enabled explicitely via pyramid.includes. [rnix]

• Use pyramid_retry instead of repoze.retry. Disabled by default, must be enabled explicitely via pyramid.includes. [rnix]

• Upgrade to cone.app 1.0b1. [rnix]

0.1 (2017-03-28)

• Initial work. [rnix]