Happy Thanksgiving

November 24, 2010 at 08:34 PM | SQLAlchemy, Code

Motivations:

  1. Went through all that trouble to rebuild my blog, still nothing on my mind. Let's make something up !
  2. Holiday cheer ! Since I am nothing if not cheery.

The exercise below uses a database to make a turkey. It does so via an entirely convoluted and pointless series of steps, persisting and restoring a needlessly esoteric set of data with a SQLite database through a series of obnoxious arithmetical translations, however delicious the final answer may be.

It does, at least, illustrate some query/domain model techniques I've been using on the job as of late. The @classmethod constructs you see below are a slimmed down version of SQLAlchemy's "hybrid attrbutes" example, attribute helpers that are going to play a much more prominent role in 0.7, where we begin to de-emphasize synonym and comparable_property in favor of the hybrid, a simpler and more versatile component.

In their full form, these components allow domain classes to define transformational expressions that work both as SQL expressions as well as instance attributes. Below, a more succinct version of them is used to associate composed SQL elements with a domain model. Ramped up to scale in a real application, I'm able to generate large paginated displays, containing a wide range of values mathemetically derived from others, all evaluated in SQL and pulled straight from a single Query object composed using this technique. In other contexts, when I load individual objects from the database, those same derivation methods are available to me at the instance level, pulling their components from the in-memory state of the object and interpreted by Python, instead of by the relational engine.

Surmounting the barrier of absurdity inherent in this exercise did yield a small bounty - writing this example uncovered some surprisingly glaring expression language bugs I have to fix (see all those self_group() calls ? Those shouldn't have to be there...ouch).

Hope you enjoy it:

packed_turkey = \
    '789c2d954b121d350c45e7bd12129cc292fc4d522936c18c82193b604465ef9cd'\
    '3c9c0cf7eb66c5d5d5da97f8fbbeaf92f67f59655a3e5aef1fc737a3d1f224f0b'\
    'f6479b6d8cdb7271bc66bb31db1ad9ee1e8d65ac16bb05b6b7c560ea839dd89cc'\
    'c6ca3df36a2b77d3783d339a2ade3cbdcadd576659bc73792516d7b7b3b3676d9'\
    '3281509dc11ceb0eb0700284d5d66e031fc969f6c42200e573b719cb8976f8054'\
    '84436362fe72c165ef01c9793ee58c44e8c9c8d5b0c7c70561b87f88a7d5f7280'\
    '36f143e431011dc9d50444dde7978c013917ec153c9f9b832452e8e5a717ec754'\
    '19d36f56aa0ebc7e2e56a7b1707975b93abfbdee7d70941333ba82f335948ff6b'\
    'd192d06092d90103109887572476810d7e92c7a2f2c5407050c5336413a070b40'\
    'f4e00631c84961054c3b908b8782a481a6100182d7c3cd03bcd741a6719d33577'\
    '6c5ed78dc82fe9004b60c633cca70db01637a05a80984be59598b1fbf349ae025'\
    '905c90caeafebbd002ab16019a41f7750898858c5a807c38edbf75160ae838a38'\
    'e27692b0816986ec1073cce737f83c686020a6970a00aa40c2ac05ec3e4ca98c9'\
    '3ec7d84a2e3fe728161caf29ccf9f5b2590f64b8812cb400e5b46bbd7e18767e6'\
    '3095f82811e31333c20e44380f1eae95b25e9f2ad310b1bc3243e91c221be7150'\
    '37a57a66c6e4b0fe1a422721fc9148a295298aa7fd6f3174f3732a828246733b8'\
    'd94e333b4d3ec404e4a680140a4e9a9515adac1cd2d4ac01cbc18a0ca4d6a6b01'\
    'a4c923db89eaab781114dc323f88804cee7f14cf88d150bb95c78219bd4123a47'\
    '2b446ef977a084778544c2811b2009ff8322c0f053820c1b8ae897954282c3def'\
    '0d6cdee926bca1c64cd760321d42a04b367b28ff93004b5627fb20ab77965a85e'\
    'd672516a4c59db07f84f852f756a7ad42d712fa5b04c026adc47c05d0acd95b35'\
    'cc2a44da89728589081a23a2cc142c6de2ceb0197c7466198dd36658b287bd6c6'\
    '92da2ceaaa08aa08ea22ae0be86b8de1e2027ef07fe07e0274582deebdd5b6189'\
    'ca56bc99b324c479b0e8c2c0a22727fc0ceb8969a33ad60cce7df89b80748969a'\
    'b5d10de7f1fcc167219f2f748a7c3e23bafb7ca7cdcfe7eb78054a72415cf3f93'\
    'b793951482e05e44045047ebb45884cd2a08745e3b7810312316d406f43826493'\
    '07dcb4dd2f8585711ebf387e6ac20cd073cdc09b67aba028e2e1f48ab72014e4c'\
    '7ef001b9674d91615e5b505e078beada0f9541a2e0a29081b641ace6dc75da5d1'\
    '80f17171211f775a25169c7dc9a806e5908af25a667ce4545a7443b42ba95a375'\
    'e69aa63bbaa969ad9d88491e3f9365e7cf5fc0f2961791f'

from sqlalchemy import create_engine, Integer, Float, \
                        CHAR, Column, ForeignKey, \
                        func, cast, case, and_
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base
import math, binascii, zlib

img = dict(
    (line[0], [int(x) for x in line[1:].split(",")])
    for line in zlib.decompress(binascii.unhexlify(packed_turkey)).\
    split("\n") if line)

class classproperty(property):
    """Class level @property."""
    def __get__(desc, self, cls):
        return desc.fget(cls)

Base = declarative_base()

class SpiralPoint(Base):
    """Store a character and its position along a spiral."""

    __tablename__ = 'spiral_point'

    def __init__(self, character, value):
        self.value = value
        self.character = character
        self.sqrt = math.sqrt(value)

    value = Column(Integer, primary_key=True)
    """The value."""

    character = Column(CHAR(1))
    """The character."""

    sqrt = Column(Float, nullable=False)
    """Store the square root of the value, SQLite
    doesn't have sqrt() built in.

    (custom SQLite functions are beyond the scope
    of this "exercise", as it were)
    """

    @classproperty
    def nearest_odd(cls):
        """Return the nearest odd number below this SpiralPoint's sqrt."""

        return (func.round(cls.sqrt / 2) * 2 - 1).\
                    self_group().label('nearest_odd')

    @classproperty
    def nearest_even(cls):
        """Return the nearest even number below this SpiralPoint's sqrt."""

        return (cls.nearest_odd + 1).\
                    self_group().label('nearest_even')

    @classproperty
    def center_distance(cls):
        """How far from the 'center' is this value ?"""

        return (cls.nearest_even / 2).label('center_distance')

    @classproperty
    def quadrant(cls):
        """Which side of the 'center' is this value part of ?"""

        return (cls.value - (
                cls.nearest_odd * cls.nearest_odd
            )).label('quadrant')

e = create_engine('sqlite://', echo=True)
Base.metadata.create_all(e)

session = Session(e)
session.add_all([SpiralPoint(char, val)
                    for char in img
                    for val in img[char]])
session.commit()

total_x = 60
total_y = 24

# load our turkey !
turkey = session.query(
    SpiralPoint.character,
    cast(case([
        (
            SpiralPoint.quadrant < SpiralPoint.nearest_even,
            (total_x / 2) + SpiralPoint.center_distance
        ),
        (
            and_(
                SpiralPoint.nearest_even <= SpiralPoint.quadrant,
                SpiralPoint.quadrant < SpiralPoint.nearest_even * 2
            ),
            (
                (total_x / 2) + SpiralPoint.center_distance - 1) -
                (SpiralPoint.quadrant - SpiralPoint.nearest_even).self_group()
            ),
        (
            and_(
                SpiralPoint.nearest_even * 2 <= SpiralPoint.quadrant,
                SpiralPoint.quadrant < SpiralPoint.nearest_even *3
            ),
            (total_x / 2) - SpiralPoint.center_distance
        )
    ],
        else_ = (
                    (total_x / 2) - SpiralPoint.center_distance + 1) +
                    (SpiralPoint.quadrant - (SpiralPoint.nearest_even * 3)
                )
    ), Integer).label('x'),

    cast(case([
        (
            SpiralPoint.quadrant < SpiralPoint.nearest_even,
            (total_y / 2) - SpiralPoint.center_distance + 1 + SpiralPoint.quadrant
        ),
        (
            and_(
                SpiralPoint.nearest_even <= SpiralPoint.quadrant,
                SpiralPoint.quadrant < SpiralPoint.nearest_even * 2
            ),
            (total_y / 2) + SpiralPoint.center_distance),
        (
            and_(
                SpiralPoint.nearest_even * 2 <= SpiralPoint.quadrant,
                SpiralPoint.quadrant < SpiralPoint.nearest_even *3
            ),
            (total_y / 2) + SpiralPoint.center_distance - 1 -
            (SpiralPoint.quadrant - (SpiralPoint.nearest_even * 2)).self_group()
        )
    ],
        else_ = (total_y / 2) - SpiralPoint.center_distance
    ), Integer).label('y')
)

# serve our turkey !
grid = [
        [' ' for x in xrange(total_x)]
        for y in xrange(total_y)
]

for char, x, y in turkey:
    grid[y][x] = char

for g in grid:
    print "".join([c for c in g])

Download Source