Re: [sqlite] Feature request for the Shell Tool: .mode json

[Eduardo]

On Sun, 21 Jan 2018 05:54:13 +
Simon Slavin  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 
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature request for the Shell Tool: .mode json

[Dominique Devienne]

On Mon, Jan 22, 2018 at 12:50 AM, Stadin, Benjamin <
benjamin.sta...@heidelberg-mobil.com> 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 ...

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

Re: [sqlite] Feature request for the Shell Tool: .mode json

[petern]

>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,"");
for(i=0; iout,"");
  output_html_string(p->out, azCol[i]);
  raw_printf(p->out,"\n");
}
raw_printf(p->out,"\n");
  }
  if( azArg==0 ) break;
  raw_printf(p->out,"");
  for(i=0; iout,"");
output_html_string(p->out, azArg[i] ? azArg[i] : p->nullValue);
raw_printf(p->out,"\n");
  }
  raw_printf(p->out,"\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  wrote:

>
>
> On 21 Jan 2018, at 11:01pm, Simon Slavin  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
> 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

Re: [sqlite] Feature request for the Shell Tool: .mode json

[J Decker]

On Sun, Jan 21, 2018 at 3:50 PM, Stadin, Benjamin <
benjamin.sta...@heidelberg-mobil.com> 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" <
> 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  .
> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature request for the Shell Tool: .mode json

[Stadin, Benjamin]

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 
#include 
#include 
#include 

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(), , 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, , 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 columnNames;
int count = sqlite3_column_count(readStmt);
for (int i=0; i > jsonBuffer;
rapidjson::Writer jsonWriter(jsonBuffer);

jsonWriter.StartArray();
int numRecs = 0;
int rc;
while ((rc = sqlite3_step(readStmt)) == SQLITE_ROW) {
numRecs++;
jsonWriter.StartObject();
for (int colIdx=0; colIdx:

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 

Re: [sqlite] Feature request for the Shell Tool: .mode json

[Jungle Boogie]

On Sun 21 Jan 2018  4:21 PM, Simon Slavin wrote:
> 
> 
> On 21 Jan 2018, at 3:05pm, Brian Curley  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature request for the Shell Tool: .mode json

[Simon Slavin]

On 21 Jan 2018, at 11:01pm, Simon Slavin  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature request for the Shell Tool: .mode json

[Simon Slavin]

On 21 Jan 2018, at 9:22pm, petern  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  wrote:

>
>
> On 21 Jan 2018, at 6:56pm, Brian Curley  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
> 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

Re: [sqlite] Feature request for the Shell Tool: .mode json

[Simon Slavin]

On 21 Jan 2018, at 6:56pm, Brian Curley  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature request for the Shell Tool: .mode json

[Brian Curley]

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 "\nError:  %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 "\nError:  %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 "\nError:  %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

}

​

Re: [sqlite] Feature request for the Shell Tool: .mode json

[Luuk]

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"  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature request for the Shell Tool: .mode json

[Simon Slavin]

On 21 Jan 2018, at 3:05pm, Brian Curley  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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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"  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
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

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

Re: [sqlite] 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"  wrote:

> On Sat, Jan 20, 2018 at 9:54 PM, Simon Slavin 
> 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  .
> > 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
> > 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature request for the Shell Tool: .mode json

[J Decker]

On Sat, Jan 20, 2018 at 9:54 PM, Simon Slavin  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  .
> 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
> 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

[sqlite] Feature request for the Shell Tool: .mode json

[Simon Slavin]

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