Hi Ricardo,
You need to create a branch (push from your local) in your GitHub fork of
Wonder for each set of changes in GitHub (aka a Feature Branch), then make a
pull request from that branch to Wonder. Otherwise all of your changes get
inter-mixed.
Chuck
From: Ricardo Parada <rpar...@mac.com>
Date: Saturday, March 18, 2017 at 11:22 PM
To: "webobjects-dev@lists.apple.com" <webobjects-dev@lists.apple.com>
Cc: Markus Ruggiero <mailingli...@kataputt.com>, Paul Hoadley
<pa...@logicsquad.net>, "koa...@icloud.com" <koa...@icloud.com>,
"g...@knuckleheads.net" <g...@knuckleheads.net>, Chuck Hill
<ch...@gevityinc.com>, "aa...@chatnbike.com" <aa...@chatnbike.com>
Subject: Re: ERXQuery Preview
Hi all,
As promised, I have committed ERXQuery to my Wonder clone on GitHub, i.e.
rparada/wonder. I just need help creating the pull request. :-)
It turns out that I created first a pull request for ERXExtensions.java
unrelated to ERXQuery. Now when I tried to create the pull request for
ERXQuery it just seems to show the previous pull request. So I’m not sure what
is going on. I don’t have much experience with GitHub.
Anyways, the ERXQuery is an additive change to Wonder and consists of only
three files: ERXQuery.java, ERXQueryAttributes and ERXQueryEOAttribute. It’s
the last two commits in rparada/wonder.
I took the time to polish the API a bit more. I think the API came out nicer
than what I had the first time around. Specially the single variadic select()
method that allows you to specify EOAttributes, Strings, ERXKeys, or any
Iterable containing these. Same for groupBy(). And the a similar technique
with the orderBy() which can take EOSortOrderings or any Iterable
(ERXSortOrderings, NSArray, List, Collection).
The documentation is more extensive now. I have included here a copy of the
ERXQuery class from the javadoc tab in my Eclipse.
I have tested with ORACLE which is the easiest for me to test. It would be
great to hear others using this with other databases (h2, derby, mssql, mysql,
frontbase, etc.)
ERXQuery.java
Overview
This class has a fluent API that mimics a select statement:
NSArray<NSDictionary<String,Object>> records =
ERXQuery.create()
.select (keys)
.from (entity)
.where (qualifier)
.groupBy (groupings)
.having (havingQualifier)
.orderBy (sortings)
.fetch();
It allows you to use EOF/Wonder higher-level constructs (qualifiers,
attributes, orderings, key paths, ERXKeys, etc.) to create a query that looks
like this:
SELECT ...
FROM ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...
Specifying the Attributes to Fetch
The select() method is very flexible and powerful. It accepts a variable number
of objects of different types that specify the attributes to fetch. These
objects can be EOAttributes, ERXKeys, Strings. You may also specify NSArray or
List objects containing any combination of these (EOAttributes, ERXKeys,
Strings).
The ERXKeys and String objects correspond to keys and key paths to the
attributes to fetch, i.e. "customer.name". The keys and key paths can also be
relationships to objects, i.e. "customer" which translate into a fetch of
foreign keys used to build object faults and return them in the results.
You may call the select() method multiple times to keep adding to the list of
attributes to fetch.
Using Ad Hoc Attributes
It is very common to aggregate attributes in these queries. For this purpose,
you may want to create what ERXQuery refers to as ad hoc attributes. These
attributes have a definition but are not physically attached to the entity. You
can use the ERXQueryAttributes class to easily create multiple ad hoc
attributes. The definition of the attribute can reference relationships and
attributes as shown below. If you just want to create a single ad hoc attribute
you may use the ERXQueryEOAttribute class.
// Using a single query against the order entity to count the number of
// orders and line items that match an order qualifier.
ERXQueryAttributes attributes = ERXQueryAttributes.create(orderEntity)
.add("itemCount", "COUNT(DISTINCT lineItems.lineItemID)", "intNumber")
.add("orderCount", "COUNT(DISTINCT orderID)", "intNumber");
ERXQuery query =
ERXQuery.create()
.select (attributes)
.from (orderEntity)
.where (qualifier);
// Fetch into a dictionary
NSDictionary<String,Object> row = query.fetch().lastObject();
int orderCount = ((Number) row.objectForKey("orderCount")).intValue();
int itemCount = ((Number) row.objectForKey("itemCount")).intValue();
Fetching Results into a Custom Class
It is useful to fetch results into objects of a custom class. This allows you
to have type checking on the getter methods and add methods for computed values
on the data fetched. For the example above you could have fetched the results
into a custom class as follows:
// Fetch into object instances of the a custom Result class
Result result = query.fetch(editingContext, Result.class).lastObject();
int orderCount = result.orderCount();
int itemCount = result.itemCount();
The Result custom class would have to be defined as shown below. The
constructor may keep the mutable dictionary passed in to the constructor or
make an immutable copy from it as shown below.
public static class Result {
NSDictionary<String,Object> data;
public Result(EOEditingContext ec, NSMutableDictionary<String,Object>
row) {
data = row.immutableClone();
}
public int itemCount() {
return ((Number) data.objectForKey("itemCount")).intValue();
}
public int orderCount() {
return ((Number) data.objectForKey("orderCount")).intValue();
}
}
}
In general, fetching into a custom class can be done in several ways:
// If your custom class has a constructor that takes an editing context and
// a mutable dictionary then it is very simple:
NSArray<Foo> objs = query.fetch(editingContext, Foo.class);
// Using java 8 or later you may use a lambda expression:
NSArray<Foo> objs = query.fetch(editingContext, (ec, row) -> new Foo(ec,
row));
// You may also create an implementation of the RecordConstructor
// functional interface and pass it into the fetch method:
ERXQuery.RecordConstructor<Foo> recordConstructor =
new ERXQuery.RecordConstructor<Foo> {
@Override
public Foo constructRecord(EOEditingContext ec, NSMutableDictionary
row) {
return new Foo(ec, row);
}
};
NSArray objs = query.fetch(editingContext, recordConstructor)
}
Augmenting Row Values
You can have entries from a dictionary added in to the rows fetched from the
database. The mutable dictionary passed in to the record constructor will
contain the data fetched along with the keys/values from this
recordInitializationValues dictionary.
NSDictionary<String,Object> recordInitializationValues = new
NSDictionary<>((Object)2017, "preferredYear");
NSArray<Foo> objs = query.fetch(editingContext, recordInitializationValues,
Foo.class);
Foo aFoo = objs.lastObject();
int preferredYear = aFoo.preferredYear(); // i.e. 2017
Defining Ad Hoc Attributes in the Entity
An alternate way to define your ad hoc attributes is to define them in your
entity and flagging them as non-class properties. Unlike ERXQueryEOAttribute
objects, these attributes will be instances of EOAttribute and reside in your
entity. They may be a bit distracting when looking at the entity if you have a
lot but this method allows you to reuse all the existing attributes and
relationships already defined in the entity and does not require code for
creating the attributes.
One incovenience is that eogeneration templates do not generate ERXKeys for
non-class properties. However, this problem could be overcome by enhancing the
eogeneration templates to generate ERXKeys for derived non-class property
attributes.
// Fetch last year's customer order totals exceeding $1000 in descending order
NSArray<OrderSummary> lastYearTopSales =
ERXQuery.create()
.select (Order.CUSTOMER) // customer to-one
.select (Order.SUM_TOTAL_AMOUNT) // non-class property defined as
SUM(totalAmount)
.from (Order.ENTITY_NAME)
.where (lastYearQualifier)
.groupBy (Order.CUSTOMER)
.having (Order.SUM_TOTAL_AMOUNT.greaterThan(1000.00))
.orderBy (Order.SUM_TOTAL_AMOUNT.desc())
.fetch(editingContext, OrderSummary.class);
// Peek at top sale record
OrderSummary topSale = ERXArrayUtilities.firstObject(lastYearTopSales);
if (topSale != null) {
System.out.println("Customer " + topSale.customer().fullName()
+ " ordered " + moneyFormatter.format(topSale.sumTotalAmount()));
}
}
It would be nice to enhance the eogeneration templates to also create a custom
class for fetching the results, i.e. WonderEntitySummary.java and
_WonderEntitySummary.java with the getters for attributes/relationships in the
entity including derived non-class properties. These templates would be used
when the entity has a user info key with ERXQuery.enabled=yes.
Limitations
Ad hoc attributes created with ERXQueryAttributes or ERXQueryEOAttribute are
not physically attached to an entity. When EOF generates SQL for a qualifier it
calls sqlStringForSQLExpression(q,e) where q is an EOQualifier and e is an
EOSQLExpression. Qualifiers then try to reach the attribute by following the
qualifier's referenced keys starting with the entity of the EOSQLExpression,
i.e. e.entity().
The current workaround used by ERXQuery is to temporarily add to the entity any
ad hoc attributes referenced by the qualifiers. This typically happens with the
havingQualifier which normally references the ad hoc attributes corresponding
to aggregated attributes. For example, "sumTotalAmount" defined as
"SUM(totalAmount)" could be used in a having qualifier:
// When grouping orders by customer and fetching sumTotalAmount we may want
to have
// this having qualifier so that we only fetch the groups totaling more than
1000.
EOQualifier havingQualifier = ERXQ.greaterThan("sumTotalAmount", new
BigDecimal(1000.0));
However, if you were to define your "sumTotalAmount" attribute in your entity
as a derived non-class property with definition "SUM(totalAmount)" then
ERXQuery doesn't have to add the attribute to the entity.
Defaults for Behavior Properties
1. er.extensions.eof.ERXQuery.useBindVariables=false
2. er.extensions.eof.ERXQuery.useEntityRestrictingQualifiers=true
3. er.extensions.eof.ERXQuery.removesForeignKeysFromRowValues=true
Author:
Ricardo J. Parada
On Mar 14, 2017, at 8:31 AM, Ricardo Parada
<rpar...@mac.com<mailto:rpar...@mac.com>> wrote:
Thanks all for the feedback. I'll be adding it by the weekend I hope.
On Mar 14, 2017, at 4:29 AM, Markus Ruggiero
<mailingli...@kataputt.com<mailto:mailingli...@kataputt.com>> wrote:
Great thing, looks very useful.
On 11.03.2017, at 23:41, Ricardo Parada
<rpar...@mac.com<mailto:rpar...@mac.com>> wrote:
Hi all,
Over the years, I’ve worked on an class for doing ad hoc queries. This class
has gotten better and better. At first, the API of this class and some
implementation details were not ready for public consumption in my opinion.
However, recently I took the time to polish the code as much as possible and if
I can get my employer's approval I would like to contribute this code to
project Wonder which has given us so much in return during the years.
Here is a preview of its functionality. Please let me know if you guys think
this would be a useful contribution.
ERXQuery.java
This class has a fluent API that mimics a select statement:
NSArray<NSDictionary<String,Object>> records =
ERXQuery.create()
.select (keys)
.from (entity)
.where (qualifier)
.groupBy (groupings)
.having (havingQualifier)
.orderBy (sortings)
.fetch();
Overview
ERXQuery allows you to use EOF constructs (EOQualifiers, EOAttributes,
EOSortOrdering, EOAttribute names, relationships, etc.) to create queries of
the form:
SELECT ...
FROM ...
WHERE ...
GROUP BY ...
HAVING ...
ORDER BY ...
Using Ad Hoc Attributes
You may use the ERXAdHocAttributes class to easily create ad hoc attributes to
use with your query. For example, the code below creates two ad hoc
EOAttributes. An ad hoc attribute is an EOAttribute that is not physically
attached to an entity and that has a definition. They are useful for
aggregating other attributes. The definition of the attribute can reference
relationships and attributes as shown below.
// Using a single query against the order entity, count the number of
// orders and line items that match the qualifier.
ERXAdHocAttributes attributes = ERXAdHocAttributes.create(orderEntity)
.add("itemCount", "COUNT(DISTINCT lineItems.lineItemID)", "intNumber")
.add("orderCount", "COUNT(DISTINCT orderID)", "intNumber");
NSDictionary<String,Object> row =
ERXQuery.create()
.select (attributes)
.from (orderEntity)
.where (qualifier)
.fetch()
.lastObject();
int orderCount = ((Number) row.objectForKey("orderCount")).intValue();
int itemCount = ((Number) row.objectForKey("itemCount")).intValue();
Fetching into a Custom Record Class
You can also fetch using a custom class of your choice, i.e. Foo, as shown
below:
// Using java >= 8 is easy with a lambda expression
NSArray<Foo> objs = query.fetch(editingContext, (ec, row) -> new Foo(ec, row));
// Using java < 8 you must provide a RecordConstructor
ERXQuery.RecordConstructor<Foo> recordConstructor =
new ERXQuery.RecordConstructor<Foo> {
@Override
public Foo constructRecord(EOEditingContext ec, NSMutableDictionary row)
{
return new Foo(ec, row);
}
};
NSArray objs = query.fetch(editingContext, recordConstructor);
Author: Ricardo J. Parada
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list
(Webobjects-dev@lists.apple.com<mailto:Webobjects-dev@lists.apple.com>)
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/mailinglists%40kataputt.com
This email sent to mailingli...@kataputt.com<mailto:mailingli...@kataputt.com>
Markus Ruggiero
mailingli...@kataputt.com<mailto:mailingli...@kataputt.com>
Check out the new book about Project Wonder and WebObjects on
http://learningthewonders.com/
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (Webobjects-dev@lists.apple.com)
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to arch...@mail-archive.com
