[odb-users] Different database schemas - postgresql

[Boris Kolpackov]

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.")

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
   {
     ...
   }

   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?

Boris