Datesearch SQL

Atom

Joined: 2006-10-24
Posts: 54

Posted: Mon, 2006-11-20 13:56

Hi,

I have installed the third party datesearch and would like to modify it so it does not consider album dates when searching. I want a pure 'when the piture was uploaded' datesearch.

Can anyone help with this?

Here is the SQL from datesearch:

SELECT [GalleryItem::id], [GalleryUser::fullName], 
[GalleryUser::userName], [GalleryEntity::modificationTimestamp], 
[GalleryItem::originationTimeStamp] FROM [GalleryItem], 
[GalleryAccessSubscriberMap], [GalleryEntity], [GalleryUser] WHERE 
(ABS( [GalleryItem::originationTimeStamp] - ?) <= 1162587404) AND 
[GalleryItem::id] = [GalleryAccessSubscriberMap::itemId] AND 
[GalleryItem::id] = [GalleryEntity::id] AND [GalleryUser::id] = 
[GalleryItem::ownerId] AND 
[GalleryAccessSubscriberMap::accessListId] IN (?,?,?) ORDER BY 
[GalleryEntity::modificationTimestamp] DESC, [GalleryItem::id] DESC

I don't see any reference to an Album. Is an album condiered a "GalleryItem" in this case? Is there any difference, internally, between an album and a picture?

Atom

Joined: 2006-10-24
Posts: 54

Posted: Mon, 2006-11-20 14:29

So I am looking in the database and I see the field "g_originationTimestamp" in the table "g2_Item".
The timestamp is something like this 1162239815.
It is of datatype INTEGER. Why is a date being stored as an integer?
How do I translate that INTEGER into someting like "10/30/2006"?

Somehow, magically, the token [GalleryItem] must be converted to "g2_Item" by the core? Because the above SQL will not execute directly in the MySQL Administrator tool.

Does anyone understand what is going on in this SQL?

Atom

Joined: 2006-10-24
Posts: 54

Posted: Mon, 2006-11-20 14:36

So it looks like I only need rows returned from "g2_Item" where g_canContainChildren ='0'.

valiant

Joined: 2003-01-04
Posts: 32509

Posted: Mon, 2006-11-20 14:38

- we're using unix timestamps thus time values are integer numbers (number of seconds since 1970-01-01)
- the G2 storage search queries are SQL queries with some abstractions. [GalleryItem] gets translated to g2_Item, [GalleryItem::title] to g2_Item.g_title, etc.

Atom

Joined: 2006-10-24
Posts: 54

Posted: Mon, 2006-11-20 15:46

Thanks Valiant,

I kind of figured that out through trial an experimenting.
So I added:

and [GalleryItem::canContainChildren] = 0

To the count query and the actual query and now my results do not include Albums. (yeah!)

However, I still get items returned that should not be returned.
For instance, if I type in a date 11/2005 in my search box, I get all images in my Gallery returned. This is incorrect because I did not even have Gallery installed last year.
I think I'll keep chipping away at this, I am getting somewhere.

Atom Joined: 2006-10-24 Posts: 54	Posted: Mon, 2006-11-20 13:56
	Hi, I have installed the third party datesearch and would like to modify it so it does not consider album dates when searching. I want a pure 'when the piture was uploaded' datesearch. Can anyone help with this? Here is the SQL from datesearch: SELECT [GalleryItem::id], [GalleryUser::fullName], [GalleryUser::userName], [GalleryEntity::modificationTimestamp], [GalleryItem::originationTimeStamp] FROM [GalleryItem], [GalleryAccessSubscriberMap], [GalleryEntity], [GalleryUser] WHERE (ABS( [GalleryItem::originationTimeStamp] - ?) <= 1162587404) AND [GalleryItem::id] = [GalleryAccessSubscriberMap::itemId] AND [GalleryItem::id] = [GalleryEntity::id] AND [GalleryUser::id] = [GalleryItem::ownerId] AND [GalleryAccessSubscriberMap::accessListId] IN (?,?,?) ORDER BY [GalleryEntity::modificationTimestamp] DESC, [GalleryItem::id] DESC I don't see any reference to an Album. Is an album condiered a "GalleryItem" in this case? Is there any difference, internally, between an album and a picture?
	XUser login Username: * Password: * Create new account Request new password
Atom Joined: 2006-10-24 Posts: 54	Posted: Mon, 2006-11-20 14:29
	So I am looking in the database and I see the field "g_originationTimestamp" in the table "g2_Item". The timestamp is something like this 1162239815. It is of datatype INTEGER. Why is a date being stored as an integer? How do I translate that INTEGER into someting like "10/30/2006"? Somehow, magically, the token [GalleryItem] must be converted to "g2_Item" by the core? Because the above SQL will not execute directly in the MySQL Administrator tool. Does anyone understand what is going on in this SQL?

Atom Joined: 2006-10-24 Posts: 54	Posted: Mon, 2006-11-20 14:36
	So it looks like I only need rows returned from "g2_Item" where g_canContainChildren ='0'.

valiant Joined: 2003-01-04 Posts: 32509	Posted: Mon, 2006-11-20 14:38
	- we're using unix timestamps thus time values are integer numbers (number of seconds since 1970-01-01) - the G2 storage search queries are SQL queries with some abstractions. [GalleryItem] gets translated to g2_Item, [GalleryItem::title] to g2_Item.g_title, etc.

Atom Joined: 2006-10-24 Posts: 54	Posted: Mon, 2006-11-20 15:46
	Thanks Valiant, I kind of figured that out through trial an experimenting. So I added: and [GalleryItem::canContainChildren] = 0 To the count query and the actual query and now my results do not include Albums. (yeah!) However, I still get items returned that should not be returned. For instance, if I type in a date 11/2005 in my search box, I get all images in my Gallery returned. This is incorrect because I did not even have Gallery installed last year. I think I'll keep chipping away at this, I am getting somewhere.

XUser login