class Collection(Base):
__tablename__ = 'collection'
__table_args__ = {
'mysql_charset': 'utf8'
}
id = Column(BIGINT, primary_key=True)
type = Column(VARCHAR(2), nullable=False)
title = Column(VARCHAR(100), nullable=False)
cover = Column(VARCHAR(300))
content = Column(TEXT)
abstract = Column(VARCHAR(150))
pack_id = Column(BIGINT, ForeignKey('collect_pack.id'))
cisn = Column(VARCHAR(200))
#CopyRight Identify Serial Number(cisn)
create_by = Column(BIGINT, ForeignKey('user.id'))
vote_count = Column(INTEGER, default=0)
bro_count = Column(INTEGER, default=0)
share_count = Column(INTEGER, default=0)
visit_count = Column(INTEGER, default=0)
focus_count = Column(INTEGER, default=0)
comment_count = Column(INTEGER, default=0)
state = Column(INTEGER, default=1)
#1 valid 0 invalid
create_time = Column(DATETIME, server_default=func.now())
last_modify = Column(DATETIME, server_default=func.now())
reserved = Column(VARCHAR(100))
images = relationship('Imagelib', backref='relevance', lazy='dynamic')
favorites = relationship('Favorite', backref='son', lazy='dynamic')
comments = relationship('Comment', backref='target', lazy='dynamic')
class Item(Base):
__tablename__ = 'item'
# convert data into a format that allows it to
# be shared later as json
def serialize(self):
return {
'id': self.id,
'name': self.name,
'description': self.description,
'category': self.category.name,
'user': self.user.name,
}
id = Column(Integer, primary_key=True)
name = Column(String(200), nullable=False)
description = Column(String(800))
category_id = Column(Integer, ForeignKey('category.id'), nullable=False)
category = relationship(Category)
user_id = Column(Integer, ForeignKey('user.id'), nullable=False)
user = relationship(User)
created = Column(DateTime, default=func.now(), nullable=False)
updated = Column(
DateTime,
default=func.now(),
onupdate=func.now(),
nullable=False)
class Distance(SurrogatePK, Model):
"""Distance from a pin to an apartment."""
__tablename__ = 'distance'
apartment_id = reference_column('apartment')
apartment = relationship('Apartment')
pin_id = reference_column('pin')
pin = relationship('Pin')
meters = Column(db.Integer(), nullable=False)
minutes = Column(db.Integer(), nullable=False)
# Google Matrix JSON
json = db.Column(postgresql.JSONB(none_as_null=True), nullable=False)
updated_at = Column(db.DateTime,
nullable=False,
onupdate=func.now(),
default=func.now())
def __repr__(self):
"""Represent the object as a unique string."""
return "<Distance('{}' to '{}', {}m / {} min.)>".format(
self.apartment.address, self.pin.address, self.meters,
self.minutes)
class AuthTokens(db.Model, HelperModel):
"""Represents the authtokens table."""
__tablename__ = 'authtokens'
id = Column(Integer, primary_key=True, autoincrement=True)
token = Column(String, nullable=False, default=uuid4)
refresh_token = Column(String, nullable=False, default=uuid4)
expiry_date = Column(DateTime, nullable=True, default=func.now())
refresh_expiry_time = Column(DateTime, nullable=True, default=func.now())
@declared_attr
def user_id(cls):
return Column(Integer, ForeignKey('users.id'), nullable=True)
def __repr__(self):
return f'<AuthToken: {self.user_id}'
def to_dict(self):
data = self.__dict__
if data.get('expiry_date'):
data['expiry_date'] = data.get('expiry_date').strftime(
'%d/%m/%Y, %H:%M:%S')
if data.get('refresh_expiry_time'):
data['refresh_expiry_time'] = (
data.get('refresh_expiry_time').strftime('%d/%m/%Y, %H:%M:%S'))
if '_sa_instance_state' in data:
del data['_sa_instance_state']
return data
class AmoAccount(Base):
__tablename__ = "amo"
id = Column(Integer, primary_key=True)
email_id = Column(UUID(as_uuid=True),
ForeignKey(Email.email_id),
unique=True,
nullable=False)
add_on_ids = Column(String(500))
display_name = Column(String(255))
email_opt_in = Column(Boolean)
language = Column(String(5))
last_login = Column(Date)
location = Column(String(255))
profile_url = Column(String(40))
user = Column(Boolean)
user_id = Column(String(40), index=True)
username = Column(String(100))
create_timestamp = Column(DateTime(timezone=True),
nullable=False,
server_default=func.now())
update_timestamp = Column(DateTime(timezone=True),
nullable=False,
onupdate=func.now(),
default=func.now())
email = relationship("Email", back_populates="amo", uselist=False)
class Newsletter(Base):
__tablename__ = "newsletters"
id = Column(Integer, primary_key=True)
email_id = Column(UUID(as_uuid=True),
ForeignKey(Email.email_id),
nullable=False)
name = Column(String(255), nullable=False)
subscribed = Column(Boolean)
format = Column(String(1))
lang = Column(String(5))
source = Column(Text)
unsub_reason = Column(Text)
create_timestamp = Column(DateTime(timezone=True),
nullable=False,
server_default=func.now())
update_timestamp = Column(DateTime(timezone=True),
nullable=False,
onupdate=func.now(),
default=func.now())
email = relationship("Email", back_populates="newsletters", uselist=False)
__table_args__ = (UniqueConstraint("email_id",
"name",
name="uix_email_name"), )
class Mailinglist(Base):
"""
Class containing mailing list subscriptions.
Based upon Sympa mailing list server https://www.sympa.org/
"""
__tablename__ = 'subscriber_table'
__table_args__ = {"schema": sympa_schema}
listname = Column('list_subscriber', String(50), primary_key=True)
email = Column('user_subscriber', String(200), primary_key=True)
user_id = Column(Integer,
ForeignKey(users_schema + '.user.id'),
nullable=False)
user = relationship(User, primaryjoin=user_id == User.id)
# additional fields, used only by Sympa
date_subscriber = Column(DateTime,
default=func.now(),
server_default=func.now(),
nullable=False)
update_subscriber = Column(DateTime)
visibility_subscriber = Column(String(20))
reception_subscriber = Column(String(20))
bounce_subscriber = Column(String(35))
bounce_score_subscriber = Column(Integer)
comment_subscriber = Column(String(150))
subscribed_subscriber = Column(Integer)
included_subscriber = Column(Integer)
include_sources_subscriber = Column(String(50))
class Visit(db.Model):
__tablename__ = 'visits'
id = db.Column(db.BigInteger, primary_key=True)
baseURI = db.Column(db.String())
URL = db.Column(db.String())
domain = db.Column(db.String())
referrer = db.Column(db.String())
navigation_start = db.Column(db.DateTime())
user_agent = db.Column(db.String())
screen_height = db.Column(db.Integer)
screen_width = db.Column(db.Integer)
initial_window_height = db.Column(db.Integer)
initial_window_width = db.Column(db.Integer)
events = db.relationship('Event', backref='visit', lazy='dynamic')
inserted_at = db.Column(db.DateTime(timezone=True),
server_default=func.now())
updated_at = db.Column(db.DateTime(timezone=True), onupdate=func.now())
def __repr__(self):
return '<Visit #{}>'.format(self.id)
class StripeCustomer(StripeBase):
__tablename__ = "stripe_customer"
stripe_id = Column(String(255), nullable=False, primary_key=True)
fxa_id = Column(String(255), nullable=False, unique=True, index=True)
default_source_id = Column(String(255), nullable=True)
invoice_settings_default_payment_method_id = Column(String(255),
nullable=True)
stripe_created = Column(DateTime(timezone=True), nullable=False)
deleted = Column(Boolean, nullable=False, default=False)
create_timestamp = Column(DateTime(timezone=True),
nullable=False,
server_default=func.now())
update_timestamp = Column(DateTime(timezone=True),
nullable=False,
onupdate=func.now(),
default=func.now())
email = relationship(
"Email",
uselist=False,
back_populates="stripe_customer",
primaryjoin=
"remote(FirefoxAccount.fxa_id)==foreign(StripeCustomer.fxa_id)",
secondaryjoin=
"remote(Email.email_id)==foreign(FirefoxAccount.email_id)",
secondary=
"join(FirefoxAccount, StripeCustomer, FirefoxAccount.fxa_id == StripeCustomer.fxa_id)",
)
fxa = relationship(
"FirefoxAccount",
uselist=False,
viewonly=True,
primaryjoin=(
"remote(FirefoxAccount.fxa_id)==foreign(StripeCustomer.fxa_id)"),
)
invoices = relationship(
"StripeInvoice",
uselist=True,
viewonly=True,
primaryjoin=("foreign(StripeCustomer.stripe_id) =="
" remote(StripeInvoice.stripe_customer_id)"),
)
subscriptions = relationship(
"StripeSubscription",
back_populates="customer",
uselist=True,
primaryjoin=("foreign(StripeCustomer.stripe_id) =="
" remote(StripeSubscription.stripe_customer_id)"),
)
def get_email_id(self) -> Optional[PythonUUID]:
if self.fxa:
return cast(PythonUUID, self.fxa.email.email_id)
return None
class AddressTag(Base):
__tablename__ = 'address_tag'
id = Column(Integer, primary_key=True)
user_id = Column(Integer)
tag = Column(String(250))
address = Column(String(250), index=True)
source = Column(String(250), index=True)
link = Column(String(250))
verified = Column(Boolean)
created_at = Column(DateTime, default=func.now())
updated_at = Column(DateTime, default=func.now(), onupdate=func.now())
class Plasmid(Base):
__tablename__ = "plasmid"
id = Column(Integer, primary_key=True)
name = Column(String)
desc = Column(TEXT)
gbk = Column(TEXT)
stock_place = Column(String)
registered = Column(DateTime, default=func.now())
updated = Column(DateTime, default=func.now())
user_id = Column(Integer, ForeignKey("user.id"))
tag_id = Column(Integer, ForeignKey("tag.id"))
class Users(db.Model, HelperModel):
"""Represents the user table."""
__tablename__ = 'users'
id = Column(Integer, primary_key=True, autoincrement=True)
username = Column(String(50), unique=True, nullable=False)
email = Column(String(150), unique=True, nullable=True)
password = Column(String(255), nullable=False)
created_on = Column(DateTime, default=func.now())
updated_on = Column(DateTime,
default=func.now(),
server_onupdate=func.now())
REQUIRED_FIELDS = {'username', 'password'}
@declared_attr
def authtoken(cls):
return relationship('AuthTokens', lazy=True)
def init(self, username, password):
self.username = username
self.password = password
def is_correct_password(self, encrypted_str):
string_ = base64.b64decode(encrypted_str).decode('utf-8')
return hashlib.sha256(string_.encode()).hexdigest() == self.password
def __repr__(self):
return f'<User: {self.username} - {self.email}>'
def to_dict(self):
data = self.__dict__
if data.get('created_on'):
data['created_on'] = data.get('created_on').strftime(
"%d/%m/%Y, %H:%M:%S")
if data.get('updated_on'):
data['updated_on'] = data.get('updated_on').strftime(
"%d/%m/%Y, %H:%M:%S")
if '_sa_instance_state' in data:
del data['_sa_instance_state']
return data
def save_instance(self):
try:
self.password = encrypt_password(self.password)
db.session.add(self)
db.session.commit()
return
except exc.SQLAlchemyError as e:
return e
class XmlModel(Base, BaseMixin):
name = Column(Text, nullable=False)
source = Column(Text, nullable=True)
module = Column(Text, nullable=True)
uri = Column(Text, nullable=True)
sender = Column(Text, nullable=True)
created_at = Column(DateTime(), default=func.now(), nullable=False)
updated_at = Column(DateTime(),
nullable=False,
default=func.now(),
onupdate=func.now())
nodes = relationship('XmlNodeInstModel', lazy='dynamic')
__tablename__ = 'xml'
class Apartment(SurrogatePK, Model):
"""A home (apartment, flat, etc.)."""
__tablename__ = 'apartment'
url = Column(db.String(), unique=True, nullable=False)
active = Column(db.Boolean, default=True, nullable=False)
title = Column(db.String())
address = Column(db.String())
neighborhood = Column(db.String())
rooms = Column(db.Numeric(3, 1))
size = Column(db.Numeric(7, 2))
# Prices
cold_rent_price = Column(db.Numeric(10, 2))
warm_rent_price = Column(db.Numeric(10, 2))
additional_price = Column(db.Numeric(10, 2))
heating_price = Column(db.Numeric(10, 2))
opinion_id = reference_column('opinion', True)
opinion = relationship('Opinion')
description = Column(db.String())
equipment = Column(db.String())
location = Column(db.String())
other = Column(db.String())
availability = Column(db.Date)
comments = Column(db.String())
json = db.Column(postgresql.JSONB(none_as_null=True), nullable=False)
errors = db.Column(postgresql.JSONB(none_as_null=True))
created_at = Column(db.DateTime, default=func.now())
updated_at = Column(db.DateTime, onupdate=func.now(), default=func.now())
distances = relationship('Distance')
def __repr__(self):
"""Represent the object as a unique string."""
return '<Apartment({}: {} {})>'.format(
self.id, self.url, self.opinion.title if self.opinion else '')
@classmethod
def get_or_create(cls, url: str):
"""Get an apartment by its URL (which should be unique).
:return: An existing apartment or a new empty instance.
:rtype: Apartment
"""
return cls.query.filter_by(url=url).first() or Apartment()
class User(Base):
__tablename__ = 'users'
u_id = Column(Integer, primary_key=True)
u_name = Column(String(24), unique=True, nullable=False)
u_password = Column(String(128), nullable=False, default='')
u_nickname = Column(String(32))
u_sex = Column(CHAR, default='')
u_phone = Column(String(32), unique=True, nullable=False)
u_birthday = Column(DateTime, server_default=func.now())
u_create_at = Column(DateTime, server_default=func.now())
u_introduce = Column(Text)
u_head_img = Column(String(128))
u_alive = Column(Integer, default=1) # 1为存活（可发贴，评论），0为死亡（禁言）
u_permission = Column(Integer, default=0) # 1为管理员，0为普通用户
def update_feed_images_upload(images, images_in, user_id):
"""When uploading a set of images, create a feed entry for the document
the images are linked to.
"""
if not images or not images_in:
return
assert len(images) == len(images_in)
# get the document that the images were uploaded to
document_id, document_type = get_linked_document(images_in)
if not document_id or not document_type:
return
image1_id, image2_id, image3_id, more_images = get_images(
images, images_in, document_id, document_type)
if not image1_id:
return
# load the feed entry for the images
change = get_existing_change(document_id)
if not change:
log.warn('no feed change for document {}'.format(document_id))
return
if change.user_id == user_id:
# if the same user, only update time and change_type.
# this avoids that multiple entries are shown in the feed for the same
# document.
change.change_type = 'updated'
change.time = func.now()
else:
# if different user: first try to get an existing feed entry of the
# user for the document
change_by_user = get_existing_change_for_user(document_id, user_id)
if change_by_user:
change = change_by_user
change.change_type = 'added_photos'
change.time = func.now()
else:
change = change.copy()
change.change_type = 'added_photos'
change.user_id = user_id
change.user_ids = list(set(change.user_ids).union([user_id]))
_update_images(change, image1_id, image2_id, image3_id, more_images)
DBSession.add(change)
DBSession.flush()
class Event(db.Model):
__tablename__ = 'events'
visit_id = db.Column(db.Integer,
db.ForeignKey('visits.id'),
nullable=False,
primary_key=True)
timestamp = db.Column(db.Float, primary_key=True)
name = db.Column(db.String(), primary_key=True)
payload = db.Column(JSON)
inserted_at = db.Column(db.DateTime(timezone=True),
server_default=func.now())
updated_at = db.Column(db.DateTime(timezone=True), onupdate=func.now())
def transfer_associations(source_document_id, target_document_id):
# get the document ids the target is already associated with
target_child_ids_result = DBSession. \
query(Association.child_document_id). \
filter(Association.parent_document_id == target_document_id). \
all()
target_child_ids = [child_id for (child_id, ) in target_child_ids_result]
target_parent_ids_result = DBSession. \
query(Association.parent_document_id). \
filter(Association.child_document_id == target_document_id). \
all()
target_parent_ids = [
parent_id for (parent_id, ) in target_parent_ids_result
]
# move the current associations (only if the target document does not
# already have an association with the same document)
DBSession.execute(Association.__table__.update().where(
_and_in(
Association.parent_document_id == source_document_id,
Association.child_document_id,
target_child_ids)).values(parent_document_id=target_document_id))
DBSession.execute(Association.__table__.update().where(
_and_in(
Association.child_document_id == source_document_id,
Association.parent_document_id,
target_parent_ids)).values(child_document_id=target_document_id))
# remove remaining associations
DBSession.execute(Association.__table__.delete().where(
or_(Association.child_document_id == source_document_id,
Association.parent_document_id == source_document_id)))
# transfer the association log entries
DBSession.execute(AssociationLog.__table__.update().where(
_and_in(AssociationLog.parent_document_id == source_document_id,
AssociationLog.child_document_id, target_child_ids)).values(
parent_document_id=target_document_id,
written_at=func.now()))
DBSession.execute(AssociationLog.__table__.update().where(
_and_in(AssociationLog.child_document_id == source_document_id,
AssociationLog.parent_document_id, target_parent_ids)).values(
child_document_id=target_document_id,
written_at=func.now()))
DBSession.execute(AssociationLog.__table__.delete().where(
or_(AssociationLog.child_document_id == source_document_id,
AssociationLog.parent_document_id == source_document_id)))
class NodeActivity(Base):
__tablename__ = 'node_activity'
id = Column(Integer, primary_key=True, index=True)
address = Column(String(250), index=True)
status = Column(String(250), index=True)
pushed_from = Column(String(25), index=True)
created_at = Column(DateTime, default=func.now(), index=True)
class Verification(Base): # 短信验证码及生成用户auth_key时间
__tablename__ = 'Verification'
Vphone = Column(CHAR(11), primary_key=True) #
Vcode = Column(CHAR(6), nullable=False)
VT = Column(DateTime(timezone=True),
default=func.now()) # 待测试是插入数据的时间还是最后一次更新该表的时间 （测试结果为第一次插入时间）
class Activity(Base): #活动表
__tablename__ = 'Activity'
ACid = Column(Integer, nullable=False, primary_key=True)
ACsponsorid = Column(Integer, ForeignKey('User.Uid',
onupdate='CASCADE')) #活动发起者
AClocation = Column(VARCHAR(128), nullable=False)
ACtitle = Column(VARCHAR(24), nullable=False) # 活动的名称？确认长度
ACtag = Column(VARCHAR(12)) # 活动的标签？确认类型
ACstartT = Column(DateTime, nullable=False)
ACendT = Column(DateTime, nullable=False)
ACjoinT = Column(DateTime) # 活动报名截止时间
ACcontent = Column(VARCHAR(128), nullable=False) # 活动介绍
ACfree = Column(Boolean)
ACprice = Column(VARCHAR(64))
ACclosed = Column(Boolean, default=1, nullable=False) # 活动是否已经结束
ACcreateT = Column(DateTime(timezone=True), default=func.now())
ACcommentnumber = Column(Integer, default=0, nullable=False)
ACmaxp = Column(Integer, nullable=False, default=0)
ACminp = Column(Integer, nullable=False, default=100)
ACscore = Column(Integer, nullable=False, default=0)
AClikenumber = Column(Integer, nullable=False, default=0)
ACvalid = Column(Boolean, nullable=False, default=1) # 活动是否已经删除
ACregistN = Column(Integer, nullable=False, default=0)
ACstatus = Column(Integer, nullable=False, default=0)
class Image(Base):
__tablename__ = 'Image'
IMid = Column(Integer, primary_key=True, nullable=False)
IMvalid = Column(Boolean, default=1)
IMT = Column(DateTime(timezone=True), default=func.now())
IMname = Column(VARCHAR(128), nullable=False)
class Appointment(Base): #摄影师-模特约拍
__tablename__ = 'Appointment'
APid = Column(Integer, primary_key=True, nullable=False)
APsponsorid = Column(Integer,
ForeignKey('User.Uid', ondelete='CASCADE'),
nullable=False) # 发起者
# APtitle=Column(VARCHAR(24),nullable=False)
APlocation = Column(VARCHAR(128), nullable=False, default='南京')
APtag = Column(VARCHAR(12)) # 约拍标签？确认长度
# APstartT = Column(DateTime, nullable=False, default='0000-00-00 00:00:00 ')
# APendT = Column(DateTime, nullable=False, default='0000-00-00 00:00:00 ')
# APjoinT=Column(DateTime, nullable=False, default='0000-00-00 00:00:00 ')
APtime = Column(VARCHAR(128), nullable=False, default='') # 约拍的时间描述
APcontent = Column(VARCHAR(128), nullable=False, default='') # 约拍的内容秒速
# APfree = Column(Boolean)
APpricetag = Column(Integer, nullable=False, default=0) # 约拍的价格类型
APprice = Column(VARCHAR(64))
APclosed = Column(Boolean, default=0)
APcreateT = Column(DateTime(timezone=True), default=func.now())
APtype = Column(Boolean, nullable=False,
default=0) # 约拍类型，模特约摄影师(1)或摄影师约模特(0)
APaddallowed = Column(Boolean, default=0)
APlikeN = Column(Integer, default=0, nullable=False)
APvalid = Column(Boolean, default=1, nullable=False)
APregistN = Column(Integer, nullable=False, default=0)
APstatus = Column(Integer, nullable=False, default=0)
APgroup = Column(Integer, nullable=False, default=0) # 约拍的分类
class CourseTagEntry(Base): #课程与标签联系的表
__tablename__="CourseTagEntry"
CTEcid = Column(Integer,ForeignKey('Course.Cid', onupdate='CASCADE'),nullable=False,primary_key=True)
CTEtid = Column(Integer,ForeignKey('CourseTag.CTid',onupdate='CASCADE'),nullable=False,primary_key=True)
CTEvalid = Column(Boolean,nullable=False,default= 0)
CTEcreateT = Column(DateTime(timezone=True), default=func.now())
class User(Base):
__tablename__ = "user"
id = Column(Integer, primary_key=True)
username = Column(String)
email = Column(String)
password = Column(TEXT)
picture = Column(LargeBinary)
birthday = Column(DateTime)
permission_level = Column(Integer, default=1)
registered = Column(DateTime, default=func.now())
updated = Column(DateTime, default=func.now())
active = Column(Boolean)
# child relations
primer = relationship("Primer")
plasmid = relationship("Plasmid")
class CourseLike(Base): #课程点赞表
__tablename__ = "CourseLike"
CLcid = Column(Integer,ForeignKey('Course.Cid', onupdate='CASCADE'),primary_key=True)
CLuid = Column(Integer,ForeignKey('User.Uid', onupdate='CASCADE'),primary_key=True)
CLlikeT = Column(DateTime(timezone=True), default=func.now())
CLvalid = Column(Boolean,nullable=False,default=0)
def count_known_users(usernames):
if len(usernames) < 1:
return 0
with DBManager.create_session_scope() as db_session:
# quick EXPLAIN ANALYZE for this query:
#
# pajbot=# EXPLAIN ANALYZE SELECT count(*) AS count_1
# FROM "user"
# WHERE ("user".login IN ('randers', 'lul', 'xd', 'penis', 'asd', 'hello', 'world') OR lower("user".name) IN ('randers', 'lul', 'xd', 'penis', 'asd', 'hello', 'world')) AND "user".last_seen IS NOT NULL AND now() - "user".last_seen <= make_interval(weeks := 2);
# QUERY PLAN
# --------------------------------------------------------------------------------------------------------------------------------------------------------------------
# Aggregate (cost=37.45..37.46 rows=1 width=8) (actual time=0.113..0.113 rows=1 loops=1)
# -> Bitmap Heap Scan on "user" (cost=21.53..37.43 rows=5 width=0) (actual time=0.110..0.110 rows=1 loops=1)
# Recheck Cond: ((login = ANY ('{randers,lul,xd,penis,asd,hello,world}'::text[])) OR (lower(name) = ANY ('{randers,lul,xd,penis,asd,hello,world}'::text[])))
# Filter: ((last_seen IS NOT NULL) AND ((now() - last_seen) <= '14 days'::interval))
# Heap Blocks: exact=6
# -> BitmapOr (cost=21.53..21.53 rows=14 width=0) (actual time=0.101..0.101 rows=0 loops=1)
# -> Bitmap Index Scan on user_login_idx (cost=0.00..10.76 rows=7 width=0) (actual time=0.054..0.054 rows=1 loops=1)
# Index Cond: (login = ANY ('{randers,lul,xd,penis,asd,hello,world}'::text[]))
# -> Bitmap Index Scan on user_lower_idx (cost=0.00..10.76 rows=7 width=0) (actual time=0.046..0.047 rows=6 loops=1)
# Index Cond: (lower(name) = ANY ('{randers,lul,xd,penis,asd,hello,world}'::text[]))
# Planning Time: 0.092 ms
# Execution Time: 0.140 ms
# (12 rows)
return (
db_session.query(User)
.with_entities(count())
.filter(or_(User.login.in_(usernames), func.lower(User.name).in_(usernames)))
.filter(and_(User.last_seen.isnot(None), (func.now() - User.last_seen) <= timedelta(weeks=2)))
.scalar()
)
class UClike(Base):
__tablename__ = 'UClike'
UCLid = Column(Integer, primary_key=True)
UClikeid = Column(Integer, ForeignKey(UserCollection.UCid, onupdate='CASCADE')) #作品集id
UClikeUserid = Column(Integer, ForeignKey(User.Uid, onupdate='CASCADE'))
UCLvalid = Column(Boolean, nullable=False, default=1)
UCLTime = Column(DateTime(timezone=True), default=func.now())
class AssociationLog(Base):
"""Model to log when an association between documents was established or
removed.
"""
__tablename__ = 'association_log'
id = Column(Integer, primary_key=True)
parent_document_id = Column(Integer,
ForeignKey(schema + '.documents.document_id'),
nullable=False,
index=True)
parent_document = relationship(
Document, primaryjoin=parent_document_id == Document.document_id)
parent_document_type = Column(String(1), nullable=False)
child_document_id = Column(Integer,
ForeignKey(schema + '.documents.document_id'),
nullable=False,
index=True)
child_document = relationship(
Document, primaryjoin=child_document_id == Document.document_id)
child_document_type = Column(String(1), nullable=False)
user_id = Column(Integer,
ForeignKey(users_schema + '.user.id'),
nullable=False,
index=True)
user = relationship(User, primaryjoin=user_id == User.id, viewonly=True)
is_creation = Column(Boolean, default=True, nullable=False)
written_at = Column(DateTime(timezone=True),
default=func.now(),
nullable=False,
index=True)
def transfer_tags(source_document_id, target_document_id):
# get the ids of users that have already tagged the target document
target_user_ids_result = DBSession. \
query(DocumentTag.user_id). \
filter(DocumentTag.document_id == target_document_id). \
all()
target_user_ids = [user_id for (user_id, ) in target_user_ids_result]
# move the current tags (only if the target document does not
# already have been tagged by the same user)
DBSession.execute(DocumentTag.__table__.update().where(
_and_in(DocumentTag.document_id == source_document_id,
DocumentTag.user_id,
target_user_ids)).values(document_id=target_document_id))
# remove remaining tags
DBSession.execute(DocumentTag.__table__.delete().where(
DocumentTag.document_id == source_document_id))
# transfer the tag log entries
DBSession.execute(DocumentTagLog.__table__.update().where(
_and_in(DocumentTagLog.document_id == source_document_id,
DocumentTagLog.user_id,
target_user_ids)).values(document_id=target_document_id,
written_at=func.now()))
DBSession.execute(DocumentTagLog.__table__.delete().where(
DocumentTagLog.document_id == source_document_id))
def update_feed_images_upload(images, images_in, user_id):
"""When uploading a set of images, create a feed entry for the document
the images are linked to.
"""
if not images or not images_in:
return
assert len(images) == len(images_in)
# get the document that the images were uploaded to
document_id, document_type = get_linked_document(images_in)
if not document_id or not document_type:
return
image1_id, image2_id, image3_id, more_images = get_images(
images, images_in, document_id, document_type)
if not image1_id:
return
# load the feed entry for the images
change = get_existing_change(document_id)
if not change:
log.warn('no feed change for document {}'.format(document_id))
return
if change.user_id == user_id:
# if the same user, only update time and change_type.
# this avoids that multiple entries are shown in the feed for the same
# document.
change.change_type = 'updated'
change.time = func.now()
else:
# if different user: copy the feed entry
change = change.copy()
change.change_type = 'added_photos'
change.user_id = user_id
change.user_ids = list(set(change.user_ids).union([user_id]))
change.image1_id = image1_id
change.image2_id = image2_id
change.image3_id = image3_id
change.more_images = more_images
DBSession.add(change)
DBSession.flush()
def update_participants_of_outing(outing_id, user_id):
existing_change = get_existing_change(outing_id)
if not existing_change:
log.warn('no feed change for document {}'.format(outing_id))
return
participant_ids = _get_participants_of_outing(outing_id)
if set(existing_change.user_ids) == set(participant_ids):
# participants have not changed, stop
return
existing_change.user_ids = participant_ids
if existing_change.user_id != user_id:
# a different user is doing this change, only set a different user id
# if the user is one of the participants (to ignore moderator edits)
if user_id in participant_ids:
existing_change.user_id = user_id
existing_change.change_type = 'updated'
existing_change.time = func.now()
DBSession.flush()
# now also update the participants of other feed entries of the outing:
# set `user_ids` to the union of the participant ids and the `user_id` of
# the entry
participants_and_editor = text(
'ARRAY(SELECT DISTINCT UNNEST(array_cat('
' ARRAY[guidebook.feed_document_changes.user_id], :participants)) '
'ORDER BY 1)')
DBSession.execute(
DocumentChange.__table__.update().
where(DocumentChange.document_id == outing_id).
where(DocumentChange.change_id != existing_change.change_id).
values(user_ids=participants_and_editor),
{'participants': participant_ids}
)
# Base = declarative_base()
metadata = MetaData()
books_authors = Table(
'books_authors',
metadata,
Column('id', Integer, primary_key=True),
Column('book_id', Integer, ForeignKey('book.id')),
Column('author_id', Integer, ForeignKey('author.id'))
)
topic = Table(
'topic',
metadata,
Column('id', Integer, primary_key=True),
Column('created', DateTime, default=func.now()),
Column('modified', DateTime, onupdate=func.now()),
Column('name', String(100), nullable=False, unique=True, index=True)
)
author = Table(
'author',
metadata,
Column('id', Integer, primary_key=True),
Column('created', DateTime, default=func.now()),
Column('modified', DateTime, onupdate=func.now()),
Column('first_name', String(40), index=True, nullable=False),
Column('last_name', String(40), index=True, nullable=False)
)
book = Table(
def get_status(session):
return session.query(
ESSyncStatus.last_update, func.now().label('date_now')). \
filter(ESSyncStatus.id == 1).one()
