· 7 years ago · Oct 03, 2018, 12:22 PM
1#!/usr/bin/env python
2#ALL SQL CALLS WHERE SOMETHING = 'value' <- QOUTES
3import sqlite3, sys
4import config
5
6#username char(25) primary key not null,
7#authority int not null,
8#datecreated text not null,
9#datemodified text not null,
10#plays real,
11#wins int,
12#loses int,
13#disconnects int,
14#banned int not null,
15#datebanned text not null,
16
17colors = config.colors
18def echo(text,color):
19 print '[sql]' + colors[color] + str(text) + colors[9]
20
21###########################################FETCH###################################
22def get_element(sql,connect,table,nick,element):
23 value = ''
24 query = sql.execute(
25 "SELECT {0} FROM {1} "
26 "WHERE username = '{2}';".format(element,table,nick)
27 )
28 if query:
29 value = query.fetchone()
30 if value is not None:
31 return value[0]
32 else:
33 return None
34 else:
35 return None
36
37###########################################FETCH###################################
38#
39###########################################MAINTENANCE#############################
40
41def update_record(sql,connect,table,nick,element,value):
42 date = config.date_now()
43 query = sql.execute(
44 "SELECT {0} FROM {1} "
45 "WHERE username = '{2}';".format(element,table,nick)
46 )
47 if query:
48 record = query.fetchone()
49 currentRecord = record[0]
50 if currentRecord != value:
51 if element == 'plays' or element == 'wins' or element == 'loses' or element == 'disconnects':
52 value = int(currentRecord) + 1
53
54 sql.execute(
55 "UPDATE {0} SET {1} = '{2}' "
56 "WHERE username = '{3}';".format(table,element,value,nick)
57 )
58 sql.execute(
59 "UPDATE {0} SET {1} = '{2}' "
60 "WHERE username = '{3}';".format(table,'datemodified',date,nick)
61 )
62 commit(connect)
63 return True
64 else:
65 return False
66
67###########################################MAINTENANCE#############################
68#
69###########################################HIGHER#LEVEL#############################
70
71def check_create_user(sql,connect,table,username,authority,message):
72 #checks if users exists and adds those not in db
73 date = config.date_now()
74 try:
75 query = sql.execute(
76 "SELECT {0} FROM {1} "
77 "WHERE username = '{2}';".format('username',table,username)
78 )
79 if query:
80 nickname = query.fetchone()
81 if nickname is None:
82 name = username
83 wins=loses=plays=disconnects=0
84 sql.execute(
85 "INSERT INTO {0} (username, authority, datecreated, datemodified, plays, wins, loses, disconnects, banned, datebanned)"
86 "VALUES ('{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}');"
87 .format(table, name, authority, date, date, wins, loses, plays, disconnects, 0, 0)
88 )
89
90 user = sql.execute("SELECT * FROM {0} WHERE username = '{1}'".format(table, name))
91 if user.fetchone() is not None:
92 commit(connect)
93 return True
94 else:
95 raise Exception('[!] Unable to create user! ({0},{1},{2})'.format(username,authority,message))
96 else:
97 return True
98 except Exception as e:
99 echo('[!] Unable to check or add user ({0}, {1}, {2}): {3}'.format(username,authority,message,e),8)
100 config.log_event('Error: User query! {0}, {1}, {2}, {3}'.format(username,authority,message,e))
101 return False
102
103###########################################HIGHER#LEVEL#############################
104#
105###########################################LOWER#LEVEL##############################
106def check_create_table(sql,connect,table):
107 #test the table exist and creates one if not.
108 try:
109 tableCheck = sql.execute("SELECT * FROM {0} ORDER BY 1 ASC LIMIT 1".format(table))
110 if tableCheck:
111 return True
112 except:
113 echo('[-] No table found.',7)
114 try:
115 #DROP TABLE IF EXISTS {0};
116 script = """
117 CREATE TABLE {0} (username char(25) primary key not null, authority int not null, datecreated text not null, datemodified text not null, plays int, wins int, loses int, disconnects int, banned int not null, datebanned text not null);
118 CREATE INDEX user_index ON {0} (username);
119 """.format(table)
120
121 sql.executescript(script)
122 echo('[+] Created table {0} with schema: {1}'.format(table,script),6)
123 config.log_event('[+] Created table {0} and schema'.format(table))
124 return True
125 except Exception as e:
126 echo('[!] Cannot create table. Exiting . . .',8)
127 config.log_event('Error: Unable to create table! {0}'.format(e))
128 return False
129
130def close(connect):
131 connect.close()
132 return
133
134def commit(connect):
135 try:
136 connect.commit()
137 return True
138 except Exception as e:
139 echo('[!] Could not commit db',8)
140 config.log_event('[!] Could not commit db')
141 return False
142
143def connect_sql():
144 try:
145 sqlFile = config.dbFile #From config file
146 table = config.dbTable #From config file
147 connect = sqlite3.connect(sqlFile)
148 sql = connect.cursor()
149 return sql, connect, table
150 except Exception as e:
151 echo('[!] unable to connect to db: {0}'.format(e),8)
152 config.log_event('Error: Unable to connect to db! {0}'.format(e))
153 return False
154###########################################LOWER#LEVEL##############################
155
156#username char(25) primary key not null,
157#authority int not null,
158#datecreated text not null,
159#datemodified text not null,
160#plays real,
161#wins int,
162#loses int,
163#disconnects int,
164#banned int not null,
165#datebanned text not null,