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,
13 PRIMARY KEY USING BTREE (`id`),
14 KEY `Company_Name` (`name`)
17 alter table Companies change column isOwner isOwner int(11);
18 ALTER TABLE Companies ADD COLUMN logo_id INT(11) NOT NULL;
19 ALTER TABLE Companies ADD COLUMN background_color varchar(8) NOT NULL;
20 ALTER TABLE Companies ADD COLUMN comptype varchar(8) NOT NULL;
23 ALTER TABLE `Companies` ADD COLUMN `url` varchar(254) NOT NULL;
24 ALTER TABLE `Companies` ADD COLUMN `main_office_id` int(11) NOT NULL;
27 ALTER TABLE `Companies` ADD COLUMN `created_by` int(11) NOT NULL;
28 ALTER TABLE `Companies` ADD COLUMN `created_dt` datetime NOT NULL;
29 ALTER TABLE `Companies` ADD COLUMN `updated_by` int(11) NOT NULL;
30 ALTER TABLE `Companies` ADD COLUMN `updated_dt` datetime NOT NULL;
32 ALTER TABLE `Companies` ADD COLUMN `passwd` varchar(64) NOT NULL;
36 ADD COLUMN dispatch_port varchar(255) NOT NULL DEFAULT '',
37 ADD COLUMN province varchar(255) NOT NULL DEFAULT '',
38 ADD COLUMN country varchar(4) NOT NULL DEFAULT '';
41 UPDATE Companies set comptype='OWNER' where isOwner=1;
44 CREATE TABLE `core_company_type` (
45 `id` int(11) NOT NULL AUTO_INCREMENT,
46 `name` varchar(64) NOT NULL,
51 CREATE TABLE `Events` (
52 `id` int(11) NOT NULL auto_increment,
53 `person_name` varchar(128) default NULL,
54 `event_when` datetime default NULL,
55 `action` varchar(32) default NULL,
56 `ipaddr` varchar(16) default NULL,
57 `on_id` int(11) default NULL,
58 `on_table` varchar(64) default NULL,
59 `person_id` int(11) default NULL,
65 ALTER TABLE Events CHANGE COLUMN EventID id INT(11) AUTO_INCREMENT NOT NULL;
66 ALTER TABLE Events CHANGE COLUMN User person_name VARCHAR(128);
67 ALTER TABLE Events ADD COLUMN person_id INT(11);
68 ALTER TABLE Events CHANGE COLUMN Date event_when DATETIME;
69 ALTER TABLE Events CHANGE COLUMN Event action VARCHAR(32);
70 ALTER TABLE Events CHANGE COLUMN Host ipaddr VARCHAR(16);
71 ALTER TABLE Events CHANGE COLUMN ItemID on_id INT(11);
72 ALTER TABLE Events CHANGE COLUMN Container on_table VARCHAR(64);
73 ALTER TABLE Events ADD COLUMN remarks INT(11);
77 CREATE TABLE `Group_Members` (
78 `group_id` int(11) default NULL,
79 `id` int(11) NOT NULL auto_increment,
80 `user_id` int(11) NOT NULL default '0',
85 CREATE TABLE `Group_Rights` (
86 `rightname` varchar(64) NOT NULL,
87 `group_id` int(11) NOT NULL,
88 `AccessMask` varchar(10) NOT NULL,
89 `id` int(11) NOT NULL auto_increment,
96 CREATE TABLE `Groups` (
97 `id` int(11) NOT NULL auto_increment,
98 `name` varchar(64) NOT NULL,
99 `type` int(11) default NULL,
100 `leader` int(11) NOT NULL default '0',
101 PRIMARY KEY USING BTREE (`id`)
106 alter table Groups add column type int(11) default 0;
107 ALTER TABLE `Groups` ADD COLUMN `leader` int(11) NOT NULL default 0;
108 ALTER TABLE Groups CHANGE COLUMN type type int(11) default 0;
113 CREATE TABLE `Office` (
114 `id` int(11) NOT NULL auto_increment,
115 `company_id` int(11) NOT NULL default '0',
116 `name` varchar(64) character set latin1 NOT NULL,
117 `address` text character set latin1 NOT NULL,
118 `phone` varchar(32) character set latin1 NOT NULL,
119 `fax` varchar(32) character set latin1 NOT NULL,
120 `email` varchar(128) character set latin1 NOT NULL,
121 `role` varchar(32) character set latin1 NOT NULL,
125 CREATE TABLE `Person` (
126 `id` int(11) NOT NULL auto_increment,
127 `office_id` int(11) default '0',
128 `name` varchar(128) character set latin1 NOT NULL,
129 `phone` varchar(32) character set latin1 NOT NULL,
130 `fax` varchar(32) character set latin1 NOT NULL,
131 `email` varchar(128) character set latin1 NOT NULL,
132 `company_id` int(11) default '0',
133 `role` varchar(32) character set latin1 NOT NULL,
134 `active` int(11) default NULL,
135 `remarks` text NOT NULL,
136 `passwd` varchar(64) NOT NULL,
137 `owner_id` int(11) NOT NULL,
138 `lang` varchar(8) default 'en',
139 `no_reset_sent` int(11) default '0',
145 ALTER TABLE Person ADD COLUMN no_reset_sent INT(11) DEFAULT 0;
146 ALTER TABLE Person ADD COLUMN action_type VARCHAR(32) DEFAULT '';
147 ALTER TABLE Person ADD COLUMN project_id int(11) default 0;
149 ALTER TABLE Person ADD COLUMN action_type VARCHAR(32) default '';
151 ALTER TABLE Person ADD COLUMN deleted_by INT(11) NOT NULL default 0 ;
152 ALTER TABLE Person ADD COLUMN deleted_dt DATETIME;;
154 alter table Person change column active active int(11) NOT NULL DEFAULT 1 ;
157 CREATE TABLE `Projects` (
158 `id` int(11) NOT NULL auto_increment,
159 `name` varchar(254) NOT NULL,
160 `remarks` text NOT NULL,
161 `owner_id` int(11) default NULL,
162 `code` varchar(32) NOT NULL,
163 `active` int(11) default '1',
164 `type` varchar(1) NOT NULL default 'P',
165 `client_id` int(11) NOT NULL default '0',
166 `team_id` int(11) NOT NULL default '0',
167 `file_location` varchar(254) NOT NULL default '',
168 `open_date` date default NULL,
169 `open_by` int(11) NOT NULL default '0',
170 PRIMARY KEY USING BTREE (`id`),
171 KEY `plookup` (`code`)
173 alter table Projects add column active int(11) default 1;
174 alter table Projects add index plookup(code);
176 ALTER TABLE Projects ADD COLUMN `type` varchar(1) NOT NULL DEFAULT 'P';
177 ALTER TABLE Projects ADD COLUMN `client_id` int(11) NOT NULL DEFAULT 0 ;
178 ALTER TABLE Projects ADD COLUMN `team_id` int(11) NOT NULL DEFAULT 0;
179 ALTER TABLE Projects ADD COLUMN `file_location` varchar(254) NOT NULL DEFAULT '';
180 ALTER TABLE Projects ADD COLUMN `open_date` date ;
181 ALTER TABLE Projects ADD COLUMN `close_date` date ;
182 ALTER TABLE Projects ADD COLUMN `open_by` int(11) NOT NULL DEFAULT 0;
184 ALTER TABLE `Projects` ADD COLUMN `countries` varchar(128) NOT NULL;
185 ALTER TABLE `Projects` ADD COLUMN `languages` varchar(128) NOT NULL;
187 ALTER TABLE Projects ADD COLUMN agency_id int(11) NOT NULL DEFAULT 0 ;
192 CREATE TABLE `Images` (
193 `id` int(11) NOT NULL auto_increment,
194 `filename` varchar(255) NOT NULL default '',
195 `ontable` varchar(32) NOT NULL default '',
196 `onid` int(11) NOT NULL default '0',
197 `mimetype` varchar(64) NOT NULL default '',
198 `width` int(11) NOT NULL default '0',
199 `height` int(11) NOT NULL default '0',
200 `filesize` int(11) NOT NULL default '0',
201 `displayorder` int(11) NOT NULL default '0',
202 `language` varchar(6) NOT NULL default 'en',
203 `parent_image_id` int(11) NOT NULL default '0',
209 ALTER TABLE images ADD COLUMN `width` int(11) NOT NULL default '0';
210 ALTER TABLE images ADD COLUMN `height` int(11) NOT NULL default '0';
211 ALTER TABLE images ADD COLUMN `filesize` int(11) NOT NULL default '0';
212 ALTER TABLE images ADD COLUMN `displayorder` int(11) NOT NULL default '0';
213 ALTER TABLE images ADD COLUMN `language` varchar(6) NOT NULL default 'en';
214 ALTER TABLE images ADD COLUMN `parent_image_id` int(11) NOT NULL default '0';
218 ALTER TABLE `Images` ADD INDEX `lookup`(`ontable`, `onid`);
220 ALTER TABLE `Images` ADD COLUMN `created` datetime NOT NULL;
221 ALTER TABLE `Images` ADD COLUMN `imgtype` VARCHAR(32) DEFAULT '' NOT NULL;
222 ALTER TABLE `Images` ADD COLUMN `linkurl` VARCHAR(254) DEFAULT '' NOT NULL;
223 ALTER TABLE `Images` ADD COLUMN `descript` TEXT DEFAULT '' NOT NULL;
224 ALTER TABLE `Images` ADD COLUMN `title` VARCHAR(128) DEFAULT '' NOT NULL;
226 CREATE TABLE `core_image_type` (
227 `id` int(11) NOT NULL AUTO_INCREMENT,
228 `name` varchar(64) NOT NULL,
235 CREATE TABLE `i18n` (
236 `id` int(11) NOT NULL AUTO_INCREMENT,
237 `ltype` varchar(1) NOT NULL,
238 `lkey` varchar(8) NOT NULL,
239 `inlang` varchar(8) NOT NULL,
240 `lval` varchar(64) NOT NULL,
242 INDEX `lookup`(`ltype`, `lkey`, `inlang`)
246 DROP FUNCTION IF EXISTS i18n_translate;
249 CREATE FUNCTION i18n_translate(in_ltype varchar(1) , in_lkey varchar(8), in_inlang varchar(8))
250 RETURNS VARCHAR(64) DETERMINISTIC
252 DECLARE ret VARCHAR(64);
254 SELECT lval INTO ret FROM i18n
255 WHERE ltype=in_ltype AND lkey=in_lkey and inlang=in_inlang LIMIT 1;
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,
270 INDEX `lookup`(`on_table`, `on_id`, `person_id`, `created`)
276 CREATE TABLE `translations` (
277 `id` int(11) NOT NULL AUTO_INCREMENT,
278 `module` varchar(64) NOT NULL,
279 tfile varchar(128) NOT NULL,
280 tlang varchar(8) NOT NULL,
281 tkey varchar(32) NOT NULL,
282 tval longtext NOT NULL,
286 ALTER TABLE translations ADD INDEX qlookup (module, tfile, tlang, tkey);