The func_odbc dialplan function is arguably the coolest and most powerful dialplan
function in Asterisk. It allows you to create and use fairly simple dialplan functions that
retrieve and use information from databases directly in the dialplan. There are all kinds
of ways in which this might be used, such as managing users or allowing sharing of
dynamic information within a clustered set of Asterisk machines.
What func_odbc allows you to do is define SQL queries to which you assign function
names. In effect, you are creating custom functions that obtain their results by executing
queries against a database. The func_odbc.conf file is where you specify the relationship
between the function names you create and the SQL statements you wish them to
perform. By referring to the named function in the dialplan, you can retrieve and update
values in the database.
While using an external script to interact with a database (from whicha flat file is created that Asterisk would read) has advantages (if the
database went down, your system would continue to function and the
script would simply not update any files until connectivity to the data-
base was restored), a major disadvantage is that any changes you make
to a user are not available until you run the update script. This is prob-
ably not a big issue on small systems, but on large systems, waiting for
changes to take effect can cause issues, such as pausing a live call while
a large file is loaded and parsed.
You can relieve some of this by utilizing a replicated database system.
In the version of Asterisk following 1.4 (currently trunk), the syntax of
the func_odbc.conf file changes slightly, but gives the ability to failover
to another database system. This way you can cluster the database
backend utilizing a master-master relationship (pgcluster; Slony-II), or
a master-slave (Slony-I) replication system.
In order to get you into the right frame of mind for what follows, we want you to picture
a Dagwood sandwich.§
Can you relay the total experience of such a thing by showing someone a picture of a
tomato, or by waving a slice of cheese about? Not hardly. That is the conundrum we
faced when trying to give a useful example of why func_odbc is so powerful. So, we
decided to build the whole sandwich for you. It’s quite a mouthful, but after a few bites
of this, peanut butter and jelly is never going to be the same.
For our example, we decided to implement something that we think could have some
practical uses. Let’s picture a small company with a sales force of five people who have
to share two desks. This is not as cruel as it seems, because these folks spend most of
their time on the road, and they are each only in the office for at most one day each week.
Still, when they do get into the office, they’d like the system to know which desk they
are sitting at, so that their calls can be directed there. Also, the boss wants to be able
to track when they are in the office, and control calling privileges from those phones
when no one is there.
This need is typically solved by what is called a hot-desking feature, so we have built
one for you in order to show you the power of func_odbc.
Lets start with the easy stuff, and create two desktop phones in the sip.conf file.