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] Is rootPage a reasonable estimate for index age?
Yeah ok that makes more sense. Sorry, that was a bit obvious - late night. Thanks! - Deon -Original Message- From: sqlite-users On Behalf Of Richard Hipp Sent: Wednesday, August 7, 2019 11:32 AM To: SQLite mailing list Subject: Re: [sqlite] Is rootPage a reasonable estimate for index age? On 8/7/19, Deon Brewis wrote: > If I have 2 indexes 'A' and 'B', can I use rootPage to determine which > one of them is older? > > This seems to work for me, or is it just because my database has been > vacuumed recently and if a database has a lot of empty pages it will > add new indexes to smaller pages than older indexes? It probably only works because your DB is vacuumed. Seems to me that the rowid of the sqlite_master table entry for your index would be a more reliable indicator. Larger rowids are newer. -- D. Richard Hipp d...@sqlite.org ___ 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
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] Is rootPage a reasonable estimate for index age?
On 8/7/19, Deon Brewis wrote: > If I have 2 indexes 'A' and 'B', can I use rootPage to determine which one > of them is older? > > This seems to work for me, or is it just because my database has been > vacuumed recently and if a database has a lot of empty pages it will add new > indexes to smaller pages than older indexes? It probably only works because your DB is vacuumed. Seems to me that the rowid of the sqlite_master table entry for your index would be a more reliable indicator. Larger rowids are newer. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Is rootPage a reasonable estimate for index age?
If I have 2 indexes 'A' and 'B', can I use rootPage to determine which one of them is older? This is for a dynamic index sweep to decide which one to drop. It doesn't matter if it's sometimes wrong, if the index is needed again it will be re-created. I just don't want to get into a situation where we keep dropping and recreating the newest index rather than the oldest one (we use a set of 16 dynamic indexes). This seems to work for me, or is it just because my database has been vacuumed recently and if a database has a lot of empty pages it will add new indexes to smaller pages than older indexes? - Deon ___ 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
[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
[sqlite] Enhance CLI with ability to assert version and compile option of sqlite3.so/.dll
Hi Richard, and others, A neat little program with added in the context of Fossil SCM, which asserts the version of the SQLite library used, and which compile time option were used to compile it (statically or not, for that matter). See https://fossil-scm.org/fossil/info/350c627a52908458 I think both would make fine enhancements to the CLI, are command line options or dot-commands (or both). Checking options would be a text-to-int converter for compile-time options of course. If the CLI is linked statically, asserts itself. If linked dynamically, asserts the shared-lib it's using. The CLI is often used directly, e.g. forked from a different program, and the ability to assert a minimum version and/or compile time options would avoid weird errors, in favor of clear requirement failure ones. My $0.02. --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with int and DateTime types with EntityFrameWorkCore.Sqlite
And what about Decimal type? Because Decimals are converted to string. It's a problem because it forces the programmer to format read values from database and cannot use them directly... Best regards El mié., 7 ago. 2019 a las 10:15, Olivier Mascia () escribió: > > Le 7 août 2019 à 09:43, Ainhoa B a écrit : > > > > So, in SQLite, it doesn't matter if I create a table with a column of > int, > > smallint or long type, it will always be trated as a INTEGER type of 64 > > bits? > > Regarding integers, yes they are always signed 64 bits integers. See > https://www.sqlite.org/datatype3.html for more details. > > When physically storing the integer value, the internal format is varying > on the magnitude of the value stored. There is nothing really lost > (storage-wise) by using signed 64 bits integers at the interface level, > even though your values would only be 0, 1, 3, and 12347. > > SQLite will store signed integers using 8, 16, 24, 32, 48 or 64 bits > payloads. Values 0 and 1 even only store their type, with no payload. > > https://www.sqlite.org/fileformat2.html#record_format > > — > Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten > Grüßen, > Olivier Mascia > > ___ > 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] escaping search terms in queries with bind params
On 7/8/62 13:50, P Kishor wrote:
Using FTS5 (sqlite3 3.29.x), the following works
SELECT Count(id) AS c FROM t JOIN v ON t.id = v.id WHERE v MATCH 'Trematoda
awaiting allocation’;
but the following fails
SELECT Count(id) AS c FROM t JOIN v ON t.id = v.id WHERE v MATCH 'Trematoda
(awaiting allocation)’;
Error: fts5: syntax error near “"
Since I am doing these queries in a program, and I can’t predict what
characters might be present in my search term, how can I properly escape the
query so the following works (showing JavaScript syntax below)
function res(q) {
const s = 'SELECT Count(id) AS c FROM t JOIN v ON t.id = v.id WHERE v
MATCH ?’;
return db.prepare(s).get(q);
}
res('Trematoda (awaiting allocation)’);
Define "works". What do you want it to do for this input?
One approach would be to strip out all characters that may not be part
of fts5 barewords before passing the query to fts5:
https://sqlite.org/fts5.html#fts5_strings
Or you could try the query as input first, then strip out the special
characters and try again only if the first attempt failed - so that
users could use advanced syntax if they get it right.
Dan.
--
Puneet Kishor
Just Another Creative Commoner
http://punkish.org/About
___
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] Problem with int and DateTime types with EntityFrameWorkCore.Sqlite
> Le 7 août 2019 à 09:43, Ainhoa B a écrit : > > So, in SQLite, it doesn't matter if I create a table with a column of int, > smallint or long type, it will always be trated as a INTEGER type of 64 > bits? Regarding integers, yes they are always signed 64 bits integers. See https://www.sqlite.org/datatype3.html for more details. When physically storing the integer value, the internal format is varying on the magnitude of the value stored. There is nothing really lost (storage-wise) by using signed 64 bits integers at the interface level, even though your values would only be 0, 1, 3, and 12347. SQLite will store signed integers using 8, 16, 24, 32, 48 or 64 bits payloads. Values 0 and 1 even only store their type, with no payload. https://www.sqlite.org/fileformat2.html#record_format — Best Regards, Meilleures salutations, Met vriendelijke groeten, Mit besten Grüßen, Olivier Mascia ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem with int and DateTime types with EntityFrameWorkCore.Sqlite
So, in SQLite, it doesn't matter if I create a table with a column of int, smallint or long type, it will always be trated as a INTEGER type of 64 bits? Regards El mié., 7 ago. 2019 a las 9:01, Clemens Ladisch () escribió: > Ainhoa B wrote: > > My database has tables with int values and DateTime values. When I > execute > > the scaffold command to convert the tables of the database to models in > > .NET Framework, my colums of type int are being converted to long > > SQLite's INTEGER type has 64 bits. The framework assumes that such columns > can have 64-bit values (probably written into the DB by somebody else). > > > Regards, > Clemens > ___ > 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] Problem with int and DateTime types with EntityFrameWorkCore.Sqlite
Ainhoa B wrote: > My database has tables with int values and DateTime values. When I execute > the scaffold command to convert the tables of the database to models in > .NET Framework, my colums of type int are being converted to long SQLite's INTEGER type has 64 bits. The framework assumes that such columns can have 64-bit values (probably written into the DB by somebody else). Regards, Clemens ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] escaping search terms in queries with bind params
Using FTS5 (sqlite3 3.29.x), the following works
> SELECT Count(id) AS c FROM t JOIN v ON t.id = v.id WHERE v MATCH 'Trematoda
> awaiting allocation’;
but the following fails
> SELECT Count(id) AS c FROM t JOIN v ON t.id = v.id WHERE v MATCH 'Trematoda
> (awaiting allocation)’;
Error: fts5: syntax error near “"
Since I am doing these queries in a program, and I can’t predict what
characters might be present in my search term, how can I properly escape the
query so the following works (showing JavaScript syntax below)
function res(q) {
const s = 'SELECT Count(id) AS c FROM t JOIN v ON t.id = v.id WHERE v
MATCH ?’;
return db.prepare(s).get(q);
}
res('Trematoda (awaiting allocation)’);
--
Puneet Kishor
Just Another Creative Commoner
http://punkish.org/About
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problem with int and DateTime types with EntityFrameWorkCore.Sqlite
Hi all, I'm working with SQLite and EntityFrameworkCore and I'm having problems. My database has tables with int values and DateTime values. When I execute the scaffold command to convert the tables of the database to models in .NET Framework, my colums of type int are being converted to long and my columns of type DateTime to string in models. As far as I know, SQLite doesn't work with DateTime, it saves that types as string, integers or other values, but I don't know what it's happening with int types. Does anyone know why int types are converted to long types? Thanks! ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
