[MDEV-33388] Dedicated table options for Spider+ODBC Created: 2020-05-28  Updated: 2024-02-05

Status: Open
Project: MariaDB Server
Component/s: Storage Engine - Spider
Fix Version/s: 11.6

Type: New Feature Priority: Critical
Reporter: Alexander Barkov Assignee: Yuchen Pei
Resolution: Unresolved Votes: 1
Labels: None

Issue Links:
Blocks
is blocked by MDEV-28856 Spider: Implement more engine-defined... Closed
Relates
relates to MDEV-5271 Support engine-defined attributes per... Closed
relates to MDEV-28861 Spider: Deprecate table options by CO... Closed
relates to MDEV-27106 Spider: specify connection to data no... Closed

 Description   

It would be nice to change the way how Spider ODBC tables are defined, from:

CREATE TABLE t1 (
 
  a INT NOT NULL
 
) ENGINE=SPIDER COMMENT='dsn "dsn", user "usr", password "pwd", table "t1", wrapper "odbc"';

to:

CREATE TABLE t1 (
 
 a INT NOT NULL
 
) ENGINE=SPIDER WRAPPER=ODBC TABNANE='t1' CONNECTION='DSN=dsn;UID=usr;PWD=pwd';

i.e. move the connection information from COMMENT to separate table options:

  • WRAPPER - with TABLE_TYPE as an alias, for ConnectSE compatibility
  • TABNAME - the remote table name. In case of remote databases that support schemas (e.g. PostgreSQL, Oracle), in addition to non-qualified names, e.g.: TABNAME='t1', it should also support qualified names, e.g. TABNAME='public.t1'.
  • CONNECTION - in the format of ODBC's SQLDriverConnect. This format will allow to pass any kind of attributes (not only DSN, UID and PWD). For example, in case of SQLite, it can be: 

    CONNECTION='Driver=SQLite3 ODBC Driver;Database=/tmp/test.sqlite3;NoWCHAR=yes'

Reasoning:

  • Easier to migrate from ConnectSE. User will need just to fix ENGINE=Connect to ENGINE=Spider.
  • Users might want to have "real" comments on their tables, e.g.: 

    CREATE TABLE t1 (
    		 
     a INT NOT NULL
    		 
    ) ....
    		 
    COMMENT='This is my favourite ODBC table';

  • More flexible about database-specific parameters (like Database in case of SQLite).
  • This is the standard way to pass parameters to engines in MariaDB.

 Comments   
Comment by Geoff Montee (Inactive) [ 2020-06-09 ]

This seems to be a duplicate of MENT-763.

Comment by Alexander Barkov [ 2020-06-11 ]

This task is specifically about ODBC tables.
ralf.gebhardt@mariadb.com, do you think we should close this one as duplicate?

Comment by Alexander Barkov [ 2020-06-15 ]

ralf.gebhardt@mariadb.com, I'm ok with "is a part". Feel free to update.

Comment by Yuchen Pei [ 2023-07-25 ]

ralf.gebhardt thanks for the comment and suggestions.

> About TABNAME

> With MDEV-27106 we have REMOTE_TABLE as table option, so this is
> already done. Does it also allow qualified names?

I don't think so. Spider constructs a qualified table name from the
database name and table name. If database is not specified (in
CREATE SERVER, REMOTE_DATABASE, COMMENT="database ..."
etc.), it will default to the same database name as the spider node.

> 1) add table option WRAPPER to MDEV-28856 and would get MDEV-28856 done

ok

> 2) we would create a MDEV to allow CONNECTION also to be in the
> ODBC's SQLDriverConnect format or the format to be interpreted
> differently for wrapper odbc or we need to create a MENT for table
> option ODBC_CONNECTION

Given spider odbc is an ES-only feature, it doesn't make sense to
update CONNECTION in CS to allow the ODBC format. OTOH there's no
virtual classes involved in parsing the connection parameters, so it
is not obvious how to implement this to be ES-only. I'll need to look
more into the implementation of ES-only features to decide the best
way to implement this. If there's no clean way of doing this then
ODBC_CONNECTION could be a solution.

> 3) we get MDEV-28861 done to deprecate spider options in COMMENT

ok.

Comment by Yuchen Pei [ 2023-09-01 ]

After having worked on MDEV-28856, I think it is better to implement
in CS CONNECTION string parsing with SQLDriverConnect format when
wrapper is odbc. We have already been including odbc options in CS,
there's no precedence of parsing different table options between CS
and ES, and WRAPPER=odbc will guard the implementation. It should be
done after MDEV-31146 - I just created MDEV-32066, which was moved to
MDEV-32627).

Generated at Thu Feb 08 10:38:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.