Re: Delete a table content / Using Raw SQL with EOF
Re: Delete a table content / Using Raw SQL with EOF
- Subject: Re: Delete a table content / Using Raw SQL with EOF
- From: Chuck Hill <email@hidden>
- Date: Thu, 8 Sep 2005 10:54:49 -0700
On Sep 8, 2005, at 7:51 AM, email@hidden wrote:
Re: caching
WebObjects caches data at multiple levels. There is nothing we or
anybody else could do about this. In the end an EO is just a fancy
copy of a database row. As such it may get out of sync with the
actual data. EOs are constructed from snapshots which are actually
intended as cache layer. EOF has mecanisms for keeping snaphots and
EOs within one EOF stack in sync. One may safely consider both
layers as being one and the same cache. Now, there are methods for
keeping this cache up-to-date. You may want the EOF stack may
update upon notifications from other EOF stacks or even upon JMS
notifications from the database itself. I don't think this approach
to be a good idea at all. It comes at the expense of great coding
effort, heavy CPU use and also has its share of risks.
Often overlooked, there is another level of caching: The displayed
web page is no more than a formated copy of the EO held on the
server side. This will get out of sync with the EO unless you find
a way to constantly push data to the client.
In much the same way the data presented on screen by any other tool
accessing the database is a mere copy of the data and as such will
go out of sync.
The real question is what caching artefacts are tolerable to your
business.
Very true.
For quite some time I have been advocating an idea I call 'trails'.
The idea is to find trailheads in the navigation paths and start
with a fresh editing context from there. That editing context will
be configured to refuse snapshots older than itself. Further down
the trail is never older than the trail itself. Imagine a standard
data browsing application: main menu, search form, result list,
detail/inspect, edit screen. On the main menu you would have no
business data safe login information. The search form would be the
trail head. The search would be executed in a fresh editing
context: The result would show up-to-date date. If you drill down
moving further along the trail you may explore a to-many
relationship. The data in there would most likely be up-to-date. In
no event would it be older than your last passing a trail head.
This would for my uses be adequate/acceptable. An edit screen would
need to be a sort of trail head showing only freshest data,
preferrably NOT automatically updated with outside changes.
Concurrent changes should lead to an optimistic locking exception.
Caveat: been talking about this for years without actually
implementing it. I have passed the past 2+ years within the number
crunching bowels of batch applications. Only now I start working
again at the UI level. Implementing a data freshness strategy based
on these ideas is on my todo list for the next months.
I've done this and it works well and exactly as you have described.
Re: RAW SQL
Short: don't do it.
EOF does a lot for you. You need to be aware of this when
evaluating the benefits of bypassing it. These services include
database abstraction, object-relational mapping, caching, object
uniquing, VALIDATION, deletion strategies (cascade, ...) ...
Validation is a good point and one that I should have mentioned. IF
you are going to update the DB behind EOF's back then you need to
take whatever steps are needed to ensure that it is proper to add/
edit/remove the data. You are on your own here.
There are situations where you need little of this. Some of it
might actually be unwanted. Deleting large amounts of database rows
may be such a situation. Why convert rows one by one into EOs only
to delete them one by one when they can be deleted all at once with
no overhead? Things to consider are: data consistency, deletion
strategies, ... . Caching issues are secondary. As Chuck said, you
would run into the just the same if another EOF application had
deleted the rows. At any rate you will need a good data freshness
strategy and handle errors where it fails.
That said, I would NOT go for RAW SQL. I'd rather have my database
administrator write a stored procedure and call upon that. This way
I have no vendor specific SQL in my Java code. I have no reference
to table names and the like in my Java code. All is in the database
and is versionned with the database creation scripts.
OK, I will take back the RAW SQL part. I should have said SQL
lightly coddled in EOF. :-) I'd certainly not advocate pasting
actual SQL into my Java code! We have EOF after all.
com.webobjects.eoaccess.EOAdaptorChannel and
com.webobjects.eoaccess.EOSQLExpression will generate most of it for
you based on the model and qualifiers. I've only once run into a
situation where I needed to partially hard code SQL and even then I
think we just did not dig deep enough into the JDBCPlugin. And I
don't have to dirty my design with stored procedures. :-)
Chuck
webobjects-dev-bounces+pierre.bernard=email@hidden wrote
on 09/07/2005 07:22:09 PM:
> This has been a fun discussion. Let me see if I can clarify some
parts.
>
> EOF caches data from the database. There, I said it. It does not
> really matter how or why or when it gets refreshed. The data is
> cached and that opens the door to problems in most situations.
It is
> a fact of life, you just have to deal with it.
>
> There are two situations in which you can ignore this. It can be
> ignored if the following are all always true:
> 1. You are only deploying a single instance,
> 2. You are using only one EOF stack
> 3. No other process, including you at the SQL window / command line
> is writing to the database.
>
> In other words, almost never. Deploying only a single instance is
> rarely enough to ensure availability and I would not want to
write an
> application which assumed this was the only way that deployment
would
> ever be,
>
> The other situation in which this can be ignored is if the problems
> happen so infrequently that your users are willing to live with it.
> If the rest of the system works fine for them, they may be quite
> willing to get an error message and log out and in (or however it
> gets fixed) once a day/week/month. Faced with the prospect of an
> invoice, many problems seem smaller than they did
originally. :-) I
> suspect that this is what is done in the majority of WO applications
> out there.
>
> If neither of those two situations describes your situation then you
> need to handle the cache in EOF being out of synch with the
> database. The cache refreshing code you will see in books and on-
> line and the change notification framework can _reduce_ how often
the
> problems happen. But neither will _eliminate_ them. Your code
needs
> to be prepared to handle optimistic locking failures, referential
> integrity failures, and missing object failures where ever they can
> happen (this depends entirely on your application).
>
> Now, back to the question of using raw SQL against the database. If
> you are in the first situation above, you need to avoid doing
this as
> it will cause problems that won't happen otherwise. Using raw SQL
> will have the same effect as running as second instance - the EOF
> cache will not match the database. If you are in the second
> situation, it might not matter. Your users already get errors, the
> worst that using raw SQL will do is to make the errors more
> frequent. Eventually, the invoice to fix this won't look so large
> after all.
>
> If you are in the last situation, go ahead and use raw SQL. Why
> not? Doing it in EOF will be OK for one instance, but all the other
> instances can't see any difference between the database changes that
> result from EOF operations or raw SQL. You are already handling the
> cache inconsistencies. There is nothing to fear. Using raw SQL
> might give your code a bit more exercise, but it can also give you a
> performance boost in bulk operations.
>
>
> Chuck
>
>
>
>
> On Sep 7, 2005, at 1:26 AM, Fabrice Pipart wrote:
>
> > In short, do you just tell me I should ALWAYS use the EOF instead
> > of raw sql to avoid having problems when I will have multiple
> > instances?
> > In fact after the discussion with Ken Anderson, I only kept raw
sql
> > for deleting all the rows of a table (this should not be a problem
> > I think...). My memory problem has been solved by removing the
> > invoice-invoiceitem relationship (wow that saves a lot of
memory!).
> > And finally you're right, I make such mistakes just because I
don't
> > really understand the caching behind EOs :-(
> > I will give a look at "freshness explorer", I 'm sure it can
help me!
> >
> > Thanks you!
> >
> > Fabrice
> >
> >
> > On Sep 6, 2005, at 1:38 PM, David Teran wrote:
> >
> >>
> >> Am 05.09.2005 um 17:48 schrieb Fabrice Pipart:
> >>
> >>
> >>> I think you are absolutely right.
> >>> I gave up the idea to change the rows with raw SQL. Loosing the
> >>> sync was too bad for the rest of the application and it did not
> >>> make sense to have to refetch all the objects !
> >>>
> >>>
> >>
> >> Sorry, but this is just a short term solution. If you want to do
> >> it right, you must -always- keep in mind that the database is out
> >> of sync. For example because you use multiple instances, the
> >> database is used by other systems and such cases. IMHO its
just to
> >> naiv thinking that there is only one WO instance and only one
> >> system accessing the database so best is: always keep in mind
that
> >> EOF caches Database information and always check if you need
> >> 'fresh' data for a certain action. I think the biggest problem
for
> >> users is the fact that EOF caches and that they have
absolutely no
> >> idea how to deal with that fact. Ignoring it just because one
uses
> >> only a single instance means a lot of refactoring when you
need to
> >> add just one additional instance or even just one additional EOF
> >> stack in the same instance. So its important to know the basics
> >> about data freshness and such things.
> >>
> >> I know others have different opinion about that but all i can say
> >> to them is ' go back in sandbox and pray you never need multiple
> >> EOF stacks or multiple instances or multiple applications
using on
> >> single database' because the reality is they will need the one or
> >> the other sooner or later ;-)
> >>
> >> There is a neat demo app called 'freshness explorer' or something
> >> like this on connect.apple.com for download. It was published
> >> after WWDC 2004 and is a good starter to understand the caching
> >> stuff from EOF.
> >>
> >>
> >> cheers, david
> >>
> >
> > www.icconsulting.mc
> >
> > International Corporate Consulting
> > 25, Boulevard de Belgique
> > MC - 98000 Monaco
> >
> > T. +377 97982104 (direct)
> > F. +377 97708807
> >
> > _______________________________________________
> > Do not post admin requests to the list. They will be ignored.
> > Webobjects-dev mailing list (email@hidden)
> > Help/Unsubscribe/Update your Subscription:
> > 40global-village.net
> >
> > This email sent to email@hidden
>
> --
> Practical WebObjects - a book for intermediate WebObjects developers
> who want to increase their overall knowledge of WebObjects, or those
> who are trying to solve specific application development problems.
> http://www.global-village.net/products/practical_webobjects
>
>
>
>
> _______________________________________________
> Do not post admin requests to the list. They will be ignored.
> Webobjects-dev mailing list (email@hidden)
> Help/Unsubscribe/Update your Subscription:
email@hidden
>
> This email sent to email@hidden
>
**********************************************************************
This email and any files transmitted with it are intended solely for
the use of the individual or entity to whom they are addressed.
If you have received this email in error please notify the sender
of this message. (email@hidden)
This email message has been checked for the presence of computer
viruses; however this protection does not ensure this message is
virus free.
Banque centrale du Luxembourg; Tel ++352-4774-1; http://www.bcl.lu
**********************************************************************
--
Practical WebObjects - a book for intermediate WebObjects developers
who want to increase their overall knowledge of WebObjects, or those
who are trying to solve specific application development problems.
http://www.global-village.net/products/practical_webobjects
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden