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