destinations to each group. This is the same problem as the current
one (adding active destination to each group).
Which other convenient way you see with routing policies to block
a few hundred prefixes that change dynamically?
2) Just stop thinking about groups;) Some people do not use groups at all.
No voiptariffgrp and voiptariffsel. Then you see that to match the correct
tariff, we need to compare currencies.
----- Original Message ----- From: "Kompnet" <kompnet@xxxxxxxxx>
Sent: Wednesday, October 27, 2004 7:25 AM
1) Yes, a life in general is very much complicated thing...
What prevents your operator to forbid calls on a destination 4870 using the
same group with the same high priority as for destination 48?
Or grate Zaratustra doesn't allow this simple way?
And not forget, we are talking about AUTHENTICATION, not about routing
policies. There is a set of other ways to block a destination.
2) Regarding to currencies. Pls, give us simple example how can it be
realised. Each voipuser (is the customer a user or not?) has only one
voipaccount and consequently can have only one currencysym. And we match
tariff by voiptariffsel.accountid including. Or I again do not understand
something?
On Tuesday 26 October 2004 19:02, Zygmuntowicz Michal wrote:1) It's a complicated example, but I think I understand this. But with your
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: Sybase ASE Linux Express Edition - download now for FREE LinuxWorld Reader's Choice Award Winner for best database on Linux. http://ads.osdn.com/?ad_id=5588&alloc_id=12065&op=click
_______________________________________________________
List: Openh323gk-users@xxxxxxxxxxxxxxxxxxxxx Archive: http://sourceforge.net/mailarchive/forum.php?forum_id=8549 Homepage: http://www.gnugk.org/
