Hey Brian. I had some time this morning to look at it. Try whacking this
new concat_replace into your SQLite build in either func.c or sqlite3.c
amalgam. Also remember to hook it up with a new entry after say,
group_concat, as follows.
AGGREGATE(group_replace, 3, 0, 0,
groupReplaceStep,groupReplaceFinalize),
[This is a first pass so there could be an edge case with NULL/empty that I
missed... pls test it! Honestly, the original replace() that I borrowed
from might also be a bit shaky with the NULL/empty edge cases.]
Here's a tester snippet you can play with:
sqlite> WITH T(pattern,replacement) AS (VALUES
('a','This'),('b','is'),('c','a'),('d','test')) SELECT group_replace('a b c
d',pattern,replacement) FROM T;
This is a test
---all C code below this point---
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 Mon, Aug 7, 2017 at 2:02 AM, Brian Clifford <
brian.cliff...@thinksmartbox.com> wrote:
> Thank Simon,
>
> I have looked into using a user defined function however it was very slow.
> I tried to create an expression index unfortunately I was not able as the
> function was seen as non deterministic. (I am using the C# interface and
> its not possible to define a user defined function as Determinstic from C#
> (Feature request!?))
>
> Thanks
> Brian
>
>
>
> --
>
>
> <http://www.thinksmartbox.com>
>
> www.thinksmartbox.com
>
> Facebook <https://www.facebook.com/thinksmartbox> Twitter
> <https://twitter.com/ThinkSmartbox> LinkedIn
> <https://www.linkedin.com/company/smartbox-assistive-technology> YouTube
> <https://www.youtube.com/user/SmartboxAT>
>
> Smartbox Assistive Technology, Ysobel House, Enigma Commercial Centre,
> Sandys
> Road, Malvern, WR14 1JJ
>
> Tel: +44 (0) 1684 578868
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
