Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

theSystem - Part II

By Steve Fibich,

With this segment we will see parts of "theSystem's" land environment take shape, we are also laying the 2nd half of the foundation for logging and debugging. We will be looking at one table metadata.debuglogging, and one stored procedure land.landBuilder. We will also take a detail look into the system data that populates tables we covered in the last segment; land.landType, land.mapType, land.landTypeExistanceMod.

We have two new objects: one a table in the metadata schema called "debuglogging". The second is a stored procedure in the land schema called "landBuilder". The table debuglogging is the partner table to the metadata.debug table introduced in the last article. This table gives "theSystem" processes a place to log information for debugging purposes. If debug is turned on for a particular procedure call or for a schema this table will begin to fill up with what will hopefully be useful information for debugging errors.

This table contains 4 columns:

  • object_name which represents the object that is doing the logging
  • Event_date will store the date for this particular log record.
  • user_name which is system user name that is running the process being logged
  • the debug_message itself that is being written to this row in the table

Hopefully I won't have to use this table often, but as the process becomes more and more complex and I get a procedure calling a procedure, calling a trigger calling another trigger, it will be nice to research where an errored line of code exists. The debuglogging table does not exist to pick up hard system errors but rather soft process errors or bugs that cause the system to behave in unintended ways. This logging table will allow me to step through a complex set of code using an audit trail. The ability to turn logging on and off not only by passing a parameter but by having a value in a table set the logging level will great help in finding bugs in a online production system.

Now that I have this structure in place all of my stored procedures will have a @debug parameter that is defaulted to 0. All of the stored procedures will also check the value of the metadata.logging table to see if the schema they exist in is set in debug mode. The @debug parameter will take precedence over a value in the metadata.logging table unless it is 0 in which case the metadata.logging value will be determined. Any value greater than one will indicate that some level of logging is necessary, and the @debug parameter will always be of type bit, tinyint, or smallint.

The second object, land.landBuilder, is a procedure that builds upon land.spaceBuilder. The land.landBuilder procedure retrieves the maximum and minimum x and y coordinates of the land.map table and builds a series of spaces in a straight line from the highest value of x with the lowest value of y to the highest value of x to the highest value of y. It then builds from the highest value of y lowest value of x to the highest value of y to the highest value of x, ignoring any spaces that already have values. In this way it builds two straight lines around two edges of a block as shown here:

----|
XXXX|
XXXX|
XXXX|
XXXX|

KEY:| and - represent new map spaces.
Currently this map building logic could result in non continuous space on the map. I will have to re-work the way land.landBuilder chooses its starting positions in the future but for now I'm not worried about it. Also this manner of adding spaces to the map means that as the map gets bigger the number of spaces added increases by two with each call. Once again this is something to keep in mind if the map ever gets to be millions of spaces square. I will also need to write a procedure to check for non continuous space in the future.

That's it for new objects let's get to the system data that makes them tick. I will put these three items on my to do list and come back to them shortly in a few segment.

Things to revisit later

  • land.landBuilder could build non continuos spaces
  • land.landBuilder adds two new spaces to the build process with each run (3,5,7,....10,001,10,003 etc.)
  • possibly may need to build a check for non conations spaces if the first item is not resolved

Here we have the code for these two system objects.
Now we have a number of tables and a number of procedures but the database doesn't really have any life of its own as of yet. This system needs its system data to allow any of the stored procedures we have built to truly function. The first system data we are going to look into is the land.MapType values. This is going to be pretty straight forward as we only have one value currently which is "map". We will be expanding this in the future for maps of different types most likely "town", "cavern", "city", etc. This system data doesn't do a whole lot any procedure at this point and is more of a stub for future development. The code for this initial value is below.

The second core system data we look into is the land.landType data. This data represents top level map land types. Currently I have no way to distinguish between top level map types and secondary level map types, but this doesn't present a huge problem, as I only have a single map type which happens to be the top level map type. Later on the structure of this table will change to allow multiple map type landtype data to be stored.

With this current iteration we have 15 landType values. Each of these values represents a textual description land, such as swamp, hills, mountains and so forth. Each landType has a value for base_incounter_percentage, base_special_incounter_percentage, and base_existence. The first two represents values for future use and will be utilized to determine the chance for a player to encounter some sort of random chance action in the game. The third value base_existence_percentage is initial value we use to calculate the chance of this particular land type to exist on any given map title (row) in the land.map table.

I have been working with these values and the land.landTypeExistanceMode values to get the land.spaceBuilder procedure produce map tiles that have a natural feel to them. In other words I don't want a bunch of ocean tiles next to desert next to the tundra map tiles. I would like it to progress from plains to hills to mountains...with a little bit of a random feel every once in a while. The code for adding these values is below

Now we come to the value for the land.landTypeExistanceMod table. These are some of the more difficult data elements to set correctly in "theSystem." These values help to determine the relationship between different land.landType data. Each land.landType value can have a modifier for each and every other land.landType value. The purpose of these modifies as mentioned earlier is to ensure that the land.spaceBuilder procedure builds the map in a logical layout and not just a random setting of land.landType tiles.

I have set these values through trial and error to produce a map that lends itself to building certain land types next to one another and pretty much avoiding the sea land type. Once a sea landType tile is inserted into the land.map table the odds of a sea mapType existing next to it increase greatly. This affinity for sea values to reside next to other sea values and everything else to reside next to itself or a similar land type somewhat mirrors we see in our own planet. The code for adding this is below.


Well I tried to keep this article easily digestible, as always please let me know if you have questions or comments on the project so far. I hope you have created your own "theSystem" database and can play with these objects on your own. If you have any enhancements to the design let me know and I'll see about working them in. The next installment will bring a new schema, a movement procedure, and updates to existing objects to bring them inline with our new debugging objects.

Resources:

theSystem.PartII.sql | theSystemInsertValues.land.sql
Total article views: 3084 | Views in the last 30 days: 97
 
Related Articles
FORUM

Inserting zero in Identity Column

Inserting zero in Identity Column

FORUM

Zero being inserted in identity column

Zero being inserted in identity column

FORUM

Inserting value for identity column in a temporary table

Inserting value for identity column in a temporary table

FORUM

Identity Column

Adding an identity column to an existing table

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones