def _db_update(self, ok, msg):
db = self._db_manager.get_db()
try:
while True:
try:
if ok:
if msg['message_name'] == 'ExecScript':
db.messages.filter(
db.messages.messageid == msg['messageid']).delete()
else:
db.messages.filter(db.messages.messageid == msg['messageid']).update({
'status': 1,
'message': '',
'dtlasthandleattempt': func.now()},
synchronize_session=False)
if msg['event_id']:
db.events.filter(db.events.event_id == msg['event_id']).update({
db.events.msg_sent: db.events.msg_sent + 1})
else:
db.messages.filter(db.messages.messageid == msg['messageid']).update({
'status': 0 if msg['handle_attempts'] < 2 else 3,
'handle_attempts': msg['handle_attempts'] + 1,
'dtlasthandleattempt': func.now()},
synchronize_session=False)
db.session.commit()
break
except db_exc.SQLAlchemyError:
db.session.remove()
LOG.error(helper.exc_info())
time.sleep(5)
finally:
db.session.remove()
def active_duration_range(query, minimum_active_duration,
maximum_active_duration):
if minimum_active_duration is not None:
minimum_active_duration = datetime.timedelta(
seconds=minimum_active_duration)
finished_claims = query.filter(
models.Claim.activated != None,
models.Claim.deactivated != None,
models.Claim.deactivated - models.Claim.activated
>= minimum_active_duration)
active_claims = query.filter(
models.Claim.activated != None,
models.Claim.deactivated == None,
func.now() - models.Claim.activated
>= minimum_active_duration)
query = finished_claims.union(active_claims)
if maximum_active_duration is not None:
maximum_active_duration = datetime.timedelta(
seconds=maximum_active_duration)
finished_claims = query.filter(
models.Claim.activated != None,
models.Claim.deactivated != None,
models.Claim.deactivated - models.Claim.activated
<= maximum_active_duration)
active_claims = query.filter(
models.Claim.activated != None,
models.Claim.deactivated == None,
func.now() - models.Claim.activated
<= maximum_active_duration)
query = finished_claims.union(active_claims)
return query
def upgrade(migrate_engine):
metadata.bind = migrate_engine
instance_table = Table(
"instance",
metadata,
Column("id", Integer, primary_key=True),
Column("key", Unicode(20), nullable=False, unique=True),
Column("label", Unicode(255), nullable=False),
Column("description", UnicodeText(), nullable=True),
Column("required_majority", Float, nullable=False),
Column("activation_delay", Integer, nullable=False),
Column("create_time", DateTime, default=func.now()),
Column("access_time", DateTime, default=func.now(), onupdate=func.now()),
Column("delete_time", DateTime, nullable=True),
Column("creator_id", Integer, ForeignKey("user.id"), nullable=False),
Column("default_group_id", Integer, ForeignKey("group.id"), nullable=True),
Column("allow_adopt", Boolean, default=True),
Column("allow_delegate", Boolean, default=True),
Column("allow_propose", Boolean, default=True),
Column("allow_index", Boolean, default=True),
Column("hidden", Boolean, default=False),
Column("locale", Unicode(7), nullable=True),
Column("css", UnicodeText(), nullable=True),
Column("frozen", Boolean, default=False),
Column("milestones", Boolean, default=False),
Column("use_norms", Boolean, nullable=True, default=True),
Column("require_selection", Boolean, nullable=True, default=False),
Column("is_authenticated", Boolean, nullable=True, default=False),
)
hide_categories = Column("hide_global_categories", Boolean, nullable=True, default=False)
hide_categories.create(instance_table)
u = instance_table.update(values={"hide_global_categories": False})
migrate_engine.execute(u)
def get_dag_duration_info():
'''get duration of currently running DagRuns
:return dag_info
'''
driver = Session.bind.driver
durations = {
'pysqlite': func.sum(
(func.julianday(func.current_timestamp()) - func.julianday(DagRun.start_date)) * 86400.0
),
'mysqldb': func.sum(func.timestampdiff(text('second'), DagRun.start_date, func.now())),
'default': func.sum(func.now() - DagRun.start_date)
}
duration = durations.get(driver, durations['default'])
with session_scope(Session) as session:
return session.query(
DagRun.dag_id,
DagRun.run_id,
duration.label('duration')
).group_by(
DagRun.dag_id,
DagRun.run_id
).filter(
DagRun.state == State.RUNNING
).all()
def whereInEquipement(self,fullQueryJoin,criteria):
sensorObj = list(filter(lambda x:'FK_Sensor'==x['Column'], criteria))[0]
sensor = sensorObj['Value']
table = Base.metadata.tables['MonitoredSiteEquipment']
joinTable = outerjoin(table,Sensor, table.c['FK_Sensor'] == Sensor.ID)
if sensorObj['Operator'].lower() in ['is','is not'] and sensorObj['Value'].lower() == 'null':
subSelect = select([table.c['FK_MonitoredSite']]
).select_from(joinTable).where(
and_(MonitoredSite.ID== table.c['FK_MonitoredSite']
,or_(table.c['EndDate'] >= func.now(),table.c['EndDate'] == None)
))
if sensorObj['Operator'].lower() == 'is':
fullQueryJoin = fullQueryJoin.where(~exists(subSelect))
else :
fullQueryJoin = fullQueryJoin.where(exists(subSelect))
else :
subSelect = select([table.c['FK_MonitoredSite']]
).select_from(joinTable).where(
and_(MonitoredSite.ID== table.c['FK_MonitoredSite']
,and_(eval_.eval_binary_expr(Sensor.UnicIdentifier,sensorObj['Operator'],sensor)
,or_(table.c['EndDate'] >= func.now(),table.c['EndDate'] == None))
))
fullQueryJoin = fullQueryJoin.where(exists(subSelect))
return fullQueryJoin
def upgrade(migrate_engine):
meta.bind = migrate_engine
instance_table = Table('instance', meta,
Column('id', Integer, primary_key=True),
Column('key', Unicode(20), nullable=False, unique=True),
Column('label', Unicode(255), nullable=False),
Column('description', UnicodeText(), nullable=True),
Column('required_majority', Float, nullable=False),
Column('activation_delay', Integer, nullable=False),
Column('create_time', DateTime, default=func.now()),
Column('access_time', DateTime, default=func.now(), onupdate=func.now()),
Column('delete_time', DateTime, nullable=True),
Column('creator_id', Integer, ForeignKey('user.id'), nullable=False),
Column('default_group_id', Integer, ForeignKey('group.id'), nullable=True),
Column('allow_adopt', Boolean, default=True),
Column('allow_delegate', Boolean, default=True),
Column('allow_index', Boolean, default=True),
Column('hidden', Boolean, default=False),
Column('locale', Unicode(7), nullable=True),
Column('css', UnicodeText(), nullable=True),
Column('use_norms', Boolean, nullable=True, default=True)
)
propose = Column('allow_propose', Boolean, default=True)
propose.create(instance_table)
u = instance_table.update(values={'allow_propose': True})
migrate_engine.execute(u)
def run(self):
import time
from v2ex.service.fetcher_manager import FetcherManager
while not self._stop_flag:
time.sleep(1)
nid = FetcherManager.get_task()
ntf_token = NtfTokenDao.get_from_nid(nid)
if ntf_token is None:
FetcherManager.remove_task(nid)
continue
if self.should_check_update(ntf_token):
if self.has_update(ntf_token):
logging.debug("found update for %s", ntf_token.token)
self.gcm_send(ntf_token.uid, gcm.send_notify)
else:
logging.debug("not found update for %s", ntf_token.token)
ntf_token.last_fetch_time = func.now()
db.get_session().commit()
elif self.should_ping(ntf_token):
self.gcm_send(ntf_token.uid, gcm.send_ping)
ntf_token.last_ping_time = func.now()
db.get_session().commit()
def advance(session, group_id, station_id):
state = GroupStation.get(group_id, station_id)
# The first state to set - if there is nothing yet - is "ARRIVED"
if not state:
group = Group.one(id=group_id)
station = Station.one(id=station_id)
state = GroupStation(group_id, station_id)
state.state = STATE_ARRIVED
session.add(state)
return STATE_ARRIVED
group = state.group
station = state.station
if state.state == STATE_UNKNOWN:
state.state = STATE_ARRIVED
elif state.state == STATE_ARRIVED:
if not group.departure_time and station.is_start:
group.departure_time = func.now()
if not group.finish_time and station.is_end:
group.finish_time = func.now()
group.completed = True
state.state = STATE_FINISHED
elif state.state == STATE_FINISHED:
state.state = STATE_UNKNOWN
else:
raise ValueError('%r is not a valid state!' % state.state)
return state.state
def get_delay(self, update):
"""Return the minutes to delay the viewing of submission results.
Only store information into the datebase when `update` is set.
"""
if hasattr(self, '_delay'):
return self._delay
now = datetime.now(UTC())
zero = timedelta(0)
delay = self.project.delay - (now - self.created_at)
if delay <= zero: # Never delay longer than the project's delay time
self._delay = None
elif self.group.viewed_at is None: # Don't delay
if update:
self.group.viewed_at = func.now()
self._delay = None
elif self.created_at <= self.group.viewed_at: # Show older results
self._delay = None
else:
pv_delay = self.project.delay - (now - self.group.viewed_at)
if pv_delay <= zero:
if update: # Update the counter
self.group.viewed_at = func.now()
self._delay = None
else:
self._delay = min(delay, pv_delay).total_seconds() / 60
return self._delay
def group(self, group, current=None):
# Filter by group
sub_query = db.session.query(GroupPatient)
sub_query = sub_query.filter(GroupPatient.group == group, GroupPatient.patient_id == Patient.id)
if current is not None:
if current:
# Between from and to date
sub_query = sub_query.filter(
GroupPatient.from_date <= func.now(),
or_(
GroupPatient.to_date == null(),
GroupPatient.to_date >= func.now(),
)
)
else:
# Outside from or to date
sub_query = sub_query.filter(or_(
GroupPatient.from_date > func.now(),
and_(
GroupPatient.to_date != null(),
GroupPatient.to_date < func.now()
)
))
self.query = self.query.filter(sub_query.exists())
return self
def _recover_delete_revisions(revisions_table, event,
last_available_revision_ids):
"""Log an action="deleted" copy for revisions with ids in passed list."""
if not last_available_revision_ids:
return
columns_to_clone = sorted(set(c.name for c in revisions_table.c) -
{"id", "event_id", "action", "created_at",
"updated_at"})
db.session.execute(
revisions_table.insert().from_select(
["event_id",
"action",
"created_at",
"updated_at"] + columns_to_clone,
select(
[event.id,
literal("deleted"),
func.now(),
func.now()] + columns_to_clone,
).select_from(
revisions_table,
).where(
revisions_table.c.id.in_(last_available_revision_ids),
),
),
)
def set_group_state(session, group_id, station_id, new_state):
"""
Updates the group state for a given station
"""
station = session.query(Station).filter_by(id=station_id).first()
if not station:
LOG.debug('No station found with ID %r', station_id)
return False
group = session.query(Group).filter_by(id=group_id).first()
if not group:
LOG.debug('No group found with ID %r', group_id)
return False
if new_state == STATE_FINISHED:
if not group.departure_time and station.is_start:
group.departure_time = func.now()
if not group.finish_time and station.is_end:
group.finish_time = func.now()
group.completed = True
state = GroupStation(
group_id=group_id,
station_id=station_id,
state=new_state)
state = session.merge(state)
session.flush()
return state
def modified(self):
"""
Modification Timestamp
"""
from .types.date import DateTime
return DateTime(server_default=func.now(), server_onupdate=func.now(), info=dict(readonly=True))
def __init__(self, master, id_num):
session = Session()
frame = Frame(master)
frame.pack()
member = session.query(Member).filter_by(mid=id_num).first()
topline = "%s\n[%s]" % (member.name, str(id_num))
nameline = Label(frame, text=topline, bg="black", fg="white", font="Monospace 30")
nameline.pack(fill=X)
lastLog = session.query(CheckIn).filter_by(mid=id_num).filter_by(timeOut=None)
if lastLog.count() == 0:
statusline = Label(frame, text="CHECKED IN", bg="black", fg="green", font="Monospace 30")
statusline.pack(fill=X)
newLog = CheckIn(mid=id_num, timeIn=func.now(), timeOut=None)
session.add(newLog)
else:
lastLog = lastLog.first()
statusline = Label(frame, text="CHECKED OUT", bg="black", fg="red", font="Monospace 30")
statusline.pack(fill=X)
lastLog.timeOut = func.now()
if os.path.isfile("images/%s.png" % str(id_num).replace("'", '')):
img = PIL.Image.open("images/%s.png" % str(id_num).replace("'", ''))
photo = PIL.ImageTk.PhotoImage(img)
picture = Label(frame, image=photo)
picture.image = photo
picture.pack()
session.commit()
session.close()
def send_profile_visitor_email(**kwargs):
template = email_template_env.get_template('profile_visitors.html')
senders = AccountUser.query.filter(
extract('dow', AccountUser.date_added) == extract('dow', func.now()),
extract('hour', AccountUser.date_added) == extract('hour', func.now()),
).join(
AccountUserVisit,
(AccountUserVisit.profile_user_id == AccountUser.id) &
(AccountUserVisit.notified == False)
)
for sender in senders:
visitors = AccountUserVisit.get_all_visits_in_last_7_days(sender, 5)
visitors = [AccountUserVisit.visit_item(*v) for v in visitors.all()]
if visitors:
body = template.render(
sender=sender,
visitors=visitors,
**kwargs
)
send_email(sender.username, body)
try:
db.session.query(AccountUserVisit).filter_by(profile_user_id=sender.id).update({"notified": True})
db.session.commit()
except:
db.session.rollback()
raise
def __init__(self):
self.create_date =func.now()
self.write_date = func.now()
self.write_uid = 1
obj_conexion = configuracion()
engine=create_engine(obj_conexion.config())
Session= sessionmaker(bind=engine)
self.session=Session()
def timestamp_mixin(cls):
"""
Model column: timestamp mixin decorator.
"""
cls.created_at = Column(DateTime, default=func.now(), nullable=False)
cls.updated_at = Column(DateTime, default=func.now(), onupdate=func.now(), nullable=False)
return cls
def active_filter(columns):
if not is_nonstr_iter(columns):
columns = [columns]
and_queries = []
for column in columns:
and_queries.append(or_(column.start_date <= func.now(), column.start_date.is_(None)))
and_queries.append(or_(column.end_date > func.now(), column.end_date.is_(None)))
return and_(*and_queries)
def active_filter(tables):
if not is_nonstr_iter(tables):
tables = [tables]
and_queries = []
for table in tables:
and_queries.append(or_(table.start_date <= func.now(), table.start_date.is_(None)))
and_queries.append(or_(table.end_date > func.now(), table.end_date.is_(None)))
return and_(*and_queries)
def _perform_sql(self):
filter = {}
filter["last_check"] = "now()"
query = self.session.query(self.tables.monitor_obs)\
.add_column((func.datediff(self.tables.monitor_obs.last_check, func.now())).label("test"))\
.filter(self.tables.monitor_obs.locked == False)\
.filter(func.datediff(self.tables.monitor_obs.last_check, func.now()) <= self.tables.monitor_obs.frequency) \
.order_by('last_check')
logger.debug(query)
def test_generic_now(self):
assert isinstance(func.now().type, sqltypes.DateTime)
for ret, dialect in [
('CURRENT_TIMESTAMP', sqlite.dialect()),
('now()', postgresql.dialect()),
('now()', mysql.dialect()),
('CURRENT_TIMESTAMP', oracle.dialect())
]:
self.assert_compile(func.now(), ret, dialect=dialect)
def test_extract(self):
fivedaysago = testing.db.scalar(select([func.now()])) - \
datetime.timedelta(days=5)
for field, exp in ('year', fivedaysago.year), \
('month', fivedaysago.month), ('day', fivedaysago.day):
r = testing.db.execute(
select([
extract(field, func.now() + datetime.timedelta(days=-5))])
).scalar()
eq_(r, exp)
def downgrade():
### commands auto generated by Alembic - please adjust! ###
op.add_column('contract', sa.Column('service_id', postgresql.UUID(), nullable=True))
op.create_table('service',
sa.Column('id', postgresql.UUID(), server_default=func.uuid_generate_v4(), autoincrement=False, nullable=False),
sa.Column('created', postgresql.TIMESTAMP(timezone=True), server_default=func.now(), autoincrement=False, nullable=False),
sa.Column('updated', postgresql.TIMESTAMP(timezone=True), server_default=func.now(), autoincrement=False, nullable=False),
sa.Column('name', sa.TEXT(), autoincrement=False, nullable=False),
sa.Column('description', sa.TEXT(), autoincrement=False, nullable=True),
sa.PrimaryKeyConstraint('id', name=u'service_pkey')
)
def table(name, *args, **kwargs):
return Table(
name,
metadata,
Column('uuid', UUID(as_uuid=True), primary_key=True, server_default=text('gen_random_uuid()')),
Column('created', DateTime, server_default=func.now(), nullable=False),
Column('updated', DateTime, server_default=func.now(), onupdate=func.now(), nullable=False),
Column('deleted', DateTime, nullable=True),
*args,
**kwargs
)
def is_in_notification_window(self, exclude_first_day=False):
from indico.modules.rb import settings as rb_settings
from indico.modules.rb.models.rooms import Room
in_the_past = cast(self.start_dt, Date) < cast(func.now(), Date)
days_until_occurrence = cast(self.start_dt, Date) - cast(func.now(), Date)
notification_window = func.coalesce(Room.notification_before_days,
rb_settings.get('notification_before_days', 1))
if exclude_first_day:
return (days_until_occurrence < notification_window) & ~in_the_past
else:
return (days_until_occurrence <= notification_window) & ~in_the_past
def _connectToDatabase(self):
try:
self.config = utils.get_config("MysqlLogger")
db_name = self.config.get("db_name")
db_user = self.config.get("db_user")
db_pass = self.config.get("db_password")
db_host = self.config.get("db_host")
try:
db_type = self.config.get("db_type")
except:
db_type = "mysql"
except Exception:
print "Could not load MysqlLogger"
#self.disabled = True
#DbConnect
self.db_engine = create_engine(
'' + db_type + '://' + db_user + ':' + db_pass + '@' + db_host +
'/' + db_name
)
self.db_metadata = MetaData()
self.db_chanlog = Table(
'irc_chanlog', self.db_metadata,
Column('ID', Integer, primary_key=True),
Column('channel', String(14)),
Column('sender', String(14), nullable=False),
Column('sender_user', String(40)),
Column('message', TEXT),
Column(
'timestamp',
TIMESTAMP,
nullable=False,
default=func.now()
)
)
self.db_chanevs = Table(
'irc_chanevs', self.db_metadata,
Column('ID', Integer, primary_key=True),
Column('channel', String(14)),
Column('sender', String(14), nullable=False),
Column('sender_user', String(40)),
Column('evtype', String(10)),
Column(
'timestamp',
TIMESTAMP,
nullable=False,
default=func.now()
)
)
try:
self.db_conn = self.db_engine.connect()
except OperationalError:
print 'Nedalo sa mi pripojit sa do databazy'
def ttl_range(query, minimum_ttl, maximum_ttl):
if minimum_ttl is not None or maximum_ttl is not None:
query = query.filter_by(status='active').join(models.Lock)
if minimum_ttl is not None:
query = query.filter(models.Lock.expiration_time - func.now()
>= datetime.timedelta(seconds=minimum_ttl))
if maximum_ttl is not None:
query = query.filter(models.Lock.expiration_time - func.now()
<= datetime.timedelta(seconds=maximum_ttl))
return query
def cleanup_expired_exports():
settings = app.conf["PYRAMID_REGISTRY"].settings
with db_session() as db:
group(
delete_expired_export.s(_.chat_id, _.user_id)
for _ in db.query(ChatExport).filter(ChatExport.expires < func.now())
).delay()
group(
delete_expired_export.s(_.chat_id, _.user_id)
for _ in db.query(ChatExport).filter(and_(
ChatExport.generated == None,
ChatExport.triggered < (func.now() - datetime.timedelta(settings["export.expiry_days"])),
))
).delay()
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.create_table(u'content_stream',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('created_at', sa.DateTime(), server_default=func.now(),nullable=False),
sa.Column('name', sa.String(length=100), nullable=False),
sa.Column('uri', sa.String(length=200), nullable=False),
sa.Column('description', sa.String(length=1000), nullable=True),
sa.Column('ok_to_play', sa.Boolean(), nullable=True, default=True),
sa.Column('created_by', sa.Integer(), nullable=True),
sa.Column('updated_at', sa.DateTime(timezone=True), server_default=func.now(), nullable=True),
sa.ForeignKeyConstraint(['created_by'], ['user_user.id'], ),
sa.PrimaryKeyConstraint('id')
)
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.add_column(u'content_communitymenu', sa.Column('date_created', sa.DateTime(timezone=True), server_default=func.now(), nullable=True))
op.alter_column(u'content_communitymenu', 'updated_at',
existing_type=postgresql.TIMESTAMP(),
nullable=True, server_default=func.now())
op.add_column(u'content_music', sa.Column('date_created', sa.DateTime(timezone=True), server_default=func.now(), nullable=True))
op.add_column(u'content_musicalbum', sa.Column('date_created', sa.DateTime(timezone=True), server_default=func.now(), nullable=True))
op.add_column(u'content_musicartist', sa.Column('date_created', sa.DateTime(timezone=True), server_default=func.now(), nullable=True))
op.add_column(u'content_musicplaylist', sa.Column('date_created', sa.DateTime(timezone=True), server_default=func.now(), nullable=True))
op.add_column(u'content_uploads', sa.Column('date_created', sa.DateTime(timezone=True), server_default=func.now(), nullable=True))
op.alter_column(u'content_uploads', 'updated_at',
existing_type=postgresql.TIMESTAMP(),
nullable=True, server_default=func.now())
class PreassemblyUpdates(Base, IndraDBTable):
__tablename__ = 'preassembly_updates'
_always_disp = ['corpus_init', 'run_datetime']
id = Column(Integer, primary_key=True)
corpus_init = Column(Boolean, nullable=False)
run_datetime = Column(DateTime, default=func.now())
def _check_running_runners(self):
with get_session(self.db_engine) as session:
running_runners = session.query(SchedulerRunner).filter_by(
status=RunnerStatus.RUNNING.value).all()
for runner in running_runners:
if self.thread_reaper.is_full():
return
lock_name = f'check_running_runner_{runner.id}_lock'
check_lock = OpLocker(lock_name, self.db_engine).try_lock()
if not check_lock:
logging.error(f'[composer] failed to lock, '
f'ignore current running_runner_{runner.id}')
continue
# TODO: restart runner if exit unexpectedly
pipeline = Pipeline(**(json.loads(runner.pipeline)))
output = json.loads(runner.output)
context = decode_context(val=runner.context,
db_engine=self.db_engine)
current = context.internal['current']
runner_fn = self.runner_cache.find_runner(runner.id, current)
# check status of current one
status, current_output = runner_fn.result(context)
if status == RunnerStatus.RUNNING:
continue # ignore
if status == RunnerStatus.DONE:
output[current] = {'status': RunnerStatus.DONE.value}
context.set_internal(f'output_{current}', current_output)
current_idx = pipeline.deps.index(current)
if current_idx == len(pipeline.deps) - 1: # all done
runner.status = RunnerStatus.DONE.value
runner.end_at = func.now()
else: # run next one
next_one = pipeline.deps[current_idx + 1]
output[next_one] = {
'status': RunnerStatus.RUNNING.value
}
context.set_internal('current', next_one)
next_runner_fn = self.runner_cache.find_runner(
runner.id, next_one)
self.thread_reaper.enqueue(name=lock_name,
fn=next_runner_fn,
context=context)
elif status == RunnerStatus.FAILED:
# TODO: abort now, need retry
output[current] = {'status': RunnerStatus.FAILED.value}
context.set_internal(f'output_{current}', current_output)
runner.status = RunnerStatus.FAILED.value
runner.end_at = func.now()
runner.pipeline = PipelineEncoder().encode(pipeline)
runner.output = json.dumps(output)
runner.context = ContextEncoder().encode(context)
updated_db = False
try:
logging.info(
f'[composer] update runner, status: {runner.status}, '
f'pipeline: {runner.pipeline}, '
f'output: {output}, context: {runner.context}')
if check_lock.is_latest_version():
if check_lock.update_version():
session.commit()
updated_db = True
else:
logging.error(f'[composer] {lock_name} is outdated, '
f'ignore updates to database')
except Exception as e: # pylint: disable=broad-except
logging.error(f'[composer] failed to update running '
f'runner status, exception: {e}')
session.rollback()
# delete useless runner obj in runner cache
if status in (RunnerStatus.DONE,
RunnerStatus.FAILED) and updated_db:
self.runner_cache.del_runner(runner.id, current)
class ReportStore(db.WikimetricsBase):
"""
Stores each report node that runs in a report node tree to the database.
Stores the necessary information to fetch the results from Celery as
well as to re-run the node.
"""
__tablename__ = 'report'
id = Column(Integer, primary_key=True)
created = Column(DateTime, default=func.now())
user_id = Column(Integer)
queue_result_key = Column(String(50))
result_key = Column(String(50))
status = Column(String(50))
name = Column(String(2000))
show_in_ui = Column(Boolean)
parameters = Column(VARBINARY(4000))
public = Column(Boolean)
recurrent = Column(Boolean, default=False, nullable=False)
recurrent_parent_id = Column(Integer, ForeignKey('report.id'))
__table_args__ = (UniqueConstraint(recurrent_parent_id,
created,
name='uix_report'),
Index('ix_report_recurrent', recurrent))
def update_status(self):
# if we don't have the result key leave as is
if self.queue_result_key and self.status not in (
celery.states.READY_STATES):
# TODO: inline import. Can't import up above because of circular reference
from wikimetrics.models.report_nodes import Report
celery_task = Report.task.AsyncResult(self.queue_result_key)
self.status = celery_task.status
existing_session = Session.object_session(self)
if not existing_session:
existing_session = db.get_session()
existing_session.add(self)
existing_session.commit()
@staticmethod
def update_reports(report_ids, owner_id, public=None, recurrent=None):
"""
Updates reports in bulk, making sure they belong to an owner
TODO: an Admin should be able to modify any report but it does not look we
have an admin check (that kind of check should be cached)
Parameters:
report_ids : list of ids of ReportStore objects to update
owner_id : the person purporting to own these reports
public : update all reports' public attribute to this, default is None
recurrent : update all reports' recurrent attribute to this, default is None
Returns:
True if the number of updated records matches the number of report_ids
False otherwise
"""
db_session = db.get_session()
values = {}
if public is not None:
values['public'] = public
if recurrent is not None:
values['recurrent'] = recurrent
update = db_session.execute(
ReportStore.__table__.update().values(**values).where(
and_(ReportStore.id.in_(report_ids),
ReportStore.user_id == owner_id)))
db_session.commit()
if update and update.rowcount == len(report_ids):
return True
else:
raise UnauthorizedReportAccessError(
'Unauthorized access to report by {0}'.format(owner_id))
@staticmethod
def make_report_public(report_id, owner_id, file_manager, data):
"""
Parameters:
report_id : id of ReportStore to update
owner_id : the User purporting to own this report
file_manager: PublicReportFileManager for file management
data : String, report data to write out to filepath
"""
ReportStore.set_public_report_state(report_id,
owner_id,
file_manager,
public=True,
data=data)
@staticmethod
def make_report_private(report_id, owner_id, file_manager):
"""
Parameters:
report_id : id of ReportStore to update
owner_id : the User purporting to own this report
file_manager: PublicReportFileManager for file management
"""
ReportStore.set_public_report_state(report_id,
owner_id,
file_manager,
public=False)
@staticmethod
def set_public_report_state(report_id,
owner_id,
file_manager,
public=True,
data=''):
"""
Internal method that sets a report public/private status.
If we are making a report private that
was public before will remove files from disk.
If a new report is made public it will save report to disk.
Validation that user can update this report has already happened before
we reach this method.
The UI calls this method on a per report basis
but updates can be done for a set of reports.
TODO: This method should not have http level code and
should be part of an API,
not be on the controller.
TODO: We should not open & close a session here, I think session should be
open/closed at the beginning/end of the request
using flask request scoped functions
Parameters:
report_id : id of ReportStore to update
owner_id : the User purporting to own this report
public : True | False if True data must be present
data : String, report data to write out to filepath
file_manager: PublicReportFileManager to manage io interactions
Returns:
Nothing
Throws:
Exception if there are issues making the report public or private
A private report is has public=False
"""
# NOTE: update_reports checks ownership and raises an exception if needed
ReportStore.update_reports([report_id], owner_id, public=public)
# good no exception
try:
path = file_manager.get_public_report_path(report_id)
if public:
file_manager.write_data(path, data)
else:
file_manager.remove_file(path)
except (PublicReportIOError, SQLAlchemyError) as e:
app.logger.exception(str(e))
# if there was an IO error rollback prior changes
# this issues a new query as now our session scope and
# transaction scope are now the same
ReportStore.update_reports([report_id],
owner_id,
public=not public)
raise e
def get_result_safely(self, result):
if result and isinstance(result, dict) and self.result_key in result:
return result[self.result_key]
else:
return {'failure': 'result not available'}
def get_json_result(self, result):
result = self.get_result_safely(result)
return {'result': result, 'parameters': self.pretty_parameters()}
def pretty_parameters(self):
"""
TODO add tests for this method
its name implies that it's generic but is looking for specific input/output
"""
raw = json.loads(self.parameters)
pretty = {}
pretty['Cohort Size'] = raw['cohort']['size']
pretty['Cohort'] = raw['cohort']['name']
pretty['Metric'] = raw['metric']['name']
pretty['Created On'] = self.created
for k in ('csrf_token', 'name', 'label'):
raw['metric'].pop(k, None)
for k, v in raw['metric'].items():
pretty['Metric_' + k] = v
return pretty
def __repr__(self):
return '<ReportStore("{0}")>'.format(self.id)
class TimestampMixin(object):
created_on = db.Column(db.DateTime, server_default=func.now())
updated_on = db.Column(db.DateTime,
server_default=func.now(),
onupdate=func.now())
def __init__(self, username, mess):
from sqlalchemy import func
self.time = func.now()
self.author = username
self.message = mess
class Host(Base):
__tablename__ = 'hosts'
id = Column(INTEGER, primary_key=True, unique=True, autoincrement=True)
hostname = Column(String(45), nullable=False, unique=True)
created = Column(TIMESTAMP, nullable=False, server_default=func.now())
class Poll(base):
"""The model for a Poll."""
__tablename__ = "poll"
id = Column(Integer, primary_key=True)
uuid = Column(
UUID(as_uuid=True),
unique=True,
nullable=False,
server_default=text("gen_random_uuid()"),
)
created_at = Column(DateTime, server_default=func.now(), nullable=False)
updated_at = Column(DateTime,
server_default=func.now(),
onupdate=func.now(),
nullable=False)
# Options
name = Column(String)
description = Column(String)
locale = Column(String, default="English")
poll_type = Column(String, nullable=False)
number_of_votes = Column(Integer, default=0)
# Functionality
anonymous = Column(Boolean, nullable=False)
results_visible = Column(Boolean, nullable=False, default=True)
due_date = Column(DateTime, nullable=True)
next_notification = Column(DateTime, nullable=True)
allow_new_options = Column(Boolean, nullable=False, default=False)
allow_sharing = Column(Boolean, nullable=False, default=False)
# Styling
show_percentage = Column(Boolean, nullable=False, default=True)
show_option_votes = Column(Boolean, nullable=False, default=True)
european_date_format = Column(Boolean, nullable=False, default=False)
permanently_summarized = Column(Boolean, nullable=False, default=False)
compact_buttons = Column(Boolean, nullable=False, default=False)
summarize = Column(Boolean, nullable=False, default=False)
option_sorting = Column(String, nullable=False)
user_sorting = Column(String, nullable=False)
# Flags
created = Column(Boolean, nullable=False, default=False)
closed = Column(Boolean, nullable=False, default=False)
# Set this, if the poll should be deleted.
# There are two modes: DB_ONLY and WITH_MESSAGES.
delete = Column(String)
# Chat state variables
expected_input = Column(String)
in_settings = Column(Boolean, nullable=False, default=False)
created_from_native = Column(Boolean,
nullable=False,
server_default="False",
default=False)
# ManyToOne
user_id = Column(
BigInteger,
ForeignKey("user.id", ondelete="cascade", name="user"),
nullable=False,
index=True,
)
user = relationship("User", foreign_keys="Poll.user_id")
# OneToMany
options = relationship(
"Option",
order_by="asc(Option.index)",
lazy="joined",
passive_deletes="all",
)
votes = relationship("Vote", passive_deletes="all")
references = relationship("Reference",
lazy="joined",
passive_deletes="all")
notifications = relationship("Notification", passive_deletes="all")
def __init__(self, user):
"""Create a new poll."""
self.user = user
self.poll_type = PollType.single_vote.name
self.anonymous = False
self.results_visible = True
self.user_sorting = UserSorting.chrono.name
self.option_sorting = OptionSorting.manual.name
@staticmethod
def create(user, session):
"""Create a poll from a user."""
poll = Poll(user)
poll.european_date_format = user.european_date_format
poll.locale = user.locale
user.current_poll = poll
user.expected_input = ExpectedInput.name.name
session.add(poll)
session.commit()
return poll
def __repr__(self):
"""Print as string."""
return f"Poll with Id: {self.id}, name: {self.name}, locale: {self.locale}"
def should_show_result(self):
"""Determine, whether this results of this poll should be shown."""
return self.results_visible or self.closed
def is_doodle(self):
return self.poll_type == PollType.doodle.name
def is_priority(self):
return self.poll_type == PollType.priority.name
def has_date_option(self):
"""Check whether this poll has a date option."""
for option in self.options:
if option.is_date:
return True
return False
def get_date_option(self, check_date):
"""Return whether an option with this date already exists."""
for option in self.options:
if option.is_date and option.as_date() == check_date:
return option
return None
def get_formatted_due_date(self):
"""Get the formatted date."""
if self.european_date_format:
return self.due_date.strftime("%d.%m.%Y %H:%M UTC")
return self.due_date.strftime("%Y-%m-%d %H:%M UTC")
def set_due_date(self, date):
"""Set the due date and the next notification."""
if date is None:
self.due_date = None
self.next_notification = None
return
# Calculate the next_notification date depending
# on the given due date
now = datetime.now()
self.due_date = date
if now < self.due_date - timedelta(days=7):
self.next_notification = self.due_date - timedelta(days=7)
elif now < self.due_date - timedelta(days=1):
self.next_notification = self.due_date - timedelta(days=1)
elif now < self.due_date - timedelta(hours=6):
self.next_notification = self.due_date - timedelta(hours=6)
else:
self.next_notification = self.due_date
class File(db.Model):
__tablename__ = "release_files"
@declared_attr
def __table_args__(cls): # noqa
return (
ForeignKeyConstraint(
["name", "version"],
["releases.name", "releases.version"],
onupdate="CASCADE",
ondelete="CASCADE",
),
CheckConstraint("sha256_digest ~* '^[A-F0-9]{64}$'"),
CheckConstraint("blake2_256_digest ~* '^[A-F0-9]{64}$'"),
Index("release_files_name_version_idx", "name", "version"),
Index("release_files_packagetype_idx", "packagetype"),
Index("release_files_version_idx", "version"),
Index(
"release_files_single_sdist",
"name",
"version",
"packagetype",
unique=True,
postgresql_where=(
(cls.packagetype == "sdist")
& (cls.allow_multiple_sdist == False) # noqa
),
),
)
name = Column(Text)
version = Column(Text)
python_version = Column(Text)
requires_python = Column(Text)
packagetype = Column(
Enum(
"bdist_dmg",
"bdist_dumb",
"bdist_egg",
"bdist_msi",
"bdist_rpm",
"bdist_wheel",
"bdist_wininst",
"sdist",
)
)
comment_text = Column(Text)
filename = Column(Text, unique=True)
path = Column(Text, unique=True, nullable=False)
size = Column(Integer)
has_signature = Column(Boolean)
md5_digest = Column(Text, unique=True, nullable=False)
sha256_digest = Column(CIText, unique=True, nullable=False)
blake2_256_digest = Column(CIText, unique=True, nullable=False)
upload_time = Column(DateTime(timezone=False), server_default=func.now())
# We need this column to allow us to handle the currently existing "double"
# sdists that exist in our database. Eventually we should try to get rid
# of all of them and then remove this column.
allow_multiple_sdist = Column(Boolean, nullable=False, server_default=sql.false())
# TODO: Once Legacy PyPI is gone, then we should remove this column
# completely as we no longer use it.
downloads = Column(Integer, server_default=sql.text("0"))
@hybrid_property
def pgp_path(self):
return self.path + ".asc"
@pgp_path.expression
def pgp_path(self):
return func.concat(self.path, ".asc")
@validates("requires_python")
def validates_requires_python(self, *args, **kwargs):
raise RuntimeError("Cannot set File.requires_python")
def upgrade():
op.add_column('images',
Column('created_at', DateTime, server_default=func.now()))
class StickerSet(base):
"""The sqlite model for a sticker set."""
__tablename__ = "sticker_set"
__table_args__ = (
Index(
"sticker_set_name_gin_idx",
"name",
postgresql_using="gin",
postgresql_ops={"name": "gin_trgm_ops"},
),
Index(
"sticker_title_name_gin_idx",
"title",
postgresql_using="gin",
postgresql_ops={"title": "gin_trgm_ops"},
),
CheckConstraint("NOT (reviewed AND NOT complete)"),
)
name = Column(String, primary_key=True)
title = Column(String)
international = Column(Boolean, default=False, nullable=False)
deleted = Column(Boolean, default=False, nullable=False)
# Flags
animated = Column(Boolean, default=False, nullable=False)
banned = Column(Boolean, default=False, nullable=False)
nsfw = Column(Boolean, default=False, nullable=False)
furry = Column(Boolean, default=False, nullable=False)
deluxe = Column(Boolean, default=False, nullable=False)
# Metadata
created_at = Column(DateTime, server_default=func.now(), nullable=False)
complete = Column(Boolean, default=False, nullable=False)
completely_tagged = Column(Boolean, default=False, nullable=False)
scan_scheduled = Column(
Boolean,
default=False,
nullable=False,
)
reviewed = Column(Boolean, default=False, nullable=False)
stickers = relationship("Sticker", order_by="desc(Sticker.file_unique_id)")
reports = relationship("Report",
order_by="desc(Report.created_at)",
back_populates="sticker_set")
tasks = relationship("Task",
order_by="asc(Task.created_at)",
back_populates="sticker_set")
chats = relationship("Chat",
secondary=chat_sticker_set,
back_populates="sticker_sets")
def __init__(self, name, stickers):
"""Create a new StickerSet instance."""
self.name = name
self.stickers = []
def __str__(self):
"""Debug string for class."""
return (
f"StickerSet: {self.title} ({self.name}) \nStickers: {len(self.stickers)}"
)
@staticmethod
def get_or_create(session, name, chat, user):
"""Get or create a new sticker set."""
name = name.lower()
sticker_set = session.query(StickerSet).get(name)
if not sticker_set:
# Create a task for adding a sticker.
# This task will be processed by a job, since adding a sticker can take quite a while
sticker_set = StickerSet(name, None)
sticker_set.international = user.international
task = Task(Task.SCAN_SET,
sticker_set=sticker_set,
chat=chat,
user=user)
session.add(sticker_set)
session.add(task)
# Error handling: Retry in case somebody sent to stickers at the same time
try:
session.commit()
except IntegrityError as e:
session.rollback()
sticker_set = session.query(StickerSet).get(name)
if sticker_set is None:
raise e
return sticker_set
class Post(db.Model):
__tablename__ = 'posts'
id = db.Column(db.Integer, primary_key=True)
uuid = db.Column(db.String(100), unique=True)
path = db.Column(db.String(512), unique=True)
project = db.Column(db.String(512), nullable=True) # DEPRECATED
repository = db.Column(db.String(512))
revision = db.Column(db.Integer())
title = db.Column(db.Text())
tldr = db.Column(db.Text)
keywords = db.Column(db.Text)
thumbnail = db.Column(db.Text())
private = db.Column(db.Integer())
created_at = db.Column(db.DateTime, default=func.now())
updated_at = db.Column(db.DateTime, default=func.now())
_authors_assoc = db.relationship("PostAuthorAssoc",
order_by='PostAuthorAssoc.order',
collection_class=ordering_list('order'),
cascade="all, delete-orphan")
_authors = association_proxy(
'_authors_assoc',
'author',
creator=lambda author: PostAuthorAssoc(author=author),
)
@hybrid_property
def authors(self):
return self._authors
@authors.setter
def authors(self, authors):
"""
Sets the tags of the post to the tags given in comma delimited string
form in tags_string
"""
user_objs = []
for author in authors:
if not isinstance(author, User):
author = author.strip()
author = User(identifier=author)
user_objs.append(author)
self._authors = user_objs
@hybrid_property
def authors_string(self):
return u', '.join([author.format_name for author in self.authors])
@authors_string.expression
def authors_string(self):
raise NotImplementedError
_tags = db.relationship("Tag",
secondary=assoc_post_tag,
backref='posts',
lazy='subquery')
@hybrid_property
def tags(self):
return self._tags
@tags.setter
def tags(self, tags):
"""
Sets the tags of the post to the tags given in comma delimited string
form in tags_string
"""
tag_objs = []
for tag in tags:
if not isinstance(tag, Tag):
tag = tag.strip()
if tag[0] == "#":
tag = tag[1:]
tag = Tag(name=tag)
tag_objs.append(tag)
self._tags = tag_objs
@property
def contains_excluded_tag(self):
excluded_tags = current_app.config.get('EXCLUDED_TAGS', [])
return any([tag.name in excluded_tags for tag in self.tags])
_groups = db.relationship("Group",
secondary=assoc_post_group,
backref='posts',
lazy='subquery')
@hybrid_property
def groups(self):
return self._groups
@groups.setter
def groups(self, groups):
# given a list of group_names, we add it.
group_objs = []
for group in groups:
if not isinstance(group, Group):
group = Group(name=group.strip())
group_objs.append(group)
# create an implicit group, group_post.id, to add
# single users to
group = Group(name=":post_group_" + str(self.id))
# this created group should have the author associated with it
# so they can add people to the post
group.users = self.authors
group_objs.append(group)
self._groups = group_objs
_status = db.Column('status', db.Integer(), default=0)
@hybrid_property
def status(self):
return current_repo.PostStatus(self._status or 0)
@status.expression
def status(self):
return func.coalesce(self._status, 0)
@status.setter
def status(self, status):
if status is None:
self._status = None
else:
assert isinstance(
status, KnowledgeRepository.PostStatus
), "Status must be an instance of KnowledgeRepository.PostStatus.Status or None"
self._status = status.value
@hybrid_property
def is_published(self):
return self.status == current_repo.PostStatus.PUBLISHED
@is_published.expression
def is_published(self):
return func.coalesce(self._status,
0) == current_repo.PostStatus.PUBLISHED.value
_views = db.relationship(
"PageView",
lazy='dynamic',
primaryjoin="and_(foreign(PageView.object_id)==Post.id, "
"PageView.object_type=='post',"
"PageView.object_action=='view')")
@hybrid_property
def views(self):
return self._views.all()
@hybrid_property
def view_count(self):
return self._views.count()
@view_count.expression
def view_count(self):
return (select([func.count(
PageView.id)]).where(PageView.object_id == self.id).where(
PageView.object_type == 'post').label("view_count"))
@hybrid_property
def view_user_count(self):
return (db.session.query(func.count(distinct(
PageView.user_id))).filter(PageView.object_id == self.id).filter(
PageView.object_type == 'post').scalar())
@view_user_count.expression
def view_user_count(self):
return (select([func.count(distinct(
PageView.user_id))]).where(PageView.object_id == self.id).where(
PageView.object_type == 'post').label("view_user_count"))
_votes = db.relationship(
"Vote",
lazy='dynamic',
primaryjoin="and_(foreign(Vote.object_id)==Post.id, "
"Vote.object_type=='post')")
@hybrid_property
def votes(self):
return self._votes.all()
@hybrid_property
def vote_count(self):
""" Given the path of a post, return the total likes """
return self._votes.count()
@vote_count.expression
def vote_count(self):
return (select([func.count(Vote.id)
]).where(Vote.object_id == self.id).where(
Vote.object_type == 'post').label("vote_count"))
def vote_counted_for_user(self, user_id):
return (db_session.query(Vote).filter(
and_(Vote.object_id == self.id, Vote.object_type == 'post',
Vote.user_id == user_id)).first()) is not None
_comments = db.relationship(
"Comment",
lazy="dynamic",
primaryjoin="and_(foreign(Comment.post_id)==Post.id, "
"Comment.type=='post')")
@hybrid_property
def comments(self):
return self._comments.all()
@hybrid_property
def comment_count(self):
""" Given the path of the a post, return the total comments """
return self._comments.count()
@comment_count.expression
def comment_count(self):
return (select([func.count(
Comment.id)]).where(Comment.post_id == self.id).where(
Comment.object_type == 'post').label("comments_count"))
@property
def kp(self):
return current_repo.post(self.path)
@property
def text(self):
return self.kp.read()
def update_metadata_from_kp(self, kp):
"""
:param kp: Maps fields of the model to values
:type kp: KnowledgePost
:param kp: Maps fields of the model to values
:type kr: KnowledgeRepository
:return: None
:rtype: None
"""
headers = kp.headers
self.uuid = kp.uuid
self.path = kp.path
self.project = headers.get('project')
self.repository = kp.repository_uri
self.revision = kp.revision
self.title = headers['title']
self.tldr = headers['tldr']
self.authors = headers.get('authors', [])
self.tags = headers.get('tags', [])
self.keywords = get_keywords(self)
self.thumbnail = kp.thumbnail_uri
self.created_at = headers['created_at']
self.updated_at = headers['updated_at']
if self.created_at > self.updated_at:
self.updated_at = self.created_at
self.status = kp.status
self.private = 0
# we do this check so that no header (None) and False are treated the same
if headers.get('private', ''):
self.private = 1
self.groups = headers.get('allowed_groups', [])
class User(db.Model, UserMixin):
__tablename__ = 'users'
id = db.Column(db.Integer, primary_key=True)
created_at = db.Column(db.DateTime, default=func.now())
identifier = db.Column(
db.String(500)) # Unique identifier across all login methods
username = db.Column(
db.String(500)) # Username used to log in (may differ from identifier)
password = db.Column(db.String(500)) # Password for local logins
name = db.Column(db.String(500)) # Name as determined by auth method
preferred_name = db.Column(
db.String(500)) # Name as determined by user preferences
email = db.Column(db.String(500)) # Email address
avatar_uri = db.Column(db.Text()) # Either external url or data uri
active = db.Column(db.Boolean, default=True)
last_login_at = db.Column(db.DateTime) # Date of last login
_posts_assoc = db.relationship("PostAuthorAssoc")
posts = association_proxy(
'_posts_assoc', 'post') # This property should not directly modified
# Method overrides for the UserMixin class for flask_login
@property
def is_active(self):
return self.active
@property
def is_authenticated(self):
return True
@property
def is_anonymous(self):
return False
def get_id(self):
return self.identifier
can_logout = True
# Other useful methods
@property
def format_name(self):
return self.preferred_name or self.name or self.identifier
@property
def subscriptions(self): # TODO: make attribute style naming
"""Get the subscriptions associated with a user.
Return an array of strings of tag_names
"""
subscriptions = (db.session.query(Subscription).filter(
Subscription.user_id == self.id).all())
out_subscriptions = []
for s in subscriptions:
if s.object_type == 'tag':
tag_obj = (db.session.query(Tag).filter(
Tag.id == s.object_id).first())
if tag_obj:
full_name = tag_obj.name
out_subscriptions.append(full_name)
else:
db.session.delete(s)
return out_subscriptions
@property
def liked_posts(self):
"""
:return: Posts that a user has liked
:rtype: list
"""
votes = (db.session.query(Vote).filter(Vote.user_id == self.id).all())
post_ids = [vote.object_id for vote in votes]
if len(post_ids) == 0:
return []
excluded_tags = current_app.config.get('EXCLUDED_TAGS', [])
posts = (db.session.query(Post).filter(Post.id.in_(post_ids)).filter(
~Post.tags.any(Tag.name.in_(excluded_tags))).all())
return posts
class ForumThread(db.Model, SinglePKMixin):
__tablename__ = 'forums_threads'
__serializer__ = ForumThreadSerializer
__cache_key__ = 'forums_threads_{id}'
__cache_key_post_count__ = 'forums_threads_{id}_post_count'
__cache_key_of_forum__ = 'forums_threads_forums_{id}'
__cache_key_last_post__ = 'forums_threads_{id}_last_post'
__permission_key__ = 'forumaccess_thread_{id}'
__deletion_attr__ = 'deleted'
_posts: List['ForumPost']
id: int = db.Column(db.Integer, primary_key=True)
topic: str = db.Column(db.String(150), nullable=False)
forum_id = db.Column(
db.Integer, db.ForeignKey('forums.id'), nullable=False, index=True
) # type: int
creator_id: int = db.Column(
db.Integer, db.ForeignKey('users.id'), nullable=False, index=True
)
created_time: datetime = db.Column(
db.DateTime(timezone=True), nullable=False, server_default=func.now()
)
locked: bool = db.Column(db.Boolean, nullable=False, server_default='f')
sticky: bool = db.Column(db.Boolean, nullable=False, server_default='f')
deleted: bool = db.Column(
db.Boolean, nullable=False, server_default='f', index=True
)
@declared_attr
def __table_args__(cls):
return (db.Index('ix_forums_threads_topic', func.lower(cls.topic)),)
@classmethod
def from_forum(
cls,
forum_id: int,
page: int = 1,
limit: Optional[int] = 50,
include_dead: bool = False,
) -> List['ForumThread']:
return cls.get_many(
key=cls.__cache_key_of_forum__.format(id=forum_id),
filter=cls.forum_id == forum_id,
order=cls.last_updated.desc(),
page=page,
limit=limit,
include_dead=include_dead,
)
@classmethod
def new(
cls, topic: str, forum_id: int, creator_id: int, post_contents: str
) -> Optional['ForumThread']:
Forum.is_valid(forum_id, error=True)
User.is_valid(creator_id, error=True)
cache.delete(cls.__cache_key_of_forum__.format(id=forum_id))
thread = super()._new(
topic=topic, forum_id=forum_id, creator_id=creator_id
)
subscribe_users_to_new_thread(thread)
ForumPost.new(
thread_id=thread.id, user_id=creator_id, contents=post_contents
)
return thread
@classmethod
def get_ids_from_forum(cls, id):
return cls.get_pks_of_many(
key=cls.__cache_key_of_forum__.format(id=id),
filter=cls.forum_id == id,
order=cls.last_updated.desc(),
)
@classmethod
def from_subscribed_user(cls, user_id: int) -> List['ForumThread']:
return cls.get_many(pks=cls.subscribed_ids(user_id))
@classmethod
def subscribed_ids(cls, user_id: int) -> List[Union[str, int]]:
return cls.get_pks_of_many(
key=ForumThreadSubscription.__cache_key_of_user__.format(
user_id=user_id
),
filter=cls.id.in_(
db.session.query(
ForumThreadSubscription.thread_id
).filter( # type: ignore
ForumThreadSubscription.user_id == user_id
)
),
order=ForumThread.id.asc(),
) # type: ignore
@hybrid_property
def last_updated(cls) -> BinaryExpression:
return (
select([func.max(ForumPost.time)])
.where(ForumPost.thread_id == cls.id)
.as_scalar()
)
@cached_property
def last_post(self) -> Optional['ForumPost']:
return ForumPost.from_query(
key=self.__cache_key_last_post__.format(id=self.id),
filter=and_(
ForumPost.thread_id == self.id, ForumPost.deleted == 'f'
),
order=ForumPost.id.desc(),
) # type: ignore
@cached_property
def last_viewed_post(self) -> Optional['ForumPost']:
return (
ForumLastViewedPost.post_from_attrs(
thread_id=self.id, user_id=flask.g.user.id
)
if flask.g.user
else None
)
@cached_property
def forum(self) -> 'Forum':
return Forum.from_pk(self.forum_id)
@cached_property
def creator(self) -> User:
return User.from_pk(self.creator_id)
@cached_property
def poll(self) -> 'ForumPoll':
return ForumPoll.from_thread(self.id)
@cached_property
def post_count(self) -> int:
return self.count(
key=self.__cache_key_post_count__.format(id=self.id),
attribute=ForumPost.id,
filter=and_(
ForumPost.thread_id == self.id, ForumPost.deleted == 'f'
),
)
@cached_property
def thread_notes(self) -> List['ForumThreadNote']:
return ForumThreadNote.from_thread(self.id)
@cached_property
def subscribed(self) -> bool:
return (
self.id in set(self.subscribed_ids(flask.g.user.id))
if flask.g.user
else False
)
@property
def posts(self) -> List['ForumPost']:
if not hasattr(self, '_posts'):
self._posts = ForumPost.from_thread(self.id, 1, limit=50)
return self._posts
def set_posts(
self, page: int = 1, limit: int = 50, include_dead: bool = False
) -> None:
self._posts = ForumPost.from_thread(self.id, page, limit, include_dead)
def can_access(self, permission: str = None, error: bool = False) -> bool:
"""Determines whether or not the user has the permissions to access the thread."""
if flask.g.user is None: # pragma: no cover
if error:
raise _403Exception
return False
# Explicit thread access
permission_key = self.__permission_key__.format(id=self.id)
if flask.g.user.has_permission(permission_key) or (
permission is not None and flask.g.user.has_permission(permission)
):
return True
# Access to forum gives access to all threads by default.
# If user has been ungranted the thread, they cannot view it regardless.
ungranted_threads = [
p
for p, g in UserPermission.from_user(
flask.g.user.id, prefix='forumaccess_thread'
).items()
if g is False
]
if permission_key not in ungranted_threads and (
flask.g.user.has_permission(
Forum.__permission_key__.format(id=self.forum_id)
)
):
return True
if error:
raise _403Exception
return False
class TextRef(Base, IndraDBTable):
__tablename__ = 'text_ref'
_ref_cols = ['pmid', 'pmcid', 'doi', 'pii', 'url', 'manuscript_id']
_always_disp = ['id', 'pmid', 'pmcid']
_indices = [
StringIndex('text_ref_pmid_idx', 'pmid'),
StringIndex('text_ref_pmcid_idx', 'pmcid'),
BtreeIndex('text_ref_pmid_num_idx', 'pmid_num'),
BtreeIndex('text_ref_pmcid_num_idx', 'pmcid_num'),
BtreeIndex('text_ref_doi_ns_idx', 'doi_ns'),
BtreeIndex('text_ref_doi_id_idx', 'doi_id'),
StringIndex('text_ref_doi_idx', 'doi')
]
id = Column(Integer, primary_key=True)
pmid = Column(String(20))
pmid_num = Column(Integer)
pmcid = Column(String(20))
pmcid_num = Column(Integer)
pmcid_version = Column(Integer)
doi = Column(String(100))
doi_ns = Column(Integer)
doi_id = Column(String)
pii = Column(String(250))
url = Column(String, unique=True)
manuscript_id = Column(String(100), unique=True)
create_date = Column(DateTime, default=func.now())
last_updated = Column(DateTime, onupdate=func.now())
__table_args__ = (UniqueConstraint('pmid', 'doi', name='pmid-doi'),
UniqueConstraint('pmid', 'pmcid', name='pmid-pmcid'),
UniqueConstraint('pmcid', 'doi', name='pmcid-doi'))
def __repr__(self):
terms = [f'id={self.id}']
for col in ['pmid', 'pmcid', 'doi', 'pii', 'url', 'manuscript_id']:
if getattr(self, col) is not None:
terms.append(f'{col}={getattr(self, col)}')
if len(terms) > 2:
break
return f'{self.__class__.__name__}({", ".join(terms)})'
@classmethod
def new(cls,
pmid=None,
pmcid=None,
doi=None,
pii=None,
url=None,
manuscript_id=None):
pmid, pmid_num = cls.process_pmid(pmid)
pmcid, pmcid_num, pmcid_version = cls.process_pmcid(pmcid)
doi, doi_ns, doi_id = cls.process_doi(doi)
return cls(pmid=pmid,
pmid_num=pmid_num,
pmcid=pmcid,
pmcid_num=pmcid_num,
pmcid_version=pmcid_version,
doi=doi,
doi_ns=doi_ns,
doi_id=doi_id,
pii=pii,
url=url,
manuscript_id=manuscript_id)
def update(self, **updates):
for id_type, id_val in updates.items():
if id_type == 'pmid':
self.pmid, self.pmid_num = self.process_pmid(id_val)
elif id_type == 'pmcid':
self.pmcid, self.pmcid_num, self.pmcid_version = \
self.process_pmcid(id_val)
elif id_type == 'doi':
self.doi, self.doi_ns, self.doi_id = \
self.process_doi(id_val)
else:
setattr(self, id_type, id_val)
return
@staticmethod
def process_pmid(pmid):
if not pmid:
return None, None
if not pmid.isdigit():
return pmid, None
return pmid, int(pmid)
@staticmethod
def process_pmcid(pmcid):
if not pmcid:
return None, None, None
if not pmcid.startswith('PMC'):
return pmcid, None, None
if '.' in pmcid:
pmcid, version_number_str = pmcid.split('.')
if version_number_str.isdigit():
version_number = int(version_number_str)
else:
version_number = None
else:
version_number = None
if not pmcid[3:].isdigit():
return pmcid, None, version_number
return pmcid, int(pmcid[3:]), version_number
@staticmethod
def process_doi(doi):
# Check for invalid DOIs
if not doi:
return None, None, None
# Regularize case.
doi = doi.upper()
if not doi.startswith('10.'):
return doi, None, None
# Split up the parts of the DOI
parts = doi[3:].split('/')
if len(parts) < 2:
return doi, None, None
# Check the namespace number, make it an integer.
namespace_str = parts[0]
if not namespace_str.isdigit():
return doi, None, None
namespace = int(namespace_str)
# Join the res of the parts together.
group_id = '/'.join(parts[1:])
return doi, namespace, group_id
@classmethod
def pmid_in(cls, pmid_list, filter_ids=False):
"""Get sqlalchemy clauses for a list of pmids."""
pmid_num_set = set()
for pmid in pmid_list:
_, pmid_num = cls.process_pmid(pmid)
if pmid_num is None:
if filter_ids:
logger.warning('"%s" is not a valid pmid. Skipping.' %
pmid)
continue
else:
ValueError('"%s" is not a valid pmid.' % pmid)
pmid_num_set.add(pmid_num)
return cls.pmid_num.in_(pmid_num_set)
@classmethod
def pmcid_in(cls, pmcid_list, filter_ids=False):
"""Get the sqlalchemy clauses for a list of pmcids."""
pmcid_num_set = set()
for pmcid in pmcid_list:
_, pmcid_num, _ = cls.process_pmcid(pmcid)
if not pmcid_num:
if filter_ids:
logger.warning('"%s" does not look like a valid '
'pmcid. Skipping.' % pmcid)
continue
else:
raise ValueError('"%s" is not a valid pmcid.' % pmcid)
else:
pmcid_num_set.add(pmcid_num)
return cls.pmcid_num.in_(pmcid_num_set)
@classmethod
def doi_in(cls, doi_list, filter_ids=False):
"""Get clause for looking up a list of dois."""
doi_tuple_set = set()
for doi in doi_list:
doi, doi_ns, doi_id = cls.process_doi(doi)
if not doi_ns:
if filter_ids:
logger.warning('"%s" does not look like a normal doi. '
'Skipping.' % doi)
continue
else:
raise ValueError('"%s" is not a valid doi.' % doi)
else:
doi_tuple_set.add((doi_ns, doi_id))
return tuple_(cls.doi_ns, cls.doi_id).in_(doi_tuple_set)
def get_ref_dict(self):
ref_dict = {}
for ref in self._ref_cols:
val = getattr(self, ref, None)
if val:
ref_dict[ref.upper()] = val
ref_dict['TRID'] = self.id
return ref_dict
class User(Base):
__tablename__ = 'users'
id = Column(Integer, Sequence('user_id_seq'), primary_key=True)
email = Column(String(50), unique=True)
email_confirmed = Column(Boolean, default=False)
email_token = Column(String(64), default=lambda: secrets.token_urlsafe(64))
created_at = Column(DateTime, default=func.now())
subscriptions = relationship("Subscription", back_populates="user")
def __repr__(self):
return "<User(email='%s', confirmed='%s')>" % (
self.email, str(self.email_confirmed))
@classmethod
def find_by_email(cls, session, email):
return session.query(User).filter(User.email == email).one_or_none()
def try_confirm(self, session, token):
tokens_match = secrets.compare_digest(self.email_token, token)
if not self.email_confirmed and tokens_match:
self.email_confirmed = True
session.add(self)
session.commit()
return tokens_match
def get_mail_template(self, name):
flask_locale = flask_get_locale()
if flask_locale:
language = flask_locale.language
else:
language = 'en'
t = gettext.translation('messages', localedir='translations', languages=[language])
if name == 'confirm':
return {"subject": t.gettext("mail:confirm:subject"),
"message": t.gettext("mail:confirm:message")}
elif name == 'alarm':
return {"subject": t.gettext("mail:alarm:subject"),
"message": t.gettext("mail:alarm:message")}
elif name == 'resolved':
return {"subject": t.gettext("mail:resolved:subject"),
"message": t.gettext("mail:resolved:message")}
else:
raise Exception(f'No mail template named {name} found!')
def send_confirm_mail(self, url):
url = url + "?email=" + self.email + "&token=" + self.email_token
mail_template = self.get_mail_template("confirm")
self.send_mail(mail_template, url=url)
def send_mail(self, mail_template, in_reply_to = None, **kwargs):
msgid = make_msgid()
subject = mail_template['subject'].format(**kwargs)
message = mail_template['message'].format(**kwargs)
msg = MIMEMultipart('alternative')
msg['Subject'] = str(Header(subject, 'utf-8'))
msg['From'] = str(Header(SMTP_FROM, 'utf-8'))
msg['To'] = str(Header(self.email, 'utf-8'))
msg['Message-ID'] = msgid
msg['Reply-To'] = SMTP_REPLY_TO_EMAIL
msg['Date'] = datetime.datetime.now(pytz.utc).strftime("%a, %e %b %Y %T %z")
if in_reply_to:
msg['In-Reply-To'] = in_reply_to
msg['References'] = in_reply_to
# add message
charset = Charset('utf-8')
# QP = quoted printable; this is better readable instead of base64, when
# the mail is read in plaintext!
charset.body_encoding = QP
message_part = MIMEText(message.encode('utf-8'), 'plain', charset)
msg.attach(message_part)
if DEBUG:
with open("/tmp/keepitup_mails.log", "a") as f:
f.write(msg.as_string() + "\n")
else:
with smtplib.SMTP(SMTP_HOST, SMTP_PORT) as server:
server.ehlo()
if SMTP_USE_STARTTLS:
context = ssl.create_default_context()
server.starttls(context=context)
server.sendmail(SMTP_FROM, self.email, msg.as_string())
return msgid
@property
def subscribed_nodes(self):
return [subscription.node for subscription in self.subscriptions]
class Poll(base):
"""The model for a Poll."""
__tablename__ = 'poll'
id = Column(Integer, primary_key=True)
uuid = Column(UUID(as_uuid=True),
unique=True,
nullable=False,
server_default=text('gen_random_uuid()'))
created_at = Column(DateTime, server_default=func.now(), nullable=False)
updated_at = Column(DateTime,
server_default=func.now(),
onupdate=func.now(),
nullable=False)
# Options
name = Column(String)
description = Column(String)
locale = Column(String, server_default='english')
poll_type = Column(String, nullable=False)
anonymous = Column(Boolean, nullable=False)
number_of_votes = Column(Integer)
allow_new_options = Column(Boolean, nullable=False, default=False)
option_sorting = Column(String, nullable=False)
user_sorting = Column(String, nullable=False)
results_visible = Column(Boolean, nullable=False, default=True)
show_percentage = Column(Boolean, nullable=False, default=True)
european_date_format = Column(Boolean, nullable=False, default=False)
# Flags
created = Column(Boolean, nullable=False, default=False)
closed = Column(Boolean, nullable=False, default=False)
due_date = Column(DateTime, nullable=True)
next_notification = Column(DateTime, nullable=True)
# Chat state variables
expected_input = Column(String)
in_settings = Column(Boolean, nullable=False, default=False)
current_date = Column(Date, server_default=func.now(), nullable=False)
# OneToOne
user_id = Column(BigInteger,
ForeignKey('user.id', ondelete='cascade', name='user'),
nullable=False,
index=True)
user = relationship('User', foreign_keys='Poll.user_id')
# OneToMany
options = relationship('PollOption',
order_by='asc(PollOption.id)',
lazy='joined',
passive_deletes='all')
votes = relationship('Vote', passive_deletes=True)
references = relationship('Reference',
lazy='joined',
passive_deletes='all')
notifications = relationship('Notification', passive_deletes='all')
def __init__(self, user):
"""Create a new poll."""
self.user = user
self.poll_type = PollType.single_vote.name
self.anonymous = False
self.results_visible = True
self.user_sorting = UserSorting.user_chrono.name
self.option_sorting = OptionSorting.option_chrono.name
def __repr__(self):
"""Print as string."""
return f'Poll with Id: {self.id}, name: {self.name}'
def should_show_result(self):
"""Determine, whether this results of this poll should be shown."""
return self.results_visible or self.closed
def has_date_option(self):
"""Check whether this poll has a date option."""
for option in self.options:
if option.is_date:
return True
return False
def get_formatted_due_date(self):
"""Get the formatted date."""
if self.european_date_format:
return self.due_date.strftime('%d.%m.%Y %H:%M UTC')
return self.due_date.strftime('%Y-%m-%d %H:%M UTC')
def set_due_date(self, date):
"""Set the due date and the next notification."""
now = datetime.now()
self.due_date = date
if now < self.due_date - timedelta(days=7):
self.next_notification = self.due_date - timedelta(days=7)
elif now < self.due_date - timedelta(days=1):
self.next_notification = self.due_date - timedelta(days=1)
elif now < self.due_date - timedelta(hours=6):
self.next_notification = self.due_date - timedelta(hours=6)
else:
self.next_notification = self.due_date
def clone(self, session):
"""Create a clone from the current poll."""
poll = Poll(self.user)
poll.created = True
session.add(poll)
poll.name = self.name
poll.description = self.description
poll.poll_type = self.poll_type
poll.anonymous = self.anonymous
poll.number_of_votes = self.number_of_votes
poll.allow_new_options = self.allow_new_options
poll.option_sorting = self.option_sorting
poll.user_sorting = self.user_sorting
poll.results_visible = self.results_visible
poll.show_percentage = self.show_percentage
from pollbot.models import PollOption
for option in self.options:
new_option = PollOption(poll, option.name)
session.add(new_option)
return poll
class UserProfile(meta.Base, BaseMixin, BaseDictMixin):
"""
Profile information is added to the 'users' table.
"""
PALETTE_DEFAULT_NAME = 'palette'
PALETTE_DEFAULT_FRIENDLY_NAME = 'Palette Server Admin'
PALETTE_DEFAULT_PASSWORD = '******'
__tablename__ = 'users'
userid = Column(BigInteger, unique=True, nullable=False, \
autoincrement=True, primary_key=True)
envid = Column(BigInteger, ForeignKey("environment.envid"), nullable=False)
active = Column(Boolean, default=True)
name = Column(String, unique=True, nullable=False)
friendly_name = Column(String)
email = Column(String)
email_level = Column(Integer, default=1)
phone = Column(String)
hashed_password = Column(String)
salt = Column(String)
roleid = Column(BigInteger, ForeignKey("roles.roleid"), default=0)
system_user_id = Column(Integer, unique=True)
login_at = Column(DateTime)
licensing_role_id = Column(Integer)
user_admin_level = Column(Integer)
system_admin_level = Column(Integer)
publisher = Column(Boolean)
system_created_at = Column(DateTime)
timestamp = Column(DateTime) # last active time (in Palette)
modification_time = Column(DateTime,
server_default=func.now(),
onupdate=func.current_timestamp())
role = relationship("Role")
def __unicode__(self):
if self.friendly_name:
return unicode(self.friendly_name)
return unicode(self.name)
def __str__(self):
return unicode(self).encode('utf-8')
def display_name(self):
return unicode(self)
def display_role(self):
# pylint: disable=no-member
if self.publisher:
if self.roleid == Role.NO_ADMIN:
return u'Publisher'
return u'Publisher & ' + self.role.name
else:
return self.role.name
@classmethod
def get(cls, envid, userid):
filters = {'envid': envid, 'userid': userid}
return cls.get_unique_by_keys(filters, default=None)
@classmethod
def get_by_system_user_id(cls, envid, system_user_id):
filters = {'envid': envid, 'system_user_id': system_user_id}
return cls.get_unique_by_keys(filters, default=None)
@classmethod
def get_by_name(cls, envid, name):
try:
query = meta.Session.query(UserProfile).\
filter(UserProfile.envid == envid).\
filter(func.lower(UserProfile.name) == name.lower())
entry = query.one()
except NoResultFound:
entry = None
return entry
@classmethod
def verify(cls, envid, name, password):
entry = cls.get_by_name(envid, name)
if not entry:
return False
return entry.hashed_password == tableau_hash(password, entry.salt)
defaults = [{
'userid': 0,
'envid': 1,
'name': PALETTE_DEFAULT_NAME,
'friendly_name': PALETTE_DEFAULT_FRIENDLY_NAME,
'email_level': 1,
'email': None,
'salt': '',
'roleid': 3, # SUPER_ADMIN
'system_user_id': 0
}]
@classmethod
def user_count(cls, envid):
return meta.Session.query(UserProfile).\
filter(UserProfile.envid == envid).\
count()
@classmethod
def update_timestamp(cls, entry):
entry.timestamp = datetime.datetime.utcnow()
def upgrade():
conn = op.get_bind()
inspector = Inspector.from_engine(conn)
tables = inspector.get_table_names()
if 'connection' not in tables:
op.create_table(
'connection',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('conn_id', sa.String(length=250), nullable=True),
sa.Column('conn_type', sa.String(length=500), nullable=True),
sa.Column('host', sa.String(length=500), nullable=True),
sa.Column('schema', sa.String(length=500), nullable=True),
sa.Column('login', sa.String(length=500), nullable=True),
sa.Column('password', sa.String(length=500), nullable=True),
sa.Column('port', sa.Integer(), nullable=True),
sa.Column('extra', sa.String(length=5000), nullable=True),
sa.PrimaryKeyConstraint('id')
)
if 'dag' not in tables:
op.create_table(
'dag',
sa.Column('dag_id', sa.String(length=250), nullable=False),
sa.Column('is_paused', sa.Boolean(), nullable=True),
sa.Column('is_subdag', sa.Boolean(), nullable=True),
sa.Column('is_active', sa.Boolean(), nullable=True),
sa.Column('last_scheduler_run', sa.DateTime(), nullable=True),
sa.Column('last_pickled', sa.DateTime(), nullable=True),
sa.Column('last_expired', sa.DateTime(), nullable=True),
sa.Column('scheduler_lock', sa.Boolean(), nullable=True),
sa.Column('pickle_id', sa.Integer(), nullable=True),
sa.Column('fileloc', sa.String(length=2000), nullable=True),
sa.Column('owners', sa.String(length=2000), nullable=True),
sa.PrimaryKeyConstraint('dag_id')
)
if 'dag_pickle' not in tables:
op.create_table(
'dag_pickle',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('pickle', sa.PickleType(), nullable=True),
sa.Column('created_dttm', sa.DateTime(), nullable=True),
sa.Column('pickle_hash', sa.BigInteger(), nullable=True),
sa.PrimaryKeyConstraint('id')
)
if 'import_error' not in tables:
op.create_table(
'import_error',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('timestamp', sa.DateTime(), nullable=True),
sa.Column('filename', sa.String(length=1024), nullable=True),
sa.Column('stacktrace', sa.Text(), nullable=True),
sa.PrimaryKeyConstraint('id')
)
if 'job' not in tables:
op.create_table(
'job',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('dag_id', sa.String(length=250), nullable=True),
sa.Column('state', sa.String(length=20), nullable=True),
sa.Column('job_type', sa.String(length=30), nullable=True),
sa.Column('start_date', sa.DateTime(), nullable=True),
sa.Column('end_date', sa.DateTime(), nullable=True),
sa.Column('latest_heartbeat', sa.DateTime(), nullable=True),
sa.Column('executor_class', sa.String(length=500), nullable=True),
sa.Column('hostname', sa.String(length=500), nullable=True),
sa.Column('unixname', sa.String(length=1000), nullable=True),
sa.PrimaryKeyConstraint('id')
)
op.create_index(
'job_type_heart',
'job',
['job_type', 'latest_heartbeat'],
unique=False
)
if 'known_event_type' not in tables:
op.create_table(
'known_event_type',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('know_event_type', sa.String(length=200), nullable=True),
sa.PrimaryKeyConstraint('id')
)
if 'log' not in tables:
op.create_table(
'log',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('dttm', sa.DateTime(), nullable=True),
sa.Column('dag_id', sa.String(length=250), nullable=True),
sa.Column('task_id', sa.String(length=250), nullable=True),
sa.Column('event', sa.String(length=30), nullable=True),
sa.Column('execution_date', sa.DateTime(), nullable=True),
sa.Column('owner', sa.String(length=500), nullable=True),
sa.PrimaryKeyConstraint('id')
)
if 'sla_miss' not in tables:
op.create_table(
'sla_miss',
sa.Column('task_id', sa.String(length=250), nullable=False),
sa.Column('dag_id', sa.String(length=250), nullable=False),
sa.Column('execution_date', sa.DateTime(), nullable=False),
sa.Column('email_sent', sa.Boolean(), nullable=True),
sa.Column('timestamp', sa.DateTime(), nullable=True),
sa.Column('description', sa.Text(), nullable=True),
sa.PrimaryKeyConstraint('task_id', 'dag_id', 'execution_date')
)
if 'slot_pool' not in tables:
op.create_table(
'slot_pool',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('pool', sa.String(length=50), nullable=True),
sa.Column('slots', sa.Integer(), nullable=True),
sa.Column('description', sa.Text(), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('pool')
)
if 'task_instance' not in tables:
op.create_table(
'task_instance',
sa.Column('task_id', sa.String(length=250), nullable=False),
sa.Column('dag_id', sa.String(length=250), nullable=False),
sa.Column('execution_date', sa.DateTime(), nullable=False),
sa.Column('start_date', sa.DateTime(), nullable=True),
sa.Column('end_date', sa.DateTime(), nullable=True),
sa.Column('duration', sa.Integer(), nullable=True),
sa.Column('state', sa.String(length=20), nullable=True),
sa.Column('try_number', sa.Integer(), nullable=True),
sa.Column('hostname', sa.String(length=1000), nullable=True),
sa.Column('unixname', sa.String(length=1000), nullable=True),
sa.Column('job_id', sa.Integer(), nullable=True),
sa.Column('pool', sa.String(length=50), nullable=True),
sa.Column('queue', sa.String(length=50), nullable=True),
sa.Column('priority_weight', sa.Integer(), nullable=True),
sa.PrimaryKeyConstraint('task_id', 'dag_id', 'execution_date')
)
op.create_index(
'ti_dag_state',
'task_instance',
['dag_id', 'state'],
unique=False
)
op.create_index(
'ti_pool',
'task_instance',
['pool', 'state', 'priority_weight'],
unique=False
)
op.create_index(
'ti_state_lkp',
'task_instance',
['dag_id', 'task_id', 'execution_date', 'state'],
unique=False
)
if 'user' not in tables:
op.create_table(
'user',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('username', sa.String(length=250), nullable=True),
sa.Column('email', sa.String(length=500), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('username')
)
if 'variable' not in tables:
op.create_table(
'variable',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('key', sa.String(length=250), nullable=True),
sa.Column('val', sa.Text(), nullable=True),
sa.PrimaryKeyConstraint('id'),
sa.UniqueConstraint('key')
)
if 'chart' not in tables:
op.create_table(
'chart',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('label', sa.String(length=200), nullable=True),
sa.Column('conn_id', sa.String(length=250), nullable=False),
sa.Column('user_id', sa.Integer(), nullable=True),
sa.Column('chart_type', sa.String(length=100), nullable=True),
sa.Column('sql_layout', sa.String(length=50), nullable=True),
sa.Column('sql', sa.Text(), nullable=True),
sa.Column('y_log_scale', sa.Boolean(), nullable=True),
sa.Column('show_datatable', sa.Boolean(), nullable=True),
sa.Column('show_sql', sa.Boolean(), nullable=True),
sa.Column('height', sa.Integer(), nullable=True),
sa.Column('default_params', sa.String(length=5000), nullable=True),
sa.Column('x_is_date', sa.Boolean(), nullable=True),
sa.Column('iteration_no', sa.Integer(), nullable=True),
sa.Column('last_modified', sa.DateTime(), nullable=True),
sa.ForeignKeyConstraint(['user_id'], ['user.id'], ),
sa.PrimaryKeyConstraint('id')
)
if 'known_event' not in tables:
op.create_table(
'known_event',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('label', sa.String(length=200), nullable=True),
sa.Column('start_date', sa.DateTime(), nullable=True),
sa.Column('end_date', sa.DateTime(), nullable=True),
sa.Column('user_id', sa.Integer(), nullable=True),
sa.Column('known_event_type_id', sa.Integer(), nullable=True),
sa.Column('description', sa.Text(), nullable=True),
sa.ForeignKeyConstraint(['known_event_type_id'],
['known_event_type.id'], ),
sa.ForeignKeyConstraint(['user_id'], ['user.id'], ),
sa.PrimaryKeyConstraint('id')
)
if 'xcom' not in tables:
op.create_table(
'xcom',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('key', sa.String(length=512), nullable=True),
sa.Column('value', sa.PickleType(), nullable=True),
sa.Column(
'timestamp',
sa.DateTime(),
default=func.now(),
nullable=False),
sa.Column('execution_date', sa.DateTime(), nullable=False),
sa.Column('task_id', sa.String(length=250), nullable=False),
sa.Column('dag_id', sa.String(length=250), nullable=False),
sa.PrimaryKeyConstraint('id')
)
class CrawlForexExchange(Base):
"""行情资讯类"""
__tablename__ = 'jujin8_forex_exchange'
id = Column(Integer, primary_key=True)
abstractInfo = Column(String(512))
applyId = Column(Integer)
applyTime = Column(String(32))
ascribe = Column(String(36))
biggestLeverage = Column(String(64))
cName = Column(String(32))
checkOrNot = Column(String(32))
checkedPass = Column(String(8))
checkedStatus = Column(String(32))
companyName = Column(String(128))
condition = Column(String(128))
createTime = Column(String(32))
degreeOfPraise = Column(String(32))
depositMethod = Column(String(64))
eName = Column(String(64))
eaDeal = Column(String(64))
establishTime = Column(String(32))
gold = Column(String(32))
hedge = Column(SmallInteger)
homeCountry = Column(String(128))
source_id = Column(Integer)
ids = Column(String(64))
intro = Column(String(512))
licenseNo = Column(String(64))
likeCount = Column(Integer)
mainSpread = Column(String(64))
maxSpread = Column(String(64))
forexExchangeCommission = Column(String(64))
minSpread = Column(String(64))
officialBankAddress = Column(String(256))
officialWebsite = Column(String(256))
orgLicense = Column(String(64))
platformLogo = Column(String(128))
platName = Column(String(64))
platStatus = Column(String(64))
platformIcon = Column(String(256))
platformStatus = Column(String(256))
platformType = Column(String(64))
recommendOrNot = Column(String(16))
relevancy = Column(String(64))
scalp = Column(String(64))
show = Column(SmallInteger)
showPlatform = Column(SmallInteger)
softwareInfo = Column(String(128))
sortNo = Column(Integer)
source = Column(String(64))
spreadInfo = Column(String(64))
supervisionOrg = Column(String(128))
supervisionOrgName = Column(String(64))
surveyType = Column(String(64))
swap = Column(String(64))
synthesisScore = Column(String(64))
tradeVariety = Column(String(64))
tradingModel = Column(String(64))
trailingStop = Column(String(64))
withdrawMethod = Column(String(64))
created_at = Column(DateTime, default=func.now())
updated_at = Column(DateTime, default=func.now(), onupdate=func.now())
def editItem(item_title):
"""
Edit new item and store changes in database.
argument:
item title
"""
edit_item = session.query(Item).filter_by(title=item_title).one()
category_selected = session.query(Category).filter_by(
id=edit_item.cat_id
).one()
items = session.query(Item).all()
categories = session.query(Category).all()
if 'username' not in login_session:
flash('Please log in')
return redirect(url_for('showLogin'))
if edit_item.user_id != login_session['user_id']:
flash('Please edit only your items!')
return redirect(url_for('categoriesDashboard'))
if request.method == 'POST':
if request.form['category'] and request.form['title']:
category_selected = session.query(Category).filter_by(
name=request.form['category']
).one()
if 'file' not in request.files and 'image' in request.files:
image = request.files['image']
if image.filename != '' and allowed_file(image.filename):
filename = secure_filename(image.filename)
if (image.filename in
[item.image.split('/')[-1] for item in items]):
flash('{} picture name already exists!'.format(
image.filename
))
return render_template(
'newitem.html', categories=categories
)
image_resize = Image.open(image)
image_resize = resizeimage.resize_contain(
image_resize, [200, 200]
)
image_resize.save(os.path.join(
app.config['UPLOAD_FOLDER'], filename
), image_resize.format)
image_path = 'item_images/' + filename
else:
image_path = 'item_images/sport-goods.jpg'
else:
image_path = 'item_images/sport-goods.jpg'
edit_item.image = image_path
if request.form['title']:
edit_item.title = request.form['title']
if request.form['description']:
edit_item.description = request.form['description']
edit_item.cat_id = category_selected.id
edit_item.init_time = func.now()
session.add(edit_item)
session.commit()
session.close()
flash('{} item has been successfully edited!'.format(
request.form['title']
))
return redirect(url_for('categoriesDashboard'))
else:
flash('Please choose a title and pick a category!')
return render_template(
'edititem.html',
item=edit_item,
category_selected=category_selected,
user_profile_pic=login_session['picture'],
login_session_provider=login_session['provider'],
categories=categories
)
else:
return render_template(
'edititem.html',
item=edit_item,
category_selected=category_selected,
user_profile_pic=login_session['picture'],
login_session_provider=login_session['provider'],
categories=categories
)
def archive_dataset(self, dataset_id):
self._connection.execute(
DATASET.update().where(DATASET.c.id == dataset_id).where(
DATASET.c.archived == None).values(archived=func.now()))
class User(BaseMixin):
__tablename__ = 'users'
first_name = Column(String(255), default="")
last_name = Column(String(255), default="")
email = Column(String(255), nullable=False, unique=True)
role_id = Column(Integer, ForeignKey('roles.id'))
role = relationship('Role', backref=backref('users', lazy='dynamic'))
add_date = Column(DateTime, default=func.now())
_pw_hash = Column(UnicodeText, nullable=False)
age = Column(Integer)
def __init__(self, *args, **kwargs):
if 'first_name' in kwargs:
self.first_name = kwargs.pop('first_name')
if 'last_name' in kwargs:
self.last_name = kwargs.pop('last_name')
if 'email' in kwargs:
self.email = kwargs.pop('email')
if 'role' in kwargs:
self.role = kwargs.pop('role')
if 'role_id' in kwargs:
self.role_id = kwargs.pop('role_id')
if 'password' in kwargs:
self.password = kwargs.pop('password')
def _to_json(self):
import json
return json.dumps({
'first_name': self.first_name,
'last_name': self.last_name,
'email': self.email,
'age': self.age,
'date_added': self.add_date,
})
@declared_attr
def __table_args__(cls):
return (UniqueConstraint('email', 'first_name', 'last_name'), {})
@property
def is_unknown(self):
return False
def check_password(self, pw):
return check_password(pw, self._pw_hash)
@classmethod
def get_by_email(cls, email):
return cls.query().filter_by(email=email).first()
@property
def password(self):
return 'private'
raise ValueError('Private Value!!!!')
@password.setter
def password(self, pw):
self._pw_hash = encrypt_password(pw)
@property
def full_name(self):
return '{} {}'.format(self.first_name.title(), self.last_name.title())
@property
def name(self):
return str(self.first_name)
def __str__(self):
if self.first_name != "":
rtn = self.full_name
else:
rtn = self.email
return rtn
def __repr__(self):
return 'User<{} {}'.format(self.email, self.first_name)
def _get_absolute_url(self):
return url_for('member.profile', member_id=str(int(self.id)))
@property
def absolute_url(self):
return str(self._get_absolute_url())
def _get_edit_url(self):
return '#'
@property
def edit_url(self):
return str(self._get_edit_url())
class User(db.Model, UserMixin):
"""
CREATE TABLE u_user(
id INT auto_increment PRIMARY KEY COMMENT '用户表id',
email VARCHAR(64) NOT NULL COMMENT 'email地址',
phone VARCHAR(16) NOT NULL DEFAULT '' COMMENT '手机',
username VARCHAR(64) NOT NULL DEFAULT '' COMMENT '用户名,默认为邮箱前缀',
password_hash VARCHAR(256) NOT NULL COMMENT '用户密码',
role_id tinyint NOT NULL DEFAULT 3 COMMENT '用户角色id, 1:admin 2:leader, 3:staff',
status tinyint NOT NULL default 0 COMMENT '用户账号状态, 0:未激活, 1:激活',
join_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建账号时间',
UNIQUE KEY email(email),
KEY join_time(join_time)
)engine=Innodb charset=utf8 COMMENT '用户表';
"""
__tablename__ = 'u_user'
id = db.Column(db.Integer, primary_key=True)
email = db.Column(db.String(64), nullable=False)
phone = db.Column(db.String(16), nullable=False, default='')
username = db.Column(db.String(64), nullable=False)
password_hash = db.Column(db.String(256), nullable=False)
role_id = db.Column(db.Integer, default=3)
status = db.Column(db.Integer, default=0)
join_time = db.Column(db.DATETIME, server_default=func.now())
@property
def password(self):
return self.password_hash
@password.setter
def password(self, passwd):
self.password_hash = pbkdf2_sha256.hash(passwd)
def check_password(self, passwd):
return pbkdf2_sha256.verify(passwd, self.password)
def user_groups(self):
if self.role_id == ADMIN:
groups = Group.query.all()
else:
groups = db.session.query(Group)\
.join(UserGroup, Group.id == UserGroup.gid)\
.join(User, UserGroup.uid == User.id)\
.filter(User.id == self.id).all()
groups = [g.to_dict() for g in groups]
return groups
def user_permissions(self):
permissions_data = defaultdict(list)
if self.role_id == ADMIN:
permissions = Permission.query.all()
else:
permissions = db.session.query(Permission)\
.join(UserPermission, Permission.id == UserPermission.pid)\
.join(User, UserPermission.uid == User.id)\
.filter(User.id == self.id).all()
for p in permissions:
name_prefix, name_suffix = p.name.split('_')
p_data = p.to_dict()
p_data['name'] = name_suffix
permissions_data[name_prefix].append(p_data)
return permissions_data
class Keyword(Base):
__tablename__ = 'keyword'
id = Column(Integer, Sequence('keyword_id_seq'), primary_key=True)
text = Column(String(256))
create_at = Column(DateTime(timezone=True), default=func.now())
class Bid(Sessionized, Base):
__tablename__ = "bids"
STATUS = Enum("AUTH", "CHARGE", "REFUND", "VOID")
# will be unique from authorize
transaction = Column(BigInteger, primary_key = True,
autoincrement = False)
# identifying characteristics
account_id = Column(BigInteger, index = True, nullable = False)
pay_id = Column(BigInteger, index = True, nullable = False)
thing_id = Column(BigInteger, index = True, nullable = False)
# breadcrumbs
ip = Column(Inet)
date = Column(DateTime(timezone = True), default = safunc.now(),
nullable = False)
# bid information:
bid = Column(Float, nullable = False)
charge = Column(Float)
status = Column(Integer, nullable = False,
default = STATUS.AUTH)
# make this a primary key as well so that we can have more than
# one freebie per campaign
campaign = Column(Integer, default = 0, primary_key = True)
@classmethod
def _new(cls, trans_id, user, pay_id, thing_id, bid, campaign = 0):
bid = Bid(trans_id, user, pay_id,
thing_id, getattr(request, 'ip', '0.0.0.0'), bid = bid,
campaign = campaign)
bid._commit()
return bid
# @classmethod
# def for_transactions(cls, transids):
# transids = filter(lambda x: x != 0, transids)
# if transids:
# q = cls.query()
# q = q.filter(or_(*[cls.transaction == i for i in transids]))
# return dict((p.transaction, p) for p in q)
# return {}
def set_status(self, status):
if self.status != status:
self.status = status
self._commit()
def auth(self):
self.set_status(self.STATUS.AUTH)
def is_auth(self):
return (self.status == self.STATUS.AUTH)
def void(self):
self.set_status(self.STATUS.VOID)
def is_void(self):
return (self.status == self.STATUS.VOID)
def charged(self):
self.charge = self.bid
self.set_status(self.STATUS.CHARGE)
self._commit()
def is_charged(self):
"""
Returns True if transaction has been charged with authorize.net or is
a freebie with "charged" status.
"""
return (self.status == self.STATUS.CHARGE)
def refund(self, amount):
current_charge = self.charge or self.bid # needed if charged() not
# setting charge attr
self.charge = current_charge - amount
self.set_status(self.STATUS.REFUND)
self._commit()
def is_refund(self):
return (self.status == self.STATUS.REFUND)
@property
def charge_amount(self):
return self.charge or self.bid
class ForumPost(db.Model, SinglePKMixin):
__tablename__ = 'forums_posts'
__serializer__ = ForumPostSerializer
__cache_key__ = 'forums_posts_{id}'
__cache_key_of_thread__ = 'forums_posts_threads_{id}'
__deletion_attr__ = 'deleted'
id: int = db.Column(db.Integer, primary_key=True)
thread_id: int = db.Column(
db.Integer,
db.ForeignKey('forums_threads.id'),
nullable=False,
index=True,
)
user_id: int = db.Column(
db.Integer, db.ForeignKey('users.id'), nullable=False, index=True
)
contents: str = db.Column(db.Text, nullable=False)
time: datetime = db.Column(
db.DateTime(timezone=True), nullable=False, server_default=func.now()
)
sticky: bool = db.Column(db.Boolean, nullable=False, server_default='f')
edited_user_id: Optional[int] = db.Column(
db.Integer, db.ForeignKey('users.id')
)
edited_time: Optional[datetime] = db.Column(db.DateTime(timezone=True))
deleted: bool = db.Column(
db.Boolean, nullable=False, server_default='f', index=True
)
@classmethod
def from_thread(
cls,
thread_id: int,
page: int = 1,
limit: int = 50,
include_dead: bool = False,
) -> List['ForumPost']:
return cls.get_many(
key=cls.__cache_key_of_thread__.format(id=thread_id),
filter=cls.thread_id == thread_id,
order=cls.id.asc(), # type: ignore
page=page,
limit=limit,
include_dead=include_dead,
)
@classmethod
def get_ids_from_thread(cls, id):
return cls.get_pks_of_many(
key=cls.__cache_key_of_thread__.format(id=id),
filter=cls.thread_id == id,
order=cls.id.asc(),
)
@classmethod
def new(
cls, *, thread_id: int, user_id: int, contents: str
) -> Optional['ForumPost']:
ForumThread.is_valid(thread_id, error=True)
User.is_valid(user_id, error=True)
cache.delete(cls.__cache_key_of_thread__.format(id=thread_id))
post = super()._new(
thread_id=thread_id, user_id=user_id, contents=contents
)
send_subscription_notices(post)
check_post_contents_for_quotes(post)
check_post_contents_for_mentions(post)
return post
@cached_property
def thread(self) -> 'ForumThread':
return ForumThread.from_pk(self.thread_id)
@cached_property
def user(self) -> User:
return User.from_pk(self.user_id)
@cached_property
def editor(self) -> Optional[User]:
return User.from_pk(self.edited_user_id)
@cached_property
def edit_history(self) -> List['ForumPostEditHistory']:
return ForumPostEditHistory.from_post(self.id)
def allocate(ctx, optid, optname, opttype, optreplace, opthigh, optlow, optriskmgr, optrisk):
'''calc high low allocate
'''
if opthigh == 0 and 'markowitz.high' in ctx.obj:
opthigh = ctx.obj['markowitz.high']
if optlow == 0 and 'markowitz.low' in ctx.obj:
optlow = ctx.obj['markowitz.low']
if opthigh == 0 and optlow == 0:
click.echo(click.style("ether --high or --low shoud be given, aborted!", fg="red"))
return 0
#
# 处理id参数
#
today = datetime.now()
if optid is not None:
#
# 检查id是否存在
#
df_existed = asset_mz_highlow.load([str(optid * 10 + x) for x in range(0, 10)])
if not df_existed.empty:
s = 'highlow instance [%s] existed' % str(optid)
if optreplace:
click.echo(click.style("%s, will replace!" % s, fg="yellow"))
else:
click.echo(click.style("%s, import aborted!" % s, fg="red"))
return -1;
else:
#
# 自动生成id
#
prefix = '70' + today.strftime("%m%d");
between_min, between_max = ('%s00' % (prefix), '%s99' % (prefix))
max_id = asset_mz_highlow.max_id_between(between_min, between_max)
if max_id is None:
max_id = between_min
if not (isinstance(max_id, int) or isinstance(max_id, long)):
max_id = string.atoi(max_id)
if max_id is None:
optid = int(between_min)
else:
if max_id >= int(between_max):
if optreplace:
s = "run out of instance id [%d]" % max_id
click.echo(click.style("%s, will replace!" % s, fg="yellow"))
else:
s = "run out of instance id [%d]" % max_id
click.echo(click.style("%s, import aborted!" % s, fg="red"))
return -1
if optreplace:
optid = str(max_id)
else:
optid = str(max_id + 10)
if optname is None:
optname = u'高低风险%s' % today.strftime("%m%d")
#
# 加载用到的资产
#
df_asset = asset_mz_markowitz_asset.load([opthigh, optlow])
df_asset = df_asset[['mz_markowitz_id', 'mz_asset_id', 'mz_asset_name', 'mz_asset_type']]
df_asset = df_asset.rename(columns={'mz_markowitz_id': 'mz_origin_id'})
df_asset = df_asset.set_index(['mz_asset_id'])
#
# 加载用到的风控
#
dt_riskmgr = {}
for k, v in df_asset.iterrows():
if optriskmgr == False:
dt_riskmgr[k] = 0
else:
df_tmp = asset_rm_riskmgr.where_asset_id(k)
if df_tmp.empty:
dt_riskmgr[k] = 0
else:
dt_riskmgr[k] = df_tmp.ix[0, 'globalid']
df_asset['mz_riskmgr_id'] = pd.Series(dt_riskmgr)
#
# 加载资产池
#
dt_pool = {}
for k, v in df_asset.iterrows():
dt_pool[k] = asset_ra_pool.match_asset_pool(k)
df_asset['mz_pool_id'] = pd.Series(dt_pool)
if '11310100' not in df_asset.index:
df_asset.loc['11310100'] = (0, u'货币(低)', 31, 0, '11310100')
if '11310101' not in df_asset.index:
df_asset.loc['11310101'] = (0, u'货币(高)', 31, 0, '11310101')
db = database.connection('asset')
metadata = MetaData(bind=db)
mz_highlow = Table('mz_highlow', metadata, autoload=True)
mz_highlow_alloc = Table('mz_highlow_alloc', metadata, autoload=True)
mz_highlow_asset = Table('mz_highlow_asset', metadata, autoload=True)
mz_highlow_pos = Table('mz_highlow_pos', metadata, autoload=True)
mz_highlow_nav = Table('mz_highlow_nav', metadata, autoload=True)
#
# 处理替换
#
if optreplace:
mz_highlow_nav.delete(mz_highlow_nav.c.mz_highlow_id.between(optid, optid + 9)).execute()
mz_highlow_pos.delete(mz_highlow_pos.c.mz_highlow_id.between(optid, optid + 9)).execute()
mz_highlow_asset.delete(mz_highlow_asset.c.mz_highlow_id == optid).execute()
mz_highlow_alloc.delete(mz_highlow_alloc.c.mz_highlow_id == optid).execute()
mz_highlow.delete(mz_highlow.c.globalid == optid).execute()
now = datetime.now()
# 导入数据: highlow
row = {
'globalid': optid, 'mz_type':opttype, 'mz_name': optname,
'mz_algo': 1, 'mz_high_id': opthigh, 'mz_low_id': optlow,
'mz_persistent': 0, 'created_at': func.now(), 'updated_at': func.now()
}
mz_highlow.insert(row).execute()
#
# 加载高风险资产仓位
#
index = None
if opthigh == 0:
optrisk = '1'
else:
df_high = asset_mz_markowitz_pos.load_raw(opthigh)
df_high_riskmgr = load_riskmgr(df_high.columns, df_high.index, optriskmgr)
index = df_high.index.union(df_high_riskmgr.index)
#
# 加载低风险资产仓位
#
if optlow == 0:
optrisk = '10'
else:
df_low = asset_mz_markowitz_pos.load_raw(optlow)
df_low_riskmgr = load_riskmgr(df_low.columns, df_low.index, optriskmgr)
if index is None:
index = df_low.index.union(df_low_riskmgr.index)
else:
index = index.union(df_low.index).union(df_low_riskmgr.index)
#
# 生成资产列表
#
for risk in [int(x) for x in optrisk.split(',')]:
highlow_id = str(string.atoi(optid) + (risk % 10))
name = optname + u"-等级%d" % (risk)
# 配置比例
ratio_h = (risk - 1) * 1.0 / 9
ratio_l = 1 - ratio_h
data_h = {}
if not df_high.empty:
df_high = df_high.reindex(index, method='pad')
df_high_riskmgr = df_high_riskmgr.reindex(index, method='pad')
for column in df_high.columns:
data_h[column] = df_high[column] * df_high_riskmgr[column] * ratio_h
df_h = pd.DataFrame(data_h)
data_l = {}
if not df_low.empty:
df_low = df_low.reindex(index, method='pad')
df_low_riskmgr = df_low_riskmgr.reindex(index, method='pad')
for column in df_low.columns:
data_l[column] = df_low[column] * df_low_riskmgr[column] * ratio_l
df_l = pd.DataFrame(data_l)
#
# 用货币补足空仓部分， 因为我们的数据库结构无法表示所有资产空
# 仓的情况（我们不存储仓位为0的资产）；所以我们需要保证任何一
# 天的持仓100%， 如果因为风控空仓，需要用货币补足。
#
if ratio_h > 0:
sr = ratio_h - df_h.sum(axis=1)
if (sr > 0.000099).any():
df_h['11310101'] = sr
if ratio_l > 0:
sr = ratio_l - df_l.sum(axis=1)
if (sr > 0.000099).any():
df_l['11310100'] = sr
#
# 合并持仓
#
df = pd.concat([df_h, df_l], axis=1)
#
# 导入数据: highlow_alloc
#
row = {
'globalid': highlow_id, 'mz_type':opttype, 'mz_name': name,
'mz_highlow_id': optid, 'mz_risk': risk / 10.0, 'created_at': func.now(), 'updated_at': func.now()
}
mz_highlow_alloc.insert(row).execute()
#
# 导入数据: highlow_asset
#
df_asset_tosave = df_asset.copy()
df_asset_tosave['mz_highlow_id'] = highlow_id
df_asset_tosave = df_asset_tosave.reset_index().set_index(['mz_highlow_id', 'mz_asset_id'])
asset_mz_highlow_asset.save([highlow_id], df_asset_tosave)
#
# 导入数据: highlow_pos
#
df = df.round(4) # 四舍五入到万分位
df[df.abs() < 0.0009999] = 0 # 过滤掉过小的份额
# print df.head()
df = df.apply(npu.np_pad_to, raw=True, axis=1) # 补足缺失
# df = DFUtil.filter_same_with_last(df) # 过滤掉相同
# if turnover >= 0.01:
# df = DFUtil.filter_by_turnover(df, turnover) # 基于换手率进行规律
df.index.name = 'mz_date'
df.columns.name='mz_asset_id'
# index
df['mz_highlow_id'] = highlow_id
df = df.reset_index().set_index(['mz_highlow_id', 'mz_date'])
# unstack
df_tosave = df.stack().to_frame('mz_ratio')
df_tosave = df_tosave.loc[(df_tosave['mz_ratio'] > 0)]
# save
# print df_tosave
asset_mz_highlow_pos.save(highlow_id, df_tosave)
# click.echo(click.style("highlow allocation complement! instance id [%s]" % (optid), fg='green'))
#
# 在context的记录id, 以便命令两中使用
#
ctx.obj['highlow'] = optid
click.echo(click.style("highlow allocation complement! instance id [%s]" % (optid), fg='green'))
class CrawlerLOG(Base):
__tablename__ = 'log'
id = Column(Integer, Sequence('log_id_seq'), primary_key=True)
level = Column(Integer)
text = Column(Text)
create_at = Column(DateTime(timezone=True), default=func.now())
class PageView(db.Model):
__tablename__ = 'pageviews'
id = db.Column(db.Integer, primary_key=True)
id_errorlog = db.Column(db.Integer)
page = db.Column(db.String(512))
endpoint = db.Column(db.String(255))
user_id = db.Column(db.Integer)
object_id = db.Column(db.Integer)
object_type = db.Column(db.String(100))
object_action = db.Column(db.String(100))
ip_address = db.Column(db.String(64))
created_at = db.Column(db.DateTime, default=func.now())
version = db.Column(db.String(100), default=__version__)
class logged(object):
def __init__(self, route, object_extractor=None):
self._route = route
self._object_extractor = object_extractor
def __getattr__(self, attr):
return getattr(self._route, attr)
def __call__(self, *args, **kwargs):
if not current_app.config.get('INDEXING_ENABLED', True):
return self._route(*args, **kwargs)
log = PageView(page=request.full_path,
endpoint=request.endpoint,
user_id=current_user.id,
ip_address=request.remote_addr,
version=__version__)
errorlog = None
log.object_id, log.object_type, log.object_action, reextract_after_request = self.extract_objects(
*args, **kwargs)
db_session.add(
log) # Add log here to ensure pageviews are accurate
try:
return self._route(*args, **kwargs)
except Exception as e:
db_session.rollback(
) # Ensure no lingering database changes remain after crashed route
db_session.add(log)
errorlog = ErrorLog.from_exception(e)
db_session.add(errorlog)
db_session.commit()
raise_with_traceback(e)
finally:
# Extract object id and type after response generated (if requested) to ensure
# most recent data is collected
if reextract_after_request:
log.object_id, log.object_type, log.object_action, _ = self.extract_objects(
*args, **kwargs)
if errorlog is not None:
log.id_errorlog = errorlog.id
db_session.add(log)
db_session.commit()
def object_extractor(self, extractor):
self._object_extractor = extractor
return self
def extract_objects(self, *args, **kwargs):
if self._object_extractor is None:
return None, None, None, False
try:
object_info = self._object_extractor(*args, **kwargs)
except Exception as e:
logger.warning("Error using object extractor: " + str(e))
object_info = {'id': (-1), 'type': None}
assert isinstance(
object_info,
dict), "Object extractors must return a dictionary."
assert len(set([
'id', 'type'
]).difference(object_info.keys())) == 0 and len(
set(object_info.keys()).difference(
['id', 'type', 'action', 'may_change'])
) == 0, "Object extractors must at least include the keys 'id' and 'type', and optionally 'action' and 'may_change'. Was provided with: {}".format(
str(list(object_info.keys())))
object_info = defaultdict(lambda: None, object_info)
return object_info['id'], object_info['type'], object_info[
'action'], object_info['may_change'] or False
def newItem():
"""
Create new item and store it in database.
"""
if 'username' not in login_session:
flash('Please log in')
return redirect(url_for('showLogin'))
categories = session.query(Category).all()
items = session.query(Item).all()
if request.method == 'POST':
if 'username' not in login_session:
flash('Please log in')
return redirect(url_for('showLogin'))
if request.form['title'] not in [item.title for item in items]:
if (
request.form['category'] != 'Choose a category...'
and request.form['title']
):
category = session.query(Category).filter_by(
name=request.form['category']
).one()
if 'file' not in request.files and 'image' in request.files:
image = request.files['image']
if image.filename != '' and allowed_file(image.filename):
filename = secure_filename(image.filename)
if (image.filename in
[item.image.split('/')[-1] for item in items]):
flash('{} picture name already exists!'.format(
image.filename
))
return render_template(
'newitem.html', categories=categories
)
image_resize = Image.open(image)
image_resize = resizeimage.resize_contain(
image_resize, [200, 200]
)
image_resize.save(os.path.join(
app.config['UPLOAD_FOLDER'], filename
), image_resize.format)
image_path = 'item_images/' + filename
else:
image_path = 'item_images/sport-goods.jpg'
else:
image_path = 'item_images/sport-goods.jpg'
new_item = Item(
title=request.form['title'],
description=request.form['description'],
user_id=login_session['user_id'],
image=image_path,
cat_id=category.id,
init_time=func.now()
)
session.add(new_item)
session.commit()
flash('{} item has been created!'.format(
request.form['title']
))
return redirect(url_for('categoriesDashboard'))
else:
flash('Please, give a name and pick a category for your item!')
return render_template(
'newitem.html',
categories=categories,
user_profile_pic=login_session['picture'],
login_session_provider=login_session['provider']
)
else:
flash('{} title already exists!'.format(request.form['title']))
return render_template(
'newitem.html',
categories=categories,
user_profile_pic=login_session['picture'],
login_session_provider=login_session['provider']
)
else:
return render_template(
'newitem.html',
categories=categories,
user_profile_pic=login_session['picture'],
login_session_provider=login_session['provider']
)
