Skip to content

Commit 36ef2d6

Browse files
lws-teamlws-team
committed
lws_struct: update and upsert
Add lws_struct_sq3_update() and lws_struct_sq3_upsert() apis Co-developed-by: Gemini 2.5 Pro
1 parent d211c4b commit 36ef2d6

File tree

2 files changed

+196
-10
lines changed

2 files changed

+196
-10
lines changed

‎include/libwebsockets/lws-struct.h‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -267,6 +267,16 @@ LWS_VISIBLE LWS_EXTERN int
267267
lws_struct_sq3_serialize(sqlite3 *pdb, const lws_struct_map_t *schema,
268268
lws_dll2_owner_t *owner, uint32_t manual_idx);
269269

270+
LWS_VISIBLE LWS_EXTERN int
271+
lws_struct_sq3_update(sqlite3 *pdb, const char *table,
272+
const lws_struct_map_t *map, size_t map_entries, const void *data,
273+
const char *where_col);
274+
275+
LWS_VISIBLE LWS_EXTERN int
276+
lws_struct_sq3_upsert(sqlite3 *pdb, const char *table,
277+
const lws_struct_map_t *map, size_t map_entries, const void *data,
278+
const char *where_col);
279+
270280
LWS_VISIBLE LWS_EXTERN int
271281
lws_struct_sq3_deserialize(sqlite3 *pdb, const char *filter, const char *order,
272282
const lws_struct_map_t *schema, lws_dll2_owner_t *o,

‎lib/misc/lws-struct-sqlite.c‎

Lines changed: 186 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -210,15 +210,13 @@ lws_struct_sq3_deserialize(sqlite3 *pdb, const char *filter, const char *order,
210210
n + 1 == (int)schema->child_map_size ? ' ' : ',');
211211

212212
where[0] = '\0';
213-
lws_snprintf(where, sizeof(where), " where _lws_idx >= %llu %s",
214-
(unsigned long long)start, filter ? filter : "");
213+
if (filter)
214+
lws_snprintf(where, sizeof(where), " WHERE 1=1 %s", filter);
215215

216216
lws_snprintf(s, sizeof(s) - 1, "select %s "
217-
"from %s %s order by %s %slimit %d;", results,
217+
"from %s %s order by %s %slimit %d OFFSET %d;", results,
218218
schema->colname, where, order,
219-
_limit < 0 ? "desc " : "", limit);
220-
221-
219+
_limit < 0 ? "desc " : "", limit, start);
222220

223221
if (sqlite3_exec(pdb, s, lws_struct_sq3_deser_cb, &a, NULL) != SQLITE_OK) {
224222
lwsl_err("%s: %s: fail %s\n", __func__, sqlite3_errmsg(pdb), s);
@@ -431,6 +429,42 @@ _lws_struct_sq3_ser_one(sqlite3 *pdb, const lws_struct_map_t *schema,
431429
return 0;
432430
}
433431

432+
/*
433+
* Serializes a single C member into its escaped SQL value representation.
434+
* Eg, a string "it's" becomes "'it''s'". An integer 123 becomes "123".
435+
*/
436+
static void
437+
ls_sq3_serialize_col(const void *memb, const lws_struct_map_t *map,
438+
char **p, char *end)
439+
{
440+
char puri[1024];
441+
uint64_t uu64;
442+
size_t q;
443+
444+
switch(map->type) {
445+
case LSMT_SIGNED:
446+
case LSMT_UNSIGNED:
447+
case LSMT_BOOLEAN:
448+
uu64 = 0;
449+
for (q = 0; q < map->aux; q++)
450+
uu64 |= ((uint64_t)((const uint8_t *)memb)[q] << (q << 3));
451+
452+
if (map->type == LSMT_SIGNED)
453+
*p += lws_snprintf(*p, lws_ptr_diff_size_t(end, *p), "%lld", (long long)(int64_t)uu64);
454+
else
455+
*p += lws_snprintf(*p, lws_ptr_diff_size_t(end, *p), "%llu", (unsigned long long)uu64);
456+
break;
457+
case LSMT_STRING_CHAR_ARRAY:
458+
*p += lws_snprintf(*p, lws_ptr_diff_size_t(end, *p), "'%s'", lws_sql_purify(puri, memb, sizeof(puri)));
459+
break;
460+
case LSMT_STRING_PTR:
461+
*p += lws_snprintf(*p, lws_ptr_diff_size_t(end, *p), "'%s'", lws_sql_purify(puri, *(const char * const *)memb, sizeof(puri)));
462+
break;
463+
default:
464+
break;
465+
}
466+
}
467+
434468
int
435469
lws_struct_sq3_serialize(sqlite3 *pdb, const lws_struct_map_t *schema,
436470
lws_dll2_owner_t *owner, uint32_t manual_idx)
@@ -447,15 +481,150 @@ lws_struct_sq3_serialize(sqlite3 *pdb, const lws_struct_map_t *schema,
447481
return 0;
448482
}
449483

484+
/*
485+
* UPDATE ... SET ... WHERE
486+
*/
487+
488+
int
489+
lws_struct_sq3_update(sqlite3 *pdb, const char *table,
490+
const lws_struct_map_t *map, size_t map_entries, const void *data,
491+
const char *where_col)
492+
{
493+
char *q, *p, *end, subsequent = 0;
494+
size_t i;
495+
496+
q = malloc(4096);
497+
if (!q)
498+
return 1;
499+
500+
p = q;
501+
end = q + 4096;
502+
503+
p += lws_snprintf(p, lws_ptr_diff_size_t(end, p), "UPDATE %s SET ", table);
504+
505+
for (i = 0; i < map_entries; i++) {
506+
const void *memb = (const uint8_t *)data + map[i].ofs;
507+
508+
/* Don't try to UPDATE the primary key or the WHERE column itself */
509+
if ((i == 0 && map[i].type == LSMT_UNSIGNED) ||
510+
!strcmp(map[i].colname, where_col) ||
511+
map[i].type == LSMT_BLOB_PTR)
512+
continue;
513+
514+
if (subsequent)
515+
p += lws_snprintf(p, lws_ptr_diff_size_t(end, p), ",");
516+
subsequent = 1;
517+
518+
p += lws_snprintf(p, lws_ptr_diff_size_t(end, p), "%s=", map[i].colname);
519+
ls_sq3_serialize_col(memb, &map[i], &p, end);
520+
}
521+
522+
p += lws_snprintf(p, lws_ptr_diff_size_t(end, p), " WHERE %s=", where_col);
523+
524+
for (i = 0; i < map_entries; i++) {
525+
if (!strcmp(map[i].colname, where_col)) {
526+
const void *memb = (const uint8_t *)data + map[i].ofs;
527+
ls_sq3_serialize_col(memb, &map[i], &p, end);
528+
break;
529+
}
530+
}
531+
532+
if (sqlite3_exec(pdb, q, NULL, NULL, NULL) != SQLITE_OK) {
533+
lwsl_warn("UPDATE failed: %s: %s\n", q, sqlite3_errmsg(pdb));
534+
free(q);
535+
return 1;
536+
}
537+
538+
free(q);
539+
540+
return sqlite3_changes(pdb) == 0;
541+
}
542+
543+
544+
/*
545+
* INSERT ... ON CONFLICT DO UPDATE
546+
*/
547+
548+
int
549+
lws_struct_sq3_upsert(sqlite3 *pdb, const char *table,
550+
const lws_struct_map_t *map, size_t map_entries, const void *data,
551+
const char *where_col)
552+
{
553+
char *q, *p, *end, subsequent = 0;
554+
size_t i;
555+
556+
q = malloc(8192);
557+
if (!q)
558+
return 1;
559+
560+
p = q;
561+
end = q + 8192;
562+
563+
p += lws_snprintf(p, lws_ptr_diff_size_t(end, p), "INSERT INTO %s (", table);
564+
565+
for (i = 0; i < map_entries; i++) {
566+
/* Skip the primary key in the column list for INSERT */
567+
if ((i == 0 && map[i].type == LSMT_UNSIGNED) ||
568+
map[i].type == LSMT_BLOB_PTR)
569+
continue;
570+
if (subsequent)
571+
p += lws_snprintf(p, lws_ptr_diff_size_t(end, p), ",");
572+
subsequent = 1;
573+
p += lws_snprintf(p, lws_ptr_diff_size_t(end, p), "%s", map[i].colname);
574+
}
575+
576+
p += lws_snprintf(p, lws_ptr_diff_size_t(end, p), ") VALUES (");
577+
subsequent = 0;
578+
579+
/* Second pass for values, skipping the primary key */
580+
for (i = 0; i < map_entries; i++) {
581+
/* Skip the primary key in the value list for INSERT */
582+
if ((i == 0 && map[i].type == LSMT_UNSIGNED) ||
583+
map[i].type == LSMT_BLOB_PTR)
584+
continue;
585+
if (subsequent)
586+
p += lws_snprintf(p, lws_ptr_diff_size_t(end, p), ",");
587+
subsequent = 1;
588+
ls_sq3_serialize_col((const uint8_t *)data + map[i].ofs, &map[i], &p, end);
589+
}
590+
591+
p += lws_snprintf(p, lws_ptr_diff_size_t(end, p), ") ON CONFLICT(%s) DO UPDATE SET ", where_col);
592+
subsequent = 0;
593+
594+
/* Third pass for the UPDATE SET part, skipping the PK and the where_col */
595+
for (i = 0; i < map_entries; i++) {
596+
if ((i == 0 && map[i].type == LSMT_UNSIGNED) || /* Skip PK */
597+
!strcmp(map[i].colname, where_col))
598+
continue;
599+
if (subsequent)
600+
p += lws_snprintf(p, lws_ptr_diff_size_t(end, p), ",");
601+
subsequent = 1;
602+
p += lws_snprintf(p, lws_ptr_diff_size_t(end, p), "%s=", map[i].colname);
603+
ls_sq3_serialize_col((const uint8_t *)data + map[i].ofs, &map[i], &p, end);
604+
}
605+
606+
if (sqlite3_exec(pdb, q, NULL, NULL, NULL) != SQLITE_OK) {
607+
lwsl_warn("UPSERT failed: %s: %s\n", q, sqlite3_errmsg(pdb));
608+
free(q);
609+
return 1;
610+
}
611+
612+
free(q);
613+
614+
return 0;
615+
}
616+
450617
int
451618
lws_struct_sq3_create_table(sqlite3 *pdb, const lws_struct_map_t *schema)
452619
{
453620
const lws_struct_map_t *map = schema->child_map;
454621
int map_size = (int)(ssize_t)schema->child_map_size, subsequent = 0;
455622
char s[2048], *p = s, *end = &s[sizeof(s) - 1],
456623
*pri = " primary key autoincrement", *use;
624+
int pk_done = 0;
457625

458626
p += lws_snprintf(p, (unsigned int)lws_ptr_diff(end, p),
627+
/* The _lws_idx is for ordering, not a real key */
459628
"create table if not exists %s (_lws_idx integer, ",
460629
schema->colname);
461630

@@ -475,18 +644,18 @@ lws_struct_sq3_create_table(sqlite3 *pdb, const lws_struct_map_t *schema)
475644

476645
} else {
477646
if (map->type < LSMT_STRING_CHAR_ARRAY) {
647+
/* This is an integer-type column */
478648
use = "";
479-
if (map->colname[0] != '_') /* _lws_idx is not primary key */
649+
if (!pk_done) {
480650
use = pri;
651+
pk_done = 1;
652+
}
481653
p += lws_snprintf(p, (unsigned int)lws_ptr_diff(end, p), "%s integer%s",
482654
map->colname, use);
483-
if (map->colname[0] != '_')
484-
pri = "";
485655
} else
486656
p += lws_snprintf(p, (unsigned int)lws_ptr_diff(end, p), "%s varchar",
487657
map->colname);
488658
}
489-
490659
map++;
491660
}
492661

@@ -498,6 +667,13 @@ lws_struct_sq3_create_table(sqlite3 *pdb, const lws_struct_map_t *schema)
498667
return -1;
499668
}
500669

670+
/* Ensure the UNIQUE constraint for upserts exists */
671+
p = s;
672+
p += lws_snprintf(p, (unsigned int)lws_ptr_diff(end, p),
673+
"CREATE UNIQUE INDEX IF NOT EXISTS name_idx ON %s (name)",
674+
schema->colname);
675+
sqlite3_exec(pdb, s, NULL, NULL, NULL);
676+
501677
return 0;
502678
}
503679

0 commit comments

Comments
 (0)