class MDatabase:
"""
Sqlite database for Marnatarlo
"""
def __init__(self, dbname):
self.dbname = dbname
try:
fh = open(dbname)
except IOError as e:
conn = sqlite3.connect(dbname)
curs = conn.cursor()
curs.execute("Create table users (name text unique, password text)")
curs.execute("Create table stats(name text, played INTEGER, won INTEGER, FOREIGN KEY(name) REFERENCES users(name))")
conn.commit()
curs.close()
self.__dbpool = ConnectionPool('sqlite3', self.dbname)
def shutdown(self):
"""
Shutdown function
It's a required task to shutdown the database connection pool:
garbage collector doesn't shutdown associated thread
"""
self.__dbpool.close()
def returnOk(self, o):
return True
def returnFailure(self, o):
return False
def returnResult(self, result):
return result
def _returnResult(self, deferred, count=None):
if count:
return self.__dbpool.fetchmany(count)
else:
return self.__dbpool.fetchall()
def execSql(self, sql, params={}):
"""
Exec an SQL command, return True or False
@type sql C{str}
@param sql SQL command
"""
def run(sql, params):
return self.__dbpool.runQuery(sql, params)
d = run(sql, params)
d.addCallback(self._returnResult)
d.addErrback(self.returnFailure)
d.addCallback(self.returnResult)
return d
def fetch(self, sql, params={}):
"""
Exec an SQL command, fetching the rows resulting
@type sql C{str}
@param sql SQL command
"""
def run(sql, params):
return self.__dbpool.runQuery(sql, params)
d = run(sql, params)
d.addCallback(self.returnResult)
d.addErrback(self.returnFailure)
return d
def get_stats(self, user):
query = "SELECT * FROM stats WHERE name=?"
return self.fetch(query, (user,))
def user_won(self, user):
query = "UPDATE stats SET won=won+1 WHERE name=?"
return self.execSql(query, (user,))
def user_play(self, user):
query = "UPDATE stats SET played=played+1 WHERE name=?"
return self.execSql(query, (user,))
def save_user(self, user, passwd):
"""
Save user / password into db
@type user C{str}
@type password C{str}
"""
def insert_user(users, user, passwd):
if len(users) > 0:
return self.returnFailure(users)
query = "INSERT INTO users(name, password) VALUES (?, ?)"
self.execSql(query, (user, passwd,))
query = "INSERT INTO stats(name, played, won) VALUES (?, 0,0)"
return self.execSql(query, (user,))
return self.get_user_login_info(user).addCallback(insert_user, user, passwd)
def get_user_login_info(self, user):
"""
Get a tuple, user / password
@type user C{str}
"""
query = "SELECT * FROM users WHERE name=?";
return self.fetch(query, (user,))
def get_all_users(self):
"""
Get all users from db
"""
query = "SELECT u.name, s.played, s.won FROM users AS u, stats AS s WHERE u.name = s.name";
return self.fetch(query)
