Resource Guide

Relational Database
Guidelines For MMOGs

Stored Procedures

The stored procedure must be central to any strategy for using a database on an MMP game. Stored procedures exhibit many desirable attributes. They are reusable and remotely callable by code with little network overhead. They are efficient because their execution plan to data is determined ahead of time, and are configurable at run time with arguments. Finally, they can return result sets from queries while hiding the underlying data structure being utilized.

Because you will be developing a lot of stored procedures, it is necessary to determine some strategies for their implementation.

The first thing to decide is to determine is how to specify the columns returned in a result set. A SELECT statement written with the shorthand '*' conveniently returns every column from each table the query. Specifying '*' when selecting from the Creature table with 2 columns, CreatureId and CreatureName, returns both columns in the result set, as shown below.

SELECT *
FROM Creature

Output:

CreatureId   Creature Name
----------   ------------------
15           Bugbear
16           King Bugbear

Using this shorthand also eliminates the need to modify the stored procedure when a third column, say HitPointAmount, gets added. Unfortunately, depending on how the code is written that issues the call to the stored procedure, you may be forced to make code changes to avoid breakage.

The alternative approach is to have the stored procedure explicitly select each column to return from the query. This makes for a little extra work when creating the stored procedure, but insulates the code when a column gets added. Of course, when the code is ready to handle the change, you have to remember to select the additional column in the stored procedure.

Stored procedures can take multiple arguments, supporting any of the data types native to the database. If your database supports specifying default values for arguments, be sure to take advantage of this feature. When a new column is added to a table, you can add the additional argument with a default value to stored procedures manipulating that table and maintain compatibility with calling code. Unfortunately, stored procedures generally do not handle arrays of types as arguments. This means that a stored procedure has to be called repeatedly to get the effect of adding multiple rows to a table.

Stored procedures support a single return value similar to functions in programming languages. While this is convenient, not all database access libraries support this feature, so be prepared to work around the limitation. The good news is that every library supports returning the results of queries and one can return the stored procedure 'result' value via a result set.

For example, given the following stored procedure snippet:

if @value = 1
begin
  return 1
end

return 0

You should be able to substitute:

if @value = 1
begin
  SELECT 1
  return
end

SELECT 0

This achieves the same net result by retrieving the return value as a result set with a single row. Naturally, if the stored procedure contains a query with a separate result set, you will have to determine if the interface library you use supports retrieving multiple result sets. The moral of this story is to discover the capabilities and limitations of your selected interface library before proceeding with heavy development on stored procedures that may end up having to be redone.

It should go without saying that if you are going to take the time develop stored procedures with return values that you should be checking the return values in the calling code to ensure that everything is running as expected.

On the topic of return values, every SQL statement will generally set an error code after it executes. Be sure to check these return codes! It may be the case that the 'problem' can be handled gracefully.

For example, suppose the data on a row needs to be updated. When the stored procedure runs, the update fails because the row does not exist. If appropriate, the specific 'does not exist' error can be trapped and handled silently with code that actually creates the row. This alleviates the need for the calling code to understand ahead of time whether the row exists or not. It always calls the combination 'update, insert if not present' stored procedure.

MS SQL Server has a quirk with its error code and other status variables. These variables only retain their values until the next statement is executed in the stored procedure, even if that statement is not a query. This requires that the error code be checked immediately, or that its value gets stored into a local variable for use later in the stored procedure. This is illustrated below:

-- Either check immediately
UPDATE TblName
WHERE ColA = 1

if @@error <> 0

-- Or save value for checking later
declare @errVal int

UPDATE TblName
WHERE ColA = 1

set @errVal = @@error

Write stored procedures to use the most efficient SQL possible. With respect to general efficiency, SQL statements can be ranked in the following order: SELECT, UPDATE/DELETE, INSERT. That is to say, that given the options of solving the same problem using one of these statement types, use the ranking is a rough guide of the order of preference.

A simple illustration: given a requirement to record the last time a player logged into a game server, the determination of which SQL statement to use might proceed as follows.

A SELECT statement can be dismissed because it is a read only operation.

The next candidate is either UPDATE or DELETE. DELETE is obviously ruled out, but UPDATE would work. A single player row could be updated each time they logged in with the current time and the requirement would be met. In addition the operation would be very efficient, since each Player row is accessible via a single column primary key, suggesting a very efficient index.

Another workable option is to INSERT a new row each time the player logs in. While this would gain us a history of every log in performed by a player, it is less efficient since INSERT operations need to establish a new row and potentially update indexes that exist for that table. If this history is not important, the UPDATE is clearly the most efficient option.

Transactions

A feature that is extremely valuable to have in your database software is transactions. Transactions provide the ability to group modifications across multiple tables into a single unit of work. When a transaction successfully completes, all of the outstanding activity is committed, or written, to the database. However if an error occurred at any time during the transaction, a rollback is issued, reinstating each table to its original state prior to the beginning of the transaction. This is best demonstrated with an example.

begin transaction

INSERT
INTO TableA
values(…)

if @@error <> 0
begin
  -- an error occurred, back out any changes to this
  -- point and exit
  rollback
  return
end

-- everything good to this point, insert the other row
INSERT
INTO TableB
values(…)

if @@error <> 0
begin
  -- an error occurred, back out any changes to this
  -- point and exit
  rollback
  return
end

-- everything is good, commit this work, which makes
-- updates to both tables permanent
commit

You should be using a transaction in every stored procedure where multiple tables are being updated. This convenient mechanism is much cleaner to read and maintain than code that tries to manually undo prior activity if an error occurs.

The MMP space has several cases where transactions are useful such as trading between players, players selling items to an NPC vendor or removing an item from a publicly accessible container.

In MS SQL Server, transactions may be nested. But be forewarned that they do not work in an obvious manner. One might expect for a nested transaction, that if that transaction's unit of work is committed, it remains committed independent of the outer transaction.

The actual implementation is that any nested transactions take on the result of the outer transaction. So if the outer transaction is rolled back, the inner transaction gets rolled back even if it was committed during execution.

Joins

The power of a relational database is realized when associating various tables together by performing joins on related columns. For example, to find all the names of creatures spawned by various spawners, we would need to join the SpawnerCreature table to the Creature table via the CreatureId column on each table.

Joins are more expensive performance wise than querying a single table, but storing creature names on the SpawnerCreature table is not only redundant, it makes changing names of creatures or adding new creatures expensive activities compared to doing the same with a normalized schema. As described elsewhere, we will tend to keep our tables normalized, but aid our overall performance in following ways:

1. Perform joins on columns that are system assigned keys with good cardinality values
2. Add an index that verifiably speeds access if a join column is not the primary key on a table.
3. Keep the number of different tables in the join down to the minimum, particular if the query will return a large number of rows from one or more tables.
4. Offload the query to server startup time when we can better afford slower than optimal performance.

There are two types of join operations available: inner and outer joins. The easiest way to distinguish between them is to ask the question if the result of the query should return a result only if there are matching rows on all tables involved in the join (inner join) or if there needs to be row is returned from the main table regardless of whether there are matches on the other tables involved (outer join).

Let's continue with our Creature and Spawner example to illustrate. If the Creature and Spawner tables are joined on CreatureId, do we need to see a row for every creature even if it has not been assigned to a spawner? If the answer is yes, then an outer join is required. If the answer is no, an inner join will suffice.

Note that there is no 'correct' answer; it depends on the dataset needed to meet our requirements. In the case of an outer join, the value NULL will appear for all columns from the joined table without one or more matching rows.

Null Value

The concept of the NULL value is worthy of its own topic because of the confusion it can create. The NULL value in a column should be read as "unknown", unlike the C programming concept where NULL has an actual value.

Any operations attempted against a NULL value will give NULL as the result. Rows with NULL values on join columns will not match each other, because NULL does not equal NULL in a comparison. NULLs cannot be allowed on any column that is part of a primary key. However, it is possible to test a column to determine if it has a NULL value, and columns that allow NULLs still honor any constraints in place when changed to a non-NULL value.

Used appropriately, NULL is quite useful. Designate a column to allow NULL if the value in that column is not always applicable. For example, if a creature may be optionally assigned a Spell, the SpellId column on the Creature table would accept NULL. If a creature currently does not have a Spell, then its SpellId column would be NULL. To return every creature without a spell, you could write the following query:

SELECT *
FROM Creature
WHERE SpellId is NULL

NULL columns are overwhelmingly preferable to synthetic "not applicable" values, which are the moral equivalent of magic numbers in code.

Referential Integrity

In my opinion, the use of referential integrity (RI) within a database is not optional. One cannot afford to let inconsistencies in data creep into the game. RI may reveal bugs in game logic or even cause code to break. But either of these is much more desirable than having errors lurk undetected until who knows when. RI can and should be used to prevent exploits by players, such as item duplication. Think of RI as an extra set of eyes that is always checking to ensure data consistency and correctness as the game runs. It is much more expensive to find out after the fact that things have gone bad, so use RI as a prevention mechanism.

Because it is essential that data be kept internally consistent, databases typically provide numerous mechanisms for implementing RI. These features usually encompass the following three major areas:

1. Foreign Key Relationships/Unique Constraints
2. Column Constraints
3. Triggers

You should take advantage of key based constraints by striving to establish a unique key for every table in the system. This prevents duplication of the key value and removes any ambiguity as to which row in a table is the 'correct' one. Every time a primary key shows up on another table, assign it a foreign key relationship with delete prevention. This way, any primary key value that shows up on a separate table will be prevented from being deleted. For example, on the CreatureSpawner table, CreatureId is a foreign key from the Creature table. With RI in place, one cannot delete a creature if it is referenced on the Spawner table.

Databases typically offer the option to cascade the delete of a key through to all its related associations. Exercise caution with this option since it works silently and you have little indication when something unintended is happening. Instead, you might find that some data has 'gone missing' because of the effects of cascading deletes.

If supported by the database, definitely take advantage of column constraint features. These allow for simple rules to be added to tables that limit the valid domain of values for individual columns. They are very easy to implement, and should be added as at table creation time. As bugs are discovered, add new constraints if they will help prevent their recurrence in the future.

To constrain the HitPointAmount column on the Creature table to the accepted range, one would add a rule to Creature table stating "HitPointAmount between 5 and 1500". The database will guarantee from that point forward that an out of range value is rejected, regardless of its source.

The last major referential integrity feature is the trigger. Triggers are snippets of code associated with a table that run when data is manipulated on the table. They are somewhat more difficult to implement than the other methods of RI. However they are the only mechanism by which referential integrity can be maintained across multiple tables when a foreign key constraint is insufficient for the task. For example, to ensure the HitPointAmount of any creature always exceeds the minimum base damage amount of any weapon in the game requires a trigger. The following is an example of the implementation of that trigger:

Create Trigger CheckCreatureVsWeapon On Creature
For Insert, Update
As
Declare @maxBaseDamage int, @hitPointAmount int

SELECT @hitPointAmount = HitPointAmount
FROM INSERTED

SELECT @maxBaseDamage = MAX(BaseDamageAmount)
FROM Weapon

If @hitPointAmount <= @maxBaseDamage
Begin
  RaiseError('HitPointAmount of creature does not exceed
              max base weapon damage')
  Rollback Transaction
End

The time spent developing triggers will pay off handsomely in overall bug prevention. Humans are prone to mistakes, and triggers are there to ensure that those mistakes are caught and don't do any harm to the game.

The last thing to be aware of is that the creation of these RI mechanisms will occur at any time in the life of a game. Be sure to take advantage of the features which validate that any existing data conforms to the new rules being added. Failure to do so will allow bad data to remain in place until a subsequent change is made to the violating value(s), which is something we obviously want to avoid.

 

______________________________________________________

In-game Logging

join | contact us | advertise | write | my profile
news | features | companies | jobs | resumes | education | product guide | projects | store



Copyright © 2003 CMP Media LLC

privacy policy | terms of service