I came up with the idea for theSystem , which I mentioned in a blog post, is my attempt to build a game engine inside MS SQL Server. I believe there will be a number of benefits to utilizing a database as the core of a game engine instead of the database being regulated to the status of data store for a game engine. I am attempting to build a game engine similar to what you find in a game like Nethack, but multi-user. (I know there are more modern examples but I want to focus on the game engine logic not the graphics) The goal is to store all games "business" logic, game functions, and data in the SQL Server engine using any and all parts of the SQL Server suit. This series is dedicated to showing my design processes and the reasons behind them as well as learning new things about SQL Server. (All of the code was written using the November CTP of SQL Server 2008 but so far everything should run in SQL Server 2005)

When I design an application I tend to layout the high level ground rules and then as the project progresses flush those rules out. When I try to execute a complete detail design from the get go without getting my hands dirty I find that it leads to massive redesigns of the details later in the project.

This is not to say I let things run wild I need rules and standard especially when working in a team environment and have multiple people creating objects that need to work within the application frame work (in this case SQL Server database). These rules cover the spectrum from specific database structuring rules and naming conventions to project goals to design philosophy. With that in mind I would like to layout some ground rules:

Schemas and Databases - I plan on limiting myself to one database "theSystem". I decided that I will separate the various logical areas of the game into different schemas within this database. This will also help with setting security permissions down the road. The schemas will be authorized by dbo for now, though we might change that. I have initially determined these schemas; dbo, metadata, land. The dbo schema will not be the only schema or the default place of residents for my objects. At this point I am not too sure what I’ll put in this schema but what goes into it will be purposely placed into this schema not just left there because it’s the default. The metadata schema will holds objects related to metadata. Metadata to me is any data about data this includes logging information, system data that is used to drive a system, and some pure statistical information (Example: ETL load stats) that you may need on hand for one reason or another. The land schema will contain all of the objects related to the game map. There will be other schemas not defined yet - I am sure there will be more to be flushed out when we get to closer to needing them. (character, objects, events)

use master</p> <p>go</p> <p>if not exists (select * from sys.databases where name = N'theSystem')</p> <p>BEGIN</p> <p> create database theSystem</p> <p> print 'database [theSystem] created'</p> <p>END</p> <p>ELSE</p> <p>BEGIN</p> <p> --drop database [theSystem] --Feel free to add this back in if you fee you need to.</p> <p> --create database theSystem</p> <p> --print 'database [theSystsem] already exists'</p> <p>END</p> <p>go</p> <p>if not exists (select * from INFORMATION_SCHEMA.SCHEMATA where schema_name='land')</p> <p>BEGIN</p> <p> USE [theSystem]</p> <p> execute ('CREATE SCHEMA [land] AUTHORIZATION [dbo]')</p> <p> print 'Schema [land] created'</p> <p>END</p> <p>ELSE</p> <p>BEGIN</p> <p> print 'Schema [land] already exists'</p> <p>END</p> <p>GO</p> <p>if not exists (select * from INFORMATION_SCHEMA.SCHEMATA where schema_name='metadata')</p> <p>BEGIN</p> <p> USE [theSystem]</p> <p> execute ('Create SCHEMA [metadata] authorization dbo')</p> <p> print 'Schema [metadata] created'</p> <p>END</p> <p>ELSE</p> <p>BEGIN</p> <p> print 'Schema land already exists'</p> <p>END

Game Security- I plan on using native SQL Server security, it seems 50% of third part SQL Server based products that I come across want to implement their own security scheme and then uses a single account for SQL Server login. I don’t like this architecture; some argue that it allows for the application to be database agnostic which is something else I don’t think is a good design decision. (Enough ranting). This means that each user will get their own native login. This could lead to a situation where there are tens of thousands of users logged in using individual accounts. This could lead to some performance issues but it will make logging and auditing a whole lot easier. (Maybe I will learn why these third party systems like to use a single log in)
Game Logic- Game logic to be held in SQL server objects, at this point I am going to try to use t-sql based functions and procedures to hold all logic, but I am allowing myself the use of CLR integration if and when necessary. Procedures and Functions will be programmed to have a large degree of encapsulation and modularity. (I don’t really think I can pull off Inheritance, polymorphism, abstraction, or creating an object instance so we are not striving for some sort of T-SQL OO)
Object Access- All user objects will be exposed through the SQL Server engine. This means that user should be able to access all required objects through an OLE/ODBC/JDBC connection. If and when time permits these objects will be exposed using web services. (Utilizing SQL Server end points and IIS)
Game Play- Game play should be possible through exposed objects. Users should be able to make a OLE/ODBC/JDBC connection or use XML interfaces to play the game. There will be no logic in the user interface; all game play possible through the GUI is based on T-SQL commands. Map generation will be automatic and happen as plays goes towards an existing edge of the map. The map generation must automate not only tile generation on the map but all aspects of game play including adding story lines to the game.
Standards All objects will start with lower case letters. Top level objects (Tables, functions, procedures ect..) will be named with no spaces separating words and each new word starting with a capital letter. Example: tableName Column names will be separated with an under bar and only use lower case letters. Example: column_name Collation - SQL Server will not be set to a binary collation. Index creation is not something I do up front. I hold off until I have enough of a system to see where I need them. Default indexes created by primary keys will occur. No rules other then stick indexes in a different file group from the main file group.
General Rules The most import rule, rules can be broken but there must be a good reason. I sometimes get ahead of myself and forget my own rules. (I know I have broken my standards in this first post!). My second most important rule, Keep an open mind. Finally more of an inevitability than a rule, that these standards will change.

Let’s jump into a design/coding session. The first few objects we are going to tackle are going to be in the metadata and land schema areas. We are going to go over five tables and two stored proceudures, I'll try to explain indetail what they will be used for and why. The five tables are, metadata.logging, land.map, land.mapType, land.landtypes, and land.landTypeExistanceMod. (schema.tableName)

Metadata.logging this is a table that holds metadata about our current debug status, this information is held on a schema level and allows us to turn debugging on and off in our code without having change the way we call a given procedure. I like the idea of being able to turn off logging without having to change anything in the system itself. I will pay a performance penalty for even having this logging possible and a bigger one when I turn it on but I am willing to pay that price for being able to trouble shoot my application easily. This table has two columns, the first Schema_name its data type sysname chosen because I took a guess that a SQL Server schema could not have description longer than the system type of sysname. The second column is Debug its data type is INT because I didn’t want be limited by a bit. I could have chosen to use a small int and I may change this later.

Land.map this is the core table for the game map. Each row represents a tile on the game map. Currently that is a three dimensional cube. I also added in a map type column so that the table could hold multiple maps, maybe a high level map – map, a city map – city. I decided to define my own tile instead of using the geospatial name space provided by SQL 2008 for two main reasons. I wanted to be able to have this code accessible to a large audience and I wanted to flush out my ideas prior to making the leap into learning a new data type and all of its functions. The map table has 6 columns map_ident this is the surrogate primary key to the table. The natural primary composite key of x-axis, y-axis, z-axis, and map type would have been 4 integers which is too two large of a key for my taste. What I mean by too large I am referring to the fact that SQL Server uses the primary key on the leaf levels on non clustered indexes as a pointer to the record. Having 4 integers would be a 8 byte read/write vs. a 2 byte read/write operation. (I am pretty sure this is how non clustered indexes works but if I’m wrong please correct me.) I have instead implemented a unique constraint over the natural key. land_ident column this is a foreign key to the land.landTypes table. This data represents generic characteristics of the particular tile. (is this a forest, road, ocean, mountain tile). The coordinate columns these are integers and represent points on a cube. I could have used any data type but integer lended itself nicely to what I am doing which is marking points on a cube. This may be changed to a BIGINT if I need to but for now INT is sufficient. Finally the map_type column which is a foreign key to the mapType table and defines the mapType for a given row. Currently I only have one map_type defined -"map" .

Land.mapType this table is used to define different types of maps that are stored in the land.map table. Currently I only have a need for one type of map but I do believe this will change quickly. This may also have a secondary use in defining values in the land.landTypes table but that has not been flushed out as of yet. This table has two columns map_type_ident and map_type_desc. Map_type_ident is the primary key that represents a single map type value. Map_type_desc is the description of the map type. Currently only one exists "map".

Land.landtypes this table defines the different land types. Currently there is no attribute to dictate which landType goes with which mapType. I think I will have a need for this in the future but have not done anything about it other than some light thinking. This table has five columns, land_ident and land_desc which are self explanatory in their function. Then it has Base_incounter_percentage which is a numeric(10,10) value that represents a percentage point or fraction of that will be used to calculate the if a user has an "random" encounter of some sort in the game. As I plan to pre-plant encounters on the map and have jobs that add encounters random may not be the correct way to describe the encounter. I chose a numeric (10,10) field because I felt that it gave me enough depth in the percentage to make things very rare to 100% change of happening. Base_special_incounter_percentage column which is same as base_incounter_percentage but for special events. Finally the Base_existance_percentage column which providesthe chance of a particular land type to exist. This will be used in conjunction with the landTypeExistanceMod table to automate map generation. The idea here is we can turn the game engine loose to create as big of a map as it needs to, increasing itself as users get close the map edge and we don’t have to worry about the game engine creating a few tiles of ocean in the middle of the plains tiles.

Land.landTypeExistanceMod This is a modification table and pits one land type against another to give an increase or decrease in the percentage chance of the new land type of exist next to the current land type. There are three columns in this table land_ident, new_land_ident both of which are related to the land_ident in the landtypes table above. The third column Modification_existance_percentage is very similar to the base_existance_percentage but it is modifiers to this value that will be used to calculate the entire chance of a give tile existing taking into consideration all surrounding tiles.
USE [theSystem]</p> <p>go</p> <p>create table land.landTypes (</p> <p>land_ident int identity(1,1) primary key</p> <p>,land_desc varchar (50) constraint un_landTypes_01 unique</p> <p>,base_incounter_percentage decimal(10,10) not null</p> <p>,base_special_incounter_percentage decimal(10,10) not null</p> <p>,base_existance_percentage decimal(10,10) not null</p> <p>)</p> <p>go</p> <p>create table land.mapType</p> <p>(</p> <p>map_type_ident int identity(1,1) primary key</p> <p>,map_type_desc varchar(50) constraint un_mapType_01 unique</p> <p>)</p> <p>go</p> <p>create table land.map (</p> <p>map_ident int identity(1,1)</p> <p>,land_ident int constraint fk_map_01 foreign key references land.landTypes (land_ident)</p> <p>,x_axis int</p> <p>,y_axis int</p> <p>,z_axis int default(0)</p> <p>,map_type int constraint fk_map_02 foreign key references land.mapType(map_type_ident)</p> <p>,constraint un_map_01 unique (x_axis,y_axis,z_axis,map_type)</p> <p>)</p> <p>go</p> <p>create table land.landTypeExistanceMod</p> <p>(</p> <p>land_ident int not null</p> <p>constraint fk_landTypeExistanceMod_01 foreign key references land.landTypes(land_ident)</p> <p>,new_land_ident int not null</p> <p>constraint fk_landTypeExistanceMod_02 foreign key references land.landTypes(land_ident)</p> <p>,modification_existance_percentage decimal(10,10)</p> <p>,constraint pk_landTypeExistanceMod primary key (land_ident,new_land_ident)</p> <p>)
Now onto the two procedures, spaceBuilder and showMap, both of these reside in the land schema.

spaceBuilder is a building block procedure that builds one tile at a time and has no logic to check to see if that tile already exists. It is a generic space/tile builder that can puts a record into the land.map table taking into account the surrounding tiles. This is built to be as modular as possible so if I need build a map from a number of future procedures I have the core logic locked up in one procedure instead of imbedding it each time I need it and ending up with slight variations of code over time. We will be reusing this in a number of future procedures.

showMap is a procedure that returns a 10x n number of records record set. It gives a text description of a section of the map to the end user. Currently it only shows the tiles and nothing else. This will be expanded as the game is developed. It is a core piece of the game so I figured I would build it early on so everyone can see how it will develop. It also gives me a good tool to see if the land.spaceBuilder tool is working correctly and creating tiles in a fashion that I would deem logical. I used a pivot function wrapped into a EXECUTE SQL statement so I could dynamically define the column headers.
go</p> <p>create procedure land.spaceBuilder</p> <p>@max_x_axis int</p> <p>,@max_y_axis int</p> <p>,@debug bit = 0</p> <p>,@map_type int = 1</p> <p>------------------------------------------------------------------</p> <p>--Created by:Steve Fibich</p> <p>--Created on: 2007.11.28</p> <p>--Version:1.0</p> <p>--Notes: this procedure creates one space in the land.map table. A space is</p> <p>--one unique x-axis, y-axis, z-axis combination</p> <p>--</p> <p>------------------------------------------------------------------</p> <p>as</p> <p>begin</p> <p>set nocount on</p> <p>------------------------------</p> <p>--Check global debug status --</p> <p>------------------------------</p> <p>if @debug=0</p> <p>BEGIN</p> <p> select @debug=debug from metadata.logging where schema_name='land'</p> <p>END</p> <p>-------------------</p> <p>--Proc Variables --</p> <p>-------------------</p> <p>declare @land_ident int</p> <p>declare @rand float</p> <p>declare @percentage float</p> <p>declare @ending_percentage numeric(11,10)</p> <p>declare @landBuilder as table(</p> <p>land_ident int primary key</p> <p>,starting_percentage numeric(11,10)</p> <p>,ending_percentage numeric(11,10)</p> <p>)</p> <p>declare @landBuilder_mep as table(</p> <p>new_land_ident int primary key</p> <p>,modification_existance_percentage numeric(11,10)</p> <p>)</p> <p>------------</p> <p>--defaults--</p> <p>------------</p> <p>set @land_ident =0</p> <p>set @ending_Percentage=0</p> <p>delete @landBuilder_mep</p> <p>delete @landBuilder</p> <p>--------</p> <p>--Code--</p> <p>--------</p> <p>insert into @landBuilder_mep</p> <p>select</p> <p>new_land_ident</p> <p>,sum(modification_existance_percentage)modification_existance_percentage</p> <p>from</p> <p>land.map m</p> <p>inner join</p> <p>land.landTypeExistanceMod l</p> <p>on</p> <p>m.land_ident=l.land_ident</p> <p>where</p> <p>x_axis between @max_x_axis-1 and @max_x_axis+1</p> <p>and</p> <p>y_axis between @max_y_axis-1 and @max_y_axis+1</p> <p>group by</p> <p>new_land_ident</p> <p>order by</p> <p>new_land_ident</p> <p>---------</p> <p>--debug--</p> <p>---------</p> <p>if @debug=1</p> <p>BEGIN</p> <p> select</p> <p> *</p> <p> from @landBuilder_mep</p> <p>END</p> <p>--Fill the temp table with landtypes and the percentage range for when they will exist</p> <p>--Currently no modifiers outside of making the percantage cover the entire range from 0 to 1</p> <p>--This is where land modifiers will be added in later</p> <p>set @land_ident=0</p> <p>while (select count(*) from land.landTypes where land_ident>=@land_ident) > 0</p> <p>begin</p> <p> insert into @landBuilder</p> <p> (land_ident,starting_percentage,ending_percentage)</p> <p> select</p> <p> l.land_ident</p> <p> ,case</p> <p> when l.land_ident=1 then 0</p> <p> else coalesce(@ending_percentage+.0000000001,0)</p> <p> end</p> <p> ,case</p> <p> when (coalesce(@ending_percentage,0)+coalesce(lm.modification_existance_percentage,0)+.0000000001+l.base_existance_percentage) > @ending_percentage</p> <p> then coalesce(@ending_percentage,0)+.0000000001+coalesce(lm.modification_existance_percentage,0)+l.base_existance_percentage</p> <p> else NULL</p> <p> end</p> <p> from</p> <p> land.landTypes l</p> <p> left outer join</p> <p> @landbuilder lb</p> <p> on</p> <p> l.land_ident=lb.land_ident+1</p> <p> left outer join</p> <p> @landBuilder_mep lm</p> <p> on</p> <p> l.land_ident=lm.new_land_ident</p> <p> where</p> <p> l.land_ident=@land_ident</p> <p> if (select ending_percentage from @landBuilder where land_ident=@land_ident) is not null</p> <p> BEGIN</p> <p> select @ending_Percentage=ending_percentage</p> <p> from @landBuilder</p> <p> where land_ident=@land_ident</p> <p> END</p> <p> set @land_ident=@land_ident+1</p> <p>end</p> <p>set @rand=rand()</p> <p>select @percentage=1/max(ending_percentage)</p> <p>from</p> <p>@landBuilder</p> <p>---------</p> <p>--debug--</p> <p>---------</p> <p>if @debug=1</p> <p>BEGIN</p> <p> select @percentage percentage</p> <p> select *</p> <p> from</p> <p> @landBuilder</p> <p> select @max_x_axis x_axis, @max_y_axis y_axis</p> <p>END</p> <p>insert into land.map</p> <p>(land_ident,x_axis,y_axis,z_axis,map_type)</p> <p>select</p> <p>coalesce(land_ident,1)</p> <p>,@max_x_axis</p> <p>,@max_y_axis</p> <p>,0</p> <p>,1</p> <p>from</p> <p>@landBuilder</p> <p>where</p> <p>@rand between cast(starting_percentage as float) * @percentage and cast(ending_percentage as float)*@percentage</p> <p>end</p> <p>go</p> <p>USE [theSystem]</p> <p>GO</p> <p>/****** Object: StoredProcedure [land].[landBuilder] Script Date: 11/28/2007 13:40:27 ******/</p> <p>IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[land].[showmap]') AND type in (N'P', N'PC'))</p> <p>DROP PROCEDURE [land].[showmap]</p> <p>go</p> <p>create proc land.showmap</p> <p>@x_axis int =5</p> <p>,@y_axis int=5</p> <p>,@debug bit = 0</p> <p>as</p> <p>------------------------------------------------------------------</p> <p>--Created by:Steve Fibich</p> <p>--Created on: 2007.11.28</p> <p>--Version:1.0</p> <p>--Notes:</p> <p>--</p> <p>------------------------------------------------------------------</p> <p>declare @sqlString nvarchar(1000)</p> <p>set @sqlString='select y_axis</p> <p>,['+cast(@x_axis-4 as nvarchar(10))+'],['+cast(@x_axis-3 as nvarchar(10))+'],['+cast(@x_axis-2 as nvarchar(10))+'],['+cast(@x_axis-1 as nvarchar(10))+'],['+cast(@x_axis as nvarchar(10))+'],['+cast(@x_axis+1 as nvarchar(10))+'],['+cast(@x_axis+2 as nvarchar(10))+'],['+cast(@x_axis+3 as nvarchar(10))+'],['+cast(@x_axis+4 as nvarchar(10))+']</p> <p>from</p> <p>(</p> <p> select</p> <p> m.x_axis</p> <p> ,m.y_axis</p> <p> ,land_desc</p> <p> from</p> <p> land.map m</p> <p> inner join</p> <p> land.landTypes lt</p> <p> on</p> <p> m.land_ident=lt.land_ident</p> <p> where</p> <p> m.y_axis between '+cast(@y_axis-4 as nvarchar(10))+' and '+cast(@y_axis+4 as nvarchar(10))+'</p> <p>) as sourceTable</p> <p>pivot</p> <p>(</p> <p>max(land_desc)</p> <p>FOR x_axis in (</p> <p>['+cast(@x_axis-4 as nvarchar(10))+'],['+cast(@x_axis-3 as nvarchar(10))+'],['+cast(@x_axis-2 as nvarchar(10))+'],['+cast(@x_axis-1 as nvarchar(10))+'],['+cast(@x_axis as nvarchar(10))+'],['+cast(@x_axis+1 as nvarchar(10))+'],['+cast(@x_axis+2 as nvarchar(10))+'],['+cast(@x_axis+3 as nvarchar(10))+'],['+cast(@x_axis+4 as nvarchar(10))+']</p> <p>)</p> <p>)as mapTable '</p> <p>if @debug=1</p> <p> BEGIN</p> <p> select @sqlString</p> <p> END</p> <p>execute (@sqlString)</p> <p>go

Well that’s it for this installment of the system design for the “theSystem”. Please let me know if you have any questions about the design or feel free to comment on the design. This is an exercise in design and exploring SQL Server so I am certain there are improvements that could be made or different ways to look at accomplishing these goals.