Yes, generally one must normalize the data on import as well. <br><br><div class="gmail_quote">On Fri, Jun 11, 2010 at 10:41 AM, Madovsky <span dir="ltr"><<a href="mailto:infos@madovsky.org">infos@madovsky.org</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div bgcolor="#ffffff">
<div><font size="2">Ha ok thanks. but the problem is usually trunk offer rates
list with country cod and city/route in separated columns.</font></div>
<div><font size="2">so if I understand I need to modify all the lists everytime to
join these to column in one ?</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="rupa@rupa.com" href="mailto:rupa@rupa.com" target="_blank">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" target="_blank">freeswitch-users</a>
</div>
</div><div><div></div><div class="h5"><div style="font:10pt arial"><b>Sent:</b> Friday, June 11, 2010 11:02
AM</div>
<div style="font:10pt arial"><b>Subject:</b> Re: [Freeswitch-users]
mod_lcr</div>
<div><br></div>That doesn't sound right.
<div><br></div>
<div>prefix is for prepending the supplied number with data. So if you
want to prepend the country code if someone just dials npa-nxx for instance.
Usually it would be used to add the customer access code that some
providers like to use.</div>
<div><br></div>
<div>digits should be a full e.164 number without the +. So, US would be
1NPANXX for 1000s resolution. </div>
<div><br></div>
<div>Normalize your numbers to e164 format prior to doing the lcr query.
</div>
<div><br></div>
<div>Using this methodology I can't see how you would have any empty digits
fields.<br><br>
<div class="gmail_quote">On Fri, Jun 11, 2010 at 9:11 AM, Madovsky <span dir="ltr"><<a href="mailto:infos@madovsky.org" target="_blank">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">I use prefix for country prefix and digits for npa-nxx
digits.</font></div>
<div><font size="2">my trunk has a list with empty npa-nxx in some
rows</font></div>
<div><font size="2"></font> </div>
<div><font size="2"></font> </div>
<blockquote style="border-left:#000000 2px solid;padding-left:5px;padding-right:0px;margin-left:5px;margin-right:0px">
<div>
<div style="font:10pt arial">----- Original Message ----- </div>
<div style="font:10pt arial;background:#e4e4e4"><b>From:</b> <a title="rupa@rupa.com" href="mailto:rupa@rupa.com" target="_blank">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" target="_blank">freeswitch-users</a> </div></div>
<div>
<div></div>
<div>
<div style="font:10pt arial"><b>Sent:</b> Friday, June 11, 2010 7:56
AM</div>
<div style="font:10pt arial"><b>Subject:</b> Re: [Freeswitch-users]
mod_lcr</div>
<div><br></div>null would be used for empty, but... why would you have an
empty digits field? <br><br>
<div class="gmail_quote">On Thu, Jun 10, 2010 at 10:21 PM, Madovsky <span dir="ltr"><<a href="mailto:infos@madovsky.org" target="_blank">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">I succeed to make mod_lcr works with prefix and
postgresql 8.4.4</font></div>
<div><font size="2">I have also changed the type of digits as varchar(20)
because pg doesn't</font></div>
<div><font size="2">accept empty numeric field (unless you know a trick to
do that).</font></div>
<div><font size="2"></font> </div>
<div><font size="2">Thanks for your patience</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>
<div style="font:10pt arial">----- Original Message ----- </div>
<div style="font:10pt arial;background:#e4e4e4"><b>From:</b> <a title="rupa@rupa.com" href="mailto:rupa@rupa.com" target="_blank">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" target="_blank">freeswitch-users</a> </div></div>
<div>
<div></div>
<div>
<div style="font:10pt arial"><b>Sent:</b> Thursday, June 10, 2010
4:10 PM</div>
<div style="font:10pt arial"><b>Subject:</b> Re: [Freeswitch-users]
mod_lcr</div>
<div><br></div>
<div>What I use that differs from default:</div>
<div><br></div>
<div>
<div><font face="'courier new', monospace"> digits
| text
| not null</font></div>
<div><span style="font-family:'courier new', monospace"> digits_prefix
| prefix_range | not
null</span></div></div>
<div>
<div>
<div><font face="'courier new', monospace">Indexes:</font></div></div>
<div>
<div><font face="'courier new', monospace">
"idx_prefix" gist (digits_prefix
gist_prefix_range_ops)</font></div></div>
<div><br></div>
<div>I keep digits around so I can test both default behavior
and new behavior. You only really NEED the second one.
</div>
<div><br></div>
<div>On insert I set them to the same value (eg: 12145551212).</div>
<div><br></div>
<div>End is fine. Doesn't really matter.</div>
<div><br>
<div class="gmail_quote">On Thu, Jun 10, 2010 at 12:44 PM, Madovsky
<span dir="ltr"><<a href="mailto:infos@madovsky.org" target="_blank">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 I understand now.</font></div>
<div><font size="2">but where to create digist_prefix in lcr table ?
the end is ok ?</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:#000000 2px solid;padding-left:5px;padding-right:0px;margin-left:5px;margin-right:0px">
<div>
<div style="font:10pt arial">----- Original Message ----- </div>
<div style="font:10pt arial;background:#e4e4e4"><b>From:</b> <a title="rupa@rupa.com" href="mailto:rupa@rupa.com" target="_blank">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" target="_blank">freeswitch-users</a> </div></div>
<div>
<div></div>
<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" target="_blank">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>
<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>
<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>
<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" 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><br clear="all"><br>-- <br>-Rupa<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>
<p></p></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><br clear="all"><br>-- <br>-Rupa<br></div></div></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>
<p></p></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><br clear="all"><br>-- <br>-Rupa<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>
<p></p></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><br clear="all"><br>-- <br>-Rupa<br></div>
</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>