2 CREATE TABLE `Companies` (
3 `code` varchar(32) collate utf8_unicode_ci NOT NULL,
4 `name` varchar(128) collate utf8_unicode_ci default NULL,
5 `remarks` text collate utf8_unicode_ci,
6 `owner_id` int(11) NOT NULL,
7 `address` text collate utf8_unicode_ci,
8 `tel` varchar(32) collate utf8_unicode_ci default NULL,
9 `fax` varchar(32) collate utf8_unicode_ci default NULL,
10 `email` varchar(128) collate utf8_unicode_ci 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) collate utf8_unicode_ci default NULL,
54 `event_when` datetime default NULL,
55 `action` varchar(32) collate utf8_unicode_ci default NULL,
56 `ipaddr` varchar(16) collate utf8_unicode_ci default NULL,
57 `on_id` int(11) default NULL,
58 `on_table` varchar(64) collate utf8_unicode_ci default NULL,
59 `person_id` int(11) default NULL,
60 `remarks` text collate utf8_unicode_ci,
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) collate utf8_unicode_ci NOT NULL,
87 `group_id` int(11) NOT NULL,
88 `AccessMask` varchar(10) collate utf8_unicode_ci 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) collate utf8_unicode_ci 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) collate utf8_unicode_ci NOT NULL,
160 `remarks` text collate utf8_unicode_ci NOT NULL,
161 `owner_id` int(11) default NULL,
162 `code` varchar(32) collate utf8_unicode_ci NOT NULL,
163 `active` int(11) default '1',
164 `type` varchar(1) collate utf8_unicode_ci 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) collate utf8_unicode_ci 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,