class InvoiceEntry(meta.BaseObject):
__tablename__ = "invoice_entry"
id = schema.Column(types.Integer(),
schema.Sequence("invoice_entry_id_seq", optional=True),
primary_key=True,
autoincrement=True)
position = schema.Column(types.Integer(), default=0)
invoice_id = schema.Column(types.Integer(),
schema.ForeignKey(Invoice.id,
onupdate="CASCADE",
ondelete="CASCADE"),
nullable=False)
invoice = orm.relationship(Invoice,
backref=orm.backref("entries",
order_by=position))
description = schema.Column(types.UnicodeText(), nullable=False)
vat = schema.Column(types.Integer(), nullable=False)
currency_id = schema.Column(types.Integer(3),
schema.ForeignKey(Currency.id,
onupdate="RESTRICT",
ondelete="RESTRICT"),
nullable=False)
currency = orm.relationship(Currency, lazy="joined")
unit_price = schema.Column(types.Numeric(precision=7, scale=2),
nullable=False)
units = schema.Column(types.Numeric(4, 2), nullable=False, default=1)
@property
def total(self):
return self.unit_price * self.units * self.currency.rate
class Training(BaseObject):
"""Data table to record trainings"""
__tablename__ = "training"
id = schema.Column(types.Integer(), primary_key=True, autoincrement=True)
time = schema.Column(types.DateTime(), nullable=True, default=func.now())
account_id = schema.Column(
types.Integer(),
schema.ForeignKey(Account.id, onupdate="CASCADE", ondelete="CASCADE"),
nullable=False,
)
account = orm.relation(
Account,
backref=orm.backref("training", cascade="all, delete, delete-orphan"),
)
session_id = schema.Column(
types.Integer(),
schema.ForeignKey("session.id", onupdate="CASCADE",
ondelete="CASCADE"),
nullable=False,
)
session = orm.relation(
"SurveySession",
cascade="all,delete-orphan",
single_parent=True,
backref=orm.backref("training", uselist=False, cascade="all"),
)
answers = schema.Column(types.Unicode, default="[]")
status = schema.Column(types.Unicode)
def setUp(self):
super(DropAllObjectsTest, self).setUp()
self.metadata = metadata = schema.MetaData()
schema.Table('a',
metadata,
schema.Column('id', types.Integer, primary_key=True),
mysql_engine='InnoDB')
schema.Table('b',
metadata,
schema.Column('id', types.Integer, primary_key=True),
schema.Column('a_id', types.Integer,
schema.ForeignKey('a.id')),
mysql_engine='InnoDB')
schema.Table('c',
metadata,
schema.Column('id', types.Integer, primary_key=True),
schema.Column('b_id', types.Integer,
schema.ForeignKey('b.id')),
schema.Column(
'd_id', types.Integer,
schema.ForeignKey('d.id',
use_alter=True,
name='c_d_fk')),
mysql_engine='InnoDB')
schema.Table('d',
metadata,
schema.Column('id', types.Integer, primary_key=True),
schema.Column('c_id', types.Integer,
schema.ForeignKey('c.id')),
mysql_engine='InnoDB')
metadata.create_all(self.engine, checkfirst=False)
# will drop nothing if the test worked
self.addCleanup(metadata.drop_all, self.engine, checkfirst=True)
def init_dirs_table(metadata):
return Table('dirs', metadata,
Column('path', types.Unicode(255), primary_key=True),
Column('name', types.Unicode(255)),
Column('root', types.Unicode(255)),
Column('rev_id', types.Integer, schema.ForeignKey('revisions.id')),
Column('in_dir', types.Unicode(255), schema.ForeignKey('dirs.path'))
)
def define_tables(metadata):
""" Create table definitions. This is used for allowing SQLSoup to
perform its autodiscover on a SQLite memory database instance.
The tables defined below are 'bare-bones' intended to illustrate
the core concepts of this project
Types to add: create date, modify date, owner,
authorizations, tags, history (revision control)
"""
# TODO: Refactor from hardcoding to resource file and expand to list
# of column parameters for each table
library_table = schema.Table(
'libraries',
metadata,
schema.Column('library_id', types.Integer, primary_key=True),
schema.Column('slug', types.String(80), nullable=False, unique=True),
schema.Column('title', types.String(120), default=u''),
schema.Column('description', types.String(240), default=u''),
)
book_table = schema.Table(
'books',
metadata,
schema.Column('book_id', types.Integer, primary_key=True),
schema.Column('library_id',
types.Integer,
schema.ForeignKey('libraries.library_id'),
nullable=False),
schema.Column('slug', types.String(80)),
schema.Column('title', types.String(120), default=u''),
schema.Column('description', types.String(240), default=u''),
)
page_table = schema.Table(
'pages',
metadata,
schema.Column('page_id', types.Integer, primary_key=True),
schema.Column('book_id',
types.Integer,
schema.ForeignKey('books.book_id'),
nullable=False),
schema.Column('slug', types.String(80)),
schema.Column('mimetype', types.String(80), nullable=False),
schema.Column('body', types.Text),
)
media_table = schema.Table(
'media',
metadata,
schema.Column('media_id', types.Integer, primary_key=True),
schema.Column('library_id',
types.Integer,
schema.ForeignKey('libraries.library_id'),
nullable=False),
schema.Column('data', types.Binary),
)
return restnotes_tables(library_table, book_table, page_table, media_table)
def init_files_table(metadata):
return Table(
'files', metadata, Column('path', types.Unicode(255),
primary_key=True),
Column('name', types.Unicode(255)), Column('size', types.Integer),
Column('root', types.Unicode(255)), Column('ext', types.Unicode(20)),
Column('type', types.Unicode(20), default=u"other"),
Column('as_thumbnail', types.Boolean, default=False),
Column('in_dir', types.Unicode(255), schema.ForeignKey('dirs.path')),
Column('rev_id', types.Integer, schema.ForeignKey('revisions.id')),
Column('used_by', types.Integer, schema.ForeignKey('assets.id')))
class UsersPrefixAssociation(object):
__tablename__ = 'user_prefix_access'
user_id = schema.Column('user_id',
types.Integer,
schema.ForeignKey('users.id'),
primary_key=True)
prefix_id = schema.Column('prefix_id',
types.Integer,
schema.ForeignKey('prefix.id'),
primary_key=True)
write_access = schema.Column('write_access', types.Integer)
def _get_mapped_class_non_declarative(self):
from sqlalchemy import Table, MetaData, Column, types, orm, schema
from sqlalchemy.ext.associationproxy import association_proxy
from papyrus.geo_interface import GeoInterface
from geoalchemy import (GeometryExtensionColumn, GeometryColumn,
Geometry)
from geoalchemy.postgis import PGComparator
md = MetaData()
child1_table = Table(
'child1', md, Column('id', types.Integer, primary_key=True),
Column('name', types.Unicode),
Column('parent_id', types.Integer, schema.ForeignKey('parent.id')))
child2_table = Table('child2', md,
Column('id', types.Integer, primary_key=True),
Column('name', types.Unicode))
parent_table = Table(
'parent', md, Column('id', types.Integer, primary_key=True),
Column('text', types.Unicode),
GeometryExtensionColumn('geom', Geometry(dimension=2, srid=3000)),
Column('child_id', types.Integer, schema.ForeignKey('child2.id')))
class Child1(object):
def __init__(self, name):
self.name = name
orm.mapper(Child1, child1_table)
class Child2(object):
def __init__(self, name):
self.name = name
orm.mapper(Child2, child2_table)
class Parent(GeoInterface):
children = association_proxy('children_', 'name')
child = association_proxy('child_', 'name')
__add_properties__ = ('child', 'children')
orm.mapper(Parent,
parent_table,
properties={
'geom':
GeometryColumn(parent_table.c.geom,
comparator=PGComparator),
'children_':
orm.relationship(Child1),
'child_':
orm.relationship(Child2)
})
return Parent
class NotificationDispatch(bm.Base, bm.BaseMixin):
"""A notification dispatch to an user, holds information about how to deliver
and when."""
__tablename__ = 'notifications_dispatch'
# Has a due date.
due = schema.Column(types.DateTime)
# Has a sent date.
sent = schema.Column(types.DateTime)
# has a Notification.
notification_id = schema.Column(
types.Integer,
schema.ForeignKey('notifications.id'),
)
# bcc info
bcc = schema.Column(types.Unicode(96))
# view -> function to decode things
view = schema.Column(types.Unicode(96))
# simple for the moment, either single or batch text. XXX use ENUM.
type_ = schema.Column(types.Unicode(96))
# dotted path for the asset spec.
single_spec = schema.Column(types.Unicode(96))
batch_spec = schema.Column(types.Unicode(96))
# simple for the moment, either email or sms. XXX use ENUM.
category = schema.Column(types.Unicode(96))
# email or telephone number
address = schema.Column(types.Unicode(96))
def test_simple(self):
class A(self.Entity):
name = Field(String(60))
self.create_all()
b_table = schema.Table(
'b', self.metadata, schema.Column('id', Integer, primary_key=True),
schema.Column('name', String(60)),
schema.Column('a_id', Integer, schema.ForeignKey(A.id)))
b_table.create()
class B(object):
pass
orm.mapper(B, b_table, properties={'a': orm.relation(A)})
with self.session.begin():
b1 = B()
b1.name = 'b1'
b1.a = A(name='a1')
self.session.add(b1)
self.session.expire_all()
b = self.session.query(B).one()
assert b.a.name == 'a1'
def test_manual_table_auto_joins(self):
from sqlalchemy import Table, Column, ForeignKey, ForeignKeyConstraint
a_b = schema.Table('a_b', self.metadata,
schema.Column('a_key1', Integer()),
schema.Column('a_key2', String(40) ),
schema.Column('b_id', Integer(), schema.ForeignKey('b.id')),
schema.ForeignKeyConstraint(['a_key1', 'a_key2'],
['a.key1', 'a.key2']))
class A(self.Entity):
using_options(shortnames=True)
key1 = Field(Integer, primary_key=True, autoincrement=False)
key2 = Field(String(40), primary_key=True)
bs_ = ManyToMany('B', table=a_b)
class B(self.Entity):
using_options(shortnames=True)
name = Field(String(60))
as_ = ManyToMany('A', table=a_b)
self.create_all()
with self.session.begin():
b1 = B(name='b1', as_=[A(key1=10, key2='a1')])
self.session.expire_all()
a = A.query.one()
b = B.query.one()
assert a in b.as_
assert b in a.bs_
class AccountChangeRequest(BaseObject):
__tablename__ = "account_change"
id = schema.Column(types.String(16), primary_key=True, nullable=False)
account_id = schema.Column(
types.Integer(),
schema.ForeignKey(Account.id, onupdate="CASCADE", ondelete="CASCADE"),
nullable=False,
unique=True,
)
account = orm.relation(
Account,
backref=orm.backref(
"change_request",
uselist=False,
cascade="all, delete, delete-orphan",
),
)
value = schema.Column(
types.String(255),
nullable=False,
)
expires = schema.Column(
types.DateTime(),
nullable=False,
)
def define_image_tags_table(meta):
# Load the images table so the foreign key can be set up properly
schema.Table('images', meta, autoload=True)
image_tags = schema.Table('image_tags',
meta,
schema.Column('id',
glance_schema.Integer(),
primary_key=True,
nullable=False),
schema.Column('image_id',
glance_schema.String(36),
schema.ForeignKey('images.id'),
nullable=False),
schema.Column('value',
glance_schema.String(255),
nullable=False),
mysql_engine='InnoDB')
schema.Index('ix_image_tags_image_id', image_tags.c.image_id)
schema.Index('ix_image_tags_image_id_tag_value', image_tags.c.image_id,
image_tags.c.value)
return image_tags
class PurchaseMaterial(Model):
__tablename__ = 'purchase_material'
id = schema.Column(types.Integer, primary_key=True)
# category 航材类型
category = schema.Column(types.String(255))
application_id = schema.Column(
types.Integer,
schema.ForeignKey('purchase_application.id'),
nullable=False)
# partNumber 件号
partNumber = schema.Column(types.String(255))
# name 航材名称
name = schema.Column(types.String(255))
# manufacturer 生产厂商
manufacturer = schema.Column(types.String(255))
# unitPrice 单价
unitPrice = schema.Column(types.Float)
# quantity 数量
quantity = schema.Column(types.Integer)
# budget 预算
budget = schema.Column(types.String(255))
class Attempt(Base):
__tablename__ = 'Attempt'
__table_args__ = (schema.Index('name_group_test_date', 'first_name',
'middle_name', 'last_name', 'group',
'testsuite_id', 'date'), {
'mysql_engine': 'InnoDB'
})
id = schema.Column(types.Integer, primary_key=True)
first_name = schema.Column(types.Unicode(255), nullable=False)
middle_name = schema.Column(types.Unicode(255), nullable=False)
last_name = schema.Column(types.Unicode(255), nullable=False)
group = schema.Column(types.Unicode(255), nullable=False)
testsuite_id = schema.Column(types.Integer,
schema.ForeignKey('TestSuite.id',
ondelete='SET NULL'),
nullable=True)
testsuite = orm.relationship(TestSuite)
test = schema.Column(types.Binary, nullable=False)
date = schema.Column(types.DateTime,
default=datetime.datetime.now,
nullable=False)
is_attempted = schema.Column(types.Boolean, default=False, nullable=False)
is_attempted_correct = schema.Column(types.Boolean,
default=False,
nullable=False)
result = schema.Column(types.Binary, nullable=True)
def init_user_data_table(metadata):
return Table(
'user_data', metadata, Column('id', types.Integer, primary_key=True),
Column('name', types.Unicode(255)), Column('nick', types.Unicode(255)),
Column('vcs_user', types.Unicode(255)),
Column('vcs_pass', types.Unicode(255)),
Column('user_id', types.Integer, schema.ForeignKey('users.id')))
class Notification(bm.Base, bm.BaseMixin):
"""A notification about an event that should be sent to an user."""
__tablename__ = 'notifications'
# has an user.
user_id = schema.Column(
types.Integer,
schema.ForeignKey('auth_users.id'),
)
user = orm.relationship(
simpleauth_model.User,
backref='notification',
)
# Has a read date.
read = schema.Column(types.DateTime)
notification_dispatch = orm.relationship(
NotificationDispatch,
backref='notification')
# has an Activity event.
# One to many
event_id = schema.Column(
types.Integer,
schema.ForeignKey('activity_events.id'),
)
event = orm.relationship(
ActivityEvent,
backref=orm.backref(
'notification',
),
)
def __json__(self, request=None):
"""Represent the event as a JSON serialisable dict."""
data = {
'id': self.id,
'user_id': self.user_id,
'created_at': self.created.isoformat(),
'read_at': self.read.isoformat(),
'event_id': self.event_id,
}
return data
class SubFormula(Model):
"子配方信息"
__tablename__ = 'sub_formula'
id = schema.Column(types.Integer, primary_key=True)
pesticide_id = schema.Column(types.Integer,
schema.ForeignKey('pesticide.id'),
nullable=False)
# 重量
weight = schema.Column(types.DECIMAL(precision=20, scale=3))
formula_id = schema.Column(types.Integer, schema.ForeignKey('formula.id'))
def __str__(self):
return '农药:{} 重量:{}'.format(self.pesticide, self.weight)
class Child1(Base):
__tablename__ = 'child1'
id = Column(types.Integer, primary_key=True)
name = Column(types.Unicode)
parent_id = Column(types.Integer, schema.ForeignKey('parent.id'))
def __init__(self, name):
self.name = name
def reflecttable(self, table):
c = self.execute("PRAGMA table_info(" + table.name + ")", {})
while True:
row = c.fetchone()
if row is None:
break
#print "row! " + repr(row)
(name, type, nullable, primary_key) = (row[1], row[2].upper(),
not row[3], row[5])
match = re.match(r'(\w+)(\(.*?\))?', type)
coltype = match.group(1)
args = match.group(2)
#print "coltype: " + repr(coltype) + " args: " + repr(args)
coltype = pragma_names.get(coltype, SLString)
if args is not None:
args = re.findall(r'(\d+)', args)
#print "args! " +repr(args)
coltype = coltype(*[int(a) for a in args])
table.append_item(
schema.Column(name,
coltype,
primary_key=primary_key,
nullable=nullable))
c = self.execute("PRAGMA foreign_key_list(" + table.name + ")", {})
while True:
row = c.fetchone()
if row is None:
break
(tablename, localcol, remotecol) = (row[2], row[3], row[4])
#print "row! " + repr(row)
remotetable = Table(tablename, self, autoload=True)
table.c[localcol].append_item(
schema.ForeignKey(remotetable.c[remotecol]))
# check for UNIQUE indexes
c = self.execute("PRAGMA index_list(" + table.name + ")", {})
unique_indexes = []
while True:
row = c.fetchone()
if row is None:
break
if (row[2] == 1):
unique_indexes.append(row[1])
# loop thru unique indexes for one that includes the primary key
for idx in unique_indexes:
c = self.execute("PRAGMA index_info(" + idx + ")", {})
cols = []
while True:
row = c.fetchone()
if row is None:
break
cols.append(row[2])
col = table.columns[row[2]]
# unique index that includes the pk is considered a multiple primary key
for col in cols:
column = table.columns[col]
table.columns[col]._set_primary_key()
def attach(self, entity, name):
super(Status, self).attach(entity, name)
assert entity != Entity
status_name = entity.__name__.lower() + '_status'
status_type_name = entity.__name__.lower() + '_status_type'
# use `type` instead of `class`, to give status type and history
# classes a specific name, so these classes can be used whithin the
# memento and the fixture module
if self.enumeration == None:
status_type = type(entity.__name__ + 'StatusType', (
StatusType,
entity._descriptor.entity_base,
), {'__tablename__': status_type_name})
foreign_key = schema.ForeignKey(status_type.id,
ondelete='cascade',
onupdate='cascade')
status_history = type(
entity.__name__ + 'StatusHistory', (
StatusHistory,
entity._descriptor.entity_base,
), {
'__tablename__':
status_name,
'classified_by_id':
schema.Column(types.Integer(), foreign_key,
nullable=False),
'classified_by':
orm.relationship(status_type)
})
self.status_type = status_type
setattr(entity, '_%s_type' % name, self.status_type)
else:
status_history = type(
entity.__name__ + 'StatusHistory', (
StatusHistory,
entity._descriptor.entity_base,
), {
'__tablename__':
status_name,
'classified_by':
schema.Column(Enumeration(self.enumeration),
nullable=False,
index=True)
})
self.status_history = status_history
setattr(entity, '_%s_history' % name, self.status_history)
class Comment(Base):
__tablename__ = 'comments'
id = schema.Column(types.Integer,
schema.Sequence('comment_seq_id', optional=True),
primary_key=True)
pageid = schema.Column(types.Integer,
schema.ForeignKey('pages.id'),
nullable=False)
content = schema.Column(types.Text(), default=u'')
name = schema.Column(types.Unicode(255))
email = schema.Column(types.Unicode(255), nullable=False)
created = schema.Column(types.TIMESTAMP(), default=now)
class Parent(Base):
__tablename__ = 'parent'
id = Column(types.Integer, primary_key=True)
text = Column(types.Unicode)
geom = GeometryColumn(Geometry(dimension=2, srid=3000))
children_ = orm.relationship(Child1, backref="parent")
child_id = Column(types.Integer, schema.ForeignKey('child2.id'))
child_ = orm.relationship(Child2)
children = association_proxy('children_', 'name')
child = association_proxy('child_', 'name')
__add_properties__ = ('child', 'children')
class Answer(Base):
__tablename__ = 'Answer'
__table_args__ = ({'mysql_engine': 'InnoDB'}, )
id = schema.Column(types.Integer, primary_key=True)
name = schema.Column(types.Unicode(255), nullable=False)
is_correct = schema.Column(types.Boolean, nullable=False)
question_id = schema.Column(types.Integer,
schema.ForeignKey('Question.id',
ondelete='CASCADE'),
nullable=False)
question = orm.relationship(Question)
def create_non_pk_cols(self):
table = orm.class_mapper(self.entity).local_table
for col in table.primary_key.columns:
col_name = u'status_for_%s' % col.name
if not hasattr(self.status_history, col_name):
constraint = schema.ForeignKey(col,
ondelete='cascade',
onupdate='cascade')
column = schema.Column(types.Integer(),
constraint,
nullable=False)
setattr(self.status_history, col_name, column)
class Comment(Base):
__tablename__ = 'comment'
id = Column(
Integer,
Sequence('comment_seq_id', optional=True),
primary_key=True,
)
pageid = Column(Integer, schema.ForeignKey('page.id'), nullable=False)
content = Column(Text(), default=u'')
name = Column(Unicode(255), nullable=False, unique=True)
email = Column(Unicode(255), nullable=False)
created = Column(TIMESTAMP(), default=now())
class UsersNotInterestedInCertificateStatusBox(model.BaseObject):
""""""
__tablename__ = "users_not_interested_in_certificate_status_box"
account_id = schema.Column(
types.Integer(),
schema.ForeignKey(model.Account.id,
onupdate="CASCADE",
ondelete="CASCADE"),
nullable=False,
primary_key=True,
)
def create_non_pk_cols(self):
table = orm.class_mapper(self.type_entity).local_table
for col in table.primary_key.columns:
col_name = u'%s_%s' % (self.name, col.name)
if not hasattr(self.entity, col_name):
constraint = schema.ForeignKey(col,
ondelete='restrict',
onupdate='cascade')
column = schema.Column(PrimaryKey(),
constraint,
index=True,
nullable=self.nullable)
setattr(self.entity, col_name, column)
class Subscription(AbstractModel):
__tablename__ = 'subscriptions'
id = schema.Column(types.String(150), primary_key=True)
collection_id = schema.Column(
types.Integer,
schema.ForeignKey(
'data_collections.id', onupdate='CASCADE', ondelete='CASCADE'))
collection = relationship('DataCollection', backref='subscriptions')
params = schema.Column(types.Text, nullable=True)
# FIXME: proper enum type
status = schema.Column(types.String(150))
service_id = schema.Column(
types.String(150),
schema.ForeignKey(
'services.id', onupdate="CASCADE", ondelete="CASCADE"))
service = relationship('Service', backref='subscriptions')
class Question(Base):
__tablename__ = 'Question'
__table_args__ = ({'mysql_engine': 'InnoDB'}, )
id = schema.Column(types.Integer, primary_key=True)
testsuite_id = schema.Column(types.Integer,
schema.ForeignKey('TestSuite.id',
ondelete='CASCADE'),
nullable=False)
testsuite = orm.relationship(TestSuite)
name = schema.Column(types.UnicodeText, nullable=False)
answers = orm.relationship('Answer',
cascade='all, delete-orphan',
passive_deletes=True)
