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 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 Name ID2 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 >> > >> _______________________________________________ >> 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 >> >_______________________________________________ >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
