Re: [sqlalchemy] How to declare a custom type that will imply a check on a column?

[Michael Bayer]

On Dec 18, 2013, at 3:05 PM, RonnyPfannschmidt <ronny.pfannschm...@gmail.com> 
wrote:

> 
> 
> you’d use a TypeDecorator for this.  See the example 
> http://docs.sqlalchemy.org/en/rel_0_9/core/types.html#coercing-encoded-strings-to-unicode
>  for something similar (coerces to utf-8, just replace that with checking for 
> non-empty). 
> 
>  Unfortunately that doesn't quite fit, i need a DDL level check (like the one 
> Boolean generates on databases that dont support native booleans).

oh, you’re looking to tie the CheckConstraint to the type.    OK well 
subclassing the SchemaType that Boolean uses is one way to go for this, however 
that type hasn’t really been worked out as a general-purpose API for users to 
subclass, so it would be kind of a YMMV kind of thing.

However, the primary system that the SchemaType makes use of is just ordinary 
metadata events.    You can just listen for Columns being attached to Tables 
and pick the ones with your type, as follows:

from sqlalchemy import String, Column
from sqlalchemy import event

class PString(String):
    pass

@event.listens_for(Column, "after_parent_attach")
def add_to_table(col, table):
    if isinstance(col.type, PString):
        table.append_constraint(CheckConstraint(col != ''))


if __name__ == '__main__':
    from sqlalchemy import *
    m = MetaData()

    t = Table('t', m,
            Column('id', Integer, primary_key=True),
            Column('data', PString(50))
        )

    e = create_engine("sqlite://", echo=True)
    m.create_all(e)

    e.execute(t.insert(), data='some data')

    e.execute(t.insert(), data='')

The SchemaType (and in 0.9 more generically you can subclass SchemaEventTarget) 
also serve as a marker for a type that gets attach events, if you wanted to go 
that route, modify the script like this:

from sqlalchemy import String
from sqlalchemy.types import SchemaType
from sqlalchemy import event

class PString(String, SchemaType):
    inherit_schema = False   # just to satisfy SchemaType for now
    metadata = None


@event.listens_for(PString, "after_parent_attach")
def create_constraint(target, parent):
    @event.listens_for(parent, "after_parent_attach")
    def add_to_table(col, table):
        table.append_constraint(CheckConstraint(parent != ''))

signature.asc
Description: Message signed with OpenPGP using GPGMail