TigerMUD database

Developer
Mar 28, 2008 at 3:53 AM
Edited Mar 28, 2008 at 3:58 AM
Hi everyone!

I've begun fleshing out the functionality of databaseLib.MSSql in my workspace.

I'd like to add a configuraiton option to the TigerMud.XML file to indicate whether or not the server admin wants to use the old MSSQL ODBC data access functions, or the new SqlClient functions. I can do this in two ways:

1) add another XML element indicating ODBC or SQLClient
-- or --
2) use the existing <type>mssql</type> node, but allow my new code to be consumed when sqlclient is specified.

These two options are mutually exclusive, of course. I'd like to hear from you guys on what you think.

From what I've heard from Adam, I'm pretty much alone in wanting this project to work with stored procs on SQL Server, so I'm going to take care to make sure that the changes that I make to the project will not negatively affect the code written against msaccess OR the existing ODBC code that goes against SQL Server.

I'd still like to hear what you guys think, though.

Secondly, I've imported the access file into SQL server and modified the schema slightly to take advantage of SQL server specific features. I plan to maintain compatibility with the other Data Access entities (particularly msaccess thorugh ODBC) by maintaining the Interfaces (eg IActor for MSSql.Actor, etc...), but the application code that I wrote in the mssql namespace isn't directly dependant on the database schema: Only on the stored procs. I've created an enumeration in BaseMSSql.cs that has a comprehensive list of all the sprocs that are called by the application. That way users can (if they want) orginize the data using whatever data pattern they feel like, and the application will still work as long as they expose those sprocs to the app.

In the meantime, however, I'm willing to bet that it would be helpful for me to upload some database creation scripts.

Microsoft makes it really easy to re-create the database schema using SSMS built in "Generate Scripts" function, so I'll just run that as soon as I've completed my lofty goals of making this app work through SqlClient. The input that I would like from you guys is how I should package the script? should I make it a solution item? Create a new solution folder called MSSQL that only holds the data relative to people who use the sqlclient functionality? I suppose asking this question is the same as answering it.

Finally, the mudactorstate table has both integers and guids in the id column. unless someone's got a clear objection, i'd like to clear that mess up. Indexing that column won't help much unless we can make it all the same datatype. :-) Can anyone wax intellectual with me on to which table the Integers are FK's, vs to which table the GUIDS are FKs?

Let me know how you guys feel about this.

thanks,

Jason
Coordinator
Mar 28, 2008 at 3:30 PM
That sounds great Jason!

How about changing the db switch to be "odbc" for the open ODBC SQL option, "msaccess" for Access, and "mssql" for MS SQL native? Our current wording is confusing because our features changed without us updating the switch name. It used to be only Acccess and MSSQL a long time ago and it made sense back then but not now.

It was fun to maintain compatibility between Access and MSSQL to date because of the difference between reserved table names and other quirks between the two. The old Sourceforge forum archives might have some hints if you run into trouble.

Database creation scripts are a godsend and very welcome. They make things alot easier for sure. MySQL and MSSQL have some crazy fun trying to read each other's scripts, but the old forums have answers to those problems I remember working on that back then. I'd add the scripts into Solution Items with nice descriptive names for folks to see, that would be perfect.

The inconsistent uniqueid problem is due to us adding code on top of code over a period of time and just the mess you sometimes get as a result. I think Guids are the best way to go (many beardy coffee-stained DB types still debate this today in an infinite loop about integer versus string/Guid uniqueids, etc.) but the whole debate is pretty much a wash overall and Guids guarantee uniqueness so I'd say its best to go with that.

Glad to have you!

Cheers!
Adam





Developer
Mar 29, 2008 at 1:58 AM


adammil wrote:
That sounds great Jason!

How about changing the db switch to be "odbc" for the open ODBC SQL option, "msaccess" for Access, and "mssql" for MS SQL native? Our current wording is confusing because our features changed without us updating the switch name. It used to be only Acccess and MSSQL a long time ago and it made sense back then but not now.


I think using odbc as a value for ServerInfo.databasetype is overly generic.... the application is wired into mysql through odbc, so wouldn't it make it all the more confusing?

How about this set of values instead?

mssql-odbc = sql server through odbc
mssql = sql server through system.data.sqlclient
mysql = mysql through odbc
msaccess = access over odbc


Coordinator
Mar 29, 2008 at 10:31 PM
Sounds good, lets do that. Thanks!
Coordinator
May 9, 2008 at 1:40 PM
Hi Jason,

Were you planning to make that change or did you need some help?

cheers
adam
Developer
May 9, 2008 at 8:57 PM
If my memory serves me correctly, I believe I made these changes on my local copy of the app, but I never checked them in because I'm using TFS instead of SVN.

If I were to provide you with my updates, would you check them in for me? I've kinda gotten distracted from the mud project by real life concerns... and the code that I've written might be the first and last contribution that I can make for the time being.
Coordinator
May 10, 2008 at 6:55 AM

No problem, if you send me the code I'll check it in. Thanks!


gs_eigh wrote:
If my memory serves me correctly, I believe I made these changes on my local copy of the app, but I never checked them in because I'm using TFS instead of SVN.

If I were to provide you with my updates, would you check them in for me? I've kinda gotten distracted from the mud project by real life concerns... and the code that I've written might be the first and last contribution that I can make for the time being.