[Asterisk-Dev] SQLite Realtime Driver

[Steven Critchfield]

On Mon, 2005-12-26 at 12:09 -0600, Steven Sokol wrote:
> I've spent a good bit of the holiday working on a SQLite driver for
> ARA.  This has turned out to be a bit more of a pain than I had
> thought...
> 
> First, I wrote the driver using SQLite 3.0.  After getting the driver
> working (including learning that the \ as escape character is _NOT_
> part of the SQL standard and therefore not supported in SQLite) I
> found one problem -- PHP does not support SQLite 3.0 until version 5.1
> (which has only been out for a few months).
> 
> So, I decided to create separate versions for SQLite 2 and SQLite 3. 
> After getting the version 2 driver built and compiled, I discovered a
> serious flaw which makes it very difficult to use with Asterisk: there
> is no way to escape the SQL expression characters (i.e. % and _) in
> version 2.  None.  Not-no-way, not-no-how. Argh.  No '_1NXXNXXNXXX'
> pattern matching.
> 
> So now I ask everyone else here - what's the best solution?  Obviously
> we can use SQLite 3 (which will match the literal _ charater) but it
> doesn't fit one of my design goals: to play well with PHP.

I think a more important goal would be that you could trust the storage
underneath asterisk. I think that conflicts with SQLite 2. 
http://www.sqlite.org/lockingv3.html

I think that should point out that you don't want anything less than
SQLite 3.

So the next step is, is the PHP version requirement really that bad? If
it is, some of my suggestions would be really helpful. Anyways, there
are many other languages with drivers for SQLite. My favorite right now
is Ruby, and specifically there are drivers for SQLite 2 and 3 in
ActiveRecord and therefore you can use Rails for whatever development
you are working on.

Of course there is a a SQLite ODBC driver that might be of interest as
php has had ODBC for a while now.

Hope this is informative and helpful.
-- 
Steven Critchfield <critch at basesys.com>