Re: [SQLObject] ViewSQLObject ID's

[Petr J. <pet...@tp...>]

> 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