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    except IndexError:
36        return None
37    timestamp = datetime.strptime(temp[8], c.TS_FORMAT)
38    delta = datetime.now() - timestamp
39
40    if delta > timedelta(minutes=c.YT_TTL_MINUTES):
41        raise IndexError
42        
43    return {
44        "id": temp[0],
45        "title": temp[1],
46        "description": temp[2],
47        "uploader": temp[3],
48        "duration": temp[4],
49        "height": temp[5],
50        "width": temp[6],
51        "url": temp[7],
52    }
53
54def get_info(video):
55    info = get_video_from_cache(video)
56
57    if info is not None:
58        return info
59    
60    info = get_info_ytdl(video)
61    if info is not None:
62        cache_video(info)
63
64    return info
65
66def clear_cache():
67    execute_query("DELETE FROM videos;")
68    execute_query("VACUUM;")
69
70execute_query(create_query)