Superset can not connect to Multiple Oracle Autonomous Data Warehouse

cdmikechen commented 2 years ago

Superset use cx_Oracle to connect oracle. If two or more different Oracle autonomous databases are connected, the default Oracle configuration will conflict, resulting in failure.

How to reproduce the bug

I created two Oracle Autonomous Database in Oracle Cloud and download two Wallet.zip.
I unzip one Wallet.zip to ${ORACLE_CLIENT_HOME}/network/admin and set TNS_ADMIN, superset can connect to oracle with sqlalchemy url like oracle+cx_oracle://xxxx:xxxx@tns_name
But if I unzip the new Wallet.zip, the first Oracle connection will fail.

Expected results

Superset can connect to multiple Oracle autonomous database at same time.

Actual results

I must to upload and unzip wallet files to superset server by my self, superset does not have a function to upload database dependent files.

Environment

(please complete the following information):

browser type and version: Edge 93.0.961.44
superset version: 1.0.1
python version: 3.8.9
node.js version: v17.0.1
any feature flags active: no

Checklist

Make sure to follow these steps before submitting your issue - thank you!

[ ] I have checked the superset logs for python stacktraces and included it here as text if there are any.
[x] I have reproduced the issue with at least the latest released version of superset.
[x] I have checked the issue tracker for the same issue and I haven't found one similar.

Additional context

I have used DB_CONNECTION_MUTATOR to handle this multi connection situation, but I can't solve the problem of uploading wallet files. In the case of stand-alone deployment, this method can also be solved by manually uploading files. However, in the case of k8s deployment, superset itself may need to add the function of uploading database related files on the database configuration page!

import cx_Oracle
import tempfile
from distutils.dir_util import copy_tree
from sqlalchemy.engine.url import make_url

class Connection(cx_Oracle.Connection):
    # 参考代码 https://github.com/oracle/python-cx_Oracle/issues/485#issuecomment-729843502
    # https://blogs.oracle.com/opal/post/connecting-to-oracle-cloud-autonomous-database-through-sqlalchemy

    def __init__(self, *args, wallet_location=None, **kwargs):
        self.temp_dir = None
        self.tns_entries = {}
        if wallet_location is not None:
            self._setup_wallet_location(wallet_location)
            if len(args) > 2:
                dsn = args[2]
            else:
                dsn = kwargs.get("dsn")
            if dsn in self.tns_entries:
                dsn = self.tns_entries[dsn]
                if len(args) > 2:
                    args = args[:2] + (dsn,) + args[3:]
                else:
                    kwargs = kwargs.copy()
                    kwargs["dsn"] = dsn
        super(Connection, self).__init__(*args, **kwargs)

    def __del__(self):
        if self.temp_dir is not None:
            self.temp_dir.cleanup()

    def _setup_wallet_location(self, wallet_location):

        self.temp_dir = tempfile.TemporaryDirectory()
        copy_tree(wallet_location, self.temp_dir.name)

        # parse tnsnames.ora to get list of entries and modify them to include
        # the wallet location
        fname = os.path.join(wallet_location, "tnsnames.ora")
        for line in open(fname):
            pos = line.find(" = ")
            if pos < 0:
                continue
            name = line[:pos]
            entry = line[pos + 3:].strip()
            key_phrase = "(security="
            pos = entry.find(key_phrase) + len(key_phrase)
            wallet_entry = "(MY_WALLET_DIRECTORY=%s)" % self.temp_dir.name
            entry = entry[:pos] + wallet_entry + entry[pos:]
            self.tns_entries[name] = entry

def DB_CONNECTION_MUTATOR(uri, params, username, security_manager, source):
    # 这里需要处理判断一下 oracle存在wallet的情况
    if uri.drivername == "oracle+cx_oracle":
        if uri.query is not None and "wallet_location" in uri.query:
            username, password, dsn = uri.username, uri.password, uri.host
            wallet_location = uri.query["wallet_location"]
            params.update(
                {'creator': lambda: Connection(username, password, dsn,
                                               wallet_location=wallet_location)})
            uri = make_url("oracle://")
    return uri, params

stale[bot] commented 2 years ago

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. For admin, please label this issue .pinned to prevent stale bot from closing the issue.

cdmikechen commented 2 years ago

@stale No, it still a bug

rusackas commented 1 year ago

Can anyone confirm if this is still a problem, or better yet... look into it if it is?

rusackas commented 6 months ago

Closing this as stale since it's been silent for so long, and we're trying to steer toward a more actionable Issues backlog. If people are still encountering this in current versions (currently 3.x) please re-open this issue, open a new Issue with updated context, or raise a PR to address the problem. Thanks!

apache / superset