I am sorry for a mistake. I certainly had in view of great Zaratustra.
On Wednesday 27 October 2004 09:25, Kompnet wrote:
> 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: 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/
--
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/
