support for informational schema in sql requests.

[bvt123]

The MySQL wire protocol is widely used nowadays in various software. It's great that Manticore supports it too.

But some "smart" products perform an additional query before doing anything to get information about tables and fields.

Among such products are development tools (DataGrip) and DBMS such as Clickhouse.

Clickhouse allows you to connect to other databases via the MySQL protocol, both for inserting and selecting data. It translate own SQL query to the remote DBMS dialect. However, it makes an additional request to informational schema of remote DBMS.

select * from mysql('127.0.0.1:9306','','wiki','qq','qq') where match('qq') ;

/ Fri Feb 11 09:56:37.139 2022 conn 3 / SELECT TABLE_NAME AS table_name, COLUMN_NAME AS column_name, COLUMN_TYPE AS column_type, IS_NULLABLE = 'YES' AS is_nullable, COLUMN_TYPE LIKE '%unsigned' AS is_unsigned, CHARACTER_MAXIMUM_LENGTH AS length, NUMERIC_PRECISION AS numeric_precision, IF(ISNULL(NUMERIC_SCALE), DATETIME_PRECISION, NUMERIC_SCALE) AS scale, COLUMN_COMMENT AS column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN ('wiki') ORDER BY ORDINAL_POSITION # error=sphinxql: syntax error, unexpected string, expecting FROM or ',' near ''%unsigned' AS is_unsigned, CHARACTER_MAXIMUM_LENGTH AS length, NUMERIC_PRECISION AS numeric_precision, IF(ISNULL(NUMERIC_SCALE), DATETIME_PRECISION, NUMERIC_SCALE) AS scale, COLUMN_COMMENT AS column_comment FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN ('wiki') ORDER BY ORDINAL_POSITION'

It would be convenient to resolve that issue to simplify the process of integration of Manticore and Clickhouse (and possibly other DBMS). It could be done according to the following scheme:

• The data coming to the Clickhouse is processed by batches through the common pipelines with Materialized View and inserted into the remote table with the engine mysql.
• The data is proxied towards the Manticore, and indexed there.
• When processing a search from a user, Clickhouse can join or subrequest data from both local and remote tables.

select title, content from wikipedia
where id in (select id from mysql('manticore.host:9306','database','wikipedia','user','pass') where match('search string') )

[tomatolog]

not quite clear do you want manticore implement dummy for that query and reply with ok message back or implement proper handling for such query?

[bvt123]

I think that proper handling is needed both for developer tools like pyCharm/DataGrip and DBMSes in proxy mode as Clickhouse.

[pavelnemirovsky]

Very useful request indeed, we had to develop our own middle-tier accessible via https://clickhouse.com/docs/en/engines/table-engines/special/url/ to be able to join data between ClickHouse and Manticore.

[virtadpt]

I'm going to say "plus one" as well, because pyCharm is catching on as an IDE, and some of our devs at work are using it to write code that interacts with Manticore.

[sanikolaev]

I've discussed this issue with the core team members and it looks like the only feasible way is to implement some kind of dictionary where:

• the key would be what some 3rd party (clickhouse, pycharm, mysqldump, whatever else) queries via mysql protocol
• the value would be what Manticore should return

Such a dictionary file would then be filled in by the user, but if it turns out there's some good default contents that works for most we can start distributing some default dictionary.

Otherwise it doesn't seem practical since Manticore just implements mysql protocol and provides somewhat similar SQL syntax, but it's of course not mysql at all and it would take us months to implement e.g. all the functionality of INFORMATION_SCHEMA.

What @pavelnemirovsky did (using Clickhouse URL table engine) also makes sense to me and may be a better way to go if the 3rd party allows it.

@virtadpt @pavelnemirovsky @bvt123 pls let me know what you think on the dictionary idea guys.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions. Feel free to re-open the issue in case it becomes actual.

[sanikolaev]

still actual