Revision 1.9.7.3583 - Database Handler Revamp

View and discuss the latests SVN commits, to ensure the best efficiency in our work.

Moderators: Support Team, Developer Team

Revision 1.9.7.3583 - Database Handler Revamp

Postby Leodagan » Mon May 23, 2016 1:03 pm

Database Handler(s) Revamp

Former DOL Database Code is pretty old and never changed much since migration from XML repository to MySQL driver, I tried to add support for SQLite some time ago, I managed to get some code working with both SQL drivers and some other parts needed complete duplication to handle differences...

I wanted to improve the SQLite driver implementation as I tend to use it a lot for standalone and portable development environment (Compiling Dawn of Light and Running server from a usb key !), I also wanted to implement low-level Unit Tests to track correctly upcoming change of the DOL Database API and make sure the Database Driver doesn't do anything funny !

So I ended up making a huge patch, which is always a long task and error prone...

Here is a List of all the major changes from this revamp :

  • Removed XML files repository support completely, and cleaned up unused Cache Logic and DataSets Handling.

  • Optimized DataTable Handler to have the full Table Object Model and Reflection Methods available when writing drivers implementation. This remove the needs for a lot of Reflection hacks in every parts of the Database Library !

  • Changed the hierarchy logic for the Database API to target SQL connection instead of Static File Access, File repository could still be used if anybody implement a file-based driver that mimic SQL behavior but it's not the default implementation logic.

  • Added a collection of Unit Tests for Database to be run with Continuous Integration, Unit Tests default to SQLite driver to run without MySQL Engine, but can be explicitly run against a local MySQL Database if needed...

  • New "Vectorized" Database API, Vectorized Methods are designed to handle whole collection of DataObjects at once, trying to group database access using parametrized queries which can reach greater throughput than query string. This can bring performance improvement up to 20 times faster when filling relations.

  • Update of XML Auto Updater to use the new Vectorized API reducing server first startup time.

  • Implementation of Multiple Field Unique Constraints, matching a group of DataObject Fields as Unique when all their values are matched against another record Fields, this can bring improvement for Table like Merchant Item which should have unique page and slot pair for each ItemList

  • New Table View Handler, can create views or handle existing views, can support update if the view has a real Table base, can be used as an easy mechanism to subclass DataObject with new Relations.

  • Complete Rewrite of the MySQL Driver Abstraction, using optimized string builders for queries and reflection methods resolved from datatable handlers, it can now flawlessly alter table primary key of any kind, supports index alteration as well to match DataObjects Indexes or Constraints.

  • Complete Rewrite of the SQLite Driver Abstraction, no more tied to MySQL Implementation, can alter table using Code logic to create a new table and import old data then destroy old table, workaround implemented to match SQLite behavior to MySQL behavior.

To sum up : everything was rewritten except the Database Interface to keep the compatibility with DOL without changing Game Server Code !

This Revamp was made to get obsolete code out of DOL Database code, clean up the mess of previous updates, make the code readable for future improvements and other driver implementations, add some new features like unique constraints, better table alteration support, get the SQLite backend up to production level, and lastly get some dramatic speed improvement !

! Important Change !
To prevent spamming your log with unfiltered debug message please update your logconfig.xml

Code: Select all
<log4net>
...
   <logger name="DOL.Database.Handlers">
      <level value="INFO" />
   </logger>
...
</log4net>


! New Unique Constraints !
If you happen to have Data Duplicate when updating to this new Database Handler here are the queries I used to solve this on Storm RvR

Code: Select all
UPDATE (SELECT COUNT(*) AS `Rows`, `ItemTemplate_ID` FROM `itemtemplate` GROUP BY `ItemTemplate_ID` Having `Rows` > 1) as `duplicates`
JOIN `ItemTemplate` ON (`ItemTemplate`.`ItemTemplate_ID` = `duplicates`.`ItemTemplate_ID`) SET `ItemTemplate`.`ItemTemplate_ID` = UUID() WHERE 1

UPDATE (SELECT COUNT(*) AS `Rows`, `CraftedItem_ID` FROM `CraftedItem` GROUP BY `CraftedItem_ID` Having `Rows` > 1) as `duplicates`
JOIN `CraftedItem` ON (`CraftedItem`.`CraftedItem_ID` = `duplicates`.`CraftedItem_ID`) SET `CraftedItem`.`CraftedItem_ID` = UUID() WHERE 1
User avatar
Leodagan
Developer
 
Posts: 1200
Joined: Tue May 01, 2012 9:30 am
Location: Lyon

Re: [STUB] Database Handler Revamp

Postby HunabKu » Mon May 23, 2016 2:13 pm

Good luck working Leo !
"C'est l'ignorance qui apporte le chaos, pas la connaissance."
Scarlett Johansson dans "Lucy" de Luc Besson
-------------------------------------------------------------------------------
"Ignorance brings chaos, not knowledge."
Scarlett Johansson on "Lucy" by Luc Besson
User avatar
HunabKu
Developer
 
Posts: 1704
Joined: Sat Jun 18, 2011 4:48 am

Re: [STUB]Revision 1.9.7.35xx - Database Handler Revamp

Postby Leodagan » Mon May 23, 2016 3:29 pm

I just need to run some Tests against Storm RvR Backup to make sure everything is working as expected and it'll be ready soon !
User avatar
Leodagan
Developer
 
Posts: 1200
Joined: Tue May 01, 2012 9:30 am
Location: Lyon

Re: [STUB]Revision 1.9.7.35xx - Database Handler Revamp

Postby HunabKu » Tue May 24, 2016 4:06 pm

Nice works as usual !

Do you have some news about archery bug ?
"C'est l'ignorance qui apporte le chaos, pas la connaissance."
Scarlett Johansson dans "Lucy" de Luc Besson
-------------------------------------------------------------------------------
"Ignorance brings chaos, not knowledge."
Scarlett Johansson on "Lucy" by Luc Besson
User avatar
HunabKu
Developer
 
Posts: 1704
Joined: Sat Jun 18, 2011 4:48 am

Re: [STUB]Revision 1.9.7.35xx - Database Handler Revamp

Postby Graveen » Fri May 27, 2016 10:58 am

/worship

To sum up:
- either you use SQLite for quick debug/work , XML will no longer be available with this implementation
- either you use MySQL

In both way, the perfs have been vastly increased !

GOOD JOB !!!
Image
* pm me to contribute in Dawn of Light: code, database *
User avatar
Graveen
Project Leader
 
Posts: 12445
Joined: Fri Oct 19, 2007 9:22 pm
Location: France

Re: [STUB]Revision 1.9.7.35xx - Database Handler Revamp

Postby HunabKu » Fri May 27, 2016 3:39 pm

I can not wait to test it!
"C'est l'ignorance qui apporte le chaos, pas la connaissance."
Scarlett Johansson dans "Lucy" de Luc Besson
-------------------------------------------------------------------------------
"Ignorance brings chaos, not knowledge."
Scarlett Johansson on "Lucy" by Luc Besson
User avatar
HunabKu
Developer
 
Posts: 1704
Joined: Sat Jun 18, 2011 4:48 am

Re: [STUB]Revision 1.9.7.35xx - Database Handler Revamp

Postby Leodagan » Sat May 28, 2016 6:12 am

@ Graveen :

XML was already "broken" in previous Database API, there was no way to configure a game server to use it correctly, no simple methods to instantiate a Database Handler with "XML" Type, and I'm pretty sure that a lot of Game Server "queries" (Select Objects with Where Clause, Relations...) would have fail with XML database...

So you sum up well, SQLite will be effective for Developers and embedded Unit Tests, and MySQL to have a real SQL engine that can handle huge Table such as Player Inventory...

But still now "according" to Unit Tests run against both drivers I can be pretty confident that both implementations work exactly the same :)

@ HunabKu :

I still have a few "quirks" to fix, I've run into small bugs when testing with Storm Database, some Table didn't have a Primary Key at all and the new code is not trying to fix that, and other tables have "old" Primary Key Column that weren't fixed by the previous Database driver, but this new driver have trouble modifying a previous Column that was Primary Key then change the Primary Key to another Column...

Except this few bugs in "Alter Table", It should be ready soon ;)
User avatar
Leodagan
Developer
 
Posts: 1200
Joined: Tue May 01, 2012 9:30 am
Location: Lyon

Re: [STUB]Revision 1.9.7.35xx - Database Handler Revamp

Postby HunabKu » Mon May 30, 2016 8:48 am

Fine, good new :D
"C'est l'ignorance qui apporte le chaos, pas la connaissance."
Scarlett Johansson dans "Lucy" de Luc Besson
-------------------------------------------------------------------------------
"Ignorance brings chaos, not knowledge."
Scarlett Johansson on "Lucy" by Luc Besson
User avatar
HunabKu
Developer
 
Posts: 1704
Joined: Sat Jun 18, 2011 4:48 am

Re: Revision 1.9.7.3583 - Database Handler Revamp

Postby Leodagan » Sat Jun 18, 2016 11:29 am

Finally Released as Revision 1.9.3583

PR : https://github.com/Dawn-of-Light/DOLSharp/pull/16
User avatar
Leodagan
Developer
 
Posts: 1200
Joined: Tue May 01, 2012 9:30 am
Location: Lyon

Re: Revision 1.9.7.3583 - Database Handler Revamp

Postby HunabKu » Sat Jun 18, 2016 12:14 pm

Realy nice Leodagan, thanks !
"C'est l'ignorance qui apporte le chaos, pas la connaissance."
Scarlett Johansson dans "Lucy" de Luc Besson
-------------------------------------------------------------------------------
"Ignorance brings chaos, not knowledge."
Scarlett Johansson on "Lucy" by Luc Besson
User avatar
HunabKu
Developer
 
Posts: 1704
Joined: Sat Jun 18, 2011 4:48 am

Re: Revision 1.9.7.3583 - Database Handler Revamp

Postby Graveen » Sat Jun 18, 2016 1:36 pm

Awesome!!!!! NAO at the apero, but i salute the idea
Image
* pm me to contribute in Dawn of Light: code, database *
User avatar
Graveen
Project Leader
 
Posts: 12445
Joined: Fri Oct 19, 2007 9:22 pm
Location: France


Return to DOL SVN Commits

Who is online

Users browsing this forum: No registered users and 1 guest