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 about the indexes added to associated table
• cross-table relations are not stored anywhere (even in database itself)

Solution

1. use meta types, that would internally map to actual MySQL data types (better create meta types from currently used MySQL data types), e.g.:
   • date - "INT(10) NULL"
   • money - "DECIMAL(20,4) NULL"
   • int32 - "INT(10)"
   • int64 - "BIGINT(20)"
   • and so on

2. describe index structure in the "DBIndexes" unit config option like so: 

   'DBIndexes' => 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

4. if table column has association with another database table, then define it via new "foreign_key" field option like so: 

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

Related Discussions

• Improvements of database backup/restore

Related Tasks