-
-
Notifications
You must be signed in to change notification settings - Fork 1.4k
Expand file tree
/
Copy pathRUBiS-create-requests.txt
More file actions
431 lines (393 loc) · 10.7 KB
/
RUBiS-create-requests.txt
File metadata and controls
431 lines (393 loc) · 10.7 KB
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
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
// -----------------------------------------------------------------------------------------------------------------------
// CREATE requests test
//
// The request must have the following format:
// #begin
// <CREATE TABLE request>
// #end
// <isValid flag>: true if this request is valid
//
// If the request is valid:
// <table concerned by the request>
// <columns concerned by the request> (eg: "col1.unique col2 col3", .unique
// means the column is unique.
//
// If the request is not valid:
// <error message>
//
// do not add empty line between the lines defining a test
// line beginning by a // are ignored except in a test
// -----------------------------------------------------------------------------------------------------------------------
// -----------------------------------------------------------------------------------------------------------------------
// RUBiS database (PostgreSQL)
// -----------------------------------------------------------------------------------------------------------------------
#begin
CREATE TABLE categories (
id SERIAL,
name VARCHAR(50),
PRIMARY KEY(id)
)
#end
true
categories
id.unique name
#begin
CREATE TABLE regions (
id SERIAL,
name VARCHAR(25),
PRIMARY KEY(id)
)
#end
true
regions
id.unique name
#begin
CREATE TABLE users (
id SERIAL,
firstname VARCHAR(20),
lastname VARCHAR(20),
nickname VARCHAR(20) NOT NULL UNIQUE,
password VARCHAR(20) NOT NULL,
email VARCHAR(50) NOT NULL,
rating INTEGER,
balance FLOAT,
creation_date DATETIME,
region INTEGER NOT NULL,
PRIMARY KEY(id)
)
#end
true
users
id.unique firstname lastname nickname.unique password email rating balance creation_date region
//CREATE INDEX auth ON users (nickname,password);
//CREATE INDEX region_id ON users (region);
#begin
CREATE TABLE items (
id SERIAL,
name VARCHAR(100),
description TEXT,
initial_price FLOAT NOT NULL,
quantity INTEGER NOT NULL,
reserve_price FLOAT DEFAULT 0,
buy_now FLOAT DEFAULT 0,
nb_of_bids INTEGER DEFAULT 0,
max_bid FLOAT DEFAULT 0,
start_date DATETIME,
end_date DATETIME,
seller INTEGER NOT NULL,
category INTEGER NOT NULL,
PRIMARY KEY(id)
)
#end
true
items
id.unique name description initial_price quantity reserve_price buy_now nb_of_bids max_bid start_date end_date seller category
//CREATE INDEX seller_id ON items (seller);
//CREATE INDEX category_id ON items (category);
#begin
CREATE TABLE old_items (
id SERIAL,
name VARCHAR(100),
description TEXT,
initial_price FLOAT NOT NULL,
quantity INTEGER NOT NULL,
reserve_price FLOAT DEFAULT 0,
buy_now FLOAT DEFAULT 0,
nb_of_bids INTEGER DEFAULT 0,
max_bid FLOAT DEFAULT 0,
start_date DATETIME,
end_date DATETIME,
seller INTEGER NOT NULL,
category INTEGER NOT NULL,
PRIMARY KEY(id)
)
#end
true
old_items
id.unique name description initial_price quantity reserve_price buy_now nb_of_bids max_bid start_date end_date seller category
//CREATE INDEX old_seller_id ON old_items (seller);
//CREATE INDEX old_category_id ON old_items (category);
#begin
CREATE TABLE bids (
id SERIAL,
user_id INTEGER NOT NULL,
item_id INTEGER NOT NULL,
qty INTEGER NOT NULL,
bid FLOAT NOT NULL,
max_bid FLOAT NOT NULL,
date DATETIME,
PRIMARY KEY(id)
)
#end
true
bids
id.unique user_id item_id qty bid max_bid date
//CREATE INDEX bid_item ON bids (item_id);
//CREATE INDEX bid_user ON bids (user_id);
#begin
CREATE TABLE comments (
id SERIAL,
from_user_id INTEGER NOT NULL,
to_user_id INTEGER NOT NULL,
item_id INTEGER NOT NULL,
rating INTEGER,
date DATETIME,
comment TEXT,
PRIMARY KEY(id)
)
#end
true
comments
id.unique from_user_id to_user_id item_id rating date comment
//CREATE INDEX from_user ON comments (from_user_id);
//CREATE INDEX to_user ON comments (to_user_id);
//CREATE INDEX item ON comments (item_id);
#begin
CREATE TABLE buy_now (
id SERIAL,
buyer_id INTEGER NOT NULL,
item_id INTEGER NOT NULL,
qty INTEGER NOT NULL,
date DATETIME,
PRIMARY KEY(id)
)
#end
true
buy_now
id.unique buyer_id item_id qty date
//CREATE INDEX buyer ON buy_now (buyer_id);
//CREATE INDEX buy_now_item ON buy_now (item_id);
#begin
CREATE TABLE ids (
id INTEGER NOT NULL UNIQUE,
category INTEGER NOT NULL,
region INTEGER NOT NULL,
users INTEGER NOT NULL,
item INTEGER NOT NULL,
comment INTEGER NOT NULL,
bid INTEGER NOT NULL,
buyNow INTEGER NOT NULL,
PRIMARY KEY(id)
)
#end
true
ids
id.unique category region users item comment bid buynow
// -----------------------------------------------------------------------------------------------------------------------
// RUBiS database (mysql)
// -----------------------------------------------------------------------------------------------------------------------
#begin
CREATE TABLE categories (
id INTEGER UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT,
name VARCHAR(50),
PRIMARY KEY(id)
)
#end
true
categories
id.unique name
#begin
CREATE TABLE regions (
id INTEGER UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT,
name VARCHAR(25),
PRIMARY KEY(id)
)
#end
true
regions
id.unique name
#begin
CREATE TABLE users (
id INTEGER UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT,
firstname VARCHAR(20),
lastname VARCHAR(20),
nickname VARCHAR(20) NOT NULL UNIQUE,
password VARCHAR(20) NOT NULL,
email VARCHAR(50) NOT NULL,
rating INTEGER,
balance FLOAT,
creation_date DATETIME,
region INTEGER UNSIGNED NOT NULL,
PRIMARY KEY(id),
INDEX auth (nickname,password),
INDEX region_id (region)
)
#end
true
users
id.unique firstname lastname nickname.unique password email rating balance creation_date region
#begin
CREATE TABLE items (
id INTEGER UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT,
name VARCHAR(100),
description TEXT,
initial_price FLOAT UNSIGNED NOT NULL,
quantity INTEGER UNSIGNED NOT NULL,
reserve_price FLOAT UNSIGNED DEFAULT 0,
buy_now FLOAT UNSIGNED DEFAULT 0,
nb_of_bids INTEGER UNSIGNED DEFAULT 0,
max_bid FLOAT UNSIGNED DEFAULT 0,
start_date DATETIME,
end_date DATETIME,
seller INTEGER UNSIGNED NOT NULL,
category INTEGER UNSIGNED NOT NULL,
PRIMARY KEY(id),
INDEX seller_id (seller),
INDEX category_id (category)
)
#end
true
items
id.unique name description initial_price quantity reserve_price buy_now nb_of_bids max_bid start_date end_date seller category
#begin
CREATE TABLE old_items (
id INTEGER UNSIGNED NOT NULL UNIQUE,
name VARCHAR(100),
description TEXT,
initial_price FLOAT UNSIGNED NOT NULL,
quantity INTEGER UNSIGNED NOT NULL,
reserve_price FLOAT UNSIGNED DEFAULT 0,
buy_now FLOAT UNSIGNED DEFAULT 0,
nb_of_bids INTEGER UNSIGNED DEFAULT 0,
max_bid FLOAT UNSIGNED DEFAULT 0,
start_date DATETIME,
end_date DATETIME,
seller INTEGER UNSIGNED NOT NULL,
category INTEGER UNSIGNED NOT NULL,
PRIMARY KEY(id),
INDEX seller_id (seller),
INDEX category_id (category)
)
#end
true
old_items
id.unique name description initial_price quantity reserve_price buy_now nb_of_bids max_bid start_date end_date seller category
#begin
CREATE TABLE bids (
id INTEGER UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT,
user_id INTEGER UNSIGNED NOT NULL,
item_id INTEGER UNSIGNED NOT NULL,
qty INTEGER UNSIGNED NOT NULL,
bid FLOAT UNSIGNED NOT NULL,
max_bid FLOAT UNSIGNED NOT NULL,
date DATETIME,
PRIMARY KEY(id),
INDEX item (item_id),
INDEX user (user_id)
)
#end
true
bids
id.unique user_id item_id qty bid max_bid date
#begin
CREATE TABLE comments (
id INTEGER UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT,
from_user_id INTEGER UNSIGNED NOT NULL,
to_user_id INTEGER UNSIGNED NOT NULL,
item_id INTEGER UNSIGNED NOT NULL,
rating INTEGER,
date DATETIME,
comment TEXT,
PRIMARY KEY(id),
INDEX from_user (from_user_id),
INDEX to_user (to_user_id),
INDEX item (item_id)
)
#end
true
comments
id.unique from_user_id to_user_id item_id rating date comment
#begin
CREATE TABLE buy_now (
id INTEGER UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT,
buyer_id INTEGER UNSIGNED NOT NULL,
item_id INTEGER UNSIGNED NOT NULL,
qty INTEGER UNSIGNED NOT NULL,
date DATETIME,
PRIMARY KEY(id),
INDEX buyer (buyer_id),
INDEX item (item_id)
)
#end
true
buy_now
id.unique buyer_id item_id qty date
#begin
CREATE TABLE ids (
id INTEGER UNSIGNED NOT NULL UNIQUE,
category INTEGER UNSIGNED NOT NULL,
region INTEGER UNSIGNED NOT NULL,
users INTEGER UNSIGNED NOT NULL,
item INTEGER UNSIGNED NOT NULL,
comment INTEGER UNSIGNED NOT NULL,
bid INTEGER UNSIGNED NOT NULL,
buyNow INTEGER UNSIGNED NOT NULL,
PRIMARY KEY(id)
)
#end
true
ids
id.unique category region users item comment bid buynow
#begin
CREATE TEMPORARY TABLE tmp_ids (
id INTEGER PRIMARY KEY,
category INTEGER UNSIGNED NOT NULL
)
#end
true
tmp_ids
id.unique category
#begin
create table test.json_test (
c1 INTEGER PRIMARY KEY,
c2 varchar(20) not null,
c3 json default null
)
#end
true
test.json_test
c1.unique c2 c3
// -----------------------------------------------------------------------------------------------------------------------
// Other tests
// -----------------------------------------------------------------------------------------------------------------------
#begin
CREATE TABLE foo
#end
true
foo
null
#begin
CREATE TABLE foo(id int4, name text
#end
false
Syntax error in this CREATE statement: 'create table foo(id int4, name text'
#begin
CREATE TABLE foo id int4, name text)
#end
false
Syntax error in this CREATE statement: 'create table foo id int4, name text)'
#begin
CREATE TABLE foo(id int4, name)
#end
false
Syntax error in this CREATE statement: 'create table foo(id int4, name)'
//#begin
//CREATE TABLE foo(id int4 name text)
//#end
//false
//Syntax error in this CREATE statement: 'create table foo(id int4 name text)'
//#begin
//CREATE TABLE foo(id int4, name text)foo
//#end
//false
//Syntax error in this CREATE statement: 'create table foo(id int4, name text)foo'
//#begin
//CREATE TABLE foo((id int4, name text)
//#end
//false
//Syntax error in this CREATE statement: 'create table foo((id int4, name text)'
#begin
CREATE TABLE categories(id INT4, name TEXT, PRIMARY KEY((id))
#end
false
Syntax error in this CREATE statement: 'create table categories(id int4, name text, primary key((id))'