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