Sqlite nach mysql umziehen

[sissiwup]

Hallo,

wer seine sqlite Daten nach mysql umziehen möchte, anbei eine Skizze, wie das geht:

Daten aus sqlite exportieren:

cd /opt/iobroker/iobroker-data/sqlite
sqlite3
.open sqlite.db
.mode csv
.output sources
select * from sources;
.output datapoints
select * from datapoints;
.output ts_bool
select * from ts_bool;
.output ts_numer
select * from ts_number;
.output ts_string
select * from ts_string;
.exit

In der mysql-DB anmelden und Importtabellen erzeugen (ich nutze dafür toad)

create TABLE iobroker.old_ts_bool like iobroker.ts_bool;
create TABLE iobroker.old_ts_number like iobroker.ts_number;
create TABLE iobroker.old_ts_string like iobroker.ts_string;
create TABLE iobroker.old_datapoints like iobroker.datapoints;
create TABLE iobroker.old_sources like iobroker.sources;

Dann für jede Tabelle die Daten importieren (also 5x):

LOAD DATA INFILE '/opt/iobroker/iobroker-data/sqlite/sources' 
INTO TABLE iobroker.old_sources 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';

Spalten hinzufügen:

alter TABLE iobroker.old_datapoints ADD new_id int;
alter TABLE iobroker.old_sources ADD new_id int;

neue id´s raussuchen und in die neuen Spalten einfügen(der select ist nur zum Prüfen):

select * from iobroker.old_datapoints o,iobroker.datapoints d where o.name=d.name;
update iobroker.old_datapoints o SET new_id = (select id FROM iobroker.datapoints d WHERE d.name=o.name);
select * from iobroker.old_sources o,iobroker.sources s where o.name like concat("%",s.name,"%") ;
update iobroker.old_sources o SET new_id = (select id FROM iobroker.sources s WHERE o.name like concat("%",s.name,"%"));

Jetzt noch die Daten übertragen (bitte _string,_bool und _number)(also 3x):

select s.new_id as _from,ack,d.new_id as id,q,ts,val 
from iobroker.old_ts_string o,iobroker.old_datapoints d,iobroker.old_sources s 
WHERE o.id = d.id and d.new_id IS NOT NULL and o._from = s.id and s.new_id IS NOT NULL ;

insert INTO  iobroker.ts_string (_from,ack,id,q,ts,val) (select s.new_id as _from,ack,d.new_id as id,q,ts,val 
from iobroker.old_ts_string o,iobroker.old_datapoints d,iobroker.old_sources s 
WHERE o.id = d.id and d.new_id IS NOT NULL and o._from = s.id and s.new_id IS NOT NULL );

Anschließend noch die Import-Tabellen wieder leeren:

delete FROM iobroker.old_datapoints ;
delete FROM iobroker.old_sources  ;
delete FROM iobroker.old_ts_bool  ;
delete FROM iobroker.old_ts_number  ;
delete FROM iobroker.old_ts_string  ;

[CaneTLOTW]

@sissiwup said in Sqlite nach mysql umziehen:

select s.new_id as _from,ack,d.new_id as id,q,ts,val

Vielen Dank für die Zusammenfassung.
Ich konnte damit erfolgreich eine sqlite Datenbank mit 16 Mio. Einträgen nach MatrinaDB übertragen.

insert INTO  iobroker.ts_string (_from,ack,id,q,ts,val) (select s.new_id as _from,ack,d.new_id as id,q,ts,val 

Musste ich nur um ignore ergänzen

insert ignore INTO  iobroker.ts_string (_from,ack,id,q,ts,val) (select s.new_id as _from,ack,d.new_id as id,q,ts,val 

Da es immer wieder zu Fehlern kam.

Dadurch konnten zwar einige Datensätze nicht übertragen werden, bisher sind mir jedoch keien Lücken aufgefallen....