OpenPetra standalone and SQLite   July 30th, 2014

I have seen on the OpenPetra IRC logs that there have been discussions about SQLite related to the new 0.3.0 release.

2 main points:

  1. functionality does work with PostgreSQL, but not with SQLite
  2. no documented upgrade path for SQLite, which is used in OpenPetra standalone

Regarding the first point:

I think it is good to support several databases, because it helps to find bugs, or unclean development. For example PostgreSQL is not so strict with transactions, but SQLite shows us when the transactions have not been opened correctly.

Another advantage: In the future there might be support for even more databases, for example MS-SQL. Then we will be in trouble with SQL code that uses PostgreSQL specific syntax. So it is good to find these problems now and solve them when the developer who wrote the SQL still knows about it.

To solve these issues, I think it is important to maintain automated integration for the winform screens. After all, we have a nightly build of the integration tests every night running against both PostgreSQL and SQLite separately. That way we should find such issues quite soon.

The second point, with the upgrade path:

There has been work on doing an upgrade of the database during the installation of the new OpenPetra standalone version, see http://www.openpetra.org/fifteenth-preview-release-of-openpetra-org and https://tracker.openpetra.org/view.php?id=113

Unfortunately, this caused too much trouble, and since the standalone installer is only intended for demo purposes, we did not support this upgrade functionality any longer.
For the longterm, the question is anyway whether the standalone installer should be maintained by the community and not by OM anymore, since OM focuses on the client/server solution with PostgreSQL rather than SQLite for production. When we had the discussions about the web services branch, support for the current standalone installer would have been dropped anyways by OM, because that would still quite some work to make it work with the single exe application.

Currently the only way is to export the data in the OpenPetra client of the previous version (login as user SYSADMIN, go to SystemManager, Database, Import/Export, Export all Data).

Then install the new OpenPetra standalone. You would get this message box when starting OpenPetra:

Unsupported upgrade: Please rename the file C:\Users\tpokorra\AppData\Roaming\OpenPetra.org0.2.28.1\db30\petra.db so that we can start with a fresh database!
Please restart the OpenPetra Client after that.

So follow this instruction, and start with a fresh database. Login with user SYSADMIN again, and import your database that you just exported earlier (SystemManager, Database, Import/Export, Restore Database from Backup)

I am personally more interested in hosting OpenPetra in the future on my own servers for free for individuals or a small hosting fee for organisations. Therefore I am not going to invest a lot into the Standalone installer.

Tags: ,
Posted in Software Development | Comments Closed