PDA

View Full Version : A PHP Database Class


Merlin
12-30-2009, 12:28 AM
PHP Database Class
This is more like a "business rules" layer that sits between the procedures that use unit data, and the low-level database class that operates on the MySQL database directly. Because it is designed for PHP 4, none of the data is protected from direct manipulation, but I'll be careful how I use it.

Background
Under the current system, which is similar to almost every other site built on PHP and MySQL, the database is used to keep track of data that must persist between sessions or visits. One place where this really hurts performance is the battle system. During a turn, there will be a lot of “small” queries to check things such as equipment, effects, move rates, to add or remove effects, and so forth.

Solution
What is needed is a method of loading the required data at the beginning of a battle, using the in-memory copy for checking effects, status, and other stats, and then saving the modified unit data in a single batch when the turn is complete. This should reduce the number of queries significantly, probably by a factor of 10 to 100 times. The load on the CPU will be about the same, but because there will be no lag time waiting for the database server, everything should be much, much faster.

Implementation Notes
Need to consider three types of data manipulations: Insert to add a new row, Update to change existing data and Delete to remove a row. Each row, when changed, will be marked with an update flag, which will indicate which rows need to be inserted, updated or deleted. Because each table has a primary key, the update can be done using the extended MySQL Replace syntax, which allows multiple lists of values, and the delete can list the keys to be deleted in an “in (delete list)”, reducing the number of statements sent to the server.

One difficulty is with Insert, as the “delayed” save means that the actual Inserted Id for auto_number fields will not be known until later. The solution is to use negative Ids that are generated sequentially down from -1. A lookup table will keep track of the negative ids and the actual insert id, which will be replaced at the time of the save in related columns. Note that this implies that Inserts cannot be “batched” like the other operations, as the insert id needs to be retrieved after each insert.

To minimize the amount of tricky code that this requires, the table design will be simplified. Instead of an auto_increment for unit combat data, for instance, the unit_id will be used. To improve the efficiency of Updates (which will be most common), the large unit table will be broken up into sections that are likely to be updated without affecting other sections. So the ao_unit table will be broken up into several smaller, related tables: ao_unit (name, race, class, icon, mount_id), ao_unit_stats (hits, moral, stamina), ao_unit_world (position and action), ao_unit_region (position and action) and ao_unit_tactical (position and action). This will also reduce the size of data, as mounts don’t need their own world, region and tactical data.

Merlin
12-30-2009, 12:34 AM
Snippets

Note that this class is under construction. I'll revise and add to snippets as they evolve.

Class definition:

class Unit {


var $units = array();

var $unit_id_array = array();
var $unit_id_list = "0";

var $mount_id_array = array();
var $mount_id_list = "0";

var $mount_unit_lookup = array(); // mount_id -> unit_id lookup

var $unit_insert_id = 0; // Index for newly created units
var $sub_insert_id = 0;

var $unit_insert_xlat = array(); // Track negative ids and db insert ids

var $columns = array();
var $tables = array(
"ao_unit","ao_unit_effect","ao_unit_equip","ao_unit_formation","ao_unit_spell",
"ao_data","ao_cost","ao_defense","ao_combat","ao_spell_lv");

var $combat_data = 0;

/* Use $update to indicate the status of this data:
* 0: Unchanged, 1: Modified, 2: Deleted
* Array index is unit_id, with same structure as $units table
*/
var $update = array();

/**
* Get the column names for each of the tables
* Needed by build_replace
*/
function Unit() {
global $db;

foreach ($this->tables as $table) {
$sql = "SHOW COLUMNS FROM " . $table;
$result = $db->query($sql);

while ($row = $db->fetch_array($result)) {
$this->columns[$table][] = $row;
}
$db->free_result($result);
}
}
Save unit_data:

/**
* Save unit data
*/
function _save_unit_data($section, $table, $key) {
global $db;

// Save the spell data, if dirty
$replace_data = array();
$delete_data = array();

foreach ($this->units as $unit_id => $unit) {

$flag = $this->update[$unit_id][$section];
if ($flag == 1) {
$replace_data[] = $unit[$section];
} elseif ($flag == 2) {
$delete_data[] = $unit_id;
}
}

// Build replace and delete queries and run them
if (count($replace_data)) {

$sql_replace = $this->_build_replace($table, $replace_data);
echo $sql_replace . "<br />";
// $db->query($sql_replace);
}

if (count($delete_data)) {

$sql_delete = $this->_build_delete($table,$key, $delete_data);
echo $sql_delete . "<br />";
// $db->query($sql_delete);
}
}
Some helper functions to build queries:

/**
* Helper function to build INSERT queries
*/
function _build_insert($table_name, & $insert_data) {

// Array of field names
$fields = array();
$types = array();

foreach ($this->columns[$table_name] as $col) {

// Leave out auto_increment columns
if ($col['Extra'] != 'auto_increment') {
$fields[] = $col['Field'];
$typ = $col['Type'];
if (substr($typ,0,4) == 'char'
|| substr($typ,0,4) == 'date'
|| substr($typ,0,4) == 'enum'
|| substr($typ,0,7) == 'varchar')
$types[] = "s";
else
$types[] = "n";
}
}

// Array of values bits
$values = array();
foreach ($insert_data as $d) {
$temp = array();
foreach ($fields as $i => $col) {
$typ = $types[$i];
$data = $d[$fields[$i]];
if (! isset($data))
$temp[] = "DEFAULT";
elseif ($typ == "s")
$temp[] = "'".addslashes($data)."'";
else
$temp[] = $data;
}
$values = "(" . implode(",", $temp) . ")";

// Build the queries
$sql_insert[] = "INSERT INTO " . $table_name .
" (" . implode(",", $fields) . ") VALUES " .
$values . ";";
}

return $sql_insert;
}

/**
* Helper function to build REPLACE queries
*/
function _build_replace($table_name, & $replace_data) {

// Array of field names
$fields = array();
$types = array();

foreach ($this->columns[$table_name] as $col) {

$fields[] = $col['Field'];
$typ = $col['Type'];
if (substr($typ,0,4) == 'char'
|| substr($typ,0,4) == 'date'
|| substr($typ,0,4) == 'enum'
|| substr($typ,0,7) == 'varchar')
$types[] = "s";
else
$types[] = "n";
}

// Array of values bits
$values = array();
foreach ($replace_data as $d) {
$temp = array();
foreach ($fields as $i => $col) {
$typ = $types[$i];
$data = $d[$fields[$i]];
if (! isset($data))
$temp[] = "DEFAULT";
elseif ($typ == "s")
$temp[] = "'".addslashes($data)."'";
else
$temp[] = $data;
}
$values[] = "(" . implode(",", $temp) . ")";
}

// Build the query
$sql_replace = "REPLACE INTO " . $table_name .
" (" . implode(",", $fields) . ") VALUES " .
implode(", ", $values) . ";";

return $sql_replace;
}

/**
* Helper functions to build DELETE queries
*/
function _build_delete($table_name,$id_name, & $delete_data) {

// Build the query
$sql_delete = "DELETE FROM " .$table_name . " WHERE " . $id_name .
" IN (" . implode(",", $delete_data) . ");";

return $sql_delete;
}

// Used for ao_unit_equip
function _build_delete_unit_id($table_name,$unit_id,$id_nam e, & $delete_data) {

// Build the query
$sql_delete = "DELETE FROM " .$table_name . " WHERE unit_id = '" . $unit_id . "'" .
" AND " . $id_name . " IN (" . implode(",", $delete_data) . ");";

return $sql_delete;
}

// Used for ao_combat and ao_cost
function _build_delete_type_id($table_name,$type_id,$id_nam e, & $delete_data) {

// Build the query
$sql_delete = "DELETE FROM " .$table_name . " WHERE type_id = '" . $type_id . "'" .
" AND " . $id_name . " IN (" . implode(",", $delete_data) . ");";

return $sql_delete;
}

Merlin
12-30-2009, 02:20 AM
I'm going to treat this as a kind of development blog, as this "little" project is going to take a significant amount of time, effort and thought to get right. One problem I will have is developing an efficient way of getting data out of the array to duplicate the results I get with SQL queries. For instance, I group all the attacks on a particular hex together like this:

// Build a list of unique destination hexes
$sql = "select tactical_dest_x,tactical_dest_y,tactical_action " .
"from ao_unit " .
"where" . where_world_region($position) .
"and player_id = '$player_id' and tactical_action in (".ACTION_ATTACK.",".ACTION_CHARGE.") ".
"and tactical_clock <= $clock " .
"group by tactical_dest_x,tactical_dest_y,tactical_action";
I'll probably build some functions that iterate through the array and create an array with the same structure as the SQL results.

Moriority
01-05-2010, 08:25 PM
It's hard to fathom the amount of data flow that goes no during just one turn of AO. Especially a Moriority sized battle. Perhapes 50 or 60 seperate units sending data back and forth through cyber-space every turn. (That's why "Please wait..." tries the patience of 21st Century man.

Russ's solutions to sound good to the us technical novices. We can help though by measuring the effectiveness of the changes Russ is working on. I have a stop-watch and a pad of paper. I'll record the "Please wait..." times as well as the size of the armies engaged.

To compare, I'll need to know when the changes will be made, and when they are complete.

Kurt

Moriority
01-06-2010, 12:06 AM
This large of a battle produced this result. It's a lot of units.
Turn #1 4:05
Turn #2 3:58
Turn #3 3:50
Turn #4 Whoops
Turn #5 3:30
Turn #6 2:25
Turn #7 2:15
Turn #8 2:30
Orc Militia City 25 2,381 Attacker 33 1
Kebub: Knight 13 hp:-10/95 None, Xurek: Warrior 9 hp:30/96 Withdraw, Ohomkug: Knight 9 hp:-10/78 None, Spathu: Mage 7 hp:-10/32 None, Viggu: Knight 6 hp:-7/54 None, Mudagut: Mage 4 hp:20/20 Withdraw, Infantry: 30 Infantry 3 hp:360/360 Move To (16, 0), Cavalry: 20 Cavalry 5 hp:85/320 Withdraw, Mtd. Infantry: 20 Mtd. Infantry 6 hp:350/360 Withdraw, Infantry: 30 Infantry 4 hp:-5/420 None, Infantry: 30 Infantry 3 hp:0/360 None, Bowmen: 25 Bowmen 4 hp:0/275 None, Mtd. Infantry: 20 Mtd. Infantry 4 hp:280/280 Withdraw, Mtd. Infantry: 20 Mtd. Infantry 2 hp:136/200 Withdraw, Cavalry: 20 Cavalry 3 hp:36/240 Withdraw, Bowmen: 25 Bowmen 4 hp:275/275 Withdraw, Bowmen: 25 Bowmen 1 hp:-56/200 None, Mtd. Infantry: 20 Mtd. Infantry 2 hp:-6/200 None, Bowmen: 25 Bowmen 1 hp:0/200 None, Infantry: 30 Infantry 5 hp:480/480 Withdraw, Cavalry: 20 Cavalry 2 hp:76/200 Withdraw, Infantry: 30 Infantry 3 hp:0/360 None, Mtd. Infantry: 20 Mtd. Infantry 3 hp:240/240 Withdraw, Mtd. Infantry: 20 Mtd. Infantry 1 hp:160/160 Withdraw, Infantry: 30 Infantry 1 hp:-5/240 None, Bowmen: 25 Bowmen 2 hp:9/225 Withdraw, Infantry: 30 Infantry 3 hp:13/360 Withdraw, Cavalry: 20 Cavalry 2 hp:172/200 Withdraw, Bowmen: 25 Bowmen 1 hp:40/200 Withdraw, Mtd. Infantry: 20 Mtd. Infantry 1 hp:153/160 Withdraw, Infantry: 30 Infantry 2 hp:300/300 Withdraw, Bowmen: 25 Bowmen 2 hp:225/225 Withdraw, Bowmen: 25 Bowmen 1 hp:0/200 None, Bowmen: 25 Bowmen 2 hp:225/225 Withdraw, Bowmen: 25 Bowmen 1 hp:200/200 Withdraw, Infantry: 30 Infantry 2 hp:10/300 Withdraw, Purbog: Mage 8 hp:0/36 None, Baugh: Mage 7 hp:-10/32 None, Nulgha: Warrior 8 hp:0/86 None, Haguk: Priest 7 hp:-10/44 None, Somoku: Knight 5 hp:8/46 Withdraw, Cavalry: 20 Cavalry 3 hp:11/240 Withdraw, Mtd. Infantry: 20 Mtd. Infantry 5 hp:-13/320 None, Bowmen: 25 Bowmen 2 hp:143/225 Withdraw, Infantry: 30 Infantry 3 hp:360/360 Withdraw, Infantry: 30 Infantry 2 hp:0/300 None, Infantry: 30 Infantry 3 hp:-7/360 None, Bowmen: 25 Bowmen 2 hp:192/225 Withdraw, Cavalry: 20 Cavalry 4 hp:-158/280 None, Mtd. Infantry: 20 Mtd. Infantry 5 hp:0/320 None

(38,42)-(14,11) Moriority Human Knight City 25 2,456 Defender 82 0
Yoemen of the M: 35 Bowmen 7 hp:392/420 None, 2nd Pikes: 32 Infantry 7 hp:490/576 None, Missana: Mage 11 hp:43/43 None, 2nd Bows: 35 Bowmen 7 hp:416/420 None, 3rd Axemen: 30 Infantry 7 hp:528/540 None, 4th Foot: 35 Infantry 7 hp:489/630 None, Prizm: Priest 11 hp:44/62 Cast Heal, Pan: Priest 11 hp:62/62 Cast Cure, Fifth Pikes: 35 Infantry 7 hp:594/630 None, 3rd X-Bows: 30 Bowmen 7 hp:321/360 None, Shields: 35 Infantry 7 hp:570/630 Move To (11, 10), Pikes: 35 Infantry 7 hp:563/630 None, Weman: Warrior 11 hp:98/109 None, Rill: Ranger 11 hp:85/85 None, Winnie: Warrior 9 hp:83/94 None, 1 Militia: 30 Militia 4 hp:263/270 None, 2 Militia: 30 Militia 4 hp:94/270 None, 3 Militia: 30 Militia 4 hp:128/270 None, 4 Militia: 30 Militia 4 hp:93/270 None, 5 Militia: 30 Militia 4 hp:-2/270 None, 6 Militia: 30 Militia 4 hp:59/270 None, 7 Militia: 30 Militia 4 hp:14/270 None

Merlin
01-06-2010, 12:17 AM
My hope is that the turn time will be so much less that you'll notice it without needing the stopwatch. Still, a numeric value will be nice - even if it's a rough measure.

Merlin
01-08-2010, 04:57 PM
Progress update: The unit-database class is now working with the action menu and update system. Next I tackle the battle system. Once that is done and tested, I'll deploy it and we'll see how the performance works out. Expect about a week for coding and testing.

Merlin
01-12-2010, 02:28 AM
Based on preliminary comparisons - there is a lot of stuff that isn't working properly yet - it looks like the speed of battles will be about 10 times faster than it is now. So, your 5 minute turn length will turn into 30 seconds. (There may still be some "internet" lags that add to that, but I anticipate pretty spectacular results!)

Merlin
01-18-2010, 12:21 AM
The class was implemented on Saturday the 16th. There were several places where, because I have only "half" integrated this into the game, I had to do some quick workarounds for glitches, but everything is going well. I'll be converting more of the system to use the Unit class as time goes on, but for now, the battle system uses the class exclusively. Let's see how the timings work out now.

Merlin
02-12-2010, 07:25 PM
So, about a month later, I've fixed most of the big and little problems that appeared with this huge, fundamental change in the battle game. The results are fairly spectacular as far as speed goes. The load on the database is significantly lessened, so the game should easily scale to hundreds of active players, as long as they aren't ALL fighting battles at the same time!

wilomakete
11-29-2013, 12:16 PM
nice all post.