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
