[Erp5-dev] start_date on non-movement object

[Łukasz Nowak]

Hello,

Using clean r18550 system.

There is Person object. They have Birthday defined as start_date. So I
set this date.

It is possible to choose Birthday as column on Person listbox. Chosen.

But it is not possible to sort/search by this column. Log message:

2008-03-21T16:14:34 WARNING SQLCatalog buildSQLQuery could not build
sort index (start_date -> None)

How to sort/search using such property? It is not catalogued, isn't it?
As start/stop date is on movements/deliveries. Yes? No? I'm stuck.
Again. ;)

Well. And if I create new table in catalog, which will have start_date
column, catalog Persons in it. How to use that? Eg. table would be named
persondata. Editing start_date to persondata.start_date is not
disallowing sort/search, and Persons object to have entries in
persondata table...

I know I do not know much about SQL related issues, but believe me - I
read many times what is in SQLCatalog/*txt, SQLCatalog/help/* and
ERP5Catalog/*txt, ERP5Catalog/help/* ...

Regards,
Luke

[bartek]

Post by Łukasz Nowak
Hello,
Using clean r18550 system.
There is Person object. They have Birthday defined as start_date. So I
set this date.
It is possible to choose Birthday as column on Person listbox. Chosen.

AFAIK technically if you add Person portal type to "movement" type group
the start_date should be catalogged.

Of course it doesn't make any sense :D Person is not a movement, so this
is not a clean way to do it. Come to think of that, maybe the date of
birth should not be start_date...?

Post by Łukasz Nowak
2008-03-21T16:14:34 WARNING SQLCatalog buildSQLQuery could not build
sort index (start_date -> None)
How to sort/search using such property? It is not catalogued, isn't it?
As start/stop date is on movements/deliveries. Yes? No? I'm stuck.
Again. ;)
Well. And if I create new table in catalog, which will have start_date
column, catalog Persons in it. How to use that? Eg. table would be named
persondata. Editing start_date to persondata.start_date is not
disallowing sort/search, and Persons object to have entries in
persondata table...

Have you followed http://www.erp5.org/HowToAddTableToCatalog? Still
doesn't work?

Bartek

Post by Łukasz Nowak
I know I do not know much about SQL related issues, but believe me - I
read many times what is in SQLCatalog/*txt, SQLCatalog/help/* and
ERP5Catalog/*txt, ERP5Catalog/help/* ...
Regards,
Luke

[Łukasz Nowak]

Hello,

On 2008-03-22, 11:23:04
bartek <bartek at erp5.pl> wrote:

(...)

Post by bartek
AFAIK technically if you add Person portal type to "movement"

type

Post by bartek
group the start_date should be catalogged.
Of course it doesn't make any sense :D Person is not a movement, so
this is not a clean way to do it. Come to think of that, maybe the
date of birth should not be start_date...?

Well - it was made to be more generic - look at upgrade notes.

Post by bartek

Post by Łukasz Nowak
2008-03-21T16:14:34 WARNING SQLCatalog buildSQLQuery could not build
sort index (start_date -> None)
How to sort/search using such property? It is not catalogued, isn't
it? As start/stop date is on movements/deliveries. Yes? No? I'm
stuck. Again. ;)
Well. And if I create new table in catalog, which will have
start_date column, catalog Persons in it. How to use that? Eg.
table would be named persondata. Editing start_date to
persondata.start_date is not disallowing sort/search, and Persons
object to have entries in persondata table...

Have you followed http://www.erp5.org/HowToAddTableToCatalog? Still
doesn't work?

Indeed. My persondata table has start_date and price - as I added price
to Person type. Everything is catalogued, but I'm able to search using
catalog with price property, using start_date do not work.

Regards,
Luke

[bartek]

Post by Łukasz Nowak
Hello,
On 2008-03-22, 11:23:04
(...)

Post by bartek
AFAIK technically if you add Person portal type to "movement"

type

Post by bartek
group the start_date should be catalogged.
Of course it doesn't make any sense :D Person is not a movement, so
this is not a clean way to do it. Come to think of that, maybe the
date of birth should not be start_date...?

Well - it was made to be more generic - look at upgrade notes.

Post by bartek

Post by Łukasz Nowak
2008-03-21T16:14:34 WARNING SQLCatalog buildSQLQuery could not build
sort index (start_date -> None)
How to sort/search using such property? It is not catalogued, isn't
it? As start/stop date is on movements/deliveries. Yes? No? I'm
stuck. Again. ;)
Well. And if I create new table in catalog, which will have
start_date column, catalog Persons in it. How to use that? Eg.
table would be named persondata. Editing start_date to
persondata.start_date is not disallowing sort/search, and Persons
object to have entries in persondata table...

Have you followed http://www.erp5.org/HowToAddTableToCatalog? Still
doesn't work?

Indeed. My persondata table has start_date and price - as I added price
to Person type. Everything is catalogued, but I'm able to search using
catalog with price property, using start_date do not work.

Did you select persondata table as search table in catalog properties?

B

Post by Łukasz Nowak
Regards,
Luke

[Łukasz Nowak]

Hello,

On 2008-03-25, 09:19:56
bartek <bartek at erp5.pl> wrote:

(...)

Post by bartek

Post by Łukasz Nowak
Indeed. My persondata table has start_date and price - as I

added

Post by bartek

Post by Łukasz Nowak
price to Person type. Everything is catalogued, but I'm able to
search using catalog with price property, using start_date do not
work.

Did you select persondata table as search table in catalog properties?

Yes. Using price property it is possible to search/sort on Person
objects.

Regards,
Luke

[bartek]

Post by Łukasz Nowak
Hello,
On 2008-03-25, 09:19:56
(...)

Post by bartek

Post by Łukasz Nowak
Indeed. My persondata table has start_date and price - as I

added

Post by bartek

Post by Łukasz Nowak
price to Person type. Everything is catalogued, but I'm able to
search using catalog with price property, using start_date do not
work.

Did you select persondata table as search table in catalog properties?

Yes. Using price property it is possible to search/sort on Person
objects.

What are the results of:

context.portal_catalog(portal_type='Person', price=123, src__=1)
context.portal_catalog(portal_type='Person', start_date='2001/01/01'
src__=1)
kw = {'portal_type':'Person', 'persondata.start_date':'2001/01/01',
'src__':1}
context.portal_catalog(**kw)

B

Post by Łukasz Nowak
Regards,
Luke

[Łukasz Nowak]

Hello,

On 2008-03-25, 09:37:11
bartek <bartek at erp5.pl> wrote:

(...)

Post by bartek
context.portal_catalog(portal_type='Person', price=123, src__=1)
context.portal_catalog(portal_type='Person', start_date='2001/01/01'
src__=1)
kw = {'portal_type':'Person', 'persondata.start_date':'2001/01/01',
'src__':1}
context.portal_catalog(**kw)

I've used 'birth_name' as another property on Person object.

Look at attached test.py - script used to create result.txt

I see - it is correctly quering catalog.

But look at this:

mysql> select * FROM persondata;
+------+---------------------+------------+
| uid | start_date | birth_name |
+------+---------------------+------------+
| 6599 | 1979-09-12 22:00:00 | young |
| 6899 | 1939-12-02 23:00:00 | old |
+------+---------------------+------------+
2 rows in set (0.00 sec)

And it does not matter, if I set start_date from UI or by
object/setStartDate?value=1979/09/13 or
object/setStartDate?value=1939/12/03


From zope those dates are printed with repr in scripts - and they look
correctly in ZODB. So - might it be, that I've created this table not
correctly?

I've attached zsql used to create and catalog values.

Using revision 18850. Thanks for pointing out this testing scenario -
more things become clear.

Regards,
Luke
--
?ukasz Nowak R&D Ventis http://www.ventis.com.pl/
tel: [phone] fax: [phone]
``Use the Source, Luke...'' I am only craftsman.
-------------- next part --------------
A non-text attachment was scrubbed...
Name: test.py
Type: text/x-python
Size: 1199 bytes
Desc: not available
URL: <http://mail.tiolive.com/pipermail/erp5-dev/attachments/20080325/4c5b4b1b/attachment.py>
-------------- next part --------------
An embedded and charset-unspecified text was scrubbed...
Name: result.txt
URL: <http://mail.tiolive.com/pipermail/erp5-dev/attachments/20080325/4c5b4b1b/attachment.txt>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: z_catalog_persondata_list.zsql
Type: application/octet-stream
Size: 727 bytes
Desc: not available
URL: <http://mail.tiolive.com/pipermail/erp5-dev/attachments/20080325/4c5b4b1b/attachment.obj>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: z_create_persondata.zsql
Type: application/octet-stream
Size: 231 bytes
Desc: not available
URL: <http://mail.tiolive.com/pipermail/erp5-dev/attachments/20080325/4c5b4b1b/attachment-0001.obj>

[Łukasz Nowak]

Hello,

On 2008-03-25, 13:14:33
?ukasz Nowak <lukasz.nowak at ventis.com.pl> wrote:

(...)

Post by Łukasz Nowak
Using revision 18850. Thanks for pointing out this testing
scenario - more things become clear.

And taht is my fault. After reading KnownBugs from "common Polish wiki",
right now all is working correctly, with such patch:
Index: sqlvar.py
===================================================================
--- sqlvar.py (revision 18850)
+++ sqlvar.py (working copy)
@@ -71,10 +71,11 @@

try:
if getattr(v, 'ISO', None) is not None:
- v=v.toZone('UTC').ISO()
+ v=v.ISO()
+ elif getattr(v, 'strftime', None) is not None:
+ v=v.strftime('%Y-%m-%d %H:%M:%S')
else:
- v = DateTime(v)
- v=v.toZone('UTC').ISO()
+ v=str(v)
except:
if not v and args.has_key('optional') and args['optional']:
return 'null'

Sorry again. That was my fault. I'm not good enough in reading unit
tests, and haven't properly noted about this bug without bugtracker.

Problem solved^Wworkarounded (no time to upgrade to HEAD from 18850
to check if on clean will it work).

Regards,
Luke

[bartek]

Post by Łukasz Nowak
Hello,
On 2008-03-25, 13:14:33
(...)

Post by Łukasz Nowak
Using revision 18850. Thanks for pointing out this testing
scenario - more things become clear.

And taht is my fault. After reading KnownBugs from "common Polish wiki",

This is our internal document which refers to r18850, I don't know what
is the HEAD status here.

Anyway, it is strange that it solved your problem; the sqlvar.py issue
causes activities to be fired one hour too late - but apparently only in
Poland :) I've discussed the issue with Ivan and he said it works in all
timezones.

B.

Post by Łukasz Nowak
Index: sqlvar.py
===================================================================
--- sqlvar.py (revision 18850)
+++ sqlvar.py (working copy)
@@ -71,10 +71,11 @@
- v=v.toZone('UTC').ISO()
+ v=v.ISO()
+ v=v.strftime('%Y-%m-%d %H:%M:%S')
- v = DateTime(v)
- v=v.toZone('UTC').ISO()
+ v=str(v)
return 'null'
Sorry again. That was my fault. I'm not good enough in reading unit
tests, and haven't properly noted about this bug without bugtracker.
Problem solved^Wworkarounded (no time to upgrade to HEAD from 18850
to check if on clean will it work).
Regards,
Luke

[Jérome Perrin]

Post by bartek

Post by Łukasz Nowak
Hello,
On 2008-03-25, 13:14:33
(...)

Post by Łukasz Nowak
Using revision 18850. Thanks for pointing out this testing
scenario - more things become clear.

And taht is my fault. After reading KnownBugs from "common Polish wiki",

This is our internal document which refers to r18850, I don't know what
is the HEAD status here.
Anyway, it is strange that it solved your problem; the sqlvar.py issue
causes activities to be fired one hour too late - but apparently only in
Poland :) I've discussed the issue with Ivan and he said it works in all
timezones.

Hello,

Are you using nexedi version of ZMySQLDA ? this patch
https://svn.erp5.org/?view=rev&revision=17868 is important.

J?rome

[Łukasz Nowak]

Hello,

On 2008-03-25, 14:09:26

Post by Łukasz Nowak
Hello,
Are you using nexedi version of ZMySQLDA ? this patch
https://svn.erp5.org/?view=rev&revision=17868 is important.

I'm using:

Path: Products/ZMySQLDA
URL: https://svn.erp5.org/repos/public/erp5/trunk/products/ZMySQLDA
Repository Root: https://svn.erp5.org/repos/public
Repository UUID: 20353a03-c40f-0410-a6d1-a30d3c3de9de
Revision: 18850
Node Kind: directory
Schedule: normal
Last Changed Author: ivan
Last Changed Rev: 17868
Last Changed Date: 2007-11-28 15:19:18 +0100 (Wed, 28 Nov 2007)

And above problem occurred.

Regards,
Luke

[bartek]

Post by Łukasz Nowak

Post by bartek

Post by Łukasz Nowak
Hello,
On 2008-03-25, 13:14:33
(...)

Post by Łukasz Nowak
Using revision 18850. Thanks for pointing out this testing
scenario - more things become clear.

And taht is my fault. After reading KnownBugs from "common Polish wiki",

This is our internal document which refers to r18850, I don't know what
is the HEAD status here.
Anyway, it is strange that it solved your problem; the sqlvar.py issue
causes activities to be fired one hour too late - but apparently only in
Poland :) I've discussed the issue with Ivan and he said it works in all
timezones.

Hello,
Are you using nexedi version of ZMySQLDA ? this patch
https://svn.erp5.org/?view=rev&revision=17868 is important.

Aha - my ZMySQLDA is from rpms and was installed in October, so indeed I
don't have this patch. I'll check with later version of ZMySQLDA.

Bartek

Post by Łukasz Nowak
J?rome
_______________________________________________
Erp5-dev mailing list
Erp5-dev at erp5.org
http://mail.nexedi.com/mailman/listinfo/erp5-dev

[Ivan Tyagov]

Hi,

Post by Łukasz Nowak
Hello,
On 2008-03-25, 09:37:11
(...)

Post by bartek
context.portal_catalog(portal_type='Person', price=123, src__=1)
context.portal_catalog(portal_type='Person', start_date='2001/01/01'
src__=1)
kw = {'portal_type':'Person', 'persondata.start_date':'2001/01/01',
'src__':1}
context.portal_catalog(**kw)

Please try got to:
portal_catalog/erp5_mysql_innodb/manage_propertiesForm
and set/select in 'sql_catalog_datetime_search_keys' as DateTime Search
Key persondata.start_date.

Currently ZSQLCatalog doesn't not EXPLICITLY determine search key used
for querying especially when using a custom catalog table. This means
that in query generation the dates you use are not converted to UTC in
the SQL generation process while in MySLQ they are saved in UTC.
Regards
Ivan