Hi Gambit,
You raise some interesting questions which I have much interest in. I'd
like to add my opinion to try and get other opinions so maybe we can all
learn something from someone.
Hey Jonathan, and everyone else,
On the subject of large-table databases and representing them in an ORM
environment, could I interest you in expanding on the following issues? These
issues are more at the 'design' level then the typical 'it doesn't work'
issues we deal with here, but with a collection of fairly clued-up people like
this, I'd love to hear what some of you think.
1) How do you manage your interactions with the non-mapped tables?
In my case, if I use the table directly and repeatedly, it is mapped.
Pretty much if the ORM enhances my productivity, reduces the amount of
thought I need to put into a db task I will use it. If I have to think
more then twice about the ORM solution vs the plain SQL solution, I go
the SQL route. This usually means all my complex queries for reporting
are usually done in straight SQL whilst all the mundane, CRUD stuff is
done using the ORM. Simple to medium complexity selects are done using
the ORM also, with the complex selects being nicely seperated in code
for easy porting if necessary (which is never in my own experience;
which is another issue I'd like to talk about)
For reporting/once off like queries I sometimes go straight resultproxy.
2) What kind of factors do you take into account in deciding whether to
represent a table within the ORM or not? Obviously there's a strong
"does it make sense" judgement, but if you can be verbose... :)
3) Do you try to create Objects's that spread across multiple tables (ie
against arbitrary selects) or use properties or custom select statements
to access related data?
Personally my domain model is quite straight forward. I've read Martin
Fowler's essays on his opinion (I say opinion because OO to me is an art
not a science) about correct domain models with business logic and all,
but can't say I have been able to implement anything of the sort which
is clean IMO. I can't find a clean way for the seperation of business
logic/calculations and the persistence mechanism without polluting my
domain model with persistence code as well as the potential recursive
importing problem.
4) On a schema of this complexity, do stored procedures play a role, and if
so how do they interface with the ORM?
I am an advocate of keeping as much business logic/validation/checks as
close to the data as is possible. This creates an language/application
agnostic environment where you can be guaranteed of data integrity and
business correctness of data regardless of the applications using your
data. So based on this, yes "stored procedures" should play a big role
in any application.
Many people don't like using SP because they want database portability.
I guess this is a call everyone needs to make per project. Personally, I
would target a single capable database, use the ORM as much as possible,
keep SPs, and custom SQL nicely seperated just in case you need to port.
Porting these parts wouldn't be a show stopper to me, unless of course
the database you need to port to does not support certain features. If
this is the case, then you can port the SPs to your specific application
language. From first hand experience, this isn't as bad as it sounds if
your stored procs are well written. I have done this personally, and the
mapping is very close.
I'm not sure what you mean by interfacing, but you would obviously need
to refresh certain objects because the SP could potential change
anything in the database without the ORM knowing. This is where ORM
cache can create some issues, but I always do a session.clear() after I
do my stored procs/triggers.
5) How about any other advanced rdbms features such as views, triggers,
etc?
I use triggers extensively. I love them. They save me so much time. I
deal with them in the same way as I deal with SPs.
A strong ORM as SA is, from my impression, encourages the user to
develop the "logic" in (this case) Python, whereas a less representative
more SQL-ish interface encourages developers to implement within the
RDBMS-side, usually as SP/triggers/etc.
I'd like to disagree with this opinion with my own. I use an ORM to
avoid the mundane task of persisting and converting sql results into
more friendly domain models. Also being able to nicely construct
conditional sql is another reason. Writing business logic with it,
although possible and much nicer then writing them just using dbapi, is
still not as nice as using the RDBMS directly for this. eg. I have
warehouse system where the stock is always updated by a trigger when
another record is created/updated/deleted (stock_pick). The trigger
checks for available stock, and either puts stock back (on delete,
update), decrements stock (on insert, update) or throws an exception
"out of stock" if no stock is available. This is all done for me just by
creating, deleting or updating the stock_pick record. This guarantees
that my stock data is always correct regardless of who/what is
manipulating the data.
So all my applications don't have to worry about updating the stock
quantity. If I want stock, I just create a stock record, if i want to
put stock back, i just delete a record. This is where the ORM excels at
(mundane CRUD).
Again this is all assuming you don't have an application which spans
multiple database servers. I think this opens a totally new kettle of
fish which most ORMs would have difficulty dealing with anyway (in the
Python world at least).
There's a religious war at the
heart of this, for sure, but I'm curious for other peoples opinions.
I've read the various commentary regarding this topic including the
sermon by the high priest of the ROR church. They obviously didn't
convince me. I went out and tried both methods, i.e written BL in
application code and BL straight in the database, and made my own
choices based on my own experiences.
I would love to hear others experiences and opinions on this topic.
Huy
Much appreciate any comments people have :)
-G
On Monday, May 8, 2006, 10:44:32 PM, you wrote:
On May 7, 2006, at 4:55 PM, Michael Bayer wrote:
regardless of if you have a DB with 250 or 1000 tables. is it then
appropriate to have 1000 separate classes ?
no way in hell. that would be a mindfuck.
in my case, I have 200+ tables and about 50 classes / objects that
are mapped against them
would I rather
a - write 50 class definitions for sqlalchemy
b - have a script autogenerate 200 table definitions in the
sqlalchemy syntax , delete 150 of them, and then just extend 50 with
the inter-table mappngs
i'm solidly in the b camp
I have only seen one database schema that had several hundred
tables before. the guy basically represented every single element
of a large and complicated DTD as a separate table, i.e. <taga> was
one table, <tagb> was another, etc. To him,
=snip
you cant tell me thats the proper way to do something. and even if
it was, you dont represent an XML document with a different class
for each XML tag, you use a DOM tree, which uses about half a dozen
classes.
sure i can. i might have to practice saying it with a straight face
for a while, but yeah, i could definitely say that.
-------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
-------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
-------------------------------------------------------
Using Tomcat but need to do more? Need to support web services, security?
Get stuff done quickly with pre-integrated technology to make your job easier
Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
_______________________________________________
Sqlalchemy-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users
