[asterisk-bugs] [Asterisk 0012947]: [patch] Column names causes

[noreply at bugs.digium.com]

The following issue has been ASSIGNED. 
====================================================================== 
http://bugs.digium.com/view.php?id=12947 
====================================================================== 
Reported By:                panolex
Assigned To:                seanbright
====================================================================== 
Project:                    Asterisk
Issue ID:                   12947
Category:                   CDR/cdr_pgsql
Reproducibility:            always
Severity:                   crash
Priority:                   normal
Status:                     assigned
Asterisk Version:           1.6.0-beta9 
SVN Branch (only for SVN checkouts, not tarball releases): N/A 
SVN Revision (number only!):  
Disclaimer on File?:        N/A 
Request Review:              
====================================================================== 
Date Submitted:             06-29-2008 04:55 CDT
Last Modified:              06-29-2008 06:59 CDT
====================================================================== 
Summary:                    [patch] Column names causes
Description: 
Names "start" and "end" is reserwed keywords in postgresql and other
sql-compliant databases.

When using following cdr table scheme:

CREATE TABLE cdr (
    id bigint NOT NULL,
    accountcode character varying(20) NOT NULL,
    src character varying(80) DEFAULT ''::character varying NOT NULL,
    dst character varying(80) DEFAULT ''::character varying NOT NULL,
    dcontext character varying(80) DEFAULT ''::character varying NOT
NULL,
    clid character varying(80) DEFAULT ''::character varying NOT NULL,
    channel character varying(80) DEFAULT ''::character varying NOT NULL,
    dstchannel character varying(80) DEFAULT ''::character varying NOT
NULL,
    lastapp character varying(80) DEFAULT ''::character varying NOT NULL,
    lastdata character varying(80) DEFAULT ''::character varying NOT
NULL,
    start timestamp with time zone DEFAULT now() NOT NULL,
    answer timestamp with time zone,
    end timestamp with time zone,
    duration integer DEFAULT 0,
    billsec integer DEFAULT 0,
    disposition disposition_t,
    amaflags character varying(80) DEFAULT ''::character varying,
    uniqueid character varying(32) DEFAULT ''::character varying,
    userfield character varying(255)
);

, there will be an error inserting into cdr table:

[Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:297 pgsql_log: cdr_pgsql:
Failed to insert call detail record into database!
[Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:298 pgsql_log: cdr_pgsql:
Reason: ERROR:  syntax error at or near "end"
LINE 1:
...ntcode,src,dst,dcontext,clid,channel,start,answer,end,durati...
                                                             ^

[Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:299 pgsql_log: cdr_pgsql:
Connection may have been lost... attempting to reconnect.
[Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:302 pgsql_log: cdr_pgsql:
Connection reestablished.
[Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:308 pgsql_log: cdr_pgsql: HARD
ERROR!  Attempted reconnection failed.  DROPPING CALL RECORD!
[Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:309 pgsql_log: cdr_pgsql:
Reason: ERROR:  syntax error at or near "end"
LINE 1:
...ntcode,src,dst,dcontext,clid,channel,start,answer,end,durati...
                                                             ^

[Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:297 pgsql_log: cdr_pgsql:
Failed to insert call detail record into database!
[Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:298 pgsql_log: cdr_pgsql:
Reason: ERROR:  syntax error at or near "end"
LINE 1:
...,channel,dstchannel,lastapp,lastdata,start,answer,end,durati...
                                                             ^

[Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:299 pgsql_log: cdr_pgsql:
Connection may have been lost... attempting to reconnect.
[Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:302 pgsql_log: cdr_pgsql:
Connection reestablished.
[Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:308 pgsql_log: cdr_pgsql: HARD
ERROR!  Attempted reconnection failed.  DROPPING CALL RECORD!
[Jun 29 11:20:12] ERROR[8297]: cdr_pgsql.c:309 pgsql_log: cdr_pgsql:
Reason: ERROR:  syntax error at or near "end"
LINE 1:
...,channel,dstchannel,lastapp,lastdata,start,answer,end,durati...

, proposed to rename column "end" to "callend" , "start" to "callstart",
and for better naming "answer" to "callanswer".

====================================================================== 

---------------------------------------------------------------------- 
 svnbot - 06-29-08 06:59  
---------------------------------------------------------------------- 
Repository: asterisk
Revision: 126274

U   trunk/cdr/cdr_pgsql.c

------------------------------------------------------------------------
r126274 | seanbright | 2008-06-29 06:59:35 -0500 (Sun, 29 Jun 2008) | 6
lines

Quote column names when inserting CDRs into postgres to avoid conflicts
with reserved words.

(closes issue http://bugs.digium.com/view.php?id=12947)
Reported by: panolex

------------------------------------------------------------------------

http://svn.digium.com/view/asterisk?view=rev&revision=126274 

Issue History 
Date Modified   Username       Field                    Change               
====================================================================== 
06-29-08 06:59  svnbot         Note Added: 0089413                          
06-29-08 06:59  svnbot         Status                   new => assigned     
06-29-08 06:59  svnbot         Assigned To               => seanbright      
======================================================================