1import sqlite3
 2from contextlib import closing
 3from datetime import datetime, timedelta
 4from fxyoutube.yt_info import get_info_ytdl
 5import fxyoutube.constants as c
 6
 7create_query = '''
 8CREATE TABLE IF NOT EXISTS videos (
 9    id TEXT PRIMARY KEY,
10    title TEXT NOT NULL,
11    description TEXT NOT NULL,
12    uploader TEXT NOT NULL,
13    uploader_id TEXT NOT NULL,
14    video_ext TEXT NOT NULL,
15    height TEXT NOT NULL,
16    width TEXT NOT NULL,
17    url TEXT NOT NULL,
18    timestamp DATETIME DEFAULT (datetime('now','localtime'))
19);'''
20
21def execute_query(query: str, attributes: list = []):
22    with sqlite3.connect(c.DB_URL) as db_connection:
23        with closing(db_connection.cursor()) as db_cursor:
24            return list(db_cursor.execute(query, attributes))
25
26def get_video(video_id):
27    return execute_query("SELECT * FROM videos WHERE id = (?);", [ video_id ])
28
29def cache_video(info):
30    return execute_query("INSERT OR REPLACE INTO videos (id, title, description, uploader, uploader_id, video_ext, height, width, url) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?);", list(info.values()))
31
32def get_info(video):
33    result = get_video(video)
34
35    try:
36        temp = result[0]
37        timestamp = datetime.strptime(temp[9], c.TS_FORMAT)
38        delta = datetime.now() - timestamp
39
40        if delta > timedelta(seconds=30):
41            raise IndexError
42        
43        info = {
44            "id": temp[0],
45            "title": temp[1],
46            "description": temp[2],
47            "uploader": temp[3],
48            "uploader_id": temp[4],
49            "video_ext": temp[5],
50            "height": temp[6],
51            "width": temp[7],
52            "url": temp[8],
53        }
54
55    except IndexError:
56        info = get_info_ytdl(video)
57        if info is not None:
58            cache_video(info)
59
60    return info
61
62def clear_cache():
63    execute_query("DELETE FROM videos;")
64    execute_query("VACUUM;")
65
66execute_query(create_query)