Commit #3328 - SQLite, May sound Crazy...

[Leodagan]

Hello,

It's been long time I haven't experimented something around DOL Data warehouse...

So it may sound crazy but I was bored this afternoon, and tried to connect DOL to a SQLite database...

I copied the class MySQLObjectDatabase to SQLiteObjectDatabase, added some link to a freshly downloaded SQLite embedded C# Library and x86 database engine.

Updated some "switch" case to handle "SQLITE" config, and started the shard to check for SQL Errors !

I fixed a few, and most of things were running nice !

Latest version of SQLite can handle Write Ahead Log (Journaling), Memory Caching, Pooling, Asynchronous Commit, Foreign Keys, Auto Increment, Multiple columns INDEX and UNIQUE CONSTRAINTS etc...

There is still some code around Select, Insert, Create that I dumb edited with ugly if statement to use some SQLite Special Case !

After a couple of hours I'm able to launch a fresh shard with an empty db auto-created with no exceptions or Errors and able to connect.

If anybody is interested I'm gonna test this some more and commit it gracefully to DOL Core so it will finally have an other Database provider (and a pretty easy to use !)

[Tolakram]

Very cool.

[Leodagan]

Actually SQLite support another feature : in memory database...

This could allow DOL to start straight ahead with a default configuration with no pre-requisite of MySQL install and still allow all "SQL Database feature" in DataObject Methods (as opposed to XML database)

and it could be useful for "sanboxing", an in-memory database shard will retrieve its formal status after each restart !

[Leodagan]

First Real Test with an own Database :

- Convert MySQL Dump to a SQLite Dump is not trivial, the opposite may be easier, the hardest work is around "create table" which can be easen if you only use a data dump and DOL autocreated schema at the same version. I may convert the current latest public DB data to fit with current Core Tables creation...

- Importing is a bit slow on SQLite at least on my Window Binary, even disabling all I/O sync and allowing 2GB of memory cache on a SSD hosted SQLite file, this is nothing near MySQL performance... But there could be all kind of tool for importing data into SQLite and the binary used for each of these tool can have different performance as SQLite is a binary embedded DB with just compatible file format across binaries...

- DOL startup gets really long with a full world shard, up to 8 minutes, there are some field that looks wrongly read (Parsing Errors around Dataquest...) maybe something to improve in data dump. SQLite "memory usage" is added to Core Memory, so I grow from about 550MB to 700MB

- There is some troubles around Connection Pooling, it may depend on the Binary used, or maybe some code I need to fix... Disabling Connection Pooling solve the trouble but the embedded database perform worse !

- Database file with a full world is around 450MB, no other file is created at all, and it can be created in DOL folder by configuration !

[Graveen]

Excellent Leodagan ! As you mention, this is a step forward to provide instant run.

Previously we got XML, but we dropped support to focus on MySQL. This was the only thing interesting it was providing: the ability to instant-run DOL.

Kudos

[Leodagan]

Ok for trying out I committed my patch to DOL trunk...

Commit #3328

I have lightly tested the SQLite Update, but the way I wrote it I'm sure it shouldn't harm anything.

here is the commit patch :

Code: Select all

Added: ConnectionType SQLITE Updated: DataConnection, made it able to handle specific SQLite queries, reader, and create statement, actually using in-code if statement to switch behavior from MySQL, MySQL is handled like a default behavior to prevent regression ! Updated: DataConnection, removed MySQL connection Pooling above driver to allow for flawless Driver Pooling and prevent dead connection idling in the DOL pool... Updated: MySQLObjectDatabase using IDataReader Interface, adding DOL header. Added: SQLiteObjectDatabase object to handle SQLite database. Updated: ObjectDatabase.Escape to virtual to allow other database implementation override Added: SQLite 1.0.84.0 precompiled DLL for x86 (to allow compatibility accross arch), should be replaced for Linux or other platform. Updated: ConsoleStart, no more summon DOLConfig on empty config dir ! Continue starting using default SQLite config... Updated: GameServerConfiguration, XML removed from default config file to use SQLite on startup... Updated: DOLDatabase.csproj to updated DLL references and new source files.

You can now start the server straight out after building with a fresh auto-created SQLite database (at the same place the default xml_db should be)

SQLite being a full fleged SQL compatible DataObject, the server start right away building his basic table schema, and starting a pretty empty shard, which you can connect to !

Some tool to browse SQLite : SQLite Browser (pretty basic)

[Leodagan]

And here is the "demo" public db as SQLite :

http://svn.code.sf.net/p/dolpubdb/code/ ... a_V3.1.sql

Startup time : 15 min
Memory Usage : 385 MB

[Leodagan]

I posted a Ticket about Pooling deadlocks @System.Data.SQLite.org : https://system.data.sqlite.org/index.ht ... d0d1d444d3

The discussion may get a bit "low level" for me

I'm starting to review the code around packet handling to see if there is any IDisposable object, but I didn't find anything...

[Graveen]

well at least we could beta test, thank you

[Leodagan]

Newer Revision 3388 :

Fixed some errors when building data tables using relations with SQLite Handler. (New Career System use a lot of these...)

I don't know if anyone is trying to build some shard above this ?

For my part I'm trying to make a "startup.sql" to load in a sqlite db, launching fast "testing" shards (But I'm still building these sql scripts from MySQL database)