sharing.py

import time
import datetime
import psycopg2
import threading

server = None
port = None
db = None
username = None
password = None

writeLock = threading.Lock()

def setDbInfo(newServer, newPort, newDb, newUsername, newPassword):
    global server, port, db, username, password

    server = newServer
    port = newPort
    db = newDb
    username = newUsername
    password = newPassword

def dbConfigured():
    return bool(server)

def getDbConnection():
    return psycopg2.connect(database=db,
                            host=server,
                            user=username,
                            password=password,
                            port=port)
    
def writeState(player, playerId, event, state):
    if not dbConfigured():
        return

    timestamp = round(time.time(), 3)

    playerNoQuery = """
        select sharing.player_no
        from sharing
        where sharing.player_name = %(player)s
	    order by sharing.timestamp desc
		limit 1
    """
    # deleteQuery = """
    #     delete from sharing
    #     where sharing.player_name = %(player)s
    # """
    insertQuery = """
        insert into sharing (player_name, player_id, event_name, state, timestamp, player_no)
        values (%(player)s, %(id)s, %(event)s, %(state)s, %(timestamp)s, %(playerNo)s);

        update events
        set last_activity = current_timestamp
        where events.name = %(event)s;
    """

    with writeLock:
        conn = getDbConnection()
        cursor = conn.cursor()

        playerNo = None
        cursor.execute(playerNoQuery, { 'player': player })
        result = cursor.fetchone()

        if result:
            playerNo = result[0]

        # cursor.execute(deleteQuery, { 'player': player })

        cursor.execute(insertQuery, {
            'player': player,
            'id': playerId,
            'event': event,
            'state': state,
            'timestamp': timestamp,
            'playerNo': playerNo,
        })

        conn.commit()

        cursor.close()
        conn.close()

    return timestamp

def getState(player, timestamp, delaySeconds=0):
    if not dbConfigured():
        return None

    query = """
        select sharing.state
              ,sharing.timestamp
        from sharing
        where sharing.player_name = %(player)s
              and sharing.timestamp > %(timestamp)s
              and (%(delaySeconds)s = 0 or sharing.creation_time <= (current_timestamp - interval '1 second' * %(delaySeconds)s))
	    order by sharing.timestamp desc
		limit 1
    """

    conn = getDbConnection()
    cursor = conn.cursor()

    cursor.execute(query, {
        'player': player,
        'timestamp': timestamp,
        'delaySeconds': delaySeconds,
    })

    result = cursor.fetchone()

    cursor.close()
    conn.close()

    return { 'state': result[0], 'timestamp': str(result[1]) } if result else None

def getPlayerId(player):
    if not dbConfigured():
        return None

    query = """
        select sharing.player_id
        from sharing
        where sharing.player_name = %(player)s
	    order by sharing.timestamp desc
		limit 1
    """

    conn = getDbConnection()
    cursor = conn.cursor()

    cursor.execute(query, {
        'player': player,
    })

    result = cursor.fetchone()

    cursor.close()
    conn.close()

    return result[0] if result else None

def getEventPlayers(event):
    if not dbConfigured():
        return None

    threshold = datetime.datetime.now() - datetime.timedelta(hours=2)
    threshold = time.mktime(threshold.timetuple())

    query = """
        select sharing.player_name
              ,max(sharing.player_no)
        from sharing
        where sharing.event_name = %(event)s
              and sharing.timestamp > %(threshold)s
		group by sharing.player_name
        order by max(sharing.player_no)
    """

    purgeQuery = """
        update sharing
        set player_no = null
        where timestamp <= %(threshold)s
    """

    updateQuery = """
        update sharing
        set player_no = %(playerNo)s
        where player_name = %(playerName)s
    """

    with writeLock:
        conn = getDbConnection()
        cursor = conn.cursor()

        cursor.execute(query, {
            'event': event,
            'threshold': threshold,
        })

        result = cursor.fetchall()

        cursor.execute(purgeQuery, {
            'threshold': threshold
        })

        conn.commit()

        players = [{ 'player': x[0], 'number': x[1] } for x in result]
        maxNumber = max([x['number'] for x in players if x['number'] != None] or [0])
        unNumberedPlayers = sorted([x for x in players if x['number'] == None], key=lambda x: x['player'].lower())

        for player in unNumberedPlayers:
            player['number'] = maxNumber + 1

            cursor.execute(updateQuery, {
                'playerName': player['player'],
                'playerNo': player['number'],
            })

            maxNumber += 1

        conn.commit()

        cursor.close()
        conn.close()

    return [x['player'] for x in sorted(players, key=lambda x: x['number'])]

def eventExists(eventName):
    if not dbConfigured():
        return

    eventQuery = """
        select 1
        from events
        where events.name = %(eventName)s

        union

        select 1
        from sharing
        where sharing.event_name = %(eventName)s
    """
    
    conn = getDbConnection()
    cursor = conn.cursor()

    cursor.execute(eventQuery, { 'eventName': eventName })
    result = cursor.fetchone()

    cursor.close()
    conn.close()

    return bool(result)

def eventInfo(eventName):
    if not dbConfigured():
        return

    eventQuery = """
        select events.name
              ,not events.view_code is null as private_view
              ,not events.join_code is null as private_join
        from events
        where events.name = %(eventName)s

        union

        select sharing.event_name
              ,false as private_view
              ,false as private_join
        from sharing
        where sharing.event_name = %(eventName)s
		
		order by private_view desc, private_join desc
    """
    
    conn = getDbConnection()
    cursor = conn.cursor()

    cursor.execute(eventQuery, { 'eventName': eventName })
    result = cursor.fetchone()

    cursor.close()
    conn.close()

    return {
        'eventName': result[0],
        'privateView': result[1],
        'privateJoin': result[2],
    } if result else None

def authenticateEvent(eventName, code):
    if not dbConfigured():
        return

    eventQuery = """
        select events.join_code
              ,events.view_code
        from events
        where events.name = %(eventName)s
    """
    
    conn = getDbConnection()
    cursor = conn.cursor()

    cursor.execute(eventQuery, { 'eventName': eventName })
    result = cursor.fetchone()

    cursor.close()
    conn.close()

    if result:
        return (code == result[0] or result[0] == None,
                code == result[1] or result[1] == None)
    
    return None

def createEvent(eventName, joinCode, viewCode):
    if not dbConfigured():
        return

    query = """
        insert into events (name, join_code, view_code)
        values (%(eventName)s, %(joinCode)s, %(viewCode)s)
    """

    success = True

    if eventExists(eventName):
        success = False

    if success:
        if joinCode == '':
            joinCode = None
        if viewCode == '':
            viewCode = None

        conn = getDbConnection()
        cursor = conn.cursor()

        cursor.execute(query, {
            'eventName': eventName,
            'joinCode': joinCode,
            'viewCode': viewCode,
        })

        conn.commit()

        cursor.close()
        conn.close()

    return success