Thread: [SQLObject] ViewSQLObject ID's
SQLObject is a Python ORM.
Brought to you by:
ianbicking,
phd
From: Hans G. S. <geo...@sc...> - 2012-06-21 07:40:08
|
Hi, I decided to try the ViewSQLObject class in an attempt to speed up my system by letting the server do some of the table joins in single queries, instead of joining in python on the client side. But I get stuck at the id. I assume that ViewSQLObject objects need an immutable and unique ID (is that right?). All the examples I have seen just copies the ID from one of the constituent SQLObject-s. In my case I am joining on a many-to-many relationship, so I assume I need to aggregate multiple ID fields into one. Can anyone offer a quick example showing how this could be done? TIA -- :-- Hans Georg |
From: Petr J. <pet...@tp...> - 2012-06-21 08:15:15
|
Hi, I am not sure if following is what are you looking for, but in my case, I have a view defined manually on the SQL server side. in the SQLObject I am working with the VIEW like it with an ordinary table, see my code below: =========== SQLObject part =============== class HashView(SQLObject): class sqlmeta: fromDatabase = True columnList = True ========================================= ================== server part ===================================== CREATE VIEW HASH_VIEW (ID, OSOBY_ID, JMENO_1, JMENO_2, IBUTTON, MD5_HASH, T_STAMP) AS select m.id, deriv.osoby_id, deriv.jmeno_1, deriv.jmeno_2, deriv.ibutton, m.md5_hash, deriv.t_stamp from hash m join ( select m1.osoby_id, p.jmeno_1, p.jmeno_2, p.ibutton, max(m1.t_stamp) from hash m1 join osoby p on (p.id = m1.osoby_id) group by 1,2,3,4) as deriv (osoby_id, jmeno_1, jmeno_2, ibutton, t_stamp) on m.osoby_id = deriv.osoby_id where deriv.t_stamp = m.t_stamp; GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE ON HASH_VIEW TO SYSDBA WITH GRANT OPTION; ===================================================================== HTH Petr |
From: Hans G. S. <geo...@sc...> - 2012-06-21 10:27:44
|
On Thu, Jun 21, 2012 at 10:15:03AM +0200, Petr Jakeš wrote: > Hi, > I am not sure if following is what are you looking for, but in my case, I > have a view defined manually on the SQL server side. I suppose I was looking for the ViewSQLObject class as a way to achieve the same thing with a lesser need to learn more SQL ... Anyway, thanks for the code example. I reckon I can figure out how it works by reading up on hash joins. > in the SQLObject I am working with the VIEW like it with an ordinary table, > see my code below: > > =========== SQLObject part =============== > class HashView(SQLObject): > class sqlmeta: > fromDatabase = True > columnList = True > ========================================= > > ================== server part ===================================== > > CREATE VIEW HASH_VIEW (ID, OSOBY_ID, JMENO_1, JMENO_2, IBUTTON, MD5_HASH, > T_STAMP) > AS > select m.id, deriv.osoby_id, deriv.jmeno_1, deriv.jmeno_2, deriv.ibutton, > m.md5_hash, deriv.t_stamp > from hash m > join > ( > select m1.osoby_id, p.jmeno_1, p.jmeno_2, p.ibutton, max(m1.t_stamp) > from hash m1 > join osoby p on (p.id = m1.osoby_id) > group by 1,2,3,4) > as deriv (osoby_id, jmeno_1, jmeno_2, ibutton, t_stamp) on > m.osoby_id = deriv.osoby_id > > where deriv.t_stamp = m.t_stamp; > > GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE > ON HASH_VIEW TO SYSDBA WITH GRANT OPTION; > ===================================================================== > > HTH > > Petr -- :-- Hans Georg |
From: Petr J. <pet...@tp...> - 2012-06-21 16:38:21
|
I suppose I was looking for the ViewSQLObject class as a way to achieve > the same thing with a lesser need to learn more SQL ... > > Anyway, thanks for the code example. I reckon I can figure out how > it works by reading up on hash joins. > > Hmmm.... Views, IMHO, are just SELECTS transformed to views. It means not special SQL digging is necessary. Anyway, for some really complex SELETS, direct SQL is the way to go. Regards Petr |
From: Oleg B. <ph...@ph...> - 2012-06-21 17:37:14
|
On Thu, Jun 21, 2012 at 06:38:09PM +0200, Petr Jake?? <pet...@tp...> wrote: > Hmmm.... Views, IMHO, are just SELECTS transformed to views. It means not > special SQL digging is necessary. They are very convenient for factoring out common SELECTs for stored procedures, e.g., and for granting access to some parts of one table. Oleg. -- Oleg Broytman http://phdru.name/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Hans G. S. <geo...@sc...> - 2012-06-21 17:57:13
|
On Thu, Jun 21, 2012 at 06:38:09PM +0200, Petr Jakeš wrote: > Hmmm.... Views, IMHO, are just SELECTS transformed to views. It means not > special SQL digging is necessary. Well. I did not understand the syntax of your view definition. In particular, I do not understand the meaning of «hash». Is it a keyword in some dialect of SQL? (I don't find it in postgresql.) If it is a table name, what is then m/m1? Second question, what makes sure that the id, presumably m.id, is unique for every record in the view? I think I saw some warnings against using SQLObjects to represent server side views, but if you say it works, it is worth a try. > Anyway, for some really complex SELETS, direct SQL is the way to go. I can live with that in some cases, but it is important for me to preserve loose coupling in the object-oriented model. However, the real challenge, whether the view is defined client side or server side, is to construct the unique and immutable ID which SQLObject requires. It would be useful to have both the SQL and the python solution to that ... Your code for reference: CREATE VIEW HASH_VIEW (ID, OSOBY_ID, JMENO_1, JMENO_2, IBUTTON, MD5_HASH, T_STAMP) AS select m.id, deriv.osoby_id, deriv.jmeno_1, deriv.jmeno_2, deriv.ibutton, m.md5_hash, deriv.t_stamp from hash m join ( select m1.osoby_id, p.jmeno_1, p.jmeno_2, p.ibutton, max(m1.t_stamp) from hash m1 join osoby p on (p.id = m1.osoby_id) group by 1,2,3,4) as deriv (osoby_id, jmeno_1, jmeno_2, ibutton, t_stamp) on m.osoby_id = deriv.osoby_id where deriv.t_stamp = m.t_stamp; -- :-- Hans Georg |
From: Petr J. <pet...@tp...> - 2012-06-21 18:44:26
|
> Well. I did not understand the syntax of your view definition. > In particular, I do not understand the meaning of «hash». Is > it a keyword in some dialect of SQL? (I don't find it in postgresql.) > If it is a table name, what is then m/m1? Second question, what makes > sure that the id, presumably m.id, is unique for every record in the > view? > I am on the Firebird. Example view is just an example, how really complex SELECTs can be used via VIEW. In the example, derived tables are used http://www.sqlteam.com/article/using-derived-tables-to-calculate-aggregate-values http://www.mssqltips.com/sqlservertip/1042/using-derived-tables-to-simplify-the-sql-server-query-process/ hash is a table name in my database. m/m1 is an alias. An alias example: select * from table t where t.ID = 1 Acording the unique m.id: generally it depends about the construction of your select. In my example I am pretty sure it returns unique IDs only. > I think I saw some warnings against using SQLObjects to represent > server side views, but if you say it works, it is worth a try. > It works very well, not problems encountered. > > > Anyway, for some really complex SELETS, direct SQL is the way to go. > > I can live with that in some cases, but it is important for me to > preserve loose coupling in the object-oriented model. > I did not find solution, how to solve above mentioned SELECT using the SQLObject. Even using raw SELECT. I mean something like: result = con.queryAllDescription("rawSqlQuerry"). Because of that I am using views and it works. > > However, the real challenge, whether the view is defined client > side or server side, is to construct the unique and immutable ID > which SQLObject requires. In the Firebird, AFAIK, the views are immutable. Anyway, I am using it just to dig the data out from the database. It would be useful to have both the SQL and the python solution to that ... > I did not find pure SQLObject solution for complex queries and I think SQLObject is not intended for that. For digging data out of the database, I am using raw SQL selects quite often. Sometimes I am using _get _set methods to achieve my goals, see: Using sqlmeta <http://sqlobject.org/SQLObject.html#using-sqlmeta>, or my example below def _get_recentHash(self): try: hashView = list(HashView.selectBy(osobyID=self.id))[0] return hashView.md5Hash except (AttributeError, IndexError): return None Petr |
From: Hans G. S. <geo...@sc...> - 2012-06-21 19:06:40
|
On Thu, Jun 21, 2012 at 08:44:18PM +0200, Petr Jakeš wrote: > Acording the unique m.id: generally it depends about the construction of > your select. In my example I am pretty sure it returns unique IDs only. Obviously, if you only have many-to-one relationship (from left to right) you can just use the ID of the left hand table. AFAICS that must be your case. My problem is that the join involves two many-to-many relationships, so I cannot even use the ID's of the relation table. > I did not find solution, how to solve above mentioned SELECT using the > SQLObject. Even using raw SELECT. > I mean something like: result = con.queryAllDescription("rawSqlQuerry"). > Because of that I am using views and it works. As far as I can see, you could do the same with the ViewSQLObject class. > > However, the real challenge, whether the view is defined client > > side or server side, is to construct the unique and immutable ID > > which SQLObject requires. > > In the Firebird, AFAIK, the views are immutable. Anyway, I am using it > just to dig the data out from the database. I was talking about the ID, or primary key in SQL terms. A view does not need one, but an SQLObject class does (according to the doc's). Making it immutable is hardly the problem here; the challenge is uniqueness. > I did not find pure SQLObject solution for complex queries and I think > SQLObject is not intended for that. Judging by the doc's, ViewSQLObject is intended to do some of that, but you are right that SQLObject encourages client side solutions. -- :-- Hans Georg |
From: Petr J. <pet...@tp...> - 2012-06-21 20:45:47
|
> > Obviously, if you only have many-to-one relationship (from left to > right) you can just use the ID of the left hand table. AFAICS that > must be your case. My problem is that the join involves two many-to-many > relationships, so I cannot even use the ID's of the relation table. > Hmmm... anyway, what about to try to construct the view (with not unique IDs as a result) on the server side and let the SQLObject "chew" this view "table". I think it worth to try it. But I am sure Oleg is reading this and gives his comments about the issue. > > > I did not find solution, how to solve above mentioned SELECT using the > > SQLObject. Even using raw SELECT. > > I mean something like: result = con.queryAllDescription("rawSqlQuerry"). > > Because of that I am using views and it works. > > As far as I can see, you could do the same with the ViewSQLObject class. > Did not think about this (I do not know the ViewSQLObject class). My feeling is I do not need more CPU load on the client side (which IMHO ViewSQLObject is). > Regards Petr |
From: Oleg B. <ph...@ph...> - 2012-06-21 20:56:08
|
On Thu, Jun 21, 2012 at 10:45:40PM +0200, Petr Jake?? <pet...@tp...> wrote: > I am sure Oleg is reading this and > gives his comments about the issue. I think there have to be a way to generate id's for a complex VIEW by, e.g., combining id's into a string with a delimiter; SQLObject supports string id. Or calculating a hash value of that combined id and using the hash as the id. Oleg. -- Oleg Broytman http://phdru.name/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Hans G. S. <geo...@sc...> - 2012-06-22 06:35:02
|
On Thu, Jun 21, 2012 at 10:45:40PM +0200, Petr Jakeš wrote: > Did not think about this (I do not know the ViewSQLObject class). My > feeling is I do not need more CPU load on the client side (which IMHO > ViewSQLObject is). I am not sure that makes a difference. There is no reason why ViewSQLObject should cause more client-side processing. The only difference would be that ViewSQLObject would construct a much more complex SELECT query, where your approach takes advantage of the server-side view. All the heavy work would be done processing the query on the server either way, and the response would be the same. That said, I don't know what ViewSQLObject actually does, but I think that's what it should do :-) On Fri, Jun 22, 2012 at 12:56:05AM +0400, Oleg Broytman wrote: > I think there have to be a way to generate id's for a complex VIEW > by, e.g., combining id's into a string with a delimiter; SQLObject > supports string id. Or calculating a hash value of that combined id and > using the hash as the id. I remember to have seen an example on how to do that, but I could not find it when I tried. There are some caveats to using string ID's, according to the doc's, are they only relevant when creating records and not when reading them? When using ViewSQLObject, is something like this supposed to work? class sqlmeta: idName = str(table1.q.id) + ":" + str(table2.q.id) Or am I totally off track? -- :-- Hans Georg |
From: Oleg B. <ph...@ph...> - 2012-06-22 10:33:50
|
On Fri, Jun 22, 2012 at 07:34:50AM +0100, Hans Georg Schaathun <geo...@sc...> wrote: > On Fri, Jun 22, 2012 at 12:56:05AM +0400, Oleg Broytman wrote: > > I think there have to be a way to generate id's for a complex VIEW > > by, e.g., combining id's into a string with a delimiter; SQLObject > > supports string id. Or calculating a hash value of that combined id and > > using the hash as the id. > > I remember to have seen an example on how to do that, but I could not > find it when I tried. There are some caveats to using string ID's, > according to the doc's, are they only relevant when creating records > and not when reading them? > > When using ViewSQLObject, is something like this supposed to work? > class sqlmeta: > idName = str(table1.q.id) + ":" + str(table2.q.id) > Or am I totally off track? Not completely. Right way of thinking, wrong direction. SQLObject doesn't "unparse" python expressions and converts them to SQL expressions. To do this there is sqlbuilder.SQLExpression. Unfortunately, in this particular case SQLExpression is not of any help (though IWBN if someone patches SQLObject to allow such a thing) - currently SQLObject expects idName to be a string. What I tried to suggest was: combine id's into one string on the server side, perhaps in the definition of a VIEW. SELECT CAST(t1.id as TEXT) + ':' + CAST(t2.id as TEXT) AS id, ... Oleg. -- Oleg Broytman http://phdru.name/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Petr J. <pet...@tp...> - 2012-06-22 11:16:14
|
> SELECT CAST(t1.id as TEXT) + ':' + CAST(t2.id as TEXT) AS id, ... > > Just wondering what is delimiter helpful for? Just to recognize t1.id and t2.id later on? Petr |
From: Oleg B. <ph...@ph...> - 2012-06-22 11:26:55
|
On Fri, Jun 22, 2012 at 01:16:02PM +0200, Petr Jake?? <pet...@tp...> wrote: > > SELECT CAST(t1.id as TEXT) + ':' + CAST(t2.id as TEXT) AS id, ... > > > > > Just wondering what is delimiter helpful for? Just to recognize t1.id and > t2.id later on? Yes. And to distinguish "1:11" and "11:1" - without a delimiter you get two different rows with the same id; with pure integer id's of a limited range that can be fixed in a different way: SELECT t1.id*1000000000 + t2.id, ... but the idea is the same. Oleg. -- Oleg Broytman http://phdru.name/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Hans G. S. <geo...@sc...> - 2012-06-22 12:22:53
|
On Fri, Jun 22, 2012 at 03:26:42PM +0400, Oleg Broytman wrote: > Yes. And to distinguish "1:11" and "11:1" - without a delimiter you > get two different rows with the same id; with pure integer id's of a > limited range that can be fixed in a different way: > > SELECT t1.id*1000000000 + t2.id, ... > > but the idea is the same. In many ways, integers would be preferable, but using the string with a non-digit delimiter saves me the trouble of figuring out how large the constituent integers may be, and how large the aggregate integer would be allowed to become. In short, a quick and dirty solution to ensure uniqueness. -- :-- Hans Georg |
From: Hans G. S. <geo...@sc...> - 2012-06-22 12:50:38
|
Hi again, On Thu, Jun 21, 2012 at 10:15:03AM +0200, Petr Jakeš wrote: > Hi, > I am not sure if following is what are you looking for, but in my case, I > have a view defined manually on the SQL server side. > in the SQLObject I am working with the VIEW like it with an ordinary table, > see my code below: your approach does not seem to work; I get AssertionError: No primary key found in table 'scaled_feature_value' As far as I can see, there is no way to define a primary key on a view. I have managed to get an id column which should be unique. Is there something I have missed? Such as a way to tell SQLObject not to check for the restriction? > > =========== SQLObject part =============== > class HashView(SQLObject): > class sqlmeta: > fromDatabase = True > columnList = True > ========================================= > > ================== server part ===================================== > > CREATE VIEW HASH_VIEW (ID, OSOBY_ID, JMENO_1, JMENO_2, IBUTTON, MD5_HASH, > T_STAMP) > AS > select m.id, deriv.osoby_id, deriv.jmeno_1, deriv.jmeno_2, deriv.ibutton, > m.md5_hash, deriv.t_stamp > from hash m > join > ( > select m1.osoby_id, p.jmeno_1, p.jmeno_2, p.ibutton, max(m1.t_stamp) > from hash m1 > join osoby p on (p.id = m1.osoby_id) > group by 1,2,3,4) > as deriv (osoby_id, jmeno_1, jmeno_2, ibutton, t_stamp) on > m.osoby_id = deriv.osoby_id > > where deriv.t_stamp = m.t_stamp; > > GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE > ON HASH_VIEW TO SYSDBA WITH GRANT OPTION; > ===================================================================== > > HTH > > Petr -- :-- Hans Georg |
From: Oleg B. <ph...@ph...> - 2012-06-22 13:05:26
|
On Fri, Jun 22, 2012 at 01:50:26PM +0100, Hans Georg Schaathun <geo...@sc...> wrote: > Hi again, > > On Thu, Jun 21, 2012 at 10:15:03AM +0200, Petr Jake?? wrote: > > Hi, > > I am not sure if following is what are you looking for, but in my case, I > > have a view defined manually on the SQL server side. > > in the SQLObject I am working with the VIEW like it with an ordinary table, > > see my code below: > > your approach does not seem to work; I get > > AssertionError: No primary key found in table 'scaled_feature_value' > > As far as I can see, there is no way to define a primary key on a view. > I have managed to get an id column which should be unique. Is there > something I have missed? Such as a way to tell SQLObject not to check > for the restriction? The bug was fixed in SQLObject releases 1.2.3 and 1.3.1. Oleg. -- Oleg Broytman http://phdru.name/ ph...@ph... Programmers don't die, they just GOSUB without RETURN. |
From: Hans G. S. <geo...@sc...> - 2012-06-22 19:41:17
|
On Fri, Jun 22, 2012 at 05:05:15PM +0400, Oleg Broytman wrote: > The bug was fixed in SQLObject releases 1.2.3 and 1.3.1. Ah. Thanks. And now at last I figured out how to upgrade :-) -- :-- Hans Georg |