69
void *cache = spatialite_alloc_connection();
70
void *cache = spatialite_alloc_connection ();
71
72
if (argc > 1 || argv[0] == NULL)
72
73
argc = 1; /* silencing stupid compiler warnings */
74
ret = sqlite3_open_v2 (":memory:", &db_handle, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
75
if (ret != SQLITE_OK) {
76
fprintf (stderr, "cannot open in-memory db: %s\n", sqlite3_errmsg (db_handle));
77
sqlite3_close (db_handle);
76
sqlite3_open_v2 (":memory:", &db_handle,
77
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL);
80
fprintf (stderr, "cannot open in-memory db: %s\n",
81
sqlite3_errmsg (db_handle));
82
sqlite3_close (db_handle);
82
87
spatialite_init_ex (db_handle, cache, 0);
84
ret = sqlite3_exec (db_handle, "create VIRTUAL TABLE places USING VirtualText(\"testcase1.csv\", UTF-8, 0, POINT, DOUBLEQUOTE);", NULL, NULL, &err_msg);
85
if (ret != SQLITE_OK) {
86
fprintf (stderr, "VirtualText error: %s\n", err_msg);
87
sqlite3_free (err_msg);
91
asprintf(&sql_statement, "select col003, col005, col006, col008 from places WHERE col003 = \"Canal Creek\";");
92
ret = sqlite3_get_table (db_handle, sql_statement, &results, &rows, &columns, &err_msg);
94
if (ret != SQLITE_OK) {
95
fprintf (stderr, "Error: %s\n", err_msg);
96
sqlite3_free (err_msg);
99
if ((rows != 2) || (columns != 4)) {
100
fprintf (stderr, "Unexpected error: select columns bad result: %i/%i.\n", rows, columns);
103
if (strcmp(results[0], "COL003") != 0) {
104
fprintf (stderr, "Unexpected error: header() bad result: %s.\n", results[0]);
107
if (strcmp(results[4], "Canal Creek") != 0) {
108
fprintf (stderr, "Unexpected error: name4() bad result: %s.\n", results[4]);
111
if (strncmp(results[5], "-27.86667", 9) != 0) {
112
fprintf (stderr, "Unexpected error: lat1() bad result: %s.\n", results[5]);
115
if (strncmp(results[6], "151.51667", 9) != 0) {
116
fprintf (stderr, "Unexpected error: lon2() bad result: %s.\n", results[6]);
119
if (strcmp(results[8], "Canal Creek") != 0) {
120
fprintf (stderr, "Unexpected error: name8() bad result: %s.\n", results[8]);
90
sqlite3_exec (db_handle,
91
"create VIRTUAL TABLE places USING VirtualText(\"testcase1.csv\", UTF-8, 0, POINT, DOUBLEQUOTE);",
92
NULL, NULL, &err_msg);
95
fprintf (stderr, "VirtualText error: %s\n", err_msg);
96
sqlite3_free (err_msg);
100
asprintf (&sql_statement,
101
"select col003, col005, col006, col008 from places WHERE col003 = \"Canal Creek\";");
103
sqlite3_get_table (db_handle, sql_statement, &results, &rows, &columns,
105
free (sql_statement);
106
if (ret != SQLITE_OK)
108
fprintf (stderr, "Error: %s\n", err_msg);
109
sqlite3_free (err_msg);
112
if ((rows != 2) || (columns != 4))
115
"Unexpected error: select columns bad result: %i/%i.\n",
119
if (strcmp (results[0], "COL003") != 0)
121
fprintf (stderr, "Unexpected error: header() bad result: %s.\n",
125
if (strcmp (results[4], "Canal Creek") != 0)
127
fprintf (stderr, "Unexpected error: name4() bad result: %s.\n",
131
if (strncmp (results[5], "-27.86667", 9) != 0)
133
fprintf (stderr, "Unexpected error: lat1() bad result: %s.\n",
137
if (strncmp (results[6], "151.51667", 9) != 0)
139
fprintf (stderr, "Unexpected error: lon2() bad result: %s.\n",
143
if (strcmp (results[8], "Canal Creek") != 0)
145
fprintf (stderr, "Unexpected error: name8() bad result: %s.\n",
123
149
sqlite3_free_table (results);
125
151
ret = sqlite3_exec (db_handle, "BEGIN;", NULL, NULL, &err_msg);
126
if (ret != SQLITE_OK) {
127
fprintf (stderr, "BEGIN error: %s\n", err_msg);
128
sqlite3_free (err_msg);
152
if (ret != SQLITE_OK)
154
fprintf (stderr, "BEGIN error: %s\n", err_msg);
155
sqlite3_free (err_msg);
132
ret = sqlite3_exec (db_handle, "DELETE FROM places WHERE col015 > 100000;", NULL, NULL, &err_msg);
133
if (ret != SQLITE_READONLY) {
134
fprintf (stderr, "UPDATE error: %s\n", err_msg);
135
sqlite3_free (err_msg);
160
sqlite3_exec (db_handle, "DELETE FROM places WHERE col015 > 100000;",
161
NULL, NULL, &err_msg);
162
if (ret != SQLITE_READONLY)
164
fprintf (stderr, "UPDATE error: %s\n", err_msg);
165
sqlite3_free (err_msg);
138
168
sqlite3_free (err_msg);
140
170
ret = sqlite3_exec (db_handle, "ROLLBACK;", NULL, NULL, &err_msg);
141
if (ret != SQLITE_OK) {
142
fprintf (stderr, "ROLLBACK error: %s\n", err_msg);
143
sqlite3_free (err_msg);
146
ret = sqlite3_get_table (db_handle, "SELECT ROWNO, col003, col015 FROM places WHERE col015 > 100000", &results, &rows, &columns, &err_msg);
147
if (ret != SQLITE_OK) {
148
fprintf (stderr, "Error: %s\n", err_msg);
149
sqlite3_free (err_msg);
152
if ((rows != 1) || (columns != 3)) {
153
fprintf (stderr, "Unexpected error: select columns bad result2: %i/%i.\n", rows, columns);
156
sqlite3_free_table (results);
158
ret = sqlite3_get_table (db_handle, "SELECT col002 FROM places WHERE col001 > 2172517 AND col001 <= 2172519", &results, &rows, &columns, &err_msg);
159
if (ret != SQLITE_OK) {
160
fprintf (stderr, "Error: %s\n", err_msg);
161
sqlite3_free (err_msg);
164
if ((rows != 2) || (columns != 1)) {
165
fprintf (stderr, "Unexpected error: select columns bad result2: %i/%i.\n", rows, columns);
168
sqlite3_free_table (results);
170
ret = sqlite3_get_table (db_handle, "SELECT col002 FROM places WHERE col001 >= 2172517 AND col001 < 2172519", &results, &rows, &columns, &err_msg);
171
if (ret != SQLITE_OK) {
172
fprintf (stderr, "Error: %s\n", err_msg);
173
sqlite3_free (err_msg);
176
if ((rows != 2) || (columns != 1)) {
177
fprintf (stderr, "Unexpected error: select columns bad result2: %i/%i.\n", rows, columns);
180
sqlite3_free_table (results);
182
ret = sqlite3_get_table (db_handle, "SELECT col002 FROM places WHERE col001 = 2172517", &results, &rows, &columns, &err_msg);
183
if (ret != SQLITE_OK) {
184
fprintf (stderr, "Error: %s\n", err_msg);
185
sqlite3_free (err_msg);
188
if ((rows != 1) || (columns != 1)) {
189
fprintf (stderr, "Unexpected error: select columns bad result2: %i/%i.\n", rows, columns);
192
sqlite3_free_table (results);
194
ret = sqlite3_get_table (db_handle, "SELECT col001 FROM places WHERE col002 > 'Canberra' AND col002 <= 'Canbrae'", &results, &rows, &columns, &err_msg);
195
if (ret != SQLITE_OK) {
196
fprintf (stderr, "Error: %s\n", err_msg);
197
sqlite3_free (err_msg);
200
if ((rows != 1) || (columns != 1)) {
201
fprintf (stderr, "Unexpected error: select columns bad result2: %i/%i.\n", rows, columns);
204
sqlite3_free_table (results);
206
ret = sqlite3_get_table (db_handle, "SELECT col001 FROM places WHERE col002 >= 'Canberra' AND col002 < 'Canbrae'", &results, &rows, &columns, &err_msg);
207
if (ret != SQLITE_OK) {
208
fprintf (stderr, "Error: %s\n", err_msg);
209
sqlite3_free (err_msg);
212
if ((rows != 2) || (columns != 1)) {
213
fprintf (stderr, "Unexpected error: select columns bad result2: %i/%i.\n", rows, columns);
216
sqlite3_free_table (results);
218
ret = sqlite3_get_table (db_handle, "SELECT col001 FROM places WHERE col002 = 'Canbrae'", &results, &rows, &columns, &err_msg);
219
if (ret != SQLITE_OK) {
220
fprintf (stderr, "Error: %s\n", err_msg);
221
sqlite3_free (err_msg);
224
if ((rows != 1) || (columns != 1)) {
225
fprintf (stderr, "Unexpected error: select columns bad result2: %i/%i.\n", rows, columns);
228
sqlite3_free_table (results);
230
ret = sqlite3_get_table (db_handle, "SELECT col001 FROM places WHERE ROWNO = 5", &results, &rows, &columns, &err_msg);
231
if (ret != SQLITE_OK) {
232
fprintf (stderr, "Error: %s\n", err_msg);
233
sqlite3_free (err_msg);
236
if ((rows != 1) || (columns != 1)) {
237
fprintf (stderr, "Unexpected error: select columns bad result2: %i/%i.\n", rows, columns);
240
sqlite3_free_table (results);
242
ret = sqlite3_get_table (db_handle, "SELECT col001 FROM places WHERE col006 > 149.0 AND col006 <= 149.1", &results, &rows, &columns, &err_msg);
243
if (ret != SQLITE_OK) {
244
fprintf (stderr, "Error: %s\n", err_msg);
245
sqlite3_free (err_msg);
248
if ((rows != 1) || (columns != 1)) {
249
fprintf (stderr, "Unexpected error: select columns bad result2: %i/%i.\n", rows, columns);
252
sqlite3_free_table (results);
254
ret = sqlite3_get_table (db_handle, "SELECT col001 FROM places WHERE col006 >= 149.1 AND col006 < 149.2", &results, &rows, &columns, &err_msg);
255
if (ret != SQLITE_OK) {
256
fprintf (stderr, "Error: %s\n", err_msg);
257
sqlite3_free (err_msg);
260
if ((rows != 2) || (columns != 1)) {
261
fprintf (stderr, "Unexpected error: select columns bad result2: %i/%i.\n", rows, columns);
264
sqlite3_free_table (results);
266
ret = sqlite3_get_table (db_handle, "SELECT col001 FROM places WHERE col006 = 149.1", &results, &rows, &columns, &err_msg);
267
if (ret != SQLITE_OK) {
268
fprintf (stderr, "Error: %s\n", err_msg);
269
sqlite3_free (err_msg);
272
if ((rows != 1) || (columns != 1)) {
273
fprintf (stderr, "Unexpected error: select columns bad result2: %i/%i.\n", rows, columns);
276
sqlite3_free_table (results);
278
ret = sqlite3_get_table (db_handle, "SELECT col001 FROM places WHERE col006 >= 149 AND col006 < 150", &results, &rows, &columns, &err_msg);
279
if (ret != SQLITE_OK) {
280
fprintf (stderr, "Error: %s\n", err_msg);
281
sqlite3_free (err_msg);
284
if ((rows != 4) || (columns != 1)) {
285
fprintf (stderr, "Unexpected error: select columns bad result2: %i/%i.\n", rows, columns);
288
sqlite3_free_table (results);
290
ret = sqlite3_get_table (db_handle, "SELECT col001 FROM places WHERE col006 > 149 AND col006 <= 150", &results, &rows, &columns, &err_msg);
291
if (ret != SQLITE_OK) {
292
fprintf (stderr, "Error: %s\n", err_msg);
293
sqlite3_free (err_msg);
296
if ((rows != 4) || (columns != 1)) {
297
fprintf (stderr, "Unexpected error: select columns bad result2: %i/%i.\n", rows, columns);
300
sqlite3_free_table (results);
302
ret = sqlite3_get_table (db_handle, "SELECT col001 FROM places WHERE col012 = 23940.0", &results, &rows, &columns, &err_msg);
303
if (ret != SQLITE_OK) {
304
fprintf (stderr, "Error: %s\n", err_msg);
305
sqlite3_free (err_msg);
308
if ((rows != 2) || (columns != 1)) {
309
fprintf (stderr, "Unexpected error: select columns bad result2: %i/%i.\n", rows, columns);
312
sqlite3_free_table (results);
314
ret = sqlite3_get_table (db_handle, "SELECT col001 FROM places WHERE col012 >= 20000.0 AND col012 < 24000.0", &results, &rows, &columns, &err_msg);
315
if (ret != SQLITE_OK) {
316
fprintf (stderr, "Error: %s\n", err_msg);
317
sqlite3_free (err_msg);
320
if ((rows != 2) || (columns != 1)) {
321
fprintf (stderr, "Unexpected error: select columns bad result2: %i/%i.\n", rows, columns);
324
sqlite3_free_table (results);
326
ret = sqlite3_get_table (db_handle, "SELECT col001 FROM places WHERE col012 > 20000.0 AND col012 <= 24000.0", &results, &rows, &columns, &err_msg);
327
if (ret != SQLITE_OK) {
328
fprintf (stderr, "Error: %s\n", err_msg);
329
sqlite3_free (err_msg);
332
if ((rows != 2) || (columns != 1)) {
333
fprintf (stderr, "Unexpected error: select columns bad result2: %i/%i.\n", rows, columns);
171
if (ret != SQLITE_OK)
173
fprintf (stderr, "ROLLBACK error: %s\n", err_msg);
174
sqlite3_free (err_msg);
178
sqlite3_get_table (db_handle,
179
"SELECT ROWNO, col003, col015 FROM places WHERE col015 > 100000",
180
&results, &rows, &columns, &err_msg);
181
if (ret != SQLITE_OK)
183
fprintf (stderr, "Error: %s\n", err_msg);
184
sqlite3_free (err_msg);
187
if ((rows != 1) || (columns != 3))
190
"Unexpected error: select columns bad result2: %i/%i.\n",
194
sqlite3_free_table (results);
197
sqlite3_get_table (db_handle,
198
"SELECT col002 FROM places WHERE col001 > 2172517 AND col001 <= 2172519",
199
&results, &rows, &columns, &err_msg);
200
if (ret != SQLITE_OK)
202
fprintf (stderr, "Error: %s\n", err_msg);
203
sqlite3_free (err_msg);
206
if ((rows != 2) || (columns != 1))
209
"Unexpected error: select columns bad result2: %i/%i.\n",
213
sqlite3_free_table (results);
216
sqlite3_get_table (db_handle,
217
"SELECT col002 FROM places WHERE col001 >= 2172517 AND col001 < 2172519",
218
&results, &rows, &columns, &err_msg);
219
if (ret != SQLITE_OK)
221
fprintf (stderr, "Error: %s\n", err_msg);
222
sqlite3_free (err_msg);
225
if ((rows != 2) || (columns != 1))
228
"Unexpected error: select columns bad result2: %i/%i.\n",
232
sqlite3_free_table (results);
235
sqlite3_get_table (db_handle,
236
"SELECT col002 FROM places WHERE col001 = 2172517",
237
&results, &rows, &columns, &err_msg);
238
if (ret != SQLITE_OK)
240
fprintf (stderr, "Error: %s\n", err_msg);
241
sqlite3_free (err_msg);
244
if ((rows != 1) || (columns != 1))
247
"Unexpected error: select columns bad result2: %i/%i.\n",
251
sqlite3_free_table (results);
254
sqlite3_get_table (db_handle,
255
"SELECT col001 FROM places WHERE col002 > 'Canberra' AND col002 <= 'Canbrae'",
256
&results, &rows, &columns, &err_msg);
257
if (ret != SQLITE_OK)
259
fprintf (stderr, "Error: %s\n", err_msg);
260
sqlite3_free (err_msg);
263
if ((rows != 1) || (columns != 1))
266
"Unexpected error: select columns bad result2: %i/%i.\n",
270
sqlite3_free_table (results);
273
sqlite3_get_table (db_handle,
274
"SELECT col001 FROM places WHERE col002 >= 'Canberra' AND col002 < 'Canbrae'",
275
&results, &rows, &columns, &err_msg);
276
if (ret != SQLITE_OK)
278
fprintf (stderr, "Error: %s\n", err_msg);
279
sqlite3_free (err_msg);
282
if ((rows != 2) || (columns != 1))
285
"Unexpected error: select columns bad result2: %i/%i.\n",
289
sqlite3_free_table (results);
292
sqlite3_get_table (db_handle,
293
"SELECT col001 FROM places WHERE col002 = 'Canbrae'",
294
&results, &rows, &columns, &err_msg);
295
if (ret != SQLITE_OK)
297
fprintf (stderr, "Error: %s\n", err_msg);
298
sqlite3_free (err_msg);
301
if ((rows != 1) || (columns != 1))
304
"Unexpected error: select columns bad result2: %i/%i.\n",
308
sqlite3_free_table (results);
311
sqlite3_get_table (db_handle,
312
"SELECT col001 FROM places WHERE ROWNO = 5",
313
&results, &rows, &columns, &err_msg);
314
if (ret != SQLITE_OK)
316
fprintf (stderr, "Error: %s\n", err_msg);
317
sqlite3_free (err_msg);
320
if ((rows != 1) || (columns != 1))
323
"Unexpected error: select columns bad result2: %i/%i.\n",
327
sqlite3_free_table (results);
330
sqlite3_get_table (db_handle,
331
"SELECT col001 FROM places WHERE col006 > 149.0 AND col006 <= 149.1",
332
&results, &rows, &columns, &err_msg);
333
if (ret != SQLITE_OK)
335
fprintf (stderr, "Error: %s\n", err_msg);
336
sqlite3_free (err_msg);
339
if ((rows != 1) || (columns != 1))
342
"Unexpected error: select columns bad result2: %i/%i.\n",
346
sqlite3_free_table (results);
349
sqlite3_get_table (db_handle,
350
"SELECT col001 FROM places WHERE col006 >= 149.1 AND col006 < 149.2",
351
&results, &rows, &columns, &err_msg);
352
if (ret != SQLITE_OK)
354
fprintf (stderr, "Error: %s\n", err_msg);
355
sqlite3_free (err_msg);
358
if ((rows != 2) || (columns != 1))
361
"Unexpected error: select columns bad result2: %i/%i.\n",
365
sqlite3_free_table (results);
368
sqlite3_get_table (db_handle,
369
"SELECT col001 FROM places WHERE col006 = 149.1",
370
&results, &rows, &columns, &err_msg);
371
if (ret != SQLITE_OK)
373
fprintf (stderr, "Error: %s\n", err_msg);
374
sqlite3_free (err_msg);
377
if ((rows != 1) || (columns != 1))
380
"Unexpected error: select columns bad result2: %i/%i.\n",
384
sqlite3_free_table (results);
387
sqlite3_get_table (db_handle,
388
"SELECT col001 FROM places WHERE col006 >= 149 AND col006 < 150",
389
&results, &rows, &columns, &err_msg);
390
if (ret != SQLITE_OK)
392
fprintf (stderr, "Error: %s\n", err_msg);
393
sqlite3_free (err_msg);
396
if ((rows != 4) || (columns != 1))
399
"Unexpected error: select columns bad result2: %i/%i.\n",
403
sqlite3_free_table (results);
406
sqlite3_get_table (db_handle,
407
"SELECT col001 FROM places WHERE col006 > 149 AND col006 <= 150",
408
&results, &rows, &columns, &err_msg);
409
if (ret != SQLITE_OK)
411
fprintf (stderr, "Error: %s\n", err_msg);
412
sqlite3_free (err_msg);
415
if ((rows != 4) || (columns != 1))
418
"Unexpected error: select columns bad result2: %i/%i.\n",
422
sqlite3_free_table (results);
425
sqlite3_get_table (db_handle,
426
"SELECT col001 FROM places WHERE col012 = 23940.0",
427
&results, &rows, &columns, &err_msg);
428
if (ret != SQLITE_OK)
430
fprintf (stderr, "Error: %s\n", err_msg);
431
sqlite3_free (err_msg);
434
if ((rows != 2) || (columns != 1))
437
"Unexpected error: select columns bad result2: %i/%i.\n",
441
sqlite3_free_table (results);
444
sqlite3_get_table (db_handle,
445
"SELECT col001 FROM places WHERE col012 >= 20000.0 AND col012 < 24000.0",
446
&results, &rows, &columns, &err_msg);
447
if (ret != SQLITE_OK)
449
fprintf (stderr, "Error: %s\n", err_msg);
450
sqlite3_free (err_msg);
453
if ((rows != 2) || (columns != 1))
456
"Unexpected error: select columns bad result2: %i/%i.\n",
460
sqlite3_free_table (results);
463
sqlite3_get_table (db_handle,
464
"SELECT col001 FROM places WHERE col012 > 20000.0 AND col012 <= 24000.0",
465
&results, &rows, &columns, &err_msg);
466
if (ret != SQLITE_OK)
468
fprintf (stderr, "Error: %s\n", err_msg);
469
sqlite3_free (err_msg);
472
if ((rows != 2) || (columns != 1))
475
"Unexpected error: select columns bad result2: %i/%i.\n",
336
479
sqlite3_free_table (results);
338
481
ret = sqlite3_exec (db_handle, "DROP TABLE places;", NULL, NULL, &err_msg);
339
if (ret != SQLITE_OK) {
340
fprintf (stderr, "DROP TABLE error: %s\n", err_msg);
341
sqlite3_free (err_msg);
482
if (ret != SQLITE_OK)
484
fprintf (stderr, "DROP TABLE error: %s\n", err_msg);
485
sqlite3_free (err_msg);
345
489
sqlite3_close (db_handle);
346
490
spatialite_cleanup_ex (cache);
347
#endif /* end ICONV conditional */
491
#endif /* end ICONV conditional */
493
spatialite_shutdown ();