Mittwoch, 3. Juni 2009

HTML pages for tracking tables


I am writing some html forms for the tracking mechanism. First we need pages for enabling / disabling tracking functionality for tables. So I created a first draft for a table with activated tracking. My plan for this page is to list all versions of the selected table, if there is any. Secondly it will be possible
to create a new version (tracking for previous version is then deactived) and the user can make a selection of data definition commands.

Modelling pma_tracking

For the tracking mechanism we need a table to storage information about the tracked database and table manipulations. I want to differentiate between data definition (schema) and data manipulation commands. So, my first step was to develop a simple table named pma_tracking which I can use for my tracking operations later:

CREATE TABLE IF NOT EXISTS `pma_tracking` (
`db_name` varchar(64) collate utf8_bin NOT NULL,
`table_name` varchar(64) collate utf8_bin NOT NULL,
`version` int(10) unsigned NOT NULL,
`date_created` datetime NOT NULL,
`date_updated` datetime NOT NULL,
`schema_snapshot` text collate utf8_bin NOT NULL,
`schema_sql` text collate utf8_bin,
`data_sql` text collate utf8_bin,
`tracking` set('UPDATE','REPLACE','INSERT','DELETE','TRUNCATE','CREATE DATABASE','ALTER DATABASE','DROP DATABASE','CREATE TABLE','ALTER TABLE','RENAME TABLE','DROP TABLE','CREATE INDEX','DROP INDEX','CREATE VIEW','ALTER VIEW','DROP VIEW') collate utf8_bin default NULL,
PRIMARY KEY (`db_name`,`table_name`,`version`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=COMPACT;



This table layout based on a proposal by a SF feature requester. I am not sure if it is a good idea to use "set" as datatype (it's not SQL standard), but for a first version I will use it.