Copy DB table to Update DB file

Hello AD1408 and happy new year,

I known the problem ! My application has had so many changes, that it can be hard to tracks user version and adapt their database to the new format (because they don't want to start from scratch and loose their data).

To compare two sqlite databases, I use this tool : Sqlite Compare
https://www.codeproject.com/Articles/22 … re-Utility

This nice (and Free) utility will compare for you two sqlite databases (structure only, or structure and data) and generates SQL scripts to upgrade from old model to new model.

Tables, triggers, foreign keys.... it handles everything and quite well.

Each time I release a new version, the conversion scripts comes with it. When the new application starts (with the old data), version numbers are compared and the scripts are applied on a copy of the user database.

The only thing the user has to do is replace the current database with the converted one.

Hope this helps

Cheers

Mathias

Adam,
If you are placing the SQL script inside an MVD project script, then you need to place the SQL scripts inside MVD SQLExecute commands.
SqlExecute('Create....');
SqlExecute('Insert.....');
SqlExecute('Drop......');
SqlExecute('Alter......');

Adam,
Your project is fixed. However, you can only run it one time. If you try running it a second time, you will get duplicate component errors. So you need to comment out the SqlExecute lines between Begin and End after running it the first time. I think Mathias had some sort of versioning process and he could put those SqlExecute lines inside an If statement so it would only execute those lines if there was a different version.

You may want to consider creating a separate project just to upgrade the database in question. You can place the SqlExecute statements within the Begin and End of the upgrade project.That's what I do when there is an upgrade to the database. You will need to change the settings.ini file in the upgrade folder. There needs to be a "server=" line after the DBMS=SQLite entry to point to the database to be upgraded.

Hi Mathias,

I hope life is treating you well and you are enjoying it.

Thank you very much for your kind help...................
Truly appreciated.........................

I have tried to apply it a smaller test project but couldn't get it right properly. It returns an error....

 { SQLite Compare script::::
BEGIN TRANSACTION;
-- Adding missing table columns

ALTER TABLE company ADD COLUMN coPhone TEXT;

CREATE TABLE products
(
    id INTEGER PRIMARY KEY ASC AUTOINCREMENT,
    pName TEXT,
    pPrice REAL,
    id_company INTEGER,
    FOREIGN KEY (id_company) REFERENCES company(id) ON DELETE CASCADE
);
COMMIT TRANSACTION;
   }

//////////////////////////////////////////////////////////////////////////

procedure Form1_Button8_OnClick (Sender: TObject; var Cancel: boolean);
var SqliteTr: TDBXSqliteTransaction;
begin
//Initiate the transaction
SqliteTr := BeginSQLiteTransaction;

SQLExecute('ALTER TABLE company ADD COLUMN coPhone TEXT;');

SQLExecute('CREATE TABLE products'+
    '('+
'id INTEGER PRIMARY KEY ASC AUTOINCREMENT,'+
'pName TEXT,'+
'pPrice REAL,'+
'id_company INTEGER REFERENCES company(id) ON DELETE CASCADE,'+
')');

//Commit the SQLTransaction
CommitSQLiteTransaction(SqliteTr);
end;

Hi Mathias,

Indeed, it did.
Thank you so much for the quick help .......................

If I understood this correctly, after we run the  db update script on new app, we'd take out update db button, compile and send it to end user with a blank sqlite.db file. All end user needs to do is overwrite blank db file with the one he has containing his data. Am I correct?

Basically, this is a second program.

The user :
- starts the program
- loads his "old database"
- no mater where the database was, it is copied in the  DB_OLD subdirectory of the tool
- the version of the database is checked
- if version is OK, conversion process can begin
- at the end, the newly created database is saved in DB_NEW subdirectory

All that is left for the user is the replace the database shipped with the new version of the application with this newly converted one, and start the program.

Code is commented

Hope this helps

Cheers

Mathias

Sent you a forum email with the download link

MVD's way of handling SQL usually gives me some issues that I cannot resolve myself. There are so many punctuations used and it's rules. On the other hand SQLite Compare uses much more easy to read and understand SQLite. I assume that's the difference of C# handling and Pascal/Delphi.

I was trying to translate SQLite Compare script to MVD. I've already spend almost 5 hours and still couldn't resolve it. I got about 4 errors atm:

Here is the script that producing errors:

procedure Form1_Button7_OnClick (Sender: TObject; var Cancel: boolean);
var SqliteTr: TDBXSqliteTransaction;
begin
    //Initiate the transaction
    SqliteTr := BeginSQLiteTransaction;

    //SQL command 1
    SQLExecute('CREATE TABLE temp_Application_534011718'+
    '('+
    'id INTEGER PRIMARY KEY ASC AUTOINCREMENT,'+
    'appName TEXT NOT NULL,'+
    'code TEXT,'+
    'issues TEXT,'+
    'todo TEXT,'+
    'notes TEXT,'+
    'id_Status INTEGER,'+
    'id_Groups INTEGER,'+
    'id_Categories INTEGER,'+
    'FOREIGN KEY (id_Status) REFERENCES Status(id),'+
    'FOREIGN KEY (id_Groups) REFERENCES Groups(id),'+
    'FOREIGN KEY (id_Categories) REFERENCES Categories(id)'+
    ')');

    //SQL command 2 (line break used after id_Categories) ' +'
    SQLExecute('INSERT INTO temp_Application_534011718 (id,appName,code,issues,todo,notes,id_Status,id_Groups,id_Categories)'
              +'SELECT id,appName,code,issues,todo,notes,id_Status,id_Groups,id_Categories FROM Application;');

    //SQL command 3
    SQLExecute('DROP TABLE Application;');

    //SQL command 4
    SQLExecute('ALTER TABLE temp_Application_534011718 RENAME TO Application;');

    //SQL command 5////
    SQLExecute('CREATE TABLE issueFilter;'+
    '('+
    'id INTEGER PRIMARY KEY ASC AUTOINCREMENT,'+
    'issueFilter TEXT,'
    ')');

    //SQL command 6
    SQLExecute('CREATE TABLE temp_Issues_1655911537'+
    '('+
    'id INTEGER PRIMARY KEY ASC AUTOINCREMENT,'+
    'issueDate TEXT,'+
    'issueName TEXT NOT NULL,'+
    'issueReoprted TEXT,'+
    'issueDesc TEXT,'+
    'id_Application INTEGER,'+
    'id_issueFilter INTEGER,'+
    'issueFilterValue TEXT,'+
    'FOREIGN KEY (id_Application) REFERENCES Application(id) ON DELETE CASCADE,'+
    'FOREIGN KEY (id_issueFilter) REFERENCES issueFilter(id'+
    ')');

    //SQL command 7
    SQLExecute('INSERT INTO temp_Issues_1655911537 (id,issueDate,issueName,issueReoprted,issueDesc,id_Application,id_issueFilter,issueFilterValue)'
              +'SELECT id,issueDate,issueName,issueReoprted,issueDesc,id_Application,NULL AS id_issueFilter,NULL AS issueFilterValue FROM Issues;');

    //SQL command 8
    SQLExecute('DROP TABLE Issues;');

    //SQL command 9
    SQLExecute('ALTER TABLE temp_Issues_1655911537 RENAME TO Issues;');

    //Commit the SQLTransaction
    CommitSQLiteTransaction(SqliteTr);
end;

Better safe than sorry,

QUERY 7
Add a blank space at the end of the first line, your commands are not separated.
Replace

issueFilterValue)'

With

issueFilterValue) '

Other than that I don't see anything else

Cheers

Math

Hello AD,

Took me some time, but I think I have something for you.

Try this little tool and tell me what you think. Instructions are included inside, just click on the 'question mark' icon, but here they are as well :

• use SQliteCompare to generate a conversion script

• start the tool and select your database to convert

• load the conversion script

• wait

https://mega.nz/#!tkoklabQ!Sbm6R5jMuVhh … d7GLpadsJU

If this tool works, I could build a version you could distribute with the needed script and instructions to your users, as well as upload it here for all users if you think it is useful.

I had to use Delphi with RAD Studio because I could not find a way to disable Foreign Key Constraints on an attached database with MVD (they are enabled again at the end of the conversion process).
The idea was to build a generic tool, not one with hard-coded instructions that would only work for a specific version and a specific database scheme. This is why the conversion script has to be loaded. But then, if the only thing you have to provide to your users is the conversion script along with the new version of your application, that is simple enough for them to do.

Cheers

Mathias

PS : many things could go wrong, starting with the SQLite connection. Let me known

where can i find conversion script?

thanks