Re: Fetching record counts.
Re: Fetching record counts.
- Subject: Re: Fetching record counts.
- From: Chuck Hill <email@hidden>
- Date: Tue, 1 Feb 2005 11:10:44 -0800
Here are some other counting implementation that I have gleaned from
the list. Perhaps they are of interest?
--------------------
If this needs to be modelled (as opposed to firing the array fault):
Q. Is there a way to set up a derived column that would contain a count
of to-many relationship objects in an entity? e.g. Entity Foo has a
to-many relationship with entity Bar I'd like to set up a derived
column in Foo that would return to me the count(*) of bars.
A. What you would want to do is create an attribute in your Foo entity
called barCount. Unlock it, and make sure it allows null value. Open
the Attribute Inspector, and set the internal data type to Integer, and
then the
external to something appropriate to your database. Change the Column
pop up to derived, and then enter something like: (SELECT COUNT(*) FROM
BAR WHERE BAR.FOO_ID = FOO.FOO_ID)
in the Custom Formatting Read field. In the advanced entity inspector,
make sure you select the Read Only option
William Hatch
-----------------------------
Add an attribute called "derivedCount" to the model for the entity
who's table you're going to be querrying against. This attribute
should be a derived/defined attribute (rather than a column attribute),
who's definition is "count(*)". It's important that this attribute is
NOT a class property!
Now, after you've built your qualify, and built your fetch
specification, just call:
fetchSpecification.setRawRowKeyPaths( new NSArray("derivedCount"));
When you execute the fetch spec, you'll get back an array with a single
element. That element will be an NSDictionary (raw row) with a single
key/value. That value will be your count.
In general, if you want unusual (for EOF) SQL in the "select" clause,
adding defined/dervied non-class-property EOAttributes, with the form
you want, is often useful. Of course, you can only get raw rows with
these non-class-property attributes.
Theoretically, instead of having to define this attribute in your
static model, you could dynamically add it to any arbitrary entity at
run-time, when needed. But I've been reluctant to do this, since
nowhere is it documented what lock()s you should take out before
modifying an EOModel that might be in use. Should be fairly safe
anyway, since you're just adding a non-class property, but I just add
the derived attribute to my actual static model for entities where it's
needed, works fine for me.
--Jonathan Rochkind
-----------------------------
On Feb 1, 2005, at 9:00 AM, Chuck Hill wrote:
I don't see any locking in there. Unless you know that this is being
called from code that has locked the EC, you should add that. You can
probably avoid the Concurrency issue by wrapping these methods in a
try...finally block and locking the EOObjectStoreCoordinator:
objectStoreCoordinator.lock();
try {
// method body here
}
finally {
objectStoreCoordinator.unlock();
}
Chuck
At 03:34 PM 01/02/2005 +1100, Marek Wawrzyczny wrote:
Thanks Arturo,
To be absolutely honest I have never used synchronized keyword in this
way before.
I will still create the "count" attribute dynamically, otherwise I
suspect there might be a risk of a deadlock if the attribute was to be
added to two different entities simultanously. So, the given entity
would be locked for the duration of addition/fetching count/removal of
the "count" attribute.
import com.webobjects.foundation.*;
import com.webobjects.eocontrol.*;
import com.webobjects.eoaccess.*;
/**
* This class impements methods that allow us to count the number of
* rows for a user defined fetch specification.
*
* <p>The count is performed directly on the database source without
having to load
* them into memory.</p>
*
* <p>Original code by Eric Noyau. This code is based on Eric Noyau's
Bugtracker application.</p>
*
* @author Eric Noyau
* @author Marek Wawrzyczny
* @version 1.1
*/
public class EOFetchCounting extends Object {
/**
* Returns the number of rows metching the fetch specifivation.
* @param editingContext - editing context to perform the fetch in
* @param fetchSpecification - the fetch specification
*/
public static Number objectCountWithFetchSpecification(
EOEditingContext editingContext,
EOFetchSpecification fetchSpecification) {
boolean hasCountAttribute = false;
EOFetchSpecification rawFetchspecification;
EOEntity entity;
EOQualifier schemaBasedQualifier;
EOAttribute attribute;
NSArray results = null;
if (fetchSpecification != null) {
try {
rawFetchspecification =
(EOFetchSpecification)fetchSpecification.clone();
} catch (Exception e) {
System.out.println("\nException in
objectCountWithFetchSpecification :\n"+e);
return null;
}
entity =
EOModelGroup.defaultGroup().entityNamed(rawFetchspecification.entityNa
me
());
// Synchronize access to the entity object, the "count" attribute
should 'live' only for the
// duration of the fetch.
synchronized (entity) {
schemaBasedQualifier =
entity.schemaBasedQualifier(rawFetchspecification.qualifier());
attribute = objectCountAttribute();
try {
entity.addAttribute(attribute);
} catch (Exception e) {
NSLog.out.appendln("FetchCounting: Exception while adding attrib
"
+ e);
}
try {
rawFetchspecification.setQualifier(schemaBasedQualifier);
rawFetchspecification.setRawRowKeyPaths(new
NSArray(attribute.name()));
results =
editingContext.objectsWithFetchSpecification(rawFetchspecification);
} catch (Exception e) {
NSLog.out.appendln("FetchCounting: Exception while performing
fetch " + e);
} finally {
entity.removeAttribute(attribute);
}
}
if ((results != null) && (results.count() == 1)) {
NSDictionary row = (NSDictionary)
results.lastObject();
return (Number)row.objectForKey(attribute.name());
}
}
return null;
}
/**
* Returns the number of rows metching the fetch specifivation.
* @param editingContext - editing context to perform the fetch in
* @param entityName - the entity to perform the count on
* @param fetchSpecification - the fetch specification
* @param bindings - the fetch bindings
*/
public static Number objectCountWithFetchSpecificationAndBindings(
EOEditingContext editingContext,
String entityName,
String fetchSpecName,
NSDictionary bindings) {
EOModelGroup modelGroup = EOModelGroup.defaultGroup();
EOFetchSpecification unboundFetchSpec;
EOFetchSpecification boundFetchSpec;
unboundFetchSpec =
modelGroup.fetchSpecificationNamed(fetchSpecName, entityName);
if (unboundFetchSpec == null) {
return null;
}
boundFetchSpec =
unboundFetchSpec.fetchSpecificationWithQualifierBindings(bindings);
return objectCountWithFetchSpecification(editingContext,
boundFetchSpec);
}
/**
* Returns the "count" entity attribute to perform the fetch on.
* @return "count" entity attribute
*/
private static EOAttribute objectCountAttribute() {
EOAttribute objectCountAttribute = new EOAttribute();
objectCountAttribute.setName("p_objectCountAttribute");
objectCountAttribute.setColumnName("p_objectCountAttribute");
objectCountAttribute.setClassName("Number");
objectCountAttribute.setValueType("i");
objectCountAttribute.setReadFormat("count(*)");
return objectCountAttribute;
}
}
On 01/02/2005, at 14:57, Arturo Pérez wrote:
On Jan 31, 2005, at 9:33 PM, Marek Wawrzyczny wrote:
This is actually where I am stuck. I don't understand the EOModel
and
EOAttribute mechanism very well. One solution I tried was to add the
count attribute permanently, that crashed the application. So I
tried
creating the attributes on the fly and removing them once finished
this worked. However, it doesn't appear I can make this thread-safe
since there is no locking mechanism on the EOModel (I think this is
why Eric's solution used a static var) and I cannot easily test this
concept under duress (when the exceptions actually start occurring).
Can anyone point me in the right direction on this?
EOF may not provide a locking mechanism but perhaps the Java one will
suffice? Have you tried synchronizing on the entity, something like:
public static Number objectCountWithFetchSpecification(
EOEditingContext editingContext,
EOFetchSpecification fetchSpecification) {
if (fetchSpecification != null) {
EOFetchSpecification rawFetchspecification;
try {
rawFetchspecification =
(EOFetchSpecification)fetchSpecification.clone();
} catch (Exception e) {
System.out.println("\nException in
objectCountWithFetchSpecification :\n"+e);
return null;
}
EOEntity entity =
EOModelGroup.defaultGroup().entityNamed(rawFetchspecification.entityN
am
e());
synchronized (entity) { // ADDED SYNCHRONIZED
EOQualifier schemaBasedQualifier =
entity.schemaBasedQualifier(rawFetchspecification.qualifier());
EOAttribute attribute =
EOFetchCounting.objectCountAttribute();
NSArray results = null;
entity.addAttribute(attribute);
// Fix to ensure that fetch counting does not trip the
site when
// the fetch fails for some reason
try {
rawFetchspecification.setQualifier(schemaBasedQualifier);
rawFetchspecification.setRawRowKeyPaths(new
NSArray(attribute.name()));
results =
editingContext.objectsWithFetchSpecification(rawFetchspecification);
} finally {
// IMPORTANT!!!
// This needs to run every time, exception or not
entity.removeAttribute(attribute);
}
} // CLOSE SYNCHRONIZED
if ((results != null) && (results.count() == 1)) {
NSDictionary row = (NSDictionary)
results.lastObject();
return (Number)row.objectForKey(attribute.name());
}
}
return null;
}
Marek Wawrzyczny
software engineer
-------------------------->
ish group pty ltd
http://www.ish.com.au
7 Darghan St Glebe 2037 Australia
phone +61 2 9660 1400 fax +61 2 9660 7400
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
villag
e.net
This email sent to email@hidden
--
Chuck Hill email@hidden
Global Village Consulting Inc.
http://www.global-village.net
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
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:
This email sent to email@hidden