--- Begin Message ---
The following issue has been CLOSED
======================================================================
http://bugs.bacula.org/view.php?id=1351
======================================================================
Reported By: jgoerzen
Assigned To:
======================================================================
Project: bacula
Issue ID: 1351
Category: Storage Daemon
Reproducibility: N/A
Severity: minor
Priority: normal
Status: closed
Resolution: not a bug
Fixed in Version:
======================================================================
Date Submitted: 2009-08-21 16:23 BST
Last Modified: 2009-08-21 17:17 BST
======================================================================
Summary: SQLite tables lack autoincrement -- also
implications for upgrades from SQLite 2
Description:
Debian received a bug report at
http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=542810
On investigating it, I noticed that src/cats/make_postgresql_tables.in
has:
CREATE TABLE filename
(
filenameid serial not null,
name text not null,
primary key (filenameid)
);
while make_sqlite3_tables.in has:
CREATE TABLE Filename (
FilenameId INTEGER,
Name TEXT DEFAULT '',
PRIMARY KEY(FilenameId)
);
Note that the serial type for PostgreSQL implies that filenameid is an
auto-increment field, but the Sqlite3 version is not auto incrementing.
MySQL also has an AUTO_INCREMENT flag on that field. So PostgreSQL and
MySQL are auto-incrementing, but Sqlite3 is not.
Note that this applies to many tables, not just this one; this is just an
example.
So the question is: why the difference?
Secondly, does it hurt anything? Does Bacula generate the IDs to load
manually anyhow? And if so, why mark them serial/autoincrement in
PostgreSQL and MySQL?
And finally, what should we do about migrating people from Sqlite v2 that
had autoincrement columns in their schema?
======================================================================
----------------------------------------------------------------------
(0004520) kern (administrator) - 2009-08-21 17:17
http://bugs.bacula.org/view.php?id=1351#c4520
----------------------------------------------------------------------
This is not a bug, rather a support question.
Quoting from your above statement "the Sqlite3 version is not auto
incrementing." and "So PostgreSQL and MySQL are auto-incrementing, but
Sqlite3 is not." This is incorrect the FilenameId field in SQLite2 and
SQLite3 *is* autoincrementing.
To understand why you must know that when SQLite was first released it did
not have the AUTOINCREMENT keyword, but it had a very specific way of
identifying autoincrement fields that we use. See:
http://www.sqlite.org/autoinc.html for the gory details.
Q: So the question is: why the difference?
A: Historically AUTOINCREMENT did not exist, so we use the SQLite way of
specifying autoincrement (actually specifying a ROWID item).
Q: Does Bacula generate the IDs to load manually anyhow?
A: No it uses a NULL as with MySQL and PostgreSQL and that automatically
generates the ID.
Q: And finally, what should we do about migrating people from Sqlite v2
that had autoincrement columns in their schema?
A: Sorry but I do not understand the question. SQLite2 and SQLite3 both
have and both had autoincrement columns in their schema, and it works.
Converting from SQLite2 to SQLite3 is easy. You simply "export" or dump
the SQLite2 database to an ASCII file (example in the default Bacula
catalog backup script) then import it using SQLite3 (see comments at the
bottom of the Bacula catalog backup script)
Converting SQLite2 to MySQL or PostgreSQL is a bit of a pain because of
the different SQL syntax that different engines use. There is a script in
the examples directory that purports to do this and many examples on the
web.
Issue History
Date Modified Username Field Change
======================================================================
2009-08-21 16:23 jgoerzen New Issue
2009-08-21 16:29 jgoerzen Issue Monitored: jgoerzen
2009-08-21 17:17 kern Note Added: 0004520
2009-08-21 17:17 kern Status new => closed
2009-08-21 17:17 kern Resolution open => not a bug
======================================================================
--- End Message ---
