def join_function():
"""Join condition used for deletion"""
definition_id = foreign(CustomAttributeDefinition.definition_id)
definition_type = foreign(
CustomAttributeDefinition.definition_type)
return and_(definition_id == cls.id,
definition_type == cls._inflector.table_singular)
class GalleryFolder(Base):
__tablename__ = 'comic_chapters'
__table_args__ = (
# Ensure these are only checked at the end of the transaction, because
# otherwise it becomes very difficult to actually edit them
UniqueConstraint('left', deferrable=True, initially='DEFERRED'),
UniqueConstraint('right', deferrable=True, initially='DEFERRED'),
)
__scope__ = 'comic-chapter'
id = SurrogateKeyColumn()
comic_id = Column(Integer, ForeignKey(Comic.id), nullable=False)
comic = relationship(Comic, backref='chapters')
title = TitleColumn()
title_slug = SlugColumn(title)
# Nested set, whee. Unique index created above!
left = Column(Integer, nullable=False)
right = Column(Integer, nullable=False)
ancestors = relationship(
'GalleryFolder',
primaryjoin=(remote(left) < foreign(left)) &
(foreign(right) < remote(right)),
order_by=left.desc(),
viewonly=True,
uselist=True,
)
order = synonym('left')
def join_function():
"""Object and CAD join function."""
definition_id = foreign(CustomAttributeDefinition.definition_id)
definition_type = foreign(
CustomAttributeDefinition.definition_type)
return and_(or_(definition_id == cls.id, definition_id.is_(None)),
definition_type == cls._inflector.table_singular)
class Scuba2(jcmt.SCUBA2):
__tablename__ = 'SCUBA2'
__table_args__ = (
{'schema':'jcmt', 'extend_existing':False}
)
files = relationship(FILES,
primaryjoin=jcmt.SCUBA2.obsid_subsysnr==foreign(FILES.obsid_subsysnr),
foreign_keys=[FILES.obsid_subsysnr],
order_by=FILES.nsubscan)
processing_jobs = relationship(JsaProcJob,
secondary=jsa_proc.obsidss.__table__,
primaryjoin=jcmt.SCUBA2.obsid_subsysnr==foreign(jsa_proc.obsidss.obsid_subsysnr),
secondaryjoin=and_(
foreign(jsa_proc.obsidss.job_id)==remote(jsa_proc.job.id),
jsa_proc.job.task.in_(['jcmt-nightly', 'jcmt-reproc'])),
)
def get_previewname(self, obs):
return PREVIEW_FORMAT.format(obs.obsnum,
instletter='s',
utdate=obs.utdate,
subsys=self.filter)
def __repr__(self):
return "<{}({})>".format(self.__class__.__name__, self.obsid_subsysnr)
class Transfer(Base):
__tablename__ = 'transfers'
_plural_name_ = 'transfers'
feed_id = Column(Integer, ForeignKey('_feed.feed_id'), primary_key=True)
from_stop_id = Column(Unicode, primary_key=True)
to_stop_id = Column(Unicode, primary_key=True)
transfer_type = Column(Integer, nullable=True) # required; allowed empty
min_transfer_time = Column(Integer, nullable=True)
__table_args__ = (
ForeignKeyConstraint([feed_id, to_stop_id],
[Stop.feed_id, Stop.stop_id]),
ForeignKeyConstraint([feed_id, from_stop_id],
[Stop.feed_id, Stop.stop_id]),
)
stop_to = relationship(Stop,
backref="transfers_to",
primaryjoin=and_(
Stop.stop_id == foreign(to_stop_id),
Stop.feed_id == feed_id))
stop_from = relationship(Stop,
backref="transfers_from",
primaryjoin=and_(
Stop.stop_id == foreign(from_stop_id),
Stop.feed_id == feed_id))
_validate_transfer_type = _validate_int_choice([None, 0, 1, 2, 3],
'transfer_type')
def __repr__(self):
return "<Transfer %s-%s>" % (self.from_stop_id, self.to_stop_id)
class VacancyParameters(db.Model):
""" Table for storing cities and positions related to vacancy """
__table_args__ = (
db.UniqueConstraint(
'city_id', 'position_id', 'vacancy_id',
name='unique_vacancy_parameters',
),
)
id = db.Column(db.Integer, primary_key=True)
city_id = db.Column(db.Integer, db.ForeignKey('city.id'), nullable=False)
position_id = db.Column(db.Integer, db.ForeignKey('position.id'), nullable=False)
vacancy_id = db.Column(db.Integer, db.ForeignKey('vacancy.id'), nullable=False)
date_created = db.Column(db.DateTime, nullable=False, default=utc_now)
date_sent = db.Column(db.DateTime, nullable=True)
subscriptions = db.relationship(
'Subscription',
primaryjoin=sa.and_(
city_id == orm.foreign(Subscription.city_id),
position_id == orm.foreign(Subscription.position_id),
),
lazy=True,
)
def exists(self) -> bool:
vacancy = VacancyParameters.query.filter(
VacancyParameters.city_id == self.city_id,
VacancyParameters.position_id == self.position_id,
VacancyParameters.vacancy_id == self.vacancy_id,
).first()
return bool(vacancy)
class Game(Base):
__tablename__ = 'game'
team = Column(String, primary_key=True, index=True)
opponent = Column(String, primary_key=True, index=True)
date = Column(Date, primary_key=True, index=True)
result = Column(win_loss_enum, index=True)
points = Column(Integer)
field_goals = Column(Integer)
field_goal_attempts = Column(Integer)
three_points = Column(Integer)
three_point_attempts = Column(Integer)
free_throws = Column(Integer)
free_throw_attempts = Column(Integer)
offensive_rebounds = Column(Integer)
rebounds = Column(Integer)
assists = Column(Integer)
steals = Column(Integer)
blocks = Column(Integer)
turnovers = Column(Integer)
fouls = Column(Integer)
opp = relationship('Game',
uselist=False,
primaryjoin=and_(
foreign(team) == remote(opponent),
foreign(opponent) == remote(team),
foreign(date) == remote(date)))
def slices(self):
return relationship(
'Slice',
primaryjoin=lambda: and_(
foreign(Slice.datasource_id) == self.id,
foreign(Slice.datasource_type) == self.type,
),
)
def slices(self) -> RelationshipProperty:
return relationship(
"Slice",
primaryjoin=lambda: and_(
foreign(Slice.datasource_id) == self.id,
foreign(Slice.datasource_type) == self.type,
),
)
def slices(self):
return relationship(
'Slice',
primaryjoin=lambda: and_(
foreign(Slice.datasource_id) == self.id,
foreign(Slice.datasource_type) == self.type,
),
)
class Resource(db.Model, BaseModelMixin):
"""Resource object
Attributes:
resource_id (`str`): Unique Resource identifier
account_id (`int`): ID of the account owning the resource
location (`str`, optional): Optional location of the resource (eg. aws region)
resource_type (`str`): :obj:`ResourceType` reference
tags (`list` of :obj:`Tag`): List of tags applied to the volume
properties (`list` of :obj:`ResourceProperty`): List of properties of the resource
"""
__tablename__ = 'resources'
resource_id = Column(String(256), primary_key=True)
account_id = Column(Integer, index=True)
location = Column(String(50), nullable=True, index=True)
resource_type_id = Column(Integer(unsigned=True), index=True)
tags = relationship('Tag',
lazy='select',
uselist=True,
primaryjoin=resource_id == foreign(Tag.resource_id),
order_by=Tag.key,
cascade='all, delete-orphan')
properties = relationship('ResourceProperty',
lazy='select',
uselist=True,
primaryjoin=resource_id == foreign(
ResourceProperty.resource_id),
cascade='all, delete-orphan')
account = relationship('Account',
lazy='joined',
uselist=False,
primaryjoin=account_id == foreign(
Account.account_id),
viewonly=True)
children = relationship(
'Resource',
lazy='select',
uselist=True,
secondary='resource_mappings',
primaryjoin='Resource.resource_id == ResourceMapping.parent',
secondaryjoin='Resource.resource_id == ResourceMapping.child',
cascade='all, delete',
backref='parents')
@staticmethod
def get(resource_id):
"""Return resource by ID
Args:
resource_id (str): Unique Resource identifier
Returns:
:obj:`Resource`: Returns Resource object if found, else None
"""
return Resource.query.filter(
Resource.resource_id == resource_id).first()
class QueuePlayer(bot_declarative_base):
"""
Represents a player in queue in a channel for a specific role.
"""
__tablename__ = "queue_player"
channel_id = Column(
BigInteger,
ForeignKey("channel_information.id", **foreignkey_cascade_options),
primary_key=True,
index=True,
)
channel_information = relationship("ChannelInformation",
viewonly=True,
backref="game_participant_objects",
sync_backref=False)
role = Column(role_enum, primary_key=True)
# Saving both allows us to going to the Player table
player_id = Column(BigInteger, primary_key=True, index=True)
player_server_id = Column(BigInteger)
# Duo queue partner
duo_id = Column(BigInteger)
duo = relationship(
"QueuePlayer",
primaryjoin=(duo_id == foreign(player_id))
& (player_id == foreign(duo_id))
& (channel_id == foreign(channel_id)),
uselist=False,
)
# Queue start time to favor players who have been in queue longer
queue_time = Column(DateTime)
# None if not in a ready_check, ID of the ready check message otherwise
ready_check_id = Column(BigInteger)
# Player relationship, which we automatically load
player = relationship("Player", viewonly=True, lazy="selectin")
# Foreign key to Player
__table_args__ = (
ForeignKeyConstraint(
(player_id, player_server_id),
(Player.id, Player.server_id),
**foreignkey_cascade_options,
),
{},
)
def __str__(self):
return f"{self.player.name} - {self.role}"
class Task(Base):
"""."""
__tablename__ = "task"
task_id = Column("task_id", KeyInteger, primary_key=True)
wf_id = Column(
"wf_id",
KeyInteger,
ForeignKey(Workflow.wf_id, ondelete="CASCADE"),
nullable=False,
)
job_id = Column(
"job_id",
KeyInteger,
ForeignKey(Job.job_id, ondelete="SET NULL"),
)
abs_task_id = Column("abs_task_id", String(255), nullable=False)
transformation = Column("transformation", Text, nullable=False)
argv = Column("argv", Text)
type_desc = Column("type_desc", String(255), nullable=False)
# Relationships
parents = relation(
lambda: Task,
backref="children",
cascade="all",
secondary=lambda: TaskEdge.__table__,
primaryjoin=lambda: and_(
Task.wf_id == TaskEdge.wf_id,
Task.abs_task_id == foreign(TaskEdge.child_abs_task_id),
),
secondaryjoin=lambda: and_(
Task.wf_id == TaskEdge.wf_id,
Task.abs_task_id == foreign(TaskEdge.parent_abs_task_id),
),
)
task_files = relation(
lambda: WorkflowFiles,
backref="task",
cascade="all, delete-orphan",
passive_deletes=True,
)
files = relation(
lambda: RCLFN,
secondary=lambda: WorkflowFiles.__table__,
primaryjoin=lambda: Task.task_id == WorkflowFiles.task_id,
secondaryjoin=lambda: WorkflowFiles.lfn_id == RCLFN.lfn_id,
)
meta = relation(
lambda: TaskMeta,
backref="task",
cascade="all, delete-orphan",
passive_deletes=True,
)
def setup_listener(mapper, class_):
import_handler_type = class_.TYPE
class_.import_handler = relationship(
DataSet,
primaryjoin=and_(
class_.id == foreign(remote(DataSet.import_handler_id)),
DataSet.import_handler_type == import_handler_type),
cascade='all,delete',
backref=backref("parent_%s" % import_handler_type,
primaryjoin=remote(class_.id) == foreign(
DataSet.import_handler_id)))
class UserRole(BaseModel, db.Model):
__tablename__ = 'user_role'
id = Column(INTEGER(11), primary_key=True)
user_id = Column(ForeignKey(db_schema + '.user.id'), index=True)
role_id = Column(ForeignKey(db_schema + '.role.id'), index=True)
role = relationship('Role',
primaryjoin=remote(Role.id) == foreign(role_id))
user = relationship('User',
primaryjoin=remote(User.id) == foreign(user_id))
class ProductCategories(Base):
__tablename__ = 'product_categories'
id = Column('id', Integer, primary_key=True)
name = Column('name', String(64))
short_description = Column('short_description', String(500))
full_description = Column('full_description', String(1000))
images = Column('images', postgresql.ARRAY(Integer))
user_creator_id = Column('user_creator_id', ForeignKey('users.id'))
creation_date = Column('creation_date',
DateTime,
default=datetime.datetime.now(
datetime.timezone.utc))
is_lock = Column('is_lock', Boolean, default=False)
parent_category_id = Column('parent_category_id', Integer)
default_image_id = Column('default_image_id', ForeignKey('attachments.id'))
is_delete = Column('is_delete', Boolean, default=False)
default_image_data = relationship(
'Attachments', backref="default_image_data_product_categories")
child_categories = relationship(
"ProductCategories",
primaryjoin=and_(
remote(parent_category_id) == foreign(id),
foreign(is_delete) == False),
uselist=True)
internal_products_count = column_property(
select([func.count(Products.id)]). \
where(and_(Products.category_id == id, Products.is_delete == False, Products.not_show_in_catalog == False)). \
correlate_except(Products)
)
@property
def internal_categories_count(self):
return object_session(self). \
scalar(
select([func.count(ProductCategories.id)]). \
where(and_(ProductCategories.parent_category_id == self.id, ProductCategories.is_delete == False))
)
@property
def child_products_count(self):
child_ids = [x.id for x in self.child_categories]
return object_session(self). \
scalar(
select([func.count(Products.id)]) \
.where(and_(Products.category_id.in_(child_ids), Products.is_delete == False, Products.not_show_in_catalog == False))
)
def __init__(self, *args):
db_tranformer.transform_constructor_params(self, args)
self.creation_date = datetime.datetime.now(datetime.timezone.utc)
self.is_lock = False
def setup_listener(mapper, cls):
cls.history = db.relationship(
HistoryItem,
primaryjoin=and_(
cls.id == foreign(remote(HistoryItem.target_id)),
HistoryItem.target_discriminator == cls.history_discriminator,
),
backref=backref(
f"target_{cls.history_discriminator}",
primaryjoin=remote(cls.id) == foreign(HistoryItem.target_id),
),
)
class MediaMediaLink(Base, _LinkMixin):
__table__ = tables.media_media_links
_identity = 'describee_id'
_linkname = 'describing'
_linkjoin = __table__.c.described_with_id == MediaItem.mediaid,
describee = relationship(
MediaItem, backref='described',
primaryjoin=foreign(__table__.c.describee_id) == remote(MediaItem.mediaid))
media_item = relationship(
MediaItem, backref='describing',
primaryjoin=foreign(__table__.c.described_with_id) == remote(MediaItem.mediaid))
def condition():
from sqlalchemy.orm import foreign, remote
from .message import Message
message = sa.alias(Message, 'msg')
subquery = sa.select(
[sa.func.max(remote(message.c.message_id))],
whereclause=remote(message.c.dialog_id) == foreign(Dialog.id))
dialog_condition = remote(Message.dialog_id) == foreign(Dialog.id)
message_condition = remote(Message.id) == subquery
return dialog_condition & message_condition
class Coverage(Base):
__tablename__ = 'coverage'
lineId = Column(Integer(), ForeignKey('line.lineId'), primary_key=True)
testcaseId = Column(Integer(),
ForeignKey('testcase.testcaseId'),
primary_key=True)
testcase = relationship('TestCase',
primaryjoin=(testcaseId == foreign(
TestCase.testcaseId)),
uselist=False)
line = relationship('Line',
primaryjoin=(lineId == foreign(Line.lineId)),
uselist=False)
class Art(Base):
__tablename__ = 'art'
__table_args__ = (Index('ix_art', 'media_id', 'media_type', 'type'), )
art_id = Column('art_id', Integer, primary_key=True)
media_id = Column('media_id', Integer)
media_type = Column('media_type', Text)
type = Column('type', Text)
url = Column('url', Text)
movie = relationship(Movie,
primaryjoin=and_(Movie.id_movie == foreign(media_id),
media_type == 'movie'),
backref="art",
sync_backref=False,
viewonly=True)
tv_show = relationship(TvShow,
primaryjoin=and_(
TvShow.id_show == foreign(media_id),
media_type == 'tvshow'),
backref="art",
sync_backref=False,
viewonly=True)
season = relationship(Season,
primaryjoin=and_(
Season.id_season == foreign(media_id),
media_type == 'season'),
backref="art",
sync_backref=False,
viewonly=True)
episode = relationship(Episode,
primaryjoin=and_(
Episode.id_episode == foreign(media_id),
media_type == 'episode'),
backref="art",
sync_backref=False,
viewonly=True)
actor = relationship(Actor,
primaryjoin=and_(
Actor.actor_id == foreign(media_id),
media_type.in_([
'actor', 'director', 'producer', 'writer',
'gueststar'
])),
backref="art",
sync_backref=False,
viewonly=True)
class SQLLeaguePositions(SQLBaseObject):
_dto_type = LeaguePositionsDto
_table = Table("league_positions", metadata,
Column("summonerId", Integer, primary_key=True),
Column("platformId", String(5), primary_key=True),
Column("lastUpdate", BigInteger))
_relationships = {
"positions": (SQLLeaguePosition, {
"primaryjoin": (remote(_table.c.summonerId) == foreign(
SQLLeaguePosition.playerOrTeamId)) &
(remote(_table.c.platformId) == foreign(
SQLLeaguePosition.platformId)),
})
}
def setup_listener(mapper, class_):
name = class_.__name__
discriminator = name.lower()
class_.addresses = relationship(
Address,
primaryjoin=and_(class_.id == foreign(remote(Address.parent_id)),
Address.discriminator == discriminator),
backref=backref("parent_%s" % discriminator,
primaryjoin=remote(class_.id) == foreign(
Address.parent_id)))
@event.listens_for(class_.addresses, "append")
def append_address(target, value, initiator):
value.discriminator = discriminator
def setup_listener(mapper, class_):
name = class_.__name__
loggable_type = name.lower()
class_.logs = relationship(
Log,
primaryjoin=and_(class_.id == foreign(remote(Log.loggable_id)),
Log.loggable_type == loggable_type),
backref=backref("parent_%s" % loggable_type,
primaryjoin=remote(class_.id) == foreign(
Log.loggable_id)),
cascade="all, delete-orphan")
@event.listens_for(class_.logs, "append")
def append_address(target, value, initiator):
value.loggable_type = loggable_type
class RolePermissionScope(BaseModel, db.Model):
__tablename__ = 'role_permission_scope'
id = Column(INTEGER(11), primary_key=True, autoincrement=True)
role_id = Column(ForeignKey(db_schema + '.role.id'), index=True)
permission_scope_key = Column(ForeignKey(db_schema +
'.permission_scope.key'),
index=True)
permission_scope = relationship(
PermissionScope,
primaryjoin=remote(
PermissionScope.key) == foreign(permission_scope_key))
role = relationship('Role',
primaryjoin=remote(Role.id) == foreign(role_id))
class Trip(Base):
__tablename__ = 'trips'
_plural_name_ = 'trips'
feed_id = Column(Integer, ForeignKey('_feed.feed_id'), primary_key=True)
route_id = Column(Unicode)
service_id = Column(Unicode)
trip_id = Column(Unicode, primary_key=True, index=True)
id = synonym('trip_id')
trip_headsign = Column(Unicode, nullable=True)
trip_short_name = Column(Unicode, nullable=True)
direction_id = Column(Integer, nullable=True)
block_id = Column(Unicode, nullable=True)
shape_id = Column(Unicode, nullable=True)
wheelchair_accessible = Column(Integer, nullable=True)
bikes_allowed = Column(Integer, nullable=True)
__table_args__ = (
ForeignKeyConstraint([feed_id, route_id],
[Route.feed_id, Route.route_id]),
ForeignKeyConstraint([feed_id, service_id],
[Service.feed_id, Service.service_id]),
Index('idx_trips_shape_id', feed_id, shape_id),
)
route = relationship(Route,
backref="trips",
primaryjoin=and_(Route.route_id == foreign(route_id),
Route.feed_id == feed_id))
shape_points = relationship(ShapePoint,
backref="trips",
secondary="_trip_shapes")
# TODO: The service_id references to calendar or to calendar_dates.
# Need to implement this requirement, but not using a simple foreign key.
service = relationship(Service,
backref='trips',
primaryjoin=and_(
foreign(service_id) == Service.service_id,
feed_id == Service.feed_id))
_validate_direction_id = _validate_int_choice([None, 0, 1], 'direction_id')
_validate_wheelchair = _validate_int_choice([None, 0, 1, 2],
'wheelchair_accessible')
_validate_bikes = _validate_int_choice([None, 0, 1, 2], 'bikes_allowed')
def __repr__(self):
return '<Trip %s>' % self.trip_id
def redirect(cls):
pk = getattr(cls, cls.get_pk_name())
return relationship(
cls, foreign_keys=[cls.redirect_page],
remote_side='{}.{}'.format(cls.__name__, cls.get_pk_name()),
primaryjoin=lambda: foreign(cls.redirect_page) == pk,
)
def test_dont_send_neverset_to_get_w_relationship(self):
# test issue #3647
CompositePk, composite_pk_table = (self.classes.CompositePk, self.tables.composite_pk_table)
User, users = (self.classes.User, self.tables.users)
mapper(
User,
users,
properties={
"elements": relationship(CompositePk, primaryjoin=users.c.id == foreign(composite_pk_table.c.i))
},
)
mapper(CompositePk, composite_pk_table)
u1 = User(id=5, name="some user")
cp1 = CompositePk(j=1, k=1)
u1.elements.append(cp1)
sess = Session()
rec = []
def go():
rec.append(sess.merge(u1))
self.assert_sql_count(testing.db, go, 1)
u2 = rec[0]
sess.commit()
eq_(u2.elements[0].i, 5)
eq_(u2.id, 5)
def __init__(self, to, ct_field='model', to_field='object_id', lazy='dynamic', primary_join=None, *args, **kwargs):
if primary_join is None:
primary_join = lambda model, fk_model: and_(
model._meta.pk.column == foreign(fk_model._meta.fields[to_field].column),
fk_model._meta.fields_dict[ct_field].column == model._meta.name
)
super(GenericOneToManyField, self).__init__(to, to_field, lazy=lazy, primary_join=primary_join, *args, **kwargs)
class CAVoteDetail(Base):
__tablename__ = "bill_detail_vote_tbl"
bill_id = Column(String(20), ForeignKey(CABill.bill_id), primary_key=True)
location_code = Column(String(6),
ForeignKey(CAVoteSummary.location_code),
primary_key=True)
legislator_name = Column(String(50), primary_key=True)
vote_date_time = Column(DateTime,
ForeignKey(CAVoteSummary.vote_date_time),
primary_key=True)
vote_date_seq = Column(Integer,
ForeignKey(CAVoteSummary.vote_date_seq),
primary_key=True)
vote_code = Column(String(5), primary_key=True)
motion_id = Column(Integer,
ForeignKey(CAVoteSummary.motion_id),
primary_key=True)
trans_uid = Column(String(30), primary_key=True)
trans_update = Column(DateTime, primary_key=True)
bill = relation(
CABill,
primaryjoin="CABill.bill_id == foreign(CAVoteDetail.bill_id)",
backref=backref('detail_votes'))
summary = relation(CAVoteSummary,
primaryjoin=and_(
CAVoteSummary.bill_id == foreign(bill_id),
CAVoteSummary.location_code == location_code,
CAVoteSummary.vote_date_time == vote_date_time,
CAVoteSummary.vote_date_seq == vote_date_seq,
CAVoteSummary.motion_id == motion_id),
backref=backref('votes'))
def __declare_last__(cls):
from .invoice import Job
cls.jobs = relation('Job',
primaryjoin=cls.guid == foreign(Job.owner_guid),
cascade='all, delete-orphan',
collection_class=CallableList,
)
class HarnessOptSet(db.Table):
"""A set of of harness options.
An option set groups options for harnesses.
"""
__tablename__ = 'harness_optsets'
id_t = _HarnessOptSetId
# Columns.
id: bytes = sql.Column(id_t, nullable=False)
opt_id: int = sql.Column(_HarnessOptId,
sql.ForeignKey('harness_opts.id'),
nullable=False)
# Relationships.
harnesses: typing.List[Harness] = orm.relationship(
Harness, primaryjoin=id == orm.foreign(Harness.optset_id))
opt: 'HarnessOpt' = orm.relationship('HarnessOpt')
# Constraints.
__table_args__ = (sql.PrimaryKeyConstraint('id',
'opt_id',
name='unique_harness_optset'), )
def __repr__(self):
hex_id = binascii.hexlify(self.id).decode('utf-8')
return f'{hex_id}: {self.opt_id}={self.opt}'
class Character(Nameable):
"""Fictional character that appears in creative work."""
#: (:class:`int`) The primary key integer.
id = Column(Integer, ForeignKey(Nameable.id), primary_key=True)
#: (:class:'int') :class:`Character.id` of :attr:`original_character`.
original_character_id = Column(Integer, ForeignKey('characters.id'))
#: (:class:'Character') The original character from which this character
#: is derived.
original_character = relationship(
'Character', primaryjoin=foreign(original_character_id) == remote(id))
#: (:class:'collections.abc.MutableSet') The set of :class:`Character`\ s
#: which is derived from this character
derived_characters = relationship(
'Character',
primaryjoin=remote(original_character_id) == id,
collection_class=set)
#: (:class:`collections.abc.MutableSet`) The set of
#: :class:`Work`\ s in which the character appeared.
works = relationship('cliche.work.Work',
secondary='work_characters',
collection_class=set)
__tablename__ = 'characters'
__repr_columns__ = [id]
__mapper_args__ = {
'polymorphic_identity': 'characters',
}
class Suggest(HtSuggest, BaseModel, SerializerMixin):
#user表虚拟对象，关联的内容会在user对象中
# 第二个参数是backref会添加一个对象给suggest模型，容易造成递归堆栈超出，超级不建议使用！
# 第三个参数lazy是决定什么时候sqlalchemy从数据库中加载数据
#uselist=False一对一关系，true是一对多
# , backref = 'users'
users = relationship('Users',
uselist=False,
primaryjoin=foreign(HtSuggest.users_id) == remote(
Users.id)) # backref='suggest'这个是毒瘤，不用！！！
# 一对多普通方式
@staticmethod
def on_to_many():
data = dBSession.query(Suggest).filter(
Users.id == Suggest.users_id).all()
# all方法返回列表要进行处理才能调用sqlalchemy_serializer的to_dict方法
data_msg = Utils.db_l_to_d(data)
return data_msg
# 一对多join方式
@staticmethod
def join():
data = dBSession.query(Suggest).join(
Users, Users.id == Suggest.users_id).all()
data_msg = Utils.db_l_to_d(data)
return data_msg
# 一对多left join
@staticmethod
def leftJoin():
data = dBSession.query(Suggest).outerjoin(
Users, Users.id == Suggest.users_id).all()
data_msg = Utils.db_l_to_d(data)
return data_msg
def setup_listener(mapper, class_):
name = class_.__name__
discriminator = name.lower()
class_.addresses = relationship(Address,
primaryjoin=and_(
class_.id == foreign(remote(Address.parent_id)),
Address.discriminator == discriminator
),
backref=backref(
"parent_%s" % discriminator,
primaryjoin=remote(class_.id) == foreign(Address.parent_id)
)
)
@event.listens_for(class_.addresses, "append")
def append_address(target, value, initiator):
value.discriminator = discriminator
class SlotFrame(DictWrapper, Slot):
__mapper_args__ = {'polymorphic_identity': KVP_Type.KVP_TYPE_FRAME}
_python_type = (dict, )
guid_val = Column('guid_val', VARCHAR(length=32))
slots = relation(
'Slot',
primaryjoin=foreign(Slot.obj_guid) == guid_val,
cascade='all, delete-orphan',
collection_class=CallableList,
single_parent=True,
backref=backref("parent", remote_side=guid_val),
)
@property
def value(self):
# convert to dict
return {sl.name: sl.value for sl in self.slots}
@value.setter
def value(self, value):
self.slots = [
slot(parent=self, name=k, value=v) for k, v in value.items()
]
def __init__(self, **kwargs):
self.guid_val = uuid.uuid4().hex
super(SlotFrame, self).__init__(**kwargs)
def slots(cls):
rel = relation('Slot',
primaryjoin=foreign(Slot.obj_guid) == cls.guid,
cascade='all, delete-orphan',
collection_class=CallableList,
)
return rel
def setup_listener(mapper, class_):
name = class_.__name__
content_type = name.lower()
# Reverse relationship (ie. Segment.entities)
class_.entities = relationship(Entity,
primaryjoin = and_(
class_.id == foreign(remote(Entity.object_id)),
Entity.content_type == content_type
),
backref = backref(
'parent_{0}'.format(content_type),
primaryjoin=remote(class_.id) == foreign(Entity.object_id)
)
)
@event.listens_for(class_.entities, 'append')
def append_entity(target, value, initiator):
value.content_type = content_type
def _unhashable_fixture(self, metadata, load_on_pending=False):
class MyHashType(sa.TypeDecorator):
impl = sa.String(100)
def process_bind_param(self, value, dialect):
return ";".join(
"%s=%s" % (k, v)
for k, v in sorted(value.items(), key=lambda key: key[0])
)
def process_result_value(self, value, dialect):
return dict(elem.split("=", 1) for elem in value.split(";"))
category = Table(
"category",
metadata,
Column("id", Integer, primary_key=True),
Column("data", MyHashType()),
)
article = Table(
"article",
metadata,
Column("id", Integer, primary_key=True),
Column("data", MyHashType()),
)
class Category(fixtures.ComparableEntity):
pass
class Article(fixtures.ComparableEntity):
pass
mapper(Category, category)
mapper(
Article,
article,
properties={
"category": relationship(
Category,
primaryjoin=orm.foreign(article.c.data) == category.c.data,
load_on_pending=load_on_pending,
)
},
)
metadata.create_all()
sess = Session(autoflush=False)
data = {"im": "unhashable"}
a1 = Article(id=1, data=data)
c1 = Category(id=1, data=data)
if load_on_pending:
sess.add(c1)
else:
sess.add_all([c1, a1])
sess.flush()
if load_on_pending:
sess.add(a1)
return Category, Article, sess, a1, c1
def rel_text_entry(self):
"""
Relation to TextEntry
:return: (object) SQLAlchemy relationship description
:since: v0.2.00
"""
return relationship(TextEntry, primaryjoin = (foreign(self.id) == remote(TextEntry.id)), uselist = False)
def rel_acl(self):
"""
Relation to AclEntry
:return: (object) SQLAlchemy relationship description
:since: v0.2.00
"""
return relationship(AclEntry, primaryjoin = (foreign(self.id) == remote(AclEntry.owned_id)), uselist = True)
def _join_fixture_o2m_to_annotated_func(self, **kw):
return relationships.JoinCondition(
self.left,
self.right,
self.left,
self.right,
primaryjoin=self.left.c.id == foreign(func.foo(self.right.c.lid)),
**kw
)
def _join_fixture_purely_single_m2o(self, **kw):
return relationships.JoinCondition(
self.purely_single_col,
self.purely_single_col,
self.purely_single_col,
self.purely_single_col,
support_sync=False,
primaryjoin=remote(self.purely_single_col.c.path).like(
foreign(self.purely_single_col.c.path.concat("%"))
),
)
def _join_fixture_remote_local_multiple_ref(self, **kw):
fn = lambda a, b: ((a == b) | (b == a))
return relationships.JoinCondition(
self.selfref, self.selfref,
self.selfref, self.selfref,
support_sync=False,
primaryjoin=fn(
# we're putting a do-nothing annotation on
# "a" so that the left/right is preserved;
# annotation vs. non seems to affect __eq__ behavior
self.selfref.c.sid._annotate({"foo": "bar"}),
foreign(remote(self.selfref.c.sid)))
)
def setup_mappers(cls):
mapper(cls.classes.Person, cls.tables.person, properties=dict(
pets=relationship(
cls.classes.Pet, primaryjoin=(
orm.foreign(cls.tables.pets.c.person_id) ==
sa.cast(
sa.type_coerce(cls.tables.person.c.id, Integer),
Integer
)
)
)
))
mapper(cls.classes.Pet, cls.tables.pets)
def related(cls):
id_column = getattr(cls, id_label)
related_id_column = getattr(related_model, related_id_label)
filters = [foreign(id_column) == related_id_column]
if cycle_label:
cycle_column = getattr(cls, cycle_label)
if use_modulus:
cycle_column = cycle_column + cycle_column % 2
related_cycle_column = getattr(related_model, related_cycle_label)
filters.append(cycle_column == related_cycle_column)
return db.relationship(
related_model,
primaryjoin=sa.and_(*filters),
)
def fetch_users_to_sync(session, required_property=None):
# type: (...) -> List[_ResultProxyType]
"""Fetch the users who should be synced
:param session: The SQLAlchemy session to use
:param str required_property: the property required to export users
:returns: An iterable of `(User, should_be_blocked)` ResultProxies
having the property `required_property' and a unix_account.
"""
if required_property:
no_unix_account_q = User.q.join(User.current_properties)\
.filter(CurrentProperty.property_name == required_property,
User.unix_account == None)
else:
no_unix_account_q = User.q.filter(User.unix_account == None)
count_exportable_but_no_account = no_unix_account_q.count()
if required_property:
logger.warning("%s users have the '%s' property but not a unix_account",
count_exportable_but_no_account, required_property)
else:
logger.warning("%s users applicable to exporting don't have a unix_account",
count_exportable_but_no_account)
# used for second join against CurrentProperty
not_blocked_property = CurrentProperty.__table__.alias('ldap_login_enabled')
return (
# Grab all users with the required property
User.q
.options(joinedload(User.unix_account))
.join(User.current_properties)
.filter(CurrentProperty.property_name == required_property,
User.unix_account != None)
# additional info:
# absence of `ldap_login_enabled` property → should_be_blocked
.add_column(not_blocked_property.c.property_name.is_(None)
.label('should_be_blocked'))
.outerjoin(
not_blocked_property,
and_(User.id == foreign(not_blocked_property.c.user_id),
~not_blocked_property.c.denied,
not_blocked_property.c.property_name == 'ldap_login_enabled')
).all()
)
def __declare_last__(cls):
from .invoice import Job
owner_type = PersonType.get(cls, None)
if owner_type:
cls.jobs = relation('Job',
primaryjoin=and_(
cls.guid == foreign(Job.owner_guid),
owner_type == Job.owner_type,
),
cascade='all, delete-orphan',
collection_class=CallableList,
)
@event.listens_for(cls.jobs, "append")
def add(target, value, initiator):
value.owner_type = owner_type
value.owner_guid = target.guid
value._assign_id()
class TigerTract(Base):
__tablename__ = 'censustractstiger2010'
ogc_fid = Column(Integer, primary_key=True)
geoid = Column('geoid10', String)
geom = GeometryColumn('GEOMETRY', Geometry(2))
def as_mapping(self):
return mapping(shapely.wkt.loads(db_session.scalar(self.geom.wkt)))
def as_geojson_dict(self):
return {
"type": "Feature",
"geometry": self.as_mapping(),
"properties": {
"geoid": self.geoid,
"total": self.p12_data.total,
"kids": self.p12_data.all_kids,
"younger_kids": self.p12_data.younger_kids,
"older_kids": self.p12_data.older_kids,
"pct_older_kids": self.p12_data.pct_older_kids,
}
}
def as_geojson(self):
return json.dumps(self.as_geojson_dict())
TigerTract.p12_data = relationship(P12Data,
uselist=False,
primaryjoin=P12Data.geoid==foreign(TigerTract.geoid))
def join_function():
return and_(
foreign(CustomAttributeValue.attributable_id) == cls.id,
foreign(CustomAttributeValue.attributable_type) == cls.__name__)
def join_function():
"""Object and CAD join function."""
definition_id = foreign(CustomAttributeDefinition.definition_id)
definition_type = foreign(CustomAttributeDefinition.definition_type)
return and_(or_(definition_id == cls.id, definition_id.is_(None)),
definition_type == cls._inflector.table_singular)
def join_function():
"""Join condition used for deletion"""
definition_id = foreign(CustomAttributeDefinition.definition_id)
definition_type = foreign(CustomAttributeDefinition.definition_type)
return and_(definition_id == cls.id,
definition_type == cls._inflector.table_singular)
def join_function():
"""Function to join CAV to its latest revision."""
resource_id = foreign(Revision.resource_id)
resource_type = foreign(Revision.resource_type)
return and_(resource_id == cls.id,
resource_type == "CustomAttributeValue")
if session:
session.commit()
mapper(SubNode, subnode_table,
properties=dict(_state=subnode_table.c.state))
mapper(Node, node_table,
properties=dict(
_state=node_table.c.state,
subnodes=relationship(
SubNode,
cascade='all, delete-orphan',
uselist=True,
primaryjoin=foreign(subnode_table.c.node_id) == node_table.c.id,
backref='node')))
mapper(SnapshotImage, snapshot_image_table,
properties=dict(_state=snapshot_image_table.c.state))
mapper(DibImage, dib_image_table,
properties=dict(_state=dib_image_table.c.state))
class NodeDatabase(object):
def __init__(self, dburi):
engine_kwargs = dict(echo=False, pool_recycle=3600)
if 'sqlite:' not in dburi:
engine_kwargs['max_overflow'] = -1
Partitioned,
j,
non_primary=True,
properties={
# note we need to disambiguate columns here - the join()
# will provide them as j.c.<tablename>_<colname> for access,
# but they retain their real names in the mapping
"other_id": [j.c.partitioned_other_id, j.c.second_other_id]
},
)
First.partitioned = relationship(
partitioned_second,
primaryjoin=and_(
First.partition_key == partitioned_second.c.partition_key,
First.first_id == foreign(partitioned_second.c.first_id),
),
innerjoin=True,
)
# when using any database other than SQLite, we will get a nested
# join, e.g. "first JOIN (partitioned JOIN second ON ..) ON ..".
# On SQLite, SQLAlchemy needs to render a full subquery.
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
s = Session(e)
s.add_all(
[
First(first_id=1, partition_key="p1"),
First(first_id=2, partition_key="p1"),
Column('visibility', types.Unicode, default=u'visible'),
Column('abuse_status',
types.Integer,
default=AbuseStatus.unmoderated.value),
)
meta.mapper(
Issue,
issue_table,
properties={
'user': relation(
model.User,
backref=backref('issues',
cascade='all, delete-orphan',
single_parent=True),
primaryjoin=foreign(issue_table.c.user_id) == remote(User.id),
uselist=False
),
'assignee': relation(
model.User,
backref=backref('resolved_issues',
cascade='all'),
primaryjoin=foreign(issue_table.c.assignee_id) == remote(User.id)
),
'dataset': relation(
model.Package,
backref=backref('issues',
cascade='all, delete-orphan',
single_parent=True),
primaryjoin=foreign(issue_table.c.dataset_id) == remote(Package.id),
uselist=False
def join_function(): """Object and CAD join function.""" object_id = foreign(Attributes.object_id) object_type = foreign(Attributes.object_type) return and_(object_id == cls.id, object_type == unicode(cls.__name__))
Chat.request = relationship(Request)
Message.chat = relationship(Chat, backref="messages")
Message.user = relationship(User, backref="messages")
Message.chat_user = relationship(
ChatUser,
primaryjoin=and_(Message.chat_id == ChatUser.chat_id, Message.user_id == ChatUser.user_id),
foreign_keys=[Message.chat_id, Message.user_id],
)
ChatUser.chat = relationship(Chat, backref="users")
ChatUser.user = relationship(User, backref="chats")
ChatUser.export = relationship(
ChatExport,
primaryjoin=and_(
ChatUser.chat_id == foreign(ChatExport.chat_id),
ChatUser.user_id == foreign(ChatExport.user_id),
),
uselist=False,
viewonly=True,
)
ChatExport.chat = relationship(Chat)
ChatExport.user = relationship(User)
PromptReport.duplicate_of = relationship(PromptReport, backref="duplicates", remote_side=PromptReport.id)
PromptReport.reporting_user = relationship(User, backref="reports_sent", primaryjoin=PromptReport.reporting_user_id == User.id)
PromptReport.reported_user = relationship(User, backref="reports_recieved", primaryjoin=PromptReport.reported_user_id == User.id)
Request.user = relationship(User, backref="requests")
Request.tags = relationship(Tag, secondary=RequestTag.__table__, order_by=(Tag.type, Tag.name), backref="requests")
