1  #include "api.h"
  2  
  3  #include <sqlite.h>
  4  #include <AsyncJson.h>
  5  #include <ArduinoJson.h>
  6  
  7  namespace {
  8  
  9  struct QueryContext {
 10    JsonArray columns;
 11    JsonArray rows;
 12    bool first_row;
 13  };
 14  
 15  int collect_row(void *ctx, int cols, char **values, char **names) {
 16    auto *qc = static_cast<QueryContext *>(ctx);
 17    if (qc->first_row) {
 18      for (int i = 0; i < cols; i++)
 19        qc->columns.add(names[i]);
 20      qc->first_row = false;
 21    }
 22    JsonArray row = qc->rows.add<JsonArray>();
 23    for (int i = 0; i < cols; i++)
 24      row.add(values[i] ? (const char *)values[i] : nullptr);
 25    return 0;
 26  }
 27  
 28  void handle_status(AsyncWebServerRequest *request) {
 29    AsyncJsonResponse *response = new AsyncJsonResponse();
 30    JsonObject root = response->getRoot().to<JsonObject>();
 31    root["ok"] = true;
 32    JsonObject data = root["data"].to<JsonObject>();
 33    data["open"] = programs::sqlite::isOpen();
 34    data["path"] = programs::sqlite::currentPath();
 35    if (programs::sqlite::lastErrorCode() != SQLITE_OK) {
 36      data["last_error_code"] = programs::sqlite::lastErrorCode();
 37      data["last_error"] = programs::sqlite::lastError();
 38    }
 39    data["sqlite_memory_used"] = (long long)programs::sqlite::memoryUsed();
 40    data["sqlite_memory_highwater"] = (long long)programs::sqlite::memoryHighwater();
 41    response->setLength();
 42    request->send(response);
 43  }
 44  
 45  void handle_tables(AsyncWebServerRequest *request) {
 46    if (!programs::sqlite::isOpen()) {
 47      request->send(400, asyncsrv::T_application_json, "{\"ok\":false,\"error\":\"not open\"}");
 48      return;
 49    }
 50  
 51    AsyncJsonResponse *response = new AsyncJsonResponse();
 52    JsonObject root = response->getRoot().to<JsonObject>();
 53    JsonArray tables = root["data"].to<JsonArray>();
 54  
 55    struct TablesCtx { JsonArray *arr; };
 56    TablesCtx ctx = {&tables};
 57  
 58    char *err = nullptr;
 59    int rc = sqlite3_exec(programs::sqlite::handle(),
 60        "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;",
 61        [](void *ctx, int cols, char **values, char **) -> int {
 62          auto *tc = static_cast<TablesCtx *>(ctx);
 63          if (cols > 0 && values[0]) tc->arr->add(values[0]);
 64          return 0;
 65        }, &ctx, &err);
 66  
 67    root["ok"] = (rc == SQLITE_OK);
 68    if (rc != SQLITE_OK) {
 69      root["error"] = err ? err : "unknown";
 70      sqlite3_free(err);
 71    }
 72    response->setLength();
 73    request->send(response);
 74  }
 75  
 76  }
 77  
 78  void services::http::api::database::registerRoutes(AsyncWebServer &server) {
 79    server.on("/api/database/status", HTTP_GET, handle_status);
 80    server.on("/api/database/tables", HTTP_GET, handle_tables);
 81  
 82    server.on("/api/database/close", HTTP_POST,
 83        [](AsyncWebServerRequest *request) {
 84      programs::sqlite::close();
 85      request->send(200, asyncsrv::T_application_json, "{\"ok\":true}");
 86    });
 87  
 88    AsyncCallbackJsonWebHandler &open_handler =
 89        server.on("/api/database/open", HTTP_POST,
 90            [](AsyncWebServerRequest *request, JsonVariant &json) {
 91      JsonObject body = json.as<JsonObject>();
 92      const char *path = body["path"] | (const char *)nullptr;
 93      bool ok = programs::sqlite::open(path);
 94  
 95      AsyncJsonResponse *response = new AsyncJsonResponse();
 96      JsonObject root = response->getRoot().to<JsonObject>();
 97      root["ok"] = ok;
 98      if (ok) {
 99        root["path"] = programs::sqlite::currentPath();
100      } else {
101        root["error"] = programs::sqlite::lastError();
102      }
103      response->setLength();
104      request->send(response);
105    });
106    open_handler.setMaxContentLength(256);
107  
108    AsyncCallbackJsonWebHandler &exec_handler =
109        server.on("/api/database/exec", HTTP_POST,
110            [](AsyncWebServerRequest *request, JsonVariant &json) {
111      if (!programs::sqlite::isOpen()) {
112        request->send(400, asyncsrv::T_application_json, "{\"ok\":false,\"error\":\"not open\"}");
113        return;
114      }
115  
116      JsonObject body = json.as<JsonObject>();
117      const char *sql = body["sql"] | (const char *)nullptr;
118      if (!sql || sql[0] == '\0') {
119        request->send(400, asyncsrv::T_application_json, "{\"ok\":false,\"error\":\"missing sql\"}");
120        return;
121      }
122  
123      AsyncJsonResponse *response = new AsyncJsonResponse();
124      JsonObject root = response->getRoot().to<JsonObject>();
125      JsonObject data = root["data"].to<JsonObject>();
126      JsonArray columns = data["columns"].to<JsonArray>();
127      JsonArray rows = data["rows"].to<JsonArray>();
128  
129      QueryContext qc = {columns, rows, true};
130      char *err = nullptr;
131      unsigned long start = micros();
132      int rc = sqlite3_exec(programs::sqlite::handle(), sql, collect_row, &qc, &err);
133      unsigned long elapsed = micros() - start;
134  
135      root["ok"] = (rc == SQLITE_OK);
136      data["elapsed_us"] = elapsed;
137      if (rc == SQLITE_OK) {
138        int changes = programs::sqlite::changes();
139        if (changes > 0) {
140          data["changes"] = changes;
141          data["last_insert_rowid"] = (long long)programs::sqlite::lastInsertRowid();
142        }
143      } else {
144        root["error"] = err ? err : "unknown";
145        root["error_code"] = sqlite3_extended_errcode(programs::sqlite::handle());
146        sqlite3_free(err);
147      }
148      response->setLength();
149      request->send(response);
150    });
151    exec_handler.setMaxContentLength(512);
152  }
153  
154  #ifdef PIO_UNIT_TESTING
155  
156  #include <testing/utils.h>
157  #include <storage.h>
158  #include <SD.h>
159  
160  static void test_db_collect_row_populates_columns_once(void) {
161    GIVEN("two rows are passed to collect_row");
162    THEN("column names are captured only on the first row");
163  
164    JsonDocument doc;
165    JsonArray columns = doc["columns"].to<JsonArray>();
166    JsonArray rows = doc["rows"].to<JsonArray>();
167    QueryContext qc = {columns, rows, true};
168  
169    char *names[] = {(char *)"id", (char *)"name"};
170    char *vals1[] = {(char *)"1", (char *)"alice"};
171    char *vals2[] = {(char *)"2", (char *)"bob"};
172  
173    collect_row(&qc, 2, vals1, names);
174    collect_row(&qc, 2, vals2, names);
175  
176    TEST_ASSERT_EQUAL_INT_MESSAGE(2, columns.size(),
177        "device: should have 2 column names");
178    TEST_ASSERT_EQUAL_STRING_MESSAGE("id", columns[0].as<const char *>(),
179        "device: first column should be 'id'");
180    TEST_ASSERT_EQUAL_STRING_MESSAGE("name", columns[1].as<const char *>(),
181        "device: second column should be 'name'");
182    TEST_ASSERT_EQUAL_INT_MESSAGE(2, rows.size(),
183        "device: should have 2 rows");
184    TEST_ASSERT_EQUAL_STRING("alice", rows[0][1].as<const char *>());
185    TEST_ASSERT_EQUAL_STRING("bob", rows[1][1].as<const char *>());
186  }
187  
188  static void test_db_collect_row_handles_null_values(void) {
189    WHEN("a SQL NULL value is passed to collect_row");
190    THEN("it serializes as JSON null");
191  
192    JsonDocument doc;
193    JsonArray columns = doc["columns"].to<JsonArray>();
194    JsonArray rows = doc["rows"].to<JsonArray>();
195    QueryContext qc = {columns, rows, true};
196  
197    char *names[] = {(char *)"v"};
198    char *vals[] = {nullptr};
199  
200    collect_row(&qc, 1, vals, names);
201  
202    TEST_ASSERT_EQUAL_INT(1, rows.size());
203    TEST_ASSERT_TRUE_MESSAGE(rows[0][0].isNull(),
204        "device: NULL value should serialize as JSON null");
205  }
206  
207  static void test_db_exec_roundtrip(void) {
208    GIVEN("a test database on SD");
209    WHEN("a table is created, a row inserted, and queried");
210    THEN("the results match");
211  
212    if (!programs::sqlite::open("/sd/test_db_api.db")) {
213      TEST_IGNORE_MESSAGE("skipped — could not open test database on SD");
214      return;
215    }
216    sqlite3 *h = programs::sqlite::handle();
217    char *err = nullptr;
218  
219    sqlite3_exec(h, "DROP TABLE IF EXISTS items;", nullptr, nullptr, nullptr);
220    sqlite3_exec(h, "CREATE TABLE items(id INTEGER PRIMARY KEY, label TEXT);",
221                 nullptr, nullptr, &err);
222    TEST_ASSERT_NULL(err);
223    sqlite3_exec(h, "INSERT INTO items(label) VALUES('sensor_a');",
224                 nullptr, nullptr, &err);
225    TEST_ASSERT_NULL(err);
226  
227    JsonDocument doc;
228    JsonArray columns = doc["columns"].to<JsonArray>();
229    JsonArray rows = doc["rows"].to<JsonArray>();
230    QueryContext qc = {columns, rows, true};
231  
232    int rc = sqlite3_exec(h, "SELECT id, label FROM items;", collect_row, &qc, &err);
233    TEST_ASSERT_EQUAL_INT_MESSAGE(SQLITE_OK, rc, "device: SELECT should succeed");
234    TEST_ASSERT_EQUAL_INT(2, columns.size());
235    TEST_ASSERT_EQUAL_INT(1, rows.size());
236    TEST_ASSERT_EQUAL_STRING("sensor_a", rows[0][1].as<const char *>());
237  
238    programs::sqlite::close();
239    SD.remove("/sd/test_db_api.db");
240  }
241  
242  void services::http::api::database::test(void) {
243    MODULE("DB");
244    RUN_TEST(test_db_collect_row_populates_columns_once);
245    RUN_TEST(test_db_collect_row_handles_null_values);
246    RUN_TEST(test_db_exec_roundtrip);
247  }
248  
249  #endif