Store database structure in unit configs [5.3.0-B1]

The database is used for storing majority of user entered data (except user uploaded files). Almost each database table has associated unit, that is responsible for reading/writing data from it.

Following is missing however:

• the unit isn't aware/responsible for the indexes added to associated table
• cross-table relations are not stored anywhere (even in database itself)
• adding new fields to database is not error-prone, because:
  1. developer needs to switch to DB application (Navicat, Sequel Pro, phpMyAdmin)
  2. developer needs to enter several values to configure new field (field name, field type, field value size, null/not null, default value)
  3. developer needs to go to "System Tools" section in Admin Console
  4. developer needs to type in unit name to see "Fields" array content
  5. developer needs to copy/paste relevant changed field declarations from there to the unit config in IDE

Solution

1. describe database table columns in "Fields" array:
   1. make "db_type" field option used to describe database type (e.g. "varchar(255)") required
   2. add new optional "db_extra" field option of array type, where "auto_increment" and other db column options are defined

2. describe index structure in the new "Indexes" unit config option like so (the field specified in "IDField" of unit config is automatically assigned an "auto_increment" db extra and added as primary key): 

   'Indexes' => array(
   	'IDX_INDEX_NAME' => array(
   		'columns' => array('DBColumn1', 'DBColumn2'),
   		'unique' => true,
   	),
   ),

3. create the "in-portal db:sync" command, that will:
   1. create missing database tables
   2. change structure of existing tables to match one from unit config (but won't delete columns/tables, because of performance)
   3. integrate code, that creates multi-lingual columns (e.g. "l1_Name") into this command
4. replace "Rebuild Multilingual Fields" action on "System Tools" page with "Rebuild Database Structure" action that would call above created command
5. run above created command before SQL migrations during deploy

6. if table column has association with another database table, then define it via new "foreign_key" field option (won't be used for now) like so: 

   'Fields' => array(
       'ItemResourceId' => array(
           'type' => 'int',
           ...,
           'default' => null, 'foreign_key' => 'l:ResourceId'
       ),
   ),

Related Discussions

• Improvements of database backup/restore

Related Tasks