headers
Anton Pirker | 5 Mar 19:11

difficult object relational mapping

Hi list!

I am trying to do a rather difficult object relational mapping (orm) and 
i am stuck. Maybe someone can help!

My object-relations:
--------------------

i have events. every event has several (1..n) entries (posts). every of 
these entries has several (1..n) changes. a change has a date.

Now i want to have a collection of events ordered by the date of the 
changes of the entries.

In SQL this looks like this:
-----------------------------

select 	
    ev.event_id,
    ev.event_title,
    max(ch.change_datetime)
from 	
    events ev,
    entries en,
    changes ch
where
    ev.venue_id = 56 and
    ev.event_id = en.event_id and
    en.entry_id = ch.change_parent_id and
    ch.change_parent_prototype = "Entry"
(Continue reading)

Permalink | Reply | 4 comments |
headers
Joshua Paine | 5 Mar 19:23

Re: difficult object relational mapping

Anton,

AFAIK, this isn't possible with Helma's ORM. Either add another column 
to your entries table with the most recent update date (a small 
denormalization that you might well end up making anyway for performance 
reasons) or create and use a view that simulates a table like the above 
with all the necessary triggers to make Helma's belief that it's a 
genuine table not lead to disaster.

If you go the first route and add a column, you could keep it updated 
either with a trigger pretty easily, I'd think. And you can mark it 
read-only for Helma to ensure nothing tries to set it directly.

-Joshua
Permalink | Reply | 3 comments |
headers
Anton Pirker | 5 Mar 19:51

Re: difficult object relational mapping

Hi Joshua!

First, thanks for the quick response.

Joshua Paine schrieb:
> Anton,
> 
> AFAIK, this isn't possible with Helma's ORM. Either add another column 
> to your entries table with the most recent update date (a small 
> denormalization that you might well end up making anyway for performance 
> reasons)  you could keep it updated 
> either with a trigger pretty easily, I'd think. And you can mark it 
> read-only for Helma to ensure nothing tries to set it directly.

i like this idea! i think i will give it a try. sometimes i really get 
lost in trying to do everything really clean an like-in-the-books and 
forget about practical stuff.

Thanks for the tip, you probably save me from a lot of misery ;)

-
Anton

> 
> -Joshua
Permalink | Reply | 2 comments |
headers
Breton Slivka | 5 Mar 21:33
Favicon

Re: difficult object relational mapping

A simpler way you might do it is to use helma.Database to perform your
ordered query, to get an array of object ID's. Then simply loop
through those id's with a collection.get(id[i]) call.  In a language
like PHP this would be a major performance mistake since it may
perform a new query for each and every get call. BUT because of
helma's object caching this actually works really fast, with very few
calls to the DB.
Permalink | Reply | 1 comment |
headers
Joshua Paine | 5 Mar 22:49

Re: difficult object relational mapping

Breton Slivka wrote:
> A simpler way you might do it...

As you say, this probably wouldn't be outrageously slow, but it would 
mean that the collection wasn't ordered correctly and you'd have to work 
with it differently from any other collections in the app.

Slightly faster to implement, perhaps (if you're not quick with writing 
database triggers), but the added inconsistency in the app would not be 
worth it IMO.
Permalink | Reply | 0 comments |
headers
Maksim Lin for technical support mailling lists | 5 Mar 23:06
Picon

Re: difficult object relational mapping

I think what what Breton was suggesting was not having a collection at
all.
The idea is that you suggest use helma.Database to do whatever
super-complex query you need that just returns the IDs of whatever
Prototype you want and then you just loop through those ids to build a
simple JS array of the HopObjects and return them.
for example:

function getEventsByRecentChanges() {

	var sqlResult =
helma.Database.getInstance("MyDataSource").query("SELECT ID from ...);
	var resultList = [];
	for (var i=0; i < sqlResults.length; i++) {
		resultList.push( Event.getById(sqlResult.[i].ID) );
	}
	return resultList;
}

voila!
the above method pretty much gives you the same as
root.recentEvents.list(), ie. an array of the Hopobjects that you need,
sorted in the correct order. And as Breton mentioned, helma cache pretty
much will make sure that you don't even generate any more DB traffic if
you already have all the Event objects in the cache already.

I am hoping that the promised generic query interface design in the wiki
would work on the same lines, just letting you define the above with
simple javascript properties rather then having to drop into SQL
*especially* since it would be nice to also have an implementation which
(Continue reading)

Permalink | Reply | 1 comment |
headers
Joshua Paine | 6 Mar 05:07

Re: difficult object relational mapping

Maksim Lin for technical support mailling lists wrote:
> function getEventsByRecentChanges() {
> 	var sqlResult =
> helma.Database.getInstance("MyDataSource").query("SELECT ID from ...);
> 	var resultList = [];
> 	for (var i=0; i < sqlResults.length; i++) {
> 		resultList.push( Event.getById(sqlResult.[i].ID) );
> 	}
> 	return resultList;
> }

This is worse than what Breton suggested because it loads every Event in 
the DB into memory all at once. For the original poster I still favor a 
way to use the provided collections feature.

We could improve on your sample code, though, to get something at least 
as fast that only loads the entire list of IDs into memory at once. 
(That's still too much in some cases, but this will work in more cases 
than the sample above.) Also make it generic and act a bit like a 
HopObject collection:

function createOrderedView(hopType,dataSourceName,query) {
   /* query must select ID */
   var rx = /^\s*[Ss][Ee][Ll][Ee][Cc][Tt]\s+(\w+\s+(as\s+)?)?ID,?\s/;
   if(!rx.test(query)) return null;
   var sqlResults = helma.Database.getInstance(dataSourceName)
                    .query(query);
   var pub = ({
     get : function(i) {
       if(!sqlResults[i]) return null;
(Continue reading)

Permalink | Reply | 0 comments |
headers
philipp.naderer | 10 Mar 10:06
Picon

Broken Docbook documentation link

http://helma.org/docs/docbook/ is not working. Can somebody fix the link? There
are some really good hints for beginners in that document ;-)

Regards,
Philipp
Permalink | Reply | 1 comment |
headers
Chris Zumbrunn | 10 Mar 17:18
Gravatar

Re: Broken Docbook documentation link


On Mar 10, 2008, at 10:06 , philipp.naderer <at> boozle.de wrote:

> http://helma.org/docs/docbook/ is not working. Can somebody fix the  
> link? There
> are some really good hints for beginners in that document ;-)

Fixed. Thanks for pointing it out. I now moved it over to the gobi  
based dev.helma.org site, so this can't happen again (this has been a  
reoccurring problem).

Cheers,
Chris
Permalink | Reply | 0 comments |
headers
Maksim Lin for technical support mailling lists | 11 Mar 06:33
Picon

Re: difficult object relational mapping

Yes that is a better solution!
I must admit I've gotten into the habit of not worrying about pulling
large collections of object into memeory as a large project I've been
working on for a while now has a very large memory cache to basically
keep every HopObject in memory for performance reason (lots of reads,
few writes) but agreed that its definitly not somehting you want to do
in most cases.

Actually I wonder if this could not be the basis for the "pure js" style
HopObject implementation?
I mean from a "data" point of view, HopObject collections need to
implement the following "interface" of functions:

getById()
get()
size()
list()
add()
addAt()
indexOf()
remove()
prefetchChildren()
update()

and to me it looks like you could implement pretty much all of it with
the approach below. I guess what I have in mind is keeping a "global"
hashmap as the in-mem cache of Hopobjects, keyed on Prototype & then on
ID (ie. the implementation of the current static HopObject.getById()
function)  and so collections could be intrinsicaly dynamic and with the
framework using them to create collections that are declaritively
(Continue reading)

Permalink | Reply | 2 comments |

Gmane