Quick Survey on MS SQL2000

#1 I have it installed on my dev machines (my real life job). You can get MSDE for free, and even distribute it with your app - its a "local only" version of sql server 2000.

#2 Look at sqlite. It's both free and cross-platform. I believe it was originally created to support the Mono project, which is an open source cross-platform implementation of the .Net framework.

While there are good reasons to use a database (such as ease of handling complex data) I don't think integrity is a good one. It makes it harder for end users to extract and back up their data. If you go that way, be sure to let your users know how to get at the data for archiving.

#3 Makes since, I was going to build a vb program that edited the data for them, etc. So yes, their would be a tool to modify the data.

#4 Kenneth,

Also, It's not really important how I store it, w/ the table schema documented, it wouldn't take much for someone to rewrite my code to read from anywhere. I'm encapsulating all my data retrieval code in one place. So it's not a lot of looking, it's all there.

And, integrity is a good reason when ur developing. It keeps me honest. In otherwords, it prevents me from "Hacking" the code as much since I have to be consistent. Currently the model is about 20 tables, which translates into about 20 linked lists (Some inside of others for joins etc.) So, when developing I like to be able to use the diagram tool of the database etc, for quick visualization of my work. My goal of this project is to have a resource, that will plug into the standard head (1.3) with no headaches. Just replace a couple scripts and your off. For example, my item class w/ out dependents is:

CREATE TABLE [dbo].[TB_ItemsLib] (
[Item_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[Item_FriendlyName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Item_DTSName] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Item_Png] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Item_Description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Item_MaxStack] [int] NOT NULL ,
[Item_DefaultPickupAmt] [int] NOT NULL ,
[Item_MaxDurability] [int] NOT NULL ,
[Item_MaxDamage] [int] NOT NULL ,
[Item_MinDamage] [int] NOT NULL ,
[Item_ShortRangeDistance] [int] NOT NULL ,
[Item_ShortRangeModifier] [float] NOT NULL ,
[Item_MediumRangeDistance] [int] NOT NULL ,
[Item_MediumRangeModifier] [float] NOT NULL ,
[Item_LongRangeDistance] [int] NOT NULL ,
[Item_LongRangeModifier] [float] NOT NULL ,
[Item_DefaultVendorValue] [float] NOT NULL ,
[Item_ItemEffect_ID] [bigint] NOT NULL ,
[Item_IsContainer] [smallint] NOT NULL ,
[Item_ContainerSize] [int] NOT NULL ,
[Item_Size_ID] [int] NOT NULL ,
[Item_Type_ID] [int] NOT NULL ,
[Item_DirectDamage] [float] NOT NULL ,
[Item_radiusDamage] [float] NOT NULL ,
[Item_damageRadius] [float] NOT NULL ,
[Item_armingDelay] [float] NOT NULL ,
[Item_isLore] [int] NOT NULL ,
[Item_isNoDrop] [int] NOT NULL ,
[Item_isAttunable] [int] NOT NULL ,
[Skill_ID] [int] NOT NULL ,
[MinSkillToUse] [int] NOT NULL ,
[MinSkillToCrit] [int] NOT NULL ,
[CritBonusPercent] [float] NOT NULL

So you can see I'm building in a good bit of functionality into the C++ so that little to none needs to be done in the torque script.

Vince

#5 If you need some "real" engine for backend usage, I would look at MySQL instead of sqlite. For small stuff though, sqlite is really nice and usable.

#6 I also plan to provide database models (w/ documentation) as well as C++ code datamodels (w/documentation). You know drawn diagrams how the objects relate as well as a documentation base of console methods available etc. It's not a small project, it's quite large, I built a smaller version and figured out most of what I need to do, so I know how to make it work w/ simpler models, so now I'm re-writing the way I would of wrote it had I planned it out from the start.

The real goal of this resource is to provide the GG community w/ a resource which with little to no effort they are able to build simple muds in 3d. Assuming that they are building an attribute (wisdom/strenght/etc they define it) and skill (slashing, range, concusion) based rpg system. The C++ will handle things like encumberance and skill checks, inventory management, all they need to do is plug it in.

Vince

#7 The problem w/ MySql (Yes I tried it) is that MySql doesn't have a complaint oledb driver out, so none of my mappings between the torque datatypes and C++ match which caused major headaches. I tried using their ODBC driver and it had problems, so I went back to what I new. I'm using the Database ODBC resource to handle database communications (It's slightly modified)

#8 Also, once the game spins up, the only thing it retrieves from the database is the player info.... EVERYTHING else is cached in memory, items etc. I'm reducing the amount of memory I'm using by creating library's in memory and having player and item objects pointing to reference libraries. etc. It's an interesting project. It will make since once you see the documentation.

#9 Also, once the game spins up, the only thing it retrieves from the database is the player info.... EVERYTHING else is cached in memory, items etc. I'm reducing the amount of memory I'm using by creating library's in memory and having player and item objects pointing to reference libraries. etc. It's an interesting project. It will make since once you see the documentation.

#10 What are you doing about Mac and Linux users?

#11 Believe me it will be easy enough to port this to any database or flat file system you want. Also, remember this is not a pack that you pay for it, it's a resource = free.

#12

Quote:MySql doesn't have a complaint oledb driver out, so none of my mappings between the torque datatypes and C++ match which caused major headaches.

Hey Vince, would you mind explaining a little more about the MySql problem you had? Is there a reason to use oledb instead of the MySql C API (was it just a familiarity thing?) I ask because I'm in the midst of trying to plug in C API for Mysql, both into Torque and another C project, and it seems like it will serve me well (if I could only solve my linker errors w/gcc). :-P

Just curious for more information as to why you didn't want to use it.

#13 And if you're restricting yourself to a Win32-only solution, why oledb instead of ADO?

#14 I'm using a modified version of this resource
Torque ODBC
http://www.garagegames.com/index.php?sec=mg&mod=resource&page=view&qid=2737

When I pointed it to a mysql database none of the datatypes were recognized causing unbelievable pain. And I AM NOT limiting myself to a win32 only build. The database part is only for initialization and player loading and it's all housed in one class that is... welll.... documented. So their is no reason I can think of.. (Except the person modifying it is a newb) that a reasonable average programmer can't take out the Torque ODBC resource files and calls and replace them with what ever they want. An example of what I'm talking about:

void RPGCore::TB_SkillTypes_Load()
{
S32 errcode;
char *ret = Con::getReturnBuffer(10000);
dStrcpy(ret, "");
DatabaseConnection* oconn = new DatabaseConnection();
oconn->Connect (dbDriver,dbProviderString);
oconn->Execute ("select Skill_ID,Skill_Name from tb_skills",1);//Excute the SQL but DO NOT register it w/ torque, since we are using it here.
errcode = oconn->mResults->GetValue ("Skill_ID", ret, 10000);
//Read the first row in and add it to the list
if (errcode!=0)
{
S32 Skill_ID = dAtoi(ret);
errcode = oconn->mResults->GetValue ("Skill_Name", ret, 10000);
char * Skill_Name =ret;
tb_skilltypes_list.Add_Item (&Skill_ID,Skill_Name);
}
//Continue reading till their are no more rows
while (oconn->mResults->NextRow ())
{
errcode = oconn->mResults->GetValue ("Skill_ID", ret, 10000);
S32 Skill_ID = dAtoi(ret);
errcode = oconn->mResults->GetValue ("Skill_Name", ret, 10000);
char * Skill_Name =ret;
tb_skilltypes_list.Add_Item (&Skill_ID,Skill_Name);
}
oconn->Disconnect ();
delete oconn;
S32 num = tb_skilltypes_list.ItemCount ;
}

Vince

#15 All of the link list loads are done this way, some I admit are link lists in link lists and some are link lists of pointers to other link list elements but for all intensive purposes the code is pretty straight forward.

Note, and I know the way I am doing the thing w/ ret can cause problems but since I am reading the only string last, their is no data corruption... that I can tell so far.

and by what I said none of the data types matched up, the resource I was using, the varchar field didn't match a ms varchar field id in the implemetation. So if someone wants to write or implement their own mysql driver and retrofit my loaders they will be more than welcome, I was just spending way too much time on the sql side of things and I had to make a call. So I decided for something that was familiar to me instead of foriegn.

Also, for anyone worring about complexity or not understanding it, I am putting together an erwin datamodel as well as a visio C++ object chart so people can see what objects are inside others etc and how things point around. I don't claim to be a good programmer... lol not even a novice... I haven't touched C++ since college and that was over 10 years ago. What I do know (And Quite Well) is datamodeling, so my only hurdle is converting my sql datamodel to a c++ datamodel(Yes, I've had a good refresher in pointers thanks to this project).

I'm probally documenting this more than what I need to, but I actually want to create a plug and play piece for torque that will have an external (Prolly) VB application to manipulate the data in the database so that people who are less skilled than I do not have to fool around w/ the C++ and can just write the torque script and use the tool I provide to access the database to build their rpg.

The usage model would be,
Designer opens tool, enters all the item information (Weapons, potions, etc They can define what ever they want then create that type of item), define the player attributes and skills, add the torque script code to talk to the RPG module etc, and boom their off and running.

Some things I'd like to do is add a "Script Generator" so that all the script for the weapons is dynamically generated and it puts the code into the right directory etc. So, really all they need to do is know how to do graphics to get a decent game running.

If I can finish this, I will prolly work on the RPG:Encounters module for the mobs in the zone and tag the loot tables etc.

I'm just not happy w/ the performance of torque when you have a great deal of torque script. When you simulate 50 users things go a tad slow even w/ a duel 2ghtz processor w/ 2 gigs of ram. So I figured that if I wrote C++ optimized for the purpose of tracking player inventory, skills, attributes, effects, etc it would run faster. I'm not trying to bend anyones arm to use it, this is really for my learning process.

One last side note, and I know I'm gonna get flammed for this, I implemented it as a class RPGCore : public SceneObject and use consolemethods to retrieve data. To add the component to your torque you basically take my code paste it into a folder in your head and put into your mission file:

new RPGCore(MyRPG) {
position = "0 0 0";
rotation = "1 0 0 0";
scale = "1 1 1";
driver = "SQL Server";
connectionstring ="";
};

Honestly, it's prolly my newbishness that did it that way. I'm sure theirs alot better ways to do it.

Vince

#16 Out of the options available, I would go with either SQLlite or MySQL. Not only are they free, but they use a lot (read mucho mucho) less resources than either Oracle or Seqel. Added bonus, they are both lighter installs, so less to go wrong, and cross platform.

If you understand SQL and relational databases, then you should find it pretty easy to move from seqel to mysql.

#17 SQLite is a local only database with no network connectivity. It is very suitable for storing local info (pref, settings, configs, etc). It can also be useful if you let a server (Torque for example) convey the data to the clients...so storing datablocks in it is a good option. It is public domain and has good xplat capabilities.

MySQL is a really good "cheap" option. Their licensing isn't "free" for commercial use though there are some creative ways to get around it with their GPL version (keeping everything in non-dsitrbuted server code). Last I checked, their commercial license is $500 which is very reasonable. I too have had some serious issues with their odbc drivers and their oledb compliancy. Until the latest version their SQL implementation has been pretty limited (no stored procedures, no views, limited union support, no nested selections, missing data types, etc.). It has been more than sufficient to power database-driven websites and authentication systems but I found it extremely frustrating to use in high level business applications where I was having to manipulate very complicated data coming in from across multiple databases. I have read that the latest version has addressed many of these issues but I haven't had a reason to test them. MySQL is very xplat friendly.

Microsoft SQL Server is an excellent piece of software despite the huge amount of derision that has been heaped on it. It has the best tools and server interface in the industry (even better than Oracle). The previous security issues have been largely addressed and it is far more scalable than people give it credit for. I came into MS SQL Server development from a background in Oracle and MySQL and expected the worst. It *earned* my grudging respect. You can use the MSDE for free for development and it is a fully functioning version of SQL Server. I found MS SQL Server to be pretty resource light (not as light as MySQL). Obviously MS SQL Server isn't going to be very xplat friendly though =)

Oracle is the ultimate powerhouse of SQL servers. *Very* scalable, very dependable, and almost stupidly good at preserving your transactions. It is nicely xplat capable. Has decent tools that continue to get better every year. Installation is a bit of a pain (on Windows it is fairly straight forward...on Solaris it is a 2-day event that requires intensive training to accomplish). It also costs a boatload of money =P

These are my opinions/impressions/experiences from working with each of these databases heavily (except SQLite) over the course of 7 years of doing database-driven web and application development.