Home › Forums › gps forum

SQLite 123 and GPS

Tue, 10/11/2005 - 12:06 — Uffe Vedenbrant

There seems to be some problems when using perl and sqlite that relates to different database files and different versions of sqlite ( 123 )

When configuring with "dbtype=sqlite" the gps application works fine
but gps-maintain.pl fails and complains with the following message:
DBD::SQLite::db prepare failed: file is encrypted or is not a database(26) at dbdimp.c

This indicates that DBI SQLite is trying to open a database created with a different version of sqlite ( in this case version 1 ).

The perl module is most likely using sqlite3 to try to open up a sqlite1 database despite configured to use sqlite ( version 1)

Ok.. That seems to be a problem of the perl DBI/SQLite since there is a lot of reports on this problem when googling on the above failure..;-)

Ok.. Then i tried to make sure to use sqlite3 in all cases.. I.e both for the gps and for the gps-maintain.pl script.. But then gps fails and cannot initiate the database at all..
I.e "dbtype=sqlite3"

As follows:

Oct 11 12:31:08 plupp gps[67167]: started (ver.: 1.004 built: Oct 11 2005 03:25:31)
Oct 11 12:31:08 plupp gps[67167]: error while initialising: DB: dbi_conn_connect: libdbi could not establish a connection , NO GREYLISTING WILL BE DONE

I assume that this indicates that GPS does not support sqlite3 at all..

Any clues on how to fix this?

/Uffe

‹ sqlite3 bad sql create index syntax.. IPv6 ›
Tue, 03/14/2006 - 23:19 — ndarlow

Re: SQLite 123 and GPS

I am running gps-1.004 on FreeBSD-6.0 using sqlite-3.3.4
and have found it necessary to provide the following settings
in /usr/local/etc/gps.conf:
dbtype=sqlite3
db_sqlite3_dbdir=/var/db/postfix-gps
Note how the db_sqlite_dbdir setting has changed.

With these settings I am able to test gps at the command
line successfully. Any other settings lead to connection
errors.

Tue, 10/11/2005 - 13:11 — Uffe Vedenbrant

Re: SQLite 123 and GPS

As some extra info for future reference: I'm using ports install on FreeBSD 5.4 for both gps ( postfix-gps-devel ) and SQLite as well as perl modules.

Why i cannot use sqlite3 and only sqlite(12) is most likely a different problem.. I'll try to figure out why later..

------------------

I guess that i found a solution ( at least this works for me.. )

gps uses sqlite ( verison 1 )

sqlite2 is actually verision 1

sqlite3 is also called sqlite ( without version indication.. )

Confusing ehh..;-)

The perl DBI needs to call SQLite2 instead of SQLite since gps when configured with "dbtype=sqlite" is using sqlite version1 aka sqlite2 and if you configure it to use SQLite it actually uses sqlite3..

That was even more confusing..;-)

The libs that exits in /usr/local/lib/dbd is however called libsqlite.so
and libsqlite3.so, no libsqlite2.so exists so you cannot tell gps to use
sqlite2 which would be the correct to do..

The following change worked for me for gps-maintain.pl...

http://plupp.mynta.org/patch-20051011 (se below as well for reference)

Note this: sqlite -> SQLite2 , sqlite2 -> SQLite2 , sqlite3 -> SQLite3

/Uffe

----------------------------------------------------------------

diff -c /usr/local/libexec/gps-maintain.pl /usr/ports/mail/postfix-gps-devel/work/gps-1.004/gps-maintain.pl
*** /usr/local/libexec/gps-maintain.pl Tue Oct 11 13:42:32 2005
--- /usr/ports/mail/postfix-gps-devel/work/gps-1.004/gps-maintain.pl Fri Apr 15 00:46:42 2005
***************
*** 31,39 ****

my %dbiDriverMap = ( 'mysql' => 'mysql',
'pgsql' => 'Pg',
! 'sqlite' => 'SQLite2',
! 'sqlite2' => 'SQLite2',
! 'sqlite3' => 'SQLite'
);

my %dbDefaultPorts = ( 'mysql' => 3306,
--- 31,38 ----

my %dbiDriverMap = ( 'mysql' => 'mysql',
'pgsql' => 'Pg',
! 'sqlite' => 'SQLite',
! 'sqlite2' => 'SQLite2'
);

my %dbDefaultPorts = ( 'mysql' => 3306,
***************
*** 123,129 ****
}

# fix up the sqlite path/dbname stuff
! if ( $gpsConfig{'dbtype'} eq 'SQLite' || $gpsConfig{'dbtype'} eq 'SQLite2' || $gpsConfig{'dbtype'} eq 'SQLite3' ) {
if ( !exists($gpsConfig{'db_sqlite_dbdir'}) ) {
# i don't like using die, it's output can be
# ugly and terse for non-programmers, but oh
--- 122,128 ----
}

# fix up the sqlite path/dbname stuff
! if ( $gpsConfig{'dbtype'} eq 'SQLite' ) {
if ( !exists($gpsConfig{'db_sqlite_dbdir'}) ) {
# i don't like using die, it's output can be
# ugly and terse for non-programmers, but oh

Tue, 10/11/2005 - 12:22 — mimo

Re: SQLite 123 and GPS

My guess is that this depends on the library version of DBI you compiled gps with. If you have a DBI/DBD driver that supports sqlite v3 then compiling this and running it with gps should work. gps itself just uses ANSI SQL commands.
Maybe it's possible to force the DBI that comes with PERL to follow the v1 specification (or whatever specification the C DBI follows). This could be the main misunderstanding btw, the C DBI library is completely independent from the PERL DBI.

So the question is not as much does gps support a certain version of sqlite but does DBI/DBD support that sqlite version. It might even be worse asking someone from the DBI team this question and see what they have to say.