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;
34 UPDATE Companies set comptype='OWNER' where isOwner=1;
38 CREATE TABLE `Events` (
39 `id` int(11) NOT NULL auto_increment,
40 `person_name` varchar(128) collate utf8_unicode_ci default NULL,
41 `event_when` datetime default NULL,
42 `action` varchar(32) collate utf8_unicode_ci default NULL,
43 `ipaddr` varchar(16) collate utf8_unicode_ci default NULL,
44 `on_id` int(11) default NULL,
45 `on_table` varchar(64) collate utf8_unicode_ci default NULL,
46 `person_id` int(11) default NULL,
47 `remarks` text collate utf8_unicode_ci,
52 ALTER TABLE Events CHANGE COLUMN EventID id INT(11) AUTO_INCREMENT NOT NULL;
53 ALTER TABLE Events CHANGE COLUMN User person_name VARCHAR(128);
54 ALTER TABLE Events ADD COLUMN person_id INT(11);
55 ALTER TABLE Events CHANGE COLUMN Date event_when DATETIME;
56 ALTER TABLE Events CHANGE COLUMN Event action VARCHAR(32);
57 ALTER TABLE Events CHANGE COLUMN Host ipaddr VARCHAR(16);
58 ALTER TABLE Events CHANGE COLUMN ItemID on_id INT(11);
59 ALTER TABLE Events CHANGE COLUMN Container on_table VARCHAR(64);
60 ALTER TABLE Events ADD COLUMN remarks INT(11);
64 CREATE TABLE `Group_Members` (
65 `group_id` int(11) default NULL,
66 `id` int(11) NOT NULL auto_increment,
67 `user_id` int(11) NOT NULL default '0',
72 CREATE TABLE `Group_Rights` (
73 `rightname` varchar(64) collate utf8_unicode_ci NOT NULL,
74 `group_id` int(11) NOT NULL,
75 `AccessMask` varchar(10) collate utf8_unicode_ci NOT NULL,
76 `id` int(11) NOT NULL auto_increment,
83 CREATE TABLE `Groups` (
84 `id` int(11) NOT NULL auto_increment,
85 `name` varchar(64) collate utf8_unicode_ci NOT NULL,
86 `type` int(11) default NULL,
87 `leader` int(11) NOT NULL default '0',
88 PRIMARY KEY USING BTREE (`id`)
93 alter table Groups add column type int(11) default 0;
94 ALTER TABLE `Groups` ADD COLUMN `leader` int(11) NOT NULL default 0;
95 ALTER TABLE Groups CHANGE COLUMN type type int(11) default 0;
100 CREATE TABLE `Office` (
101 `id` int(11) NOT NULL auto_increment,
102 `company_id` int(11) NOT NULL default '0',
103 `name` varchar(64) character set latin1 NOT NULL,
104 `address` text character set latin1 NOT NULL,
105 `phone` varchar(32) character set latin1 NOT NULL,
106 `fax` varchar(32) character set latin1 NOT NULL,
107 `email` varchar(128) character set latin1 NOT NULL,
108 `role` varchar(32) character set latin1 NOT NULL,
112 CREATE TABLE `Person` (
113 `id` int(11) NOT NULL auto_increment,
114 `office_id` int(11) default '0',
115 `name` varchar(128) character set latin1 NOT NULL,
116 `phone` varchar(32) character set latin1 NOT NULL,
117 `fax` varchar(32) character set latin1 NOT NULL,
118 `email` varchar(128) character set latin1 NOT NULL,
119 `company_id` int(11) default '0',
120 `role` varchar(32) character set latin1 NOT NULL,
121 `active` int(11) default NULL,
122 `remarks` text NOT NULL,
123 `passwd` varchar(64) NOT NULL,
124 `owner_id` int(11) NOT NULL,
125 `lang` varchar(8) default 'en',
126 `no_reset_sent` int(11) default '0',
132 ALTER TABLE Person ADD COLUMN no_reset_sent INT(11) DEFAULT 0;
133 ALTER TABLE Person ADD COLUMN action_type VARCHAR(32) DEFAULT '';
134 ALTER TABLE Person CHANGE COLUMN active active int(11);
135 ALTER TABLE Person ADD COLUMN project_id int(11) default 0;
137 ALTER TABLE Person ADD COLUMN action_type VARCHAR(32) default '';
142 CREATE TABLE `ProjectDirectory` (
143 `id` int(11) NOT NULL auto_increment,
144 `project_id` int(11) NOT NULL,
145 `person_id` int(11) NOT NULL,
146 `ispm` int(11) NOT NULL,
147 `office_id` int(11) default NULL,
148 `company_id` int(11) default NULL,
149 `role` varchar(16) NOT NULL,
153 CREATE TABLE `Projects` (
154 `id` int(11) NOT NULL auto_increment,
155 `name` varchar(254) collate utf8_unicode_ci NOT NULL,
156 `remarks` text collate utf8_unicode_ci NOT NULL,
157 `owner_id` int(11) default NULL,
158 `code` varchar(32) collate utf8_unicode_ci NOT NULL,
159 `active` int(11) default '1',
160 `type` varchar(1) collate utf8_unicode_ci NOT NULL default 'P',
161 `client_id` int(11) NOT NULL default '0',
162 `team_id` int(11) NOT NULL default '0',
163 `file_location` varchar(254) collate utf8_unicode_ci NOT NULL default '',
164 `open_date` date default NULL,
165 `open_by` int(11) NOT NULL default '0',
166 PRIMARY KEY USING BTREE (`id`),
167 KEY `plookup` (`code`)
169 alter table Projects add column active int(11) default 1;
170 alter table Projects add index plookup(code);
172 ALTER TABLE Projects ADD COLUMN `type` varchar(1) NOT NULL DEFAULT 'P';
173 ALTER TABLE Projects ADD COLUMN `client_id` int(11) NOT NULL DEFAULT 0 ;
174 ALTER TABLE Projects ADD COLUMN `team_id` int(11) NOT NULL DEFAULT 0;
175 ALTER TABLE Projects ADD COLUMN `file_location` varchar(254) NOT NULL DEFAULT '';
176 ALTER TABLE Projects ADD COLUMN `open_date` date ;
177 ALTER TABLE Projects ADD COLUMN `close_date` date ;
178 ALTER TABLE Projects ADD COLUMN `open_by` int(11) NOT NULL DEFAULT 0;
180 ALTER TABLE `Projects` ADD COLUMN `countries` varchar(128) NOT NULL;
181 ALTER TABLE `Projects` ADD COLUMN `languages` varchar(128) NOT NULL;
183 ALTER TABLE Projects ADD COLUMN agency_id int(11) NOT NULL DEFAULT 0 ;
186 CREATE TABLE `Remarks` (
187 `id` int(11) NOT NULL auto_increment,
188 `remark` text NOT NULL,
189 `add_when` datetime NOT NULL,
190 `on_table` varchar(32) NOT NULL,
191 `on_id` int(11) NOT NULL,
192 `person_name` varchar(64) NOT NULL,
193 `person_id` int(11) NOT NULL,
194 `isHTML` int(11) default '0',
195 `action` varchar(32) default '',
197 KEY `qlookup` (`add_when`,`on_table`,`on_id`)
199 alter table Remarks add column isHTML int(11) default 0;
205 CREATE TABLE `Images` (
206 `id` int(11) NOT NULL auto_increment,
207 `filename` varchar(255) NOT NULL default '',
208 `ontable` varchar(32) NOT NULL default '',
209 `onid` int(11) NOT NULL default '0',
210 `mimetype` varchar(64) NOT NULL default '',
211 `width` int(11) NOT NULL default '0',
212 `height` int(11) NOT NULL default '0',
213 `filesize` int(11) NOT NULL default '0',
214 `displayorder` int(11) NOT NULL default '0',
215 `language` varchar(6) NOT NULL default 'en',
216 `parent_image_id` int(11) NOT NULL default '0',
226 ALTER TABLE `Images` ADD INDEX `lookup`(`ontable`, `onid`);
228 ALTER TABLE `Images` ADD COLUMN `created` datetime NOT NULL;
229 ALTER TABLE `Images` ADD COLUMN `imgtype` VARCHAR(32) DEFAULT '' NOT NULL;
230 ALTER TABLE `Images` ADD COLUMN `linkurl` VARCHAR(254) DEFAULT '' NOT NULL;
231 ALTER TABLE `Images` ADD COLUMN `descript` TEXT DEFAULT '' NOT NULL;
232 ALTER TABLE `Images` ADD COLUMN `title` VARCHAR(128) DEFAULT '' NOT NULL;
241 CREATE TABLE `i18n` (
242 `id` int(11) NOT NULL AUTO_INCREMENT,
243 `ltype` varchar(1) NOT NULL,
244 `lkey` varchar(8) NOT NULL,
245 `inlang` varchar(8) NOT NULL,
246 `lval` varchar(64) NOT NULL,
248 INDEX `lookup`(`ltype`, `lkey`, `inlang`)
252 DROP FUNCTION IF EXISTS i18n_translate;
255 CREATE FUNCTION i18n_translate(in_ltype varchar(1) , in_lkey varchar(8), in_inlang varchar(8))
256 RETURNS VARCHAR(64) DETERMINISTIC
258 DECLARE ret VARCHAR(64);
260 SELECT lval INTO ret FROM i18n
261 WHERE ltype=in_ltype AND lkey=in_lkey and inlang=in_inlang LIMIT 1;
269 CREATE TABLE `Signup` (
270 `id` int(11) NOT NULL auto_increment,
271 `name` varchar(128) character set latin1 NOT NULL,
272 `email` varchar(128) character set latin1 NOT NULL,
273 `role` varchar(32) character set latin1 NOT NULL,
274 `company_name` varchar(128) character set latin1 NOT NULL,
275 `person_id` int(11) default 0,
276 `remarks` text NOT NULL,