Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Changes In Branch csv_ext Excluding Merge-Ins
This is equivalent to a diff from eb7a544fe4 to d474195a99
2009-11-06
| ||
03:22 | Add some very basic tests for the commands available from the CLI. (check-in: 6bf4333804 user: shaneh tags: trunk) | |
2009-11-05
| ||
18:46 | Recover an ophaned branch that resulted from some ill-advised "shunning". This check-in should be identical to [bd443f39a9] except that it should be on the trunk. (check-in: 38eaf1ab6e user: drh tags: trunk) | |
04:14 | Update maxRow size after shrinking the row buffer (CSV). (Closed-Leaf check-in: d474195a99 user: shaneh tags: csv_ext) | |
04:01 | Removed line limit on rows. (check-in: ec08b15f77 user: shaneh tags: csv_ext) | |
02:34 | Initial implementation of a virtual table for CSV files. (check-in: 90e63b7d84 user: shaneh tags: csv_ext) | |
2009-11-04
| ||
13:30 | Version 3.6.20 (check-in: eb7a544fe4 user: drh tags: trunk, release) | |
13:17 | Create a new datatype "ynVar" to hold "nVar" (number of variable) values. This is normally 16-bits can can be 32-bits if SQLITE_MAX_VARIABLE_NUMBER is large enough. (check-in: 24a4d520d5 user: drh tags: trunk) | |
Added ext/csv/csv.c.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 | /* ** 2009 March 26 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ************************************************************************* ** This file contains code for implementations of the CSV ** algorithms packaged as an SQLite virtual table module. */ #if defined(_WIN32) || defined(WIN32) /* This needs to come before any includes for MSVC compiler */ #define _CRT_SECURE_NO_WARNINGS #endif #if !defined(SQLITE_CORE) || defined(SQLITE_ENABLE_CSV) #include <stdio.h> #include <stdlib.h> #include <string.h> #ifndef SQLITE_CORE #include "sqlite3ext.h" SQLITE_EXTENSION_INIT1 #else #include "sqlite3.h" #endif #define UNUSED_PARAMETER(x) (void)(x) /* ** The CSV virtual-table types. */ typedef struct CSV CSV; typedef struct CSVCursor CSVCursor; /* ** An CSV virtual-table object. */ struct CSV { sqlite3_vtab base; /* Must be first */ sqlite3 *db; /* Host database connection */ char *zDb; /* Name of database containing CSV table */ char *zName; /* Name of CSV table */ char *zFile; /* Name of CSV file */ int nBusy; /* Current number of users of this structure */ FILE *f; /* File pointer for source CSV file */ long offsetFirstRow; /* ftell position of first row */ int eof; /* True when at end of file */ int maxRow; /* Size of zRow buffer */ char *zRow; /* Buffer for current CSV row */ char cDelim; /* Character to use for delimiting columns */ int nCol; /* Number of columns in current row */ int maxCol; /* Size of aCols array */ char **aCols; /* Array of parsed columns */ }; /* ** An CSV cursor object. */ struct CSVCursor { sqlite3_vtab_cursor base; /* Must be first */ long csvpos; /* ftell position of current zRow */ }; /* ** Forward declarations. */ static int csvNext( sqlite3_vtab_cursor* pVtabCursor ); static int csvInit( sqlite3 *db, /* Database connection */ void *pAux, /* Unused */ int argc, const char *const*argv, /* Parameters to CREATE TABLE statement */ sqlite3_vtab **ppVtab, /* OUT: New virtual table */ char **pzErr, /* OUT: Error message, if any */ int isCreate /* True for xCreate, false for xConnect */ ); static void csvReference( CSV *pCSV ); static int csvRelease( CSV *pCSV ); /* ** Abstract out file io routines for porting */ static FILE *csv_open( CSV *pCSV ){ return fopen( pCSV->zFile, "rb" ); } static void csv_close( CSV *pCSV ){ if( pCSV->f ) fclose( pCSV->f ); } static int csv_seek( CSV *pCSV, long pos ){ return fseek( pCSV->f, pos, SEEK_SET ); } static long csv_tell( CSV *pCSV ){ return ftell( pCSV->f ); } /* ** This routine reads a line of text from FILE in, stores ** the text in memory obtained from malloc() and returns a pointer ** to the text. NULL is returned at end of file, or if malloc() ** fails. ** ** The interface is like "readline" but no command-line editing ** is done. ** ** This code was modified from existing code in shell.c of the sqlite3 CLI. */ static char *csv_getline( CSV *pCSV ){ int n = 0; int bEol = 0; int bShrink = 0; /* allocate initial row buffer */ if( pCSV->maxRow < 1 ){ pCSV->zRow = sqlite3_malloc( 100 ); if( pCSV->zRow ){ pCSV->maxRow = 100; } } if( !pCSV->zRow ) return 0; /* read until eol */ while( !bEol ){ /* grow row buffer as needed */ if( n+100>pCSV->maxRow ){ int newSize = pCSV->maxRow*2 + 100; char *p = sqlite3_realloc(pCSV->zRow, newSize); if( !p ) return 0; pCSV->maxRow = newSize; pCSV->zRow = p; bShrink = -1; } if( fgets(&pCSV->zRow[n], pCSV->maxRow-n, pCSV->f)==0 ){ if( n==0 ){ break; } pCSV->zRow[n] = '\0'; bEol = -1; break; } /* look for line delimiter */ while( pCSV->zRow[n] ){ n++; } if( (n>0) && ((pCSV->zRow[n-1]=='\n') || (pCSV->zRow[n-1]=='\r')) ){ pCSV->zRow[n-1] = '\n'; /* uniform line ending */ pCSV->zRow[n] = '\0'; bEol = -1; } } if( bShrink ){ pCSV->zRow = realloc( pCSV->zRow, n+1 ); pCSV->maxRow = n+1; } return bEol ? pCSV->zRow : 0; } /* ** CSV virtual table module xCreate method. */ static int csvCreate( sqlite3* db, void *pAux, int argc, const char *const *argv, sqlite3_vtab **ppVtab, char **pzErr ){ return csvInit( db, pAux, argc, argv, ppVtab, pzErr, 1 ); } /* ** CSV virtual table module xConnect method. */ static int csvConnect( sqlite3 *db, void *pAux, int argc, const char *const*argv, sqlite3_vtab **ppVtab, char **pzErr ){ return csvInit(db, pAux, argc, argv, ppVtab, pzErr, 0); } /* ** CSV virtual table module xBestIndex method. */ static int csvBestIndex( sqlite3_vtab *pVtab, sqlite3_index_info* info ) { UNUSED_PARAMETER(pVtab); UNUSED_PARAMETER(info); /* TBD */ return SQLITE_OK; } /* ** CSV virtual table module xDisconnect method. */ static int csvDisconnect( sqlite3_vtab *pVtab ){ return csvRelease( (CSV *)pVtab ); } /* ** CSV virtual table module xDestroy method. */ static int csvDestroy( sqlite3_vtab *pVtab ){ return csvDisconnect( pVtab ); } /* ** CSV virtual table module xOpen method. */ static int csvOpen( sqlite3_vtab *pVtab, sqlite3_vtab_cursor **ppVtabCursor ){ int rc = SQLITE_NOMEM; CSVCursor *pCsr; /* create a new cursor object */ pCsr = (CSVCursor *)sqlite3_malloc(sizeof(CSVCursor)); if( pCsr ){ memset(pCsr, 0, sizeof(CSVCursor)); pCsr->base.pVtab = pVtab; rc = SQLITE_OK; } *ppVtabCursor = (sqlite3_vtab_cursor *)pCsr; return rc; } /* ** CSV virtual table module xClose method. */ static int csvClose( sqlite3_vtab_cursor *pVtabCursor ){ CSVCursor *pCsr = (CSVCursor *)pVtabCursor; sqlite3_free(pCsr); return SQLITE_OK; } /* ** CSV virtual table module xFilter method. */ static int csvFilter( sqlite3_vtab_cursor *pVtabCursor, int idxNum, const char *idxStr, int argc, sqlite3_value **argv ){ CSV *pCSV = (CSV *)pVtabCursor->pVtab; int rc; UNUSED_PARAMETER(idxNum); UNUSED_PARAMETER(idxStr); UNUSED_PARAMETER(argc); UNUSED_PARAMETER(argv); csvReference( pCSV ); /* seek back to start of first zRow */ pCSV->eof = 0; csv_seek( pCSV, pCSV->offsetFirstRow ); /* read and parse next line */ rc = csvNext( pVtabCursor ); csvRelease( pCSV ); return rc; } /* ** CSV virtual table module xNext method. */ static int csvNext( sqlite3_vtab_cursor* pVtabCursor ){ CSV *pCSV = (CSV *)pVtabCursor->pVtab; CSVCursor *pCsr = (CSVCursor *)pVtabCursor; int nCol = 0; char *s; char zDelims[3] = ",\n"; char cDelim; /* char that delimited current col */ if( pCSV->eof ){ return SQLITE_ERROR; } /* update the cursor */ pCsr->csvpos = csv_tell( pCSV ); /* read the next row of data */ s = csv_getline( pCSV ); if( !s ){ /* and error or eof occured */ pCSV->eof = -1; return SQLITE_OK; } /* allocate initial space for the column pointers */ if( pCSV->maxCol < 1 ){ /* take a guess */ int maxCol = (int)(strlen(pCSV->zRow) / 5 + 1); pCSV->aCols = (char **)sqlite3_malloc( sizeof(char*) * maxCol ); if( pCSV->aCols ){ pCSV->maxCol = maxCol; } } if( !pCSV->aCols ) return SQLITE_NOMEM; /* add custom delim character */ zDelims[0] = pCSV->cDelim; /* parse the zRow into individual columns */ do{ /* if it begins with a quote, assume it's a quoted col */ if( *s=='\"' ){ s++; /* skip quote */ pCSV->aCols[nCol] = s; /* save pointer for this col */ /* TBD: handle escaped quotes "" */ /* find closing quote */ s = strchr(s, '\"'); if( !s ){ /* no closing quote */ pCSV->eof = -1; return SQLITE_ERROR; } *s = '\0'; /* null terminate this col */ /* fall through and look for following ",\n" */ s++; }else{ pCSV->aCols[nCol] = s; /* save pointer for this col */ } s = strpbrk(s, zDelims); if( !s ){ /* no col delimiter */ pCSV->eof = -1; return SQLITE_ERROR; } cDelim = *s; /* null terminate the column by overwriting the delimiter */ *s = '\0'; nCol++; /* if end of zRow, stop parsing cols */ if( cDelim == '\n' ) break; /* move to start of next col */ s++; /* skip delimiter */ if(nCol >= pCSV->maxCol ){ /* we need to grow our col pointer array */ char **p = (char **)sqlite3_realloc( pCSV->aCols, sizeof(char*) * (nCol+5) ); if( !p ){ /* out of memory */ return SQLITE_ERROR; } pCSV->maxCol = nCol + 5; pCSV->aCols = p; } }while( *s ); pCSV->nCol = nCol; return SQLITE_OK; } /* ** CSV virtual table module xEof method. ** ** Return non-zero if the cursor does not currently point to a valid ** record (i.e if the scan has finished), or zero otherwise. */ static int csvEof( sqlite3_vtab_cursor *pVtabCursor ) { CSV *pCSV = (CSV *)pVtabCursor->pVtab; return pCSV->eof; } /* ** CSV virtual table module xColumn method. */ static int csvColumn(sqlite3_vtab_cursor *pVtabCursor, sqlite3_context *ctx, int i){ CSV *pCSV = (CSV *)pVtabCursor->pVtab; if( i<0 || i>=pCSV->nCol ){ sqlite3_result_null( ctx ); }else{ char *col = pCSV->aCols[i]; if( !col ){ sqlite3_result_null( ctx ); }else{ sqlite3_result_text( ctx, col, -1, SQLITE_TRANSIENT ); } } return SQLITE_OK; } /* ** CSV virtual table module xRowid method. ** We probably should store a hidden table ** mapping rowid's to csvpos. */ static int csvRowid( sqlite3_vtab_cursor* pVtabCursor, sqlite3_int64 *pRowid ){ CSVCursor *pCsr = (CSVCursor *)pVtabCursor; *pRowid = pCsr->csvpos; return SQLITE_OK; } static sqlite3_module csvModule = { 0, /* iVersion */ csvCreate, /* xCreate - create a table */ csvConnect, /* xConnect - connect to an existing table */ csvBestIndex, /* xBestIndex - Determine search strategy */ csvDisconnect, /* xDisconnect - Disconnect from a table */ csvDestroy, /* xDestroy - Drop a table */ csvOpen, /* xOpen - open a cursor */ csvClose, /* xClose - close a cursor */ csvFilter, /* xFilter - configure scan constraints */ csvNext, /* xNext - advance a cursor */ csvEof, /* xEof */ csvColumn, /* xColumn - read data */ csvRowid, /* xRowid - read data */ 0, /* xUpdate - write data */ 0, /* xBegin - begin transaction */ 0, /* xSync - sync transaction */ 0, /* xCommit - commit transaction */ 0, /* xRollback - rollback transaction */ 0, /* xFindFunction - function overloading */ 0 /* xRename - rename the table */ }; /* ** Increment the CSV reference count. */ static void csvReference( CSV *pCSV ){ pCSV->nBusy++; } /* ** Decrement the CSV reference count. When the reference count reaches ** zero the structure is deleted. */ static int csvRelease( CSV *pCSV ){ pCSV->nBusy--; if( pCSV->nBusy<1 ){ /* finalize any prepared statements here */ csv_close( pCSV ); if( pCSV->zRow ) sqlite3_free( pCSV->zRow ); if( pCSV->aCols ) sqlite3_free( pCSV->aCols ); sqlite3_free( pCSV ); } return 0; } /* ** This function is the implementation of both the xConnect and xCreate ** methods of the CSV virtual table. ** ** argv[0] -> module name ** argv[1] -> database name ** argv[2] -> table name ** argv[3] -> csv file name ** argv[4] -> custom delimiter ** argv[5] -> optional: use header row for column names */ static int csvInit( sqlite3 *db, /* Database connection */ void *pAux, /* Unused */ int argc, const char *const*argv, /* Parameters to CREATE TABLE statement */ sqlite3_vtab **ppVtab, /* OUT: New virtual table */ char **pzErr, /* OUT: Error message, if any */ int isCreate /* True for xCreate, false for xConnect */ ){ int rc = SQLITE_OK; int i; CSV *pCSV; char *zSql; char cDelim = ','; /* Default col delimiter */ int bUseHeaderRow = 0; /* Default to not use zRow headers */ size_t nDb; /* Length of string argv[1] */ size_t nName; /* Length of string argv[2] */ size_t nFile; /* Length of string argv[3] */ CSVCursor csvCsr; /* Used for calling csvNext */ const char *aErrMsg[] = { 0, /* 0 */ "No CSV file specified", /* 1 */ "Error opening CSV file: '%s'", /* 2 */ "No columns found", /* 3 */ "No column name found", /* 4 */ "Out of memory", /* 5 */ }; UNUSED_PARAMETER(pAux); UNUSED_PARAMETER(isCreate); if( argc < 4 ){ *pzErr = sqlite3_mprintf("%s", aErrMsg[1]); return SQLITE_ERROR; } /* allocate space for the virtual table object */ nDb = strlen(argv[1]); nName = strlen(argv[2]); nFile = strlen(argv[3]); pCSV = (CSV *)sqlite3_malloc( (int)(sizeof(CSV)+nDb+nName+nFile+3) ); if( !pCSV ){ /* out of memory */ *pzErr = sqlite3_mprintf("%s", aErrMsg[5]); return SQLITE_NOMEM; } /* intialize virtual table object */ memset(pCSV, 0, sizeof(CSV)+nDb+nName+nFile+3); pCSV->nBusy = 1; pCSV->base.pModule = &csvModule; pCSV->cDelim = cDelim; pCSV->zDb = (char *)&pCSV[1]; pCSV->zName = &pCSV->zDb[nDb+1]; pCSV->zFile = &pCSV->zName[nName+1]; memcpy(pCSV->zDb, argv[1], nDb); memcpy(pCSV->zName, argv[2], nName); /* pull out name of csv file (remove quotes) */ if( argv[3][0] == '\'' ){ memcpy( pCSV->zFile, argv[3]+1, nFile-2 ); pCSV->zFile[nFile-2] = '\0'; }else{ memcpy( pCSV->zFile, argv[3], nFile ); } /* if a custom delimiter specified, pull it out */ if( argc > 4 ){ if( argv[4][0] == '\'' ){ pCSV->cDelim = argv[4][1]; }else{ pCSV->cDelim = argv[4][0]; } } /* should the header zRow be used */ if( argc > 5 ){ if( !strcmp(argv[5], "USE_HEADER_ROW") ){ bUseHeaderRow = -1; } } /* open the source csv file */ pCSV->f = csv_open( pCSV ); if( !pCSV->f ){ *pzErr = sqlite3_mprintf(aErrMsg[2], pCSV->zFile); csvRelease( pCSV ); return SQLITE_ERROR; } /* Read first zRow to obtain column names/number */ csvCsr.base.pVtab = (sqlite3_vtab *)pCSV; rc = csvNext( (sqlite3_vtab_cursor *)&csvCsr ); if( (SQLITE_OK!=rc) || (pCSV->nCol<=0) ){ *pzErr = sqlite3_mprintf("%s", aErrMsg[3]); csvRelease( pCSV ); return SQLITE_ERROR; } if( bUseHeaderRow ){ pCSV->offsetFirstRow = csv_tell( pCSV ); } /* Create the underlying relational database schema. If ** that is successful, call sqlite3_declare_vtab() to configure ** the csv table schema. */ zSql = sqlite3_mprintf("CREATE TABLE x("); for(i=0; zSql && i<pCSV->nCol; i++){ const char *zTail = (i+1<pCSV->nCol) ? ", " : ");"; char *zTmp = zSql; if( bUseHeaderRow ){ const char *zCol = pCSV->aCols[i]; if( !zCol ){ *pzErr = sqlite3_mprintf("%s", aErrMsg[4]); sqlite3_free(zSql); csvRelease( pCSV ); return SQLITE_ERROR; } zSql = sqlite3_mprintf("%s%s%s", zTmp, zCol, zTail); }else{ zSql = sqlite3_mprintf("%scol%d%s", zTmp, i+1, zTail); } sqlite3_free(zTmp); } if( !zSql ){ *pzErr = sqlite3_mprintf("%s", aErrMsg[5]); csvRelease( pCSV ); return SQLITE_NOMEM; } rc = sqlite3_declare_vtab( db, zSql ); sqlite3_free(zSql); if( SQLITE_OK != rc ){ *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(db)); csvRelease( pCSV ); return SQLITE_ERROR; } *ppVtab = (sqlite3_vtab *)pCSV; *pzErr = NULL; return SQLITE_OK; } /* ** Register the CSV module with database handle db. This creates the ** virtual table module "csv". */ int sqlite3CsvInit(sqlite3 *db){ int rc = SQLITE_OK; if( rc==SQLITE_OK ){ void *c = (void *)NULL; rc = sqlite3_create_module_v2(db, "csv", &csvModule, c, 0); } return rc; } #if !SQLITE_CORE /* ** Support auto-extension loading. */ int sqlite3_extension_init( sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi ){ SQLITE_EXTENSION_INIT2(pApi) return sqlite3CsvInit(db); } #endif #endif |
Added ext/csv/csv.h.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | /* ** 2009 March 26 ** ** The author disclaims copyright to this source code. In place of ** a legal notice, here is a blessing: ** ** May you do good and not evil. ** May you find forgiveness for yourself and forgive others. ** May you share freely, never taking more than you give. ** ****************************************************************************** ** ** This header file is used by programs that want to link against the ** CSV Virtual Table extention. ** ** All it does is declare the sqlite3CsvInit() interface. */ #include "sqlite3.h" #ifdef __cplusplus extern "C" { #endif /* __cplusplus */ int sqlite3CsvInit(sqlite3 *db); #ifdef __cplusplus } /* extern "C" */ #endif /* __cplusplus */ |
Added ext/csv/csv1.test.
> > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 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 | # 2009 Nov 11 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # # The focus of this file is testing the csv extension. # # $Id: csv1.test,v 1.7 2009/07/17 16:54:48 shaneh Exp $ # if {![info exists testdir]} { set testdir [file join [file dirname $argv0] .. .. test] } source $testdir/tester.tcl # Test plan: # # csv-1.*: Creating/destroying csv tables. # csv-2.*: Linear scans of csv data. # csv-3.*: Test renaming an csv table. # csv-4.*: CREATE errors # ifcapable !csv { finish_test return } # This file is delimited by ',' and has quoted fields. set test1csv [file join [file dirname [info script]] test1.csv] # This file is delimited by '|' and has quoted fields. set test2csv [file join [file dirname [info script]] test2.csv] # This file is delimited by '|'. It does NOT have quoted fields. set test3csv [file join [file dirname [info script]] test3.csv] #---------------------------------------------------------------------------- # Test cases csv-1.* test CREATE and DROP table statements. # # Test creating and dropping an csv table with a header row. # do_test csv-1.1.1 { execsql " CREATE VIRTUAL TABLE t1 USING csv('$test1csv', ',', USE_HEADER_ROW) " } {} do_test csv-1.1.2 { execsql { SELECT name FROM sqlite_master ORDER BY name } } {t1} do_test csv-1.1.3 { execsql { DROP TABLE t1; SELECT name FROM sqlite_master ORDER BY name; } } {} # Test creating and dropping an csv table without a header row. # do_test csv-1.2.1 { execsql " CREATE VIRTUAL TABLE t1 USING csv('$test1csv', ',') " } {} do_test csv-1.2.2 { execsql { SELECT name FROM sqlite_master ORDER BY name } } {t1} do_test csv-1.2.3 { execsql { DROP TABLE t1; SELECT name FROM sqlite_master ORDER BY name; } } {} # Test creating and dropping an csv table without a header row # and with the default delimiter ','. # do_test csv-1.3.1 { execsql " CREATE VIRTUAL TABLE t1 USING csv('$test1csv') " } {} do_test csv-1.3.2 { execsql { SELECT name FROM sqlite_master ORDER BY name } } {t1} do_test csv-1.3.3 { execsql { DROP TABLE t1; SELECT name FROM sqlite_master ORDER BY name; } } {} # Test creating and dropping an csv table without a header row # and with the custom delimiter '|'. # do_test csv-1.4.1 { execsql " CREATE VIRTUAL TABLE t1 USING csv('$test2csv', '|') " } {} do_test csv-1.4.2 { execsql { SELECT name FROM sqlite_master ORDER BY name } } {t1} do_test csv-1.4.3 { execsql { DROP TABLE t1; SELECT name FROM sqlite_master ORDER BY name; } } {} #---------------------------------------------------------------------------- # Test cases csv-2.* test linear scans of csv table data. # do_test csv-2.1.1 { execsql " CREATE VIRTUAL TABLE t1 USING csv('$test1csv', ',', USE_HEADER_ROW) " } {} do_test csv-2.1.2 { execsql { SELECT * FROM t1; } } {1 2 3 a b c a b c a b {c .. z} a b c,d} do_test csv-2.1.3 { execsql { SELECT * FROM t1 WHERE colA='a'; } } {a b c a b c a b {c .. z} a b c,d} do_test csv-2.2.1 { execsql " CREATE VIRTUAL TABLE t2 USING csv('$test1csv', ',') " } {} do_test csv-2.2.2 { execsql { SELECT * FROM t2; } } {colA colB colC 1 2 3 a b c a b c a b {c .. z} a b c,d} do_test csv-2.2.3 { execsql { SELECT * FROM t2 WHERE col1='a'; } } {a b c a b c a b {c .. z} a b c,d} # Test scanning with the custom delimiter '|'. # do_test csv-2.3.1 { execsql " CREATE VIRTUAL TABLE t3 USING csv('$test2csv', '|') " } {} do_test csv-2.3.2 { execsql { SELECT * FROM t3; } } {colA colB colC 1 2 3 a b c a b c a b {c .. z} a b c|d} do_test csv-2.3.3 { execsql { SELECT * FROM t3 WHERE col1='a'; } } {a b c a b c a b {c .. z} a b c|d} # Test scanning with the custom delimiter ';'. The test file # uses | for a delimiter, so everything should be treated as # a single column. # do_test csv-2.4.1 { execsql " CREATE VIRTUAL TABLE t4 USING csv('$test3csv', ';') " } {} do_test csv-2.4.2 { execsql { SELECT * FROM t4; } } {colA|colB|colC 1|2|3 a|b|c {a|b|c .. z} a|b|c|d} do_test csv-2.4.3 { execsql { SELECT * FROM t4 WHERE col1 LIKE 'a%'; } } {a|b|c {a|b|c .. z} a|b|c|d} # Test rowid column. # do_test csv-2.5.1 { execsql { SELECT rowid FROM t1; } } {21 27 33 41 58} do_test csv-2.5.2 { execsql { SELECT rowid FROM t1 WHERE colA='a'; } } {27 33 41 58} # Clean-up. # do_test csv-2.6.1 { execsql { DROP TABLE t1; DROP TABLE t2; DROP TABLE t3; DROP TABLE t4; } } {} #---------------------------------------------------------------------------- # Test cases csv-3.* test rename operations. # do_test csv-3.1.1 { execsql " CREATE VIRTUAL TABLE t1 USING csv('$test1csv', ',', USE_HEADER_ROW) " execsql " CREATE VIRTUAL TABLE t2 USING csv('$test1csv', ',', USE_HEADER_ROW) " } {} do_test csv-3.1.2 { catchsql { ALTER TABLE t2 RENAME TO t1 } } {1 {there is already another table or index with this name: t1}} do_test csv-3.1.3 { execsql { DROP TABLE t1; ALTER TABLE t2 RENAME TO t1 } } {} do_test csv-3.1.4 { execsql { ALTER TABLE t1 RENAME TO t5 } execsql { SELECT * FROM t5 } } {1 2 3 a b c a b c a b {c .. z} a b c,d} do_test csv-3.1.5 { db close sqlite3 db test.db execsql { SELECT * FROM t5 } } {1 2 3 a b c a b c a b {c .. z} a b c,d} do_test csv-3.1.6 { execsql { ALTER TABLE t5 RENAME TO 'raisara "one"'''} execsql { SELECT * FROM "raisara ""one""'" } } {1 2 3 a b c a b c a b {c .. z} a b c,d} do_test csv-3.1.7 { execsql { SELECT * FROM 'raisara "one"''' } } {1 2 3 a b c a b c a b {c .. z} a b c,d} do_test csv-3.1.8 { execsql { ALTER TABLE "raisara ""one""'" RENAME TO "abc 123" } execsql { SELECT * FROM "abc 123" } } {1 2 3 a b c a b c a b {c .. z} a b c,d} do_test csv-3.1.9 { db close sqlite3 db test.db execsql { SELECT * FROM "abc 123" } } {1 2 3 a b c a b c a b {c .. z} a b c,d} #---------------------------------------------------------------------------- # Test cases csv-4.* test CREATE errors # # Test creating and dropping an csv table with a header row. # do_test csv-4.1.1 { catchsql " CREATE VIRTUAL TABLE t1 USING csv() " } {1 {No CSV file specified}} do_test csv-4.1.2 { catchsql " CREATE VIRTUAL TABLE t1 USING csv('foo') " } {1 {Error opening CSV file: 'foo'}} do_test csv-4.1.3 { catchsql " CREATE VIRTUAL TABLE t1 USING csv(foo foo) " } {1 {Error opening CSV file: 'foo foo'}} |
Added ext/csv/test1.csv.
> > > > > > | 1 2 3 4 5 6 | "colA","colB","colC" 1,2,3 a,b,c a,"b",c "a","b","c .. z" "a","b","c,d" |
Added ext/csv/test2.csv.
> > > > > > | 1 2 3 4 5 6 | "colA"|"colB"|"colC" 1|2|3 a|b|c a|"b"|c "a"|"b"|"c .. z" "a"|"b"|"c|d" |
Added ext/csv/test3.csv.
> > > > > | 1 2 3 4 5 | colA|colB|colC 1|2|3 a|b|c a|b|c .. z a|b|c|d |
Changes to src/main.c.
︙ | ︙ | |||
21 22 23 24 25 26 27 28 29 30 31 32 33 34 | #endif #ifdef SQLITE_ENABLE_RTREE # include "rtree.h" #endif #ifdef SQLITE_ENABLE_ICU # include "sqliteicu.h" #endif /* ** The version of the library */ #ifndef SQLITE_AMALGAMATION const char sqlite3_version[] = SQLITE_VERSION; #endif | > > > | 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 | #endif #ifdef SQLITE_ENABLE_RTREE # include "rtree.h" #endif #ifdef SQLITE_ENABLE_ICU # include "sqliteicu.h" #endif #ifdef SQLITE_ENABLE_CSV # include "csv.h" #endif /* ** The version of the library */ #ifndef SQLITE_AMALGAMATION const char sqlite3_version[] = SQLITE_VERSION; #endif |
︙ | ︙ | |||
1710 1711 1712 1713 1714 1715 1716 1717 1718 1719 1720 1721 1722 1723 | #endif #ifdef SQLITE_ENABLE_RTREE if( !db->mallocFailed && rc==SQLITE_OK){ rc = sqlite3RtreeInit(db); } #endif sqlite3Error(db, rc, 0); /* -DSQLITE_DEFAULT_LOCKING_MODE=1 makes EXCLUSIVE the default locking ** mode. -DSQLITE_DEFAULT_LOCKING_MODE=0 make NORMAL the default locking ** mode. Doing nothing at all also makes NORMAL the default. */ | > > > > > > | 1713 1714 1715 1716 1717 1718 1719 1720 1721 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 | #endif #ifdef SQLITE_ENABLE_RTREE if( !db->mallocFailed && rc==SQLITE_OK){ rc = sqlite3RtreeInit(db); } #endif #ifdef SQLITE_ENABLE_CSV if( !db->mallocFailed && rc==SQLITE_OK){ rc = sqlite3CsvInit(db); } #endif sqlite3Error(db, rc, 0); /* -DSQLITE_DEFAULT_LOCKING_MODE=1 makes EXCLUSIVE the default locking ** mode. -DSQLITE_DEFAULT_LOCKING_MODE=0 make NORMAL the default locking ** mode. Doing nothing at all also makes NORMAL the default. */ |
︙ | ︙ |
Changes to src/test_config.c.
︙ | ︙ | |||
379 380 381 382 383 384 385 386 387 388 389 390 391 392 | #endif #ifdef SQLITE_ENABLE_RTREE Tcl_SetVar2(interp, "sqlite_options", "rtree", "1", TCL_GLOBAL_ONLY); #else Tcl_SetVar2(interp, "sqlite_options", "rtree", "0", TCL_GLOBAL_ONLY); #endif #ifdef SQLITE_OMIT_SCHEMA_PRAGMAS Tcl_SetVar2(interp, "sqlite_options", "schema_pragmas", "0", TCL_GLOBAL_ONLY); #else Tcl_SetVar2(interp, "sqlite_options", "schema_pragmas", "1", TCL_GLOBAL_ONLY); #endif | > > > > > > | 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 | #endif #ifdef SQLITE_ENABLE_RTREE Tcl_SetVar2(interp, "sqlite_options", "rtree", "1", TCL_GLOBAL_ONLY); #else Tcl_SetVar2(interp, "sqlite_options", "rtree", "0", TCL_GLOBAL_ONLY); #endif #ifdef SQLITE_ENABLE_CSV Tcl_SetVar2(interp, "sqlite_options", "csv", "1", TCL_GLOBAL_ONLY); #else Tcl_SetVar2(interp, "sqlite_options", "csv", "0", TCL_GLOBAL_ONLY); #endif #ifdef SQLITE_OMIT_SCHEMA_PRAGMAS Tcl_SetVar2(interp, "sqlite_options", "schema_pragmas", "0", TCL_GLOBAL_ONLY); #else Tcl_SetVar2(interp, "sqlite_options", "schema_pragmas", "1", TCL_GLOBAL_ONLY); #endif |
︙ | ︙ |