class BaseModel(Base): """ ベースモデル """ __abstract__ = True id = Column( INTEGER, primary_key=True, autoincrement=True, ) created_at = Column( 'created_at', TIMESTAMP(timezone=True), server_default=current_timestamp(), nullable=False, comment='登録日時', ) updated_at = Column( 'updated_at', TIMESTAMP(timezone=True), onupdate=current_timestamp(), comment='最終更新日時', ) @declared_attr def __mapper_args__(cls): """ デフォルトのオーダリングは主キーの昇順 降順にしたい場合 from sqlalchemy import desc # return {'order_by': desc('id')} """ return {'order_by': 'id'}
def make_changes_table(table, metadata): """Create an object log table for an object. """ table_name = table.name entity_name = singular(table_name) changes_name = "%s_changes" % (entity_name) fk_id = "%s_id" % (entity_name) changes_table = rdb.Table(changes_name, metadata, rdb.Column("change_id", rdb.Integer, primary_key=True), rdb.Column("content_id", rdb.Integer, rdb.ForeignKey(table.c[fk_id])), rdb.Column("action", rdb.Unicode(16)), # audit date, exclusively managed by the system rdb.Column("date_audit", rdb.DateTime(timezone=False), default=functions.current_timestamp(), nullable=False ), # user-modifiable effective date, defaults to same value as audit date; # this is the date to be used for all intents and purposes other than # for data auditing rdb.Column("date_active", rdb.DateTime(timezone=False), default=functions.current_timestamp(), nullable=False ), rdb.Column("description", rdb.UnicodeText), rdb.Column("notes", rdb.UnicodeText), rdb.Column("user_id", rdb.Integer, rdb.ForeignKey("users.user_id")), useexisting=True # !+ZCA_TESTS(mr, jul-2011) tests break without this ) # create index for changes table index_name = "%s_cid_idx" % (entity_name) changes_table_index = rdb.Index(index_name, changes_table.c["content_id"]) return changes_table
def authenticate(self, identifier, attributes, arguments): ''' @see: IAuthenticationSupport.authenticate ''' assert isinstance(identifier, str), 'Invalid identifier %s' % identifier assert isinstance(attributes, dict), 'Invalid attributes %s' % attributes assert isinstance(arguments, dict), 'Invalid arguments %s' % arguments olderThan = self.session().query(current_timestamp()).scalar() olderThan -= self.sessionTimeOut sql = self.session().query(LoginMapped) sql = sql.filter(LoginMapped.Session == identifier) sql = sql.filter(LoginMapped.AccessedOn > olderThan) try: login = sql.one() except NoResultFound: return False assert isinstance(login, LoginMapped), 'Invalid login %s' % login login.AccessedOn = current_timestamp() self.session().flush((login,)) self.session().expunge(login) commitNow() # We need to fore the commit because if there is an exception while processing the request we need to make # sure that the last access has been updated. for authType in arguments: assert isinstance(authType, Type), 'Invalid type %s' % authType if authType == typeFor(User.Id): arguments[authType] = login.User else: raise DevelError('Invalid authenticated type %s' % authType) return True
def make_changes_table(table, metadata): """Create an object log table for an object. """ table_name = table.name entity_name = singular(table_name) changes_name = "%s_changes" % (entity_name) fk_id = "%s_id" % (entity_name) changes_table = rdb.Table(changes_name, metadata, rdb.Column("change_id", rdb.Integer, primary_key=True), # the item_id of the "owning" item being logged !+HEAD_DOCUMENT_ITEM rdb.Column("content_id", rdb.Integer, rdb.ForeignKey(table.c[fk_id]), nullable=False, index=True ), rdb.Column("action", rdb.Unicode(16)), # audit date, exclusively managed by the system rdb.Column("date_audit", rdb.DateTime(timezone=False), default=functions.current_timestamp(), nullable=False ), # user-modifiable effective date, defaults to same value as audit date; # this is the date to be used for all intents and purposes other than # for data auditing rdb.Column("date_active", rdb.DateTime(timezone=False), default=functions.current_timestamp(), nullable=False ), rdb.Column("description", rdb.UnicodeText), rdb.Column("notes", rdb.UnicodeText), rdb.Column("user_id", rdb.Integer, rdb.ForeignKey("users.user_id")), #!+SA0.7 rdb.Index("%s_changes_cid_idx" % (entity_name), "content_id"), useexisting=False ) return changes_table
def authenticate(self, session): ''' @see: IAuthenticationService.authenticate ''' olderThan = self.session().query(current_timestamp()).scalar() olderThan -= self._sessionTimeOut sql = self.session().query(LoginMapped) sql = sql.filter(LoginMapped.Session == session) sql = sql.filter(LoginMapped.AccessedOn > olderThan) try: login = sql.one() except NoResultFound: raise InputError(Ref(_('Invalid session'), ref=Login.Session)) assert isinstance(login, LoginMapped), 'Invalid login %s' % login login.AccessedOn = current_timestamp() self.session().flush((login,)) self.session().expunge(login) commitNow() # We need to fore the commit because if there is an exception while processing the request we need to make # sure that the last access has been updated. userId = str(login.User) rights = (right.Name for right in self.userRbacService.getRights(login.User)) accesses = self.aclAccessService.accessFor(self.aclAccessService.rightsFor(rights)) allowed = [] for access in accesses: assert isinstance(access, AclAccess), 'Invalid access %s' % access for propertyType, mark in access.markers.items(): assert isinstance(propertyType, TypeProperty), 'Invalid property type %s' % propertyType assert isinstance(propertyType.parent, TypeModel) if propertyType.parent.clazz == User or issubclass(propertyType.parent.clazz, User): for k in range(len(access.Filter)): access.Filter[k] = access.Filter[k].replace(mark, userId) allowed.append(access) return allowed
class Unimac(BaseModel): __tablename__ = 'unimac' id = Column(Integer, primary_key=True, nullable=False) mac = Column(CHAR(20), nullable=False) manu = Column(CHAR(20), nullable=False) station = Column(CHAR(20), nullable=False) start_time = Column(DateTime, index=True, default=func.current_timestamp(), nullable=False) end_time = Column(DateTime, index=True, default=func.current_timestamp(), nullable=False) mid_time = Column(TIMESTAMP, default=func.current_timestamp(), nullable=False) flag = Column(UnicodeText) ap = Column(UnicodeText) ap_no_empty = Column(UnicodeText) def __repr__(self): return '<Unimac id : %r, mac : %r, start_time: %r, end_time: %r>' % ( self.id, self.mac, self.start_time, self.end_time) def get_eval(self, attr, default=None): if hasattr(self, attr) and getattr(self, attr): return eval(getattr(self, attr)) else: return default
def make_changes_table(table, metadata): """Create an object log table for an object. """ table_name = table.name entity_name = table_name.endswith("s") and table_name[:-1] or table_name changes_name = "%s_changes" % (entity_name) fk_id = "%s_id" % (entity_name) changes_table = rdb.Table( changes_name, metadata, rdb.Column("change_id", rdb.Integer, primary_key=True), rdb.Column("content_id", rdb.Integer, rdb.ForeignKey(table.c[fk_id])), rdb.Column("action", rdb.Unicode(16)), # audit date, exclusively managed by the system rdb.Column("date_audit", rdb.DateTime(timezone=False), default=functions.current_timestamp(), nullable=False), # user-modifiable effective date, defaults to same value as audit date; # this is the date to be used for all intents and purposes other than # for data auditing rdb.Column("date_active", rdb.DateTime(timezone=False), default=functions.current_timestamp(), nullable=False), rdb.Column("description", rdb.UnicodeText), rdb.Column("notes", rdb.UnicodeText), rdb.Column("user_id", rdb.Integer, rdb.ForeignKey("users.user_id")), ) return changes_table
def getGateways(self, session): ''' @see: IAuthenticationService.getGateways ''' olderThan = self.session().query(current_timestamp()).scalar() olderThan -= self._sessionTimeOut sql = self.session().query(LoginMapped) sql = sql.filter(LoginMapped.Session == session) sql = sql.filter(LoginMapped.AccessedOn > olderThan) try: login = sql.one() except NoResultFound: return () assert isinstance(login, LoginMapped), 'Invalid login %s' % login login.AccessedOn = current_timestamp() self.session().flush((login,)) self.session().expunge(login) commitNow() # We need to fore the commit because if there is an exception while processing the request we need to make # sure that the last access has been updated. proc = self._processing assert isinstance(proc, Processing), 'Invalid processing %s' % proc solicit = proc.execute(FILL_CLASSES, solicit=proc.ctx.solicit(acl=login.User)).solicit assert isinstance(solicit, Solicit), 'Invalid solicit %s' % solicit return solicit.gateways or ()
class BasicMixin: # noinspection PyMethodParameters @declared_attr def __tablename__(cls): # SomeModel -> some_models return pluralize(underscore(cls.__name__)) id = Column(BigInteger, primary_key=True, autoincrement=True) created_at = Column(DateTime, default=current_timestamp(), nullable=False) updated_at = Column(DateTime, default=current_timestamp(), onupdate=current_timestamp(), nullable=False) def update_from_model(self, other): for attr in dir(self): cls_attr = getattr(type(self), attr, None) if cls_attr is None or not hasattr(cls_attr, "__clause_element__"): continue if isinstance(cls_attr.__clause_element__(), Column): val = getattr(other, attr, None) if val is None: continue if val is Empty: val = None setattr(self, attr, val)
def authenticate(self, identifier, attributes, arguments): ''' @see: IAuthenticationSupport.authenticate ''' assert isinstance(identifier, str), 'Invalid identifier %s' % identifier assert isinstance(attributes, dict), 'Invalid attributes %s' % attributes assert isinstance(arguments, dict), 'Invalid arguments %s' % arguments olderThan = self.session().query(current_timestamp()).scalar() olderThan -= self.sessionTimeOut sql = self.session().query(LoginMapped) sql = sql.filter(LoginMapped.Session == identifier) sql = sql.filter(LoginMapped.AccessedOn > olderThan) try: login = sql.one() except NoResultFound: return False assert isinstance(login, LoginMapped), 'Invalid login %s' % login login.AccessedOn = current_timestamp() self.session().flush((login, )) self.session().expunge(login) commitNow() # We need to fore the commit because if there is an exception while processing the request we need to make # sure that the last access has been updated. for authType in arguments: assert isinstance(authType, Type), 'Invalid type %s' % authType if authType == typeFor(User.Id): arguments[authType] = login.User else: raise DevelError('Invalid authenticated type %s' % authType) return True
def authenticate(self, session): ''' @see: IAuthenticationService.authenticate ''' olderThan = self.session().query(current_timestamp()).scalar() olderThan -= self._sessionTimeOut sql = self.session().query(LoginMapped) sql = sql.filter(LoginMapped.Session == session) sql = sql.filter(LoginMapped.AccessedOn > olderThan) try: login = sql.one() except NoResultFound: raise InputError(Ref(_('Invalid session'), ref=Login.Session)) assert isinstance(login, LoginMapped), 'Invalid login %s' % login login.AccessedOn = current_timestamp() self.session().flush((login,)) self.session().expunge(login) commitNow() # We need to fore the commit because if there is an exception while processing the request we need to make # sure that the last access has been updated. proc = self._processing assert isinstance(proc, Processing), 'Invalid processing %s' % proc solicitation = proc.ctx.solicitation() assert isinstance(solicitation, Solicitation), 'Invalid solicitation %s' % solicitation solicitation.userId = login.User solicitation.types = self.acl.types chain = Chain(proc) chain.process(**proc.fillIn(solicitation=solicitation, reply=proc.ctx.reply())).doAll() reply = chain.arg.reply assert isinstance(reply, Reply), 'Invalid reply %s' % reply if reply.gateways is None: return () return sorted(reply.gateways, key=lambda gateway: (gateway.Pattern, gateway.Methods))
def make_changes_table(table, metadata): """Create an object log table for an object. """ table_name = table.name entity_name = singular(table_name) changes_name = "%s_changes" % (entity_name) fk_id = "%s_id" % (entity_name) changes_table = rdb.Table(changes_name, metadata, rdb.Column("change_id", rdb.Integer, primary_key=True), rdb.Column("content_id", rdb.Integer, rdb.ForeignKey(table.c[fk_id])), rdb.Column("action", rdb.Unicode(16)), # audit date, exclusively managed by the system rdb.Column("date_audit", rdb.DateTime(timezone=False), default=functions.current_timestamp(), nullable=False ), # user-modifiable effective date, defaults to same value as audit date; # this is the date to be used for all intents and purposes other than # for data auditing rdb.Column("date_active", rdb.DateTime(timezone=False), default=functions.current_timestamp(), nullable=False ), rdb.Column("description", rdb.UnicodeText), rdb.Column("notes", rdb.UnicodeText), rdb.Column("user_id", rdb.Integer, rdb.ForeignKey("users.user_id")), useexisting=True # !+ZCA_TESTS(mr, jul-2011) tests break without this ) return changes_table
class SlackUser(BaseObject): __tablename__ = 'slack_user' id = Column(BigInteger, primary_key=True, autoincrement=True) created_at = Column(DateTime, default=current_timestamp(), nullable=False) updated_at = Column(DateTime, default=current_timestamp(), onupdate=current_timestamp(), nullable=False) user = Column(String(100)) other_name = Column(String(100))
class SlackChannel(BaseObject): __tablename__ = 'slack_channels' id = Column(BigInteger, primary_key=True, autoincrement=True) created_at = Column(DateTime, default=current_timestamp(), nullable=False) updated_at = Column(DateTime, default=current_timestamp(), onupdate=current_timestamp(), nullable=False) name = Column(String(200)) token = Column(String(200)) channel = Column(String(100))
class MExternalSystem(Base): __tablename__ = DB_SCHEMA_NAME + '.' + TABLE_NAME id = Column(Integer(13), Sequence(DB_SCHEMA_NAME + '.' + TABLE_NAME + '_id_seq'), primary_key=True) name = Column(String, nullable=False) is_deleted = Column(Boolean, nullable=False, server_default='f') created_at = Column(DateTime, onupdate=current_timestamp(), nullable=False, server_default=current_timestamp()) updetad_at = Column(DateTime, onupdate=current_timestamp(), nullable=False, server_default=current_timestamp()) deleted_at = Column(DateTime, onupdate=current_timestamp(), nullable=False, server_default=current_timestamp()) schema = DB_SCHEMA_NAME
class HideServer(BaseObject): __tablename__ = 'hide_servers' id = Column(BigInteger, primary_key=True, autoincrement=True) created_at = Column(DateTime, default=current_timestamp(), nullable=False) updated_at = Column(DateTime, default=current_timestamp(), onupdate=current_timestamp(), nullable=False) name = Column(Text)
class WatchLog(BaseObject): __tablename__ = 'watch_logs' id = Column(BigInteger, primary_key=True, autoincrement=True) created_at = Column(DateTime, default=current_timestamp(), nullable=False) updated_at = Column(DateTime, default=current_timestamp(), onupdate=current_timestamp(), nullable=False) watch_link_id = Column(BigInteger) message = Column(Text)
class GitHubUser(BaseObject): __tablename__ = 'git_hub_users' id = Column(BigInteger, primary_key=True, autoincrement=True) created_at = Column(DateTime, default=current_timestamp(), nullable=False) updated_at = Column(DateTime, default=current_timestamp(), onupdate=current_timestamp(), nullable=False) login = Column(String(128), nullable=False, unique=True) db_schema = Column(Text)
class GoogleChatWebhook(BaseObject): __tablename__ = 'google_chat_webhook' id = Column(BigInteger, primary_key=True, autoincrement=True) created_at = Column(DateTime, default=current_timestamp(), nullable=False) updated_at = Column(DateTime, default=current_timestamp(), onupdate=current_timestamp(), nullable=False) name = Column(String(200)) url = Column(String(500))
class Mean(Base): __tablename__ = 'means' id = Column(Integer, primary_key=True) text = Column(String(128), nullable=False) bad = Column(Integer, default=0) created_at = Column(DATETIME, server_default=current_timestamp(), nullable=False) updated_at = Column(DATETIME, server_default=current_timestamp(), nullable=False) word_id = Column(Integer, ForeignKey('words.id'), nullable=False)
class FreeeWorkRecord(BaseObject): __tablename__ = 'freee_work_records' id = Column(BigInteger, primary_key=True, autoincrement=True) created_at = Column(DateTime, default=current_timestamp(), nullable=False) updated_at = Column(DateTime, default=current_timestamp(), onupdate=current_timestamp(), nullable=False) emp_id = Column(String(100)) date = Column(String(100)) clock_in_at = Column(String(100)) clock_out_at = Column(String(100))
class BasicMixin: # noinspection PyMethodParameters @declared_attr def __tablename__(cls): # SomeModel -> some_models return pluralize(underscore(cls.__name__)) id = Column(BigInteger, primary_key=True, autoincrement=True) created_at = Column(DateTime, default=current_timestamp(), nullable=False) updated_at = Column(DateTime, default=current_timestamp(), onupdate=current_timestamp(), nullable=False)
def last_update_time() -> sa.Column: """A timestamp column set to CURRENT_TIMESTAMP on update. Return a column containing the time that a record was last updated. :return: a SQLAlchemy Column for a datetime with time zone auto-updating column """ return sa.Column( pg.TIMESTAMP(timezone=True), nullable=False, server_default=current_timestamp(), onupdate=current_timestamp(), )
class User(db.Model): __tablename__ = "users" id = db.Column(db.Integer, primary_key=True, nullable=False) name = db.Column(db.String(100), nullable=False) created_at = db.Column(db.TIMESTAMP, server_default=current_timestamp(), default=current_timestamp(), nullable=False) def to_dict(self): return dict(id=self.id, name=self.name, created_at=self.created_at) def __repr__(self): return f'<User {self.id}:{self.name},{self.created_at}>'
class FreeeCompany(BaseObject): __tablename__ = 'freee_companies' id = Column(BigInteger, primary_key=True, autoincrement=True) created_at = Column(DateTime, default=current_timestamp(), nullable=False) updated_at = Column(DateTime, default=current_timestamp(), onupdate=current_timestamp(), nullable=False) company_id = Column(String(100), unique=True, nullable=False) name = Column(String(200)) freee_employees = relationship('FreeeEmployee', back_populates='freee_company')
class Commit(BaseObject): __tablename__ = 'commits' id = Column(BigInteger, primary_key=True, autoincrement=True) created_at = Column(DateTime, default=current_timestamp(), nullable=False) updated_at = Column(DateTime, default=current_timestamp(), onupdate=current_timestamp(), nullable=False) sha = Column(String(40), unique=True, nullable=False) message = Column(Text) parent_a = Column(String(40)) parent_b = Column(String(40)) production_reported = Column(Integer)
class Word(Base): __tablename__ = 'words' id = Column(Integer, primary_key=True) name = Column(String(32), nullable=False) tweeted_at = Column(DATETIME, server_default=current_timestamp(), nullable=False) created_at = Column(DATETIME, server_default=current_timestamp(), nullable=False) updated_at = Column(DATETIME, server_default=current_timestamp(), nullable=False) means = relationship("Mean", backref="words")
class User(db.Model): __tablename__ = 'users' __table_args__ = (UniqueConstraint('email', name='uq_email'), ) id = db.Column('id', db.Integer, autoincrement=True, primary_key=True) name = db.Column('name', db.String(225), nullable=False) created_at = db.Column('created_at', Timestamp, server_default=current_timestamp(), nullable=False) created_by = db.Column('created_by', db.String(225), nullable=True) updated_at = db.Column('updated_at', Timestamp, server_default=current_timestamp(), nullable=False) updated_by = db.Column('updated_by', db.String(225), nullable=True) email = db.Column('email', db.String(225), nullable=True) def __init__(self, id, name, created_at, created_by, updated_at, updated_by, email): self.id = id self.name = name self.created_at = created_at self.created_by = created_by self.updated_at = updated_at self.updated_by = updated_by self.email = email def __repr__(self): return '<User %r>' % self.name def get_user_list(): # SELECT * FROM users user_list = db.session.query(User).all() if user_list == None: return [] else: return user_list def create_user(user): record = User(name=user['name'], ) # INSERT INTO users(name) VALUES(...) db.session.add(record) db.session.commit() return user def get_user_by_id(id): return db.session.query(User)\ .filter(User.id == id)\ .one()
class Note(db.Model): __tablename__ = 'note' id = db.Column(Integer, primary_key=True) author_id = db.Column(Integer, unique=False, nullable=False) root_note_id = db.Column(Integer, unique=False, nullable=True) created = db.Column(DateTime, unique=False, nullable=False, server_default=current_timestamp()) updated = db.Column(DateTime, unique=False, nullable=False, server_default=current_timestamp()) kind = db.Column(Integer, unique=False, nullable=False) sentence = db.Column(Text, unique=False, nullable=False)
class Issue(BaseObject): __tablename__ = 'issues' id = Column(BigInteger, primary_key=True, autoincrement=True) created_at = Column(DateTime, default=current_timestamp(), nullable=False) updated_at = Column(DateTime, default=current_timestamp(), onupdate=current_timestamp(), nullable=False) number = Column(Integer, unique=True, nullable=False) state = Column(String(10)) title = Column(Text) body = Column(Text) labels = Column(String(128)) assignee = Column(String(128))
class Article(Base): __tablename__ = "article" id = Column(Integer, primary_key=True, index=True) title = Column(String(30), index=True) description = Column(String(1000), index=True) thumbnailURL = Column(String(100), index=True, nullable=True, unique=False) created_at = Column(DateTime, default=datetime.now(), server_default=current_timestamp()) updated_at = Column(DateTime, default=datetime.now(), server_onupdate=current_timestamp()) owner_id = Column(Integer, ForeignKey("user.id")) owner = relationship("User", back_populates="articles")
def update_available_cookies(self, user): now = datetime.datetime.now() now = datetime.datetime(now.year,now.month,now.day) if not user.last_cookie_date or (now - user.last_cookie_date).days > 0: user.available_cookies = Config.getint("Alliance","cookies") user.last_cookie_date = current_timestamp() session.commit()
def cancel(self, message, user, params): id = params.group(1) prop = self.load_prop(id) if prop is None: message.reply("No proposition number %s exists (idiot)."%(id,)) return if not prop.active: message.reply("You can't cancel prop %d, it's already expired."%(prop.id,)) return if prop.proposer is not user and not user.is_admin(): message.reply("Only %s may cancel proposition %d."%(prop.proposer.name,prop.id)) return self.recalculate_carebears(prop) yes, no, veto = self.sum_votes(prop) vote_result = "cancel" reply = self.text_result(vote_result, yes, no, veto) reply+= self.text_summary(prop) message.reply(reply) prop.active = False prop.closed = current_timestamp() prop.vote_result = vote_result session.commit()
class Update(Base): """ Update class that represents a device update """ __tablename__ = 'updates' id: int = Column(INT(), primary_key=True, autoincrement=True) codename: str = Column(VARCHAR(30), nullable=False) version: str = Column(VARCHAR(20), nullable=False) android: str = Column(VARCHAR(5), nullable=False) branch: str = Column(VARCHAR(15), nullable=False) type: str = Column(VARCHAR(11), nullable=False) method: str = Column(VARCHAR(8), nullable=False) size: str = Column(BIGINT(), nullable=True) md5: str = Column(CHAR(32), unique=True, nullable=True) filename: str = Column(TEXT(), unique=True, nullable=True) link: str = Column(TEXT(), nullable=False) changelog: str = Column(TEXT(), nullable=True, default='Bug fixes and system optimizations.') date: str = Column(DATE(), nullable=True) inserted_on: str = Column(TIMESTAMP(), default=current_timestamp()) def __repr__(self): return f"<Update(codename={self.codename}, version={self.version}, branch={self.branch}, method={self.method})>" def __str__(self): return str({k: v for k, v in self.__dict__.items() if not k.startswith("_")})
def insert(self, blog): ''' @see: IBlogService.insert ''' assert isinstance(blog, Blog), 'Invalid blog %s' % blog if blog.CreatedOn is None: blog.CreatedOn = current_timestamp() return super().insert(blog)
def createArticles(): artService = entityFor(IArticleService) slug = 'article-demo' #assert isinstance(artService, ArticleServiceAlchemy) try: artService.getBySlug(slug) return # session.query(ArticleMapped.Id).filter(ArticleMapped.Id == '1').one()[0] except (NoResultFound, InputError): theArticle = ArticleMapped() theArticle.CreatedOn = current_timestamp() theArticle.Slug = slug artService.insert(theArticle) artCtxService = entityFor(IArticleCtxService) assert isinstance(artCtxService, ArticleCtxServiceAlchemy) for typ in [1, 2, 3, 4]: ctx = ArticleCtxMapped() ctx.Type = typ ctx.Content = DATA['article_content'] ctx.Article = theArticle.Id artCtxService.insert(ctx)
def insert(self, post): ''' @see: IPostService.insert ''' assert isinstance(post, Post), 'Invalid post %s' % post postDb = PostMapped() copy(post, postDb, exclude=COPY_EXCLUDE) postDb.typeId = self._typeId(post.Type) # TODO: implement the proper fix using SQLAlchemy compilation rules nohigh = { i: None for i in range(0x10000, 0x110000) } if postDb.Meta: postDb.Meta = postDb.Meta.translate(nohigh) if postDb.Content: postDb.Content = postDb.Content.translate(nohigh) if postDb.ContentPlain: postDb.ContentPlain = postDb.ContentPlain.translate(nohigh) if post.CreatedOn is None: postDb.CreatedOn = current_timestamp() if not postDb.Author: colls = self.session().query(CollaboratorMapped).filter(CollaboratorMapped.User == postDb.Creator).all() if not colls: coll = CollaboratorMapped() coll.User = postDb.Creator src = self.session().query(SourceMapped).filter(SourceMapped.Name == PostServiceAlchemy.default_source_name).one() coll.Source = src.Id self.session().add(coll) self.session().flush((coll,)) colls = (coll,) postDb.Author = colls[0].Id self.session().add(postDb) self.session().flush((postDb,)) post.Id = postDb.Id return post.Id
class Misc(Base): """This model contains all possible MySQL types""" __tablename__ = "misc" id = Column(Integer, primary_key=True) big_integer_field = Column(BigInteger, default=0) big_integer_unsigned_field = Column(BIGINT(unsigned=True), default=0) large_binary_field = Column(LargeBinary, nullable=True) boolean_field = Column(Boolean, default=False) char_field = Column(CHAR(255), nullable=True) date_field = Column(Date, nullable=True) date_time_field = Column(DateTime, nullable=True) decimal_field = Column(DECIMAL(10, 2), nullable=True) float_field = Column(Float(12, 4), default=0) integer_field = Column(Integer, default=0) integer_unsigned_field = Column(INTEGER(unsigned=True), default=0) tinyint_field = Column(TINYINT, default=0) tinyint_unsigned_field = Column(TINYINT(unsigned=True), default=0) mediumint_field = Column(MEDIUMINT, default=0) mediumint_unsigned_field = Column(MEDIUMINT(unsigned=True), default=0) if environ.get("LEGACY_DB", "0") == "0": json_field = Column(JSON, nullable=True) nchar_field = Column(NCHAR(255), nullable=True) numeric_field = Column(Numeric(12, 4), default=0) unicode_field = Column(Unicode(255), nullable=True) real_field = Column(REAL(12, 4), default=0) small_integer_field = Column(SmallInteger, default=0) small_integer_unsigned_field = Column(SMALLINT(unsigned=True), default=0) string_field = Column(String(255), nullable=True) text_field = Column(Text, nullable=True) time_field = Column(Time, nullable=True) varbinary_field = Column(VARBINARY(255), nullable=True) varchar_field = Column(VARCHAR(255), nullable=True) timestamp_field = Column(TIMESTAMP, default=current_timestamp()) dupe = Column(Boolean, index=True, default=False)
def insert(self, post): ''' @see: IPostService.insert ''' assert isinstance(post, Post), 'Invalid post %s' % post postDb = PostMapped() copy(post, postDb, exclude=COPY_EXCLUDE) postDb.typeId = self._typeId(post.Type) postDb = self._adjustTexts(postDb) if post.CreatedOn is None: postDb.CreatedOn = current_timestamp() if not postDb.Author: colls = self.session().query(CollaboratorMapped).filter(CollaboratorMapped.User == postDb.Creator).all() if not colls: coll = CollaboratorMapped() coll.User = postDb.Creator src = self.session().query(SourceMapped).filter(SourceMapped.Name == PostServiceAlchemy.default_source_name).one() coll.Source = src.Id self.session().add(coll) self.session().flush((coll,)) colls = (coll,) postDb.Author = colls[0].Id self.session().add(postDb) self.session().flush((postDb,)) post.Id = postDb.Id return post.Id
class Authenticator(db.Model): __tablename__ = "authenticators" __table_args__ = (UniqueConstraint("credential_id", "user_id"), ) id = db.Column(pg.UUID(as_uuid=True), primary_key=True, default=uuid4) name = db.Column(db.String, nullable=False, default="authenticator") credential_id = db.Column(db.String, nullable=False) public_key = db.Column(db.String, nullable=False) sign_count = db.Column(db.Integer, nullable=False) created_at = db.Column( db.TIMESTAMP(timezone=True), nullable=False, server_default=functions.current_timestamp(), ) last_used_at = db.Column(db.TIMESTAMP(timezone=True)) user_id = db.Column(pg.UUID(as_uuid=True), db.ForeignKey("users.id"), nullable=False) user = db.relationship("User", backref="authenticators") @classmethod def generate_fake(cls): return cls( id=uuid4(), name="authenticator", credential_id="7SI3X75x6wFGxMS1TAHQEFRtifLfD9Gi", public_key="iOy7iL2iTKhQ3t4fFHrG3zwSivyaDYIS", sign_count=11, )
def createArticles(): artService = entityFor(IArticleService) slug = 'article-demo' #assert isinstance(artService, ArticleServiceAlchemy) try: artService.getBySlug(slug) return # session.query(ArticleMapped.Id).filter(ArticleMapped.Id == '1').one()[0] except (NoResultFound, InputError): theArticle = ArticleMapped() theArticle.CreatedOn = current_timestamp() theArticle.Slug = slug artService.insert(theArticle) artCtxService = entityFor(IArticleCtxService) assert isinstance(artCtxService, ArticleCtxServiceAlchemy) for typ in [1,2,3,4]: ctx = ArticleCtxMapped() ctx.Type = typ ctx.Content = DATA['article_content'] ctx.Article = theArticle.Id artCtxService.insert(ctx)
def performLogin(self, authentication): ''' @see: IAuthenticationService.performLogin ''' assert isinstance(authentication, Authentication), 'Invalid authentication %s' % authentication if authentication.Token is None: raise InputError(Ref(_('The login token is required'), ref=Authentication.Token)) if authentication.HashedToken is None: raise InputError(Ref(_('The hashed login token is required'), ref=Authentication.HashedToken)) if authentication.UserName is None: raise InputError(Ref(_('A user name is required for authentication'), ref=Authentication.UserName)) olderThan = self.session().query(current_timestamp()).scalar() olderThan -= self._authenticationTimeOut sql = self.session().query(TokenMapped) sql = sql.filter(TokenMapped.Token == authentication.Token) sql = sql.filter(TokenMapped.requestedOn > olderThan) if sql.delete() > 0: commitNow() # We make sure that the delete has been performed try: user = self.session().query(UserMapped).filter(UserMapped.Name == authentication.UserName).filter(UserMapped.DeletedOn == None).one() except NoResultFound: user = None if user is not None: assert isinstance(user, UserMapped), 'Invalid user %s' % user hashedToken = hmac.new(bytes(user.Name, 'utf8'), bytes(user.password, 'utf8'), hashlib.sha512).hexdigest() hashedToken = hmac.new(bytes(hashedToken, 'utf8'), bytes(authentication.Token, 'utf8'), hashlib.sha512).hexdigest() if authentication.HashedToken == hashedToken: hash = hashlib.sha512() hash.update(urandom(self.authentication_token_size)) login = LoginMapped() login.Session = hash.hexdigest() login.User = user.Id login.CreatedOn = login.AccessedOn = current_timestamp() try: self.session().add(login) except SQLAlchemyError as e: handle(e, login) return login raise InputError(_('Invalid credentials'))
def is_updated(self): return deferred( sa.Column( sa.TIMESTAMP, nullable=False, default=datetime.datetime.now, server_default=sqlaexp.text('0'), onupdate=datetime.datetime.now, server_onupdate=sqlafunc.current_timestamp(), ))
def hide(self, blogId): ''' @see: IBlogService.hide ''' blog = self.session().query(BlogMapped).get(blogId) if not blog: raise InputError(_('Invalid blog or credentials')) assert isinstance(blog, Blog), 'Invalid blog %s' % blog blog.DeletedOn = current_timestamp() self.session().merge(blog)
def putLive(self, blogId): ''' @see: IBlogService.putLive ''' blog = self.session().query(BlogMapped).get(blogId) if not blog: raise InputError(_('Invalid blog or credentials')) assert isinstance(blog, Blog), 'Invalid blog %s' % blog blog.LiveOn = current_timestamp() if blog.LiveOn is None else None self.session().merge(blog)
def delete(self, id): ''' @see: IUserService.delete ''' userDb = self.session().query(UserMapped).get(id) if not userDb or userDb.DeletedOn is not None: return False assert isinstance(userDb, UserMapped), 'Invalid user %s' % userDb userDb.DeletedOn = current_timestamp() self.session().merge(userDb) return True
def updateLastAccessOn(session, groupId): sql = session.query(BlogCollaboratorGroupMapped) sql = sql.filter(BlogCollaboratorGroupMapped.Id == groupId) try: group = sql.one() except NoResultFound: raise InputError(Ref(_('No collaborator group'), ref=BlogCollaboratorGroupMapped.Id)) group.LastAccessOn = current_timestamp() session.add(group) session.flush((group,))
def delete(self, id): ''' @see: IPostService.delete ''' postDb = self.session().query(PostMapped).get(id) if not postDb or postDb.DeletedOn is not None: return False postDb.DeletedOn = current_timestamp() self.session().flush((postDb,)) return True
def _syncBlog(self, blogSync): ''' Synchronize the blog for the given sync entry. @param blogSync: BlogSync The blog sync entry declaring the blog and source from which the blog has to be updated. ''' assert isinstance(blogSync, BlogSync), 'Invalid blog sync %s' % blogSync source = self.sourceService.getById(blogSync.Source) assert isinstance(source, Source) (scheme, netloc, path, params, query, fragment) = urlparse(source.URI) q = parse_qsl(query, keep_blank_values=True) q.append(('asc', 'cId')) q.append(('cId.since', blogSync.CId if blogSync.CId is not None else 0)) if blogSync.SyncStart is not None: q.append(('publishedOn.since', blogSync.SyncStart.strftime(self.date_time_format))) url = urlunparse((scheme, netloc, path + '/' + self.published_posts_path, params, urlencode(q), fragment)) req = Request(url, headers={'Accept' : self.acceptType, 'Accept-Charset' : self.encodingType, 'X-Filter' : '*,Author.Source.*,Author.User.*'}) try: resp = urlopen(req) except (HTTPError, socket.error) as e: log.error('Read error on %s: %s' % (source.URI, e)) return try: msg = json.load(codecs.getreader(self.encodingType)(resp)) except ValueError as e: log.error('Invalid JSON data %s' % e) return for post in msg['PostList']: try: if post['IsPublished'] != 'True' or 'DeletedOn' in post: continue lPost = BlogPost() lPost.Type = post['Type']['Key'] lPost.Creator = blogSync.Creator lPost.Author = self._getCollaboratorForAuthor(post['Author'], source) lPost.Meta = post['Meta'] if 'Meta' in post else None lPost.ContentPlain = post['ContentPlain'] if 'ContentPlain' in post else None lPost.Content = post['Content'] if 'Content' in post else None lPost.CreatedOn = lPost.PublishedOn = current_timestamp() # prepare the blog sync model to update the change identifier blogSync.CId = int(post['CId']) if blogSync.CId is None or int(post['CId']) > blogSync.CId else blogSync.CId blogSync.SyncStart = datetime.strptime(post['PublishedOn'], '%m/%d/%y %I:%M %p') # insert post from remote source self.blogPostService.insert(blogSync.Blog, lPost) # update blog sync entry self.blogSyncService.update(blogSync) except KeyError as e: log.error('Post from source %s is missing attribute %s' % (source.URI, e)) except Exception as e: log.error('Error in source %s post: %s' % (source.URI, e))
def update(self, blogSync): ''' @see IBlogSyncService.update ''' assert isinstance(blogSync, BlogSync), 'Invalid blog sync %s' % blogSync blogSyncDb = self.getById(blogSync.Id) if blogSync.Auto and not blogSyncDb.Auto and blogSync.SyncStart is None: blogSync.SyncStart = current_timestamp() return super().update(blogSync)
def update(self, post): ''' @see: IPostService.update ''' assert isinstance(post, Post), 'Invalid post %s' % post postDb = self.session().query(PostMapped).get(post.Id) if not postDb or postDb.DeletedOn is not None: raise InputError(Ref(_('Unknown post id'), ref=Post.Id)) if Post.Type in post: postDb.typeId = self._typeId(post.Type) if post.UpdatedOn is None: postDb.UpdatedOn = current_timestamp() self.session().flush((copy(post, postDb, exclude=COPY_EXCLUDE),))
def requestLogin(self): ''' @see: IAuthenticationService.requestLogin ''' hash = hashlib.sha512() hash.update(urandom(self.authentication_token_size)) token = TokenMapped() token.Token = hash.hexdigest() token.requestedOn = current_timestamp() self.session().add(token) return token
def insertAndPublish(self, blogId, post): ''' @see: IBlogPostService.insertAndPublish ''' assert isinstance(post, Post), 'Invalid post %s' % post postEntry = BlogPostEntry(Blog=blogId, blogPostId=self.postService.insert(post)) postEntry.CId = self._nextCId() postEntry.Order = self._nextOrdering(blogId) self.session().add(postEntry) self.session().query(BlogPostMapped).get(postEntry.blogPostId).PublishedOn = current_timestamp() return postEntry.blogPostId
def insert(self, post): """ @see: IPostService.insert """ assert isinstance(post, Post), "Invalid post %s" % post if post.Uuid is None: post.Uuid = str(uuid4().hex) if post.WasPublished is None: if post.PublishedOn is None: post.WasPublished = 0 else: post.WasPublished = 1 postDb = PostMapped() copy(post, postDb, exclude=COPY_EXCLUDE) postDb.typeId = self._typeId(post.Type) postDb = self._adjustTexts(postDb) if post.CreatedOn is None: postDb.CreatedOn = current_timestamp() if not postDb.Author: colls = self.session().query(CollaboratorMapped).filter(CollaboratorMapped.User == postDb.Creator).all() if not colls: coll = CollaboratorMapped() coll.User = postDb.Creator src = ( self.session() .query(SourceMapped) .filter(SourceMapped.Name == PostServiceAlchemy.default_source_name) .one() ) coll.Source = src.Id self.session().add(coll) self.session().flush((coll,)) colls = (coll,) postDb.Author = colls[0].Id self.session().add(postDb) self.session().flush((postDb,)) post.Id = postDb.Id postVerification = PostVerification() postVerification.Id = post.Id self.postVerificationService.insert(postVerification) return post.Id
def mark_error(jid, session): ''' jid (int): The identifier to mark an error status for session (netutils.DBSession): A session object with an active connection to the Autonomics database Marks the provided job as having a status of 'E' in the jn_mapping table. ''' jn_mapping = netutils.get_table_object('jn_mapping', session) session.conn.execute(jn_mapping.update().where( jn_mapping.c.job_id==jid).values(finished='E', f_ts=functions.current_timestamp()))
def insert(self, user): ''' @see: IUserService.insert ''' assert isinstance(user, User), 'Invalid user %s' % user userDb = UserMapped() userDb.password = user.Password userDb.CreatedOn = current_timestamp() userDb.typeId = self._userTypeId(user.Type) try: self.session().add(copy(user, userDb, exclude=('Type',))) self.session().flush((userDb,)) except SQLAlchemyError as e: handle(e, userDb) user.Id = userDb.Id return user.Id
def insert(self, collaboratorGroup): ''' @see IBlogCollaboratorGroupService.insert ''' group = BlogCollaboratorGroupMapped() group.Blog = collaboratorGroup.Blog group.LastAccessOn = current_timestamp() self.session().add(group) self.session().flush((group,)) insert = InsertFromSelect(tableFor(BlogCollaboratorGroupMemberMapped), 'fk_group_id, fk_collaborator_id', select([group.Id, BlogCollaboratorMapped.blogCollaboratorId]).where(BlogCollaboratorMapped.Blog == group.Blog)) self.session().execute(insert) return group.Id
def cleanExpired(self): ''' @see: ICleanupService.cleanExpired ''' olderThan = self.session().query(current_timestamp()).scalar() # Cleaning the expired tokens. sql = self.session().query(TokenMapped) sql = sql.filter(TokenMapped.requestedOn <= olderThan - self._authenticationTimeOut) deleted = sql.delete() assert log.debug('Cleaned \'%s\' expired authentication requests', deleted) or True # Cleaning the expired sessions. sql = self.session().query(LoginMapped) sql = sql.filter(LoginMapped.AccessedOn <= olderThan - self._sessionTimeOut) deleted = sql.delete() assert log.debug('Cleaned \'%s\' expired sessions', deleted) or True