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;
38 ADD COLUMN dispatch_port varchar(255) NOT NULL DEFAULT '',
39 ADD COLUMN province varchar(255) NOT NULL DEFAULT '',
40 ADD COLUMN country varchar(4) NOT NULL DEFAULT '';
43 UPDATE Companies set comptype='OWNER' where isOwner=1;
46 CREATE TABLE `core_company_type` (
47 `id` int(11) NOT NULL AUTO_INCREMENT,
48 `name` varchar(64) NOT NULL,
53 CREATE TABLE `Events` (
54 `id` int(11) NOT NULL auto_increment,
55 `person_name` varchar(128) default NULL,
56 `event_when` datetime default NULL,
57 `action` varchar(32) default NULL,
58 `ipaddr` varchar(16) default NULL,
59 `on_id` int(11) default NULL,
60 `on_table` varchar(64) default NULL,
61 `person_id` int(11) default NULL,
67 ALTER TABLE Events CHANGE COLUMN EventID id INT(11) AUTO_INCREMENT NOT NULL;
68 ALTER TABLE Events CHANGE COLUMN User person_name VARCHAR(128);
69 ALTER TABLE Events ADD COLUMN person_id INT(11);
70 ALTER TABLE Events CHANGE COLUMN Date event_when DATETIME;
71 ALTER TABLE Events CHANGE COLUMN Event action VARCHAR(32);
72 ALTER TABLE Events CHANGE COLUMN Host ipaddr VARCHAR(16);
73 ALTER TABLE Events CHANGE COLUMN ItemID on_id INT(11);
74 ALTER TABLE Events CHANGE COLUMN Container on_table VARCHAR(64);
75 ALTER TABLE Events ADD COLUMN remarks INT(11);
79 CREATE TABLE `Group_Members` (
80 `group_id` int(11) default NULL,
81 `id` int(11) NOT NULL auto_increment,
82 `user_id` int(11) NOT NULL default '0',
87 CREATE TABLE `Group_Rights` (
88 `rightname` varchar(64) NOT NULL,
89 `group_id` int(11) NOT NULL,
90 `AccessMask` varchar(10) NOT NULL,
91 `id` int(11) NOT NULL auto_increment,
98 CREATE TABLE `Groups` (
99 `id` int(11) NOT NULL auto_increment,
100 `name` varchar(64) NOT NULL,
101 `type` int(11) default NULL,
102 `leader` int(11) NOT NULL default '0',
108 alter table Groups add column type int(11) default 0;
109 ALTER TABLE `Groups` ADD COLUMN `leader` int(11) NOT NULL default 0;
110 ALTER TABLE Groups CHANGE COLUMN type type int(11) default 0;
115 CREATE TABLE `Office` (
116 `id` int(11) NOT NULL auto_increment,
117 `company_id` int(11) NOT NULL default '0',
118 `name` varchar(64) NOT NULL,
119 `address` text NOT NULL,
120 `phone` varchar(32) NOT NULL,
121 `fax` varchar(32) NOT NULL,
122 `email` varchar(128) NOT NULL,
123 `role` varchar(32) NOT NULL,
127 CREATE TABLE `Person` (
128 `id` int(11) NOT NULL auto_increment,
129 `office_id` int(11) default '0',
130 `name` varchar(128) NOT NULL,
131 `phone` varchar(32) NOT NULL,
132 `fax` varchar(32) NOT NULL,
133 `email` varchar(128) NOT NULL,
134 `company_id` int(11) default '0',
135 `role` varchar(32) NOT NULL,
136 `active` int(11) default NULL,
137 `remarks` text NOT NULL,
138 `passwd` varchar(64) NOT NULL,
139 `owner_id` int(11) NOT NULL,
140 `lang` varchar(8) default 'en',
141 `no_reset_sent` int(11) default '0',
147 ALTER TABLE Person ADD COLUMN no_reset_sent INT(11) DEFAULT 0;
148 ALTER TABLE Person ADD COLUMN action_type VARCHAR(32) DEFAULT '';
149 ALTER TABLE Person ADD COLUMN project_id int(11) default 0;
151 ALTER TABLE Person ADD COLUMN action_type VARCHAR(32) default '';
153 ALTER TABLE Person ADD COLUMN deleted_by INT(11) NOT NULL default 0 ;
154 ALTER TABLE Person ADD COLUMN deleted_dt DATETIME;
156 alter table Person change column active active int(11) NOT NULL DEFAULT 1 ;
159 CREATE TABLE `Projects` (
160 `id` int(11) NOT NULL auto_increment,
161 `name` varchar(254) NOT NULL,
162 `remarks` text NOT NULL,
163 `owner_id` int(11) default NULL,
164 `code` varchar(32) NOT NULL,
165 `active` int(11) default '1',
166 `type` varchar(1) NOT NULL default 'P',
167 `client_id` int(11) NOT NULL default '0',
168 `team_id` int(11) NOT NULL default '0',
169 `file_location` varchar(254) NOT NULL default '',
170 `open_date` date default NULL,
171 `open_by` int(11) NOT NULL default '0',
175 ALTER TABLE `Projects` ADD INDEX `plookup` (`code`);
177 alter table Projects add column active int(11) default 1;
178 alter table Projects add index plookup(code);
180 ALTER TABLE Projects ADD COLUMN `type` varchar(1) NOT NULL DEFAULT 'P';
181 ALTER TABLE Projects ADD COLUMN `client_id` int(11) NOT NULL DEFAULT 0 ;
182 ALTER TABLE Projects ADD COLUMN `team_id` int(11) NOT NULL DEFAULT 0;
183 ALTER TABLE Projects ADD COLUMN `file_location` varchar(254) NOT NULL DEFAULT '';
184 ALTER TABLE Projects ADD COLUMN `open_date` date ;
185 ALTER TABLE Projects ADD COLUMN `close_date` date ;
186 ALTER TABLE Projects ADD COLUMN `open_by` int(11) NOT NULL DEFAULT 0;
188 ALTER TABLE `Projects` ADD COLUMN `countries` varchar(128) NOT NULL;
189 ALTER TABLE `Projects` ADD COLUMN `languages` varchar(128) NOT NULL;
191 ALTER TABLE Projects ADD COLUMN agency_id int(11) NOT NULL DEFAULT 0 ;
196 CREATE TABLE `Images` (
197 `id` int(11) NOT NULL auto_increment,
198 `filename` varchar(255) NOT NULL default '',
199 `ontable` varchar(32) NOT NULL default '',
200 `onid` int(11) NOT NULL default '0',
201 `mimetype` varchar(64) NOT NULL default '',
202 `width` int(11) NOT NULL default '0',
203 `height` int(11) NOT NULL default '0',
204 `filesize` int(11) NOT NULL default '0',
205 `displayorder` int(11) NOT NULL default '0',
206 `language` varchar(6) NOT NULL default 'en',
207 `parent_image_id` int(11) NOT NULL default '0',
213 ALTER TABLE images ADD COLUMN `width` int(11) NOT NULL default '0';
214 ALTER TABLE images ADD COLUMN `height` int(11) NOT NULL default '0';
215 ALTER TABLE images ADD COLUMN `filesize` int(11) NOT NULL default '0';
216 ALTER TABLE images ADD COLUMN `displayorder` int(11) NOT NULL default '0';
217 ALTER TABLE images ADD COLUMN `language` varchar(6) NOT NULL default 'en';
218 ALTER TABLE images ADD COLUMN `parent_image_id` int(11) NOT NULL default '0';
222 ALTER TABLE `Images` ADD INDEX `lookup`(`ontable`, `onid`);
224 ALTER TABLE `Images` ADD COLUMN `created` datetime NOT NULL;
225 ALTER TABLE `Images` ADD COLUMN `imgtype` VARCHAR(32) DEFAULT '' NOT NULL;
226 ALTER TABLE `Images` ADD COLUMN `linkurl` VARCHAR(254) DEFAULT '' NOT NULL;
227 ALTER TABLE `Images` ADD COLUMN `descript` TEXT DEFAULT '' NOT NULL;
228 ALTER TABLE `Images` ADD COLUMN `title` VARCHAR(128) DEFAULT '' NOT NULL;
230 CREATE TABLE `core_image_type` (
231 `id` int(11) NOT NULL AUTO_INCREMENT,
232 `name` varchar(64) NOT NULL,
236 CREATE TABLE `i18n` (
237 `id` int(11) NOT NULL AUTO_INCREMENT,
238 `ltype` varchar(1) NOT NULL,
239 `lkey` varchar(8) NOT NULL,
240 `inlang` varchar(8) NOT NULL,
241 `lval` varchar(64) NOT NULL,
245 ALTER TABLE i18n ADD INDEX `lookup` (`ltype`, `lkey`, `inlang`);
250 CREATE TABLE core_locking (
251 `int` int(11) NOT NULL AUTO_INCREMENT,
252 `on_table` varchar(64) NOT NULL,
253 `on_id` int(11) NOT NULL,
254 `person_id` int(11) NOT NULL,
255 `created` datetime NOT NULL,
258 alter table core_locking ADD INDEX `lookup`(`on_table`, `on_id`, `person_id`, `created`);
261 # -- a generic enumeraction
263 CREATE TABLE `core_enum` (
264 `id` int(11) NOT NULL AUTO_INCREMENT,
265 `etype` varchar(32) NOT NULL,
266 `name` varchar(255) NOT NULL,
267 `active` int(2) NOT NULL DEFAULT 1,
268 `seqid` int(11) NOT NULL DEFAULT 0,
270 INDEX `lookup`(`seqid`, `active`, `name`, `etype`)
277 CREATE TABLE `translations` (
278 `id` int(11) NOT NULL AUTO_INCREMENT,
279 `module` varchar(64) NOT NULL,
280 tfile varchar(128) NOT NULL,
281 tlang varchar(8) NOT NULL,
282 tkey varchar(32) NOT NULL,
283 tval longtext NOT NULL,
287 ALTER TABLE translations ADD INDEX qlookup (module, tfile, tlang, tkey);
290 # - used to trigger emails about changes to items being watched.
292 CREATE TABLE `core_watch` (
293 `id` int(11) NOT NULL AUTO_INCREMENT,
294 `ontable` varchar(128) NOT NULL,
295 `onid` int(11) NOT NULL,
296 `person_id` int(11) NOT NULL,
297 `event` varchar(128) NOT NULL,
298 `medium` varchar(128) NOT NULL,
299 `active` int(11) NOT NULL DEFAULT '1',
302 ALTER TABLE core_watch ADD INDEX qlookup (`ontable`,`onid`,`user_id`,`event`,`medium`);
307 # - used by email / tracker to handle alises - we have to be carefull adding to this table...
309 CREATE TABLE `core_person_aliases` (
310 `id` int(11) NOT NULL AUTO_INCREMENT,
311 `person_id` varchar(128) DEFAULT NULL,
312 `alias` varchar(254) NOT NULL,
313 PRIMARY KEY (`alias`)
315 ALTER TABLE core_watch ADD INDEX qlookup (`alias`);