Summary:ASTERISK-02352: [post-1.0] [patch] res_sqlite, adds sqlite_switch, cdr engine, cli tools and SQL dialplan application by anthm
Reporter:Brian West (bkw918)Labels:
Date Opened:2004-09-05 13:03:05Date Closed:2004-10-02 22:08:08
Versions:Frequency of
Environment:Attachments:( 0) res_sqlite.tar.gz
Description:This adds a load of features to asterisk.


switch => SQlite/table

sqlite_cdr: (no need to explain)


Now you can do SQL queries from the asterisk CLI.


the SQL application will let you do an SQL query from the Dialplan.  This app blows away DBget/DBput.  Its 10000% more flexable and powerful.

exten => 555,1,SQL(SELECT balance,total,date FROM blah WHERE id=\'${IDNUM}\')

This will instantly pull balance, total and date into ${balance], ${total} and ${date}.

All this thanks to anthm and Asterlink.com, be sure to visit our booth at Astricon.

Comments:By: schurig (schurig) 2004-09-06 03:19:25

I am not sure if SQLite should be part of Asterisk's source code. Maybe we make a config file entry like the current "make mpg123" that downloads and installs sqlite on-the-fly, but normally assume users to use their Linux distribution package management.

Misc notes:
* you have CVS directorie in the tar file
* use ASTDIR in ASTXS, ASTXS=$(ASTDIR)/contrib/scripts/astxs
* maybe move the SQL application command into app_sql
* get rid of the cdr.sql file, when I read the source correctly, then it's automatically created (like in my cdr_slite.c)

By: schurig (schurig) 2004-09-06 04:35:28

Hmm, just saw that you're using internal functions of SQLIte (#include <sqliteInt.h>). So I guess you have to use the included sqlite.

By: ltropiano (ltropiano) 2004-09-06 10:47:42

This looks awesome, I wouldn't mind some configuration/dialplan examples of it's use to get going.

By: Brian West (bkw918) 2004-09-06 11:30:02

* no need to make app_sql its all there, other res_* register apps.
* astxs should be installed in your path.
* CVS directories were removed.
* the .sql files are for ref.

I feel that this is a nice addition to asterisk.  It must bring a copy of SQLite with it as we must compile it with threadsafe on.  The default is for threadsafe to be off.  We also statically link to it.  Also the hashing routines in sqlite are KICK ASS we might want to use them in other ways in asterisk down the line.  Since SQLite is free we can do all kinds of kewl things with the technology.


By: cypromis (cypromis) 2004-09-06 17:46:08

Your example code does not work since asterisk automatically converts , to | and so the whole statement gets blown up :) correct version would be:

exten => 555,1,SQL(SELECT balance\,total\,date FROM blah WHERE id=\'${IDNUM}\')

By: Brian West (bkw918) 2004-09-06 23:26:06

woops mybad.. haha

By: constfilin (constfilin) 2004-09-07 01:05:09

2 questions:

1) Isn't this patch a step away from the general policy not to integrate
  Asterisk with proprietary (vs. generic ODBC based ) SQL database

2) Even when someone integrates Asteriks with a database engine, then
  it is better to call the callication SQLite, so that application "SQL"
  is available for the support of generic ODBC based solution for
  integration of Asterisk and a database.

edited on: 09-07-04 01:05

By: schurig (schurig) 2004-09-07 01:45:43

* if cdr.sql etc are for reference, then put them into /doc ... once you made a patch out of res_sqlite
* Currently, you have:


and later

       $(ASTXS) ...

so it doesn't matter if 'astxs' is installed into the path or not. That was not my point. My point was if I have a different source code directory for Asterisks, then I have to change to lines (ASTDIR= and ASTXS=), there I suggested you let ASTXS= use ASTDIR=.

BTW, for me astxs is in asterisk/contrib/scripts/astxs and there is no Makefile entry anywhere that installs this file into my path.

By: Mark Spencer (markster) 2004-09-07 23:20:43

There may be value in having a SQL engine embedded in Asterisk if (a) it is small and (b) Asterisk can use an external one without code modification.  SQLite is definitely an interesting idea, and I'd like to evaluate its value post 1.0.

By: Brian West (bkw918) 2004-09-14 19:54:41

exten => *61,1,SQL(select dst from cdr where src=\${CALLERIDNUM} order by end desc limit 1|cdr)
exten => *61,2,Goto(default|${dst},1)
exten => *69,1,SQL(select src from cdr where dst=\${CALLERIDNUM} order by end desc limit 1|cdr)
exten => *69,2,Goto(default|${src},1)

Fewer lines... more power.. :)

By: Brian West (bkw918) 2004-09-23 10:43:05

Updated. syntax for Sql

Sql("sql code"[|filename])

edited on: 09-23-04 17:02

By: scaredycat (scaredycat) 2004-09-26 08:23:02

This should replace astdb imho - much more powerful.

cdr using this should be switchable from the config, since some like to have both a local and a remote copy and others just want the remote..

edited on: 09-26-04 10:21

By: Brian West (bkw918) 2004-09-26 10:13:07

ScardyCat that was one of the orginal ideas.

By: scaredycat (scaredycat) 2004-09-26 12:58:27

Would be good to also tidy it up so that it took notice of any verbosity setting you may have atm when using switch it spews bucket loads even with verbosity @ 1

Would also like to set the cache timeout from the config, and perhaps an option on the cli for force a cache flush.

Still an excellent app tho.

edited on: 09-26-04 13:00

By: Brian West (bkw918) 2004-09-26 20:16:37

Accually all those requests are there.. and yse the Switch stuff is very chatty.. i'll have anthm post the examples but the cache timeout stuff is there and you can also flush it.


By: Brian West (bkw918) 2004-09-26 20:21:46

sql cacheall
sql clearcache

switch => SQLite/table:300

the :300 is the timeout.. that should work.

By: florian (florian) 2004-09-30 16:09:31

ScaredyCat pointed this one out to me tonight. One of the things that I'm concerned with is the locking when writes are being executed. SQLite appears to lock the entire file for writing when an update or insert is run. How would an asterisk box behave if users could use *<foo> codes to insert data into the SQLite database ? What if there's many users all toying with this ?

By: Brian West (bkw918) 2004-09-30 22:21:29

Go read sqlite.org it has the info about this... It shouldn't be an issue.

By: scaredycat (scaredycat) 2004-10-01 04:52:16

I'm not usre you're quite right there bkw918 - res_sqlite doesn't appear to use SQLITE_BUSY (except in config_sqlite/sqlite_log), sqlite_busy_handler() or sqlite_busy_timeout().

From sqlite.org

Locking in SQLite is very course-grained. SQLite locks the entire database. Big database servers (PostgreSQL, Oracle, etc.) generally have finer grained locking, such as locking on a single table or a single row within a table. If you have a massively parallel database application, you should consider using a big database server instead of SQLite.

When SQLite tries to access a file that is locked by another process, the default behavior is to return SQLITE_BUSY. You can adjust this behavior from C code using the sqlite_busy_handler() or sqlite_busy_timeout() API functions. See the API documentation for details.

edited on: 10-01-04 04:57

By: Mark Spencer (markster) 2004-10-02 21:52:34

This bug has been updated.

By: Russell Bryant (russell) 2004-10-02 22:08:08

not included in the 1.0 branch