Feature request for the Shell Tool: .mode json

classic Classic list List threaded Threaded
18 messages Options
Reply | Threaded
Open this post in threaded view
|

Feature request for the Shell Tool: .mode json

Simon Slavin-3
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request for the Shell Tool: .mode json

J Decker
On Sat, Jan 20, 2018 at 9:54 PM, Simon Slavin <[hidden email]> wrote:

> 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".
>
Numbers should be unquoted.

NaN, Infinity are not definable through JSON.


> 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.
>
could be arrays of bytes.  A reviver could covert it to UInt8Array when
used on javascript side.  can't just use character strings; many
combinations of bytes are invalid unicode code points.
[0,1,2,255]


>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request for the Shell Tool: .mode json

Brian Curley
Is there even a need to embed it into sqlite itself? Since you're on the
shell, and in keeping with the whole 'do one thing well' mandate: pipe it
through jq instead.

Beautiful creature that jq...

Regards.

Brian P Curley



On Jan 21, 2018 9:54 AM, "J Decker" <[hidden email]> wrote:

> On Sat, Jan 20, 2018 at 9:54 PM, Simon Slavin <[hidden email]>
> wrote:
>
> > 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".
> >
> Numbers should be unquoted.
>
> NaN, Infinity are not definable through JSON.
>
>
> > 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.
> >
> could be arrays of bytes.  A reviver could covert it to UInt8Array when
> used on javascript side.  can't just use character strings; many
> combinations of bytes are invalid unicode code points.
> [0,1,2,255]
>
>
> >
> > Simon.
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request for the Shell Tool: .mode json

Luuk
On 21-01-18 16:05, Brian Curley wrote:

> Is there even a need to embed it into sqlite itself? Since you're on the
> shell, and in keeping with the whole 'do one thing well' mandate: pipe it
> through jq instead.
>
> Beautiful creature that jq...
>
> Regards.
>
> Brian P Curley
>
>
luuk@opensuse:~/tmp> echo 'select * from test;' | sqlite test.db
1
2
3
luuk@opensuse:~/tmp> echo 'select * from test;' | sqlite test.db | jq
1
2
3

Can you give an example please?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request for the Shell Tool: .mode json

Brian Curley
Well, I did oversimplify to just say 'pipe it through', but it's really
more like a sed usage.

You wouldn't see much difference if you'd pipe your delimited output
through sed or awk either, unless you threw in some directives, or a
script. It would require some planning on the part of the user, but there's
a cookbook on the jq site that covers this.


https://github.com/stedolan/jq/wiki/Cookbook#convert-a-csv-file-with-headers-to-json


There's other takes on this same recipe out there, on StackExchange, etc.

As with any such localized solution, once you get it working, you can use
it seamlessly as a function or an aliased call.

Regards.

Brian P Curley




On Jan 21, 2018 10:15 AM, "Luuk" <[hidden email]> wrote:

On 21-01-18 16:05, Brian Curley wrote:

> Is there even a need to embed it into sqlite itself? Since you're on the
> shell, and in keeping with the whole 'do one thing well' mandate: pipe it
> through jq instead.
>
> Beautiful creature that jq...
>
> Regards.
>
> Brian P Curley
>
>
luuk@opensuse:~/tmp> echo 'select * from test;' | sqlite test.db
1
2
3
luuk@opensuse:~/tmp> echo 'select * from test;' | sqlite test.db | jq
1
2
3

Can you give an example please?
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request for the Shell Tool: .mode json

Simon Slavin-3
In reply to this post by Brian Curley


On 21 Jan 2018, at 3:05pm, Brian Curley <[hidden email]> wrote:

> pipe it
> through jq instead.

I did not know jq existed.  Thanks.  Just gave the documentation a quick glance.

jq is not installed on my platform (macOS) whereas sqlite3 is.

Does jq do conversion both ways ?

Can jq deduce the column names (SQL) / keys (JSON) from the output of SELECT without extra work from the programmer/scripter ?

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request for the Shell Tool: .mode json

Luuk
In reply to this post by Brian Curley
On 21-01-18 17:15, Brian Curley wrote:

> Well, I did oversimplify to just say 'pipe it through', but it's really
> more like a sed usage.
>
> You wouldn't see much difference if you'd pipe your delimited output
> through sed or awk either, unless you threw in some directives, or a
> script. It would require some planning on the part of the user, but there's
> a cookbook on the jq site that covers this.
>
>
> https://github.com/stedolan/jq/wiki/Cookbook#convert-a-csv-file-with-headers-to-json
>
>
> There's other takes on this same recipe out there, on StackExchange, etc.
>
> As with any such localized solution, once you get it working, you can use
> it seamlessly as a function or an aliased call.
>
> Regards.
>
> Brian P Curley
>
>
>
>
> On Jan 21, 2018 10:15 AM, "Luuk" <[hidden email]> wrote:
>
> On 21-01-18 16:05, Brian Curley wrote:
>> Is there even a need to embed it into sqlite itself? Since you're on the
>> shell, and in keeping with the whole 'do one thing well' mandate: pipe it
>> through jq instead.
>>
>> Beautiful creature that jq...
>>
>> Regards.
>>
>> Brian P Curley
>>
>>
> luuk@opensuse:~/tmp> echo 'select * from test;' | sqlite test.db
> 1
> 2
> 3
> luuk@opensuse:~/tmp> echo 'select * from test;' | sqlite test.db | jq
> 1
> 2
> 3
>
> Can you give an example please?
> _______________________________________________
>

Thanks, will look at it, when i'm doing someting with JSON, and CSV
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request for the Shell Tool: .mode json

Brian Curley
In reply to this post by Simon Slavin-3
hi, Simon.

In short, yes...you can get jq to convert both ways.

It's not exactly as simple as just piping it through jq though, just to
reiterate my earlier self-correction. JSON is intended to allow rich data
definition, such that there's no quick fix that would suit all parties; in
my own case, I had to drill down to one object that had at least four
alternate formats in the same file. Itrequires some factory-style handling
to abstract and manage that deduction anyone might need individually. Same
concern would apply for any embedded .mode handling to produce JSON through
SQLite; nothing would ever be lightweight AND comprehensive for all uses.

I'd created a local function that does what I need when forcing JSON down
to @csv format through jq. It does what I need (ie, crushing "plump" JSON
output down to a table-ready format, including BLOB fields, at a specific
path level), but it'd need some tweaking to handle depth and even separator
preferences per individual use case.

Assuming some abstraction planning is done, the same can be done in either
direction.

I'd posted a link to the jq Developer's own cookbook, but the following is
a link that actually uses a similar approach where you parse the header
rows to produce a template-based JSON output:

   http://infiniteundo.com/post/99336704013/convert-csv-to-json-with-jq ​​

​Sample function for using jq to make @csv
​ to get JSON into SQLite​
: ​


​   ###########################
   #  function jq_csv ...
   #     -  This function does a quick fix of .json files for import use...
   #
function jq_csv () {

#set -x

   #  A little help here...
   if [ $# -eq 0 ] \
         || [ "${1}" == "-h" ]; then
         #  Usage: script_name [-h|-v] filename
         #
      cat <<-ENDOFHELP

    jq_csv  requires 1-2 args:

         jq_csv  [${1:-" -h | -v "}] [${2:-"filename"}]

      Examples of use:

         jq_csv  -h ...       [this help listing]
         jq_csv  -v filename  [turns on xtrace]
         jq_csv     filename  [cleans csv for loading]

ENDOFHELP

      return 0

   fi

   if [ "${1}" == "-v" ]; then
   #  Turn on xtrace...we'll kill it on our way out
      set -x
   fi

   #  There's a path length 'feature' in jq, which presents as a
segmentation error.
      #  Switch to target file's directory, run from there...
      #  ...and switch back. We could use cd -, but let's be explicit...
   typeset my_path=${PWD}
   #  Pure laziness...not going w optargs just yet.
   my_file="${2:-${1}}"
#   not_template="Error: file is not a csv-delimited document"  2>&1

   if [ -e ${my_file} ] ;then
      #  Invalid first argument...
         #  STFU
      typeset jq_path=$(dirname ${my_file} )
      #  Guard against undefined options...
      if [ -z $(echo "${1}" |egrep "\-v|\-h" ) ] \
         && [ "$(echo ${1} |cut -b 1 )" == "-" ]; then
            printf "\n    Error:  %s \n" "Invalid arg1... "
            return
      #  File exists, but...it's either zero byte or not an appropriate
file...
         #  GTFO
      elif [ -z "$(head ${my_file} |egrep '\,|\"\,|\,\"' )" ] ;then
            printf "\n    Error:  %s \n" "${not_template}"
            return
      fi
      #  Hey, look...a real file!
         #  A little gymnastics to establish our column headers...
            #  Have jq generate the keys into a CSV for us,
            #  and then take only the 2nd field onward. (Since {metadata}
isn't useful...)
         typeset my_hdrs=$(cd ${jq_path}                          \
                          && jq -rc                               \
                              '.d.results[] |keys_unsorted |@csv' \
                              $(basename ${my_file} )             \
                          |awk -F',' '!uniq[substr($0,1,length($0))]++;'
2>&1 \
                          |cut -d, -f2-                           \
                          && cd ${my_path}                        \
                           )
         #  Do NOT mess with the output here, as it produces a literal for
the upcoming call to jq.
         #
            #  Reformulate the headers into jq syntax to be sent back for
retrieval...
         typeset to_flds=$(echo "${my_hdrs}"                      \
                          |sed 's/^"/[ ./g;s/,"/, ./g;s/"//g;s/$/ |tostring
]/g; ' \
                           )
         cd ${jq_path}
         #  First the headers...then the fields.
            #  But we'll pass them through additional parsing to make them
|-delimited.
         (echo ${my_hdrs}                                \
            && jq -rc ".d.results[] |${to_flds} |@csv "  \
            $(basename ${my_file} ) 2>&1 )               \
            |sed 's/","/"|"/g;'              #\
         cd ${my_path}

   elif [ ! -e ${my_file} ]; then
      printf "\n    Error:  %s \n"  "No input file found..."
   fi

   #  Turn off debug/verbose mode...IF it was us that did it...
   if [ "${1}" == "-v" ] \
      && [ ! -z "$(set |grep xtrace )" ]; then
         set +x
   fi

}


Regards.

Brian P Curley



On Sun, Jan 21, 2018 at 11:21 AM, Simon Slavin <[hidden email]> wrote:

>
>
> On 21 Jan 2018, at 3:05pm, Brian Curley <[hidden email]> wrote:
>
> > pipe it
> > through jq instead.
>
> I did not know jq existed.  Thanks.  Just gave the documentation a quick
> glance.
>
> jq is not installed on my platform (macOS) whereas sqlite3 is.
>
> Does jq do conversion both ways ?
>
> Can jq deduce the column names (SQL) / keys (JSON) from the output of
> SELECT without extra work from the programmer/scripter ?
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request for the Shell Tool: .mode json

Simon Slavin-3


On 21 Jan 2018, at 6:56pm, Brian Curley <[hidden email]> wrote:

> In short, yes...you can get jq to convert both ways.
>
> It's not exactly as simple as just piping it through jq though, just to
> reiterate my earlier self-correction.

Hi, Brian.  Thanks for your detailed example which I read.  I can see that the tasks can be done by jq.  But I think the SQLite shell tool, which knows which key/column names to use, will do them faster and with far less work from the user.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request for the Shell Tool: .mode json

petern
Simon.   You want something like MySQL but using SQLite's shallower column
type awareness?  Reference:

https://dev.mysql.com/doc/refman/5.7/en/mysql-shell-json-output.html

Would you include a header variable when headers are turned on? Column
types too?
There are a number of design choices to work out, but the new code and test
cases would be entirely isolated to shell.c

The output mode is just formatting and indentation changes and the input
mode can crib from json1.c input routine.
Have you considered writing a reference implementation for a new mode_json
case of shell_callback to work out a practical design?

The other possibility would be to upgrade json1.c to allow
update/insert/delete and readout/writeout of current table in JSON format.
Given the trouble with integrating SQLite shell into every situation, such
a readout mode for json1.c could have wider utility.  Others have run into
the same portability problem with the read only csv.c extension.

Peter





On Sun, Jan 21, 2018 at 11:54 AM, Simon Slavin <[hidden email]> wrote:

>
>
> On 21 Jan 2018, at 6:56pm, Brian Curley <[hidden email]> wrote:
>
> > In short, yes...you can get jq to convert both ways.
> >
> > It's not exactly as simple as just piping it through jq though, just to
> > reiterate my earlier self-correction.
>
> Hi, Brian.  Thanks for your detailed example which I read.  I can see that
> the tasks can be done by jq.  But I think the SQLite shell tool, which
> knows which key/column names to use, will do them faster and with far less
> work from the user.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request for the Shell Tool: .mode json

Simon Slavin-3
On 21 Jan 2018, at 9:22pm, petern <[hidden email]> wrote:

> Simon.   You want something like MySQL but using SQLite's shallower column
> type awareness?  Reference:
>
> https://dev.mysql.com/doc/refman/5.7/en/mysql-shell-json-output.html

Just the data that is stored in the table, expressed as a JSON object, not an array.

> Would you include a header variable when headers are turned on? Column
> types too?

Since the data should be expressed as objects, the SQL column headers get expressed as JSON property keys.  Thus the names of the headers are preserved without any special measures.

> Have you considered writing a reference implementation for a new mode_json
> case of shell_callback to work out a practical design?

It would be a waste of time.  Either the development team likes my idea or it doesn’t.  If it does like my idea it needs code, and it won’t want to use my code since it won’t be certain that I didn’t copy it from somewhere.

I know that the idea works in some cases because I wrote my own code to do it.  But my own code wasn’t in C, and I didn’t worry about possibilities like BLOBs.

> The other possibility would be to upgrade json1.c to allow
> update/insert/delete and readout/writeout of current table in JSON format.
> Given the trouble with integrating SQLite shell into every situation, such
> a readout mode for json1.c could have wider utility.

I considered asking for it to appear in JSON1.  I rejected that option because JSON1 is not included by default, and because if you already have access to JSON functions, you can pretty-much do what I asked for myself.  I see my request as being more like another CSV input / output facility in the shell, suitable for scripting, rather than detailed flexible JSON capabilities in the API, suitable for programmers.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request for the Shell Tool: .mode json

Simon Slavin-3


On 21 Jan 2018, at 11:01pm, Simon Slavin <[hidden email]> wrote:

> Just the data that is stored in the table, expressed as a JSON object, not an array.

Sorry, what I meant was

Just the data returned by the SELECT command, expressed as an array of objects, one object per row.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request for the Shell Tool: .mode json

Jungle Boogie
In reply to this post by Simon Slavin-3
On Sun 21 Jan 2018  4:21 PM, Simon Slavin wrote:
>
>
> On 21 Jan 2018, at 3:05pm, Brian Curley <[hidden email]> wrote:
>
> > pipe it
> > through jq instead.
>
> I did not know jq existed.  Thanks.  Just gave the documentation a quick glance.
>

You might like to see some code examples:
https://www.rosettacode.org/wiki/Category:Jq

> jq is not installed on my platform (macOS) whereas sqlite3 is.

Right, but fortunately jq is a single binary file.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request for the Shell Tool: .mode json

Stadin, Benjamin
In reply to this post by Simon Slavin-3
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" <[hidden email] im Auftrag von [hidden email]>:

    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
    [hidden email]
    http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
   

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request for the Shell Tool: .mode json

J Decker
On Sun, Jan 21, 2018 at 3:50 PM, Stadin, Benjamin <
[hidden email]> wrote:

> 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
>
>
>
Or in javascript...

var sack = require( 'sack.vfs' );

function sqliteQueryToJson( dbPath, sql, jsonFile )
var db = sack.Sqlite( dbPath );
sack.Volume().file( jsonFIle).write( JSON.strinigfy( db.do( sql ) ) );

sack.JSON and sack.JSON6 have streaming JSON parsers... can read a stream
of records with a callback for each value or object discovered along the
stream.




> Am 21.01.18, 06:55 schrieb "sqlite-users im Auftrag von Simon Slavin" <
> [hidden email] im Auftrag von
> [hidden email]>:
>
>     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
>     [hidden email]
>     http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request for the Shell Tool: .mode json

petern
In reply to this post by Simon Slavin-3
>Just the data returned by the SELECT command, expressed as an array of
objects, one object per row.

That's what shell_callback() does inside shell.c.  It outputs one row at a
time in the current mode selected by the cases of a big switch()
statement.  Not sure I follow how your code would be subject to other
intellectual property claims.  You would merely be copying and pasting code
from SQLite itself.  For one, below is the row output code for MODE_html in
shell.c.  Your proposed MODE_json is probably a similar pattern with
different decorations.   The other modes are also there to be studied and
copied.

   case MODE_Html: {
      if( p->cnt++==0 && p->showHeader ){
        raw_printf(p->out,"<TR>");
        for(i=0; i<nArg; i++){
          raw_printf(p->out,"<TH>");
          output_html_string(p->out, azCol[i]);
          raw_printf(p->out,"</TH>\n");
        }
        raw_printf(p->out,"</TR>\n");
      }
      if( azArg==0 ) break;
      raw_printf(p->out,"<TR>");
      for(i=0; i<nArg; i++){
        raw_printf(p->out,"<TD>");
        output_html_string(p->out, azArg[i] ? azArg[i] : p->nullValue);
        raw_printf(p->out,"</TD>\n");
      }
      raw_printf(p->out,"</TR>\n");
      break;
    }

Just copy, edit, compile and test until it produces sensible output you can
demonstrate in an email.   Incorporating relevant features from MySQL JSON
output mode documentation that others will miss is also a good idea
considering the entire json1.c API model was also cloned from MySQL.

The only thing not to like about it would be if your design decisions make
something that isn't generally useful.   Not a waste of time if other JSON
users are interested enough in your idea to provide feedback on the
features they need.  Most of the development work here is figuring out
'what' and 'why' rather than 'how'.  The 'how' is trivial.

Peter

On Sun, Jan 21, 2018 at 3:04 PM, Simon Slavin <[hidden email]> wrote:

>
>
> On 21 Jan 2018, at 11:01pm, Simon Slavin <[hidden email]> wrote:
>
> > Just the data that is stored in the table, expressed as a JSON object,
> not an array.
>
> Sorry, what I meant was
>
> Just the data returned by the SELECT command, expressed as an array of
> objects, one object per row.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request for the Shell Tool: .mode json

Dominique Devienne
In reply to this post by Stadin, Benjamin
On Mon, Jan 22, 2018 at 12:50 AM, Stadin, Benjamin <
[hidden email]> wrote:

> 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.
>
...

>     while ((rc = sqlite3_step(readStmt)) == SQLITE_ROW) {
> ...

        for (int colIdx=0; colIdx<columnNames.size(); colIdx++) {
> ...

            sqlite3_value *val = sqlite3_column_value(readStmt, colIdx);
>             switch (int type = sqlite3_value_type(val)) {
> ...

            }
>

Since it's based on on sqlite3_value_type(), that's not "type affinity"
exactly,
just the actual "storage" type of the value. AFAIK there's no way to know
the
affinity [1] of a column of a table using an SQLite API. Perhaps there's a
pragma?

There's sqlite3_value_numeric_type() which allows you to emulate SQLite's
numeric affinity, but that's not the same. FWIW. --DD

[1] https://www.sqlite.org/datatype3.html#type_affinity
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request for the Shell Tool: .mode json

Eduardo
In reply to this post by Simon Slavin-3
On Sun, 21 Jan 2018 05:54:13 +0000
Simon Slavin <[hidden email]> escribió:

> Feature request for the Shell Tool: ".mode json".
>

Others has pointed to libraries to export to json, so I point to the one I use:
libucl https://github.com/vstakhov/libucl

Using the generation functions [1] you can convert from C structs and types to ucl and
export to any suportted formats, json, compact json, yaml and nginx like config files.

Licence BSD 2-clause "Simplified" License

> Simon.

[1] https://github.com/vstakhov/libucl/blob/master/doc/api.md#generation-functions-1

--
Eduardo <[hidden email]>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users