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