solution, the billing operator can create security holes. Without the longest
matching prefix rule applied always, blocking some destinations will not work.
Example, I have a premium numbers +4870.... I don't want to route these numbers.
I put this destination with active flag disabled. This way all 4870 numbers are blocked.
With your group priorities, a billing operator can create a +48 tariff with high priority
and blocked destinations will be no longer blocked.
But maybe we can think of some other solution that will not create any security implications
and it will be flexible enough.
2) Imagine someone does not use groups (or use it for other purpose). He bills his customers
with different currencies, let's say EUR and USD. So for each destination, it has two tariffs,
one with EUR specified as currency and the other with USD. How can the billing choose
then the right tarifff without checking the currency?
----- Original Message ----- From: "Kompnet" <kompnet@xxxxxxxxx>
Sent: Tuesday, October 26, 2004 4:12 PM
Michal, we can't to understand one another.
1) Simple example:
You have user2 who buys all the world under tariff1 (tariff table with diff
tariffs fo diff destinations) and the tariff on Russia is the single blanket
tariff for him (the raw in tariff1 table).
And you have user1 who buys all the world under tariff1, but different dests
in Russia with diff tariffs.
If to follow your logic you should to specify in details tariffs for Russia
for the user2 as well as for the user1. You know even not large country can
have hundreds of preffixes with different tariffs. And all these hundreds of
preffixes you need to wright into tariff tables for all users ("Tariff
matching should always find the longest matching prefix..."). And then you
must to select tariffdst.id from LARGE tariffdst table.
In this case it is incomprehensible why did you inserted matching with groups
<-> priority in you query at all.
If to follow my logic you can create table tariff1 and add to it ONLY detail
tariffs to Russia and specify tariff to Russia for user2 with priority 2,
tariffs to other countries with priority 1 (or no priority at all - it will
be 'default group') and detail tariffs to Russia for user1 with priority 1
(or no priority). And you must to specify user2 as a member of group 2
(priority 2) and of group 1 (or no group) with priority 1 (or no priority);
and to specify user1 as a member of group 1 (or no group) only.
2) I use currencies. But ONE user for ONE destination must to have only ONE
currency. I can't understand how can it be: user1 for destination 49 for
example has tariff 0.01USD and 0.015 EU at the same time.
I can to imagine the other situation: for dest 49 the tariff is equal to
0.01USD and for dest 48 - to 0.02 EU. In this case one can use different
groups for different currencies.
On Tuesday 26 October 2004 14:06, Zygmuntowicz Michal wrote:Tariff matching should always find the longest matching prefix, we should make no exception to this rule whether groups are used or no. Group priorities should be compared after the longest prefix match is found, to resolve conflicts among multiple groups and the same prefix.
Regarding currency: If you don't use currency or you can deduct currency
based
on group tariff membership that's your particular case. Others use currency
and need
this feature. And don't use groups. But as you noted, the query could be
optimized
by moving somehow currency filter to the last query planner step. I'll play
with the query
to optimize it.
----- Original Message ----- From: "Kompnet" <kompnet@xxxxxxxxx> Sent: Tuesday, October 26, 2004 9:04 AM
> Hi, Michal.
> I wuldn't be happy :-((.
> In your query order of ORDERs changed, and as result not the tariff > with
> highest priority matches but with longest preffix.
> Must be "....ORDER BY voiptariffgrp.priority DESC,
> length(voiptariffdst.prefix) DESC LIMIT 1;"
> or in your terms
> ".... ORDER BY G.priority DESC, length(D.prefix) DESC LIMIT 1;".
> And I think the rules for the structure of tables must be more strong.
> Is it really necessary to create one group with different currencysym-s
> for
> the same destination? Only introduction of strict conformity " one > group
> - one currency " allows to reduce time of processing of query up to > four
> times:
>
> voipdb=> explain analyze SELECT voiptariffdst.id, > voiptariffdst.active,
> voiptariff.id FROM voiptariffdst LEFT JOIN voiptariff ON > voiptariff.dstid
> =
> voiptariffdst.id LEFT JOIN voiptariffgrp ON voiptariff.grpid =
> voiptariffgrp.id LEFT JOIN voiptariffsel ON voiptariffsel.grpid =
> voiptariffgrp.id WHERE NOT voiptariffdst.exactmatch AND
> ('0022#70963588998'
> LIKE (voiptariffdst.prefix || '%')) AND NOT voiptariff.terminating AND
> voiptariff.currencysym = 'RUB' AND (voiptariff.grpid IS NULL OR
> voiptariffsel.accountid = 2) ORDER BY voiptariffgrp.priority DESC,
> length(voiptariffdst.prefix) DESC LIMIT 1;
>
> QUERY PLAN
> -------------------------------------------------------------------------
>--------------------------------------------------------------------------
>---------------- Limit (cost=76.16..76.16 rows=1 width=45) (actual
> time=22.762..22.764 rows=1
> loops=1)
> -> Sort (cost=76.16..76.16 rows=2 width=45) (actual
> time=22.755..22.755
> rows=1 loops=1)
> Sort Key: voiptariffgrp.priority, length(voiptariffdst.prefix)
> -> Nested Loop Left Join (cost=0.00..76.15 rows=2 width=45)
> (actual
> time=0.490..22.703 rows=2 loops=1)
> Filter: (("outer".grpid IS NULL) OR ("inner".accountid =
> 2)) -> Nested Loop Left Join (cost=0.00..41.86 rows=2 width=53)
> (actual time=0.436..22.603 rows=2 loops=1)
> -> Nested Loop (cost=0.00..37.02 rows=1 width=45)
> (actual time=0.410..22.539 rows=2 loops=1)
> -> Seq Scan on voiptariff (cost=0.00..22.50
> rows=3 width=12) (actual time=0.046..5.757 rows=855 loops=1)
> Filter: ((NOT terminating) AND
> (currencysym =
> 'RUB'::bpchar))
> -> Index Scan using voiptariffdst_pkey on
> voiptariffdst (cost=0.00..4.83 rows=1 width=37) (actual
> time=0.015..0.015 rows=0 loops=855)
> Index Cond: ("outer".dstid =
> voiptariffdst.id)
> Filter: ((NOT exactmatch) AND
> ('0022#70963588998'::text ~~ (prefix || '%'::text)))
> -> 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) -> Index Scan
> using voiptariffsel_unique on voiptariffsel (cost=0.00..17.07 rows=5
> width=8) (actual time=0.012..0.022 rows=2 loops=2)
> Index Cond: (voiptariffsel.grpid = "outer".id)
> Total runtime: 23.083 ms
> ______
>
> voipdb=> explain analyze SELECT voiptariffdst.id, > voiptariffdst.active,
> voiptariff.id FROM voiptariffdst LEFT JOIN voiptariff ON > voiptariff.dstid
> =
> voiptariffdst.id LEFT JOIN voiptariffgrp ON voiptariff.grpid =
> voiptariffgrp.id LEFT JOIN voiptariffsel ON voiptariffsel.grpid =
> voiptariffgrp.id WHERE NOT voiptariffdst.exactmatch AND
> ('0022#70963588998'
> LIKE (voiptariffdst.prefix || '%')) AND NOT voiptariff.terminating AND
> (voiptariff.grpid IS NULL OR voiptariffsel.accountid = 2) ORDER BY
> voiptariffgrp.priority DESC, length(voiptariffdst.prefix) DESC LIMIT 1;
>
> QUERY
> PLAN
> -------------------------------------------------------------------------
>--------------------------------------------------------------------------
>------- Limit (cost=108.66..108.66 rows=1 width=45) (actual
> time=4.881..4.883 rows=1
> loops=1)
> -> Sort (cost=108.66..108.66 rows=3 width=45) (actual
> time=4.875..4.875
> rows=1 loops=1)
> Sort Key: voiptariffgrp.priority, length(voiptariffdst.prefix)
> -> Nested Loop Left Join (cost=25.01..108.63 rows=3 width=45)
> (actual time=1.754..4.831 rows=2 loops=1)
> Filter: (("outer".grpid IS NULL) OR ("inner".accountid =
> 2)) -> Nested Loop Left Join (cost=25.01..57.20 rows=3 width=53)
> (actual time=1.703..4.732 rows=2 loops=1)
> -> Hash Join (cost=25.01..47.53 rows=2 width=45)
> (actual time=1.661..4.639 rows=2 loops=1)
> Hash Cond: ("outer".dstid = "inner".id)
> -> Seq Scan on voiptariff (cost=0.00..20.00
> rows=500 width=12) (actual time=0.008..1.811 rows=855 loops=1)
> Filter: (NOT terminating)
> -> Hash (cost=25.00..25.00 rows=3 width=37)
> (actual time=1.581..1.581 rows=0 loops=1)
> -> Seq Scan on voiptariffdst
> (cost=0.00..25.00 rows=3 width=37) (actual time=0.076..1.568 rows=2
> loops=1)
> Filter: ((NOT exactmatch) AND
> ('0022#70963588998'::text ~~ (prefix || '%'::text)))
> -> Index Scan using voiptariffgrp_pkey on
> voiptariffgrp
> (cost=0.00..4.82 rows=1 width=8) (actual time=0.025..0.028 rows=1
> loops=2) Index Cond: ("outer".grpid = voiptariffgrp.id) -> Index Scan
> using voiptariffsel_unique on voiptariffsel (cost=0.00..17.07 rows=5
> width=8) (actual time=0.012..0.021 rows=2 loops=2)
> Index Cond: (voiptariffsel.grpid = "outer".id)
> Total runtime: 5.196 ms
>
> On Monday 25 October 2004 17:42, Zygmuntowicz Michal wrote:
>> You should consider groups to be an optional feature.
>> You are using it, but another person may not need it at all.
>> It is important difference, as someone else might write a patch
>> that works well for scenario that does not use groups
>> and does not work well for groups.
>> Would you be happy with such patch?;-)
>>
>> Anyway, the problem is solved in the cvs.
>>
>> ----- Original Message -----
>> From: "Kompnet" <kompnet@xxxxxxxxx>
>> Sent: Monday, October 25, 2004 3:35 PM
>>
>> > What does it mean "default tariff (not assigned to any
>> > group/account)"? I meant the default tariff is the tariff with the
>> > lowest priority. But it must be assigned to any groups.
>> > If I really understand you, you mean if user havn't grpid in
>> > voiptariffsel for
>> > any destinations the tariff with grpid=NULL from voiptariff must be
>> > applied
>> > for these destinations?
>> > Well, if you think this is really importante difference I'll try to
>> > fix my query.
>> >
>> > On Monday 25 October 2004 14:04, Zygmuntowicz Michal wrote:
>> >> Unfortunatelly, your patch does not work fine.
>> >> It cannot find a default tariff (not assigned to any group/account)
>> >> - did you really check it?
>> >>
>> >> ----- Original Message -----
>> >> From: "Kompnet" <kompnet@xxxxxxxxx>
>> >> Sent: Friday, October 22, 2004 9:03 AM
>> >>
>> >> > 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
------------------------------------------------------- This SF.net email is sponsored by: IT Product Guide on ITManagersJournal Use IT products in your business? Tell us what you think of them. Give us Your Opinions, Get Free ThinkGeek Gift Certificates! Click to find out more http://productguide.itmanagersjournal.com/guidepromo.tmpl
_______________________________________________________
List: Openh323gk-users@xxxxxxxxxxxxxxxxxxxxx Archive: http://sourceforge.net/mailarchive/forum.php?forum_id=8549 Homepage: http://www.gnugk.org/
