async def update_user_index(db: aiosqlite.Connection,
user: str,
index: int,
insert: bool = False) -> None:
if insert:
async with db.execute(
'''INSERT INTO "user_status" VALUES (?, ?)''',
(user, index)):
pass
else:
async with db.execute(
'''UPDATE "user_status" SET "index" = ? WHERE "user_id" = ?''',
(index, user)):
pass
await db.commit()
async def get_message_part_cid(conn: aiosqlite.Connection, message_id: int,
cid: str) -> sqlite3.Row:
async with conn.execute(
'SELECT * FROM message_part WHERE message_id = ? AND cid = ?',
(message_id, cid)) as cur:
data = await cur.fetchone()
return data
async def __write_permission_into_dataBase(
db: aiosqlite.Connection,
id: str,
description: str = "",
parent: str = ROOT_PERMISSION_ID) -> NoReturn:
try:
async with db.cursor() as cursor:
await cursor.execute(
'''
SELECT * FROM Permissions
WHERE PermissionId=?
''', (id, ))
if await cursor.fetchone():
await cursor.execute(
'''
UPDATE Permissions SET
PermissionId=?,Description=?,PermissionParent=?
WHERE PermissionId=?
''', (id, description, parent, id))
else:
await cursor.execute(
'''
INSERT INTO Permissions (PermissionId,Description,PermissionParent)
VALUES (?,?,?)
''', (id, description, parent))
except:
await db.rollback()
raise
finally:
await db.commit()
async def _authenticate(self, db: aiosqlite.Connection, email: str, password: str):
if not self.is_initialised:
await self._initialise(db)
async with db.execute('SELECT salt, password FROM users WHERE email = ?', (email,)) as cursor:
async for salt, hashed_password in cursor:
return self._is_valid_password(password, salt, hashed_password)
return False
async def get_enabled(db: aiosqlite.Connection) -> bool:
query = 'SELECT enabled FROM vim_enabled ORDER BY ROWID DESC LIMIT 1'
async with db.execute(query) as cursor:
ret = await cursor.fetchone()
if ret is None:
return True
else:
enabled, = ret
return bool(enabled)
async def get_messages(conn: aiosqlite.Connection) -> List[dict]:
async with conn.execute(
'SELECT * FROM message ORDER BY created_at ASC') as cur:
data = await cur.fetchall()
data = list(map(dict, data))
for row in data:
_prepare_message_row_inplace(row)
return data
async def create_database_schema(conn: aiosqlite.Connection) -> None:
"""Generate tables in database"""
schema = "db/database.sql"
with open(schema, encoding="UTF-8") as file:
sql_script = file.read()
async with conn.cursor() as cursor:
await cursor.executescript(sql_script)
await conn.commit()
async def get_today(db: aiosqlite.Connection) -> str:
await ensure_today_table_exists(db)
query = 'SELECT msg FROM today ORDER BY ROWID DESC LIMIT 1'
async with db.execute(query) as cursor:
row = await cursor.fetchone()
if row is None:
return 'not working on anything?'
else:
return esc(row[0])
async def _initialize_db(self, db: Connection) -> None:
async with db.execute("SELECT COUNT(*) FROM sqlite_master") as c:
count = await c.fetchone()
assert count is not None
if count[0] > 0:
return
async with aiofiles.open(db_schema_path()) as f:
sql = await f.read()
await db.executescript(sql)
async def get_message(conn: aiosqlite.Connection,
message_id: int) -> Optional[dict]:
async with conn.execute('SELECT * FROM message WHERE id = ?',
(message_id, )) as cur:
row = await cur.fetchone()
if not row:
return None
row = dict(row)
_prepare_message_row_inplace(row)
return row
async def test_get_post(client: Client, db: aiosqlite.Connection) -> None:
post = await client.create("test title", "test text")
async with db.execute(
"SELECT title, text, owner, editor FROM posts WHERE id = ?", [post.id]
) as cursor:
record = await cursor.fetchone()
assert record["title"] == "test title"
assert record["text"] == "test text"
assert record["owner"] == "test_user"
assert record["editor"] == "test_user"
async def __check_permittee_permission(db: aiosqlite.Connection,
permission: PermissionId,
permittee: PermitteeId) -> bool:
async with db.execute(
'''
SELECT * FROM CommandPermission
WHERE PermissionId=? AND PermitteeId=?
''', (permission.id, permittee.id)) as cursor:
if await cursor.fetchone():
return True
return False
async def __get_permission_from_database(
db: aiosqlite.Connection,
permittee: PermitteeId) -> Iterable[PermissionId]:
async with db.cursor() as cursor:
await cursor.execute(
'''
SELECT * FROM CommandPermission
WHERE PermissionId=?
''', (permittee.id, ))
async for row in cursor:
yield await load_permission_from_dataBase(db, row[0])
async def __get_permittee_from_database(
db: aiosqlite.Connection,
permission: PermissionId) -> Iterable[PermitteeId]:
async with db.cursor() as cursor:
await cursor.execute(
'''
SELECT * FROM CommandPermission
WHERE PermissionId=?
''', (permission.id, ))
async for row in cursor:
yield PermitteeId(id=row[1], directParents=tuple())
async def _search_playlist(db: aiosqlite.Connection, playlist_id: str,
search_terms: str) -> List[YouTubeVideo]:
query = ('SELECT video_id, title, playlist_id '
'FROM youtube_videos '
'WHERE playlist_id = ? AND title MATCH ? ORDER BY rank')
# Append a wildcard character to the search to include plurals etc.
if not search_terms.endswith('*'):
search_terms += '*'
async with db.execute(query, (playlist_id, search_terms)) as cursor:
results = await cursor.fetchall()
return [YouTubeVideo(*row) for row in results]
async def get_nearest_match(connection: aiosqlite.Connection, command: str):
nearest_matches = []
if len(command) > 2:
# produces too many matches with only 2 characters in a command so ignore this
async with connection.execute(" SELECT * FROM commands; ") as cursor:
async for row in cursor:
if await is_nearest_match(command, row['command']):
nearest_matches.append(row['command'])
return nearest_matches
async def get_messages(conn: aiosqlite.Connection,
offset: int = 0,
limit: int = 30) -> List[dict]:
async with conn.execute(
'SELECT * FROM message ORDER BY created_at DESC LIMIT ? OFFSET ?',
(limit, offset)) as cur:
data = await cur.fetchall()
data = list(map(dict, data))
for row in data:
_prepare_message_row_inplace(row)
return data
async def fetch_post(db: aiosqlite.Connection, post_id: int) -> Dict[str, Any]:
async with db.execute(
"SELECT owner, editor, title, text FROM posts WHERE id = ?",
[post_id]) as cursor:
row = await cursor.fetchone()
if row is None:
raise RuntimeError(f"Post {post_id} doesn't exist")
return {
"id": post_id,
"owner": row["owner"],
"editor": row["editor"],
"title": row["title"],
"text": row["text"],
}
async def get_time_left(db: aiosqlite.Connection) -> int:
if not await get_enabled(db):
return 0
query = 'SELECT timestamp FROM vim_time_left ORDER BY ROWID DESC LIMIT 1'
async with db.execute(query) as cursor:
ret = await cursor.fetchone()
if ret is None:
return 0
else:
dt = datetime.datetime.fromisoformat(ret[0])
if dt < datetime.datetime.now():
return 0
else:
return (dt - datetime.datetime.now()).seconds
async def fetch_post(db: aiosqlite.Connection, post_id: int) -> Dict[str, Any]:
async with db.execute(
"select owner, editor, title, text, image from posts where id = ?",
[post_id]) as cursor:
row = await cursor.fetchone()
print(row)
if row is None:
raise RuntimeError(f"Post {post_id} does not exist")
return {
"id": post_id,
"owner": row["owner"],
"editor": row["editor"],
"title": row["title"],
"text": row["text"],
"image": row["image"],
}
async def __set_permission_with_permittee(db: aiosqlite.Connection,
permission: PermissionId,
permittee: PermitteeId) -> NoReturn:
try:
if await __check_permittee_permission(db, permission, permittee):
return
async with db.cursor() as cursor:
await cursor.execute(
'''
INSERT INTO CommandPermission (PermissionId,PermitteeId)
VALUES (?,?)
''', (permission.id, permittee.id))
except:
await db.rollback()
raise
finally:
await db.commit()
async def get_tracks(
self,
filter_query: str = None,
orderby: str = "name",
db_conn: aiosqlite.Connection = None,
) -> List[Track]:
"""Return all track records from the database."""
sql_query = "SELECT * FROM tracks"
if filter_query:
sql_query += " " + filter_query
sql_query += " ORDER BY %s" % orderby
async with aiosqlite.connect(self._dbfile, timeout=120) as db_conn:
db_conn.row_factory = aiosqlite.Row
return [
Track.from_db_row(db_row)
for db_row in await db_conn.execute_fetchall(sql_query, ())
]
async def _message_has_types(conn: aiosqlite.Connection, message_id: int,
types: List[str]) -> bool:
sql = """
SELECT
1
FROM
message_part
WHERE
message_id = ? AND
is_attachment = 0 AND
type IN ({0})
LIMIT
1
""".format(','.join('?' * len(types))) # noqa: S608
async with conn.execute(sql, (message_id, ) + types) as cur:
data = await cur.fetchone()
return data is not None
async def get_message_attachments(conn: aiosqlite.Connection,
message_id: int) -> Iterable[sqlite3.Row]:
sql = """
SELECT
message_id, cid, type, filename, size
FROM
message_part
WHERE
message_id = ? AND
is_attachment = 1
ORDER BY
filename ASC
"""
async with conn.execute(sql, (message_id, )) as cur:
data = await cur.fetchall()
return data
async def _get_message_part_types(conn: aiosqlite.Connection, message_id: int,
types: List[str]) -> sqlite3.Row:
sql = """
SELECT
*
FROM
message_part
WHERE
message_id = ? AND
type IN ({0}) AND
is_attachment = 0
LIMIT
1
""".format(','.join('?' * len(types)))
async with conn.execute(sql, (message_id, ) + types) as cur:
data = await cur.fetchone()
return data
async def __load_permission_from_dataBase(db: aiosqlite.Connection,
id: str) -> Dict[str, str]:
try:
async with db.cursor() as cursor:
await cursor.execute(
'''
SELECT * FROM Permissions
WHERE PermissionId=?
''', (id, ))
row = await cursor.fetchone()
if row:
return {"id": row[0], "description": row[1], "parent": row[2]}
await __write_permission_into_dataBase(db, id)
return await __load_permission_from_dataBase(db, id)
except:
await db.rollback()
raise
finally:
await db.commit()
async def test_get_post(client: _TestClient, db: aiosqlite.Connection) -> None:
async with db.execute(
"INSERT INTO posts (title, text, owner, editor) VALUES (?, ?, ?, ?)",
["title", "text", "user", "user"],
) as cursor:
post_id = cursor.lastrowid
await db.commit()
resp = await client.get(f"/api/{post_id}")
assert resp.status == 200
data = await resp.json()
assert data == {
"data": {
"editor": "user",
"id": "1",
"owner": "user",
"text": "text",
"title": "title",
},
"status": "ok",
}
async def _user_exists(cls, db: aiosqlite.Connection, email: str) -> bool:
async with db.execute('SELECT * FROM users WHERE email = ?', (email,)) as cursor:
async for _ in cursor:
return True
return False
async def query_target_id(origin_id: int, db: aiosqlite.Connection):
async with db.execute(
"SELECT `target_id` FROM `id_mapping` WHERE `origin_id` = ?",
(origin_id, )) as cursor:
return (await cursor.fetchone())[0]
async def _get_user_vim_bits(db: aiosqlite.Connection, ) -> Counter[str]:
vim_bits_query = 'SELECT user, SUM(bits) FROM vim_bits GROUP BY user'
async with db.execute(vim_bits_query) as cursor:
rows = await cursor.fetchall()
bits_counts = collections.Counter(dict(rows))
return bits_counts