On 10/10/2012 08:14 PM, Hamish Allan wrote:
Short form question:
Working: SELECT a, userfunc(systemfunc) FROM t;
Working: SELECT a, sum(systemfunc) FROM t GROUP BY a;
Not working: SELECT a, sum(userfunc(systemfunc)) FROM t GROUP BY a;
Long form question:
I have a user-defined C function called "hits", loosely based on the
function "rankfunc" from the FTS3 example code, but returning (using
sqlite3_result_int) the total number of hits per row from the
matchinfo (code below).
As things are now, you can't use any of the FTS auxiliary functions
as arguments to an aggregate function.
The easiest workaround is probably to use a temp table to store the
unaggregated results of the FTS query.
The function is working fine; I can query with:
SELECT (rowid>> 32), hits(matchinfo(text)) FROM text WHERE content MATCH 'a';
and get output like:
1|21
1|6
1|6
1|5
1|8
1|10
1|8
1|16
1|48
(In case you're wondering what the shift is for, I'm using the high 32
bits of the rowid as a collection identifier, and the low 32 bits as a
document identifier.)
And for the query:
SELECT (rowid>> 32), SUM(LENGTH(content)) FROM text WHERE content
MATCH 'a' GROUP BY (rowid>> 32);
I get output like:
1|6507
In other words, the user-defined function is working fine, and an
aggregate query is working fine.
But if I combine them, replacing the SQL with:
SELECT (rowid>> 32), sum(hits(matchinfo(text))) FROM text WHERE
content MATCH 'a' GROUP BY (rowid>> 32);
I get a result code of 1, SQLITE_ERROR, "SQL error or missing database".
What am I doing wrong?
Thanks,
Hamish
// --------
// Query code
// const char *sql = "SELECT (rowid>> 32), hits(matchinfo(text)) FROM
text WHERE content MATCH ?"; // works
// const char *sql = "SELECT (rowid>> 32), sum(length(content)) FROM
text WHERE content MATCH ? GROUP BY (rowid>> 32)"; // works
const char *sql = "SELECT (rowid>> 32), sum(hits(matchinfo(text)))
FROM text WHERE content MATCH ? GROUP BY (rowid>> 32)"; // doesn't
work
result = sqlite3_prepare_v2(db, sql, -1,&stmt, NULL);
assert(result == SQLITE_OK);
result = sqlite3_bind_text(stmt, 1, "a", -1, SQLITE_STATIC);
assert(result == SQLITE_OK);
do
{
result = sqlite3_step(stmt);
if (result == SQLITE_ROW)
{
sqlite_int64 c0 = sqlite3_column_int64(stmt, 0);
int c1 = sqlite3_column_int(stmt, 1);
printf("%lld|%d", c0, c1);
}
}
while (result == SQLITE_ROW);
// --------
// User-defined function code
static void OLVSqliteHitsFunc(sqlite3_context *pCtx, int nVal,
sqlite3_value **apVal)
{
if (nVal != 1)
{
sqlite3_result_error(pCtx, "Wrong number of arguments to
PDFXHitsFunc", -1);
return;
}
int hits = 0;
unsigned int *blob = (unsigned int *)sqlite3_value_blob(apVal[0]);
unsigned int numberOfPhrases = blob[0];
unsigned int numberOfColumns = blob[1];
int phrase;
for (phrase = 0; phrase< numberOfPhrases; ++phrase)
{
unsigned int *phraseBlob =&blob[2 + phrase * numberOfColumns * 3];
int column;
for (column = 0; column< numberOfColumns; ++column)
hits += phraseBlob[3 * column];
}
sqlite3_result_int(pCtx, hits);
}
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
