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 > >> > > >> > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
