2 CREATE TABLE `Companies` (
3 `code` varchar(32) NOT NULL,
4 `name` varchar(128) default NULL,
6 `owner_id` int(11) NOT NULL,
8 `tel` varchar(32) default NULL,
9 `fax` varchar(32) default NULL,
10 `email` varchar(128) default NULL,
11 `id` int(11) NOT NULL auto_increment,
12 `isOwner` int(11) default NULL,
16 ALTER TABLE `Company_Name` ADD INDEX name_lookup (`name`);
19 alter table Companies change column isOwner isOwner int(11);
20 ALTER TABLE Companies ADD COLUMN logo_id INT(11) NOT NULL;
21 ALTER TABLE Companies ADD COLUMN background_color varchar(8) NOT NULL;
22 ALTER TABLE Companies ADD COLUMN comptype varchar(8) NOT NULL;
25 ALTER TABLE `Companies` ADD COLUMN `url` varchar(254) NOT NULL;
26 ALTER TABLE `Companies` ADD COLUMN `main_office_id` int(11) NOT NULL;
29 ALTER TABLE `Companies` ADD COLUMN `created_by` int(11) NOT NULL;
30 ALTER TABLE `Companies` ADD COLUMN `created_dt` datetime NOT NULL;
31 ALTER TABLE `Companies` ADD COLUMN `updated_by` int(11) NOT NULL;
32 ALTER TABLE `Companies` ADD COLUMN `updated_dt` datetime NOT NULL;
34 ALTER TABLE `Companies` ADD COLUMN `passwd` varchar(64) NOT NULL;
36 ALTER TABLE Companies ADD COLUMN dispatch_port varchar(255) NOT NULL DEFAULT '';
37 ALTER TABLE Companies ADD COLUMN province varchar(255) NOT NULL DEFAULT '';
38 ALTER TABLE Companies ADD COLUMN country varchar(4) NOT NULL DEFAULT '';
41 UPDATE Companies set comptype='OWNER' where isOwner=1;
43 #// core comapy types - use core enums (Company Type)
44 DROP TABLE core_company_type;
46 CREATE TABLE `Events` (
47 `id` int(11) NOT NULL auto_increment,
48 `person_name` varchar(128) default NULL,
49 `event_when` datetime default NULL,
50 `action` varchar(32) default NULL,
51 `ipaddr` varchar(16) default NULL,
52 `on_id` int(11) default NULL,
53 `on_table` varchar(64) default NULL,
54 `person_id` int(11) default NULL,
59 ALTER TABLE Events ADD COLUMN person_id INT(11);
60 ALTER TABLE Events ADD COLUMN remarks INT(11);
61 ALTER TABLE Events CHANGE COLUMN EventID id INT(11) AUTO_INCREMENT NOT NULL;
62 ALTER TABLE Events CHANGE COLUMN User person_name VARCHAR(128);
63 ALTER TABLE Events CHANGE COLUMN Date event_when DATETIME;
64 ALTER TABLE Events CHANGE COLUMN Event action VARCHAR(32);
65 ALTER TABLE Events CHANGE COLUMN Host ipaddr VARCHAR(16);
66 ALTER TABLE Events CHANGE COLUMN ItemID on_id INT(11);
67 ALTER TABLE Events CHANGE COLUMN Container on_table VARCHAR(64);
70 ALTER TABLE Events ADD INDEX lookup (on_id, on_table, person_id, event_when);
72 CREATE TABLE core_event_audit (
73 `id` int(11) NOT NULL AUTO_INCREMENT,
74 `event_id` int(11) NOT NULL,
75 `name` varchar(128) NOT NULL,
76 `old_audit_id` int(11) NOT NULL,
77 `newvalue` BLOB NOT NULL,
79 INDEX `lookup`(`event_id`, `name`, `old_audit_id`)
83 CREATE TABLE `Group_Members` (
84 `group_id` int(11) default NULL,
85 `id` int(11) NOT NULL auto_increment,
86 `user_id` int(11) NOT NULL default '0',
91 CREATE TABLE `Group_Rights` (
92 `rightname` varchar(64) NOT NULL,
93 `group_id` int(11) NOT NULL,
94 `AccessMask` varchar(10) NOT NULL,
95 `id` int(11) NOT NULL auto_increment,
102 CREATE TABLE `Groups` (
103 `id` int(11) NOT NULL auto_increment,
104 `name` varchar(64) NOT NULL,
105 `type` int(11) default NULL,
106 `leader` int(11) NOT NULL default '0',
112 alter table Groups add column type int(11) default 0;
113 ALTER TABLE `Groups` ADD COLUMN `leader` int(11) NOT NULL default 0;
114 ALTER TABLE Groups CHANGE COLUMN type type int(11) default 0;
119 CREATE TABLE `Office` (
120 `id` int(11) NOT NULL auto_increment,
121 `company_id` int(11) NOT NULL default '0',
122 `name` varchar(64) NOT NULL,
123 `address` text NOT NULL,
124 `phone` varchar(32) NOT NULL,
125 `fax` varchar(32) NOT NULL,
126 `email` varchar(128) NOT NULL,
127 `role` varchar(32) NOT NULL,
131 CREATE TABLE `Person` (
132 `id` int(11) NOT NULL auto_increment,
133 `office_id` int(11) default '0',
134 `name` varchar(128) NOT NULL,
135 `phone` varchar(32) NOT NULL,
136 `fax` varchar(32) NOT NULL,
137 `email` varchar(128) NOT NULL,
138 `company_id` int(11) default '0',
139 `role` varchar(32) NOT NULL,
140 `active` int(11) default NULL,
141 `remarks` text NOT NULL,
142 `passwd` varchar(64) NOT NULL,
143 `owner_id` int(11) NOT NULL,
144 `lang` varchar(8) default 'en',
145 `no_reset_sent` int(11) default '0',
151 ALTER TABLE Person ADD COLUMN no_reset_sent INT(11) DEFAULT 0;
152 ALTER TABLE Person ADD COLUMN action_type VARCHAR(32) DEFAULT '';
153 ALTER TABLE Person ADD COLUMN project_id int(11) default 0;
155 ALTER TABLE Person ADD COLUMN action_type VARCHAR(32) default '';
157 ALTER TABLE Person ADD COLUMN deleted_by INT(11) NOT NULL default 0 ;
158 ALTER TABLE Person ADD COLUMN deleted_dt DATETIME;
160 alter table Person change column active active int(11) NOT NULL DEFAULT 1 ;
163 CREATE TABLE `Projects` (
164 `id` int(11) NOT NULL auto_increment,
165 `name` varchar(254) NOT NULL,
166 `remarks` text NOT NULL,
167 `owner_id` int(11) default NULL,
168 `code` varchar(32) NOT NULL,
169 `active` int(11) default '1',
170 `type` varchar(1) NOT NULL default 'P',
171 `client_id` int(11) NOT NULL default '0',
172 `team_id` int(11) NOT NULL default '0',
173 `file_location` varchar(254) NOT NULL default '',
174 `open_date` date default NULL,
175 `open_by` int(11) NOT NULL default '0',
179 ALTER TABLE `Projects` ADD INDEX `plookup` (`code`);
181 alter table Projects add column active int(11) default 1;
182 alter table Projects add index plookup(code);
184 ALTER TABLE Projects ADD COLUMN `type` varchar(1) NOT NULL DEFAULT 'P';
185 ALTER TABLE Projects ADD COLUMN `client_id` int(11) NOT NULL DEFAULT 0 ;
186 ALTER TABLE Projects ADD COLUMN `team_id` int(11) NOT NULL DEFAULT 0;
187 ALTER TABLE Projects ADD COLUMN `file_location` varchar(254) NOT NULL DEFAULT '';
188 ALTER TABLE Projects ADD COLUMN `open_date` date ;
189 ALTER TABLE Projects ADD COLUMN `close_date` date ;
190 ALTER TABLE Projects ADD COLUMN `open_by` int(11) NOT NULL DEFAULT 0;
192 ALTER TABLE `Projects` ADD COLUMN `countries` varchar(128) NOT NULL;
193 ALTER TABLE `Projects` ADD COLUMN `languages` varchar(128) NOT NULL;
195 ALTER TABLE Projects ADD COLUMN agency_id int(11) NOT NULL DEFAULT 0 ;
198 #-- we duplicate office_id and company_id here...
199 #-- not sure if we should keep doing that in the new design...
200 #-- we should improve our links code to handle this..
203 CREATE TABLE `ProjectDirectory` (
204 `id` int(11) NOT NULL auto_increment,
205 `project_id` int(11) NOT NULL,
206 `person_id` int(11) NOT NULL,
207 `ispm` int(11) NOT NULL,
208 `role` varchar(16) NOT NULL,
213 CREATE TABLE `Images` (
214 `id` int(11) NOT NULL auto_increment,
215 `filename` varchar(255) NOT NULL default '',
216 `ontable` varchar(32) NOT NULL default '',
217 `onid` int(11) NOT NULL default '0',
218 `mimetype` varchar(64) NOT NULL default '',
219 `width` int(11) NOT NULL default '0',
220 `height` int(11) NOT NULL default '0',
221 `filesize` int(11) NOT NULL default '0',
222 `displayorder` int(11) NOT NULL default '0',
223 `language` varchar(6) NOT NULL default 'en',
224 `parent_image_id` int(11) NOT NULL default '0',
230 ALTER TABLE Images ADD COLUMN `width` int(11) NOT NULL default '0';
231 ALTER TABLE Images ADD COLUMN `height` int(11) NOT NULL default '0';
232 ALTER TABLE Images ADD COLUMN `filesize` int(11) NOT NULL default '0';
233 ALTER TABLE Images ADD COLUMN `displayorder` int(11) NOT NULL default '0';
234 ALTER TABLE Images ADD COLUMN `language` varchar(6) NOT NULL default 'en';
235 ALTER TABLE Images ADD COLUMN `parent_image_id` int(11) NOT NULL default '0';
239 ALTER TABLE `Images` ADD INDEX `lookup`(`ontable`, `onid`);
241 ALTER TABLE `Images` ADD COLUMN `created` datetime NOT NULL;
242 ALTER TABLE `Images` ADD COLUMN `imgtype` VARCHAR(32) DEFAULT '' NOT NULL;
243 ALTER TABLE `Images` ADD COLUMN `linkurl` VARCHAR(254) DEFAULT '' NOT NULL;
244 ALTER TABLE `Images` ADD COLUMN `descript` TEXT DEFAULT '' NOT NULL;
245 ALTER TABLE `Images` ADD COLUMN `title` VARCHAR(128) DEFAULT '' NOT NULL;
247 #// old core image type - merged into enum.
248 DROP TABLE core_image_type;
251 CREATE TABLE `i18n` (
252 `id` int(11) NOT NULL AUTO_INCREMENT,
253 `ltype` varchar(1) NOT NULL,
254 `lkey` varchar(8) NOT NULL,
255 `inlang` varchar(8) NOT NULL,
256 `lval` varchar(64) NOT NULL,
260 ALTER TABLE i18n ADD INDEX `lookup` (`ltype`, `lkey`, `inlang`);
265 CREATE TABLE core_locking (
266 `int` int(11) NOT NULL AUTO_INCREMENT,
267 `on_table` varchar(64) NOT NULL,
268 `on_id` int(11) NOT NULL,
269 `person_id` int(11) NOT NULL,
270 `created` datetime NOT NULL,
273 alter table core_locking ADD INDEX `lookup`(`on_table`, `on_id`, `person_id`, `created`);
276 # -- a generic enumeraction
278 CREATE TABLE `core_enum` (
279 `id` int(11) NOT NULL AUTO_INCREMENT,
280 `etype` varchar(32) NOT NULL,
281 `name` varchar(255) NOT NULL,
282 `active` int(2) NOT NULL DEFAULT 1,
283 `seqid` int(11) NOT NULL DEFAULT 0,
285 INDEX `lookup`(`seqid`, `active`, `name`, `etype`)
292 CREATE TABLE `translations` (
293 `id` int(11) NOT NULL AUTO_INCREMENT,
294 `module` varchar(64) NOT NULL,
295 tfile varchar(128) NOT NULL,
296 tlang varchar(8) NOT NULL,
297 tkey varchar(32) NOT NULL,
298 tval longtext NOT NULL,
302 ALTER TABLE translations ADD INDEX qlookup (module, tfile, tlang, tkey);
305 # - used to trigger emails about changes to items being watched.
307 CREATE TABLE `core_watch` (
308 `id` int(11) NOT NULL AUTO_INCREMENT,
309 `ontable` varchar(128) NOT NULL,
310 `onid` int(11) NOT NULL,
311 `person_id` int(11) NOT NULL,
312 `event` varchar(128) NOT NULL,
313 `medium` varchar(128) NOT NULL,
314 `active` int(11) NOT NULL DEFAULT '1',
317 ALTER TABLE core_watch ADD INDEX qlookup (`ontable`,`onid`,`user_id`,`event`,`medium`);
322 CREATE TABLE core_notify (
323 `id` int(11) NOT NULL AUTO_INCREMENT,
324 `act_when` DATETIME NOT NULL,
325 act_start DATETIME NOT NULL,
326 `onid` int(11) NOT NULL DEFAULT 0,
327 `ontable` varchar(128) NOT NULL DEFAULT '',
328 `person_id` int(11) NOT NULL DEFAULT 0,
329 `msgid` varchar(128) NOT NULL DEFAULT '',
330 `sent` DATETIME NOT NULL,
331 `event_id` int(11) NOT NULL DEFAULT 0,
334 INDEX `lookup`(`act_when`, `msgid`)
336 ALTER TABLE core_notify CHANGE COLUMN bounced event_id INT(11) NOT NULL DEFAULT 0;
338 ALTER TABLE core_notify ADD COLUMN act_start DATETIME NOT NULL;
339 ALTER TABLE core_notify ADD COLUMN watch_id INT(11) NOT NULL DEFAULT 0;
342 # - used by email / tracker to handle alises - we have to be carefull adding to this table...
344 CREATE TABLE `core_person_alias` (
345 `id` int(11) NOT NULL AUTO_INCREMENT,
346 `person_id` varchar(128) DEFAULT NULL,
347 `alias` varchar(254) NOT NULL,
350 ALTER TABLE core_watch ADD INDEX qlookup (`alias`);