Bug: Sql import indexes are not parsed properly

[luckcolors]

When importing an SQL table containing indexes those are not parsed properly into index properties but are instead left as keys in the table.

To reproduce:

SQL Code:

CREATE TABLE 'users' (
  'id' bigint unsigned NOT NULL AUTO_INCREMENT,
  'name' varchar(30) NOT NULL,
  'email' varchar(30) NOT NULL,
  PRIMARY KEY ('id'),
  UNIQUE KEY 'users_email_unique' ('email'),
  KEY 'user_name_index' ('name')
);

Result:

Additionally the index section of the table properties is not populated.

[luckcolors]

Additional info on KEY index: see.

[dineug]

vcode extension v0.9.7 has been reflected.

[luckcolors]

Seems to be partially fixed both indexes are not present as keys in the table, but only the "KEY" index is added to the indexes of the table. The unique index is missing.

Try to run my example SQL.

[dineug]

Automatically generated indexes are not explicitly managed as index data.

[luckcolors]

Okay but an UNIQUE index isn't automatically generated or rather the user still needs to add it.
So i would say since you're saving for every entity the indices it has, this index is then lost on import.

[dineug]

Are you looking for these?

[luckcolors]

Nono that's not what i mean.
I'm saying that on the Database->Indexes page only the name index is listed, the unique email index is not there.

[luckcolors]

Yeah, if i add the view change i can see it aswell. But it is still not present in the table properties tab.

[dineug]

Doesn't UNIQUE KEY generate indexes automatically?

[luckcolors]

It does, but i think it makes sense for it to be in the list of indexes.
Also it wether a key is unique or not is not taken into account in the generated SQL atm it seems.

I personally think all indexes should be listed in the table properties indexes section of the app, it can be confusing to the user to see some indexes only when you change the app view.

Honestly i doesn't really matter where you define the indexes: wether it be immediately in the CREATE TABLE statement or in a following CREATE INDEX statement.

[dineug]

I checked that it was missing from the simple table SQL DDL.

PRIMARY KEY, UNIQUE KEY can't be edited.
I think it needs to be done in a way that only shows the list.

[luckcolors]

Okay no, I wasn't talking about the Primary key. That's fine the way it is.

I'm talking about the custom UNIQUE index i'm creating on email.

According to this
You can actually add the unique index just fine later with the CREATE INDEX statement (it automatically does the alter table for you).

I think i understand what you where saying earlier.
I suppose it's up to you wether you want to list all indexes in the Table Properties Indexes.
I don't think it's going to be problematic, might even be more user friendly, you could aswell list the primary key index there aswell.

Now how you actually generate the table could change based on how every index is defined:

• The PRIMARY INDEX ofcourse needs to happend in the CREATE TABLE.

• UNIQUE index with only 1 key can be rapresented with the UNIQUE keyword in the table definition query.

• Other more complex index could be added either as a KEY statement or with extra CREATE INDEX statements.

Again i don't think it matters that much as all of the SQL it is generating is going to be used to create a DATABASE from scratch.
I just think it can be confusing not listing just UNIQUE immediate indexes in the Table Properties, as you will only be able to see them by changing the table view.

[luckcolors]

For example the SQL query i was tesing with can be rapresented as:

CREATE TABLE 'users' (
  'id' bigint unsigned NOT NULL AUTO_INCREMENT,
  'name' varchar(30) NOT NULL,
  'email' varchar(30) NOT NULL UNIQUE,
  PRIMARY KEY ('id'),
  KEY 'user_name_index' ('name')
);

Of course with multi key indexes you need to use the KEY statement.

Or as:

CREATE TABLE 'users' (
  'id' bigint unsigned NOT NULL AUTO_INCREMENT,
  'name' varchar(30) NOT NULL,
  'email' varchar(30) NOT NULL,
  PRIMARY KEY ('id'),
);

CREATE UNIQUE INDEX users_email_index
ON users(email ASC);

CREATE INDEX users_name_index
ON users(name ASC);