Re: [sqlite] Generating VDBE program

[Stephen Chrzanowski]

When you do add scripts, put them on pastebin or something of the sort as
this mailing list doesn't always allow attachments.  (I've seen one or two
slip through the cracks)

On Fri, Sep 19, 2014 at 1:20 AM, Prakash Premkumar 
wrote:

> Thanks a lot Richard for your help. I apologize for not including the
> script. I will make it a point to add the scripts from hereon.
>
> On Fri, Sep 19, 2014 at 10:30 AM, Richard Hipp  wrote:
>
> > You start by presenting us with a stand-alone script that we can run to
> see
> > your question.  See you are getting free help, you really should be
> > striving to make it easy for people to help you.  We don't have a script
> > from you, so I made one up.  I'm using:
> >
> > CREATE TABLE employee(eid INTEGER PRIMARY KEY, name TEXT);
> > CREATE TABLE location(lid INTEGER PRIMARY KEY, name TEXT);
> > CREATE TABLE company(cid INTEGER PRIMARY KEY, name TEXT);
> > .explain
> > EXPLAIN
> > SELECT *
> >   FROM employee, company, location
> >  WHERE location.name=company.name
> >AND location.name=employee.name;
> >
> > Be sure to indent your code clearly so that people and understand it more
> > easily.
> >
> > For the trunk of SQLite, the first few instructions are:
> >
> > addr  opcode p1p2p3p4 p5  comment
> >   -        -  --  -
> > 0 Init   0 44000  Start at 44
> > 1 OpenRead   0 2 0 2  00  root=2 iDb=0;
> > employee
> > 2 OpenRead   2 3 0 2  00  root=3 iDb=0;
> > location
> > 3 OpenRead   1 4 0 2  00  root=4 iDb=0;
> > company
> > 4 Explain0 0 0 SCAN TABLE employee
> > 00
> > 5 Rewind 0 42000
> > 6   Once   0 14000
> > 7   OpenAutoindex  3 2 0 k(2,nil,nil)   00  nColumn=2;
> for
> > location
> > 8   Rewind 2 14000
> > 9 Column 2 1 200  r[2]=
> > location.name
> > 10Rowid  2 3 000  r[3]=rowid
> > 11MakeRecord 2 2 100
> > r[1]=mkrec(r[2..3])
> > 12IdxInsert  3 1 010  key=r[1]
> > 13  Next   2 9 003
> >
> > I think you are interested in knowing where the P3 operand (the output
> > register number) for instruction 9 comes from.  (Note that the program
> > shown above might be slightly different in whatever version of SQLite you
> > are running.)
> >
> > The way you find this out is to first compile sqlite3 using
> > -DSQLITE_DEBUG.  Then add the statement:
> >
> >  PRAGMA vdbe_addoptrace=ON;
> >
> > Right before the "EXPLAIN" in the script.  That pragma (only available
> when
> > SQLite is compiled with -DSQLITE_DEBUG) causes SQLite to print a message
> on
> > the screen every time it generates a new opcode.  Next, run sqlite3 in a
> > debugger and set a breakpoint on the "test_addop_breakpoint" procedure.
> > (This is a dummy procedure created specifically to give you a place to
> set
> > a breakpoint.)  Then run your script.  Continue past the first few
> > breakpoints until the "Column" instruction is emitted.  Now you can
> examine
> > the call stack to figure out exactly how that opcode was generated.
> >
> > It appears that the output register 2 comes from here:
> >
> > http://www.sqlite.org/src/artifact/fae81cc2eb14b?ln=812-813
> >
> > Specifically the last parameter.  regBase+j.  You can continue to follow
> > the stack to figure out where regBase was computed.  There are comments
> on
> > each function that try to explain what that particular function is doing.
> >
> >
> >
> >
> >
> >
> > --
> > D. Richard Hipp
> > d...@sqlite.org
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Number of registers/Mem in sqlite Vdbe

[Prakash Premkumar]

Can you kindly tell me where in the source code is the number of registers
for Vdbe allocated ?

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

Re: [sqlite] Generating VDBE program

[Prakash Premkumar]

Thanks a lot Richard for your help. I apologize for not including the
script. I will make it a point to add the scripts from hereon.

On Fri, Sep 19, 2014 at 10:30 AM, Richard Hipp  wrote:

> You start by presenting us with a stand-alone script that we can run to see
> your question.  See you are getting free help, you really should be
> striving to make it easy for people to help you.  We don't have a script
> from you, so I made one up.  I'm using:
>
> CREATE TABLE employee(eid INTEGER PRIMARY KEY, name TEXT);
> CREATE TABLE location(lid INTEGER PRIMARY KEY, name TEXT);
> CREATE TABLE company(cid INTEGER PRIMARY KEY, name TEXT);
> .explain
> EXPLAIN
> SELECT *
>   FROM employee, company, location
>  WHERE location.name=company.name
>AND location.name=employee.name;
>
> Be sure to indent your code clearly so that people and understand it more
> easily.
>
> For the trunk of SQLite, the first few instructions are:
>
> addr  opcode p1p2p3p4 p5  comment
>   -        -  --  -
> 0 Init   0 44000  Start at 44
> 1 OpenRead   0 2 0 2  00  root=2 iDb=0;
> employee
> 2 OpenRead   2 3 0 2  00  root=3 iDb=0;
> location
> 3 OpenRead   1 4 0 2  00  root=4 iDb=0;
> company
> 4 Explain0 0 0 SCAN TABLE employee
> 00
> 5 Rewind 0 42000
> 6   Once   0 14000
> 7   OpenAutoindex  3 2 0 k(2,nil,nil)   00  nColumn=2; for
> location
> 8   Rewind 2 14000
> 9 Column 2 1 200  r[2]=
> location.name
> 10Rowid  2 3 000  r[3]=rowid
> 11MakeRecord 2 2 100
> r[1]=mkrec(r[2..3])
> 12IdxInsert  3 1 010  key=r[1]
> 13  Next   2 9 003
>
> I think you are interested in knowing where the P3 operand (the output
> register number) for instruction 9 comes from.  (Note that the program
> shown above might be slightly different in whatever version of SQLite you
> are running.)
>
> The way you find this out is to first compile sqlite3 using
> -DSQLITE_DEBUG.  Then add the statement:
>
>  PRAGMA vdbe_addoptrace=ON;
>
> Right before the "EXPLAIN" in the script.  That pragma (only available when
> SQLite is compiled with -DSQLITE_DEBUG) causes SQLite to print a message on
> the screen every time it generates a new opcode.  Next, run sqlite3 in a
> debugger and set a breakpoint on the "test_addop_breakpoint" procedure.
> (This is a dummy procedure created specifically to give you a place to set
> a breakpoint.)  Then run your script.  Continue past the first few
> breakpoints until the "Column" instruction is emitted.  Now you can examine
> the call stack to figure out exactly how that opcode was generated.
>
> It appears that the output register 2 comes from here:
>
> http://www.sqlite.org/src/artifact/fae81cc2eb14b?ln=812-813
>
> Specifically the last parameter.  regBase+j.  You can continue to follow
> the stack to figure out where regBase was computed.  There are comments on
> each function that try to explain what that particular function is doing.
>
>
>
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Generating VDBE program

[Richard Hipp]

You start by presenting us with a stand-alone script that we can run to see
your question.  See you are getting free help, you really should be
striving to make it easy for people to help you.  We don't have a script
from you, so I made one up.  I'm using:

CREATE TABLE employee(eid INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE location(lid INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE company(cid INTEGER PRIMARY KEY, name TEXT);
.explain
EXPLAIN
SELECT *
  FROM employee, company, location
 WHERE location.name=company.name
   AND location.name=employee.name;

Be sure to indent your code clearly so that people and understand it more
easily.

For the trunk of SQLite, the first few instructions are:

addr  opcode p1p2p3p4 p5  comment
  -        -  --  -
0 Init   0 44000  Start at 44
1 OpenRead   0 2 0 2  00  root=2 iDb=0;
employee
2 OpenRead   2 3 0 2  00  root=3 iDb=0;
location
3 OpenRead   1 4 0 2  00  root=4 iDb=0;
company
4 Explain0 0 0 SCAN TABLE employee
00
5 Rewind 0 42000
6   Once   0 14000
7   OpenAutoindex  3 2 0 k(2,nil,nil)   00  nColumn=2; for
location
8   Rewind 2 14000
9 Column 2 1 200  r[2]=
location.name
10Rowid  2 3 000  r[3]=rowid
11MakeRecord 2 2 100
r[1]=mkrec(r[2..3])
12IdxInsert  3 1 010  key=r[1]
13  Next   2 9 003

I think you are interested in knowing where the P3 operand (the output
register number) for instruction 9 comes from.  (Note that the program
shown above might be slightly different in whatever version of SQLite you
are running.)

The way you find this out is to first compile sqlite3 using
-DSQLITE_DEBUG.  Then add the statement:

 PRAGMA vdbe_addoptrace=ON;

Right before the "EXPLAIN" in the script.  That pragma (only available when
SQLite is compiled with -DSQLITE_DEBUG) causes SQLite to print a message on
the screen every time it generates a new opcode.  Next, run sqlite3 in a
debugger and set a breakpoint on the "test_addop_breakpoint" procedure.
(This is a dummy procedure created specifically to give you a place to set
a breakpoint.)  Then run your script.  Continue past the first few
breakpoints until the "Column" instruction is emitted.  Now you can examine
the call stack to figure out exactly how that opcode was generated.

It appears that the output register 2 comes from here:

http://www.sqlite.org/src/artifact/fae81cc2eb14b?ln=812-813

Specifically the last parameter.  regBase+j.  You can continue to follow
the stack to figure out where regBase was computed.  There are comments on
each function that try to explain what that particular function is doing.






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

Re: [sqlite] Generating VDBE program

[Prakash Premkumar]

The schema is as follows :

create table employee (name text,age int);
create table location (name text,addr text);
create table company (name text,cname text);

Thanks

On Fri, Sep 19, 2014 at 10:13 AM, Richard Hipp  wrote:

> On Fri, Sep 19, 2014 at 12:35 AM, Prakash Premkumar <
> prakash.p...@gmail.com>
> wrote:
>
> > Let's take this example
> >
> > explain select * from employee,company,location where location.name=
> > company.name and location.name=employee.name;
> >
>
> Also please include the schema for your database.
>
>
>
> >
> > addr  opcode p1p2p3p4 p5  comment
> >
> >   -        -  --  -
> >
> > 0 Init   0 46000  Start at 46
> >
> > 1 OpenRead   0 2 0 2  00  root=2 iDb=0;
> > employee
> >
> > 2 OpenRead   2 3 0 2  00  root=3 iDb=0;
> > location
> >
> > 3 OpenRead   1 4 0 2  00  root=4 iDb=0;
> > company
> >
> > 4 Explain0 0 0 SCAN TABLE employee  00
> >
> >
> > 5 Rewind 0 44000
> >
> > 6   Once   0 15000
> >
> > 7   OpenAutoindex  3 3 0 k(3,nil,nil,nil)  00  nColumn=3;
> > for location
> >
> > 8   Rewind 2 15000
> >
> > 9 Column 2 0 200  r[2]=
> > location.name
> >
> > 10Column 2 1 300
> > r[3]=location.addr
> >
> > 11Rowid  2 4 000  r[4]=rowid
> >
> > 12MakeRecord 2 3 100
> > r[1]=mkrec(r[2..4])
> >
> > 13IdxInsert  3 1 010  key=r[1]
> >
> > 14  Next   2 9 003
> >
> > 15  Explain0 1 2 SEARCH TABLE location USING
> > AUTOMATIC COVERING INDEX (name=?)  00
> >
> > 16  Column 0 0 500  r[5]=
> > employee.name
> >
> > 17  IsNull 5 43000  if r[5]==NULL
> > goto 43
> >
> > 18  SeekGE 3 435 1  00  key=r[5]
> >
> > 19IdxGT  3 435 1  00  key=r[5]
> >
> > 20Once   1 29000
> >
> > 21OpenAutoindex  4 3 0 k(3,nil,nil,nil)  00
> nColumn=3;
> > for company
> >
> > 22Rewind 1 29000
> >
> > 23  Column 1 0 200  r[2]=
> > company.name
> >
> > 24  Column 1 1 300
> > r[3]=company.cname
> >
> > 25  Rowid  1 4 000
> r[4]=rowid
> >
> >
> > 26  MakeRecord 2 3 100
> > r[1]=mkrec(r[2..4])
> >
> > 27  IdxInsert  4 1 010  key=r[1]
> >
> >
> > 28Next   1 23003
> >
> > 29Explain0 2 1 SEARCH TABLE company USING
> > AUTOMATIC COVERING INDEX (name=?)  00
> >
> > 30Column 3 0 600  r[6]=
> > location.name
> >
> > 31IsNull 6 42000  if
> r[6]==NULL
> > goto 42
> >
> > 32SeekGE 4 426 1  00  key=r[6]
> >
> > 33  IdxGT  4 426 1  00  key=r[6]
> >
> >
> > 34  Copy   5 7 000  r[7]=r[5]
> >
> >
> > 35  Column 0 1 800
> > r[8]=employee.age
> >
> > 36  Column 4 0 900  r[9]=
> > company.name
> >
> > 37  Column 4 1 10   00
> > r[10]=company.cname
> >
> > 38  Copy   6 11000
> r[11]=r[6]
> >
> >
> > 39  Column 3 1 12   00
> > r[12]=location.addr
> >
> > 40  ResultRow  7 6 000
> > output=r[7..12]
> >
> > 41Next   4 33000
> >
> > 42  Next   3 19000
> >
> > 43Next   0 6 001
> >
> > 44Close  0 0 000
> >
> > 45Halt   0 0 000
> >
> > 46Transaction0 0 3 0  01
> >
> > 47TableLock  0 2 0 employee   00  iDb=0 root=2
> > write=0
> >
> > 48TableLock  0 3 0 location   00  iDb=0 root=3
> > write=0
> >
> > 49TableLock  0 4 0 company00  iDb=0 root=4
> > write=0
> >
> > 50Goto   0 1 0

Re: [sqlite] Generating VDBE program

[Prakash Premkumar]

The opcode I'm interested in is
10 column 2 1 3 00
 On 19 Sep 2014 10:11, "Richard Hipp"  wrote:

> On Fri, Sep 19, 2014 at 12:35 AM, Prakash Premkumar <
> prakash.p...@gmail.com>
> wrote:
>
> > Let's take this example
> >
> > explain select * from employee,company,location where location.name=
> > company.name and location.name=employee.name;
> >
> >
> > How's the register for the highlighted opcode allocated and how does the
> > virtual machine remember this allocation ?
> >
> >
> This mailing list uses plain text (not HTML) for maximal portability.  That
> means that there is no highlighting.
>
> Please provide the opcode address and the operand (P1, P2, P3, etc) that
> you are interested in.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Generating VDBE program

[Richard Hipp]

On Fri, Sep 19, 2014 at 12:35 AM, Prakash Premkumar 
wrote:

> Let's take this example
>
> explain select * from employee,company,location where location.name=
> company.name and location.name=employee.name;
>

Also please include the schema for your database.



>
> addr  opcode p1p2p3p4 p5  comment
>
>   -        -  --  -
>
> 0 Init   0 46000  Start at 46
>
> 1 OpenRead   0 2 0 2  00  root=2 iDb=0;
> employee
>
> 2 OpenRead   2 3 0 2  00  root=3 iDb=0;
> location
>
> 3 OpenRead   1 4 0 2  00  root=4 iDb=0;
> company
>
> 4 Explain0 0 0 SCAN TABLE employee  00
>
>
> 5 Rewind 0 44000
>
> 6   Once   0 15000
>
> 7   OpenAutoindex  3 3 0 k(3,nil,nil,nil)  00  nColumn=3;
> for location
>
> 8   Rewind 2 15000
>
> 9 Column 2 0 200  r[2]=
> location.name
>
> 10Column 2 1 300
> r[3]=location.addr
>
> 11Rowid  2 4 000  r[4]=rowid
>
> 12MakeRecord 2 3 100
> r[1]=mkrec(r[2..4])
>
> 13IdxInsert  3 1 010  key=r[1]
>
> 14  Next   2 9 003
>
> 15  Explain0 1 2 SEARCH TABLE location USING
> AUTOMATIC COVERING INDEX (name=?)  00
>
> 16  Column 0 0 500  r[5]=
> employee.name
>
> 17  IsNull 5 43000  if r[5]==NULL
> goto 43
>
> 18  SeekGE 3 435 1  00  key=r[5]
>
> 19IdxGT  3 435 1  00  key=r[5]
>
> 20Once   1 29000
>
> 21OpenAutoindex  4 3 0 k(3,nil,nil,nil)  00  nColumn=3;
> for company
>
> 22Rewind 1 29000
>
> 23  Column 1 0 200  r[2]=
> company.name
>
> 24  Column 1 1 300
> r[3]=company.cname
>
> 25  Rowid  1 4 000  r[4]=rowid
>
>
> 26  MakeRecord 2 3 100
> r[1]=mkrec(r[2..4])
>
> 27  IdxInsert  4 1 010  key=r[1]
>
>
> 28Next   1 23003
>
> 29Explain0 2 1 SEARCH TABLE company USING
> AUTOMATIC COVERING INDEX (name=?)  00
>
> 30Column 3 0 600  r[6]=
> location.name
>
> 31IsNull 6 42000  if r[6]==NULL
> goto 42
>
> 32SeekGE 4 426 1  00  key=r[6]
>
> 33  IdxGT  4 426 1  00  key=r[6]
>
>
> 34  Copy   5 7 000  r[7]=r[5]
>
>
> 35  Column 0 1 800
> r[8]=employee.age
>
> 36  Column 4 0 900  r[9]=
> company.name
>
> 37  Column 4 1 10   00
> r[10]=company.cname
>
> 38  Copy   6 11000  r[11]=r[6]
>
>
> 39  Column 3 1 12   00
> r[12]=location.addr
>
> 40  ResultRow  7 6 000
> output=r[7..12]
>
> 41Next   4 33000
>
> 42  Next   3 19000
>
> 43Next   0 6 001
>
> 44Close  0 0 000
>
> 45Halt   0 0 000
>
> 46Transaction0 0 3 0  01
>
> 47TableLock  0 2 0 employee   00  iDb=0 root=2
> write=0
>
> 48TableLock  0 3 0 location   00  iDb=0 root=3
> write=0
>
> 49TableLock  0 4 0 company00  iDb=0 root=4
> write=0
>
> 50Goto   0 1 000
>
>
> How's the register for the highlighted opcode allocated and how does the
> virtual machine remember this allocation ?
>
> Thanks for your help
>
> On Fri, Sep 19, 2014 at 9:46 AM, Richard Hipp  wrote:
>
> > On Fri, Sep 19, 2014 at 12:10 AM, Prakash Premkumar <
> > prakash.p...@gmail.com>
> > wrote:
> >
> > > Thanks for your reply. I would like to know how the register allocation
> > > decision is made and the part of the code which does it . can you
> please
> > > help me find that? Thanks
> > >
> >
> > Which register.
> >
> > Show me

Re: [sqlite] Generating VDBE program

[Richard Hipp]

On Fri, Sep 19, 2014 at 12:35 AM, Prakash Premkumar 
wrote:

> Let's take this example
>
> explain select * from employee,company,location where location.name=
> company.name and location.name=employee.name;
>
>
> How's the register for the highlighted opcode allocated and how does the
> virtual machine remember this allocation ?
>
>
This mailing list uses plain text (not HTML) for maximal portability.  That
means that there is no highlighting.

Please provide the opcode address and the operand (P1, P2, P3, etc) that
you are interested in.

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

Re: [sqlite] Generating VDBE program

[Prakash Premkumar]

Let's take this example

explain select * from employee,company,location where location.name=
company.name and location.name=employee.name;

addr  opcode p1p2p3p4 p5  comment

  -        -  --  -

0 Init   0 46000  Start at 46

1 OpenRead   0 2 0 2  00  root=2 iDb=0;
employee

2 OpenRead   2 3 0 2  00  root=3 iDb=0;
location

3 OpenRead   1 4 0 2  00  root=4 iDb=0;
company

4 Explain0 0 0 SCAN TABLE employee  00


5 Rewind 0 44000

6   Once   0 15000

7   OpenAutoindex  3 3 0 k(3,nil,nil,nil)  00  nColumn=3;
for location

8   Rewind 2 15000

9 Column 2 0 200  r[2]=
location.name

10Column 2 1 300
r[3]=location.addr

11Rowid  2 4 000  r[4]=rowid

12MakeRecord 2 3 100
r[1]=mkrec(r[2..4])

13IdxInsert  3 1 010  key=r[1]

14  Next   2 9 003

15  Explain0 1 2 SEARCH TABLE location USING
AUTOMATIC COVERING INDEX (name=?)  00

16  Column 0 0 500  r[5]=
employee.name

17  IsNull 5 43000  if r[5]==NULL
goto 43

18  SeekGE 3 435 1  00  key=r[5]

19IdxGT  3 435 1  00  key=r[5]

20Once   1 29000

21OpenAutoindex  4 3 0 k(3,nil,nil,nil)  00  nColumn=3;
for company

22Rewind 1 29000

23  Column 1 0 200  r[2]=
company.name

24  Column 1 1 300
r[3]=company.cname

25  Rowid  1 4 000  r[4]=rowid


26  MakeRecord 2 3 100
r[1]=mkrec(r[2..4])

27  IdxInsert  4 1 010  key=r[1]


28Next   1 23003

29Explain0 2 1 SEARCH TABLE company USING
AUTOMATIC COVERING INDEX (name=?)  00

30Column 3 0 600  r[6]=
location.name

31IsNull 6 42000  if r[6]==NULL
goto 42

32SeekGE 4 426 1  00  key=r[6]

33  IdxGT  4 426 1  00  key=r[6]


34  Copy   5 7 000  r[7]=r[5]


35  Column 0 1 800
r[8]=employee.age

36  Column 4 0 900  r[9]=
company.name

37  Column 4 1 10   00
r[10]=company.cname

38  Copy   6 11000  r[11]=r[6]


39  Column 3 1 12   00
r[12]=location.addr

40  ResultRow  7 6 000
output=r[7..12]

41Next   4 33000

42  Next   3 19000

43Next   0 6 001

44Close  0 0 000

45Halt   0 0 000

46Transaction0 0 3 0  01

47TableLock  0 2 0 employee   00  iDb=0 root=2
write=0

48TableLock  0 3 0 location   00  iDb=0 root=3
write=0

49TableLock  0 4 0 company00  iDb=0 root=4
write=0

50Goto   0 1 000


How's the register for the highlighted opcode allocated and how does the
virtual machine remember this allocation ?

Thanks for your help

On Fri, Sep 19, 2014 at 9:46 AM, Richard Hipp  wrote:

> On Fri, Sep 19, 2014 at 12:10 AM, Prakash Premkumar <
> prakash.p...@gmail.com>
> wrote:
>
> > Thanks for your reply. I would like to know how the register allocation
> > decision is made and the part of the code which does it . can you please
> > help me find that? Thanks
> >
>
> Which register.
>
> Show me a specific VDBE program and a specific instruction in that program
> and a specific operand on that instruction, and I can point you to the
> place in the code where that register is allocated.
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/m

Re: [sqlite] Generating VDBE program

[Simon Slavin]

On 19 Sep 2014, at 5:10am, Prakash Premkumar  wrote:

> Thanks for your reply. I would like to know how the register allocation
> decision is made and the part of the code which does it . can you please
> help me find that? Thanks

Prakesh, perhaps it would help to read this document:



And then download and browse the SQLite source code from this file

sqlite-src-3080600.zip

on this page:



You should be able to work out which pieces of source code are involved in the 
virtual machine.

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

Re: [sqlite] Generating VDBE program

[Richard Hipp]

On Fri, Sep 19, 2014 at 12:10 AM, Prakash Premkumar 
wrote:

> Thanks for your reply. I would like to know how the register allocation
> decision is made and the part of the code which does it . can you please
> help me find that? Thanks
>

Which register.

Show me a specific VDBE program and a specific instruction in that program
and a specific operand on that instruction, and I can point you to the
place in the code where that register is allocated.


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

Re: [sqlite] Generating VDBE program

[Prakash Premkumar]

Thanks for your reply. I would like to know how the register allocation
decision is made and the part of the code which does it . can you please
help me find that? Thanks
On 18 Sep 2014 20:20, "Richard Hipp"  wrote:

> On Thu, Sep 18, 2014 at 10:10 AM, Prakash Premkumar <
> prakash.p...@gmail.com>
> wrote:
>
> > Which function in the sqlite generates the Vdbe program for the given
> > query?
> >
> > Let's take an example of the join of two tables. Table A has n columns
> and
> > Table B has m columns, and the result has n+m columns.
> >
> > During the execution of the Vdbe program the values from these columns
> get
> > copied to the Mem struct randomly (when OP_Column opcode is formed) and
> > finally when the OP_ResultRow is executed, the Mem's are rearranged so
> that
> > the columns of each table are contiguous.
> >
> > How does sqlite know in which Mem the value of a column is stored and how
> > does it finally rearrange the Mems contiguously?
> > Can you point me to the portion of the source code which makes this
> > decision?
> >
> > Can you kindly explain me the concept?
> >
>
> SQLite is a compiler.  It transforms a high-level language (SQL) into
> machine code (or in this case VDBE code, which is very similar to machine
> code).   In this sense, SQLite is similar to GCC.  GCC translates C code
> into x86 assembly language.  SQLite translates SQL into VDBE assembly
> language.  The source and target languages are different, but the
> fundamental techniques are the same.
>
> When you ask "how does sqlite know which Mem the value of a column is
> stored?" that is the same as asking "how does gcc know which processor
> registers the value of a variable is stored?".
>
> I recommend that you first study up on compiler construction.  After you
> understand how compilers work, SQLite will likely make a lot more sense to
> you.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[FarSight Data Systems]

Thanks, I'll keep it in min.  In this case, howevery, I don't think that will 
be an issue.  All of 
the names are from American published pulp magazines,  writers, artists, and 
editors.

Mark

On Friday, September 19, 2014 02:02:30 AM Simon Slavin wrote:
> On 19 Sep 2014, at 1:15am, Mark Halegua  wrote:
> > that resolved it.  I didn't know you needed to put the desc with both
> > columns.
> > 
> > It means another table I had thought was properly ordered wasn't.
> > 
> > Thank you.
> 
> You're welcome.  Glad you figured it out.
> 
> By the way I wanted to warn you about starting any project with first name,
> middle name and last name fields.  This leads to problems, and I would go
> to some lengths to avoid it if possible.  It would be better to provide two
> columns:
> 
> name(their name, however they want it to be shown)
> nameInSortOrder (their name, in whatever order you feel it should be sorted)
> 
> For the second field your name might appear as "Halegua, Mark" and someone
> with a middle name might appear as "Smith, Mark Edward".  The comma is
> needed because some people have a surname which is two separate words, e.g.
> Patrick Nielsen Hayden.  Given the way SQLite works you would want to
> declare the field nameInSortOrder as having COLLATE NOCASE.
> 
> This is especially important if you are storing names which don't all have
> Western-style 'given-name surname' format.  For instance, you may see these
> words between the first and last parts of people's names: "bin", "ben",
> "ibn", "bas", "bat", "O'", "al-", "de" "van de", "Fitz".  They"re not
> middle names.  They mean "son of" or "daughter of" or "from" or other
> things.  They should definitely not be capitalised, except for "O'".  And
> you don't sort on them at all.
> 
> Similarly, surnames beginning with 'Mac' or 'Mc' should not be sorted
> together, not as if the name begins with the letters 'MAC'.  It's a
> convention that they all be sorted at the beginning of the 'M' listings,
> ignoring the difference between "Mc" and "Mac".
> 
> For a longer list of reasons, see this article:
> 
>  mes/>
> 
> and for those who like that, there's a similar
> 
> 
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-- 

Mark S. Halegua
718-360-1712
917-686-8794
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Performance regression between 3.7.17 and 3.8.0.2?

[Richard Hipp]

On Thu, Sep 18, 2014 at 8:54 AM, Merike  wrote:

> Hi everyone,
>
> Since upgrading to Kubuntu 14.04 I've had an issue with Quassel irc
> client being slow on startup when it retrieves backlog from database.
> I've tracked it down to different sqlite version being installed.
> Previously I had 3.7.17 and now have 3.8.2. I've tried various versions
> from https://launchpad.net/ubuntu/trusty/+source/sqlite3/+builds and can
> only pinpoint it to between 3.7.17 and 3.8.0.2 because there doesn't
> appear to be intermediate builds.
>

Thanks again for the regression report.  This problem is now fixed on the
SQLite trunk.  See
http://www.sqlite.org/src/info/72727b68cd0796?dc=22 for the check-in that
fixes the problem.

If all you want to know is that the problem has been fixed, you may stop
reading now.  If you are curious about the cause of the problem was, you
may continue reading.

Here is a simplified test case that illustrates the problem:

CREATE TABLE t1(x INTEGER PRIMARY KEY, a, b);
CREATE INDEX t1a ON t1(a);
CREATE INDEX t1ab ON t1(a,b);
explain query plan
SELECT * FROM t1 WHERE a=?1 ORDER BY x;

The query could use either index, t1a or t1ab, to lookup the appropriate
rows based on "WHERE a=?1".  If index t1a is used for the lookup, then the
rows will come out ordered by x, since every index contains the primary key
after the indexed columns.  It is as if the t1a index was really over two
columns, a and x, and the t1ab index was over three columns, a, b, and x.
So when t1a is used for lookup, SQLite searches for the first entry in the
index where a=?1 then it starts reading successive entries, as long as the
a=?1 condition holds, to find the primary key and hence to look up the
table content.  But notice how doing that extracts the rows in ascending
"x" order - exactly as requested by the ORDER BY clause.  SQLite recognizes
this and suppresses the sort operation since the rows are going to come out
in the correct order naturally.

But if t1ab is used to lookup the rows, there is that pesky 'b" column in
between the "a" which is constrained by "WHERE a=?" and the "x" column.
There might be two or more different "b" values for the same "a", and hence
the "x" values will not necessarily be in the right order.  So the output
must be sorted by a separate post-processing step.  That is much slower.

Normally, the query planner looks at all possible indexes and tries each
one to see which one will generate a plan with the least amount of work.
In that case, t1a would be selected over t1ab since t1a can omit the sort
operation at the end, which is usually one of the most expensive parts of a
query.  Unfortunately, the query planner (incorrectly) rejected the t1a
index in favor of t1ab very early in the query planning process.  There is
a prefilter that asks whether or not a index might give output in the ORDER
BY order naturally.  Such indexes are given special protection from early
rejection so that they can be thoroughly analyzed.  But in this case,
because the ORDER BY was on the INTEGER PRIMARY KEY rather than on a key
column, that prefilter failed to recognize that t1a might naturally render
the output in the desired order.  Hence, t1a ended up being rejected in
favor of t1ab, which is a covering index and hence involves less lookup
work.

The one-line change shown above fixes the prefilter so that it recognizes
that t1a might be useful in implementing the ORDER BY clause, thus giving
t1a protection from being overridden by t1ab.  Both t1a and t1ab indexes
have to compete in the final selection process which t1a clearly wins
because the satisfies the ORDER BY clause without having to do a separate
sort.




>
> I don't know what exact queries Quassel runs but most likely (after
> looking at some Quassel source on github) it's something like the
> following for a single channel:
> SELECT messageid, time, type, flags, sender, message
> FROM backlog
> JOIN sender ON backlog.senderid = sender.senderid
> WHERE bufferid = 102
> ORDER BY messageid DESC
> LIMIT 100;
>
> When I run this query on 3.7.17 it returns nearly instantly. When I do
> it on 3.8.0.2 or 3.8.6 which I also tried then it takes about 4 seconds
> on my not so fast spinning drive laptop. I've posted example database
> (minimized from original) at
> https://docs.google.com/file/d/0Bzx3gCDqfzVdcDNhdzlfVlh4ZTA/. Could
> someone either confirm or prove false that this query has become slower
> with newer versions?
>
> Merike
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] Performance regression between 3.7.17 and 3.8.0.2?

[Richard Hipp]

On Thu, Sep 18, 2014 at 8:54 AM, Merike  wrote:

> Hi everyone,
>
> Since upgrading to Kubuntu 14.04 I've had an issue with Quassel irc
> client being slow on startup when it retrieves backlog from database.
> I've tracked it down to different sqlite version being installed.
> Previously I had 3.7.17 and now have 3.8.2. I've tried various versions
> from https://launchpad.net/ubuntu/trusty/+source/sqlite3/+builds and can
> only pinpoint it to between 3.7.17 and 3.8.0.2 because there doesn't
> appear to be intermediate builds.
>

Thanks for the performance regression report.

A simple script to reproduce the problem in the latest SQLite is as follows:

CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER, c INTEGER, d INTEGER);
CREATE INDEX t1b ON t1(b);
CREATE TABLE t2(x INTEGER PRIMARY KEY, y);
explain query plan
SELECT * FROM t1, t2 WHERE x=c AND b=?1 ORDER BY a;
.print --
CREATE INDEX t1bd ON t1(b,d);
explain query plan
SELECT * FROM t1, t2 WHERE x=c AND b=?1 ORDER BY a;


If you copy/paste the above script into an sqlite3 command-line shell,
you'll see that, for some reason, the query planner decides to sort
manually rather than using an index for sorting after the t1bd index is
added. We are still investigating to try understand why that is.

Please note that if you run ANALYZE on your sample database, the query is
fast again.  On my (Ubuntu) desktop, the query takes 648 milliseconds
before being analyzed and 600 microseconds afterwards - a 1000-fold speedup.



>
> I don't know what exact queries Quassel runs but most likely (after
> looking at some Quassel source on github) it's something like the
> following for a single channel:
> SELECT messageid, time, type, flags, sender, message
> FROM backlog
> JOIN sender ON backlog.senderid = sender.senderid
> WHERE bufferid = 102
> ORDER BY messageid DESC
> LIMIT 100;
>
> When I run this query on 3.7.17 it returns nearly instantly. When I do
> it on 3.8.0.2 or 3.8.6 which I also tried then it takes about 4 seconds
> on my not so fast spinning drive laptop. I've posted example database
> (minimized from original) at
> https://docs.google.com/file/d/0Bzx3gCDqfzVdcDNhdzlfVlh4ZTA/. Could
> someone either confirm or prove false that this query has become slower
> with newer versions?
>
> Merike
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



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

Re: [sqlite] An order by problem, maybe a bug?

[Simon Slavin]

On 19 Sep 2014, at 1:15am, Mark Halegua  wrote:

> that resolved it.  I didn't know you needed to put the desc with both columns.
> 
> It means another table I had thought was properly ordered wasn't.
> 
> Thank you.

You're welcome.  Glad you figured it out.

By the way I wanted to warn you about starting any project with first name, 
middle name and last name fields.  This leads to problems, and I would go to 
some lengths to avoid it if possible.  It would be better to provide two 
columns:

name(their name, however they want it to be shown)
nameInSortOrder (their name, in whatever order you feel it should be sorted)

For the second field your name might appear as "Halegua, Mark" and someone with 
a middle name might appear as "Smith, Mark Edward".  The comma is needed 
because some people have a surname which is two separate words, e.g. Patrick 
Nielsen Hayden.  Given the way SQLite works you would want to declare the field 
nameInSortOrder as having COLLATE NOCASE.

This is especially important if you are storing names which don't all have 
Western-style 'given-name surname' format.  For instance, you may see these 
words between the first and last parts of people's names: "bin", "ben", "ibn", 
"bas", "bat", "O'", "al-", "de" "van de", "Fitz".  They"re not middle names.  
They mean "son of" or "daughter of" or "from" or other things.  They should 
definitely not be capitalised, except for "O'".  And you don't sort on them at 
all.

Similarly, surnames beginning with 'Mac' or 'Mc' should not be sorted together, 
not as if the name begins with the letters 'MAC'.  It's a convention that they 
all be sorted at the beginning of the 'M' listings, ignoring the difference 
between "Mc" and "Mac".

For a longer list of reasons, see this article:



and for those who like that, there's a similar



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

Re: [sqlite] I'm trying to figure out how to ...

[FarSight Data Systems]

Kieth,

Thanks for the reply.  I should have asked sooner and would have wasted less 
time trying to 
do something sqlite wasn't meant for.  I will however look up rolling cursors.  
That may help 
a little.  Again, thanks.

Mark


On Wednesday, September 17, 2014 11:14:36 PM Keith Medcalf wrote:
> You cannot do any of these things in any relational database.  You can only
> do this in navigational databases.  There are various kludges which permit
> you to simulate navigational abilities on top of relational databases, but
> they are all kludges which are performed by various forms of fakery to
> impose navigability on top of a model which inherently is not navigable.
> 
> For example, many database drivers can kludge you up what is called a
> "keyset" driven cursor.  It does this by executing the query and storing a
> temporary table containing the primary keys of each table in the query for
> each result row (this is stored either in the driver (for a client-driven
> keyset) or on the server (for a server driven keyset).  When you ask for a
> row from the keyset, the primary keys are used to issue a bunch of queries
> to "reconstruct" the "present view" of the result that would be at that
> navigational location for you.  There are also, usually in these same
> drivers, what are called "scrollable" cursors.  These differ from a
> "keyset" cursor in that the query result set is stored in a temporary table
> (rather than a keyset table).  The driver can then "pretend" there is
> ordering and record numbers on the results and can internally reissue
> queries against the temporary table and its row numbers so that it appears
> you can scroll forwards and backwards and access rand om rows of the result
> set (this type of cursor is almost always implemented on the server as a
> temp table and the only information sent to the client are the extents of
> the rowset).  The key difference is that keyset cursors can be used to
> update the database (since you have the primary keys for the original data
> rows stored away) whereas plain scrollable cursors are read only.
> 
> In some systems these types of capabilities exist solely in the drivers.  In
> others, there is inherent support in the database engine itself.  There are
> even cases where there is a combination of both, or where you can select
> whether the support should be implemented server-side or client-side.  In
> some cases the choice of implementation method is taken away from you in
> order to protect you from doing something "abysmal", such as retrieving the
> primary keys for a billion row keyset into driver storage.)
> 
> This is a kludge to give the appearance of navigational capabilities where
> they inherently do not exist.
> 
> The other way to do it is the way primitive folks do it -- retrieve the
> entire result set into a big list of records in your programs storage, and
> then navigate through your list in memory.  This is popular with Microsoft
> tools, for example, and is why most Microsoft tools take aeons to open (try
> to open the event viewer on a busy Microsoft server, or open DSA against a
> domain with a several hundred thousand objects.  You come in to work and
> sign on, then open the tool and lock the screen, then go for breakfast, and
> meetings, and lunch.  When you return after lunch the tool is ready to use.
>  Or it has crashed because it ran out of memory.)
> 
> It is also quite common for "bitty system" developers to do this. 
> Everything works swimmingly well on their test database with 5 customers
> and 3 products.  However, once the "production" database is loaded that
> contains a few thousand customers and several millions of products and
> components, the system craps out or is abysmally slow.  You then read
> stories in the newspaper about how some company (or government) spent
> hundreds of millions or billions of dollars on a failed computer system.
> 
> There are still other products which do not provide drivers which kludge up
> any of these illusions for you, and the database engine does not have the
> baked in complication to assist with the creation of these illusions.  For
> these systems you have to do all the skull-duggery yourself.  SQLite falls
> into this category.  I don't think anyone was written a "driver" which
> implements this in automated fashion either, so you have to "roll your own"
> as it were.
> >I'm racking my brain trying to figure out how to get directly to the last
> >item in a (potentially) sorted or ordered table.  At least oe of the
> >tables will be ordered by a name and a date, so uising the rtowid won't
> >work.
> Read all the results until you run out of results.  At this point the last
> result you successfully retrieved was the last.  If this takes too long
> then,
> >Also, how to traverse a table or cursor in a reverse direction.
> 
> Issue the same query again, and "reverse" the sort order of each column in
> the group by clause.  You will now retrieve the result set in t

[sqlite] Performance regression between 3.7.17 and 3.8.0.2?

[Merike]

Hi everyone,

Since upgrading to Kubuntu 14.04 I've had an issue with Quassel irc
client being slow on startup when it retrieves backlog from database.
I've tracked it down to different sqlite version being installed.
Previously I had 3.7.17 and now have 3.8.2. I've tried various versions
from https://launchpad.net/ubuntu/trusty/+source/sqlite3/+builds and can
only pinpoint it to between 3.7.17 and 3.8.0.2 because there doesn't
appear to be intermediate builds.

I don't know what exact queries Quassel runs but most likely (after
looking at some Quassel source on github) it's something like the
following for a single channel:
SELECT messageid, time, type, flags, sender, message
FROM backlog
JOIN sender ON backlog.senderid = sender.senderid
WHERE bufferid = 102
ORDER BY messageid DESC
LIMIT 100;

When I run this query on 3.7.17 it returns nearly instantly. When I do
it on 3.8.0.2 or 3.8.6 which I also tried then it takes about 4 seconds
on my not so fast spinning drive laptop. I've posted example database
(minimized from original) at
https://docs.google.com/file/d/0Bzx3gCDqfzVdcDNhdzlfVlh4ZTA/. Could
someone either confirm or prove false that this query has become slower
with newer versions?

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

Re: [sqlite] Provider not showing up in .Net Model import wizard

[Freeliner]

Looks like I found a small workaround for absence of SQLite DataSource in the
list when trying to start New Connection after choosing "Update Model From
Database..." or "Generate Database from Model..." from your edmx file:

Strangely, but "SQLite Database File" data source  *exists* in the same
selection list when accessing it via 
"Tools-Connect To Database..." Visual Studio menu.

 


 

So first of all you can create a data connection in Visual Studio to an
SQLite file in that menu.
After that press drop-down list of existing database connections and find
that one which you have just created. After that you can proceed with your
operation.

PS. I have just found out - that after you complete this operation - "SQLite
Database File" finally appears in drop-down selection list even when you try
to access it via edmx model context menu:

 





--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Provider-not-showing-up-in-Net-Model-import-wizard-tp75306p78026.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[Mark Halegua]

Simon,
that resolved it.  I didn't know you needed to put the desc with both columns.

It means another table I had thought was properly ordered wasn't.

Thank you.

Mark


On Friday, September 19, 2014 12:50:57 AM Simon Slavin wrote:
> On 19 Sep 2014, at 12:40am, Mark Halegua  wrote:
> > Here are the commands:
> > 
> > select * from contributors order by contrib_lname, contrib_fname; (works
> > properly) select * from contributors order by contrib_lname,
> > contrib_fname desc; (get the same order as above)
> 
> Note that the DESC you provided above applies only to the first name.  If
> you need DESC to apply to the last name too, you want
> 
> select * from contributors order by contrib_lname DESC, contrib_fname desc
> 
> > select * from contributors order by contrib_lname desc; (this works but
> > obviosiosly doesn't sub-order the contrib_fname)
> 
> If the above doesn't solve your problem, please ...
> 
> tell us which version of SQLite you're using.
> 
> give us a couple of INSERT commands which lead to results which are the
> wrong way around.  It should be possible to demonstrate this with just two
> INSERTs.  If it isn't, please try to give us as few as possible rows of
> data to demonstrate the problem.
> 
> If this is a result retrieved by something in one of your programs, please
> try the same commands with one of the SQLite shell tools, downloadable from
> the SQLite site.
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

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

Re: [sqlite] An order by problem, maybe a bug?

[Simon Slavin]

On 19 Sep 2014, at 12:40am, Mark Halegua  wrote:

> Here are the commands:
> 
> select * from contributors order by contrib_lname, contrib_fname; (works 
> properly)
> select * from contributors order by contrib_lname, contrib_fname desc; (get 
> the same order 
> as above)

Note that the DESC you provided above applies only to the first name.  If you 
need DESC to apply to the last name too, you want

select * from contributors order by contrib_lname DESC, contrib_fname desc

> select * from contributors order by contrib_lname desc; (this works but 
> obviosiosly doesn't 
> sub-order the contrib_fname)

If the above doesn't solve your problem, please ...

tell us which version of SQLite you're using.

give us a couple of INSERT commands which lead to results which are the wrong 
way around.  It should be possible to demonstrate this with just two INSERTs.  
If it isn't, please try to give us as few as possible rows of data to 
demonstrate the problem.

If this is a result retrieved by something in one of your programs, please try 
the same commands with one of the SQLite shell tools, downloadable from the 
SQLite site.

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

[sqlite] An order by problem, maybe a bug?

[Mark Halegua]

I've come upon a problem in sqlite3.

Here's the table:

CREATE TABLE contributors(
contrib_id integer primary key, 
contrib_lname char not null, 
contrib_fname char, 
contrib_mname char,  
writer int, 
artist int, 
editor int)

I've inserted several names.  When I order by contrib_lname, contrib_fname I 
get the 
correct order, however when I say desc, I get the same order as without.  It's 
only when I 
don't include the contrib_fname does it come out in last name order, however, 
there are a 
couple of entries with same last names but different first names and they won't 
necessarily 
sort properly if I don't include the contrib_fname column.

Why am I not getting the results I'm expecting?

Here are the commands:

select * from contributors order by contrib_lname, contrib_fname; (works 
properly)
select * from contributors order by contrib_lname, contrib_fname desc; (get the 
same order 
as above)
select * from contributors order by contrib_lname desc; (this works but 
obviosiosly doesn't 
sub-order the contrib_fname)

Mark

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

Re: [sqlite] Provider not showing up in .Net Model import wizard

[Joe Mistachkin]

Freeliner wrote:
>
> I still do not see any SQLite DataSource:
> 
>  
> 
> I am sure there must be some important step which I have missed after
> installation.
> I will appreciate any suggestions on this topic.
> 
 
Are you able to add a System.Data.SQLite-based connection via the Server
Explorer
Window?

If so, can you try adding the "System.Data.SQLite" NuGet package (NOTE: this
MUST
be version 1.0.94.1) to your project (which also includes EF6 support)?

This should also setup the appropriate entries in the "App.config" file.

--
Joe Mistachkin

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

Re: [sqlite] Why can't you find the Wiki by Navigation from the Homepage?

[Richard Hipp]

On Thu, Sep 18, 2014 at 5:31 PM, Sebastian <
sebastianspublicaddr...@googlemail.com> wrote:

>
> But a page like this already exists in the Wiki:
>
> http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor
>
> I guess one reason why this isn't known more might be that this Wiki can't
> be reached by navigating links, when you start at sqlite.org.
>
> Is there any reason for this (too much outdated information there) or is
> it just an accident?
>

That's an old, old Wiki using the CVSTrac tool from the previous decade.
It has been read-only for ages - preserved for historical reference only.

Yes, it would be good to transfer some of the relevant information from
that old wiki into the latest documentation...

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

[sqlite] Why can't you find the Wiki by Navigation from the Homepage?

[Sebastian]

recently there was this on the mailing list:

> Date: Thu, 18 Sep 2014 10:38:38 +0200
> From: RSmith 
> 
> On 2014/09/18 08:18, Keith Medcalf wrote:
> > ...long article...  
> 
> Thanks Keith for taking the time, I imagine this is a topic you are 
> passionate about and I think it should be published somewhere as 
> an article because it will save lives of beginner DBA/Programmers for 
> millenia to come - I have certainly born witness to many of 
> these pitfalls at various clients, and in the past stepped in some myself.

But a page like this already exists in the Wiki:

http://www.sqlite.org/cvstrac/wiki?p=ScrollingCursor

I guess one reason why this isn't known more might be that this Wiki can't be 
reached by navigating links, when you start at sqlite.org.

Is there any reason for this (too much outdated information there) or is it 
just an accident?
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I'm trying to figure out how to ...

[Nico Williams]

If you do a select with the an ORDER BY ... DESC (or ASC) LIMIT 1,
with the order-by clause matching one of the indexes on that table,
then you'll get the last row without having to use a rowid.

For example:

CREATE TABLE person (firstname TEXT, lastname TEXT, stuff TEXT,
PRIMARY KEY (lastname, firstname)) WITHOUT ROWID;

-- insert some rows

-- efficiently select the last cursor
SELECT * from person ORDER BY lastname DESC, firstname DESC LIMIT 1;

Traversing from end to start is easy: drop the LIMIT.

The same applies to going back one, but you need to add a WHERE clause
(you want rows with smaller index key than the current row).

When using primary this is all very efficient, but NOT as efficient as
when you have a low-level cursor and can move it around at the
low-level b-tree (or whatever) interface.

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

Re: [sqlite] I'm trying to figure out how to ...

[Mark Halegua]

Kieth,
 
Thanks for the reply.  I should have asked sooner and would have wasted less 
time trying to 
do something sqlite wasn't meant for.  I will however look up rolling cursors.  
That may help 
a little.  Again, thanks.
 
Mark

On Wednesday, September 17, 2014 11:14:36 PM Keith Medcalf wrote:
> You cannot do any of these things in any relational database.  You can only
> do this in navigational databases.  There are various kludges which permit
> you to simulate navigational abilities on top of relational databases, but
> they are all kludges which are performed by various forms of fakery to
> impose navigability on top of a model which inherently is not navigable.
> 
> For example, many database drivers can kludge you up what is called a
> "keyset" driven cursor.  It does this by executing the query and storing a
> temporary table containing the primary keys of each table in the query for
> each result row (this is stored either in the driver (for a client-driven
> keyset) or on the server (for a server driven keyset).  When you ask for a
> row from the keyset, the primary keys are used to issue a bunch of queries
> to "reconstruct" the "present view" of the result that would be at that
> navigational location for you.  There are also, usually in these same
> drivers, what are called "scrollable" cursors.  These differ from a
> "keyset" cursor in that the query result set is stored in a temporary table
> (rather than a keyset table).  The driver can then "pretend" there is
> ordering and record numbers on the results and can internally reissue
> queries against the temporary table and its row numbers so that it appears
> you can scroll forwards and backwards and access rand om rows of the result
> set (this type of cursor is almost always implemented on the server as a
> temp table and the only information sent to the client are the extents of
> the rowset).  The key difference is that keyset cursors can be used to
> update the database (since you have the primary keys for the original data
> rows stored away) whereas plain scrollable cursors are read only.
> 
> In some systems these types of capabilities exist solely in the drivers.  In
> others, there is inherent support in the database engine itself.  There are
> even cases where there is a combination of both, or where you can select
> whether the support should be implemented server-side or client-side.  In
> some cases the choice of implementation method is taken away from you in
> order to protect you from doing something "abysmal", such as retrieving the
> primary keys for a billion row keyset into driver storage.)
> 
> This is a kludge to give the appearance of navigational capabilities where
> they inherently do not exist.
> 
> The other way to do it is the way primitive folks do it -- retrieve the
> entire result set into a big list of records in your programs storage, and
> then navigate through your list in memory.  This is popular with Microsoft
> tools, for example, and is why most Microsoft tools take aeons to open (try
> to open the event viewer on a busy Microsoft server, or open DSA against a
> domain with a several hundred thousand objects.  You come in to work and
> sign on, then open the tool and lock the screen, then go for breakfast, and
> meetings, and lunch.  When you return after lunch the tool is ready to use.
>  Or it has crashed because it ran out of memory.)
> 
> It is also quite common for "bitty system" developers to do this. 
> Everything works swimmingly well on their test database with 5 customers
> and 3 products.  However, once the "production" database is loaded that
> contains a few thousand customers and several millions of products and
> components, the system craps out or is abysmally slow.  You then read
> stories in the newspaper about how some company (or government) spent
> hundreds of millions or billions of dollars on a failed computer system.
> 
> There are still other products which do not provide drivers which kludge up
> any of these illusions for you, and the database engine does not have the
> baked in complication to assist with the creation of these illusions.  For
> these systems you have to do all the skull-duggery yourself.  SQLite falls
> into this category.  I don't think anyone was written a "driver" which
> implements this in automated fashion either, so you have to "roll your own"
> as it were.
> >I'm racking my brain trying to figure out how to get directly to the last
> >item in a (potentially) sorted or ordered table.  At least oe of the
> >tables will be ordered by a name and a date, so uising the rtowid won't
> >work.
> Read all the results until you run out of results.  At this point the last
> result you successfully retrieved was the last.  If this takes too long
> then,
> >Also, how to traverse a table or cursor in a reverse direction.
> 
> Issue the same query again, and "reverse" the sort order of each column in
> the group by clause.  You will now retrieve the result set in 

Re: [sqlite] Generating VDBE program

[Richard Hipp]

On Thu, Sep 18, 2014 at 10:10 AM, Prakash Premkumar 
wrote:

> Which function in the sqlite generates the Vdbe program for the given
> query?
>
> Let's take an example of the join of two tables. Table A has n columns and
> Table B has m columns, and the result has n+m columns.
>
> During the execution of the Vdbe program the values from these columns get
> copied to the Mem struct randomly (when OP_Column opcode is formed) and
> finally when the OP_ResultRow is executed, the Mem's are rearranged so that
> the columns of each table are contiguous.
>
> How does sqlite know in which Mem the value of a column is stored and how
> does it finally rearrange the Mems contiguously?
> Can you point me to the portion of the source code which makes this
> decision?
>
> Can you kindly explain me the concept?
>

SQLite is a compiler.  It transforms a high-level language (SQL) into
machine code (or in this case VDBE code, which is very similar to machine
code).   In this sense, SQLite is similar to GCC.  GCC translates C code
into x86 assembly language.  SQLite translates SQL into VDBE assembly
language.  The source and target languages are different, but the
fundamental techniques are the same.

When you ask "how does sqlite know which Mem the value of a column is
stored?" that is the same as asking "how does gcc know which processor
registers the value of a variable is stored?".

I recommend that you first study up on compiler construction.  After you
understand how compilers work, SQLite will likely make a lot more sense to
you.

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

[sqlite] Generating VDBE program

[Prakash Premkumar]

Which function in the sqlite generates the Vdbe program for the given query?

Let's take an example of the join of two tables. Table A has n columns and
Table B has m columns, and the result has n+m columns.

During the execution of the Vdbe program the values from these columns get
copied to the Mem struct randomly (when OP_Column opcode is formed) and
finally when the OP_ResultRow is executed, the Mem's are rearranged so that
the columns of each table are contiguous.

How does sqlite know in which Mem the value of a column is stored and how
does it finally rearrange the Mems contiguously?
Can you point me to the portion of the source code which makes this
decision?

Can you kindly explain me the concept?

Thanks for you help.

Regards,
Prakash Premkumar
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] FW: I'm trying to figure out how to ...

[Simon Slavin]

On 18 Sep 2014, at 9:38am, RSmith  wrote:

> Thanks Keith for taking the time, I imagine this is a topic you are 
> passionate about and I think it should be published somewhere as an article 
> because it will save lives of beginner DBA/Programmers for millenia to come - 
> I have certainly born witness to many of these pitfalls at various clients, 
> and in the past stepped in some myself.

But the actual lesson from this list (plus academic books, experienced database 
professions and the industry) is not to do anything that works like a database 
cursor at all.  In the bad old days, with single-access databases and limited 
screen space, it might have been a good idea.  These days, where people have 
big screens and the database is being continually updated in the background, it 
leads to nonsense.

That's the gist of Keith's post and many previous ones.

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

Re: [sqlite] I'm trying to figure out how to ...

[Pavlos Christoforou]

Thanks Keith,

We have taken the liberty to forward your answer to our whole dev
team, your insights on the subject are useful to all of us.

Cheers

Pavlos

On 18 September 2014 07:14, Keith Medcalf  wrote:
>
> You cannot do any of these things in any relational database.  You can only 
> do this in navigational databases.  There are various kludges which permit 
> you to simulate navigational abilities on top of relational databases, but 
> they are all kludges which are performed by various forms of fakery to impose 
> navigability on top of a model which inherently is not navigable.
>
> For example, many database drivers can kludge you up what is called a 
> "keyset" driven cursor.  It does this by executing the query and storing a 
> temporary table containing the primary keys of each table in the query for 
> each result row (this is stored either in the driver (for a client-driven 
> keyset) or on the server (for a server driven keyset).  When you ask for a 
> row from the keyset, the primary keys are used to issue a bunch of queries to 
> "reconstruct" the "present view" of the result that would be at that 
> navigational location for you.  There are also, usually in these same 
> drivers, what are called "scrollable" cursors.  These differ from a "keyset" 
> cursor in that the query result set is stored in a temporary table (rather 
> than a keyset table).  The driver can then "pretend" there is ordering and 
> record numbers on the results and can internally reissue queries against the 
> temporary table and its row numbers so that it appears you can scroll 
> forwards and backwards and access ra
 nd
>  om rows of the result set (this type of cursor is almost always implemented 
> on the server as a temp table and the only information sent to the client are 
> the extents of the rowset).  The key difference is that keyset cursors can be 
> used to update the database (since you have the primary keys for the original 
> data rows stored away) whereas plain scrollable cursors are read only.
>
> In some systems these types of capabilities exist solely in the drivers.  In 
> others, there is inherent support in the database engine itself.  There are 
> even cases where there is a combination of both, or where you can select 
> whether the support should be implemented server-side or client-side.  In 
> some cases the choice of implementation method is taken away from you in 
> order to protect you from doing something "abysmal", such as retrieving the 
> primary keys for a billion row keyset into driver storage.)
>
> This is a kludge to give the appearance of navigational capabilities where 
> they inherently do not exist.
>
> The other way to do it is the way primitive folks do it -- retrieve the 
> entire result set into a big list of records in your programs storage, and 
> then navigate through your list in memory.  This is popular with Microsoft 
> tools, for example, and is why most Microsoft tools take aeons to open (try 
> to open the event viewer on a busy Microsoft server, or open DSA against a 
> domain with a several hundred thousand objects.  You come in to work and sign 
> on, then open the tool and lock the screen, then go for breakfast, and 
> meetings, and lunch.  When you return after lunch the tool is ready to use.  
> Or it has crashed because it ran out of memory.)
>
> It is also quite common for "bitty system" developers to do this.  Everything 
> works swimmingly well on their test database with 5 customers and 3 products. 
>  However, once the "production" database is loaded that contains a few 
> thousand customers and several millions of products and components, the 
> system craps out or is abysmally slow.  You then read stories in the 
> newspaper about how some company (or government) spent hundreds of millions 
> or billions of dollars on a failed computer system.
>
> There are still other products which do not provide drivers which kludge up 
> any of these illusions for you, and the database engine does not have the 
> baked in complication to assist with the creation of these illusions.  For 
> these systems you have to do all the skull-duggery yourself.  SQLite falls 
> into this category.  I don't think anyone was written a "driver" which 
> implements this in automated fashion either, so you have to "roll your own" 
> as it were.
>
>>I'm racking my brain trying to figure out how to get directly to the last
>>item in a (potentially) sorted or ordered table.  At least oe of the
>>tables will be ordered by a name and a date, so uising the rtowid won't work.
>
> Read all the results until you run out of results.  At this point the last 
> result you successfully retrieved was the last.  If this takes too long then,
>
>>Also, how to traverse a table or cursor in a reverse direction.
>
> Issue the same query again, and "reverse" the sort order of each column in 
> the group by clause.  You will now retrieve the result set in the opposite 
> order and the "first" row will

Re: [sqlite] Provider not showing up in .Net Model import wizard

[Freeliner]

The problems in this question closely resemble those which I have met with my
project with Visual Studio support.

I've followed recommendations by visiting
http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki
and choosing *sqlite-netFx40-setup-bundle-x86-2010-1.0.94.0.exe* to download
since it is targeted for VS 2010 and states as "*capable of installing the
design-time components for Visual Studio 2010*".

I've installed it, checking options to install design-time VS components:

 

However, still after restarting VS and trying to generate database from the
model:

 

I still do not see any SQLite DataSource:

 

I am sure there must be some important step which I have missed after
installation.
I will appreciate any suggestions on this topic.



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Provider-not-showing-up-in-Net-Model-import-wizard-tp75306p78023.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] I'm trying to figure out how to ...

[John McKown]

And _excellent_ reply. I'm grabbing this email to put as a "gist" on
my github account. From my own experience, programmers who come from a
"non-relational" background, the world view of how to _properly_ do
SQL is difficult.

On Thu, Sep 18, 2014 at 12:14 AM, Keith Medcalf  wrote:
>
> You cannot do any of these things in any relational database.  You can only 
> do this in navigational databases.  There are various kludges which permit 
> you to simulate navigational abilities on top of relational databases, but 
> they are all kludges which are performed by various forms of fakery to impose 
> navigability on top of a model which inherently is not navigable.
>
> For example, many database drivers can kludge you up what is called a 
> "keyset" driven cursor.  It does this by executing the query and storing a 
> temporary table containing the primary keys of each table in the query for 
> each result row (this is stored either in the driver (for a client-driven 
> keyset) or on the server (for a server driven keyset).  When you ask for a 
> row from the keyset, the primary keys are used to issue a bunch of queries to 
> "reconstruct" the "present view" of the result that would be at that 
> navigational location for you.  There are also, usually in these same 
> drivers, what are called "scrollable" cursors.  These differ from a "keyset" 
> cursor in that the query result set is stored in a temporary table (rather 
> than a keyset table).  The driver can then "pretend" there is ordering and 
> record numbers on the results and can internally reissue queries against the 
> temporary table and its row numbers so that it appears you can scroll 
> forwards and backwards and access ra
 nd
>  om rows of the result set (this type of cursor is almost always implemented 
> on the server as a temp table and the only information sent to the client are 
> the extents of the rowset).  The key difference is that keyset cursors can be 
> used to update the database (since you have the primary keys for the original 
> data rows stored away) whereas plain scrollable cursors are read only.
>
> In some systems these types of capabilities exist solely in the drivers.  In 
> others, there is inherent support in the database engine itself.  There are 
> even cases where there is a combination of both, or where you can select 
> whether the support should be implemented server-side or client-side.  In 
> some cases the choice of implementation method is taken away from you in 
> order to protect you from doing something "abysmal", such as retrieving the 
> primary keys for a billion row keyset into driver storage.)
>
> This is a kludge to give the appearance of navigational capabilities where 
> they inherently do not exist.
>
> The other way to do it is the way primitive folks do it -- retrieve the 
> entire result set into a big list of records in your programs storage, and 
> then navigate through your list in memory.  This is popular with Microsoft 
> tools, for example, and is why most Microsoft tools take aeons to open (try 
> to open the event viewer on a busy Microsoft server, or open DSA against a 
> domain with a several hundred thousand objects.  You come in to work and sign 
> on, then open the tool and lock the screen, then go for breakfast, and 
> meetings, and lunch.  When you return after lunch the tool is ready to use.  
> Or it has crashed because it ran out of memory.)
>
> It is also quite common for "bitty system" developers to do this.  Everything 
> works swimmingly well on their test database with 5 customers and 3 products. 
>  However, once the "production" database is loaded that contains a few 
> thousand customers and several millions of products and components, the 
> system craps out or is abysmally slow.  You then read stories in the 
> newspaper about how some company (or government) spent hundreds of millions 
> or billions of dollars on a failed computer system.
>
> There are still other products which do not provide drivers which kludge up 
> any of these illusions for you, and the database engine does not have the 
> baked in complication to assist with the creation of these illusions.  For 
> these systems you have to do all the skull-duggery yourself.  SQLite falls 
> into this category.  I don't think anyone was written a "driver" which 
> implements this in automated fashion either, so you have to "roll your own" 
> as it were.
>
>>I'm racking my brain trying to figure out how to get directly to the last
>>item in a (potentially) sorted or ordered table.  At least oe of the
>>tables will be ordered by a name and a date, so uising the rtowid won't work.
>
> Read all the results until you run out of results.  At this point the last 
> result you successfully retrieved was the last.  If this takes too long then,
>
>>Also, how to traverse a table or cursor in a reverse direction.
>
> Issue the same query again, and "reverse" the sort order of each column in 
> the group by clause.  You will no

Re: [sqlite] I'm trying to figure out how to ...

[RSmith]

On 2014/09/18 05:38, Mark Halegua wrote:

I'm racking my brain trying to figure out how to get directly to the last item 
in a (potentially)
sorted or ordered table.  At least oe of the tables will be ordered by a name 
and a date,
so uising the rtowid won't work.

Also, how to traverse a table or cursor in a reverse direction.


Hi Mark, Keith's reply is quite complete and you should design systems with 
those tenets in mind.


That said - to elaborate more on the quick and dirty methods (in case you are 
hacking together a quicky in stead of a proper system):


Getting only the last item in table t which has fields fDate and fName among 
others:

SELECT * FROM t WHERE 1 ORDER BY fDate DESC, fName DESC LIMIT 1;

(The DESC does the trick of making the sort order go the other way round, the LIMIT is to get just the 1st item [which is really the 
last item had the sort-order been normal] and the WHERE clause is superfluous here, but shows where it might be. Also note that this 
method will be real fast if fDate and fName are contained in Indices, else it may well be quite slow for large tables)



Traversing the results in reverse is achieved much the same way, just remove the limit clause. If however you meant that you need to 
move "back" through the list after having gone forward or such, then you are in scrolling cursor territory and I respectfully refer 
you back to Keith's article and urge to take heed of the advice.



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

Re: [sqlite] FW: I'm trying to figure out how to ...

[RSmith]

On 2014/09/18 08:18, Keith Medcalf wrote:

...long article...


Thanks Keith for taking the time, I imagine this is a topic you are passionate about and I think it should be published somewhere as 
an article because it will save lives of beginner DBA/Programmers for millenia to come - I have certainly born witness to many of 
these pitfalls at various clients, and in the past stepped in some myself.


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