New version of coreupdater module was released yesterday, and it contains one new functionality I'd like to talk about a little bit:
Database schema comparator and migration
What does it mean? It means that core updater will now look into php codebase and extract metadata from object model classes (like Product, Customer, or Order). These metadata describes exactly how database should look like -- what database tables should exists, what columns should they contain. It describes primary or foreign keys, column type, default values, collations, etc..
Core updater will use these collected information and compare them against your actual database schema. And if there are some differences, it will report them. And it will also allow you to fix them.
This essentially makes php codebase single source of truth in regard to database structure.
When you install your system from scratch you shouldn't see any database differences. But those of you that are running your thirtybees system for a long time now will see a lot of differences. It could happen for many reasons
we developers tend to forget about implementing respective sql migration when we change php code
when we actually implement such migration script, it can contain errors. After all, php devs aren't usually mysql experts
also, some modules can change database schema for their needs
Why do these differences matter so much? That's because PHP code operates under some assumptions. For example, it expects that order reference code can contain 11 characters. The php code that generates unique reference code for order can fail terribly if the actual database column can store only 10 characters. The error can occur right there during reference code generation (then it's easy to find). But it can happen much later. With these data consistency issues it's hard to figure out how they happend, and how to fix them.
By aligning your database structure with expected database schema, you are much closer to (my favourite) vanilla thirtybees installation. Thirtybees core code will run more smoothly in such environment. I'm not saying it will not run OK on modified database schema, just that there can be some very strange behaviour sometimes.
How to use this new tool
Please be aware this is experimental feature, and requires some database knowledge. It's not very user friendly. And some messages will probably scare you -- but that's intended behaviour. It is very powerfull tool, but we should use it very carefully.
There is new tab named Database schema (for developers). This tab will display you all (relevant) database differences.
Note that thirtybees core needs to support this functionality. At the moment, this is supported in bleeding edge only. In current version 1.1.0 or older, this tab will simply inform you that the database comparison functionality is not supported. So, if you want to test this new functionality, please migrated to bleeding edge.
This is how the list of database differences looks like. You should review all differences, starting with those marked as critical
While it says critical, the reality is usually not so grim. Obvisouly, missing columns or tables are indeed very serious issues, and you should fix these immediately. But different column type is not such a big deal.
All database differences can be fixed. You can simply click on Apply fix button to adjust database.
Please make sure you understand what this means!!! Some database differences can be actually valid and wanted. For example, some module might have changed database structure of core table in order to provide some additional functionality. *Fixing* this table back to original state might break the module. One such module is *Multiple feature values* -- this module drops unique key for one core database table in order to actually save multiple feature values into the database.
If unsure, please ask here -- it also gives us opportunity to learn more about different type of customizations.
And some video: