I think this is more complicate than just loading schemas to database (as setup already does that for some PubSub and few other components), ie. PubSub may use different database, same as MUC and MA, etc. We should load it to proper database.

The question here is: How to do this properly?

In my opinion we could modify setup to be a little more complex, to allow configuration of multiple data sources and configuration of components on different data sources.

We can also leave it as it is and install schemas only of enabled components on single database and leave more advanced configuration to the administrators (to manually apply schemas, reconfigure Tigase, etc.).

I do not like idea to load all schemas we have to single database, ie. there is no point to load UA schema if MA only will be used.

As for using changing configuration at later stage, I think that this should stop Tigase from running (as it does now), but we could provide new "feature/option" to the Tigase XMPP Server startup script. Following call from command line:

scripts/tigase.sh apply-schemas etc/tigase.conf

could result in Tigase XMPP Server checking it's configuration looking for data sources and components using it and loading latest schema versions. This could be useful at later stage when user would like to upgrade installation when new Tigase XMPP Server is released. Same code could be used in setup mode to allow upgrade of a schema.

%wojtek %kobit What do you think?

Andrzej Wójcik wrote:

I think this is more complicate than just loading schemas to database (as setup already does that for some PubSub and few other components), ie. PubSub may use different database, same as MUC and MA, etc. We should load it to proper database.

The question here is: How to do this properly?

In my opinion we could modify setup to be a little more complex, to allow configuration of multiple data sources and configuration of components on different data sources.

As usually with such concerns, my question is: Who is this for? Who would be using this and how?

In my opinion the web installer is really used by users who want to run a simple installation for testing or development purposes. Maybe a simple production system for a small community or something like this. I do not think anybody who is deploying Tigase on multiple cluster nodes which can be turned ON/OFF with multiple databases would use web installer to deploy Tigase in each machine. Instead, Tigase would be installed on a single machine, manually tweaked up and then the installation/configuration would be replicated somehow on other cluster nodes.

Therefore, I think the web installer should be simple and easy to use and serve this kind of users - so they can easily install and configure Tigase to run it for testing and development purposes.

Complex, multi-database installation would be setup manually anyway. And now question: when someone would want to run multi-database installation? For scaling and performance reasons. So, again, we should have a section in our performance related documentation how to setup Tigase to use multiple databases to get maximum performance and scalability. This is quite broad topic, and this documenting this part would not be a small task but we could start from a simple statement: Tigase can be setup on multiple databases, a separate database for each component: PubSub, MUC, MA, UA, Core server, AMP, etc..... For some people this is very new and important information.

We can also leave it as it is and install schemas only of enabled components on single database and leave more advanced configuration to the administrators (to manually apply schemas, reconfigure Tigase, etc.).

+1

I do not like idea to load all schemas we have to single database, ie. there is no point to load UA schema if MA only will be used.

Yes and no.

Really it does not hurt with anything to load all the schemas. The database would be just full of empty tables if they are not used. On the other hand it does not solve anything, as there may be some custom added or new components in the future added to the installation for which there will be no schema loaded yet. So we are again on a position with database not initialized for some components.

And avoiding lots of empty, unused tables in DB, gives a better impression and confidence in the software which is tide and more controlled.

As for using changing configuration at later stage, I think that this should stop Tigase from running (as it does now), but we could provide new "feature/option" to the Tigase XMPP Server startup script. Following call from command line:

+1

Yes, I like this idea very much.

could result in Tigase XMPP Server checking it's configuration looking for data sources and components using it and loading latest schema versions. This could be useful at later stage when user would like to upgrade installation when new Tigase XMPP Server is released. Same code could be used in setup mode to allow upgrade of a schema.

Actually, I have implemented many scripts checking Tigase's environment, Tigase configuration files, system settings, network, DNS, etc..... These are just a number of random scripts which can be run from command line to quickly check most common stuff. I think it might be a good idea to add such checks as an option to the Tigase command line like the above one. This might be very useful. Also, maybe we could add a command to collect some information from an installation for us, when we are asked to troubleshoot the installation? The command could get the config files, some logs, system configs, get Tigase stats, some HW info, pack it all into an archive file and prepare for us.

%kobit I agree with almost all what was written. See comments below:

Artur Hefczyc wrote:

As for using changing configuration at later stage, I think that this should stop Tigase from running (as it does now), but we could provide new "feature/option" to the Tigase XMPP Server startup script. Following call from command line:

+1

Yes, I like this idea very much.

could result in Tigase XMPP Server checking it's configuration looking for data sources and components using it and loading latest schema versions. This could be useful at later stage when user would like to upgrade installation when new Tigase XMPP Server is released. Same code could be used in setup mode to allow upgrade of a schema.

Actually, I have implemented many scripts checking Tigase's environment, Tigase configuration files, system settings, network, DNS, etc..... These are just a number of random scripts which can be run from command line to quickly check most common stuff. I think it might be a good idea to add such checks as an option to the Tigase command line like the above one. This might be very useful. Also, maybe we could add a command to collect some information from an installation for us, when we are asked to troubleshoot the installation? The command could get the config files, some logs, system configs, get Tigase stats, some HW info, pack it all into an archive file and prepare for us.

I think we are talking about 2 different commands here as you want to check configuration and gather information for us for analysis, while I was talking about command which would verify configuration and check if databases are properly prepared (ie. if schema in newest version is loaded) and if no then command could apply new schema as well (on databases which will need this).

How will use it: anyone? Developers during installation or upgrade, administrators of big installations to verify that schema is ok after reconfiguration or checking what needs to be loaded to database during upgrade of Tigase XMPP Server.

In this case you would create configuration manually (as it is complex task), but you could load schema easily (or verify what needs to be loadad - something like a dry run).

We already have code to load schema and we need to check that Tigase uses correct schema (in proper version during startup). So all we would need to add is detection which databases are used in separate command and verify them.

A couple of comments

• as %kobit mentioned - setup is for a testdrive so the result should be a fully functional Tigase Server afterwards (and functional for all selected components) so in that sense loading schema for all selecte components seems like a good idea (yes, it may generate empty table but user explicitly selected [or not] particular component and there is a step in the installer which informs that schema for particular component was loaded;

• one of my previous comment about re-configuration was based on the idea that at any point in time we allow re-running of the installer (and it updates the configuration) so if the user enables new component which requires schema installer should check if tables/SPs are present and load them (actually SchemaLoader does that already in a way, and SPs are dropped each time only leaving tables intact).

• there is a task #3582 which idea is to enable automatic schema management, so if a user would enable some component, upon startup SchemaLoader would be run internally/automatically and would load appropriate file (it would also be used for handling upgrades; which until 7.1.0 [inclusive] were done from within sourcecode in a dirty way by checking existence of particular columns/values and then applying particular changes.

Taking into consideration last point I think we could settle on:

• loading schema for all selected components in the installer;

• relying on the #3582 to load schema if someone would enable component after running setup.

Remaining problem - how to settle on where to install schema - majority of the installations still use single database for all tigase related schemas and don't leverage separation. Possible use-case for multi-database installation that comes to my mind requires manually loading schema and providing where to install it - which would entail updating our scripts to work correctly -- actually I would remove all-but-one db-create-* shellscripts that we have and leave only one that uses SchemaLoader class which in turn could simply load SchemaLoader and it could handle advanced loading of the schema (in addition to current modus operandi based on passed parameters):

• select component(s);

• select database(s) where to load schema for selected component;

• in the end it would print (like currently) a configuration for all configured components and data sources.

Automatic schema management could still be use for:

• automatic loading of the schema for component to default database (if not configured differently, for convenience);

• applying updates.

Separate topic mentioned - obtaining the configuration and some such - it's out of scope of this ticket and we already provide it - via ad-hocs, which can be accessed via Admin ui for example. (there is a task for "simple status page" which could have plain-and-simple buttons which would copy basic diagnostics and config for copy-past to post to the forums but again, it's a different topic).

Andrzej Wójcik wrote:

I think we are talking about 2 different commands here as you want to check configuration and gather information for us for analysis, while I was talking about command which would verify configuration and check if databases are properly prepared (ie. if schema in newest version is loaded) and if no then command could apply new schema as well (on databases which will need this).

I was just talking about the idea to add additional functions to the Tigase startup script like this:

scripts/tigase.sh apply-schemas etc/tigase.conf

So, in a similar way we could add more functions:

scripts/tigase.sh check-config etc/tigase.conf
scripts/tigase.sh check-dns etc/tigase.conf
scripts/tigase.sh check-clustering etc/tigase.conf
scripts/tigase.sh collect-service-files etc/tigase.conf

Wojciech Kapcia wrote:

Taking into consideration last point I think we could settle on:

• loading schema for all selected components in the installer;

• relying on the #3582 to load schema if someone would enable component after running setup.

If we have well working #3582 why to bother loading schema in the installer? I would prefer to focus on #3582 solution and then we do not need anything else.

Remaining problem - how to settle on where to install schema - majority of the installations still use single database for all tigase related schemas and don't leverage separation. Possible use-case for multi-database installation that comes to my mind requires manually loading schema and providing where to install it - which would entail updating our scripts to work correctly -- actually I would remove all-but-one db-create-* shellscripts that we have and leave only one that uses SchemaLoader class which in turn could simply load SchemaLoader and it could handle advanced loading of the schema (in addition to current modus operandi based on passed parameters):

• select component(s);

• select database(s) where to load schema for selected component;

• in the end it would print (like currently) a configuration for all configured components and data sources.

Ok. Maybe I do not fully understand the issue. However, it seems to me that all databases for all components (for multi-db use-case) should be correctly configured in the Tigase configuration file. So the SchemaLoader class should first load Tigase's config file and based on the configuration, load schema for each component to a correct database.

Automatic schema management could still be use for:

• automatic loading of the schema for component to default database (if not configured differently, for convenience);

• applying updates.

+1

Separate topic mentioned - obtaining the configuration and some such - it's out of scope of this ticket and we already provide it - via ad-hocs, which can be accessed via Admin ui for example. (there is a task for "simple status page" which could have plain-and-simple buttons which would copy basic diagnostics and config for copy-past to post to the forums but again, it's a different topic).

Good point.

Artur Hefczyc wrote:

Wojciech Kapcia wrote:

Taking into consideration last point I think we could settle on:

• loading schema for all selected components in the installer;

• relying on the #3582 to load schema if someone would enable component after running setup.

If we have well working #3582 why to bother loading schema in the installer? I would prefer to focus on #3582 solution and then we do not need anything else.

The original idea behind #3582 was to aid with the upgrade process (instead of dealing with the cumbersome updates within code).

Initial setup however require quite often providing also root credentials to the database (for the purpose of creating appropriate user and granting rights). And while storing tigase db user credentials is unavoidable, storing root credential would be a big NO.

Additionally web installer was created to mimic the original IzPack installer and at that time #3582 wasn't even considered (process of extracting schema started after the webinstaller).

The idea behind having an installer is to get Tigase up-and-running quickly and allow testing while giving a good first impression, hence loading schema at that time IMHO makes sense (and resolving this ticket in this context would mean adding call to SchemaLoader for all the configured components in the default location - right now it's already done for Socks5 an PubSub).

Also, telling from the experience - even seemingly power users seem to prefer some kind of UI to perform installation/management (vide creation of AdminUI) so having something like "I want to enable component X and deploy it to external database" resulting in a form where one could provide details for the new database could be useful.

Remaining problem - how to settle on where to install schema - majority of the installations still use single database for all tigase related schemas and don't leverage separation. Possible use-case for multi-database installation that comes to my mind requires manually loading schema and providing where to install it - which would entail updating our scripts to work correctly -- actually I would remove all-but-one db-create-* shellscripts that we have and leave only one that uses SchemaLoader class which in turn could simply load SchemaLoader and it could handle advanced loading of the schema (in addition to current modus operandi based on passed parameters):

• select component(s);

• select database(s) where to load schema for selected component;

• in the end it would print (like currently) a configuration for all configured components and data sources.

Ok. Maybe I do not fully understand the issue. However, it seems to me that all databases for all components (for multi-db use-case) should be correctly configured in the Tigase configuration file. So the SchemaLoader class should first load Tigase's config file and based on the configuration, load schema for each component to a correct database.

While this seems true and could be useful (enable component, configure data source with particular details you would like to use) remains the issue with db root credentials, which you need if you want to load schema to separate/external/new database (creating appropriate user, granting rights, creating tables and SPs).

So while this works already in SchemaLoader (run manually from command line or via webinstaller as same tool is used) I would say it would be tricky to achieve following use-case:

• we start from some, configured installation;

• we decide to enable additional component and/or externalise it's database;

• we put configuration in etc/init.properties with component configuration and DB URI for that component pointing to separate database;

  • running mentioned scripts/tigase.sh apply-schemas etc/tigase.conf won't work out of the box as we still would need to provide root credentials (so all in all we can simply run scripts/db-schema-* script and pass needed parameters or directly call SchemaLoader class with desired parameters (which is already covered in documentation)

  • bootstrapping it to the startup (automatic check and load if missing) won't work for the same reason as above.

Separate topic mentioned - obtaining the configuration and some such - it's out of scope of this ticket and we already provide it - via ad-hocs, which can be accessed via Admin ui for example. (there is a task for "simple status page" which could have plain-and-simple buttons which would copy basic diagnostics and config for copy-past to post to the forums but again, it's a different topic).

We already have #2103/#2083 and related work, but… it's not finished.

Ok, now I think I fully understand it all. It makes sense to me. The installer calls SchemaLoader to do the DB work. The SchemaLoader can be also run from a command line or even from inside the Tigase server if necessary. Andrzej's suggestion to have something like this:

scripts/tigase.sh apply-schemas etc/tigase.conf

also fits the logic.

Artur Hefczyc wrote:

Ok, now I think I fully understand it all. It makes sense to me. The installer calls SchemaLoader to do the DB work. The SchemaLoader can be also run from a command line or even from inside the Tigase server if necessary.

Yes, correct.

Andrzej's suggestion to have something like this:

[...]

also fits the logic.

Yes, on condition that we would provide root credentials. However I'm not sure if this would be better or simply sticking with already existing db-scripts (@scripts/tigase.sh@ is mostly related with management of the service: start/stop/check state and I'm not sure db/schema fits into this; and also we are still left with the problem of providing credentials).

All in all - what is the decision regarding supporting setting up complete working and functional Tigase with schemas for all components in installer? Given that this is considered "promotional" I would think that it's setting up everything (in single database) would make sense (in comparison to forcing user to setup it manually from console, which defies purpose of the installer IMHO)

All in all - what is the decision regarding supporting setting up complete working and functional Tigase with schemas for all components in installer? Given that this is considered "promotional" I would think that it's setting up everything (in single database) would make sense.

I agree. The installer should setup a complete working and functional Tigase with schemas for all components.

It was agreed that the initial description is correct, with the condition that it will simply load schema to main configured database for all selected components.

It looked like a simple task but while I decide to merge this work with #5460 (as in many parts they overlap) it got tricky as DBSchemaLoader was not so good and was not designed to support Mongo at all. This forced me to reimplement some parts and create generic interfaces to allow schema loading for JDBC and Mongo with upgrade-schema task.

Loading of tigase-server schema was being handled in a different way than loading schema of components which after a short discussion with Wojciech it was decided that it should be unified to simplify schema management.

Moreover, it is not possible to execute the same tigase-server SQL script more than once as it will fail, while according to our new method of schema loading it should work.

I've implemented SchemaManager class and:

• used it in setup to load schemas during initial setup

• created upgrade-schema task of tigase.sh script which loads configuration from a config file, detects defined data sources and required schemas for each data source and then applies those schemas.

This way we have unified solution for initial installation and upgrade.

EDIT: Actually those comments fit better in another issue: #5460. This one can be closed.

As suggested in a comment, closing issue.