RE: HQL question (wasRE: [xwiki-users] SQL hair-pulling)
by THOMAS, BRIAN M (SBCSI)
Mark:
That seemed, intuitively, to be the way that things were put together, and that's encouraging. However, I'm hoping that your claim that the xwiki objects give most of what I need is true, since that's as deep as I hope to have to get into it. Again, I'm acting as proxy to application developers here, so I need to be sure that this is all as easy as it seems.
In particular, the joins are hairy - a necessary consequence of the flexible object structure. And I'm still a bit confused about how to parse the results.
A use case is this: I have a document that I threw together quickly and easily that lists projects that I am responsible for. Being driven entirely from the objects on the page, it was easy to share, to the point that nearly everyone else who uses it simply uses #includeForm("Brian.WorkPages") - not a good idea from an organizational point of view, but wholly acceptable in a purely experimental environment. Each object on the page lists the name of the project, the name of the project's plan document, and its current status, due dates, etc. For terseness and convenience, I make the project name the text of a link to the project plan document. As success breeds success, I decided it was a really good way to report status to my boss, so I did two things: added a "journal entry" feature to each of the plan documents, which allowed me to add dated comments on document progress and status changes; and created another data-driven page which copied the data from the original page, followed the links to the plan pages, and displayed the project table again, then the journal entries for each one, with intra-page links from the status field text for convenience. In this way, I didn't need to make out a separate weekly status report for my boss; this page would show status at a glance, with links to more detailed information right at hand.
This was good, but two things were wrong: first, the projects were listed in the default order in which getObjects() listed them, which was the order of their creation; second, all the journal entries showed up. I wanted the projects to be listed in order by priority and/or due date, and to list journal entries from only the current week.
I had already seen that I could modify the WHERE clause in searchDocuments(), and used that to add options to Main.AllDocs to order by date or creator. but getObjects() gave me no such ability, so I had to use search(). And rather than futz around with trying to get the search results to fit the same wiki Object format, I followed another example and called getObject() with the objects' number fields, which was all I returned from the query. I could also have used getObjects with a list of objects, but I didn't think of it, and am still not sure I'd know the right way to turn the returned ArrayList into a Vector.
In short, the SQL wasn't hard; tying all the attributes to the BaseObject wasn't hard, when I wanted to do it, but for one thing, when I returned a BaseObject only, I wasn't able to find any of its properties attached to it at all, and none of the syntax that I expected to be able to use to request all fields worked - I suspect because the query schema is virtual and each field had to be specified. In other words, "select obj.* from BaseObject as obj, ..." was refused.
The other matter I asked about before was why I couldn't return an arbitrary expression as I could with SQL, even though the same expression had worked in a qualifier on the same query. I really wanted to ditch all the code I had written to figure out when one week ago was, and since it was already in my qualifier, I thought I'd return it also, but Hibernate didn't like that.
brain[sic]
-----Original Message-----
From: Mark Robinson [mailto:mark.robinson@hk.tntfreight.com]
Sent: Friday, March 24, 2006 11:14 PM
To: xwiki-users(a)objectweb.org
Subject: Re: HQL question (wasRE: [xwiki-users] SQL hair-pulling)
Hi Brian.
Sorry for the lack of clarity there, it was late in my part of the world.
This is my understanding of it in general, but I am sure someone can correct how it relates to xwiki along the way.
Hibernate hides the process of creating objects and populating them with data retrieved from the database.
The Java objects have an almost direct relationship to the database structure. The tables are represented by classes and the columns are represented by properties of the object.
So when an object is created, it already fully represents the table structure. This gives you the advantage of being able to describe the equivalent of SQL queries very simply by referring to their properties.
There are getters and setter for the properties so to select a particular item from the database all you have to do is "get" that property with the value you want.
Example: Instead of "SELECT * FROM foo WHERE id = '22'" you could just use something like ...
item = getSession().get(foo.id, '22');
This is the beautiful bit about it. "item" is now an object with all the data from that row of the table and you can adjust the properties with the setter methods.
Example: Instead of "UPDATE foo SET Name='New Name', Age='36' WHERE id='22'"
item.setName("New Name");
item.setAge("36");
getSession().update(item);
Note: When you want to update the database you just call method (eg update(item)) and your object returns all the current object properties to the appropriate row in the database.
So how does this relate to XWiki? The mapping of xwiki object to the MySQL database is in xwiki.hbm.xml. Using the hibernate query from the category field in the blog as an example.
Example: select prop.value from BaseObject as obj, StringProperty as prop where obj.className='Blog.Categories' and prop.id.id = obj.id and prop.id.name='name'
>From looking at the mapping I can see that "BaseObject" is of the class "com.xpn.xwiki.objects.BaseObject" and it is mapped to the table "xwikiobjects" in the database.
It is then aliased to "obj".
I can also see that the property "className" of "obj" is mapped to "XWO_CLASSNAME", "name" is mapped to "XWO_NAME" and "id" is mapped to "XWO_ID".
"StringProperty" is a joined subclass (hibernate returns all the subclasses automatically) and is mapped to "com.xpn.xwiki.objects.StringProperty" which relates to the table "xwikistrings". In that subclass "value" is mapped to "XWS_VALUE".
StringProperty is then aliased as prop.
So after all that, what we are trying to say is something to the effect of "give me a list object that is made up of the names of the categories".
Why do we have to do it like that? Because the data is described generically in the database schema and stored in 3 different places. To retrieve it you need to ask first to find the objects of the wiki defined class 'Blog.Categories' then you need to retrieve the properties related to those objects (in this case, only the strings) and then you want to list the values of those strings.
So the strength of XWiki is that you can create classes, instances of those classes and manipulate them through the wiki pages to represent almost anything. It also means that it is very independent of the backend (as shown by using HQSQL in the standalone version).
However, the drawback is that you need to understand clearly how your classes are put together because the data is spread across the database (Remember, your classes/objects that you have created are represented in the database by the generic BaseObject and its properties).
Getting back to using this info. I find mostly that the $xwiki, $request and $doc objects give you access via the api to most of what you need anyway without getting that deep into it.
Example: From the mapping file I can see that the string property "fullName" in class com.xpn.xwiki.doc.XWikiDocument (eg $doc or doc) relates to "XWD_FULLNAME" in the "xwikidoc" table.
So if I wanted to list all the different webspaces in my xwiki with the default page of "WebHome" I know that "doc" represents the "xwikidoc" table and "fullName" represents "XWD_FULLNAME" so I write the "where" part of the query as "doc.fullName like '%.WebHome'"
The searchDocuments method of $xwiki translates this as "select distinct doc.web, doc.name from com.xpn.xwiki.doc.XWikiDocument as doc where fullName like '%.WebHome'"
So you end up with a list as demonstrated below in a Velocity setting.
#set($whereclause = "doc.fullName like '%.WebHome'")
#set($mylist = $xwiki.searchDocuments($whereclause))
1.1 The list returned
\\$mylist
1.1 Individual items
#foreach ($item in $mylist)
#set($thisdoc = $xwiki.getDocument($item))
* $thisdoc.getFullName()
#end
Hope that wasn't too wandering and full of mistakes. It is just from poking around a bit inside so could be completely misguided. I look forward to comments.
Cheers,
Mark
On 25 Mar 2006, at 2:30 AM, THOMAS, BRIAN M ((SBCSI)) wrote:
Okay; will do.
But I was just about to reply that if you omit the result-list ("select field, field, ...") then the returned value is the object type of the first object in the "from" list. Exactly what I wanted, and it sounds like what you were describing, but your examples didn't show it.
brain[sic]
-----Original Message-----
From: Mark Robinson [mailto:mark.robinson@hk.tntfreight.com]
Sent: Friday, March 24, 2006 11:35 AM
To: xwiki-users(a)objectweb.org
Subject: Re: HQL question (wasRE: [xwiki-users] SQL hair-pulling)
Hi Brian.
Check this thread out.
http://mail-archive.objectweb.org/xwiki-dev/2005-11/msg00031.html
Cheers,
Mark
On 25 Mar 2006, at 12:57 AM, THOMAS, BRIAN M ((SBCSI)) wrote:
Huh...
Sounds intriguing, but I'm puzzled about a lot of details, like:
how do you do that?
Probably an example would be very useful. Do you have one handy?
brain[sic]
-----Original Message-----
From: Mark Robinson [mailto:mark.robinson@hk.tntfreight.com]
Sent: Friday, March 24, 2006 10:48 AM
To: xwiki-users(a)objectweb.org
Subject: Re: HQL question (wasRE: [xwiki-users] SQL hair-pulling)
It seems to be like ADODB, forget about query strings. Create an
object. Set variables, indicate the relationships (eg joins) and call
the method fpr retrieving the data.
Result data as an object. Updates are a breeze.
Cheers,
Mark
On 25 Mar 2006, at 12:32 AM, THOMAS, BRIAN M ((SBCSI)) wrote:
Would this perhaps be why I can't use an arbitrary expression as a
result field?
Specifically, my query for objects less than a week old used the SQL
expression:
subdate(curdate(), 7)
which worked fine when used in a comparison to a date field in the
WHERE clause, but provoked an error when I tried to use it in the
column-list, which is (I believe) clearly legal SQL syntax. If this
were a feature that HQL simply didn't support, it would explain the
problem. Indeed, that is what the error message seems to say:
Error number 4001 in 4: Error while parsing velocity page
Brian.WorkPagesTest Wrapped Exception: Invocation of method 'search'
in class com.xpn.xwiki.api.XWiki threw exception class
com.xpn.xwiki.XWikiException : Error number 3223 in 3: Exception
while searching documents with sql select subdate(curdate(), 7),
prop.value, [...] where [...] or subdate(curdate(), 7) <=
prop.value ) [...]
Wrapped Exception: No data type for node:
org.hibernate.hql.ast.MethodNode
+-METHOD_CALL? MethodNode: '('
| +-METHOD_NAME? IdentNode: 'subdate' {originalText=subdate}
| -EXPR_LIST? SqlNode: 'exprList'
| +-METHOD_CALL? MethodNode: '('
| | +-METHOD_NAME? IdentNode: 'curdate' {originalText=curdate}
| | -EXPR_LIST? SqlNode: 'exprList'
| -NUM_INT? LiteralNode: '7'
brain[sic]
-----Original Message-----
From: jeremi joslin [mailto:jeremi23@gmail.com]
Sent: Thursday, March 23, 2006 10:47 PM
To: xwiki-users(a)objectweb.org
Subject: Re: [xwiki-users] SQL hair-pulling
Yes, XWiki is using hibernate for the database mapping. So it's not
a sql query, but a hql query. You use the objects and members name to
make the request. (if you don't know the hql, you can take a look to
the hibernate documentation :
http://www.hibernate.org/hib_docs/v3/reference/en/html_single/
#queryhql)
Jérémi
--
You receive this message as a subscriber of the xwiki-
users(a)objectweb.org mailing list. To unsubscribe:
mailto:xwiki-users-unsubscribe@objectweb.org
For general help: mailto:sympa@objectweb.org?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/
wws
--
You receive this message as a subscriber of the xwiki-
users(a)objectweb.org mailing list.
To unsubscribe: mailto:xwiki-users-unsubscribe@objectweb.org
For general help: mailto:sympa@objectweb.org?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/
wws
--
You receive this message as a subscriber of the xwiki-users(a)objectweb.org mailing list.
To unsubscribe: mailto:xwiki-users-unsubscribe@objectweb.org
For general help: mailto:sympa@objectweb.org?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws
