Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-13136

enhance CREATE SERVER MyServerName FOREIGN DATA WRAPPER to work with CONNECT engine

    XMLWordPrintable

    Details

      Description

      The idea is to enhance the existing CREATE SERVER command to work better with CONNECT engine JDBC and other future extensions & improvements to CONNECT.

      Proposed syntax:

      CREATE SERVER MyServerName FOREIGN DATA WRAPPER ConnectEngine
      OPTIONS (URL 'jdbc:sqlserver://blah/blah?key=val;', 
                        USER 'username', 
                        PASSWORD 'mypass', 
                        OTHER 'table_type=jdbc;dbcp=1;memory=3;quote=1;') 
      

      By having the URL as its own field you eliminate complicated escaping, and by putting all the CONNECT specific options in a single OTHER you have an easy to understand syntax.

      Some notes on the above:

      • For our needs, CREATE SERVER really needs to not require SUPER priv and be a DATABASE level priv so would need to be able to grant something like this:

        GRANT CREATE SERVER, DROP SERVER on dbname.* to X
        

      • for our purposes, the drop server permission could automatically go with create server; we would mostly use CREATE OR REPLACE SERVER x .... and DROP SERVER x
      • The URL string size limit needs to handle URLs with inline .pem SSL data, so 2k minimum but better if 6k or TEXT datatype to allow for other SSL files in future being passed in the URL itself
      • in the above example I have "dbcp=1" as a synonym for wrapper=ApacheWrapper as I see this as the standard db connection pooling solution for connect jdbc - works great, no special driver needed.

      Being able to issue a command like CREATE TABLE .... ENGINE=CONNECT SERVER=MyServerName means that we could change passwords, turn pooling on-off, edit the connection params, replace ssl files, etc without having to recreate each of the connect tables (customers have dozens).

      This would be a REALLY great enhancement!

        Attachments

          Activity

            People

            Assignee:
            bertrandop Olivier Bertrand
            Reporter:
            rdyas Robert Dyas
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: