SQLite support

[the-liquid-metal]

SQLite support

[ansgarbecker]

Here we go, the very first experimental SQLite implementation for HeidiSQL:

*Edit: Many features do not yet work, for instance the Table and Data tab show nothing, the Query grids cannot be edited and such things. Very early stage.
However, if you'd like to test it, install the latest built installer, or just update to the latest build and copy sqlite3.dll from here to your HeidiSQL directory. Note you have to put the database filename into HeidiSQL's "Hostname/IP" text field.

[penCsharpener]

I'm using the DB Browser for SQLite which does a good job but the HeidiSQL UI is better so if it was possible to support SQLite, that would be great.

[UweRupprecht]

Yes, the ALTER TABLE statement is very limited. As you said, you can work around this limitationl, so this should not be that big deal :)

[FBachofner]

HeidiSQL forum poster aikona (Eric) doesn't have a Github account but made a useful post which I am excerpting here to help move forward the conversation.

"Delphi uses Firedac as data access library and Firedac supports SQLite."

It seems considering adopting this library (if not already the case) could help with bringing SQLite support to HeidiSQL . . .

[leeoniya]

@ansgarbecker this is great! will be testing soon. and you have a Christmas present in your account.

[igitur]

SELECT * FROM pragma_foreign_key_list('HangFire.JobQueue');

or

PRAGMA foreign_key_list('HangFire.JobQueue');

[ansgarbecker]

Regarding the current state of SQLite support in HeidiSQL, I would like to close this issue, and let the remaining issues be discussed in separate tickets.
I should announce it on heidisql.com so other users can contribute with reports.

[ansgarbecker]

Does SQLite have some SQL API? If not, then HeidiSQL might be the wrong client for it, as everything is based on SQL here. Also, I need a 32bit + 64bit Windows library/dll to support it. Probably you can put some stuff here to get started here.

[tomasfejfar]

I'm not sure about what you're asking. There is C API to SQLite - usually, you bundle the DLL (http://www.sqlite.org/download.html) and use it from your code. Then you can connect (https://sqlite.org/capi3ref.html#sqlite3_open) and call SQL as you would with MySQL or any other RDBMS if I understand it correctly. (pardon me if I'm totally off, as I've never developed desktop apps seriously - only playing with buttons and alerts :D)

[UweRupprecht]

I worked with sqlite3 with delphi (using directly the API and also using ZEOS). So theres should be not that big problem on integrate it to HeidiSQL. Precompiled dll's are available at sqlite.org :)

I think this will be a greate advance for Heidi, cause a lot of developers use sqlite, as you do not need to
setup and install a whole serversoftware. You can just use the dll :)

[igitur]

Slightly off-topic question: I understand that ZeosLib was long ago, but then removed. Am I right? And why was that decision taken?

[ansgarbecker]

Yes, very early versions of HeidiSQL made use of ZeosLib until I removed that in Sep 2009 in e2674ca. There were quite a few features I could not implement with Zeos, so I decided to write my own, more lightweight abstraction layer, which was added in 12a8e2e.

[rentalhost]

About the SQL API support, I think that it is available here in C/C++. But I don't have sure if it is compatible with Delphi.

The biggest problem with SQLite is that it have a lot of database limitations like, for instance, do not support ALTER TABLE. Which imples to the need to create a new temp table with the "final state" of modifications, query old table with the existing columns, copy to new table, remove old table and rename new.

So if you intends to implements a SQLite support (and I really hope that you do that), maybe you should create a secret option to enable it while it is developed, once that it will cause a lot of bugs on initial development.

[dcanoh]

Hi, Egmond.
I am also interested in SQLite with HeidiSQL.
I would like to hear from you some kind of proposal for me to help to incorporate this support.
I can share some info about your questions:

Does SQLite have some SQL API? .... as everything is based on SQL here. 
.... 32bit + 64bit Windows library/dll to support it. 
.... some stuff here to get started .....

Tell me how we can proceed.

You can find me in cano.hernan, in gmail.

HERNAN CANO M
Systems Analyst

[erdincgc]

We all waiting for the great HeidiSQL's Sqlite support :) ... It will come true one day :)

[FBachofner]

Hallo Ansgar:

Here we go, the very first experimental SQLite implementation for HeidiSQL

What a fabulous Christmas present. Fröhliche Weihnachten!

I'll definitely test this when I return home in the New Year.

[ansgarbecker]

Todo:

• multiple queries executed one by one return too many results
• multiple queries executed in one go always return 1 result
• table tab does not show columns
• data tab shows an error

[penCsharpener]

I did a very quick test and tried creating a database by entering a path where no sqlite file existed to see if it would create it. I opened a blank database but I couldn't create tables without running into errors.

[igitur]

Good work, @ansgarbecker .

I notice that some statements are still wrong, e.g. to get the list of columns in a table, this is currently executed:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG='main' AND TABLE_NAME='HangFire.List';
/* no such table: INFORMATION_SCHEMA.COLUMNS */

but it should be:

PRAGMA table_info('HangFire.List');

The full list of pragma commands is available at: https://www.sqlite.org/pragma.html

[igitur]

@ansgarbecker I see you're now doing queries like this for determining column info:

SELECT "sql" FROM sqlite_master WHERE "type"='table' AND name='HangFire.AggregatedCounter';

I really really really think you should be using the PRAGMA commands to avoid having to parse SQL statements that's returned by the queries that you're using. The PRAGMA statements' results are much easier to work with.

[ansgarbecker]

@igitur The SQL code currently fits better into HeidiSQL's routine for retrieving table columns and keys. I will probably change the complex way of parsing that stuff. But I also see that PRAGMA table_info(...) does not provide key details, apart from the "pk" value. Foreign keys are also not included. How would you retrieve these if not from the SQL code?

[ansgarbecker]

@igitur one more pragma-related question: how to check if an index returned by PRAGMA index_list('xyz') is a foreign key or a normal index? PRAGMA foreign_key_list() does not return the same key name, and PRAGMA index_info() does not return the index type.

[igitur]

I don't have much experience with that, but I did a quick test and it seems index_list returns only normal indices and foreign_key_list returns only foreign keys. Here is a sample script:

drop table if exists contacts;
drop table if exists groups;

create table groups (
  id integer primary key autoincrement,
  group_name text
);

create table contacts (
    first_name text NOT NULL,
    last_name text NOT NULL,
    email text NOT NULL,
    group_id  int not null,
    constraint FOREIGNKEYNAME foreign key (group_id) references groups
);

CREATE UNIQUE INDEX idx_contacts_email 
ON contacts (email);

CREATE INDEX idx_contacts_name 
ON contacts (first_name, last_name);     

insert into groups (id, group_name) values (1, 'Normal');
insert into groups (id, group_name) values (2, 'Admin');

INSERT INTO contacts (first_name, last_name, email, group_id)
VALUES('John','Doe','[email protected]', 1);

INSERT INTO contacts (first_name, last_name, email, group_id)
VALUES('David','Brown','[email protected]', 1),
      ('Lisa','Smith','[email protected]', 2);
      
select * from contacts;

PRAGMA index_list('contacts');
PRAGMA foreign_key_list('contacts');

[srsbiz]

New build 10.3.0.5807 is incompatible with older MySQL servers (before 5.5. I think) due to lack of comment for indexes.

Opening table either to edit results in error: Column "Index_comment" not available.

[igitur]

@srsbiz This thread is about SQLite support. File a new issue, please.

[pantantrollo]

In the portable versions, in which the configuration is shared in a single file portable_settings.txt, the stable version (v.5571) gives an error when starting because there are sentences in this file, which refer to sqlite when it has been configured in the versions that support it. (The .exe files are in the same folder, obviously 😅 )

Possible solution would be to have the configuration files with the name of the .exe that launches them.

[ansgarbecker]

Please don't do that. Configuration settings are not backwards compatible.

[pantantrollo]

Please don't do that. Configuration settings are not backwards compatible.

Ok, sorry I thought, mistakenly, that they were. thx

[ansgarbecker]

Foreign keys are now detected, but generate a MySQL/MariaDB syntax:

CONSTRAINT "2" FOREIGN KEY ("AlbumId")
REFERENCES "main"."albums" ("AlbumId") ON UPDATE NO ACTION ON DELETE NO ACTION

I suppose this should be:

FOREIGN KEY ("AlbumId")
REFERENCES "albums"("AlbumId") ON UPDATE NO ACTION ON DELETE NO ACTION

[igitur]

@ansgarbecker Seems like BOOL fields in Sqlite are not recognised yet. I'm on build 5819

CREATE TABLE "MyTable" (
        Id  integer primary key autoincrement,
       CreatedAt DATETIME not null,
       CreatedBy TEXT not null,
       ModifiedAt DATETIME,
       ModifiedBy TEXT,
       Status TEXT not null,
       EffectiveDate DATETIME not null,
       SomeFlag BOOL not null,
       User_id INT not null,
       constraint FKB3819CC9A2587882 foreign key (User_id) references "User"
    )

[igitur]

Same with the UNIQUEIDENTIFIER type in Sqlite.

[ansgarbecker]

@igitur: next build (08fa8a0) detects BOOL columns as TINYINT, as loosely documented on https://www.sqlite.org/datatype3.html .
Please post some documentation of UNIQUEIDENTIFIER data type for SQLite.

[igitur]

@igitur: next build (08fa8a0) detects BOOL columns as TINYINT, as loosely documented on https://www.sqlite.org/datatype3.html .
Please post some documentation of UNIQUEIDENTIFIER data type for SQLite.

Yes, it's weird that UNIQUEIDENTIFIER isn't in that list. I work Sqlite databases that are generated via NHibernate ORM. See this class in NHibernate that describe all the column types that they support. In fact, there's a whole further discussion about whether UNIQUEIDENTIFIER should be internally mapped to TEXT or BLOB. See https://www.connectionstrings.com/sqlite-net-provider/store-guid-as-text/ for the BinaryGuid property on the .NET driver. Would you be able to do the same?

[ansgarbecker]

Indeed I just could successfully create such a table:

CREATE TABLE [unid](
  [uid] UNIQUEIDENTIFIER
);

... and afterwards a PRAGMA table_info('unid') said that is a "UNIQUEIDENTIFIER" type. So, I just added this type to HeidiSQL's known types, handling it like text columns:

[igitur]

I think binary blob should be the default type for UNIQUEIDENDIFIER.

…

On Sat, 18 Jan 2020, 19:21 Ansgar Becker, ***@***.***> wrote: Indeed I just could successfully create such a table: CREATE TABLE [unid]( [uid] UNIQUEIDENTIFIER ); ... and afterwards a PRAGMA table_info('unid') said that is a "UNIQUEIDENTIFIER" type. So, I just added this type to HeidiSQL's known types, handling it like text columns: [image: grafik] <https://user-images.githubusercontent.com/7986591/72667727-5274b980-3a1f-11ea-9b02-14cc4bc0fcd4.png> — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#12?email_source=notifications&email_token=AABDTPWVXN6MHQX5CEA2MY3Q6M3BTA5CNFSM4EEMYVM2YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEJJ5P6I#issuecomment-575920121>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AABDTPQO4QXHDGJAQVIJ4A3Q6M3BTANCNFSM4EEMYVMQ> .

[ansgarbecker]

I found out SQLite even allows the creation of custom data types:

So data types only have their mentioned "affinity" determination, as documented. Further on, HeidiSQL supports the commonly used types, plus a few for which it has a special grid editor, as for example ENUM and SET. UNIQUEIDENTIFIER is also a custom type, and now mapped to the binary group internally.

[ansgarbecker]

One further question: PRAGMA index_list('mytable') gives me an entry which I yet interpreted as a non-unique index. But that does not seem to exist in SQLite, does it? The entry seems to belong to a foreign key which is also present in mytable, and the sql code in sqlite_master table does not create an extra key for it. So, how do I have to interprete such entries from PRAGMA index_list() ?

[leeoniya]

not related to your last question, but it would be useful if the grids showed the internal rowid. more info:

https://www.sqlite.org/rowidtable.html
https://www.sqlite.org/withoutrowid.html

[igitur]

Wow, now I'm learning things about Sqlite that I never knew existed. I agree with @leeoniya about the rowid, but maybe style the column to indicate that it is different. I'm assuming the rowid field is read-only, right? So maybe gray it out or something?

[igitur]

One further question: PRAGMA index_list('mytable') gives me an entry which I yet interpreted as a non-unique index. But that does not seem to exist in SQLite, does it? The entry seems to belong to a foreign key which is also present in mytable, and the sql code in sqlite_master table does not create an extra key for it. So, how do I have to interprete such entries from PRAGMA index_list() ?

Can you give me a script to run on my side so that I can see what you're trying to do?

[ansgarbecker]

Let us please discuss that rowid thing in a separate issue.

For the index stuff, I am trying to find out what the result of PRAGMA index_list('mytable') exactly shows me. In the current HeidiSQL code, the results are taken as non-unique indexes, while that seems wrong. Instead, they seem to show me foreign keys, though there is also PRAGMA foreign_key_list('mytable'). I am confused :)