OOPS! I've forgot the files!
Michal, hi.
The new patch works fine with default and other groups.
In attachment you can find EXPLAIN ANALYZE output.
I hope you'll apply it to the CVS. I thought it was in CVS at least 1.5 month
ago.
Igor.
On Tuesday 31 August 2004 17:21, you wrote:
> And assign it to each new account... Not the best idea too.
> But your patch is very useful, so I will modify it a bit to work
> well with default tariffs too and apply it to the cvs.
>
> Did you check the query with EXPLAIN? Is it using indices
> in a correct way, so it is fast?
>
> ----- Original Message -----
> From: "Kompnet" <kompnet@xxxxxxxxx>
> Sent: Tuesday, August 31, 2004 3:11 PM
>
> > I've not checked it, but it (default tariff with no group/selector) is
> > not necessary in my way: you can set 'default' group 1.
> >
> > On Tuesday 31 August 2004 16:52, you wrote:
> > > I've got the idea - it's good. But isn't your patch breaking
> > > tariff matching for default tariffs (with no group/selector)?
> > >
> > > ----- Original Message -----
> > > From: "Kompnet" <kompnet@xxxxxxxxx>
> > > To: <openh323gk-users@xxxxxxxxxxxxxxxxxxxxx>; "Zygmuntowicz Michal"
> > > <m.zygmuntowicz@xxxxxxx> Sent: Tuesday, August 31, 2004 1:51 PM
> > > Subject: Changes in match_tariff function (sqlbill)
> > >
> > > > Hi, Michal.
> > > > I've changed you match_tariff function a little.
> > > > If it's needed to apply this schema:
> > > > the 1-st user must can place calls whit prefix 1234# with common
> > > > price, for the 2-nd user tariffs must be different for different
> > > > destinations, but he use 1234# prefix too,
> > > > and we use your variance of match_tariff, we need to write all
> > > > destinations for both users:
> > > > user1 1234#1 price1
> > > > user2 1234#1 price2
> > > > user1 1234#2 price1
> > > > user2 1234#2 price3
> > > > user1 1234#3 price1
> > > > user2 1234#3 price4
> > > > ...
> > > > In my variance we can to write destinations separately for each user:
> > > > user1 1234# price1
> > > > user2 1234#1 price2
> > > > user2 1234#2 price3
> > > > user2 1234#2 price4
> > > > .....
> > > > --
> > > > Best regards,
> > > > Igor Prokhorov
> >
> > --
> > Best regards,
> > Igor Prokhorov
--
Best regards,
Igor Prokhorov
voipdb=> explain analyze SELECT voiptariffdst.id, voiptariffdst.active, prefix,voiptariffdst.description FROM voiptariffdst, voiptariffgrp, voiptariffsel,voiptariff WHERE voiptariffgrp.id=voiptariffsel.grpid and voiptariffdst.active AND ascii(prefix) = ascii('0022#70967969360') AND ('0022#70967969360' LIKE (prefix || '%')) AND voiptariffdst.id=voiptariff.dstid AND voiptariffgrp.id=voiptariffsel.grpid and voiptariff.grpid=voiptariffsel.grpid and voiptariffsel.accountid=2 ORDER BY priority DESC, length(prefix) DESC;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=61.66..61.66 rows=1 width=73) (actual time=2.125..2.128 rows=2 loops=1)
Sort Key: voiptariffgrp.priority, length(voiptariffdst.prefix)
-> Nested Loop (cost=0.00..61.65 rows=1 width=73) (actual time=0.191..2.093 rows=2 loops=1)
-> Nested Loop (cost=0.00..56.81 rows=1 width=77) (actual time=0.151..2.015 rows=2 loops=1)
-> Nested Loop (cost=0.00..47.13 rows=2 width=73) (actual time=0.119..1.945 rows=2 loops=1)
-> Seq Scan on voiptariffdst (cost=0.00..30.00 rows=1 width=69) (actual time=0.067..1.839 rows=2 loops=1)
Filter: (active AND (ascii(prefix) = 48) AND ('0022#70967969360'::text ~~ (prefix || '%'::text)))
-> Index Scan using voiptariff_dstid_idx on voiptariff (cost=0.00..17.07 rows=5 width=8) (actual time=0.029..0.034 rows=1 loops=2)
Index Cond: ("outer".id = voiptariff.dstid)
-> Index Scan using voiptariffsel_unique on voiptariffsel (cost=0.00..4.83 rows=1 width=4) (actual time=0.017..0.019 rows=1 loops=2)
Index Cond: (("outer".grpid = voiptariffsel.grpid) AND (voiptariffsel.accountid = 2))
-> Index Scan using voiptariffgrp_pkey on voiptariffgrp (cost=0.00..4.82 rows=1 width=8) (actual time=0.013..0.017 rows=1 loops=2)
Index Cond: ("outer".grpid = voiptariffgrp.id)
Total runtime: 2.386 ms
(ЪБРЙУЕК: 14)
diff -uNr ./src/c_functions.sql ./src.new/c_functions.sql
--- ./src/c_functions.sql Thu Sep 30 02:49:00 2004
+++ ./src.new/c_functions.sql Fri Oct 22 10:39:18 2004
@@ -90,10 +90,19 @@
-- find an active destination for the given e164 (longest prefix match)
IF length(e164) > 0 THEN
IF ascii(e164) >= 48 AND ascii(e164) <= 57 THEN
- SELECT INTO dst * FROM voiptariffdst
- WHERE active AND NOT exactmatch AND ascii(prefix) = ascii(e164)
- AND (e164 LIKE (prefix || ''%''))
- ORDER BY length(prefix) DESC
+ SELECT INTO dst voiptariffdst.id, voiptariffdst.active,
+ prefix,voiptariffdst.description
+ FROM voiptariffdst, voiptariffgrp, voiptariffsel,
+ voiptariff
+ WHERE voiptariffgrp.id=voiptariffsel.grpid
+ AND voiptariffdst.active
+ AND ascii(prefix) = ascii(e164)
+ AND (e164 LIKE (prefix || ''%''))
+ AND voiptariffdst.id=voiptariff.dstid
+ AND voiptariffgrp.id=voiptariffsel.grpid
+ AND voiptariff.grpid=voiptariffsel.grpid
+ AND voiptariffsel.accountid=accid
+ ORDER BY priority DESC, length(prefix) DESC
LIMIT 1;
ELSE
SELECT INTO dst * FROM voiptariffdst
