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;
45 CREATE TABLE `Events` (
46 `id` int(11) NOT NULL auto_increment,
47 `person_name` varchar(128) collate utf8_unicode_ci default NULL,
48 `event_when` datetime default NULL,
49 `action` varchar(32) collate utf8_unicode_ci default NULL,
50 `ipaddr` varchar(16) collate utf8_unicode_ci default NULL,
51 `on_id` int(11) default NULL,
52 `on_table` varchar(64) collate utf8_unicode_ci default NULL,
53 `person_id` int(11) default NULL,
54 `remarks` text collate utf8_unicode_ci,
59 ALTER TABLE Events CHANGE COLUMN EventID id INT(11) AUTO_INCREMENT NOT NULL;
60 ALTER TABLE Events CHANGE COLUMN User person_name VARCHAR(128);
61 ALTER TABLE Events ADD COLUMN person_id INT(11);
62 ALTER TABLE Events CHANGE COLUMN Date event_when DATETIME;
63 ALTER TABLE Events CHANGE COLUMN Event action VARCHAR(32);
64 ALTER TABLE Events CHANGE COLUMN Host ipaddr VARCHAR(16);
65 ALTER TABLE Events CHANGE COLUMN ItemID on_id INT(11);
66 ALTER TABLE Events CHANGE COLUMN Container on_table VARCHAR(64);
67 ALTER TABLE Events ADD COLUMN remarks INT(11);
71 CREATE TABLE `Group_Members` (
72 `group_id` int(11) default NULL,
73 `id` int(11) NOT NULL auto_increment,
74 `user_id` int(11) NOT NULL default '0',
79 CREATE TABLE `Group_Rights` (
80 `rightname` varchar(64) collate utf8_unicode_ci NOT NULL,
81 `group_id` int(11) NOT NULL,
82 `AccessMask` varchar(10) collate utf8_unicode_ci NOT NULL,
83 `id` int(11) NOT NULL auto_increment,
90 CREATE TABLE `Groups` (
91 `id` int(11) NOT NULL auto_increment,
92 `name` varchar(64) collate utf8_unicode_ci NOT NULL,
93 `type` int(11) default NULL,
94 `leader` int(11) NOT NULL default '0',
95 PRIMARY KEY USING BTREE (`id`)
100 alter table Groups add column type int(11) default 0;
101 ALTER TABLE `Groups` ADD COLUMN `leader` int(11) NOT NULL default 0;
102 ALTER TABLE Groups CHANGE COLUMN type type int(11) default 0;
107 CREATE TABLE `Office` (
108 `id` int(11) NOT NULL auto_increment,
109 `company_id` int(11) NOT NULL default '0',
110 `name` varchar(64) character set latin1 NOT NULL,
111 `address` text character set latin1 NOT NULL,
112 `phone` varchar(32) character set latin1 NOT NULL,
113 `fax` varchar(32) character set latin1 NOT NULL,
114 `email` varchar(128) character set latin1 NOT NULL,
115 `role` varchar(32) character set latin1 NOT NULL,
119 CREATE TABLE `Person` (
120 `id` int(11) NOT NULL auto_increment,
121 `office_id` int(11) default '0',
122 `name` varchar(128) character set latin1 NOT NULL,
123 `phone` varchar(32) character set latin1 NOT NULL,
124 `fax` varchar(32) character set latin1 NOT NULL,
125 `email` varchar(128) character set latin1 NOT NULL,
126 `company_id` int(11) default '0',
127 `role` varchar(32) character set latin1 NOT NULL,
128 `active` int(11) default NULL,
129 `remarks` text NOT NULL,
130 `passwd` varchar(64) NOT NULL,
131 `owner_id` int(11) NOT NULL,
132 `lang` varchar(8) default 'en',
133 `no_reset_sent` int(11) default '0',
139 ALTER TABLE Person ADD COLUMN no_reset_sent INT(11) DEFAULT 0;
140 ALTER TABLE Person ADD COLUMN action_type VARCHAR(32) DEFAULT '';
141 ALTER TABLE Person CHANGE COLUMN active active int(11);
142 ALTER TABLE Person ADD COLUMN project_id int(11) default 0;
144 ALTER TABLE Person ADD COLUMN action_type VARCHAR(32) default '';
149 CREATE TABLE `Projects` (
150 `id` int(11) NOT NULL auto_increment,
151 `name` varchar(254) collate utf8_unicode_ci NOT NULL,
152 `remarks` text collate utf8_unicode_ci NOT NULL,
153 `owner_id` int(11) default NULL,
154 `code` varchar(32) collate utf8_unicode_ci NOT NULL,
155 `active` int(11) default '1',
156 `type` varchar(1) collate utf8_unicode_ci NOT NULL default 'P',
157 `client_id` int(11) NOT NULL default '0',
158 `team_id` int(11) NOT NULL default '0',
159 `file_location` varchar(254) collate utf8_unicode_ci NOT NULL default '',
160 `open_date` date default NULL,
161 `open_by` int(11) NOT NULL default '0',
162 PRIMARY KEY USING BTREE (`id`),
163 KEY `plookup` (`code`)
165 alter table Projects add column active int(11) default 1;
166 alter table Projects add index plookup(code);
168 ALTER TABLE Projects ADD COLUMN `type` varchar(1) NOT NULL DEFAULT 'P';
169 ALTER TABLE Projects ADD COLUMN `client_id` int(11) NOT NULL DEFAULT 0 ;
170 ALTER TABLE Projects ADD COLUMN `team_id` int(11) NOT NULL DEFAULT 0;
171 ALTER TABLE Projects ADD COLUMN `file_location` varchar(254) NOT NULL DEFAULT '';
172 ALTER TABLE Projects ADD COLUMN `open_date` date ;
173 ALTER TABLE Projects ADD COLUMN `close_date` date ;
174 ALTER TABLE Projects ADD COLUMN `open_by` int(11) NOT NULL DEFAULT 0;
176 ALTER TABLE `Projects` ADD COLUMN `countries` varchar(128) NOT NULL;
177 ALTER TABLE `Projects` ADD COLUMN `languages` varchar(128) NOT NULL;
179 ALTER TABLE Projects ADD COLUMN agency_id int(11) NOT NULL DEFAULT 0 ;
184 CREATE TABLE `Images` (
185 `id` int(11) NOT NULL auto_increment,
186 `filename` varchar(255) NOT NULL default '',
187 `ontable` varchar(32) NOT NULL default '',
188 `onid` int(11) NOT NULL default '0',
189 `mimetype` varchar(64) NOT NULL default '',
190 `width` int(11) NOT NULL default '0',
191 `height` int(11) NOT NULL default '0',
192 `filesize` int(11) NOT NULL default '0',
193 `displayorder` int(11) NOT NULL default '0',
194 `language` varchar(6) NOT NULL default 'en',
195 `parent_image_id` int(11) NOT NULL default '0',
201 ALTER TABLE images ADD COLUMN `width` int(11) NOT NULL default '0';
202 ALTER TABLE images ADD COLUMN `height` int(11) NOT NULL default '0';
203 ALTER TABLE images ADD COLUMN `filesize` int(11) NOT NULL default '0';
204 ALTER TABLE images ADD COLUMN `displayorder` int(11) NOT NULL default '0';
205 ALTER TABLE images ADD COLUMN `language` varchar(6) NOT NULL default 'en';
206 ALTER TABLE images ADD COLUMN `parent_image_id` int(11) NOT NULL default '0';
210 ALTER TABLE `Images` ADD INDEX `lookup`(`ontable`, `onid`);
212 ALTER TABLE `Images` ADD COLUMN `created` datetime NOT NULL;
213 ALTER TABLE `Images` ADD COLUMN `imgtype` VARCHAR(32) DEFAULT '' NOT NULL;
214 ALTER TABLE `Images` ADD COLUMN `linkurl` VARCHAR(254) DEFAULT '' NOT NULL;
215 ALTER TABLE `Images` ADD COLUMN `descript` TEXT DEFAULT '' NOT NULL;
216 ALTER TABLE `Images` ADD COLUMN `title` VARCHAR(128) DEFAULT '' NOT NULL;
222 CREATE TABLE `i18n` (
223 `id` int(11) NOT NULL AUTO_INCREMENT,
224 `ltype` varchar(1) NOT NULL,
225 `lkey` varchar(8) NOT NULL,
226 `inlang` varchar(8) NOT NULL,
227 `lval` varchar(64) NOT NULL,
229 INDEX `lookup`(`ltype`, `lkey`, `inlang`)
233 DROP FUNCTION IF EXISTS i18n_translate;
236 CREATE FUNCTION i18n_translate(in_ltype varchar(1) , in_lkey varchar(8), in_inlang varchar(8))
237 RETURNS VARCHAR(64) DETERMINISTIC
239 DECLARE ret VARCHAR(64);
241 SELECT lval INTO ret FROM i18n
242 WHERE ltype=in_ltype AND lkey=in_lkey and inlang=in_inlang LIMIT 1;