AryaWu/sqlite

1#include "sqliteInt.h"2#include "unity.h"3#include <stdio.h>4#include <string.h>5 6static sqlite3 *gDb = NULL;7 8static sqlite3_stmt* prepare_stmt(sqlite3 *db, const char *sql){9  sqlite3_stmt *stmt = NULL;10  int rc = sqlite3_prepare_v2(db, sql, -1, &stmt, 0);11  TEST_ASSERT_EQUAL_INT_MESSAGE(SQLITE_OK, rc, "sqlite3_prepare_v2 failed");12  return stmt;13}14 15static void finalize_stmt(sqlite3_stmt *stmt){16  int rc = sqlite3_finalize(stmt);17  TEST_ASSERT_EQUAL_INT_MESSAGE(SQLITE_OK, rc, "sqlite3_finalize failed");18}19 20static double scalar_double(sqlite3 *db, const char *sql){21  sqlite3_stmt *stmt = prepare_stmt(db, sql);22  int rc = sqlite3_step(stmt);23  TEST_ASSERT_EQUAL_INT_MESSAGE(SQLITE_ROW, rc, "Expected one row");24  double v = sqlite3_column_double(stmt, 0);25  finalize_stmt(stmt);26  return v;27}28 29static sqlite3_int64 scalar_int64(sqlite3 *db, const char *sql){30  sqlite3_stmt *stmt = prepare_stmt(db, sql);31  int rc = sqlite3_step(stmt);32  TEST_ASSERT_EQUAL_INT_MESSAGE(SQLITE_ROW, rc, "Expected one row");33  sqlite3_int64 v = sqlite3_column_int64(stmt, 0);34  finalize_stmt(stmt);35  return v;36}37 38static void scalar_text(sqlite3 *db, const char *sql, char *buf, int buflen){39  sqlite3_stmt *stmt = prepare_stmt(db, sql);40  int rc = sqlite3_step(stmt);41  TEST_ASSERT_EQUAL_INT_MESSAGE(SQLITE_ROW, rc, "Expected one row");42  const unsigned char *z = sqlite3_column_text(stmt, 0);43  TEST_ASSERT_NOT_NULL_MESSAGE(z, "Expected non-NULL text result");44  snprintf(buf, buflen, "%s", z);45  finalize_stmt(stmt);46}47 48static int scalar_is_null(sqlite3 *db, const char *sql){49  sqlite3_stmt *stmt = prepare_stmt(db, sql);50  int rc = sqlite3_step(stmt);51  TEST_ASSERT_EQUAL_INT_MESSAGE(SQLITE_ROW, rc, "Expected one row");52  int isNull = (sqlite3_column_type(stmt, 0) == SQLITE_NULL);53  finalize_stmt(stmt);54  return isNull;55}56 57void setUp(void) {58  /* Ensure a fresh SQLite environment and register the date/time functions59     prior to opening a new connection so registration applies to it. */60  sqlite3_shutdown();61  TEST_ASSERT_EQUAL_INT(SQLITE_OK, sqlite3_initialize());62  sqlite3RegisterDateTimeFunctions();63  int rc = sqlite3_open(":memory:", &gDb);64  TEST_ASSERT_EQUAL_INT(SQLITE_OK, rc);65}66 67void tearDown(void) {68  if (gDb) {69    TEST_ASSERT_EQUAL_INT(SQLITE_OK, sqlite3_close(gDb));70    gDb = NULL;71  }72}73 74#ifndef SQLITE_OMIT_DATETIME_FUNCS75 76void test_sqlite3RegisterDateTimeFunctions_julianday_known_epoch(void) {77  double jd = scalar_double(gDb, "SELECT julianday('2000-01-01 00:00:00')");78  /* 2000-01-01 00:00:00 is 2451544.5 */79  TEST_ASSERT_DOUBLE_WITHIN(1e-7, 2451544.5, jd);80}81 82void test_sqlite3RegisterDateTimeFunctions_unixepoch_zero(void) {83  sqlite3_int64 ue = scalar_int64(gDb, "SELECT unixepoch('1970-01-01 00:00:00')");84  TEST_ASSERT_EQUAL_INT64(0, ue);85}86 87void test_sqlite3RegisterDateTimeFunctions_date_modifier_chain(void) {88  char buf[64];89  scalar_text(gDb, "SELECT date('2000-01-02','-1 day')", buf, sizeof(buf));90  TEST_ASSERT_EQUAL_STRING("2000-01-01", buf);91}92 93void test_sqlite3RegisterDateTimeFunctions_time_extraction(void) {94  char buf[64];95  scalar_text(gDb, "SELECT time('2020-01-01 12:34:56')", buf, sizeof(buf));96  TEST_ASSERT_EQUAL_STRING("12:34:56", buf);97}98 99void test_sqlite3RegisterDateTimeFunctions_datetime_chain(void) {100  char buf[64];101  scalar_text(gDb, "SELECT datetime('2001-01-01','+1 day','-1 second')", buf, sizeof(buf));102  TEST_ASSERT_EQUAL_STRING("2001-01-01 23:59:59", buf);103}104 105void test_sqlite3RegisterDateTimeFunctions_strftime_formatting(void) {106  char buf[64];107  scalar_text(gDb, "SELECT strftime('%Y/%m/%d %H-%M-%S','2001-02-03 04:05:06')", buf, sizeof(buf));108  TEST_ASSERT_EQUAL_STRING("2001/02/03 04-05-06", buf);109}110 111void test_sqlite3RegisterDateTimeFunctions_invalid_input_returns_null(void) {112  TEST_ASSERT_TRUE(scalar_is_null(gDb, "SELECT julianday('not-a-date')"));113  TEST_ASSERT_TRUE(scalar_is_null(gDb, "SELECT date('not-a-date')"));114  TEST_ASSERT_TRUE(scalar_is_null(gDb, "SELECT time('not-a-date')"));115  TEST_ASSERT_TRUE(scalar_is_null(gDb, "SELECT datetime('not-a-date')"));116  TEST_ASSERT_TRUE(scalar_is_null(gDb, "SELECT strftime('%s','not-a-date')"));117}118 119void test_sqlite3RegisterDateTimeFunctions_timediff_registered_and_text_result(void) {120  char buf[16];121  scalar_text(gDb, "SELECT typeof(timediff('2000-01-01 00:00:10','2000-01-01 00:00:05'))", buf, sizeof(buf));122  TEST_ASSERT_EQUAL_STRING("text", buf);123}124 125void test_sqlite3RegisterDateTimeFunctions_idempotent_registration(void) {126  /* Call registration again and verify functions still work as expected */127  sqlite3RegisterDateTimeFunctions();128 129  double jd = scalar_double(gDb, "SELECT julianday('2000-01-01 00:00:00')");130  TEST_ASSERT_DOUBLE_WITHIN(1e-7, 2451544.5, jd);131 132  sqlite3_int64 ue = scalar_int64(gDb, "SELECT unixepoch('1970-01-01 00:00:00')");133  TEST_ASSERT_EQUAL_INT64(0, ue);134 135  char buf[64];136  scalar_text(gDb, "SELECT strftime('%Y-%m-%d %H:%M:%S','2001-02-03 04:05:06')", buf, sizeof(buf));137  TEST_ASSERT_EQUAL_STRING("2001-02-03 04:05:06", buf);138}139 140void test_sqlite3RegisterDateTimeFunctions_current_functions_available(void) {141  char t1[16], t2[16], t3[16];142  scalar_text(gDb, "SELECT typeof(current_date)", t1, sizeof(t1));143  scalar_text(gDb, "SELECT typeof(current_time)", t2, sizeof(t2));144  scalar_text(gDb, "SELECT typeof(current_timestamp)", t3, sizeof(t3));145  TEST_ASSERT_EQUAL_STRING("text", t1);146  TEST_ASSERT_EQUAL_STRING("text", t2);147  TEST_ASSERT_EQUAL_STRING("text", t3);148}149 150#else /* SQLITE_OMIT_DATETIME_FUNCS defined */151 152void test_sqlite3RegisterDateTimeFunctions_current_time_format_length(void) {153  /* In this build, current_* are registered via STR_FUNCTION with fixed formats */154  sqlite3RegisterDateTimeFunctions(); /* idempotent */155  sqlite3_int64 len_time = scalar_int64(gDb, "SELECT length(current_time)");156  sqlite3_int64 len_date = scalar_int64(gDb, "SELECT length(current_date)");157  sqlite3_int64 len_ts   = scalar_int64(gDb, "SELECT length(current_timestamp)");158  TEST_ASSERT_EQUAL_INT64(8,  len_time); /* %H:%M:%S */159  TEST_ASSERT_EQUAL_INT64(10, len_date); /* %Y-%m-%d */160  TEST_ASSERT_EQUAL_INT64(19, len_ts);   /* %Y-%m-%d %H:%M:%S */161}162 163void test_sqlite3RegisterDateTimeFunctions_current_functions_text_type(void) {164  char t1[16], t2[16], t3[16];165  scalar_text(gDb, "SELECT typeof(current_date)", t1, sizeof(t1));166  scalar_text(gDb, "SELECT typeof(current_time)", t2, sizeof(t2));167  scalar_text(gDb, "SELECT typeof(current_timestamp)", t3, sizeof(t3));168  TEST_ASSERT_EQUAL_STRING("text", t1);169  TEST_ASSERT_EQUAL_STRING("text", t2);170  TEST_ASSERT_EQUAL_STRING("text", t3);171}172 173#endif174 175int main(void) {176  UNITY_BEGIN();177 178#ifndef SQLITE_OMIT_DATETIME_FUNCS179  RUN_TEST(test_sqlite3RegisterDateTimeFunctions_julianday_known_epoch);180  RUN_TEST(test_sqlite3RegisterDateTimeFunctions_unixepoch_zero);181  RUN_TEST(test_sqlite3RegisterDateTimeFunctions_date_modifier_chain);182  RUN_TEST(test_sqlite3RegisterDateTimeFunctions_time_extraction);183  RUN_TEST(test_sqlite3RegisterDateTimeFunctions_datetime_chain);184  RUN_TEST(test_sqlite3RegisterDateTimeFunctions_strftime_formatting);185  RUN_TEST(test_sqlite3RegisterDateTimeFunctions_invalid_input_returns_null);186  RUN_TEST(test_sqlite3RegisterDateTimeFunctions_timediff_registered_and_text_result);187  RUN_TEST(test_sqlite3RegisterDateTimeFunctions_idempotent_registration);188  RUN_TEST(test_sqlite3RegisterDateTimeFunctions_current_functions_available);189#else190  RUN_TEST(test_sqlite3RegisterDateTimeFunctions_current_time_format_length);191  RUN_TEST(test_sqlite3RegisterDateTimeFunctions_current_functions_text_type);192#endif193 194  return UNITY_END();195}