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
|
#
# Testing zipped DOS tables
#
CREATE TABLE t1 (
digit INT(3) NOT NULL,
letter CHAR(16) NOT NULL)
ENGINE=CONNECT TABLE_TYPE=DOS FILE_NAME='newdos.zip'
OPTION_LIST='ENTRY=new1.dos' ZIPPED=1;
INSERT INTO t1 VALUES(1,'One'),(2,'Two'),(3,'Three'),(4,'Four'),(5,'Five'),(6,'Six'),(7,'Seven'),(8,'Eight'),(9,'Nine'),(10,'Ten');
SELECT * FROM t1;
digit letter
1 One
2 Two
3 Three
4 Four
5 Five
6 Six
7 Seven
8 Eight
9 Nine
10 Ten
CREATE TABLE t2 (
digit INT(3) NOT NULL,
letter CHAR(16) NOT NULL)
ENGINE=CONNECT TABLE_TYPE=DOS FILE_NAME='newdos.zip'
OPTION_LIST='ENTRY=new2.dos,APPEND=1' ZIPPED=1;
INSERT INTO t2 VALUES(11,'Eleven'),(12,'Twelve'),(13,'Thirteen'),(14,'Fourteen'),(15,'Fiften'),(16,'Sixteen'),(17,'Seventeen'),(18,'Eighteen'),(19,'Nineteen'),(20,'Twenty');
SELECT * FROM t2;
digit letter
11 Eleven
12 Twelve
13 Thirteen
14 Fourteen
15 Fiften
16 Sixteen
17 Seventeen
18 Eighteen
19 Nineteen
20 Twenty
CREATE TABLE t3 (
digit INT(3) NOT NULL,
letter CHAR(16) NOT NULL)
ENGINE=CONNECT TABLE_TYPE=DOS FILE_NAME='newdos.zip'
OPTION_LIST='MULENTRIES=1' ZIPPED=1;
SELECT * FROM t3;
digit letter
1 One
2 Two
3 Three
4 Four
5 Five
6 Six
7 Seven
8 Eight
9 Nine
10 Ten
11 Eleven
12 Twelve
13 Thirteen
14 Fourteen
15 Fiften
16 Sixteen
17 Seventeen
18 Eighteen
19 Nineteen
20 Twenty
CREATE TABLE t4 (
fn VARCHAR(256)NOT NULL,
cmpsize BIGINT NOT NULL FLAG=1,
uncsize BIGINT NOT NULL FLAG=2,
method INT NOT NULL FLAG=3)
ENGINE=CONNECT TABLE_TYPE=ZIP FILE_NAME='newdos.zip';
SELECT * FROM t4;
fn cmpsize uncsize method
new1.dos 67 79 8
new2.dos 77 112 8
DROP TABLE t1,t2,t3,t4;
#
# Testing zipped CSV tables
#
CREATE TABLE t1 (
digit INT(3) NOT NULL,
letter CHAR(16) NOT NULL)
ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='newcsv.zip'
OPTION_LIST='ENTRY=new1.csv' HEADER=1 ZIPPED=1;
INSERT INTO t1 VALUES(1,'One'),(2,'Two'),(3,'Three'),(4,'Four'),(5,'Five'),(6,'Six'),(7,'Seven'),(8,'Eight'),(9,'Nine'),(10,'Ten');
SELECT * FROM t1;
digit letter
1 One
2 Two
3 Three
4 Four
5 Five
6 Six
7 Seven
8 Eight
9 Nine
10 Ten
CREATE TABLE td1
ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='newcsv.zip'
OPTION_LIST='ENTRY=new1.csv' HEADER=1 ZIPPED=1;
SELECT * FROM td1;
digit letter
1 One
2 Two
3 Three
4 Four
5 Five
6 Six
7 Seven
8 Eight
9 Nine
10 Ten
DROP TABLE td1;
CREATE TABLE t2 (
digit INT(3) NOT NULL,
letter CHAR(16) NOT NULL)
ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='newcsv.zip'
OPTION_LIST='ENTRY=new2.csv,APPEND=1' HEADER=1 ZIPPED=1;
INSERT INTO t2 VALUES(11,'Eleven'),(12,'Twelve'),(13,'Thirteen'),(14,'Fourteen'),(15,'Fiften'),(16,'Sixteen'),(17,'Seventeen'),(18,'Eighteen'),(19,'Nineteen'),(20,'Twenty');
SELECT * FROM t2;
digit letter
11 Eleven
12 Twelve
13 Thirteen
14 Fourteen
15 Fiften
16 Sixteen
17 Seventeen
18 Eighteen
19 Nineteen
20 Twenty
CREATE TABLE t3
ENGINE=CONNECT TABLE_TYPE=CSV FILE_NAME='newcsv.zip'
OPTION_LIST='MULENTRIES=1' HEADER=1 ZIPPED=1;
SELECT * FROM t3;
digit letter
1 One
2 Two
3 Three
4 Four
5 Five
6 Six
7 Seven
8 Eight
9 Nine
10 Ten
11 Eleven
12 Twelve
13 Thirteen
14 Fourteen
15 Fiften
16 Sixteen
17 Seventeen
18 Eighteen
19 Nineteen
20 Twenty
CREATE TABLE t4 (
fn VARCHAR(256)NOT NULL,
cmpsize BIGINT NOT NULL FLAG=1,
uncsize BIGINT NOT NULL FLAG=2,
method INT NOT NULL FLAG=3)
ENGINE=CONNECT TABLE_TYPE=ZIP FILE_NAME='newcsv.zip';
SELECT * FROM t4;
fn cmpsize uncsize method
new1.csv 79 83 8
new2.csv 94 125 8
DROP TABLE t1,t2,t3,t4;
#
# Testing zipped JSON tables
#
CREATE TABLE t1 (
_id INT(2) NOT NULL,
name_first CHAR(9) NOT NULL FIELD_FORMAT='$.name.first',
name_aka CHAR(4) DEFAULT NULL FIELD_FORMAT='$.name.aka',
name_last CHAR(10) NOT NULL FIELD_FORMAT='$.name.last',
title CHAR(12) DEFAULT NULL,
birth CHAR(20) DEFAULT NULL,
death CHAR(20) DEFAULT NULL,
contribs CHAR(7) NOT NULL FIELD_FORMAT='$.contribs',
awards_award CHAR(42) DEFAULT NULL FIELD_FORMAT='$.awards.award',
awards_year CHAR(4) DEFAULT NULL FIELD_FORMAT='$.awards.year',
awards_by CHAR(38) DEFAULT NULL FIELD_FORMAT='$.awards.by'
) ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='bios.zip' OPTION_LIST='ENTRY=bios.json,LOAD=bios.json' ZIPPED=YES;
SELECT * FROM t1;
_id name_first name_aka name_last title birth death contribs awards_award awards_year awards_by
1 John NULL Backus NULL 1924-12-03T05:00:00Z 2007-03-17T04:00:00Z Fortran W.W. McDowell Award 1967 IEEE Computer Society
2 John NULL McCarthy NULL 1927-09-04T04:00:00Z 2011-12-24T05:00:00Z Lisp Turing Award 1971 ACM
3 Grace NULL Hopper Rear Admiral 1906-12-09T05:00:00Z 1992-01-01T05:00:00Z UNIVAC Computer Sciences Man of the Year 1969 Data Processing Management Association
4 Kristen NULL Nygaard NULL 1926-08-27T04:00:00Z 2002-08-10T04:00:00Z OOP Rosing Prize 1999 Norwegian Data Association
5 Ole-Johan NULL Dahl NULL 1931-10-12T04:00:00Z 2002-06-29T04:00:00Z OOP Rosing Prize 1999 Norwegian Data Association
6 Guido NULL van Rossum NULL 1956-01-31T05:00:00Z NULL Python Award for the Advancement of Free Software 2001 Free Software Foundation
7 Dennis NULL Ritchie NULL 1941-09-09T04:00:00Z 2011-10-12T04:00:00Z UNIX Turing Award 1983 ACM
8 Yukihiro Matz Matsumoto NULL 1965-04-14T04:00:00Z NULL Ruby Award for the Advancement of Free Software 2011 Free Software Foundation
9 James NULL Gosling NULL 1955-05-19T04:00:00Z NULL Java The Economist Innovation Award 2002 The Economist
10 Martin NULL Odersky NULL NULL NULL Scala NULL NULL NULL
CREATE TABLE t2
ENGINE=CONNECT TABLE_TYPE=JSON FILE_NAME='bios.zip' ZIPPED=1
OPTION_LIST='LEVEL=5';
SELECT * FROM t2;
_id name_first name_aka name_last title birth death contribs awards_award awards_year awards_by
1 John NULL Backus NULL 1924-12-03T05:00:00Z 2007-03-17T04:00:00Z Fortran W.W. McDowell Award 1967 IEEE Computer Society
2 John NULL McCarthy NULL 1927-09-04T04:00:00Z 2011-12-24T05:00:00Z Lisp Turing Award 1971 ACM
3 Grace NULL Hopper Rear Admiral 1906-12-09T05:00:00Z 1992-01-01T05:00:00Z UNIVAC Computer Sciences Man of the Year 1969 Data Processing Management Association
4 Kristen NULL Nygaard NULL 1926-08-27T04:00:00Z 2002-08-10T04:00:00Z OOP Rosing Prize 1999 Norwegian Data Association
5 Ole-Johan NULL Dahl NULL 1931-10-12T04:00:00Z 2002-06-29T04:00:00Z OOP Rosing Prize 1999 Norwegian Data Association
6 Guido NULL van Rossum NULL 1956-01-31T05:00:00Z NULL Python Award for the Advancement of Free Software 2001 Free Software Foundation
7 Dennis NULL Ritchie NULL 1941-09-09T04:00:00Z 2011-10-12T04:00:00Z UNIX Turing Award 1983 ACM
8 Yukihiro Matz Matsumoto NULL 1965-04-14T04:00:00Z NULL Ruby Award for the Advancement of Free Software 2011 Free Software Foundation
9 James NULL Gosling NULL 1955-05-19T04:00:00Z NULL Java The Economist Innovation Award 2002 The Economist
10 Martin NULL Odersky NULL NULL NULL Scala NULL NULL NULL
CREATE TABLE t3 (
_id INT(2) NOT NULL,
firstname CHAR(9) NOT NULL FIELD_FORMAT='$.name.first',
aka CHAR(4) DEFAULT NULL FIELD_FORMAT='$.name.aka',
lastname CHAR(10) NOT NULL FIELD_FORMAT='$.name.last',
title CHAR(12) DEFAULT NULL,
birth date DEFAULT NULL date_format="YYYY-DD-MM'T'hh:mm:ss'Z'",
death date DEFAULT NULL date_format="YYYY-DD-MM'T'hh:mm:ss'Z'",
contribs CHAR(64) NOT NULL FIELD_FORMAT='$.contribs.[", "]',
award CHAR(42) DEFAULT NULL FIELD_FORMAT='$.awards[*].award',
year CHAR(4) DEFAULT NULL FIELD_FORMAT='$.awards[*].year',
`by` CHAR(38) DEFAULT NULL FIELD_FORMAT='$.awards[*].by'
) ENGINE=CONNECT TABLE_TYPE='json' FILE_NAME='bios.zip' ZIPPED=YES;
SELECT * FROM t3 WHERE _id = 1;
_id firstname aka lastname title birth death contribs award year by
1 John NULL Backus NULL 1924-03-12 2008-05-03 Fortran, ALGOL, Backus-Naur Form, FP W.W. McDowell Award 1967 IEEE Computer Society
1 John NULL Backus NULL 1924-03-12 2008-05-03 Fortran, ALGOL, Backus-Naur Form, FP National Medal of Science 1975 National Science Foundation
1 John NULL Backus NULL 1924-03-12 2008-05-03 Fortran, ALGOL, Backus-Naur Form, FP Turing Award 1977 ACM
1 John NULL Backus NULL 1924-03-12 2008-05-03 Fortran, ALGOL, Backus-Naur Form, FP Draper Prize 1993 National Academy of Engineering
CREATE TABLE t4 (
fn VARCHAR(256)NOT NULL,
cmpsize BIGINT NOT NULL FLAG=1,
uncsize BIGINT NOT NULL FLAG=2,
method INT NOT NULL FLAG=3)
ENGINE=CONNECT TABLE_TYPE=ZIP FILE_NAME='bios.zip';
SELECT * FROM t4;
fn cmpsize uncsize method
bios.json 1096 6848 8
DROP TABLE t1,t2,t3,t4;
|
