Paul Hollands: Building Application DB Tables with tables.ini and create

« Using prototype.js For Faster JS Development and AJAX | Main | Mac Cufflinks »

April 15, 2009

Building Application DB Tables with tables.ini and create_database.php

The framework requires that each table in the database has a core set of columns in order to work. Also, there are some rules about how tables should be defined.

Primary and foreign key columns should be set as int(10) and listed at the top.
All columns apart from the primary key should be set as null by default.
Date columns are set as int(20) and dates should be inserted as unix timestamps.
Varchar columns are always set as varchar(255).
Enumerations shouldn't be used with the framework.
Every table should contain an expired_date column which will normally be set to null. If you want to remove a row from view, set the expired_date to a timestamp.

For speed tables for an application can be defined with a couple of framework tools. In each application directory, there is an /sql sub-directory. You will find two files in here, create_database.php and tables.ini.

The script parses the tables.ini file which contains the table definitions in Windows .ini format and prints the SQL necessary to create the tables out to the browser.

N.B. The script does not create the tables, it only generates the SQL to do so. This can then be pasted into PHPMyAdmin.

The tables.ini file looks like this:

[table_list]
panel = 0
panel_group = 0
panel_content = 0
panel_location = 0


; The above defines the tables to be created. The 1 or 0 value is 
; significant. 1 means that a corresponding type table is created and 
; a foreign key column for it is included in the main table.
; 0 means that no type table is created. Also the type table foreign
; key column is dropped after the table is created thus:
; alter table `panel` drop `panel_type_id`;


[panel_location]
panel_location_name = text;

[link_tables]
panel:panel_group = 0
user:panel_group = 0
panel:panel_content = 0
panel_group:panel_content = 0


; If you want to create link tables between others defined herein, you 
; can using the syntax shown above. Table names are separated by a 
; colon.


[panel]
panel_location_id = "int(10)"
panel_name = "varchar(255)"
panel_description = text
ip_address = "varchar(255)"


; Each table has a section which defines extra columns to be included
; above and beyond the default. In the resulting SQL these columns are 
; indented to differenciate them from the defaults:
;
;    drop table if exists `panel`;
;    create table `panel`
;    (
;    `panel_id` int(10) primary key auto_increment not null, 
;    `panel_type_id` int(10) default null, 
;	`panel_location_id` int(10) default null,
;	`panel_name` varchar(255) default null,
;	`panel_description` text default null,
;	`ip_address` varchar(255) default null,
;    `insert_by` int(10) default null,
;    `insert_date` int(20) default null,
;    `update_by` int(10) default null,
;    `update_date` int(20) default null,
;    `expired_date` int(20) default null );
; 
 


[panel_group]
panel_group_name = "varchar(255)"
panel_group_description = text

[panel_content]
panel_id = "int(10)"
panel_group_id = "int(10)"
content_title = text
content_description = text
content_html = text
content_html_url = text
content_stylesheet_url = text
content_image_url = text
content_image_type = "varchar(255)"
content_image_name = text
content_image_size = "int(20)"
content_image_data = longblob
priority "int(1)"
start_date = "int(20)"
end_date = "int(20)"

This generates the following output:



    drop table if exists `panel`;
    create table `panel`
    (
    `panel_id` int(10) primary key auto_increment not null, 
    `panel_type_id` int(10) default null, 
	`panel_location_id` int(10) default null,
	`panel_name` varchar(255) default null,
	`panel_description` text default null,
	`ip_address` varchar(255) default null,
    `insert_by` int(10) default null,
    `insert_date` int(20) default null,
    `update_by` int(10) default null,
    `update_date` int(20) default null,
    `expired_date` int(20) default null );



alter table `panel` drop `panel_type_id`;




    drop table if exists `panel_group`;
    create table `panel_group`
    (
    `panel_group_id` int(10) primary key auto_increment not null, 
    `panel_group_type_id` int(10) default null, 
	`panel_group_name` varchar(255) default null,
	`panel_group_description` text default null,
    `insert_by` int(10) default null,
    `insert_date` int(20) default null,
    `update_by` int(10) default null,
    `update_date` int(20) default null,
    `expired_date` int(20) default null );



alter table `panel_group` drop `panel_group_type_id`;




    drop table if exists `panel_content`;
    create table `panel_content`
    (
    `panel_content_id` int(10) primary key auto_increment not null, 
    `panel_content_type_id` int(10) default null, 
	`panel_id` int(10) default null,
	`panel_group_id` int(10) default null,
	`content_title` text default null,
	`content_description` text default null,
	`content_html` text default null,
	`content_html_url` text default null,
	`content_stylesheet_url` text default null,
	`content_image_url` text default null,
	`content_image_type` varchar(255) default null,
	`content_image_name` text default null,
	`content_image_size` int(20) default null,
	`content_image_data` longblob default null,
	`priority` int(1) default null,
	`start_date` int(20) default null,
	`end_date` int(20) default null,
    `insert_by` int(10) default null,
    `insert_date` int(20) default null,
    `update_by` int(10) default null,
    `update_date` int(20) default null,
    `expired_date` int(20) default null );



alter table `panel_content` drop `panel_content_type_id`;




    drop table if exists `panel_location`;
    create table `panel_location`
    (
    `panel_location_id` int(10) primary key auto_increment not null, 
    `panel_location_type_id` int(10) default null, 
	`panel_location_name` text default null,
    `insert_by` int(10) default null,
    `insert_date` int(20) default null,
    `update_by` int(10) default null,
    `update_date` int(20) default null,
    `expired_date` int(20) default null );



alter table `panel_location` drop `panel_location_type_id`;




    drop table if exists `panel_panel_group_link`;   
    create table `panel_panel_group_link`
    (
    `panel_panel_group_link_id` int(10) primary key auto_increment not null,
    `panel_id` int(10) default null,
    `panel_group_id` int(10) default null,
    `insert_by` int(10) default null,
    `insert_date` int(20) default null,
    `update_by` int(10) default null,
    `update_date` int(20) default null,
    `expired_date` int(20) default null );


    drop table if exists `user_panel_group_link`;   
    create table `user_panel_group_link`
    (
    `user_panel_group_link_id` int(10) primary key auto_increment not null,
    `user_id` int(10) default null,
    `panel_group_id` int(10) default null,
    `insert_by` int(10) default null,
    `insert_date` int(20) default null,
    `update_by` int(10) default null,
    `update_date` int(20) default null,
    `expired_date` int(20) default null );


    drop table if exists `user_panel_link`;   
    create table `user_panel_link`
    (
    `user_panel_link_id` int(10) primary key auto_increment not null,
    `user_id` int(10) default null,
    `panel_id` int(10) default null,
    `insert_by` int(10) default null,
    `insert_date` int(20) default null,
    `update_by` int(10) default null,
    `update_date` int(20) default null,
    `expired_date` int(20) default null );


    drop table if exists `panel_panel_content_link`;   
    create table `panel_panel_content_link`
    (
    `panel_panel_content_link_id` int(10) primary key auto_increment not null,
    `panel_id` int(10) default null,
    `panel_content_id` int(10) default null,
    `insert_by` int(10) default null,
    `insert_date` int(20) default null,
    `update_by` int(10) default null,
    `update_date` int(20) default null,
    `expired_date` int(20) default null );


    drop table if exists `panel_group_panel_content_link`;   
    create table `panel_group_panel_content_link`
    (
    `panel_group_panel_content_link_id` int(10) primary key auto_increment not null,
    `panel_group_id` int(10) default null,
    `panel_content_id` int(10) default null,
    `insert_by` int(10) default null,
    `insert_date` int(20) default null,
    `update_by` int(10) default null,
    `update_date` int(20) default null,
    `expired_date` int(20) default null );

Note the drop table statement before each creation statement. If you want to change the structure of a table after the fact, ensure you dump the data out before you do otherwise it will be destroyed.

Tags: Lightweight PHP Web Application Framework

Posted by pj at April 15, 2009 11:19 AM

Paul Hollands

April 15, 2009

Building Application DB Tables with tables.ini and create_database.php

Comments