Re: [sqlite] [EXTERNAL] Intersect and Minus

[Hick Gunter]

It would be easier if you were to provide your schema, the text of the query 
and the error message.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Mohit Mathur
Gesendet: Montag, 15. April 2019 21:31
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] [sqlite] Intersect and Minus

Hi tech gurus,

I am working on one sqllite query, in which i am doing left outer join between 
two tables and than using intersect and again doing left outer join between two 
other tablescolumns that i am selecting are exactly same in number and 
datatypes.Please let me know why it is throwing error.How intersect works in 
sqllite ,i am well aware of oracle intersectin sqllite is it differnt?

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


___
 Gunter Hick | Software Engineer | Scientific Games International GmbH | 
Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 
1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Intersect and Minus

[Keith Medcalf]

On Monday, 15 April, 2019 13:31, Mohit Mathur  wrote:

>I am working on one sqllite query, in which i am doing left outer
>join between two tables and than using intersect and again doing 
>left outer join between two other tablescolumns that i am 
>selecting are exactly same in number and datatypes.

>Please let me know why it is throwing error.  

Could be the phase of the moon or random gamma particles emitted by the Sun 
hitting your RAM chips just so.  Or you could be doing something wrong.  Any 
guess is as good as any other since you have not deemed to show the query and a 
schema sufficient to demonstrate your issue.

As Mr Spock would say "There are insufficient referents for a meaningful 
discussion".

>How intersect works in sqllite, i am well aware of oracle intersectin 
>sqllite is it differnt?

According to the SQL Standard, INTERSECT returns rows that appear in the 
right-hand SELECT if and only if they also are present in the left-hand SELECT. 
 Does Oracle do something non-standard?

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

Re: [sqlite] Intersect and Minus

[Simon Slavin]

On 15 Apr 2019, at 8:31pm, Mohit Mathur  wrote:

> Please let me know why it is throwing error.

What error ?  Do you have an error number or text ?

If you perform the same SELECT in the SQLite command line tool do you get the 
same error ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Documentation correction

[tom-sqlite]

Hi, I just wanted to point out a minor discrepancy in the docs below.

https://www.sqlite.org/lang_createtable.html#rowid

where it says:

If a table contains a user defined column named "rowid", "oid" or "_rowid_", 
then that name always refers the explicitly declared column and cannot be used 
to retrieve the integer rowid value.

Shouldn't it say at the end "unless that user defined column is an alias for 
the rowid."

For example, you can retrieve the integer rowid value from a user defined 
column named "rowid" if it defined as the integer primary key:

CREATE TABLE t(rowid integer primary key);
insert into t values(15);
select rowid, oid from t;

Results:
15|15

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

[sqlite] Intersect and Minus

[Mohit Mathur]

Hi tech gurus,

I am working on one sqllite query, in which i am doing left outer join
between two tables and than using intersect and again doing left outer join
between two other tablescolumns that i am selecting are exactly same in
number and datatypes.Please let me know why it is throwing error.How
intersect works in sqllite ,i am well aware of oracle intersectin
sqllite is it differnt?

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

Re: [sqlite] Help with loading .DAT files

[Stephen Chrzanowski]

.DAT files can be anything.  If you can just use sqlite3.exe to open the
.DAT and do proper queries on it (IE: sqlite3.exe yourfile.dat), then it's
a proper SQLite3 database, so then you SHOULD be able to use the Attach
command.  Otherwise, you need to change the .DAT contents to something else
that can be understood by IMPORT and OPEN.


On Mon, Apr 15, 2019 at 12:25 PM Pablo Boswell (US - ASR) <
pablo.bosw...@pwc.com> wrote:

> I am trying to use Command Line Interface (CLI) sqlite3.exe to import .DAT
> files to an in-memory SQLite database.  I cannot get the following commands
> to load anything reasonable (the engine always decides to load the data as
> a single TEXT column with a column name of "sqlite3 data"):
>
> - .ATTACH
> - .IMPORT
> - .OPEN
>
> What am I doing wrong?
>
> --
> *Pablo Boswell*
>
> __
> The information transmitted, including any attachments, is intended only
> for the person or entity to which it is addressed and may contain
> confidential and/or privileged material. Any review, retransmission,
> dissemination or other use of, or taking of any action in reliance upon,
> this information by persons or entities other than the intended recipient
> is prohibited, and all liability arising therefrom is disclaimed. If you
> received this in error, please contact the sender and delete the material
> from any computer. PricewaterhouseCoopers LLP is a Delaware limited
> liability partnership.  This communication may come from
> PricewaterhouseCoopers LLP or one of its subsidiaries.
> ___
> 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] Help with loading .DAT files

[Simon Slavin]

On 15 Apr 2019, at 4:48pm, Pablo Boswell (US - ASR)  
wrote:

> I cannot get the following commands
> to load anything reasonable (the engine always decides to load the data as
> a single TEXT column with a column name of "sqlite3 data"):

Please copy-and-paste the first line, and another line from lower down the 
file, into a followup message.  Make very sure to keep spacing and punctuation 
exactly the same as it is in the original file.

If the numbers or text must be kept secret, you can swap digits for other 
digits and letter for other letter.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Reporting two more interesting queries with performance regression

[Jinho Jung]

Hello,

We are also trying to automatically narrow down the root cause of the
performance regression by using traditional statistical debugging
technique. (e.g.,
http://people.cs.uchicago.edu/~shanlu/preprint/oopsla161-song.pdf)

From the process, statistical debugger returns file/function name which is
closely related with the problem. To support your analysis, we would like
to share the result that we acquired from the debugger. We do analysis on
queries from our second/third report:
http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2019-April/083868.html
http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2019-April/083970.html


1) 6.sql (second report)
  - bisect commit: 57eb2abd5b
  - stat-dbg result:
+ file: select.c
+ function: sqlite3Select
 ==> especially places near /* Do the constant propagation optimization
*/
 causes slow down

2) 19.sql (second report)
  - bisect commit: 7d9072b027
  - stat-dbg result:
+ file: expr.c
+ function: codeApplyAffinity
 ==> removal of this function introduces slow down

3) 10002.sql (third report)
  - bisect commit: 9fb646f29c
  - stat-dbg result:
+ file: expr.c
+ function: sqlite3VdbeJumpHere, sqlite3VdbeAddOp0

4) 10052.sql (third report)
  - bisect commit: 7153552bac
  - stat-dbg result:
+ file: expr.c
+ function: sqlite3VdbeJumpHere, sqlite3VdbeAddOp0


Hope this help for your further analysis.

Best regards,
Jinho Jung

On Sat, Apr 6, 2019 at 9:47 AM Jinho Jung  wrote:

>
> Hello,
>
> We are submitting the third report with two more interesting cases with
> bisecting result using "SQL-Perf-Fuzzer".
>
> This is the timeline of our activities:
>
>   [Apr 1, 2019] Submitted 1st report (3 queries, 1 bisected commits)
>   (
> http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2019-April/083863.html
> )
> ==> confirmed the problem with correct bisect result (Apr/1)
>
>   [Apr 2, 2019] Submitted 2nd report: 2 queries, 2 unique bisected commits
>   (
> http://mailinglists.sqlite.org/cgi-bin/mailman/private/sqlite-users/2019-April/083868.html
> )
> ==> not yet confirmed
>
>   [Apr 5, 2019] Submitted 3rd report (2 queries, 2 unique bisected commits)
> ==> this report
>
>
> Here are the steps for reproducing our observations. All steps are same
> except for the link to download new test-cases:
>
> [Our test environment]
> * Ubuntu 16.04 machine "Linux sludge 4.4.0-116-generic #140-Ubuntu SMP Mon
> Feb 12 21:23:04 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux"
> * Database: TPC-C benchmark
>
> [Setup Test Environment]
>
> 1. build SQLite 3.27.2 (verion of Feb 2019)
>   $ wget https://www.sqlite.org/2019/sqlite-src-3270200.zip
>   $ unzip sqlite-src-3270200.zip
>   $ mv sqlite-src-3270200 sqlite327
>   $ cd sqlite327
>   $ ./configure
>   $ make
>   $ cd ..
>
> 2. build SQLite 3.23.0 (verion of Apr 2018)
>   $ wget https://www.sqlite.org/2018/sqlite-src-323.zip
>   $ unzip sqlite-src-323.zip
>   $ mv sqlite-src-323 sqlite323
>   $ cd sqlite323
>   $ ./configure
>   $ make
>   $ cd ..
>
> 3. download tpc-c for sqlite3 (scale-factor of 1)
>   $ mkdir testcase
>   $ cd testcase
>
>   $ wget https://gts3.org/~/jjung/sqlite/tpcc_sqlite.tar.gz
>   $ tar xzvf tpcc_sqlite.tar.gz
>
> ; download regression queries
>   $ wget https://gts3.org/~/jjung/sqlite/report3.tar.gz
>   $ tar xzvf report1.tar.gz
>   $ cd ..
>
> 4. launch two SQLites
>   - start
> $ sqlite327/sqlite3 testcase/test.db
> $ sqlite323/sqlite3 testcase/test.db
>
>   - for each DB, set up timer
> sqlite> .timer on
>
>  - copy and paste extracted queries
>
>
> Here’s the time taken to execute four SQL queries on old (v3.23) and newer
> version (v3.27.2) of SQLite (in milliseconds). We also try bisecting to
> know which commit activate the regression.
>
> +--++
> | Query|   Time |
> +--++
> | 10002.sql (v3.23)|789 |
> | 10002.sql (v3.27.2)  |   1270 |
> +--++
> | 10052.sql (v3.23)|   3094 |
> | 10052.sql (v3.27.2)  |   4478 |
> +--++
>
> 1) 10002.sql shows 60% performance regression
>  - bisect fossil commit:
>   === 2018-12-31 ===
>   [9fb646f29c] *MERGE* Merge enhancements and bug fixes from trunk. (user:
> drh tags: reuse-
>subqueries)
>
> 2) 10052.sql shows 40% performance regression
>  - bisect fossil commit:
>   === 2018-12-24 ===
>   [7153552bac] Improvements to EXPLAIN QUERY PLAN formatting. The
> MULTI-INDEX OR now
>shows a separate "INDEX" subtree for each index. SCALAR SUBQUERY
> entries provide a
>subquery number that is related back to the .selecttrace output. (user:
> drh tags: reuse-
>subqueries)
>
> Thanks for your checking and support.
>
> Best regards,
> Jinho Jung
>
>
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mai

[sqlite] Help with loading .DAT files

[Pablo Boswell (US - ASR)]

I am trying to use Command Line Interface (CLI) sqlite3.exe to import .DAT
files to an in-memory SQLite database.  I cannot get the following commands
to load anything reasonable (the engine always decides to load the data as
a single TEXT column with a column name of "sqlite3 data"):

- .ATTACH
- .IMPORT
- .OPEN

What am I doing wrong?

-- 
*Pablo Boswell*

__
The information transmitted, including any attachments, is intended only for 
the person or entity to which it is addressed and may contain confidential 
and/or privileged material. Any review, retransmission, dissemination or other 
use of, or taking of any action in reliance upon, this information by persons 
or entities other than the intended recipient is prohibited, and all liability 
arising therefrom is disclaimed. If you received this in error, please contact 
the sender and delete the material from any computer. PricewaterhouseCoopers 
LLP is a Delaware limited liability partnership.  This communication may come 
from PricewaterhouseCoopers LLP or one of its subsidiaries.
___
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

[Simon Slavin]

I don't know about any of this, but it seems that someone needs to write a 
'Unicode' (or 'Multibyte charaacters') page for the SQLite documentation.

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

[Keith Medcalf]

sqlite3_value_text and sqlite3_value_bytes will cause the conversion (if 
required) to UTF-8, and then return the data requested.
sqlite3_value_text16 and sqlite3_value_bytes16 will cause the conversion (if 
required) to UTF-16 and then return the data requested.

So if you call sqlite3_value_text AND THEN sqlite3_value_bytes16 your original 
UTF-8 text pointer will be invalid.  (Because the data must be converted to 
UTF-16 so that you can get the bytes count of that, thus the original UTF-8 no 
longer exists).

If you call only "like for like" functions, then the conversion will only be 
carried out the first time it is required and not for the subsequent calls to 
the "other function" that does not require conversion ...

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

>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of x
>Sent: Monday, 15 April, 2019 04:08
>To: SQLite mailing list
>Subject: Re: [sqlite] Help with sqlite3_value_text
>
>>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.
>
>JD, Could you explain that to me? I’m not sure why any conversion
>takes place and, on reading the text below, I would’ve thought it
>would be better to call sqlite3_value_bytes first (if it’s called
>“subsequently” the pointer returned by sqlite3_value_text “can be
>invalidated”).
>
>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



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

Re: [sqlite] Json paths

[Charles Leifer]

I still think that, in spite of the absence of a clear standard, sqlite
could benefit from more sophisticated path support.

Sqlite's json functions seem modeled after the MySQL json functions. Since
MySQL supports some nice features (wildcard, prefix/suffix match), it does
seem reasonable that sqlite might support similar behavior.

And, as you mentioned, sqlite tends to follow postgres as a kind of
standard, so once 12.0 lands it would be cool to see sqlite offer similar
json path support.

Anyway, I understand that there are perhaps many other things that are much
higher priority. Love the window functions, on conflict, etc which have
been added in the last few releases. Amazing stuff. Just wanted to see if
json path would be even on the road map.

On Mon, Apr 15, 2019, 4:57 AM Dominique Devienne 
wrote:

> On Mon, Apr 15, 2019 at 6:34 AM Charles Leifer  wrote:
>
> > I was wondering if there were any plans to support wildcard paths?
> >
>
> The main issue here IMHO is that there's no official standard, AFAIK.
>
>
> > Postgres v12 release looks like it has a pretty sophisticated jsonpath
> > type.
>
>
> SQLite does often follow PG's lead, so maybe that's the path forward.
>
> Haven't looked up the PG 12's JsonPath doc yet, especially since release
> later this year only.
>
> Here's one of the many JsonPath flavor I found:
> https://restfulapi.net/json-jsonpath/
>
> I'm used XPath2 (within XSLT2) extensively years ago, which is an actual
> standard,
> and has Michael Kay's [1] excellent book to explain it [2], but so such
> resources around "JsonPath".
>
> Again, doesn't prevent "inventing" one's semantic, or following an existing
> one. Still, it's unfortunate. --DD
>
> [1] https://en.wikipedia.org/wiki/Michael_Howard_Kay
> [2]
> https://www.amazon.com/XSLT-2-0-XPath-Programmers-Reference/dp/0470192747
> ___
> 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] Help with sqlite3_value_text

[Clemens Ladisch]

x wrote:
>> 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.
>
> Could you explain that to me? I’m not sure why any conversion takes place
> and, on reading the text below, I would’ve thought it would be better to
> call sqlite3_value_bytes first

As shown in the table, conversion from TEXT to BLOB does not change anything.
However, conversion from BLOB to TEXT might require appending a zero terminator.


Regards,
Clemens
___
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/15/19, Tim Streater  wrote:
>>
>> This command was added to the command-line tool recently.
>
> 3.19.3 has it - that's almost two years ago.
>

The .selftest command was added on 2017-03-09 by
https://www.sqlite.org/src/timeline?c=f4fcd46f08ba59d2 and hence as
likely first in release 3.18.0 on 2017-03-28.

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

Re: [sqlite] Database corruption check.

[Tim Streater]

On 15 Apr 2019, at 11:02, Simon Slavin  wrote:

> On 15 Apr 2019, at 10:36am, Lullaby Dayal  wrote:
>
>> Thank you very much for your response. The link seems to be helpful. But I
>> fail to run the .selftest command from my sqlite3 prompt. I got the error:
>> unknown command or invalid arguments error.
>
> This command was added to the command-line tool recently.

3.19.3 has it - that's almost two years ago.


-- 
Cheers  --  Tim
___
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]

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

JD, Could you explain that to me? I’m not sure why any conversion takes place 
and, on reading the text below, I would’ve thought it would be better to call 
sqlite3_value_bytes first (if it’s called “subsequently” the pointer returned 
by sqlite3_value_text “can be invalidated”).

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

[Dominique Devienne]

On Mon, Apr 15, 2019 at 11:37 AM Lullaby Dayal 
wrote:

> [...]. But I fail to run the .selftest command from my sqlite3 prompt. I
> got the error:
> unknown command or invalid arguments error.
>

That code dates back to July 2017. So you must have a very old version.


> I am a newbie in SQLite. I am not sure how can I get this to working in our
> application running in QNX on an ARM board.
>

Well, try on desktop first, with up-to-date versions you download from
sqlite.org
Then you you worry about more exotic environments like QNX. --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database corruption check.

[Simon Slavin]

On 15 Apr 2019, at 10:36am, Lullaby Dayal  wrote:

> Thank you very much for your response. The link seems to be helpful. But I
> fail to run the .selftest command from my sqlite3 prompt. I got the error:
> unknown command or invalid arguments error.

This command was added to the command-line tool recently.  You can download a 
current version of the tool from



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

Re: [sqlite] Json paths

[Dominique Devienne]

On Mon, Apr 15, 2019 at 6:34 AM Charles Leifer  wrote:

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

The main issue here IMHO is that there's no official standard, AFAIK.


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


SQLite does often follow PG's lead, so maybe that's the path forward.

Haven't looked up the PG 12's JsonPath doc yet, especially since release
later this year only.

Here's one of the many JsonPath flavor I found:
https://restfulapi.net/json-jsonpath/

I'm used XPath2 (within XSLT2) extensively years ago, which is an actual
standard,
and has Michael Kay's [1] excellent book to explain it [2], but so such
resources around "JsonPath".

Again, doesn't prevent "inventing" one's semantic, or following an existing
one. Still, it's unfortunate. --DD

[1] https://en.wikipedia.org/wiki/Michael_Howard_Kay
[2]
https://www.amazon.com/XSLT-2-0-XPath-Programmers-Reference/dp/0470192747
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Database corruption check.

[Lullaby Dayal]

Hi Richard,

Thank you very much for your response. The link seems to be helpful. But I
fail to run the .selftest command from my sqlite3 prompt. I got the error:
unknown command or invalid arguments error.

I am a newbie in SQLite. I am not sure how can I get this to working in our
application running in QNX on an ARM board.

Thank you!
Lullaby

On Sun, Apr 14, 2019, 5:38 PM 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&name=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-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLite v3.27.2 memory usage

[Warren Young]

On Apr 14, 2019, at 10:18 PM, David Ashman - Zone 7 Engineering, LLC 
 wrote:
> 
> It appears that there is a leak somewhere.

It is certainly in your code.  My bet’s on a missing sqlite3_finalize() call, 
but there are many other possibilities.

> Does anyone know why this error occurs?

I suggest you cross-compile your application, or at least its SQLite-using 
core, to a real OS and use its memory tracking tools to debug your use of 
SQLite, then apply that to the embedded app.

I’m talking about things like Valgrind, the Google AddressSanitizer now built 
into GCC and Clang, etc.

> Do I have to periodically call the release memory functions

No, you have to pair each function that makes allocations (e.g. 
sqlite3_prepare()) with a call to free that memory (e.g. sqlite3_finalize()).

> or is that inherent to SQLite?

This is C.  There is no inherent memory cleanup.  It’s all on you, the 
programmer.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users