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 16:37:46 +0100
This is for anyone who might benefit from the knowledge.
From: Jonathan Rochkind <email@hidden>
To: Ricardo Strausz <email@hidden>, email@hidden
Subject: Re: From OpenBase To MSSQLServer2000 (EOModeller)
Date: Wed, 04 Jun 2003 14:36:13 -0500
At 01:35 PM 6/4/2003 -0500, Ricardo Strausz wrote:
What you actually need is something like:
SELECT COUNT(*) FROM TB_JOB_PICTURE WHERE TB_JOB_PICTURE.TB_JOB_ID =
<TB_JOB_ID value for the current TB_JOB row>
I don't recall ever using such a derived attribute, so I'm not sure what
EOF-specific (i.e., non-SQL) syntax might be substituted for the value of
the current row's primary key. Now I wonder whether such a derived
attribute is supported by EOF.
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 not
be acceptable, but it works for me. If it were included as a class
property, it would slow down fetches, as someone else mentions, obviously
becuase every fetch to that class will create SQL that has the db compute
this value for every row. Not included---well, the main reason I do it is
just because it works, and it's simple. And for the uses I need to put it
to, having to do a seperate fetch to get the count works fine.
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.
To set up the seperate fetch for the count, you use a fetch specification
set with rawRowAttributes including just your derived count attribute. You
will get a dictionary (or dictionaries) back as a result which include just
one key/value pair each, with the value being your count. The qualifier
for the fetch can be an ordinary qualifier to select the
TB_JOB_PICTURE-corresponding objects you want. You can follow
relationships, and include non-class-properties (like a pk!) in the
qualifier. Also, keep in mind that you can have a qualifier along the
lines of:
Department d;
EOQualifier qual = EOQualifier.qualifierWithQualifierFormat("department =@
", new NSArray(d));
Assuming you are qualifying Employees who have a to-one relationship
'department' to Department. It just works, the SQL joining on the pk and fk
involved are generated for you. Very handy!
Hope this helps,
--Jonathan
The way I have gone about this is to create a view table in MSSQLServer2000
(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
_________________________________________________________________
Hotmail messages direct to 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.