On Mon, Feb 1, 2010 at 11:30 AM, Haron Media <[email protected]> wrote:
> Also, good number of row retrievals requires a hierarchical list, so I
> have a helper class that constructs multilevel arrays (which would be
> dictionaries in Pylons),
Oh, I do this too. If the controller really wants a dict or
hierarchical structure or scalar result rather than an iterable of ORM
instances, I calculate that and return it.
> I like to push only data related logic into stored procedures and
> triggers. If nothing then for one reason: tampering with pure SQL will
> have same result as if the application interface was used, otherwise one
> risks corrupting the data. Especially if more than one administrator is
> involved.
Well, they believe strongly in PostgreSQL and use its unique features
(which means the applications aren't portable to other SQLAlchemy
databases). The argument is that you get a lot of efficiency, data
integrity guarantees, and security if you push logic to the lowest
database level. So that you won't ever have another client
accessing/writing it in an inconsistent way. Plus it's neutral to
client programming languages.
> I also toyed with having no queries at all in the application except
> simple calls to stored procedures that deal with the data. This approach
> for example would turn models into nothing more than simple ORM maps,
> with a few extra methods that directly call stored procedures. I
> understand that this might lose the benefit of a certain level of
> "caching" that occurs within SQLAlchemy...
The SQLAlchemy session caches mapped records in case you ask for them
again. I'm not sure that matters much in a Pylons application. The
session is cleared after every request, and how often do you make
repeated calls for the same record within a request? You have the
record in a variable, why do you need to fetch it again?
Postgres functions can return ad hoc "rows" or "tables", so you'd have
to have a lot of ORM classes if you wanted to map all those. Either
that or just use the raw ResultProxy.
>> - Exceptable: convert PostgreSQL exceptions to Python exceptions.
>>
>
> Thanks, I'll be needing this!
It does what you said your code does: it catches general Postgres
exceptions and parses the message to raise a specific Python
exception.
>> - VerticallyChallenged: authorization via database roles, with
>> Repoze.who for authentication. (This one doesn't seem to be online
>> yet.)
>>
>
> Other than for DB administration, I don't see the point in this. :)
The argument is that Postgres' built-in authorization is better tested
than anything you can write on your own. Plus, by protecting the data
at the lowest database level, you can be sure that no Python or other
utility can bypass your security policy.
The downside is that it's meant for a limited number of Postgres
users, and is not attuned to a web application that may create dozens
of users in a week. So it only works if you can map your web users
into a few pre-specified database users. You would also have to log
into the database with the highest-permission user and then switch
down using a SQL statement, similar to Unix 'su'. But I'm not sure how
you'd go up in privilege again in another request (if you're reusing
the same connection).
The other thing is it doesn't have row-based permissions, only
table-based. Unless it was added in PostgreSQL 8.4. Although I suppose
you can mimic row-based permissions in a stored procedure.
The other issue is how to get all your stored procedures into the
database. The solution seems to be text SQL files in your version
control system.
> How else are SQL errors raised in Python, then? I suppose it is driver
> specific?
I did a few tests with MySQL (I'm not using Postgres myself yet) and got:
sqlalchemy.exc.ProgrammingError # SQL syntax
sqlalchemy.exc.IntegrityError # Duplicate primary key
OperationalError: (OperationalError) (1048, "Column 'name' cannot be null")
# I can't tell who raised this exception.
InvalidRequestError: The transaction is inactive due to a rollback in
a subtransaction. Issue rollback() to cancel the transaction.
# This was the primary error raised when the previous
# occurred during a session.commit().
FlushError: New instance <User at 0xa65b5ac> with identity key (<class
'rlink.model.auth.User'>, ('[email protected]',)) conflicts with
persistent instance <User at 0xa749e6c>
# Raised during another session.commit()
So it looks like all of them except maybe the third are generic
SQLAlchemy exceptions.
--
Mike Orr <[email protected]>
--
You received this message because you are subscribed to the Google Groups
"pylons-discuss" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/pylons-discuss?hl=en.
