Composite Value is a pattern in PofEAA that maps a simple Value Object, which is an attribute of an Entity, as multiple columns in the table to which the Entity is mapped.
There is also a similar pattern called Serialized LOB, which serializes an object into JSON etc. and puts it in a BLOB or CLOB. Composite Value has the advantage that it can be used from SQL compared to Serialized LOB.
An example of doing Composite Value in SQLAlchemy can be found in the original documentation. Composite Column Types
However, since no operator is defined in this example, only equal value comparison is possible.
You can also customize the SQL generation part by using comparator_factory, but since the sample of that part was separated, I made a sample to put it together.
This sample is easier because it uses named tuple to create a Value Object.
composite_example.py
# -*- coding: utf-8 -*-
from __future__ import division, print_function, absolute_import
from collections import namedtuple
from sqlalchemy import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.properties import CompositeProperty
from sqlalchemy.orm import composite, sessionmaker, configure_mapper
#Copy from ORM tutorial
Base = declarative_base()
engine = create_engine('sqlite:///:memory:', echo=True)
Session = sessionmaker(engine)
#Range type that is the Value Object this time. There are start and end.
class Range(namedtuple('Range', 'start end')):
#This method returns a tuple-mapped value,
#namedtuple is a tuple, so it's enough to return self
def __composite_values__(self):
return self
#This is just a method. Use via instance member
def includes(self, value):
return self.start <= value < self.end
#This is the person who generates SQL
class RangeComparator(CompositeProperty.Comparator):
#Methods to generate SQL.Let's match the method and usage of Value Object
def includes(self, value):
#Take out the mapped column. This part is copy.
start, end = self.__clause_element__().clauses
# and_()Generate SQL using.
return and_(start <= value, value < end)
#Helper function
def range_composite(start, end):
return composite(Range, start, end, comparator_factory=RangeComparator)
class MyTable(Base):
__tablename__ = 'mytable'
id = Column(Integer, primary_key=True)
foo_start = Column(Integer)
foo_end = Column(Integer)
foo_range = range_composite(foo_start, foo_end)
def __repr__(self):
return "MyTable(foo_start={0.foo_start!r}, foo_end={0.foo_end!r}".format(self)
#Not required for this sample.But for complex projects you have to do this
#Since there are cases where SQL generation fails because the mapping that spans multiple classes is not completed,
#Let's do it after defining all the models.
# configure_mappers()
print("Create tables")
Base.metadata.create_all(engine)
session = Session()
print("Insert test data")
session.add(MyTable(foo_start=10, foo_end=100))
session.add(MyTable(foo_start=100, foo_end=200))
session.add(MyTable(foo_start=1, foo_end=10))
session.commit()
print("Select using filter")
# RangeComparator.includes()Can be used to build the filter part
values = session.query(MyTable).filter(MyTable.foo_range.includes(42)).all()
print("values:", values)
#Of course, Range.includes()Can be used normally in an instance
v = values[0]
print("test")
print(9, v.foo_range.includes(9))
print(10, v.foo_range.includes(10))
print(99, v.foo_range.includes(99))
print(100, v.foo_range.includes(100))
The output looks like this.
Create tables
2014-01-15 22:59:15,334 INFO sqlalchemy.engine.base.Engine PRAGMA table_info("mytable")
2014-01-15 22:59:15,334 INFO sqlalchemy.engine.base.Engine ()
2014-01-15 22:59:15,335 INFO sqlalchemy.engine.base.Engine
CREATE TABLE mytable (
id INTEGER NOT NULL,
foo_start INTEGER,
foo_end INTEGER,
PRIMARY KEY (id)
)
2014-01-15 22:59:15,335 INFO sqlalchemy.engine.base.Engine ()
2014-01-15 22:59:15,335 INFO sqlalchemy.engine.base.Engine COMMIT
Insert test data
2014-01-15 22:59:15,336 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2014-01-15 22:59:15,337 INFO sqlalchemy.engine.base.Engine INSERT INTO mytable (foo_start, foo_end) VALUES (?, ?)
2014-01-15 22:59:15,337 INFO sqlalchemy.engine.base.Engine (10, 100)
2014-01-15 22:59:15,337 INFO sqlalchemy.engine.base.Engine INSERT INTO mytable (foo_start, foo_end) VALUES (?, ?)
2014-01-15 22:59:15,337 INFO sqlalchemy.engine.base.Engine (100, 200)
2014-01-15 22:59:15,338 INFO sqlalchemy.engine.base.Engine INSERT INTO mytable (foo_start, foo_end) VALUES (?, ?)
2014-01-15 22:59:15,338 INFO sqlalchemy.engine.base.Engine (1, 10)
2014-01-15 22:59:15,338 INFO sqlalchemy.engine.base.Engine COMMIT
Select using filter
2014-01-15 22:59:15,339 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2014-01-15 22:59:15,340 INFO sqlalchemy.engine.base.Engine SELECT mytable.id AS mytable_id, mytable.foo_start AS mytable_foo_start, mytable.foo_end AS mytable_foo_end
FROM mytable
WHERE mytable.foo_start <= ? AND mytable.foo_end > ?
2014-01-15 22:59:15,340 INFO sqlalchemy.engine.base.Engine (42, 42)
values: [MyTable(foo_start=10, foo_end=100]
test
9 False
10 True
99 True
100 False
Recommended Posts