[Serusers] Database queries?
sip
sip at arcdiv.com
Tue Sep 27 15:43:30 CEST 2005
You know... I really THOUGHT I understood what I was doing.... :)
Is there any way to essentially grab an entire DB row and do checks and
queries on different portions of it using AVPops? I know it's essentially
meant to be a 1 to 1 matching sort of thing... but the strength of databases
is that they have more than just one bit of data for each key.
My current mishmash of code is all horridly wrong... I know that much... but
I'm still trying to grasp the basic concepts.
I replaced this horrid block of code:
#if(exec_msg('
# USER=`/usr/local/sbin/return.user.sh "$SIP_HF_FROM"`;
# QEURY="SELECT * FROM calls_forwarding WHERE USERNAME=\"$SIP_OUSER\" \
# AND uri_re=\"$USER\" AND purpose=\"callblock\" AND \
# action=\"reply\"";
# BLOCK=`mysql -Bsuser -pd34th -e "$QEURY" ser`;
# if [ -z "$BLOCK" ]; then exit 1; fi'))
#{
# # If this user is blocking the TO user in the
# # calls_forwarding table....
# sl_send_reply("603", "Call Declined");
# break;
#};
This code's main function was to strip the sip:username at domain from the from
header and pass it into the DB query to determine if a user should be blocked.
Replacing it with AVPops, I have run into the problem that (well, for one, I'm
only VAGUELY aware of what I'm doing) each time I do an avp_db_load, it either
grabs the last match or grabs all of them... but it will only match on a 1 to
1 basis (attribute/value). I'm still trying to figure out how to grab a full
row and then parse portions of that row for information as opposed to grabbing
a row and only being able to get back 1 piece of information or ALL rows
matching that attribute.
Am I making any sense here?
For instance, my non-functioning AVPops stuff looks like this:
if(avp_db_load("$ruri/username","$purpose/$purpose_scheme"))
{
# If we can load the purpose portion from this username
# in the calls_forwarding table (i.e. if this username
# has a purpose portion
if(avp_check("$purpose", "eq/callblock/i"))
{
log(1,"Callblock found");
# If the purpose is callblock, load the uri to see
# if there's a match
avp_db_load("$ruri/username", "$urire/$urire_scheme");
# Load the urire column for this and check to see if
# it matches the $from address
avp_print();
if(avp_check("$urire", "eq/$from/gi"))
{
# If it matches, figure out what to do
avp_db_load("$ruri/username",
"$action/$action_scheme");
if(avp_check("$action", "eq/reply/i"))
{
# If the action is to reply, reply with the
# codes in param1 and param2 (load the
# codes)
avp_db_load("$ruri/username",
"$param1/$param1_scheme");
avp_db_load("$ruri/username",
"$param2/$param2_scheme");
#sl_send_reply("avp[$param1]","avp[$param2]");
sl_send_reply("603","Call declined.");
break;
}
else if(avp_check("$action", "eq/relay/i"))
{
# Currently, we have no relay handler...
# just send to voicemail)
route(4);
break;
};
}; # End if uri_re is equal to blocked caller
}; # End if purpose=callblock
}; # End if load purpose from DB (if entry exists)
I think I may be trying to do something for which AVP was not intended.
I'm open to scalable workarounds. :)
N.
On Tue, 27 Sep 2005 08:50:30 -0400, Steve Blair wrote
> sip wrote:
>
> >Good call, Steve....
> >
> >My ngrep INVITE showed this:
> >
> >INVITE sip:bob at mydomain.com;user=phone SIP/2.0..Via: SIP/2.0/
> > UDP XXX.XXX.XXX.XXX:2051;branch=z9hG4bK-riq61voy9x1q;rport..From: "Mom" <sip
> > :motherinlaw at another.domain.com:5060>;tag=lvqskhz9h7..To:
> ><sip:bob at mydomain.com;user=phone>
> >
> >So... placing sip:motherinlaw at another.domain.com:5060 into the DB entry does
> >exactly what I want it to.
> >
> >Which leads to another question... why do some UAs attach a port and some do
> >not? Is there any way other than putting two entries into the DB for each user
> >(one with a port, and one without) to make this into a more generic test case?
> >
> >
> >
> Don't actually know. Sorry. I've run into this a few times myself though.
>
> >N.
> >
> >
> >On Tue, 27 Sep 2005 08:02:22 -0400, Steve Blair wrote
> >
> >
> >>sip wrote:
> >>
> >>
> >>
> >>>Daniel,
> >>>
> >>>I've tried that as well. If I replace the entry in the database with
> >>>sip:motherinlaw at another.domain.com, then I still have the problem that using
> >>>
> >>>if(avp_check("$urire", "eq/$from/i"))
> >>>
> >>>doesn't match, but using
> >>>
> >>>if(avp_check("$urire", "eq/sip:motherinlaw at another.domain.com/i"))
> >>>
> >>>
> >>>
> >>>
> >>>
> >>I didn't see any mention of ngrep (or other packet sniffer) in this
> >>discussion. Have you used
> >>ngrep to see exactly what is arriving/departing from you proxy? It
> >>may be as simple as a port number is appended or perhaps the
> >>protocol tag (sip:) is not included. These are just suggestions but
> >>I think a packet sniffer will tell you exactly what is in the SIP message.
> >>
> >>
> >>
> >>>does match. Seems to be picking up the DB entry and will match it if I
> >>>statically enter what it's supposed to match, but not against a $from
> >>>variable. I was trying to figure out how to get debug messages to print so I
> >>>could see what was going on, but I have as yet been unsuccessful.
> >>>
> >>>
> >>>N.
> >>>
> >>>On Tue, 27 Sep 2005 13:50:56 +0300, Daniel-Constantin Mierla wrote
> >>>
> >>>
> >>>
> >>>
> >>>>Hello,
> >>>>
> >>>>the content of $from is sip:motherinlaw at another.domain.com
> >>>>
> >>>>$from is a sip uri, that's why does not match when do the check.
> >>>>
> >>>>Daniel
> >>>>
> >>>>On 09/26/05 22:09, sip wrote:
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>
> >>>>>I'm attempting to figure out how to use AVPops to grab data from the
> >>>>>calls_forwarding database table so I can avoid having to write my own
module
> >>>>>to handle that sort of thing, and I can avoid doing what I'm CURRENTLY
doing
> >>>>>which is using a pretty hideous exec_msg SQL query hack.
> >>>>>
> >>>>>I've run into a snag that I can't figure a way around....
> >>>>>
> >>>>>In the calls_forwarding table, I have these fields used in this fashion:
> >>>>>username == username
> >>>>>domain == domain
> >>>>>uri_re == username at domain information (for use in a couple of ways)
> >>>>>purpose == purpose for this entry (callblock, callfwd, screening, etc)
> >>>>>action == action to take (for callblock, could be reply or relay, etc)
> >>>>>param1 == option for the action above
> >>>>>param2 == additional option
> >>>>>
> >>>>>So... if I have a call block entry, it would be like this:
> >>>>>
> >>>>>username = bob
> >>>>>domain = mydomain.com
> >>>>>uri_re = motherinlaw at another.domain.com
> >>>>>purpose = callblock
> >>>>>action = reply
> >>>>>param1 = 486
> >>>>>param2 = "Busy"
> >>>>>
> >>>>>The logic would be along the lines of, when a call comes in for
> >>>>>bob at mydomain.com, I check to see if he has any callblock entries. If so, I
> >>>>>check those to see if any match the From user. If there's a match, take the
> >>>>>action in action (in this case reply with 486 - Busy... could be relay to
> >>>>>voicemail, etc).
> >>>>>
> >>>>>The problem I'm having is in the grabbing of all the data from the DB and
> >>>>>comparing it to the right values. For instance, with this avpops query
> >>>>>
> >>>>>
> >>>>>avp_db_load("$ruri/username", "$urire/$urire_scheme");
> >>>>># Load the urire column for this and check to see if
> >>>>># it matches the $from address
> >>>>>if(avp_check("$urire", "eq/$from/I"))
> >>>>>{
> >>>>>send reply... take action... etc.
> >>>>>}
> >>>>>
> >>>>>I'm calling from the number motherinlaw at another.domain.com. If I use the
> >>>>>above, the avp_check test fails. If I put
> >>>>>
> >>>>>if(avp_check("$urire", "eq/motherinlaw at another.domain.com/I"))
> >>>>>{
> >>>>>send reply... take action... etc.
> >>>>>}
> >>>>>
> >>>>>
> >>>>>Then the test succeeds and the appropriate action is taken.
> >>>>>
> >>>>>Any guesses as to what I'm missing here... why the test is failing when
I use
> >>>>>the $from variable for AVPops, but not when I hard-code the $from address
> >>>>>
> >>>>>
> >into
> >
> >
> >>>>>the test (which implies to me that it's grabbing the right data from the
> >>>>>database, but there's an issue in either the $from variable or the
comparison
> >>>>>statement)?
> >>>>>
> >>>>>I'd love a little shove in the right direction. I thought about writing a
> >>>>>module that returned what I need without the need for complex logical
paths,
> >>>>>but my C is pretty rusty, and I'm trying to save myself the
hair-pulling. :)
> >>>>>
> >>>>>
> >>>>>N.
> >>>>>
> >>>>>_______________________________________________
> >>>>>Serusers mailing list
> >>>>>serusers at lists.iptel.org
> >>>>>http://lists.iptel.org/mailman/listinfo/serusers
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>>>
> >>>_______________________________________________
> >>>Serusers mailing list
> >>>serusers at lists.iptel.org
> >>>http://lists.iptel.org/mailman/listinfo/serusers
> >>>
> >>>
> >>>
> >>>
> >>--
> >>
> >>ISC Network Engineering
> >>The University of Pennsylvania
> >>3401 Walnut Street, Suite 221A
> >>Philadelphia, PA 19104
> >>
> >>voice: 215-573-8396
> >>
> >> 215-746-8001
> >>
> >>fax: 215-898-9348
> >>
> >>sip:blairs at upenn.edu
> >>
> >>
>
> --
>
> ISC Network Engineering
> The University of Pennsylvania
> 3401 Walnut Street, Suite 221A
> Philadelphia, PA 19104
>
> voice: 215-573-8396
>
> 215-746-8001
>
> fax: 215-898-9348
>
> sip:blairs at upenn.edu
More information about the sr-users
mailing list