CMPSEM027 Online Gaming

1
CMPSEM027Online Gaming

• Abdennour El Rhalibi

2
Internet-based Database Systems
3
Content

• Lecture 1 Aspects of Network Games, TCP/IP
  Fundamentals, Internet Services and Application
  Layer Protocol, DirectPlay Overview, Online Games
  Issues
• Lecture 2 Network Game Design Fundamentals
• Lecture 3 DirectPlay (1)
• Lecture 4 DirectPlay (2)
• Lecture 5 Socket Programming (1)
• Lecture 6 Socket Programming (2)
• Lecture 7 Internet-based Database Systems
• Lecture 8 Concurrent/Multi-Threading Programming
  in Client/Server Software
• Lecture 9 Massively Multiplayer Games Design and
  Issues and Solutions Implementation
• Lecture 10 11 The Fundamentals and Issues of
  Mobile Games Mobile Game Development
• Lecture 12 Advanced Topics about Online Gaming

4
Introduction

• This lecture covers how to create a stable and
  fast database system for the game server.
• We will discuss the way to access the database
  from sockets and the Internet.

5
Why dont store the data in local? (1)

• It is possible to store player information on the
  players local machine, but it makes much more
  sense to store the data on the server for many
  reasons.
• The first and most important reason is to avoid
  players running hacks on their own computer to
  change their character data in the game or even
  backing the data up to another file.
• If the data is stored on the server, it makes it
  impossible for the player to run a local game
  hack that will modify the character data.

6
Why dont store the data in local? (2)

• Another reasons for this is to allow players to
  play from different locations and machines
  without having to copy their character data to
  another computer.

7
MySQL

• Free download from http//www.mysql.com
• Most updated release version is 5.0
• Can be run in both UNIX and Windows platforms
• TCP/IP supported

8
(No Transcript)
9
(No Transcript)
10
(No Transcript)
11
(No Transcript)
12
Summary of MySQL Administration commands

• To start the MySQL Daemon
• mysqld or mysqld-nt
• To stop the MySQL Daemon
• mysqladmin shutdown
• To start the MySQL command based interface
• mysql
• To exit the MySQL command based interface
• quit

13
Creating a Database in MySQL

• To create our gamedata database
• mysqlgt CREATE DATABASE gamedata
• To view all the databases that have been created
  in our MySQL server
• mysqlgt SHOW DATABASE
• To select which database we wish to work with
• mysqlgt USE gamedata

14
Adding Tables to a Database (1)

• We would like to create a table to store several
  players character data in a database, each record
  should (at least) have the following information
  about the character for each player
• Character Name
• X Position on the Map
• Y Position on the Map
• Current Experience Level

15
Adding Tables to a Database (2)

• Following are the commands to create the table
• mysqlgt USE gamedata
• mysqlgt CREATE TABLE playerinfo (
• mysqlgt id INT AUTO_INCREMENT,
• mysqlgt name VARCHAR(30),
• mysqlgt xpos INT,
• mysqlgt ypos INT,
• mysqlgt exp INT,
• mysqlgt PRIMARY KEY(id))

16
Adding Data to Tables

• We would like to add four records into the
  playerinfo table
• mysqlgt INSERT INTO playerinfo VALUES
• mysqlgt (NULL, Peter, 14, 35, 1431),
• mysqlgt (NULL, Paul, 21, 86, 4563),
• mysqlgt (NULL, Mary, 10, 5, 231),
• mysqlgt (NULL, John, 13, 73, 5400)

17
Viewing Data in a Table

• To select all the data from our table, we would
  use the following SELECT command
• mysqlgt SELECT FROM playerinfo
• mysqlgt SELECT name, exp FROM playerinfo
• mysqlgt SELECT FROM playerinfo WHERE exp gt 1000
• mysqlgt SELECT id, name FROM playerinfo WHERE ypos
  86
• mysqlgt SELECT FROM playerinfo WHERE name LIKE
  P

18
Adding Extra Fields to Tables

• We would like to modify the existing table
  structure
• mysqlgt ALTER TABLE playerinfo ADD lastlogin
  TIMESTAMP
• mysqlgt DESCRIBE playerinfo
• mysqlgt ALTER TABLE playerinfo MODIFY name
  VARCHAR(50)

19
Updating Data in a Field

• To update the field lastlogin for Johns
  record to NULL
• mysqlgt UPDATE playerinfo SET lastlogin NULL
  WHERE name John
• To update the field lastlogin for Pauls
  record to 134535 05 Jan 2006
• mysqlgt UPDATE playerinfo SET lastlogin
  20060105134535 WHERE name Paul

20
Ordering Output

• To view all the data in the table, but we wish to
  sort the names in alphabetical order, we can
  use the following command
• mysql gt SELECT from playerinfo ORDER BY name
• Other SELECT commands
• mysql gt SELECT from playerinfo ORDER BY name
  DESC

21
Retrieving the Last Data Entered

• To select the maximum ID and store it in a
  variable
• mysqlgt SELECT _at_maxidMAX(id) FROM playerinfo
• mysqlgt SELECT FROM playerinfo WHERE id
  _at_maxid
• mysqlgt SELECT _at_lastloginMAX(lastlogin) FROM
  playerinfo
• mysqlgt SELECT FROM playerinfo WHERE lastlogin
  _at_lastlogin

22
Limiting Output Data

• To limit the output to a certain amount
• mysqlgt SELECT FROM playerinfo WHERE name LIKE
  P LIMIT 2

23
Deleting Data from a Table

• To delete a record from a table
• mysqlgt DELETE FROM playerinfo WHERE name
  Peter
• mysqlgt DELETE FROM playerinfo WHERE id 3

24
Deleting Tables and Databases

• To delete a table
• mysqlgt DROP TABLE playerinfo
• mysqlgt SHOW TABLES
• To delete a database
• mysqlgt DROP DATABASE gamedata
• mysqlgt SHOW DATABASES

25
Relational Databases (1)

• Similar to other Management Information Systems,
  the database design for an online game
  server/multiplayer game server is similar
• NOT possible to use a single table to store all
  the records
• Therefore, a set of tables are required
• As a result, normalization and table relationship
  should be formed

26
Relational Databases (2)

• We need to create Five tables for the online game
  server
• Player Data (id, name, X position, Y position,
  last login)
• Payment Info (id, player id, date payment made,
  payment type, amount paid)
• Notes (id, player id, note)
• Rel Friends (id, player id, friend id)
• Rel Enemies (id, player id, enemy id)

27
Creating the Relational Database (1)

• To create the database
• mysqlgt CREATE DATABASE onlinegame
• mysqlgt SHOW DATABASES
• mysqlgt USE onlinegame
• Player Data
• mysqlgt CREATE TABLE playerdata (
• mysqlgt id INT auto_increment,
• mysqlgt name VARCHAR(50),
• mysqlgt xpos INT,
• mysqlgt ypos INT,
• mysqlgt lastlogin TIMESTAMP,
• mysqlgt PRIMARY KEY(id))

28
Creating the Relational Database (2)

• Payment Info
• mysqlgt CREATE TABLE paymentinfo (
• mysqlgt id INT auto_increment,
• mysqlgt playerid INT,
• mysqlgt datepaid TIMESTAMP,
• mysqlgt type VARCHAR(100),
• mysqlgt amt INT,
• mysqlgt PRIMARY KEY(id))

29
Creating the Relational Database (3)

• Notes
• mysqlgt CREATE TABLE notes (
• mysqlgt id INT auto_increment,
• mysqlgt playerid INT,
• mysqlgt note VARCHAR(255),
• mysqlgt PRIMARY(id))

30
Creating the Relational Database (4)

• Rel Friends
• mysqlgt CREATE TABLE relfriends (
• mysqlgt id INT auto_increment,
• mysqlgt playerid INT,
• mysqlgt friendid INT,
• mysqlgt PRIMARY KEY(id))

31
Creating the Relational Database (5)

• Rel Enemies
• mysqlgt CREATE TABLE relenemies (
• mysqlgt id INT auto_increment,
• mysqlgt playerid INT,
• mysqlgt enemyid INT,
• mysqlgt PRIMARY KEY(id))

32
Adding Relational Data

• We simply use insert to add records into the
  above five tables

33
Other Methods of Data Input (1)

• Text File Input
• mysqlgt LOAD DATA LOCAL INFILE player.txt INTO
  TABLE playerdata

34
Other Methods of Data Input (2)

• Native Database Input
• Save the excel file as a Text (Tab delimited)
  (.txt) file

35
Backup and Restoration of Databases

• To backup the database
• c\mysql\bingt mysqldump onlinegame gt backup.sql
• To restore the database
• mysqlgt DROP DATABASE onlinegame
• mysqlgt CREATE DATABASE onlinegame
• c\mysql\bingt mysql onlinegame lt backup.sql

36
MySQL C Interface

• The C interface for MySQL is MySQL
• Free download from http//www.mysql.com/downloads/
  api-mysql.html
• Most updated release version is 1.7.1 (for VC
  6.0)
• Last release however is version 2.0.7

37
(No Transcript)
38
Example 1 Connecting and Retrieving Data from
MySQL

• include ltiostreamgt
• include ltiomanipgt
• include ltmysqlgt
• int main(void)
• // -gt Create a connection to the database
• Connection con("gamedata","193.168.0.3")
• // -gt Create a query object that is bound to our
  connection
• Query query con.query()
• // -gt Assign the query to that object
• query ltlt "SELECT FROM paymentinfo"
• // -gt Store the results from the query
• Result res query.store()
• // -gt Display the results to the console

39
Example 2 Updating Data in MySQL from an
Application (1)

• include ltiostreamgt
• include ltiomanipgt
• include ltmysqlgt
• int main(void)
• // -gt Create a connection to the database
• Connection con("gamedata","193.168.0.3")
• // -gt Create a query object that is bound to our
  connection
• Query query con.query()
• //// DISPLAY BEFORE UPDATE
• // -gt Assign the query to that object
• query ltlt "SELECT FROM paymentinfo WHERE id
  4"
• // -gt Store the results from then query
• Result res query.store()

40
Example 2 Updating Data in MySQL from an
Application (2)
ltlt setw(20) ltlt row"type" ltlt setw(20)
ltlt row"amount" ltlt endl //// UPDATE THE
INFORMATION // Send an execute an update query
in mySQL query ltlt "UPDATE paymentinfo SET type
\'American Express\' WHERE id
4" query.execute() //// DISPLAY AFTER
UPDATE // -gt Assign the query to that
object query ltlt "SELECT FROM paymentinfo WHERE
id 4" // -gt Store the results from then
query res query.store() // -gt Display the
results to the console cout ltlt endl ltlt
endl cout ltlt "After Update" ltlt endl cout ltlt
"------------" ltlt endl // -gt Show the Field
Headings cout.setf(iosleft) cout ltlt setw(6)
ltlt "id" ltlt setw(10) ltlt "playerid"
ltlt setw(20) ltlt "datepaid" ltlt setw(20) ltlt
"type" ltlt setw(20) ltlt "amount" ltlt
endl // The Result class has a read-only
Random Access Iterator for (i res.begin() i
! res.end() i) row i cout ltlt
setw(6) ltlt row"id" ltlt setw(10) ltlt
row"playerid" ltlt setw(20) ltlt
row"datepaid" ltlt setw(20) ltlt
row"type" ltlt setw(20) ltlt row"amount" ltlt
endl return 1