sqlite permissions

sqlite_permissions.py

import sqlite3 as lite
from random import randint


def real_random(total):
    result = []
    while True:
        new_value = randint(1, 10000)
        if new_value not in result:
            result.append(new_value)
            if len(result) == total:
                return result

con = lite.connect(':memory:')

cur = con.cursor()
cur.execute("PRAGMA foreign_keys = ON")
cur.execute("CREATE TABLE users(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)")
cur.execute("CREATE TABLE groups(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)")
cur.execute("CREATE TABLE perms(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, example TEXT)")
# table rels
# FOREIGN KEY(trackartist) REFERENCES artist(artistid)

cur.execute('CREATE TABLE "USERS_GROUPS"("id" INTEGER PRIMARY KEY, "id_user" INTEGER, "id_group" INTEGER, FOREIGN KEY(id_user) REFERENCES users(id), FOREIGN KEY(id_group) REFERENCES groups(id))')
cur.execute('CREATE TABLE "GROUPS_PERMS"("id" INTEGER PRIMARY KEY, "id_group" INTEGER, "id_perm" INTEGER, FOREIGN KEY(id_group) REFERENCES groups(id), FOREIGN KEY(id_perm) REFERENCES perms(id))')

cur.execute('CREATE INDEX USERS_GROUPSIndex ON USERS_GROUPS (id_user, id_group);')
cur.execute('CREATE INDEX GROUPS_PERMSIndex ON GROUPS_PERMS (id_group, id_perm);')
for x in range(1, 10000):
    cur.execute("INSERT INTO perms (name) VALUES (?)", ("perm_%s" % x,))
con.commit()
for x in range(1, 10000):
    cur.execute("INSERT INTO groups (name) VALUES (?)", ("group_%s" % x,))
    random_list = real_random(10)
    for y in range(10):
        cur.execute("INSERT INTO GROUPS_PERMS (id_group, id_perm) VALUES (?, ?)", (x, randint(1, random_list[y])))
con.commit()
for x in range(1, 1000000):
    cur.execute("INSERT INTO users (name) VALUES (?)", ("user_%s" % x,))
    random_list = real_random(10)
    for y in range(10):
        cur.execute("INSERT INTO USERS_GROUPS (id_user, id_group) VALUES (?, ?)", (x, randint(1, random_list[y])))

con.commit()

def get_info_user(_id):
    result = cur.execute("SELECT * FROM users JOIN USERS_GROUPS ON USERS_GROUPS.id_user = users.id JOIN groups ON USERS_GROUPS.id_group = groups.id JOIN GROUPS_PERMS ON GROUPS_PERMS.id_group = groups.id JOIN perms ON GROUPS_PERMS.id_perm = perms.id WHERE users.id='"+str(_id)+"' GROUP BY users.id")
    return result.fetchall()