On Thu, Jan 24, 2019 at 9:30 AM Антонио Антуан <a.ch....@gmail.com> wrote: > > Greetings. > > I want to implement NestedType for the dialect of ClickHouse database. > > At the first sight it looks like copmosite type, but not not completely: > 1. Each subtype considers like separate column > 2. `NestedType` is just syntax sugar: > CREATE TABLE test_table1 ( > date Date, > object Nested ( > type String, > content String > > ) > > )... > > > CREATE TABLE test_table2 ( > date Date, > object.type String, > object.content String > )... > Both of DDL's create tables with the same structure. > 3. Queries can be done only using full format, like that: > SELECT count(*), objects.type FROM test_table group by objects.type > or > SELECT count(*), test_table.objects.type FROM test_table group by > test_table.objects.type > > > > My question is how can I implement that behaviour? I've tried to adjust > sqlachemy_utils.CompositeType, but it looks like there are a lot of > differences.
I'm not familiar with CompositeType but the general approach here, if you're looking for the expression behavior, is that your type also needs to have a Comparator class that will implement that dotted behavior. Examples are at https://docs.sqlalchemy.org/en/latest/core/custom_types.html#redefining-and-creating-new-operators where you can see how to build out these behaviors though you will likely need your operation to spit out a sub column('name', subtype) object when someone calls upon "somecol.somefield". This is not a simple problem when not familiar with the API so POC is down below. > > Looks like I need to use separated DeclarativeMeta class and register each > member of nested field (with sqlalchemy.ext.declarative.base._add_attribute). DeclarativeMeta has nothing to do with datatypes, nor does the ORM in general. A composite datatype is strictly Core stuff. > Another problem: append parent column name to nested column name, because I > want to make that: > > > class Test(Base): > __tablename__ = 'test' > object = Column( > Nested( > Column('type', String), > Column('content', String), > ) > > ) I'd advise using lower-case column: "from sqlalchemy import column" since these columns are not table-bound. > > > Session.query(Test).filter(Test.object.type == 'type')... > # generates that query... > select ... from test where test.object.type = 'type' sure here's a POC from sqlalchemy import Column from sqlalchemy import column from sqlalchemy import Integer from sqlalchemy import String from sqlalchemy.ext.compiler import compiles from sqlalchemy.ext.declarative import declarative_base from sqlalchemy.orm import Session from sqlalchemy.sql.elements import ColumnClause from sqlalchemy.types import UserDefinedType class NestedColumn(ColumnClause): """serves the role of the "nested" column in a nested type expression.""" def __init__(self, parent, sub_column): self.parent = parent self.sub_column = sub_column super(NestedColumn, self).__init__( sub_column.name, sub_column.type, _selectable=self.parent.table ) @compiles(NestedColumn) def _comp(element, compiler, **kw): return "%s.%s" % ( compiler.process(element.parent), compiler.visit_column(element, include_table=False), ) class Nested(UserDefinedType): def __init__(self, *cols): self.cols = cols self._col_dict = {col.name: col for col in cols} class Comparator(UserDefinedType.Comparator): def __getattr__(self, key): # this is a particular convention for serving names that conflict # with existing elements on Column which you can choose # to replace with something else, see below str_key = key.rstrip("_") try: sub = self.type._col_dict[str_key] except KeyError: raise AttributeError(key) else: # probably want to cache these eventually return NestedColumn(self.expr, sub) comparator_factory = Comparator Base = declarative_base() class Test(Base): __tablename__ = "test" id = Column(Integer, primary_key=True) object = Column(Nested(column("type", String), column("content", String))) s = Session() # CONVENTION: suffix element with "_" if the element you are accessing is # already an element on a Column object. Better approach - have it be # Test.object.elements.content , Test.object.elements.type, or # Test.object['content'] Test.object['type'], or something like that print(s.query(Test.object.content).filter(Test.object.type_ == "foo")) > > -- > SQLAlchemy - > The Python SQL Toolkit and Object Relational Mapper > > http://www.sqlalchemy.org/ > > To post example code, please provide an MCVE: Minimal, Complete, and > Verifiable Example. See http://stackoverflow.com/help/mcve for a full > description. > --- > 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 https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- 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 https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
