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
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
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 = 10010 or loc_type = 10020 or , loc_type = 10030 or loc_type = 10040 or , loc_type = 10050 or loc_type = 10060 or , loc_type = 10070 or loc_type = 10080 or create table geodb_hierarchies ( loc_id integer not null references geodb_locations , levelinteger 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
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
