Bug#542810: update from sqlite2 will fail because of AUTOINCREMENT in table definition

[Sven Hartge]

Package: bacula-director-sqlite3
Version: 3.0.2-1
Severity: serious

Hello.

During the update from a previous sqlite2-base director, the postinst runs the
following command:

    sqlite "$DB" .dump | sqlite3 "$DB.sqlite3"

This will fail on older installations of bacula-director-sqlite, because the
database may contain table schemas like the following:

CREATE TABLE Filename (
  FilenameId INTEGER UNSIGNED AUTOINCREMENT,
  Name TEXT DEFAULT "",
  PRIMARY KEY(FilenameId) 
  );

Problem is, sqlite3 does not understand AUTOINCREMENT in this way, this keyword
is only allowed alongside a PRIMARY KEY statement. Besides, every integer
primary key will autoincrement without this keyword, as per 
http://sqlite.org/faq.html#q1

I suggest using a statement like this in the postinst of 
bacula-director-sqlite3:

   sqlite "$DB" .dump | sed 's/ AUTOINCREMENT,/,/' | sqlite3 "$DB.sqlite3"

This will eliminate the unwanted AUTOINCREMENT and the resulting database dump
will be the same as a newly created bacula sqlite3 database:

CREATE TABLE Filename (
  FilenameId INTEGER UNSIGNED,
  Name TEXT DEFAULT "",
  PRIMARY KEY(FilenameId)
  );


But: testing revealed the FilenameId column will not autoincrement despite it
being the primary key for this table. This sees more like a bug to be
discussed with upstream. From my reading of the source and my tests with
sqlite3, the currect table schema should not be working.

The (to me) correct schema of this table should be:

CREATE TABLE Filename (
  FilenameId INTEGER PRIMARY KEY AUTOINCREMENT,
  Name TEXT DEFAULT "",
  );


So far, I am unsure what to do.


-- System Information:
Debian Release: squeeze/sid
  APT prefers unstable
  APT policy: (900, 'unstable'), (200, 'experimental')
Architecture: i386 (i686)

Kernel: Linux 2.6.29.1-217
Locale: lang=de...@euro, lc_ctype=de...@euro (charmap=ISO-8859-15)
Shell: /bin/sh linked to /bin/dash

Versions of packages bacula-director-sqlite3 depends on:
ii  bacula-director-common        2.4.4-1+b1 network backup, recovery and verif
ii  debconf [debconf-2.0]         1.5.27     Debian configuration management sy
ii  libc6                         2.9-25     GNU C Library: Shared libraries
ii  libgcc1                       1:4.4.1-2  GCC support library
ii  libsqlite3-0                  3.6.17-2   SQLite 3 shared library
ii  libstdc++6                    4.4.1-2    The GNU Standard C++ Library v3
ii  libwrap0                      7.6.q-18   Wietse Venema's TCP wrappers libra
ii  python2.4                     2.4.6-2    An interactive high-level object-o
ii  sqlite3                       3.6.17-2   A command line interface for SQLit

bacula-director-sqlite3 recommends no packages.

bacula-director-sqlite3 suggests no packages.



-- 
To UNSUBSCRIBE, email to debian-bugs-rc-requ...@lists.debian.org
with a subject of "unsubscribe". Trouble? Contact listmas...@lists.debian.org