<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=iso-8859-1" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.6001.18904">
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT size=2>Rupa,</FONT></DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV><FONT size=2>I'd like to test PREFIX module form postgresql.</FONT></DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV><FONT size=2>so in your postgresql sql I can see :</FONT></DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV><FONT size=2>CREATE TABLE lcr<BR>(<BR> ...<BR> digits
NUMERIC(20, 0),</FONT></DIV>
<DIV><FONT size=2>....<BR> prefix VARCHAR(16) NOT NULL DEFAULT
'',<BR>...<BR>);</FONT></DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV><FONT size=2>but in your PREFIX postgresql module example it's written
:</FONT></DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV><FONT size=2><PRE>create table rates (id serial not null, prefix prefix_range, [...]);
create index rates_prefix_idx on rates using gist (prefix gist_prefix_range_ops);
insert [...]
select * from rates where prefix @> '16666666666';
</PRE><PRE><FONT size=3 face=Arial>and in the custom example :</FONT></PRE><PRE>...</PRE><PRE><PRE>WHERE c.enabled = '1' AND cg.enabled = '1' AND l.enabled = '1'
AND digits_prefix @> '%q'
....</PRE></PRE></DIV>
<DIV> </DIV>
<DIV>My question is how to uise the lcr table with PREFIX module of postgresql
?</DIV>
<DIV> </DIV>
<DIV>Thaks</DIV>
<DIV><BR>Franck</DIV>
<DIV><BR> </DIV></FONT>
<BLOCKQUOTE
style="BORDER-LEFT: #000000 2px solid; PADDING-LEFT: 5px; PADDING-RIGHT: 0px; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="FONT: 10pt arial; BACKGROUND: #e4e4e4; font-color: black"><B>From:</B>
<A title=rupa@rupa.com href="mailto:rupa@rupa.com">Rupa Schomaker</A> </DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A
title=freeswitch-users@lists.freeswitch.org
href="mailto:freeswitch-users@lists.freeswitch.org">freeswitch-users</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Thursday, June 10, 2010 8:38
AM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> Re: [Freeswitch-users]
mod_lcr</DIV>
<DIV><BR></DIV>Read the part about how to define the table + the gist index.
The whole custom_sql thing assumes a familiarity with sql. you can
choose to not have a digits_prefix column and just change the datatype of
prefix to prefix. You can do what I did which is to have prefix be text
and digits_prefix be of type prefix and a trigger to keep the two in sync.
the key is that you are searching against the prefix column for which
there is a GIST index. <BR><BR>
<DIV class=gmail_quote>On Thu, Jun 10, 2010 at 2:53 AM, Madovsky <SPAN
dir=ltr><<A
href="mailto:infos@madovsky.org">infos@madovsky.org</A>></SPAN> wrote:<BR>
<BLOCKQUOTE
style="BORDER-LEFT: #ccc 1px solid; MARGIN: 0px 0px 0px 0.8ex; PADDING-LEFT: 1ex"
class=gmail_quote>
<DIV bgcolor="#ffffff">
<DIV><FONT size=2>ok thanks I will read again</FONT></DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV><FONT size=2>F</FONT></DIV>
<BLOCKQUOTE
style="BORDER-LEFT: #000000 2px solid; PADDING-LEFT: 5px; PADDING-RIGHT: 0px; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px">
<DIV class=im>
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV style="FONT: 10pt arial; BACKGROUND: #e4e4e4"><B>From:</B> <A
title=gcd@i.ph href="mailto:gcd@i.ph" target=_blank>Nandy Dagondon</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A
title=freeswitch-users@lists.freeswitch.org
href="mailto:freeswitch-users@lists.freeswitch.org"
target=_blank>freeswitch-users@lists.freeswitch.org</A> </DIV></DIV>
<DIV>
<DIV></DIV>
<DIV class=h5>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Thursday, June 10, 2010 3:09
AM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> Re: [Freeswitch-users]
mod_lcr</DIV>
<DIV><BR></DIV>it's the digits_prefix in the WHERE clause that's causing
the error. <BR><BR>ur question re prefix+digits, it's explained in the
Custom SQL portion in the wiki.<BR><BR>-nandy<BR><BR><BR>
<DIV class=gmail_quote>On Thu, Jun 10, 2010 at 2:56 PM, Madovsky <SPAN
dir=ltr><<A href="mailto:infos@madovsky.org"
target=_blank>infos@madovsky.org</A>></SPAN> wrote:<BR>
<BLOCKQUOTE
style="BORDER-LEFT: rgb(204,204,204) 1px solid; MARGIN: 0pt 0pt 0pt 0.8ex; PADDING-LEFT: 1ex"
class=gmail_quote>
<DIV bgcolor="#ffffff">
<DIV><FONT size=2>ok so it needs also the alias l.digits in the
condition I think.</FONT></DIV>
<DIV><FONT size=2>I'm a little confused about digits and
prefix.</FONT></DIV>
<DIV><FONT size=2>if I check a number with the country code is it need
to join</FONT></DIV>
<DIV><FONT size=2>prefix+digits ? how with this kinkd of sql request
?</FONT></DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV><FONT size=2>Thanks</FONT></DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV><FONT size=2>F</FONT></DIV>
<BLOCKQUOTE
style="BORDER-LEFT: rgb(0,0,0) 2px solid; PADDING-LEFT: 5px; PADDING-RIGHT: 0px; MARGIN-LEFT: 5px; MARGIN-RIGHT: 0px">
<DIV>
<DIV></DIV>
<DIV>
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="FONT: 10pt arial; BACKGROUND: rgb(228,228,228)"><B>From:</B> <A
title=gcd@i.ph href="mailto:gcd@i.ph" target=_blank>Nandy Dagondon</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A
title=freeswitch-users@lists.freeswitch.org
href="mailto:freeswitch-users@lists.freeswitch.org"
target=_blank>freeswitch-users@lists.freeswitch.org</A> </DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Thursday, June 10, 2010
2:44 AM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> Re: [Freeswitch-users]
mod_lcr</DIV>
<DIV><BR></DIV>i think it's a typo. i changed digits_prefix to digits.
to be sure, pls check the CREATE TABLE entries. <BR>-nandy<BR><BR><BR>
<DIV class=gmail_quote>On Thu, Jun 10, 2010 at 2:16 PM, Madovsky <SPAN
dir=ltr><<A href="mailto:infos@madovsky.org"
target=_blank>infos@madovsky.org</A>></SPAN> wrote:<BR>
<BLOCKQUOTE
style="BORDER-LEFT: rgb(204,204,204) 1px solid; MARGIN: 0pt 0pt 0pt 0.8ex; PADDING-LEFT: 1ex"
class=gmail_quote>
<DIV bgcolor="#ffffff">
<DIV><FONT size=2>I'm experimenting with mod_lcr with postgresql
(8.4.4)</FONT></DIV>
<DIV><FONT size=2>there s an example of custom sql on wiki below
:</FONT></DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV><PRE> <profile name="use_prefix">
<param name="custom_sql" value="
SELECT l.digits, c.carrier_name, l.${lcr_rate_field}, cg.prefix AS gw_prefix, cg.suffix AS gw_suffix,
l.lead_strip, l.trail_strip, l.prefix, l.suffix, cg.codec, l.cid
FROM lcr l
JOIN carriers c ON l.carrier_id=<A href="http://c.id" target=_blank>c.id</A>
JOIN carrier_gateway cg ON <A href="http://c.id" target=_blank>c.id</A>=cg.carrier_id
WHERE c.enabled = '1' AND cg.enabled = '1' AND l.enabled = '1'
AND digits_prefix @> '%q'
AND CURRENT_TIMESTAMP BETWEEN date_start AND date_end
ORDER BY digits DESC, ${lcr_rate_field} asc, random();
"/>
</profile>
</PRE><PRE><FONT face=Arial>however the query failed cause of digits_prefix field doesn't exist in the table.</FONT></PRE><PRE><FONT face=Arial>is it a typo ? or does it need a field concatenation of prefix and digits ?</FONT></PRE><PRE><FONT face=Arial>Thanks</FONT></PRE><PRE><FONT face=Arial></FONT> </PRE><PRE><FONT face=Arial>Franck</FONT></PRE></DIV></DIV><BR>_______________________________________________<BR>FreeSWITCH-users
mailing list<BR><A
href="mailto:FreeSWITCH-users@lists.freeswitch.org"
target=_blank>FreeSWITCH-users@lists.freeswitch.org</A><BR><A
href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users"
target=_blank>http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</A><BR>UNSUBSCRIBE:<A
href="http://lists.freeswitch.org/mailman/options/freeswitch-users"
target=_blank>http://lists.freeswitch.org/mailman/options/freeswitch-users</A><BR><A
href="http://www.freeswitch.org"
target=_blank>http://www.freeswitch.org</A><BR><BR></BLOCKQUOTE></DIV><BR></DIV></DIV>
<P></P>
<HR>
<DIV>
<P></P>_______________________________________________<BR>FreeSWITCH-users
mailing list<BR><A href="mailto:FreeSWITCH-users@lists.freeswitch.org"
target=_blank>FreeSWITCH-users@lists.freeswitch.org</A><BR><A
href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users"
target=_blank>http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</A><BR>UNSUBSCRIBE:<A
href="http://lists.freeswitch.org/mailman/options/freeswitch-users"
target=_blank>http://lists.freeswitch.org/mailman/options/freeswitch-users</A><BR><A
href="http://www.freeswitch.org"
target=_blank>http://www.freeswitch.org</A><BR></DIV></BLOCKQUOTE></DIV><BR>_______________________________________________<BR>FreeSWITCH-users
mailing list<BR><A href="mailto:FreeSWITCH-users@lists.freeswitch.org"
target=_blank>FreeSWITCH-users@lists.freeswitch.org</A><BR><A
href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users"
target=_blank>http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</A><BR>UNSUBSCRIBE:<A
href="http://lists.freeswitch.org/mailman/options/freeswitch-users"
target=_blank>http://lists.freeswitch.org/mailman/options/freeswitch-users</A><BR><A
href="http://www.freeswitch.org"
target=_blank>http://www.freeswitch.org</A><BR><BR></BLOCKQUOTE></DIV><BR></DIV></DIV>
<P></P>
<HR>
<DIV class=im>
<P></P>_______________________________________________<BR>FreeSWITCH-users
mailing list<BR><A href="mailto:FreeSWITCH-users@lists.freeswitch.org"
target=_blank>FreeSWITCH-users@lists.freeswitch.org</A><BR><A
href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users"
target=_blank>http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</A><BR>UNSUBSCRIBE:<A
href="http://lists.freeswitch.org/mailman/options/freeswitch-users"
target=_blank>http://lists.freeswitch.org/mailman/options/freeswitch-users</A><BR><A
href="http://www.freeswitch.org"
target=_blank>http://www.freeswitch.org</A><BR></DIV>
<P></P></BLOCKQUOTE></DIV><BR>_______________________________________________<BR>FreeSWITCH-users
mailing list<BR><A
href="mailto:FreeSWITCH-users@lists.freeswitch.org">FreeSWITCH-users@lists.freeswitch.org</A><BR><A
href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users"
target=_blank>http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</A><BR>UNSUBSCRIBE:<A
href="http://lists.freeswitch.org/mailman/options/freeswitch-users"
target=_blank>http://lists.freeswitch.org/mailman/options/freeswitch-users</A><BR><A
href="http://www.freeswitch.org"
target=_blank>http://www.freeswitch.org</A><BR><BR></BLOCKQUOTE></DIV><BR><BR
clear=all><BR>-- <BR>-Rupa<BR>
<P>
<HR>
<P></P>_______________________________________________<BR>FreeSWITCH-users
mailing
list<BR>FreeSWITCH-users@lists.freeswitch.org<BR>http://lists.freeswitch.org/mailman/listinfo/freeswitch-users<BR>UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users<BR>http://www.freeswitch.org<BR></BLOCKQUOTE></BODY></HTML>