Setting the "defaultSchema" of a live CalciteConnection?

[Gavin Ray <ra...@gmail.com>]

I have a scenario in which I have a rootSchema that holds multiple
datasource schemas
There's a tool I'd like to integrate it with, but it can't handle  (catalog
-> schema -> schema) structure

I figured an easy way around this would be to iterate the names of the
datasource schemas, and then
and then make a clone of the current CalciteConnection with the
defaultSchema set to <db name>

I can't seem to figure out how to do this, though.

Calling .setSchema() doesn't seem to work (probably no impact on the JDBC
metadata)
and if I create a new connection with the "schema" property set, then it's
a fresh/empty Calcite source.

Is there any way to do this? Or some craft hack?

Thank you =)

Re: Setting the "defaultSchema" of a live CalciteConnection?

[Gavin Ray <ra...@gmail.com>]

Julian, tried what you said. It turns out that the schema WAS being set in
the Avatica driver, but it didn't seem to make any difference for the tool
(For what it's worth, it's SchemaCrawler --
https://github.com/schemacrawler/SchemaCrawler)

After trawling the Calcite source for hours and trying many things, thanks
to whoever wrote this ~10 lines of test code I was able to figure it out!
https://github.com/apache/calcite/blob/a8a6569e6ba75efe9d5725c49338a7f181d3ab5c/core/src/test/java/org/apache/calcite/test/MultiJdbcSchemaJoinTest.java#L106-L117

===============================================

Driver driver = new Driver();
CalciteJdbc41Factory factory = new CalciteJdbc41Factory();
SchemaPlus pgSchema = calciteConn.getRootSchema().getSubSchema("pg");
CalciteSchema pgRootSchema = CalciteSchema.createRootSchema(false, false,
"", pgSchema);

try (Connection scopedConnection =
             factory.newConnection(
                     driver, factory, "jdbc:calcite:",
                     new Properties(), pgRootSchema, null)) {
    CalciteConnection calciteScopedConn =
scopedConnection.unwrap(CalciteConnection.class);
    CalciteSchemaCrawler.crawlCalciteSchemas(calciteScopedConn);
}

On Tue, Mar 15, 2022 at 7:10 PM Julian Hyde <jh...@gmail.com> wrote:

> An ancestor project of Calcite, Eigenbase (aka Farrago) used to have a SET
> SCHEMA command. It would be nice if we added that back. (Maybe only in the
> “server” module, since in “core” the connections are stateless.)
>
> I’m a bit surprised that "CalciteConnection.setSchema(String)" doesn’t
> work. Put a breakpoint in the constructor of CalciteCatalogReader and see
> what is the value of schemaPaths. That is effectively the default schema
> for purposes of validating SQL statements.
>
> Julian
>
>
>
>
>
>
>
> > On Mar 15, 2022, at 11:49 AM, Gavin Ray <ra...@gmail.com> wrote:
> >
> > I have a scenario in which I have a rootSchema that holds multiple
> > datasource schemas
> > There's a tool I'd like to integrate it with, but it can't handle
> (catalog
> > -> schema -> schema) structure
> >
> > I figured an easy way around this would be to iterate the names of the
> > datasource schemas, and then
> > and then make a clone of the current CalciteConnection with the
> > defaultSchema set to <db name>
> >
> > I can't seem to figure out how to do this, though.
> >
> > Calling .setSchema() doesn't seem to work (probably no impact on the JDBC
> > metadata)
> > and if I create a new connection with the "schema" property set, then
> it's
> > a fresh/empty Calcite source.
> >
> > Is there any way to do this? Or some craft hack?
> >
> > Thank you =)
>
>

Re: Setting the "defaultSchema" of a live CalciteConnection?

[Julian Hyde <jh...@gmail.com>]

An ancestor project of Calcite, Eigenbase (aka Farrago) used to have a SET SCHEMA command. It would be nice if we added that back. (Maybe only in the “server” module, since in “core” the connections are stateless.)

I’m a bit surprised that "CalciteConnection.setSchema(String)" doesn’t work. Put a breakpoint in the constructor of CalciteCatalogReader and see what is the value of schemaPaths. That is effectively the default schema for purposes of validating SQL statements.

Julian







> On Mar 15, 2022, at 11:49 AM, Gavin Ray <ra...@gmail.com> wrote:
> 
> I have a scenario in which I have a rootSchema that holds multiple
> datasource schemas
> There's a tool I'd like to integrate it with, but it can't handle  (catalog
> -> schema -> schema) structure
> 
> I figured an easy way around this would be to iterate the names of the
> datasource schemas, and then
> and then make a clone of the current CalciteConnection with the
> defaultSchema set to <db name>
> 
> I can't seem to figure out how to do this, though.
> 
> Calling .setSchema() doesn't seem to work (probably no impact on the JDBC
> metadata)
> and if I create a new connection with the "schema" property set, then it's
> a fresh/empty Calcite source.
> 
> Is there any way to do this? Or some craft hack?
> 
> Thank you =)