U++ framework: U++ SQL » Application with two different databases schema

Home » U++ Library support » U++ SQL » Application with two different databases schema

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Application with two different databases schema [message #53841]

Fri, 08 May 2020 17:41

forlano
Messages: 1221
Registered: March 2006
Location: Italy

Senior Contributor

Hello,

I have a program that stores data on a local Sqlite3 db. Moreover it needs to connect to an online MySql db.
Can this scenario be managed? In case of affermative answer how to do it? It is convenient to have one .sch file or two?...

After trying several possibilities, to avoid error from CLANG (many duplicate and redefinition), I found a solution that is accepeted by CLANG. I am using a single .SCH file with the definition of the two dbs. In a .h file I have

#define SCHEMADIALECT <plugin/sqlite3/Sqlite3Schema.h>
#define MODEL <OrionVR/db.sch>

#define SCHEMADIALECT2 <MySql/MySqlSchema.h>

#include <Sql/sch_header.h>

Then in a .cpp file I used

#include "Sql/sch_schema.h"
#include "Sql/sch_source.h"

It compiles but the program crash immediately.
I am sure I am doing weird things. Which is the correct way to do it?

Thanks,
Luigi

Report message to a moderator

Re: Application with two different databases schema [message #53844 is a reply to message #53841]

Sat, 09 May 2020 03:00

forlano
Messages: 1221
Registered: March 2006
Location: Italy

Senior Contributor

I have done new experiments. What provoked the crash was something else now fixed.
Now I have declared

#define SCHEMADIALECT <plugin/sqlite3/Sqlite3Schema.h>
#define MODEL <OrionVR/db.sch>
#include <Sql/sch_header.h>

and

#include "Sql/sch_schema.h"
#include "Sql/sch_source.h"

The code related the sqlite3 db works but I have not yet connected the online MySql db. Still remain the doubts about what I am doing and if I must wait some drawback. Rolling Eyes

Luigi

[Updated on: Sat, 09 May 2020 03:01]

Report message to a moderator

Re: Application with two different databases schema [message #53852 is a reply to message #53844]

Sat, 09 May 2020 12:13

slashupp
Messages: 231
Registered: July 2009

Experienced Member

Hi Luigi

I wrote the following on github: https://github.com/slashmais/dbsqlite3
that I use for all my data, maybe you find something useful. This works
very well for me for a while now.

Sorry for the lack of description in readme, I still need to make
time & energy to tidy-up all these projects

hth

Report message to a moderator

Re: Application with two different databases schema [message #53853 is a reply to message #53852]

Sat, 09 May 2020 12:46

forlano
Messages: 1221
Registered: March 2006
Location: Italy

Senior Contributor

slashupp wrote on Sat, 09 May 2020 12:13

Hi Luigi

I wrote the following on github: https://github.com/slashmais/dbsqlite3
that I use for all my data, maybe you find something useful. This works
very well for me for a while now.
hth

Thank you for the reply.
I saw you did something interesting for sqlite3. I'll use something in future.
My problem now is different. I am working with two db simultaneously: sqlite3 and mysql. Each of them has its own .sch file. The problem is how to declare and use them without mix up the code.

Thanks,
Luigi

Report message to a moderator

Re: Application with two different databases schema [message #53885 is a reply to message #53853]

Mon, 11 May 2020 10:21

mirek
Messages: 14290
Registered: November 2005

Ultimate Member

I think that there are 2 separate issues:

- Session

and

- Schema

WRT to Session, things get complicated here as you cannot obviously use global session (the one assigned with SQL = statement) for both databases.

This is no showstopper - Sql instances have constructor form with SqlSession as parameter while default Sql constructors are using global session.

Usually one DB is "dominant" for the application. In that case, it makes sense to assign global session for dominant database connection and use constructor with parameter for secondary one.

Now for the schema issue. Obviously, you need two schema files and import each one with its dialect, also duplicate all other details you need.
Simply #undef SCHEMADIALECT after the include and define a new value for it.

I will try to make a reference example for this...

Mirek

Report message to a moderator

Re: Application with two different databases schema [message #53886 is a reply to message #53885]

Mon, 11 May 2020 12:08

forlano
Messages: 1221
Registered: March 2006
Location: Italy

Senior Contributor

mirek wrote on Mon, 11 May 2020 10:21

I think that there are 2 separate issues:

- Session

and

- Schema

WRT to Session, things get complicated here as you cannot obviously use global session (the one assigned with SQL = statement) for both databases.

This is no showstopper - Sql instances have constructor form with SqlSession as parameter while default Sql constructors are using global session.

Usually one DB is "dominant" for the application. In that case, it makes sense to assign global session for dominant database connection and use constructor with parameter for secondary one.

Now for the schema issue. Obviously, you need two schema files and import each one with its dialect, also duplicate all other details you need.
Simply #undef SCHEMADIALECT after the include and define a new value for it.

I will try to make a reference example for this...

Mirek

Thanks for the reply.
I do not use SQL, so the I have no problem with sessions. I can use both, but currently with the same .SCH. Moreover when I create a new db of course appear tables not wanted belonging to the other db.

My problems is how to deal with two schema.
I have just tried to duplicate the code

#define SCHEMADIALECT <plugin/sqlite3/Sqlite3Schema.h>
#define MODEL <OrionVR/db.sch>
#include <Sql/sch_header.h>

#undef SCHEMADIALECT
#undef MODEL

#define SCHEMADIALECT <MySql/MySqlSchema.h>
#define MODEL <OrionVR/db2.sch>
#include <Sql/sch_header.h>

but the compiler signal error.
Luigi

Report message to a moderator