mysql_json_implementation

import mysql.connector
from dotenv import load_dotenv
import json
from pymemcache.client import base
import time

load_dotenv()

conn = None
mc = base.Client(('localhost','11211'))

def main():
    connect()
    init()
    scenario()

def connect():
    """ Connect to MySQL database """
    global conn
    try:
        print('Connecting to MySQL database...')
        conn = mysql.connector.connect(host="34.30.23.118", user="root", password='|0zHafT),7"#<H&<', database='model_storage')

        if conn.is_connected():
            print('Connection is established.')
        else:
            print('Connection is failed.')
    except mysql.connector.Error as error:
        print(error)

def init():
    global conn
    cur = conn.cursor()

    cur.execute("CREATE TABLE IF NOT EXISTS students(id INT PRIMARY KEY AUTO_INCREMENT, student_data JSON NOT NULL);")
    cur.execute("CREATE TABLE IF NOT EXISTS lecture(id INT PRIMARY KEY AUTO_INCREMENT, lecture_data JSON NOT NULL);")
    cur.close()

def write(data):
    global conn
    cur = conn.cursor()
    cur.execute("INSERT INTO students VALUES (null, '%s')" % data)

    conn.commit()
    cur.close()

def read_v1(all=False):
    global conn
    cur = conn.cursor()
    sql = "SELECT * FROM students LIMIT 100" if not all else "SELECT * FROM students"
    print(sql)
    cur.execute(sql)
    result = cur.fetchall()
    return result

def read_v2():
    if not mc.get("read"):
        global conn
        cur = conn.cursor()
        cur.execute("SELECT * FROM students LIMIT 100")

        result = cur.fetchall()
        mc.set("read", json.dumps(result))
        return result

    return mc.get("read")

def read_id_v1(id=None):
    global conn
    cur = conn.cursor()
    cur.execute("SELECT * FROM students WHERE `student_data`->>'$.nim' = '%s' LIMIT 1" % str(id))
    result = cur.fetchall()
    return result

def read_id_v2(id=None):
    if not mc.get("read_%s" % str(id)):
        global conn
        cur = conn.cursor()
        cur.execute("SELECT * FROM students WHERE `student_data`->>'$.nim' = '%s' LIMIT 1" % str(id))
        result = cur.fetchall()
        mc.set("read_%s" % str(id), json.dumps(result))
        return result

    return mc.get("read_%s" % str(id))

def delete(id=None):
    global conn
    cur = conn.cursor()
    if(id):
        cur.execute("DELETE FROM students WHERE id=%s" % str(id))
    else:
        cur.execute("DELETE FROM students")
    conn.commit()

    cur.close()

def reset():
    global conn
    cur = conn.cursor()
    cur.execute("TRUNCATE TABLE students")
    cur.execute("TRUNCATE TABLE lecture")
    conn.commit()

    cur.close()

# Scenario:
# i tried basic read, read with caching, write, and delete
def scenario():
    # reset()

    example_data = {
        "name": "andika",
        "nim": 2105551019,
        "lecture": [
            {"name": "alprog", "description": "belajar algoritma"},
            {"name": "tbd", "description": "belajar basis data"},
            {"name": "ml", "description": "belajar machine learning"}
        ]
    }

    start = time.time()
    for i in range(5000):
        write(json.dumps(example_data))
    end = time.time()
    print(f"Write 5000 row time taken: {end - start:.6f} seconds")

    results = read_v1(all=True)

    start = time.time()
    for item in results:
        read_id_v1(json.loads(item[1])['nim'])
    end = time.time()
    print(f"Read 5000 row time taken: {end - start:.6f} seconds")

    start = time.time()
    for item in results:
        read_id_v2(json.loads(item[1])['nim'])
    end = time.time()
    print(f"Read with caching 5000 row time taken: {end - start:.6f} seconds")


    start = time.time()
    for item in results:
        delete(item[0])
    end = time.time()
    print(f"Delete 5000 row time taken: {end - start:.6f} seconds")


if __name__ == '__main__':
    main()