DataObjects/Core_event_audit.php

[Pman.Core] / DataObjects / core.sql

diff --git a/DataObjects/core.sql b/DataObjects/core.sql
index 26948f2..d7fb0d3 100644 (file)
--- a/DataObjects/core.sql
+++ b/DataObjects/core.sql
@@ -33,22 +33,15 @@ ALTER TABLE `Companies` ADD COLUMN `updated_dt` datetime  NOT NULL;
 
 ALTER TABLE `Companies` ADD COLUMN   `passwd` varchar(64) NOT NULL;
 
-
-ALTER TABLE Companies 
-       ADD COLUMN dispatch_port varchar(255) NOT NULL DEFAULT '',
-       ADD COLUMN province varchar(255) NOT NULL DEFAULT '',
-       ADD COLUMN country varchar(4) NOT NULL DEFAULT '';
+ALTER TABLE Companies  ADD COLUMN dispatch_port varchar(255) NOT NULL DEFAULT '';
+ALTER TABLE Companies  ADD COLUMN province varchar(255) NOT NULL DEFAULT '';
+ALTER TABLE Companies  ADD COLUMN country varchar(4) NOT NULL DEFAULT '';
 
  
 UPDATE Companies set comptype='OWNER' where isOwner=1;
 
-
-CREATE TABLE  `core_company_type` (
-  `id` int(11)  NOT NULL AUTO_INCREMENT,
-  `name` varchar(64)  NOT NULL,
-  PRIMARY KEY (`id`)
-);
-
+#// core comapy types - use core enums (Company Type)
+DROP TABLE core_company_type;
 
 CREATE TABLE `Events` (
   `id` int(11) NOT NULL auto_increment,
@@ -63,18 +56,29 @@ CREATE TABLE `Events` (
   PRIMARY KEY  (`id`)
 ) ;
 
-
+ALTER TABLE Events ADD COLUMN person_id INT(11);
+ALTER TABLE Events ADD COLUMN remarks INT(11);
 ALTER TABLE Events CHANGE COLUMN EventID id INT(11) AUTO_INCREMENT NOT NULL;
 ALTER TABLE Events CHANGE COLUMN User person_name VARCHAR(128);
-ALTER TABLE Events ADD COLUMN person_id INT(11);
 ALTER TABLE Events CHANGE COLUMN Date event_when DATETIME;
 ALTER TABLE Events CHANGE COLUMN Event action VARCHAR(32);
 ALTER TABLE Events CHANGE COLUMN Host ipaddr VARCHAR(16);
 ALTER TABLE Events CHANGE COLUMN ItemID on_id INT(11);
 ALTER TABLE Events CHANGE COLUMN Container on_table VARCHAR(64);
-ALTER TABLE Events ADD COLUMN remarks INT(11);
 
 
+ALTER TABLE Events ADD INDEX lookup (on_id, on_table, person_id, event_when);
+
+CREATE TABLE  core_event_audit  (
+    `id` int(11)  NOT NULL AUTO_INCREMENT,
+    `event_id` int(11)  NOT NULL,
+    `name` varchar(128)  NOT NULL,
+    `old_audit_id` int(11)  NOT NULL,
+    `newvalue` BLOB  NOT NULL,
+    PRIMARY KEY (`id`),
+    INDEX `lookup`(`event_id`, `name`, `old_audit_id`)
+);
+
 
 CREATE TABLE `Group_Members` (
   `group_id` int(11) default NULL,
@@ -191,7 +195,20 @@ ALTER TABLE `Projects`  ADD COLUMN `languages` varchar(128)  NOT NULL;
 ALTER TABLE  Projects ADD COLUMN agency_id int(11)  NOT NULL DEFAULT 0 ;
 
 
+#-- we duplicate office_id and company_id here...
+#-- not sure if we should keep doing that in the new design...
+#-- we should improve our links code to handle this..
+
 
+CREATE TABLE `ProjectDirectory` (
+  `id` int(11) NOT NULL auto_increment,
+  `project_id` int(11) NOT NULL,
+  `person_id` int(11) NOT NULL,
+  `ispm` int(11) NOT NULL,
+  `role` varchar(16) NOT NULL,
+  PRIMARY KEY  (`id`)
+) ;
+ 
 
 CREATE TABLE   `Images` (
   `id` int(11) NOT NULL auto_increment,
@@ -210,12 +227,12 @@ CREATE TABLE   `Images` (
 
 
 
-ALTER TABLE images    ADD COLUMN  `width` int(11) NOT NULL default '0';
-ALTER TABLE images    ADD COLUMN  `height` int(11) NOT NULL default '0';
-ALTER TABLE images    ADD COLUMN  `filesize` int(11) NOT NULL default '0';
-ALTER TABLE images    ADD COLUMN  `displayorder` int(11) NOT NULL default '0';
-ALTER TABLE images    ADD COLUMN  `language` varchar(6) NOT NULL default 'en';
-ALTER TABLE images    ADD COLUMN  `parent_image_id` int(11) NOT NULL default '0';
+ALTER TABLE Images    ADD COLUMN  `width` int(11) NOT NULL default '0';
+ALTER TABLE Images    ADD COLUMN  `height` int(11) NOT NULL default '0';
+ALTER TABLE Images    ADD COLUMN  `filesize` int(11) NOT NULL default '0';
+ALTER TABLE Images    ADD COLUMN  `displayorder` int(11) NOT NULL default '0';
+ALTER TABLE Images    ADD COLUMN  `language` varchar(6) NOT NULL default 'en';
+ALTER TABLE Images    ADD COLUMN  `parent_image_id` int(11) NOT NULL default '0';
 
 
 
@@ -227,11 +244,9 @@ ALTER TABLE  `Images` ADD COLUMN `linkurl` VARCHAR(254) DEFAULT '' NOT NULL;
 ALTER TABLE  `Images` ADD COLUMN `descript` TEXT DEFAULT '' NOT NULL;
 ALTER TABLE  `Images` ADD COLUMN `title` VARCHAR(128) DEFAULT '' NOT NULL;
  
-CREATE TABLE  `core_image_type` (
-  `id` int(11)  NOT NULL AUTO_INCREMENT,
-  `name` varchar(64)  NOT NULL,
-  PRIMARY KEY (`id`)
-);
+#// old core image type - merged into enum.
+DROP TABLE core_image_type;
+
 
 CREATE TABLE  `i18n` (
   `id` int(11)  NOT NULL AUTO_INCREMENT,
@@ -301,12 +316,28 @@ CREATE TABLE `core_watch` (
 ) ;
 ALTER TABLE core_watch ADD INDEX qlookup (`ontable`,`onid`,`user_id`,`event`,`medium`);
 
-
+CREATE TABLE  core_notify  (
+  `id` int(11)  NOT NULL AUTO_INCREMENT,
+  `act_when` DATETIME NOT NULL,
+  act_start DATETIME NOT NULL,
+  `onid` int(11)  NOT NULL DEFAULT 0,
+  `ontable` varchar(128)  NOT NULL DEFAULT '',
+  `person_id` int(11)  NOT NULL DEFAULT 0,
+  `msgid` varchar(128)  NOT NULL  DEFAULT '',
+  `sent` DATETIME  NOT NULL,
+  `event_id` int(11)  NOT NULL DEFAULT 0,
+  
+  PRIMARY KEY (`id`),
+  INDEX `lookup`(`act_when`, `msgid`)
+);
+ALTER TABLE core_notify CHANGE COLUMN bounced event_id INT(11) NOT NULL DEFAULT 0;
  
+ALTER TABLE core_notify ADD COLUMN  act_start DATETIME NOT NULL;
+
 
 # - used by email / tracker to handle alises - we have to be carefull adding to this table...
 
-CREATE TABLE `core_person_aliases` (
+CREATE TABLE `core_person_alias` (
   `id` int(11)  NOT NULL AUTO_INCREMENT,
   `person_id` varchar(128) DEFAULT NULL,
   `alias` varchar(254) NOT NULL,