On Nov 3, 2013, at 6:28 PM, Jon Rosebaugh <chai...@gmail.com> wrote:
> I'm trying to implement support for Postgres's composite types, which > essentially let you make a type which is a struct of other types. This > involves several kinds of functionality: > > * Psycopg2 maps composite types as namedtuples. However, the > register_composite() function has to be called for each composite type, in > the same way the register_uuid() function is called on connections. Moreover, > the register_composite() calls need to be made in order of dependency, in the > case of nested types. > (http://initd.org/psycopg/docs/extras.html#composite-types-casting) > * A UserDefinedType to use in column definitions. Here I was able to work off > zzzeek's example from > https://groups.google.com/d/msg/sqlalchemy/f9BPVHfdvbg/M88ruLo6lzQJ > * DDL compilation stuff to actually create the type before it's used. If > there's nested types, then one type must be created before another type can > use it. > > I actually got the DDL compilation to work, but I had to mess around with a > lot of SQLAlchemy internals, copying liberally from SchemaType and the ENUM > implementation. It works, but I don't understand how it works, and I'm quite > certain I'm doing several things wrongly. I'd also appreciate some tips on > how to properly do the register_composite calls on the connections which will > use them, in dependency order (and of course not doing them if the types > haven't been created yet). > > Is there a particular reason _CreateDropBase is internal-only? It seems > useful for any DDL constructs which will need to create and drop things. > > Here's an example of using what I built so far to create types and tables: > > EyeSpec = PGCompositeType("eyespec", [("sphere", Numeric(4, 2)), ("cylinder", > Numeric(4, 2)), ("axis", SmallInteger)]) > > ScriptSpec = PGCompositeType("scriptspec", [("od", EyeSpec), ("os", EyeSpec), > ("pd", Numeric(4, 2))]) OK well I think you did a pretty good job here and this type works well. I tried to rework it in terms of “public” APIs, that is, just using @event.listens_for(), and I think this route is also possible, but is not as easy to come up with because it’s not how it works in the source code. I think though there’s some important behaviors of PG’s ENUM type that also apply here, which lead me to believe that PG’s ENUM approach should be turned into a “base” that can be more easily subclassed. What that type does is keeps track of if a particular named ENUM was created for any other tables within the MetaData structure, so that if more than one Table uses the same-named type, you don’t get redundant CREATE statements - you can see this in the _check_for_name_in_memos() method. What would be helpful here would be if you could try to propose a generic version of sqlalchemy.dialects.postgresql.ENUM which provides a “base” for “named types” - that way you can just plug in your create/drop objects. If it also has some built in way of “cascading” to contained types, that would help too. We already have some issues with “cascading” of type behavior for types like pg.ARRAY. > > class Order(Base): > client_id = Column(Integer, ForeignKey('clients.id')) > prescription = Column(ScriptSpec, nullable=False) > > And here's the implementation of PGCompositeType and its dependencies: > > from collections import namedtuple, OrderedDict > from sqlalchemy import event, util > from sqlalchemy.ext.compiler import compiles > from sqlalchemy.types import UserDefinedType, to_instance, SchemaType > from sqlalchemy.sql.expression import ColumnElement > from sqlalchemy.schema import _CreateDropBase > > > class CreateCompositeType(_CreateDropBase): > pass > > > @compiles(CreateCompositeType) > def visit_create_composite_type(create, compiler, **kw): > type_ = create.element > > fields = [] > for key, value in type_.typemap.items(): > fields.append("{} {}".format(key, > compiler.dialect.type_compiler.process(to_instance(value)))) > > return "CREATE TYPE {} AS > ({})".format(compiler.preparer.format_type(type_), ", ".join(fields)) > > > class DropCompositeType(_CreateDropBase): > pass > > > @compiles(DropCompositeType) > def visit_drop_composite_type(drop, compiler, **kw): > type_ = drop.element > > return "DROP TYPE {}".format(compiler.preparer.format_type(type_)) > > > # PGCompositeElement and PGCompositeType are based on Michael Bayer's > # example at > # https://groups.google.com/d/msg/sqlalchemy/f9BPVHfdvbg/M88ruLo6lzQJ > > class PGCompositeElement(ColumnElement): > def __init__(self, base, attrname, type_): > self.base = base > self.attrname = attrname > self.type = to_instance(type_) > > > @compiles(PGCompositeElement) > def _compile_pgelem(element, compiler, **kw): > return "%s.%s" % ( > compiler.process(element.base, **kw), > element.attrname > ) > > > class _Namespace(object): > def __init__(self, comparator): > self.comparator = comparator > > def __getattr__(self, key): > try: > type_ = self.comparator.type.typemap[key] > except KeyError: > raise KeyError( > "Type '%s' doesn't have an attribute: '%s'" % > (self.comparator.type, key)) > return PGCompositeElement( > self.comparator.expr, > key, > type_) > > > class PGCompositeType(UserDefinedType, SchemaType): > def __init__(self, name, typemap): > SchemaType.__init__(self) > self.name = name > if not isinstance(typemap, OrderedDict): > typemap = OrderedDict(typemap) > self.typemap = typemap > self.tupletype = namedtuple(name, typemap.keys()) > > > class comparator_factory(UserDefinedType.Comparator): > @property > def attrs(self): > return _Namespace(self) > > def get_col_spec(self): > return self.name > > def create(self, bind, checkfirst=True): > if not checkfirst or not bind.dialect.has_type(bind, self.name, > schema=self.schema): > bind.execute(CreateCompositeType(self)) > > def drop(self, bind, checkfirst=True): > if not checkfirst or bind.dialect.has_type(bind, self.name, > schema=self.schema): > bind.execute(DropCompositeType(self)) > > def _set_parent(self, parent): > if isinstance(parent, PGCompositeType): > # already have parent.... duh. > self._set_table(self, parent) > else: > super(PGCompositeType, self)._set_parent(parent) > for value in self.typemap.values(): > if isinstance(value, SchemaType): > value._set_parent_with_dispatch(self) > > def _set_table(self, column, table): > event.listen( > table, > "before_create", > util.portable_instancemethod( > self._on_table_create) > ) > event.listen( > table, > "after_drop", > util.portable_instancemethod(self._on_table_drop) > ) > > def _on_table_create(self, target, bind, checkfirst, **kw): > self.dispatch.before_create(self, bind, checkfirst, **kw) > self.create(bind=bind, checkfirst=checkfirst) > > def _on_metadata_drop(self, target, bind, checkfirst, **kw): > self.drop(bind=bind, checkfirst=checkfirst) > self.dispatch.after_drop(self, bind, checkfirst, **kw) > > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > Visit this group at http://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/groups/opt_out.
signature.asc
Description: Message signed with OpenPGP using GPGMail
