/ Check-in [90e63b7d]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Initial implementation of a virtual table for CSV files.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | csv_ext
Files: files | file ages | folders
SHA1:90e63b7d845bacc9a1a1db13c1e9a406e5306faa
User & Date: shaneh 2009-11-05 02:34:43
Context
2009-11-05
04:01
Removed line limit on rows. check-in: ec08b15f user: shaneh tags: csv_ext
02:34
Initial implementation of a virtual table for CSV files. check-in: 90e63b7d user: shaneh tags: csv_ext
2009-11-04
13:30
Version 3.6.20 check-in: eb7a544f user: drh tags: trunk, release
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added ext/csv/csv.c.

            1  +/*
            2  +** 2009 March 26
            3  +**
            4  +** The author disclaims copyright to this source code.  In place of
            5  +** a legal notice, here is a blessing:
            6  +**
            7  +**    May you do good and not evil.
            8  +**    May you find forgiveness for yourself and forgive others.
            9  +**    May you share freely, never taking more than you give.
           10  +**
           11  +*************************************************************************
           12  +** This file contains code for implementations of the CSV
           13  +** algorithms packaged as an SQLite virtual table module.
           14  +*/
           15  +#if defined(_WIN32) || defined(WIN32)
           16  +/* This needs to come before any includes for MSVC compiler */
           17  +#define _CRT_SECURE_NO_WARNINGS
           18  +#endif
           19  +
           20  +#if !defined(SQLITE_CORE) || defined(SQLITE_ENABLE_CSV)
           21  +
           22  +
           23  +#include <stdio.h>
           24  +#include <stdlib.h>
           25  +#include <string.h>
           26  +
           27  +
           28  +#ifndef SQLITE_CORE
           29  +  #include "sqlite3ext.h"
           30  +  SQLITE_EXTENSION_INIT1
           31  +#else
           32  +  #include "sqlite3.h"
           33  +#endif
           34  +
           35  +
           36  +#define UNUSED_PARAMETER(x) (void)(x)
           37  +
           38  +
           39  +/* 
           40  +** The CSV virtual-table types.
           41  +*/
           42  +typedef struct CSV CSV;
           43  +typedef struct CSVCursor CSVCursor;
           44  +
           45  +
           46  +/* 
           47  +** An CSV virtual-table object.
           48  +*/
           49  +struct CSV {
           50  +  sqlite3_vtab base;           /* Must be first */
           51  +  sqlite3 *db;                 /* Host database connection */
           52  +  char *zDb;                   /* Name of database containing CSV table */
           53  +  char *zName;                 /* Name of CSV table */ 
           54  +  char *zFile;                 /* Name of CSV file */ 
           55  +  int nBusy;                   /* Current number of users of this structure */
           56  +  FILE *f;                     /* File pointer for source CSV file */
           57  +  long offsetFirstRow;         /* ftell position of first row */
           58  +  int eof;                     /* True when at end of file */
           59  +  char zRow[4096];             /* Buffer for current CSV row */
           60  +  char cDelim;                 /* Character to use for delimiting columns */
           61  +  int nCol;                    /* Number of columns in current row */
           62  +  int maxCol;                  /* Size of aCols array */
           63  +  char **aCols;                /* Array of parsed columns */
           64  +};
           65  +
           66  +
           67  +/* 
           68  +** An CSV cursor object.
           69  +*/
           70  +struct CSVCursor {
           71  +  sqlite3_vtab_cursor base;    /* Must be first */
           72  +  long csvpos;                 /* ftell position of current zRow */
           73  +};
           74  +
           75  +
           76  +/*
           77  +** Forward declarations.
           78  +*/
           79  +static int csvNext( sqlite3_vtab_cursor* pVtabCursor );
           80  +static int csvInit(
           81  +  sqlite3 *db,                        /* Database connection */
           82  +  void *pAux,                         /* Unused */
           83  +  int argc, const char *const*argv,   /* Parameters to CREATE TABLE statement */
           84  +  sqlite3_vtab **ppVtab,              /* OUT: New virtual table */
           85  +  char **pzErr,                       /* OUT: Error message, if any */
           86  +  int isCreate                        /* True for xCreate, false for xConnect */
           87  +);
           88  +static void csvReference( CSV *pCSV );
           89  +static int csvRelease( CSV *pCSV );
           90  +
           91  +
           92  +/* 
           93  +** Abstract out file io routines for porting 
           94  +*/
           95  +static FILE *csv_open( CSV *pCSV ){
           96  +  return fopen( pCSV->zFile, "rb" );
           97  +}
           98  +static void csv_close( CSV *pCSV ){
           99  +  if( pCSV->f ) fclose( pCSV->f );
          100  +}
          101  +static int csv_seek( CSV *pCSV, long pos ){
          102  +  return fseek( pCSV->f, pos, SEEK_SET );
          103  +}
          104  +static long csv_tell( CSV *pCSV ){
          105  +  return ftell( pCSV->f );
          106  +}
          107  +static char *csv_gets( CSV *pCSV ){
          108  +  return fgets( pCSV->zRow, sizeof(pCSV->zRow), pCSV->f );
          109  +}
          110  +
          111  +
          112  +/* 
          113  +** CSV virtual table module xCreate method.
          114  +*/
          115  +static int csvCreate(
          116  +  sqlite3* db, 
          117  +  void *pAux,
          118  +  int argc, 
          119  +  const char *const *argv,
          120  +  sqlite3_vtab **ppVtab, 
          121  +  char **pzErr 
          122  +){
          123  +  return csvInit( db, pAux, argc, argv, ppVtab, pzErr, 1 );
          124  +}
          125  +
          126  +
          127  +/* 
          128  +** CSV virtual table module xConnect method.
          129  +*/
          130  +static int csvConnect(
          131  +  sqlite3 *db,
          132  +  void *pAux,
          133  +  int argc, const char *const*argv,
          134  +  sqlite3_vtab **ppVtab,
          135  +  char **pzErr
          136  +){
          137  +  return csvInit(db, pAux, argc, argv, ppVtab, pzErr, 0);
          138  +}
          139  +
          140  +
          141  +/*
          142  +** CSV virtual table module xBestIndex method.
          143  +*/
          144  +static int csvBestIndex( sqlite3_vtab *pVtab, sqlite3_index_info* info )
          145  +{
          146  +  UNUSED_PARAMETER(pVtab);
          147  +  UNUSED_PARAMETER(info);
          148  +
          149  +  /* TBD */
          150  +
          151  +  return SQLITE_OK;
          152  +}
          153  +
          154  +
          155  +/* 
          156  +** CSV virtual table module xDisconnect method.
          157  +*/
          158  +static int csvDisconnect( sqlite3_vtab *pVtab ){
          159  +  return csvRelease( (CSV *)pVtab );
          160  +}
          161  +
          162  +
          163  +/* 
          164  +** CSV virtual table module xDestroy method.
          165  +*/
          166  +static int csvDestroy( sqlite3_vtab *pVtab ){
          167  +  return csvDisconnect( pVtab );
          168  +}
          169  +
          170  +
          171  +/* 
          172  +** CSV virtual table module xOpen method.
          173  +*/
          174  +static int csvOpen( sqlite3_vtab *pVtab, sqlite3_vtab_cursor **ppVtabCursor ){
          175  +  int rc = SQLITE_NOMEM;
          176  +  CSVCursor *pCsr;
          177  +
          178  +  /* create a new cursor object */
          179  +  pCsr = (CSVCursor *)sqlite3_malloc(sizeof(CSVCursor));
          180  +  if( pCsr ){
          181  +    memset(pCsr, 0, sizeof(CSVCursor));
          182  +    pCsr->base.pVtab = pVtab;
          183  +    rc = SQLITE_OK;
          184  +  }
          185  +  *ppVtabCursor = (sqlite3_vtab_cursor *)pCsr;
          186  +
          187  +  return rc;
          188  +}
          189  +
          190  +
          191  +/* 
          192  +** CSV virtual table module xClose method.
          193  +*/
          194  +static int csvClose( sqlite3_vtab_cursor *pVtabCursor ){
          195  +  CSVCursor *pCsr = (CSVCursor *)pVtabCursor;
          196  +
          197  +  sqlite3_free(pCsr);
          198  +
          199  +  return SQLITE_OK;
          200  +}
          201  +
          202  +
          203  +/* 
          204  +** CSV virtual table module xFilter method.
          205  +*/
          206  +static int csvFilter(
          207  +  sqlite3_vtab_cursor *pVtabCursor, 
          208  +  int idxNum, const char *idxStr,
          209  +  int argc, sqlite3_value **argv
          210  +){
          211  +  CSV *pCSV = (CSV *)pVtabCursor->pVtab;
          212  +  int rc;
          213  +
          214  +  UNUSED_PARAMETER(idxNum);
          215  +  UNUSED_PARAMETER(idxStr);
          216  +  UNUSED_PARAMETER(argc);
          217  +  UNUSED_PARAMETER(argv);
          218  +
          219  +  csvReference( pCSV );
          220  +
          221  +  /* seek back to start of first zRow */
          222  +  pCSV->eof = 0;
          223  +  csv_seek( pCSV, pCSV->offsetFirstRow );
          224  +  /* read and parse next line */
          225  +  rc = csvNext( pVtabCursor );
          226  +
          227  +  csvRelease( pCSV );
          228  +
          229  +  return rc;
          230  +}
          231  +
          232  +
          233  +/* 
          234  +** CSV virtual table module xNext method.
          235  +*/
          236  +static int csvNext( sqlite3_vtab_cursor* pVtabCursor ){
          237  +  CSV *pCSV = (CSV *)pVtabCursor->pVtab;
          238  +  CSVCursor *pCsr = (CSVCursor *)pVtabCursor;
          239  +  int nCol = 0;
          240  +  char *s;
          241  +  char zDelims[4] = ",\r\n";
          242  +  char cDelim; /* char that delimited current col */
          243  +
          244  +  if( pCSV->eof ){
          245  +    return SQLITE_ERROR;
          246  +  }
          247  +
          248  +  /* update the cursor */
          249  +  pCsr->csvpos = csv_tell( pCSV );
          250  +
          251  +  /* read the next row of data */
          252  +  s = csv_gets( pCSV );
          253  +  if( !s ){
          254  +    /* and error or eof occured */
          255  +    pCSV->eof = -1;
          256  +    return SQLITE_OK;
          257  +  }
          258  +
          259  +  /* allocate initial space for the column pointers */
          260  +  if( pCSV->maxCol < 1 ){
          261  +    /* take a guess */
          262  +    pCSV->maxCol = (int)(strlen(pCSV->zRow) / 5 + 1);
          263  +    if( pCSV->aCols ) sqlite3_free( pCSV->aCols );
          264  +    pCSV->aCols = (char **)sqlite3_malloc( sizeof(char*) * pCSV->maxCol );
          265  +    if( !pCSV->aCols ){
          266  +      /* out of memory */
          267  +      return SQLITE_NOMEM;
          268  +    }
          269  +  }
          270  +
          271  +  /* add custom delim character */
          272  +  zDelims[0] = pCSV->cDelim;
          273  +
          274  +  /* parse the zRow into individual columns */
          275  +  do{
          276  +    /* if it begins with a quote, assume it's a quoted col */
          277  +    if( *s=='\"' ){
          278  +      s++;  /* skip quote */
          279  +      pCSV->aCols[nCol] = s; /* save pointer for this col */
          280  +      /* find closing quote */
          281  +#if 1
          282  +      s = strchr(s, '\"');
          283  +      if( !s ){
          284  +        /* no closing quote */
          285  +        pCSV->eof = -1;
          286  +        return SQLITE_ERROR;
          287  +      }
          288  +      *s = '\0'; /* null terminate this col */
          289  +      /* fall through and look for following ",\n\r" */
          290  +      s++;
          291  +#else
          292  +      /* TBD: handle escaped quotes "" */
          293  +      while( s[0] ){
          294  +        if( s[0]=='\"' ){
          295  +          if( s[1]=='\"' ){
          296  +          }
          297  +          break;
          298  +        }
          299  +        s++;
          300  +      }
          301  +#endif
          302  +    }else{
          303  +      pCSV->aCols[nCol] = s; /* save pointer for this col */
          304  +    }
          305  +    s = strpbrk(s, zDelims);
          306  +    if( !s ){
          307  +      /* no col delimiter */
          308  +      pCSV->eof = -1;
          309  +      return SQLITE_ERROR;
          310  +    }
          311  +    cDelim = *s;
          312  +    /* null terminate the column by overwriting the delimiter */
          313  +    *s = '\0';
          314  +    nCol++;
          315  +    /* if end of zRow, stop parsing cols */
          316  +    if( (cDelim == '\n') || (cDelim == '\r') ) break;
          317  +    /* move to start of next col */
          318  +    s++; /* skip delimiter */
          319  +
          320  +    if(nCol >= pCSV->maxCol ){
          321  +      /* we need to grow our col pointer array */
          322  +      char **p = (char **)sqlite3_realloc( pCSV->aCols, sizeof(char*) * (nCol+5) );
          323  +      if( !p ){
          324  +        /* out of memory */
          325  +        return SQLITE_ERROR;
          326  +      }
          327  +      pCSV->maxCol = nCol + 5;
          328  +      pCSV->aCols = p;
          329  +    }
          330  +
          331  +  }while( *s );
          332  +
          333  +  pCSV->nCol = nCol;
          334  +  return SQLITE_OK;
          335  +}
          336  +
          337  +
          338  +/*
          339  +** CSV virtual table module xEof method.
          340  +**
          341  +** Return non-zero if the cursor does not currently point to a valid 
          342  +** record (i.e if the scan has finished), or zero otherwise.
          343  +*/
          344  +static int csvEof( sqlite3_vtab_cursor *pVtabCursor )
          345  +{
          346  +  CSV *pCSV = (CSV *)pVtabCursor->pVtab;
          347  +
          348  +  return pCSV->eof;
          349  +}
          350  +
          351  +
          352  +/* 
          353  +** CSV virtual table module xColumn method.
          354  +*/
          355  +static int csvColumn(sqlite3_vtab_cursor *pVtabCursor, sqlite3_context *ctx, int i){
          356  +  CSV *pCSV = (CSV *)pVtabCursor->pVtab;
          357  +
          358  +  if( i<0 || i>=pCSV->nCol ){
          359  +    sqlite3_result_null( ctx );
          360  +  }else{
          361  +    char *col = pCSV->aCols[i];
          362  +    if( !col ){
          363  +      sqlite3_result_null( ctx );
          364  +    }else{
          365  +      sqlite3_result_text( ctx, col, -1, SQLITE_TRANSIENT );
          366  +    }
          367  +  }
          368  +
          369  +  return SQLITE_OK;
          370  +}
          371  +
          372  +
          373  +/* 
          374  +** CSV virtual table module xRowid method.
          375  +** We probably should store a hidden table
          376  +** mapping rowid's to csvpos.
          377  +*/
          378  +static int csvRowid( sqlite3_vtab_cursor* pVtabCursor, sqlite3_int64 *pRowid ){
          379  +  CSVCursor *pCsr = (CSVCursor *)pVtabCursor;
          380  +
          381  +  *pRowid = pCsr->csvpos;
          382  +
          383  +  return SQLITE_OK;
          384  +}
          385  +
          386  +
          387  +static sqlite3_module csvModule = {
          388  +  0,                        /* iVersion */
          389  +  csvCreate,                /* xCreate - create a table */
          390  +  csvConnect,               /* xConnect - connect to an existing table */
          391  +  csvBestIndex,             /* xBestIndex - Determine search strategy */
          392  +  csvDisconnect,            /* xDisconnect - Disconnect from a table */
          393  +  csvDestroy,               /* xDestroy - Drop a table */
          394  +  csvOpen,                  /* xOpen - open a cursor */
          395  +  csvClose,                 /* xClose - close a cursor */
          396  +  csvFilter,                /* xFilter - configure scan constraints */
          397  +  csvNext,                  /* xNext - advance a cursor */
          398  +  csvEof,                   /* xEof */
          399  +  csvColumn,                /* xColumn - read data */
          400  +  csvRowid,                 /* xRowid - read data */
          401  +  0,                        /* xUpdate - write data */
          402  +  0,                        /* xBegin - begin transaction */
          403  +  0,                        /* xSync - sync transaction */
          404  +  0,                        /* xCommit - commit transaction */
          405  +  0,                        /* xRollback - rollback transaction */
          406  +  0,                        /* xFindFunction - function overloading */
          407  +  0                         /* xRename - rename the table */
          408  +};
          409  +
          410  +
          411  +/*
          412  +** Increment the CSV reference count.
          413  +*/
          414  +static void csvReference( CSV *pCSV ){
          415  +  pCSV->nBusy++;
          416  +}
          417  +
          418  +
          419  +/*
          420  +** Decrement the CSV reference count. When the reference count reaches
          421  +** zero the structure is deleted.
          422  +*/
          423  +static int csvRelease( CSV *pCSV ){
          424  +  pCSV->nBusy--;
          425  +  if( pCSV->nBusy<1 ){
          426  +
          427  +    /* finalize any prepared statements here */
          428  +
          429  +    csv_close( pCSV );
          430  +    if( pCSV->aCols ) sqlite3_free( pCSV->aCols );
          431  +    sqlite3_free( pCSV );
          432  +  }
          433  +  return 0;
          434  +}
          435  +
          436  +
          437  +/* 
          438  +** This function is the implementation of both the xConnect and xCreate
          439  +** methods of the CSV virtual table.
          440  +**
          441  +**   argv[0]   -> module name
          442  +**   argv[1]   -> database name
          443  +**   argv[2]   -> table name
          444  +**   argv[3]   -> csv file name
          445  +**   argv[4]   -> custom delimiter
          446  +**   argv[5]   -> optional:  use header row for column names
          447  +*/
          448  +static int csvInit(
          449  +  sqlite3 *db,                        /* Database connection */
          450  +  void *pAux,                         /* Unused */
          451  +  int argc, const char *const*argv,   /* Parameters to CREATE TABLE statement */
          452  +  sqlite3_vtab **ppVtab,              /* OUT: New virtual table */
          453  +  char **pzErr,                       /* OUT: Error message, if any */
          454  +  int isCreate                        /* True for xCreate, false for xConnect */
          455  +){
          456  +  int rc = SQLITE_OK;
          457  +  int i;
          458  +  CSV *pCSV;
          459  +  char *zSql;
          460  +  char cDelim = ',';       /* Default col delimiter */
          461  +  int bUseHeaderRow = 0;   /* Default to not use zRow headers */
          462  +  size_t nDb;              /* Length of string argv[1] */
          463  +  size_t nName;            /* Length of string argv[2] */
          464  +  size_t nFile;            /* Length of string argv[3] */
          465  +  CSVCursor csvCsr;        /* Used for calling csvNext */
          466  +
          467  +  const char *aErrMsg[] = {
          468  +    0,                                                    /* 0 */
          469  +    "No CSV file specified",                              /* 1 */
          470  +    "Error opening CSV file: '%s'",                       /* 2 */
          471  +    "No columns found",                                   /* 3 */
          472  +    "No column name found",                               /* 4 */
          473  +    "Out of memory",                                      /* 5 */
          474  +  };
          475  +
          476  +  UNUSED_PARAMETER(pAux);
          477  +  UNUSED_PARAMETER(isCreate);
          478  +
          479  +  if( argc < 4 ){
          480  +    *pzErr = sqlite3_mprintf("%s", aErrMsg[1]);
          481  +    return SQLITE_ERROR;
          482  +  }
          483  +
          484  +  /* allocate space for the virtual table object */
          485  +  nDb = strlen(argv[1]);
          486  +  nName = strlen(argv[2]);
          487  +  nFile = strlen(argv[3]);
          488  +  pCSV = (CSV *)sqlite3_malloc( (int)(sizeof(CSV)+nDb+nName+nFile+3) );
          489  +  if( !pCSV ){
          490  +    /* out of memory */
          491  +    *pzErr = sqlite3_mprintf("%s", aErrMsg[5]);
          492  +    return SQLITE_NOMEM;
          493  +  }
          494  +
          495  +  /* intialize virtual table object */
          496  +  memset(pCSV, 0, sizeof(CSV)+nDb+nName+nFile+3);
          497  +  pCSV->nBusy = 1;
          498  +  pCSV->base.pModule = &csvModule;
          499  +  pCSV->cDelim = cDelim;
          500  +  pCSV->zDb = (char *)&pCSV[1];
          501  +  pCSV->zName = &pCSV->zDb[nDb+1];
          502  +  pCSV->zFile = &pCSV->zName[nName+1];
          503  +  memcpy(pCSV->zDb, argv[1], nDb);
          504  +  memcpy(pCSV->zName, argv[2], nName);
          505  +
          506  +  /* pull out name of csv file (remove quotes) */
          507  +  if( argv[3][0] == '\'' ){
          508  +    memcpy( pCSV->zFile, argv[3]+1, nFile-2 );
          509  +    pCSV->zFile[nFile-2] = '\0';
          510  +  }else{
          511  +    memcpy( pCSV->zFile, argv[3], nFile );
          512  +  }
          513  +
          514  +  /* if a custom delimiter specified, pull it out */
          515  +  if( argc > 4 ){
          516  +    if( argv[4][0] == '\'' ){
          517  +      pCSV->cDelim = argv[4][1];
          518  +    }else{
          519  +      pCSV->cDelim = argv[4][0];
          520  +    }
          521  +  }
          522  +
          523  +  /* should the header zRow be used */
          524  +  if( argc > 5 ){
          525  +    if( !strcmp(argv[5], "USE_HEADER_ROW") ){
          526  +      bUseHeaderRow = -1;
          527  +    }
          528  +  }
          529  +
          530  +  /* open the source csv file */
          531  +  pCSV->f = csv_open( pCSV );
          532  +  if( !pCSV->f ){
          533  +    *pzErr = sqlite3_mprintf(aErrMsg[2], pCSV->zFile);
          534  +    csvRelease( pCSV );
          535  +    return SQLITE_ERROR;
          536  +  }
          537  +
          538  +  /* Read first zRow to obtain column names/number */
          539  +  csvCsr.base.pVtab = (sqlite3_vtab *)pCSV;
          540  +  rc = csvNext( (sqlite3_vtab_cursor *)&csvCsr );
          541  +  if( (SQLITE_OK!=rc) || (pCSV->nCol<=0) ){
          542  +    *pzErr = sqlite3_mprintf("%s", aErrMsg[3]);
          543  +    csvRelease( pCSV );
          544  +    return SQLITE_ERROR;
          545  +  }
          546  +  if( bUseHeaderRow ){
          547  +    pCSV->offsetFirstRow = csv_tell( pCSV );
          548  +  }
          549  +
          550  +  /* Create the underlying relational database schema. If
          551  +  ** that is successful, call sqlite3_declare_vtab() to configure
          552  +  ** the csv table schema.
          553  +  */
          554  +  zSql = sqlite3_mprintf("CREATE TABLE x(");
          555  +  for(i=0; zSql && i<pCSV->nCol; i++){
          556  +    const char *zTail = (i+1<pCSV->nCol) ? ", " : ");";
          557  +    char *zTmp = zSql;
          558  +    if( bUseHeaderRow ){
          559  +      const char *zCol = pCSV->aCols[i];
          560  +      if( !zCol ){
          561  +        *pzErr = sqlite3_mprintf("%s", aErrMsg[4]);
          562  +        sqlite3_free(zSql);
          563  +        csvRelease( pCSV );
          564  +        return SQLITE_ERROR;
          565  +      }
          566  +      zSql = sqlite3_mprintf("%s%s%s", zTmp, zCol, zTail);
          567  +    }else{
          568  +      zSql = sqlite3_mprintf("%scol%d%s", zTmp, i+1, zTail);
          569  +    }
          570  +    sqlite3_free(zTmp);
          571  +  }
          572  +  if( !zSql ){
          573  +    *pzErr = sqlite3_mprintf("%s", aErrMsg[5]);
          574  +    csvRelease( pCSV );
          575  +    return SQLITE_NOMEM;
          576  +  }
          577  +
          578  +  rc = sqlite3_declare_vtab( db, zSql );
          579  +  sqlite3_free(zSql);
          580  +  if( SQLITE_OK != rc ){
          581  +    *pzErr = sqlite3_mprintf("%s", sqlite3_errmsg(db));
          582  +    csvRelease( pCSV );
          583  +    return SQLITE_ERROR;
          584  +  }
          585  +
          586  +  *ppVtab = (sqlite3_vtab *)pCSV;
          587  +  *pzErr  = NULL;
          588  +  return SQLITE_OK;
          589  +}
          590  +
          591  +
          592  +/*
          593  +** Register the CSV module with database handle db. This creates the
          594  +** virtual table module "csv".
          595  +*/
          596  +int sqlite3CsvInit(sqlite3 *db){
          597  +  int rc = SQLITE_OK;
          598  +
          599  +  if( rc==SQLITE_OK ){
          600  +    void *c = (void *)NULL;
          601  +    rc = sqlite3_create_module_v2(db, "csv", &csvModule, c, 0);
          602  +  }
          603  +
          604  +  return rc;
          605  +}
          606  +
          607  +
          608  +#if !SQLITE_CORE
          609  +/*
          610  +** Support auto-extension loading.
          611  +*/
          612  +int sqlite3_extension_init(
          613  +  sqlite3 *db,
          614  +  char **pzErrMsg,
          615  +  const sqlite3_api_routines *pApi
          616  +){
          617  +  SQLITE_EXTENSION_INIT2(pApi)
          618  +  return sqlite3CsvInit(db);
          619  +}
          620  +#endif
          621  +
          622  +
          623  +#endif

Added ext/csv/csv.h.

            1  +/*
            2  +** 2009 March 26
            3  +**
            4  +** The author disclaims copyright to this source code.  In place of
            5  +** a legal notice, here is a blessing:
            6  +**
            7  +**    May you do good and not evil.
            8  +**    May you find forgiveness for yourself and forgive others.
            9  +**    May you share freely, never taking more than you give.
           10  +**
           11  +******************************************************************************
           12  +**
           13  +** This header file is used by programs that want to link against the
           14  +** CSV Virtual Table extention.
           15  +**
           16  +** All it does is declare the sqlite3CsvInit() interface.
           17  +*/
           18  +#include "sqlite3.h"
           19  +
           20  +#ifdef __cplusplus
           21  +extern "C" {
           22  +#endif  /* __cplusplus */
           23  +
           24  +int sqlite3CsvInit(sqlite3 *db);
           25  +
           26  +#ifdef __cplusplus
           27  +}  /* extern "C" */
           28  +#endif  /* __cplusplus */

Added ext/csv/csv1.test.

            1  +# 2009 Nov 11
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +# The focus of this file is testing the csv extension.
           13  +#
           14  +# $Id: csv1.test,v 1.7 2009/07/17 16:54:48 shaneh Exp $
           15  +#
           16  +
           17  +if {![info exists testdir]} {
           18  +  set testdir [file join [file dirname $argv0] .. .. test]
           19  +}
           20  +source $testdir/tester.tcl
           21  +
           22  +# Test plan:
           23  +#
           24  +#   csv-1.*: Creating/destroying csv tables.
           25  +#   csv-2.*: Linear scans of csv data.
           26  +#   csv-3.*: Test renaming an csv table.
           27  +#   csv-4.*: CREATE errors
           28  +#
           29  +
           30  +ifcapable !csv {
           31  +  finish_test
           32  +  return
           33  +}
           34  +
           35  +# This file is delimited by ',' and has quoted fields.
           36  +set test1csv [file join [file dirname [info script]] test1.csv]
           37  +# This file is delimited by '|' and has quoted fields.
           38  +set test2csv [file join [file dirname [info script]] test2.csv]
           39  +# This file is delimited by '|'.  It does NOT have quoted fields.
           40  +set test3csv [file join [file dirname [info script]] test3.csv]
           41  +
           42  +#----------------------------------------------------------------------------
           43  +# Test cases csv-1.* test CREATE and DROP table statements.
           44  +#
           45  +
           46  +# Test creating and dropping an csv table with a header row.
           47  +#
           48  +do_test csv-1.1.1 {
           49  +  execsql " CREATE VIRTUAL TABLE t1 USING csv('$test1csv', ',', USE_HEADER_ROW) "
           50  +} {}
           51  +do_test csv-1.1.2 {
           52  +  execsql { SELECT name FROM sqlite_master ORDER BY name }
           53  +} {t1}
           54  +do_test csv-1.1.3 {
           55  +  execsql { 
           56  +    DROP TABLE t1; 
           57  +    SELECT name FROM sqlite_master ORDER BY name;
           58  +  }
           59  +} {}
           60  +
           61  +# Test creating and dropping an csv table without a header row.
           62  +#
           63  +do_test csv-1.2.1 {
           64  +  execsql " CREATE VIRTUAL TABLE t1 USING csv('$test1csv', ',') "
           65  +} {}
           66  +do_test csv-1.2.2 {
           67  +  execsql { SELECT name FROM sqlite_master ORDER BY name }
           68  +} {t1}
           69  +do_test csv-1.2.3 {
           70  +  execsql { 
           71  +    DROP TABLE t1; 
           72  +    SELECT name FROM sqlite_master ORDER BY name;
           73  +  }
           74  +} {}
           75  +
           76  +# Test creating and dropping an csv table without a header row
           77  +# and with the default delimiter ','.
           78  +#
           79  +do_test csv-1.3.1 {
           80  +  execsql " CREATE VIRTUAL TABLE t1 USING csv('$test1csv') "
           81  +} {}
           82  +do_test csv-1.3.2 {
           83  +  execsql { SELECT name FROM sqlite_master ORDER BY name }
           84  +} {t1}
           85  +do_test csv-1.3.3 {
           86  +  execsql { 
           87  +    DROP TABLE t1; 
           88  +    SELECT name FROM sqlite_master ORDER BY name;
           89  +  }
           90  +} {}
           91  +
           92  +# Test creating and dropping an csv table without a header row
           93  +# and with the custom delimiter '|'.
           94  +#
           95  +do_test csv-1.4.1 {
           96  +  execsql " CREATE VIRTUAL TABLE t1 USING csv('$test2csv', '|') "
           97  +} {}
           98  +do_test csv-1.4.2 {
           99  +  execsql { SELECT name FROM sqlite_master ORDER BY name }
          100  +} {t1}
          101  +do_test csv-1.4.3 {
          102  +  execsql { 
          103  +    DROP TABLE t1; 
          104  +    SELECT name FROM sqlite_master ORDER BY name;
          105  +  }
          106  +} {}
          107  +
          108  +#----------------------------------------------------------------------------
          109  +# Test cases csv-2.* test linear scans of csv table data. 
          110  +#
          111  +do_test csv-2.1.1 {
          112  +  execsql " CREATE VIRTUAL TABLE t1 USING csv('$test1csv', ',', USE_HEADER_ROW) "
          113  +} {}
          114  +do_test csv-2.1.2 {
          115  +  execsql { 
          116  +    SELECT * FROM t1;
          117  +  }
          118  +} {1 2 3 a b c a b c a b {c .. z} a b c,d}
          119  +do_test csv-2.1.3 {
          120  +  execsql { 
          121  +    SELECT * FROM t1 WHERE colA='a';
          122  +  }
          123  +} {a b c a b c a b {c .. z} a b c,d}
          124  +
          125  +do_test csv-2.2.1 {
          126  +  execsql " CREATE VIRTUAL TABLE t2 USING csv('$test1csv', ',') "
          127  +} {}
          128  +do_test csv-2.2.2 {
          129  +  execsql { 
          130  +    SELECT * FROM t2;
          131  +  }
          132  +} {colA colB colC 1 2 3 a b c a b c a b {c .. z} a b c,d}
          133  +do_test csv-2.2.3 {
          134  +  execsql { 
          135  +    SELECT * FROM t2 WHERE col1='a';
          136  +  }
          137  +} {a b c a b c a b {c .. z} a b c,d}
          138  +
          139  +# Test scanning with the custom delimiter '|'.
          140  +#
          141  +do_test csv-2.3.1 {
          142  +  execsql " CREATE VIRTUAL TABLE t3 USING csv('$test2csv', '|') "
          143  +} {}
          144  +do_test csv-2.3.2 {
          145  +  execsql { 
          146  +    SELECT * FROM t3;
          147  +  }
          148  +} {colA colB colC 1 2 3 a b c a b c a b {c .. z} a b c|d}
          149  +do_test csv-2.3.3 {
          150  +  execsql { 
          151  +    SELECT * FROM t3 WHERE col1='a';
          152  +  }
          153  +} {a b c a b c a b {c .. z} a b c|d}
          154  +
          155  +# Test scanning with the custom delimiter ';'.  The test file
          156  +# uses | for a delimiter, so everything should be treated as
          157  +# a single column.
          158  +#
          159  +do_test csv-2.4.1 {
          160  +  execsql " CREATE VIRTUAL TABLE t4 USING csv('$test3csv', ';') "
          161  +} {}
          162  +do_test csv-2.4.2 {
          163  +  execsql { 
          164  +    SELECT * FROM t4;
          165  +  }
          166  +} {colA|colB|colC 1|2|3 a|b|c {a|b|c .. z} a|b|c|d}
          167  +do_test csv-2.4.3 {
          168  +  execsql { 
          169  +    SELECT * FROM t4 WHERE col1 LIKE 'a%';
          170  +  }
          171  +} {a|b|c {a|b|c .. z} a|b|c|d}
          172  +
          173  +# Test rowid column.
          174  +#
          175  +do_test csv-2.5.1 {
          176  +  execsql { 
          177  +    SELECT rowid FROM t1;
          178  +  }
          179  +} {21 27 33 41 58}
          180  +do_test csv-2.5.2 {
          181  +  execsql { 
          182  +    SELECT rowid FROM t1 WHERE colA='a';
          183  +  }
          184  +} {27 33 41 58}
          185  +
          186  +# Clean-up.
          187  +#
          188  +do_test csv-2.6.1 {
          189  +  execsql { 
          190  +    DROP TABLE t1; 
          191  +    DROP TABLE t2; 
          192  +    DROP TABLE t3; 
          193  +    DROP TABLE t4; 
          194  +  }
          195  +} {}
          196  +
          197  +#----------------------------------------------------------------------------
          198  +# Test cases csv-3.* test rename operations.
          199  +#
          200  +do_test csv-3.1.1 {
          201  +  execsql " CREATE VIRTUAL TABLE t1 USING csv('$test1csv', ',', USE_HEADER_ROW) "
          202  +  execsql " CREATE VIRTUAL TABLE t2 USING csv('$test1csv', ',', USE_HEADER_ROW) "
          203  +} {}
          204  +do_test csv-3.1.2 {
          205  +  catchsql { ALTER TABLE t2 RENAME TO t1 }
          206  +} {1 {there is already another table or index with this name: t1}}
          207  +do_test csv-3.1.3 {
          208  +  execsql { 
          209  +    DROP TABLE t1; 
          210  +    ALTER TABLE t2 RENAME TO t1 
          211  +  }
          212  +} {}
          213  +do_test csv-3.1.4 {
          214  +  execsql { ALTER TABLE t1 RENAME TO t5 }
          215  +  execsql { SELECT * FROM t5 }
          216  +} {1 2 3 a b c a b c a b {c .. z} a b c,d}
          217  +do_test csv-3.1.5 {
          218  +  db close
          219  +  sqlite3 db test.db
          220  +  execsql { SELECT * FROM t5 }
          221  +} {1 2 3 a b c a b c a b {c .. z} a b c,d}
          222  +do_test csv-3.1.6 {
          223  +  execsql { ALTER TABLE t5 RENAME TO 'raisara "one"'''}
          224  +  execsql { SELECT * FROM "raisara ""one""'" }
          225  +} {1 2 3 a b c a b c a b {c .. z} a b c,d}
          226  +do_test csv-3.1.7 {
          227  +  execsql { SELECT * FROM 'raisara "one"''' }
          228  +} {1 2 3 a b c a b c a b {c .. z} a b c,d}
          229  +do_test csv-3.1.8 {
          230  +  execsql { ALTER TABLE "raisara ""one""'" RENAME TO "abc 123" }
          231  +  execsql { SELECT * FROM "abc 123" }
          232  +} {1 2 3 a b c a b c a b {c .. z} a b c,d}
          233  +do_test csv-3.1.9 {
          234  +  db close
          235  +  sqlite3 db test.db
          236  +  execsql { SELECT * FROM "abc 123" }
          237  +} {1 2 3 a b c a b c a b {c .. z} a b c,d}
          238  +
          239  +#----------------------------------------------------------------------------
          240  +# Test cases csv-4.* test CREATE errors
          241  +#
          242  +
          243  +# Test creating and dropping an csv table with a header row.
          244  +#
          245  +do_test csv-4.1.1 {
          246  +  catchsql " CREATE VIRTUAL TABLE t1 USING csv() "
          247  +} {1 {No CSV file specified}}
          248  +do_test csv-4.1.2 {
          249  +  catchsql " CREATE VIRTUAL TABLE t1 USING csv('foo') "
          250  +} {1 {Error opening CSV file: 'foo'}}
          251  +do_test csv-4.1.3 {
          252  +  catchsql " CREATE VIRTUAL TABLE t1 USING csv(foo foo) "
          253  +} {1 {Error opening CSV file: 'foo foo'}}

Added ext/csv/test1.csv.

            1  +"colA","colB","colC"
            2  +1,2,3
            3  +a,b,c
            4  +a,"b",c
            5  +"a","b","c .. z"
            6  +"a","b","c,d"

Added ext/csv/test2.csv.

            1  +"colA"|"colB"|"colC"
            2  +1|2|3
            3  +a|b|c
            4  +a|"b"|c
            5  +"a"|"b"|"c .. z"
            6  +"a"|"b"|"c|d"

Added ext/csv/test3.csv.

            1  +colA|colB|colC
            2  +1|2|3
            3  +a|b|c
            4  +a|b|c .. z
            5  +a|b|c|d

Changes to src/main.c.

    21     21   #endif
    22     22   #ifdef SQLITE_ENABLE_RTREE
    23     23   # include "rtree.h"
    24     24   #endif
    25     25   #ifdef SQLITE_ENABLE_ICU
    26     26   # include "sqliteicu.h"
    27     27   #endif
           28  +#ifdef SQLITE_ENABLE_CSV
           29  +# include "csv.h"
           30  +#endif
    28     31   
    29     32   /*
    30     33   ** The version of the library
    31     34   */
    32     35   #ifndef SQLITE_AMALGAMATION
    33     36   const char sqlite3_version[] = SQLITE_VERSION;
    34     37   #endif
................................................................................
  1710   1713   #endif
  1711   1714   
  1712   1715   #ifdef SQLITE_ENABLE_RTREE
  1713   1716     if( !db->mallocFailed && rc==SQLITE_OK){
  1714   1717       rc = sqlite3RtreeInit(db);
  1715   1718     }
  1716   1719   #endif
         1720  +
         1721  +#ifdef SQLITE_ENABLE_CSV
         1722  +  if( !db->mallocFailed && rc==SQLITE_OK){
         1723  +    rc = sqlite3CsvInit(db);
         1724  +  }
         1725  +#endif
  1717   1726   
  1718   1727     sqlite3Error(db, rc, 0);
  1719   1728   
  1720   1729     /* -DSQLITE_DEFAULT_LOCKING_MODE=1 makes EXCLUSIVE the default locking
  1721   1730     ** mode.  -DSQLITE_DEFAULT_LOCKING_MODE=0 make NORMAL the default locking
  1722   1731     ** mode.  Doing nothing at all also makes NORMAL the default.
  1723   1732     */

Changes to src/test_config.c.

   379    379   #endif
   380    380   
   381    381   #ifdef SQLITE_ENABLE_RTREE
   382    382     Tcl_SetVar2(interp, "sqlite_options", "rtree", "1", TCL_GLOBAL_ONLY);
   383    383   #else
   384    384     Tcl_SetVar2(interp, "sqlite_options", "rtree", "0", TCL_GLOBAL_ONLY);
   385    385   #endif
          386  +
          387  +#ifdef SQLITE_ENABLE_CSV
          388  +  Tcl_SetVar2(interp, "sqlite_options", "csv", "1", TCL_GLOBAL_ONLY);
          389  +#else
          390  +  Tcl_SetVar2(interp, "sqlite_options", "csv", "0", TCL_GLOBAL_ONLY);
          391  +#endif
   386    392   
   387    393   #ifdef SQLITE_OMIT_SCHEMA_PRAGMAS
   388    394     Tcl_SetVar2(interp, "sqlite_options", "schema_pragmas", "0", TCL_GLOBAL_ONLY);
   389    395   #else
   390    396     Tcl_SetVar2(interp, "sqlite_options", "schema_pragmas", "1", TCL_GLOBAL_ONLY);
   391    397   #endif
   392    398