Custom fields as dates patch

Hi RT devs,

One of my customers requested the ability to have custom fields with
date values. After some googling around I started with the patch at
http://issues.bestpractical.com/SelfService/Display.html?id=8721

There was a minor hiccup with the patch vs. current 3.8.8 but it was
trivial to manually resolve.

The results are working well in terms of editing and showing the fields,
but I’m having some difficulty with searching. Specifically:

  1. I’m able to add the field to the search criteria, and the selection
    seems to work fine in that it has a before/on/after dropdown and date
    selection. It adds a portion to the query that looks like
    "CF.{field name} < ‘2009-07-01 12:07’". However, it doesn’t
    seem to actually take effect with regard to the search results. Tickets
    with empty values and dates after the value are still shown.

  2. Once I’ve added the custom field to the search, clicking on “Edit
    Search” brings me back to the “Advanced” page with the two textareas
    instead of the “Query Builder” page I would expect.

I don’t want to duplicate work that’s already been done, so before I
jump in and start fixing things, just wondering if anyone else has
worked on this already.

I see the patch is slated for 3.8.9
(http://issues.bestpractical.com/SelfService/Display.html?id=14582); has
it been incorporated in git and the search issues sorted out?

If no one has this working already, I’ll of course contribute a patch to
the patch once I have a fix.

Ivan Kohler
Open-source billing, ticketing and provisioning
for ISPs, VoIP providers and online businesses
http://www.freeside.biz/freeside/

I see the patch is slated for 3.8.9
(http://issues.bestpractical.com/SelfService/Display.html?id=14582); has
it been incorporated in git and the search issues sorted out?

It is not slated for 3.8.9

Being a child of the 3.8.9 ticket just means it should be triaged
before then, not that we won’t release 3.8.9 without it.

As you’ve noted already, it does not work well enough to be merged
as-is

-kevin

Being a child of the 3.8.9 ticket just means it should be triaged
before then, not that we won’t release 3.8.9 without it.

Apologies for my misunderstanding.

As you’ve noted already, it does not work well enough to be merged
as-is

Once I dug into things, it wasn’t difficult to fix the search issues I
had pointed out. I also updated the patch to apply cleanly against
3.8.8. It is attached to this mail and ticket #8721.

Seems to work well enough now AFAICT. :slight_smile:

If there are any additional changes Best Practical would like in order
to merge this for 3.8.9 or 3.10.0, please feel free to ask and I’ll see
what I can do.

Ivan Kohler
Open-source billing, ticketing and provisioning
for ISPs, VoIP providers and online businesses
http://www.freeside.biz/freeside/

RT_Date_CustomField-3.8.8.patch (15.5 KB)

Hi there,

as I see at github, sunnavy created a branche to integrate date type
customfields into trunk.
I really appreciate that. :slight_smile:

Maybe it is worth also to integrate Ruslan’s
RT-Extension-CustomField-Checkbox into trunk?

Thanks!
ChrisAm 21.07.2010 04:50, schrieb Ivan Kohler:

On Tue, Jul 20, 2010 at 02:27:27PM -0500, Kevin Falcone wrote:

Being a child of the 3.8.9 ticket just means it should be triaged
before then, not that we won’t release 3.8.9 without it.

Apologies for my misunderstanding.

As you’ve noted already, it does not work well enough to be merged
as-is

Once I dug into things, it wasn’t difficult to fix the search issues I
had pointed out. I also updated the patch to apply cleanly against
3.8.8. It is attached to this mail and ticket #8721.

Seems to work well enough now AFAICT. :slight_smile:

If there are any additional changes Best Practical would like in order
to merge this for 3.8.9 or 3.10.0, please feel free to ask and I’ll see
what I can do.

Ivan Kohler wrote:

Seems to work well enough now AFAICT. :slight_smile:

I have found one problem. If I add a date CF and apply it to a queue
(General), that works OK. If I then fill in this CF thats also OK. But
if I goto Tickets (Query Builder) and select the General queue I don’t
get the ‘Calendar’ link besides my custom Date CF.
Looking at it with Firebug reveals that the code is generated but the '
are escaped as '
This isn’t the case for the dates generated by PickBasics but somehow it
is for PickCFs. The why of it eludes me.

Regards,

Joop

Hi

the problem is not because ’ are escaped to ’ in html,
but because they are not escaped to \’ before that.

fixed in cf-date branch

thanks for reporting this

best wishes
sunnavyOn 10-07-23 12:41, Joop wrote:

Ivan Kohler wrote:

Seems to work well enough now AFAICT. :slight_smile:

I have found one problem. If I add a date CF and apply it to a queue
(General), that works OK. If I then fill in this CF thats also OK.
But if I goto Tickets (Query Builder) and select the General queue I
don’t get the ‘Calendar’ link besides my custom Date CF.
Looking at it with Firebug reveals that the code is generated but
the ’ are escaped as '
This isn’t the case for the dates generated by PickBasics but
somehow it is for PickCFs. The why of it eludes me.

Regards,

Joop


List info: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel

Ivan Kohler wrote:

Seems to work well enough now AFAICT. :slight_smile:
Found one more problem.
I have entered a date through the Calendar popup (2010-07-23) and saved it.
Then I tried to search for it and wasn’t able to find the ticket back.
Looking at the database I discovered why.
It is stored in the database (ObjectCustomFieldValues) as 2010-07-22
22:00:00
The datepicker picks the date in in my Timezone (GMT+1, +1 for DST =+2)
but its stored in the database as GMT. That is not the problem in itself
but the date entered in the Query builder is NOT converted to GMT and so
never finds the ticket.

Regards,

Joop

Did you use latest cf-date branch of rt?On 10-07-26 11:49, Joop wrote:

Ivan Kohler wrote:

Seems to work well enough now AFAICT. :slight_smile:
Found one more problem.
I have entered a date through the Calendar popup (2010-07-23) and saved it.
Then I tried to search for it and wasn’t able to find the ticket
back. Looking at the database I discovered why.
It is stored in the database (ObjectCustomFieldValues) as 2010-07-22
22:00:00
The datepicker picks the date in in my Timezone (GMT+1, +1 for DST
=+2) but its stored in the database as GMT. That is not the problem
in itself but the date entered in the Query builder is NOT converted
to GMT and so never finds the ticket.

Regards,

Joop


List info: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel

the most important difference is in Tickets_Overlay.pm, the patch in rt-devel
actually doesn’t work.On 10-07-26 14:57, Joop wrote:

sunnavy wrote:

Did you use latest cf-date branch of rt?
Sorry, no I didn’t. I applied the code from your patch to get the
calender to show up but didn’t use the whole patch or branch.
I had a look at it I didn’t spot anything obviously different from
the patch send to the rt-devel, but time permitting I’ll checkout
the cf-date branch and retry.

Regards,

Joop

the most important difference is in Tickets_Overlay.pm, the patch in rt-devel
actually doesn’t work.

I sort of wonder whether we want Date custom fields and Date + Time custom fields as separate types. Date Fields wouldn’t be sensitive to timezones, nor would they ahve sub-day granularity.

-j

Joop wrote:

Found one more problem.
I have entered a date through the Calendar popup (2010-07-23) and
saved it.
Then I tried to search for it and wasn’t able to find the ticket back.
Looking at the database I discovered why.
It is stored in the database (ObjectCustomFieldValues) as 2010-07-22
22:00:00
The datepicker picks the date in in my Timezone (GMT+1, +1 for DST
=+2) but its stored in the database as GMT. That is not the problem in
itself but the date entered in the Query builder is NOT converted to
GMT and so never finds the ticket.
Using cf-date branch is I what should have done instead of manually
patching HEAD with a single commit from cf-date. Using branch cf-date
did result in a correct working search with a date CF but only when
looking for ON not with BEFORE or AFTER

The reason that fails is because of the queries being build. The ON
query looks like:
SELECT COUNT (DISTINCT main.id)
FROM Tickets main
JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_1
ON (ObjectCustomFieldValues_1.CustomField = ‘327’)
AND (ObjectCustomFieldValues_1.ObjectType = ‘RT::Ticket’)
AND (ObjectCustomFieldValues_1.Disabled = ‘0’)
AND (ObjectCustomFieldValues_1.ObjectId = main.id)
WHERE (main.Status != ‘deleted’)
AND (main.Queue = '13’
AND ( ( ( (ObjectCustomFieldValues_1.Content >=
'2010-07-25 22:00:00’
AND ObjectCustomFieldValues_1.Content <=
‘2010-07-26 22:00:00’)))))
AND (main.TYPE = ‘ticket’)
AND (main.EffectiveId = main.id)

And the BEFORE/AFTER looks like:
SELECT main.*
FROM Tickets main
JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_1
ON (ObjectCustomFieldValues_1.CustomField = ‘327’)
AND (ObjectCustomFieldValues_1.ObjectType = ‘RT::Ticket’)
AND (ObjectCustomFieldValues_1.Disabled = ‘0’)
AND (ObjectCustomFieldValues_1.ObjectId = main.id)
WHERE (main.Status != ‘deleted’)
AND (main.Queue = '13’
AND ( ( (ObjectCustomFieldValues_1.Content < '2010-07-29’
OR ( (ObjectCustomFieldValues_1.Content = ''
OR ObjectCustomFieldValues_1.Content IS NULL)
– AND ObjectCustomFieldValues_1.LargeContent <
– ‘2010-07-29’
)))))
AND (main.TYPE = ‘ticket’)
AND (main.EffectiveId = main.id)

This query works and returns the expected result because of the two
commented lines. If I run the original query it doesn’t since Oracle
doesn’t like comparing CLOB columns like LargeContent against a constant.

I don’t know why LargeContent is used only in BEFORE/AFTER queries and
not with ON. Solutions seems to be to not use LargeContent but don’t
know the ramifications of that.

Regards,

Joop

Hi Joop

I just fixed “before” and “after” in cf-date too,
could you run tests in t/api/cf_date_search.t and t/web/cf_date.t and
see if all of them can pass?

thanks!On 10-07-27 12:56, Joop wrote:

Joop wrote:

Found one more problem.
I have entered a date through the Calendar popup (2010-07-23) and
saved it.
Then I tried to search for it and wasn’t able to find the ticket
back. Looking at the database I discovered why.
It is stored in the database (ObjectCustomFieldValues) as
2010-07-22 22:00:00
The datepicker picks the date in in my Timezone (GMT+1, +1 for DST
=+2) but its stored in the database as GMT. That is not the
problem in itself but the date entered in the Query builder is NOT
converted to GMT and so never finds the ticket.
Using cf-date branch is I what should have done instead of manually
patching HEAD with a single commit from cf-date. Using branch
cf-date did result in a correct working search with a date CF but
only when looking for ON not with BEFORE or AFTER

The reason that fails is because of the queries being build. The ON
query looks like:
SELECT COUNT (DISTINCT main.id)
FROM Tickets main
JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_1
ON (ObjectCustomFieldValues_1.CustomField = ‘327’)
AND (ObjectCustomFieldValues_1.ObjectType = ‘RT::Ticket’)
AND (ObjectCustomFieldValues_1.Disabled = ‘0’)
AND (ObjectCustomFieldValues_1.ObjectId = main.id)
WHERE (main.Status != ‘deleted’)
AND (main.Queue = '13’
AND ( ( ( (ObjectCustomFieldValues_1.Content >=
'2010-07-25 22:00:00’
AND ObjectCustomFieldValues_1.Content <=
‘2010-07-26 22:00:00’)))))
AND (main.TYPE = ‘ticket’)
AND (main.EffectiveId = main.id)

And the BEFORE/AFTER looks like:
SELECT main.*
FROM Tickets main
JOIN
ObjectCustomFieldValues ObjectCustomFieldValues_1
ON (ObjectCustomFieldValues_1.CustomField = ‘327’)
AND (ObjectCustomFieldValues_1.ObjectType = ‘RT::Ticket’)
AND (ObjectCustomFieldValues_1.Disabled = ‘0’)
AND (ObjectCustomFieldValues_1.ObjectId = main.id)
WHERE (main.Status != ‘deleted’)
AND (main.Queue = '13’
AND ( ( (ObjectCustomFieldValues_1.Content < '2010-07-29’
OR ( (ObjectCustomFieldValues_1.Content = ''
OR ObjectCustomFieldValues_1.Content IS NULL)
– AND ObjectCustomFieldValues_1.LargeContent <
– ‘2010-07-29’
)))))
AND (main.TYPE = ‘ticket’)
AND (main.EffectiveId = main.id)

This query works and returns the expected result because of the two
commented lines. If I run the original query it doesn’t since Oracle
doesn’t like comparing CLOB columns like LargeContent against a
constant.

I don’t know why LargeContent is used only in BEFORE/AFTER queries
and not with ON. Solutions seems to be to not use LargeContent but
don’t know the ramifications of that.

Regards,

Joop


List info: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel

if date fields(without time) are not sensitive to timezones, which timezone
will it be actually in then, system’s timezone?
We need this so we can find out what users really mean.
( sometimes, I hate that the earth is global :wink:

If so, we have to assume when users input a date field, it is in system or some
fixed timezone, but when they input a datetime field, it’s in their own timezone,
kinda confusing.

best wishes
sunnavyOn 10-07-26 10:52, Jesse Vincent wrote:

On Mon, Jul 26, 2010 at 09:13:34PM +0800, sunnavy wrote:

the most important difference is in Tickets_Overlay.pm, the patch in rt-devel
actually doesn’t work.

I sort of wonder whether we want Date custom fields and Date + Time custom fields as separate types. Date Fields wouldn’t be sensitive to timezones, nor would they ahve sub-day granularity.

-j


List info: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel

I’m glad that it works for you now.

the test failure is indeed weird though, how is it for other tests below t/web?
e.g. how is t/web/rights.t?
Most of them use the same $m->login stuff, so normally, they should all pass or
none passes at all.

best wishes
sunnavyOn 10-07-28 10:58, Joop wrote:

sunnavy wrote:

Hi Joop

I just fixed “before” and “after” in cf-date too,
could you run tests in t/api/cf_date_search.t and t/web/cf_date.t and
see if all of them can pass?

Test t/api/cf_date_search.t all pass
Test t/web/cf_date.t does not pass, in fact it errors out rather soon.
t/web/cf_date.t …
1…46
You can connect to your server at http://127.0.0.1:2296/
ok 1 - started server
not ok 2 - logged in as root

error: page has no Logout

Failed test ‘logged in as root’

at t/web/cf_date.t line 10.

ok 3 - load root user
not ok 4 - admin screen
not ok 5 - admin-cf screen

Create a CF

Failed test ‘admin screen’

at t/web/cf_date.t line 20.

got: undef

expected: “RT Administration”

Failed test ‘admin-cf screen’

at t/web/cf_date.t line 22.

got: undef

expected: “Select a Custom Field”

There is no form named “ModifyCustomField” at
/usr/lib/perl5/site_perl/5.8.8/WWW/Mechanize.pm line 1867,
line 16.
There is no form named “ModifyCustomField” at
/usr/lib/perl5/site_perl/5.8.8/WWW/Mechanize.pm line 1867,
line 16.

Some tests failed or we bailed out, tmp directory

‘/home/rt/rt/t/tmp/Pks5xJL36g’ is not cleaned

Looks like you planned 46 tests but ran 5.

Looks like you failed 3 tests of 5 run.

Looks like your test exited with 255 just after 5.

Dubious, test returned 255 (wstat 65280, 0xff00)
Failed 44/46 subtests

Test Summary Report

t/web/cf_date.t (Wstat: 65280 Tests: 5 Failed: 3)
Failed tests: 2, 4-5
Non-zero exit status: 255
Parse errors: Bad plan. You planned 46 tests but ran 5.
Files=1, Tests=5, 12 wallclock secs ( 0.03 usr 0.02 sys + 3.35
cusr 0.89 csys = 4.29 CPU)
Result: FAIL
Failed 1/1 test programs. 3/5 subtests failed.

I had a look at the test file but can’t find the problem. Is it
because it is run as the only test and not from ‘make test’ ?
What am I doing wrong.

The tests that I have done myself are all OK. No more errors in
var/rt.log and searching returns the correct results.

Regards,

Joop