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 '';
146 ALTER TABLE Person ADD COLUMN deleted_by INT(11) NOT NULL default 0 ;
147 ALTER TABLE Person ADD COLUMN deleted_dt DATETIME;;
151 CREATE TABLE `Projects` (
152 `id` int(11) NOT NULL auto_increment,
153 `name` varchar(254) collate utf8_unicode_ci NOT NULL,
154 `remarks` text collate utf8_unicode_ci NOT NULL,
155 `owner_id` int(11) default NULL,
156 `code` varchar(32) collate utf8_unicode_ci NOT NULL,
157 `active` int(11) default '1',
158 `type` varchar(1) collate utf8_unicode_ci NOT NULL default 'P',
159 `client_id` int(11) NOT NULL default '0',
160 `team_id` int(11) NOT NULL default '0',
161 `file_location` varchar(254) collate utf8_unicode_ci NOT NULL default '',
162 `open_date` date default NULL,
163 `open_by` int(11) NOT NULL default '0',
164 PRIMARY KEY USING BTREE (`id`),
165 KEY `plookup` (`code`)
167 alter table Projects add column active int(11) default 1;
168 alter table Projects add index plookup(code);
170 ALTER TABLE Projects ADD COLUMN `type` varchar(1) NOT NULL DEFAULT 'P';
171 ALTER TABLE Projects ADD COLUMN `client_id` int(11) NOT NULL DEFAULT 0 ;
172 ALTER TABLE Projects ADD COLUMN `team_id` int(11) NOT NULL DEFAULT 0;
173 ALTER TABLE Projects ADD COLUMN `file_location` varchar(254) NOT NULL DEFAULT '';
174 ALTER TABLE Projects ADD COLUMN `open_date` date ;
175 ALTER TABLE Projects ADD COLUMN `close_date` date ;
176 ALTER TABLE Projects ADD COLUMN `open_by` int(11) NOT NULL DEFAULT 0;
178 ALTER TABLE `Projects` ADD COLUMN `countries` varchar(128) NOT NULL;
179 ALTER TABLE `Projects` ADD COLUMN `languages` varchar(128) NOT NULL;
181 ALTER TABLE Projects ADD COLUMN agency_id int(11) NOT NULL DEFAULT 0 ;
186 CREATE TABLE `Images` (
187 `id` int(11) NOT NULL auto_increment,
188 `filename` varchar(255) NOT NULL default '',
189 `ontable` varchar(32) NOT NULL default '',
190 `onid` int(11) NOT NULL default '0',
191 `mimetype` varchar(64) NOT NULL default '',
192 `width` int(11) NOT NULL default '0',
193 `height` int(11) NOT NULL default '0',
194 `filesize` int(11) NOT NULL default '0',
195 `displayorder` int(11) NOT NULL default '0',
196 `language` varchar(6) NOT NULL default 'en',
197 `parent_image_id` int(11) NOT NULL default '0',
203 ALTER TABLE images ADD COLUMN `width` int(11) NOT NULL default '0';
204 ALTER TABLE images ADD COLUMN `height` int(11) NOT NULL default '0';
205 ALTER TABLE images ADD COLUMN `filesize` int(11) NOT NULL default '0';
206 ALTER TABLE images ADD COLUMN `displayorder` int(11) NOT NULL default '0';
207 ALTER TABLE images ADD COLUMN `language` varchar(6) NOT NULL default 'en';
208 ALTER TABLE images ADD COLUMN `parent_image_id` int(11) NOT NULL default '0';
212 ALTER TABLE `Images` ADD INDEX `lookup`(`ontable`, `onid`);
214 ALTER TABLE `Images` ADD COLUMN `created` datetime NOT NULL;
215 ALTER TABLE `Images` ADD COLUMN `imgtype` VARCHAR(32) DEFAULT '' NOT NULL;
216 ALTER TABLE `Images` ADD COLUMN `linkurl` VARCHAR(254) DEFAULT '' NOT NULL;
217 ALTER TABLE `Images` ADD COLUMN `descript` TEXT DEFAULT '' NOT NULL;
218 ALTER TABLE `Images` ADD COLUMN `title` VARCHAR(128) DEFAULT '' NOT NULL;
224 CREATE TABLE `i18n` (
225 `id` int(11) NOT NULL AUTO_INCREMENT,
226 `ltype` varchar(1) NOT NULL,
227 `lkey` varchar(8) NOT NULL,
228 `inlang` varchar(8) NOT NULL,
229 `lval` varchar(64) NOT NULL,
231 INDEX `lookup`(`ltype`, `lkey`, `inlang`)
235 DROP FUNCTION IF EXISTS i18n_translate;
238 CREATE FUNCTION i18n_translate(in_ltype varchar(1) , in_lkey varchar(8), in_inlang varchar(8))
239 RETURNS VARCHAR(64) DETERMINISTIC
241 DECLARE ret VARCHAR(64);
243 SELECT lval INTO ret FROM i18n
244 WHERE ltype=in_ltype AND lkey=in_lkey and inlang=in_inlang LIMIT 1;