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