Scott. I almost forgot about this example. Consider group_concat's
orthogonal function group_replace.
I adapted this from SQLite replace and group_concat. The author disclaims
all rights to the following code:
---------------
struct StrRepl {
const unsigned char* zStr;
u32 nStr;
u8 replError; /* STRREPL_NOMEM or STRREPL_TOOBIG */
};
typedef struct StrRepl StrRepl;
#define STRREPL_NOMEM 1
#define STRREPL_TOOBIG 2
/*
** Aggregate group_replace(A,B,C) result string is derived from A by
replacing
** every exact match occurrence of B with C. A is presumed constant over
the group
** while B and C may vary at each step. Collating sequences are not used.
*/
static void groupReplaceStep(
sqlite3_context *context,
int argc,
sqlite3_value **argv
) {
const unsigned char *zStr; /* The input string A */
const unsigned char *zPattern; /* The pattern string B */
const unsigned char *zRepl; /* The replacement string C */
unsigned char *zOut; /* The output */
int nStr; /* Size of zStr */
int nPattern; /* Size of zPattern */
int nRepl; /* Size of zRep */
i64 nOut; /* Maximum size of zOut */
int loopLimit; /* Last zStr[] that might match zPattern[] */
int i, j; /* Loop counters */
assert(argc == 3);
UNUSED_PARAMETER(argc);
zStr = sqlite3_value_text(argv[0]);
if (zStr == 0) return;
nStr = sqlite3_value_bytes(argv[0]);
assert(zStr == sqlite3_value_text(argv[0])); /* No encoding change */
zPattern = sqlite3_value_text(argv[1]);
if (zPattern == 0) {
assert(sqlite3_value_type(argv[1]) == SQLITE_NULL
|| sqlite3_context_db_handle(context)->mallocFailed);
return;
}
if (zPattern[0] == 0) {
assert(sqlite3_value_type(argv[1]) != SQLITE_NULL);
sqlite3_result_value(context, argv[0]);
return;
}
nPattern = sqlite3_value_bytes(argv[1]);
assert(zPattern == sqlite3_value_text(argv[1])); /* No encoding change */
zRepl = sqlite3_value_text(argv[2]);
if (zRepl == 0) return;
nRepl = sqlite3_value_bytes(argv[2]);
assert(zRepl == sqlite3_value_text(argv[2]));
StrRepl* pStrRepl = (StrRepl*) sqlite3_aggregate_context(context, sizeof
(StrRepl));
if (pStrRepl) {
if (pStrRepl->replError) return;
if (!pStrRepl->zStr) {
if (sqlite3_value_type(argv[0]) == SQLITE_NULL
|| sqlite3_value_type(argv[1]) == SQLITE_NULL) return;
pStrRepl->nStr = sqlite3_value_bytes(argv[0]);
pStrRepl->zStr = contextMalloc(context,pStrRepl->nStr);
memcpy((void*)pStrRepl->zStr,sqlite3_value_text(argv[0]),pStrRepl->nStr);
/* No encoding change */
}
zPattern = sqlite3_value_text(argv[1]);
nPattern = sqlite3_value_bytes(argv[1]);
assert(zPattern == sqlite3_value_text(argv[1])); /* No encoding change
*/
if (0 == pStrRepl->nStr || 0 == nPattern) return;
zRepl = sqlite3_value_text(argv[2]);
nRepl = sqlite3_value_bytes(argv[2]);
if (0 == zRepl) return;
nOut = pStrRepl->nStr + 1;
assert(nOut < SQLITE_MAX_LENGTH);
zOut = contextMalloc(context, (i64) nOut);
if (0 == zOut) return;
loopLimit = pStrRepl->nStr - nPattern;
for (i = j = 0; i <= loopLimit; i++) {
if (pStrRepl->zStr[i] != zPattern[0] || memcmp(&(pStrRepl->zStr)[i],
zPattern, nPattern)) {
zOut[j++] = pStrRepl->zStr[i];
} else {
u8 *zOld;
sqlite3 *db = sqlite3_context_db_handle(context);
nOut += nRepl - nPattern;
testcase(nOut - 1 == db->aLimit[SQLITE_LIMIT_LENGTH]);
testcase(nOut - 2 == db->aLimit[SQLITE_LIMIT_LENGTH]);
if (nOut - 1 > db->aLimit[SQLITE_LIMIT_LENGTH]) {
pStrRepl->replError = STRREPL_TOOBIG;
sqlite3_free(zOut);
return;
}
zOld = zOut;
zOut = sqlite3_realloc64(zOut, (int) nOut);
if (zOut == 0) {
pStrRepl->replError = STRREPL_NOMEM;
sqlite3_free(zOld);
return;
}
memcpy(&zOut[j], zRepl, nRepl);
j += nRepl;
i += nPattern - 1;
}
}
assert(j + pStrRepl->nStr - i + 1 == nOut);
memcpy(&zOut[j], &pStrRepl->zStr[i], pStrRepl->nStr - i);
j += pStrRepl->nStr - i;
assert(j <= nOut);
zOut[j] = 0;
void* pFree = (void*)pStrRepl->zStr;
pStrRepl->zStr = zOut;
pStrRepl->nStr = nOut;
sqlite3_free(pFree);
}
}
static void groupReplaceFinalize(sqlite3_context *context) {
const char **pzVal;
StrRepl* pStrRepl = sqlite3_aggregate_context(context, 0);
if (pStrRepl) {
if (pStrRepl->replError == STRREPL_TOOBIG) {
sqlite3_result_error_toobig(context);
} else if (pStrRepl->replError == STRREPL_NOMEM) {
sqlite3_result_error_nomem(context);
} else {
sqlite3_result_text(context, pStrRepl->zStr, -1, sqlite3_free);
}
}
}
On Tue, Jan 2, 2018 at 4:57 PM, Scott Robison <[email protected]>
wrote:
> On Tue, Jan 2, 2018 at 5:46 PM, petern <[email protected]>
> wrote:
> > Hi Scott.
> >
> >>Are there other aggregate functions that take multiple arguments?
> >
> > Absolutely. I've got a few in my code which deserialize table rows into
> > runtime objects. Fortunately, the DISTINCT filter makes no sense in that
> > use case, so I didn't bump into this issue myself.
>
> Thanks for the info. In doing some quick searches, I found multiple
> descriptions of "generic SQL" aggregates that gave a syntax of
> "aggregate([DISTINCT|ALL] expression)", which led me to assume that
> maybe the standard only allows that syntax with a single expression,
> not an expression list. I say maybe because the examples I found were
> clearly not the standard.
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
