Sort/Group/Organize Connections

[byjg]

On MySQLWorkbench, I can group my connections and organize their order by dragging and dropping the connection to desired position.

Is it possible to have a similar feature on the VSCode extension?

[mike-lischke]

Yes, we like this too and it's planned for a future version.

[byjg]

I use a solution hard-code to try to achieve the sort. However, it didn't work.

As my connection name is defined in a way I can order it, I connected to the $HOME/.mysqlsh-gui/plugin_data/gui_plugin/mysqlsh_gui_backend.sqlite3 and did this:

-- Get the last id to be used later
select max(id) from db_connection;
-- Check if the order is what I expected
select caption from db_connection order by caption;


-- Insert in the end the connection with the order I want
insert into db_connection (db_type, caption, description, options) select db_type, caption, description, options from db_connection order by caption;

-- Delete the older connections
delete from db_connection where id <= 25;

-- Redefine this table based on the new connections
delete from profile_has_db_connection;
insert into profile_has_db_connection select 1, id, null, 1 from db_connection;

But, unfortunately, this doesn't work. I got no connections at all.

What did I miss?

[mbodzek]

Hello @byjg,

Here is an example how to do it properly. Suppose I have 3 connections in db:

SELECT c.id, c.caption, hc.profile_id, hc.folder_path, hc.`index` 
FROM db_connection c 
LEFT JOIN profile_has_db_connection hc 
ON c.id = hc.db_connection_id;

1|Conection1|1|/|1
2|Connection2|1|/|2
3|Connection3|1|/|3

Now I want move Connection3 into position between 1 and 2. To do that I need update all indexes in profile_has_db_connection table that are grater than 1 and smaller than 3 by adding +1 to move them and make space for Connection3

UPDATE profile_has_db_connection 
SET `index`=`index`+1 
WHERE profile_id=1 AND folder_path='/' AND `index`>1 AND `index`<3;

Next I have to update index for Connection3

UPDATE profile_has_db_connection 
SET `index`=2 
WHERE profile_id=1 AND folder_path='/' AND db_connection_id=3;

At the end my connections are in order that I want

SELECT c.id, c.caption, hc.profile_id, hc.folder_path, hc.`index` 
FROM db_connection c 
LEFT JOIN profile_has_db_connection hc 
ON c.id = hc.db_connection_id;

1|Conection1|1|/|1
2|Connection2|1|/|3
3|Connection3|1|/|2

For grouping connection you have to update folder_path in profile_has_db_connection.
Keep it mind that feature is not yet supported by the frontend and will not work in extension.
We have that on our todo list and eventually we'll implement it.

I hope my post will be helpful for you.

[byjg]

I updated the index as you said, but there is no real effect in the connection order. The table profile_has_db_connection has the index field values in the order I want, but the interface still showing in the db_connection_id order.

Is there anything else I have to do?

[mbodzek]

No, that's all, but seems frontend doesn't have that implemented yet.

[Hyunho-Jung]

I would like to reiterate the need for this feature as well.

[mike-lischke]

@Hyunho-Jung to signal approbation upvote the original request above, instead of adding a new comment. Thanks.