[Bug 58668] New: sqlite_prepare() is unable to handle foreign_keys changes

[bu...@apache.org]

https://bz.apache.org/bugzilla/show_bug.cgi?id=58668

            Bug ID: 58668
           Summary: sqlite_prepare() is unable to handle foreign_keys
                    changes
           Product: APR
           Version: HEAD
          Hardware: PC
                OS: Mac OS X 10.1
            Status: NEW
          Severity: major
          Priority: P2
         Component: APR
          Assignee: bugs@apr.apache.org
          Reporter: jan.m.danielsson@gmail.com

Created attachment 33310
  --> https://bz.apache.org/bugzilla/attachment.cgi?id=33310&action=edit
Use sqlite3_prepare_v2() for sqlite 3.5.0 and later.

This isn't strictly a problem isolated to apr, but one of the fixes is in apr.

I have a web application on apache httpd 2.4.10, using mod_lua and mod_dbd
against an sqlite3 database (with some prepared statements in the apache
config). I want to use foreign_keys constraints; but these are (currently)
default "off" in sqlite3, and need to be enabled using "PRAGMA
foreign_keys=ON", which affects the connection object.

The httpd's mod_dbd module has a neat feature for initializing database
connection objects called DBDInitSQL, but when I add DBDInitSQL "PRAGMA
foreign_keys=ON", my prepared statements start failing in the web application
with a "schema has changed" error.

The documentation for sqlite3's "PRAGMA foreign_keys"
(https://www.sqlite.org/pragma.html#pragma_foreign_keys) stipulates that:

``Changing the foreign_keys setting affects the execution of all statements
prepared using the database connection, including those prepared before the
setting was changed. Any existing statements prepared using the legacy
sqlite3_prepare() interface may fail with an SQLITE_SCHEMA error after the
foreign_keys setting is changed.''

It looks like apache httpd isn't running DBDInitSQL before the DBDPrepareSQL
statements _and_ (relevant to apr) it's using sqlite3_prepare() rather than
sqlite3_prepare_v2().  There seems to be two solutions; one is unrelated to
apr, and the other one is using sqlite3_prepare_v2() in apr.

I read that the v2 function was introduced in 3.5.0, and the SQL_VERSION_NUMBER
can be used to retain compatibility with pre-3.5.0 versions.

*Untested* patch attached.  Sorry for messing up the code with excessive
preprocessor junk; should probably rather do something more along the line of:

#if SQLITE_VERSION_NUMBER >= 3005000
#define sqlite3_prepare sqlite3_prepare_v2
#endif

-- 
You are receiving this mail because:
You are the assignee for the bug.

---------------------------------------------------------------------
To unsubscribe, e-mail: bugs-unsubscribe@apr.apache.org
For additional commands, e-mail: bugs-help@apr.apache.org

[Bug 58668] sqlite_prepare() is unable to handle foreign_keys changes

[bu...@apache.org]

https://bz.apache.org/bugzilla/show_bug.cgi?id=58668

Eric Hontz <ai...@gmail.com> changed:

           What    |Removed                     |Added
----------------------------------------------------------------------------
                 CC|                            |airikh@gmail.com

-- 
You are receiving this mail because:
You are the assignee for the bug.
---------------------------------------------------------------------
To unsubscribe, e-mail: bugs-unsubscribe@apr.apache.org
For additional commands, e-mail: bugs-help@apr.apache.org