Transfer datas fro old to new database

Hello v_pozidis,

Ouch, that is a vast subject, especially if you have foreign keys all over the place...

There are two approaches to that kind of problem.

If you are using SQLite, you can "deactivate" the auto increment on IDs in your table and force insert the old records with their previous IDs - you'll need something like PRAGMA foreign_keys=off on your tables BEFORE proceeding.

But that is a real pain in the @33...

What I usually do is :

• rebuild the global database structure empty, without FOREIGN KEY constraints

• I insert one more field in every new tables called old_id and save the previous ID of the record I want to transfer

• when all tables are transferred, I rebuild the pivot tables (the link tables) and UPDATE the foreign keys according to the new ID of the old ID if that makes sens
  Something like UPDATE new_link_table SET new_foreign_key_id = (SELECT new_id FROM some_table WHERE old_id = something), at least that is the general idea, it's hard to be more precise with no actual tables to work on
  

• finally, I reintroduce the foreign key constraints

This is also a long process and preparation is the key to success.

In any case, if you have a big database, it's going to be a tedious process.

Cheers

Mathias

Hello v_pozidis,

Sure, you can send me a project, or just the database if you wish.

Let me precise that I do not do that through MVD though, I do it outside with third party tools. I assume it's possible to do it with MVD though, but I'd build a second application just to convert the database if I were you.

Anyway, I'll have a look at your file

Have a good day

Math

Yes I know this topic, I am in this conversation too, but I need more help Thank's anyway

Hello v_pozidis,

I had a look at your database but had no time to work on it this week. Fortunately, the week-end is coming and I'l have more time to setup an example for you.

Cheers

Math

Hello v_pozidis

Here is a simple example but that I used on a real database of mine. It's just 3 tables of the complete scheme but the approach is the same for all.

OLD TABLES STRUCTURE

As you can see, the assets and the artist are linked through a join table on their respective database IDs, that is standard and probably what most of us do.
Now, over time:

• “holes” have been produced in all three tables because of delete and add operations

• New needs arose and some fields need to be added to both tables or anything else that could justify having to transfer data to new tables

The question is: how do I transfer all data in new tables, but without loosing the correct links since (unless copying the old IDs over) new unique key IDs will be produced on insert?

Here is the new table’s structure:

Note that:

• The join table is not yet linked

• Both the artists and assets tables have a field cold “old_id”

• The join table also included the “old_id”, that’s the reference we will be copying

The first step is to copy all assets and artists over to the new tables, leaving the new IDs up to the database engine but storing the old ones in the corresponding fields

Transferring the old assets to the new asset table is done like (it is also a good time to sort the select query on any field you want to “clean” the table):

INSERT INTO data_assets(asset_name,asset_old_id)
SELECT
asset_name,
id
FROM asset
ORDER BY 
asset.id_vendor ASC,
CAST(asset_SKU AS INTEGER) ASC

Same things applies to the artist table:

INSERT INTO dico_artists(artist_name,artist_old_id)
SELECT
artist_name,
id
FROM
Artist
ORDER BY UPPER(artist_name)

Finally, the join table is transferred, but we transfer only the old IDs

INSERT INTO link_artist_asset(asset_old_id,artist_old_id)
SELECT
id_asset,
id_artist
FROM
artist_asset

DON’T MIX THE ORDER OF THE IDs OR YOU’LL GET IN TROUBLE

It’s time now to update the “new ID” fields based on the new IDs generated while transferring the artists and the assets to their new tables. I do it in two passes:

UPDATE link_artist_asset SET artist_id = 
(
SELECT artist_id FROM dico_artists WHERE link_artist_asset.artist_old_id = dico_artists.artist_old_id
)

My second query is almost identical but it is updating 185.000 records searching for IDs in a table containing 235.000 records (when there were only 4.500 in the artist table). It was still running after 3.600 seconds. So I stopped it and built and index on the data_assets table based on asset_id and asset_old_id.

UPDATE link_artist_asset SET asset_id = 
(
SELECT asset_id FROM data_assets WHERE data_assets.asset_old_id = link_artist_asset.asset_old_id
)
> Affected rows: 184488
> Time: 2114,027s

It still ran for 35 minutes. Once done, just rebuild the foreign keys. This will be the test because of you get no error, it means that all IDs have been transferred and converted correctly.

You can also delete the asset_old_id and artist_old_id fields form the three tables if you want to gain some space or keep them for references if you need them later.

ALTER TABLE "main"."link_artist_asset" RENAME TO "_link_artist_asset_old_20220917";

CREATE TABLE "main"."link_artist_asset" (
  "link_artist_asset_id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  "asset_id" INTEGER,
  "artist_id" INTEGER,
  "asset_old_id" INTEGER,
  "artist_old_id" INTEGER,
  CONSTRAINT "fk_asset" FOREIGN KEY ("asset_id") REFERENCES "data_assets" ("asset_id"),
  CONSTRAINT "fk_artist" FOREIGN KEY ("artist_id") REFERENCES "dico_artists" ("artist_id")
);

INSERT INTO "main"."sqlite_sequence" (name, seq) VALUES ("link_artist_asset", '184488');

INSERT INTO "main"."link_artist_asset" ("link_artist_asset_id", "asset_id", "artist_id", "asset_old_id", "artist_old_id") SELECT "link_artist_asset_id", "asset_id", "artist_id", "asset_old_id", "artist_old_id" FROM "main"."_link_artist_asset_old_20220917";

(This is correct for my tables, you Navicat will adapt to yours)

Here is the result with no errors

There is not much more to it: you need to be precise and… patient.

As I said, another approach would be to transfer data form old to new table with PRAGMA KEY off and force the transfer of the current IDs to the new tables. I do not like this method and do not trust myself enough to use it knowing I will probably forget something at one point or another

I hope this will be of some help for you. As I said, I had a look at your database and it's huge and... I don't read Greek

Cheers

Math

Hi,

In the Database Administration tool, open the old database.
Attach new base with command

ATTACH 'c:\...\sqlite.db' as new

c:\...\sqlite.db - Path to database.

If you will be copying an unmodified table use

INSERT INTO new.TABLE SELECT * FROM old.TABLE;

example

 INSERT INTO new.nomos SELECT * FROM old.nomos

If you copy to a table where columns are added i.e. columns vary in number

INSERT INTO new.TABLE(fieldname1, fieldname2, ...) SELECT fieldname1, fieldname2, ... FROM old.TABLE;

Migrate all tables this way.
new - the name assigned by the ATTACH command
old - the name under which you opened the old database in the Database Administration tool.

Try it on a small base first

Or in a copy of the old base in the tables, create the added columns and substitute this base
into the program. Some database skills are required. The parameters of the added columns
can be obtained from the new database.