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,
60 ALTER TABLE Events CHANGE COLUMN EventID id INT(11) AUTO_INCREMENT NOT NULL;
61 ALTER TABLE Events CHANGE COLUMN User person_name VARCHAR(128);
62 ALTER TABLE Events ADD COLUMN person_id INT(11);
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);
68 ALTER TABLE Events ADD COLUMN remarks INT(11);
70 CREATE TABLE core_event_audit (
71 `id` int(11) NOT NULL AUTO_INCREMENT,
72 `event_id` int(11) NOT NULL,
73 `name` varchar(128) NOT NULL,
74 `old_audit_id` int(11) NOT NULL,
75 `newvalue` BLOB NOT NULL,
77 INDEX `lookup`(`event_id`, `name`, `last_audit_id`)
81 CREATE TABLE `Group_Members` (
82 `group_id` int(11) default NULL,
83 `id` int(11) NOT NULL auto_increment,
84 `user_id` int(11) NOT NULL default '0',
89 CREATE TABLE `Group_Rights` (
90 `rightname` varchar(64) NOT NULL,
91 `group_id` int(11) NOT NULL,
92 `AccessMask` varchar(10) NOT NULL,
93 `id` int(11) NOT NULL auto_increment,
100 CREATE TABLE `Groups` (
101 `id` int(11) NOT NULL auto_increment,
102 `name` varchar(64) NOT NULL,
103 `type` int(11) default NULL,
104 `leader` int(11) NOT NULL default '0',
110 alter table Groups add column type int(11) default 0;
111 ALTER TABLE `Groups` ADD COLUMN `leader` int(11) NOT NULL default 0;
112 ALTER TABLE Groups CHANGE COLUMN type type int(11) default 0;
117 CREATE TABLE `Office` (
118 `id` int(11) NOT NULL auto_increment,
119 `company_id` int(11) NOT NULL default '0',
120 `name` varchar(64) NOT NULL,
121 `address` text NOT NULL,
122 `phone` varchar(32) NOT NULL,
123 `fax` varchar(32) NOT NULL,
124 `email` varchar(128) NOT NULL,
125 `role` varchar(32) NOT NULL,
129 CREATE TABLE `Person` (
130 `id` int(11) NOT NULL auto_increment,
131 `office_id` int(11) default '0',
132 `name` varchar(128) NOT NULL,
133 `phone` varchar(32) NOT NULL,
134 `fax` varchar(32) NOT NULL,
135 `email` varchar(128) NOT NULL,
136 `company_id` int(11) default '0',
137 `role` varchar(32) NOT NULL,
138 `active` int(11) default NULL,
139 `remarks` text NOT NULL,
140 `passwd` varchar(64) NOT NULL,
141 `owner_id` int(11) NOT NULL,
142 `lang` varchar(8) default 'en',
143 `no_reset_sent` int(11) default '0',
149 ALTER TABLE Person ADD COLUMN no_reset_sent INT(11) DEFAULT 0;
150 ALTER TABLE Person ADD COLUMN action_type VARCHAR(32) DEFAULT '';
151 ALTER TABLE Person ADD COLUMN project_id int(11) default 0;
153 ALTER TABLE Person ADD COLUMN action_type VARCHAR(32) default '';
155 ALTER TABLE Person ADD COLUMN deleted_by INT(11) NOT NULL default 0 ;
156 ALTER TABLE Person ADD COLUMN deleted_dt DATETIME;
158 alter table Person change column active active int(11) NOT NULL DEFAULT 1 ;
161 CREATE TABLE `Projects` (
162 `id` int(11) NOT NULL auto_increment,
163 `name` varchar(254) NOT NULL,
164 `remarks` text NOT NULL,
165 `owner_id` int(11) default NULL,
166 `code` varchar(32) NOT NULL,
167 `active` int(11) default '1',
168 `type` varchar(1) NOT NULL default 'P',
169 `client_id` int(11) NOT NULL default '0',
170 `team_id` int(11) NOT NULL default '0',
171 `file_location` varchar(254) NOT NULL default '',
172 `open_date` date default NULL,
173 `open_by` int(11) NOT NULL default '0',
177 ALTER TABLE `Projects` ADD INDEX `plookup` (`code`);
179 alter table Projects add column active int(11) default 1;
180 alter table Projects add index plookup(code);
182 ALTER TABLE Projects ADD COLUMN `type` varchar(1) NOT NULL DEFAULT 'P';
183 ALTER TABLE Projects ADD COLUMN `client_id` int(11) NOT NULL DEFAULT 0 ;
184 ALTER TABLE Projects ADD COLUMN `team_id` int(11) NOT NULL DEFAULT 0;
185 ALTER TABLE Projects ADD COLUMN `file_location` varchar(254) NOT NULL DEFAULT '';
186 ALTER TABLE Projects ADD COLUMN `open_date` date ;
187 ALTER TABLE Projects ADD COLUMN `close_date` date ;
188 ALTER TABLE Projects ADD COLUMN `open_by` int(11) NOT NULL DEFAULT 0;
190 ALTER TABLE `Projects` ADD COLUMN `countries` varchar(128) NOT NULL;
191 ALTER TABLE `Projects` ADD COLUMN `languages` varchar(128) NOT NULL;
193 ALTER TABLE Projects ADD COLUMN agency_id int(11) NOT NULL DEFAULT 0 ;
196 #-- we duplicate office_id and company_id here...
197 #-- not sure if we should keep doing that in the new design...
198 #-- we should improve our links code to handle this..
201 CREATE TABLE `ProjectDirectory` (
202 `id` int(11) NOT NULL auto_increment,
203 `project_id` int(11) NOT NULL,
204 `person_id` int(11) NOT NULL,
205 `ispm` int(11) NOT NULL,
206 `role` varchar(16) NOT NULL,
211 CREATE TABLE `Images` (
212 `id` int(11) NOT NULL auto_increment,
213 `filename` varchar(255) NOT NULL default '',
214 `ontable` varchar(32) NOT NULL default '',
215 `onid` int(11) NOT NULL default '0',
216 `mimetype` varchar(64) NOT NULL default '',
217 `width` int(11) NOT NULL default '0',
218 `height` int(11) NOT NULL default '0',
219 `filesize` int(11) NOT NULL default '0',
220 `displayorder` int(11) NOT NULL default '0',
221 `language` varchar(6) NOT NULL default 'en',
222 `parent_image_id` int(11) NOT NULL default '0',
228 ALTER TABLE Images ADD COLUMN `width` int(11) NOT NULL default '0';
229 ALTER TABLE Images ADD COLUMN `height` int(11) NOT NULL default '0';
230 ALTER TABLE Images ADD COLUMN `filesize` int(11) NOT NULL default '0';
231 ALTER TABLE Images ADD COLUMN `displayorder` int(11) NOT NULL default '0';
232 ALTER TABLE Images ADD COLUMN `language` varchar(6) NOT NULL default 'en';
233 ALTER TABLE Images ADD COLUMN `parent_image_id` int(11) NOT NULL default '0';
237 ALTER TABLE `Images` ADD INDEX `lookup`(`ontable`, `onid`);
239 ALTER TABLE `Images` ADD COLUMN `created` datetime NOT NULL;
240 ALTER TABLE `Images` ADD COLUMN `imgtype` VARCHAR(32) DEFAULT '' NOT NULL;
241 ALTER TABLE `Images` ADD COLUMN `linkurl` VARCHAR(254) DEFAULT '' NOT NULL;
242 ALTER TABLE `Images` ADD COLUMN `descript` TEXT DEFAULT '' NOT NULL;
243 ALTER TABLE `Images` ADD COLUMN `title` VARCHAR(128) DEFAULT '' NOT NULL;
245 #// old core image type - merged into enum.
246 DROP TABLE core_image_type;
249 CREATE TABLE `i18n` (
250 `id` int(11) NOT NULL AUTO_INCREMENT,
251 `ltype` varchar(1) NOT NULL,
252 `lkey` varchar(8) NOT NULL,
253 `inlang` varchar(8) NOT NULL,
254 `lval` varchar(64) NOT NULL,
258 ALTER TABLE i18n ADD INDEX `lookup` (`ltype`, `lkey`, `inlang`);
263 CREATE TABLE core_locking (
264 `int` int(11) NOT NULL AUTO_INCREMENT,
265 `on_table` varchar(64) NOT NULL,
266 `on_id` int(11) NOT NULL,
267 `person_id` int(11) NOT NULL,
268 `created` datetime NOT NULL,
271 alter table core_locking ADD INDEX `lookup`(`on_table`, `on_id`, `person_id`, `created`);
274 # -- a generic enumeraction
276 CREATE TABLE `core_enum` (
277 `id` int(11) NOT NULL AUTO_INCREMENT,
278 `etype` varchar(32) NOT NULL,
279 `name` varchar(255) NOT NULL,
280 `active` int(2) NOT NULL DEFAULT 1,
281 `seqid` int(11) NOT NULL DEFAULT 0,
283 INDEX `lookup`(`seqid`, `active`, `name`, `etype`)
290 CREATE TABLE `translations` (
291 `id` int(11) NOT NULL AUTO_INCREMENT,
292 `module` varchar(64) NOT NULL,
293 tfile varchar(128) NOT NULL,
294 tlang varchar(8) NOT NULL,
295 tkey varchar(32) NOT NULL,
296 tval longtext NOT NULL,
300 ALTER TABLE translations ADD INDEX qlookup (module, tfile, tlang, tkey);
303 # - used to trigger emails about changes to items being watched.
305 CREATE TABLE `core_watch` (
306 `id` int(11) NOT NULL AUTO_INCREMENT,
307 `ontable` varchar(128) NOT NULL,
308 `onid` int(11) NOT NULL,
309 `person_id` int(11) NOT NULL,
310 `event` varchar(128) NOT NULL,
311 `medium` varchar(128) NOT NULL,
312 `active` int(11) NOT NULL DEFAULT '1',
315 ALTER TABLE core_watch ADD INDEX qlookup (`ontable`,`onid`,`user_id`,`event`,`medium`);
317 CREATE TABLE core_notify (
318 `id` int(11) NOT NULL AUTO_INCREMENT,
319 `act_when` DATETIME NOT NULL,
320 act_start DATETIME NOT NULL,
321 `onid` int(11) NOT NULL DEFAULT 0,
322 `ontable` varchar(128) NOT NULL DEFAULT '',
323 `person_id` int(11) NOT NULL DEFAULT 0,
324 `msgid` varchar(128) NOT NULL DEFAULT '',
325 `sent` DATETIME NOT NULL,
326 `event_id` int(11) NOT NULL DEFAULT 0,
329 INDEX `lookup`(`act_when`, `msgid`)
331 ALTER TABLE core_notify CHANGE COLUMN bounced event_id INT(11) NOT NULL DEFAULT 0;
333 ALTER TABLE core_notify ADD COLUMN act_start DATETIME NOT NULL;
336 # - used by email / tracker to handle alises - we have to be carefull adding to this table...
338 CREATE TABLE `core_person_alias` (
339 `id` int(11) NOT NULL AUTO_INCREMENT,
340 `person_id` varchar(128) DEFAULT NULL,
341 `alias` varchar(254) NOT NULL,
344 ALTER TABLE core_watch ADD INDEX qlookup (`alias`);