Re: [sqlite] Feature request: import MySQL dumps in CLI
On 7 Aug 2019, at 9:16pm, Thomas Kurz wrote: > Well, that's why I asked for an *import* support. It's widely spread practice > to offer at least import capabilities from other software. This is what the .import function in SQLite's shell tool is for. It reads a well-documented text format. All you need to do is have a tool, supplied by the MySql/MariaDB development team which writes that format. I do not think that you're going to see the SQLite development team write any code which depends on a library for another database system. The commitment to upkeep as the library changes would be too expensive. Bear in mind that anything added to SQLite has to be supported for the next 31 years because of existing commitments to existing consortium members. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: import MySQL dumps in CLI
Wednesday, August 07, 2019, 9:16:17 PM, Thomas Kurz wrote: >> I highly doubt the SQLite team will undertake this task. They >> Surely have the skill to do so, but their priority is the one >> software product you desire to use, undoubtedly due to its >> high utility. I doubt that utility would exist if they were >> to wander off tacking the conversion challenge for the other >> popular database systems. Another reason they might not want to is that by making it an "official feature" then -- at least to some degree -- they would need to track changes in MySQL/MariaDB that might break things. Graham Holden ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: import MySQL dumps in CLI
> I highly doubt the SQLite team will undertake this task. They > Surely have the skill to do so, but their priority is the one > software product you desire to use, undoubtedly due to its > high utility. I doubt that utility would exist if they were > to wander off tacking the conversion challenge for the other > popular database systems. Well, that's why I asked for an *import* support. It's widely spread practice to offer at least import capabilities from other software. The other way round would be up to MySql/MariaDB. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Feature request: import MySQL dumps in CLI
Mr. Kurz writes: > 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. If you dislike the available converters and do not wish to bother writing or adapting one to your tastes, you could use any of several ETL tools, such as Talend or Pentahoe Kettle. These tools are good at extracting data and loading it into a(nother) database. You might have to handle getting your schema translated, but that will mainly involve minor editing of the converter outputs you have already obtained. Mr. Kurz wrote further: > 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. I highly doubt the SQLite team will undertake this task. They Surely have the skill to do so, but their priority is the one software product you desire to use, undoubtedly due to its high utility. I doubt that utility would exist if they were to wander off tacking the conversion challenge for the other popular database systems. Best regards, - Larry Brasfield ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: import MySQL dumps in CLI
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 #! 00 numbers represent to the MySQL
engine that the 00 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 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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: import MySQL dumps in CLI
t null references geodb_locations , coord_type integer not null check (coord_type=20010) , lat double precision , lon double precision , coord_subtypeinteger , 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_typeinteger 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 = 50010 or text_type = 50014 or , text_type = 50012 or text_type = 50070 or , text_type = 50071 or text_type = 50080 or , text_type = 50080 or text_type = 50090 , ) and , text_locale like '__%' and , is_native_lang is not null and , is_default_name is not null ,) or ,( , (text_type = 50011 or text_type = 50013 or , text_type = 50030 or text_type = 50050 or , text_type = 50060 , ) 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_valdouble 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 , datumdate not null , beschreibung text not null , autorvarchar(50) not null END TRANSACTION; - Original Message - From: Simon Slavin To: SQLite mailing list 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 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: import MySQL dumps in CLI
On 7 Aug 2019, at 5:13pm, Thomas Kurz 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Feature request: import MySQL dumps in CLI
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
