ViewVC Help
View File | Revision Log | Show Annotations | View Changeset | Root Listing
root/phenotyper/trunk/PhenotypingSchemeComposer/DatabaseSchema.sql
Revision: 3
Committed: Thu Mar 19 10:27:04 2015 UTC (5 years, 2 months ago) by jgremmels
File size: 12867 byte(s)
Log Message:
Initial import of PhenotypingSchemeComposer.
Line File contents
1 /*
2 Phenotyper - a tool for collecting phenotyping data using mobile terminals
3 Copyright (C) 2015, jgremmels(at)bioinformatics.org
4
5 Phenotyper is free software: you can redistribute it and/or modify
6 it under the terms of the GNU General Public License as published by
7 the Free Software Foundation, either version 3 of the License, or
8 any later version.
9
10 This program is distributed in the hope that it will be useful,
11 but WITHOUT ANY WARRANTY; without even the implied warranty of
12 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 GNU General Public License for more details.
14
15 You should have received a copy of the GNU General Public License
16 along with this program. If not, see <http://www.gnu.org/licenses/>
17
18 Contributors:
19 jgremmels(at)bioinformatics.org - initial API and implementation
20 */
21
22 CREATE DATABASE IF NOT EXISTS `PhenotypingSchemeComposer` /*!40100 DEFAULT CHARACTER SET latin1 */;
23 USE `PhenotypingSchemeComposer`;
24 -- MySQL dump 10.13 Distrib 5.5.16, for Win32 (x86)
25 --
26 -- Host: cosmos Database: PhenotypingSchemeComposer
27 -- ------------------------------------------------------
28 -- Server version 5.1.61
29
30 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
31 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
32 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
33 /*!40101 SET NAMES utf8 */;
34 /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
35 /*!40103 SET TIME_ZONE='+00:00' */;
36 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
37 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
38 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
39 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
40
41 --
42 -- Table structure for table `project_entity`
43 --
44
45 DROP TABLE IF EXISTS `project_entity`;
46 /*!40101 SET @saved_cs_client = @@character_set_client */;
47 /*!40101 SET character_set_client = utf8 */;
48 CREATE TABLE `project_entity` (
49 `project_entity_id` smallint(6) NOT NULL AUTO_INCREMENT,
50 `entity_id` smallint(6) NOT NULL,
51 `project_id` smallint(6) NOT NULL,
52 PRIMARY KEY (`project_entity_id`),
53 KEY `project_entity_fk1` (`entity_id`),
54 KEY `project_entity_fk2` (`project_id`),
55 CONSTRAINT `project_entity_fk1` FOREIGN KEY (`entity_id`) REFERENCES `test_mpiscore_entities` (`OrganID`),
56 CONSTRAINT `project_entity_fk2` FOREIGN KEY (`project_id`) REFERENCES `project` (`project_id`)
57 ) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=latin1;
58 /*!40101 SET character_set_client = @saved_cs_client */;
59
60 --
61 -- Table structure for table `testprogramm_entity`
62 --
63
64 DROP TABLE IF EXISTS `testprogramm_entity`;
65 /*!40101 SET @saved_cs_client = @@character_set_client */;
66 /*!40101 SET character_set_client = utf8 */;
67 CREATE TABLE `testprogramm_entity` (
68 `Testprogramm_Entity_id` smallint(6) NOT NULL AUTO_INCREMENT,
69 `testprogramm` smallint(6) DEFAULT NULL,
70 `Entity` smallint(6) DEFAULT NULL,
71 `Value` smallint(6) DEFAULT NULL,
72 `OrderNumber` smallint(6) DEFAULT NULL,
73 PRIMARY KEY (`Testprogramm_Entity_id`),
74 KEY `testprogramm_entity_FK1` (`testprogramm`),
75 KEY `testprogramm_entity_FK2` (`Entity`),
76 KEY `testprogramm_entity_FK3` (`Value`),
77 CONSTRAINT `testprogramm_entity_FK1` FOREIGN KEY (`testprogramm`) REFERENCES `testprogramme` (`Testprogramm_id`),
78 CONSTRAINT `testprogramm_entity_FK2` FOREIGN KEY (`Entity`) REFERENCES `test_mpiscore_entities` (`OrganID`),
79 CONSTRAINT `testprogramm_entity_FK3` FOREIGN KEY (`Value`) REFERENCES `test_mpiscore_values` (`ValueID`)
80 ) ENGINE=InnoDB AUTO_INCREMENT=1106 DEFAULT CHARSET=latin1;
81 /*!40101 SET character_set_client = @saved_cs_client */;
82
83 --
84 -- Table structure for table `test_mpiscore_entities`
85 --
86
87 DROP TABLE IF EXISTS `test_mpiscore_entities`;
88 /*!40101 SET @saved_cs_client = @@character_set_client */;
89 /*!40101 SET character_set_client = utf8 */;
90 CREATE TABLE `test_mpiscore_entities` (
91 `OrganID` smallint(6) NOT NULL AUTO_INCREMENT,
92 `Parameter` varchar(50) DEFAULT NULL,
93 `Parameter_dt` varchar(50) DEFAULT NULL,
94 `TROST` tinyint(1) DEFAULT NULL,
95 `Arabidopsis` tinyint(1) DEFAULT NULL,
96 `Plant` tinyint(1) DEFAULT NULL,
97 `Population` tinyint(1) DEFAULT NULL,
98 `Ecosystem` tinyint(1) DEFAULT NULL,
99 `PO_id` varchar(50) DEFAULT NULL,
100 `definition` varchar(300) DEFAULT NULL,
101 PRIMARY KEY (`OrganID`)
102 ) ENGINE=InnoDB AUTO_INCREMENT=812 DEFAULT CHARSET=latin1;
103 /*!40101 SET character_set_client = @saved_cs_client */;
104
105 --
106 -- Table structure for table `operator_role`
107 --
108
109 DROP TABLE IF EXISTS `operator_role`;
110 /*!40101 SET @saved_cs_client = @@character_set_client */;
111 /*!40101 SET character_set_client = utf8 */;
112 CREATE TABLE `operator_role` (
113 `operator_role_id` int(11) NOT NULL AUTO_INCREMENT,
114 `operator_id` smallint(5) unsigned NOT NULL,
115 `role_id` int(11) NOT NULL,
116 PRIMARY KEY (`operator_role_id`),
117 KEY `operator_role_fk1` (`operator_id`),
118 KEY `operator_role_fk2` (`role_id`),
119 CONSTRAINT `operator_role_fk1` FOREIGN KEY (`operator_id`) REFERENCES `operator` (`operator_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
120 CONSTRAINT `operator_role_fk2` FOREIGN KEY (`role_id`) REFERENCES `role` (`role_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
121 ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1;
122 /*!40101 SET character_set_client = @saved_cs_client */;
123
124 --
125 -- Table structure for table `bbch0506`
126 --
127
128 DROP TABLE IF EXISTS `bbch0506`;
129 /*!40101 SET @saved_cs_client = @@character_set_client */;
130 /*!40101 SET character_set_client = utf8 */;
131 CREATE TABLE `bbch0506` (
132 `BBCH` smallint(6) NOT NULL AUTO_INCREMENT,
133 `Tomate_d` varchar(200) DEFAULT NULL,
134 `Tomate_e` varchar(200) DEFAULT NULL,
135 `Rice_e` varchar(200) DEFAULT NULL,
136 `Rice_d` varchar(200) DEFAULT NULL,
137 `Ara_d` varchar(200) DEFAULT NULL,
138 `Ara_e` varchar(200) DEFAULT NULL,
139 `Kartoffel_d` varchar(200) DEFAULT NULL,
140 `Kartoffel_e` varchar(200) DEFAULT NULL,
141 PRIMARY KEY (`BBCH`)
142 ) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=latin1;
143 /*!40101 SET character_set_client = @saved_cs_client */;
144
145 --
146 -- Table structure for table `test_object_type`
147 --
148
149 DROP TABLE IF EXISTS `test_object_type`;
150 /*!40101 SET @saved_cs_client = @@character_set_client */;
151 /*!40101 SET character_set_client = utf8 */;
152 CREATE TABLE `test_object_type` (
153 `test_object_type_id` int(11) NOT NULL AUTO_INCREMENT,
154 `test_object_name` varchar(45) NOT NULL,
155 `test_object_name_D` varchar(45) NOT NULL,
156 PRIMARY KEY (`test_object_type_id`)
157 ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
158 /*!40101 SET character_set_client = @saved_cs_client */;
159
160 --
161 -- Table structure for table `test_mpiscore_values`
162 --
163
164 DROP TABLE IF EXISTS `test_mpiscore_values`;
165 /*!40101 SET @saved_cs_client = @@character_set_client */;
166 /*!40101 SET character_set_client = utf8 */;
167 CREATE TABLE `test_mpiscore_values` (
168 `ValueID` smallint(6) NOT NULL AUTO_INCREMENT,
169 `Attribut_E` varchar(50) DEFAULT NULL,
170 `value` varchar(50) DEFAULT NULL,
171 `Attribut_D` varchar(50) DEFAULT NULL,
172 `Wert_D` varchar(50) DEFAULT NULL,
173 `code` smallint(6) DEFAULT NULL,
174 `code_soure` smallint(6) DEFAULT NULL,
175 `TROST` bit(1) DEFAULT NULL,
176 PRIMARY KEY (`ValueID`)
177 ) ENGINE=InnoDB AUTO_INCREMENT=254 DEFAULT CHARSET=latin1;
178 /*!40101 SET character_set_client = @saved_cs_client */;
179
180 --
181 -- Table structure for table `testprogramme_operator`
182 --
183
184 DROP TABLE IF EXISTS `testprogramme_operator`;
185 /*!40101 SET @saved_cs_client = @@character_set_client */;
186 /*!40101 SET character_set_client = utf8 */;
187 CREATE TABLE `testprogramme_operator` (
188 `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
189 `testprogramm` smallint(6) NOT NULL,
190 `operator` smallint(5) unsigned NOT NULL,
191 PRIMARY KEY (`id`),
192 KEY `testprogramme_operator_FK1` (`operator`),
193 KEY `testprogramme_operator_FK2` (`testprogramm`),
194 CONSTRAINT `testprogramme_operator_FK1` FOREIGN KEY (`operator`) REFERENCES `operator` (`operator_id`),
195 CONSTRAINT `testprogramme_operator_FK2` FOREIGN KEY (`testprogramm`) REFERENCES `testprogramme` (`Testprogramm_id`)
196 ) ENGINE=InnoDB AUTO_INCREMENT=70 DEFAULT CHARSET=latin1;
197 /*!40101 SET character_set_client = @saved_cs_client */;
198
199 --
200 -- Table structure for table `project`
201 --
202
203 DROP TABLE IF EXISTS `project`;
204 /*!40101 SET @saved_cs_client = @@character_set_client */;
205 /*!40101 SET character_set_client = utf8 */;
206 CREATE TABLE `project` (
207 `project_id` smallint(6) NOT NULL AUTO_INCREMENT,
208 `name` varchar(45) NOT NULL,
209 PRIMARY KEY (`project_id`)
210 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
211 /*!40101 SET character_set_client = @saved_cs_client */;
212
213 --
214 -- Table structure for table `operator`
215 --
216
217 DROP TABLE IF EXISTS `operator`;
218 /*!40101 SET @saved_cs_client = @@character_set_client */;
219 /*!40101 SET character_set_client = utf8 */;
220 CREATE TABLE `operator` (
221 `operator_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
222 `login` varchar(255) CHARACTER SET ucs2 NOT NULL,
223 `password` varchar(255) CHARACTER SET ucs2 NOT NULL,
224 `first_name` varchar(255) CHARACTER SET ucs2 NOT NULL,
225 `last_name` varchar(255) CHARACTER SET ucs2 NOT NULL,
226 `allow_login` bit(1) NOT NULL DEFAULT b'0',
227 PRIMARY KEY (`operator_id`)
228 ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
229 /*!40101 SET character_set_client = @saved_cs_client */;
230
231 --
232 -- Table structure for table `value_groups`
233 --
234
235 DROP TABLE IF EXISTS `value_groups`;
236 /*!40101 SET @saved_cs_client = @@character_set_client */;
237 /*!40101 SET character_set_client = utf8 */;
238 CREATE TABLE `value_groups` (
239 `value_groups_id` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
240 `value_id` smallint(6) DEFAULT NULL,
241 `value_group` smallint(6) DEFAULT NULL,
242 PRIMARY KEY (`value_groups_id`),
243 KEY `value_groups_FK1` (`value_id`),
244 CONSTRAINT `value_groups_FK1` FOREIGN KEY (`value_id`) REFERENCES `test_mpiscore_values` (`ValueID`)
245 ) ENGINE=InnoDB AUTO_INCREMENT=247 DEFAULT CHARSET=latin1;
246 /*!40101 SET character_set_client = @saved_cs_client */;
247
248 --
249 -- Table structure for table `role`
250 --
251
252 DROP TABLE IF EXISTS `role`;
253 /*!40101 SET @saved_cs_client = @@character_set_client */;
254 /*!40101 SET character_set_client = utf8 */;
255 CREATE TABLE `role` (
256 `role_id` int(11) NOT NULL AUTO_INCREMENT,
257 `name` varchar(45) NOT NULL,
258 `description` varchar(200) DEFAULT NULL,
259 PRIMARY KEY (`role_id`),
260 UNIQUE KEY `role_id` (`role_id`),
261 UNIQUE KEY `name_UNIQUE` (`name`)
262 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
263 /*!40101 SET character_set_client = @saved_cs_client */;
264
265 --
266 -- Table structure for table `project_value`
267 --
268
269 DROP TABLE IF EXISTS `project_value`;
270 /*!40101 SET @saved_cs_client = @@character_set_client */;
271 /*!40101 SET character_set_client = utf8 */;
272 CREATE TABLE `project_value` (
273 `project_value_id` smallint(6) NOT NULL AUTO_INCREMENT,
274 `project_id` smallint(6) NOT NULL,
275 `value_id` smallint(6) NOT NULL,
276 PRIMARY KEY (`project_value_id`),
277 KEY `project_value_fk1` (`value_id`),
278 KEY `project_value_fk2` (`project_id`),
279 CONSTRAINT `project_value_fk1` FOREIGN KEY (`value_id`) REFERENCES `test_mpiscore_values` (`ValueID`),
280 CONSTRAINT `project_value_fk2` FOREIGN KEY (`project_id`) REFERENCES `project` (`project_id`)
281 ) ENGINE=InnoDB AUTO_INCREMENT=247 DEFAULT CHARSET=latin1;
282 /*!40101 SET character_set_client = @saved_cs_client */;
283
284 --
285 -- Table structure for table `testprogramme`
286 --
287
288 DROP TABLE IF EXISTS `testprogramme`;
289 /*!40101 SET @saved_cs_client = @@character_set_client */;
290 /*!40101 SET character_set_client = utf8 */;
291 CREATE TABLE `testprogramme` (
292 `Testprogramm_id` smallint(6) NOT NULL AUTO_INCREMENT,
293 `Testprogrammname` varchar(200) DEFAULT NULL,
294 `Version` varchar(20) DEFAULT NULL,
295 `Messobjekt` varchar(100) DEFAULT NULL,
296 `Description` varchar(500) DEFAULT NULL,
297 `TestObjectType` int(11) NOT NULL,
298 PRIMARY KEY (`Testprogramm_id`),
299 KEY `testprogramme_FK1` (`TestObjectType`),
300 CONSTRAINT `testprogramme_FK1` FOREIGN KEY (`TestObjectType`) REFERENCES `test_object_type` (`test_object_type_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
301 ) ENGINE=InnoDB AUTO_INCREMENT=73 DEFAULT CHARSET=latin1;
302 /*!40101 SET character_set_client = @saved_cs_client */;
303 /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
304
305 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
306 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
307 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
308 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
309 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
310 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
311 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
312
313 -- Dump completed on 2012-11-27 10:45:27

Properties

Name Value
svn:executable