Feature request: import MySQL dumps in CLI

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Feature request: import MySQL dumps in CLI

Thomas Kurz
Dear SQLite team,

I suppose I am not the only one having to convert between MySQL/MariaDB and SQLite databases every now and then. I know there are converters for MySQL dumps but none of any I have ever tried did work nearly reliable.

So my suggestion would be to add an import feature to the CLI that allows to directly import MySQL/MariaDB dumps into an SQLite database keeping as many information as possible. As SQLite already has a complete SQL parser I expect much better results than with existing converters.

Kind regards,
Thomas

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request: import MySQL dumps in CLI

Simon Slavin-3
On 7 Aug 2019, at 5:13pm, Thomas Kurz <[hidden email]> wrote:

> So my suggestion would be to add an import feature to the CLI that allows to directly import MySQL/MariaDB dumps into an SQLite database keeping as many information as possible. As SQLite already has a complete SQL parser I expect much better results than with existing converters.

MySQL has a tool which dumps the database as SQL commands.  SQLite has a tool which reads SQL commands and makes a database from them.

However, there are occasional compatibility problems with using the two together because of differing rules on text quoting, entity names, etc..  If you're running into one of these give us some details, and what OS you're using, and we'll see if we can figure out a script which works around them.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request: import MySQL dumps in CLI

Thomas Kurz
You can use the SQL files from OpenGeoDB as an example: http://www.fa-technik.adfc.de/code/opengeodb/opengeodb-begin.sql

The result (see below) from the https://github.com/dumblob/mysql2sqlite converter is completely useless as none of the create statements is complete. I have observed severe problems with any converter I tried in the past with different dumps. Be it incomplete statements, incomplete data, affinity problems,....

Imho it should be far less work for sqlite3.exe to import MySQL dumps than for any other person to create a working converter, because the latter requires more or less a complete SQL parser as it is not sufficient to just replace some chars here or there.

Finally, here's the converted dump from the source cited above:

PRAGMA synchronous = OFF;
PRAGMA journal_mode = MEMORY;
BEGIN TRANSACTION;
create table geodb_type_names (
  type_id              integer not null
,  type_locale          varchar(5) not null
create table geodb_locations (
  loc_id               integer not null primary key
,  loc_type             integer not null
,    check (loc_type = 100100000 or loc_type = 100200000 or
,           loc_type = 100300000 or loc_type = 100400000 or
,           loc_type = 100500000 or loc_type = 100600000 or
,           loc_type = 100700000 or loc_type = 100800000 or
create table geodb_hierarchies (
  loc_id               integer not null references geodb_locations
,  level                integer not null check (level>0 and level<=9)
,  id_lvl1              integer not null
,  id_lvl2              integer
,  id_lvl3              integer
,  id_lvl4              integer
,  id_lvl5              integer
,  id_lvl6              integer
,  id_lvl7              integer
,  id_lvl8              integer
,  id_lvl9              integer
,  valid_since          date
,  date_type_since      integer
,  valid_until          date not null
,  date_type_until      integer not null
,  check (
,    (
,      (level = 1 and /* loc_id = id_lvl1 and */
,                     id_lvl2 is null and id_lvl3 is null and
,                     id_lvl4 is null and id_lvl5 is null and
,                     id_lvl6 is null and id_lvl7 is null and
,                     id_lvl8 is null and id_lvl9 is null) or
,      (level = 2 and /* loc_id = id_lvl2 and */
,                     id_lvl1 is not null and id_lvl3 is null and
,                     id_lvl4 is null and id_lvl5 is null and
,                     id_lvl6 is null and id_lvl7 is null and
,                     id_lvl8 is null and id_lvl9 is null) or
,      (level = 3 and /* loc_id = id_lvl3 and */
,                     id_lvl1 is not null and id_lvl2 is not null and
,                     id_lvl4 is null and id_lvl5 is null and
,                     id_lvl6 is null and id_lvl7 is null and
,                     id_lvl8 is null and id_lvl9 is null) or
,      (level = 4 and /* loc_id = id_lvl4 and */
,                     id_lvl1 is not null and id_lvl2 is not null and
,                     id_lvl3 is not null and id_lvl5 is null and
,                     id_lvl6 is null and id_lvl7 is null and
,                     id_lvl8 is null and id_lvl9 is null) or
,      (level = 5 and /* loc_id = id_lvl5 and */
,                     id_lvl1 is not null and id_lvl2 is not null and
,                     id_lvl3 is not null and id_lvl4 is not null and
,                     id_lvl6 is null and id_lvl7 is null and
,                     id_lvl8 is null and id_lvl9 is null) or
,      (level = 6 and /* loc_id = id_lvl6 and */
,                     id_lvl1 is not null and id_lvl2 is not null and
,                     id_lvl3 is not null and id_lvl4 is not null and
,                     id_lvl5 is not null and id_lvl7 is null and
,                     id_lvl8 is null and id_lvl9 is null) or
,      (level = 7 and /* loc_id = id_lvl7 and */
,                     id_lvl1 is not null and id_lvl2 is not null and
,                     id_lvl3 is not null and id_lvl4 is not null and
,                     id_lvl5 is not null and id_lvl6 is not null and
,                     id_lvl8 is null and id_lvl9 is null) or
,      (level = 8 and /* loc_id = id_lvl8 and */
,                     id_lvl1 is not null and id_lvl2 is not null and
,                     id_lvl3 is not null and id_lvl4 is not null and
,                     id_lvl5 is not null and id_lvl6 is not null and
,                     id_lvl7 is not null and id_lvl9 is null) or
,      (level = 9 and /* loc_id = id_lvl9 and */
,                     id_lvl1 is not null and id_lvl2 is not null and
,                     id_lvl3 is not null and id_lvl4 is not null and
,                     id_lvl5 is not null and id_lvl6 is not null and
,                     id_lvl7 is not null and id_lvl8 is not null)
,      ) and
,      (
,        (valid_since is null and date_type_since is null) or
,        (valid_since is not null and date_type_since is not null)
,      )
create table geodb_coordinates (
  loc_id               integer not null references geodb_locations
,  coord_type           integer not null check (coord_type=200100000)
,  lat                  double precision
,  lon                  double precision
,  coord_subtype        integer
,  valid_since          date
,  date_type_since      integer
,  valid_until          date not null
create table geodb_textdata (
  loc_id               integer not null references geodb_locations
,  text_type            integer not null
,  text_val             varchar(255) not null,                  /* varchar(2000)? */
,  text_locale          varchar(5),                          /* ISO 639-1 */
,  is_native_lang       smallint(1)
,  is_default_name      smallint(1)
,  valid_since          date
,  date_type_since      integer
,  valid_until          date not null
,  date_type_until      integer not null
,    check (
,      (
,        (
,          (text_type = 500100000 or text_type = 500100004 or
,           text_type = 500100002 or text_type = 500700000 or
,           text_type = 500700001 or text_type = 500800000 or
,           text_type = 500800000 or text_type = 500900000
,          ) and
,          text_locale like '__%' and
,          is_native_lang is not null and
,          is_default_name is not null
,        ) or
,        (
,          (text_type = 500100001 or text_type = 500100003 or
,           text_type = 500300000 or text_type = 500500000 or
,           text_type = 500600000
,          ) and
,          text_locale is null and
,          is_native_lang is null and
,          is_default_name is null
,        )
,      ) and
,        (
,          (valid_since is null and date_type_since is null) or
,          (valid_since is not null and date_type_since is not null)
,        )
create table geodb_intdata (
  loc_id               integer not null references geodb_locations
,  int_type             integer not null
,  int_val              bigint not null
,  valid_since          date
,  date_type_since      integer
,  valid_until          date not null
create table geodb_floatdata (
  loc_id               integer not null references geodb_locations
,  float_type           integer not null
,  float_val            double precision not null,    /* double / float??? */
,  valid_since          date
,  date_type_since      integer
,  valid_until          date not null
create table geodb_changelog (
  id                   integer not null primary key
,  datum                date not null
,  beschreibung         text not null
,  autor                varchar(50) not null
END TRANSACTION;


----- Original Message -----
From: Simon Slavin <[hidden email]>
To: SQLite mailing list <[hidden email]>
Sent: Wednesday, August 7, 2019, 18:25:45
Subject: [sqlite] Feature request: import MySQL dumps in CLI

On 7 Aug 2019, at 5:13pm, Thomas Kurz <[hidden email]> wrote:

> So my suggestion would be to add an import feature to the CLI that allows to directly import MySQL/MariaDB dumps into an SQLite database keeping as many information as possible. As SQLite already has a complete SQL parser I expect much better results than with existing converters.

MySQL has a tool which dumps the database as SQL commands.  SQLite has a tool which reads SQL commands and makes a database from them.

However, there are occasional compatibility problems with using the two together because of differing rules on text quoting, entity names, etc..  If you're running into one of these give us some details, and what OS you're using, and we'll see if we can figure out a script which works around them.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Feature request: import MySQL dumps in CLI

Stephen Chrzanowski
In reply to this post by Thomas Kurz
The BIGGEST problem I had with importing data from MySQL to SQLite is the
table definitions.

If you do two dumps, one specifically for table definitions, the other for
the actual data to be imported, you could get a script to handle the table
definition file to make it conform to what SQLite can use, and the raw data
is sitting there ready to be imported.  Most of the work is going to be
against the table definitions as MySQL dumps information about what MySQL
engine needs to be used, while SQLite has no such necessity and breaks.

The work to be done would be to do a multi-pass "string replacement".
Remove the text that is superficial to SQLite that MySQL requires, like the
engine used by MySQL.  Remove anything that's MySQL language specific in
regards to table definitions, and wipe them or swap them for a generic TEXT
or NUMERIC definition.  Etc.  If you run into problems with the import,
it'd be easy enough modify the script and rerun the job.

I learned in my venture that the #! 000000 numbers represent to the MySQL
engine that the 000000 is a version number that must be met by the
importing engine for the command to be executed.  So (off the cuff) if the
export was done on MySQL 1.2, and the importer is MySQL 1.1, any line that
has #! 010200 {some command} would not execute on the 1.1 version.  Some of
these statements still must be executed for SQLite to behave as expected,
so you can't just blindly remove all #! lines.


On Wed, Aug 7, 2019 at 12:13 PM Thomas Kurz <[hidden email]> wrote:

> Dear SQLite team,
>
> I suppose I am not the only one having to convert between MySQL/MariaDB
> and SQLite databases every now and then. I know there are converters for
> MySQL dumps but none of any I have ever tried did work nearly reliable.
>
> So my suggestion would be to add an import feature to the CLI that allows
> to directly import MySQL/MariaDB dumps into an SQLite database keeping as
> many information as possible. As SQLite already has a complete SQL parser I
> expect much better results than with existing converters.
>
> Kind regards,
> Thomas
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users