X-Git-Url: http://git.roojs.org/?a=blobdiff_plain;f=DataObjects%2Fcore.sql;h=5a54b8dd09f9760ffc8113f9fd9c5a145ae41849;hb=a494a3ac8bb3ea4d6929493b01657972d722e56e;hp=6a806454a7e1fd7c8865cab18404c8a2d9854067;hpb=703ccb6228f9d77203b4279ee15b046c944ec512;p=Pman.Core diff --git a/DataObjects/core.sql b/DataObjects/core.sql index 6a806454..5a54b8dd 100644 --- a/DataObjects/core.sql +++ b/DataObjects/core.sql @@ -16,7 +16,7 @@ ALTER TABLE Companies ADD COLUMN email varchar(128) default NULL; ALTER TABLE Companies ADD COLUMN isOwner int(11) default NULL; ALTER TABLE Companies ADD COLUMN logo_id INT(11) NOT NULL DEFAULT 0;; ALTER TABLE Companies ADD COLUMN background_color varchar(8) NOT NULL; -ALTER TABLE Companies ADD COLUMN comptype varchar(8) NOT NULL DEFAULT ''; +ALTER TABLE Companies ADD COLUMN comptype varchar(32) NOT NULL DEFAULT ''; ALTER TABLE Companies ADD COLUMN url varchar(254) NOT NULL DEFAULT ''; ALTER TABLE Companies ADD COLUMN main_office_id int(11) NOT NULL DEFAULT 0; ALTER TABLE Companies ADD COLUMN created_by int(11) NOT NULL DEFAULT 0; @@ -30,7 +30,8 @@ ALTER TABLE Companies ADD COLUMN country varchar(4) NOT NULL DEFAULT ''; ALTER TABLE Companies CHANGE COLUMN isOwner isOwner int(11); -#postres +ALTER TABLE Companies CHANGE COLUMN comptype comptype VARCHAR(32) DEFAULT ''; +-- postres ALTER TABLE Companies ALTER isOwner TYPE int(11); ALTER TABLE Companies ALTER owner_id SET DEFAULT 0; ALTER TABLE Companies ALTER url SET DEFAULT ''; @@ -41,7 +42,7 @@ ALTER TABLE Companies ADD INDEX name_lookup (name); UPDATE Companies set comptype='OWNER' where isOwner=1; -#// core comapy types - use core enums (Company Type) +-- // core comapy types - use core enums (Company Type) DROP TABLE core_company_type; @@ -61,13 +62,15 @@ ALTER TABLE Events ADD COLUMN ipaddr varchar(16) default NULL; ALTER TABLE Events ADD COLUMN on_id int(11) default NULL; ALTER TABLE Events ADD COLUMN on_table varchar(64) default NULL; ALTER TABLE Events ADD COLUMN person_id int(11) default NULL; +ALTER TABLE Events ADD COLUMN person_table varchar(64) default NULL; + ALTER TABLE Events ADD COLUMN remarks text ; -#very old code.. +--#very old code.. ALTER TABLE Events CHANGE COLUMN EventID id INT(11) AUTO_INCREMENT NOT NULL; -# this are for pre-postgres support code.. +--# this are for pre-postgres support code.. ALTER TABLE Events CHANGE COLUMN User person_name VARCHAR(128); ALTER TABLE Events RENAME COLUMN User TO person_name; ALTER TABLE Events CHANGE COLUMN Date event_when DATETIME ; @@ -81,7 +84,16 @@ ALTER TABLE Events ADD INDEX lookup (on_id, on_table, person_id, event_when); ALTER TABLE Events ADD INDEX lookupf (on_id, action, on_table, person_id, event_when); +ALTER TABLE Events ADD INDEX lookuppt ( person_table); + +--# speeding up lookups. +ALTER TABLE Events ADD INDEX lookup_when( person_id, event_id ); +ALTER TABLE Events add index lookup_event_when (event_when); +ALTER TABLE Events add index lookup_action (action); +ALTER TABLE Events add index lookup_on_table (on_table); + +ALTER TABLE Events add index lookup_action_person (action, person_id); CREATE TABLE core_event_audit ( @@ -95,23 +107,30 @@ ALTER TABLE core_event_audit ADD COLUMN old_audit_id int(11) NOT NULL DEF ALTER TABLE core_event_audit ADD COLUMN newvalue BLOB NOT NULL DEFAULT ''; ALTER TABLE core_event_audit ADD INDEX lookup(event_id, name, old_audit_id); -CREATE TABLE Group_Members ( +-- BC name.. +RENAME TABLE Group_Members TO group_members; + +CREATE TABLE group_members ( id int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ); -ALTER TABLE Group_Members ADD COLUMN group_id int(11) default NULL; -ALTER TABLE Group_Members ADD COLUMN user_id int(11) NOT NULL default 0; +ALTER TABLE group_members ADD COLUMN group_id int(11) default NULL; +ALTER TABLE group_members ADD COLUMN user_id int(11) NOT NULL default 0; -CREATE TABLE Group_Rights ( +-- BC name.. +RENAME TABLE Group_Rights TO group_rights; + + +CREATE TABLE group_rights ( id int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ); -ALTER TABLE Group_Rights ADD COLUMN rightname varchar(64) NOT NULL DEFAULT ''; -ALTER TABLE Group_Rights ADD COLUMN group_id int(11) NOT NULL DEFAULT 0; -ALTER TABLE Group_Rights ADD COLUMN accessmask varchar(10) NOT NULL DEFAULT ''; +ALTER TABLE group_rights ADD COLUMN rightname varchar(64) NOT NULL DEFAULT ''; +ALTER TABLE group_rights ADD COLUMN group_id int(11) NOT NULL DEFAULT 0; +ALTER TABLE group_rights ADD COLUMN accessmask varchar(10) NOT NULL DEFAULT ''; #old mysql. -ALTER TABLE Group_Rights CHANGE COLUMN AccessMask accessmask varchar(10) NOT NULL DEFAULT ''; +ALTER TABLE group_rights CHANGE COLUMN AccessMask accessmask varchar(10) NOT NULL DEFAULT ''; @@ -171,6 +190,13 @@ ALTER TABLE Person ADD COLUMN project_id int(11) default 0; ALTER TABLE Person ADD COLUMN deleted_by INT(11) NOT NULL default 0 ; ALTER TABLE Person ADD COLUMN deleted_dt DATETIME ; +ALTER TABLE Person ADD COLUMN firstname varchar(128) NOT NULL DEFAULT ''; +ALTER TABLE Person ADD COLUMN lastname varchar(128) NOT NULL DEFAULT ''; + +ALTER TABLE `clipping_dev`.`Person` ADD COLUMN `name_facebook` VARCHAR(128) NULL AFTER `crm_type_id` , ADD COLUMN `url_blog` VARCHAR(256) NULL AFTER `name_facebook` , ADD COLUMN `url_twitter` VARCHAR(256) NULL AFTER `url_blog` , ADD COLUMN `url_linkedin` VARCHAR(256) NULL AFTER `url_twitter` ; + + + # old mysql alter table Person change column active active int(11) NOT NULL DEFAULT 1 ; alter table Person change role role varchar(254) NOT NULL DEFAULT ''; @@ -198,7 +224,7 @@ ALTER TABLE Projects ADD COLUMN team_id int(11) NOT NULL default '0'; ALTER TABLE Projects ADD COLUMN file_location varchar(254) NOT NULL default ''; ALTER TABLE Projects ADD COLUMN open_date date default NULL; ALTER TABLE Projects ADD COLUMN open_by int(11) NOT NULL default '0'; - +ALTER TABLE Projects ADD COLUMN updated_dt DATETIME NOT NULL; # these should be removed, as they are code specific.. ALTER TABLE Projects ADD COLUMN countries varchar(128) NOT NULL DEFAULT ''; @@ -294,14 +320,16 @@ CREATE TABLE core_enum ( ); -alter table core_enum ADD COLUMN etype varchar(32) NOT NULL DEFAULT ''; +alter table core_enum ADD COLUMN etype varchar(32) NOT NULL DEFAULT ''; alter table core_enum ADD COLUMN name varchar(255) NOT NULL DEFAULT ''; -alter table core_enum ADD COLUMN active int(2) NOT NULL DEFAULT 1; +alter table core_enum ADD COLUMN active int(2) NOT NULL DEFAULT 1; alter table core_enum ADD COLUMN seqid int(11) NOT NULL DEFAULT 0; alter table core_enum ADD COLUMN seqmax int(11) NOT NULL DEFAULT 0; +alter table core_enum ADD COLUMN display_name varchar(255) NOT NULL DEFAULT ''; alter table core_enum ADD INDEX lookup(seqid, active, name, etype); - + +UPDATE core_enum SET display_name = name WHERE display_name = ''; @@ -344,9 +372,10 @@ CREATE TABLE core_notify ( id int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) ); - -ALTER TABLE core_notify ADD COLUMN act_when DATETIME ; -ALTER TABLE core_notify ADD COLUMN act_start DATETIME ; +ALTER TABLE core_notify ADD COLUMN evtype VARCHAR(128) NOT NULL default ''; +ALTER TABLE core_notify ADD COLUMN recur_id INT(11) NOT NULL default 0; +ALTER TABLE core_notify ADD COLUMN act_when DATETIME ; +ALTER TABLE core_notify ADD COLUMN act_start DATETIME ; ALTER TABLE core_notify ADD COLUMN onid int(11) NOT NULL DEFAULT 0; ALTER TABLE core_notify ADD COLUMN ontable varchar(128) NOT NULL DEFAULT ''; ALTER TABLE core_notify ADD COLUMN person_id int(11) NOT NULL DEFAULT 0; @@ -385,37 +414,31 @@ CREATE TABLE core_notify_recur ( id int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (id) -) ; +); ALTER TABLE core_notify_recur ADD COLUMN person_id int(11) NOT NULL; ALTER TABLE core_notify_recur ADD COLUMN dtstart datetime NOT NULL; ALTER TABLE core_notify_recur ADD COLUMN dtend datetime NOT NULL; -ALTER TABLE core_notify_recur ADD COLUMN tz decimal(4,2) NOT NULL;, ALTER TABLE core_notify_recur ADD COLUMN max_applied_dt datetime NOT NULL; ALTER TABLE core_notify_recur ADD COLUMN updated_dt datetime NOT NULL; ALTER TABLE core_notify_recur ADD COLUMN last_applied_dt datetime NOT NULL; - -ALTER TABLE core_notify_recur ADD INDEX lookup(person_id, dtstart, dtend, tz, max_applied_dt, updated_dt, last_applied_dt); - - - -CREATE TABLE core_notify_recur_repeat ( - id int(11) NOT NULL AUTO_INCREMENT, - - PRIMARY KEY (id) -) ; -ALTER TABLE core_notify_recur_repeat ADD COLUMN +ALTER TABLE core_notify_recur ADD COLUMN tz varchar(64) NOT NULL; +ALTER TABLE core_notify_recur ADD COLUMN freq varchar(8) NOT NULL; +ALTER TABLE core_notify_recur ADD COLUMN freq_day text NOT NULL; +ALTER TABLE core_notify_recur ADD COLUMN freq_hour text NOT NULL; -ALTER TABLE core_notify_recur_repeat ADD COLUMN recur_id int(11) NOT NULL; -ALTER TABLE core_notify_recur_repeat ADD COLUMN freq varchar(8) NOT NULL; -ALTER TABLE core_notify_recur_repeat ADD COLUMN freq_day text NOT NULL; -ALTER TABLE core_notify_recur_repeat ADD COLUMN freq_hour text NOT NULL;, - -ALTER TABLE core_notify_recur_repeat ADD INDEX lookup(recur_id); - +ALTER TABLE core_notify_recur ADD COLUMN onid int(11) NOT NULL default 0; +ALTER TABLE core_notify_recur ADD COLUMN ontable varchar(128) NOT NULL default ''; +ALTER TABLE core_notify_recur ADD COLUMN last_event_id int(11) default 0; +ALTER TABLE core_notify_recur ADD COLUMN method varchar(128) default ''; +ALTER TABLE core_notify_recur ADD COLUMN method_id int(11) default 0; +ALTER TABLE core_notify_recur ADD INDEX lookup(person_id, dtstart, dtend, tz, max_applied_dt, updated_dt, last_applied_dt); +-- old design.. +ALTER TABLE core_notify_recur CHANGE COLUMN tz tz varchar(64) NOT NULL; +