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()