Archive for November 2007
Work continues on Perenthia and my PBBG Engine. I think I got the core PBBG engine framework complete, at least complete enough to start porting over the content items from Perenthia. My goal is to get a small area working with a few items, monsters, etc. before I port over the entire database.
I am hoping to improve performance in this new version as well as complete several of the features I didn't get implemented in Beta 1. The engine uses a lof of in memory objects while still persisting the world to the database. Running a lot of this in memory will eat of memory on the web server but will hopefully improve performance in regards to interactions from users. The main bulk of the places or rooms will be loaded up into memory when the application starts. Along with this load will be a load of NPCs and items located in towns and designated areas. Then, whenever a player logs in and begins playing their player object will be loaded into memory as well. At regular intervals the world state will be updated and player objects persisted to the database. Inactive player objects will be saved and then discarded from memory until the next time the player logs in.
Loading these objects into memory will keep the database connection initialization and IO traffic to a minimum and help speed up actions such as moving around and engaging NPCs. Since I don't know of anyone actually doing this I'm not completely sure it will work. 🙂 My testing so far is working great and at most I may need to beef up the RAM on the web server.
I am still planning for a January release of Beta 2 and depending on how that beta goes; a full release in the early Spring.
I've seen a lot of stuff out there regarding the SQL 2005 XML data type but most of it is just regurgitates the MSDN documentation. That's fine and all but what about practical uses of it? Well, I have a practical use sample. In building my persistent browser based game Perenthia I have a concept of a Place. A place is a virtual space in which objects are stored. For Perenthia the places represent the various rooms or tiles players move around on. The place or room has exits defined that allow the player to move from one place to the next. The exits are the typical directions; north, south, up, down, etc. In the database I have a Places table and a PlaceExits table. The Places table stores all the information regarding a place and the PlaceExits table stores the placeId along with a directionId and destinationId so I know what exits are available in any room and what rooms they lead to.
The simplified schema for the places would be:
In the stored procedure that retrieves the place information I use the following query snippet in the select clause:
e.DirectionId AS "@directionId",
e.DestinationId AS "@destinationId"
e.PlaceId = p.PlaceId
FOR XML PATH('exit'), ROOT('exits')
) AS ExitsXml
FROM dbo.Places p
This creates an XML fragment I can then parse in the application to fill a collection of Exits on the Place object.
When saving place information I pass XML generated from the Exits collection in a stored procedure like so:
CREATE PROCEDURE dbo.Places_SavePlace (@PlaceId int, @ExitsXml xml)
From within the save procedure I perform an update or insert of the place data and then execute the following sql to insert and update the exits for the current place:
— Process the existing exits first
DestinationId = e.ex.value('(@destinationId)', 'int')
@ExitsXml.nodes('/exits/exit') as e(ex)
PlaceId = @PlaceId
AND DirectionId = e.ex.value('(@directionId)', 'tinyint')
— Process any new exits
INSERT INTO dbo.PlaceExits
ObjectId, DirectionId, DestinationId
@ExitsXml.nodes('/exits/exit') as e(ex)
e.ex.value('(@directionId)', 'tinyint') NOT IN
SELECT DirectionId FROM dbo.PlaceExits WHERE PlaceId = @PlaceId
This is working pretty well and keeps me from having to loop through the exits in the application and make multiple database calls.
I've worked on some large projects in the past with 500+ database tables but I have yet to see any persistent browser based games or any games get up that kind of table count. I only have 61 tables in Perenthia right now with a few more features to add which could possibly add another 10 tables. I would be curious to see a larger model, to see what kinds of things are handled. Right now I have tables for users, roles, places, things, avatars, skills, attributes, quests, guilds, professions and an assortment of lookup tables for things like item conditions, materials, titles, levels, etc.