Toad World® Forums

Reverse Engineering SQLite database not importing indexes


#1

Good day,

I am trialing Toad Data Modeler version 6.5.4 and am testing with an existing SQLite database, it imports everything correctly but indexes are missing.

Am I missing something? When using the Reverse Engineering wizard, I have the option to select all the objects, including indexes and I have verified the index option is selected during the wizard selection process.

Most of the indexes are for foreign relationships and I can recreate them by clicking the “Index to Foreign Key in Child Entity” option in the Relationship properties window so this isn’t a huge issue but it’d be nice if the indexes already existing in the SQLite db are retrieved.


#2

Hi Alexander,

We can not simulate such problem. All our tested indexes are reversed well. For example this table/index:

create table tab1 (a int, b int);
create index ix_tab1 on tab1 (a,b DESC);

Which version of SQLite are you using, please?
Can I ask you for sending some CREATE INDEX command (and CREATE TABLE command of table with this index), which was used in your SQLite and afterwards was not reversed?

Thanks.

Mario


#3

Thank you for your response.

I’m using SQLite 3.17.

When using your test SQL script to create a test db and reversing it, I confirm the model has the index ix_tab1.

I created a small sample SQL script derived from my database containing three tables and an index where reversing does not capture the index. Which email address do I use to send the sample SQL to you?


#4

I was able to reduce my sample SQL down to a very simple example which shows the problem.

CREATE TABLE tab1 (a int,b int);
CREATE INDEX “IXFK_tab1” ON tab1 (a ASC,b ASC);

Apparently Toad doesn’t like the double quotes surrounding IXFX_tab1. If I remove the quotes, the index is reversed as expected. Otherwise it doesn’t work.

According to https://www.sqlite.org/lang_keywords.html double-quoted names are valid identifiers.


#5

You are right. TDM doesn’t reverse indexes with quoted names. We created issue TDM-4854. You can track it in release notes for next version 6.6.


#6

When do you expect v6.6 will be released?


#7

It will be in the next Beta version, which will be released probably before Christmas.
I can send you some “workaround patch” in the meanwhile - send me email on Radim.Tkacik@quest.com.