Re: OO vs. RDB challenge

From: Costin Cozianu (c_cozianu_at_hotmail.com)
Date: 03/21/05 

Date: Mon, 21 Mar 2005 09:13:50 -0800

kurbylogic@hotmail.com wrote:
> I'm kinda comming into the discussion a bit late, so I appologize if I
> reiterate points already made, but here goes.
>
> First I completely disagree with the idea that if we only had enough
> persistant ram we wouldn't want or need a relational store. Its rather
> about choosing the correct tool for the task at hand. If you have a
> nail use a hammer if you have a screw use a screw-driver. SQL is
> designed to store and query large sets of structured data. Howerver, it
> isn't ideally suited for complex calculations or polymorphic behavior.
>
>

Ahem, you're right about complex calculation, not about "polymorphic
behavior" -- there's no such thing.

> So you've figured out how to prersist users/groups and their
> relationships in a database without any OO code... well done what do
> you indend to do with it? I assume you are storing this data because
> you want to do something with it, correct? Perhaps this data is used
> to "authorize" a user for a specific task? perhaps they need they need
> to be a member of the "Lender" group before they are allowed to approve
> a loan application? Simple enough we can query if the user is IN
> ('Lender') before allowing them do anything with Loan tables. But lets
> complicate things a bit shall we?

Sure, let's. On the other hand, I shall not commit the imprudence of
debating complex things until the simple ones have not been settled.

> We decide we that simple groups are
> not sufficient we need a bit more granularity so we want to assign
> "rights" such as 'Approve Loan' to a user OR a group and allow groups
> to contain groups to make management of user rights easier.
>
> Now give me a simple query that can be used to determine if the user
> has the "Approve Loan" right granted directly to the user or any groups
> he is a member of and any groups those groups might be a member of...
> also remember that deny overrides any grant at any level).
>

Well, that is rather for the more sophisticated and well read DB guru.
However, technically it's not big deal after all.

It boils down to
        SELECT right FROM TC_GROUPS tc JOIN GROUP_RIGHTS gr ON (tc.in_group_id=
gr.group_id)
         WHERE tc.group_id IN
                (SELECT group_id from users_groups ug WHERE ug.user_id= :user_id))
         AND right='Approve Loan';

Where TC_GROUPS(group_id, in_group_id) is the transitive closure of
group membership, the only relevant problem remaining being the
maintenance of transitive closure. This is left as an exercise for the
reader.
        
But before asking me to post a complete solution, could I see the OO
counterpart ?

> Lets add a bit more type dependant (polymorphic) functionality shall
> we... Our fictitious lender offers many different types of loans, home
> loans have greater risk so not all users that can approve a loan can
> approve all types of loans we have decided to create permissions for
> different types of loans and/or the value of the loan... home loans
> require explicit approval by a user with the "Approve Home Loan" right,
> otherwise "Approve Loan" permission is good enough.
>
>

That is rather trivial.

> While your at it I (roleplaying customer) need an amoritization
> schedule (the loan applicaiton has the principal loan amount, annual
> interest rate, loan duration and payment schedule (monthly, quarterly,
> weekly, etc)). If you can write an sproc or PL/SQL script for me to do
> that would be cool then I don't need to waste our presious network
> resources to transmit the loan objects to the application tier.
>

Surely you don't mean to say that loan rates, amortizations, etc. are
"complex calculations" ?

>
> I think your on to something with this SQL approach, we can give all
> our loan reps a Query Analyzer and if they want to create a loan
> application they can just type exec sp_createLoan('... oh wait customer
> first exec sp_createCustomer('first.. wait I think last name goes
> first... well nevermind guess we to create a gui of some sort
> perhaps... what language did they use to create Query Analyzer (or the
> database engine for that matter)? I guess we need a high level
> language for somethings, ah well.

I guess you might be missing here that PL/SQL is a pretty high level
language, and especially with regards to distinguishing positional
parameters is better designed than java or C# or C++, because in PL_SQL
you can just say
  sp_xxx(
...
        first_name => 'Costin',
        last_name => 'Cozianu',
...
);

whereas in most OO languages you simply cannot name your parameters. I
guess this is an instance of ignorant irony that backfires.

> The amoritization schedule you can
> still do for me right?

Right, but only after I'll have decided you're not an incompetent troll.
Given the quality of portions of your post, I am rather suspicious.

> and you've already got me convinced the db is
> the best place for this user/group thing, the additional complexity
> isn't to much for you is it?
>
>
> And about this thread saftey issue... I guess we don't need to worry to
> much about thread synchronization at the database level but I'm a bit
> concerned about multiple users working with the same data at the same
> time. I was reading something the other day about 'dirty data' and
> 'phantom data' such as when a user inserts a row and then another user
> reads that row and then the user that inserted that row aborts his
> transaction, are you familiar with this? I checked the docs and by
> default SQL does not prevent phantom data! I guess they have provided
> an issolation level called serializable that prevents phantom data but
> the documentation says "use only when necessary." When do you suppose
> this is necessary? I don't want to generate a report and send it off
> for funding that that says we have approved a loan when that loan
> approval hasn't even been commited yet! I'm torn about this
> user/group/right scenario though, do you think we need to be concerned
> about dirty data and phantom reads?
> Suppose we create this gui application and this application allows the
> administrator to change the user right assignments by checking a few
> boxes or something and then save his changes, I guess it probably would
> replace the existing options with the ones he is viewing right? so if
> he perhaps checks the deny on the 'approve home loan' box and another
> administrator was told to grant him permission to 'approve auto loan',
> last person to save wins right and he would still be authorized to
> approve home loans? I guess we could just keep the transaction open
> with serializable issolation the entire time the administrator is
> viewing those options so that the second admin isn't even allowed to
> view the data until the first admin finishes... but if we do that we
> may not be able to approve other users actions either and are entire
> system could come to a halt waiting on this one admin. I was looking
> into some alternatives, what do you think about optimistic concurrency
> control? this doesn't seem to be a 'native' database feature. Its
> rather simplistic in theory though, when you read the data you also
> include a version number and then when you do the update you include
> that same version number if it changed then you know its been modified
> by someone else, so the only lock that we need in this scenerio is
> during the update.

I guess you are more confused about the above scenario.

If an admin takes away one user's right after that user has already
performed an action it was previously authorized for, but no longer
subsequently, then the action stays. The session that needs to be
working under "serializable" is that of the user, not that of the admin.

Just because you've read about "optimistic concurrency" in some
"popularization of science" books (I know how some OO circles felt in
love with the idea) doesn't mean you understand the issues. And given
how you framed the above problem I suspect a reading from a more serious
book (like "Transactional Information Systems")would help you think
about these types of problems in a much sounder way.

> What was it again in high-level languages you found
> difficult (ArrayList.Synchronized method)?
>
>

What was that you found easy ?

Did you find it easy to deadlock, or did you find it easy to arrive at
inconsistent results inspite going through all kinds of locks ?

>
> Anyways, relational storage wont be going away anytime soon perhaps
> never, the decomposition of the data makes it possible to construct
> very effecient queries over large sets of data. Currently searching
> object graphs is more difficult and less efficient then it could be,
> for example searching for all customers located in 'AZ' its not to
> write a foreach loop, however it gets old and its somewhat hard-coded
> making arbitrary queries more difficult to implement efficiently, I
> suspect someday technologies like XQuery might become a native part of
> the language allowing easy creation of arbitrary queries on object
> graphs. Memory managers probably need some work in this area as well,
> ts not very efficient to query object graphs compared to a database,
> primarily because of the way that memory is allocated, objects are
> allocated onto the heap in a somewhat arbitrary location based on how
> much data you want to allocate and what it has available, there is no
> gaurantee that objects of the same type will be located remotely close
> to each other in memory or in memory at all (paged to disk), so a
> statment like foreach customer in getcustomers() { if
> customer.address.state = "AZ" list.add(customer); } could result in
> lots of memory paging. The database manages the memory much
> differently loading records of the same type close together in "pages"
> with fixed record lengths allowing quick navigation from one address
> record to the next address record. I suspect someday heap allocation
> for objects might become more optimized for querying done more like a
> database where objects of the same type will be allocated in the same
> location (inheritance I'm sure complicates this but perhaps some form
> of replication of base class properties as one might denormalize a
> database could work but I'm not an expert on that kinda thing anyway).
> Databases however are learning to work better with (not to eliminate)
> OO apps, in a way that is more object graph friendly, SQL 2005 will
> allow multiple active result sets or xml resultsets provide ways make
> it easier to read an object and its children in one step from multiple
> tables simultaniously rather then needing to read all customer data
> then go back to each customer previously read to add the address
> record. Asynchronous notification makes it possible to be informed
> when things change rather then needing to repeadedly read the database
> to ensure you are most up to date. SQL 2005 has some useful features
> to help bridge the gap between OO and relational, each tool has a
> purpose neither trying to elimiate the other just the gap, the goal is
> rather to get "seamless integration" so they play nice giving the
> developer the most productive tool for the current task without having
> to pick one or the other and/or become a bridge builder.
>
> - Kurt
>

Nice deluge of irrelevant 2c that I cannot make any sense of. I'd rather
recommend to you and all contributors to this thread to stay strictly
technical and within your areas of competence, refraining from
unwarranted speculations.

For example it would have helped if rather than being unsubtly ironic
about stuff you think databases cannot handle, you would have tried a
more prudent tone along the lines "In my experience, I found difficult
addressing this kind of issues". Because it may turn out that irony
backfires and bites you somewhere unpleasant .

And again, before complicating this challenge to no end, I'd rather see
the response to the simple challenge, and it would also help if
proponents of new challenges would have already worked out one solution
using their technology/approach of their choice so that they can post it
and/or estimate the complexity, rather than playing it for a sport.