Forums

by cdshearer >> Mon, 16 Dec 2002 22:42:43 GMT

Hi All

Here's a little method I came up with that I thought I'd share.

I've always been frustrated by wanting to do a quick query on all instances of a class, and having to create a workspace method such as:

vars
oa : ObjectArray;
o : Object;
count : Integer;begin

create oa transient;
Customer.allInstances(oa, 0, true);
foreach o in oa where o.Customer.name = "" do
count := count + 1;
endforeach;

write count;
epilog
delete oa;
end;


and then select it and execute it.

Just think how much easier this would be in SQL:

SELECT COUNT(*) FROM Customer WHERE Name=""



So, I realised that I could add a method to the Class class. Here's the method:

countAllInstances(pWhereClause: String): Integer;
vars
whereClause,
notStr,
source : String;
errorCode,
errorPos,
errorLength : Integer;
meth : JadeMethod;begin

source := "
vars
oa : ObjectArray;
o : Object;
c : Integer;begin

create oa transient;
" & name & ".allInstances(oa, 0, true);

foreach o in oa ";

if pWhereClause <> null then
whereClause := pWhereClause.trimBlanks();
if whereClause[1:4].toUpper() = "NOT " then
whereClause := whereClause[4:end].trimBlanks();
notStr := "not ";
endif;
source := source & " where " & notStr & "o." & name & "." & whereClause; endif;

source := source &
" do
c := c + 1;
endforeach;

return c;
epilog
delete oa;
end;
";

meth := process.createTransientMethod("count", Class, currentSchema,
source, true, Integer,
errorCode, errorPos, errorLength);
if errorCode = 0 then
return process.executeTransientMethod(meth, self).Integer;
else
app.msgBox("Your where clause contains a syntax error!",
null, MsgBox_Stop_Icon);
return null;
endif;
epilog
if meth <> null then
process.deleteTransientMethod(meth);
endif;
end;


Now, to use this, just bring up a Workspace window, and type:

write Customer.countAllInstances('name = ""');

The method only handles simple expressions, and doesn't handle AND, OR, etc. It does detect a NOT in the expression and adjusts the code to handle this (so you can write 'not name=""'). However, if you were willing to write an interpreter, this could be expanded.

Easy huh? Obviously you wouldn't use this in production, but it certainly makes development situations convenient.

Enjoy!

Craig

by cdshearer >> Tue, 17 Dec 2002 3:46:10 GMT

Don't you just hate it when somebody comes along and radically improves your code...

Here's a better version of the method. We can compile a method on the actual instance class, and that way we can get access to protected properties, and also we can have complex expressions. Here's my new method:

countAllInstances(pWhereClause: String): Integer;
vars
o : Object;
oa : ObjectArray;
whereClause,
source : String;
count,
errorCode,
errorPos,
errorLength : Integer;
meth : JadeMethod;begin

if pWhereClause <> null then
source := "begin return " & pWhereClause & "; end;";
meth := process.createTransientMethod("filter", self, currentSchema,
source, true, Boolean,
errorCode, errorPos, errorLength);

if errorCode <> 0 then
app.msgBox("Your where clause contains a syntax error!",
null, MsgBox_Stop_Icon);
return null;
endif;
endif;

create oa transient;
allInstances(oa, 0, true);

if pWhereClause <> null then
foreach o in oa where process.executeTransientMethod(meth, o).Boolean do count := count + 1;
endforeach;

return count;
else
return oa.size;
endif;

epilog
if meth <> null then
process.deleteTransientMethod(meth);
endif;
delete oa;
end;



So, it's now possible to write:

write Customer.countAllInstances("name=null and signUpDate<app.actualTime.date - 1");

by johnmunro >> Tue, 17 Dec 2002 11:41:51 GMT

Don't you just hate it when somebody comes along and radically improves your code...

Well, to annoy you further, here is a slightly modified version of your method (great idea by the way):

countAllInstances(pWhereClause: String; includeSubclasses : Boolean): Integer;

vars
o : Object;
oa : ObjectArray;
whereClause, source : String;
count, errorCode, errorPos, errorLength : Integer;
meth : JadeMethod;
ex : SystemException;
begin

if pWhereClause <> null then
source := "begin return " & pWhereClause & "; end;";
meth := process.createTransientMethod("filter", self, currentSchema, source, true, Boolean, errorCode, errorPos, errorLength);

if errorCode <> 0 then
create ex;

ex.errorCode := errorCode;
ex.errorItem := source[errorPos:errorLength+1];

if ex.text.pos("Undefined message number", 1) > 0 then
ex.extendedErrorText := process.getErrorText(errorCode);
endif;

raise ex;

return null;
endif;
endif;

create oa transient;
allInstances(oa, 0, includeSubclasses);

if pWhereClause <> null then
foreach o in oa where process.executeTransientMethod(meth, o).Boolean do count := count + 1;
endforeach;

return count;
else
return oa.size;
endif;

epilog
if meth <> null then
process.deleteTransientMethod(meth);
endif;

delete oa;

end;

As you can see, I've added an 'includeSubclasses' parameter, in case you don't want subclasses included in the query.

The other change is that it now raises an exception which displays the syntax error. The reason for the messing about with extendedErrorText is that for some of the error numbers createTransientMethod returns, the exception displays "Undefined message number" rather than the correct error text. The part of the WHERE clause that caused the error is shown in the errorItem using the handy errorPos and errorLength filled by createTransientMethod.

I have also created a getAllInstances version of this method which returns any matching object instaces in an object array (like a SELECT * rather than a SELECT COUNT), but that method is almost identical to this one and I didn't think it was worth posting.


John Munro

---
Synergist Limited - Home of FileVision
The Bioscience Innovation Centre
Cowley Road, Cambridge, UK
CB4 0DS

Telephone: +44 (0) 1223 478200
Fax: +44 (0) 1223 477969
Email: john.munro@filevision.com
Web: http://www.filevision.com

The contents of this communication are confidential and are only intended to be read by the addressee. We apologize if you receive this communication in error and ask that you contact Synergist Limited immediately to arrange for its return. The use of any information contained in this communication by an unauthorized person is strictly prohibited. Synergist Limited cannot accept responsibility for the accuracy or completeness of this communication as it is being transmitted over a public network. If you suspect this message may have been intercepted or amended, please inform Synergist Limited.

by allistar >> Tue, 17 Dec 2002 18:00:24 GMT

[snip]

A better approach would be to have the query run on a particular collection instead of SomeClass.instances. e.g.

app.myCompany.allCustomers.getInstancesThatMatch(objectSet, whereClause);

Doing it this way (and by using transient methods**) you can make the query much more efficient as you can take advantage of how the dictionary is keyed. I.e. is the dictionay is keyed by a property called "name" and the where clause is based on "name" then you can make the transient a lot more efficient by jumping into the collection at the right point. This is MUCH faster for very large collections.

We do this in our product and queries on collections are much faster as we take advantage of how the collection is keyed.

Regards,
Allistar.

** Never underestimate the power of creating Jade code on the fly and running it by using transient methods. It is a very powerful feature which definitely has it's applications, expecially with things like efficient querying.

by johnmunro >> Thu, 19 Dec 2002 18:58:36 GMT

OK, here is a version of the method which goes on Collection. The type override is for cases when you know the items in a collection are a different type than the member type of the collection (e.g. the collection is of type BasePerson but you know it's full of User objects).

countMembers(pWhereClause : String; typeOverride : Type): Integer;

vars
source : String;
count, errorCode, errorPos, errorLength : Integer;
meth : JadeMethod;
ex : SystemException;
iter : Iterator;
item : Any;
compileType : Type;
begin

if typeOverride <> null then
compileType := typeOverride;
else
compileType := class.CollClass.memberType;
endif;

if pWhereClause <> null then
source := "begin return " & pWhereClause & "; end;";
meth := process.createTransientMethod("filter", compileType, currentSchema, source, true, Boolean, errorCode, errorPos, errorLength);

if errorCode <> 0 then
create ex;

ex.errorCode := errorCode;
ex.errorItem := source[errorPos:errorLength+1];

if ex.text.pos("Undefined message number", 1) > 0 then
ex.extendedErrorText := process.getErrorText(errorCode);
endif;

raise ex;

return null;
endif;
endif;

if pWhereClause <> null then
iter := createIterator;

while iter.next(item) do
if process.executeTransientMethod(meth, item).Boolean then
count := count + 1;
endif;
endwhile;

return count;
else
return size;
endif;

epilog
if meth <> null then
process.deleteTransientMethod(meth);
endif;

delete iter;

end;

Incidentally, the type override is optional, but Jade doesn't support optional paramters, so you pass in null if you don't want to use a type override. I did have a go at using paramListType but couldn't work out how to get individual parameters out of it...

John Munro

---
Synergist Limited - Home of FileVision
The Bioscience Innovation Centre
Cowley Road, Cambridge, UK
CB4 0DS

Telephone: +44 (0) 1223 478200
Fax: +44 (0) 1223 477969
Email: john.munro@filevision.com
Web: http://www.filevision.com

The contents of this communication are confidential and are only intended to be read by the addressee. We apologize if you receive this communication in error and ask that you contact Synergist Limited immediately to arrange for its return. The use of any information contained in this communication by an unauthorized person is strictly prohibited. Synergist Limited cannot accept responsibility for the accuracy or completeness of this communication as it is being transmitted over a public network. If you suspect this message may have been intercepted or amended, please inform Synergist Limited.

by allistar >> Thu, 19 Dec 2002 21:21:17 GMT

Hi John,
One problem with that methodology is that it is executing the
transient method on every object in the collection without paying attention to how the collection is keyed.

Attached are two classes: UTQuery and UTQueryLine.
***I am having trouble sending the attachments, I'll try in a followup message.***

Take a look at the UTQuery:generateQuerySource method, it creates a transient method on the actual collection class, and runs that query.
It:
- takes advantage of the keys of the collection
- does AND and OR queries

to display all Customers who have "name > "Bob" and "isSpecial" = true and "allInvoices" is emtpy, do this:

create utQuery transient;
utQuery.set(<type of class in collection>);
utQuery.addQueryLine(true, "name", UTQueryLine.Query_Greater, "Bob", String, true);
utQuery.addQueryLine(true, "isSpecial" UTQueryLine.Query_True, null, Boolean, false);
utQuery.addQueryLine(true, "allInvoices" UTQueryLine.Query_Empty,
null, InvoiceDict, false);

//the run the query, passing it an empty result collection utQuery.runQuery(app.myCompany, "allCustomers", null, resultArray);

This will fillup the "results" array with all objects in the app.myCompany.allCustomers collection that match the three criteria above.

We have found that this creates a very efficient query, especially as
it looks at the meta data to see how the collection is keyed. It also updates a progress bar (if you pass it one) during the query as well.

If you try this out let me know if you get it to work (or if you have
any issues).

Regards,
Allistar. ------------------------------------------------------------------
Allistar Melville
Software Developer
Auckland, NEW ZEALAND

Greentree International,
Developer of Greentree Financial Software. ------------------------------------------------------------------

by allistar >> Thu, 19 Dec 2002 21:26:23 GMT

On Thu, 19 Dec 2002 21:21:17 GMT, allistar@ihug.co.nz (allistar)
wrote:

The Jade news server is not letting me attach any files, so I've put
it here:

http://203.96.39.8/query.zip

It's only 5 kb (zipped).

Note that we use this in our Jade product to allow for flexible, fast user definable queries.

Regards,
Allistar.

------------------------------------------------------------------
Allistar Melville
Software Developer
Auckland, NEW ZEALAND

Greentree International,
Developer of Greentree Financial Software. ------------------------------------------------------------------