예제 #1
0
 def __init__(self, settings):
     self.settings = settings
     self.games = {}
     self.observers = []
     self.pollable_plugins = []
     self.auth = Auth(
     )  # to be overriden by an auth plugin (contains unimplemented interfaces)
예제 #2
0
 def __init__(self, settings):
     self.settings = settings
     self.games = {}
     self.players = {}
     self.observers = []
     self.pollable_plugins = []
     self.auth = Auth() # to be overriden by an auth plugin (contains unimplemented interfaces)
예제 #3
0
    def test00_request_auth(self):
        '''Check that the preprocess correctly calls the right plugin methods'''

        player_id = 10
        owner_id = 12
        sessionid = 'session_id_string'

        auth = Auth()
        auth.authenticate = Mock(return_value=True)

        # Should authenticate player_id parameters
        class request_player_id:
            def __init__(self):
                self.args = {
                    'action': ['invite'],
                    'player_id': [player_id],
                    'invited_email': '*****@*****.**'
                }

            def getCookie(self, key):
                return sessionid

        request = request_player_id()
        result_in = 'RESULT'
        result_out = yield auth.preprocess(result_in, request)
        self.assertEquals(result_in, result_out)
        auth.authenticate.assert_called_once_with(request, player_id)

        # And should authenticate owner_id parameters, too
        class request_owner_id:
            def __init__(self):
                self.args = {'action': ['invite'], 'owner_id': [owner_id]}

            def getCookie(self, key):
                return sessionid

        request = request_owner_id()
        result_in = 'RESULT'
        result_out = yield auth.preprocess(result_in, request)
        self.assertEquals(result_in, result_out)
        auth.authenticate.assert_called_with(request, owner_id)
예제 #4
0
    def test00_request_auth(self):
        '''Check that the preprocess correctly calls the right plugin methods'''
        
        player_id = 10
        owner_id = 12
        sessionid = 'session_id_string'

        auth = Auth()
        auth.authenticate = Mock(return_value=True)
        
        # Should authenticate player_id parameters
        class request_player_id:
            def __init__(self):
                self.args = {'action': ['invite'],
                             'player_id': [player_id],
                             'invited_email': '*****@*****.**'}
            def getCookie(self, key):
                return sessionid
            
        request = request_player_id()
        result_in = 'RESULT'
        result_out = yield auth.preprocess(result_in, request)
        self.assertEquals(result_in, result_out)
        auth.authenticate.assert_called_once_with(request, player_id)
        
        # And should authenticate owner_id parameters, too
        class request_owner_id:
            def __init__(self):
                self.args = {'action': ['invite'],
                             'owner_id': [owner_id]}
            def getCookie(self, key):
                return sessionid
            
        request = request_owner_id()
        result_in = 'RESULT'
        result_out = yield auth.preprocess(result_in, request)
        self.assertEquals(result_in, result_out)
        auth.authenticate.assert_called_with(request, owner_id)
예제 #5
0
class CardstoriesService(service.Service, Observable):

    ACTIONS_GAME = ('set_card', 'set_sentence', 'participate', 'voting', 'pick', 'vote',
                    'complete', 'invite', 'set_countdown')
    ACTIONS = ACTIONS_GAME + ('create', 'poll', 'state', 'player_info', 'remove_tab')

    def __init__(self, settings):
        self.settings = settings
        self.games = {}
        self.players = {}
        self.observers = []
        self.pollable_plugins = []
        self.auth = Auth() # to be overriden by an auth plugin (contains unimplemented interfaces)

    def startService(self):
        database = self.settings['db']
        exists = os.path.exists(database)
        db = sqlite3.connect(database)
        c = db.cursor()
        if exists:
            self.load(c)
        else:
            self.create_base(c)
            db.commit()
        c.close()
        db.close()
        self.db = adbapi.ConnectionPool("sqlite3", database=database, cp_noisy=True, check_same_thread=False)
        self.notify({'type': 'start'})

    @defer.inlineCallbacks
    def stopService(self):
        yield self.notify({'type': 'stop'})
        for game in self.games.values():
            game.destroy()
        for player in self.players.values():
            if player.timer.active():
                player.timer.cancel()
            player.destroy()
        defer.returnValue(None)

    def create_base(self, c):
        c.execute(
            "CREATE TABLE games ( "
            "  id INTEGER PRIMARY KEY, "
            "  owner_id INTEGER, "
            "  players INTEGER DEFAULT 1, "
            "  sentence TEXT, "
            "  cards TEXT, "
            "  board TEXT, "
            "  state VARCHAR(8) DEFAULT 'create', " + # create, invitation, vote, complete
            "  created DATETIME, "
            "  completed DATETIME"
            "); ")
        c.execute(
            "CREATE INDEX games_idx ON games (id); "
            )
        c.execute(
            "CREATE TABLE player2game ( "
            "  serial INTEGER PRIMARY KEY, "
            "  player_id INTEGER, "
            "  game_id INTEGER, "
            "  cards TEXT, "
            "  picked CHAR(1), "
            "  vote CHAR(1), "
            "  win CHAR(1) DEFAULT 'n' "
            "); ")
        c.execute(
            "CREATE UNIQUE INDEX player2game_idx ON player2game (player_id, game_id); "
            )
        c.execute(
            "CREATE TABLE invitations ( "
            "  player_id INTEGER, "
            "  game_id INTEGER"
            "); ")
        c.execute(
            "CREATE UNIQUE INDEX invitations_idx ON invitations (player_id, game_id); "
            )
        c.execute(
            "CREATE TABLE tabs ( "
            "  player_id INTEGER, "
            "  game_id INTEGER, "
            "  created DATETIME "
            "); ")
        c.execute(
            "CREATE UNIQUE INDEX tabs_idx ON tabs (player_id, game_id); "
            )
        c.execute(
            "CREATE TABLE players ( "
            "  player_id INTEGER, "
            "  score BIGINTEGER, "
            "  score_prev BIGINTEGER, "
            "  levelups INTEGER, "
            "  earned_cards TEXT, "
            "  earned_cards_cur TEXT "
            "); ")
        c.execute(
            "CREATE UNIQUE INDEX players_idx ON players (player_id); "
            )
        c.execute(
            "CREATE TABLE event_logs ( "
            "  player_id INTEGER, "
            "  game_id INTEGER, "
            "  event_type SMALLINT, "
            "  data TEXT, "
            "  timestamp DATETIME "
            "); ")
        c.execute(
            "CREATE INDEX eventlogs_player_idx ON event_logs (player_id, timestamp); "
            )
        c.execute(
            "CREATE INDEX eventlogs_game_idx ON event_logs (game_id, timestamp); "
            )

    def load(self, c):
        c.execute("SELECT id, sentence FROM games WHERE state != 'complete' AND state != 'canceled'")
        for (id, sentence) in c.fetchall():
            game = CardstoriesGame(self, id)
            game.load(c)

            # Notify listeners of the game, but use the 'load' notification to signal
            # that the game is being loaded, not created
            # Note that the db is not accessible during that stage
            self.game_init(game, sentence, init_type='load')

    def poll(self, args):
        self.required(args, 'poll', 'type', 'modified')
        deferreds = []

        if 'game' in args['type']:
            game_id = self.required_game_id(args)
            if not self.games.has_key(game_id):
                # This means the game has been deleted from memory - probably because
                # it has been completed. The client doesn't seem to be aware of this yet,
                # so just return the poll immediately to let the client know the state
                # has changed.
                return defer.succeed({'game_id': [game_id],
                                      'modified': [int(runtime.seconds() * 1000)]})
            else:
                deferreds.append(self.games[game_id].poll(args))

        if 'tabs' in args['type']:
            deferreds.append(self.poll_tabs(args))

        for plugin in self.pollable_plugins:
            if plugin.name() in args['type']:
                deferreds.append(plugin.poll(args))

        d = defer.DeferredList(deferreds, fireOnOneCallback=True)
        d.addCallback(lambda x: x[0])

        # Allow listeners to monitor when polls are started or ended
        if deferreds:
            if 'player_id' in args:
                player_id = args['player_id'][0]
            else:
                player_id = None
            self.notify({'type': 'poll_start',
                         'player_id': player_id})

            def on_poll_end(return_value):
                self.notify({'type': 'poll_end',
                             'player_id': player_id})
                return return_value
            d.addCallback(on_poll_end)

        return d

    def poll_tabs(self, args):
        """
        Gets the games that should be monitored as tabs by the current user,
        and returns a deferred list of polled games.
        """
        # We need to nest one deferred inside another, because we are dealing with
        # two async operations: fetching game ids from the DB, and waiting in a poll.
        # The outer callback fires when the game ids are fetched from the DB, while the
        # inner one fires when one of the polled games has been modified, causing poll to return.
        outer_deferred = self.get_opened_tabs_from_args(args)
        def outer_callback(result):
            game_deferreds = []
            for game_id in result:
                if self.games.has_key(game_id):
                    game_deferreds.append(self.games[game_id].poll(args))
            def inner_callback(result):
                # Make the tabs poll always return just the arguments with updated timestamp.
                args['modified'] = result[0]['modified']
                return args
            inner_deferred = defer.DeferredList(game_deferreds, fireOnOneCallback=True)
            inner_deferred.addCallback(inner_callback)
            return inner_deferred
        outer_deferred.addCallback(outer_callback)
        return outer_deferred

    @defer.inlineCallbacks
    def get_opened_tabs_from_args(self, args):
        """
        Expects 'player_id' and optionally a 'game_id' in the args.
        If there is a 'game_id' in the args and that game_id is not yet associated
        with the player in the tabs table, it associates the game_id with player_id in
        the table.
        Returns a list of game_ids associated with the player in the tabs table.
        """
        player_id = args['player_id'][0]
        game_id = args.has_key('game_id') and args['game_id'][0]
        try:
            game_id = int(game_id)
        except:
            game_id = None
        if player_id:
            # Try to associate current game with the player in the tabs table.
            # This wont't do any harm if current game is already opened in a tab.
            if game_id:
                yield self.open_tab(player_id, game_id)
            game_ids = yield self.get_opened_tabs(player_id)
        else:
            game_ids = []
        defer.returnValue(game_ids)

    @defer.inlineCallbacks
    def get_opened_tabs(self, player_id):
        """
        Returns a deferred which results in a list of game_ids of games
        which the player keeps open in tabs.
        """
        sql = 'SELECT game_id from tabs WHERE player_id = ? ORDER BY created ASC'
        rows = yield self.db.runQuery(sql, [player_id])
        game_ids = []
        for row in rows: game_ids.append(row[0])
        defer.returnValue(game_ids)

    def openTabInteraction(self, transaction, player_id, game_id):
        transaction.execute('SELECT * FROM tabs WHERE player_id = ? AND game_id = ?', [player_id, game_id])
        rows = transaction.fetchall()
        inserted = False
        if not len(rows):
            sql = "INSERT INTO tabs (player_id, game_id, created) VALUES (?, ?, datetime('now'))"
            transaction.execute(sql, [player_id, game_id])
            inserted = True
        return inserted

    def closeTabInteraction(self, transaction, player_id, game_id):
        transaction.execute('SELECT * FROM tabs WHERE player_id = ? AND game_id = ?', [player_id, game_id])
        rows = transaction.fetchall()
        deleted = False
        if len(rows):
            transaction.execute('DELETE FROM tabs WHERE player_id = ? AND game_id = ?', [player_id, game_id])
            deleted = True
        return deleted

    @defer.inlineCallbacks
    def open_tab(self, player_id, game_id):
        """
        Associates game_id with player_id in the tabs table, if they are not already
        associated, and returns True.
        If they are already assiociated, doesn't do anything and returns False.
        """
        inserted = yield self.db.runInteraction(self.openTabInteraction, player_id, game_id)
        if inserted:
            self.notify({'type': 'tab_opened', 'player_id': player_id, 'game_id': game_id})
        defer.returnValue(inserted)

    @defer.inlineCallbacks
    def close_tab(self, player_id, game_id):
        """
        Removes the association between player_id and game_id from the tabs table
        and return True.
        If player_id and game_id weren't associated, doesn't do anything and return False.
        """
        deleted = yield self.db.runInteraction(self.closeTabInteraction, player_id, game_id)
        if deleted:
            self.notify({'type': 'tab_closed', 'player_id': player_id, 'game_id': game_id})
        defer.returnValue(deleted)

    def remove_tab(self, args):
        """
        Processes requests to remove game from player's list of tabs.
        Expects 'player_id' and 'game_id' to be present in the args.
        Removes association between player and game from the tabs table.
        """
        self.required(args, 'remove_tab', 'player_id')
        game_id = self.required_game_id(args)
        player_id = int(args['player_id'][0])
        d = self.close_tab(player_id, game_id)
        def success(result):
            return {'type': 'remove_tab'}
        d.addCallback(success)
        return d

    @defer.inlineCallbacks
    def update_players_info(self, players_info, players_id_list):
        '''Add new player ids as key to players_info dict, from players_list'''
        # Python's DB-API doesn't support interpolating lists into SQL's "WHERE x IN (...)" statements,
        # so we have to generate the correct number of '?' placeholders programatically.
        format_strings = ','.join(['?'] * len(players_id_list))
        sql_statement = 'SELECT player_id, score FROM players WHERE player_id IN (%s)' % format_strings
        rows = yield self.db.runQuery(sql_statement, players_id_list)
        # Build up a dict of {player_id: player_level} key-value pairs.
        levels = {}
        for row in rows:
            level, _, _ = calculate_level(row[1])
            levels[row[0]] = level

        for player_id in players_id_list:
            if player_id not in players_info:
                info = {}
                if levels.has_key(player_id):
                    info['level'] = levels[player_id]
                try:
                    info['name'] = yield self.auth.get_player_name(player_id)
                    info['avatar_url'] = yield self.auth.get_player_avatar_url(player_id)
                except Exception as e:
                    raise CardstoriesException('Failed fetching player data (player_id=%s): %s' % (player_id, e))
                players_info[str(player_id)] = info

        defer.returnValue(players_info)

    @defer.inlineCallbacks
    def player_info(self, args):
        '''Process requests to retreive player_info for a player_id'''

        self.required(args, 'player_info', 'player_id')

        players_info = {'type': 'players_info'}
        yield self.update_players_info(players_info, args['player_id'])
        defer.returnValue([players_info])

    @defer.inlineCallbacks
    def state(self, args):
        self.required(args, 'state', 'type', 'modified')
        states = []
        players_info = {'type': 'players_info'} # Keep track of all players being referenced

        if 'game' in args['type']:
            game_args = {'action': 'game',
                         'game_id': args['game_id'] }
            if args.has_key('player_id'):
                game_args['player_id'] = args['player_id']

            game, players_id_list = yield self.game(game_args)
            game['type'] = 'game'
            states.append(game)
            yield self.update_players_info(players_info, players_id_list)

        if 'tabs' in args['type']:
            game_ids = yield self.get_opened_tabs_from_args(args)
            tabs = {'type': 'tabs', 'games': []}
            player_id = args.get('player_id')
            max_modified = 0
            for game_id in game_ids:
                game_args = {'action': 'game', 'game_id': [game_id]}
                if player_id: game_args['player_id'] = player_id
                game, players_id_list = yield self.game(game_args)
                tabs['games'].append(game)
                if game['modified'] > max_modified:
                    max_modified = game['modified']
            tabs['modified'] = max_modified
            states.append(tabs)

        for plugin in self.pollable_plugins:
            if plugin.name() in args['type']:
                state, players_id_list = yield plugin.state(args)
                state['type'] = plugin.name()
                state['modified'] = plugin.get_modified(args=args)
                states.append(state)
                yield self.update_players_info(players_info, players_id_list)

        states.append(players_info)
        defer.returnValue(states)

    @defer.inlineCallbacks
    def game_notify(self, args, game_id):
        if args == None:
            yield self.notify({'type': 'delete', 'game': self.games[game_id], 'details': args})
            del self.games[game_id]
            defer.returnValue(False)

        if not self.games.has_key(game_id):
            defer.returnValue(False)

        game = self.games[game_id]
        modified = game.get_modified()
        yield self.notify({'type': 'change', 'game': game, 'details': args})

        for player_id in game.get_players():
            if self.players.has_key(player_id):
                yield self.players[player_id].touch(args)
        #
        # the functions being notified must not change the game state
        # because the behavior in this case is undefined
        #
        assert game.get_modified() == modified
        d = game.wait(args)
        d.addCallback(self.game_notify, game_id)
        defer.returnValue(True)

    @defer.inlineCallbacks
    def game_init(self, game, sentence, init_type='create', previous_game_id=None):
        self.games[game.get_id()] = game
        args = {
            'type': init_type,
            'modified': [0],
            'game_id': [game.get_id()],
            'sentence': sentence,
            'previous_game_id': previous_game_id}

        args = yield game.wait(args)
        yield self.game_notify(args, game.get_id())

    @defer.inlineCallbacks
    def create(self, args):
        self.required(args, 'create', 'owner_id')
        owner_id = int(args['owner_id'][0])

        # Keep track of consecutive games
        if 'previous_game_id' in args:
            previous_game_id = args['previous_game_id'][0]
        else:
            previous_game_id = None

        game = CardstoriesGame(self)
        game_id = yield game.create(owner_id)

        yield self.game_init(game, '', previous_game_id=previous_game_id)

        defer.returnValue({'game_id': game_id})

    def complete(self, args):
        self.required(args, 'complete', 'owner_id')
        owner_id = int(args['owner_id'][0])
        game_id = self.required_game_id(args)
        d = self.game_method(game_id, 'complete', owner_id)
        return d

    def game(self, args):
        self.required(args, 'game')
        game_id = self.required_game_id(args)
        if args.has_key('player_id'):
            player_id = int(args['player_id'][0])
        else:
            player_id = None
        if self.games.has_key(game_id):
            return self.games[game_id].game(player_id)
        else:
            game = CardstoriesGame(self, game_id)
            d = game.game(player_id)
            def destroy(game_info):
                game.destroy()
                return game_info
            d.addCallback(destroy)
            return d

    def game_method(self, game_id, action, *args, **kwargs):
        if not self.games.has_key(game_id):
            raise CardstoriesWarning('GAME_NOT_LOADED', {'game_id': game_id})
        return getattr(self.games[game_id], action)(*args, **kwargs)

    def set_card(self, args):
        self.required(args, 'set_card', 'player_id', 'card')
        player_id = int(args['player_id'][0])
        card = int(args['card'][0])
        game_id = self.required_game_id(args)
        return self.game_method(game_id, args['action'][0], player_id, card)

    def set_sentence(self, args):
        self.required(args, 'set_sentence', 'player_id', 'sentence')
        player_id = int(args['player_id'][0])
        game_id = self.required_game_id(args)
        sentence = args['sentence'][0].decode('utf-8')
        return self.game_method(game_id, args['action'][0], player_id, sentence)

    def participate(self, args):
        self.required(args, 'participate', 'player_id')
        player_id = int(args['player_id'][0])
        game_id = self.required_game_id(args)
        return self.game_method(game_id, args['action'][0], player_id)

    def player2game(self, args):
        self.required(args, 'player2game', 'player_id')
        player_id = int(args['player_id'][0])
        game_id = self.required_game_id(args)
        return self.game_method(game_id, args['action'][0], player_id)

    def pick(self, args):
        self.required(args, 'pick', 'player_id', 'card')
        player_id = int(args['player_id'][0])
        card = int(args['card'][0])
        game_id = self.required_game_id(args)
        return self.game_method(game_id, args['action'][0], player_id, card)

    def vote(self, args):
        self.required(args, 'vote', 'player_id', 'card')
        player_id = int(args['player_id'][0])
        card = int(args['card'][0])
        game_id = self.required_game_id(args)
        return self.game_method(game_id, args['action'][0], player_id, card)

    def voting(self, args):
        self.required(args, 'voting', 'owner_id')
        owner_id = int(args['owner_id'][0])
        game_id = self.required_game_id(args)
        return self.game_method(game_id, args['action'][0], owner_id)

    @defer.inlineCallbacks
    def invite(self, args):
        self.required(args, 'invite')
        if args.has_key('invited_email'):
            player_ids = yield self.auth.get_players_ids(args['invited_email'], create=True)
        else:
            player_ids = []

        if args.has_key('player_id'):
            player_ids += args['player_id']

        game_id = self.required_game_id(args)
        result = yield self.game_method(game_id, args['action'][0], player_ids)
        defer.returnValue(result)

    def set_countdown(self, args):
        self.required(args, 'set_countdown', 'duration')
        duration = int(args['duration'][0])
        game_id = self.required_game_id(args)
        return self.game_method(game_id, args['action'][0], duration)

    def handle(self, result, args):
        if not args.has_key('action'):
            return defer.succeed(result)
        try:
            action = args['action'][0]
            if action in self.ACTIONS:
                d = getattr(self, action)(args)
                def error(reason):
                    error = reason.value
                    log.err(reason)
                    if reason.type is CardstoriesWarning:
                        return {'error': {'code': error.code, 'data': error.data}}
                    else:
                        tb = error.args[0]
                        tb += '\n\n'
                        tb += ''.join(traceback.format_tb(reason.getTracebackObject()))
                        return {'error': {'code': 'PANIC', 'data': tb}}
                d.addErrback(error)
                return d
            else:
                raise CardstoriesException, 'Unknown action: %s' % action
        except CardstoriesWarning as e:
            log.err(e)
            return defer.succeed({'error': {'code': e.code, 'data': e.data}})
        except Exception as e:
            log.err(e)
            tb = traceback.format_exc()
            return defer.succeed({'error': {'code': 'PANIC', 'data': tb}})

    @staticmethod
    def required(args, action, *keys):
        for key in keys:
            if not args.has_key(key):
                raise CardstoriesException, "Action '%s' requires argument '%s', but it was missing." % (action, key)
        return True

    @staticmethod
    def required_game_id(args):
        CardstoriesService.required(args, args['action'][0], 'game_id')
        game_id = int(args['game_id'][0])
        if game_id <= 0:
            raise CardstoriesException, 'game_id cannot be negative: %d' % args['game_id']
        return game_id
예제 #6
0
class CardstoriesService(service.Service, Observable):

    ACTIONS_GAME = (
        "set_card",
        "set_sentence",
        "participate",
        "voting",
        "pick",
        "vote",
        "complete",
        "invite",
        "set_countdown",
    )
    ACTIONS = ACTIONS_GAME + ("create", "poll", "state", "player_info", "close_tab_action")

    ACTIONS_INTERNAL = "grant_cards_to_player"

    def __init__(self, settings):
        self.settings = settings
        self.games = {}
        self.observers = []
        self.pollable_plugins = []
        self.auth = Auth()  # to be overriden by an auth plugin (contains unimplemented interfaces)

    def startService(self):
        database = self.settings["db"]
        exists = os.path.exists(database)
        db = sqlite3.connect(database)
        c = db.cursor()
        if exists:
            self.load(c)
        else:
            self.create_base(c)
            db.commit()
        c.close()
        db.close()
        self.db = adbapi.ConnectionPool("sqlite3", database=database, cp_noisy=True, check_same_thread=False)
        self.notify({"type": "start"})

    @defer.inlineCallbacks
    def stopService(self):
        yield self.notify({"type": "stop"})
        for game in self.games.values():
            game.destroy()
        defer.returnValue(None)

    def create_base(self, c):
        c.execute(
            "CREATE TABLE games ( "
            "  id INTEGER PRIMARY KEY, "
            "  owner_id INTEGER, "
            "  players INTEGER DEFAULT 1, "
            "  sentence TEXT, "
            "  cards TEXT, "
            "  board TEXT, "
            "  state VARCHAR(8) DEFAULT 'create', " + "  created DATETIME, "  # create, invitation, vote, complete
            "  completed DATETIME"
            "); "
        )
        c.execute("CREATE INDEX games_idx ON games (id); ")
        c.execute(
            "CREATE TABLE player2game ( "
            "  serial INTEGER PRIMARY KEY, "
            "  player_id INTEGER, "
            "  game_id INTEGER, "
            "  cards TEXT, "
            "  picked CHAR(1), "
            "  vote CHAR(1), "
            "  win CHAR(1) DEFAULT 'n' "
            "); "
        )
        c.execute("CREATE UNIQUE INDEX player2game_idx ON player2game (player_id, game_id); ")
        c.execute("CREATE TABLE invitations ( " "  player_id INTEGER, " "  game_id INTEGER" "); ")
        c.execute("CREATE UNIQUE INDEX invitations_idx ON invitations (player_id, game_id); ")
        c.execute("CREATE TABLE tabs ( " "  player_id INTEGER, " "  game_id INTEGER, " "  created DATETIME " "); ")
        c.execute("CREATE UNIQUE INDEX tabs_idx ON tabs (player_id, game_id); ")
        c.execute(
            "CREATE TABLE players ( "
            "  player_id INTEGER, "
            "  score BIGINTEGER, "
            "  score_prev BIGINTEGER, "
            "  levelups INTEGER, "
            "  earned_cards TEXT, "
            "  earned_cards_cur TEXT "
            "); "
        )
        c.execute("CREATE UNIQUE INDEX players_idx ON players (player_id); ")
        c.execute(
            "CREATE TABLE event_logs ( "
            "  player_id INTEGER, "
            "  game_id INTEGER, "
            "  event_type SMALLINT, "
            "  data TEXT, "
            "  timestamp DATETIME "
            "); "
        )
        c.execute("CREATE INDEX eventlogs_player_idx ON event_logs (player_id, timestamp); ")
        c.execute("CREATE INDEX eventlogs_game_idx ON event_logs (game_id, timestamp); ")

    def load(self, c):
        c.execute("SELECT id, sentence FROM games WHERE state != 'complete' AND state != 'canceled'")
        for (id, sentence) in c.fetchall():
            game = CardstoriesGame(self, id)
            game.load(c)

            # Notify listeners of the game, but use the 'load' notification to signal
            # that the game is being loaded, not created
            # Note that the db is not accessible during that stage
            self.game_init(game, sentence, init_type="load")

    def poll(self, args):
        self.required(args, "poll", "type", "modified")
        deferreds = []

        if "game" in args["type"]:
            game_id = self.required_game_id(args)
            if not self.games.has_key(game_id):
                # This means the game has been deleted from memory - probably because
                # it has been completed. The client doesn't seem to be aware of this yet,
                # so just return the poll immediately to let the client know the state
                # has changed.
                return defer.succeed({"game_id": [game_id], "modified": [int(runtime.seconds() * 1000)]})
            else:
                deferreds.append(self.games[game_id].poll(args))

        if "tabs" in args["type"]:
            deferreds.append(self.poll_tabs(args))

        for plugin in self.pollable_plugins:
            if plugin.name() in args["type"]:
                deferreds.append(plugin.poll(args))

        d = defer.DeferredList(deferreds, fireOnOneCallback=True)
        d.addCallback(lambda x: x[0])

        # Allow listeners to monitor when polls are started or ended
        if deferreds:
            if "player_id" in args:
                player_id = args["player_id"][0]
            else:
                player_id = None
            self.notify({"type": "poll_start", "player_id": player_id})

            def on_poll_end(return_value):
                self.notify({"type": "poll_end", "player_id": player_id})
                return return_value

            d.addCallback(on_poll_end)

        return d

    def poll_tabs(self, args):
        """
        Gets the games that should be monitored as tabs by the current user,
        and returns a deferred list of polled games.
        """
        # We need to nest one deferred inside another, because we are dealing with
        # two async operations: fetching game ids from the DB, and waiting in a poll.
        # The outer callback fires when the game ids are fetched from the DB, while the
        # inner one fires when one of the polled games has been modified, causing poll to return.
        outer_deferred = self.get_open_tabs(args)

        def outer_callback(result):
            game_deferreds = []
            for game_id in result:
                if self.games.has_key(game_id):
                    game_deferreds.append(self.games[game_id].poll(args))

            def inner_callback(result):
                # Make the tabs poll always return just the arguments with updated timestamp.
                if result[0] != None:
                    args["modified"] = result[0]["modified"]
                return args

            inner_deferred = defer.DeferredList(game_deferreds, fireOnOneCallback=True)
            inner_deferred.addCallback(inner_callback)
            return inner_deferred

        outer_deferred.addCallback(outer_callback)
        return outer_deferred

    @defer.inlineCallbacks
    def get_open_tabs(self, args):
        """
        Expects 'player_id' and optionally a 'game_id' in the args.
        If there is a 'game_id' in the args and that game_id is not yet associated
        with the player in the tabs table, it associates the game_id with player_id in
        the table.
        Returns a list of game_ids associated with the player in the tabs table.
        """
        player_id = args["player_id"][0]
        game_id = args.has_key("game_id") and args["game_id"][0]
        try:
            game_id = int(game_id)
        except:
            game_id = None
        if player_id:
            # Try to associate current game with the player in the tabs table.
            # This wont't do any harm if current game is already open in a tab.
            if game_id:
                yield self.open_tab(player_id, game_id)
            game_ids = yield self.get_tabs_for_player(player_id)
        else:
            game_ids = []
        defer.returnValue(game_ids)

    @defer.inlineCallbacks
    def get_tabs_for_player(self, player_id):
        """
        Returns a deferred which results in a list of game_ids of games
        which the player keeps open in tabs.
        """
        sql = "SELECT game_id from tabs WHERE player_id = ? ORDER BY created ASC"
        rows = yield self.db.runQuery(sql, [player_id])
        game_ids = []
        for row in rows:
            game_ids.append(row[0])
        defer.returnValue(game_ids)

    def openTabInteraction(self, transaction, player_id, game_id):
        inserted = False
        # Make sure the game exists before trying to associate it with the player.
        transaction.execute("SELECT id FROM games WHERE id = ?", [game_id])
        rows = transaction.fetchall()
        if len(rows):
            # Make sure the game isn't already associated with the player.
            transaction.execute("SELECT * FROM tabs WHERE player_id = ? AND game_id = ?", [player_id, game_id])
            rows = transaction.fetchall()
            if not len(rows):
                sql = "INSERT INTO tabs (player_id, game_id, created) VALUES (?, ?, datetime('now'))"
                transaction.execute(sql, [player_id, game_id])
                inserted = True
        return inserted

    def closeTabInteraction(self, transaction, player_id, game_id):
        transaction.execute("SELECT * FROM tabs WHERE player_id = ? AND game_id = ?", [player_id, game_id])
        rows = transaction.fetchall()
        deleted = False
        if len(rows):
            transaction.execute("DELETE FROM tabs WHERE player_id = ? AND game_id = ?", [player_id, game_id])
            deleted = True
        return deleted

    @defer.inlineCallbacks
    def open_tab(self, player_id, game_id):
        """
        Associates game_id with player_id in the tabs table, if they are not already
        associated, and returns True.
        If they are already assiociated, doesn't do anything and returns False.
        """
        inserted = yield self.db.runInteraction(self.openTabInteraction, player_id, game_id)
        if inserted:
            self.notify({"type": "tab_opened", "player_id": player_id, "game_id": game_id})
        defer.returnValue(inserted)

    @defer.inlineCallbacks
    def close_tab(self, player_id, game_id):
        """
        Removes the association between player_id and game_id from the tabs table
        and returns True.
        If player_id and game_id weren't associated, doesn't do anything and returns False.
        """
        deleted = yield self.db.runInteraction(self.closeTabInteraction, player_id, game_id)
        if deleted:
            self.notify({"type": "tab_closed", "player_id": player_id, "game_id": game_id})
        defer.returnValue(deleted)

    def close_tab_action(self, args):
        """
        Processes requests to remove game from player's list of tabs.
        Expects 'player_id' and 'game_id' to be present in the args.
        Removes association between player and game from the tabs table.
        """
        self.required(args, "close_tab_action", "player_id")
        game_id = self.required_game_id(args)
        player_id = int(args["player_id"][0])
        d = self.close_tab(player_id, game_id)

        def success(result):
            return {"type": "close_tab_action"}

        d.addCallback(success)
        return d

    @defer.inlineCallbacks
    def update_players_info(self, players_info, players_id_list):
        """Add new player ids as key to players_info dict, from players_list"""
        # Python's DB-API doesn't support interpolating lists into SQL's "WHERE x IN (...)" statements,
        # so we have to generate the correct number of '?' placeholders programatically.
        format_strings = ",".join(["?"] * len(players_id_list))
        sql_statement = "SELECT player_id, score FROM players WHERE player_id IN (%s)" % format_strings
        rows = yield self.db.runQuery(sql_statement, players_id_list)
        # Build up a dict of {player_id: player_level} key-value pairs.
        levels = {}
        for row in rows:
            level, _, _ = calculate_level(row[1])
            levels[row[0]] = level

        for player_id in players_id_list:
            if player_id not in players_info:
                info = {}
                if levels.has_key(player_id):
                    info["level"] = levels[player_id]
                try:
                    info["name"] = yield self.auth.get_player_name(player_id)
                    info["avatar_url"] = yield self.auth.get_player_avatar_url(player_id)
                except Exception as e:
                    raise CardstoriesException("Failed fetching player data (player_id=%s): %s" % (player_id, e))
                players_info[str(player_id)] = info

        defer.returnValue(players_info)

    @defer.inlineCallbacks
    def player_info(self, args):
        """Process requests to retreive player_info for a player_id"""

        self.required(args, "player_info", "player_id")

        players_info = {"type": "players_info"}
        yield self.update_players_info(players_info, args["player_id"])
        defer.returnValue([players_info])

    @defer.inlineCallbacks
    def state(self, args):
        self.required(args, "state", "type", "modified")
        states = []
        players_info = {"type": "players_info"}  # Keep track of all players being referenced

        if "game" in args["type"]:
            game_args = {"action": "game", "game_id": args["game_id"]}
            if args.has_key("player_id"):
                game_args["player_id"] = args["player_id"]

            game, players_id_list = yield self.game(game_args)
            game["type"] = "game"
            states.append(game)
            yield self.update_players_info(players_info, players_id_list)

        if "tabs" in args["type"]:
            game_ids = yield self.get_open_tabs(args)
            tabs = {"type": "tabs", "games": []}
            player_id = args.get("player_id")
            max_modified = 0
            for game_id in game_ids:
                game_args = {"action": "game", "game_id": [game_id]}
                if player_id:
                    game_args["player_id"] = player_id
                game, players_id_list = yield self.game(game_args)
                tabs["games"].append(game)
                if game["modified"] > max_modified:
                    max_modified = game["modified"]
            tabs["modified"] = max_modified
            states.append(tabs)

        for plugin in self.pollable_plugins:
            if plugin.name() in args["type"]:
                state, players_id_list = yield plugin.state(args)
                state["type"] = plugin.name()
                state["modified"] = plugin.get_modified(args=args)
                states.append(state)
                yield self.update_players_info(players_info, players_id_list)

        states.append(players_info)
        defer.returnValue(states)

    @defer.inlineCallbacks
    def game_notify(self, args, game_id):
        if args == None:
            yield self.notify({"type": "delete", "game": self.games[game_id], "details": args})
            del self.games[game_id]
            defer.returnValue(False)

        if not self.games.has_key(game_id):
            defer.returnValue(False)

        game = self.games[game_id]
        d = game.wait(args)
        d.addCallback(self.game_notify, game_id)

        # Start listenning for new game events before asynchronously
        # yielding, to not miss any game notifications.

        yield self.notify({"type": "change", "game": game, "details": args})

        defer.returnValue(True)

    @defer.inlineCallbacks
    def game_init(self, game, sentence, init_type="create", previous_game_id=None):
        self.games[game.get_id()] = game
        args = {
            "type": init_type,
            "modified": [0],
            "game_id": [game.get_id()],
            "sentence": sentence,
            "previous_game_id": previous_game_id,
        }

        args = yield game.wait(args)
        yield self.game_notify(args, game.get_id())

    @defer.inlineCallbacks
    def create(self, args):
        self.required(args, "create", "owner_id")
        owner_id = int(args["owner_id"][0])

        # Keep track of consecutive games
        if "previous_game_id" in args:
            previous_game_id = args["previous_game_id"][0]
        else:
            previous_game_id = None

        game = CardstoriesGame(self)
        game_id = yield game.create(owner_id)

        yield self.game_init(game, "", previous_game_id=previous_game_id)

        defer.returnValue({"game_id": game_id})

    def complete(self, args):
        self.required(args, "complete", "owner_id")
        owner_id = int(args["owner_id"][0])
        game_id = self.required_game_id(args)
        d = self.game_method(game_id, "complete", owner_id)
        return d

    def game(self, args):
        self.required(args, "game")
        game_id = self.required_game_id(args)
        if args.has_key("player_id"):
            player_id = int(args["player_id"][0])
        else:
            player_id = None
        if self.games.has_key(game_id):
            return self.games[game_id].game(player_id)
        else:
            game = CardstoriesGame(self, game_id)
            d = game.game(player_id)

            def destroy(game_info):
                game.destroy()
                return game_info

            d.addCallback(destroy)
            return d

    def game_method(self, game_id, action, *args, **kwargs):
        if not self.games.has_key(game_id):
            raise CardstoriesWarning("GAME_NOT_LOADED", {"game_id": game_id})
        return getattr(self.games[game_id], action)(*args, **kwargs)

    def set_card(self, args):
        self.required(args, "set_card", "player_id", "card")
        player_id = int(args["player_id"][0])
        card = int(args["card"][0])
        game_id = self.required_game_id(args)
        return self.game_method(game_id, args["action"][0], player_id, card)

    def set_sentence(self, args):
        self.required(args, "set_sentence", "player_id", "sentence")
        player_id = int(args["player_id"][0])
        game_id = self.required_game_id(args)
        sentence = args["sentence"][0].decode("utf-8")
        return self.game_method(game_id, args["action"][0], player_id, sentence)

    def participate(self, args):
        self.required(args, "participate", "player_id")
        player_id = int(args["player_id"][0])
        game_id = self.required_game_id(args)
        return self.game_method(game_id, args["action"][0], player_id)

    def player2game(self, args):
        self.required(args, "player2game", "player_id")
        player_id = int(args["player_id"][0])
        game_id = self.required_game_id(args)
        return self.game_method(game_id, args["action"][0], player_id)

    def pick(self, args):
        self.required(args, "pick", "player_id", "card")
        player_id = int(args["player_id"][0])
        card = int(args["card"][0])
        game_id = self.required_game_id(args)
        return self.game_method(game_id, args["action"][0], player_id, card)

    def vote(self, args):
        self.required(args, "vote", "player_id", "card")
        player_id = int(args["player_id"][0])
        card = int(args["card"][0])
        game_id = self.required_game_id(args)
        return self.game_method(game_id, args["action"][0], player_id, card)

    def voting(self, args):
        self.required(args, "voting", "owner_id")
        owner_id = int(args["owner_id"][0])
        game_id = self.required_game_id(args)
        return self.game_method(game_id, args["action"][0], owner_id)

    @defer.inlineCallbacks
    def invite(self, args):
        self.required(args, "invite")
        if args.has_key("invited_email"):
            player_ids = yield self.auth.get_players_ids(args["invited_email"], create=True)
        else:
            player_ids = []

        if args.has_key("player_id"):
            player_ids += args["player_id"]

        game_id = self.required_game_id(args)
        result = yield self.game_method(game_id, args["action"][0], player_ids)
        defer.returnValue(result)

    def set_countdown(self, args):
        self.required(args, "set_countdown", "duration")
        duration = int(args["duration"][0])
        game_id = self.required_game_id(args)
        return self.game_method(game_id, args["action"][0], duration)

    def grantCardsInteraction(self, transaction, player_id, card_ids):
        cards = [chr(i) for i in card_ids]
        transaction.execute("SELECT earned_cards FROM players WHERE player_id = ?", [player_id])
        earned_cards = transaction.fetchone()[0]
        if earned_cards is None:
            earned_cards = []
        else:
            earned_cards = list(earned_cards)

        for card in cards:
            if card not in earned_cards:
                earned_cards.append(card)

        transaction.execute(
            "UPDATE players SET " "earned_cards = ? " "WHERE player_id = ?", ("".join(earned_cards), player_id)
        )

    @defer.inlineCallbacks
    def grant_cards_to_player(self, args):
        self.required(args, "grant_cards_to_player", "player_id", "card_ids")
        player_id = int(args["player_id"][0])
        card_ids = [int(i) for i in args["card_ids"]]
        yield self.db.runInteraction(self.grantCardsInteraction, player_id, card_ids)
        defer.returnValue({"status": "success"})

    def handle(self, result, args, internal_request=False):
        if not args.has_key("action"):
            return defer.succeed(result)
        try:
            action = args["action"][0]
            if action in self.ACTIONS or (internal_request and action in self.ACTIONS_INTERNAL):
                d = getattr(self, action)(args)

                def error(reason):
                    error = reason.value
                    log.err(reason)
                    if reason.type is CardstoriesWarning:
                        return {"error": {"code": error.code, "data": error.data}}
                    else:
                        tb = error.args[0]
                        tb += "\n\n"
                        tb += "".join(traceback.format_tb(reason.getTracebackObject()))
                        return {"error": {"code": "PANIC", "data": tb}}

                d.addErrback(error)
                return d
            else:
                raise CardstoriesException, "Unknown action: %s" % action
        except CardstoriesWarning as e:
            log.err(e)
            return defer.succeed({"error": {"code": e.code, "data": e.data}})
        except Exception as e:
            log.err(e)
            tb = traceback.format_exc()
            return defer.succeed({"error": {"code": "PANIC", "data": tb}})

    @staticmethod
    def required(args, action, *keys):
        for key in keys:
            if not args.has_key(key):
                raise CardstoriesException, "Action '%s' requires argument '%s', but it was missing." % (action, key)
        return True

    @staticmethod
    def required_game_id(args):
        CardstoriesService.required(args, args["action"][0], "game_id")
        game_id = int(args["game_id"][0])
        if game_id <= 0:
            raise CardstoriesException, "game_id cannot be negative: %d" % args["game_id"]
        return game_id
예제 #7
0
class CardstoriesService(service.Service, Observable):

    ACTIONS_GAME = ('set_card', 'set_sentence', 'participate', 'voting',
                    'pick', 'vote', 'complete', 'invite', 'set_countdown')
    ACTIONS = ACTIONS_GAME + ('create', 'poll', 'state', 'player_info',
                              'close_tab_action')

    ACTIONS_INTERNAL = ('grant_cards_to_player')

    def __init__(self, settings):
        self.settings = settings
        self.games = {}
        self.observers = []
        self.pollable_plugins = []
        self.auth = Auth(
        )  # to be overriden by an auth plugin (contains unimplemented interfaces)

    def startService(self):
        database = self.settings['db']
        exists = os.path.exists(database)
        db = sqlite3.connect(database)
        c = db.cursor()
        if exists:
            self.load(c)
        else:
            self.create_base(c)
            db.commit()
        c.close()
        db.close()
        self.db = adbapi.ConnectionPool("sqlite3",
                                        database=database,
                                        cp_noisy=True,
                                        check_same_thread=False)
        self.notify({'type': 'start'})

    @defer.inlineCallbacks
    def stopService(self):
        yield self.notify({'type': 'stop'})
        for game in self.games.values():
            game.destroy()
        defer.returnValue(None)

    def create_base(self, c):
        c.execute("CREATE TABLE games ( "
                  "  id INTEGER PRIMARY KEY, "
                  "  owner_id INTEGER, "
                  "  players INTEGER DEFAULT 1, "
                  "  sentence TEXT, "
                  "  cards TEXT, "
                  "  board TEXT, "
                  "  state VARCHAR(8) DEFAULT 'create', "
                  +  # create, invitation, vote, complete
                  "  created DATETIME, "
                  "  completed DATETIME"
                  "); ")
        c.execute("CREATE INDEX games_idx ON games (id); ")
        c.execute("CREATE TABLE player2game ( "
                  "  serial INTEGER PRIMARY KEY, "
                  "  player_id INTEGER, "
                  "  game_id INTEGER, "
                  "  cards TEXT, "
                  "  picked CHAR(1), "
                  "  vote CHAR(1), "
                  "  win CHAR(1) DEFAULT 'n' "
                  "); ")
        c.execute(
            "CREATE UNIQUE INDEX player2game_idx ON player2game (player_id, game_id); "
        )
        c.execute("CREATE TABLE invitations ( "
                  "  player_id INTEGER, "
                  "  game_id INTEGER"
                  "); ")
        c.execute(
            "CREATE UNIQUE INDEX invitations_idx ON invitations (player_id, game_id); "
        )
        c.execute("CREATE TABLE tabs ( "
                  "  player_id INTEGER, "
                  "  game_id INTEGER, "
                  "  created DATETIME "
                  "); ")
        c.execute(
            "CREATE UNIQUE INDEX tabs_idx ON tabs (player_id, game_id); ")
        c.execute("CREATE TABLE players ( "
                  "  player_id INTEGER, "
                  "  score BIGINTEGER, "
                  "  score_prev BIGINTEGER, "
                  "  levelups INTEGER, "
                  "  earned_cards TEXT, "
                  "  earned_cards_cur TEXT "
                  "); ")
        c.execute("CREATE UNIQUE INDEX players_idx ON players (player_id); ")
        c.execute("CREATE TABLE event_logs ( "
                  "  player_id INTEGER, "
                  "  game_id INTEGER, "
                  "  event_type SMALLINT, "
                  "  data TEXT, "
                  "  timestamp DATETIME "
                  "); ")
        c.execute(
            "CREATE INDEX eventlogs_player_idx ON event_logs (player_id, timestamp); "
        )
        c.execute(
            "CREATE INDEX eventlogs_game_idx ON event_logs (game_id, timestamp); "
        )

    def load(self, c):
        c.execute(
            "SELECT id, sentence FROM games WHERE state != 'complete' AND state != 'canceled'"
        )
        for (id, sentence) in c.fetchall():
            game = CardstoriesGame(self, id)
            game.load(c)

            # Notify listeners of the game, but use the 'load' notification to signal
            # that the game is being loaded, not created
            # Note that the db is not accessible during that stage
            self.game_init(game, sentence, init_type='load')

    def poll(self, args):
        self.required(args, 'poll', 'type', 'modified')
        deferreds = []

        if 'game' in args['type']:
            game_id = self.required_game_id(args)
            if not self.games.has_key(game_id):
                # This means the game has been deleted from memory - probably because
                # it has been completed. The client doesn't seem to be aware of this yet,
                # so just return the poll immediately to let the client know the state
                # has changed.
                return defer.succeed({
                    'game_id': [game_id],
                    'modified': [int(runtime.seconds() * 1000)]
                })
            else:
                deferreds.append(self.games[game_id].poll(args))

        if 'tabs' in args['type']:
            deferreds.append(self.poll_tabs(args))

        for plugin in self.pollable_plugins:
            if plugin.name() in args['type']:
                deferreds.append(plugin.poll(args))

        d = defer.DeferredList(deferreds, fireOnOneCallback=True)
        d.addCallback(lambda x: x[0])

        # Allow listeners to monitor when polls are started or ended
        if deferreds:
            if 'player_id' in args:
                player_id = args['player_id'][0]
            else:
                player_id = None
            self.notify({'type': 'poll_start', 'player_id': player_id})

            def on_poll_end(return_value):
                self.notify({'type': 'poll_end', 'player_id': player_id})
                return return_value

            d.addCallback(on_poll_end)

        return d

    def poll_tabs(self, args):
        """
        Gets the games that should be monitored as tabs by the current user,
        and returns a deferred list of polled games.
        """
        # We need to nest one deferred inside another, because we are dealing with
        # two async operations: fetching game ids from the DB, and waiting in a poll.
        # The outer callback fires when the game ids are fetched from the DB, while the
        # inner one fires when one of the polled games has been modified, causing poll to return.
        outer_deferred = self.get_open_tabs(args)

        def outer_callback(result):
            game_deferreds = []
            for game_id in result:
                if self.games.has_key(game_id):
                    game_deferreds.append(self.games[game_id].poll(args))

            def inner_callback(result):
                # Make the tabs poll always return just the arguments with updated timestamp.
                if result[0] != None:
                    args['modified'] = result[0]['modified']
                return args

            inner_deferred = defer.DeferredList(game_deferreds,
                                                fireOnOneCallback=True)
            inner_deferred.addCallback(inner_callback)
            return inner_deferred

        outer_deferred.addCallback(outer_callback)
        return outer_deferred

    @defer.inlineCallbacks
    def get_open_tabs(self, args):
        """
        Expects 'player_id' and optionally a 'game_id' in the args.
        If there is a 'game_id' in the args and that game_id is not yet associated
        with the player in the tabs table, it associates the game_id with player_id in
        the table.
        Returns a list of game_ids associated with the player in the tabs table.
        """
        player_id = args['player_id'][0]
        game_id = args.has_key('game_id') and args['game_id'][0]
        try:
            game_id = int(game_id)
        except:
            game_id = None
        if player_id:
            # Try to associate current game with the player in the tabs table.
            # This wont't do any harm if current game is already open in a tab.
            if game_id:
                yield self.open_tab(player_id, game_id)
            game_ids = yield self.get_tabs_for_player(player_id)
        else:
            game_ids = []
        defer.returnValue(game_ids)

    @defer.inlineCallbacks
    def get_tabs_for_player(self, player_id):
        """
        Returns a deferred which results in a list of game_ids of games
        which the player keeps open in tabs.
        """
        sql = 'SELECT game_id from tabs WHERE player_id = ? ORDER BY created ASC'
        rows = yield self.db.runQuery(sql, [player_id])
        game_ids = []
        for row in rows:
            game_ids.append(row[0])
        defer.returnValue(game_ids)

    def openTabInteraction(self, transaction, player_id, game_id):
        inserted = False
        # Make sure the game exists before trying to associate it with the player.
        transaction.execute('SELECT id FROM games WHERE id = ?', [game_id])
        rows = transaction.fetchall()
        if len(rows):
            # Make sure the game isn't already associated with the player.
            transaction.execute(
                'SELECT * FROM tabs WHERE player_id = ? AND game_id = ?',
                [player_id, game_id])
            rows = transaction.fetchall()
            if not len(rows):
                sql = "INSERT INTO tabs (player_id, game_id, created) VALUES (?, ?, datetime('now'))"
                transaction.execute(sql, [player_id, game_id])
                inserted = True
        return inserted

    def closeTabInteraction(self, transaction, player_id, game_id):
        transaction.execute(
            'SELECT * FROM tabs WHERE player_id = ? AND game_id = ?',
            [player_id, game_id])
        rows = transaction.fetchall()
        deleted = False
        if len(rows):
            transaction.execute(
                'DELETE FROM tabs WHERE player_id = ? AND game_id = ?',
                [player_id, game_id])
            deleted = True
        return deleted

    @defer.inlineCallbacks
    def open_tab(self, player_id, game_id):
        """
        Associates game_id with player_id in the tabs table, if they are not already
        associated, and returns True.
        If they are already assiociated, doesn't do anything and returns False.
        """
        inserted = yield self.db.runInteraction(self.openTabInteraction,
                                                player_id, game_id)
        if inserted:
            self.notify({
                'type': 'tab_opened',
                'player_id': player_id,
                'game_id': game_id
            })
        defer.returnValue(inserted)

    @defer.inlineCallbacks
    def close_tab(self, player_id, game_id):
        """
        Removes the association between player_id and game_id from the tabs table
        and returns True.
        If player_id and game_id weren't associated, doesn't do anything and returns False.
        """
        deleted = yield self.db.runInteraction(self.closeTabInteraction,
                                               player_id, game_id)
        if deleted:
            self.notify({
                'type': 'tab_closed',
                'player_id': player_id,
                'game_id': game_id
            })
        defer.returnValue(deleted)

    def close_tab_action(self, args):
        """
        Processes requests to remove game from player's list of tabs.
        Expects 'player_id' and 'game_id' to be present in the args.
        Removes association between player and game from the tabs table.
        """
        self.required(args, 'close_tab_action', 'player_id')
        game_id = self.required_game_id(args)
        player_id = int(args['player_id'][0])
        d = self.close_tab(player_id, game_id)

        def success(result):
            return {'type': 'close_tab_action'}

        d.addCallback(success)
        return d

    @defer.inlineCallbacks
    def update_players_info(self, players_info, players_id_list):
        '''Add new player ids as key to players_info dict, from players_list'''
        # Python's DB-API doesn't support interpolating lists into SQL's "WHERE x IN (...)" statements,
        # so we have to generate the correct number of '?' placeholders programatically.
        format_strings = ','.join(['?'] * len(players_id_list))
        sql_statement = 'SELECT player_id, score FROM players WHERE player_id IN (%s)' % format_strings
        rows = yield self.db.runQuery(sql_statement, players_id_list)
        # Build up a dict of {player_id: player_level} key-value pairs.
        levels = {}
        for row in rows:
            level, _, _ = calculate_level(row[1])
            levels[row[0]] = level

        for player_id in players_id_list:
            if player_id not in players_info:
                info = {}
                if levels.has_key(player_id):
                    info['level'] = levels[player_id]
                try:
                    info['name'] = yield self.auth.get_player_name(player_id)
                    info['avatar_url'] = yield self.auth.get_player_avatar_url(
                        player_id)
                except Exception as e:
                    raise CardstoriesException(
                        'Failed fetching player data (player_id=%s): %s' %
                        (player_id, e))
                players_info[str(player_id)] = info

        defer.returnValue(players_info)

    @defer.inlineCallbacks
    def player_info(self, args):
        '''Process requests to retreive player_info for a player_id'''

        self.required(args, 'player_info', 'player_id')

        players_info = {'type': 'players_info'}
        yield self.update_players_info(players_info, args['player_id'])
        defer.returnValue([players_info])

    @defer.inlineCallbacks
    def state(self, args):
        self.required(args, 'state', 'type', 'modified')
        states = []
        players_info = {
            'type': 'players_info'
        }  # Keep track of all players being referenced

        if 'game' in args['type']:
            game_args = {'action': 'game', 'game_id': args['game_id']}
            if args.has_key('player_id'):
                game_args['player_id'] = args['player_id']

            game, players_id_list = yield self.game(game_args)
            game['type'] = 'game'
            states.append(game)
            yield self.update_players_info(players_info, players_id_list)

        if 'tabs' in args['type']:
            game_ids = yield self.get_open_tabs(args)
            tabs = {'type': 'tabs', 'games': []}
            player_id = args.get('player_id')
            max_modified = 0
            for game_id in game_ids:
                game_args = {'action': 'game', 'game_id': [game_id]}
                if player_id: game_args['player_id'] = player_id
                game, players_id_list = yield self.game(game_args)
                tabs['games'].append(game)
                if game['modified'] > max_modified:
                    max_modified = game['modified']
            tabs['modified'] = max_modified
            states.append(tabs)

        for plugin in self.pollable_plugins:
            if plugin.name() in args['type']:
                state, players_id_list = yield plugin.state(args)
                state['type'] = plugin.name()
                state['modified'] = plugin.get_modified(args=args)
                states.append(state)
                yield self.update_players_info(players_info, players_id_list)

        states.append(players_info)
        defer.returnValue(states)

    @defer.inlineCallbacks
    def game_notify(self, args, game_id):
        if args == None:
            yield self.notify({
                'type': 'delete',
                'game': self.games[game_id],
                'details': args
            })
            del self.games[game_id]
            defer.returnValue(False)

        if not self.games.has_key(game_id):
            defer.returnValue(False)

        game = self.games[game_id]
        d = game.wait(args)
        d.addCallback(self.game_notify, game_id)

        # Start listenning for new game events before asynchronously
        # yielding, to not miss any game notifications.

        yield self.notify({'type': 'change', 'game': game, 'details': args})

        defer.returnValue(True)

    @defer.inlineCallbacks
    def game_init(self,
                  game,
                  sentence,
                  init_type='create',
                  previous_game_id=None):
        self.games[game.get_id()] = game
        args = {
            'type': init_type,
            'modified': [0],
            'game_id': [game.get_id()],
            'sentence': sentence,
            'previous_game_id': previous_game_id
        }

        args = yield game.wait(args)
        yield self.game_notify(args, game.get_id())

    @defer.inlineCallbacks
    def create(self, args):
        self.required(args, 'create', 'owner_id')
        owner_id = int(args['owner_id'][0])

        # Keep track of consecutive games
        if 'previous_game_id' in args:
            previous_game_id = args['previous_game_id'][0]
        else:
            previous_game_id = None

        game = CardstoriesGame(self)
        game_id = yield game.create(owner_id)

        yield self.game_init(game, '', previous_game_id=previous_game_id)

        defer.returnValue({'game_id': game_id})

    def complete(self, args):
        self.required(args, 'complete', 'owner_id')
        owner_id = int(args['owner_id'][0])
        game_id = self.required_game_id(args)
        d = self.game_method(game_id, 'complete', owner_id)
        return d

    def game(self, args):
        self.required(args, 'game')
        game_id = self.required_game_id(args)
        if args.has_key('player_id'):
            player_id = int(args['player_id'][0])
        else:
            player_id = None
        if self.games.has_key(game_id):
            return self.games[game_id].game(player_id)
        else:
            game = CardstoriesGame(self, game_id)
            d = game.game(player_id)

            def destroy(game_info):
                game.destroy()
                return game_info

            d.addCallback(destroy)
            return d

    def game_method(self, game_id, action, *args, **kwargs):
        if not self.games.has_key(game_id):
            raise CardstoriesWarning('GAME_NOT_LOADED', {'game_id': game_id})
        return getattr(self.games[game_id], action)(*args, **kwargs)

    def set_card(self, args):
        self.required(args, 'set_card', 'player_id', 'card')
        player_id = int(args['player_id'][0])
        card = int(args['card'][0])
        game_id = self.required_game_id(args)
        return self.game_method(game_id, args['action'][0], player_id, card)

    def set_sentence(self, args):
        self.required(args, 'set_sentence', 'player_id', 'sentence')
        player_id = int(args['player_id'][0])
        game_id = self.required_game_id(args)
        sentence = args['sentence'][0].decode('utf-8')
        return self.game_method(game_id, args['action'][0], player_id,
                                sentence)

    def participate(self, args):
        self.required(args, 'participate', 'player_id')
        player_id = int(args['player_id'][0])
        game_id = self.required_game_id(args)
        return self.game_method(game_id, args['action'][0], player_id)

    def player2game(self, args):
        self.required(args, 'player2game', 'player_id')
        player_id = int(args['player_id'][0])
        game_id = self.required_game_id(args)
        return self.game_method(game_id, args['action'][0], player_id)

    def pick(self, args):
        self.required(args, 'pick', 'player_id', 'card')
        player_id = int(args['player_id'][0])
        card = int(args['card'][0])
        game_id = self.required_game_id(args)
        return self.game_method(game_id, args['action'][0], player_id, card)

    def vote(self, args):
        self.required(args, 'vote', 'player_id', 'card')
        player_id = int(args['player_id'][0])
        card = int(args['card'][0])
        game_id = self.required_game_id(args)
        return self.game_method(game_id, args['action'][0], player_id, card)

    def voting(self, args):
        self.required(args, 'voting', 'owner_id')
        owner_id = int(args['owner_id'][0])
        game_id = self.required_game_id(args)
        return self.game_method(game_id, args['action'][0], owner_id)

    @defer.inlineCallbacks
    def invite(self, args):
        self.required(args, 'invite')
        if args.has_key('invited_email'):
            player_ids = yield self.auth.get_players_ids(args['invited_email'],
                                                         create=True)
        else:
            player_ids = []

        if args.has_key('player_id'):
            player_ids += args['player_id']

        game_id = self.required_game_id(args)
        result = yield self.game_method(game_id, args['action'][0], player_ids)
        defer.returnValue(result)

    def set_countdown(self, args):
        self.required(args, 'set_countdown', 'duration')
        duration = int(args['duration'][0])
        game_id = self.required_game_id(args)
        return self.game_method(game_id, args['action'][0], duration)

    def grantCardsInteraction(self, transaction, player_id, card_ids):
        cards = [chr(i) for i in card_ids]
        transaction.execute(
            'SELECT earned_cards FROM players WHERE player_id = ?',
            [player_id])
        earned_cards = transaction.fetchone()[0]
        if earned_cards is None:
            earned_cards = []
        else:
            earned_cards = list(earned_cards)

        for card in cards:
            if card not in earned_cards:
                earned_cards.append(card)

        transaction.execute(
            'UPDATE players SET '
            'earned_cards = ? '
            'WHERE player_id = ?', (''.join(earned_cards), player_id))

    @defer.inlineCallbacks
    def grant_cards_to_player(self, args):
        self.required(args, 'grant_cards_to_player', 'player_id', 'card_ids')
        player_id = int(args['player_id'][0])
        card_ids = [int(i) for i in args['card_ids']]
        yield self.db.runInteraction(self.grantCardsInteraction, player_id,
                                     card_ids)
        defer.returnValue({'status': 'success'})

    def handle(self, result, args, internal_request=False):
        if not args.has_key('action'):
            return defer.succeed(result)
        try:
            action = args['action'][0]
            if action in self.ACTIONS or (internal_request
                                          and action in self.ACTIONS_INTERNAL):
                d = getattr(self, action)(args)

                def error(reason):
                    error = reason.value
                    log.err(reason)
                    if reason.type is CardstoriesWarning:
                        return {
                            'error': {
                                'code': error.code,
                                'data': error.data
                            }
                        }
                    else:
                        tb = error.args[0]
                        tb += '\n\n'
                        tb += ''.join(
                            traceback.format_tb(reason.getTracebackObject()))
                        return {'error': {'code': 'PANIC', 'data': tb}}

                d.addErrback(error)
                return d
            else:
                raise CardstoriesException, 'Unknown action: %s' % action
        except CardstoriesWarning as e:
            log.err(e)
            return defer.succeed({'error': {'code': e.code, 'data': e.data}})
        except Exception as e:
            log.err(e)
            tb = traceback.format_exc()
            return defer.succeed({'error': {'code': 'PANIC', 'data': tb}})

    @staticmethod
    def required(args, action, *keys):
        for key in keys:
            if not args.has_key(key):
                raise CardstoriesException, "Action '%s' requires argument '%s', but it was missing." % (
                    action, key)
        return True

    @staticmethod
    def required_game_id(args):
        CardstoriesService.required(args, args['action'][0], 'game_id')
        game_id = int(args['game_id'][0])
        if game_id <= 0:
            raise CardstoriesException, 'game_id cannot be negative: %d' % args[
                'game_id']
        return game_id