SQLite Forum

SQLDIFF to Import
Login

SQLDIFF to Import

(1) By Stephen C (StephenC) on 2020-08-29 22:18:34 [source]

I'm using sqldiff to produce the following in Windows

>sqldiff NULL LTNNetwork.db3 --schema
CREATE TABLE [NetworkList](
  [GameID] INTEGER NOT NULL,
  [NetworkID] INTEGER NOT NULL);
CREATE TABLE [TrainStyleList](
  [StyleID] INTEGER REFERENCES [tblSavedGames]([GameID]) ON DELETE CASCADE,
  [Title] CHAR);
CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE [tblSavedGames](
  [GameID] INTEGER PRIMARY KEY AUTOINCREMENT,
  [GameName] CHAR,
  [Description] CHAR);

I'm taking this file and putting it into a resource file to be build in to have a "new file" created based on the current schema of the database. For what this program does, there is no concern for future proofing or upgrades or whatever as the program will probably only ever be run by me and recompiled when I blow my system away. I just want to make sure that after a reinstall of Windows, when I run the app, the DB is created on the fly, and is completely empty.

The issue I have is the CREATE TABLE sqlite_sequence(name,seq); statement. Do I need to programaticaly filter out statements like this? Basically read line by line each statement and ignore the **CREATE TABLE sqlite_ ** entries that may come along? When running, I'm getting an (expected) error stating that I can't create due to the reserved wording.

(2.1) By Larry Brasfield (LarryBrasfield) on 2020-08-29 23:12:12 edited from 2.0 in reply to 1 [link] [source]

It is an interesting question as to whether the sqlite_sequence table should be generated by sqldiff output. I think it is arguably correct and arguably incorrect.

If you want the same result as if that explicit sqlite_sequence creation was absent, you can use the SQLite shell's .dump command in this way: sqlite3 -batch -cmd ".dump" testit.sdb > testit_dump.sql < nul , where 'testit.sdb' is your template database (and using the equivalent of the null device on your system for 'nul'.)

(3) By Stephen C (StephenC) on 2020-08-30 05:43:23 in reply to 2.1 [link] [source]

This works rather well, except for the INSERT statements, but, that's fine. I can work with and without that.

What about an enhancement request to have an option in sqldiff so that the sqlite_* tables are not compared or output?

Also, it'd be really nice to have each create statement put on a single line with an option again to specify CRLF or just LF or CR. I've already written something to handle multi-line statements, but, I'm sure its not bulletproof.

(4) By anonymous on 2020-08-30 09:06:58 in reply to 3 [link] [source]

And while on the SQLDIFF subject, how about an option to ignore any rowid differences.

A restored dump of a slightly changed db (e.g. a single row in some table) and the original may end up with tons of differences, all due to different rowid.