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 `Projects` (
143 `id` int(11) NOT NULL auto_increment,
144 `name` varchar(254) collate utf8_unicode_ci NOT NULL,
145 `remarks` text collate utf8_unicode_ci NOT NULL,
146 `owner_id` int(11) default NULL,
147 `code` varchar(32) collate utf8_unicode_ci NOT NULL,
148 `active` int(11) default '1',
149 `type` varchar(1) collate utf8_unicode_ci NOT NULL default 'P',
150 `client_id` int(11) NOT NULL default '0',
151 `team_id` int(11) NOT NULL default '0',
152 `file_location` varchar(254) collate utf8_unicode_ci NOT NULL default '',
153 `open_date` date default NULL,
154 `open_by` int(11) NOT NULL default '0',
155 PRIMARY KEY USING BTREE (`id`),
156 KEY `plookup` (`code`)
158 alter table Projects add column active int(11) default 1;
159 alter table Projects add index plookup(code);
161 ALTER TABLE Projects ADD COLUMN `type` varchar(1) NOT NULL DEFAULT 'P';
162 ALTER TABLE Projects ADD COLUMN `client_id` int(11) NOT NULL DEFAULT 0 ;
163 ALTER TABLE Projects ADD COLUMN `team_id` int(11) NOT NULL DEFAULT 0;
164 ALTER TABLE Projects ADD COLUMN `file_location` varchar(254) NOT NULL DEFAULT '';
165 ALTER TABLE Projects ADD COLUMN `open_date` date ;
166 ALTER TABLE Projects ADD COLUMN `close_date` date ;
167 ALTER TABLE Projects ADD COLUMN `open_by` int(11) NOT NULL DEFAULT 0;
169 ALTER TABLE `Projects` ADD COLUMN `countries` varchar(128) NOT NULL;
170 ALTER TABLE `Projects` ADD COLUMN `languages` varchar(128) NOT NULL;
172 ALTER TABLE Projects ADD COLUMN agency_id int(11) NOT NULL DEFAULT 0 ;
177 CREATE TABLE `Images` (
178 `id` int(11) NOT NULL auto_increment,
179 `filename` varchar(255) NOT NULL default '',
180 `ontable` varchar(32) NOT NULL default '',
181 `onid` int(11) NOT NULL default '0',
182 `mimetype` varchar(64) NOT NULL default '',
183 `width` int(11) NOT NULL default '0',
184 `height` int(11) NOT NULL default '0',
185 `filesize` int(11) NOT NULL default '0',
186 `displayorder` int(11) NOT NULL default '0',
187 `language` varchar(6) NOT NULL default 'en',
188 `parent_image_id` int(11) NOT NULL default '0',
198 ALTER TABLE `Images` ADD INDEX `lookup`(`ontable`, `onid`);
200 ALTER TABLE `Images` ADD COLUMN `created` datetime NOT NULL;
201 ALTER TABLE `Images` ADD COLUMN `imgtype` VARCHAR(32) DEFAULT '' NOT NULL;
202 ALTER TABLE `Images` ADD COLUMN `linkurl` VARCHAR(254) DEFAULT '' NOT NULL;
203 ALTER TABLE `Images` ADD COLUMN `descript` TEXT DEFAULT '' NOT NULL;
204 ALTER TABLE `Images` ADD COLUMN `title` VARCHAR(128) DEFAULT '' NOT NULL;
210 CREATE TABLE `i18n` (
211 `id` int(11) NOT NULL AUTO_INCREMENT,
212 `ltype` varchar(1) NOT NULL,
213 `lkey` varchar(8) NOT NULL,
214 `inlang` varchar(8) NOT NULL,
215 `lval` varchar(64) NOT NULL,
217 INDEX `lookup`(`ltype`, `lkey`, `inlang`)
221 DROP FUNCTION IF EXISTS i18n_translate;
224 CREATE FUNCTION i18n_translate(in_ltype varchar(1) , in_lkey varchar(8), in_inlang varchar(8))
225 RETURNS VARCHAR(64) DETERMINISTIC
227 DECLARE ret VARCHAR(64);
229 SELECT lval INTO ret FROM i18n
230 WHERE ltype=in_ltype AND lkey=in_lkey and inlang=in_inlang LIMIT 1;
238 CREATE TABLE `Signup` (
239 `id` int(11) NOT NULL auto_increment,
240 `name` varchar(128) character set latin1 NOT NULL,
241 `email` varchar(128) character set latin1 NOT NULL,
242 `role` varchar(32) character set latin1 NOT NULL,
243 `company_name` varchar(128) character set latin1 NOT NULL,
244 `person_id` int(11) default 0,
245 `remarks` text NOT NULL,