[odb-users] A view on top of several tables from different sqlite files (DB)

[Boris Kolpackov]

Hi Yoav,

Weiss, Yoav <yoav.weiss at intel.com> writes:

> #pragma db view object (Cell = c) \
>                 object (Instance = i : c::m_id == i::m_cell_id) \
>                 query((?) + " GROUP BY " + c::m_name)
> struct CellOccCount {
>     #pragma db column("c.cell_name")
>     std::string m_name;
> 
>     #pragma db column ("count(*)")
>     long m_count;
> };
> 
> And the way I query the view is:
> odb::result<CellOccCount> results(m_p_db->query<CellOccCount>(query_cond));
> 
> Now suppose these 2 tables (Cell and Instance) are located in 2 different
> sqlite databases. My questions are:
> 
> 1. How should I modify the view (if at all) to reflect this?

The view doesn't need to be modified, but see #2 below.


> 2. On which odb::database should I initiate the query (assuming I have
>    2 instances - one for each database)?

No, that's not going to work. You cannot have a query span multiple
SQLite connections. Instead, what you will have to do is attach the
second database file to the existing connection with the SQLite
ATTACH DATABASE statement:

https://www.sqlite.org/lang_attach.html

In ODB you can use execute(), for example:

m_p_db->execute ("ATTACH DATABASE cell.sqlite AS cells");

If you don't have identically names tables in the two databases, then
you should be all set. Otherwise, you would have to qualify the table
names, for example:

#pragma db object table("cells.Cell")
class Cell
{
  ...
};

Boris