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
|
#set connect_enable_mongo=1;
--echo #
--echo # Test the MONGO table type
--echo #
eval CREATE TABLE t1 (Document varchar(1024) field_format='*')
ENGINE=CONNECT TABLE_TYPE=$TYPE TABNAME=restaurants $CONN
OPTION_LIST='Driver=$DRV,Version=$VERS' DATA_CHARSET=utf8;
SELECT * from t1 limit 3;
DROP TABLE t1;
--echo #
--echo # Test catfunc
--echo #
eval CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=$TYPE TABNAME=restaurants CATFUNC=columns
OPTION_LIST='Level=1,Driver=$DRV,Version=$VERS' DATA_CHARSET=utf8 $CONN;
SELECT * from t1;
DROP TABLE t1;
--echo #
--echo # Explicit columns
--echo #
eval CREATE TABLE t1 (
_id VARCHAR(24) NOT NULL,
name VARCHAR(255) NOT NULL,
cuisine VARCHAR(255) NOT NULL,
borough VARCHAR(255) NOT NULL,
restaurant_id VARCHAR(255) NOT NULL)
ENGINE=CONNECT TABLE_TYPE=$TYPE TABNAME=restaurants
CONNECTION='mongodb://localhost:27017' LRECL=1024 DATA_CHARSET=utf8
OPTION_LIST='Driver=$DRV,Version=$VERS';
SELECT * FROM t1 LIMIT 10;
DROP TABLE t1;
--echo #
--echo # Test discovery
--echo #
eval CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=$TYPE TABNAME=restaurants
OPTION_LIST='Level=1,Driver=$DRV,Version=$VERS' $CONN DATA_CHARSET=utf8;
SHOW CREATE TABLE t1;
SELECT * FROM t1 LIMIT 5;
DROP TABLE t1;
--echo #
--echo # Dropping a column
--echo #
let $COLIST= $PROJ{"grades":0}$ENDP;
eval CREATE TABLE t1 ENGINE=CONNECT TABLE_TYPE=$TYPE TABNAME=restaurants DATA_CHARSET=utf8
COLIST='$COLIST' OPTION_LIST='Driver=$DRV,Version=$VERS,level=0' $CONN;
SELECT * FROM t1 LIMIT 10;
DROP TABLE t1;
--echo #
--echo # Specifying Jpath
--echo #
eval CREATE TABLE t1 (
_id VARCHAR(24) NOT NULL,
name VARCHAR(64) NOT NULL,
cuisine CHAR(200) NOT NULL,
borough CHAR(16) NOT NULL,
street VARCHAR(65) FIELD_FORMAT='address.street',
building CHAR(16) FIELD_FORMAT='address.building',
zipcode CHAR(5) FIELD_FORMAT='address.zipcode',
grade CHAR(1) FIELD_FORMAT='grades.0.grade',
score INT(4) NOT NULL FIELD_FORMAT='grades.0.score',
`date` DATE FIELD_FORMAT='grades.0.date',
restaurant_id VARCHAR(255) NOT NULL)
ENGINE=CONNECT TABLE_TYPE=$TYPE TABNAME='restaurants' DATA_CHARSET=utf8
OPTION_LIST='Driver=$DRV,Version=$VERS' $CONN;
--vertical_results
SELECT * FROM t1 LIMIT 1;
--horizontal_results
SELECT name, street, score, date FROM t1 LIMIT 5;
SELECT name, cuisine, borough FROM t1 WHERE grade = 'A' LIMIT 10;
SELECT COUNT(*) FROM t1 WHERE grade = 'A';
SELECT * FROM t1 WHERE cuisine = 'English';
SELECT * FROM t1 WHERE score = building;
DROP TABLE t1;
--echo #
--echo # Specifying Filter
--echo #
eval CREATE TABLE t1 (
_id CHAR(24) NOT NULL,
name CHAR(64) NOT NULL,
borough CHAR(16) NOT NULL,
restaurant_id CHAR(8) NOT NULL)
ENGINE=CONNECT TABLE_TYPE=$TYPE TABNAME=restaurants DATA_CHARSET=utf8
FILTER='{"cuisine":"French","borough":{"\$ne":"Manhattan"}}'
OPTION_LIST='Driver=$DRV,Version=$VERS' $CONN;
SELECT name FROM t1 WHERE borough = 'Queens';
DROP TABLE t1;
--echo #
--echo # Testing pipeline
--echo #
eval CREATE TABLE t1 (
name VARCHAR(64) NOT NULL,
borough CHAR(16) NOT NULL,
date DATETIME NOT NULL,
grade CHAR(1) NOT NULL,
score INT(4) NOT NULL)
ENGINE=CONNECT TABLE_TYPE=$TYPE TABNAME='restaurants' DATA_CHARSET=utf8
COLIST='{"pipeline":[{"\$match":{"cuisine":"French"}},{"\$unwind":"\$grades"},{"\$project":{"_id":0,"name":1,"borough":1,"date":"\$grades.date","grade":"\$grades.grade","score":"\$grades.score"}}]}'
OPTION_LIST='Driver=$DRV,Version=$VERS,Pipeline=1' $CONN;
SELECT * FROM t1 LIMIT 10;
SELECT name, grade, score, date FROM t1 WHERE borough = 'Bronx';
DROP TABLE t1;
--echo #
--echo # try level 2 discovery
--echo #
let $COLIST= $PROJ{"cuisine":0}$ENDP;
eval CREATE TABLE t1
ENGINE=CONNECT TABLE_TYPE=$TYPE TABNAME=restaurants
FILTER='{"cuisine":"French","borough":{"\$ne":"Manhattan"}}'
COLIST='$COLIST' $CONN
OPTION_LIST='Driver=$DRV,level=2,version=$VERS';
SHOW CREATE TABLE t1;
IF ($TYPE == MONGO)
{
SELECT name, borough, address_street, grades_0_score AS score FROM t1 WHERE grades_0_grade = 'B';
}
IF ($TYPE == JSON)
{
SELECT name, borough, address_street, grades_score AS score FROM t1 WHERE grades_grade = 'B';
}
DROP TABLE t1;
--echo #
--echo # try CRUD operations
--echo #
--exec $MONGO --eval "db.testcoll.drop()" --quiet
eval CREATE TABLE t1 (_id INT(4) NOT NULL, msg CHAR(64))
ENGINE=CONNECT TABLE_TYPE=$TYPE TABNAME='testcoll'
OPTION_LIST='Driver=$DRV,Version=$VERS' $CONN;
DELETE FROM t1;
INSERT INTO t1 VALUES(0,NULL),(1,'One'),(2,'Two'),(3,'Three');
SELECT * FROM t1;
UPDATE t1 SET msg = 'Deux' WHERE _id = 2;
DELETE FROM t1 WHERE msg IS NULL;
SELECT * FROM t1;
DELETE FROM t1;
DROP TABLE t1;
--exec $MONGO --eval "db.testcoll.drop()" --quiet
--echo #
--echo # List states whose population is equal or more than 10 millions
--echo #
--exec $MONGO --eval "db.cities.drop()" --quiet
--exec $MONGOIMPORT --quiet $MTR_SUITE_DIR/std_data/cities.json
eval CREATE TABLE t1 (
_id char(5) NOT NULL,
city char(16) NOT NULL,
loc_0 double(12,6) NOT NULL `FIELD_FORMAT`='loc.0',
loc_1 char(12) NOT NULL `FIELD_FORMAT`='loc.1',
pop int(11) NOT NULL,
state char(2) NOT NULL)
ENGINE=CONNECT CONNECTION='mongodb://localhost:27017' TABLE_TYPE=$TYPE TABNAME='cities'
OPTION_LIST='Driver=$DRV,Version=$VERS' $CONN DATA_CHARSET='utf8';
--echo # Using SQL for grouping
SELECT state, sum(pop) AS totalPop FROM t1 GROUP BY state HAVING totalPop >= 10000000 ORDER BY totalPop DESC;
DROP TABLE t1;
--echo # Using a pipeline for grouping
eval CREATE TABLE t1 (_id CHAR(2) NOT NULL, totalPop INT(11) NOT NULL)
ENGINE=CONNECT TABLE_TYPE=$TYPE TABNAME='cities' DATA_CHARSET=utf8
COLIST='{"pipeline":[{"\$group":{"_id":"\$state","totalPop":{"\$sum":"\$pop"}}},{"\$match":{"totalPop":{"\$gte":10000000}}},{"\$sort":{"totalPop":-1}}]}'
OPTION_LIST='Driver=$DRV,Version=$VERS,Pipeline=1' $CONN;
SELECT * FROM t1;
DROP TABLE t1;
--exec $MONGO --eval "db.cities.drop()" --quiet
--echo #
--echo # Test making array
--echo #
eval CREATE TABLE t1 (
_id int(4) NOT NULL,
item CHAR(8) NOT NULL,
prices_0 INT(6) FIELD_FORMAT='prices.0',
prices_1 INT(6) FIELD_FORMAT='prices.1',
prices_2 INT(6) FIELD_FORMAT='prices.2',
prices_3 INT(6) FIELD_FORMAT='prices.3',
prices_4 INT(6) FIELD_FORMAT='prices.4')
ENGINE=CONNECT TABLE_TYPE=$TYPE TABNAME='testcoll' DATA_CHARSET=utf8
OPTION_LIST='Driver=$DRV,Version=$VERS' $CONN;
INSERT INTO t1 VALUES
(1,'journal',87,45,63,12,78),
(2,'notebook',123,456,789,NULL,NULL),
(3,'paper',5,7,3,8,NULL),
(4,'planner',25,71,NULL,44,27),
(5,'postcard',5,7,3,8,NULL);
SELECT * FROM t1;
DROP TABLE t1;
--echo #
--echo # Test array aggregation
--echo #
eval CREATE TABLE t1
ENGINE=CONNECT TABLE_TYPE=$TYPE TABNAME='testcoll'
COLIST='{"pipeline":[{"\$project":{"_id":0,"item":1,"total":{"\$sum":"\$prices"},"average":{"\$avg":"\$prices"}}}]}'
OPTION_LIST='Driver=$DRV,Version=$VERS,Pipeline=YES' $CONN;
SELECT * FROM t1;
DROP TABLE t1;
--exec $MONGO --eval "db.testcoll.drop()" --quiet
#set connect_enable_mongo=0;
|
