[sqlite] Json paths

[Charles Leifer]

Many of the sqlite json1 functions accept a path parameter, which the
documents describe as:

For functions that accept PATH arguments, that PATH must be well-formed or
else the function will throw an error. A well-formed PATH is a text value
that begins with exactly one '$' character followed by zero or more
instances of ".objectlabel" or "[arrayindex]".

I was wondering if there were any plans to support wildcard paths?

Postgres v12 release looks like it has a pretty sophisticated jsonpath
type. Examples:

SELECT jsonb_path_query(x, '$.** ? (@.name == "a")') FROM test;
 SELECT jsonb_path_query(x, '$.sizes[1 to last]') FROM test;


MySQL allows wildcards in a couple flavors.

Do you anticipate anything like this landing in sqlite's json extension?

Thank you for the wonderful software,

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

[sqlite] SQLite v3.27.2 memory usage

[David Ashman - Zone 7 Engineering, LLC]

Hello -

I have a question on SQLite memory usage.  
I'm successfully using SQLite v3.27.2 amalgamation on an embedded ARM processor 
from STMicro with SD card and no OS.  The database file size is about 3.8GB.  
The file system is Segger emFile FAT32.  I've configured SQLite to use 3.7MB 
RAM for heap and 2MB RAM for cache.  My application RTree queries the db every 
1 second.  After about 25-30 minutes of operation, the query returns 
SQLITE_ERROR with error message "out of memory".  It appears that there is a 
leak somewhere.  Does anyone know why this error occurs?  Do I have to 
periodically call the release memory functions or is that inherent to SQLite? 
Any other ideas?
Compilation 
flags:SQLITE_MUTEX_NOOP=1SQLITE_OS_OTHER=1SQLITE_DISABLE_LFS=1SQLITE_THREADSAFE=0SQLITE_ENABLE_RTREE=1SQLITE_ENABLE_MEMSYS5=1HAVE_MALLOC_USABLE_SIZE=1Thanks
 in advance!Dave

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

Re: [sqlite] Help with sqlite3_value_text

[J Decker]

On Sun, Apr 14, 2019 at 5:40 AM x  wrote:

> On second thoughts JD, can’t use strlen or sqlite3_value_bytes in case
> values(1) contains more than a single unicode character. This looks OK.
>
> Bytes are what you need though; it doesn't matter how big the buffer is,
as long as you have all of it.

As long as you use _value_bytes after _text you're fine... so if any
conversion did take place the value will be right of the last returned
string type.


> # define CHARLEN(x) !(x & 128) ? 1 : (x & 16 ? 4 : (x & 32 ? 3 : 2))
>
> char *c = (char *)sqlite3_value_text(values[0]);
> char *Sep = (char *)sqlite3_value_text(values[1]);
> int Count=0, Len, SepLen = CHARLEN(*Sep);
>
> while (*c)
> {
>if ((Len = CHARLEN(*c)) == SepLen && memcmp(c, Sep, Len)==0)
> Count++; // at start of Sep
>c += Len;
> }
> sqlite3_result_int(ctx, Count);
>
> ___
> 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] Database corruption check.

[Richard Hipp]

On 4/14/19, Lullaby Dayal  wrote:
>
> For Sqlite database, as per my understanding, implementing pragma
> integrity_check won't guarantee all errors to be detected.

Maybe you are confused with "PRAGMA quick_check"?  The "PRAGMA
integrity_check" takes a little longer, but does a better job.  There
is very little that will slip past integrity_check.

Both of these pragmas only check the meta-data and linkage. If a bit
flips in the middle of a data field (perhaps due to a cosmic-ray hit
or something) and that field is not indexed, then there is nothing
that will detect that change.

To verify the data, you can use checksums.  The
https://www.sqlite.org/src/file/ext/misc/shathree.c extension
implements SHA3 hash functions, for example.  The CLI uses those hash
functions to checksum the data.  In the CLI you can type:

 .selftest --init

And that will create a new table named "selftest" that contains
checksums for all other tables.  Then later to verify those checksums,
run just:

 .selftest

You can look at the CLI source code
(https://www.sqlite.org/src/artifact?ln=7748-7852=c1986496062f9dba)
to see how this is implemented, and even copy/paste the CLI source
code into your application, if you want.


-- 
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] Database corruption check.

[Lullaby Dayal]

Hi,

We are using sqlite for our embedded automotive system based on QNX. We
have a requirement to check whether database is corrupted on start-up and
replace it with default database if such a scenario happens.

For Sqlite database, as per my understanding, implementing pragma
integrity_check won't guarantee all errors to be detected. Are there any
recommendations/best practices you suggest to check for corruption? For
eg:- CRC or hashing or ping pong technique? Please share some thoughts on
this..

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

Re: [sqlite] Help with sqlite3_value_text

[x]

On second thoughts JD, can’t use strlen or sqlite3_value_bytes in case 
values(1) contains more than a single unicode character. This looks OK.

# define CHARLEN(x) !(x & 128) ? 1 : (x & 16 ? 4 : (x & 32 ? 3 : 2))

char *c = (char *)sqlite3_value_text(values[0]);
char *Sep = (char *)sqlite3_value_text(values[1]);
int Count=0, Len, SepLen = CHARLEN(*Sep);

while (*c)
{
   if ((Len = CHARLEN(*c)) == SepLen && memcmp(c, Sep, Len)==0) Count++; // 
at start of Sep
   c += Len;
}
sqlite3_result_int(ctx, Count);

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

Re: [sqlite] Help with sqlite3_value_text

[x]

From: J Decker
Sent: 13 April 2019 20:05
To: SQLite mailing list
Subject: Re: [sqlite] Help with sqlite3_value_text

>> char *c = (char *)sqlite3_value_text(values[0]);
>> char *Sep = (char *)sqlite3_value_text(values[1]);
>> int Byte1, Count=0, NrBytes, NrSepBytes = strlen(Sep);

>you could use sqlite3_value_bytes(values[1]); instead.

Hi JD, Would I have to worry about this

“Please pay particular attention to the fact that the pointer returned from 
sqlite3_value_blob(), 
sqlite3_value_text(), or 
sqlite3_value_text16() can be 
invalidated by a subsequent call to 
sqlite3_value_bytes(), 
sqlite3_value_bytes16(), 
sqlite3_value_text(), or 
sqlite3_value_text16().”

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

Re: [sqlite] Inserting the same column multiple times

[Shawn Wagner]

On Sun, Apr 14, 2019 at 1:16 AM Luuk  wrote:

>
>
> Because, i do think, that it would never be possible to specify more
> than the number of columns in an insert statement?
>
>
The original issue was with some java/android sqlite binding that has a
method that builds an insert statement on the fly given lists of column
names and corresponding values being passed ones that had duplicates. It
might not be something any normal person does with a hand-written
statement, but automatically generated ones (combined with someone making
silly mistakes) are a different story.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inserting the same column multiple times

[Luuk]

On 14-4-2019 09:36, Shawn Wagner wrote:

Discovered this tonight answering a question on stack overflow:

sqlite> create table foo(a, b);
sqlite> insert into foo(a,b,a,b) values(1,2,3,4);
sqlite> select * from foo;
a   b
--  --
1   2

Inserting a column multiple times only uses the first corresponding value.
I don't see this documented anywhere.

By contract, a single UPDATE of the same column multiple times uses the
last one and ignores the rest:

sqlite> update foo set a=3, a=4;
sqlite> select * from foo;
a   b
--  --
4   2

And that is documented.

The inconsistency is annoying, but changing how either one works will
doubtless break somebody's code. Maybe clarify INSERT's behavior in its
documentation? Logging a warning in the case of a column being used
multiple times might be nice too.


An error like this one should do:

sqlite> insert into foo values(1,2,3,4);
Error: table foo has 2 columns but 4 values were supplied

Because, i do think, that it would never be possible to specify more 
than the number of columns in an insert statement?



MS-SQL has this:

create table foo(a int, b int);

insert into foo(a,b,a,b) values(1,2,3,4);

Msg 264, Level 16, State 1, Line 3
The column name 'a' is specified more than once in the SET clause or 
column list of an INSERT. A column cannot be assigned more than one 
value in the same clause. Modify the clause to make sure that a column 
is updated only once. If this statement updates or inserts columns into 
a view, column aliasing can conceal the duplication in your code.

Msg 264, Level 16, State 1, Line 3
The column name 'b' is specified more than once in the SET clause or 
column list of an INSERT. A column cannot be assigned more than one 
value in the same clause. Modify the clause to make sure that a column 
is updated only once. If this statement updates or inserts columns into 
a view, column aliasing can conceal the duplication in your code.



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

[sqlite] Inserting the same column multiple times

[Shawn Wagner]

Discovered this tonight answering a question on stack overflow:

sqlite> create table foo(a, b);
sqlite> insert into foo(a,b,a,b) values(1,2,3,4);
sqlite> select * from foo;
a   b
--  --
1   2

Inserting a column multiple times only uses the first corresponding value.
I don't see this documented anywhere.

By contract, a single UPDATE of the same column multiple times uses the
last one and ignores the rest:

sqlite> update foo set a=3, a=4;
sqlite> select * from foo;
a   b
--  --
4   2

And that is documented.

The inconsistency is annoying, but changing how either one works will
doubtless break somebody's code. Maybe clarify INSERT's behavior in its
documentation? Logging a warning in the case of a column being used
multiple times might be nice too.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users