[SQLite] Command "Script as CREATE" for index generates incorrect index name

[Bergam64]

Command Script as CREATE for index generates an index name like this: TableName + "_" + IndexName, instead of just IndexName.

Steps to reproduce

• Open any SQLite DB.
• Open a new query.
• Execute this:

  CREATE TABLE Demo (
   Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
   Name TEXT NOT NULL
  );
  
  CREATE UNIQUE INDEX IX_Demo_Name ON Demo (
   Name ASC
  );

• Refresh table list in Object Tree.
• In Object Tree, select Tables -> Demo -> Indexes.
• You can see 2 indexes:
  • sqlite_master_PK_demo (Primary Key),
  • IX_Demo_Name (Unique).
• Right-click IX_Demo_Name -> Script as CREATE.
• This gives this:

  -- Script Date: 09/10/2024 19:43  - ErikEJ.SqlCeScripting version 3.5.2.95
  CREATE UNIQUE INDEX [Demo_IX_Demo_Name] ON [Demo] ([Name] ASC);

• Index is named Demo_IX_Demo_Name instead of IX_Demo_Name!

Notes:

• Unsurprisingly, the same bug is present in command Script as CREATE for table:

  -- Script Date: 09/10/2024 19:43  - ErikEJ.SqlCeScripting version 3.5.2.95
  CREATE TABLE [Demo] (
    [Id] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
  , [Name] text NOT NULL
  );
  CREATE UNIQUE INDEX [Demo_IX_Demo_Name] ON [Demo] ([Name] ASC);

• But oddly enough, this bug is not present in command Script as DROP for index:

  -- Script Date: 09/10/2024 19:43  - ErikEJ.SqlCeScripting version 3.5.2.95
  DROP INDEX [IX_Demo_Name];

Further technical details

Toolbox/Power Tools version:
Version 4.8.776.0 - more than 1.100.000 downloads

SQL Server Compact 4.0 in GAC - No
SQL Server Compact 4.0 DbProvider - No
SQL Server Compact 4.0 Simple DDEX provider - Yes

SQL Server Compact 3.5 in GAC - No
SQL Server Compact 3.5 DbProvider - No
Sync Framework 2.1 SqlCe 3.5 provider - No

SQLite Engine in use - 3.40.0
SQLite ADO.NET provider loaded - 1.0.117.0
SQLite EF6 DbProvider in GAC - No
System.Data.SQLite DDEX provider - No
SQLite Simple DDEX provider - Yes

Database engine:
SQlite

Visual Studio or SSMS version:
Visual Studio 2022 v17.11.4

[ErikEJ]

I think this may be by design, as index names from SQL Compact only needs to be unique per table and SQL Server index names mus b unique per database (and the SQLite code has inherited this "feature")

[Bergam64]

Hello,

I humbly disagree, since commands Script as CREATE and Script as DROP generate different names for the same index. And of course, the bug is not present in Object Browser, where the index name is correct.

Long story short, SQLite index names are inconsistent across the tool. This is clearly a bug to me.

Is there anything you can do about it?

Thanks in advance.

[ErikEJ]

@Bergam64 I accept PRs (to fix for SQLite only), just discovered that index names on SQL Server only need to be unique per table!

[ErikEJ]

Related issue #732

[ErikEJ]

@Bergam64 I am working on a fix...

[ErikEJ]

@Bergam64 This was fixed in the latest daily build

[Bergam64]

Hello,

Thanks for the fix.

I wanted to try it out, so I've just installed the latest daily build (4.8.802), but it's broken :

Does this ring any bell?

[ErikEJ]

Probably due to my attempt to update to .119 - let me try to revert that for now.

[ErikEJ]

@Bergam64 Could you try build .803 ?

[ErikEJ]

@Bergam64 Any updates?

[Bergam64]

Hello,

I've just installed v4.8.806.

• The regression in v4.8.802 has been fixed.
• The initial issue with the index name has been fixed as well in commands Script as CREATE for table and for index.

Thanks!

[ErikEJ]

@Bergam64 Wonderful, thanks for confirming