SQLite "read file" virtual table example

vtab.c

// gcc -shared vtab.c -o vtab.dll -s -static
// select * from vtab('D:/1.txt')
// select line from vtab where path = 'D:/1.txt'
#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1
#include <string.h>
#include <stdio.h>
#include <stdlib.h>

#define MAX_LINE_SIZE 1024

typedef struct vtab_vtab vtab_vtab;
struct vtab_vtab {
	sqlite3_vtab base;
};

static int vtabConnect(sqlite3 *db, void *pAux, int argc, const char *const*argv, sqlite3_vtab **ppVtab, char **pzErr) {
	vtab_vtab *pNew;
	int rc = sqlite3_declare_vtab(db, "CREATE TABLE x(line text, path hidden)");
	if (rc == SQLITE_OK) {
		pNew = sqlite3_malloc(sizeof(*pNew));
		*ppVtab = (sqlite3_vtab*)pNew;
		if (pNew == 0) 
			return SQLITE_NOMEM;
			
		memset(pNew, 0, sizeof(*pNew));
	}
	
	return rc;
}

static int vtabDisconnect(sqlite3_vtab *pVtab){
	vtab_vtab *p = (vtab_vtab*)pVtab;
	sqlite3_free(p);
	return SQLITE_OK;
}

static int vtabBestIndex(sqlite3_vtab *tab, sqlite3_index_info *pIdxInfo) {
	if (!pIdxInfo->nConstraint)
		return SQLITE_RANGE;
			
	int i, j;
	int idxNum = 0;
	int unusableMask = 0;
	int nArg = 0;
	int nCol = 1; // line
	int nHid = 1; // path	
	int aIdx[nHid];

	const struct sqlite3_index_constraint *pConstraint;
	pConstraint = pIdxInfo->aConstraint;
	for (i = 0; i < nHid; i++)
		aIdx[i] = -1;

	for(i = 0; i < pIdxInfo->nConstraint; i++, pConstraint++) {
		int iCol;
		int iMask;
		if(pConstraint->iColumn < nCol) 
			continue;
		
		iCol = pConstraint->iColumn - nCol;
		iMask = 1 << iCol;
		if(pConstraint->usable==0) {
			unusableMask |=  iMask;
			continue;
		} else if (pConstraint->op == SQLITE_INDEX_CONSTRAINT_EQ) {
			idxNum |= iMask;
			aIdx[iCol] = i;
		}
	}
	
	for(i = 0; i < nHid; i++) {
		if((j = aIdx[i]) >= 0){
			pIdxInfo->aConstraintUsage[j].argvIndex = ++nArg;
			pIdxInfo->aConstraintUsage[j].omit = 0;
		}
	}
	
	if((unusableMask & ~idxNum) != 0)
		return SQLITE_CONSTRAINT;
	
	pIdxInfo->estimatedCost = (double)100;
	pIdxInfo->estimatedRows = 100;
	pIdxInfo->idxNum = idxNum;
	return SQLITE_OK;
}

typedef struct vtab_cursor vtab_cursor;
struct vtab_cursor {
	sqlite3_vtab_cursor base;  
	sqlite3_int64 iRowid;
	int isEof;
	char* path;
	FILE* file;
	char line[MAX_LINE_SIZE];
};

static int vtabOpen(sqlite3_vtab *p, sqlite3_vtab_cursor **ppCursor) {
	vtab_cursor *pCur;
	pCur = sqlite3_malloc(sizeof(*pCur));
	if (pCur == 0) 
		return SQLITE_NOMEM;
		
	memset(pCur, 0, sizeof(*pCur));
	*ppCursor = &pCur->base;
	return SQLITE_OK;
}

static int vtabClose(sqlite3_vtab_cursor *cur){
	vtab_cursor *pCur = (vtab_cursor*)cur;
	if (pCur->file) {
		fclose(pCur->file);
		free(pCur->path);
	}
	sqlite3_free(pCur);
	
	return SQLITE_OK;
}

static int vtabNext(sqlite3_vtab_cursor *cur) {
	vtab_cursor *pCur = (vtab_cursor*)cur;
	pCur->iRowid++;
	pCur->isEof = pCur->isEof || fscanf(pCur->file, "%s", pCur->line) == EOF;
	return SQLITE_OK;
}

static int vtabColumn(sqlite3_vtab_cursor *cur, sqlite3_context *ctx, int colNo){
	vtab_cursor *pCur = (vtab_cursor*)cur;
	const char* value = colNo == 0 ? pCur->line : pCur->path;
	sqlite3_result_text(ctx, (char*)value, -1, SQLITE_TRANSIENT);
	return SQLITE_OK;
}

static int vtabEof(sqlite3_vtab_cursor *cur){
	vtab_cursor *pCur = (vtab_cursor*)cur;
	return pCur->isEof;
}

static int vtabFilter(sqlite3_vtab_cursor *pVtabCursor, int idxNum, const char *idxStr, int argc, sqlite3_value **argv) {
	vtab_cursor *pCur = (vtab_cursor *)pVtabCursor;
	if (argc == 0) {
		pCur->isEof = 1;
		return SQLITE_OK;
	}
		
	if (sqlite3_value_type(argv[0]) != SQLITE_TEXT)
		return SQLITE_ERROR;	
		
	pCur->iRowid = 1;
	if (!pCur->file) {
		const char* path = sqlite3_value_text(argv[0]);
		pCur->path = malloc(sizeof(char) * (strlen(path) + 1));
		sprintf(pCur->path, path);	
		pCur->file = fopen(pCur->path, "r");
	} else {
		rewind(pCur->file);
	}
	pCur->isEof = fscanf(pCur->file, "%s", pCur->line) == EOF;

	return pCur->file ? SQLITE_OK : SQLITE_ERROR;
}

static int vtabRowid(sqlite3_vtab_cursor *cur, sqlite_int64 *pRowid) {
  vtab_cursor *pCur = (vtab_cursor*)cur;
  *pRowid = pCur->iRowid;
  return SQLITE_OK;
}

static sqlite3_module vtabModule = {
  /* iVersion    */ 0,
  /* xCreate     */ 0,
  /* xConnect    */ vtabConnect,
  /* xBestIndex  */ vtabBestIndex,
  /* xDisconnect */ vtabDisconnect,
  /* xDestroy    */ 0,
  /* xOpen       */ vtabOpen,
  /* xClose      */ vtabClose,
  /* xFilter     */ vtabFilter,
  /* xNext       */ vtabNext,
  /* xEof        */ vtabEof,
  /* xColumn     */ vtabColumn,
  /* xRowid      */ vtabRowid,
  /* xUpdate     */ 0,
  /* xBegin      */ 0,
  /* xSync       */ 0,
  /* xCommit     */ 0,
  /* xRollback   */ 0,
  /* xFindMethod */ 0,
  /* xRename     */ 0,
  /* xSavepoint  */ 0,
  /* xRelease    */ 0,
  /* xRollbackTo */ 0,
  /* xShadowName */ 0
};

#ifdef _WIN32
__declspec(dllexport)
#endif
int sqlite3_vtab_init(sqlite3 *db, char **pzErrMsg, const sqlite3_api_routines *pApi) {
	SQLITE_EXTENSION_INIT2(pApi);
	return sqlite3_create_module(db, "vtab", &vtabModule, 0);
}