Wiki ⇒ v9 Developer's Manual ⇒ Installer ⇒ data.inc ⇒ CPG Dragonfly™ CMS
Wikiv9 Developer's ManualInstaller ⇒ data.inc

13.1. 2: data.inc Parent


This is the file containing the module database structure and module's data records and there is only one place where this file can exists modules/Example/sql/data.inc. The table below illustrates logic and main principles used while projecting and writing the file structure.
PostgreSQL users, please forgive us, if you see to much of MySQL slang in our naming.
With this method will be easier to compare existing table structure and to automatically add, modify or delete the table data or table structure. This method is used by the core installer with great results and a very low maintenance for upgrades steps.

MySQL columnsPostgreSQL columns

SHOW COLUMNS FROM cms_modules

SELECT column_name as field, data_type as type,
       character_maximum_length as lenght,
       is_nullable as null, column_default as default
FROM information_schema.columns
WHERE table_name='cms_modules'
ORDER BY ordinal_position

+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| mid          | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| title        | varchar(255)     | NO   | MUL | NULL    |                |
| custom_title | varchar(255)     | YES  | MUL | NULL    |                |
| version      | varchar(10)      | YES  |     | NULL    |                |
| active       | tinyint(1)       | NO   | MUL | 0       |                |
| view         | int(11)          | NO   |     | 0       |                |
| inmenu       | tinyint(1)       | NO   |     | 1       |                |
| pos          | tinyint(4)       | NO   |     | 0       |                |
| uninstall    | tinyint(1)       | NO   |     | 0       |                |
| cat_id       | tinyint(4)       | NO   |     | 0       |                |
| blocks       | tinyint(4)       | NO   |     | 1       |                |
+--------------+------------------+------+-----+---------+----------------+
+---------------------------------------------------------------------------------------------+
| field        | type              | lenght | null | default                                  |
+--------------+-------------------+--------+------+------------------------------------------+
| mid          | integer           |        | NO   | nextval('cms_modules_mid_seq'::regclass) |
| title        | character varying | 255    | NO   | ''::character varying                    |
| custom_title | character varying | 255    | YES  | ''::character varying                    |
| version      | character varying | 10     | YES  | ''::character varying                    |
| active       | smallint          |        | NO   | 0::smallint                              |
| view         | integer           |        | NO   | 0                                        |
| inmenu       | smallint          |        | NO   | 1::smallint                              |
| pos          | smallint          |        | NO   | 0::smallint                              |
| uninstall    | smallint          |        | NO   | 0::smallint                              |
| cat_id       | smallint          |        | NO   | 0::smallint                              |
| blocks       | smallint          |        | NO   | 1::smallint                              |
+--------------+-------------------+--------+------+------------------------------------------+


$tables
['modules'] = array(
    
'mid'          => array('Type' => 'SERIAL4',      'Null' => 0),
    
'title'        => array('Type' => 'VARCHAR(255)''Null' => 0'Default' => ''),
    
'custom_title' => array('Type' => 'VARCHAR(255)''Null' => 1'Default' => ''),
    
'version'      => array('Type' => 'VARCHAR(10)',  'Null' => 1'Default' => ''),
    
'active'       => array('Type' => DBFT_BOOL,      'Null' => 0'Default' => 0),
    
'view'         => array('Type' => DBFT_INT4,      'Null' => 0'Default' => 0),
    
'inmenu'       => array('Type' => DBFT_BOOL,      'Null' => 0'Default' => 1),
    
'pos'          => array('Type' => DBFT_INT1,      'Null' => 0'Default' => 0),
    
'uninstall'    => array('Type' => DBFT_BOOL,      'Null' => 0'Default' => 0),
    
'cat_id'       => array('Type' => DBFT_INT1,      'Null' => 0'Default' => 0),
    
'blocks'       => array('Type' => DBFT_INT1,      'Null' => 0'Default' => 1),
);

As you might notice the logic behind the array structure is to inject the installer with the same hierarchy of data that an RDMBS would return when asking for the same data type.

It drastically reduce the stress previously addressed to strings matching and strings replacements. It does still match and replace but in a very limited manner since the data is already ordered and well defined.
MySQL indexesPostgreSQL indexes

SHOW INDEX FROM cms_modules

SELECT
       ic.relname AS index_name,
       ic.reltuples as called,
       bc.relname AS tab_name,
       ta.attname AS column_name,
       i.indisunique AS unique_key,
       i.indisprimary AS primary_key
FROM
       pg_class bc,
       pg_class ic,
       pg_index i,
       pg_attribute ta,
       pg_attribute ia
WHERE
         (bc.oid = i.indrelid)
  AND (ic.oid = i.indexrelid)
  AND (ia.attrelid = i.indexrelid)
  AND (ta.attrelid = bc.oid)
  AND (bc.relname = 'cms_modules')
  AND (ta.attrelid = i.indrelid)
  AND (ta.attnum = i.indkey[ia.attnum-1])
ORDER BY index_name, tab_name, column_name

+------------+--------------+--------------+----------+------+------------+
| Non_unique | Key_name     | Column_name  | Sub_part | Null | Index_type |
+------------+--------------+--------------+----------+------+------------+
|          0 | PRIMARY      | mid          |     NULL |      | BTREE      |
|          1 | title        | title        |     NULL |      | BTREE      |
|          1 | custom_title | custom_title |     NULL | YES  | BTREE      |
+------------+--------------+--------------+----------+------+------------+
+--------------------------+--------------+------------+-------------+
| index_name               | column_name  | unique_key | primary_key |
+--------------------------+--------------+------------+-------------+
| cms_modules_pkey         | mid          | True       | True        |
| cms_modules_title        | title        | False      | False       |
| cms_modules_custom_title | custom_title | False      | False       |
+--------------------------+--------------+------------+-------------+
Note: some attributes have been voluntary stripped to keep the reading easy as possible


$indexes
['modules'] = array(
    
'PRIMARY' => array('unique' => 1'type' => 'BTREE',
        
=> array('name' => 'mid''Sub_part' => '''Null' => 0)),
    
'title' => array('unique' => 0'type' => 'BTREE',
        
=> array('name' => 'title''Sub_part' => '''Null' => 0)),
    
'custom_title' => array('unique' => 0'type' => 'BTREE',
        
=> array('name' => 'custom_title''Sub_part' => '''Null' => 0))
);

Even on this case we apply the same logic above explained but with a little difference.

In the example on the left side we have been demonstrating one key binding to a single column. Whats if our key needs to be set for more then one column?


$indexes
['log'] = array(
    ...
    ...
    ...
    'error' 
=> array('unique' => 0'type' => 'BTREE',
        
=> array('name' => 'log_ip''Sub_part' => '''Null' => 0),
        
=> array('name' => 'log_mod_id''Sub_part' => '''Null' => 0),
        
=> array('name' => 'log_type''Sub_part' => '''Null' => 0),
        
=> array('name' => 'log_uri''Sub_part' => '''Null' => 0),
        
=> array('name' => 'log_user_id''Sub_part' => '''Null' => 0))
);

This example is taken from the cms_log indexes table and as we can see it use an unique key called error and will be set for log_ip, log_mod_id, log_type, log_uri and log_user_id.

Important: please note that log_ip, log_mod_id, log_type, log_uri and log_user_id are alphabetically ordered this is why some RDBMS apply the same king of ordering for columns names. If you don't follow this order the indexes will be repacked for every future upgrade! No data will be lost, but if that happen on a big table then we can all go to have a pizza while the upgrade complete.

work in progress ....

Created: Friday, February 29, 2008 (14:23:42) by NanoCaiordo
Updated: Saturday, March 01, 2008 (03:46:40) by NanoCaiordo