class StatusHistory(object):
"""Mixin class to track the history of the status an object
has.
.. attribute:: status_datetime For statuses that occur at a specific point in time
.. attribute:: status_from_date For statuses that require a date range
.. attribute:: from_date When a status was enacted or set
"""
status_datetime = schema.Column(types.Date, nullable=True)
status_from_date = schema.Column(types.Date, nullable=True)
status_thru_date = schema.Column(types.Date, nullable=True)
from_date = schema.Column(types.Date,
nullable=False,
default=datetime.date.today)
thru_date = schema.Column(types.Date, nullable=False, default=end_of_times)
class Admin(EntityAdmin):
#verbose_name = statusable_entity + ' Status'
#verbose_name_plural = statusable_entity + ' Statuses'
list_display = [
'status_from_date', 'status_thru_date', 'classified_by'
]
#verbose_name = statusable_entity + ' Status'
#if verbose_entity_name is not None:
#verbose_name = verbose_entity_name + ' Status'
def __unicode__(self):
return u'Status'
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
def test_sql_field_attributes(self):
#
# test a generic SQLA field type
#
column_1 = schema.Column(types.Unicode(), nullable=False)
fa_1 = EntityAdmin.get_sql_field_attributes([column_1])
self.assertTrue(fa_1['editable'])
self.assertFalse(fa_1['nullable'])
self.assertEqual(fa_1['delegate'], delegates.PlainTextDelegate)
#
# test sql standard types
#
column_2 = schema.Column(types.FLOAT(), nullable=True)
fa_2 = EntityAdmin.get_sql_field_attributes([column_2])
self.assertTrue(fa_2['editable'])
self.assertTrue(fa_2['nullable'])
self.assertEqual(fa_2['delegate'], delegates.FloatDelegate)
#
# test a vendor specific field type
#
from sqlalchemy.dialects import mysql
column_3 = schema.Column(mysql.BIGINT(), default=2)
fa_3 = EntityAdmin.get_sql_field_attributes([column_3])
self.assertTrue(fa_3['default'])
self.assertEqual(fa_3['delegate'], delegates.IntegerDelegate)
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'
class Permission(Base):
__tablename__ = 'Permission'
__table_args__ = ({'mysql_engine': 'InnoDB'}, )
id = schema.Column(types.Integer, primary_key=True)
name = schema.Column(types.Unicode(255), unique=True, nullable=False)
groups = orm.relationship('Group', secondary=group_permission_table)
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,
)
class AirmaterialCategory(Model, AuditModel):
"航材类别的模型定义"
# 为了兼容原外包实现的名称
__tablename__ = 'airmaterial_category'
id = schema.Column(types.Integer, primary_key=True)
# partNumber 航材件号
partNumber = schema.Column(types.String(255), nullable=False, unique=True)
# name 航材名称
name = schema.Column(types.String(255), nullable=False)
# category 航材类型
category = schema.Column(types.String(255), nullable=False)
# miniStock 最低库存
minStock = schema.Column(types.Integer)
# unit 航材单位
unit = schema.Column(types.String(255))
# applicableModel 适用机型
applicableModel = schema.Column(types.String(255))
# 是否有库存有效期
isOrNotHaveEffectiveDate = schema.Column(types.Boolean)
# 是否有定期检查
isOrNotHavePeriodCheck = schema.Column(types.Boolean)
# statusName 状态值
statusName = schema.Column(types.String(255))
@property
def status(self):
return self.statusName
@status.setter
def status(self, value):
self.statusName = value
class CompanyStatistics(Base):
"""Statistically relevant data concerning a company."""
__tablename__ = "company"
id = schema.Column(types.Integer(), primary_key=True, autoincrement=True)
country = schema.Column(types.String(3))
employees = schema.Column(
Enum(["no answer", "1-9", "10-49", "50-249", "250+"]))
conductor = schema.Column(
Enum(["no answer", "staff", "third-party", "both"]))
referer = schema.Column(
Enum([
"no answer",
"employers-organisation",
"trade-union",
"national-public-institution",
"eu-institution",
"health-safety-experts",
"other",
]))
workers_participated = schema.Column(Enum(["no answer", "yes", "no"]))
needs_met = schema.Column(Enum(["no answer", "yes", "no"]))
recommend_tool = schema.Column(Enum(["no answer", "yes", "no"]))
date = schema.Column(types.DateTime(), nullable=True)
tool_path = schema.Column(types.String(512), nullable=False)
def test_manual_table_manual_joins(self):
from sqlalchemy import Table, Column, and_
a_b = schema.Table('a_b', self.metadata,
schema.Column('a_key1', Integer),
schema.Column('a_key2', String(40)),
schema.Column('b_id', String(60)))
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,
primaryjoin=lambda: and_(A.key1 == a_b.c.a_key1,
A.key2 == a_b.c.a_key2),
secondaryjoin=lambda: B.id == a_b.c.b_id,
foreign_keys=[a_b.c.a_key1, a_b.c.a_key2,
a_b.c.b_id])
class B(self.Entity):
using_options(shortnames=True)
name = Field(String(60))
self.create_all()
with self.session.begin():
a1 = A(key1=10, key2='a1', bs_=[B(name='b1')])
self.session.expire_all()
a = A.query.one()
b = B.query.one()
assert b in a.bs_
class TechMaterial(Model, AuditModel):
"技术资料的模型定义"
# 为了兼容原外包实现的名称
__tablename__ = 'file_resource'
def _id_generator():
return id_generator('JSZL', TechMaterial, 'fileResourceNum')
id = schema.Column(types.Integer, primary_key=True)
statusName = schema.Column(types.String(255))
fileResourceNum = schema.Column(types.String(255), default=_id_generator)
version = schema.Column(types.String(255))
fileResourceName = schema.Column(types.String(255))
addTime = schema.Column(db.TIMESTAMP(True),
nullable=False,
default=datetime.now)
fileResourceType = schema.Column(types.String(255))
content = schema.Column(types.Text)
relatePlanType = schema.Column(types.String(255))
fileResourceUrl = schema.Column(types.String(1000))
@property
def status(self):
return self.statusName
@status.setter
def status(self, value):
self.statusName = value
@property
def business_id(self):
return self.fileResourceNum
class TrainigMaterial(Model, AuditModel):
"培训资料的模型定义"
# 为了兼容原外包实现的名称
__tablename__ = 'train_file_resource'
def _id_generator():
return id_generator('PXZL', TrainigMaterial, 'trainNumber')
id = schema.Column(types.Integer, primary_key=True)
trainNumber = schema.Column(types.String(255), default=_id_generator)
trainFileResourceType = schema.Column(types.String(255))
trainFileResourceName = schema.Column(types.String(255))
trainFileResourceContent = schema.Column(types.Text)
addTime = schema.Column(types.DateTime)
updateUser = schema.Column(types.String(255))
updTime = schema.Column(types.DateTime)
statusName = schema.Column(types.String(255))
trainFileResourceUrl = schema.Column(types.String(1000))
@property
def status(self):
return self.statusName
@status.setter
def status(self, value):
self.statusName = value
def _foreign_key_constraint(self, name, source, referent,
local_cols, remote_cols,
onupdate=None, ondelete=None,
deferrable=None, source_schema=None,
referent_schema=None):
m = sa_schema.MetaData()
if source == referent:
t1_cols = local_cols + remote_cols
else:
t1_cols = local_cols
sa_schema.Table(referent, m,
*[sa_schema.Column(n, NULLTYPE) for n in remote_cols],
schema=referent_schema)
t1 = sa_schema.Table(source, m,
*[sa_schema.Column(n, NULLTYPE) for n in t1_cols],
schema=source_schema)
tname = "%s.%s" % (referent_schema, referent) if referent_schema \
else referent
f = sa_schema.ForeignKeyConstraint(local_cols,
["%s.%s" % (tname, n)
for n in remote_cols],
name=name,
onupdate=onupdate,
ondelete=ondelete,
deferrable=deferrable
)
t1.append_constraint(f)
return f
class Prefix(Base):
__tablename__ = 'prefix'
id = schema.Column('id',
types.Integer,
schema.Sequence('prefix_id_seq'),
primary_key=True)
prefix = schema.Column('prefix', types.Text())
class AuditModel(object):
__versioned__ = {}
auditStatus = schema.Column(types.String(255))
timestamp = schema.Column(types.DateTime)
suggestion = schema.Column(types.String(255))
@declared_attr
def relatedUser_id(cls):
return schema.Column('relatedUser_id', ForeignKey('user.id'))
@declared_attr
def relatedUser(cls):
return relationship("User", foreign_keys=[cls.relatedUser_id])
@declared_attr
def allowedUser_id(cls):
return schema.Column('allowedUser_id', ForeignKey('user.id'))
@declared_attr
def allowedUser(cls):
return relationship("User", foreign_keys=[cls.allowedUser_id])
@property
def business_id(self):
raise NotImplementedError()
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_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_
def test_m2o_non_pk_target(self):
a_table = schema.Table('a', self.metadata,
schema.Column('id', Integer, primary_key=True),
schema.Column('name', String(60), unique=True))
a_table.create()
class A(object):
pass
orm.mapper(A, a_table)
class B(self.Entity):
name = Field(String(60))
a = ManyToOne(A, target_column=['name'])
self.create_all()
with self.session.begin():
a1 = A()
a1.name = 'a1'
b1 = B(name='b1', a=a1)
self.session.expire_all()
b = B.query.one()
assert b.a.name == 'a1'
def create_sa_proxies(self):
# create the table and mapper
metadata = schema.MetaData()
user_table = schema.Table(
'user', metadata,
schema.Column('id', types.Integer, primary_key=True),
schema.Column('first_name', types.Unicode(25)),
schema.Column('last_name', types.Unicode(25)))
class User(object):
pass
orm.mapper(User, user_table)
# create the session
engine = create_engine('sqlite:///:memory:')
metadata.bind = engine
metadata.create_all()
session = orm.sessionmaker(bind=engine)()
# add some dummy data
user_table.insert().execute([{
'first_name': 'Jonathan',
'last_name': 'LaCour'
}, {
'first_name': 'Yoann',
'last_name': 'Roman'
}])
# get the SA objects
self.sa_object = session.query(User).first()
select = user_table.select()
self.result_proxy = select.execute()
self.row_proxy = select.execute().fetchone()
class PartyCategory(Entity):
using_options(tablename='party_category')
name = schema.Column(Unicode(40), index=True, nullable=False)
color = schema.Column(camelot.types.Color())
# end category definition
parties = ManyToMany('Party',
lazy=True,
backref='categories',
tablename='party_category_party',
remote_colname='party_id',
local_colname='party_category_id')
def get_contact_mechanisms(self, virtual_address_type):
"""Function to be used to do messaging
:param virtual_address_type: a virtual address type, such as 'phone' or 'email'
:return: a generator that yields strings of contact mechanisms, egg '*****@*****.**'
"""
for party in self.parties:
for party_contact_mechanism in party.contact_mechanisms:
contact_mechanism = party_contact_mechanism.contact_mechanism
if contact_mechanism:
virtual_address = contact_mechanism.mechanism
if virtual_address and virtual_address[
0] == virtual_address_type:
yield virtual_address[1]
def __unicode__(self):
return self.name or ''
class Admin(EntityAdmin):
verbose_name = _('Category')
verbose_name_plural = _('Categories')
list_display = ['name', 'color']
class MyModel(Base):
__tablename__ = 'my_table'
id = schema.Column(sqltypes.Integer, primary_key=True)
uuid = schema.Column(sqltypes.String(36), nullable=False, unique=True)
x = schema.Column(sqltypes.Integer)
y = schema.Column(sqltypes.String(40))
z = schema.Column(sqltypes.String(40))
class PartyAddressRoleType(Entity):
using_options(tablename='party_address_role_type')
code = schema.Column(Unicode(10))
description = schema.Column(Unicode(40))
class Admin(EntityAdmin):
verbose_name = _('Address role type')
list_display = ['code', 'description']
class Users(object):
__tablename__ = 'users'
id = schema.Column('id',
types.Integer,
schema.Sequence('users_id_seq'),
primary_key=True)
name = schema.Column('name', types.Text())
password = schema.Column('password', types.Text())
real_name = schema.Column('real_name', types.Text())
class PartyAddress(Entity, Addressable):
using_options(tablename='party_address')
party = ManyToOne(Party,
required=True,
ondelete='cascade',
onupdate='cascade',
lazy='subquery',
backref=orm.backref(
'addresses',
lazy=True,
cascade='all, delete, delete-orphan'))
address = ManyToOne(Address,
required=True,
backref='party_addresses',
ondelete='cascade',
onupdate='cascade',
lazy='subquery')
from_date = schema.Column(Date(),
default=datetime.date.today,
nullable=False,
index=True)
thru_date = schema.Column(Date(),
default=end_of_times,
nullable=False,
index=True)
comment = schema.Column(Unicode(256))
def party_name(self):
return sql.select([sql.func.coalesce(Party.full_name, '')],
whereclause=(Party.id == self.party_id))
party_name = ColumnProperty(party_name, deferred=True)
def __unicode__(self):
return '%s : %s' % (six.text_type(
self.party), six.text_type(self.address))
class Admin(EntityAdmin):
verbose_name = _('Address')
verbose_name_plural = _('Addresses')
list_search = [
'party_name',
'street1',
'street2',
]
list_display = ['party_name', 'street1', 'street2', 'city', 'comment']
form_display = [
'party', 'street1', 'street2', 'city', 'comment', 'from_date',
'thru_date'
]
form_size = (700, 200)
field_attributes = dict(party_name=dict(
editable=False, name='Party', minimal_column_width=30))
def get_compounding_objects(self, party_address):
if party_address.address != None:
yield party_address.address
def foreign_key_constraint(
self,
name: Optional[str],
source: str,
referent: str,
local_cols: List[str],
remote_cols: List[str],
onupdate: Optional[str] = None,
ondelete: Optional[str] = None,
deferrable: Optional[bool] = None,
source_schema: Optional[str] = None,
referent_schema: Optional[str] = None,
initially: Optional[str] = None,
match: Optional[str] = None,
**dialect_kw
) -> "ForeignKeyConstraint":
m = self.metadata()
if source == referent and source_schema == referent_schema:
t1_cols = local_cols + remote_cols
else:
t1_cols = local_cols
sa_schema.Table(
referent,
m,
*[sa_schema.Column(n, NULLTYPE) for n in remote_cols],
schema=referent_schema
)
t1 = sa_schema.Table(
source,
m,
*[sa_schema.Column(n, NULLTYPE) for n in t1_cols],
schema=source_schema
)
tname = (
"%s.%s" % (referent_schema, referent)
if referent_schema
else referent
)
dialect_kw["match"] = match
f = sa_schema.ForeignKeyConstraint(
local_cols,
["%s.%s" % (tname, n) for n in remote_cols],
name=name,
onupdate=onupdate,
ondelete=ondelete,
deferrable=deferrable,
initially=initially,
**dialect_kw
)
t1.append_constraint(f)
return f
class MissionNature(Model):
"""任务性质信息"""
__tablename__ = 'mission_nature'
id = schema.Column(types.Integer, primary_key=True)
# 任务性质编号
number = schema.Column(types.Integer)
# 任务性质名称
name = schema.Column(types.String(100))
class SurveyStatistics(model.BaseObject):
"""Data table to store survey (tool) information."""
__tablename__ = "statistics_surveys"
zodb_path = schema.Column(types.String(512), primary_key=True)
language = schema.Column(types.String(128), nullable=True)
published = schema.Column(types.Boolean(), nullable=True)
published_date = schema.Column(types.DateTime, nullable=True)
creation_date = schema.Column(types.DateTime, nullable=True)
class PlaneType(Model):
"""机型信息"""
__tablename__ = 'plane_type'
id = schema.Column(types.Integer, primary_key=True)
# 机型编号
number = schema.Column(types.String(100))
# 机型名称
name = schema.Column(types.String(100))
class FlyNature(Model):
"""飞行性质信息"""
__tablename__ = 'fly_nature'
id = schema.Column(types.Integer, primary_key=True)
# 飞行性质编号
number = schema.Column(types.String(100))
# 飞行性质名称
name = schema.Column(types.String(100))
def upgrade(migrate_engine):
meta.bind = migrate_engine
table = schema.Table(
'instance', meta,
schema.Column('environment_id', types.String(100), primary_key=True),
schema.Column('instance_id', types.String(100), primary_key=True),
schema.Column('instance_type', types.Integer, nullable=False),
schema.Column('created', types.Integer, nullable=False),
schema.Column('destroyed', types.Integer, nullable=True))
table.create()
class SurveyStatistics(Base):
"""Statistically relevant data concerning a survey (tool)."""
__tablename__ = "tool"
tool_path = schema.Column(types.String(512), primary_key=True)
published_date = schema.Column(types.DateTime, nullable=True)
years_online = schema.Column(types.Integer(), nullable=True)
num_users = schema.Column(types.Integer(), nullable=True)
num_assessments = schema.Column(types.Integer(), nullable=True)
