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