Metadata and schemas other than public

[cstiborg]

The metadata code introduced by #480 hardcodes the used schema to public.
This is not great if you want to investigate any other schema than the public schema.
I've created test in my own fork of SOCI - and I'm ready to make a PR with that code - which stores the schema on the session object (default is public) and allows backends to change this based on whatever, but most likely a type of connection string.

E.g. for postgres "user=postgres host=127.0.0.1 port=5432 dbname=postgres options='--search_path=my_schema' password=mypwd" will select my_schema for the metadata instead of forcing public, or for mysql the database name will be used for the schema name - which is also quite sensical.

I have only implemented this in the mysql and postgresql backends as I don't have experience with- or any test DBs for any of the other supported DBs.

[vadz]

As this seems to be backwards compatible (right?), I see no reason not to add support for specifying a different schema — please submit your PR and I'll try to merge it soon.

TIA!

[cstiborg]

The api does not change, but the sematics of the connection string changes for the two databases I have implemented the change for.

• MySQL: The database you connect to will now be used as the table_schema when looking up in the information_schema metadata. This, to me, makes total sense as you will only have the hardcoded "public" schema if you created a database called "public", for anything else it will never work.
• PostgreSQL: I am querying the database about which schema it uses as default, meaning that what you set using the "options='--search_path='" parameter in the connection string will now be used for the table_schema when looking up in the information_schema metadata. This means that there will be consistency between the ddl code (create/alter/drop) of tables which will already be done in this schema (default being the public schema) and the sql statements w/o schema (select/update/delete). This is a change of the semantics, as previously the metadata would always be looked up in the public schema, while everything else, ddl and statements would be done within the schema from the connection string. I am still trying to figure out why metadata would ever be implemented with the public schema hardcoded.

Everything else is unchanged.

PR in #1156

[Krzmbrzl]

@vadz I think this has been implemented via #1156 and can therefore be closed, no?

[cstiborg]

Yes - I'm working on bringing metadata up to par for ODBC - but it's going a bit slow for the time being.

[Krzmbrzl]

Ah, I thought it was already done since the PR is closed. My bad 🤷