Bug 166662 - www/asterisk-stat - pgsql select substring() problem
www/asterisk-stat - pgsql select substring() problem
Status: Closed FIXED
Product: Ports & Packages
Classification: Unclassified
Component: Individual Port(s)
Latest
Any Any
: Normal Affects Only Me
Assigned To: scheidell
:
Depends on:
Blocks:
  Show dependency treegraph
 
Reported: 2012-04-05 09:20 UTC by vess
Modified: 2012-06-09 12:20 UTC (History)
0 users

See Also:


Attachments
file.diff (606 bytes, patch)
2012-04-05 09:20 UTC, vess
no flags Details | Diff
www_askrisk-stat.patch (1.62 KB, patch)
2012-05-26 12:57 UTC, scheidell
no flags Details | Diff

Note You need to log in before you can comment on or make changes to this bug.
Description vess 2012-04-05 09:20:08 UTC
PG_VERSION = 9.0

While using statustics page for 'cdr report', 'RED TOTAL BAR TABLE' at the bottom not displayed. 

Here is part of  /var/log/message:

Apr  5 10:22:37 server postgres[34881]: [2-1] ERROR:  function pg_catalog.substring(timestamp with time zone, integer, integer) does not exist at character 8
Apr  5 10:22:37 server postgres[34881]: [2-2] HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
Apr  5 10:22:37 server postgres[34881]: [2-3] STATEMENT:  SELECT substring(calldate,1,10) AS day, sum(duration) AS calltime, count(*) as nbcall FROM cdr WHERE (calldate) >= ('2012-01-01') AND (calldate) <= ('2012-03-31 23:59:59') GROUP BY substring(calldate,1,10)

Fix: patch file attached

Patch attached with submission follows:
How-To-Repeat: http://127.0.0.1/asterisk-stat/cdr.php?s=1

Choose any period and click search.
Comment 1 vess 2012-04-05 09:55:28 UTC
Even better - optimized query with order:

$QUERY = "SELECT cast(calldate as date) AS day, sum(duration) AS
calltime, count(*) as nbcall FROM cdr WHERE ".$FG_TABLE_CLAUSE." GROUP
BY cast(calldate as date) order by 1 desc"; //extract(DAY from calldate)
Comment 2 Mark Linimon freebsd_committer 2012-04-05 09:56:54 UTC
Responsible Changed
From-To: freebsd-www->freebsd-ports-bugs

ports PR.  The 'www' category is for problems with the FreeBSD.org 
website.
Comment 3 scheidell freebsd_committer 2012-05-01 21:51:53 UTC
Maintainer:
I am assuming you didn't get a copy of this, due to the original 
submitters mistaken category.

Please check this patch, and decide if you want to approve it.
I would suggest a PORTREVISION bump (at least), and if this patch is 
needed, send it upstream?

-- 
Michael Scheidell, CTO
 >*| * SECNAP Network Security Corporation
d: +1.561.948.2259
w: http://people.freebsd.org/~scheidell
Comment 4 scheidell freebsd_committer 2012-05-26 12:39:26 UTC
Responsible Changed
From-To: freebsd-ports-bugs->scheidell

I'll take it. Maintainer feedback timeout.
Comment 5 scheidell freebsd_committer 2012-05-26 12:57:04 UTC
For audit, could everyone please test the attached patch? 

(curl 'http://www.freebsd.org/cgi/query-pr.cgi?pr=ports%2F166662&getpatch=2' | patch -EuNI)

Veselin: to avoid confusion, this appears to apply your second QUERY.
jfkimura:  do you approve this patch?

(note, I did bump PORTREVISION, this will force a rebuild for mysql as well as pgsql users, but it looks like this is the 
safest thing to do)

-- 
Michael Scheidell, CTO
SECNAP Network Security Corporation
http://people.freebsd.org/~scheidell

______________________________________________________________________
This email has been scanned and certified safe by SpammerTrap(r). 
For Information please see http://www.spammertrap.com/
______________________________________________________________________  
  
Comment 6 scheidell freebsd_committer 2012-05-27 11:13:40 UTC
State Changed
From-To: open->feedback

Ask for maintainer approval.
Comment 7 scheidell freebsd_committer 2012-06-07 23:00:42 UTC
State Changed
From-To: feedback->closed

No response. Don't know what submitter or maintainer wants to do. 
Closeing pr.
Comment 8 dfilter freebsd_committer 2012-06-09 12:11:00 UTC
scheidell    2012-06-09 11:10:50 UTC

  FreeBSD ports repository

  Modified files:
    www/asterisk-stat    Makefile 
  Added files:
    www/asterisk-stat/files pgsql__patch-call.log.php 
  Log:
  - Fix pgsql select substring() problem
  - pet portlint (gd.4 to gd.[4-9] in LIB_DEPENDS)
  - Bump PORTREVISION
  
  PR:             ports/166662
  Submitted by:   Veselin Slavov <vess@slavof.net>
  Reviewed by:    scheidell@ (me)
  Approved by:    F. Kimura <jfkimura@yahoo.co.jp> (maintainer)
  
  Revision  Changes    Path
  1.15      +3 -2      ports/www/asterisk-stat/Makefile
  1.1       +9 -0      ports/www/asterisk-stat/files/pgsql__patch-call.log.php (new)
_______________________________________________
cvs-all@freebsd.org mailing list
http://lists.freebsd.org/mailman/listinfo/cvs-all
To unsubscribe, send any mail to "cvs-all-unsubscribe@freebsd.org"
Comment 9 scheidell freebsd_committer 2012-06-09 12:11:22 UTC
-------- Original Message --------
Subject: 	Re: ports/166662: www/asterisk-stat - pgsql select substring() 
problem
Date: 	Sat, 9 Jun 2012 16:04:48 +0900
From: 	F. Kimura <jfkimura@yahoo.co.jp>
Reply-To: 	F. Kimura <jfkimura@yahoo.co.jp>
To: 	scheidell@FreeBSD.org <scheidell@FreeBSD.org>, vess@slavof.net 
<vess@slavof.net>



I was not using e-mail for a month. Sorry. If possible, I want to accept this PR.

// Fumihiko Kimura