With this newest installment of "theSystem" I'm introducing a large number of new objects and I'm taking a minute to re-release the code from articles I and II. First off is a refresher of the code from theSystem I and II. There is nothing new here and this code is being provided to correct any errors in the initial release. I've tried to handle any and all cases of the code that may be loaded to someones system if they have been following the series. The only difference is that I have been tweaking the creation percentage values in the land tables.
That being said the code discussed in this article will function if you are following the series from its beginning or just starting with this article. Lets go over all of the objects we will be covering so I don't forget and so you know what your getting yourself into. First off we have two new schemas in this release test and dice. Following that we will be going over two new additions to the sys.messages table. We have not added any records to this table in past articles but this is a great system supplied object I plan on taking full advantage of in the future. Next up we will be taking a quick look into the two metadata entries into metadata.logging table for the test and dice schemas . We will then jump into the three new tables introduced in this article one in the test schema dice, and two in the land schema currentMapIdent and movementTracker. Finally we will wrap things up with a dive into twelve new or modified procedures which makes up the bulk of this release.
I and II:
So lets dig into this releases objects starting with two new schema's test and dice and the reasons for creating them. I'm not just putting all my objects into dbo for particular reasons. Dividing my objects across schema makes it easier for me to logically divide functionality into distinct units. This may not be a big deal when there is only one database developer but it comes in handy when you are working on a team.
Secondly it allows me to search for objects with in a schema by knowing what the object does but maybe not its name. I realize this may not seem like a very handy thing to have when all your objects are in say sys.tables but I find myself knowing what I'm looking for but not the exact name. So then if I'm looking for a table dealing with land types I only have to search sys.tables where schema_id = (select schema_id from sys.schemas where schema name ='land') Once again you may say I can just look through sys.tables or SSMS object explorer and find what I need much faster and keep everything in the dbo schema then run a query. You could and I wouldn't disagree with you.....as long as you only have 100 or so tables, procedures, or functions in the are you are looking through. Also if you divide objects across schemas SSMS object explorer will list your objects accordingly as well.
Once you start down the path of building integrated database system where everything is or almost is in the database using tables and there constructs (primary keys, unique constraints, foreign keys, triggers, defaults, check constraints ect..) to enforce data integrity and using procedures, functions, views, and triggers to enforce business logic, a large number of objects makes it a little cumbersome to search through only one schema. Yes,I said business logic in the database using the database as its own data access layer enforcing access through objects to control that all views of the data are presented in a uniformed manner for a specific purpose.
Finally and I don't know if this is really a great reason because it can be handled in other ways it makes securing logical areas of the database very easy. You don't want users to have access to the system metadata no problem restrict that schema, you want users to have access to all objects in the player schema simply give them access to it all. So on to the schema explanation, Test is a schema that exist just for that test objects that may be useful in a production environment. This is different then having a development, testing/QA, production environment for code promotion. This schema exists so you can run test scenario's using production data in a production environment but do not modify the production data itself.
For example , you have a new land generation procedure but want to execute it in the production environment using production data but not impact the production land.map table you could have a procedure written and dumped into the test schema for this purpose. Or you may have a piece of production code that you want to put through its paces for stress testing on the production system but you do not want to fill a production table with test data, the test schema allows for an area to dump this data to.
I am still advocating a separate Development, Test, and production environment, but in some instances its necessary to test your code in a semi production environment and this schema allows for the storage of these types of objects. The Dice schema will hold objects related controlled random number generation. I say controlled because I have a need of generating numbers in certain number ranges and I will need to implement this over and over again and instead risking different developers or event the same developer doing this different ways different times encapsulating this code is a stored procedure seems to be a wiser move. As an example is generating random numbers 1 thru 6 will be a common piece of code that I need throughout this gaming system and I can think of a two ways off the top of my head that produce numbers between 1 thru 6 with different weights for the outcome of a given number. Example pseudo-code: floor(rand()*6)+1 if 7 then 6, abs(ceiling(rand()*6)-1)+1, and I'm sure there are more.
Schemas:
We are adding two entries into the sys.messages table. If you are unfamiliar with this table it is the system table that the raiseError T-SQL function pulls its string messages from. This table allows you to add messages formatted in a C printf type syntax so that specific values can be inserted at run-time for specific error messages. We currently have two error messages that may need to be called from a few different places so I would like to have a single place to manage the details of the message.
Once the message is inserted into this table and if you view the parameters list as sort of an interface you can change this message anytime as long as you keep the parameters the same. This interface type of functionallity can be very useful if you decide you would like enhance the error description later, the change would be limited to single update statement in a table vs updating a number of procedures. The two messages we are adding are for missing character id on a given map table, and a message for the non valid direction command. Using raiseError for relaying error messages has a number of benefits including adding reactive alerts (That themselves can fire jobs and or messages out to notify personnel) based on certain errors and logging to SQL Server Error log. We may take advantage of both of these features in later articles but for now we are just using sys.messages, a storage place for standardized messages. raiseError is great piece of T-SQL that is very under utilized in my opinion. If you have a chance I suggest you take a few minutes and dig into this command and see what you can do with it.
Sys.Messages:
As mentioned earlier we have three new tables we will be covering in this article test.dice, land.currentMapIdent, and land.movementTracker. The first table we will review is the test.dice table. This table's sole purpose is to allow us to verify the randomness of our dice stored procedures mentioned later on in this article. This table has no real production value and will not be utilized in anyway in the actual game itself. I need a way to test to see if my dice procedures are working properly proving me with the proper "randomness" to the number generation process and I wanted a way to test weighting the outcomes of these procedures. This table has 4 columns dice_Counter and identity field that servers as a primary key. The dice sides a tinyInt column that represents the number of sides the dice being tested has. The dice column holds the result for that particular test in a tinyInt, and the dice_Date column which holds the date and time at which the test occurred.
The land.currentMapIdent table holds a record for each being on the map. I say being because an entry into this table can be a Player, a System Administrative Player, or a Monster in the game. This is the first time we see the character_id column so I will take a minute to define its role within the game system itself. The character_id column represents any users character, system administrated character, and or system controlled monster.
A quick run down, on those terms, a system users character is just that a character in the game controlled by a regular player (PC). A system administrated player is one controlled by system administrators and stick to the rules of regular players but can be used to move the game play along by having someone with an overarching system view into the game make things happen in the game environment(NPC). Finally there is the system controlled monsters and this are entities that are controlled automatically by the game engine and are not activity controlled by system administrators.
Currently all system user characters and system administrated characters are regular log-ins and therefore their Id's fall into the standard integer (INT) range of values, system controlled monsters will have all of their IDs start in the range outside of a standard integer (MAX(INT)+1) and go to the top of the bigInt range. The land.currentMapIdent has 3 columns to represent this piece of data, the character_id field as a bigInt defined above. A map_ident column INT that is foreign key to the land.map table, and finally a movement_time column that represents the dateTime when that character entered that particular map space. You will notice that the land.currentMapIdent has a primary key of the character_id column so it will only allow one entry per player into this table.
The last table we will introduce in this code release is the land.movementTracker. This table is basically a history of the land.currentMapIdent table. It has 4 columns the first three of which are values from the land.currentMapIdent table and match them in definition as well. The forth is timestamp row_id column that I will use to see which movement entry was truly put in place first if I have entities arriving at the same map_ident at the same time. This table will be utilized in the game to allow players to see where they have been, to see where others have been if that player has that ability, and to allow for the game engine to have system controlled monster behave intelligently as they move around the map and possible hunt players! Its important to note this table will only ever receive insert values, never and update. I will address this with security settings in a future article.
Tables:
The new procedures for this release and article can be broken into four logical areas and accordingly broken across 4 schemas, metadata procedures, random number generation procedures, testing procedures, and movement procedures related to the land objects. We will start with the one procedure that has to do with metadata, the metadata.TryCatchDebugLogging procedure. The point of this procedure is once again encapsulation of logic that will be utilized over and over again. SQL 2005 introduced the concept of a try catch block implemented through the use of BEGIN TRY END TRY, BEGIN CATCH END CATCH commands. I find this command can be very useful and so I utilize quite often.
There are however some limited to issues with it. First there is not simple one line call of a logging mechanism attached to but they do provide TRY CATCH block functions that will return most of what you would want to use for logging an error in the code block. So I have taken these functions and built a logging procedure that will allow me to control logging from a TRY CATCH block in one single place. Currently I have the logging into the metadata.debuglogging table but there is no reason that I could not have this procedure log to another area in the error log or outside of the database if I needed it to.
The one issue I do have is that MS provided a manner to capture the error number, error message, the procedure that errored, the error state and even the error line, but they provided no way to capture the schema of the procedure that errored so if you have more than one procedure in two different schema's you have no way of capturing which is firing this event without expanding the procedure to pass in the schema value. I am purposely keeping this procedure with no parameters so that it is very easy to call and will not be avoided by database developers. If I can figure out a way to capture the errored procedure schema I will add that into this object in the future.
Next up are the procedures that deal with random number generation or to be specific those procedures that imitate the function of dice in a standard RPG. I have diced to put these objects into their own schema named appropriately dice. Each of these procedures are pretty much a cookie cutter of the procedure dice.d4, so I will explain the purpose behind that particular procedure and leave it up to you to investigate the other similar procedures.
dice.d4 is a fairly simple procedure that generates a number using the T-SQL function rand() and assigns it to a float variable. I will want to use the exact value generated later so I capture it in a variable vs executing the entire function in one select. The T-SQL rand() function produces a float value between 0 and 1 so I then multiple this value by 4 and run it through the T-SQL floor() function to round off an decimal value, I then and add one to the result. This guarantees I will have a value between 1 and 5, I then have a case statement in a select that takes values 1 thru 4 and assigns the corresponding values to the @dice variable data type INT. If I have a value of 5 I assign that to 4 and if I somehow get a value outside of that range I insert a record into my debug.logging table and assign a value of to the @dice variable. I really wanted these procedures to exist in functions because I thought it would be easier to call in subsequent code but I quickly realized that the rand() function is not allowed in a UDF so a procedure it is. This rounding and bounding logic is expanded upon for the d6,d8,d10,d12,d20, and d100 procedures but is basically the same code.
Procedures designed for testing in both your development and production environment is a new concept I am introducing with this article. This is something new for me as well so I will see if I continue this concept in future articles but so far I really like being able to run some test in both my development environment and then migrate that test to a production environment and be able to execute the same test with different sets and quantities of data.
There are three test procedures we will be working with initially, the test.diceTester, test.movementLocks, and test.spaceBuilderViewer. The first up the test.diceTester does what it sounds like, it runs the dice procedures through an exhaust set of runs and logs each of the values returned to the test.dice table. This procedure takes two parameters the first @dice_sides tinyInt determines the dice.dX procedure that is called value of 4 will call dice.d4 and so on. The second parameter is defaulted to 10000 and is the number of times that the dice procedure will be executed. test.diceTester will then build a dynamic SQL statement based on the first parameter and execute it in a while loop based on the second parameter, inserting the OUTPUT value into the test.dice table as well as information for the dice_sides that was being executed, and the time it was executed so we can report on these results later. This procedure has no debug parameter as the only debug value I would want to look for is always logged to the metadata.debuglogging table.
The next test procedure is the test.movementLocks, this procedure was built to help me test locking in my land.currentLandIdent and land.movementTracker tables. So far in my limited testing I have not had any locking issues when I run 3 concurrent sessions with different users moving about the map. I expect to utilize this procedure more in the future and do expect that I will see locking issue as the game engine becomes more complex, but it is never two early to start testing your code and to start building your test code. test.movementLocks requires one parameter which is a valid user id (character_id) and has one defaulted parameter for debugging as is standard. It then cycles through a total of 100 counts of movements of this character_id in a random direction checking for a possible direction change with each iteration. I plan on giving a little bit more control to the random direction that is chosen in the future so there are two loops one using a @x_axis counter and the other using a @y_axis counter but at this point they are used strictly as loop counters with no weights being applied to the logic that determines the direction.
The last procedure in the testing space is the test.spaceBuilderViewer which is my personal favorite of this release. It allows you to see without building the chance of any particular map tile being placed next to any tile on the current land.map table. This procedure rips the tile building logic from the land.spaceBuilder procedure and allows you to see the percentage chance of any map tile being generated from the raw land.landTypes table. The procedure currently does not take any map tiles into consideration and is displaying values in there raw form. This will be enhanced in future releases so you can see what a land.landTypes records percentage chance of being built next to any existing land.map map_ident value. For now its nice to see what the percentage chance of a given land.landTypes is and how it compares to the other land.landType records it also shows you its relative percentage range.
Please take a look at the logic and ask any question you may have on how or why this procedure is executing in the manner that it is. Unfortunately it requires a loop over the land.landTypes table to build a percentage range out of a set of base percentages and modification percentage points.
The last set of procedures we will be going over are the map related land.move and land.showMap. The land.showMap procedures as been altered to add additional logic that will allow a player to see themselves on the map and to see where they have been on that map screen. This is done by adding the land.currentMapIdent and land.movementTracker tables to the dynamic SQL statement that is being run by this procedure. When security is introduced this will be one of the first procedures available to the "player" database role.
The last procedure we will be discussing is land.move. This will be the core movement procedure and will be responsible for checking a number of game constraints to ensure that movement rules set up in the game are followed. Currently this procedure only ensures that the movement direction is valid (8-up,2-down,4-left,5,-right) is valid. It ensures the character_id being moved is a valid ID in the map currently. It also insures that the player is always within map bounds by adding to the map if a player gets too close to one of the edges. Finally it ensures that the land.movementTracker and land.currentLandIdent tables stay in sync. This could have been done by the user of a trigger and may still be moved in that direction but currently I wanted to have more flexibility in-case there becomes a reason why I need to break this direct tie between the two objects. It is also just easier to modify all of my movement logic in one place vs. having to deal with a procedure and a trigger. If the trigger is deemed the best logic construct to hold this code then the transaction that keeps the land.movementTracker and land.currentMapIdent in-sync will be moved to a trigger.
Procedures:
Well that's it for the code review and explanation section of this article the only thing left is to run the example code and play around with the very beginnings of the game. This is really the first time a user can start to manipulate the system using objects that are intended for game play! As always please let me know how you feel about the project, the design, or anything else that comes to mind.
Run the Code!
