You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by xiaobo <gu...@qq.com.INVALID> on 2022/02/11 15:20:48 UTC
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");
Properties info = new Properties();
info.setProperty("lex", "JAVA");
info.setProperty(CalciteConnectionProperty.CASE_SENSITIVE.camelName(), "false");
info.setProperty("defaultSchema", "hr");
try {
Connection connection =
DriverManager.getConnection("jdbc:calcite:", info);
CalciteConnection conn =
connection.unwrap(CalciteConnection.class);
SchemaPlus rootSchema = conn.getRootSchema();
Re: can we set a default schema for calcite connection to avoid writing schema names in sql
Posted by xiaobo <gu...@qq.com.INVALID>.
I tried the example with the following testing calcitedatabse class,
String sql = "select count(*) from emps";
Schema schema = new ReflectiveSchema(new HrSchema(emps1, depts1));
CalciteDatabase db = new CalciteDatabase(schema, "hr");
try {
Long l = db.exeGetLong(sql);
System.out.println("sql result " + l);
} catch (SQLException | ValidationException | SqlParseException | RelConversionException e) {
e.printStackTrace();
}
and the simple counting sql failed with :
java.sql.SQLException: exception while executing query: null
at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
at org.apache.calcite.avatica.AvaticaConnection.executeQueryInternal(AvaticaConnection.java:576)
at org.apache.calcite.avatica.AvaticaPreparedStatement.executeQuery(AvaticaPreparedStatement.java:137)
at com.xsmartware.common.calcite.CalciteDatabase.executeQuery(CalciteDatabase.java:77)
at com.xsmartware.common.calcite.CalciteDatabase.executeQuery(CalciteDatabase.java:84)
at com.xsmartware.common.calcite.CalciteDatabase.exeGetLong(CalciteDatabase.java:90)
at com.xsmartware.javatest.calcite.CalCiteTest.test6(CalCiteTest.java:149)
at com.xsmartware.javatest.calcite.CalCiteTest.run(CalCiteTest.java:107)
at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:758)
at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:748)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:309)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1301)
at org.springframework.boot.SpringApplication.run(SpringApplication.java:1290)
at com.xsmartware.javatest.JavaTestApplication.main(JavaTestApplication.java:9)
Caused by: java.lang.NullPointerException
at Baz.bind(Unknown Source)
at org.apache.calcite.jdbc.CalcitePrepare$CalciteSignature.enumerable(CalcitePrepare.java:363)
at org.apache.calcite.jdbc.CalciteConnectionImpl.enumerable(CalciteConnectionImpl.java:338)
at org.apache.calcite.jdbc.CalciteMetaImpl._createIterable(CalciteMetaImpl.java:578)
at org.apache.calcite.jdbc.CalciteMetaImpl.createIterable(CalciteMetaImpl.java:569)
at org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:184)
at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:64)
at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:43)
at org.apache.calcite.avatica.AvaticaConnection.executeQueryInternal(AvaticaConnection.java:572)
... 12 more
public class CalciteDatabase {
static {
try {
Class.forName("org.apache.calcite.jdbc.Driver");
}catch(Throwable t ) {
throw new RuntimeException(t);
}
}
private final CalciteConnection conn;
private final FrameworkConfig config;
public CalciteDatabase(Schema schema, String schemaName)
throws SQLException {
conn = openConnection();
SchemaPlus rootSchema = Frameworks.createRootSchema(true);
rootSchema.add(schemaName, schema);
config = Frameworks.newConfigBuilder()
.defaultSchema(rootSchema.getSubSchema(schemaName))
.parserConfig(SqlParser.config().withCaseSensitive(false))
.build();
}
public SchemaPlus getRootSchema() {
return config.getDefaultSchema().getParentSchema();
}
public SchemaPlus getDefaultSchema() {
return config.getDefaultSchema();
}
private CalciteConnection openConnection() throws SQLException{
Properties info = new Properties();
info.setProperty("lex", "JAVA");
Connection connection = DriverManager.getConnection("jdbc:calcite:", info);
CalciteConnection conn = connection.unwrap(CalciteConnection.class);
return conn;
}
public ResultSet executeQuery(RelNode relRoot) throws SQLException {
relRoot.getCluster().getPlanner().setRoot(
relRoot.getCluster().getPlanner().changeTraits(
relRoot,
relRoot.getCluster().traitSet().replace(EnumerableConvention.INSTANCE)));
final RelNode bestExp = relRoot.getCluster().getPlanner().findBestExp();
final RelRunner runner = conn.unwrap(RelRunner.class);
try (PreparedStatement statement = runner.prepareStatement(bestExp)) {
return statement.executeQuery();
}
}
public ResultSet executeQuery(String sql)
throws SQLException, ValidationException,
SqlParseException, RelConversionException {
return executeQuery(CalciteUtils.parseSql(sql, config));
}
public Long exeGetLong(String sql)
throws SQLException, ValidationException,
SqlParseException, RelConversionException {
ResultSet rs = executeQuery(sql);
Long ret = null;
if(rs.next()) {
ret = rs.getLong(1);
}
rs.close();
return ret;
}
}
------------------ Original ------------------
From: "Gavin Ray";<ra...@gmail.com>;
Send time: Saturday, Feb 19, 2022 0:37 AM
To: "dev"<de...@calcite.apache.org>;
Subject: Re: can we set a default schema for calcite connection to avoid writing schema names in sql
Sure
Here is a singleton object that uses FrameworkConfig in Kotlin to manage a
single master RootSchema + CalciteConnection:
https://github.com/GavinRay97/GraphQLCalcite/blob/e4ba2426edb546bda9bd5bd87a61764850138348/src/main/kotlin/CalciteSchemaManager.kt#L23-L117
And here is the same in Java:
https://github.com/GavinRay97/GraphQLCalcite/blob/1070d179b67d803f05975cf416c392b010823069/src/main/java/com/example/calcitewrappers/DatabaseManager.java#L22-L60
Hope this is helpful =)
On Fri, Feb 18, 2022 at 10:06 AM xiaobo <gu...@qq.com.invalid> wrote:
> 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";<ra...@gmail.com>;
> Send time: Sunday, Feb 13, 2022 10:25 AM
> To: "dev"<de...@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 <gu...@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";<ra...@gmail.com>;
> > Send time: Sunday, Feb 13, 2022 1:43 AM
> > To: "dev"<de...@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 <gu...@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 ";<gu...@qq.com.INVALID>;
> > > Send time: Friday, Feb 11, 2022 11:20 PM
> > > To: "dev"<de...@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");
> > > Properties
> info
> > =
> > > new Properties();
> > >
> > > info.setProperty("lex", "JAVA");
> > >
> > > info.setProperty(CalciteConnectionProperty.CASE_SENSITIVE.camelName(),
> > > "false");
> > >
> > > info.setProperty("defaultSchema", "hr");
> > > try {
> > >
> > > Connection connection =
> > >
> > >
> > > DriverManager.getConnection("jdbc:calcite:", info);
> > >
> > > CalciteConnection conn =
> > >
> > >
> > > connection.unwrap(CalciteConnection.class);
> > >
> > > SchemaPlus rootSchema = conn.getRootSchema();
> > >
Re: can we set a default schema for calcite connection to avoid writing schema names in sql
Posted by xiaobo <gu...@qq.com.INVALID>.
Thanks Gavin,
From the Java example it seems FrameworkConfig can't be attached to calciteconnection something like passing a Property object when opening the connection, and in order to make FrameworkConfig work, we must use the relation related APIs of calcite(like the executeQuery(RelNode relRoot) method), but can't use the JDBC APIs of calcite connection, are we right?
------------------ Original ------------------
From: "Gavin Ray";<ra...@gmail.com>;
Send time: Saturday, Feb 19, 2022 0:37 AM
To: "dev"<de...@calcite.apache.org>;
Subject: Re: can we set a default schema for calcite connection to avoid writing schema names in sql
Sure
Here is a singleton object that uses FrameworkConfig in Kotlin to manage a
single master RootSchema + CalciteConnection:
https://github.com/GavinRay97/GraphQLCalcite/blob/e4ba2426edb546bda9bd5bd87a61764850138348/src/main/kotlin/CalciteSchemaManager.kt#L23-L117
And here is the same in Java:
https://github.com/GavinRay97/GraphQLCalcite/blob/1070d179b67d803f05975cf416c392b010823069/src/main/java/com/example/calcitewrappers/DatabaseManager.java#L22-L60
Hope this is helpful =)
On Fri, Feb 18, 2022 at 10:06 AM xiaobo <gu...@qq.com.invalid> wrote:
> 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";<ra...@gmail.com>;
> Send time: Sunday, Feb 13, 2022 10:25 AM
> To: "dev"<de...@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 <gu...@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";<ra...@gmail.com>;
> > Send time: Sunday, Feb 13, 2022 1:43 AM
> > To: "dev"<de...@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 <gu...@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 ";<gu...@qq.com.INVALID>;
> > > Send time: Friday, Feb 11, 2022 11:20 PM
> > > To: "dev"<de...@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");
> > > Properties
> info
> > =
> > > new Properties();
> > >
> > > info.setProperty("lex", "JAVA");
> > >
> > > info.setProperty(CalciteConnectionProperty.CASE_SENSITIVE.camelName(),
> > > "false");
> > >
> > > info.setProperty("defaultSchema", "hr");
> > > try {
> > >
> > > Connection connection =
> > >
> > >
> > > DriverManager.getConnection("jdbc:calcite:", info);
> > >
> > > CalciteConnection conn =
> > >
> > >
> > > connection.unwrap(CalciteConnection.class);
> > >
> > > SchemaPlus rootSchema = conn.getRootSchema();
> > >
Re: can we set a default schema for calcite connection to avoid writing schema names in sql
Posted by xiaobo <gu...@qq.com.INVALID>.
Gavin, thank you very much.
My example works too now, the key point is we must attache the same schema to both
calciteconnection using conn.getRootSchema().addSubSchema() and FrameworkConfig using .defaultSchema() method.
And we think that if a defaultSchema parameter for the connection string takes the work, it will be much easier.
------------------ Original ------------------
From: "Gavin Ray";<ra...@gmail.com>;
Send time: Sunday, Feb 20, 2022 0:59 AM
To: "dev"<de...@calcite.apache.org>;
Subject: Re: can we set a default schema for calcite connection to avoid writing schema names in sql
Here you go:
https://gist.github.com/GavinRay97/ffb97088b895389770b7a5d42dc713e9
I have tried to comment on it as best I could.
The important part is what you pass as the default schema name to
"getFrameworkConfig()"
This sets the query namespace when you make queries.
I wrote two examples -- one of setting "HR" as default schema, so that the
query is just:
"SELECT * FROM emps"
And another using the rootSchema, so that it is namespaced under "hr.emps"
On Sat, Feb 19, 2022 at 10:26 AM Gavin Ray <ra...@gmail.com> wrote:
> I will write full working implementation of ReflectiveSchema and "select
> count(*) from emps"; query execution to help you
> Give me one moment please
>
> On Sat, Feb 19, 2022 at 8:52 AM xiaobo <gu...@qq.com.invalid>
> wrote:
>
>> Hi,
>>
>> There is a documented "schema" parameter for the jdbc connection string
>> parameters at page:
>> https://calcite.apache.org/docs/adapter.html
>> the question is : is a init schema the default schema, if not , can you
>> add a defaultSchema paramter for the jdbc connection string?
>>
>> Regards
>>
>>
>>
>>
>>
>> ------------------ Original ------------------
>> From: "Gavin Ray";<ra...@gmail.com>;
>> Send time: Saturday, Feb 19, 2022 0:37 AM
>> To: "dev"<de...@calcite.apache.org>;
>>
>> Subject: Re: can we set a default schema for calcite connection to avoid
>> writing schema names in sql
>>
>>
>>
>> Sure
>>
>> Here is a singleton object that uses FrameworkConfig in Kotlin to manage a
>> single master RootSchema + CalciteConnection:
>>
>> https://github.com/GavinRay97/GraphQLCalcite/blob/e4ba2426edb546bda9bd5bd87a61764850138348/src/main/kotlin/CalciteSchemaManager.kt#L23-L117
>>
>> And here is the same in Java:
>>
>> https://github.com/GavinRay97/GraphQLCalcite/blob/1070d179b67d803f05975cf416c392b010823069/src/main/java/com/example/calcitewrappers/DatabaseManager.java#L22-L60
>>
>> Hope this is helpful =)
>>
>> On Fri, Feb 18, 2022 at 10:06 AM xiaobo <gu...@qq.com.invalid>
>> wrote:
>>
>> > 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";<ra...@gmail.com>;
>> > Send time: Sunday, Feb 13, 2022 10:25 AM
>> > To: "dev"<de...@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 <gu...@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";<ra...@gmail.com>;
>> > > Send time: Sunday, Feb 13, 2022 1:43 AM
>> > > To: "dev"<de...@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 <gu...@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 ";<gu...@qq.com.INVALID>;
>> > > > Send time: Friday, Feb 11, 2022 11:20 PM
>> > > > To: "dev"<de...@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");
>> > > > Properties
>> > info
>> > > =
>> > > > new Properties();
>> > > >
>> > > > info.setProperty("lex", "JAVA");
>> > > >
>> > > >
>> info.setProperty(CalciteConnectionProperty.CASE_SENSITIVE.camelName(),
>> > > > "false");
>> > > >
>> > > > info.setProperty("defaultSchema", "hr");
>> > > > try {
>> > > >
>> > > > Connection connection =
>> > > >
>> > > >
>> > > > DriverManager.getConnection("jdbc:calcite:", info);
>> > > >
>> > > > CalciteConnection conn =
>> > > >
>> > > >
>> > > > connection.unwrap(CalciteConnection.class);
>> > > >
>> > > > SchemaPlus rootSchema = conn.getRootSchema();
>> > > >
>
>
Re: can we set a default schema for calcite connection to avoid writing schema names in sql
Posted by Gavin Ray <ra...@gmail.com>.
Here you go:
https://gist.github.com/GavinRay97/ffb97088b895389770b7a5d42dc713e9
I have tried to comment on it as best I could.
The important part is what you pass as the default schema name to
"getFrameworkConfig()"
This sets the query namespace when you make queries.
I wrote two examples -- one of setting "HR" as default schema, so that the
query is just:
"SELECT * FROM emps"
And another using the rootSchema, so that it is namespaced under "hr.emps"
On Sat, Feb 19, 2022 at 10:26 AM Gavin Ray <ra...@gmail.com> wrote:
> I will write full working implementation of ReflectiveSchema and "select
> count(*) from emps"; query execution to help you
> Give me one moment please
>
> On Sat, Feb 19, 2022 at 8:52 AM xiaobo <gu...@qq.com.invalid>
> wrote:
>
>> Hi,
>>
>> There is a documented "schema" parameter for the jdbc connection string
>> parameters at page:
>> https://calcite.apache.org/docs/adapter.html
>> the question is : is a init schema the default schema, if not , can you
>> add a defaultSchema paramter for the jdbc connection string?
>>
>> Regards
>>
>>
>>
>>
>>
>> ------------------ Original ------------------
>> From: "Gavin Ray";<ra...@gmail.com>;
>> Send time: Saturday, Feb 19, 2022 0:37 AM
>> To: "dev"<de...@calcite.apache.org>;
>>
>> Subject: Re: can we set a default schema for calcite connection to avoid
>> writing schema names in sql
>>
>>
>>
>> Sure
>>
>> Here is a singleton object that uses FrameworkConfig in Kotlin to manage a
>> single master RootSchema + CalciteConnection:
>>
>> https://github.com/GavinRay97/GraphQLCalcite/blob/e4ba2426edb546bda9bd5bd87a61764850138348/src/main/kotlin/CalciteSchemaManager.kt#L23-L117
>>
>> And here is the same in Java:
>>
>> https://github.com/GavinRay97/GraphQLCalcite/blob/1070d179b67d803f05975cf416c392b010823069/src/main/java/com/example/calcitewrappers/DatabaseManager.java#L22-L60
>>
>> Hope this is helpful =)
>>
>> On Fri, Feb 18, 2022 at 10:06 AM xiaobo <gu...@qq.com.invalid>
>> wrote:
>>
>> > 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";<ra...@gmail.com>;
>> > Send time: Sunday, Feb 13, 2022 10:25 AM
>> > To: "dev"<de...@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 <gu...@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";<ra...@gmail.com>;
>> > > Send time: Sunday, Feb 13, 2022 1:43 AM
>> > > To: "dev"<de...@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 <gu...@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 ";<gu...@qq.com.INVALID>;
>> > > > Send time: Friday, Feb 11, 2022 11:20 PM
>> > > > To: "dev"<de...@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");
>> > > > Properties
>> > info
>> > > =
>> > > > new Properties();
>> > > >
>> > > > info.setProperty("lex", "JAVA");
>> > > >
>> > > >
>> info.setProperty(CalciteConnectionProperty.CASE_SENSITIVE.camelName(),
>> > > > "false");
>> > > >
>> > > > info.setProperty("defaultSchema", "hr");
>> > > > try {
>> > > >
>> > > > Connection connection =
>> > > >
>> > > >
>> > > > DriverManager.getConnection("jdbc:calcite:", info);
>> > > >
>> > > > CalciteConnection conn =
>> > > >
>> > > >
>> > > > connection.unwrap(CalciteConnection.class);
>> > > >
>> > > > SchemaPlus rootSchema = conn.getRootSchema();
>> > > >
>
>
Re: can we set a default schema for calcite connection to avoid writing schema names in sql
Posted by Gavin Ray <ra...@gmail.com>.
I will write full working implementation of ReflectiveSchema and "select
count(*) from emps"; query execution to help you
Give me one moment please
On Sat, Feb 19, 2022 at 8:52 AM xiaobo <gu...@qq.com.invalid> wrote:
> Hi,
>
> There is a documented "schema" parameter for the jdbc connection string
> parameters at page:
> https://calcite.apache.org/docs/adapter.html
> the question is : is a init schema the default schema, if not , can you
> add a defaultSchema paramter for the jdbc connection string?
>
> Regards
>
>
>
>
>
> ------------------ Original ------------------
> From: "Gavin Ray";<ra...@gmail.com>;
> Send time: Saturday, Feb 19, 2022 0:37 AM
> To: "dev"<de...@calcite.apache.org>;
>
> Subject: Re: can we set a default schema for calcite connection to avoid
> writing schema names in sql
>
>
>
> Sure
>
> Here is a singleton object that uses FrameworkConfig in Kotlin to manage a
> single master RootSchema + CalciteConnection:
>
> https://github.com/GavinRay97/GraphQLCalcite/blob/e4ba2426edb546bda9bd5bd87a61764850138348/src/main/kotlin/CalciteSchemaManager.kt#L23-L117
>
> And here is the same in Java:
>
> https://github.com/GavinRay97/GraphQLCalcite/blob/1070d179b67d803f05975cf416c392b010823069/src/main/java/com/example/calcitewrappers/DatabaseManager.java#L22-L60
>
> Hope this is helpful =)
>
> On Fri, Feb 18, 2022 at 10:06 AM xiaobo <gu...@qq.com.invalid>
> wrote:
>
> > 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";<ra...@gmail.com>;
> > Send time: Sunday, Feb 13, 2022 10:25 AM
> > To: "dev"<de...@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 <gu...@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";<ra...@gmail.com>;
> > > Send time: Sunday, Feb 13, 2022 1:43 AM
> > > To: "dev"<de...@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 <gu...@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 ";<gu...@qq.com.INVALID>;
> > > > Send time: Friday, Feb 11, 2022 11:20 PM
> > > > To: "dev"<de...@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");
> > > > Properties
> > info
> > > =
> > > > new Properties();
> > > >
> > > > info.setProperty("lex", "JAVA");
> > > >
> > > >
> info.setProperty(CalciteConnectionProperty.CASE_SENSITIVE.camelName(),
> > > > "false");
> > > >
> > > > info.setProperty("defaultSchema", "hr");
> > > > try {
> > > >
> > > > Connection connection =
> > > >
> > > >
> > > > DriverManager.getConnection("jdbc:calcite:", info);
> > > >
> > > > CalciteConnection conn =
> > > >
> > > >
> > > > connection.unwrap(CalciteConnection.class);
> > > >
> > > > SchemaPlus rootSchema = conn.getRootSchema();
> > > >
Re: can we set a default schema for calcite connection to avoid writing schema names in sql
Posted by xiaobo <gu...@qq.com.INVALID>.
Hi,
There is a documented "schema" parameter for the jdbc connection string parameters at page:
https://calcite.apache.org/docs/adapter.html
the question is : is a init schema the default schema, if not , can you add a defaultSchema paramter for the jdbc connection string?
Regards
------------------ Original ------------------
From: "Gavin Ray";<ra...@gmail.com>;
Send time: Saturday, Feb 19, 2022 0:37 AM
To: "dev"<de...@calcite.apache.org>;
Subject: Re: can we set a default schema for calcite connection to avoid writing schema names in sql
Sure
Here is a singleton object that uses FrameworkConfig in Kotlin to manage a
single master RootSchema + CalciteConnection:
https://github.com/GavinRay97/GraphQLCalcite/blob/e4ba2426edb546bda9bd5bd87a61764850138348/src/main/kotlin/CalciteSchemaManager.kt#L23-L117
And here is the same in Java:
https://github.com/GavinRay97/GraphQLCalcite/blob/1070d179b67d803f05975cf416c392b010823069/src/main/java/com/example/calcitewrappers/DatabaseManager.java#L22-L60
Hope this is helpful =)
On Fri, Feb 18, 2022 at 10:06 AM xiaobo <gu...@qq.com.invalid> wrote:
> 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";<ra...@gmail.com>;
> Send time: Sunday, Feb 13, 2022 10:25 AM
> To: "dev"<de...@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 <gu...@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";<ra...@gmail.com>;
> > Send time: Sunday, Feb 13, 2022 1:43 AM
> > To: "dev"<de...@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 <gu...@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 ";<gu...@qq.com.INVALID>;
> > > Send time: Friday, Feb 11, 2022 11:20 PM
> > > To: "dev"<de...@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");
> > > Properties
> info
> > =
> > > new Properties();
> > >
> > > info.setProperty("lex", "JAVA");
> > >
> > > info.setProperty(CalciteConnectionProperty.CASE_SENSITIVE.camelName(),
> > > "false");
> > >
> > > info.setProperty("defaultSchema", "hr");
> > > try {
> > >
> > > Connection connection =
> > >
> > >
> > > DriverManager.getConnection("jdbc:calcite:", info);
> > >
> > > CalciteConnection conn =
> > >
> > >
> > > connection.unwrap(CalciteConnection.class);
> > >
> > > SchemaPlus rootSchema = conn.getRootSchema();
> > >
Re: can we set a default schema for calcite connection to avoid writing schema names in sql
Posted by Gavin Ray <ra...@gmail.com>.
Sure
Here is a singleton object that uses FrameworkConfig in Kotlin to manage a
single master RootSchema + CalciteConnection:
https://github.com/GavinRay97/GraphQLCalcite/blob/e4ba2426edb546bda9bd5bd87a61764850138348/src/main/kotlin/CalciteSchemaManager.kt#L23-L117
And here is the same in Java:
https://github.com/GavinRay97/GraphQLCalcite/blob/1070d179b67d803f05975cf416c392b010823069/src/main/java/com/example/calcitewrappers/DatabaseManager.java#L22-L60
Hope this is helpful =)
On Fri, Feb 18, 2022 at 10:06 AM xiaobo <gu...@qq.com.invalid> wrote:
> 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";<ra...@gmail.com>;
> Send time: Sunday, Feb 13, 2022 10:25 AM
> To: "dev"<de...@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 <gu...@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";<ra...@gmail.com>;
> > Send time: Sunday, Feb 13, 2022 1:43 AM
> > To: "dev"<de...@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 <gu...@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 ";<gu...@qq.com.INVALID>;
> > > Send time: Friday, Feb 11, 2022 11:20 PM
> > > To: "dev"<de...@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");
> > > Properties
> info
> > =
> > > new Properties();
> > >
> > > info.setProperty("lex", "JAVA");
> > >
> > > info.setProperty(CalciteConnectionProperty.CASE_SENSITIVE.camelName(),
> > > "false");
> > >
> > > info.setProperty("defaultSchema", "hr");
> > > try {
> > >
> > > Connection connection =
> > >
> > >
> > > DriverManager.getConnection("jdbc:calcite:", info);
> > >
> > > CalciteConnection conn =
> > >
> > >
> > > connection.unwrap(CalciteConnection.class);
> > >
> > > SchemaPlus rootSchema = conn.getRootSchema();
> > >
Re: can we set a default schema for calcite connection to avoid writing schema names in sql
Posted by xiaobo <gu...@qq.com.INVALID>.
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";<ra...@gmail.com>;
Send time: Sunday, Feb 13, 2022 10:25 AM
To: "dev"<de...@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 <gu...@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";<ra...@gmail.com>;
> Send time: Sunday, Feb 13, 2022 1:43 AM
> To: "dev"<de...@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 <gu...@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 ";<gu...@qq.com.INVALID>;
> > Send time: Friday, Feb 11, 2022 11:20 PM
> > To: "dev"<de...@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");
> > Properties info
> =
> > new Properties();
> >
> > info.setProperty("lex", "JAVA");
> >
> > info.setProperty(CalciteConnectionProperty.CASE_SENSITIVE.camelName(),
> > "false");
> >
> > info.setProperty("defaultSchema", "hr");
> > try {
> >
> > Connection connection =
> >
> >
> > DriverManager.getConnection("jdbc:calcite:", info);
> >
> > CalciteConnection conn =
> >
> >
> > connection.unwrap(CalciteConnection.class);
> >
> > SchemaPlus rootSchema = conn.getRootSchema();
> >
Re: can we set a default schema for calcite connection to avoid writing schema names in sql
Posted by Gavin Ray <ra...@gmail.com>.
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 <gu...@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";<ra...@gmail.com>;
> Send time: Sunday, Feb 13, 2022 1:43 AM
> To: "dev"<de...@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 <gu...@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 ";<gu...@qq.com.INVALID>;
> > Send time: Friday, Feb 11, 2022 11:20 PM
> > To: "dev"<de...@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");
> > Properties info
> =
> > new Properties();
> >
> > info.setProperty("lex", "JAVA");
> >
> > info.setProperty(CalciteConnectionProperty.CASE_SENSITIVE.camelName(),
> > "false");
> >
> > info.setProperty("defaultSchema", "hr");
> > try {
> >
> > Connection connection =
> >
> >
> > DriverManager.getConnection("jdbc:calcite:", info);
> >
> > CalciteConnection conn =
> >
> >
> > connection.unwrap(CalciteConnection.class);
> >
> > SchemaPlus rootSchema = conn.getRootSchema();
> >
Re: can we set a default schema for calcite connection to avoid writing schema names in sql
Posted by xiaobo <gu...@qq.com.INVALID>.
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";<ra...@gmail.com>;
Send time: Sunday, Feb 13, 2022 1:43 AM
To: "dev"<de...@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 <gu...@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 ";<gu...@qq.com.INVALID>;
> Send time: Friday, Feb 11, 2022 11:20 PM
> To: "dev"<de...@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");
> Properties info =
> new Properties();
>
> info.setProperty("lex", "JAVA");
>
> info.setProperty(CalciteConnectionProperty.CASE_SENSITIVE.camelName(),
> "false");
>
> info.setProperty("defaultSchema", "hr");
> try {
>
> Connection connection =
>
>
> DriverManager.getConnection("jdbc:calcite:", info);
>
> CalciteConnection conn =
>
>
> connection.unwrap(CalciteConnection.class);
>
> SchemaPlus rootSchema = conn.getRootSchema();
>
Re: can we set a default schema for calcite connection to avoid writing schema names in sql
Posted by Gavin Ray <ra...@gmail.com>.
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 <gu...@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 ";<gu...@qq.com.INVALID>;
> Send time: Friday, Feb 11, 2022 11:20 PM
> To: "dev"<de...@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");
> Properties info =
> new Properties();
>
> info.setProperty("lex", "JAVA");
>
> info.setProperty(CalciteConnectionProperty.CASE_SENSITIVE.camelName(),
> "false");
>
> info.setProperty("defaultSchema", "hr");
> try {
>
> Connection connection =
>
>
> DriverManager.getConnection("jdbc:calcite:", info);
>
> CalciteConnection conn =
>
>
> connection.unwrap(CalciteConnection.class);
>
> SchemaPlus rootSchema = conn.getRootSchema();
>
Re: can we set a default schema for calcite connection to avoid writing schema names in sql
Posted by xiaobo <gu...@qq.com.INVALID>.
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 ";<gu...@qq.com.INVALID>;
Send time: Friday, Feb 11, 2022 11:20 PM
To: "dev"<de...@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");
Properties info = new Properties();
info.setProperty("lex", "JAVA");
info.setProperty(CalciteConnectionProperty.CASE_SENSITIVE.camelName(), "false");
info.setProperty("defaultSchema", "hr");
try {
Connection connection =
DriverManager.getConnection("jdbc:calcite:", info);
CalciteConnection conn =
connection.unwrap(CalciteConnection.class);
SchemaPlus rootSchema = conn.getRootSchema();