Andrzej, I think this task makes most sense for you. But before you start working on this, please let me know what do you think about this. The functionality is not a "must have" stuff but could be very useful for systems like tigase.im where we have many vhosts and vhosts can be added all the time. Having vhost data in a separate database could be kind of a "paid add-on" we may offer. This way we could easily generate daily DB snapshots for selected vhosts and offer this as a kind of a backup to our customers.

%kobit I like this idea a lot. Maybe not this automatic DB creation part but storage of data for separate domains in separate databases. I've already added support for separate domains in separate databases for PubSub, MA, UA and MUC in 7.2.0.

To implement feature we need to:

• slightly modify repository pools to make it work correctly (most of code is already in place)

• store JDBC URI for each vhost - I think for that we could use separate database or better use default database as we do right now.

• create automatic database schema loader and updater mechanism

• decide how we decided that this vhost should have separate DB, ie. How we enable it for DB?

Andrzej Wójcik wrote:

%kobit I like this idea a lot. Maybe not this automatic DB creation part but storage of data for separate domains in separate databases. I've already added support for separate domains in separate databases for PubSub, MA, UA and MUC in 7.2.0.

Yes, support for separate DB for a vhost was implemented for UserRepository long time ago and we do use it. However, right now, if we want to have a separate DB for a vhost this is all manual work, it requires a special entry in the init.properties configuration file, manually creating DB, loading schema, etc....

To implement feature we need to:

• slightly modify repository pools to make it work correctly (most of code is already in place)

As far as I remember it all works correctly for the UserRepository and AuthRepository. Not sure about lower level like DataRepository and connectors for other components.

• store JDBC URI for each vhost - I think for that we could use separate database or better use default database as we do right now.

Yes, that's the main missing part. Instead of making this a global configuration option, it should be a vhost level configuration option (with global default). Then, when adding a new vhost we would have an option to provide a custom DB connection URI and then it would be stored in DB along with the rest of the VHost config options.

• create automatic database schema loader and updater mechanism

Yes, but it does not necessarily have to be a Tigase built-in functionality. It could be an external cron-job running periodically. Something like we have now for Redmine to create source code repository for newly created projects. When a new vhost is added that requires a dedicated DB, it could be in non-active state until the DB is created or something like this.

• decide how we decided that this vhost should have separate DB, ie. How we enable it for DB?

I think, we should make this one of the vhost configuration options. Add it to the vhost config forms so we could set it through the ad-hoc commands used to add/manage vhosts. However, this one option should be made somehow special. So we could restrict who can enable it when adding a new vhost. Currently, on the tigase.im service anybody can add new a vhost which is fine but I do not want the DB creation to be part of the public options. This should be kind of an admin stuff only, so we could enable it for a vhost as a special feature.

Artur Hefczyc wrote:

Andrzej Wójcik wrote:

%kobit I like this idea a lot. Maybe not this automatic DB creation part but storage of data for separate domains in separate databases. I've already added support for separate domains in separate databases for PubSub, MA, UA and MUC in 7.2.0.

Yes, support for separate DB for a vhost was implemented for UserRepository long time ago and we do use it. However, right now, if we want to have a separate DB for a vhost this is all manual work, it requires a special entry in the init.properties configuration file, manually creating DB, loading schema, etc....

To implement feature we need to:

• slightly modify repository pools to make it work correctly (most of code is already in place)

As far as I remember it all works correctly for the UserRepository and AuthRepository. Not sure about lower level like DataRepository and connectors for other components.

• store JDBC URI for each vhost - I think for that we could use separate database or better use default database as we do right now.

Yes, that's the main missing part. Instead of making this a global configuration option, it should be a vhost level configuration option (with global default). Then, when adding a new vhost we would have an option to provide a custom DB connection URI and then it would be stored in DB along with the rest of the VHost config options.

I agree. We can extend class which acts now as a replacement for RepositoryFactory in 7.2.0 to create separate connection for particular domains from VHost. This way we would have separate DataRepository instances.

I already added new feature to in new implementations of pools, so it is possible to force usage of separate database for particular domain in DataRepository with name matching domain is already created.

This can be turned on using single parameter in configuration so we only need this automatic registration of DataRepository instances on base of VHost domains.

• create automatic database schema loader and updater mechanism

Yes, but it does not necessarily have to be a Tigase built-in functionality. It could be an external cron-job running periodically. Something like we have now for Redmine to create source code repository for newly created projects. When a new vhost is added that requires a dedicated DB, it could be in non-active state until the DB is created or something like this.

OK, but we should not allow this after vhost is created. As we are not migrating data, right?

• decide how we decided that this vhost should have separate DB, ie. How we enable it for DB?

I think, we should make this one of the vhost configuration options. Add it to the vhost config forms so we could set it through the ad-hoc commands used to add/manage vhosts. However, this one option should be made somehow special. So we could restrict who can enable it when adding a new vhost. Currently, on the tigase.im service anybody can add new a vhost which is fine but I do not want the DB creation to be part of the public options. This should be kind of an admin stuff only, so we could enable it for a vhost as a special feature.

Yes, we can work on this to add this feature. However %wojtek will be working on better handing use cases with a lot of vhosts and I think we should work on this feature after he finishes his task.

Yes, that's the main missing part. Instead of making this a global configuration option, it should be a vhost level configuration option (with global default). Then, when adding a new vhost we would have an option to provide a custom DB connection URI and then it would be stored in DB along with the rest of the VHost config options.

I agree. We can extend class which acts now as a replacement for RepositoryFactory in 7.2.0 to create separate connection for particular domains from VHost. This way we would have separate DataRepository instances.

I already added new feature to in new implementations of pools, so it is possible to force usage of separate database for particular domain in DataRepository with name matching domain is already created.

This can be turned on using single parameter in configuration so we only need this automatic registration of DataRepository instances on base of VHost domains.

OK, I am not up to date with current code. It worked somehow in earlier versions and it somehow works even right now (tigase.im). But the current implementation might be not optimal, especially in relation to the most recent server APIs and framework development. I am leaving this to the developer who will be working on the ticket (Wojciech from what I can see).

• create automatic database schema loader and updater mechanism

Yes, but it does not necessarily have to be a Tigase built-in functionality. It could be an external cron-job running periodically. Something like we have now for Redmine to create source code repository for newly created projects. When a new vhost is added that requires a dedicated DB, it could be in non-active state until the DB is created or something like this.

OK, but we should not allow this after vhost is created. As we are not migrating data, right?

We can allow this after vhost is created but without data migration. The way I see it working is this:

A user adds a new vhost on our service and then contacts us that he wants to pay for keeping his vhosts data in a separate database. Then we update the vhost configuration and somehow (automatically, semiautomatically or manually) create the DB. In most cases the new vhost will have no data or very little data so migration would not be an issue. If somebody would want data migration for long established vhost, then it would be kind of a special thing we would do.

• decide how we decided that this vhost should have separate DB, ie. How we enable it for DB?

I think, we should make this one of the vhost configuration options. Add it to the vhost config forms so we could set it through the ad-hoc commands used to add/manage vhosts. However, this one option should be made somehow special. So we could restrict who can enable it when adding a new vhost. Currently, on the tigase.im service anybody can add new a vhost which is fine but I do not want the DB creation to be part of the public options. This should be kind of an admin stuff only, so we could enable it for a vhost as a special feature.

Yes, we can work on this to add this feature. However %wojtek will be working on better handing use cases with a lot of vhosts and I think we should work on this feature after he finishes his task.

Ok, so I am assigning this ticket to Wojciech.

By the way. %wojtek could you please send me some information on: "%wojtek will be working on better handing use cases with a lot of vhosts"? I am very curious about this.

Artur Hefczyc wrote:

Ok, so I am assigning this ticket to Wojciech.

By the way. wojtek could you please send me some information on: "wojtek will be working on better handing use cases with a lot of vhosts"? I am very curious about this.

Most likely Andrzej was referring to #4693.

OK, this makes sense.