One directory, multiple database

[jsetzer]

Hi,

I'm just making up my mind and doing some research on how to use the same application (files, directories) with different databases to build some kind of multi-tenant application with seperated databases. I'm not a friend of copying files from one directory to n other directories, one per client/tenant. So I was thinking about using the same files with different databases.

First idea was to change the database-related variables in config.php depending on to some $_POST variable coming from a new login page.

2019-08-23_16-43-54.png (56.61 KiB) Viewed 8475 times

But this will not be enough because there are some overlapping parts:

• images directory being used for all uploads
• autmoatic database-migration after update using a file named setup.md5 per database
• SMTP settings for sending mails and membership-management (mm)
• the csv-files for custom dropdowns
• I'm afraid there will be more things to consider

So at the first glance all that cannot be done with (non-overwritten) hooks only.

I'm wondering if anyone else here has tried or at least thought about this, too. If so, please share your ideas so that we can collaborate on this topic.

Thanks in advance!

Best regards,
Jan

[onoehring]

Hi Jan,

interesting request. Just my (few) ideas for now, problems occur, when thinking about. But still - maybe it's starting point:

* For SMTP I do not see a big problem. As the settings come from /config.php, you can overwrite them including some file which won't be replaces (e.g. in the hooks folder). Maybe not even this is needed, in case you are using two different config.php files. Acutally, you might change config.php to include the file that is actually needed.

* I have not used csv files for custom dropdowns - is this an AppGini feature? If it is not, you should be able to draw some additional string from some configuration file and attach that to the filename of the csv (maybe the the customer name?).

* the images folder seems problematic to me as well. Maybe a search and replace over all files is needed. Whenever /images is being used, /images/$yourVariable could be used instead ... but ... how can this be done with JS files?

* I am not sure what the setup.md5 file is for anyways. (I recently changed servers and database user - db name stayed the same - and was able to use the old setup.md5).

Olaf

[onoehring]

Hi Jan,

one more thing.
As we can see, it's a problem, when to many things are hardcoded. Maybe a suggestion for future versions of AG is that things like the "/image" folder should be set in the config.php as well.

Olaf

[jsetzer]

Hi Olaf,

thanks for sharing your ideas.

CSV files for custom dropdowns

• CSV-files in hooks-folder for custom dropdowns is a built-in feature of AppGini.
  Anyway: usually I do not use this feature

Possible problem with setup.md5

• Whenever you generate your app, AppGini creates a file called updateDB.php
• updateDB.php contains code to create or alter all required tables, columns and constraints
• When connecting to the configured database there is a comparism between the current updateDB.php and a file named setup.md5, if it exists.
• If there is no setup.md5, migrations will be run
• If there is a setup.md5 which does not match with update.DB.php, migrations will be run
• If there is a setup.md5 which matches with update.DB.php, no migrations will be run
• after running the migrations, a fingerprint of the current updateDB.php will be stored as setup.md5
• After the migrations have been applied, next time you connect, the fingerprint of updateDB.php matches setup.md5 and there will not be another database migration

As you can see, the database migrations will be run once. Setup.md5 is kind of fingerprint of the recently applied database migrations.

But now the problem is:
The decision for migrating a database or not will be made according to two files updateDB.php and setup.md5. There is no check against the database structure itself. If we have multiple databases but the migration will only be started once, this means only one (!) database will be migrated. The others will not.

Thinking it over
We need a different comparism method, for example after migration don't save the fingerprint as setup.md5 but as setup_{$dbName}.md5.On next connect, we'd have to check if updateDB.php matches with that setup file.

Could work this way. One issue probably solvable.

[jsetzer]

...
As we can see, it's a problem, when to many things are hardcoded. Maybe a suggestion for future versions of AG is that things like the "/image" folder should be set in the config.php as well.
...

Right now it's not hardcoded but configured in defaultLang.php:

Code: Select all

$Translation['ImageFolder'] = './images/'; 

This variable can be overwritten in your language.php or later on anywhere in your code just by changing $Translation['ImageFolder'] to what you need.

So I guess there is a way to move uploads into separate directories depending on the selected database / tenant.
Maybe this issue is also solvable.

Has anyone managed to redirect uploads in different directories according to some variable?

Regards,
Jan

[onoehring]

Hi Jan,

why not store the database version in the database itself (instead of using setup.md5)? A simple comparison would be enough to see that (no) DB changes are needed.

If you search "/images" in your generated app, you will finde several occurrences, so the implementation seems not completely independent / dependent from the $Translation['ImageFolder'].

in incCommon.php we find the explanation

getUploadDir($dir) -- if dir is empty, returns upload dir configured in defaultLang.php, else returns $dir.

Maybe this can be used to interfere the directory selection?

Just some idea:
Dir A: starting point for Company A
Dir B: starting point for Company B
Dir C: files for application
If files that are requested from AG exist in A (or B) those shall be used, if not, files in dir C are used.

Olaf

[Alisson]

Unfortunately I have nothing to contribute to this. But this would be a great update for Appgini or maybe a new version of Appgini that I would definitely buy.
I also really don't like to copy folders for every new location that I have to add for my work.
I have now more than 30 folders one for each location, so every little change or improvement that I make I have to copy to all 30 folders.

[federico]

hi all

Just a quick suggestion. Till now I created 8 different databases in our Company.
Customer manager, Customers complaint, Non Conformity Reports, internal Reports, Employs, etc...
and one indexpage with different links.
Do you suggest to keep this solution or should I merge in one big database?

thanks in advance
Federico

[onoehring]

Hi federico,

well, tough question. It depends.
Database wise I would suggest using only one application: Currently you are duplicating your data (customer etc.) which is not good, at the same time you are giving the option away to easily connect tables. A customer complaint may come from a customer manager and result in an internal report and a talk to Employees.... How would your databases cover this?

On the other hand (as in "it depends") there might be things you do not want to have in a common database. This could be a reason to have more than the one big one.

Yes, I would probably merge. The usergroups and access permissions in AG provide a great way to have control over who can do/see what.

just 2 cts
Olaf

[federico]

Hi Olaf
thanks for your suggestion. I totaly agree with your idea. I began with a project, than another one and so on. Now I have the idea that something could be merged to easely interact with data.

[onoehring]

Hi Jan and all,

is there any progress to report when thinking about building a multi tenant app to have it running as a software as a service (saas)?

Are there any known plans for AG to incorporate a single plattform, as Jan described for all tenants (without copying files for each tenant and creating seperate databases)?

Olaf