Okay, I have givven up implementing direct VDBE access in a loadable extension.
Last thing I tried was to link the extension against all needed objects. Read:
> cd sqlite/src
> for f in *.c; do gcc -c "$f"; done
> cd ../..
> gcc -shared sqlite/src/*.o ext.o -o ext.so
...or something like that. But then I could not get SQLite's malloc subsystem
running from within the extension - segfaulted.
Instead of digging even deeper into SQLite, I added some lines of code to
insert.c and parse.y - diff is attached (yes, public domain).
I have added "INSERT INTO INDEX [db.]index VALUES(...)" to be able to insert
into a table's index directly, without having redundant data in table and index.
However, my code does no checking for correct values or unique constraints. it
just inserts what you throw at it :)
Accessing the data only works if the query uses the index, for example:
> CREATE TABLE t (a INTEGER, b TEXT);
> CREATE UNIQUE INDEX i ON t (a, b);
>
> INSERT INTO t VALUES (1, "a");
> INSERT INTO INDEX i VALUES (2, "b");
>
> SELECT * FROM t; -- access table only
>> 1|a
> SELECT * FROM t WHERE a > 0; -- access index only
>> 2|b
This saves me about 50% of disk space - 2GB vs. 4GB :)
Cheers
--- cvs-090731/sqlite/src/insert.c 2009-07-24 19:58:53.000000000 +0200
+++ cvs-090725-183100/sqlite/src/insert.c 2009-07-31 13:05:53.155154176
+0200
@@ -1060,6 +1060,90 @@
sqlite3DbFree(db, aRegIdx);
}
+void sqlite3InsertIntoIndex(
+ Parse *pParse, /* Parser context */
+ SrcList *pIdxName, /* Name of index into which we are inserting */
+ ExprList *pValues /* List of values to be inserted */
+){
+ sqlite3 *db;
+ Index *pIdx;
+ char *zIdx;
+ const char *zDb;
+ Vdbe *v;
+ int nColumn;
+ int iDb;
+ Db *pDb;
+ int i;
+
+ assert( pIdxName->nSrc==1 );
+
+ db = pParse->db;
+ v = sqlite3GetVdbe(pParse);
+ if( v== 0 ){
+ sqlite3ErrorMsg(pParse, "Could not init VDBE", 0);
+ goto insert_cleanup;
+ }
+
+ zIdx = pIdxName->a[0].zName;
+ //if( NEVER(zIdx==0) ) goto insert_cleanup;
+ zDb = pIdxName->a[0].zDatabase;
+
+ pIdx = sqlite3FindIndex(pParse->db, zIdx, zDb);
+ if( pIdx==0 ){
+ sqlite3ErrorMsg(pParse, "no such index: %S", pIdxName, 0);
+ goto insert_cleanup;
+ }
+
+ /* insert must cover all index fields */
+ if( pIdx->nColumn!=pValues->nExpr ){
+ sqlite3ErrorMsg(pParse, "%i values for %i columns",
pValues->nExpr, pIdx->nColumn);
+ goto insert_cleanup;
+ }
+
+ iDb = sqlite3SchemaToIndex(db, pIdx->pSchema);
+
+ sqlite3BeginWriteOperation(pParse, 0, iDb);
+
+ KeyInfo *pKey = sqlite3IndexKeyinfo(pParse, pIdx);
+
+ int iMem = ++pParse->nMem;
+ pParse->nMem += pValues->nExpr + 3; // values, rowid, record, rowcount
+
+ // open cursor 0 on index
+ sqlite3VdbeAddOp4(v, OP_OpenWrite, 0, pIdx->tnum, iDb, (char*)pKey,
P4_KEYINFO_HANDOFF);
+
+ // push values onto stack
+ for( i=0; i<pValues->nExpr; i++ ){
+ sqlite3ExprCode(pParse, pValues->a[i].pExpr, ++iMem);
+ }
+
+ // pseudo row id (zero)
+ sqlite3VdbeAddOp2(v, OP_Integer, 0, ++iMem);
+
+ // build index record
+ sqlite3VdbeAddOp3(v, OP_MakeRecord, (iMem - pValues->nExpr - 1),
pValues->nExpr+1, ++iMem);
+ sqlite3IndexAffinityStr(v, pIdx);
+
+ // insert
+ sqlite3VdbeAddOp2(v, OP_IdxInsert, 0, iMem);
+
+ // close cursor 0
+ sqlite3VdbeAddOp1(v, OP_Close, 0);
+
+ if( db->flags & SQLITE_CountRows ){
+ // set row count
+ sqlite3VdbeAddOp2(v, OP_Integer, 1, ++iMem);
+ sqlite3VdbeAddOp2(v, OP_ResultRow, iMem, 1);
+ }
+ sqlite3VdbeSetNumCols(v, 1);
+ sqlite3VdbeSetColName(v, 0, COLNAME_NAME, "rows inserted",
SQLITE_STATIC);
+
+insert_cleanup:
+
+ sqlite3SrcListDelete(db, pIdxName);
+ sqlite3ExprListDelete(db, pValues);
+}
+
/*
** Generate code to do constraint checks prior to an INSERT or an UPDATE.
**
--- cvs-090731/sqlite/src/parse.y 2009-07-03 17:37:28.000000000 +0200
+++ cvs-090725-183100/sqlite/src/parse.y 2009-07-29 18:39:43.757241718
+0200
@@ -687,6 +687,9 @@
cmd ::= insert_cmd(R) INTO fullname(X) inscollist_opt(F) DEFAULT VALUES.
{sqlite3Insert(pParse, X, 0, 0, F, R);}
+cmd ::= insert_cmd INTO INDEX fullname(X) VALUES LP itemlist(Y) RP.
+ {sqlite3InsertIntoIndex(pParse, X, Y);}
+
%type insert_cmd {int}
insert_cmd(A) ::= INSERT orconf(R). {A = R;}
insert_cmd(A) ::= REPLACE. {A = OE_Replace;}
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
