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. make "db_type" field option (in "Fields" array) contain pseudo database type, instead of actual MySQL data type:

   1. date - "INT(10) NULL DEFAULT NULL"

   2. money - "DECIMAL(20,6) NULL DEFAULT NULL"

   3. int32 - "INT(10)"

   4. int64 - "BIGINT(20)"

   5. auto - "INT(10)" + auto-increment
   6. auto64 - "BIGINT(20)" + auto-increment
   7. text255 - "VARCHAR(255) NOT NULL DEFAULT ''"
   8. text - "text NULL DEFAULT NULL"

   9. and so on

2. database table columns must be specified in "Fields" array first:
3. only, when "db_type" field option is specified for all fields in the table, then auto-adjust that table

4. 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, ), ),

5. 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
6. add "OnBeforeTableStructureChange" and "OnAfterTableStructureChange" events, that will:
   1. be called before/after each column is added/changed
   2. have "old_schema" event parameter, when changing column definition (fragment of ALTER TABLE statement relevant to affected column)
   3. have "new_schema" event parameter, when adding/changing column definition (fragment of ALTER TABLE statement relevant to affected column)
   4. have "column_name" event parameter
   5. allow developer to write universal code to migrate existing data in the column using PHP code
7. replace "Rebuild Multilingual Fields" action on "System Tools" page with "Rebuild Database Structure" action that would call above created command
8. run above created command before SQL migrations during deploy

9. 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' ), ),

Problems

1. existing unit configs might be already not in sync with db and attempting to mass convert db based on unit configs might make things worse
2. none of unit configs have "db_type" and we have lots of tables/fields to populate them automatically
3. when adding new fields via "OnAfterConfigRead" event there is no way to specify position at which they will be added into the table; specifying position of each field is time consuming

Related Discussions

• Improvements of database backup/restore

Related Tasks

Tasks, associated with this discussion.