Hi Simon,
I recently wrote a tool to convert an arbitrary SQLite result set to properly
typed json key/value pairs, using the SQLite type affinity of the objects.
Though the code is in C++. But it gives an idea how simple this is when with a
JSON library (I'm using RapidJson). Rapidjson can also be used in buffered
mode, so it's also easily possible to write out large tables of any size.
The code for the SQLite to Json exporter is below. For an importer using
Rapidjson, I can imagine this can be done fairly simple and efficiently (SAX
parsing mode) with a custom "filter" handler. For example, it should be
possible to keep track of when an object begins and ends, collect all values as
variant values with json type info, and write out each complete object with
proper types and using a prepared statement.
Regards
Ben
---- SQLite to Json converter class ----
#include "sqlite_to_json.hpp"
#include "rapidjson/filereadstream.h"
#include "rapidjson/stringbuffer.h"
#include "rapidjson/writer.h"
#include "rapidjson/document.h"
#include <stdio.h>
#include <iostream>
#include <string>
#include <vector>
extern "C" {
#include "sqlite3.h"
}
using namespace hdm::sqlitejson;
using namespace rapidjson;
bool SqliteToJson::sqliteQueryToJson(std::string dbPath, std::string sql,
std::string jsonFile) {
// open the sqlite db at dbPath
if (!dbPath.length()) {
std::cout << "No db path provided\n";
return false;
}
sqlite3 *db = NULL;
int error = sqlite3_open_v2(dbPath.c_str(), &db, SQLITE_OPEN_READONLY,
NULL);
if (error != SQLITE_OK) {
std::cout << "Failed to open db at path" << dbPath << "\n";
return false;
}
// prepare the sql statement
sqlite3_stmt* readStmt = NULL;
error = sqlite3_prepare_v2(db, sql.c_str(), -1, &readStmt, NULL);
if (error != SQLITE_OK) {
const char *errMsg = sqlite3_errmsg(db);
std::cout << "SQL error: " << errMsg << "\n";
return false;
}
// get the column names
std::vector<std::string> columnNames;
int count = sqlite3_column_count(readStmt);
for (int i=0; i<count; i++) {
const char *colName = sqlite3_column_name(readStmt, i);
if (colName) {
std::string colStr(colName);
columnNames.push_back(colName);
}
}
rapidjson::GenericStringBuffer<rapidjson::UTF8< > > jsonBuffer;
rapidjson::Writer<rapidjson::StringBuffer> jsonWriter(jsonBuffer);
jsonWriter.StartArray();
int numRecs = 0;
int rc;
while ((rc = sqlite3_step(readStmt)) == SQLITE_ROW) {
numRecs++;
jsonWriter.StartObject();
for (int colIdx=0; colIdx<columnNames.size(); colIdx++) {
std::string columnName = columnNames[colIdx];
jsonWriter.String(columnName.c_str());
// write the column according to data affinity
sqlite3_value *val = sqlite3_column_value(readStmt, colIdx);
switch (int type = sqlite3_value_type(val)) {
case SQLITE_INTEGER: {
int64_t val = sqlite3_column_int64(readStmt, colIdx);
jsonWriter.Int64(val);
break;
}
case SQLITE_TEXT: {
const unsigned char *val = sqlite3_column_text(readStmt,
colIdx);
if (val) {
jsonWriter.String((const char*)val);
}
else {
jsonWriter.Null();
}
break;
}
case SQLITE_FLOAT: {
double val = sqlite3_column_double(readStmt, colIdx);
jsonWriter.Double(val);
break;
}
case SQLITE_BLOB: {
jsonWriter.String("<blob>");
break;
}
case SQLITE_NULL: {
jsonWriter.Null();
break;
}
default: {
assert(false);
}
}
}
jsonWriter.EndObject();
}
jsonWriter.EndArray();
// write to json
FILE* file = fopen(jsonFile.c_str(), "wt");
if (!file) {
std::cout << "Failed to write to file " << jsonFile.c_str() <<
std::endl;
return false;
}
std::cout << "Wrote " << numRecs << " records to file" << jsonFile.c_str()
<< std::endl;
fputs(jsonBuffer.GetString(), file);
fclose(file);
return true;
}
Am 21.01.18, 06:55 schrieb "sqlite-users im Auftrag von Simon Slavin"
<sqlite-users-boun...@mailinglists.sqlite.org im Auftrag von
slav...@bigfraud.org>:
Feature request for the Shell Tool: ".mode json".
Output should be as a JSON array of objects, with one object for each row
of the table. Output should start with the "[" character and end with "]".
Rows should be separated with ",\n". Quotes in strings should be escaped for
JSON, with a leading backslash. NULL should be supported as the four
lower-case characters "null", ignoring ".nullvalue".
The above setting should also affect the ".import filename [table]" command
as described in section 8 of <https://sqlite.org/cli.html> . Columns should be
created as necessary. Signed zeros should be imported as zero.
The above facilities should be implemented whether or not
DSQLITE_ENABLE_JSON1 was enabled when compiling the shell tool. They are for
export and import, not for handling JSON within SQLite.
Implementation questions:
I am not sure what the program should do if asked to import a value which
is an array or object. Perhaps, for compatibility with the JSON1 extension,
those should be imported as a string.
I am not sure whether the program should respect the settings for
".separator" for JSON mode, either for output or .import.
I am not sure how BLOBs should be handled, either for output or .import.
Simon.
_______________________________________________
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
