Hy,
For a long time ago I manage versioned data's into relational
database. You must be careful with the way you deal with timeable
information.
Membership table represent information with a validity period
specified by start and stop attribute.
The first change that I propose is to replace your 'Integer' column
type for yours start and top attribute by a DateTime
class Membership(Base):
__tablename__ = 'membership'
group_id=Column(ForeignKey(GroupInfo.group_id,
onupdate="cascade",ondelete='cascade'))
employee_id = Column(Integer, primary_key=True, nullable=False)
_start = Column(DateTime, nullable=False, primary_key=True,
autoincrement=False)
_stop = Column(DateTime, nullable=False,
primary_key=True,autoincrement=False)
When dealing with period information (_start, _stop) you have to make
the difference between the value of yours data in the code and their
values into the database. A common mistake is to save null value in
the database for one of their attributes. Another mistake is the
meaning of each limit. When you give me a value for _start, _end do
you mean that the limit is included or excluded? If a membershipA
start at 2pm, stop at 3pm and membershipB start at 3pm and stop at 4pw
do you mean that they are an overlap between these 2 memberships.
In period management, a common pattern is to work with include,
excluded limit so a period is represented by [_start, _end[ meaning
that at _end, your are no more membership
To hide the whole complexity behind period, I use a composite property
to encapsulate _start and _end
import datetime
from sqlalchemy.orm import composite
from sqlalchemy import sql
from sqlalchemy import DateTime
from sqlalchemy.sql import or_
from sqlalchemy.sql import and_
from sqlalchemy.sql.expression import ClauseList
from sqlalchemy.sql.expression import _BinaryExpression
from sqlalchemy.sql.expression import _literal_as_binds
from sqlalchemy.sql.expression import cast
from sqlalchemy.orm.properties import CompositeProperty
from sqlalchemy.databases.postgres import PGDialect
class Membership(Base):
__tablename__ = 'membership'
group_id=Column(ForeignKey(GroupInfo.group_id,
onupdate="cascade",ondelete='cascade'))
employee_id = Column(Integer, primary_key=True, nullable=False)
validity_period = composite(Period,
Column("start", DateTime, nullable=False,
primary_key=True, autoincrement=False),
Column("end", DateTime, nullable=False,
primary_key=True,autoincrement=False) ,
comparator_factory = PeriodComparator)
the code for Period and PeriodComparator
-*- coding: utf-8 -*-
# Copyright (c) 2008
# Authors: Laurent Mignon (Software AG)
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License version 2 or
higher
# as published by the Free Software Foundation.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA
# 02111-1307, USA.
class Period(object):
"""Period datetype
This class implement a custom datetype for sqlalchemy where values
are provided by the composition of two columns.
The requirement for the custom this class are that it have a
constructor
which accepts positional arguments corresponding to its column
format.
In our case, two DATETIME columns definition corresponding to the
start and the end of the period.
It also provides a method __composite_values__() which returns the
state of
the object as a list or tuple, in order of its column-based
attributes.
It also should supply adequate __eq__() and __ne__() methods which
test
equality of two instances.
"""
PRECISION = datetime.timedelta(minutes=1)
endMax = datetime.datetime.max - PRECISION
def __init__(self, start=None, end=None):
"""Constructor
"""
self.__start = None
self.__end = None
self.start = start
self.end = end
def __set_start(self, value):
if value is None:
value = datetime.datetime.min
self.__start = value
def __get_start(self):
if self.__start == datetime.datetime.min:
return None
return self.__start
start = property(__get_start, __set_start)
def __set_end(self, value):
if value is None or value == datetime.datetime.max:
value = self.endMax
self.__end = value
def __get_end(self):
if self.__end == self.endMax:
return None
return self.__end
end = property(__get_end, __set_end)
def __composite_values__(self):
"""
returns the state of the object as a list or tuple, in order
of its
column-based attribute
"""
self.start = self.start
self.end = self.end
return [self.__start, self.__end]
def __eq__(self, other):
if other is None:
return False
if not isinstance(other, Period) :
return False
return other.start == self.start and \
other.end == self.end
def __ne__(self, other):
return not self.__eq__(other)
def __cmp__(self, other):
if self.start == other.start:
return cmp(self.end, other.end)
return cmp(self.start, other.start)
def __contains__(self, other):
if other is None:
return False
other_start = other.start or datetime.datetime.min
other_end = other.end or datetime.datetime.max
start = self.start or datetime.datetime.min
end = self.end or datetime.datetime.max
# Zero-length periods are excluded of search results when
occuring at
# the end of a period, since upper bound is not inclusive
# TODO include zero length periods that have the same bounds
=> see
# includes operator
if other_start == other_end and \
end == other_end:
return False
elif start <= other_start and \
end >= other_end:
return True
return False
class PeriodComparator(CompositeProperty.Comparator):
"""Specific operators used to request datas based on period
criteria
The most important operator is the OVERLAPS operator. It's
probably the
only one required to efficiently retrieve period based datas since
we
always store no null value to qualify a period.
"""
def overlaps(self, other):
"""
Constrains the property to overlap the given
[beginDate,endDate[ period
When the backend is a postgres database, the OVERLAPS operator
is used
otherwhise a complex sql statement is issued
"""
beginColumn = self.prop.columns[0]
endColumn = self.prop.columns[1]
other_start = other.start or datetime.datetime.min
other_end = other.end or datetime.datetime.max
dialect = self.mapper.tables[0].metadata.bind.dialect.name
if dialect == PGDialect.name:
#SELECT *
#FROM PERIODE
#WHERE
# (P1_DEBUT, P1_FIN) OVERLAPS (P2_DEBUT, P2_FIN)
leftClause = ClauseList(beginColumn, endColumn)
rightClause = ClauseList(
cast(_literal_as_binds(other_start), DateTime),
cast(_literal_as_binds(other_end), DateTime))
expr = _BinaryExpression(
leftClause,
rightClause,
'OVERLAPS')
return expr
else:
#SELECT *
#FROM PERIODE
#WHERE
#WHERE (P1_DEBUT > P2_DEBUT AND
# (P1_DEBUT < P2_FIN OR P1_FIN < P2_FIN)) OR
# (P2_DEBUT > P1_DEBUT AND
# (P2_DEBUT < P1_FIN OR P2_FIN < P1_FIN)) OR
# (P1_DEBUT = P2_DEBUT AND
# (P1_FIN IS NOT NULL AND P2_FIN IS NOT NULL))
return or_(
and_(beginColumn > other_start,
or_(beginColumn < other_end,
endColumn < other_end),
),
and_(other_start > beginColumn,
or_(other_start < endColumn,
other_end < endColumn),
),
beginColumn == other_start,
)
def partially_overlaps(self, other):
"""
Returns a query which selects the records where the period
* include(other.start) AND exclude other.end
OR
* include(other.end) AND exclude other.start
For example, the two period p1 and p2 will overlap in cases
(a), (b),
and (c), but not in cases (d) and (e):
p2 [ [
(a) p1 [ [
(b) p1 [ [
(c) p1 [ [
(d) p1 [ [
(e) p1 [ [
------------+----------------+-------------------------
> time
p1.start p1.end
"""
if other is None:
return True
column_start = self.prop.columns[0]
column_end = self.prop.columns[1]
other_start = other.start or datetime.datetime.min
other_end = other.end or datetime.datetime.max
ret = sql.or_(
sql.and_(
sql.and_(column_start > other_start,
column_start < other_end),
column_end > other_end),
sql.and_(
sql.and_(column_end > other_start,
column_end < other_end),
column_start < other_start))
return ret
def includes(self, other):
"""
Returns a query which selects the records where the period
includes
the specified period.
For example, the period p2 is included in period p1 in case
(c), but
not in other cases:
p2 [ [
(a) p1 [ [
(b) p1 [ [
(c) p1 [ [
(d) p1 [ [
(e) p1 [ [
------------+----------------+-------------------------
> time
p1.start p1.end
"""
if other is None:
return True
column_start = self.prop.columns[0]
column_end = self.prop.columns[1]
other_start = other.start or datetime.datetime.min
other_end = other.end or Period.endMax
# If both periods have the same end date, we have to exclude
null
# length periods, since upper bound is exclusive.
# In addition to that, we have to include periods with the
same bounds
# (even if they have null length)
if other_start == other_end:
ret = sql.or_(
sql.and_(column_start == other_start,
column_end == other_end),
sql.and_(
sql.and_(column_start <= other_start,
column_end >= other_end),
sql.not_(other_end == column_end)))
else:
ret = sql.or_(
sql.and_(column_start == other_start,
column_end == other_end),
sql.and_(column_start <= other_start,
column_end >= other_end))
return ret
def isincluded(self, other):
"""
Returns a query which selects the records where the period is
included
into the specified period.
"""
if other is None:
return True
column_start = self.prop.columns[0]
column_end = self.prop.columns[1]
other_start = other.start or datetime.datetime.min
other_end = other.end or datetime.datetime.max
ret = sql.and_(column_start >= other_start,
column_end <= other_end)
return ret
Now you can initialize your membership with:
mebership = Membership()
membership.validity_period = Period(datetime.datetime.now())
And simply query using the 'overlap' operator
query = session.query(Membership)
query = query.filter(
Membership.validity_period.comparator.overlaps(
Period(datetime.datetime.now(), datetime.datetime.now() +
datetime.timedelate(days=1))))
I give you the code without guaranty since it's used in non
declarative way but I think that I've made (on the fly and without
testing) the required change to your sample...
Laurent Mignon
Senior Software Engineer
Software AG Belgium
If you want to query the membership whit an overlap operator or an
other dedicated operator
On 16 juil, 18:36, Gregg Lind <gregg.l...@gmail.com> wrote:
> That's a totally fair answer! Mostly, I wish some sense of relational
> change over time was built into SQL, the way it is in BigTable style
> systems.
>
> Maybe you could shed a little light on how to use the overlap
> operator? I'm having trouble getting the multiple fields into the
> clause statement.
>
> In [43]:
> session.query(Membership).filter(Membership.start.op('OVERLAPS',[1,100]))
>
> TypeError: op() takes exactly 2 arguments (3 given)
>
> It would also be have / emulate a .when(ts) method in queries (perhaps
> via a query subclass) that would take the "timings" into account, but
> this does smack of magic as you suggest!
>
> Thanks for the advice!
>
> Gregg
>
> On Thu, Jul 16, 2009 at 10:17 AM, Michael Bayer<mike...@zzzcomputing.com>
> wrote:
>
> > Gregg Lind wrote:
>
> >> Questions:
>
> >> 1. Is there a "SQLAlchemical" way to write group_snapshot_ts into
> >> a declarative class, such that the joins and loader respect the
> >> time
> >> constraints? (Read-only is fine as well on the loaded attributes)
> >> a. eager_loader?
> >> b. subclassing query?
>
> > im really not in favor of trying to build "magic" awareness of things like
> > this into mappers and query subclasses. If you have complex conditions
> > to be queried upon I am a fan of abstracting the common kinds of queries
> > you need into Python functions and/or class methods. no magic, just
> > regular old application architecture.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
