2019-02-18 23:30:11 +01:00
import sqlite3
2019-02-19 19:44:41 +01:00
import config
2019-04-08 20:26:38 +02:00
from collections import OrderedDict
2019-02-18 23:30:11 +01:00
2019-03-31 21:46:57 +02:00
db_name = config . db_file
2019-02-18 23:30:11 +01:00
db_version = 1
ROLE_PLAYER = 10
ROLE_MASTER = 20
2019-04-08 20:26:38 +02:00
game_templates = OrderedDict ( [ ( ' fae ' , ' Fate Accelerated RPG ' ) ,
2020-03-15 13:49:49 +01:00
( ' fae-blank ' , ' Fate Accelerated RPG (empty character sheet) ' ) ,
( ' dnd ' , ' Dungeons & Dragons ' ) ,
( ' dnd-blank ' , ' Dungeons & Dragons (empty character sheet) ' ) ] )
2019-02-23 14:37:53 +01:00
room_container = ' room '
2019-02-23 15:27:48 +01:00
rolls_container = ' rolls '
2019-02-18 23:30:11 +01:00
2019-08-04 19:31:30 +02:00
preferred_container_order = [ ' general ' , ' aspects ' , ' stunts ' , ' skills ' , ' spells ' , ' inventory ' , ' rolls ' ]
preferred_key_order = { ' general ' : [ ' description ' , ' fatepoints ' , ' stress2 ' , ' stress4 ' , ' stress6 ' ] , ' aspects ' : [ ' highconcept ' , ' trouble ' ] }
2019-02-18 23:30:11 +01:00
def open_connection ( ) :
return sqlite3 . connect ( db_name )
def close_connection ( db ) :
db . close ( )
def table_exists ( db , table ) :
c = db . cursor ( )
query = c . execute ( ''' SELECT count(*) FROM sqlite_master WHERE type= ' table ' AND name=? ''' , ( table , ) )
result = query . fetchone ( )
if result [ 0 ] == 0 :
return False
else :
return True
2019-03-13 21:41:36 +01:00
class Group ( ) :
def __init__ ( self , gameid , groupid , groupname ) :
self . gameid = gameid
self . groupid = groupid
self . groupname = groupname
class Player ( ) :
def __init__ ( self , gameid , playerid , role , playername ) :
self . gameid = gameid
self . playerid = playerid
self . role = role
self . playername = playername
2019-02-18 23:30:11 +01:00
def init ( ) :
db = open_connection ( )
try :
print ( ' Initializing database... ' )
c = db . cursor ( )
if not table_exists ( db , ' Games ' ) :
2019-02-19 19:30:41 +01:00
c . execute ( ''' CREATE TABLE IF NOT EXISTS Games (gameid integer primary key autoincrement, version int, lastactivity datetime, gamename text, template text) ''' )
2019-02-18 23:30:11 +01:00
if not table_exists ( db , ' Groups ' ) :
c . execute ( ''' CREATE TABLE IF NOT EXISTS Groups (gameid integer, groupid integer primary key, groupname text) ''' )
if not table_exists ( db , ' Players ' ) :
c . execute ( ''' CREATE TABLE IF NOT EXISTS Players (gameid integer, playerid integer, role integer, playername text, PRIMARY KEY(gameid, playerid)) ''' )
if not table_exists ( db , ' Contents ' ) :
c . execute ( ''' CREATE TABLE IF NOT EXISTS Contents (gameid integer, playerid integer, container text, key text, value text, PRIMARY KEY(gameid, playerid, container, key)) ''' )
except :
print ( ' failed to initialize database ' )
raise
db . commit ( )
close_connection ( db )
2020-03-15 13:49:49 +01:00
def convert_template ( template ) :
"""
The blank versions of the character sheets use the same templates ( same dices )
but treat treat the sheet creation differently ( . . . by skipping it )
"""
if template == ' fae-blank ' :
return ' fae ' , True
if template == ' dnd-blank ' :
return ' dnd ' , True
return template , False
2019-02-19 19:30:41 +01:00
def new_game ( db , admin , playername , gamename , groupid , groupname , template ) :
2019-02-23 19:01:42 +01:00
"""
Creates a new game .
DOES NOT CHECK that the group is not already in a game .
Returns :
None - the group is already in a game .
the gameid otherwise .
"""
2019-02-19 19:30:41 +01:00
if template not in game_templates :
raise
2020-03-15 13:49:49 +01:00
2019-02-18 23:30:11 +01:00
c = db . cursor ( )
2019-02-23 19:01:42 +01:00
try :
query = c . execute ( ''' INSERT INTO Games(version, lastactivity, gamename, template) VALUES (?, datetime( ' now ' ), ?, ?) ''' , ( db_version , gamename , template ) )
except sqlite3 . IntegrityError :
# probably group is already in game
return None
2019-02-18 23:30:11 +01:00
gameid = c . lastrowid
2019-02-23 19:01:42 +01:00
try :
query = c . execute ( ''' INSERT INTO Groups(gameid, groupid, groupname) VALUES (?, ?, ?) ''' , ( gameid , groupid , groupname , ) )
except sqlite3 . IntegrityError :
return None
2019-02-18 23:30:11 +01:00
add_player ( db , admin , playername , gameid , ROLE_MASTER )
db . commit ( )
2019-02-20 22:44:18 +01:00
return gameid
2019-02-18 23:30:11 +01:00
def del_game ( db , gameid ) :
c = db . cursor ( )
query = c . execute ( ''' DELETE FROM Games WHERE gameid=? ''' , ( gameid , ) )
query = c . execute ( ''' DELETE FROM Groups WHERE gameid=? ''' , ( gameid , ) )
query = c . execute ( ''' DELETE FROM Players WHERE gameid=? ''' , ( gameid , ) )
query = c . execute ( ''' DELETE FROM Contents WHERE gameid=? ''' , ( gameid , ) )
db . commit ( )
2019-02-19 19:30:41 +01:00
def get_template_from_gameid ( db , gameid ) :
c = db . cursor ( )
query = c . execute ( ''' SELECT template FROM Games WHERE gameid=? ''' , ( gameid , ) )
result = query . fetchone ( )
if result == None :
# invalid gameid or database not updated
raise
template = result [ 0 ]
return template
def get_template_from_groupid ( db , groupid ) :
c = db . cursor ( )
query = c . execute ( ''' SELECT template FROM Games LEFT JOIN Groups ON Games.gameid = Groups.gameid WHERE groupid=? ''' , ( groupid , ) )
result = query . fetchone ( )
if result == None :
# group not in a game
return None
template = result [ 0 ]
return template
2019-03-13 21:41:36 +01:00
def get_group_from_playerid ( db , playerid ) :
c = db . cursor ( )
query = c . execute ( ''' SELECT Groups.gameid, groupid, groupname, playerid, role, playername FROM Groups LEFT JOIN Players ON Groups.gameid = Players.gameid WHERE playerid=? ''' , ( playerid , ) )
result = query . fetchone ( )
if result is None :
return None , None
group = Group ( result [ 0 ] , result [ 1 ] , result [ 2 ] )
player = Player ( result [ 0 ] , result [ 3 ] , result [ 4 ] , result [ 5 ] )
return group , player
2019-07-24 14:26:48 +02:00
def get_group_from_groupid ( db , groupid ) :
c = db . cursor ( )
query = c . execute ( ''' SELECT gameid, groupid, groupname FROM Groups WHERE groupid=? ''' , ( groupid , ) )
result = query . fetchone ( )
if result is None :
return None
group = Group ( result [ 0 ] , result [ 1 ] , result [ 2 ] )
return group
2019-02-18 23:30:11 +01:00
2019-02-20 22:43:59 +01:00
def get_player_role ( db , userid , gameid ) :
2019-02-18 23:30:11 +01:00
c = db . cursor ( )
query = c . execute ( ''' SELECT role FROM Players WHERE playerid=? AND gameid=? ''' , ( userid , gameid , ) )
result = query . fetchone ( )
2019-02-20 22:43:59 +01:00
if result is None :
return None
role = result [ 0 ]
return role
2019-02-23 14:37:53 +01:00
def get_player_name ( db , gameid , userid ) :
c = db . cursor ( )
query = c . execute ( ''' SELECT playername FROM Players WHERE playerid=? AND gameid=? ''' , ( userid , gameid , ) )
result = query . fetchone ( )
if result is None :
return None
name = result [ 0 ]
return name
2019-02-20 22:43:59 +01:00
def add_player ( db , userid , username , gameid , role ) :
old_role = get_player_role ( db , userid , gameid )
2019-02-19 19:30:41 +01:00
new_player_added = False
2019-02-20 22:43:59 +01:00
if old_role is None :
2019-02-19 19:30:41 +01:00
new_player_added = True
else :
2019-02-20 22:43:59 +01:00
if old_role > = role :
role = old_role
2019-02-18 23:30:11 +01:00
2019-02-20 22:43:59 +01:00
c = db . cursor ( )
2019-02-18 23:30:11 +01:00
query = c . execute ( ''' INSERT OR REPLACE INTO Players(gameid, playerid, role, playername) VALUES (?, ?, ?, ?) ''' , ( gameid , userid , role , username , ) )
db . commit ( )
2019-02-19 19:30:41 +01:00
return new_player_added
2019-02-24 15:00:03 +01:00
def get_masters_for_game ( db , gameid ) :
c = db . cursor ( )
query = c . execute ( ''' SELECT playerid FROM Players WHERE role=? AND gameid=? ''' , ( ROLE_MASTER , gameid , ) )
ret = [ ]
for player in query :
ret . append ( player [ 0 ] )
return ret
2019-03-19 21:55:31 +01:00
def get_all_players_from_game ( db , gameid ) :
c = db . cursor ( )
query = c . execute ( ''' SELECT gameid, playerid, role, playername FROM Players WHERE gameid=? ''' , ( gameid , ) )
ret = [ ]
for player in query :
p = Player ( player [ 0 ] , player [ 1 ] , player [ 2 ] , player [ 3 ] )
ret . append ( p )
return ret
2019-02-19 19:30:41 +01:00
def add_default_items ( db , userid , gameid , template ) :
if template == ' fae ' :
2019-08-04 19:31:30 +02:00
update_item ( db , gameid , userid , ' general ' , ' description ' , ' Describe your character in a few words. ' , False )
update_item ( db , gameid , userid , ' general ' , ' fatepoints ' , ' 3 ' , False )
update_item ( db , gameid , userid , ' general ' , ' refresh ' , ' 3 ' , False )
update_item ( db , gameid , userid , ' general ' , ' stress2 ' , ' Inactive ' , False )
update_item ( db , gameid , userid , ' general ' , ' stress4 ' , ' Inactive ' , False )
update_item ( db , gameid , userid , ' general ' , ' stress6 ' , ' Inactive ' , False )
2019-02-19 19:30:41 +01:00
update_item ( db , gameid , userid , ' stunts ' , ' 1 ' , ' Set this to your first stunt. ' , False )
2019-08-04 19:31:30 +02:00
update_item ( db , gameid , userid , ' aspects ' , ' highconcept ' , ' Set this to your high concept. ' , False )
update_item ( db , gameid , userid , ' aspects ' , ' trouble ' , ' Your character \' s trouble. ' , False )
update_item ( db , gameid , userid , ' aspects ' , ' 1 ' , ' Set this to your first aspect. ' , False )
2019-03-19 19:53:14 +01:00
update_item ( db , gameid , userid , ' approaches ' , ' careful ' , ' 0 ' , False )
update_item ( db , gameid , userid , ' approaches ' , ' clever ' , ' 0 ' , False )
update_item ( db , gameid , userid , ' approaches ' , ' flashy ' , ' 0 ' , False )
update_item ( db , gameid , userid , ' approaches ' , ' forceful ' , ' 0 ' , False )
update_item ( db , gameid , userid , ' approaches ' , ' quick ' , ' 0 ' , False )
update_item ( db , gameid , userid , ' approaches ' , ' sneaky ' , ' 0 ' , False )
2019-04-08 20:04:35 +02:00
elif template == ' dnd ' :
2019-08-04 19:31:30 +02:00
update_item ( db , gameid , userid , ' general ' , ' class ' , ' Your class. ' , False )
update_item ( db , gameid , userid , ' general ' , ' race ' , ' Your race. ' , False )
update_item ( db , gameid , userid , ' general ' , ' alignment ' , ' Your alignment. ' , False )
update_item ( db , gameid , userid , ' general ' , ' level ' , ' 1 ' , False )
2019-04-08 20:04:35 +02:00
update_item ( db , gameid , userid , ' attributes ' , ' strength ' , ' 14 ' , False )
update_item ( db , gameid , userid , ' attributes ' , ' dexterity ' , ' 14 ' , False )
update_item ( db , gameid , userid , ' attributes ' , ' constitution ' , ' 14 ' , False )
update_item ( db , gameid , userid , ' attributes ' , ' intelligence ' , ' 14 ' , False )
update_item ( db , gameid , userid , ' attributes ' , ' wisdom ' , ' 14 ' , False )
update_item ( db , gameid , userid , ' attributes ' , ' charisma ' , ' 14 ' , False )
2019-02-19 19:30:41 +01:00
2019-02-18 23:30:11 +01:00
def number_of_games ( db , user ) :
c = db . cursor ( )
query = c . execute ( ''' SELECT count(*) FROM Players WHERE playerid=? ''' , ( user , ) )
result = query . fetchone ( )
return result [ 0 ]
def get_game_from_group ( db , groupid ) :
c = db . cursor ( )
query = c . execute ( ''' SELECT gameid FROM Groups WHERE groupid=? ''' , ( groupid , ) )
result = query . fetchone ( )
2019-02-24 13:32:14 +01:00
if result is None :
return None
2019-02-18 23:30:11 +01:00
return result [ 0 ]
def get_game_info ( db , gameid ) :
c = db . cursor ( )
2019-04-08 20:26:38 +02:00
query = c . execute ( ''' SELECT gamename, template FROM Games WHERE gameid=? ''' , ( gameid , ) )
2019-02-18 23:30:11 +01:00
result = query . fetchone ( )
gamename = result [ 0 ]
2019-04-08 20:26:38 +02:00
template = result [ 1 ]
2019-02-18 23:30:11 +01:00
query = c . execute ( ''' SELECT groupname FROM Groups WHERE gameid=? ''' , ( gameid , ) )
groups = [ ]
for group in query :
groups . append ( group [ 0 ] )
2019-02-20 22:51:46 +01:00
query = c . execute ( ''' SELECT playername, role FROM Players WHERE gameid=? ''' , ( gameid , ) )
players = { }
2019-02-18 23:30:11 +01:00
for player in query :
2019-02-20 22:51:46 +01:00
players [ player [ 0 ] ] = player [ 1 ]
2019-04-08 20:26:38 +02:00
return gamename , template , groups , players
2019-02-18 23:30:11 +01:00
def number_of_items ( db , gameid , playerid ) :
c = db . cursor ( )
query = c . execute ( ''' SELECT count(*) FROM Contents WHERE gameid=? AND playerid=? ''' , ( gameid , playerid , ) )
result = query . fetchone ( )
return result [ 0 ]
2019-02-20 23:39:24 +01:00
def update_item ( db , gameid , playerid , container , key , change , replace_only ) :
"""
change : can be a string , a number , or a relative change ( e . g . ' +1 ' , ' -1 ' )
replace_only : will avoid adding a new key
Returns :
oldvalue : None if didn ' t exist, int if it was digits, or a string
newvalue : int if oldvalue was digits and change is a relative change , text otherwise
"""
2019-02-18 23:30:11 +01:00
c = db . cursor ( )
query = c . execute ( ''' SELECT value FROM Contents WHERE gameid=? AND playerid=? AND container=? AND key=? ''' , ( gameid , playerid , container , key , ) )
result = query . fetchone ( )
if result is None :
2019-02-20 23:39:24 +01:00
oldvalue = None
if replace_only :
return oldvalue , None
2019-02-18 23:30:11 +01:00
else :
2019-02-20 23:39:24 +01:00
oldvalue = result [ 0 ]
2019-03-07 22:53:47 +01:00
if ( oldvalue is None or oldvalue . isdigit ( ) ) and ( change . isdigit ( ) or ( change [ 0 ] in [ ' + ' , ' - ' ] and change [ 1 : ] . isdigit ( ) ) ) :
if oldvalue is None :
oldvalue = 0
else :
oldvalue = int ( oldvalue )
2019-02-20 23:39:24 +01:00
if change [ 0 ] == ' + ' :
newvalue = oldvalue + int ( change [ 1 : ] )
elif change [ 0 ] == ' - ' :
newvalue = oldvalue - int ( change [ 1 : ] )
else :
newvalue = int ( change )
2019-02-18 23:30:11 +01:00
else :
newvalue = change
2019-02-20 23:39:24 +01:00
2019-02-18 23:30:11 +01:00
query = c . execute ( ''' INSERT OR REPLACE INTO Contents(gameid, playerid, container, key, value) VALUES (?, ?, ?, ?, ?) ''' , ( gameid , playerid , container , key , newvalue , ) )
db . commit ( )
return oldvalue , newvalue
2019-02-21 23:10:57 +01:00
def to_number ( thing ) :
"""
Converts something to an integer .
Returns the integer upon success , or 0 upon failure .
"""
try :
test = int ( thing )
return test
except :
return 0
def add_to_list ( db , gameid , playerid , container , description ) :
"""
description : the new item description
"""
c = db . cursor ( )
query = c . execute ( ''' SELECT key FROM Contents WHERE gameid=? AND playerid=? AND container=? ''' , ( gameid , playerid , container , ) )
maximum = 0
for keys in query :
maximum = max ( to_number ( keys [ 0 ] ) , maximum )
new = maximum + 1
query = c . execute ( ''' INSERT OR REPLACE INTO Contents(gameid, playerid, container, key, value) VALUES (?, ?, ?, ?, ?) ''' , ( gameid , playerid , container , new , description , ) )
db . commit ( )
2019-02-23 15:18:17 +01:00
def get_item_value ( db , gameid , playerid , container , key ) :
c = db . cursor ( )
query = c . execute ( ''' SELECT value FROM Contents WHERE gameid=? AND playerid=? AND container=? AND key=? ''' , ( gameid , playerid , container , key , ) )
result = query . fetchone ( )
if result == None :
return None
return result [ 0 ]
2019-02-18 23:30:11 +01:00
def delete_item ( db , gameid , playerid , container , key ) :
c = db . cursor ( )
query = c . execute ( ''' SELECT value FROM Contents WHERE gameid=? AND playerid=? AND container=? AND key=? ''' , ( gameid , playerid , container , key , ) )
result = query . fetchone ( )
if result == None :
return None
oldvalue = result [ 0 ]
query = c . execute ( ''' DELETE FROM Contents WHERE gameid=? AND playerid=? AND container=? AND key=? ''' , ( gameid , playerid , container , key , ) )
db . commit ( )
return oldvalue
def get_items ( db , gameid , playerid ) :
ret = { }
c = db . cursor ( )
2019-03-13 21:41:36 +01:00
contents = { }
query = c . execute ( ''' SELECT container, key, value FROM Contents WHERE gameid=? AND playerid=? ''' , ( gameid , playerid , ) )
for row in query . fetchall ( ) :
if row [ 0 ] not in contents :
contents [ row [ 0 ] ] = { }
contents [ row [ 0 ] ] [ row [ 1 ] ] = row [ 2 ]
return contents