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";<ray.gavi...@gmail.com>; Send time: Sunday, Feb 20, 2022 0:59 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 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 <ray.gavi...@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 <guxiaobo1...@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";<ray.gavi...@gmail.com>; >> Send time: Saturday, Feb 19, 2022 0:37 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 >> >> >> >> 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 <guxiaobo1...@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";<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"); >> > > > 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(); >> > > > > >
