Metadata-Version: 2.5
Name: cone.sql
Version: 1.2.0
Summary: SQLAlchemy integration for cone.app
Project-URL: Homepage, http://github.com/conestack/cone.sql
Author-email: Cone Contributors <dev@conestack.org>
License: Simplified BSD
License-File: LICENSE.rst
Keywords: cone,node,pyramid,web
Classifier: Environment :: Web Environment
Classifier: Programming Language :: Python
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Programming Language :: Python :: 3.14
Classifier: Topic :: Internet :: WWW/HTTP :: Dynamic Content
Requires-Python: >=3.10
Requires-Dist: cone-app>1.0.99
Requires-Dist: node-ext-ugm>1.99
Requires-Dist: pyramid-retry
Requires-Dist: pyramid-tm
Requires-Dist: zope-sqlalchemy
Provides-Extra: test
Requires-Dist: cone-ugm[test]; extra == 'test'
Provides-Extra: ugm
Requires-Dist: cone-ugm>1.0.99; extra == 'ugm'
Description-Content-Type: text/x-rst

.. image:: https://img.shields.io/pypi/v/cone.sql.svg
    :target: https://pypi.python.org/pypi/cone.sql
    :alt: Latest PyPI version

.. image:: https://img.shields.io/pypi/dm/cone.sql.svg
    :target: https://pypi.python.org/pypi/cone.sql
    :alt: Number of PyPI downloads

.. image:: https://github.com/conestack/cone.sql/actions/workflows/test.yml/badge.svg
    :target: https://github.com/conestack/cone.sql/actions/workflows/test.yml
    :alt: Test cone.sql

This package provides SQLAlchemy integration in ``cone.app`` and basic
application nodes for publishing SQLAlchemy models.


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.

.. code-block:: ini

    [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.

.. code-block:: python

    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.

.. code-block:: python

    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.

.. code-block:: python

    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.

.. code-block:: python

    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:

.. code-block:: python

    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:

.. code-block:: python

    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``:

.. code-block:: python

    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:

.. code-block:: python

    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.

.. code-block:: python

    >>> 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.

.. code-block:: python

    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.

.. code-block:: python

    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.

.. code-block:: python

    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``.

.. code-block:: python

    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``.

.. code-block:: python

    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:

.. code-block::

                           +------------+
                           |  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.:

.. code-block:: ini

    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.

.. code-block:: ini

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

    sql.log_auth = True

    cone.plugins =
        cone.ugm
        cone.sql


TODO
----

- Support multiple primary keys.


Contributors
============

- Robert Niederreiter (Author)
- Phil Auersperg


Changes
=======

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]


License
=======

Copyright (c) 2017-2021, BlueDynamics Alliance, Austria
Copyright (c) 2021-2025, Cone Contributors
All rights reserved.

Redistribution and use in source and binary forms, with or without
modification, are permitted provided that the following conditions are met:

* Redistributions of source code must retain the above copyright notice, this
  list of conditions and the following disclaimer.

* Redistributions in binary form must reproduce the above copyright notice, this
  list of conditions and the following disclaimer in the documentation and/or
  other materials provided with the distribution.

THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE
DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR
ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
(INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND
ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT
(INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS
SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
