Database Team (CSCI 599 IMS)

Contents

last modified: Tuesday, 08-Oct-2002 14:00:32 PDT

Members

• Project Management
  • Beomjoo Seo bseo@usc.edu
• Mini-Server (Input Manager, Query Manager, Replay Agent)
  • Aditya Pandurangan apandura@usc.edu
  • Brijesh Gadhiya gadhiya@usc.edu
  • Preetham Chandrasekhar pchandra@usc.edu
  • Xin Liu xinliu@usc.edu
• Database Management Module
  • Jinwoo Kim jinwook@usc.edu
  • Wei-Shinn Ku wku@usc.edu
• Test Code Generation
  • Wenhan Wang wenhanwa@usc.edu

Introduction

Database group provides the DB related interface to a game server and spectator clients.

Approach

Project, Distributed Collaborative Game, is assumed to have a game server, multiple player clients, a database server, and several live and replay spectator clients. Database module primarily concerns about how to store the updated game information and the events we are interested in and to retrieve the results for a given query.

Database module consists of four software components; input manager, DB interface, query manager, and replay agent. Especially, input manager, query manager, and replay agent are so strongly related with the MFSM design concept.

Fig 1. System Architecture

Input Manager(IM) is a MFSM Cell, whose input active stream is connected to the leaf node of the application graph of the game server, whose path transmits the updated world descriptions per active pulse through the network module. Whenever it receives the updated world, it analyses its inputs and figures out what kind of database queries it should issue to store its raw, augmented, or transformed records.

DB Interface(DBI) provides the ODBC connectivity to the commercial DB products like IBM DB2 or Informix. Of course, this module supports a lots of APIs, which are primarily called from IM or QM for inserting, modifying, deleting the incoming records, and querying. By separating the actual implementation of the set of DB specific data table from the abstraction of the transformed query inputs, designer of IM and QM does not need to know the whole internal representations of the tables at DB side. Additionaly, it gives the web interface to the clients.

Query Manager(QM) is a permanent Cell whose active input stream is connected to the client UI module or spectator clients. As soon as it receives the user request from spectator clients or other based dialog UIs, it constructs the corresponding SQL query and sends it to the DB engine through the DBI. When it get the results from engine, it creates a local world to send a newly created world representation to the network, which will be received by the Spectator. And waits some time for the next time stamp to issue a query to a DBI to get the updated world representation. It terminates when the time period expires.

Reference

Common Datastructures

We do not use CNode declared in MFSM because all the data structure defined in this module is not used by any other modules.

Struct team_info_t

• m_listPlayersInfo : list of the information of active players playing now.
• m_uiScore : current score if playing a game now. Otherwise, it should be null. Its value will be updated whenever it meets any goal condition.
• This is a temporary structure. At this point, project does not define any team related information but it is sure to use.

Class Snapshot

• std::string m_spectorName : the name of spector who requests the replay query to DB. this information has no side-effect even if you do not specify.
• m_GameID : unique ID represented as a long long type. This value will be assigned by IM and manipulated only in DB. It is invisible to outside.
• m_cFieldInfo : field information like field width, height, goal area.
• m_cBallInfo : It contains ball information.
• m_listPlayersInfo
• m_GameTS : The exact time the snashot has been updated.
• m_sid : Logical game clock starting from 0. This value is useful for replay implementation at QM. This value is incremental.

Cells

Class InputManager

• String ID: DATABASE_IM
• Passive Filter:
• Active Filter:

Class QueryManager

• String ID: DATABASE_QM
• Passive Filter:
• Active Filter:

Others

Class DataBaseInterface

• m_bConnected : It indicates whether DBI is already connected to the DB server. Otherwise, it should be connected before querying.
• m_strUserId, m_strPasswd : user authentication information when connecting to the DB2.
• m_strDSN : Data Source Name which is mandatory when accessing the local ODBC Driver.
• m_strStatement : Local Variable to construct any SQL statement
• m_hStatement : Windows Handle to access the ODBC statement
• m_hDbc : Windows ODBC Connection Handle
• m_hEnv : Windows ODBC Environment Handle
• m_pBuf[1024] : Temporary Memory to construct a SQL statement

Database Management Module

E-R Diagram

Fig 2. E-R diagream for game world

Above tables is the official E-R diagram. It may miss may specific attributes and key declarations because of many changes of the world description (like CMass).

But the basic functionality or basic entities and relations are not changed anymore. Everybody who wonders how the DB construct its internal tables may refer to this figure.

Team entity, Ball entity, and Player entity are are assumed to be constructed before Game Server starts. And member of relation will also be constructed in advance.

Descriptions

OR(Object Relational) DB Tables

Implementatioon Issues

Creating Tables

• Database Name: G2
• Source: createdb.sql

Descriptions

• Game Table:
  This table contains two attributes, Game Identifier and the Information of the Field like field width, height and goal area information. The field attribute is a composite attribute declared at type field_t. Inserting a new game is requested by IM. All the game should have a corresponding game tuple in this table.
• Team Table:
  This table contains two attributes, Name of a Team and the current score information which is live updated.
• Player Table:
  This table contains three attributes; name of the player, its radius, and its height. It only stores the static information of the player. Note that it does not store any dynamic information like current position of the player.
• Ball Table:
• Snapshot table:

DBI APIs

connect

• Input
• Output
• Return Value
• Description
  • This API connects the application program to DB2 database through the ODBC driver.

disconnect

• Input
• Output
• Return Value
• Description

set_game

• Input
  • GID_TYPE game_id
  • DWORD startTime
  • common::CField *p_Field
    contains the field information to be recorded
• Output
• Return Value
  It returns success or failure
• Description

set_rivalry

• Input
  • GID_TYPE game_id
  • const char *TeamName1
  • const char *TeamName2
• Output
• Return Value
  It returns success or failure
• Description
  TeamName1 and TeamName2 should be a unique in table Team

set_use_ball

• Input
  • GID_TYPE game_id
  • common::CBall *p_Ball
    contains the ball information
• Output
• Return Value
  It returns success or failure
• Description

set_play_in

• Input
  • GID_TYPE game_id
  • common::CPlayer *p_player
• Output
• Return Value
  It returns success or failure
• Description

set_movement

• Input
  • GID_TYPE game_id
  • DWORD currentTime
  • bool is_player
    if this function records the movement of player, this value should be true. Otherwise, it should be set to false.
  • common::CMass *massObj
    containing the movement information of a mass object
• Output
• Return Value
  It returns success or failure
• Description

set_snapshot

• Input
  • Snapshot *p_snapshot
• Output
• Return Value
  It returns success or failure
• Description

set_teamscore_at_time

• Input
  • const char *teamName
  • int score
• Output
• Return Value
  It returns success or failure
• Description

Input Manager

When game server starts its game, IM will parse this event from the data which is passed through the application graph per active pulse. Because there is no information of the game world, IM should construct its local world which is invisible to other modules or cells to represent what the current world description is. It is so unclear whether we can use synchronized world description maintained by game server that temporarily we just maintain our specific local world representations which will not be affected by changes of other modules but by transmitted active stream.

DB Initialization

IM assigns a specific game id for the game. We assume the start time of a game is a unique value, which is being used as a unique game id. After generating a game id, it constructs a local world, which also needs which players joins this game and what kind of ball is used. Constructing the local world, it creates a game environment;

DBI::set_game_environment( game id, field info )

Then, it requests DBI to initialize all related relation tables;
DBI::use_ball( game id, ball model no.)

DBI::rivalry( game id, team name, team name)

while (all the players joining the game) DBI::play_in( game id, player name )

DBI::progress( game id, time stamp )

DBI::current_ball_position( game id, time stamp, 0, 0, 0 )

for (all the players joining the game) DBI::current_player_position(game id, time stamp, player name, 0, 0, 0)

DB updates

whenever IM received a updated game world, it compares the value with the current world description. If it found any changes, it issues the updated commands accordingly.

DBI::progress( game id, time stamp )

If ball moves, DBI::current_ball_position( game id, time stamp, new x pos, new y pos, new z pos)

If player moves, DBI::current_player_position( game id, time stamp, new x pos, new y pos, new z pos)

Query Manager

Query Manager(QM) is a Xcell which handles replace requests from multiple spectators. It will first parse the ReplayRequest Node from the active pulse and then query the database according to the value assigned in the ReplayRequest Node such as 'gameID', 'from', and 'to' field to retrieve all the game snapshots within the time interval from time 'from' to time 'to'. After QM gets those snapshots, it will packet those snapshot as DBWorld and then adds it to the active pulse for later Xcell use. If spectator wants to replay all the live stream, it should specify the 'to' as -1.

QM needs to retrieve the correct snapshots by its sequence ID while not by time as user assigned, so here QM must map time to its corresponding time value. After it gets the corret time interval, QM will call serials DBI function to generate the snapshot for constructing the DBWorld.

The functions QM uses are as follows;

Run QM
QueryManager::Process(fsf::CPassiveHandle *pPassiveHandle, fsf::CActiveHandle *pActiveHandle, fsf::CActivePulse *pActivePulse)

Map time to corresponding sequence ID
DBI::get_Start_SID(game id, from, SID)
DBI::get_End_SID(game id, to, SID)

get the moved CMass Objects including ball and player and construct the whole snapshot
DBI::get_Movement(game id, current SID, ball or player, CMassObj &Object)
DBI::get_Snapshot(game id, current SID, Snapshot &snapshot)

Schedule and Progress Report

- 15/Oct/2002

• DBI: install MS-ACCESS and DB2 done
• DBI: test the ODBC connectivity to DB2 done
• DBI: design basic database table done
• IM: describe the data structures used by IM done

- 22/Oct/2002

• DBI: implement the database table based on OR data model done
• DBI: provide DB recording C++ programming interface to DB2 done
• DBI: design DB querying C++ programming interfaces done
• IM: implement IM data structures and methods done
• IM: test the validity of IM functionalities done

- 29/Oct/2002

• DBI: design an extended database table done
• DBI: implement DB querying C++ programming interfaces done
• DBI+IM: integration of its functionality done
• QM: describe the data structure and Method done

- 12/Nov/2002

• QM: implementation done
• Test Code done
• DBI+IM+QM: integration test done

References

// TODO