Orderer/model.py

import sqlite3 as db
import sys
import uuid

connection = None
cursor = None


def connect():
    global connection
    global cursor
    if connection is None or cursor is None:
        try:
            connection = db.connect('boerse.db')

            cursor = connection.cursor()

        except db.Error as e:
            print("Database error %s:" % e.args[0])
            sys.exit(1)

        # finally:
        #     if con is not None:
        #         con.close()


def setup():
    connect()

    print('Database setup...')

    replace = True
    if replace:
        cursor.execute("DROP TABLE users")
        cursor.execute("DROP TABLE stocks")
        cursor.execute("DROP TABLE ownership")
        cursor.execute("DROP TABLE sessions")

    cursor.execute('''
                CREATE TABLE IF NOT EXISTS users(
                    username VARCHAR(10) UNIQUE NOT NULL, 
                    password VARCHAR(6) NOT NULL)
                ''')
    cursor.execute('''
                CREATE TABLE IF NOT EXISTS stocks(
                    name VARCHAR(10) UNIQUE NOT NULL, 
                    total_available INTEGER NOT NULL)
                ''')
    cursor.execute('''
                CREATE TABLE IF NOT EXISTS ownership(
                    user_id INTEGER NOT NULL,
                    stock_id INTEGER NOT NULL,
                    amount INTEGER NOT NULL DEFAULT 0,
                    FOREIGN KEY (user_id) REFERENCES users(rowid),
                    FOREIGN KEY (stock_id) REFERENCES stocks(rowid),
                    UNIQUE (user_id, stock_id)
                )
                ''')
    cursor.execute('''
                CREATE TABLE IF NOT EXISTS sessions(
                    user_id INTEGER NOT NULL,
                    session_id STRING NOT NULL,
                    FOREIGN KEY (user_id) REFERENCES users(rowid)
                )
                ''')


def login(username, password):
    connect()

    cursor.execute('''
                SELECT rowid
                FROM users
                WHERE username = ?
                AND password = ?
                ''', (username, password))
    user_id = cursor.fetchone()
    if user_id:
        return new_session(user_id)
    else:
        return None


def register(username, password):
    connect()

    cursor.execute('''
                INSERT INTO users 
                (username, password)
                VALUES (? , ?)
                ''', (username, password))


def new_session(user_id):
    connect()

    session_id = str(uuid.uuid4())

    cursor.execute('''
                INSERT INTO SESSIONS 
                (user_id, session_id)
                VALUES (? , ?)
                ''', (user_id[0], session_id))

    return session_id


def drop_old_sessions():
    connect()
    # TODO: test
    cursor.execute('''
                DELETE FROM sessions s1
                WHERE 
                    (SELECT COUNT(*) as newer
                     FROM sessions s2
                     WHERE s1.user_id = s2.user_id
                     AND s1.rowid < s2.rowid) >= 10
                ''')


def user_exists(username):
    cursor.execute('''
                SELECT rowid
                FROM users
                WHERE username = ?
                ''', (username,))

    if cursor.fetchone():
        return True
    else:
        return False