MS Sql Server

[MrPickles]

Hello, I suppose I haven't really introduced myself. I'm Ryan or MrPickles... I've been writing C#/.net applications for about 6 years.
Now that's out of the way , here's my question:

I have been trying to write a MS Sql Server implementation for the fun of it. I know, there's no reason not to use MySQL as it's faster than MS Sql (from what I've read), I'm not aiming to create a public freeshard, just looking for a project and possibly, an offer for a contribution as I plan to share anyway, having options never hurts right? I have successfully migrated the MySQL database to my local Sql Server Db and completed all the work in DataConnection.cs and now starting on a SqlServerObject subclass. I'm going to use ado.net just to get it up and running then maybe consider bringing in EF - code first, as I HATE edmx. I pulled down the entire repository and noticed there's an MSSQl Server implementation already wrote in one of the branches (dol database v2 I think). When I tried opening it in VS2013, converting it from 2008 messed it up and I can't get it to build, too much of a mess. Has anyone done a successful MS Sql server implementation? Or did that turn out to be a wash / something nobody wanted? Also, would anyone consider this a waste of time? Meaning not a valid contribution or something is already in the works for it. As I said, I know it's more practical to use MySQL, I'm more or less doing it for the fun of it but I don't want it to be for nothing either.

btw:
Please, Nobody tell me how much work is ahead or what obstacles to expect, I want all of it to be a surprise. The hair pulling and frustration is so worth it when things work out, no spoilers

[Leodagan]

Hello,

I'm the Author of the recent SQLite connector for DOL (which I made for pretty much same reason as you, some fun, then I tried to think what it could be used for )

It's hard to reply your question without spoiling, but let's try :

- having options never hurts right?

Yes having other database connectors won't hurt, they don't need to be maintained in time to be efficient, they're fun to experiment and bring totally new "server architecture options" to Shards owners

- I'm going to use ado.net just to get it up and running then maybe consider bringing in EF

DOL was written to work with ado.net, so it's a good start, if you try to implement EF and succeed we should really talk/work about a "merge" and "migration" of other connectors to EF... (existing MySQL and SQlite connector could have EF compatibility with recent drivers)

SQL queries used by DOL are pretty simple, it's only :
- (RDBMS independent) Select, Update, Delete
- (RDMS Dependent) Describe Table, Create Table, Alter Table, Create Index, Drop Index... (no drop table used)

The API can build some "Where" clause for queries (mostly field = value or field like value), and there is no "JOIN" used for relation it simply query the other table using the key field (DOL don't use the Database intensively on runtime, so we gather most of data at startup and cache whole tables)

- pulled down the entire repository and noticed there's an MSSQl Server implementation already wrote in one of the branches (dol database v2 I think).

This branch is pretty old, you can always try to get some ideas from reading code in there but don't expect to make it run

- Has anyone done a successful MS Sql server implementation? Or did that turn out to be a wash / something nobody wanted?

DOL is only supported (by community) when running on MySQL, even SQLite connector is a dev feature and shouldn't be used on a production shard (it doesn't support alter table for example...), so there have been few attempts to bring other connectors to DOL, MySQL is available everywhere

This won't be the first feature "nobody wanted", but that's not the point for me when coding for free

- Also, would anyone consider this a waste of time? Meaning not a valid contribution or something is already in the works for it.

The only way it wouldn't be a valid contribution for "me" would be that implementing MSSQL drivers prevent from compiling DOL with Mono (but I have no idea if this is going to be an obstacle with MSSQL or other connectors), I mean that as long as there is no regression from having an inactive MSSQL connector in project there is no reason to not commit this new feature


I can't finish this without spoiling a bit :
You'll need some skill with "System.Reflection" to understand correctly existing connectors and mimic them...

[Tolakram]

Hi Ryan

I think it would be nice to have a MSSQL connector, just for the heck of it. The DB layer is fairly static so once written it shouldn't need that many changes going forward.

[Graveen]

Do not hesitate to remove the old non-working code. There is VCS for keeping track of it.

Basically, base your work on the existing MySQL code, the queries should not differ a lot

[MrPickles]

DOL was written to work with ado.net, so it's a good start, if you try to implement EF and succeed we should really talk/work about a "merge" and "migration" of other connectors to EF... (existing MySQL and SQlite connector could have EF compatibility with recent drivers)

There is a MySQL Entity Framework package available on the nugget library, I have not used it with MySQL so I don't know if all that's needed is a dll or if there's some kind of "black magic" that only works with visual studio. Something I'll look into though. EF with an edmx most likely wouldn't work in Mono (maybe) so that's why I figure a "code-first" approach would be best for a project like this.

I changed my mind about the spoiler thing, if there's more to spoil, please do, I tend to overestimate my free-time. I have been messing around with DoL for about 2 months off and on but only really hit it hard in the past few days as I've been out sick from work. Once I go back, free time goes back to little to nothing so maybe some spoilers wouldn't hurt in making some progress as I can tell there's a lot of work to go.

I've been making efforts to not touch any existing code, I can't avoid it in DataConnection.cs but it's only checking if the connection type's MSSQL the impact's minimal to nothing.

Basically, base your work on the existing MySQL code, the queries should not differ a lot

I have been doing that so far, it's been almost identical as far as code goes, the syntax from MySQL and MSSql are fairly similar

I'm the Author of the recent SQLite connector for DOL (which I made for pretty much same reason as you, some fun, then I tried to think what it could be used for )

I like the work you did with setting up sql lite, it helped me get a local freeshard just "up and running" instantly to get an idea of how other developers would use it if they already had their db set up, I eventually switched to MySQL but being able to just dive in right away was very helpful. Neat stuff

Thank you for all the info, really appreciated it!

[Leodagan]

in DataConnection there was a lot of check to make sure the database was either SQL connector (anything but xml) or MySQL connector specifically for some "SHOW" Query instead of Describe Table or other specific command, to enable SQLite Connector I changed a lot of these check to use "else if (SQLite)" behavior, you should only have to implement an other "else if (MSSQL)" even if it's a bit ugly and most of these method should be moved to SqlDataObject Connector subclass, it's still the fastest way to test your connector

Then you should only work around numeric field type (Int/Long storage), maybe some specific behavior for boolean field type (either a tiny int value or a true database boolean or even a ENUM type...), most text field should be pretty large (MySQL handles them differently between Varchar and Text field but it should be anyway a variable size text field we're not ready for fixed size...), floating point number should be no trouble, we don't need a lot of precision here, any implementation should do

The Server Startup Table Creation is a good test for your connector, there is always some script lurking in Quest that should try to create a lot of item and npc, if you're using latest revision of DOL the startup sequence should also try to create some Zones and Regions, then further test should be done by logging a character in game with some item auto-added (this will test object creation with relation), the final test would be using an Admin character to update an item in your inventory or change some mob/template value and save them to database

If all this is working the final test is to add some "DataElement" to any DataTable, and test if Alter Table is going ok, then add some indexes on column and multi column and some unique key constraints to make sure any alter table or create table scenario is covered

[MrPickles]

An update in case anyone is interested
I've only had last weekend and today to work on this but so far progress has been looking good. The DataConnection class is complete to my knowledge (I was wrong in my original post, it had a lot of work to go). However, it now creates the tables, all the constraints, and adding a column to a table class adds the column to the table in the DB. It's loaded with of nasty magic strings and has a "hacked" together feel but currently it "just works" which I'm not a fan of, but it's a start. Now that I have a better understanding of the over all flow of it, I can put together a helper classes to clean up the query strings, make it look a little more professional. I don't want to get too carried away since I'm just going to re-write it in EF anyway.

Next step is the SqlServerObjectDatabase subclass, I've been passing in MySQL's to get it to just run, loads of errors of course but at least gave me a way to work on the Data Connection piece. Going doing the UI last of course, just been manually setting the connection string and type in the config for now. You guys are awesome for making this thing open source, this has been a really fun project.

[Graveen]

/cheers

[MrPickles]

Finally have it working =)
I hate the way it's working, but it is working. Now, I just need to learn how to put together a half-assed freeshard for testing purposes. I get some errors because it tries to insert duplicate record but the testing I have done is looking good. Let me know if anyone's interested in trying it out. I didn't do anything to the UI, I just hard-coded the DB type and connection string into the config. I won't post anymore updates on here so I don't keep bumping this thread up, just excited .
Once I feel it's in a stable place and has had thorough testing. I'll submit it as a contribution and you can all determine if it's something you want. But in the mean-time, let me know if any of you want a copy of my solution.

[Graveen]

Thank you MrPickles, nice gap

[Leodagan]

You should open a quick and dirty GitHub repository or any other kind of public code repo to share your current work...

Telling "I have something to test", "I won't post here anymore" and "anyone wanting a copy needs to ask me" won't appeal to voluntary help

It's way easier to review and test this kind of update with whole project code to resolve object type and methods references...

And by the way you should link to some quick start guide to install a MS-SQL RDBMS ?

[MrPickles]

You should open a quick and dirty GitHub repository or any other kind of public code repo to share your current work...

Telling "I have something to test", "I won't post here anymore" and "anyone wanting a copy needs to ask me" won't appeal to voluntary help

It's way easier to review and test this kind of update with whole project code to resolve object type and methods references...

And by the way you should link to some quick start guide to install a MS-SQL RDBMS ?

I was just more or less posting this because I was excited I'm able to actually get it running and playable with SQLServer. At the moment, it's not quite ready for me to start asking for volunteers for testing yet was just offering if someone wanted to take a look, but I get what you are saying . Once I feel it's ready and want to request people's help testing, I'll open up a new thread on the contribution side with a link to download my copy. I need to put together a somewhat functional freeshard together first to test. I don't really get to play Dark Age too much anymore, so I have to look at what other people have done to get a feel for how it's used. Any one-off situations you can think of that normally wouldn't be caught in testing? I know I can't catch every scenario but I know nothing about making a freeshard so any tips, hints, or gotcha's you can think of would be helpful. (I love that little register whelp on Freyad btw, reminds me of navi haha.)

Thank you!

[lalacurf121]

Now, I just need to learn how to put together a half-assed freeshard for testing purposes.
วิธีแทงเดิมพันเกมฟุตบอล