Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
Home » U++ Library support » U++ SQL » Application with two different databases schema
Application with two different databases schema [message #53841] Fri, 08 May 2020 17:41 Go to next message
forlano is currently offline  forlano
Messages: 1182
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
Re: Application with two different databases schema [message #53844 is a reply to message #53841] Sat, 09 May 2020 03:00 Go to previous messageGo to next message
forlano is currently offline  forlano
Messages: 1182
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 Go to previous messageGo to next message
slashupp is currently offline  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
Re: Application with two different databases schema [message #53853 is a reply to message #53852] Sat, 09 May 2020 12:46 Go to previous messageGo to next message
forlano is currently offline  forlano
Messages: 1182
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
Re: Application with two different databases schema [message #53885 is a reply to message #53853] Mon, 11 May 2020 10:21 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
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
Re: Application with two different databases schema [message #53886 is a reply to message #53885] Mon, 11 May 2020 12:08 Go to previous messageGo to next message
forlano is currently offline  forlano
Messages: 1182
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
Re: Application with two different databases schema [message #53890 is a reply to message #53886] Wed, 13 May 2020 11:24 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 13975
Registered: November 2005
Ultimate Member
Please check new reference/SQL_MultiDB.

Also needed to fix some destruction issues for SQL....
Re: Application with two different databases schema [message #53894 is a reply to message #53890] Wed, 13 May 2020 15:49 Go to previous messageGo to next message
forlano is currently offline  forlano
Messages: 1182
Registered: March 2006
Location: Italy
Senior Contributor
mirek wrote on Wed, 13 May 2020 11:24
Please check new reference/SQL_MultiDB.


I tried it and seems to work properly. The code was a bit tricky for me, but I was able to follow the right path.
Thanks a lot!

Luigi
Re: Application with two different databases schema [message #57165 is a reply to message #53890] Wed, 02 June 2021 13:11 Go to previous message
JeyCi is currently offline  JeyCi
Messages: 50
Registered: July 2020
Member
mirek wrote on Wed, 13 May 2020 11:24
Please check new reference/SQL_MultiDB.....

SQL_MultiDB is really helpful. Thanks!


Best regards.
Previous Topic: Jsonize() in database table structures (S_*) [patch]
Next Topic: OleDBSession in Archive
Goto Forum:
  


Current Time: Thu Mar 28 18:57:05 CET 2024

Total time taken to generate the page: 0.01404 seconds