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 CHANGE COLUMN active active int(11);
148 ALTER TABLE Person ADD COLUMN project_id int(11) default 0;
150 ALTER TABLE Person ADD COLUMN action_type VARCHAR(32) default '';
152 ALTER TABLE Person ADD COLUMN deleted_by INT(11) NOT NULL default 0 ;
153 ALTER TABLE Person ADD COLUMN deleted_dt DATETIME;;
155 alter table Person change column active active int(11) NOT NULL DEFAULT 1 ;
158 CREATE TABLE `Projects` (
159 `id` int(11) NOT NULL auto_increment,
160 `name` varchar(254) collate utf8_unicode_ci NOT NULL,
161 `remarks` text collate utf8_unicode_ci NOT NULL,
162 `owner_id` int(11) default NULL,
163 `code` varchar(32) collate utf8_unicode_ci NOT NULL,
164 `active` int(11) default '1',
165 `type` varchar(1) collate utf8_unicode_ci NOT NULL default 'P',
166 `client_id` int(11) NOT NULL default '0',
167 `team_id` int(11) NOT NULL default '0',
168 `file_location` varchar(254) collate utf8_unicode_ci NOT NULL default '',
169 `open_date` date default NULL,
170 `open_by` int(11) NOT NULL default '0',
171 PRIMARY KEY USING BTREE (`id`),
172 KEY `plookup` (`code`)
174 alter table Projects add column active int(11) default 1;
175 alter table Projects add index plookup(code);
177 ALTER TABLE Projects ADD COLUMN `type` varchar(1) NOT NULL DEFAULT 'P';
178 ALTER TABLE Projects ADD COLUMN `client_id` int(11) NOT NULL DEFAULT 0 ;
179 ALTER TABLE Projects ADD COLUMN `team_id` int(11) NOT NULL DEFAULT 0;
180 ALTER TABLE Projects ADD COLUMN `file_location` varchar(254) NOT NULL DEFAULT '';
181 ALTER TABLE Projects ADD COLUMN `open_date` date ;
182 ALTER TABLE Projects ADD COLUMN `close_date` date ;
183 ALTER TABLE Projects ADD COLUMN `open_by` int(11) NOT NULL DEFAULT 0;
185 ALTER TABLE `Projects` ADD COLUMN `countries` varchar(128) NOT NULL;
186 ALTER TABLE `Projects` ADD COLUMN `languages` varchar(128) NOT NULL;
188 ALTER TABLE Projects ADD COLUMN agency_id int(11) NOT NULL DEFAULT 0 ;
193 CREATE TABLE `Images` (
194 `id` int(11) NOT NULL auto_increment,
195 `filename` varchar(255) NOT NULL default '',
196 `ontable` varchar(32) NOT NULL default '',
197 `onid` int(11) NOT NULL default '0',
198 `mimetype` varchar(64) NOT NULL default '',
199 `width` int(11) NOT NULL default '0',
200 `height` int(11) NOT NULL default '0',
201 `filesize` int(11) NOT NULL default '0',
202 `displayorder` int(11) NOT NULL default '0',
203 `language` varchar(6) NOT NULL default 'en',
204 `parent_image_id` int(11) NOT NULL default '0',
210 ALTER TABLE images ADD COLUMN `width` int(11) NOT NULL default '0';
211 ALTER TABLE images ADD COLUMN `height` int(11) NOT NULL default '0';
212 ALTER TABLE images ADD COLUMN `filesize` int(11) NOT NULL default '0';
213 ALTER TABLE images ADD COLUMN `displayorder` int(11) NOT NULL default '0';
214 ALTER TABLE images ADD COLUMN `language` varchar(6) NOT NULL default 'en';
215 ALTER TABLE images ADD COLUMN `parent_image_id` int(11) NOT NULL default '0';
219 ALTER TABLE `Images` ADD INDEX `lookup`(`ontable`, `onid`);
221 ALTER TABLE `Images` ADD COLUMN `created` datetime NOT NULL;
222 ALTER TABLE `Images` ADD COLUMN `imgtype` VARCHAR(32) DEFAULT '' NOT NULL;
223 ALTER TABLE `Images` ADD COLUMN `linkurl` VARCHAR(254) DEFAULT '' NOT NULL;
224 ALTER TABLE `Images` ADD COLUMN `descript` TEXT DEFAULT '' NOT NULL;
225 ALTER TABLE `Images` ADD COLUMN `title` VARCHAR(128) DEFAULT '' NOT NULL;
227 CREATE TABLE `core_image_type` (
228 `id` int(11) NOT NULL AUTO_INCREMENT,
229 `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,
243 INDEX `lookup`(`ltype`, `lkey`, `inlang`)
247 DROP FUNCTION IF EXISTS i18n_translate;
250 CREATE FUNCTION i18n_translate(in_ltype varchar(1) , in_lkey varchar(8), in_inlang varchar(8))
251 RETURNS VARCHAR(64) DETERMINISTIC
253 DECLARE ret VARCHAR(64);
255 SELECT lval INTO ret FROM i18n
256 WHERE ltype=in_ltype AND lkey=in_lkey and inlang=in_inlang LIMIT 1;
264 CREATE TABLE core_locking (
265 `int` int(11) NOT NULL AUTO_INCREMENT,
266 `on_table` varchar(64) NOT NULL,
267 `on_id` int(11) NOT NULL,
268 `person_id` int(11) NOT NULL,
269 `created` datetime NOT NULL,
271 INDEX `lookup`(`on_table`, `on_id`, `person_id`, `created`)