Mailing List Archive: Request Tracker: Devel

Custom fields as dates patch

Index | Next | Previous | View Threaded

ivan-rt-devel at 420

Jul 20, 2010, 12:11 PM

Post #1 of 14 (1949 views)
Permalink

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/
_______________________________________________
List info: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel

falcone at bestpractical

Jul 20, 2010, 12:27 PM

Post #2 of 14 (1908 views)
Permalink

Re: Custom fields as dates patch [In reply to]

On Tue, Jul 20, 2010 at 12:11:40PM -0700, Ivan Kohler wrote:
> 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

ivan-rt-devel at 420

Jul 20, 2010, 7:50 PM

Post #3 of 14 (1908 views)
Permalink

Re: Custom fields as dates patch [In reply to]

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. :)

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/

Attachments:

RT_Date_CustomField-3.8.8.patch (15.5 KB)

cloos at netcologne

Jul 22, 2010, 3:06 AM

Post #4 of 14 (1893 views)
Permalink

Re: Custom fields as dates patch [In reply to]

Hi there,

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

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

Thanks!
Chris

Am 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. :)
>
> 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.
_______________________________________________
List info: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel

JoopvandeWege at mococo

Jul 23, 2010, 3:41 AM

Post #5 of 14 (1872 views)
Permalink

Re: Custom fields as dates patch [In reply to]

Ivan Kohler wrote:
> Seems to work well enough now AFAICT. :)

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

sunnavy at bestpractical

Jul 23, 2010, 5:11 PM

Post #6 of 14 (1871 views)
Permalink

Re: Custom fields as dates patch [In reply to]

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
sunnavy

On 10-07-23 12:41, Joop wrote:
> Ivan Kohler wrote:
> >Seems to work well enough now AFAICT. :)
>
> 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
_______________________________________________
List info: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel

JoopvandeWege at mococo

Jul 26, 2010, 2:49 AM

Post #7 of 14 (1815 views)
Permalink

Re: Custom fields as dates patch [In reply to]

Ivan Kohler wrote:
> Seems to work well enough now AFAICT. :)
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

sunnavy at bestpractical

Jul 26, 2010, 5:45 AM

Post #8 of 14 (1820 views)
Permalink

Re: Custom fields as dates patch [In reply to]

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. :)
> 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
_______________________________________________
List info: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel

sunnavy at bestpractical

Jul 26, 2010, 6:13 AM

Post #9 of 14 (1820 views)
Permalink

Re: Custom fields as dates patch [In reply to]

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
>
>
_______________________________________________
List info: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel

jesse at bestpractical

Jul 26, 2010, 7:52 AM

Post #10 of 14 (1815 views)
Permalink

Re: Custom fields as dates patch [In reply to]

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

JoopvandeWege at mococo

Jul 27, 2010, 3:56 AM

Post #11 of 14 (1802 views)
Permalink

Re: Custom fields as dates patch [In reply to]

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

sunnavy at bestpractical

Jul 27, 2010, 6:30 PM

Post #12 of 14 (1766 views)
Permalink

Re: Custom fields as dates patch [In reply to]

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
_______________________________________________
List info: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel

sunnavy at bestpractical

Jul 27, 2010, 9:32 PM

Post #13 of 14 (1770 views)
Permalink

Re: Custom fields as dates patch [In reply to]

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 ;)

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
sunnavy

On 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
_______________________________________________
List info: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel

sunnavy at bestpractical

Jul 28, 2010, 2:14 AM

Post #14 of 14 (1767 views)
Permalink

Re: Custom fields as dates patch [In reply to]

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
sunnavy

On 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, <DATA>
> line 16.
> There is no form named "ModifyCustomField" at
> /usr/lib/perl5/site_perl/5.8.8/WWW/Mechanize.pm line 1867, <DATA>
> 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
>
_______________________________________________
List info: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads