Automated upgrade system.

[Mike-Benoit]

Currently you leave upgrading the database to the user and require them to manually run SQL commands to upgrade their database. This is especially error prone (potential for data loss) and tedious if the user has many versions to upgrade. Its also made even more difficult when supporting multiple databases like MySQL and PostgreSQL.

It would be nice to have the installer automatically detect that an upgrade is taking place and handle this all for the user automatically, so the original install process and the upgrade process can essentially be the same procedure from the users perspective.

After all, the easier you make the installation and upgrade procedure, the more users will use the software, and of course the higher potential for donations.

ezcDB can already handle this for you in a really simple way:

The following code will dump an existing database schema to an normalized .XML file, so you can use that just before you release a new version:

$db = ezcDbInstance::get();
$schema = ezcDbSchema::createFromDb( $db );
$schema->writeToFile( 'xml', 'lhc_db_schema.xml' );

Then for users to upgrade, you can have the existing installer simply detect that an upgrade is needed and run the following code to calculate the differences in schema and bring it up to date with the latest version:

$db = ezcDbInstance::get();
$xmlSchema = ezcDbSchema::createFromFile( 'xml', 'lhc_db_schema.xml' );
$dbSchema = ezcDbSchema::createFromDb( $db );
$diff = ezcDbSchemaComparator::compareSchemas( $dbSchema, $xmlSchema );
$diff->applyToDb( $db );

Given the above you wouldn't need to handle SQL schema changes manually ever again, and it will work the exact same for both MySQL and PostgreSQL, and be fully automated for end users.

This could even be the basis for a fully automated end-user upgrade system combined with GIT, with something as simple as this:
/var/www/lhc/git pull && php cron.php -s site_admin -c upgrade_schema

If enabled, that could even be run from cron or a button on the LHC admin page. I'm seeing more and more projects provide transparent upgrades in a similar way.

Finally, this should make it much easier to combine the MySQL and PostgreSQL branches into a single one to easier maintenance, and also open the doors for supporting other databases in the future.

[remdex]

At the moment mysql version either automated hosting or standalone does not require users to run manual SQL queries. For standalone update there is documentation how to do that from back office. As for automated hosting this can be done using
php cron.php -s site_admin -e instance -c cron/update_structure

And it will upgrade mysql automated hosting structure table.

So i'm not sure about what exactly enviroment you are talking :)

[remdex]

In my automated hosting enviroment to update everything is enough just execute these commands.
cd /var/www/admin/automated-hosting && git pull origin master
cd /var/www/manager/ && git pull origin master
cd /var/www/client/lhc_web && git pull origin master
cd /var/www/manager/lhc_web && php cron.php -s site_admin -c cron/util/update_database
cd /var/www/manager/lhc_web && php cron.php -s site_admin -e instance -c cron/update_structure
cd /var/www/manager/lhc_web && php cron.php -s site_admin -e instance -c cron/update_instances
​cd /var/www/manager/lhc_web && php cron.php -s site_admin -e instance -c cron/extensions_update
cd /var/www/manager/lhc_web && php cron.php -s site_admin -c cron/util/clear_cache
cd /var/www/client/lhc_web && php cron.php -s site_admin -c cron/util/clear_cache

and boom manager and clients instances are updated. End of story :)

[Mike-Benoit]

What about PostgreSQL though? The above suggestion would allow you to use any database in the same way, without having to manually code any SQL queries to update the schema.

Or you could at least use the above system to semi-manually create the SQL queries, which you dump to .SQL files to help perform the automated upgrades.

[remdex]

The thing is. For database management in the future i would prefer laravel framework itself and rely even more on eZComponents does not make any sense to me just... Just that's the way it is now. And i'm just running out of time past six month or year :)

[Mike-Benoit]

Laravel has similar ways to handle schemas across databases too, so if you simply used the above to create the .SQL files used for automatic upgrades, when you migrate away from eZC it shouldn't be a problem at all. You could move away from it really easy, or even continue to use it just for generating schema files, you wouldn't need to bundle eZC to end-users anymore.

The problem is that currently PostgreSQL support isn't really designed for production as there is no easy way to upgrade. By creating a simple 5 line PHP CLI script you could have it create the necessary .SQL files to perform an automated upgrade.

Once that is in place, you could work towards merging the PostgreSQL and MySQL branches into one (using standard SQL queries which are easy to do), so its much easier to maintain and takes less time/work for you.

Most projects that support multiple databases do it in a single code branch, it doesn't make sense to do it in separate branches as it takes way too much time and is more likely to cause bugs. Doing it all in one branch should give you multiple database support for virtually no extra effort. Handling the schema changes is the hardest part, but you only need to "fix" that issue once.

[remdex]

Yes I know all this. Just as O told i'm running out of time. If you make such changes I would gladly review your pull request, just by myself I do not have time for all this at the moment.