[Freeswitch-users] Lua and database access to core_db

[Jon Bruel]

Dear all

Some feedback regarding using Lua to access core database:

First of all, I did not succeed to get SQLite drivers in Lua or ODBC-drivers in Lua to work. The SQLite driver did compile OK, but there was an error when loading into Lua. The ODBC driver did also compile OK, did load into Lua, but could not connect. Accessing the SQLite from the Linux console using "isql -v <name of DSN>" worked OK. The problems may be related to the present Linux distribution, which is Ubuntu 9.1 server. Unfortunately the public searchable information about Lua ODBC driver problems is sparse.

So I continued to try to get the FS to use MySQL as the core db. A number of problem occurred, which I did not find solution for in the FS documents. The problems and solutions are described below:

1)       The core database is not automatically created by FS, therefore I created it manually.
2)       During startup, the FS test for transaction support, and this test failed. To achieved transaction support with MySQL and MyODBC, three things had to be changed:
a.       A line was added in my.cnf to force innoDB as the default table: under the [mysqld] header, the following line was added: set-variable = default-table-type=InnoDB.
b.      The a line under the DNS was added to allow for multiple line statement support: option = 67108864. (ODBC version is 3.51).
3)       After these changes the transaction worked, but all the tables in the core db were not created, therefore I copied the structure from the SQLite tables into tables with the same names in the MySQL database. This exercise also showed what the problem was: MySQL could not create tables with many VARCHAR type files with a size of 4096 (sound very big?). The size was reduced to 255, and most of the tables were created OK. One table still gave problems: the interface table. One of the fields is called key, which is a reserved word in MySQL, and by backticking the word key in the create statement, it worked.
4)       Finally the FS started up using the MySQL, but errors splashed over the screen just after startup. There was a problem creating new records in the interface table, the problem was the key field. Changing the insert statement in switch.core.sqldb.c file by backticking the key field name and recompiling the FS solved that problem.

I guess this will be fixed in later releases and I hope this will assist the brave programmers!

I would like to argue for the development of SQLite connectivity in Lua. The ODBC core solution is not as clean as a direct database connection, and as long as this is limited to SQLite, a direct connection from "recommended script language" would be the cleanest solution. Further, it would be nice if everything works after having compiled the FS package.

Jon Brüel
Consiglia Telecommunications
DK-2960 Rungsted Kyst
Tel: +45 45 16 1000
Mob: +45 26 15 30 60
CVR: 27047882


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.freeswitch.org/pipermail/freeswitch-users/attachments/20091206/5c1f6c40/attachment-0002.html