-
Notifications
You must be signed in to change notification settings - Fork 0
/
databaseHandler.py
90 lines (79 loc) · 2.89 KB
/
databaseHandler.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
import sqlite3
import random
import time
from datetime import datetime
dbFile = 'cyberoam.db'
tableExist = False
maxID = None
''' function to check whether table exists '''
def checkTableExists(cursor):
global tableExist
if tableExist is False:
cursor.execute('''SELECT count(*) FROM sqlite_master WHERE type="table" AND name="users"''')
res = cursor.fetchone()
if res[0] is 1:
tableExist = True
return tableExist
''' function to get max id '''
def getMax(cursor):
global maxID
if maxID is None:
cursor.execute('''SELECT MAX(uid) FROM users''')
res = cursor.fetchone()
maxID = res[0]
return maxID
''' fucntion to get users details '''
def getCredentials(cursor):
# Check if table exists from cache variable
if checkTableExists(cursor) is True:
# If valid = 1 then user hasn't changed their password and login details are valid
# Also check that same id is not logged in twice in a single day
query = '''SELECT * FROM users WHERE uid = ? and valid = ? and lastlogin is not ? limit 1'''
random.seed(datetime.now())
uid = [random.randint(1, getMax(cursor)), 1, time.strftime('%Y-%m-%d')]
cursor.execute(query, uid)
return cursor.fetchone()
else:
# Table doesn't exist return false and deal with in calling function
print("Table doesn't exist.")
return False
''' function to insert user '''
def insertUser(cursor, userid, password):
# Checking if table exists
if checkTableExists(cursor) is False:
# If table doesn't exist create one
createTable = '''CREATE TABLE users (uid INTEGER PRIMARY KEY ON CONFLICT ROLLBACK AUTOINCREMENT NOT NULL UNIQUE, userid STRING UNIQUE NOT NULL, password STRING NOT NULL, valid BOOLEAN NOT NULL, lastlogin DATE)'''
cursor.execute(createTable)
else :
# formulating query
query = '''INSERT INTO {} (uid, userid, password, valid, lastlogin) VALUES (?,?,?,?,?)'''.format("users")
data = [None, userid, password, 1, None]
# Trying to insert the userdetails
try:
cursor.execute(query, data)
return True
# Couldn't insert
except:
return False
''' function to update user details with new one '''
def updateUser(cursor, userid, valid):
if checkTableExists(cursor) is True:
# If valid = 1 then user hasn't changed their password and login details are valid
query = '''UPDATE users set valid = ?, lastlogin = ? where uid = ?'''
# If login is valid set valid = 1 and last login = current date
if valid is 1:
data = [valid, time.strftime('%Y-%m-%d'), userid]
# If login is not valid set valid = 0 and last login = NULL
else:
data = [valid, time.strftime('%Y-%m-%d'), userid]
# Try executing the query
try:
cursor.execute(query, data)
return True
except:
print("Couldn't update values.")
return False
else:
# Table doesn't exist return false and deal with in calling function
print("Table doesn't exist.")
return False