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

[Sven Hartge]

Addendum:

After further experimentation I discovered the following facts.

Given a schema like this, 'x' _will_ autoincrement on insert:

CREATE TABLE t1 ( 
  x INTEGER, 
  name TEXT DEFAULT "", 
  PRIMARY KEY(x)
);

Give a schema like this, with added "UNSIGNED" after "INTEGER", 'x' *will 
not* autoincrement on insert:

CREATE TABLE t1 ( 
  x INTEGER UNSIGNED, 
  name TEXT DEFAULT "", 
  PRIMARY KEY(x)
);


Considering this discovery, the sqlite -> sqlite3 upgrade command should 
be:

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

This should be safe, since the only columns with AUTOINCREMENT added are 
INTEGER and all are also PRIMARY KEYs:

ds9:/var/lib/bacula # sqlite bacula.db .schema  | grep AUTOINCREMENT
   BaseId INTEGER UNSIGNED AUTOINCREMENT,
   ClientId INTEGER UNSIGNED AUTOINCREMENT,
   FileId INTEGER UNSIGNED AUTOINCREMENT,
   FileSetId INTEGER UNSIGNED AUTOINCREMENT,
   FilenameId INTEGER UNSIGNED AUTOINCREMENT,
   PathId INTEGER UNSIGNED AUTOINCREMENT,
   UnsavedId INTEGER UNSIGNED AUTOINCREMENT,

ds9:/var/lib/bacula # sqlite bacula.db .schema  | grep "PRIMARY KEY"
   PRIMARY KEY(BaseId)
   PRIMARY KEY (MediaId)
   PRIMARY KEY(ClientId)
   PRIMARY KEY (Counter)
   PRIMARY KEY(DeviceId)
   PRIMARY KEY(FileId) 
   PRIMARY KEY(FileSetId)
  PRIMARY KEY(FilenameId) 
   PRIMARY KEY(JobId)
   PRIMARY KEY(JobMediaId) 
   PRIMARY KEY(LocationId)
   PRIMARY KEY(LocLogId)
   PRIMARY KEY(LogId) 
   PRIMARY KEY(MediaId)
   PRIMARY KEY(MediaTypeId)
   PRIMARY KEY (TableName)
   PRIMARY KEY(PathId) 
   PRIMARY KEY (PoolId)
   PRIMARY KEY (JobStatus)
   PRIMARY KEY(StorageId)
   PRIMARY KEY (UnsavedId)



Grüße,
Sven



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