Summary:ASTERISK-01080: [patch] Adds nat, reinvite, accountcode, amaflags, callerid, restrictcid to mySQL functionality
Reporter:Rob Gagnon (rgagnon)Labels:
Date Opened:2004-02-23 15:35:16.000-0600Date Closed:2011-06-07 14:05:03
Versions:Frequency of
Environment:Attachments:( 0) chan_sip.c.patch6.txt
( 1) sip-acl.sql
( 2) sip-friends.sql.patch6.txt
Description:mysql_peer() function was not enough to pull some needed callerid and billing information from sipfriends table (replacement for sip.conf)  added mysql_user() function


Patch for the table definition of sipfriends

Patch with new code

- Possible SEG fault fixed in mysql_peer(): was missing an "if (p)" before a free(p);
- restrictcid bug in initreqprep():  if restrictcid was on, CID name was still sent instead of "Unknown" (CID number was hidden properly however)

New values added to sipfriends:
nat (0/1) default=1, used by peers and users.
canreinvite (0/1) default=0, used by users.
accountcode (string), used by users for billing.
amaflags (0,1,2,3) default=0, used by users for billing.
cidname (string) and cidnumber (string), used by users for producing caller id information to send to called party.
restrictcid (0/1), default=0, used to hide caller id information from called party.

Changes to support user being read from DB:
- added "int temponly" to sip_user struct
- moved "guts" of while loop within check_user() function to follow the while loop, in order to allow for optional mySQL query before the "guts" execute
Comments:By: Rob Gagnon (rgagnon) 2004-02-24 12:44:40.000-0600

Reminder sent to markster

[patch] to add more mysql support for SIP on the USER side instead of just PEER.

Not sure how some of these get noticed for all to use... Dont want to be "the guy that keeps sending reminders", but not sure what rules each dev might have setup to notice new patches coming in.

By: Olle Johansson (oej) 2004-02-26 15:29:36.000-0600

Please don't mix several fixes into one. Move the caller id stuff to another bug #.

By: Rob Gagnon (rgagnon) 2004-02-26 15:38:45.000-0600


This is just a patch to read sip.conf information from sipfriends in mysql

This is where it should go as sipfriends is the db equivalent of portions of sip.conf

By: Olle Johansson (oej) 2004-02-26 15:43:12.000-0600

"restrictcid bug in initreqprep(): if restrictcid was on, CID name was still sent instead of "Unknown" (CID number was hidden properly however) "


By: Rob Gagnon (rgagnon) 2004-02-26 15:47:38.000-0600

Oh. Ok.  seemed like a waste to make a bug id up, when the fix was one line (totalling 6 characters) as shown:

n = l;

Will change to remove the callerid stuff as requested.  Also found out that I need to read the dtmfmode from the db since 3com SIP phones don't work if its not set properly.

Patch to come in about 30 mins or so

edited on: 02-26-04 14:47

By: Olle Johansson (oej) 2004-02-26 16:28:31.000-0600

Thank you for separating the patches, even if it sounds silly. Each patch need to be valuated by itself. Different people look for different things. A patch to mysql_peers is quite apart from a patch to callerids.

By: Rob Gagnon (rgagnon) 2004-02-26 16:55:41.000-0600

OK.  The 2 patches ending with "patch2.txt" go together and ARE based on latest cvs chan_sip.c file (1.301 I believe)

I removed the one line fix for caller id restriction. See bug ASTERISK-1071116 that fixes that.

I added code since my last patch that will process "dtmfmode" in both the mysql_peer() and mysql_user() functions now.

Also I touched-up my original patches to validate the data from the database better.

New value in sipfriends table:
dtmfmode int(2)
allowed values: 1(RFC2833), 2(INBAND), or 4(INFO))
default value: 1(RFC2833)

edited on: 02-26-04 15:53

By: Olle Johansson (oej) 2004-02-26 17:05:34.000-0600

Are you aware that a user or a peer can have multiple dtmfmode lines in the configuration file?


Some settings are hard to do on a db field basis. Mysql_peers doesn't really follow the .conf file formats, so it will always be a compromise. ACL (permit/deny), codec settings and other stuff will not work easily.

By: Rob Gagnon (rgagnon) 2004-02-26 17:09:47.000-0600

Are you sure about that, the build_user(), and build_peer() functions contain no code to logically OR the dtmfmode flags as read from sip.conf

edited on: 02-26-04 15:59

By: Olle Johansson (oej) 2004-03-06 03:23:27.000-0600

Obviously, I'm wrong in this case. Sorry. The dtmfmode setting only allows for one setting. The ACL is still a problem you have to solve with a db-specific syntax.

By: zoa (zoa) 2004-03-06 04:35:37.000-0600

how about adding the codecs too ?

By: lancey (lancey) 2004-03-06 16:04:28.000-0600

There could be a field in the db called Codecs. it could be contained of bits, so it could combine different codecs. The standard codes could be used (do a 'show codecs'). Allow all could be all bits set to 1. It should be easy to develop. I may help if needed.

By: Rob Gagnon (rgagnon) 2004-03-07 01:24:17.000-0600

ok.  I added codecs for now (simple compared to acl's).
For oej (and others), I will work on acl's from the database as well. (it requires another table, but fairly straightforward).

For now, the 2 patches ending with "patch3.txt" are as follows:

Adds "codecflags" (int) to sipfriends table.
Follows the values exactly as in "frame.h" for AST_FORMAT_xxxxx
==> Default value is 524302 (0x0008000E) which corresponds to the default capability value in chan_sip.c (ULAW|ALAW|GSM|H263)
==> This is a bitwise field as lancey stated.  Here are your decimal and hex values for reference:  (to allow a codec, place a 1 in the bit, to disallow, place a 0)
G723.1 = 1 = 0x1
GSM = 2 = 0x2
ULAW = 4 = 0x4
ALAW = 8 = 0x8
G726 = 16 = 0x10
ADPCM = 32 = 0x20
SLINEAR = 64 = 0x40
LPC10 = 128 = 0x80
G729A = 256 = 0x100
SPEEX = 512 = 0x200
ILBC = 1024 = 0x400
MAX_AUDIO = 32768 = 0x8000
JPEG = 65536 = 0x10000
PNG = 131072 = 0x20000
H261 = 262144 = 0x40000
H263 = 524288 = 0x80000
MAX_VIDEO = 16777216 = 0x1000000

For a non-programmer reading this note.... To use this information, just add together the numbers for the codecs you want to allow, and put that value in the "codecflags" field.  Anything not added in will automatically be disallowed.

Code to support use of codecflags field defined above from the mysql sipfriends table.
==> Altered mysql_peer() also so it notices the "codecflags" field.
==> Altered mysql_user()[ new in this patch ] to support "codecflags" as well.
==> Both functions will log with LOG_WARNING level should either find the "codecflags" value to be zero in the database.

Also to note... these two patches are diff'd against the latest CVS (1.305) for chan_sip.c

edited on: 03-07-04 00:18

By: Rob Gagnon (rgagnon) 2004-03-07 04:38:39.000-0600

OK.  Huge update in this set of patches....

First of all... the current working files for this patch are:
==> sip-acl.sql
==> chan_sip.c.patch5.txt
==> sip-friends.sql.patch3.txt

ACL is now in mysql database with this patch.... Here are the details:

Schema for table "sipacl" used by code in "chan_sip.c.patch5.txt"
ACL's are applied by INSERTing however many rows per "name" as you want.  
Not entering anything in the table for a given user is equivalent to "allow all"
(Should probably be added to /asterisk/contrib/scripts/ directory)

name:  varchar(40)
Matches the name field in sipfriends table to which this ACL applies

sense: int(2)
Tells which type of access control this is: 0 = allow, 1 = deny

ipaddr: varchar(20)
IP Address or network number of hosts(s) to allow/deny

netmask: varchar(20)
Subnet mask to go with ipaddr above for host(s) to allow/deny

Code changes since chan_sip.c.patch3.txt to support ACL's.

Added function mysql_getacl() which is called whenever mysql_peer() or mysql_user() are called.  The function populates the appropriate ->ha struct for the user or peer involved.

Modified all places in code where user or peer function were free()'d due to ->temponly being 1.  Needed to call ast_free_ha() before the user or peer was free()'d.  Previously this extra call was not needed since temponly=1 would mean there was no ACL information to be free()'d.

All places where mysql_peer() or mysql_user() are called, and it is appropriate, a check with ast_apply_ha() is done to make sure the ACL still applies.

edited on: 03-07-04 03:51

edited on: 03-08-04 14:58

By: lancey (lancey) 2004-03-07 14:09:51.000-0600

I'm working on an Asterisk billing system I will eventually release open-sourced. I will use your work and base my billing on it!

By: Rob Gagnon (rgagnon) 2004-03-08 01:40:12.000-0600

Thanks for the support lancey.  It is greatly appreciated.

Now, not to shake your confidence, but I reviewed my own code, and found I may have introduced a bug.  So I have uploaded 2 new patch files to compensate.

The complete set now for this patch:

NOTE:  There never was a "sip-friends.sql.patch5.txt"  I just jumped to 6 to keep the patch file names in line as a set.

What I found, was that sharing the "sipfriends" table with the mysql_peer() and new mysql_user() function was lacking the old "type=" value from the sip.conf file.

This would allow for queries for a peer to find a user, and vice-versa.  Now, for example, if you set an entry in the sipfriends table with type=SIP_TYPE_PEER, mysql_user() will not find the record (as it shouldn't).

The sip-friends.sql.patch6.txt file adds one more field:
"type", int(2), default=3, where the types allowed are:
(1)user, (2)peer, (3)friend
NOTE THAT THESE REALLY ARE NOT SEQUENTIAL, but are bitwise definitions. A "friend" is really both a "user" and a "peer"

Added definitions:

Appended the following to 2 SQL statements in mysql_peer():
" AND (type="2" OR type="3")"

Appended the following to the only SQL statement in mysql_user():
" AND (type="1" OR type="3")"

edited on: 03-08-04 11:51

By: Rob Gagnon (rgagnon) 2004-03-15 11:50:13.000-0600

An update.

I built a query API for asterisk, so I am going to move my "mysql_user()" function out of chan_sip.c and put it into the API.

This is a step to removing fixed mysql queries from the asterisk code-base and into an external module, yet preserving the exact functionality of the function.

As far as the API goes, I will have to find someone with commit powers to discuss it, as it is not a big change to the main codebase, but it is powerful.

By: Olle Johansson (oej) 2004-03-15 12:06:50.000-0600

The preferred generic database API is UnixODBC. Brian K West (BKW) started working on a generic ODBC api in Asterisk, check the status with him so you don't design two different approaches.

Also, consider looking into chan_sip2 and my mysql_auth function if you want to do something generic.

By: Rob Gagnon (rgagnon) 2004-03-15 13:20:59.000-0600

This is more generic than that.  This will have NO SQL in the code base, allowing queries to go to either ODBC, RADIUS, LDAP, or whatever you feel like.

By: Michael Shuler (mikes2277) 2004-03-26 01:08:13.000-0600

rgagnon, can you please update your patch6 so it will work with the current * CVS development branch's chan_sip.  It would really help me out a lot.  Thanks in advance.

By: Rob Gagnon (rgagnon) 2004-03-26 01:37:36.000-0600

Hmmm... the code for chan_sip.c I have now works with the API I have.  At the time patch6 was posted, it was current with CVS.

See ASTERISK-1181229 for more information on the API.

By: Michael Shuler (mikes2277) 2004-03-26 02:06:36.000-0600

Is 0001229 meant to replace this patch?  If you would like to contact me directly I can be emailed at mike@bwsys.net.

edited on: 03-26-04 00:57

By: Rob Gagnon (rgagnon) 2004-03-26 11:03:21.000-0600

Thats what I am hoping.  In #asterisk I had a discussion with a bunch of people regarding the number of hooks into the code that were mysql specific, or postgres specific, etc.

The most generic database access method would be to put unixODBC in, but some said that that would only allow for querying DATABASES.

There are people that might want to use other validating services for different look-ups (IE: RADIUS, or LDAP), so ASTERISK-1181229 is a proposal to solve that.

edited on: 03-26-04 10:11

By: Olle Johansson (oej) 2004-03-26 11:23:10.000-0600

I think that MYSQL_FRIENDS will have to be there for a long time, even though other more generic methods will be promoted heavily. When something is implemented in source, people will start using it and it will take time migrating them to anything else.

Also, I don't think any major additions will be added before 1.0, so it will take time until something else is implemented in CVS.

Have you sent a disclaimer to digium?

By: Rob Gagnon (rgagnon) 2004-04-05 12:25:56

Re: disclaimer, yes, a long time ago

Re: MYSQL_FRIENDS, using the code in ASTERISK-1181229 would not change the database format at all, and the data can remain.  It is just another way to get to it that is more uniform.... but then again, that discussion should stay on ASTERISK-1181229