First, create a table in your favorite ODBC-compatible database. Here's my setup for Postgres 8.0. If you're using MySQL the SQL will be slightly different.
--Now that the database is built, you need to connect it with the ODBC by creating a 'dsn' (short for Data Source Name) in the /etc/odbc.ini. Here's an approximation of mine:
-- Name: skype_alias; Type: TABLE; Schema: public; Owner: ssokol; Tablespace:
--
CREATE TABLE skype_alias (
id integer NOT NULL,
name character varying(50) NOT NULL,
description character varying(50),
date_added timestamp with time zone DEFAULT now() NOT NULL,
date_updated timestamp with time zone DEFAULT now() NOT NULL,
exten integer NOT NULL
);
--
-- Name: skype_alias_id_seq; Type: SEQUENCE; Schema: public; Owner: ssokol
--
CREATE SEQUENCE skype_alias_id_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
ALTER TABLE public.skype_alias_id_seq OWNER TO ssokol;
--
-- Name: skype_alias_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: ssokol
--
ALTER SEQUENCE skype_alias_id_seq OWNED BY skype_alias.id;
--
-- Name: id; Type: DEFAULT; Schema: public; Owner: ssokol
--
ALTER TABLE skype_alias ALTER COLUMN id SET DEFAULT nextval('skype_alias_id_seq'::regclass);
--
-- Name: skype_alias_name_key; Type: CONSTRAINT; Schema: public; Owner: ssokol; Tablespace:
--
ALTER TABLE ONLY skype_alias
ADD CONSTRAINT skype_alias_name_key UNIQUE (name);
--
-- Name: skype_alias_pkey; Type: CONSTRAINT; Schema: public; Owner: ssokol; Tablespace:
--
ALTER TABLE ONLY skype_alias
ADD CONSTRAINT skype_alias_pkey PRIMARY KEY (id);
[asterisk]
Description = Asterisk Database
Driver = PostgreSQL
Trace = Yes
TraceFile = sql.log
Database = asterisk
Servername = localhost
UserName = ssokol
Password = whatever
Port = 5432
Protocol = 6.4
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
ConnSettings =
Next, you will need to make Asterisk aware of the DSN by adding it to the res_odbc.conf file:
[asterisk]
enabled => yes
dsn => asterisk
username => ssokol
password => whatever
pre-connect => yes
Now Asterisk knows how to talk to the database over ODBC. Next, you need to build a couple of query functions in the func_odbc.ini file:
[SKYPEALIAS]
dsn=asterisk
readsql=SELECT exten FROM skype_alias WHERE name = '${SQL_ESC(${ARG1})}'
writesql=INSERT INTO skype_alias(name, description, exten) VALUES ('${SQL_ESC(${VAL1})}', '${SQL_ESC(${VAL2})}', ${SQL_ESC(${VAL3})})
[SKYPENEXT]
dsn=asterisk
readsql=SELECT MAX(exten) AS maxexten FROM skype_alias;
[SKYPENAME]
dsn=asterisk
readsql=SELECT name FROM skype_alias WHERE exten = ${SQL_ESC(${ARG1})}
These do the following:
The "skypealias" query has both a read and a write function. The read function attempts to return an alias extension number based on a known Skype name. This is executed whenever an incoming Skype call arrives at the Asterisk system.
The write function creates an alias record for the Skype user based on three inputs: Skype name, description (usually the caller-id name value), and the assigned alias extension number.
The "skypenext" query is used to help the system generate a new alias extension number by selecting the current highest value from the skype_alias table. (Note that this could also be done using a sequence, and probably should in Postgres. I'm not sure how sequences work in MySQL, so I'm taking the easy way out here. Feel free to post a comment with the recipe.)
The "skypename" query is used by the system to translate alias numbers into Skype names for outgoing calls.
Once you have the queries set up in func_odbc.conf, you need to add a number of Dialplan macros to your extensions.conf file. These implement the necessary reads and writes:
;Skype Alias - Find or generate an alias extension number for an
;incoming Skype call
[macro-skypealias]
exten => s,1,NoOp(Searching for Skype Extension Alias for ${CALLERID(number)})
exten => s,n,Set(SKYPEXT=${ODBC_SKYPEALIAS(${CALLERID(number)})})
exten => s,n,GotoIf($["${SKYPEXT}" = ""]?create:assign)
exten => s,n(create),Set(SKYPEXT=${ODBC_SKYPENEXT()})
exten => s,n,GotoIf($["${SKYPEXT}" = ""]?noval:nextval)
exten => s,n(noval),Set(SKYPEXT=3000)
exten => s,n(nextval),Set(SKYPEXT=$[${SKYPEXT}+1])
exten => s,n,Set(ODBC_SKYPEALIAS()=${CALLERID(number)},${CALLERID(name)},${SKYPEXT})
exten => s,n(assign),Set(CALLERID(number)=${SKYPEXT})
exten => s,n,MacroExit()
;Translate an alias extension into a Skype name for call-back
[macro-skypename]
exten => s,1,Set(SKYPENAME=${ODBC_SKYPENAME(${ARG1})})
The first macro, "macro-skypealias" needs to be called on all incoming Skype calls. It grabs the Skype name of the caller (which is stored in the caller-id number field in Asterisk) and uses that to search the alias table for an existing number asignment. If it doesn't find one it creates a new alias extension number and stores it in the database. In either case it replaces the caller-id number values with a dial-able value which is passed on to the endpoint.
In this example I've set the Skype alias extensions to start with 3000 (actually 3001) and count up. This is arbitrary and you can alter it to match your dial plan as needed. If you have a LOT of friends on Skype you may want to consider using a four digit extension pattern.
My system has a bunch of Skype names associated with it (
[from_skype]
exten => ssokol.macbook,1,Macro(skypealias)
exten => ssokol.macbook,n,Dial(SIP/ssokol-ip650, 20)
exten => ssokol.macbook,n,Answer()
exten => ssokol.macbook,n,Wait(0.5)
exten => ssokol.macbook,n,Playback(try-my-cell-number)
exten => ssokol.macbook,n,Hangup()
exten => s,1,Macro(skypealias)
exten => s,n,Goto(mainmenu|s|1)
So when a call comes in from Skype, the system automatically generates an alias extension which I can use to call back over Skype from my Polycom IP650 desk phone (or any other normal phone with a key-pad). To make this work, I've included a context that matches Skype alias extensions (in my case 3XXX):
[to_skype]
exten => _3XXX,1,Macro(skypename,${ARG1})
exten => _3XXX,n,Dial(Skype/ssokol.macbook@${SKYPENAME}, 60)
exten => _3XXX,n,Congestion()
I simply include the "to_skype" context along with "inside", "local", "longdistance", "international" and all of my other system contexts. When I dial a 3XXX number Asterisk check the database and tries to translate that into a Skype name to dial. Obviously I could get much fancier in the error handling but this serves as a perfectly worthy example.
Other enhancements that could be added would include a web-based GUI that allows you to manually create alias extensions for people you want to call. Since the alias table is stored in a "real" database (rather than the Asterisk internal Berkley database), it's trivial to hook up a web page for adds, edits and deletes.
Please set this up and give it a try. If you have questions, please post comments.
