Re: From OpenBase To MSSQLServer2000 (EOModeller)[SOLUTIONS]
Re: From OpenBase To MSSQLServer2000 (EOModeller)[SOLUTIONS]
- Subject: Re: From OpenBase To MSSQLServer2000 (EOModeller)[SOLUTIONS]
- From: "Jonathan Fleming" <email@hidden>
- Date: Mon, 09 Jun 2003 23:22:25 +0100
From: Jonathan Rochkind <email@hidden>
To: "Jonathan Fleming" <email@hidden>, email@hidden,
email@hidden
CC: email@hidden
Subject: Re: From OpenBase To MSSQLServer2000 (EOModeller)[SOLUTIONS]
Date: Mon, 09 Jun 2003 10:52:28 -0500
At 04:37 PM 6/9/2003 +0100, Jonathan Fleming wrote:
The way I've approached this is to define a derived attribute (on the
TB_JOB_PICTURE) "count(*)" that is _not_ a class property. This means it
won't be automatically included in any of your EO objects at all, but
that you can do a seperate fetch when you need the count. This may or
may
[...]
The only problem I found with this approach (assuming I followed you
instructions correctly) is that you can not get this to list a count in a
repetition for a row's toMany objects. Please correct me if I'm wrong. I
would still like to know of alternative approaches to this problem.
I'm not sure what you are asking. Depending on the situation and you're
requirements, you won't need to define a special derived count(*)
attribute. If you just want the count of a to-many destination, you can
just bind to "object.relationship.count" and get that
This is what I have been avoiding because it seems to cause a performance
drag. As I understand it, to count the to-Many objects of each row the whole
database is fetched to do a count for that row and so on with the other rows
in a WORepitition.
But in cases where you do need a derived count(*) attribute, and there's a
repetition going on, if I understand what you are suggesting properly...
you still could get the count in the repetition.
As above... does this cause a perfomace drag? Or is this acceptable if a
fetch is going on for the first time around anyway?
But with the method I suggest it would probably require a seperate fetch
for each iteration of the repetition. But you can just bind a WOString to a
method which sets up the fetch to perform the count. But maybe I'm not
understanding the situation we're talking about here.
No, I think you are, it's moree likley i've not explained myself correctly,
however, you've got the gist of what I've been trying to do
The way I have gone about this is to create a view table in
MSSQLServer2000
Yes, this seems like a fine idea too. Personally, I prefer to implement all
of my logic in WO code, and avoid special stuff set up on the db.
to be honest I would have prefered this too but I just wasn't having much
luck with do it so that i would have this performance problem. I didn't know
the SQL that the derived attribute would accept and time was pressing on.
Now that I have a fairly good workaround i feel better but would still like
to know if there is a better way to do this within WebObjects
Thanks
Jonathan F :^)
For a variety of reasons, some more rational than others, but none of them
universally applying to everyone, it's really just my personal preference.
A view is probably quite a good way to do this, if you are willing to set
up a view like that. I don't see any problems with what you are outlining
below.
--Jonathan
(I know in the past you have said you use MS-SQL-7.0 but I have never used
it so I don't know the features it in order to say you can take this
approach there too). Anyway, create a view table in MSSQLServer2000 and
set it up with code similar to this:
CREATE VIEW dbo.CountOfMyObjects
AS
SELECT COUNT(*) AS countOfMyObjects,
dbo.MY_TO_MANY_OBJECT.MY_TO_ONE_OBJECT_ID
FROM dbo.MY_TO_ONE_OBJECT INNER JOIN
dbo.MY_TO_MANY_OBJECT ON
dbo.MY_TO_ONE_OBJECT.MY_TO_ONE_OBJECT_ID =
dbo.MY_TO_MANY_OBJECT.MY_TO_ONE_OBJECT_ID
GROUP BY dbo.MY_TO_MANY_OBJECT.MY_TO_ONE_OBJECT_ID
A view is a virtual table whose contents are defined by a query. Like a
real table, a view consists of a set of named columns and rows of data.
However, a view does not exist as a stored set of data values in a
database. The rows and columns of data come from tables referenced in the
query defining the view and are produced dynamically when the view is
referenced. This is much like how the derived column in an EOModel works I
would say since you would use the same sort of code to reference the other
table(s).
A view acts as a filter on the underlying tables referenced in the view.
The query that defines the view can be from one or more tables or from
other views in the current or other databases. Distributed queries can
also be used to define views that use data from multiple heterogeneous
sources. This is useful, for example, if you want to combine similarly
structured data from different servers each of which stores data for a
different region of your organization.
There are no restrictions on querying through views and few restrictions
on modifying data through them. However in our case I have made the one
attribute we are referencing read only as you will learn.
Once you have setup the view table you can then import that into your
model by choosing Model>New Updated Model from the menu bar. You will be
presented with a dialog box where you select the table/s you want, there
you will find your view table/s Eg. MY_DERIVED_COUNT (if you named it
such. It may be appended with dbo. but this can be droped from the name in
the table inspector dialog box with no adverse effects). Next you will be
presented with the stored procedure list (uncheck them if you don't want
them). Click OK and you will now have a new model with your selected
table/s and SP's if you chose any. All you need do now is copy the
required table/s into you working model and set it up as a toOne link on
the table you are trying to obtain the count for. Before you finish make
sure you make the attribute of your derived table read only by using the
advanced inspector after highlighting that attribute, as well as making
the attribute a class property, but do remove it from the locking
mechanism as it is a derived property and therefore not needed as a
qualifier in a snapshot.
There will be no need to make the derived table an EOCustomObject so you
can leave it as an EOGenericRecord, but you will have to update your
to-One tables if they are of an EOCustomObject class, otherwise, that's
it, you're done. Code and link as you would any other object.
Now you have the befefit of getting the count of each row's to_Many
objects without creating an EO -- so having the database trawl through
everything in it just to get a count -- and therefore the count of an
individual row's to-Many objects when needed.
If this approach is fraught with danger, some please tell me fast! But
right now it looks good and tested good. I can't really see that there
would be any major problems with this.
Jonathan F ;^)
As far as I remember, EOF DO support such derived atributes; they have
to be flatened in EOModeler. But as far as I remember they slow down the
fetch.
I suppose you could define a stored procedure instead if you
didn't want to fetch the related objects in order to count them on the
> client.
Aloha,
Art
_________________________________________________________________
On the move? Get Hotmail on your mobile phone http://www.msn.co.uk/msnmobile
_______________________________________________
webobjects-dev mailing list | email@hidden
Help/Unsubscribe/Archives: http://www.lists.apple.com/mailman/listinfo/webobjects-dev
Do not post admin requests to the list. They will be ignored.