The filesystem hierarchy is a great analogy, I understand it much better now I think -- thank you.
This seems like a problem with potentially very brittle solutions. Using your explanation I was able to get it to work, with very terrible logic that says "If catalogs are all null, then it's one-level so make a fake "root" schema to hold it in the rootSchema" But this doesn't feel so good. Not 100% sure what the best thing to do here is, but at least you've cleared up what's going on. On Thu, Jan 27, 2022 at 6:41 PM Julian Hyde <[email protected]> wrote: > Let’s not worry about the names, and say that some DBs have two namespace > levels and others have just one. > > Calcite’s word for a namespace is ’schema’. Calcite schemas are arranged > in a hierarchy, like a filesystem, so there is no preferred depth. Any > schema can contain both tables and (sub)schemas. So you can easily built a > one- or two-level namespace structure, or whatever you want. > > Calcite’s catalog has a single ‘root schema’ (analogous to the root > directory, ‘/‘ in file systems), and you can get to anything else from > there. > > In JDBC parlance, a a level 1 namespace is called ‘catalog’, and a level 2 > namespace is a ’schema’. If a DB has a one-level namespace then catalog > will be null, or the empty string, or something. > > If you’re running an Avatica JDBC server backed by a particular Calcite > root schema, and you want your database to look like a one-level or > two-level database, we probably don’t make it particularly easy. > > Julian > > > > On Jan 27, 2022, at 7:25 AM, Gavin Ray <[email protected]> wrote: > > > > My RDBMS experience is nearly exclusively Postgres > > While working on this project, I've made the assumption that the > structure > > of a database is: > > > > Database -> Schema -> Table > > > > It turns out that this isn't accurate. In MySQL for instance, "Schema" is > > an alias for "DB". > > From the below StackOverflow answer, it seems like this is all over the > > place: > > > > https://stackoverflow.com/a/7944489/13485494 > > > > I have a "CalciteSchemaManager" object which has a "rootSchema" to which > > all datasources are attached > > This "rootSchema" is used to generate the GraphQL API and types > > > > It seems like I have two options, and I'm not sure which is a better > design: > > > > 1. Force all datasources to conform to (Database -> Schema -> Table) > > > > This means that adding a new MySQL database, would generate ("mysql_db" > -> > > "root" (fake schema) -> "some_table") > > Adding a CSV schema too, would be something like ("csv_datasource" -> > > "root" -> "some_csv_file") > > > > 2. Have an irregular data shape. Datasources can be of arbitrary > sub-schema > > depth. > > > > Example Postgres: ("pg_db_1" -> "public" -> "user") > > Example MySQL: ("mysql_db_1" -> "user") > > Example CSV: ("some_csv_file") or maybe ("csv_source_1" -> > "some_csv_file") > > > > What do you folks think I ought to do? > > Thank you =) > >
