[sqlite] Search % using sqlite

[Hegde, Deepakakumar (D.)]

Hi All,


We are implementing a wild card search feature.  our query is as below:


sprintf (acQstring,

"SELECT NAME FROM AUDIO WHERE NAME LIKE '%%%s%%'", acInputString);


In the input string is '%' then we are getting t all the entry in the column.


ex: name column have following:

%a

a

a%a

aa%


we are expecting entry 2 which don't have % in it should not get as output. But 
it seems not the case, it is giving all the 4 entry as output.

Please can we know is there any way of searching this? Thanks.


Thanks and Regards

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

[sqlite] LSM extension unusual memory growth?

[Josu Diaz de Arcaya]

Hi all,

I have been giving the LSM extension a try and I have a concern
regarding memory consumption. It seems as as long as you keep the
connection open and insert and delete
entries memory keeps growing without an apparent roof. I have made a
very simple program that reproduces this.

#include "lsm.h"

#include 
#include 
#include 
#include 

int j=-1;

void generateKey(char *key);

int main() {

lsm_db *db;
lsm_new(lsm_default_env(), );
lsm_open(db, "test.db");

char key[5], val[5];
generateKey(val);

lsm_cursor *csr;
// delete and insert 400M elements
for (j=0; j<4;j++) {
generateKey(key);
lsm_delete(db, key, 3);
generateKey(key);
lsm_insert(db, key, 3, val, 3);
}

lsm_close(db);
}

void generateKey(char *key) {
key[0] = ( rand() % 40 ) + 45;
key[1] = ( rand() % 40 ) + 45;
key[2] = ( rand() % 40 ) + 45;
key[3] = '\0';
This program deletes and insert entries into the database, the maximum
amount of entries is 40^3 but it stabilizes around half that much.
However, memory keeps growing
as shown in the next graph, which corresponds to a 15 hour run.
(Forgive me if the quality is not too good in the browser, if you copy
and paste it in a text editor it shows a constant slope)


Command:./test
Massif arguments:   --stacks=yes
ms_print arguments: massif.out.11718


MB
10.48^   :
 |@@@#
 |   #
 | :@ @  #
 |  @@@:@ @@@ @  #
 |  @@@:@ @@@ @  #
 |    @ @@@:@ @@@ @  #
 |   : @@ @ @@@:@ @@@ @  #
 |@@ @ @@ @ @@@:@ @@@ @  #
 |:: :@@ @ @@ @ @@@:@ @@@ @  #
 |  @ :: :@@ @ @@ @ @@@:@ @@@ @  #
 |@@ @@ @ :: :@@ @ @@ @ @@@:@ @@@ @  #
 |   @ @ @@ @ :: :@@ @ @@ @ @@@:@ @@@ @  #
 | @ @ @ @@ @ :: :@@ @ @@ @ @@@:@ @@@ @  #
 |  @@:@: :  @ @ @@ @ :: :@@ @ @@ @ @@@:@ @@@ @  #
 | :@@:@: :  @ @ @@ @ :: :@@ @ @@ @ @@@:@ @@@ @  #
 |  @@:@ @ :@@:@: :  @ @ @@ @ :: :@@ @ @@ @ @@@:@ @@@ @  #
 |   ::@@ :@ @ :@@:@: :  @ @ @@ @ :: :@@ @ @@ @ @@@:@ @@@ @  #
 |   : @@ :@ @ :@@:@: :  @ @ @@ @ :: :@@ @ @@ @ @@@:@ @@@ @  #
 |   :: :: @@ :@ @ :@@:@: :  @ @ @@ @ :: :@@ @ @@ @ @@@:@ @@@ @  #
   0 +--->Ti
 0   8.616

I've run this test with valgrind's massif tool. I've made longer tests
but the behaviour looks the same. It looks to me that there is
something odd going on with memory,
but I may be missing something.

Is this a problem with LSM extension itself or am I missing some
important point in here?

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

Re: [sqlite] Database Variable

[Gregory Moore]

Thanks for the reply. I see your point about avoiding including sqlite3.h in 
the header. I found the same can be accomplished by declaring a category in the 
implementation file and declaring the variable there as follows:

@interface DatabaseController()
{
 sqlite3 *_database;
}

@implementation
{
 
}

In fact that’s the technique FMDB uses even though they still declare the 
variable as a void pointer. My apologies for neglecting to mention that.

Greg Moore
thewatchful...@gmail.com

> On Jan 2, 2018, at 4:51 PM, Jens Alfke  wrote:
> 
> 
> 
>> On Jan 2, 2018, at 1:49 PM, Gregory Moore  wrote:
>> 
>> I was poking around in FMDB’s source code on github. FMDB is a widely used 
>> Objective-C wrapper around SQLite. I was looking to see what they do out of 
>> curiosity.  FMDB declares and uses the database variable like this:
>> 
>> void *_database;
> 
> This was probably just done to avoid having to include  in the 
> FMDB header files.
> 
> —Jens
> ___
> 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] new Error database disk image is malformed

[Peter Da Silva]

> Ok. I've read the Document and I think I may be having a rouge thread issue.  
> I hope not since I use synclock in my code when ever a thread is attempting a 
> write to the database.  That seems like the only issue from that page that I 
> may be doing.  I could have up to 30 or more threads reading from the DB but 
> only one to three active threads writing.  The way my code is set up, though 
> is that each thread would have to wait for the previous thread to finish 
> writing before its turn to write. 

Since you're I/O bound on socket connections, and not CPU or database bound, 
you might want to just have one database thread that communicates using native 
inter-thread messaging to pass out work and accept responses from the worker 
threads.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

[Dinu]

Thank you for your answer, Keith. I had my problem "fixed" before I wrote the
first mail. Also with every problem I also provided the fix that worked, for
anyone that might run into the same problem.
However, it's difficult to not get a little frustrated with your answer. 

At https://sqlite.org/queryplanner.html I read:

"The best feature of SQL (in all its implementations, not just SQLite) is
that it is a declarative language, not a procedural language. When
programming in SQL you tell the system what you want to compute, not how to
compute it."

And I completely agree with this, "how to compute it" is called relational
algebra and it's what a query planner should do best. And the two queries
are algebrically identical. "(X ∊ S or X:=null) AND (X is not null)" is
equivalent to "X ∊ S is not null". The two queries might look different only
from an imperative programming point of view.

As to why the query is written that way: with the above in mind, I will
contend that there can absolutely never exist a "mistaken" way to write a
query, as long as the description of the predicates is correct and
consistent with the schema. You should consider that quite frequently
queries are the result of one or more levels of logic abstraction (ORM,
DBAL, etc). In my case, modifying the query was not difficult to do, but in
other cases one may have few options on rewriting the way the query
structure is generated. The only way to reduce a fabricated query is through
relational algebra, and that is up to the DB, not the programmer, not the
abstractions in-between.

In this particular case, the where is optional; depending on parameters, I
want the set of data that is correctly defined as the left join of tables a
and b, or I might want a subset of this join that has a particular property
over the left-joined set. The query was correctly written, to rewrite it so
that the query planner might know how to run it is wrong, IMHO.

To sum it up: I think it's every DB's intention to optimize as best possible
a query into an execution plan. None does it perfectly, but all try to, very
hard. With this intention, I reported a case where the query planner COULD
be improved. I think you will at least agree with me that making it better
can't be wrong. Whether that happens tomorrow, in a year or never, that's up
to the mercy, resources and priorities of the developers, so I am really am
not interested in an argue over this. 



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] new Error database disk image is malformed

[Richard Damon]

On 1/4/18 8:02 PM, Jens Alfke wrote:



On Jan 4, 2018, at 4:51 PM, Ron Barnes  wrote:

I hope not since I use synclock in my code when ever a thread is attempting a 
write to the database.  That seems like the only issue from that page that I 
may be doing.  I could have up to 30 or more threads reading from the DB but 
only one to three active threads writing.

It depends on the value of SQLITE_THREADSAFE that SQLite was compiled with. 
(Check the docs for details.) If it’s set to 1, you can do what you’re doing. 
Otherwise, you cannot use a single SQLite connection on multiple threads 
without using your own mutex (if it’s 2), or at all (if it’s 0).

30 threads sounds like overkill, BTW. Generally the appropriate number is equal 
to the number of CPU cores. And if the task is I/O-bound there’s not much 
benefit to having more than one since only one of them can be inside SQLite at 
a time.

—Jens
Limiting to number of CPU cores make sense for homogeneous threads 
(multiple threads doing the same sort of work, so make it go in 
parallel). One case where more can make sense is if they are 
heterogeneous, each thread doing some unique task to keep the worker 
threads simple.


--
Richard Damon

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

Re: [sqlite] new Error database disk image is malformed

[Ron Barnes]

Hi Jens,

Thank you for the response.  
I left it at default so it should be a 1.  The threads are not I/O.  They each 
execute only a few short lines of code to gather network information and then 
hit the DB for a read using the information they just gathered to populate a 
key.  After the read they add the formatted data to a table that is eventually 
displayed to the user.

A quick overview of the code.
I check 64K ports for a response to determine if they are open or not (Nothing 
nefarious).  I use 30+ threads because it could take up to 10 seconds for a 
port to respond while other ports respond immediately.

If you are curious to see the app in action and get a better picture of what 
I'm doing.  It's on the Microsoft store for free.

Do a search of the MS Store for an App named Lo and Behold.

Or click this link..

https://www.microsoft.com/en-us/store/p/lo-and-behold/9nblggh533kc

-Ron

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Jens Alfke
Sent: Thursday, January 4, 2018 8:03 PM
To: SQLite mailing list 
Subject: Re: [sqlite] new Error database disk image is malformed



> On Jan 4, 2018, at 4:51 PM, Ron Barnes  wrote:
> 
> I hope not since I use synclock in my code when ever a thread is attempting a 
> write to the database.  That seems like the only issue from that page that I 
> may be doing.  I could have up to 30 or more threads reading from the DB but 
> only one to three active threads writing.

It depends on the value of SQLITE_THREADSAFE that SQLite was compiled with. 
(Check the docs for details.) If it’s set to 1, you can do what you’re doing. 
Otherwise, you cannot use a single SQLite connection on multiple threads 
without using your own mutex (if it’s 2), or at all (if it’s 0).

30 threads sounds like overkill, BTW. Generally the appropriate number is equal 
to the number of CPU cores. And if the task is I/O-bound there’s not much 
benefit to having more than one since only one of them can be inside SQLite at 
a time.

—Jens
___
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] new Error database disk image is malformed

[Jens Alfke]

> On Jan 4, 2018, at 4:51 PM, Ron Barnes  wrote:
> 
> I hope not since I use synclock in my code when ever a thread is attempting a 
> write to the database.  That seems like the only issue from that page that I 
> may be doing.  I could have up to 30 or more threads reading from the DB but 
> only one to three active threads writing.

It depends on the value of SQLITE_THREADSAFE that SQLite was compiled with. 
(Check the docs for details.) If it’s set to 1, you can do what you’re doing. 
Otherwise, you cannot use a single SQLite connection on multiple threads 
without using your own mutex (if it’s 2), or at all (if it’s 0).

30 threads sounds like overkill, BTW. Generally the appropriate number is equal 
to the number of CPU cores. And if the task is I/O-bound there’s not much 
benefit to having more than one since only one of them can be inside SQLite at 
a time.

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

Re: [sqlite] new Error database disk image is malformed

[Ron Barnes]

Hi,

Ok. I've read the Document and I think I may be having a rouge thread issue.  I 
hope not since I use synclock in my code when ever a thread is attempting a 
write to the database.  That seems like the only issue from that page that I 
may be doing.  I could have up to 30 or more threads reading from the DB but 
only one to three active threads writing.  The way my code is set up, though is 
that each thread would have to wait for the previous thread to finish writing 
before its turn to write.  

Each process works as follows...

Gather information.
Format the information

Enter the write routine using synclock (Visual Basic)

Write the record
Exit the write routine.

I'm using Visual Studio 2015
Visual Basic
Samsung 2TB SSD
64GB RAM
8 Core AMD Processor

Could any of the 30 or so concurrent reads be messing up my writes?

Here is my Create string...

Dim CreateNewDBConString As String = "Data Source=" + Trim(MyPath) + 
"\LoAndBehold.DB3;Version=3;New=True;Max Page 
Count=10485760;Compress=True;journal_mode=WAL;"

Here is my read/write string...

Dim Myconstring As String = "Data Source=" + Trim(MyPath) + 
"\LoAndBehold.DB3;Version=3;New=False;Max Page 
Count=10485760;Compress=True;journal_mode=WAL;"


Regards,

-Ron

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of R Smith
Sent: Thursday, January 4, 2018 3:14 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] new Error database disk image is malformed


On 2018/01/04 9:49 PM, Ron Barnes wrote:
> Hi All,
>
> I keep generating this error and I can't figure out why.  I have deleted and 
> re-created the database but it keeps popping up.
>
> Error
>
> database disk image is malformed
>
>
> Any ideas why?

This is a wild guess, but I'm thinking it's because the disk image is 
malformed...? :)

More seriously, what you probably meant to ask is: "Any idea why my file gets 
corrupt in this way?"

To which the best answer is: No idea, but here are some things that are typical 
culprits:
https://sqlite.org/howtocorrupt.html

Once you've checked all those, and you still are not sure what could cause the 
corruption, please write again but then include all information, OS, storage 
media, file system, use case, etc. and likely someone here would have had 
similar experience or run a similar system (or perhaps see some other obvious 
problem).

Good luck!
Ryan


___
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] new Error database disk image is malformed

[Ron Barnes]

Thank you - I will and report back!

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of R Smith
Sent: Thursday, January 4, 2018 3:14 PM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] new Error database disk image is malformed


On 2018/01/04 9:49 PM, Ron Barnes wrote:
> Hi All,
>
> I keep generating this error and I can't figure out why.  I have deleted and 
> re-created the database but it keeps popping up.
>
> Error
>
> database disk image is malformed
>
>
> Any ideas why?

This is a wild guess, but I'm thinking it's because the disk image is 
malformed...? :)

More seriously, what you probably meant to ask is: "Any idea why my file gets 
corrupt in this way?"

To which the best answer is: No idea, but here are some things that are typical 
culprits:
https://sqlite.org/howtocorrupt.html

Once you've checked all those, and you still are not sure what could cause the 
corruption, please write again but then include all information, OS, storage 
media, file system, use case, etc. and likely someone here would have had 
similar experience or run a similar system (or perhaps see some other obvious 
problem).

Good luck!
Ryan


___
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] LEFT JOIN + WHERE / OR optimisation

[Keith Medcalf]

They are not the same.  Just as 5 - 3 is not the same as 1 + 1, even though 
both come up with the same result, 2. by happenstance.

Your "where" condition is effectively converted an OUTER JOIN into an INNER 
JOIN through artifice (and quite likely mistake).  If you *want* an inner join, 
use an inner join.  If you want an outer join, use an outer join.  Just because 
subtraction of two different numbers may have the same result as addition of 
two other numbers, does not meant that addition and subtraction are the same 
thing.

---
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 Dinu
>Sent: Thursday, 4 January, 2018 16:01
>To: sqlite-users@mailinglists.sqlite.org
>Subject: Re: [sqlite] LEFT JOIN + WHERE / OR optimisation
>
>Algebrically, having a non-null imperative lookup condition in the
>WHERE
>clause means you have a stronger predicate on the same subject (ALL
>MUST fit
>vs. ANY that fit).
>
>
>
>--
>Sent from: http://sqlite.1065341.n5.nabble.com/
>___
>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] LEFT JOIN + WHERE / OR optimisation

[Dinu]

Algebrically, having a non-null imperative lookup condition in the WHERE
clause means you have a stronger predicate on the same subject (ALL MUST fit
vs. ANY that fit).



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

[Dinu]

I think they are equivalent, if you look closer.

SELECT FROM a LEFT JOIN b ON a.x=b.x WHERE b.y=5 -is- equivalent to 
SELECT FROM a JOIN b ON a.x=b.x AND b.y=5
SELECT FROM a JOIN b WHERE a.x=b.x AND b.y=5
SELECT FROM a LEFT JOIN b ON a.x=b.x AND b.y=5 WHERE b.y IS NOT NULL

All the above are semantically equivalent. When there is only one LEFT JOIN,
the presence of any non-null non-alternative condition on the joined table
in the WHERE clause transforms it in an INNER join. There is no other way to
have a non-null value except if the row exists. The reciprocal is not true
of course.

I don't know how difficult it is to compute the -OR- closure, as it is more
difficult. But for an imperative non-null condition, I did expect the WHERE
condition to be ported to the ON lookup for optimisation.



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Efficient query to count number of leaves in a DAG.

[Shane Dev]

Hi David

I recommend using longer names than 1 letter for your aliases, what you
> save in typing you lose a couple times over again when wondering what "r"
> is or why "t" has anything to do with "nodes"
>

Fair enough. I tend to use shorts names to reduce the risk of typos. My
original node table was called "tasks". I tried to simplify the query for
this forum post but neglected to change the alias.

>
> In your CTE you're doing a 3 table join. There's no need to include the
> nodes table in there at all, you can get the node ID from the edge table.
> ...union all select e.child, top from r, edges as e where e.parent = r.id
> )...
>

You're right in this case. My original node table "tasks" had more columns
which I wanted in the final result set.

>
> The big thing though is in the where clause.
> where...and id not in (select parent from edges where parent = id)...
>

That was a sloppy mistake, I changed it to  ..and id not in (select parent
from edges)... but it was still very slow

>
> Old:
> sqlite> explain query plan with recursive r (id, top) as (select id, id
> from nodes union all select t.id, top from nodes as t, edges as e, r
> where e.parent = r.id and t.id = e.child) select top, count(*) from r
> where top != id and id not in (select parent from edges where parent = id)
> group by top;
>

CREATE VIEW v_count_leaves as with recursive r (id, top) as (select id, id
from nodes union all select t.id, top from nodes as t, edges as e, r where
e.parent = r.id and t.id = e.child) select top, count(*) from r where top
!= id and id not in (select parent from edges where parent = id) group by
top;

sqlite> select * from v_count_leaves where top=679;
top count(*)
679 2
Run Time: real 73.365 user 73.328125 sys 0.00


> New:
> sqlite> explain query plan with recursive r (id, top) as (select id, id
> from nodes union all select e.child, top from edges as e, r where e.parent
> = r.id) select top, count(*) from r where top != id and id not in (select
> parent from edges) group by top;
> Now give your modified query a go and let me know how it compares to what
> I came up with.
>

CREATE VIEW v_count_leaves_new as with recursive r (id, top) as (select id,
id from nodes union all select e.child, top from edges as e, r where
e.parent = r.id) select top, count(*) from r where top != id and id not in
(select parent from edges) group by top;

sqlite> select * from v_count_leaves_new where top=679;
top count(*)
679 2
Run Time: real 45.099 user 45.093750 sys 0.00

faster, but about 8 times slower than your query -

sqlite> select * from leafcounts where parent=679;
parent  leafCount
679 2
Run Time: real 5.639 user 5.640625 sys 0.00

and that is without the reverseEdges index.

I still don't understand why "leafcounts" is so much faster than
"v_count_leaves_new"




> -Original Message-
> From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of Shane Dev
> Sent: Wednesday, January 03, 2018 12:45 AM
> To: SQLite mailing list
> Subject: Re: [sqlite] Efficient query to count number of leaves in a DAG.
>
> Hi David,
>
> Nice work! your query is far quicker than mine- even without the
> reverseEdges index. I think you are right about the problem of potentially
> double counting leaves. There weren't any multi-parent nodes in my test
> data so I didn't notice this mistake.
>
> Could you please explain why your query is so much faster?
>
> On 2 January 2018 at 17:50, David Raymond 
> wrote:
>
> > I think you need a union there instead of a union all. Otherwise you're
> > double (or more) counting leaves where there is more than 1 path to get
> to
> > the leaf.
> >
> > I don't have a large dataset to test it on, but how about something like:
> >
> > create table nodes
> > (
> >   id integer primary key,
> >   description text
> > );
> >
> > create table edges
> > (
> >   parent int not null references nodes,
> >   child int not null references nodes,
> >   primary key (parent, child),
> >   check (parent != child)
> > ) without rowid;
> > create index reverseEdges on edges (child, parent);
> >
> > create view leafCounts as with recursive
> > leaves (id) as (
> >   select nodes.id
> >   from nodes left outer join edges
> >   on nodes.id = edges.parent
> >   where edges.parent is null
> > ),
> > paths (parent, child) as (
> >   select parent, child from edges
> >   union
> >   select paths.parent, edges.child
> >   from paths inner join edges
> >   on paths.child = edges.parent
> > )
> > select parent, count(*) as leafCount
> > from paths
> > where child in leaves
> > group by parent;
> >
> >
> > -Original Message-
> > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > On Behalf Of Shane Dev
> > Sent: Monday, January 01, 2018 11:14 AM
> > To: SQLite mailing list
> > Subject: [sqlite] Efficient query to count number of leaves in a DAG.
> >
> > Hi,
> >
> > I want to the count the number of leaves 

Re: [sqlite] new Error database disk image is malformed

[Jens Alfke]

(1) Try opening & querying the database file using the `sqlite3` tool, to 
verify whether it’s actually corrupted.
(2) If it is, check the documentation page “How To Corrupt A SQLite Database” 
and see if you’re making any of those mistakes.

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

Re: [sqlite] new Error database disk image is malformed

[R Smith]

On 2018/01/04 9:49 PM, Ron Barnes wrote:

Hi All,

I keep generating this error and I can't figure out why.  I have deleted and 
re-created the database but it keeps popping up.

Error

database disk image is malformed


Any ideas why?


This is a wild guess, but I'm thinking it's because the disk image is 
malformed...? :)


More seriously, what you probably meant to ask is: "Any idea why my file 
gets corrupt in this way?"


To which the best answer is: No idea, but here are some things that are 
typical culprits:

https://sqlite.org/howtocorrupt.html

Once you've checked all those, and you still are not sure what could 
cause the corruption, please write again but then include all 
information, OS, storage media, file system, use case, etc. and likely 
someone here would have had similar experience or run a similar system 
(or perhaps see some other obvious problem).


Good luck!
Ryan


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

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

[David Raymond]

The ON condition is used  the "add one result row for each row of the 
outer table where nothing matches the ON condition"
The WHERE condition is used  those rows are added.

Example with the basic "not in" type of outer join:

SQLite version 3.21.0 2017-10-24 18:55:49
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.

sqlite> create table a (x);

sqlite> create table b (x);

sqlite> insert into a values (1), (2), (3);

sqlite> insert into b values (2);

sqlite> select a.x from a left outer join b on a.x = b.x where b.x is null;
x
1
3

sqlite> select a.x from a left outer join b on a.x = b.x and b.x is null;
x
1
2
3

sqlite> select a.x from a left outer join b where a.x = b.x and b.x is null;

sqlite>

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Keith Medcalf
Sent: Thursday, January 04, 2018 2:53 PM
To: SQLite mailing list
Subject: Re: [sqlite] LEFT JOIN + WHERE / OR optimisation


They are not semantically equivalent.  join conditions attached to an outer 
join operation are not semantically equivalent to the same conditions being in 
the where clause.

In other words:

select a,b,c
  from a
  join b
  join c on a.a=b.b
 where c.c=b.d

is simply syntactic sugar for

select a,b,c
  from a, b, c
 where a.a=b.b
   and c.c=b.d;

In all cases the conditions in ON clauses of INNER JOINS are nothing more than 
WHERE clause filters.  You do not even have to have the tables used in the ON 
clause "referenced" at the point you refer to them.

the word "INNER JOIN" is syntactic sugar for a comma (,), and ON is sytactic 
sugar for the word WHERE (or AND).

However, for OUTER JOINS the conditions in the ON clause "glue themselves" to 
the OUTER JOIN operation and ARE NEITHER syntactically or symantically the same 
as WHERE clause conditions.

That is to say the behaviour observed is how it is designed to work and you 
expectations are misguided.

---
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 Dinu
>Sent: Thursday, 4 January, 2018 12:29
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] LEFT JOIN + WHERE / OR optimisation
>
>Hi all,
>I've ran into an optimisation problem with a double-left join that
>works as
>an "either" clause.
>
>The query is as follows:
>
>SELECT *
>FROM
>  a
>LEFT JOIN
>  b ON 
>LEFT JOIN
>  c ON 
>WHERE
>  b.someId IN (1,2,3) OR
>  c.someId IN (4,5)
>
>This results in a bloated execution plan:
>SEARCH a
>SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX
>SEARCH SUBQUERY 2 USING AUTOMATIC COVERING INDEX
>
>However, the semantically equivalent:
>SELECT *
>FROM
>  a
>LEFT JOIN
>  b ON  AND b.someId IN (1,2,3)
>LEFT JOIN
>  c ON AND c.someId IN (4,5)
>WHERE
>  b.someId IS NOT NULL OR
>  c.someId IS NOT NULL
>
>Gets the proper execution plan:
>SEARCH b
>SEARCH c
>EXECUTE LIST SUBQUERY
>
>
>
>--
>Sent from: http://sqlite.1065341.n5.nabble.com/
>___
>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] LEFT JOIN + WHERE / OR optimisation

[R Smith]

On 2018/01/04 9:28 PM, Dinu wrote:

Hi all,
I've ran into an optimisation problem with a double-left join that works as
an "either" clause.

The query is as follows:

SELECT *
FROM
   a
LEFT JOIN
   b ON 
LEFT JOIN
   c ON 
WHERE
   b.someId IN (1,2,3) OR
   c.someId IN (4,5)

This results in a bloated execution plan:
SEARCH a
SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX
SEARCH SUBQUERY 2 USING AUTOMATIC COVERING INDEX

However, the semantically equivalent:
SELECT *
FROM
   a
LEFT JOIN
   b ON  AND b.someId IN (1,2,3)
LEFT JOIN
   c ON AND c.someId IN (4,5)
WHERE
   b.someId IS NOT NULL OR
   c.someId IS NOT NULL

Gets the proper execution plan:
SEARCH b
SEARCH c
EXECUTE LIST SUBQUERY


These Queries are not equivalent, they cannot and should not have the 
same query plan.




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

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

[Keith Medcalf]

They are not semantically equivalent.  join conditions attached to an outer 
join operation are not semantically equivalent to the same conditions being in 
the where clause.

In other words:

select a,b,c
  from a
  join b
  join c on a.a=b.b
 where c.c=b.d

is simply syntactic sugar for

select a,b,c
  from a, b, c
 where a.a=b.b
   and c.c=b.d;

In all cases the conditions in ON clauses of INNER JOINS are nothing more than 
WHERE clause filters.  You do not even have to have the tables used in the ON 
clause "referenced" at the point you refer to them.

the word "INNER JOIN" is syntactic sugar for a comma (,), and ON is sytactic 
sugar for the word WHERE (or AND).

However, for OUTER JOINS the conditions in the ON clause "glue themselves" to 
the OUTER JOIN operation and ARE NEITHER syntactically or symantically the same 
as WHERE clause conditions.

That is to say the behaviour observed is how it is designed to work and you 
expectations are misguided.

---
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 Dinu
>Sent: Thursday, 4 January, 2018 12:29
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] LEFT JOIN + WHERE / OR optimisation
>
>Hi all,
>I've ran into an optimisation problem with a double-left join that
>works as
>an "either" clause.
>
>The query is as follows:
>
>SELECT *
>FROM
>  a
>LEFT JOIN
>  b ON 
>LEFT JOIN
>  c ON 
>WHERE
>  b.someId IN (1,2,3) OR
>  c.someId IN (4,5)
>
>This results in a bloated execution plan:
>SEARCH a
>SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX
>SEARCH SUBQUERY 2 USING AUTOMATIC COVERING INDEX
>
>However, the semantically equivalent:
>SELECT *
>FROM
>  a
>LEFT JOIN
>  b ON  AND b.someId IN (1,2,3)
>LEFT JOIN
>  c ON AND c.someId IN (4,5)
>WHERE
>  b.someId IS NOT NULL OR
>  c.someId IS NOT NULL
>
>Gets the proper execution plan:
>SEARCH b
>SEARCH c
>EXECUTE LIST SUBQUERY
>
>
>
>--
>Sent from: http://sqlite.1065341.n5.nabble.com/
>___
>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] new Error database disk image is malformed

[Ron Barnes]

Hi All,

I keep generating this error and I can't figure out why.  I have deleted and 
re-created the database but it keeps popping up.

Error

database disk image is malformed


Any ideas why?

-Ron


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

Re: [sqlite] LEFT JOIN + WHERE / OR optimisation

[Dinu]

Probably related:

Compound join with a left outer join generates different execution plans:

LEFT JOIN (
  b 
  JOIN c ON 
)
WHERE
  b.something = 5

vs.

LEFT JOIN (
  b 
  JOIN c ON  AND b.something = 5
)
WHERE
  b.something IS NOT NULL




--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Is WAL mode more robust against corruption?

[Chris Brody]

On Thu, Dec 28, 2017 at 3:59 PM, Simon Slavin  wrote:
> On 28 Dec 2017, at 8:10pm, Chris Brody  wrote:
>
>> [...]
>> But I wondered if WAL may be more robust against possible sqlite
>> corruption, in theory or in practice.

After sending I found a couple threads that *seem* to confirm that WAL
mode may be more reliable against possible corruption:
* http://sqlite.1065341.n5.nabble.com/WAL-mode-is-reliable-td41264.html
* https://marc.info/?l=sqlite-users=132052237302135=2

>> Any comments?
>
> WAL mode makes SQLite neither more nor less liable to corruption.  You should 
> not be seeing corruption in SQLite no matter what mode it’s in.

Thanks Simon for the response. I just found it today, unfortunately
directed to my spam folder. Makes sense, assuming there is nothing
wrong according to the "how to corrupt" checklist.

> If you want a PRAGMA to strengthen against corruption, try "PRAGMA 
> synchronous = FULL":
>
> 

I will guess you meant to try "PRAGMA synchronous = EXTRA" (seems to
be FULL by default). Maybe a good idea in a hybrid mobile application
environment.

> If you are seeing corruption, you might want to see this checklist.
>
> 

Right. The challenge for me is that my users are JavaScript
developers, often with very limited native platform experience.

> Given that you’re running on a phone/tablet, emphasis is on handling 
> suspension (backgrounding) and termination of the app properly.  There are 
> people here experienced with using SQLite on iPhone and Android who can 
> criticise your approach.
>
> If nothing obvious occurs to you, please post some details: which platform, 
> which circumstances, are you doing multi-thread or multi-process, do you 
> check result codes for your API calls, are you getting error results ?
>
> Simon.

In general I would expect that the app may be suspended or terminated
with no advance notice due to the hybrid environment they run in. I
think the Cordova/PhoneGap framework should give some form of
notification but I would not trust this mechanism to be 100% reliable.
I also want the apps to be robust against possible crashes, memory
issues for example. Right now I am thinking it would be safest to use
"PRAGMA synchronous = EXTRA" at the beginning, feedback would be
highly appreciated.

The hybrid JavaScript/native SQLite API component I maintain is
available in the following location:
https://github.com/litehelpers/Cordova-sqlite-storage

This component acts as a bridge between JavaScript and native code on
Android, iOS, and Windows. I am in the middle of some updates and the
documentation really needs cleanup at this point. Right now it is
using one background thread per database on Android, dynamic
background threads on iOS, no form of background threading on Windows.

I already fixed a couple possible causes of corruption on iOS as discussed in:
- https://github.com/litehelpers/Cordova-sqlite-storage/issues/703
- https://github.com/litehelpers/Cordova-sqlite-storage/issues/716

Reports of database corruption have been extremely rare in this
project but I just wanted to check how to make it as safe as possible.

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

Re: [sqlite] Can i check database corruption by select statement instead of quick_check?

[Keith Medcalf]

Of course you can:

select * from pragma_quick_check;
or
select * from pragma_integrity_check;



---
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 David Raymond
>Sent: Thursday, 4 January, 2018 09:59
>To: SQLite mailing list
>Subject: Re: [sqlite] Can i check database corruption by select
>statement instead of quick_check?
>
>You can't replace it with a select, no. There are plenty of different
>ways in which a database can be corrupt. When you run a select query
>you'll only find ones that are fatal errors, and only in places that
>deal with your query.
>
>The type of corruption you seem to have there is fatal, but there are
>plenty of ways for a database to be corrupt and yet still give
>results back from a select query. Quick_check and integrity_check
>look for all these different types of corruption.
>
>-Original Message-
>From: sqlite-users [mailto:sqlite-users-
>boun...@mailinglists.sqlite.org] On Behalf Of Junyoung Park
>Sent: Thursday, January 04, 2018 12:56 AM
>To: sqlite-users@mailinglists.sqlite.org
>Subject: [sqlite] Can i check database corruption by select statement
>instead of quick_check?
>
>Hi,
>
>I am currently testing to check database corrupted / malformed.
>Database size :1G
>table num : 1
>index num : 1
>
>The database can be malformed or normal.
>
>When I executing quick_check against a normal database, it takes
>about
>3 minutes from the time of the first execution since opening.
>If database is malformed, it will be checked faster than 3 minutes
>based on what part of the file is malformed.
>
>On the other hands, when i execute the "select max (rowid) from
>table_name" command on the database when it is malformed or normal,
>get results according to the situation and get results within about 1
>second.
>
>== TEST CASE 
>* malformed DB checking by select statement *
>sqlite> select max(rowid) from test_t;
>Run Time: real 0.003 user 0.00 sys 0.00
>Error: database disk image is malformed
>
>* normal DB checking by select statement *
>sqlite> select max(rowid) from test_t;
>4644298
>Run Time: real 0.074 user 0.00 sys 0.00
>
>* malformed DB checking by "quick_check" *
>sqlite> pragma quick_check;
>Run Time: real 0.000 user 0.00 sys 0.00
>Error: database disk image is malformed
>
>* normal DB checking by "quick_check" *
>sqlite> pragma quick_check;
>ok
>Run Time: real 201.368 user 1.35 sys 1.57
>===
>
>The main point of the question is that if the "quick_check" is slow
>according to the size of the database,
>in order to confirm the checking of malformed of the DB I would like
>to ask if I can replace it with the above select statement instead of
>"integrity_check" or "quick_check".
>
>Thank you very much.
>___
>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

[sqlite] LEFT JOIN + WHERE / OR optimisation

[Dinu]

Hi all,
I've ran into an optimisation problem with a double-left join that works as
an "either" clause.

The query is as follows:

SELECT *
FROM
  a
LEFT JOIN
  b ON 
LEFT JOIN 
  c ON 
WHERE
  b.someId IN (1,2,3) OR
  c.someId IN (4,5)

This results in a bloated execution plan:
SEARCH a
SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX
SEARCH SUBQUERY 2 USING AUTOMATIC COVERING INDEX

However, the semantically equivalent:
SELECT *
FROM
  a
LEFT JOIN
  b ON  AND b.someId IN (1,2,3)
LEFT JOIN 
  c ON AND c.someId IN (4,5)
WHERE
  b.someId IS NOT NULL OR
  c.someId IS NOT NULL

Gets the proper execution plan:
SEARCH b
SEARCH c
EXECUTE LIST SUBQUERY



--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Can i check database corruption by select statement instead of quick_check?

[R Smith]

On 2018/01/04 7:56 AM, Junyoung Park wrote:

Hi,

I am currently testing to check database corrupted / malformed.
Database size :1G



.
The main point of the question is that if the "quick_check" is slow
according to the size of the database,
in order to confirm the checking of malformed of the DB I would like
to ask if I can replace it with the above select statement instead of
"integrity_check" or "quick_check".


In addition to David's clear description, consider also that SELECT 
MAX(rowid) FROM... or similar things like SELECT COUNT(*) FROM... that 
the DB engine shortcuts to an optimization reading typically only a page 
or two from the best Index (with rowid the Index is implicit) and in no 
way checks any integrity.  (One could possibly deduce, should the 
operation not error out AND return quick, that the Index is present, at 
the least).


On the other hand, during an integrity check the engine needs to check a 
lot more. It needs to verify that the rows referenced by the Index, all 
Indexes in fact, are indeed present and pointing correctly. It further 
has to ensure the Index(es) itself, even if correctly pointing, do not 
violate any Index specific constraints (Uniqueness etc.), and a few more 
things - all of which simply illustrates that testing DB integrity, even 
the quick kind, is a laborious exercise and quick-scanning the Index in 
any way would defeat the purpose.


Why the frequent need to test for corruption? Do you often get 
corruption? If so, then perhaps solving that problem is a more worthy 
exercise?


Cheers,
Ryan

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

Re: [sqlite] Can i check database corruption by select statement instead of quick_check?

[David Raymond]

You can't replace it with a select, no. There are plenty of different ways in 
which a database can be corrupt. When you run a select query you'll only find 
ones that are fatal errors, and only in places that deal with your query.

The type of corruption you seem to have there is fatal, but there are plenty of 
ways for a database to be corrupt and yet still give results back from a select 
query. Quick_check and integrity_check look for all these different types of 
corruption.

-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Junyoung Park
Sent: Thursday, January 04, 2018 12:56 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] Can i check database corruption by select statement instead 
of quick_check?

Hi,

I am currently testing to check database corrupted / malformed.
Database size :1G
table num : 1
index num : 1

The database can be malformed or normal.

When I executing quick_check against a normal database, it takes about
3 minutes from the time of the first execution since opening.
If database is malformed, it will be checked faster than 3 minutes
based on what part of the file is malformed.

On the other hands, when i execute the "select max (rowid) from
table_name" command on the database when it is malformed or normal,
get results according to the situation and get results within about 1
second.

== TEST CASE 
* malformed DB checking by select statement *
sqlite> select max(rowid) from test_t;
Run Time: real 0.003 user 0.00 sys 0.00
Error: database disk image is malformed

* normal DB checking by select statement *
sqlite> select max(rowid) from test_t;
4644298
Run Time: real 0.074 user 0.00 sys 0.00

* malformed DB checking by "quick_check" *
sqlite> pragma quick_check;
Run Time: real 0.000 user 0.00 sys 0.00
Error: database disk image is malformed

* normal DB checking by "quick_check" *
sqlite> pragma quick_check;
ok
Run Time: real 201.368 user 1.35 sys 1.57
===

The main point of the question is that if the "quick_check" is slow
according to the size of the database,
in order to confirm the checking of malformed of the DB I would like
to ask if I can replace it with the above select statement instead of
"integrity_check" or "quick_check".

Thank you very much.
___
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] Can i check database corruption by select statement instead of quick_check?

[Junyoung Park]

Hi,

I am currently testing to check database corrupted / malformed.
Database size :1G
table num : 1
index num : 1

The database can be malformed or normal.

When I executing quick_check against a normal database, it takes about
3 minutes from the time of the first execution since opening.
If database is malformed, it will be checked faster than 3 minutes
based on what part of the file is malformed.

On the other hands, when i execute the "select max (rowid) from
table_name" command on the database when it is malformed or normal,
get results according to the situation and get results within about 1
second.

== TEST CASE 
* malformed DB checking by select statement *
sqlite> select max(rowid) from test_t;
Run Time: real 0.003 user 0.00 sys 0.00
Error: database disk image is malformed

* normal DB checking by select statement *
sqlite> select max(rowid) from test_t;
4644298
Run Time: real 0.074 user 0.00 sys 0.00

* malformed DB checking by "quick_check" *
sqlite> pragma quick_check;
Run Time: real 0.000 user 0.00 sys 0.00
Error: database disk image is malformed

* normal DB checking by "quick_check" *
sqlite> pragma quick_check;
ok
Run Time: real 201.368 user 1.35 sys 1.57
===

The main point of the question is that if the "quick_check" is slow
according to the size of the database,
in order to confirm the checking of malformed of the DB I would like
to ask if I can replace it with the above select statement instead of
"integrity_check" or "quick_check".

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