Nic--<br><br>Before diving into databasing the CEL events, I would suggest that you log all events to a<br>text log file first, and then get a feel for the data you are going to database. Then, after<br>that, I'd map out the queries you are going to need to do to get the information you want.<br>
<br>Originally, I provided the same backends that CDR records have, but the first implementation<br>based on CEL did not use any database, as the queries necessary to pick out the information<br>desired were either impossible or impractically complex. It pulled the data it needed directly<br>
from the event list itself.<br><br>So, I'm interested in how you go about using CEL. I've offered to do a CEL->CDR converter<br>before, but no-one seemed interested in actually having to pay something to get a CDR<br>
based system that actually works in the face of transfers, parking, etc. And, if no-one<br>is willing to pay to get a job done, the job ain't worth doing.<br><br>murf<br><br><br><div class="gmail_quote">On Mon, Dec 28, 2009 at 9:58 AM, Nic Colledge <span dir="ltr"><<a href="mailto:nic@njcolledge.net">nic@njcolledge.net</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div link="blue" vlink="purple" lang="EN-GB">
<div>
<p class="MsoNormal">Hi,</p>
<p class="MsoNormal"> </p>
<p class="MsoNormal">I’ve been playing around with CEL in asterisk trunk
(SVN-Trunk-r236144) and have found some strange behaviour and am wondering if
these are bugs.</p>
<p class="MsoNormal"> </p>
<p class="MsoNormal">I have the CEL table in a postgreSQL database the layout of
which was taken from the cel_pgsql.conf file. I also added a “id”
serial to use a primary key. The SQL create statement is included below.</p>
<p class="MsoNormal"> </p>
<p class="MsoNormal">CREATE TABLE cel</p>
<p class="MsoNormal">(</p>
<p class="MsoNormal"> uniqueid character varying(150) NOT NULL,</p>
<p class="MsoNormal"> linkedid character varying(150) NOT NULL,</p>
<p class="MsoNormal"> eventtime timestamp with time zone NOT NULL,</p>
<p class="MsoNormal"> eventtype integer NOT NULL,</p>
<p class="MsoNormal"> userdeftype character varying(100),</p>
<p class="MsoNormal"> cid_name character varying(100),</p>
<p class="MsoNormal"> cid_num character varying(100),</p>
<p class="MsoNormal"> cid_ani character varying(100),</p>
<p class="MsoNormal"> cid_rdnis character varying(100),</p>
<p class="MsoNormal"> cid_dnid character varying(100),</p>
<p class="MsoNormal"> exten character varying(100),</p>
<p class="MsoNormal"> context character varying(100),</p>
<p class="MsoNormal"> channame character varying(100),</p>
<p class="MsoNormal"> appname character varying(100),</p>
<p class="MsoNormal"> appdata character varying(200),</p>
<p class="MsoNormal"> accountcode character varying(100),</p>
<p class="MsoNormal"> peeraccount character varying(100),</p>
<p class="MsoNormal"> amaflag integer,</p>
<p class="MsoNormal"> userfield character varying(100),</p>
<p class="MsoNormal"> peer character varying(100),</p>
<p class="MsoNormal"> id serial NOT NULL</p>
<p class="MsoNormal">)</p>
<p class="MsoNormal">WITH (</p>
<p class="MsoNormal"> OIDS=FALSE</p>
<p class="MsoNormal">);</p>
<p class="MsoNormal"> </p>
<p class="MsoNormal">Anyway, when I use cel_pgsql.conf to connect to the database
the “id” is always set to zero (which when it were a primary key
caused a violation and made errors to that effect show up in the asterisk
console).</p>
<p class="MsoNormal"> </p>
<p class="MsoNormal">Ok, so not the end of the world, I tried cel_adaptive_odbc.conf.
Here the “id” serial is always set correctly by the database, but the
following errors show up in the asterisk console:</p>
<p class="MsoNormal">[Dec 28 16:30:38] WARNING[1416]: cel_adaptive_odbc.c:574
odbc_log: CEL variable amaflag is not an integer.</p>
<p class="MsoNormal">[Dec 28 16:30:38] WARNING[1416]: cel_adaptive_odbc.c:574
odbc_log: CEL variable id is not an integer.</p>
<p class="MsoNormal"> </p>
<p class="MsoNormal">Any idea what’s going on here? I’m working under
the assumption that amaflag should be an int because the cel_pgsql.conf file
comments say “amaflag (an int)”.</p>
<p class="MsoNormal"> </p>
<p class="MsoNormal">Is there any way to make the pgsql backend ignore the ID (serial)
column so that the database will sort its value out properly?</p>
<p class="MsoNormal">What are these other two errors from the adaptive ODBC
backend?</p>
<p class="MsoNormal"> </p>
<p class="MsoNormal">I think it makes sense to have a primary key on this table,
unless I’m missing something obvious I don’t think one can be made
from the other columns, can it?</p>
<p class="MsoNormal">Maybe it would be better to have asterisk make a extra field
so that uniqueid + [somthing extra] can be used as a primary key? Of course I’m
assuming it’s not possible to use something that already exists when I
suggest this. </p>
<p class="MsoNormal"> </p>
<p class="MsoNormal">Thanks in advance.</p>
<p class="MsoNormal"> </p>
<p class="MsoNormal">Regards,</p>
<p class="MsoNormal">Nic Colledge</p>
<p class="MsoNormal"> </p>
</div>
</div>
<br>_______________________________________________<br>
--Bandwidth and Colocation Provided by <a href="http://www.api-digital.com--" target="_blank">http://www.api-digital.com--</a><br>
<br>
asterisk-dev mailing list<br>
To UNSUBSCRIBE or update options visit:<br>
<a href="http://lists.digium.com/mailman/listinfo/asterisk-dev" target="_blank">http://lists.digium.com/mailman/listinfo/asterisk-dev</a><br></blockquote></div><br><br clear="all"><br>-- <br>Steve Murphy<br>ParseTree Corp<br>
<br>