Connector - PostgreSQL - no auto-completion, boolean value not displayed

[spo-ijaz]

We should check this connector to be sure that :

• auto-completion is working as expected ;
• database structure is correctly displayed ;
• boolean value are correctly displayed in the Table view.

[PhilippeAccorsi]

If it can help to better understand what I say on linuxfr ;)

About boolean value:

• From psql (12.18 (Ubuntu 12.18-0ubuntu0.20.04.1)):

select user_id,is_active,is_deleted,auth_type from users;

user_id	is_active	is_deleted	auth_type
329	f	f	INTERNAL
224	f	f	INTERNAL
297	t	f	INTERNAL
307	t	f	INTERNAL
334	t	f	INTERNAL
240	f	f	INTERNAL

• From ROOTDB (docker):

About auto-completion:

auto-completion_not.purpose.mp4

[PhilippeAccorsi]

And if I open console view, nothing visible in the left bar, and if I click on refresh button, I can seen this error message:

Unable to load this connector.
SQLSTATE[42883]: Undefined function: 7 ERROR: function group_concat(information_schema.sql_identifier) does not exist LINE 2: SELECT GROUP_CONCAT(kc.column_name) AS colum... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

And If I try to execute sql request, I can see this message:

Une erreur s'est produite.
Error: You must install at least one postgresql-client- package

[gyan000]

Ok, thanks for the feedback.
For log, seeing your last comment with :Error: You must install at least one postgresql-client- package - means that it miss at least one postgres package in the docker image…

[spo-ijaz]

Hi @PhilippeAccorsi,

I rebuilt latest rootdbapp/rootdb image with updated rootdbapp/rootdb-nginx-php-fpm-supervisor image, now containing the missing package (also updated to php 8.2.18) and I made a test with prod image.

Just for log, it should works like this :
https://github.com/RootDBApp/api/assets/130065059/a1132d1e-b37f-498b-9cbf-3f6acc3d23ea

Some notes :

• after a connector is added, there's this issue where the connector tests made when we refresh the listing is not working properly, you need to switch admin tabs to make it works.. ;
• if all is OK with the connector, you should see a list of all the tables your postgre user has access ;
• I reproduced the SQL error you got, and here we clearly need to improve the user feedback because it took time to understand what happened here: my test postgre database was empty (in my case, but it can be another issue, like grants issue) - so there was nothing in the database (when checking in cli with \dt command), no tables, no views, hence this error when generating database structure for auto-completion & co. The query used to get all info fails and that's normal

So, if you have no tables listed in the connector configuration form, auto-completion & co won't work.

Finally, I was also able to reproduce this error, with a real prod postgre database - credentials are ok, tables & view are ok, but I can't fetch the database structure, and get the same sql error.
So when this will get fixed, I think it will be ok.

[spo-ijaz]

Hi @PhilippeAccorsi ,

When you will have some time, you can test the rootdbapp/rootdb:dev image containing some fixes for this connector :

• in the Connector form, tables available for your postgres user should be visible, with associated grants ;
• auto-completion should also work.

About the boolean values not displayed, which is normal since the PHP module return real boolean values, it will be fixed on the frontend side: frontend - #14
As a quick workaroud, you can use something like that :

SELECT *, CASE WHEN boolean_column THEN 't' ELSE 'f' END AS boolean_column  FROM a_table;

[PhilippeAccorsi]

Thanks for the answer. I think I can test that next week :)

[PhilippeAccorsi]

I have tested with dev docker image and I can seen all table on the left bar in the console view \o/

But I don't have autocompletion in console form. If I write "select * from us" i not seen users in list, but users table exist. If I write "select * from users" i have the correct result. If I doubleclick on the left, I can seen results.

autocompletion_not_work.mp4

Regards,

[spo-ijaz]

Just to be sure before I investigate further, did you use CTRL+SPACE to trigger the auto-completion ?

[PhilippeAccorsi]

I have tested with CTRL+SPACE but this not change anything in the list.
I have tested to click on "reload" button (blue button) and now its work. No tooltip on this button.

I don't know if I make something wrong in the first test or if something wrong after first connection to the database (double click works to execute request so I not really understand why reload is necessary).

:)

[spo-ijaz]

Ok, thanks a lot for the update.

You did nothing wrong, here what I suspect:

1. your connector was already configured ;
2. you updated to latest dev image, restarted containers & co ;
3. you opened the Console tab ;
4. here, only a refresh of the schema tree cache is done, when the cache is empty - that's the issue I think ;
5. no auto-completion refresh from API side was executed ;
6. then you clicked on the refresh button ( where I'll add a tooltip :) ) and ...
7. ... here, a refresh of the schema tree and auto-completion cache is done, thus explaining why you got auto-completion after.

So, yep :

• we should always perform an auto-completion cache refresh when the schema tree cache is empty - which make sense ;
• and add an tiny tooltip over the refresh button to explain what is performed when clicked.

[spo-ijaz]

I think the auto-completes refresh issue is fixed.

For log it's not what I think it was initially: it's just that the local storage of the web browser already got an empty array of auto-completes. Now we check if this array is emply or not when the CustomEditor is displayed and perform a auto-completes refresh if needed.

[spo-ijaz]

Hi @PhilippeAccorsi,

Just to let you know that boolean values shoud be displayed as the CLI stdout (t / f) when using a postrgre connector. rootdbapp/rootdb:dev image is updated - if you want to check before the next release.