using both SQLite and Mysql

[daviz]

Hi, following the advice of Ulrich and others: https://forums.indigorose.com/forum/...on-web-hosting, I am migrating my project from SQlite to Mysql.
I am using Wamp server and all tests are running locally, everything is working as expected.
I have a slight problem however, my project is divided in two parts:
1- One main module, this is accessed by one person who is in charge of inserting records in the database, he maintains it (updates). Without his input the other modules wouldn´t have any data to work with. This main module is independent in that no other user can manipulate its data.
2- Several other modules (6) that performs many activities based on the data from main module. These other modules highly interact with each other tables and that is why Mysql is so valuable for this particular project. Using SQLite has lots of troubles.

Based on that description, you can tell that main module really does not need to be migrated to Mysql at all. On the other hand the other modules benefit greatly of Mysql capabilities.

Is it possible to keep integration between Mysql and SQlite within the same project in AMS? The main module is only used by other modules for searching (by making SELECT or JOIN queries).
What I want to achieve is this: keep main module using SQLite and other modules use Mysql.

Plugins I am using are: SQLite3, MySQL (mindquake)

Thanks for any comments or observations.

David

[Ulrich]

You can build a single project using multiple databases, and even servers, of course. But as you say that the other modules wouldn't have data to work with if this single person does not feed information into it, I understand that everything is using one single set of data. If the data entered by this person is not the same which is returned to the other users querying MySQL, then there is no problem, but if it is a single set, then everything would better be stored and processed in the same database engine, so the information can be shared. (Unless I misunderstood the situation.)

Ulrich

[daviz]

Ulrich thanks,
I have already migrated 90% of regular modules, it has taken me almost two months but is worth the time and effort.
I haven´t touched the main module though. It is the most important module so I had took a lot of time fine tuning it, given that it doesn´t interact with any other table it performs fast. So no problem in the "speed area". I would rather don´t touch this module and leave it as it is (using SQLite).

So, when I asked "integration between Mysql and SQlite" I meant : is it possible communication between them? (given they are different).

I have hope that such integration or communication is possible because I watched a youtube video that succesfully fetched SQLite data and inserted into Mysql by using JSON as a bridge. At least that worked for Android OS. Let me know if it´s OK to share the link for the video and the "how to".

Regarding your question about data set (if I understand correctly): data inserted in main module is exactly what the other modules use. The whole project is basically a big task manager or planner, so the main user feeds new tasks (which include description, client, amount, delivery date, etc) and what the other users do is select whatever task is relevant to them and register date and time (when task starts and finishes) and other activities into their respective tables.

- - -

Finally, if there is no better way for keeping data integrity then I should breath deep and get my hands on main module in order to migrate it to Mysql.

David