Re: [sqlite] Multi layer JSON query

2019-12-20 Thread Jens Alfke 


> On Dec 19, 2019, at 7:45 PM, No.1 Perfect <757171...@qq.com> wrote:
> 
> How can I count the amountand numof goods when the customer or 
> goodsname is different.

Querying the contents of arrays is kind of complicated. SQL doesn't understand 
arrays, so the query has to use a "table-valued function", json_each, that 
makes the array appear to be a table where each item is a row. Then you can use 
a JOIN to query that "table".

The documentation has examples: https://sqlite.org/json1.html#jeach 


—Jens

PS: Your email program is turning nonbreaking spaces into "", which makes 
your message hard to read. Please try to fix that.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Makefile.msc omits rbu exports

2019-12-20 Thread Keith Medcalf 

When the sqlite3.def is made, the rbu exports are not included.

That is, this

# <>
sqlite3.def:libsqlite3.lib
echo EXPORTS > sqlite3.def
dumpbin /all libsqlite3.lib \
| $(TCLSH_CMD) $(TOP)\tool\replace.tcl include "^\s+1 
_?(sqlite3(?:session|changeset|changegroup|rebaser)?_[^@]*)(?:@\d+)?$$" \1 \
| sort >> sqlite3.def
# <>

should be

# <>
sqlite3.def:libsqlite3.lib
echo EXPORTS > sqlite3.def
dumpbin /all libsqlite3.lib \
| $(TCLSH_CMD) $(TOP)\tool\replace.tcl include "^\s+1 
_?(sqlite3(?:rbu|session|changeset|changegroup|rebaser)?_[^@]*)(?:@\d+)?$$" \1 \
| sort >> sqlite3.def
# <>

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] RBU fails as of checkin f84a1539

2019-12-20 Thread Ralf Junker 

As of Fossil checkin f84a1539, the RBU code in the following C example
is no longer executed to completion. Instead, an error message is
generated and the result database is not correctly written.

The code works fine with Fossil checkin 28091a48. It generates no error
messages and produces the expected result database.

The problem is still present on trunk, checkin 289158aa at the time of
this writing.

Could anyone reproduce my findings?

Many thanks,

Ralf

--

#include 
#include "sqlite3.h"
#include "sqlite3rbu.h"

static void check(int r, int e) {
  if (r != e) {
printf ("ERROR %d, expected %d\n", e, r);
  }
}

static int callback (void *user, int nCol, char **r, char **c) {
  int i;
  for (i = 0; i < nCol; i++) {
printf("%s ", r[i]);
  }
  printf("\n");
  return 0;
}

static int runrbu(char *zTarget, char *zRbu) {
  sqlite3rbu* rbu;
  int rc;
  char* zError;

  rbu = sqlite3rbu_open (zTarget, zRbu, NULL);
  do {
rc = sqlite3rbu_step(rbu);
  } while (rc == SQLITE_OK);

  rc = sqlite3rbu_close(rbu, );
  if (zError) {
printf("RBU error: %s\n", zError);
sqlite3_free(zError);
  }
  return rc;
}

int main(void)
{
  sqlite3 *db, *dbRbu;
  int rc;

  remove ("test.db");
  check(SQLITE_OK, sqlite3_open_v2 ("test.db", ,
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL));

  check(SQLITE_OK, sqlite3_exec(db,
"CREATE TABLE t1(a, b, c PRIMARY KEY);" \
"CREATE INDEX i1 ON t1(a, null, b+1);" \
"CREATE INDEX i2 ON t1(a+1, b+1, c+1);" \

"INSERT INTO t1 VALUES(1, 2, 3);" \
"INSERT INTO t1 VALUES(4, 5, 6);" \
"INSERT INTO t1 VALUES(7, 8, 9);" \
"INSERT INTO t1 VALUES(10, 11, 12);" ,
callback, NULL, NULL));

  sqlite3_close(db);

  remove ("rbu.db");
  check(SQLITE_OK, sqlite3_open_v2 ("rbu.db", ,
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, NULL));

  check(SQLITE_OK, sqlite3_exec(db,
"CREATE TABLE data_t1(a, b, c, rbu_control);" \
"INSERT INTO data_t1 VALUES(13, 14, 15, 0);" \
"INSERT INTO data_t1 VALUES(NULL, NULL, 6, 1);" \
"INSERT INTO data_t1 VALUES(NULL, 'three', 3, '.x.');",
callback, NULL, NULL));

  sqlite3_close(dbRbu);

  check(SQLITE_DONE, runrbu("test.db", "rbu.db"));

  printf("Done - Press ENTER to exit.\n");
  getchar();

  return 0;
}
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sanitising user input for FTS5 MATCH parameter

2019-12-20 Thread Jose Isaias Cabrera 

test user, on Friday, December 20, 2019 10:03 AM, wrote...
>
> Hello,
>
> I have a search box on a website that uses FTS5/MATCH.
>
> MATCH seems to take its own custom language for matching.
>
> 1. Is it safe to just pass the users query to MATCH ? via the SQLite bind
> FFI?
>
> - This would give them full access to the FTS5 matching language.
>
> 2. If not, how should I be sanitising user input?
>
> - E.g. How can I transform a string of words and text into a query? What
> characters should I be removing or escaping? How can I prevent them using
> the FTS5 keywords "AND" "OR" etc?

Have you taken a look at the FTS5 site[1]?  It has lots of information there 
that may be helpful.

josé

[1] https://www.sqlite.org/fts5.html

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sanitising user input for FTS5 MATCH parameter

2019-12-20 Thread test user 
Hello,

I have a search box on a website that uses FTS5/MATCH.

MATCH seems to take its own custom language for matching.

1. Is it safe to just pass the users query to MATCH ? via the SQLite bind
FFI?

- This would give them full access to the FTS5 matching language.

2. If not, how should I be sanitising user input?

- E.g. How can I transform a string of words and text into a query? What
characters should I be removing or escaping? How can I prevent them using
the FTS5 keywords "AND" "OR" etc?

Thanks
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite database gets corrupted

2019-12-20 Thread Simon Slavin 
On 20 Dec 2019, at 12:11pm, Syed Ahmad  wrote:

> Any way to recover it.

This mailing list strips attachments.

When's the last time you could use this file ?  Did it have any form of 
encryption ?

Was it usable with your own software ?  Could you open it with the SQLite 
command-line tool ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Patch for Mac Catalyst

2019-12-20 Thread Paulo Coutinho 
Hi,

I made a patch to sqlite3 can be compiled for Mac Catalyst. It is reported
into my project ezored:

https://github.com/ezored/ezored/issues/20

With this i was able to compile everywhere.

And here is the working framework:
https://www.youtube.com/watch?v=ro2WVX_wHuQ

Thanks.

-- 
Atenciosamente,
Paulo Coutinho.
*PR**S** - Soluções Inteligentes*

Site: www.prsolucoes.com
Blog: www.pcoutinho.com
Skype: paulo.prsolucoes
Telefone: +55 21 99386-0010
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Sqlite database gets corrupted

2019-12-20 Thread Syed Ahmad 
Hi,

Operating system: Embedded Linux.

Version : 3.14.2 2016-09-12

sqlite3 bad.sqlite "PRAGMA integrity_check"
Error: file is encrypted or is not a database


I have attached the file.


Any way to recover it. Is any there at fixes in latest sqlite version 3.x.
which will fix this issue.


Thanks,
Naeem
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Lemon source code?

2019-12-20 Thread Richard Hipp 
On 12/20/19, David Given  wrote:
> Does anyone know if the original source code for the lemon parser still
> exists, and if so, where to find it?
>
> The lemon.c file shipped with sqlite, which the hwaci website points me at,
> does not appear to be it --- instead it's an amalgamation of a bunch of
> other files, including some generated by aagen from data files; but I have
> been able to track down neither the data files nor the aagen tool itself.
>
> Or does maintainence these days happen directly on the amalgamation?

I have long since lost the original source files (which I wrote on a
Sun4 in K C).  All maintenance these days happens on the amalgamated
"lemon.c" source file, which isn't that big really - less than 6K
lines total and less than 5K SLOC.

The complete edit history of the "lemon.c" source file, going back
almost 20 years, can be seen here:
https://www.sqlite.org/src/finfo/tool/lemon.c

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Lemon source code?

2019-12-20 Thread David Given 
Does anyone know if the original source code for the lemon parser still
exists, and if so, where to find it?

The lemon.c file shipped with sqlite, which the hwaci website points me at,
does not appear to be it --- instead it's an amalgamation of a bunch of
other files, including some generated by aagen from data files; but I have
been able to track down neither the data files nor the aagen tool itself.

Or does maintainence these days happen directly on the amalgamation?

Thanks!

-- 
┌─── http://www.cowlark.com ───
│ "I have always wished for my computer to be as easy to use as my
│ telephone; my wish has come true because I can no longer figure out
│ how to use my telephone." --- Bjarne Stroustrup
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Multi layer JSON query

2019-12-20 Thread Robert Hairgrove 

On 20.12.19 04:45, No.1 Perfect wrote:

I've read the full page instructions of JSON1, But I don't know how to use 
JSON1 to query multiple layers JSON data.


The Data as follows :


{
 id: 1,
 data: {
  customer: 1,
  goods: [
{ id: 1, name: "apple", price: 12, num: 10, 
amount: 120 },
{ id: 2, name: "perl", price: 10, num: 5, 
amount: 50 }
  ]
 }
}


(snipped...)

Two suggestions come to mind:

1. Normally, JSON data is not displayed directly in a web page, so all 
the  (entity references for non-breaking space) shouldn't be in 
there (and probably is not valid JSON, anyway).


2. If you do need to display the JSON code for some reason, use regular 
spaces and line feeds and wrap it in HTML  tags.


Unfortunately, I never used JSON queries with SQLite, so others will 
have to help after this point.


HTH,
Bob Hairgrove

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Multi layer JSON query

2019-12-20 Thread No.1 Perfect 
My English is poor, please forgive me.



The first time I use SQLite database.


I've read the full page instructions of JSON1, But I don't know how to use 
JSON1 to query multiple layers JSON data.


The Data as follows :


{
 id: 1,
 data: {
  customer: 1,
  goods: [
{ id: 1, name: "apple", price: 12, num: 10, 
amount: 120 },
{ id: 2, name: "perl", price: 10, num: 5, 
amount: 50 }
  ]
 }
}

{
 id: 2,
 data: {
  customer: 2,
  goods: [
{ id: 1, name: "apple", price: 12, num: 10, 
amount: 120 },
{ id: 2, name: "perl", price: 10, num: 5, 
amount: 50 },
{ id: 3, name: "banana", price: 10, num: 5, 
amount: 50 }
  ]
 }
}


{
 id: 3,
 data: {
  customer: 2,
  goods: [
{ id: 1, name: "apple", price: 12, num: 10, 
amount: 120 }
  ]
 }
}




How can I count the amountand numof goods when the customer or 
goodsname is different.



I only write the SQL:  
selectjson_extract(data,'$.goods')from table_name where 
customer = 2



When I got the data, I use the programming language to loop the results and add 
amountand numby myself.



Can you help me, tell me how I should write this.


And thanks for your help, your sqlite database is good database, I will 
continue to use it on my program.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users