Re: [sqlite] sqlite command line tool fails to dump data

2018-01-21 Thread Clemens Ladisch
J Decker wrote: >> *If any NUL characters occur at byte| offsets less than the value of the >> fourth parameter then the resulting| string value will contain embedded >> NULs.* > > So it's best used as data, and not keys > and I see unless custom aggregate()s or function()s ... If you want

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

2018-01-21 Thread 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

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

2018-01-21 Thread 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++.

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

2018-01-21 Thread 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

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

2018-01-21 Thread 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:

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

2018-01-21 Thread 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.

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

2018-01-21 Thread 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,

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

2018-01-21 Thread 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

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

2018-01-21 Thread 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

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

2018-01-21 Thread 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

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

2018-01-21 Thread 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

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

2018-01-21 Thread 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

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

2018-01-21 Thread 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,

Re: [sqlite] sqlite command line tool fails to dump data

2018-01-21 Thread J Decker
On Sun, Jan 21, 2018 at 7:42 AM, Clemens Ladisch wrote: > J Decker wrote: > > insert into test (a,b) values ( ?,? ) > > bind 'hello\0world.' 'te\0st' > > says: > | If a non-negative fourth parameter is provided to

Re: [sqlite] sqlite command line tool fails to dump data

2018-01-21 Thread Clemens Ladisch
J Decker wrote: > insert into test (a,b) values ( ?,? ) > bind 'hello\0world.' 'te\0st' says: | If a non-negative fourth parameter is provided to sqlite3_bind_text() | or sqlite3_bind_text16() or sqlite3_bind_text64() then that parameter | must be

Re: [sqlite] sqlite command line tool fails to dump data

2018-01-21 Thread Luuk
On 21-01-18 16:16, J Decker wrote: > create table test (a,b) > insert into test (a,b) values ( ?,? ) > bind 'hello\0world.' 'te\0st' luuk@opensuse:~/tmp> sqlite3 SQLite version 3.8.10.2 2015-05-20 18:17:19 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open

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

2018-01-21 Thread 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 > >

[sqlite] sqlite command line tool fails to dump data

2018-01-21 Thread J Decker
sqlite test.db create table test (a,b) insert into test (a,b) values ( ?,? ) bind 'hello\0world.' 'te\0st' .dump table -- output CREATE TABLE test (a,b); INSERT INTO test VALUES('hello','te'); ___ sqlite-users mailing list

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

2018-01-21 Thread 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

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

2018-01-21 Thread 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