1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
|
/**
* @licence app begin@
* Copyright (C) 2017 PSA GROUP
*
* This file is part of GENIVI Project POISearch Proof Of Concept [POIPOCSupplier].
*
* Contributions are licensed to the GENIVI Alliance under one or more
* Contribution License Agreements.
*
* \copyright
* This Source Code Form is subject to the terms of the
* Mozilla Public License, v. 2.0. If a copy of the MPL was not distributed with
* this file, You can obtain one at http://mozilla.org/MPL/2.0/.
*
* \author Philippe Colliot <philippe.colliot@mpsa.com
*
* \file CreateScheme.sql
* Modifications:
* 19-12-2013 Philippe Colliot
* add new fields to poi (brand and operateur)
* 04-07-2017 Philippe Colliot
* add COLLATE NOCASE to poi name
*
* For further information see http://www.genivi.org/.
* @licence end@
*/
/*
* Table of available area
*/
DROP TABLE IF EXISTS availablearea;
CREATE TABLE availablearea (
Id int NOT NULL,
leftlongitude double DEFAULT NULL,
bottomlatitude double DEFAULT NULL,
rightlongitude double DEFAULT NULL,
toplatitude double DEFAULT NULL,
PRIMARY KEY (Id)
);
/*
* Table of available attribute
*/
DROP TABLE IF EXISTS poiattribute;
CREATE TABLE poiattribute (
Id int NOT NULL,
name text UNIQUE,
PRIMARY KEY (Id)
);
/*
* Table of poi
*/
DROP TABLE IF EXISTS poi;
CREATE TABLE poi (
Id int NOT NULL,
name text UNIQUE COLLATE NOCASE,
latitude double DEFAULT NULL,
longitude double DEFAULT NULL,
altitude int DEFAULT NULL,
segment int DEFAULT NULL,
offset int DEFAULT NULL,
source text,
website text,
phone text,
stars int,
openingHours text,
addrHouseNumber text,
addrStreet text,
addrPostCode int,
addrCity text,
brand text,
operateur text,
PRIMARY KEY (Id)
);
/*
* Table of category
*/
DROP TABLE IF EXISTS poicategory;
CREATE TABLE poicategory (
Id int NOT NULL,
name text UNIQUE,
PRIMARY KEY (Id)
);
/*
* Table of kinship between categories
*/
DROP TABLE IF EXISTS poicategorykinship;
CREATE TABLE poicategorykinship (
Id int NOT NULL,
childId int NOT NULL,
parentId int NOT NULL,
PRIMARY KEY (Id)
);
/*
* Table of provider
*/
DROP TABLE IF EXISTS poiprovider;
CREATE TABLE poiprovider (
Id int NOT NULL,
name text UNIQUE,
PRIMARY KEY (Id)
);
/*
* Table of icons
*/
DROP TABLE IF EXISTS iconset;
CREATE TABLE iconset (
Id int NOT NULL,
width int DEFAULT NULL,
height int DEFAULT NULL,
url text DEFAULT NULL,
format text DEFAULT NULL,
PRIMARY KEY (Id)
);
/*
* Table of media files
*/
DROP TABLE IF EXISTS mediaset;
CREATE TABLE mediaset (
Id int NOT NULL,
description text DEFAULT NULL,
url text DEFAULT NULL,
format text DEFAULT NULL,
PRIMARY KEY (Id)
);
/*
* Link between a poicategory and an attribute
* (followed by the associated triggers)
*/
DROP TABLE IF EXISTS hasattribute;
CREATE TABLE hasattribute (
Id int NOT NULL,
poicategory_Id int NOT NULL,
poiattribute_Id int NOT NULL,
PRIMARY KEY (Id,poiattribute_Id,poicategory_Id),
FOREIGN KEY (poicategory_Id)
REFERENCES poicategory(Id)
FOREIGN KEY (poiattribute_Id)
REFERENCES poiattribute(Id)
);
DROP TRIGGER IF EXISTS trig_poiattribute_Id;
CREATE TRIGGER trig_poiattribute_Id
BEFORE INSERT ON [hasattribute]
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'key violation...')
WHERE (SELECT Id FROM poiattribute WHERE Id = NEW.poiattribute_Id) IS NULL;
END;
DROP TRIGGER IF EXISTS trig_poicategory1_Id;
CREATE TRIGGER trig_poicategory1_Id
BEFORE INSERT ON [hasattribute]
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'key violation...')
WHERE (SELECT Id FROM poicategory WHERE Id = NEW.poicategory_Id) IS NULL;
END;
/*
* Link between a poicategory and an icon (restricted to only one icon per category)
* (followed by the associated triggers)
*/
DROP TABLE IF EXISTS isdisplayedas;
CREATE TABLE isdisplayedas (
Id int NOT NULL,
poicategory_Id int NOT NULL,
iconset_Id int NOT NULL,
PRIMARY KEY (Id,poicategory_Id,iconset_Id),
FOREIGN KEY (poicategory_Id)
REFERENCES poicategory(Id)
FOREIGN KEY (iconset_Id)
REFERENCES iconset(Id)
);
DROP TRIGGER IF EXISTS trig_iconset_Id;
CREATE TRIGGER trig_iconset_Id
BEFORE INSERT ON [isdisplayedas]
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'key violation...')
WHERE (SELECT Id FROM iconset WHERE Id = NEW.iconset_Id) IS NULL;
END;
DROP TRIGGER IF EXISTS trig_poicategory_Id;
CREATE TRIGGER trig_poicategory_Id
BEFORE INSERT ON [isdisplayedas]
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'key violation...')
WHERE (SELECT Id FROM poicategory WHERE Id = NEW.poicategory_Id) IS NULL;
END;
/*
* Link between a poi, a poicategory and a poiprovider
* (followed by the associated triggers)
*/
DROP TABLE IF EXISTS belongsto;
CREATE TABLE belongsto (
Id int NOT NULL,
poi_Id int NOT NULL,
poicategory_Id int NOT NULL,
poiprovider_Id int NOT NULL,
PRIMARY KEY (Id,poi_Id,poicategory_Id,poiprovider_Id),
FOREIGN KEY (poiprovider_Id)
REFERENCES poiprovider(Id)
FOREIGN KEY (poicategory_Id)
REFERENCES poicategory(Id)
FOREIGN KEY (poi_Id)
REFERENCES poi(Id)
);
DROP TRIGGER IF EXISTS trig_poiprovider_Id;
CREATE TRIGGER trig_poiprovider_Id
BEFORE INSERT ON [belongsto]
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'key violation...')
WHERE (SELECT Id FROM poiprovider WHERE Id = NEW.poiprovider_Id) IS NULL;
END;
DROP TRIGGER IF EXISTS trig_poicategory_Id;
CREATE TRIGGER trig_poicategory_Id
BEFORE INSERT ON [belongsto]
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'key violation...')
WHERE (SELECT Id FROM poicategory WHERE Id = NEW.poicategory_Id) IS NULL;
END;
DROP TRIGGER IF EXISTS trig_poi_Id;
CREATE TRIGGER trig_poi_Id
BEFORE INSERT ON [belongsto]
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'key violation...')
WHERE (SELECT Id FROM poi WHERE Id = NEW.poi_Id) IS NULL;
END;
/*
* Link between a poi and an media file (restricted to only one media file per poi)
* (followed by the associated triggers)
*/
DROP TABLE IF EXISTS isshownas;
CREATE TABLE isshownas (
Id int NOT NULL,
poi_Id int NOT NULL,
mediaset_Id int NOT NULL,
PRIMARY KEY (Id,poi_Id,mediaset_Id),
FOREIGN KEY (poi_Id)
REFERENCES poi(Id)
FOREIGN KEY (mediaset_Id)
REFERENCES mediaset(Id)
);
DROP TRIGGER IF EXISTS trig_mediaset_Id;
CREATE TRIGGER trig_mediaset_Id
BEFORE INSERT ON [isshownas]
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'key violation...')
WHERE (SELECT Id FROM mediaset WHERE Id = NEW.mediaset_Id) IS NULL;
END;
DROP TRIGGER IF EXISTS trig_poi1_Id;
CREATE TRIGGER trig_poi1_Id
BEFORE INSERT ON [isshownas]
FOR EACH ROW BEGIN
SELECT RAISE(ROLLBACK, 'key violation...')
WHERE (SELECT Id FROM poi WHERE Id = NEW.poi_Id) IS NULL;
END;
|
