[odb-users] Different database schemas - postgresql

[Konstantin Mikhailov]

Hi Boris.

On Tue, Dec 13, 2011 at 9:18 PM, Boris Kolpackov <boris at codesynthesis.com>wrote:

> Hi Konstantin,
>
> Konstantin Mikhailov <ekimka at gmail.com> writes:
>
> > Is it possible to specify different database schema
> > for different persistent objects/tables?
>
> No, not yet. But we have been thinking about adding this
> support for some time now.
>
>
> > We need simulanteous access to the tables located in the
> > different schemas.
>
> What will be the best way to implement the mapping of objects
> to schemas in your case? We have thought of three mechanisms:
>
> 1. By specifying the schema in the 'db table' pragma, as you
>   have already tried:
>
>   #pragma db object table("myschema.test")
>
>    Or, to just specify the schema name and get the auto-derived
>   table name:
>
>   #pragma db object table("myschema.")
>
>
This option looks confusing alittle i think. I'm also think that separate
pragma option to specify database schema/namespace will be more clear.


> 2. By mapping a C++ namespace to a database schema. This will
>   result in all the persistent classes in this namespace having
>   tables in this schema. The syntax is not yet finalized, but
>   it could look something like this:
>
>   #pragma db namespace database("myschema")
>   namespace N
>   {
>     ...
>   }
>
>
In my opinion this option looks like most elegant one  (i prefer
something shorter abit #pragma db namespace("myschema") for example).
Unfortunately there will be significant overhead to specify database
schema/namespace in each header file. We've found that one header
file for each table is most optimal for us (we have over 50 header files at
the moment and will be much more in the nearest future). btw, we've also
found that write all header files by the hand is too difficult/tedious. so
we're
using simple homemade xml format to generate all hxx headers required by
odb.


>   Or:
>
>   #pragma db namespace(N) database("myschema")
>
>   While something like this might seem more natural:
>
>   #pragma db namespace(N) schema("myschema")
>
>   We don't want to use the term 'schema' because (a) not all
>   databases have this notion (some, like MySQL and SQLite use
>   the term 'database' for this while others, like SQL Server
>   can have much more that just the schema appearing before
>   the table, as in: server.db.schema.table) and (b) the term
>   schema is quite overloaded (i.e., we also generate a "database
>   schema"). So, instead, we are thinking of using a more neutral
>   'database namespace' term. So in the above example, the more
>    verbose alternative would be:
>
>   #pragma db namespace(N) database_namespace("myschema")
>
>
> 3. By specifying the schema on the command line, for example,
>   with with the --database-namespace option:
>
>   --database-namespace myschema
>
>   This will lead to all the classes in this header that don't
>   have a schema assigned using one of the above two methods,
>   having tables in myschema.
>
> Which option will work best for your case?


In our case last option will be most interresting one, since i don't need
to edit hxx files just spread them in different folders for example and
run odb in each directory with correct arguments.



> Boris
>