Igor Tandetnik, I tried your suggestion
insert or replace into BlobLastNameTest(rowid, FieldName, Vertices)
select min(rowid), FieldName, AGGREGATEBLOB(Vertices,rowid,%d,\'%s\')
from BlobLastNameTest group by FieldName",
(The AGGREGATEBLOB C++ User Defined Function is shown at bottom of this post)
but it only concatenates a maximum of two rowids into the VERTICES BLOB for
the MIN(ROWID).
Perhaps, We need to break this into a several steps
1. CREATE TABLE TEMPRESULT(FieldName CHAR(25), PreviousFieldName CHAR(25).
MINROWID INT);
2. INSERT INTO TEMPRESULTS SELECT .................
3. C++ code NOT SQL Code
sprintf(InsertStatement"insert or replace into BlobLastNameTest(rowid,
FieldName, Vertices)
select min(rowid), FieldName,
AGGREGATEBLOB(X.Vertices,X.rowid,%d,\'%s\',X.FIELDNAME,Y.PREVIOUSFIELDNAME)
from BlobLastNameTest X, TEMPRESULTS Y WHERE X.FIELDNAME = Y.FIELDNAME group by
X.FieldName", ........
In this way when the FieldName changes , we could possibly refresh the Standard
Template Library Vector Column3 which stores the rowid's which are embedded in
the VERTICES BLOB for each unique fieldname Thank you for your help.
void cIntersectingGroupCache::AGGREGATEBLOBFunc(sqlite3_context *context, int
argc, sqlite3_value **argv){
char* TableName;
int size;
int* ip2;
long long int iVal;
((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))->Column3.clear();
switch( sqlite3_value_type(argv[0]) ){
case SQLITE_INTEGER: {
iVal = sqlite3_value_int64(argv[0]);
iVal = ( iVal > 0) ? 1: ( iVal < 0 ) ? -1: 0;
sqlite3_result_int64(context, iVal);
break;
}
case SQLITE_NULL: {
size = sqlite3_value_bytes(argv[3]);
TableName = new char[size + 1];
memcpy(TableName, sqlite3_value_text(argv[3]),size);
TableName[size] = '\x0';
((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))->RowCountMap[TableName]
+= 1;
delete [] TableName;
break;
}
case SQLITE_BLOB: {
size = sqlite3_value_bytes(argv[0]);
ip2 = (int *)sqlite3_value_blob(argv[0]);
for (int i = 0; i < size/sizeof(int); i++){
((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))->Column3.push_back(ip2[i]);
}
break;
}
default: {
break;
}
}
switch( sqlite3_value_type(argv[1]) ){
case SQLITE_INTEGER: {
int iVal = sqlite3_value_int(argv[1]);
((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))->Column3.push_back(iVal);
char* blob =
reinterpret_cast<char*>(&(((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))->Column3[0]));
sqlite3_result_blob(context,blob,((cIntersectingGroupCache*)(sqlite3_value_int(argv[2])))->Column3.size()*sizeof(int),NULL);
break;
}
default: {
break;
}
}
}
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users