sqla_registration.py

from timeit import default_timer as timer

from flask import Flask
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy.orm import joinedload

app = Flask(__name__)
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.config['SQLALCHEMY_DATABASE_URI'] = 'postgresql:///test'
db = SQLAlchemy(app)


### Models

class RegistrationData(db.Model):
    __tablename__ = 'registration_data'

    registration_id = db.Column(db.Integer,db.ForeignKey('registrations.id'),primary_key=True,autoincrement=False)
    field_data_id = db.Column(db.Integer,db.ForeignKey('form_field_data.id'),primary_key=True,autoincrement=False)

    field_data = db.relationship(
        'RegistrationFormFieldData',
        lazy=True,
        backref=db.backref(
            'registration_data',
            lazy=True,
            cascade='all, delete-orphan'
        )
    )


class Registration(db.Model):
    __tablename__ = 'registrations'

    id = db.Column(db.Integer,primary_key=True)
    registration_form_id = db.Column(db.Integer,db.ForeignKey('forms.id'),index=True,nullable=False)
    data = db.relationship(
        'RegistrationData',
        lazy=True,
        cascade='all, delete-orphan',
        backref=db.backref(
            'registration',
            lazy=True
        )
    )


class RegistrationForm(db.Model):
    __tablename__ = 'forms'

    id = db.Column(db.Integer,primary_key=True)

    form_items = db.relationship(
        'RegistrationFormItem',
        lazy=True,
        cascade='all, delete-orphan',
        backref=db.backref(
            'registration_form',
            lazy=True
        )
    )
    registrations = db.relationship(
        'Registration',
        lazy=True,
        cascade='all, delete-orphan',
        foreign_keys=[Registration.registration_form_id],
        backref=db.backref(
            'registration_form',
            lazy=True
        )
    )


class RegistrationFormFieldData(db.Model):
    __tablename__ = 'form_field_data'

    id = db.Column(db.Integer,primary_key=True)
    field_id = db.Column(db.Integer,db.ForeignKey('form_items.id'),index=True,nullable=False)


class RegistrationFormItem(db.Model):
    __tablename__ = 'form_items'

    id = db.Column(db.Integer,primary_key=True)
    registration_form_id = db.Column(db.Integer,db.ForeignKey('forms.id'),index=True,nullable=False)
    current_data_id = db.Column(db.Integer,db.ForeignKey('form_field_data.id', use_alter=True),index=True,nullable=True)

    current_data = db.relationship(
        'RegistrationFormFieldData',
        primaryjoin='RegistrationFormItem.current_data_id == RegistrationFormFieldData.id',
        foreign_keys=current_data_id,
        lazy=True,
        post_update=True
    )

    data_versions = db.relationship(
        'RegistrationFormFieldData',
        primaryjoin='RegistrationFormItem.id == RegistrationFormFieldData.field_id',
        foreign_keys='RegistrationFormFieldData.field_id',
        lazy=True,
        cascade='all, delete-orphan',
        backref=db.backref(
            'field',
            lazy=False
        )
    )


## Customize values

REGISTRATION_NUMBER = 254
REGISTRATION_FORM_ITEMS = 88

## Create DB

db.create_all()
db.session.commit()

# Remove existent DB content

RegistrationData.query.delete()
RegistrationFormFieldData.query.delete()
RegistrationFormItem.query.delete()
Registration.query.delete()
RegistrationForm.query.delete()

## Create dummy data

regform = RegistrationForm()
db.session.add(regform)
db.session.commit()

for _ in range(REGISTRATION_FORM_ITEMS):
    regformitem = RegistrationFormItem(registration_form_id=regform.id)
    db.session.add(regformitem)
db.session.commit()


regform_items_id = db.session.query(RegistrationFormItem.id).all()
for _ in range(REGISTRATION_NUMBER):
    reg = Registration(registration_form_id=regform.id)
    db.session.add(reg)
    for id in regform_items_id:
        regform_field_data = RegistrationFormFieldData(field_id=id)
        reg_data = RegistrationData(registration=reg, field_data=regform_field_data)
        db.session.add(regform_field_data)
        db.session.add(reg_data)
db.session.commit()

### Query

regform = db.session.query(RegistrationForm).one()

q = Registration.query.with_parent(regform).options(joinedload('data').joinedload('field_data').joinedload('field'))

start = timer()
res = iter(q)
print 'sql time: {}'.format(timer() - start)
start = timer()
list(res)
print 'orm time: {}'.format(timer() - start)

# print q.all()