Re: [sqlite] Results of Joins in sqlite

[James K. Lowden]

On Wed, 24 Sep 2014 11:03:53 +0530
Prakash Premkumar  wrote:

> Let's say an output of the join is:
> 
> r11,r21,r31
> r11,r21,r32
> r11,r21,r33
> 
> where r1i is the i th row in T1, r2i is the i th row in T2 and r3i is
> the ith row in T3:
> 
> sqlite produces 3 result rows , but I would like to produce one
> result row where,
> 
>   r11
> |
>   r21
>  /   |   \
> r31   r32  r33
...
> The efficiency I gain with the 2nd approach, is the reduction is in
> the number of copies .i.e each row is copied only once.

Granting your thesis ad argumentum, your approach has three
inefficiencies:

1.  The graph needs pointers for the values, or some metadata to
indicate the graph structure.  

2.  To create the graph, the data must be sorted.  

3.  Memory for the graph has to be allocated.  

If you build the graph yourself atop the API, you will do very much
what you're asking SQLite to do.  You will find that not only is it not
very efficient after all but, because graphs are complex data
structures, the interface is hideous.  If you don't believe me, try
reading IBM's IMS documentation sometime. Or consider the complexity of
"next" in a depth-first search, compared with row iteration.  

Let's suppose none of that is true, though, and pretend a graph can be
summoned instantly and navigated effortlessly.  I ask you: how will your
application use the data in the graph?  Will it compute on them in
situ?  Or will it copy them into a native structure, so you can operate
on them efficiently as named variables?  If the latter, you're
still copying the data.  

These among other reasons are why the relational model evicted its
predecessors from the database landscape.  If you revisit your
assumptions, you may find it easier to use and more efficient than it
first seemed.  

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

Re: [sqlite] Results of Joins in sqlite

[dave]

It does sound like that, like an Object Relational Mapping.  And maybe
someone has already done this.
Googling "ORM sqlite c++"
Yields a lot of existing ORM projects that support sqlite, so maybe Prakash
doesn't need to work so hard on implementing that part of his project
himself.

-dave

> -Original Message-
> From: sqlite-users-boun...@sqlite.org 
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Keith Medcalf
> Sent: Wednesday, September 24, 2014 8:27 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Results of Joins in sqlite
> 
> 
> 
> Sounds like you want to "hack up" where the resultrow is 
> returned for each table row visited as the query is 
> processed, but only once for each table row satisfying the 
> query.  You can certainly do this -- the source code is 
> freely available.
> 
> I am sure you could "hack that up" if you want, but then you 
> would not be having a relational database anymore.  Are you 
> trying to turn a relational database into an object 
> datastore, or something else perchance?
> 
> Also, how would you intend to deal with such things as 
> computed results (which have no underlying table), grouped 
> results (which have a multitude of underlying records), 
> distinct sub-constraints (which may cover multiple underlying 
> table records), correlated or non-correlated subquery 
> conditions (may include all the preceding).
> 
> Iterating through the result set, as you put it, or more 
> correctly "retrieving the projection set" is the 
> implementation of precisely the algebraic basis for what is 
> called the "Relational" database model.  SQLite is a 
> "Relational" database and processes data in accordance with 
> the principles of relational (set) algebra tempered by the 
> necessity of being implementable on inherently serial 
> processing systems.
> 
> The changes you are suggesting would turn SQLite into 
> something other than a "Relational" database.  What is the 
> thing you want?  Perhaps you should be looking for a database 
> using that underlying mathematical model (whatever it is) 
> rather than a "relational" model.  To me, it sounds like you 
> want a Hierarchical Network Extended Database model, not a 
> Relational Model.
> 
> >-Original Message-
> >
> >Thanks a lot , Simon and Hick,
> >
> >What I am looking for is , instead of iterating through the 
> result which
> >sqlite provides and then form the respective objects and setting
> >pointers,
> >is it possible to hack sqlite to fill in the objects this way.
> >I would like to prevent the extra iteration through the result set.
> >
> >When the result row is formed in OP_ResultRow, we should be 
> able to form
> >the structs and their links. Any pointers in this direction?
> >
> >Thanks
> >Prakash
> >
> >On Wed, Sep 24, 2014 at 5:36 PM, Hick Gunter 
> <h...@scigames.at> wrote:
> >
> >> How about writing something that uses the SQLite Api as 
> intended and
> >works
> >> first?
> >>
> >> -Ursprüngliche Nachricht-
> >> Von: Prakash Premkumar [mailto:prakash.p...@gmail.com]
> >> Gesendet: Mittwoch, 24. September 2014 12:54
> >> An: General Discussion of SQLite Database
> >> Betreff: Re: [sqlite] Results of Joins in sqlite
> >>
> >> Thanks a lot Hick,for your approach.
> >> With the approach you suggested, we are creating extra 
> queries and if
> >the
> >> join is on n tables there will be n+1 queries , and each query will
> >have to
> >> go through a query planning stage.
> >> Is there an alternative idea ?
> >>
> >> Thanks a lot
> >> Prakash
> >>
> >> On Wed, Sep 24, 2014 at 3:34 PM, Hick Gunter 
> <h...@scigames.at> wrote:
> >>
> >> > You are free to build your own result conversion routine 
> on top of
> >the
> >> > SQLite Api.
> >> >
> >> >
> >> > May I suggest selecting the rowids of the tables too i.e.
> >> >
> >> > SELECT t1.rowid, t2.rowid, t3.rowid, <.. more fields ...> FROM
> >> > <...your join...>;
> >> >
> >> > When you first come across a new rowid you can create your memory
> >> > object and populate it from the required fields.
> >> > The disadvantage ist hat SQLite will still retrieve all requested
> >> > fields for each result row.
> >> >
> >> >
> >> > Or maybe you might like to
> >> &

Re: [sqlite] Results of Joins in sqlite

[RSmith]

On 2014/09/24 15:06, Prakash Premkumar wrote:

Thanks a lot , Simon and Hick,

What I am looking for is , instead of iterating through the result which
sqlite provides and then form the respective objects and setting pointers,
is it possible to hack sqlite to fill in the objects this way.
I would like to prevent the extra iteration through the result set.

When the result row is formed in OP_ResultRow, we should be able to form
the structs and their links. Any pointers in this direction?


Hi Prakash,

I understand what and why you are trying to do to achieve this - but please let me suggest some alternatives, and explain why myself 
(and everyone else seemingly) is urging you to not do it the way you are trying. Your code will only work with the current SQLite 
amalgamation, it won't work ever again unless you update it every time a new version appears - which will be very tedious, and core 
functionality might change, which means not only will you need to change your code, but at some point may even need to rethink your 
entire strategy. How many times do you want to reinvent the wheel?


To be clear, new versions appear every other month more or less. Further to this, the amount of work you need to do to achieve what 
you are trying is enormous, there is no quick "hack" for it, you have to meticulously adjust and inject code and test it into the 
SQLite main engine - YES this is possible, but at what price?  You are trying to do a job that requires 900% effort for a <1% 
efficiency gain.


Further to this, the kind of tree-graph that you wish to build will only work for very specific kinds of queries, for instance 
requirements such as columns need to be ordered in ascending complexity. If this is the only job this engine does and will not be 
updated, then that does not matter so much.


Why not consider (as suggested by others) using the API as it is supposed to be used, for instance making a virtual table will do 
this job exceedingly well, and really efficient. Just define a new table , write to it via a SELECT query and get from it the 
structure or map or graph which you can very easily ascertain.


if the queries are going to be specific, you can even add SQL functions via the function adder API to systematically build tree 
structure while the query is executing (though specific ordering will be required since the functions do not know at the time of 
being called which specific row is being processed in the output and might be called more than once if it appears in the order-by 
clause etc.).


Then, simply using the sqlite_step() and column value reading APIs will all do exactly what you want WHILE the query executes, no 
need for extra loops... this is why they exist and no injected code could possibly be measurably faster. Why would you insist on 
using another way?


Consider also that using ANY of the above suggested API methods will ensure you can plug-and-play any updated data engine from the 
SQLite site in future (or past if you fancy testing) and capitalize on the improved performance or newer features without any 
problems, without rewriting a single line of code. How is this not a 100 times better?



Please consider the alternates, save yourself a LOT of work.

Cheers!
Ryan





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

Re: [sqlite] Results of Joins in sqlite

[John Hascall]

This is a really terrible idea.  It is dependent on the internals of sqlite
which makes it extremely fragile.
Have you profiled your code to show that this is your bottleneck?  If so,
it's still a terrible idea,
but at least has some basis for considering the idea.

John

On Wed, Sep 24, 2014 at 8:06 AM, Prakash Premkumar <prakash.p...@gmail.com>
wrote:

> Thanks a lot , Simon and Hick,
>
> What I am looking for is , instead of iterating through the result which
> sqlite provides and then form the respective objects and setting pointers,
> is it possible to hack sqlite to fill in the objects this way.
> I would like to prevent the extra iteration through the result set.
>
> When the result row is formed in OP_ResultRow, we should be able to form
> the structs and their links. Any pointers in this direction?
>
> Thanks
> Prakash
>
> On Wed, Sep 24, 2014 at 5:36 PM, Hick Gunter <h...@scigames.at> wrote:
>
> > How about writing something that uses the SQLite Api as intended and
> works
> > first?
> >
> > -Ursprüngliche Nachricht-
> > Von: Prakash Premkumar [mailto:prakash.p...@gmail.com]
> > Gesendet: Mittwoch, 24. September 2014 12:54
> > An: General Discussion of SQLite Database
> > Betreff: Re: [sqlite] Results of Joins in sqlite
> >
> > Thanks a lot Hick,for your approach.
> > With the approach you suggested, we are creating extra queries and if the
> > join is on n tables there will be n+1 queries , and each query will have
> to
> > go through a query planning stage.
> > Is there an alternative idea ?
> >
> > Thanks a lot
> > Prakash
> >
> > On Wed, Sep 24, 2014 at 3:34 PM, Hick Gunter <h...@scigames.at> wrote:
> >
> > > You are free to build your own result conversion routine on top of the
> > > SQLite Api.
> > >
> > >
> > > May I suggest selecting the rowids of the tables too i.e.
> > >
> > > SELECT t1.rowid, t2.rowid, t3.rowid, <.. more fields ...> FROM
> > > <...your join...>;
> > >
> > > When you first come across a new rowid you can create your memory
> > > object and populate it from the required fields.
> > > The disadvantage ist hat SQLite will still retrieve all requested
> > > fields for each result row.
> > >
> > >
> > > Or maybe you might like to
> > >
> > > SELECT t1.rowid, t2.rowid, t3.rowid FROM <...your join...>;
> > >
> > > and then populate your structure by binding the rowid received and
> > > executing the appropriate
> > >
> > > SELECT <...t1 fields> FROM t1 where t1.rowid=?; SELECT <...t2 fields>
> > > FROM t2 where t2.rowid=?; SELECT <...t3 fields> FROM t3 where
> > > t3.rowid=?;
> > >
> > >
> > >
> > > -Ursprüngliche Nachricht-
> > > Von: Prakash Premkumar [mailto:prakash.p...@gmail.com]
> > > Gesendet: Mittwoch, 24. September 2014 09:32
> > > An: General Discussion of SQLite Database
> > > Betreff: Re: [sqlite] Results of Joins in sqlite
> > >
> > > Thanks for the reply Clemens. Yes.I'm sorry. It will not have 8
> columns.
> > >
> > > When one result row comes in , I want to create an object for each
> > > table (My application map one struct to one table) and put the columns
> > > of respective tables in their individual structs. i,e  I would set
> > > column 0 and column 1 in result row to the struct of T1 and column 2
> > > and 3 to that of T2 and so on.
> > >
> > > and I set pointers between T1 and T2 , T2 and T3.
> > > When a new row comes in and if the object for that row is already
> > created.
> > > i.e if r11 again comes as output, I will not create a new
> > > object,instead I would use the old object and set pointers between the
> > > old T1 and object and the T2 object (if r21 is new,else do not set
> > > pointers and do not create
> > > objects)
> > >
> > > similarly for T3. Thus,for the above case (the example we discussed),
> > > there will be one pointer from T1 row to T2 row and 3 pointers from T2
> > > row to the
> > > 3 T3 rows(one pointer per row).
> > >
> > > The end data structure will look like a graph.
> > >
> > > I want the API to return this graph./ is there an alternative ?
> > > API call : graph* sqlite3_join(select_stmt*)
> > >
> > > Thanks
> > > Prakash
> > >
> > >
> > >
> > >
> > > On Wed, Sep

Re: [sqlite] Results of Joins in sqlite

[Keith Medcalf]

Sounds like you want to "hack up" where the resultrow is returned for each 
table row visited as the query is processed, but only once for each table row 
satisfying the query.  You can certainly do this -- the source code is freely 
available.

I am sure you could "hack that up" if you want, but then you would not be 
having a relational database anymore.  Are you trying to turn a relational 
database into an object datastore, or something else perchance?

Also, how would you intend to deal with such things as computed results (which 
have no underlying table), grouped results (which have a multitude of 
underlying records), distinct sub-constraints (which may cover multiple 
underlying table records), correlated or non-correlated subquery conditions 
(may include all the preceding).

Iterating through the result set, as you put it, or more correctly "retrieving 
the projection set" is the implementation of precisely the algebraic basis for 
what is called the "Relational" database model.  SQLite is a "Relational" 
database and processes data in accordance with the principles of relational 
(set) algebra tempered by the necessity of being implementable on inherently 
serial processing systems.

The changes you are suggesting would turn SQLite into something other than a 
"Relational" database.  What is the thing you want?  Perhaps you should be 
looking for a database using that underlying mathematical model (whatever it 
is) rather than a "relational" model.  To me, it sounds like you want a 
Hierarchical Network Extended Database model, not a Relational Model.

>-Original Message-
>
>Thanks a lot , Simon and Hick,
>
>What I am looking for is , instead of iterating through the result which
>sqlite provides and then form the respective objects and setting
>pointers,
>is it possible to hack sqlite to fill in the objects this way.
>I would like to prevent the extra iteration through the result set.
>
>When the result row is formed in OP_ResultRow, we should be able to form
>the structs and their links. Any pointers in this direction?
>
>Thanks
>Prakash
>
>On Wed, Sep 24, 2014 at 5:36 PM, Hick Gunter <h...@scigames.at> wrote:
>
>> How about writing something that uses the SQLite Api as intended and
>works
>> first?
>>
>> -Ursprüngliche Nachricht-
>> Von: Prakash Premkumar [mailto:prakash.p...@gmail.com]
>> Gesendet: Mittwoch, 24. September 2014 12:54
>> An: General Discussion of SQLite Database
>> Betreff: Re: [sqlite] Results of Joins in sqlite
>>
>> Thanks a lot Hick,for your approach.
>> With the approach you suggested, we are creating extra queries and if
>the
>> join is on n tables there will be n+1 queries , and each query will
>have to
>> go through a query planning stage.
>> Is there an alternative idea ?
>>
>> Thanks a lot
>> Prakash
>>
>> On Wed, Sep 24, 2014 at 3:34 PM, Hick Gunter <h...@scigames.at> wrote:
>>
>> > You are free to build your own result conversion routine on top of
>the
>> > SQLite Api.
>> >
>> >
>> > May I suggest selecting the rowids of the tables too i.e.
>> >
>> > SELECT t1.rowid, t2.rowid, t3.rowid, <.. more fields ...> FROM
>> > <...your join...>;
>> >
>> > When you first come across a new rowid you can create your memory
>> > object and populate it from the required fields.
>> > The disadvantage ist hat SQLite will still retrieve all requested
>> > fields for each result row.
>> >
>> >
>> > Or maybe you might like to
>> >
>> > SELECT t1.rowid, t2.rowid, t3.rowid FROM <...your join...>;
>> >
>> > and then populate your structure by binding the rowid received and
>> > executing the appropriate
>> >
>> > SELECT <...t1 fields> FROM t1 where t1.rowid=?; SELECT <...t2 fields>
>> > FROM t2 where t2.rowid=?; SELECT <...t3 fields> FROM t3 where
>> > t3.rowid=?;
>> >
>> >
>> >
>> > -Ursprüngliche Nachricht-
>> > Von: Prakash Premkumar [mailto:prakash.p...@gmail.com]
>> > Gesendet: Mittwoch, 24. September 2014 09:32
>> > An: General Discussion of SQLite Database
>> > Betreff: Re: [sqlite] Results of Joins in sqlite
>> >
>> > Thanks for the reply Clemens. Yes.I'm sorry. It will not have 8
>columns.
>> >
>> > When one result row comes in , I want to create an object for each
>> > table (My application map one struct to one table) and put the
>columns
>> > of respective tables in their individual structs. i,e  

Re: [sqlite] Results of Joins in sqlite

[Prakash Premkumar]

Thanks a lot , Simon and Hick,

What I am looking for is , instead of iterating through the result which
sqlite provides and then form the respective objects and setting pointers,
is it possible to hack sqlite to fill in the objects this way.
I would like to prevent the extra iteration through the result set.

When the result row is formed in OP_ResultRow, we should be able to form
the structs and their links. Any pointers in this direction?

Thanks
Prakash

On Wed, Sep 24, 2014 at 5:36 PM, Hick Gunter <h...@scigames.at> wrote:

> How about writing something that uses the SQLite Api as intended and works
> first?
>
> -Ursprüngliche Nachricht-
> Von: Prakash Premkumar [mailto:prakash.p...@gmail.com]
> Gesendet: Mittwoch, 24. September 2014 12:54
> An: General Discussion of SQLite Database
> Betreff: Re: [sqlite] Results of Joins in sqlite
>
> Thanks a lot Hick,for your approach.
> With the approach you suggested, we are creating extra queries and if the
> join is on n tables there will be n+1 queries , and each query will have to
> go through a query planning stage.
> Is there an alternative idea ?
>
> Thanks a lot
> Prakash
>
> On Wed, Sep 24, 2014 at 3:34 PM, Hick Gunter <h...@scigames.at> wrote:
>
> > You are free to build your own result conversion routine on top of the
> > SQLite Api.
> >
> >
> > May I suggest selecting the rowids of the tables too i.e.
> >
> > SELECT t1.rowid, t2.rowid, t3.rowid, <.. more fields ...> FROM
> > <...your join...>;
> >
> > When you first come across a new rowid you can create your memory
> > object and populate it from the required fields.
> > The disadvantage ist hat SQLite will still retrieve all requested
> > fields for each result row.
> >
> >
> > Or maybe you might like to
> >
> > SELECT t1.rowid, t2.rowid, t3.rowid FROM <...your join...>;
> >
> > and then populate your structure by binding the rowid received and
> > executing the appropriate
> >
> > SELECT <...t1 fields> FROM t1 where t1.rowid=?; SELECT <...t2 fields>
> > FROM t2 where t2.rowid=?; SELECT <...t3 fields> FROM t3 where
> > t3.rowid=?;
> >
> >
> >
> > -Ursprüngliche Nachricht-
> > Von: Prakash Premkumar [mailto:prakash.p...@gmail.com]
> > Gesendet: Mittwoch, 24. September 2014 09:32
> > An: General Discussion of SQLite Database
> > Betreff: Re: [sqlite] Results of Joins in sqlite
> >
> > Thanks for the reply Clemens. Yes.I'm sorry. It will not have 8 columns.
> >
> > When one result row comes in , I want to create an object for each
> > table (My application map one struct to one table) and put the columns
> > of respective tables in their individual structs. i,e  I would set
> > column 0 and column 1 in result row to the struct of T1 and column 2
> > and 3 to that of T2 and so on.
> >
> > and I set pointers between T1 and T2 , T2 and T3.
> > When a new row comes in and if the object for that row is already
> created.
> > i.e if r11 again comes as output, I will not create a new
> > object,instead I would use the old object and set pointers between the
> > old T1 and object and the T2 object (if r21 is new,else do not set
> > pointers and do not create
> > objects)
> >
> > similarly for T3. Thus,for the above case (the example we discussed),
> > there will be one pointer from T1 row to T2 row and 3 pointers from T2
> > row to the
> > 3 T3 rows(one pointer per row).
> >
> > The end data structure will look like a graph.
> >
> > I want the API to return this graph./ is there an alternative ?
> > API call : graph* sqlite3_join(select_stmt*)
> >
> > Thanks
> > Prakash
> >
> >
> >
> >
> > On Wed, Sep 24, 2014 at 12:45 PM, Clemens Ladisch <clem...@ladisch.de>
> > wrote:
> >
> > > Prakash Premkumar wrote:
> > > > Let's say I have tables T1,T2 and T3 with 2 columns each and I am
> > > joining them.
> > > > The result rows will have 8 columns each.
> > >
> > > No.  The result will have between 4 and 6 columns, depending on how
> > > you do the joins.
> > >
> > > Example:
> > >
> > > CREATE TABLE T1(ID1, Name);
> > > INSERT INTO "T1" VALUES('r11',NULL); CREATE TABLE T2(ID2, ID1);
> > > INSERT INTO "T2" VALUES('r21','r11'); CREATE TABLE T3(ID3, ID2);
> > > INSERT INTO "T3" VALUES('r31','r21'); INSERT INTO "T3"
> > > VALUES('r32','r21'); INSERT INTO "T3" VALUES('r33','r21');
> > >

Re: [sqlite] Results of Joins in sqlite

[Hick Gunter]

How about writing something that uses the SQLite Api as intended and works 
first?

-Ursprüngliche Nachricht-
Von: Prakash Premkumar [mailto:prakash.p...@gmail.com]
Gesendet: Mittwoch, 24. September 2014 12:54
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Results of Joins in sqlite

Thanks a lot Hick,for your approach.
With the approach you suggested, we are creating extra queries and if the join 
is on n tables there will be n+1 queries , and each query will have to go 
through a query planning stage.
Is there an alternative idea ?

Thanks a lot
Prakash

On Wed, Sep 24, 2014 at 3:34 PM, Hick Gunter <h...@scigames.at> wrote:

> You are free to build your own result conversion routine on top of the
> SQLite Api.
>
>
> May I suggest selecting the rowids of the tables too i.e.
>
> SELECT t1.rowid, t2.rowid, t3.rowid, <.. more fields ...> FROM
> <...your join...>;
>
> When you first come across a new rowid you can create your memory
> object and populate it from the required fields.
> The disadvantage ist hat SQLite will still retrieve all requested
> fields for each result row.
>
>
> Or maybe you might like to
>
> SELECT t1.rowid, t2.rowid, t3.rowid FROM <...your join...>;
>
> and then populate your structure by binding the rowid received and
> executing the appropriate
>
> SELECT <...t1 fields> FROM t1 where t1.rowid=?; SELECT <...t2 fields>
> FROM t2 where t2.rowid=?; SELECT <...t3 fields> FROM t3 where
> t3.rowid=?;
>
>
>
> -Ursprüngliche Nachricht-
> Von: Prakash Premkumar [mailto:prakash.p...@gmail.com]
> Gesendet: Mittwoch, 24. September 2014 09:32
> An: General Discussion of SQLite Database
> Betreff: Re: [sqlite] Results of Joins in sqlite
>
> Thanks for the reply Clemens. Yes.I'm sorry. It will not have 8 columns.
>
> When one result row comes in , I want to create an object for each
> table (My application map one struct to one table) and put the columns
> of respective tables in their individual structs. i,e  I would set
> column 0 and column 1 in result row to the struct of T1 and column 2
> and 3 to that of T2 and so on.
>
> and I set pointers between T1 and T2 , T2 and T3.
> When a new row comes in and if the object for that row is already created.
> i.e if r11 again comes as output, I will not create a new
> object,instead I would use the old object and set pointers between the
> old T1 and object and the T2 object (if r21 is new,else do not set
> pointers and do not create
> objects)
>
> similarly for T3. Thus,for the above case (the example we discussed),
> there will be one pointer from T1 row to T2 row and 3 pointers from T2
> row to the
> 3 T3 rows(one pointer per row).
>
> The end data structure will look like a graph.
>
> I want the API to return this graph./ is there an alternative ?
> API call : graph* sqlite3_join(select_stmt*)
>
> Thanks
> Prakash
>
>
>
>
> On Wed, Sep 24, 2014 at 12:45 PM, Clemens Ladisch <clem...@ladisch.de>
> wrote:
>
> > Prakash Premkumar wrote:
> > > Let's say I have tables T1,T2 and T3 with 2 columns each and I am
> > joining them.
> > > The result rows will have 8 columns each.
> >
> > No.  The result will have between 4 and 6 columns, depending on how
> > you do the joins.
> >
> > Example:
> >
> > CREATE TABLE T1(ID1, Name);
> > INSERT INTO "T1" VALUES('r11',NULL); CREATE TABLE T2(ID2, ID1);
> > INSERT INTO "T2" VALUES('r21','r11'); CREATE TABLE T3(ID3, ID2);
> > INSERT INTO "T3" VALUES('r31','r21'); INSERT INTO "T3"
> > VALUES('r32','r21'); INSERT INTO "T3" VALUES('r33','r21');
> >
> > SELECT * FROM T1 NATURAL JOIN T2 NATURAL JOIN T3;
> >
> > ID1 NameID2 ID3
> > --  --  --  --
> > r11 r21 r31
> > r11 r21 r32
> > r11 r21 r33
> >
> > > sqlite produces 3 result rows , but I would like to produce one
> > > result
> > row
> > > where the results are linked and the rows r11 and r21 are not
> > repeated.(It
> > > is similar to an adjacency list representation)
> >
> > The output of an SQLite query always has a fixed number of columns,
> > and for specific data, a fixed number of rows.
> >
> > Please specify exactly how the output for this example data should
> > look like.
> >
> > > The efficiency I gain with the 2nd approach, is the reduction is
> > > in the number of copies .i.e each row is copied only once.
> >
> > Your p

Re: [sqlite] Results of Joins in sqlite

[Simon Slavin]

On 24 Sep 2014, at 11:53am, Prakash Premkumar  wrote:

> Thanks a lot Hick,for your approach.
> With the approach you suggested, we are creating extra queries and if the
> join is on n tables there will be n+1 queries , and each query will have to
> go through a query planning stage.
> Is there an alternative idea ?

Execute the SELECT with the JOIN you originally described and pick the results 
apart in your programming language instead of importing each row of the result 
into a separate object.  In other words, write your program to do this piece of 
analysis you described:

> When a new row comes in and if the object for that row is already created.
> i.e if r11 again comes as output, I will not create a new object,instead I
> would use the old object and set pointers between the old T1 and object and
> the T2 object (if r21 is new,else do not set pointers and do not create
> objects)

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

Re: [sqlite] Results of Joins in sqlite

[Prakash Premkumar]

Thanks a lot Hick,for your approach.
With the approach you suggested, we are creating extra queries and if the
join is on n tables there will be n+1 queries , and each query will have to
go through a query planning stage.
Is there an alternative idea ?

Thanks a lot
Prakash

On Wed, Sep 24, 2014 at 3:34 PM, Hick Gunter <h...@scigames.at> wrote:

> You are free to build your own result conversion routine on top of the
> SQLite Api.
>
>
> May I suggest selecting the rowids of the tables too i.e.
>
> SELECT t1.rowid, t2.rowid, t3.rowid, <.. more fields ...> FROM <...your
> join...>;
>
> When you first come across a new rowid you can create your memory object
> and populate it from the required fields.
> The disadvantage ist hat SQLite will still retrieve all requested fields
> for each result row.
>
>
> Or maybe you might like to
>
> SELECT t1.rowid, t2.rowid, t3.rowid FROM <...your join...>;
>
> and then populate your structure by binding the rowid received and
> executing the appropriate
>
> SELECT <...t1 fields> FROM t1 where t1.rowid=?;
> SELECT <...t2 fields> FROM t2 where t2.rowid=?;
> SELECT <...t3 fields> FROM t3 where t3.rowid=?;
>
>
>
> -Ursprüngliche Nachricht-
> Von: Prakash Premkumar [mailto:prakash.p...@gmail.com]
> Gesendet: Mittwoch, 24. September 2014 09:32
> An: General Discussion of SQLite Database
> Betreff: Re: [sqlite] Results of Joins in sqlite
>
> Thanks for the reply Clemens. Yes.I'm sorry. It will not have 8 columns.
>
> When one result row comes in , I want to create an object for each table
> (My application map one struct to one table) and put the columns of
> respective tables in their individual structs. i,e  I would set column 0
> and column 1 in result row to the struct of T1 and column 2 and 3 to that
> of T2 and so on.
>
> and I set pointers between T1 and T2 , T2 and T3.
> When a new row comes in and if the object for that row is already created.
> i.e if r11 again comes as output, I will not create a new object,instead I
> would use the old object and set pointers between the old T1 and object and
> the T2 object (if r21 is new,else do not set pointers and do not create
> objects)
>
> similarly for T3. Thus,for the above case (the example we discussed),
> there will be one pointer from T1 row to T2 row and 3 pointers from T2 row
> to the
> 3 T3 rows(one pointer per row).
>
> The end data structure will look like a graph.
>
> I want the API to return this graph./ is there an alternative ?
> API call : graph* sqlite3_join(select_stmt*)
>
> Thanks
> Prakash
>
>
>
>
> On Wed, Sep 24, 2014 at 12:45 PM, Clemens Ladisch <clem...@ladisch.de>
> wrote:
>
> > Prakash Premkumar wrote:
> > > Let's say I have tables T1,T2 and T3 with 2 columns each and I am
> > joining them.
> > > The result rows will have 8 columns each.
> >
> > No.  The result will have between 4 and 6 columns, depending on how
> > you do the joins.
> >
> > Example:
> >
> > CREATE TABLE T1(ID1, Name);
> > INSERT INTO "T1" VALUES('r11',NULL);
> > CREATE TABLE T2(ID2, ID1);
> > INSERT INTO "T2" VALUES('r21','r11');
> > CREATE TABLE T3(ID3, ID2);
> > INSERT INTO "T3" VALUES('r31','r21');
> > INSERT INTO "T3" VALUES('r32','r21');
> > INSERT INTO "T3" VALUES('r33','r21');
> >
> > SELECT * FROM T1 NATURAL JOIN T2 NATURAL JOIN T3;
> >
> > ID1 NameID2 ID3
> > --  --  --  --
> > r11 r21 r31
> > r11 r21 r32
> > r11 r21 r33
> >
> > > sqlite produces 3 result rows , but I would like to produce one
> > > result
> > row
> > > where the results are linked and the rows r11 and r21 are not
> > repeated.(It
> > > is similar to an adjacency list representation)
> >
> > The output of an SQLite query always has a fixed number of columns,
> > and for specific data, a fixed number of rows.
> >
> > Please specify exactly how the output for this example data should
> > look like.
> >
> > > The efficiency I gain with the 2nd approach, is the reduction is in
> > > the number of copies .i.e each row is copied only once.
> >
> > Your program has to read either the value of ID1, to compare it with
> > the last one, or some other indication that there is a new T1 row.
> > What's the difference?  Or how exactly should the API look like?
> >
> >
> > Regards,
> > Clemens
> > _

Re: [sqlite] Results of Joins in sqlite

[Hick Gunter]

You are free to build your own result conversion routine on top of the SQLite 
Api.


May I suggest selecting the rowids of the tables too i.e.

SELECT t1.rowid, t2.rowid, t3.rowid, <.. more fields ...> FROM <...your 
join...>;

When you first come across a new rowid you can create your memory object and 
populate it from the required fields.
The disadvantage ist hat SQLite will still retrieve all requested fields for 
each result row.


Or maybe you might like to

SELECT t1.rowid, t2.rowid, t3.rowid FROM <...your join...>;

and then populate your structure by binding the rowid received and executing 
the appropriate

SELECT <...t1 fields> FROM t1 where t1.rowid=?;
SELECT <...t2 fields> FROM t2 where t2.rowid=?;
SELECT <...t3 fields> FROM t3 where t3.rowid=?;



-Ursprüngliche Nachricht-
Von: Prakash Premkumar [mailto:prakash.p...@gmail.com]
Gesendet: Mittwoch, 24. September 2014 09:32
An: General Discussion of SQLite Database
Betreff: Re: [sqlite] Results of Joins in sqlite

Thanks for the reply Clemens. Yes.I'm sorry. It will not have 8 columns.

When one result row comes in , I want to create an object for each table (My 
application map one struct to one table) and put the columns of respective 
tables in their individual structs. i,e  I would set column 0 and column 1 in 
result row to the struct of T1 and column 2 and 3 to that of T2 and so on.

and I set pointers between T1 and T2 , T2 and T3.
When a new row comes in and if the object for that row is already created.
i.e if r11 again comes as output, I will not create a new object,instead I 
would use the old object and set pointers between the old T1 and object and the 
T2 object (if r21 is new,else do not set pointers and do not create
objects)

similarly for T3. Thus,for the above case (the example we discussed), there 
will be one pointer from T1 row to T2 row and 3 pointers from T2 row to the
3 T3 rows(one pointer per row).

The end data structure will look like a graph.

I want the API to return this graph./ is there an alternative ?
API call : graph* sqlite3_join(select_stmt*)

Thanks
Prakash




On Wed, Sep 24, 2014 at 12:45 PM, Clemens Ladisch <clem...@ladisch.de>
wrote:

> Prakash Premkumar wrote:
> > Let's say I have tables T1,T2 and T3 with 2 columns each and I am
> joining them.
> > The result rows will have 8 columns each.
>
> No.  The result will have between 4 and 6 columns, depending on how
> you do the joins.
>
> Example:
>
> CREATE TABLE T1(ID1, Name);
> INSERT INTO "T1" VALUES('r11',NULL);
> CREATE TABLE T2(ID2, ID1);
> INSERT INTO "T2" VALUES('r21','r11');
> CREATE TABLE T3(ID3, ID2);
> INSERT INTO "T3" VALUES('r31','r21');
> INSERT INTO "T3" VALUES('r32','r21');
> INSERT INTO "T3" VALUES('r33','r21');
>
> SELECT * FROM T1 NATURAL JOIN T2 NATURAL JOIN T3;
>
> ID1 NameID2 ID3
> --  --  --  --
> r11 r21 r31
> r11 r21 r32
> r11 r21 r33
>
> > sqlite produces 3 result rows , but I would like to produce one
> > result
> row
> > where the results are linked and the rows r11 and r21 are not
> repeated.(It
> > is similar to an adjacency list representation)
>
> The output of an SQLite query always has a fixed number of columns,
> and for specific data, a fixed number of rows.
>
> Please specify exactly how the output for this example data should
> look like.
>
> > The efficiency I gain with the 2nd approach, is the reduction is in
> > the number of copies .i.e each row is copied only once.
>
> Your program has to read either the value of ID1, to compare it with
> the last one, or some other indication that there is a new T1 row.
> What's the difference?  Or how exactly should the API look like?
>
>
> Regards,
> Clemens
> ___
> 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


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.


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

Re: [sqlite] Results of Joins in sqlite

[Prakash Premkumar]

Thanks for the reply Clemens. Yes.I'm sorry. It will not have 8 columns.

When one result row comes in , I want to create an object for each table
(My application map one struct to one table) and put the columns of
respective tables in their individual structs. i,e  I would set column 0
and column 1 in result row to the struct of T1 and column 2 and 3 to that
of T2 and so on.

and I set pointers between T1 and T2 , T2 and T3.
When a new row comes in and if the object for that row is already created.
i.e if r11 again comes as output, I will not create a new object,instead I
would use the old object and set pointers between the old T1 and object and
the T2 object (if r21 is new,else do not set pointers and do not create
objects)

similarly for T3. Thus,for the above case (the example we discussed), there
will be one pointer from T1 row to T2 row and 3 pointers from T2 row to the
3 T3 rows(one pointer per row).

The end data structure will look like a graph.

I want the API to return this graph./ is there an alternative ?
API call : graph* sqlite3_join(select_stmt*)

Thanks
Prakash




On Wed, Sep 24, 2014 at 12:45 PM, Clemens Ladisch 
wrote:

> Prakash Premkumar wrote:
> > Let's say I have tables T1,T2 and T3 with 2 columns each and I am
> joining them.
> > The result rows will have 8 columns each.
>
> No.  The result will have between 4 and 6 columns, depending on how you
> do the joins.
>
> Example:
>
> CREATE TABLE T1(ID1, Name);
> INSERT INTO "T1" VALUES('r11',NULL);
> CREATE TABLE T2(ID2, ID1);
> INSERT INTO "T2" VALUES('r21','r11');
> CREATE TABLE T3(ID3, ID2);
> INSERT INTO "T3" VALUES('r31','r21');
> INSERT INTO "T3" VALUES('r32','r21');
> INSERT INTO "T3" VALUES('r33','r21');
>
> SELECT * FROM T1 NATURAL JOIN T2 NATURAL JOIN T3;
>
> ID1 NameID2 ID3
> --  --  --  --
> r11 r21 r31
> r11 r21 r32
> r11 r21 r33
>
> > sqlite produces 3 result rows , but I would like to produce one result
> row
> > where the results are linked and the rows r11 and r21 are not
> repeated.(It
> > is similar to an adjacency list representation)
>
> The output of an SQLite query always has a fixed number of columns, and
> for specific data, a fixed number of rows.
>
> Please specify exactly how the output for this example data should look
> like.
>
> > The efficiency I gain with the 2nd approach, is the reduction is in the
> > number of copies .i.e each row is copied only once.
>
> Your program has to read either the value of ID1, to compare it with the
> last one, or some other indication that there is a new T1 row.  What's
> the difference?  Or how exactly should the API look like?
>
>
> Regards,
> Clemens
> ___
> 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] Results of Joins in sqlite

[Clemens Ladisch]

Prakash Premkumar wrote:
> Let's say I have tables T1,T2 and T3 with 2 columns each and I am joining 
> them.
> The result rows will have 8 columns each.

No.  The result will have between 4 and 6 columns, depending on how you
do the joins.

Example:

CREATE TABLE T1(ID1, Name);
INSERT INTO "T1" VALUES('r11',NULL);
CREATE TABLE T2(ID2, ID1);
INSERT INTO "T2" VALUES('r21','r11');
CREATE TABLE T3(ID3, ID2);
INSERT INTO "T3" VALUES('r31','r21');
INSERT INTO "T3" VALUES('r32','r21');
INSERT INTO "T3" VALUES('r33','r21');

SELECT * FROM T1 NATURAL JOIN T2 NATURAL JOIN T3;

ID1 NameID2 ID3
--  --  --  --
r11 r21 r31
r11 r21 r32
r11 r21 r33

> sqlite produces 3 result rows , but I would like to produce one result row
> where the results are linked and the rows r11 and r21 are not repeated.(It
> is similar to an adjacency list representation)

The output of an SQLite query always has a fixed number of columns, and
for specific data, a fixed number of rows.

Please specify exactly how the output for this example data should look
like.

> The efficiency I gain with the 2nd approach, is the reduction is in the
> number of copies .i.e each row is copied only once.

Your program has to read either the value of ID1, to compare it with the
last one, or some other indication that there is a new T1 row.  What's
the difference?  Or how exactly should the API look like?


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

Re: [sqlite] Results of Joins in sqlite

[Prakash Premkumar]

To further clarify, the result of a join forms a row that has a new schema
. (the new schema is derived from the schemas of the tables participating
in the joins.) I would like to retain the old schema  in the join result as
well, so there is a split between which column belongs / is coming from
which table.

Thanks
Prakash

On Wed, Sep 24, 2014 at 11:03 AM, Prakash Premkumar 
wrote:

> Hi,
>
> Let's say I have tables T1,T2 and T3 with 2 columns each and I am joining
> them.
> The result rows will have 8 columns each.
>
> Let's say an output of the join is:
>
> r11,r21,r31
> r11,r21,r32
> r11,r21,r33
>
> where r1i is the i th row in T1, r2i is the i th row in T2 and r3i is the
> ith row in T3:
>
> sqlite produces 3 result rows , but I would like to produce one result row
> where,
>
>   r11
> |
>   r21
>  /   |   \
> r31   r32  r33
>
> where the results are linked and the rows r11 and r21 are not repeated.(It
> is similar to an adjacency list representation)
>
> The efficiency I gain with the 2nd approach, is the reduction is in the
> number of copies .i.e each row is copied only once.
>
> Now , I traverse the graph depth first and get the required results as
> r11,r21,r31
> r11,r21,r32
> r11,r21,r33
>
> are there any hacks in sqlite which does this ? or can you give me some
> pointers as in how I should proceed ? I would like to have the linked
> output of the join at any point of time and I will traverse the link depth
> first to retrieve the individual rows.
>
> Thanks a lot for your help .
>
> Prakash
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users