Revision 1.9.7.3583 - Database Handler Revamp
PostPosted: 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 :
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
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
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.
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>
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
