AryaWu/sqlite

1#include "sqliteInt.h"2#include "unity.h"3#include <stdlib.h>4#include <string.h>5#include <stdio.h>6 7/* Forward decl of the function under test */8void sqlite3RegisterJsonFunctions(void);9 10/* Utility: Execute SQL returning first column of first row as int.11   Returns 0 if no row or on error (caller can assert prepare status separately if needed). */12static int exec_int(sqlite3 *db, const char *sql){13  sqlite3_stmt *stmt = 0;14  int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);15  int val = 0;16  if( rc!=SQLITE_OK ){17    return 0;18  }19  rc = sqlite3_step(stmt);20  if( rc==SQLITE_ROW ){21    val = sqlite3_column_int(stmt, 0);22  }23  sqlite3_finalize(stmt);24  return val;25}26 27/* Utility: Execute SQL returning first column of first row as newly malloc'd string.28   Returns NULL if no row or on error. Caller must sqlite3_free() the returned string. */29static char *exec_text_dup(sqlite3 *db, const char *sql){30  sqlite3_stmt *stmt = 0;31  int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);32  char *out = NULL;33  if( rc!=SQLITE_OK ){34    return NULL;35  }36  rc = sqlite3_step(stmt);37  if( rc==SQLITE_ROW ){38    const unsigned char *z = sqlite3_column_text(stmt, 0);39    if( z ){40      out = sqlite3_mprintf("%s", z);41    }42  }43  sqlite3_finalize(stmt);44  return out;45}46 47/* Utility: create a test function "subtype(x)" that returns sqlite3_value_subtype(x) */48static void subtype_sqlfunc(sqlite3_context *ctx, int argc, sqlite3_value **argv){49  (void)argc;50  unsigned int st = sqlite3_value_subtype(argv[0]);51  sqlite3_result_int(ctx, (int)st);52}53static int create_subtype_function(sqlite3 *db){54  return sqlite3_create_function(db, "subtype", 1, SQLITE_UTF8, 0,55                                 subtype_sqlfunc, 0, 0);56}57 58void setUp(void) {59  /* no-op */60}61void tearDown(void) {62  /* no-op */63}64 65/* Test: After registering JSON functions, basic JSON scalar functions exist and behave */66void test_sqlite3RegisterJsonFunctions_basics(void){67  sqlite3 *db = NULL;68  int rc;69 70  sqlite3RegisterJsonFunctions(); /* function under test */71 72  rc = sqlite3_open(":memory:", &db);73  TEST_ASSERT_EQUAL_INT_MESSAGE(SQLITE_OK, rc, "sqlite3_open failed");74  TEST_ASSERT_EQUAL_INT_MESSAGE(SQLITE_OK, create_subtype_function(db),75                                "create_subtype_function failed");76 77  /* json_valid should accept valid JSON */78  TEST_ASSERT_EQUAL_INT(1, exec_int(db, "SELECT json_valid('{\"a\":1}')"));79 80  /* json_type basic usage */81  char *t = exec_text_dup(db, "SELECT json_type('{\"a\":1}', '$.a')");82  TEST_ASSERT_NOT_NULL(t);83  TEST_ASSERT_EQUAL_STRING("integer", t);84  sqlite3_free(t);85 86  /* json_quote should return a JSON string */87  char *q = exec_text_dup(db, "SELECT json_quote('abc')");88  TEST_ASSERT_NOT_NULL(q);89  TEST_ASSERT_EQUAL_STRING("\"abc\"", q);90  sqlite3_free(q);91 92  /* typeof(json_array(...)) should be text */93  char *ty = exec_text_dup(db, "SELECT typeof(json_array(1,2,3))");94  TEST_ASSERT_NOT_NULL(ty);95  TEST_ASSERT_EQUAL_STRING("text", ty);96  sqlite3_free(ty);97 98  sqlite3_close(db);99}100 101/* Test: JSONB variants exist, return BLOB, and differ in subtype from text JSON results */102void test_sqlite3RegisterJsonFunctions_jsonb_and_subtype(void){103  sqlite3 *db = NULL;104  int rc;105 106  sqlite3RegisterJsonFunctions(); /* function under test */107 108  rc = sqlite3_open(":memory:", &db);109  TEST_ASSERT_EQUAL_INT(SQLITE_OK, rc);110  TEST_ASSERT_EQUAL_INT(SQLITE_OK, create_subtype_function(db));111 112  /* typeof of jsonb_array should be 'blob' */113  char *ty_b = exec_text_dup(db, "SELECT typeof(jsonb_array(1,2,3))");114  TEST_ASSERT_NOT_NULL(ty_b);115  TEST_ASSERT_EQUAL_STRING("blob", ty_b);116  sqlite3_free(ty_b);117 118  /* typeof of json_array should be 'text' */119  char *ty_t = exec_text_dup(db, "SELECT typeof(json_array(1,2,3))");120  TEST_ASSERT_NOT_NULL(ty_t);121  TEST_ASSERT_EQUAL_STRING("text", ty_t);122  sqlite3_free(ty_t);123 124  /* Subtype check: ensure subtypes differ between json and jsonb results */125  int st_text = exec_int(db, "SELECT subtype(json_array(1,2,3))");126  int st_blob = exec_int(db, "SELECT subtype(jsonb_array(1,2,3))");127  /* They should both be non-zero and not equal (JSON text subtype vs JSONB subtype) */128  TEST_ASSERT_NOT_EQUAL(0, st_text);129  TEST_ASSERT_NOT_EQUAL(0, st_blob);130  TEST_ASSERT_NOT_EQUAL(st_text, st_blob);131 132  sqlite3_close(db);133}134 135/* Test: Aggregate functions exist and produce expected types and usable JSON */136void test_sqlite3RegisterJsonFunctions_aggregates(void){137  sqlite3 *db = NULL;138  int rc;139 140  sqlite3RegisterJsonFunctions(); /* function under test */141 142  rc = sqlite3_open(":memory:", &db);143  TEST_ASSERT_EQUAL_INT(SQLITE_OK, rc);144 145  /* json_group_array result type is text */146  char *ty1 = exec_text_dup(db,147    "WITH t(v) AS (VALUES(1),(2),(3)) SELECT typeof(json_group_array(v)) FROM t");148  TEST_ASSERT_NOT_NULL(ty1);149  TEST_ASSERT_EQUAL_STRING("text", ty1);150  sqlite3_free(ty1);151 152  /* jsonb_group_array result type is blob */153  char *ty2 = exec_text_dup(db,154    "WITH t(v) AS (VALUES(1),(2),(3)) SELECT typeof(jsonb_group_array(v)) FROM t");155  TEST_ASSERT_NOT_NULL(ty2);156  TEST_ASSERT_EQUAL_STRING("blob", ty2);157  sqlite3_free(ty2);158 159  /* json_group_object produces valid JSON with expected keys/values */160  int ok = exec_int(db,161    "WITH t(k,v) AS (VALUES('a',1),('b',2)) "162    "SELECT json_extract(json_group_object(k,v),'$.a')=1 "163    "   AND json_extract(json_group_object(k,v),'$.b')=2");164  TEST_ASSERT_EQUAL_INT(1, ok);165 166  sqlite3_close(db);167}168 169/* Test: Operator-style functions -> and ->> exist and behave */170void test_sqlite3RegisterJsonFunctions_arrow_operators(void){171  sqlite3 *db = NULL;172  int rc;173 174  sqlite3RegisterJsonFunctions(); /* function under test */175 176  rc = sqlite3_open(":memory:", &db);177  TEST_ASSERT_EQUAL_INT(SQLITE_OK, rc);178 179  /* -> returns JSON (text) */180  char *ty1 = exec_text_dup(db,181    "SELECT typeof('{\"a\":[10,20]}'->'$.a[1]')");182  TEST_ASSERT_NOT_NULL(ty1);183  TEST_ASSERT_EQUAL_STRING("text", ty1);184  sqlite3_free(ty1);185 186  /* ->> returns SQL scalar (integer here) */187  char *ty2 = exec_text_dup(db,188    "SELECT typeof('{\"a\":[10,20]}'->>'$.a[1]')");189  TEST_ASSERT_NOT_NULL(ty2);190  TEST_ASSERT_EQUAL_STRING("integer", ty2);191  sqlite3_free(ty2);192 193  /* Value correctness for ->> */194  int v = exec_int(db, "SELECT '{\"a\":[10,20]}'->>'$.a[1]'");195  TEST_ASSERT_EQUAL_INT(20, v);196 197  sqlite3_close(db);198}199 200/* Test: Repeated registration does not break availability */201void test_sqlite3RegisterJsonFunctions_idempotent_calls(void){202  sqlite3 *db1 = NULL;203  sqlite3 *db2 = NULL;204 205  sqlite3RegisterJsonFunctions();206  sqlite3RegisterJsonFunctions(); /* call twice */207 208  TEST_ASSERT_EQUAL_INT(SQLITE_OK, sqlite3_open(":memory:", &db1));209  TEST_ASSERT_EQUAL_INT(SQLITE_OK, sqlite3_open(":memory:", &db2));210 211  /* Both connections should have the functions available */212  TEST_ASSERT_EQUAL_INT(1, exec_int(db1, "SELECT json_valid('[1,2,3]')"));213  TEST_ASSERT_EQUAL_INT(1, exec_int(db2, "SELECT json_valid('{\"x\":42}')"));214 215  sqlite3_close(db1);216  sqlite3_close(db2);217}218 219int main(void) {220  UNITY_BEGIN();221  RUN_TEST(test_sqlite3RegisterJsonFunctions_basics);222  RUN_TEST(test_sqlite3RegisterJsonFunctions_jsonb_and_subtype);223  RUN_TEST(test_sqlite3RegisterJsonFunctions_aggregates);224  RUN_TEST(test_sqlite3RegisterJsonFunctions_arrow_operators);225  RUN_TEST(test_sqlite3RegisterJsonFunctions_idempotent_calls);226  return UNITY_END();227}