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)