async def _upsert_listing(self, txn, log, batch, update=False):
descs = self.table_descriptors
rendered = [(dataset.id, *self.render_listing(dataset))
for dataset in batch]
listing_values = ((id, rowdumps(row), *fields.values())
for id, row, fields, _ in rendered)
relvalues = sorted((key, value, id) for id, _, _, relfields in rendered
for key, values in relfields.items()
for value in values)
await txn.execute_query(
Query.into(descs[0].table).columns(
'id', 'row', *rendered[0][2].keys()).insert(
*listing_values).ignore().get_sql().replace(
'IGNORE', 'OR REPLACE'))
for key, group in groupby(relvalues, key=lambda x: x[0]):
group = list(group)
values = {(x[1], ) for x in group}
relations = [(x[1], x[2]) for x in group]
desc = [x for x in descs if x.key == key][0]
await self.site.db.update_listing_relations(desc,
values,
relations,
txn=txn)
for dataset in batch:
log.info(f'{"updated" if update else "added"} %r', dataset)
def get_user(self, u: types.User) -> tuple:
query = SQLLiteQuery.from_(USER).select('*').where(
USER.telegram_id.eq(u.id))
db_user = self._cursor.execute(query.get_sql()).fetchone()
if not db_user:
raise DoesNotExist(query.get_sql())
return db_user
def set_interval_seconds(self, u: types.User,
interval_seconds: int) -> int:
query = SQLLiteQuery.update(USER) \
.set(USER.interval_seconds, interval_seconds) \
.where(USER.telegram_id.eq(u.id))
_ = self._cursor.execute(query.get_sql())
self._con.commit()
return self._cursor.rowcount
def _get_active_session(self, u: types.User) -> tuple:
query = SQLLiteQuery.from_(SESSION).select(SESSION.id) \
.where(SESSION.user_telegram_id.eq(u.id)) \
.where(SESSION.stop_at.isnull())
session = self._cursor.execute(query.get_sql()).fetchone()
if not session:
raise DoesNotExist(query.get_sql())
return session
def register_user(self, u: types.User) -> None:
columns = 'telegram_id', 'interval_seconds', 'first_name', 'last_name', 'created_at'
values = u.id, constants.DEFAULT_INTERVAL_SECONDS, u.first_name, u.last_name, str(
datetime.now())
column_value_map = dict(zip(columns, values))
params = map(lambda col: f':{col}', columns)
query = SQLLiteQuery.into(USER).columns(*columns).insert(
*map(Parameter, params))
_ = self._cursor.execute(query.get_sql(), column_value_map)
self._con.commit()
def create_default_categories(self,
u: types.User) -> Tuple[Tuple[int, str]]:
categories = tuple((u.id, category_name)
for category_name in constants.DEFAULT_CATEGORIES)
query = SQLLiteQuery.into(CATEGORY).columns(
CATEGORY.user_telegram_id, CATEGORY.name).insert(*categories)
_ = self._cursor.execute(query.get_sql())
self._con.commit()
return categories
def get_last_started_session(self, u: types.User) -> int:
query = SQLLiteQuery().from_(SESSION).select('*') \
.where(SESSION.user_telegram_id.eq(Parameter(':user_id'))) \
.orderby(SESSION.start_at, order=Order.desc) \
.limit(1).get_sql()
session = self._cursor.execute(query, {'user_id': u.id}).fetchone()
if not session:
raise DoesNotExist()
return session
def get_category(self, category_id: int) -> tuple:
query = SQLLiteQuery.from_(CATEGORY).select('*') \
.where(CATEGORY.id == Parameter(':category_id'))
category = self._cursor.execute(query.get_sql(), {
'category_id': category_id
}).fetchone()
if not category:
raise DoesNotExist()
return category
def create_session(self, u: types.User):
columns = 'user_telegram_id', 'start_at'
values = u.id, str(datetime.now())
column_value_map = dict(zip(columns, values))
params = map(lambda col: f':{col}', columns)
query = SQLLiteQuery.into(SESSION).columns(*columns).insert(
*map(Parameter, params))
_ = self._cursor.execute(query.get_sql(), column_value_map)
self._con.commit()
created_session_id = self._cursor.lastrowid
return created_session_id
def stop_activity(self, activity_id: str, category_id: int):
_ = self.get_unstopped_activity(activity_id) # check existing
column_value_map = {'activity_id': activity_id}
query = SQLLiteQuery.update(TIMESHEET).set(
TIMESHEET.default_category_id, category_id).where(
TIMESHEET.activity_id == Parameter(':activity_id'))
self._cursor.execute(query.get_sql(), column_value_map)
self._con.commit()
if self._cursor.rowcount > 1:
raise RuntimeError()
def get_unstopped_activity(self, activity_id: str) -> tuple:
column_value_map = {'activity_id': activity_id}
query = SQLLiteQuery.from_(TIMESHEET).select(
'*').where((TIMESHEET.activity_id == Parameter(':activity_id'))
& (TIMESHEET.default_category_id.isnull())
& (TIMESHEET.user_category_id.isnull()))
activity = self._cursor.execute(query.get_sql(),
column_value_map).fetchone()
if not activity:
raise DoesNotExist()
return activity
def start_activity(self, session_id: int, interval_seconds: int) -> str:
finish = datetime.now()
start = finish - timedelta(0, interval_seconds)
columns = 'activity_id', 'session_id', 'start', 'finish'
values = str(uuid()), session_id, str(start), str(finish)
column_value_map = dict(zip(columns, values))
params = map(lambda col: f':{col}', columns)
query = SQLLiteQuery.into(TIMESHEET).columns(*columns).insert(
*map(Parameter, params))
_ = self._cursor.execute(query.get_sql(), column_value_map)
self._con.commit()
return column_value_map['activity_id']
def try_stop_session(self, u: types.User) -> bool:
"""Return True if session stopped or False otherwise
that means that there is no active session to stop"""
try:
opened_session = self._get_active_session(u)
except DoesNotExist:
return False
else:
session_id, *_ = opened_session
query = SQLLiteQuery.update(SESSION) \
.set(SESSION.stop_at, datetime.now()) \
.where(SESSION.id.eq(session_id))
self._cursor.execute(query.get_sql())
self._con.commit()
return True
def get_timesheet_frame_by_sessions(self,
session_ids: tuple) -> List[tuple]:
if not session_ids:
raise DoesNotExist()
query = SQLLiteQuery().from_(TIMESHEET) \
.inner_join(CATEGORY).on(Criterion.all((
TIMESHEET.default_category_id.notnull(),
TIMESHEET.default_category_id.eq(CATEGORY.id),
))) \
.select(
TIMESHEET.star,
# (TIMESHEET.finish - TIMESHEET.start).as_('activity_duration'), # datetime in sqlite in str
CATEGORY.name) \
.where(TIMESHEET.session_id.isin(session_ids)).get_sql()
timesheet_frame = self._cursor.execute(query).fetchall()
if not timesheet_frame:
raise DoesNotExist()
return timesheet_frame
def test_make_tables_factory_with_dialect_query_cls(self):
t1, t2 = SQLLiteQuery.Tables("abc", "def")
q1 = t1.select("1")
q2 = t2.select("2")
self.assertIs(q1.dialect, Dialects.SQLITE)
self.assertIs(q2.dialect, Dialects.SQLITE)
async def store_db_version(self, txn):
metadata = Table('metadata')
await txn.exq(
Query.into(metadata).columns(metadata.key, metadata.value).insert(
'database_version', self.db.VERSION))
def test_table_factory_with_dialect_query_cls(self):
table = SQLLiteQuery.Table("abc")
q = table.select("1")
self.assertIs(q.dialect, Dialects.SQLITE)
def test_update_with_bool(self):
q = SQLLiteQuery.update(self.table_abc).set(self.table_abc.foo, True)
self.assertEqual('UPDATE "abc" SET "foo"=1', str(q))
def test_update_with_limit_order(self):
q = (SQLLiteQuery.update(self.table_abc).set(
self.table_abc.lname, "test").limit(1).orderby(self.table_abc.id))
self.assertEqual(
'UPDATE "abc" SET "lname"=\'test\' ORDER BY "id" LIMIT 1', str(q))
def test_delete_with_orderby_limit(self):
q = SQLLiteQuery.from_(self.table_abc).orderby(
self.table_abc.id).limit(1).delete()
self.assertEqual('DELETE FROM "abc" ORDER BY "id" LIMIT 1', str(q))
def list_categories(self, u: types.User) -> Tuple[tuple]:
"""Get categories for current user"""
query = SQLLiteQuery.from_(CATEGORY).select('*').where(
CATEGORY.user_telegram_id.eq(u.id))
categories = self._cursor.execute(query.get_sql())
return tuple(categories)