spacesavers2_pdq_create_db

#!/usr/bin/env python3
# pqd = pretty darn quick

from src.VersionCheck import version_check
from src.VersionCheck import __version__
from src.utils import *

version_check()

# import required modules
import sqlite3
import textwrap
import argparse
from pathlib import Path

def main():
    elog = textwrap.dedent(
        """\
    Version:
        {}
    Example:
        > spacesavers2_pdq_create_db -f /path/to/sqlitedbfile
        """.format(
            __version__
        )
    )
    parser = argparse.ArgumentParser(
        description="spacesavers2_pdq_create_db: create a sqlitedb file with the optimized schema.",
        epilog=elog,
        formatter_class=argparse.RawDescriptionHelpFormatter,
    )
    parser.add_argument(
        "-f",
        "--filepath",
        dest="filepath",
        required=True,
        type=str,
        help="spacesavers2_pdq_create_db will create this sqlitedb file",
    )
    parser.add_argument(
        "-o",
        "--overwrite",
        dest="overwrite",
        required=False,
        action=argparse.BooleanOptionalAction,
        help="overwrite output file if it already exists. Use this with caution as it will delete existing file and its contents!!",
    )
    parser.add_argument("-v", "--version", action="version", version=__version__)

    global args
    args = parser.parse_args()

    filepath = args.filepath
    p = Path(filepath).absolute()
    pp = p.parents[0]
    if not os.access(pp, os.W_OK):
        exit("ERROR: {} folder exists but cannot be written to".format(pp))
    if os.path.exists(p):
        if not args.overwrite:
            exit("ERROR: {} file exists and overwrite argument is not selected!".format(p))
        if not os.access(p, os.W_OK):
            exit("ERROR: {} file exists but is not writeable/appendable".format(p))
        if args.overwrite and os.access(p, os.W_OK):
            os.remove(p)

    # Connect to the SQLite database (or create it if it doesn't exist)
    conn = sqlite3.connect(p)
    cursor = conn.cursor()

    # Create the "users" table
    cursor.execute('''CREATE TABLE IF NOT EXISTS users (
                        user_id INTEGER PRIMARY KEY,
                        username TEXT NOT NULL,
                        first_name TEXT NOT NULL,
                        last_name TEXT NOT NULL
                    )''')

    # Create the "dates" table
    cursor.execute('''CREATE TABLE IF NOT EXISTS dates (
                        date_int INTEGER PRIMARY KEY,
                        date_text TEXT UNIQUE NOT NULL
                    )''')
    
    # Create datamounts table
    cursor.execute('''CREATE TABLE IF NOT EXISTS datamounts (
                        datamount_id INTEGER PRIMARY KEY,
                        datamount_name TEXT UNIQUE NOT NULL
                    )''')


    # Create the "orders" table with a foreign key constraint
    cursor.execute('''CREATE TABLE IF NOT EXISTS datapoints (
                        datapoint_id INTEGER PRIMARY KEY,
                        date_int INTEGER,
                        datamount_id INTEGER,
                        user_id INTEGER,
                        ninodes INTEGER,
                        nbytes INTEGER,
                        FOREIGN KEY (user_id) REFERENCES users(user_id),
                        FOREIGN KEY (datamount_id) REFERENCES datamounts(datamount_id),
                        FOREIGN KEY (date_int) REFERENCES dates(date_int)
                    )''')

    # Commit changes and close the connection
    conn.commit()
    conn.close()

if __name__ == "__main__":
    main()