Re: can we set a default schema for calcite connection to avoid writing schema names in sql

[xiaobo]

Hi Gavin,

              Can you share a complete example of using a FrameworkConfig 
object to open a calcite connection, thanks.

               SchemaPlus rootSchema = Frameworks.createRootSchema(true);
                Schema schema = new ReflectiveSchema(new HrSchema2(emps1));
                rootSchema.add("hr", schema);
                
                FrameworkConfig builder = Frameworks.newConfigBuilder()
                .defaultSchema(rootSchema.getSubSchema("hr"))
                .parserConfig(SqlParser.config().withCaseSensitive(false))
                .build();




------------------ Original ------------------
From:  "Gavin Ray";<ray.gavi...@gmail.com>;
Send time: Sunday, Feb 13, 2022 10:25 AM
To: "dev"<dev@calcite.apache.org>; 

Subject:  Re: can we set a default schema for calcite connection to avoid 
writing schema names in sql



You can create an empty root schema, add a ReflectiveSchema, and then set
this as the default schema:

val rootSchema: SchemaPlus = Frameworks.createRootSchema(true)
val hrReflectiveSchema = ReflectiveSchema(HrSchema())
rootSchema.add("hr", hrReflectiveSchema)

Frameworks.newConfigBuilder()
    .defaultSchema(rootSchema.getSubSchema("hr"))

On Sat, Feb 12, 2022 at 9:14 PM xiaobo <guxiaobo1...@qq.com.invalid> wrote:

> Hi Gavin,
> Thanks for your help,
>
> the defaultSchema(SchemaPlus defaultSchema) method need a SchemaPlus which
> only can be get after a connection is opened, but we want to set a target
> subschema such as RelfectiveShcema to be the default one, and we guess the
> default schema setting operation should be done before the connection is
> opened.
>
>
>
> ------------------ Original ------------------
> From:  "Gavin Ray";<ray.gavi...@gmail.com>;
> Send time: Sunday, Feb 13, 2022 1:43 AM
> To: "dev"<dev@calcite.apache.org>;
>
> Subject:  Re: can we set a default schema for calcite connection to avoid
> writing schema names in sql
>
>
>
> Hey Xiabo,
>
> You can do this, however it is easiest to do from the "FrameworkConfig"
> object, like this:
>
> import org.apache.calcite.tools.FrameworkConfig
> // Need to set case-sensitive to false, or else it tries to
> // look up capitalized table names and fails
> //
> // IE: "EMPS" instead of "emps"
> val frameworkConfig: FrameworkConfig = Frameworks.newConfigBuilder()
>  .defaultSchema(connection.rootSchema)
>  .parserConfig(SqlParser.config().withCaseSensitive(false))
>  .build()
>
> Hope this helps =)
>
> On Fri, Feb 11, 2022 at 9:09 PM xiaobo <guxiaobo1...@qq.com.invalid>
> wrote:
>
> > sorry for the html escape characters,
> > we tried the following and it does not work
> >
> >         Class.forName("org.apache.calcite.jdbc.Driver");
> >                 Properties info = new Properties();
> >                 info.setProperty("lex", "JAVA");
> >                 info.setProperty(InternalProperty.CASE_SENSITIVE.name(),
> > "false");
> >                 info.setProperty("defaultSchema", "hr");
> >                 Connection connection =
> > DriverManager.getConnection("jdbc:calcite:", info);
> >                 CalciteConnection conn =
> > connection.unwrap(CalciteConnection.class);
> >                 SchemaPlus rootSchema = conn.getRootSchema();
> >                 Schema schema = new ReflectiveSchema(target);
> >                 rootSchema.add(schemaName, schema);
> >                 return conn;
> >
> >
> >
> >
> > ------------------ Original ------------------
> > From:  "xiaobo ";<guxiaobo1...@qq.com.INVALID>;
> > Send time: Friday, Feb 11, 2022 11:20 PM
> > To: "dev"<dev@calcite.apache.org>;
> >
> > Subject:  can we set a default schema for calcite connection to avoid
> > writing schema names in sql
> >
> >
> >
> > we have tried the following and it does not work
> >
> >
> > Class.forName("org.apache.calcite.jdbc.Driver");
> > &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; Properties info
> =
> > new Properties();
> > &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;
> > info.setProperty("lex", "JAVA");
> > &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;
> > info.setProperty(CalciteConnectionProperty.CASE_SENSITIVE.camelName(),
> > "false");
> > &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;
> > info.setProperty("defaultSchema", "hr");
> > &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; try {
> > &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;
> > &nbsp;&nbsp;&nbsp; Connection connection =
> > &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;
> > &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;
> > DriverManager.getConnection("jdbc:calcite:", info);
> > &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;
> > &nbsp;&nbsp;&nbsp; CalciteConnection conn =
> > &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;
> > &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;
> > connection.unwrap(CalciteConnection.class);
> > &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;
> > &nbsp;&nbsp;&nbsp; SchemaPlus rootSchema = conn.getRootSchema();
> > &nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;