Possible specifying database name? about sqlpp11 HOT 5 CLOSED

Hi Thomas,

You did not miss it. There is no such mechanism yet.

I'll think about it. If you have suggestions for the syntax, please let
me know. There is also the question of how much type-safety is required
here? Should we define the database of the table in the header? Probably
not.

BTW: I think I am getting closer to the custom/patchwork query.

Regards,

Roland

On 2014-10-16 21:34, Thomas marsh wrote:

Hi Roland,

Perhaps I missed it while browsing the code, but is there a way to
specify the database in a query? I need to work with multiple
databases (e.g., via |ATTACH| in SQLite), and I am unable to perform
operations outside of the |main| (default) database. E.g., I perform
the following:

|ATTACH 'a.db' AS a;
ATTACH 'b.db' AS b;
SELECT t1.foo, t2.bar FROM a.my_table AS t1, b.my_table AS t2 WHERE t1.id = t2.id;
|

Currently, I'm unaware of a mechanism to provide such queries in a
type-safe manner.

—
Reply to this email directly or view it on GitHub
#19.

from sqlpp11.

I'm not sure about the best syntax. It should be in the from clause (or near to it) and seems like it would be natural as a method on the table instance or as a decorator. In the SQL:2011 standard grammar, the portion before the dot in a table name (e.g., the "a" in SELECT foo FROM a.my_table) is called the "schema qualifier". So, one usage pattern might be:

auto a_t = sqlpp::schema_qualified("a", MyTable());

select(a_t.foo).from(a_t).where(true);

from sqlpp11.

That is certainly reasonable enough to start with. The calling "ATTACH"
via db.exectute() would be the developer's responsibility, right?

It should be relatively easy to do this similar to using select
statements as tables.

Best,

Roland

from sqlpp11.

Yes, I anticipated calling ATTACH via db.execute() since it is a database specific extension and not part of the SQL standard.

P.S. I'm looking forward to patchwork! Exciting that it's coming along.

from sqlpp11.

Hi Thomas,

I finally got to this one. You can now do

  sql::connection_config config;
  config.path_to_database = ":memory:";
  auto other = db.attach(config, "other");
  auto right = schema_qualified_table(other, TabSample{}).as(sqlpp::alias::right);

right then evaluates to other.tab_sample.

The attach method is currently implemented for the sqlite connector only.

Cheers,

Roland

from sqlpp11.