def test_bans_get(self):
from datetime import timedelta
from sqlalchemy.sql import func
from ..interfaces import IBanQueryService
from ..models import Ban
from ..services import BanQueryService, ScopeService
from ..views.admin import bans_get
from . import mock_service
ban1 = self._make(
Ban(ip_address="10.0.1.0/24", active_until=func.now() + timedelta(hours=1))
)
self._make(
Ban(ip_address="10.0.2.0/24", active_until=func.now() - timedelta(hours=1))
)
ban3 = self._make(Ban(ip_address="10.0.3.0/24"))
self._make(Ban(ip_address="10.0.3.0/24", active=False))
ban5 = self._make(
Ban(ip_address="10.0.3.0/24", active_until=func.now() + timedelta(hours=2))
)
ban6 = self._make(
Ban(ip_address="10.0.3.0/24", created_at=func.now() + timedelta(minutes=5))
)
self.dbsession.commit()
request = mock_service(
self.request,
{IBanQueryService: BanQueryService(self.dbsession, ScopeService())},
)
request.method = "GET"
response = bans_get(request)
self.assertEqual(response["bans"], [ban6, ban3, ban5, ban1])
def test_list_inactive(self):
from datetime import timedelta
from sqlalchemy.sql import func
from ..models import Ban
self._make(
Ban(ip_address="10.0.1.0/24", active_until=func.now() + timedelta(hours=1))
)
ban2 = self._make(
Ban(ip_address="10.0.2.0/24", active_until=func.now() - timedelta(hours=1))
)
self._make(Ban(ip_address="10.0.3.0/24"))
ban4 = self._make(Ban(ip_address="10.0.3.0/24", active=False))
ban5 = self._make(
Ban(ip_address="10.0.3.0/24", active_until=func.now() - timedelta(hours=2))
)
ban6 = self._make(
Ban(
ip_address="10.0.3.0/24",
created_at=func.now() + timedelta(minutes=5),
active=False,
)
)
self.dbsession.commit()
ban_query_svc = self._make_one()
self.assertEqual(ban_query_svc.list_inactive(), [ban6, ban4, ban2, ban5])
def init(self, auto_create=True):
# TODO handle if user does not pass in table sqlite://path.db
uri_splt = self.uri.split(":")
engine_uri = u":".join(uri_splt[:-1])
table_name = uri_splt[-1]
metadata = MetaData()
postref_table = Table(table_name, metadata,
Column('id', Integer, primary_key=True),
Column('created_at', DateTime, default=func.now()),
Column('updated_at', DateTime, default=func.now(), onupdate=func.current_timestamp()),
Column('uuid', String(512)),
Column('path', String(512)),
Column('revision', Integer, default=0),
Column('status', Integer, default=self.PostStatus.DRAFT.value),
Column('ref', String(512)),
Column('data', LargeBinary))
self.engine = create_engine(engine_uri, pool_recycle=3600)
self.session = scoped_session(sessionmaker(bind=self.engine))
if auto_create:
postref_table.create(self.engine, checkfirst=True)
class PostRef(object):
pass
mapper(PostRef, postref_table)
self.PostRef = PostRef
def _process_request(context, organization_id, member, status):
''' Cancel a member request or existing membership.
Delete from database the member request (if existing) and set delete state in member table
:param member: id of the member
:type member: string
'''
user = context.get("user")
# Logical delete on table member
member.state = 'deleted'
# Fetch the newest member_request associated to this membership (sort by
# last modified field)
member_request = model.Session.query(MemberRequest).filter(
MemberRequest.membership_id == member.id).order_by('request_date desc').limit(1).first()
# BFW: Create a new instance every time membership status is changed
message = u'MemberRequest cancelled by own user'
locale = get_safe_locale()
mrequest_date = func.now()
if member_request is not None and member_request.status == status:
locale = member_request.language
mrequest_date = member_request.request_date
member_request = MemberRequest(membership_id=member.id, role=member.capacity, status="cancel", request_date=mrequest_date,
language=locale, handling_date=func.now(), handled_by=c.userobj.name, message=message)
model.Session.add(member_request)
revision = model.repo.new_revision()
revision.author = user
revision.message = u'Member request deleted by user'
member.save()
model.repo.commit()
return model_dictize.member_dictize(member, context)
def create_with_user(self, board_slug, user_id, title, body, ip_address):
"""Creates a topic similar to :meth:`create` but with user ID
associated to it.
This method will make the post delegate ident and name from the user
as well as allow posting in board or topic that are not archived.
:param board_slug: A slug :type:`str` identifying a board.
:param user_id: A user ID :type:`int` to post as.
:param title: A :type:`str` topic title.
:param body: A :type:`str` topic body.
:param ip_address: An IP address of the topic creator.
"""
user = self.user_query_svc.user_from_id(user_id)
board = self._prepare_c(
board_slug, allowed_board_status=("open", "restricted", "locked")
)
# Create topic
topic = Topic(
board=board,
title=title,
created_at=func.now(),
updated_at=func.now(),
status="open",
)
self.dbsession.add(topic)
# Create topic meta
topic_meta = TopicMeta(
topic=topic, post_count=1, posted_at=func.now(), bumped_at=func.now()
)
self.dbsession.add(topic_meta)
# Create post
ident = user.ident
ident_type = user.ident_type
name = user.name
post = Post(
topic=topic,
number=topic_meta.post_count,
body=body,
bumped=True,
name=name,
ident=ident,
ident_type=ident_type,
ip_address=ip_address,
)
self.dbsession.add(post)
return topic
def test_dashboard_get(self):
from datetime import timedelta
from sqlalchemy.sql import func
from ..interfaces import IUserSessionQueryService, IUserLoginService
from ..models import User, UserSession
from ..services import UserSessionQueryService, UserLoginService
from ..views.admin import dashboard_get
from . import mock_service
user = self._make(
User(
username="******",
encrypted_password="******",
ident="foo",
ident_type="ident_admin",
name="Nameless Foo",
)
)
user_session1 = self._make(
UserSession(
user=user,
token="user1_token1",
ip_address="127.0.0.1",
last_seen_at=func.now() - timedelta(days=2),
)
)
user_session2 = self._make(
UserSession(
user=user,
token="user1_token2",
ip_address="127.0.0.1",
last_seen_at=func.now() - timedelta(days=3),
)
)
user_session3 = self._make(
UserSession(
user=user,
token="user1_token3",
ip_address="127.0.0.1",
last_seen_at=func.now() - timedelta(days=1),
)
)
self.dbsession.commit()
request = mock_service(
self.request,
{
IUserLoginService: UserLoginService(self.dbsession),
IUserSessionQueryService: UserSessionQueryService(self.dbsession),
},
)
request.method = "GET"
self.config.testing_securitypolicy(userid="user1_token3")
response = dashboard_get(request)
self.assertEqual(response["user"], user)
self.assertEqual(
response["sessions"], [user_session3, user_session1, user_session2]
)
def create(self, board_slug, title, body, ip_address):
"""Creates a new topic and associate related metadata. Unlike
``enqueue``, this method performs the actual creation of the topic.
:param board_slug: A slug :type:`str` identifying a board.
:param title: A :type:`str` topic title.
:param body: A :type:`str` topic body.
:param ip_address: An IP address of the topic creator.
"""
board = self._prepare_c(board_slug, allowed_board_status=("open",))
# Create topic
topic = Topic(
board=board,
title=title,
created_at=func.now(),
updated_at=func.now(),
status="open",
)
self.dbsession.add(topic)
# Create topic meta
topic_meta = TopicMeta(
topic=topic, post_count=1, posted_at=func.now(), bumped_at=func.now()
)
self.dbsession.add(topic_meta)
# Create post
ident = None
ident_type = "none"
if board.settings["use_ident"]:
ident_type = "ident"
ident = self.identity_svc.identity_with_tz_for(
self.setting_query_svc.value_from_key("app.time_zone"),
board=topic.board.slug,
ip_address=ip_address,
)
post = Post(
topic=topic,
number=topic_meta.post_count,
body=body,
bumped=True,
name=board.settings["name"],
ident=ident,
ident_type=ident_type,
ip_address=ip_address,
)
self.dbsession.add(post)
return topic
def vacuum_zones(self, age_days=None):
"""
Destroy zones older than age_days
"""
self._begin_op()
db_session = self.db_session
db_query_slice = get_numeric_setting('db_query_slice', int)
age_days_from_config = float(zone_cfg.get_row_exc(db_session,
key='zone_del_age'))
if age_days_from_config <= 0 and age_days is None:
age_days = get_numeric_setting('zone_del_off_age', float)
elif age_days is None:
age_days = age_days_from_config
age_days = timedelta(days=age_days)
count = 0
# Clear old and nuked zones one by one
id_query = db_session.query(ZoneSM.id_)\
.filter(ZoneSM.state == ZSTATE_DELETED)\
.filter(or_(ZoneSM.deleted_start == None,
(func.now() - ZoneSM.deleted_start) > age_days))\
.filter(ZoneSM.zone_files == False)\
.yield_per(db_query_slice)
id_results = []
for zone_id, in id_query:
id_results.append(zone_id)
for zone_id in id_results:
try:
zone_sm = db_session.query(ZoneSM)\
.filter(ZoneSM.id_ == zone_id).one()
except NoResultFound:
continue
if zone_sm.state != ZSTATE_DELETED:
# Skip this if a customer has undeleted zone in the mean time..
continue
db_session.delete(zone_sm)
db_session.commit()
count += 1
# Finally do zone_sm destroy operation to
query = db_session.query(ZoneSM)\
.filter(ZoneSM.state == ZSTATE_DELETED)\
.filter(or_(ZoneSM.deleted_start == None,
(func.now() - ZoneSM.deleted_start) > age_days))
for zone_sm in query:
if zone_sm.state != ZSTATE_DELETED:
# Skip this if a customer has undeleted zone in the mean time..
continue
try:
exec_zonesm(zone_sm, ZoneSMDoDestroy)
except ZoneSmFailure:
continue
count += 1
result = {'num_deleted': count}
self._finish_op()
return result
def make_filter(cls, field, ftype, value):
filter = None
if ftype == 'IN':
filter = field.in_([v for v in value if v])
elif ftype == 'date_gt':
filter = field > value
elif ftype == 'date_gte':
filter = field >= value
elif ftype == 'date_gt_now_less':
qty, granularity = value.split(" ")
filter = field > func.date_trunc(granularity, func.now() - cast(value, Interval()))
elif ftype == 'date_lt_now_less':
qty, granularity = value.split(" ")
filter = field < func.date_trunc(granularity, func.now() - cast(value, Interval()))
elif ftype == 'date_x_last_n':
qty, granularity, count_current_period = value.split(" ")
filter = (field > func.date_trunc(granularity, func.now() - cast("%s %s" % (qty, granularity), Interval())), field < func.date_trunc(granularity, func.now() - cast('0', Interval())),)
if count_current_period == 'on':
filter = filter[0]
elif ftype == 'date_month_ne':
filter = extract('month', field) != value
elif ftype == 'date_month_gt':
filter = extract('month', field) > value
elif ftype == 'date_month_lt':
filter = extract('month', field) < value
elif ftype == 'date_month_eq':
filter = extract('month', field) == value
elif ftype == 'date_hour_ne':
filter = extract('hour', field) != value
elif ftype == 'date_hour_gt':
filter = extract('hour', field) > value
elif ftype == 'date_hour_lt':
filter = extract('hour', field) < value
elif ftype == 'date_hour_eq':
filter = extract('hour', field) == value
elif ftype == 'date_lt':
filter = field < value
elif ftype == 'date_lte':
filter = field <= value
elif ftype == '=':
filter = field == value
elif ftype == '!=':
filter = field != value
elif ftype == '>':
filter = field > value
elif ftype == '>=':
filter = field >= value
elif ftype == '<':
filter = field < value
elif ftype == '<=':
filter = field <= value
elif ftype == 'like':
filter = field.ilike(value)
return filter
def _make_topic(days=0, hours=0, **kwargs):
topic = self._make(Topic(**kwargs))
self._make(
TopicMeta(
topic=topic,
post_count=0,
posted_at=func.now(),
bumped_at=func.now() - timedelta(days=days, hours=hours),
)
)
return topic
def update_user_data(self, username, type, new_data):
new_data = json.dumps(new_data)
res = self.engine.execute(
UserStorage.__table__.update((UserStorage.__table__.c.userid == username) & (UserStorage.__table__.c.type == type)),
data=new_data,
last_updated=func.now())
if not res.rowcount:
self.engine.execute(
UserStorage.__table__.insert(),
userid=username,
type=type,
data=new_data,
last_updated=func.now())
def vacuum_syslog (self, age_days=None):
"""
Destroy syslog messages received more than age_days ago
"""
self._begin_op()
db_session = self.db_session
if age_days is None:
age_days = float(zone_cfg.get_row_exc(db_session,
key='syslog_max_age'))
age_days = timedelta(days=age_days)
count = 0
# Do a straight SQL DELETE first to speed things along
# Count events to be deleted
syslog_table = sql_data['tables'][SyslogMsg]
where_stmt = and_(SyslogMsg.receivedat != None,
(func.now() - SyslogMsg.receivedat) > age_days)
count_select = select([func.count(syslog_table.c.get('id'))],
where_stmt)
result = db_session.execute(count_select).fetchall()
count += result[0][0]
db_session.execute(syslog_table.delete().where(where_stmt))
result = {'num_deleted': count}
self._finish_op()
return result
def vacuum_event_queue(self, age_days=None):
"""
Destroy events processed more than age_days ago
"""
self._begin_op()
db_session = self.db_session
if age_days is None:
age_days = float(zone_cfg.get_row_exc(db_session,
key='event_max_age'))
age_days = timedelta(days=age_days)
count = 0
# Do a straight SQL DELETE first to speed things along
# Count events to be deleted
event_table = sql_data['tables'][Event]
where_stmt = and_(Event.state.in_(event_processed_states),
Event.processed != None,
(func.now() - Event.processed) > age_days)
count_select = select([func.count(event_table.c.get('id'))],
where_stmt)
result = db_session.execute(count_select).fetchall()
count += result[0][0]
db_session.execute(event_table.delete().where(where_stmt))
result = {'num_deleted': count}
self._finish_op()
return result
def _user_session_c(self, token, ip_address):
"""Internal method for querying user session object and cache
it throughout the request lifecycle.
:param token: A user login token :type:`str`.
:param ip_address: IP address of the user.
"""
if not (token, ip_address) in self.sessions_map:
user_session = (
self.dbsession.query(UserSession)
.options(joinedload(UserSession.user))
.filter(
and_(
UserSession.token == token,
UserSession.ip_address == ip_address,
UserSession.last_seen_at != None, # noqa: E711
or_(
UserSession.revoked_at == None, # noqa: E711
UserSession.revoked_at >= func.now(),
),
)
)
.first()
)
self.sessions_map[(token, ip_address)] = user_session
return self.sessions_map[(token, ip_address)]
def _update_topic_meta_states(session, context, instance):
"""Update topic metadata and related states when new posts are made."""
for post in filter(lambda m: isinstance(m, Post), session.new):
topic = post.topic
board = topic.board
if topic in session.new:
topic_meta = topic.meta
else:
topic_meta = session.query(TopicMeta).\
filter_by(topic=topic).\
with_for_update().\
one()
topic_meta.post_count = post.number = topic_meta.post_count + 1
topic_meta.posted_at = post.created_at or func.now()
if post.bumped is None or post.bumped:
topic_meta.bumped_at = topic_meta.posted_at
if topic.status == 'open' and \
topic_meta.post_count >= board.settings['max_posts']:
topic.status = 'archived'
session.add(topic_meta)
session.add(topic)
session.add(post)
def create_comments(id): current_time = func.now() user_id = 14 comment = Comments(comment_text = request.json['user_comment'],created_at = current_time, updated_at = current_time,user_id = 14,post_id = id) sql_alchemy_session.add(comment) sql_alchemy_session.commit() return jsonify(comment_id = comment.comment_id)
def ping(self):
"""Use this active session to check the database connectivity."""
try:
self.execute(select([func.now()])).first()
except exc.OperationalError:
return False
return True
def register():
user_info = {
'first_name': request.json['first_name'],
'last_name': request.json['last_name'],
'email': request.json['email'],
'password': request.json['password'],
'password_cnf': request.json['confirm_password']
}
user_status = validate_user(user_info)
if user_status['flag'] == True :
return jsonify(error = True)
else :
current_time = func.now()
pw_hash=bcrypt.generate_password_hash(request.json['password'])
user = Users(first_name = request.json['first_name'],last_name = request.json['last_name'],email = request.json['email'],password = pw_hash,created_at = current_time,updated_at = current_time)
print("user is",user.__dict__)
session['current_user'] = request.json['first_name'] + request.json['last_name']
sql_alchemy_session.add(user)
#Flush() to add the current transaction to the db and get the auto generated user_idfo rthis transaction
sql_alchemy_session.flush()
user_id = user.__dict__.get('user_id')
#commit() commits any pending changes to the db
sql_alchemy_session.commit()
return jsonify(error = False,username = session['current_user'],user_id = user_id)
def bulk_get(self, queue, message_ids, project):
if project is None:
project = ''
message_ids = [id for id in
map(utils.msgid_decode, message_ids)
if id is not None]
statement = sa.sql.select([tables.Messages.c.id,
tables.Messages.c.body,
tables.Messages.c.ttl,
tables.Messages.c.created])
and_stmt = [tables.Messages.c.id.in_(message_ids),
tables.Queues.c.name == queue,
tables.Queues.c.project == project,
tables.Messages.c.ttl >
sfunc.now() - tables.Messages.c.created]
j = sa.join(tables.Messages, tables.Queues,
tables.Messages.c.qid == tables.Queues.c.id)
statement = statement.select_from(j).where(sa.and_(*and_stmt))
now = timeutils.utcnow_ts()
records = self.driver.run(statement)
for id, body, ttl, created in records:
yield {
'id': utils.msgid_encode(id),
'ttl': ttl,
'age': now - calendar.timegm(created.timetuple()),
'body': json.loads(body),
}
def post(self, queue, messages, client_uuid, project):
if project is None:
project = ''
with self.driver.trans() as trans:
qid = utils.get_qid(self.driver, queue, project)
# Delete the expired messages
and_stmt = sa.and_(tables.Messages.c.ttl <=
sfunc.now() - tables.Messages.c.created,
tables.Messages.c.qid == qid)
statement = tables.Messages.delete().where(and_stmt)
trans.execute(statement)
# executemany() sets lastrowid to None, so no matter we manually
# generate the IDs or not, we still need to query for it.
def it():
for m in messages:
yield dict(qid=qid,
ttl=m['ttl'],
body=utils.json_encode(m['body']),
client=str(client_uuid))
result = trans.execute(tables.Messages.insert(), list(it()))
statement = sa.sql.select([tables.Messages.c.id])
statement = statement.limit(result.rowcount)
statement = statement.order_by(tables.Messages.c.id.desc())
result = trans.execute(statement).fetchall()
return [utils.msgid_encode(i[0]) for i in reversed(result)]
def test_ban_edit_get(self):
from datetime import timedelta
from sqlalchemy.sql import func
from ..models import Ban
from ..interfaces import IBanQueryService
from ..services import BanQueryService, ScopeService
from ..views.admin import ban_edit_get
from . import mock_service
now = func.now()
ban = self._make(
Ban(
ip_address="10.0.0.0/24",
description="Violation of galactic law",
active_until=now + timedelta(days=30),
scope="galaxy_far_away",
active=True,
created_at=now,
)
)
self.dbsession.commit()
request = mock_service(
self.request,
{IBanQueryService: BanQueryService(self.dbsession, ScopeService())},
)
request.method = "GET"
request.matchdict["ban"] = str(ban.id)
response = ban_edit_get(request)
self.assertEqual(response["ban"], ban)
self.assertEqual(response["form"].ip_address.data, "10.0.0.0/24")
self.assertEqual(response["form"].description.data, "Violation of galactic law")
self.assertEqual(response["form"].duration.data, 30)
self.assertEqual(response["form"].scope.data, "galaxy_far_away")
self.assertTrue(response["form"].active.data)
def setup_tables(create=True, drop=False):
"""
Binds the model classes to registered metadata and engine and (potentially)
creates the db tables.
This function expects that you have bound the L{meta.metadata} and L{meta.engine}.
@param create: Whether to create the tables (if they do not exist).
@type create: C{bool}
@param drop: Whether to drop the tables (if they exist).
@type drop: C{bool}
"""
global frames_table
frames_table = Table(
"frames",
meta.metadata,
Column("message_id", String(255), primary_key=True),
Column("sequence", BigInteger, primary_key=False, autoincrement=True),
Column("destination", String(255), index=True),
Column("frame", PickleType),
Column("queued", DateTime, default=func.now()),
)
if drop:
meta.metadata.drop_all()
if drop or create:
meta.metadata.create_all()
def test_relations(self):
from datetime import timedelta
from sqlalchemy.sql import func
from ..models import User, UserSession
user = self._make(
User(
username="******",
encrypted_password="******",
ident="fooident",
name="Nameless Foo",
)
)
session1 = self._make(
UserSession(
user=user,
token="test1",
ip_address="127.0.0.1",
created_at=func.now() - timedelta(days=1),
)
)
session2 = self._make(
UserSession(user=user, token="test2", ip_address="127.0.0.1")
)
self.dbsession.commit()
self.assertEqual(session1.user, user)
self.assertEqual(session2.user, user)
self.assertEqual(list(user.sessions), [session2, session1])
def create(
self, ip_address, description=None, duration=None, scope=None, active=True
):
"""Create a new ban.
:param ip_address: An IP address or IP network to ban.
:param description: Optional description.
:param duration: Duration in days to auto-expire the ban.
:param scope: Scope to apply the ban to (e.g. ``board:meta``)
:param active: Boolean flag whether the ban should be active.
"""
if not description:
description = None
active_until = None
if duration:
duration_delta = datetime.timedelta(days=duration)
active_until = func.now() + duration_delta
if not scope:
scope = None
ban = Ban(
ip_address=ip_address,
description=description,
scope=scope,
active_until=active_until,
active=bool(active),
)
self.dbsession.add(ban)
return ban
def insert(self, model, **values):
"""Run an insert statement.
:return: primary key of the inserted row.
"""
for k, v in [
('version', 1),
('created', func.now()),
('updated', func.now()),
('active', True)
]:
if hasattr(model.__table__.c, k):
values.setdefault(k, v)
res = self.execute(model.__table__.insert().values(**values))
return res.inserted_primary_key[0]
def get(self, queue, message_id, project, count=False):
if project is None:
project = ''
mid = utils.msgid_decode(message_id)
if mid is None:
raise errors.MessageDoesNotExist(message_id, queue, project)
try:
j = sa.join(tables.Messages, tables.Queues,
tables.Messages.c.qid == tables.Queues.c.id)
sel = sa.sql.select([tables.Messages.c.body,
tables.Messages.c.ttl,
tables.Messages.c.created])
if count:
sel = sa.sql.select([sfunc.count(tables.Messages.c.id)])
sel = sel.select_from(j)
sel = sel.where(sa.and_(tables.Messages.c.id == mid,
tables.Queues.c.project == project,
tables.Queues.c.name == queue,
tables.Messages.c.ttl >
sfunc.now() - tables.Messages.c.created))
return self.driver.get(sel)[0]
except utils.NoResult:
raise errors.MessageDoesNotExist(message_id, queue, project)
def upgrade():
op.add_column('show',
sa.Column('date_tagged', sa.DateTime(), nullable=False,
server_default=func.now(), index=True))
op.add_column('show',
sa.Column('hour_tagged', sa.Integer(),
nullable=False, index=True,
server_default=str(int(time.time()/7200))))
def create_user():
current_time = func.now()
user = Users(first_name = request.json['first_name'],last_name = request.json['last_name'],email = request.json['email'],password = request.json['password'],created_at = current_time,updated_at = current_time)
db.session.add(user)
db.session.commit()
session['user_id'] = user.user_id
print("Success User created")
return jsonify(user_id = user.user_id)
def mark_seen(self, token, ip_address, revocation=SESSION_TOKEN_VALIDITY):
"""Mark the given token as seen and extend the token validity period
by the given :param:`revocation` seconds.
:param token: A user login token :type:`str`.
:param ip_address: IP address of the user.
:param revocation: Number of seconds until the token is invalidated.
"""
user_session = self._user_session_c(token, ip_address)
if user_session is None:
return None
if user_session.user.deactivated:
return None
user_session.last_seen_at = func.now()
user_session.revoked_at = func.now() + datetime.timedelta(seconds=revocation)
self.dbsession.add(user_session)
return user_session
def update(self, model, values, **where):
"""Run an update statement."""
if not isinstance(values, dict):
values = dict(values)
if hasattr(model.__table__.c, 'updated'):
values.setdefault('updated', func.now())
table = model.__table__
self.execute(_with_where_clause(table, table.update(), **where).values(**values))
class Clicks(db.Model):
id = db.Column(db.Integer, primary_key=True)
sender = db.Column(db.String(150), db.ForeignKey('users.username'))
receiver = db.Column(db.String(150))
creation_date = db.Column(db.DateTime(timezone=True), default=func.now())
confirmed = db.Column(db.Boolean)
def wipe(self):
self.content = '--DELETED--'
self.topics = []
self.last_edit_date = func.now()
db.session.commit()
class Topic(Versioned, Base):
"""Model class for topic. This model only holds topic metadata such as
title or its associated board. The actual content of a topic belongs
to :class:`Post`.
"""
__tablename__ = "topic"
id = Column(Integer, primary_key=True)
created_at = Column(DateTime(timezone=True), default=func.now())
updated_at = Column(DateTime(timezone=True), onupdate=func.now())
board_id = Column(Integer, ForeignKey("board.id"), nullable=False)
title = Column(Unicode(255), nullable=False)
status = Column(TopicStatusEnum, default="open", nullable=False)
board = relationship(
"Board",
backref=backref(
"topics",
lazy="dynamic",
cascade="all,delete",
order_by=desc(
func.coalesce(
select([TopicMeta.bumped_at
]).where(TopicMeta.topic_id == id).as_scalar(),
created_at,
)),
),
)
QUERY = (
("single_post", re.compile("^(\d+)$")),
("ranged_posts", re.compile("^(\d+)?\-(\d+)?$")),
("recent_posts", re.compile("^l(\d+)$")),
("recent_posts", re.compile("^recent$")),
)
def scoped_posts(self, query=None):
"""Return single post or multiple posts according to `query`. If
`query` is not given, this method is an equivalent of calling
:attr:`posts` directly. This method always returns an iterator.
Single numeric (e.g. "253")
Returns a single post that matches the number. For example if
"253" is given, then an iterator containing post number "253" is
returned.
Ranged query (e.g. "100-150")
Returns all posts within range. If start number is missing ("-150")
or end number is missing ("100-") then the first post and last post
are automatically assumed.
Recent query (e.g. "l30", "recent")
Returns the n last posts where n is the number after "l" letter.
If named "recent" is given, then a default value of last 20 posts
is used instead.
"""
if query is None:
return self.posts.all()
else:
for handler, matcher in self.QUERY:
match = matcher.match(str(query))
if match:
fn = getattr(self, handler)
return fn(*match.groups())
return []
def single_post(self, number=None):
"""Returns an iterator that contains a single post that matches
`number`. If post with such number could not be found, an empty
iterator is returned.
"""
if not number:
number = -1
return self.posts.filter_by(number=int(number)).all()
def ranged_posts(self, start=None, end=None):
"""Returns a range of post between `start` and `end`. When `start` or
`end` is empty, the first and last posts are assumed respectively.
"""
if start is None:
start = 1
if end is None:
query = Post.number >= start
else:
query = Post.number.between(start, end)
return self.posts.filter(query).all()
def recent_posts(self, count=30):
"""Returns recent `count` number of posts associated with this topic.
Defaults to 30 posts if `count` is not given.
"""
return (self.posts.order_by(False).order_by(desc(
Post.number)).limit(count).all()[::-1])
def time_updated(cls):
return Column(DateTime(timezone=True), onupdate=func.now())
class Order(Base):
__tablename__ = 'order'
id = Column(Integer, primary_key=True)
creation_date = Column(DateTime(timezone=True), server_default=func.now())
class Item(Base):
"""Represents an item object"""
__tablename__ = 'item'
id = Column(Integer, primary_key=True)
name = Column(String(60), nullable=False)
description = Column(String(500))
categoryId = Column(Integer, ForeignKey('category.id'))
category = relationship(Category)
userId = Column(String(60), ForeignKey('user.email'))
user = relationship(User)
image = Column(String(), nullable=True)
created = Column(DateTime(timezone=True), server_default=func.now())
updated = Column(DateTime(timezone=True), onupdate=func.now(), default=datetime.datetime.utcnow)
def __init__(self, name='', description='', category=None, user=None,
image='default.jpg'):
self.name = name
self.description = description
self.category = category
self.user = user
self.image = image
@property
def serialize(self):
"""Return object data in easily serializeable format"""
return {
'id': self.id,
'category': self.categoryId,
'name': self.name,
'description': self.description,
'created by': self.userId,
'image': self.image,
}
@staticmethod
def validParams(params, image):
if params['name'] == '':
flash('You entered an invalid value for the name field')
return False
if not images.validName(image.filename.lower()):
flash('You selected an invalid picture to upload')
return False
return True
@staticmethod
def save(item, params, image, userId):
if Item.validParams(params, image):
item.name = params['name']
# item.categoryId = int(params['category'])
item.category = db.getOne(Category, "id", params['category'])
item.description = params['description'].strip()
# item.userId = userId
item.user = db.getOne(User, "email", userId)
db.session.add(item)
db.session.flush()
url = images.save(image, item)
if url:
item.image = url
db.session.commit()
flash("%s has been saved" % item.name)
return True
return False
@staticmethod
def delete(item):
try:
images.delete(item.image)
db.session.delete(item)
db.session.commit()
flash('Item %s has been deleted' % item.name)
return True
except:
flash('An error occured and Item %s not deleted. Please try again' % item.name)
return False
class User(UserMixin, db.Model):
id = db.Column(db.Integer(), primary_key=True)
first_name = db.Column(db.String(30), nullable=True)
last_name = db.Column(db.String(150), nullable=True)
email = db.Column(db.String(254), nullable=False, unique=True)
username = db.Column(db.String(150), nullable=False)
password = db.Column(db.String(128), nullable=False)
is_active = db.Column(db.Boolean, nullable=False, default=False)
date_joined = db.Column(db.DateTime(timezone=True),
server_default=func.now(),
nullable=False)
bio = db.Column(db.TEXT, nullable=True)
image = db.Column(db.String(500), nullable=True)
is_superuser = db.Column(db.Boolean, nullable=False, default=False)
def __init__(self,
email,
username,
password,
first_name=None,
last_name=None,
bio=None,
image=None,
is_active=False,
is_superuser=False):
self.first_name = first_name
self.last_name = last_name
self.email = email
self.username = username
self.password = generate_password_hash(password)
self.is_active = is_active
self.bio = bio
self.image = image
self.is_superuser = is_superuser
def save(self):
db.session.add(self)
db.session.commit()
from .schemas.schmas import UserSchema
data = UserSchema().dump(self)
event_type = 'user_created'
print(data, 'datauser')
Publish(data=data, event_type=event_type)
def send_confirmation_mail(self):
token = generate_confirmation_token(self.email)
confirm_url = url_for('confirm_email', token=token, _external=True)
html = render_template('email/confirmation_email.html',
confirm_url=confirm_url,
user=self)
data = {
'subject': 'Confirmation mail',
'body': html,
'to': [self.email],
'subtype': 'html',
}
event_type = 'send_mail'
Publish(data, event_type)
@property
def get_full_name(self):
return f'{self.first_name} {self.last_name}'
def DATABASE_URL(
connection: str = db_config().db_connection,
database: str = db_config().db_database,
):
return str(connection + ":///" + database)
# Notes of Api
notes = Table(
"notes",
metadata,
Column("id", Integer, primary_key=True),
Column("title", String(50)),
Column("description", String(50)),
Column("created_date", DateTime, default=func.now(), nullable=False),
)
# comnotes of Comments
comnotes = Table(
"comnotes",
metadata,
Column("id", Integer, primary_key=True),
Column("title", String(50)),
Column("description", String(50)),
Column("created_date", DateTime, default=func.now(), nullable=False),
)
# fnotes of feed
fnotes = Table(
"fnotes",
metadata,
Column("id", Integer, primary_key=True),
def update_last_modified_timestamp(mapper, connection, row):
# updates the value of the `last_modified` column when we're doing an UPDATE
row.last_modified = func.now()
class Field(Model, SoftDeleteableMixin):
__tablename__ = 'field'
id = Column(Integer, primary_key=True)
form_id = Column(Integer, ForeignKey('form.id'))
name = Column(String(255))
title = Column(String(500))
discriminator = Column(String(50))
constraints = relationship('Constraint')
readonly = Column(Boolean, default=False)
layout_row_index = Column(Integer)
layout_column_index = Column(Integer)
layout_columns = Column(Integer)
created_at = Column(DateTime, default=func.now())
updated_at = Column(DateTime, default=func.now(), onupdate=func.now())
__mapper_args__ = {
'polymorphic_identity': 'field',
'polymorphic_on': discriminator
}
def __init__(self, **kwargs):
super().__init__()
for k, v in kwargs.items():
setattr(self, k, v)
self.errors = []
self._value = None
@property
def value(self):
if not hasattr(self, '_value'):
return None
return self._value
@value.setter
def value(self, value):
self._value = value
@property
def has_errors(self):
if hasattr(self, 'errors') and self.errors:
return True
return False
def get_key(self):
if self.name:
return self.name
return str(self.id)
def format(self, val):
return val
def validate(self):
self.errors = []
for constraint in self.constraints:
try:
constraint.validate(self.value)
except ValidationError as e:
self.errors.append(e)
return 0 == len(self.errors)
def to_text_value(self, val):
return str(val)
class BaseCertificate(object):
createDatetime = Column(DateTime,
default=datetime.datetime.now,
comment='Дата создания документа')
@declared_attr
def createUserId(self):
return Column(Integer,
ForeignKey('User.id'),
primary_key=False,
comment='Пользователь, создавший предварительную запись')
@declared_attr
def createUser(cls):
return db.relationship('UserModel', foreign_keys=[cls.createUserId])
updateDatetime = Column(DateTime,
onupdate=func.now(),
comment='Дата последнего обновления файла')
@declared_attr
def updateUserId(cls):
return Column(Integer,
ForeignKey('User.id'),
primary_key=False,
comment='Пользователь, внесший последние изменения')
@declared_attr
def updateUser(cls):
return db.relationship('UserModel', foreign_keys=[cls.updateUserId])
@property
def updateUserLogin(self):
return self.updateUser.login if self.updateUser is not None else ''
closedDatetime = Column(
DateTime,
nullable=True,
comment='Дата и время создания предварительной записи')
@declared_attr
def closedUserId(cls):
return Column(Integer,
ForeignKey('User.id'),
primary_key=False,
comment='Пользователь, закрывший запись')
@declared_attr
def closedUser(cls):
return db.relationship('UserModel', foreign_keys=[cls.closedUserId])
status = Column(Integer, comment='Статус документа')
serial = Column(String16, nullable=True, comment='Серия документа')
number = Column(Integer,
nullable=True,
comment='Номер документа',
index=True)
deliveryDate_Y = Column(YearInt,
comment='Корешок. Дата выдачи документа (год)')
deliveryDate_M = Column(MonthInt,
comment='Корешок. Дата выдачи документа (месяц)')
deliveryDate_D = Column(DayInt,
comment='Корешок. Дата выдачи документа (день)')
@property
def deliveryDate(self):
return combine_date(self.deliveryDate_Y, self.deliveryDate_M,
self.deliveryDate_D)
medicalOrganisationHeadFamilyName = Column(
String64, comment='Фамилия руководителя мед. учреждения')
medicalOrganisationHeadFirstName = Column(
String64, comment='Имя руководителя мед. учреждения')
medicalOrganisationHeadMiddleName = Column(
String64, comment='Отчество руководителя мед. учреждения')
@property
def medicalOrganisationHeadName(self):
return combine_name(self.medicalOrganisationHeadFamilyName,
self.medicalOrganisationHeadFirstName,
self.medicalOrganisationHeadMiddleName)
recipientFamilyName = Column(String64, comment='Корешок. Фамилия')
recipientFirstName = Column(String64, comment='Корешок. Имя')
recipientMiddleName = Column(String64, comment='Корешок. Отчество')
@property
def recipientName(self) -> str:
return combine_name(self.recipientFamilyName, self.recipientFirstName,
self.recipientMiddleName)
@declared_attr
def recipientDocumentType(cls):
return Column(Integer,
ForeignKey('rbDocumentType.id', ondelete='SET NULL'),
comment='Корешок. Документ {rbDocumentType}',
nullable=True)
@declared_attr
def recipientDocType(cls):
return db.relationship('DocumentTypeModel',
foreign_keys=[cls.recipientDocumentType])
recipientDocumentSerial = Column(String64,
comment='Корешок. Серия документа')
recipientDocumentNumber = Column(String64,
comment='Корешок. Номер документа')
recipientDocumentOrigin = Column(String128, comment='Кем выдан документ')
recipientDocumentDate_Y = Column(
YearInt,
comment='Дата выдачи документа удостоверяющего личность (год)')
recipientDocumentDate_M = Column(
MonthInt,
comment='Дата выдачи документа удостоверяющего личность (месяц)')
recipientDocumentDate_D = Column(
DayInt,
comment='Дата выдачи документа удостоверяющего личность (день)')
@property
def recipientDocumentDate(self):
return combine_date(self.recipientDocumentDate_Y,
self.recipientDocumentDate_M,
self.recipientDocumentDate_D)
@property
def recipientDocument(self) -> str:
""" Документ устоверяющий личность """
return ' '.join(it for it in (
self.recipientDocType.name if self.recipientDocType else '',
self.recipientDocumentSerial, self.recipientDocumentNumber,
' '.join(t for t in (
'выдан', self.recipientDocumentOrigin or '',
self.recipientDocumentDate.strftime('%d.%m.%Y') if self.
recipientDocumentDate else '')
if t) if self.recipientDocumentDate is not None
or self.recipientDocumentOrigin else '') if it)
@declared_attr
def doctorWhoGaveCertificatePost_id(cls):
return Column(
Integer,
ForeignKey('rbPost.id'),
primary_key=False,
comment='Должность врача, выдавшего медицинское свидетельство')
@declared_attr
def doctorWhoGaveCertificatePost(cls):
return db.relationship(
'PostModel', foreign_keys=[cls.doctorWhoGaveCertificatePost_id])
doctorWhoGaveCertificateFamilyName = Column(
String64, comment='Фамилия врача, выдавшего медицинское свидетельство')
doctorWhoGaveCertificateFirstName = Column(
String64, comment='Имя врача, выдавшего медицинское свидетельство')
doctorWhoGaveCertificateMiddleName = Column(
String64,
comment='Отчество врача, выдавшего медицинское свидетельство')
@property
def doctorWhoGaveCertificateName(self):
return combine_name(self.doctorWhoGaveCertificateFamilyName,
self.doctorWhoGaveCertificateFirstName,
self.doctorWhoGaveCertificateMiddleName)
@declared_attr
def doctorWhoCheckedCertificatePost_id(cls):
return Column(
Integer,
ForeignKey('rbPost.id'),
primary_key=False,
comment='Должность врача, проверившего медицинское свидетельство')
@declared_attr
def doctorWhoCheckedCertificatePost(cls):
return db.relationship(
'PostModel', foreign_keys=[cls.doctorWhoCheckedCertificatePost_id])
doctorWhoCheckedCertificateFamilyName = Column(
String64,
comment='Фамилия врача, проверившего медицинское свидетельство')
doctorWhoCheckedCertificateFirstName = Column(
String64, comment='Имя врача, проверившего медицинское свидетельство')
doctorWhoCheckedCertificateMiddleName = Column(
String64,
comment='Отчество врача, проверившего медицинское свидетельство')
@property
def doctorWhoCheckedCertificateName(self):
return combine_name(self.doctorWhoCheckedCertificateFamilyName,
self.doctorWhoCheckedCertificateFirstName,
self.doctorWhoCheckedCertificateMiddleName)
@declared_attr
def doctorWhoFillCertificatePost_id(cls):
return Column(
Integer,
ForeignKey('rbPost.id'),
primary_key=False,
comment='Должность врача, заполнившего медицинское свидетельство')
@declared_attr
def doctorWhoFillCertificatePost(cls):
return db.relationship(
'PostModel', foreign_keys=[cls.doctorWhoFillCertificatePost_id])
doctorWhoFillCertificateFamilyName = Column(
String64,
comment='Фамилия врача, заполнившего медицинское свидетельство')
doctorWhoFillCertificateFirstName = Column(
String64, comment='Имя врача, заполнившего медицинское свидетельство')
doctorWhoFillCertificateMiddleName = Column(
String64,
comment='Отчество врача, заполнившего медицинское свидетельство')
@property
def doctorWhoFillCertificateName(self) -> str:
return combine_name(self.doctorWhoFillCertificateFamilyName,
self.doctorWhoFillCertificateFirstName,
self.doctorWhoFillCertificateMiddleName)
@declared_attr
def organisation_id(cls):
return Column(Integer,
ForeignKey('Organisation.id'),
primary_key=False,
comment='Организация, выдавшая сертификат')
@declared_attr
def organisation(cls):
return db.relationship('OrganisationModel',
foreign_keys=[cls.organisation_id])
@classmethod
def get_serial(cls, year=None):
""" Текущая серия сертификатов """
raise NotImplementedError
def init_from_user(self, user: UserModel, onlyOrganisation=False):
self.organisation_id = user.organisationId
if not onlyOrganisation:
self.doctorWhoFillCertificateFamilyName = user.familyName
self.doctorWhoFillCertificateFirstName = user.firstName
self.doctorWhoFillCertificateMiddleName = user.middleName
self.doctorWhoFillCertificatePost_id = user.post_id
head_user = OrganisationModel.get_head_user(user.organisationId)
if head_user is not None:
self.medicalOrganisationHeadFamilyName = head_user.familyName
self.medicalOrganisationHeadFirstName = head_user.firstName
self.medicalOrganisationHeadMiddleName = head_user.middleName
counter_code = ''
@classmethod
def update_serial_number(cls, cert_id, year=None):
""" Заполнение серии/номера в сертификате
номер должен заполняться счетчиком (текущий + 1),
выполняется update-запросом, чтобы при параллельных запросах на создание не было дублирования номера """
counter = CounterModel.get_counter(code=cls.counter_code, year=year)
table = cls.__table__
db.session.execute(table.update().where(table.c.id == cert_id).values(
serial=cls.get_serial(counter.year),
number=func.getCounterValue(counter.id)))
db.session.commit()
metadata = MetaData()
engine = create_engine("mysql+mysqldb://root:root@localhost/hello")
conn = engine.connect()
c = [
## date/time functions ##
#func.timeofday(), # for postgresql
func.localtime(),
func.current_timestamp(),
#func.date_part("month", func.now()), # for postgresql
func.now(),
## mathematical functions ##
func.pow(4, 2),
func.sqrt(441),
func.pi(),
func.floor(func.pi()),
func.ceil(func.pi()),
## string functions ##
func.lower("ABC"),
func.upper("abc"),
func.length("abc"),
func.trim(" ab c "),
#func.chr(65), # for postgresql
]
class Note(db.Model):
id = db.Column(db.Integer, primary_key=True)
data = db.Column(db.String(10000))
date = db.Column(db.DateTime(timezone=True), default=func.now())
# one user many notes
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
class Note(db.Model):
id = db.Column(db.Integer(), primary_key=True)
text = db.Column(db.String(100))
complete = db.Column(db.Boolean())
date = db.Column(db.DateTime(timezone=True), default=func.now())
TABLE: str = os.environ["TABLE"]
if TABLE not in ["flights", "positions"]:
raise ValueError(
f"Invalid TABLE env variable: {TABLE} - must be 'flights' or 'positions'"
)
if TABLE == "flights":
table = sa.Table(
"flights",
meta,
sa.Column("id", sa.String, primary_key=True),
sa.Column("added",
TIMESTAMP_TZ(),
nullable=False,
server_default=func.now()),
sa.Column(
"changed",
TIMESTAMP_TZ(),
nullable=False,
server_default=func.now(),
onupdate=func.now(),
),
sa.Column("flight_number", sa.String, key="ident"),
sa.Column("registration", sa.String, key="reg"),
sa.Column("atc_ident", sa.String, key="atcident"),
sa.Column("hexid", sa.String),
sa.Column("origin", sa.String, key="orig"),
sa.Column("destination", sa.String, key="dest"),
sa.Column("aircraft_type", sa.String, key="aircrafttype"),
sa.Column("filed_ground_speed", sa.Integer, key="gs"),
class Chapter(BaseModel):
__tablename__ = 'chapter'
id = Column(Integer, primary_key=True, autoincrement=True)
title = Column(String(50))
book_id = Column(Integer)
category_id = Column(Integer)
chapter_path = Column(String(255))
serial_num = Column(Integer)
crawl_url = Column(String(255))
create_time = Column(DateTime, default=func.now())
update_time = Column(DateTime, onupdate=datetime.now())
@classmethod
def get_by_serial_num_and_book(cls, serial_num, book_id):
session = db_session()
chapter = session.query(Chapter).filter(Chapter.serial_num == serial_num,
Chapter.book_id == book_id).scalar()
session.close()
return chapter
@classmethod
def add(cls, session, title, book_id, category_id, serial_num, chapter_path, crawl_url):
chapter = Chapter(title=title, book_id=book_id, category_id=category_id, chapter_path=chapter_path, serial_num=serial_num,
crawl_url=crawl_url)
session.add(chapter)
session.commit()
return chapter
@classmethod
def get_newest_chapter_by_book(cls, book_id):
session = db_session()
query = session.query(Chapter).filter(Chapter.book_id == book_id)
chapter = query.order_by('-serial_num').first()
return chapter
#
# class Content(BaseModel):
#
# __tablename__ = 'content'
#
# id = Column(Integer, primary_key=True, autoincrement=True)
# content = Column(Text)
# create_time = Column(DateTime, default=func.now())
# update_time = Column(DateTime, default=datetime.now())
#
# @classmethod
# def add(cls, content):
# session = db_session()
# content = Content(content=content)
# session.add(content)
# session.commit()
# return content
#
# def init_db():
# engine = create_engine(DB_CONNECT_STRING, echo=True)
# BaseModel.metadata.create_all(engine)
#
# init_db()
class BiosConfigList(Base):
__tablename__ = "tb_bios_config_list"
id = Column("id", Integer, primary_key=True)
list_cnt = Column(Integer, default=0, nullable=False)
register_dt = Column(DateTime(timezone=True), default=func.now())
class Order(SoftDeletionModel):
__tablename__ = "orders"
id = db.Column(db.Integer, primary_key=True)
identifier = db.Column(db.String, unique=True, default=get_new_order_identifier)
amount = db.Column(db.Float, nullable=False, default=0)
address = db.Column(db.String)
city = db.Column(db.String)
state = db.Column(db.String)
country = db.Column(db.String)
zipcode = db.Column(db.String)
company = db.Column(db.String)
tax_business_info = db.Column(db.String)
user_id = db.Column(db.Integer, db.ForeignKey('users.id', ondelete='SET NULL'))
event_id = db.Column(db.Integer, db.ForeignKey('events.id', ondelete='SET NULL'))
marketer_id = db.Column(db.Integer, db.ForeignKey('users.id', ondelete='SET NULL'))
created_at = db.Column(db.DateTime(timezone=True), default=func.now())
completed_at = db.Column(db.DateTime(timezone=True), nullable=True, default=None)
trashed_at = db.Column(db.DateTime(timezone=True), nullable=True, default=None)
transaction_id = db.Column(db.String)
paid_via = db.Column(db.String)
payment_mode = db.Column(db.String)
is_billing_enabled = db.Column(db.Boolean, nullable=False, default=False)
brand = db.Column(db.String)
exp_month = db.Column(db.Integer)
exp_year = db.Column(db.Integer)
last4 = db.Column(db.String)
stripe_token = db.Column(db.String)
paypal_token = db.Column(db.String)
status = db.Column(db.String, default='initializing')
cancel_note = db.Column(db.String, nullable=True)
order_notes = db.Column(db.String)
tickets_pdf_url = db.Column(db.String)
discount_code_id = db.Column(
db.Integer,
db.ForeignKey('discount_codes.id', ondelete='SET NULL'),
nullable=True,
default=None,
)
discount_code = db.relationship('DiscountCode', backref='orders')
event = db.relationship('Event', backref='orders')
user = db.relationship('User', backref='orders', foreign_keys=[user_id])
invoices = db.relationship("EventInvoice", backref='invoice_order')
marketer = db.relationship(
'User', backref='marketed_orders', foreign_keys=[marketer_id]
)
tickets = db.relationship("Ticket", secondary='orders_tickets', backref='order')
order_tickets = db.relationship("OrderTicket", backref='order')
def __repr__(self):
return '<Order %r>' % self.id
def get_invoice_number(self):
return (
'O' + str(int(time.mktime(self.created_at.timetuple()))) + '-' + str(self.id)
)
@property
def invoice_number(self):
return self.get_invoice_number()
@property
def tickets_count(self):
return sum(t.quantity for t in self.order_tickets)
@property
def is_free(self):
return self.payment_mode == 'free'
def get_revenue(self):
if self.amount:
return self.amount - min(
self.amount * (self.event.fee / 100.0), self.event.maximum_fee
)
else:
return 0.0
# Saves the order and generates and sends appropriate
# documents and notifications
def populate_and_save(self):
from app.api.orders import save_order
save_order(self)
class Record(db.Model):
id = db.Column(db.Integer, primary_key=True)
timestamp = db.Column(db.DateTime(timezone=True),
server_default=func.now())
entity = db.Column(db.String(20), nullable=False)
value = db.Column(db.Float, nullable=False)
class Person(db.Model):
"""Main Table"""
__tablename__ = 'corona__person'
uuid = db.Column(UUID(as_uuid=True),
unique=True,
server_default=text("uuid_generate_v4()"))
id = db.Column(db.Integer, index=True, primary_key=True)
# ---- Basic Person details ---- #
name = db.Column(db.String(64), index=True, nullable=False)
gender = db.Column(db.String(10), index=True, nullable=False)
age = db.Column(db.Integer, nullable=False)
address = db.Column(db.String(128), nullable=False)
town = db.Column(db.String(40), nullable=False)
phone = db.Column(db.Unicode(20), nullable=False)
location = db.Column(db.String(64), nullable=False)
coordinates = db.Column(JSON, nullable=False)
type_of_person = db.Column(db.String(15), index=True, nullable=False)
# ---- Meta data ---- #
created_at = db.Column(db.DateTime, index=True, server_default=func.now())
updated_at = db.Column(
db.DateTime, index=True,
server_default=func.now()) # ToDo: fix auto updation
# ---- Relationships ---- #
interaction_from = relationship('Association',
backref='suspect__interaction',
primaryjoin=(id == Association.suspect_id))
interaction_to = relationship('Association',
backref='patient__interaction',
primaryjoin=(id == Association.patient_id))
def to_json(self):
json_person = {
'id': 'Pta / cov / {}'.format(self.id),
'name': self.name,
'created_at': self.created_at,
'updated_at': self.updated_at
}
return json_person
def complete_json(self):
json_person = {
'id': 'Pta / cov / {}'.format(self.id),
'name': self.name,
'gender': self.gender,
'age': self.age,
'address': self.address,
'town': self.town,
'phone': self.phone,
'location': {
'value': self.location,
'coordinates': self.coordinates
},
'type_of_person': self.type_of_person,
'created_at': self.created_at,
'updated_at': self.updated_at
}
if self.type_of_person == 'suspect' and len(
self.interaction_from) != 0:
"""self.interaction_from is an array"""
json_person['category_of_suspect'] = self.interaction_from[
0].category_of_suspect
json_person['severity'] = self.interaction_from[0].severity
return json_person
# method tells Python how to print objects of this class
def __repr__(self):
return '<Person {}>'.format(self.id)
def time_created(cls):
return Column(DateTime(timezone=True), default=func.now())
class HyperLink(BaseModel):
# 表名
__tablename__ = 'res_hyper_link'
# 表结构
link_id = Column(Integer, primary_key=True)
link_type = Column(String(10))
link_name = Column(String(100))
link_val = Column(String(200))
is_valid = Column(String(1))
create_time = Column(TIMESTAMP, server_default=func.now())
create_user = Column(String(50))
modify_time = Column(TIMESTAMP, server_onupdate=func.now())
modify_user = Column(String(50))
def _uuid(self):
uid = md5('{}-{}'.format(self.link_id, self.__class__.__name__))
uname = u'关键词百科超链接'
task = {
'id': uid,
'name': uname,
'func': 'zpb.service.hyperlinkservice.GetBaikLink',
'args': (self.link_id, self.link_name),
'kwargs': {
'priority': 50
}
}
return task
@staticmethod
def _appendLink(linkname, linktype):
session = DBInstance.session
try:
link = session.query(HyperLink).filter(
and_(HyperLink.link_type == linktype,
HyperLink.link_name == linkname)).first()
finally:
session.close()
if not link:
link = HyperLink()
link.link_type = linktype
link.link_name = linkname
link.link_val = ''
link.is_valid = 'F'
link.create_time = datetime.today()
link.create_user = '******'
return link.save()
@staticmethod
def appendLink(linkid, linkval, isvalid):
session = DBInstance.session
try:
link = session.query(HyperLink).filter(
HyperLink.link_id == linkid).first()
finally:
session.close()
if link:
link.link_val = linkval
link.is_valid = isvalid
link.modify_time = datetime.today()
link.modify_user = '******'
return link.save()
@classmethod
def queryPending(cls):
session = DBInstance.session
try:
ret = []
rows = session.query(HyperLink).filter(
HyperLink.link_val == '').limit(20)
if rows:
for row in rows:
ret.append(row._uuid())
return ret
except BaseException as e:
session.rollback()
raise DBOperateError(currentFuncName(cls), e)
finally:
session.close()
def edit(self, content):
self.content = content
self.set_topics(
[word for word in content.split() if word.startswith('#')])
self.last_edit_date = func.now()
db.session.commit()
class User(UserMixin, db.Model):
id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(64), index=True, unique=True)
email = db.Column(db.String(120), index=True, unique=True)
phone_number = db.Column(db.String(64), index=True, unique=True)
isteacher = db.Column(db.Boolean, default=False)
password_hash = db.Column(db.String(128))
time_created = db.Column(db.DateTime(timezone=True),
server_default=func.now())
posts = db.relationship('Post', backref='author', lazy='dynamic')
about_me = db.Column(db.String(140))
last_seen = db.Column(db.DateTime, default=datetime.utcnow)
followed = db.relationship(
'User',
secondary=followers,
primaryjoin=(
followers.c.follower_id == id), #上述id赋值为关系表的follower_id，和粉丝id链接
secondaryjoin=(followers.c.followed_id == id), #上述id赋值给要关注的人，和大Vid链接
backref=db.backref('followers', lazy='dynamic'),
lazy='dynamic')
def follow(self, user):
if not self.is_following(user):
self.followed.append(user)
def unfollow(self, user):
if self.is_following(user):
self.followed.remove(user)
def is_following(self, user):
return self.followed.filter(
followers.c.followed_id == user.id).count() > 0
def followed_posts(self):
followed = Post.query.join(
followers, (followers.c.followed_id == Post.user_id)).filter(
followers.c.follower_id == self.id)
own = Post.query.filter_by(user_id=self.id)
return followed.union(own).order_by(Post.timestamp.desc())
def get_reset_password_token(self, expires_in=600):
return jwt.encode(
{
'reset_password': self.id,
'exp': time() + expires_in
},
current_app.config['SECRET_KEY'],
algorithm='HS256')
@staticmethod
def verify_reset_password_token(token):
try:
id = jwt.decode(token,
current_app.config['SECRET_KEY'],
algorithms=['HS256'])['reset_password']
except:
return
return User.query.get(id)
def __repr__(self):
return '<User {}>'.format(self.username)
#有哈希密码的表单添加下列方法
def set_password(self, password):
self.password_hash = generate_password_hash(password)
def check_password(self, password):
return check_password_hash(self.password_hash, password)
class Activity(CommentMixin, ReactMixin, BaseModel):
kind = config.K_ACTIVITY
user_id = Column(Integer())
target_id = Column(Integer())
target_kind = Column(Integer())
created_at = Column(DateTime, server_default=func.now(), nullable=False)
@classmethod
@cache(MC_KEY_ACTIVITIES % '{page}')
async def get_multi_by(cls, page: int = 1) -> List[Dict]:
items = []
queryset = await cls.async_all(offset=(page - 1) * PER_PAGE,
limit=PER_PAGE,
order_by='created_at',
desc=True)
for data in queryset:
try:
items.append(await cls(**data).to_full_dict())
except:
continue
return items
@cached_property
async def target(self) -> dict:
kls = None
if self.target_kind == config.K_POST:
kls = Post
data = await kls.cache(ident=self.target_id)
elif self.target_kind == config.K_STATUS:
kls = Status
data = await kls.cache(id=self.target_id)
if kls is None:
return
return await kls(**data).to_async_dict(**data)
@property
async def action(self):
action = None
if self.target_kind == config.K_STATUS:
target = await self.target
attachments = target.attachments
if attachments:
layout = attachments[0]['layout']
if layout == Attachment.LAYOUT_LINK:
action = '分享网页'
elif layout == Attachment.LAYOUT_PHOTO:
action = f'上传了{len(attachments)}张照片'
elif layout == Attachment.LAYOUT_VIDEO:
action = f'上传了{len(attachments)}个视频'
elif '```' in target.content:
action = '分享了代码片段'
elif self.target_kind == config.K_POST:
action = '写了新文章'
if action is None:
action = '说'
return action
@property
async def attachments(self):
if self.target_kind == config.K_STATUS:
target = await self.target
attachments = target.attachments
elif self.target_kind == config.K_POST:
target = await self.target
attachments = [
asdict(
Link(url=target.url,
title=target.title,
abstract=target.summary))
]
else:
attachments = []
return attachments
@property
async def user(self) -> dict:
return await User.cache(id=self.user_id)
@classmethod
# @cache(MC_KEY_ACTIVITY_COUNT)
async def count(cls) -> int:
data = await cls.async_all()
return len(data)
@cache(MC_KEY_ACTIVITY_FULL_DICT % '{self.id}')
async def to_full_dict(self) -> Dict[str, Any]:
target = await self.target
if not target:
return {}
user = await self.user
if self.target_kind == config.K_STATUS:
target['url'] = ''
elif self.target_kind == config.K_POST:
pass
avatar = user['avatar']
if avatar:
domain = config.CDN_DOMAIN if config.CDN_DOMAIN and not config.DEBUG else ''
avatar = f'{domain}/static/upload/{avatar}'
attachments = await self.attachments
return {
'id': self.id,
'user': {
'name': user['name'],
'avatar': avatar
},
'target': target,
'action': await self.action,
'created_at': self.created_at,
'attachments': attachments,
'can_comment': False, # contemporary
'layout': attachments[0]['layout'] if attachments else '',
}
async def clear_mc(self):
total = await self.count()
page_count = math.ceil(total / PER_PAGE)
keys = [MC_KEY_ACTIVITIES % p for p in range(1, page_count + 1)]
keys.extend(
[MC_KEY_ACTIVITY_COUNT, MC_KEY_ACTIVITY_FULL_DICT % self.id])
await clear_mc(*keys)
class User(BaseModel):
profile_id = db.Column(UUID(), db.ForeignKey('profile.id', ondelete='CASCADE'), nullable=False)
profile = relationship("Profile", backref=backref("user", uselist=False))
email = db.Column(db.String, unique=True)
password_hash = db.Column(db.String)
is_active = db.Column(db.Boolean, default=False)
admin = db.Column(db.Boolean, default=False)
last_login = db.Column(db.DateTime(timezone=True), server_default=func.now())
friends = relationship(
"User",
secondary=friendships,
primaryjoin="User.id == friendships.c.user_id",
secondaryjoin="User.id == friendships.c.friend_id",
backref="friended_you")
def __repr__(self):
return self.email
@classmethod
def create(cls, email, password=None):
user = User.query.filter_by(email=email).first()
if user:
raise Exception('duplicate user create')
username = email.split('@')[0]
profile = Profile(
username=username
)
user = User(
email=email,
profile=profile,
)
user.set_password(password)
db.session.add(user)
db.session.commit()
return user
def set_password(self, password):
if not password:
# Generate a random password for social users.
print("Generating random password for %s" % self.email)
password = bcrypt.gensalt().decode('utf-8')
print('Password length', len(password))
hashed = bcrypt.hashpw(password.encode('utf-8'), bcrypt.gensalt())
# This needs to be stored as a string, not bytes.
self.password_hash = hashed.decode('utf-8')
def check_password(self, password_attempt):
match = bcrypt.hashpw(password_attempt.encode('utf-8'), self.password_hash.encode('utf-8')) == self.password_hash.encode('utf-8')
if match:
return True
print('Password check failed')
return False
# For flask login
@property
def is_authenticated(self):
return True
def get_id(self):
return str(self.id)
def get_preferred_timezone(self):
# TODO should get from profile.
# local_tz = pytz.timezone(current_user.profile.timezone)
return pytz.timezone('Pacific/Auckland')
class Book(BaseModel):
__tablename__ = 'book'
id = Column(Integer, primary_key=True, autoincrement=True)
title = Column(String(50))
avatar = Column(String(255))
info = Column(Text)
author = Column(String(50))
category_id = Column(Integer)
book_update_time = Column(DateTime)
status = Column(Integer) # 0代表连载中,1代表已完本
crawl_url = Column(String(255))
create_time = Column(DateTime, default=func.now())
update_time = Column(DateTime, onupdate=datetime.now())
@classmethod
def exists_book(cls, title, author):
session = db_session()
q = session.query(Book).filter_by(title = title, author = author)
return session.query(q.exists())
@classmethod
def get_by_title_and_author(cls, title, author):
session = db_session()
book = session.query(Book).filter(Book.title == title,
Book.author == author).scalar()
return book
@classmethod
def add(cls, title, avatar, author, info, category_id, book_update_time, crawl_url, status):
session = db_session()
if avatar:
md5_res = hashlib.new('md5', '%s%s%s' % (title.encode('utf-8'), author, datetime.now().strftime('%Y-%m-%d %H:%M:%S')))
avatar_file_name = 'cover/%s' % md5_res.hexdigest()
else:
avatar_file_name = ''
book = Book(title=title, avatar=avatar_file_name, author=author, info=info, category_id=category_id,
book_update_time=book_update_time, crawl_url=crawl_url)
session.add(book)
try:
session.commit()
except:
session.rollback()
if avatar:
bucket.fetch(avatar, bucket_name, key=avatar_file_name)
return book
@classmethod
def update(cls, title, avatar, author, info, category_id, book_update_time, crawl_url, status):
session = db_session()
query = session.query(Book)
query.filter(Book.title == title, Book.author == author).update({
Book.info: info,
Book.category_id: category_id,
Book.book_update_time: book_update_time,
Book.status: status,
})
try:
session.commit()
except:
session.rollback()
book = session.query(Book).filter(Book.title == title,
Book.author == author).scalar()
return book
class SectionTypeModel(base.Model):
__tablename__ = "section_types"
id = base.Column(base.Integer, primary_key = True)
type = base.Column(base.Text, nullable = False, unique = True)
addedOn = base.Column(base.DateTime, server_default = func.now())
class ScrapedJob(Base):
__tablename__ = 'scraped-jobs'
id = Column(UUID, server_default=func.gen_random_uuid())
url = Column(Text, nullable=False, primary_key=True)
created_at = Column(TIMESTAMP(timezone=True),
nullable=False,
default=func.now())
last_modified = Column(TIMESTAMP(timezone=True),
nullable=False,
default=func.now())
data = Column(JSONB, nullable=False)
def __repr__(self):
return '<Job created_at: {self.created_at}' \
' last_modified: {self.last_modified}' \
' url: {self.url!r}' \
'>'.format(self=self)
@classmethod
def from_dict(cls, input_dict):
"""
Used to instantiate a `ScrapedJob` instance from the data yielded by a Scrapy spider.
Args:
input_dict (dict): the Scrapy item
Returns:
obj:`ScrapedJob`: instance of the `ScrapedJob` class
"""
instance = cls()
if 'url' in input_dict:
instance.url = input_dict['url']
instance.data = input_dict
return instance
@hybrid_property
def posted(self):
"""
Extracts the timestamp of the posting from the data blob. If it is missing, or the data blob hasn't been set
we get None instead.
Returns:
datetime.datetime or None: posting date
"""
if self.data is None or 'posted' not in self.data:
return None
return dateutil.parser.parse(self.data['posted'])
@posted.expression
def posted(cls):
return cast(cls.data.op('->>')('posted'), TIMESTAMP(timezone=True))
@hybrid_property
def date_of_posting(self):
"""
Returns the date of the posting, as a YYYY-MM-DD string.
Returns:
str: date of posting
"""
posting = self.posted
if posting is not None:
return posting.date()
return None
@date_of_posting.expression
def date_of_posting(cls):
return cast(func.date_trunc('day', cls.posted), DATE)
@hybrid_property
def spider(self):
"""
Extracts the spider name from the data blob. If the spider field isn't present, or there is not data blob,
we get a None instead.
Returns:
string or None: the name of the spider
"""
if self.data is None or 'spider' not in self.data:
return None
return self.data['spider']
@spider.expression
def spider(cls):
return cls.data.op('->>')('spider')
def as_dict(self):
"""
Flattens the fields of the instance into a single dict. This is useful for returning the data in the API,
since all fields are included.
Returns:
dict: all the fields in the `ScrapedJob` instance, flattened
"""
contents = dict()
if self.data:
contents.update(self.data)
for key in ('created_at', 'last_modified'):
contents[key] = value = getattr(self, key)
if value is not None:
contents[key] = getattr(self, key).isoformat()
contents['id'] = self.id
return contents
import os
from databases import Database
from sqlalchemy import (create_engine, MetaData, Column, Table, Integer,
String, DateTime)
from sqlalchemy.sql import func
DATABASE_URL = os.getenv('DATABASE_URL')
engine = create_engine(DATABASE_URL)
metadata = MetaData()
notes = Table(
'notes', metadata, Column('id', Integer, primary_key=True),
Column('title', String(50)), Column('description', String(50)),
Column('created_date', DateTime, default=func.now(), nullable=False))
database = Database(DATABASE_URL)