c8tmScuB

1-- -----------------------------------------------------------------------------
2-- CCAREDB STRUCTURE CREATION
3-- -----------------------------------------------------------------------------
4
5
6/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
7/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
8/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
9/*!40101 SET NAMES utf8 */;
10/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
11/*!40103 SET TIME_ZONE='+00:00' */;
12/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
13/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
14/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
15/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
16DROP DATABASE IF EXISTS `ccaredb`;
17
18CREATE DATABASE `ccaredb`;
19USE `ccaredb`;
20
21--
22-- Table structure for table `actions`
23--
24
25DROP TABLE IF EXISTS `actions`;
26/*!40101 SET @saved_cs_client     = @@character_set_client */;
27/*!40101 SET character_set_client = utf8 */;
28CREATE TABLE `actions` (
29  `action_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
30  `session_id` varchar(32) NOT NULL,
31  `action_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
32  `verb` varchar(150) NOT NULL,
33  `return_code` smallint(6) NOT NULL,
34  `duration` int(11) NOT NULL,
35  `params` text,
36  `action_user` varchar(100) NOT NULL,
37  `profile_id` bigint(20) unsigned NOT NULL,
38  `is_risky` tinyint(1) DEFAULT '0',
39  PRIMARY KEY (`action_id`),
40  KEY `session_id` (`session_id`)
41) ENGINE=InnoDB AUTO_INCREMENT=127468 DEFAULT CHARSET=latin1;
42/*!40101 SET character_set_client = @saved_cs_client */;
43
44--
45-- Table structure for table `addons`
46--
47
48DROP TABLE IF EXISTS `addons`;
49/*!40101 SET @saved_cs_client     = @@character_set_client */;
50/*!40101 SET character_set_client = utf8 */;
51CREATE TABLE `addons` (
52  `addon_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
53  `addon_name` varchar(50) NOT NULL,
54  `addon_multi_partners` TINYINT(1) NOT NULL DEFAULT 0,
55  PRIMARY KEY (`addon_id`)
56) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
57/*!40101 SET character_set_client = @saved_cs_client */;
58
59--
60-- Table structure for table `addons_countries`
61--
62
63DROP TABLE IF EXISTS `addons_countries`;
64/*!40101 SET @saved_cs_client     = @@character_set_client */;
65/*!40101 SET character_set_client = utf8 */;
66CREATE TABLE `addons_countries` (
67  `addon_id` bigint(20) unsigned NOT NULL,
68  `country_id` bigint(20) unsigned NOT NULL,
69  `addon_ip` varchar(15) NOT NULL,
70  `addon_port` smallint(6) NOT NULL,
71  `addon_proxy_ip` varchar(15) DEFAULT NULL,
72  `addon_proxy_port` smallint(6) DEFAULT NULL,
73  `addon_rp_ip` varchar(15) DEFAULT NULL,
74  `addon_rp_port` smallint(6) DEFAULT NULL,
75  `addon_share_folder` varchar(200) DEFAULT NULL,
76  `addon_pref_lang` varchar(3) DEFAULT NULL,
77  PRIMARY KEY (`addon_id`,`country_id`),
78  KEY `country_id` (`country_id`),
79  CONSTRAINT `addons_countries_ibfk_1` FOREIGN KEY (`addon_id`) REFERENCES `addons` (`addon_id`),
80  CONSTRAINT `addons_countries_ibfk_2` FOREIGN KEY (`country_id`) REFERENCES `countries` (`country_id`)
81) ENGINE=InnoDB DEFAULT CHARSET=latin1;
82/*!40101 SET character_set_client = @saved_cs_client */;
83
84--
85-- Table structure for table `addons_has_profiles`
86--
87
88DROP TABLE IF EXISTS `addons_has_profiles`;
89/*!40101 SET @saved_cs_client     = @@character_set_client */;
90/*!40101 SET character_set_client = utf8 */;
91CREATE TABLE `addons_has_profiles` (
92  `addon_id` bigint(20) unsigned NOT NULL,
93  `profile_id` bigint(20) unsigned NOT NULL,
94  `country_id` bigint(20) unsigned NOT NULL,
95  `menu_item` varchar(150) NOT NULL,
96  `allowedRO` tinyint(1) NOT NULL,
97  `allowedRW` tinyint(1) NOT NULL,
98  `home_page` tinyint(1) NOT NULL DEFAULT '0',
99  PRIMARY KEY (`addon_id`,`profile_id`,`country_id`,`menu_item`),
100  KEY `profile_id` (`profile_id`),
101  KEY `country_id` (`country_id`),
102  CONSTRAINT `addons_has_profiles_ibfk_1` FOREIGN KEY (`addon_id`) REFERENCES `addons` (`addon_id`),
103  CONSTRAINT `addons_has_profiles_ibfk_2` FOREIGN KEY (`profile_id`) REFERENCES `profiles` (`profile_id`),
104  CONSTRAINT `addons_has_profiles_ibfk_3` FOREIGN KEY (`country_id`) REFERENCES `countries` (`country_id`)
105) ENGINE=InnoDB DEFAULT CHARSET=latin1;
106/*!40101 SET character_set_client = @saved_cs_client */;
107
108--
109-- Table structure for table `auth`
110--
111
112DROP TABLE IF EXISTS `auth`;
113/*!40101 SET @saved_cs_client     = @@character_set_client */;
114/*!40101 SET character_set_client = utf8 */;
115CREATE TABLE `auth` (
116  `auth_id` int(20) unsigned NOT NULL AUTO_INCREMENT,
117  `auth_pass` varchar(250) NOT NULL,
118  PRIMARY KEY (`auth_id`)
119) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
120/*!40101 SET character_set_client = @saved_cs_client */;
121
122--
123-- Table structure for table `countries`
124--
125
126DROP TABLE IF EXISTS `countries`;
127/*!40101 SET @saved_cs_client     = @@character_set_client */;
128/*!40101 SET character_set_client = utf8 */;
129CREATE TABLE `countries` (
130  `country_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
131  `country_name` varchar(100) NOT NULL,
132  `country_code` varchar(5) NOT NULL,
133  PRIMARY KEY (`country_id`),
134  UNIQUE KEY `country_name` (`country_name`),
135  UNIQUE KEY `country_code` (`country_code`)
136) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=latin1;
137/*!40101 SET character_set_client = @saved_cs_client */;
138
139--
140-- Table structure for table `database_versioning`
141--
142
143DROP TABLE IF EXISTS `database_versioning`;
144/*!40101 SET @saved_cs_client     = @@character_set_client */;
145/*!40101 SET character_set_client = utf8 */;
146CREATE TABLE `database_versioning` (
147  `version` varchar(12) NOT NULL,
148  `installation_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
149  `comment` varchar(255) DEFAULT NULL,
150  PRIMARY KEY (`version`),
151  UNIQUE KEY `UI_VERSION` (`version`)
152) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='table used to trace database schema version and updates';
153/*!40101 SET character_set_client = @saved_cs_client */;
154
155--
156-- Table structure for table `profiles`
157--
158
159DROP TABLE IF EXISTS `profiles`;
160/*!40101 SET @saved_cs_client     = @@character_set_client */;
161/*!40101 SET character_set_client = utf8 */;
162CREATE TABLE `profiles` (
163  `profile_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
164  `profile_name` varchar(50) NOT NULL,
165  `display_name` varchar(50) NOT NULL,
166  `partner_id` VARCHAR(50) DEFAULT NULL,
167  `partner_name` VARCHAR(50) DEFAULT NULL,
168  PRIMARY KEY (`profile_id`)
169) ENGINE=InnoDB AUTO_INCREMENT=166 DEFAULT CHARSET=latin1;
170/*!40101 SET character_set_client = @saved_cs_client */;
171
172--
173-- Table structure for table `profiles_can_create`
174--
175
176DROP TABLE IF EXISTS `profiles_can_create`;
177/*!40101 SET @saved_cs_client     = @@character_set_client */;
178/*!40101 SET character_set_client = utf8 */;
179CREATE TABLE `profiles_can_create` (
180  `id_creator` bigint(20) unsigned NOT NULL,
181  `id_created` bigint(20) unsigned NOT NULL,
182  PRIMARY KEY (`id_creator`,`id_created`),
183  KEY `can_create_fk2` (`id_created`),
184  CONSTRAINT `can_create_fk1` FOREIGN KEY (`id_creator`) REFERENCES `profiles` (`profile_id`),
185  CONSTRAINT `can_create_fk2` FOREIGN KEY (`id_created`) REFERENCES `profiles` (`profile_id`)
186) ENGINE=InnoDB DEFAULT CHARSET=latin1;
187/*!40101 SET character_set_client = @saved_cs_client */;
188
189--
190-- Table structure for table `sessions`
191--
192
193DROP TABLE IF EXISTS `sessions`;
194/*!40101 SET @saved_cs_client     = @@character_set_client */;
195/*!40101 SET character_set_client = utf8 */;
196CREATE TABLE `sessions` (
197  `session_id` varchar(32) NOT NULL,
198  `profile_id` bigint(20) unsigned NOT NULL,
199  `start_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
200  PRIMARY KEY (`session_id`),
201  KEY `profile_id` (`profile_id`),
202  CONSTRAINT `sessions_ibfk_1` FOREIGN KEY (`profile_id`) REFERENCES `profiles` (`profile_id`)
203) ENGINE=InnoDB DEFAULT CHARSET=latin1;
204/*!40101 SET character_set_client = @saved_cs_client */;
205/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
206
207/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
208/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
209/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
210/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
211/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
212/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
213/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
214
215
216-- -----------------------------------------------------------------------------
217-- CCAREDB DATA INSERTION
218-- -----------------------------------------------------------------------------
219
220SELECT 'Starting Ccare Database data insert (did you edit the script file before ?)...' AS ' ';
221
222LOCK TABLES `profiles` WRITE;
223/*!40000 ALTER TABLE `profiles` DISABLE KEYS */;
224INSERT INTO `profiles` (profile_id, profile_name, display_name) VALUES (1,'SuperAdminProfile','Super Admin Profile');
225/*!40000 ALTER TABLE `profiles` ENABLE KEYS */;
226UNLOCK TABLES;
227
228LOCK TABLES `profiles_can_create` WRITE;
229INSERT INTO `profiles_can_create` VALUES (1,1);
230UNLOCK TABLES;
231
232LOCK TABLES `auth` WRITE;
233DELETE FROM `auth`;
234-- Edit the key 'useless' here, following your target conf.ini.
235-- Careful, if the encryption key (use + less) is not the same, no one we'll be able to access to the CC.
236SELECT @useless:="ae345fcd19e7a3f45a879ceda18f";
237
238-- Edit the LdapAdmin password (set in the ldif file while inserting it into the Ldap DB)
239INSERT INTO `auth` VALUES (1,AES_ENCRYPT("LdapAdmin8",@useless));
240
241UNLOCK TABLES;
242
243SELECT 'Ccare Database data insert: done.' AS ' ';