[xwiki-devs] Which database engine should be the default for 3.0?
Hi devs, The default standalone distribution currently uses HSQLDB 1.8 as the database engine, wrapped by Hibernate 3.2.6 as the ORM engine. Both are a bit deprecated. One option is to use the 2.0 version of HSQLDB, which brings lots of improvements. Unfortunately, a direct replacement of the old jar with the new one doesn't work. It complains when trying to open an existing database, and even with a new database it complains when using boolean fields in queries, which means all searchDocuments calls will fail because of the "doc.hidden <> true" condition. Another option is to use H2, which is a reimplementation of HSQLDB, originally intended to be version 2 of HSQLDB. On their http://www.h2database.com/html/features.html#comparison feature comparison, H2 seems to have more meta-features than HSQLDB2. A third option which was already partially implemented is to use Derby. Personally I prefer H2, which seems to be more maintained than the others, with new releases every 2-3 weeks, and also has better performance and more features. -- Sergiu Dumitriu http://purl.org/net/sergiu/
The correct version of HSQLDB for upgrades is 2.0.1. By default it is compatible with databases generated with older versions. Upgrades have been very extensively tested (especially by the OpenOffice.org community). There are even connection property flags to make it work the same as 1.8.0 when necessary. If you use Hibernate 3.6.0 with 2.0.1, you should get good results. HSQLDB is very actively maintained. The SVN head is kept as production quality and every few weeks there has been a snapshot or release candidate. Regarding your other statements, H2 was a separate project from the start and could never be the version 2 of HSQLDB. The feature matrix on the H2 site contains a couple of features that are not built in HSQLDB but can be done with addons (for example Lucene search can be done with Hibernate). In fact HSQLDB has a wide range of features that are not found in H2 or Derby, for example SQL routines and multi-statement triggers. The performance tests there do not look convincing either as most of the reported time is used inserting bulk data in a smallish database. When database size goes over 100 MB or so, the results are very different. HSQLDB has a feature that would be uniquely useful to content management, which is a physically separate lob store. This preforms very well and can handle thousands of very large lobs, all in a single file. In short HSQLDB 2.0.1 is very fast and effective for database sizes up to about 500MB plus multiple gigabytes of lobs. Regards Fred Toussi HSQLDB Project On Mon, 06 Dec 2010 02:26 +0100, "Sergiu Dumitriu" <[email protected]> wrote:
Hi devs,
The default standalone distribution currently uses HSQLDB 1.8 as the database engine, wrapped by Hibernate 3.2.6 as the ORM engine. Both are a bit deprecated.
One option is to use the 2.0 version of HSQLDB, which brings lots of improvements. Unfortunately, a direct replacement of the old jar with the new one doesn't work. It complains when trying to open an existing database, and even with a new database it complains when using boolean fields in queries, which means all searchDocuments calls will fail because of the "doc.hidden <> true" condition.
Another option is to use H2, which is a reimplementation of HSQLDB, originally intended to be version 2 of HSQLDB. On their http://www.h2database.com/html/features.html#comparison feature comparison, H2 seems to have more meta-features than HSQLDB2.
A third option which was already partially implemented is to use Derby.
Personally I prefer H2, which seems to be more maintained than the others, with new releases every 2-3 weeks, and also has better performance and more features.
-- Sergiu Dumitriu http://purl.org/net/sergiu/ _______________________________________________ devs mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/devs
Hi Fred, thanks for your input on this. On 12/06/2010 04:19 AM, Fred Toussi wrote:
The correct version of HSQLDB for upgrades is 2.0.1.
Yep, 2.0 is just the major version number, we're always using the latest version available on the central repository.
By default it is compatible with databases generated with older versions. Upgrades have been very extensively tested (especially by the OpenOffice.org community). There are even connection property flags to make it work the same as 1.8.0 when necessary.
Well, first error I got was: java.sql.SQLException: old version database must be shutdown This might be caused by the fact that we're not closing the database on shutdown, so it's a bad usage on our side. Do you have a quick fix for this? The second error is the boolean comparison error, which might be caused by the fact that the old database was created with a BOOLEAN column, while the new version uses BIT. Any hints on that? Will an upgrade for Hiberate automatically take care of this?
If you use Hibernate 3.6.0 with 2.0.1, you should get good results.
HSQLDB is very actively maintained. The SVN head is kept as production quality and every few weeks there has been a snapshot or release candidate.
OK, I said that H2 was more actively maintained because between 1.8 and 2.0 there have been several years. I know that the complete core rewrite and the new transaction mechanisms took a lot of time, but for those not following the progress of HSQLDB, it looked like the project wasn't as active as it should be.
Regarding your other statements, H2 was a separate project from the start and could never be the version 2 of HSQLDB.
Right, I expressed myself incorrectly.
The feature matrix on the H2 site contains a couple of features that are not built in HSQLDB but can be done with addons (for example Lucene search can be done with Hibernate). In fact HSQLDB has a wide range of features that are not found in H2 or Derby, for example SQL routines and multi-statement triggers. The performance tests there do not look convincing either as most of the reported time is used inserting bulk data in a smallish database. When database size goes over 100 MB or so, the results are very different.
HSQLDB has a feature that would be uniquely useful to content management, which is a physically separate lob store. This preforms very well and can handle thousands of very large lobs, all in a single file. In short HSQLDB 2.0.1 is very fast and effective for database sizes up to about 500MB plus multiple gigabytes of lobs.
OK, thanks for this information. I think that in 2.0.1 it is also possible to have several schemas with the same database connection, which is needed for our virtual wikis setup. Is that correct?
Regards
Fred Toussi HSQLDB Project
On Mon, 06 Dec 2010 02:26 +0100, "Sergiu Dumitriu"<[email protected]> wrote:
Hi devs,
The default standalone distribution currently uses HSQLDB 1.8 as the database engine, wrapped by Hibernate 3.2.6 as the ORM engine. Both are a bit deprecated.
One option is to use the 2.0 version of HSQLDB, which brings lots of improvements. Unfortunately, a direct replacement of the old jar with the new one doesn't work. It complains when trying to open an existing database, and even with a new database it complains when using boolean fields in queries, which means all searchDocuments calls will fail because of the "doc.hidden<> true" condition.
Another option is to use H2, which is a reimplementation of HSQLDB, originally intended to be version 2 of HSQLDB. On their http://www.h2database.com/html/features.html#comparison feature comparison, H2 seems to have more meta-features than HSQLDB2.
A third option which was already partially implemented is to use Derby.
Personally I prefer H2, which seems to be more maintained than the others, with new releases every 2-3 weeks, and also has better performance and more features.
-- Sergiu Dumitriu http://purl.org/net/sergiu/
Hi Sergiu, Focusing on the issues: 1. Upgrade without proper shutdown. I think we could allow this, and advise users to perform a SHUTDOWN. I'll check the implications and may have a solution in a few days. 2. Regarding BOOLEAN and BIT. The 2.0.x supports both. The new Hibernate 3.6.0 dialect should generate BOOLEAN. I think it will work fine with database upgrades, but you should test and let me know if there's an issue. 3. Version 2.0.x supports multiple schemas. All such features of 1.8.x have been kept. 4. You may want to use the new online database backup feature, which is very popular among users. Fred On Mon, 06 Dec 2010 17:03 +0100, "Sergiu Dumitriu" <[email protected]> wrote:
Hi Fred, thanks for your input on this.
On 12/06/2010 04:19 AM, Fred Toussi wrote:
The correct version of HSQLDB for upgrades is 2.0.1.
Yep, 2.0 is just the major version number, we're always using the latest version available on the central repository.
By default it is compatible with databases generated with older versions. Upgrades have been very extensively tested (especially by the OpenOffice.org community). There are even connection property flags to make it work the same as 1.8.0 when necessary.
Well, first error I got was:
java.sql.SQLException: old version database must be shutdown
This might be caused by the fact that we're not closing the database on shutdown, so it's a bad usage on our side. Do you have a quick fix for this?
The second error is the boolean comparison error, which might be caused by the fact that the old database was created with a BOOLEAN column, while the new version uses BIT. Any hints on that? Will an upgrade for Hiberate automatically take care of this?
If you use Hibernate 3.6.0 with 2.0.1, you should get good results.
HSQLDB is very actively maintained. The SVN head is kept as production quality and every few weeks there has been a snapshot or release candidate.
OK, I said that H2 was more actively maintained because between 1.8 and 2.0 there have been several years. I know that the complete core rewrite and the new transaction mechanisms took a lot of time, but for those not following the progress of HSQLDB, it looked like the project wasn't as active as it should be.
Regarding your other statements, H2 was a separate project from the start and could never be the version 2 of HSQLDB.
Right, I expressed myself incorrectly.
The feature matrix on the H2 site contains a couple of features that are not built in HSQLDB but can be done with addons (for example Lucene search can be done with Hibernate). In fact HSQLDB has a wide range of features that are not found in H2 or Derby, for example SQL routines and multi-statement triggers. The performance tests there do not look convincing either as most of the reported time is used inserting bulk data in a smallish database. When database size goes over 100 MB or so, the results are very different.
HSQLDB has a feature that would be uniquely useful to content management, which is a physically separate lob store. This preforms very well and can handle thousands of very large lobs, all in a single file. In short HSQLDB 2.0.1 is very fast and effective for database sizes up to about 500MB plus multiple gigabytes of lobs.
OK, thanks for this information. I think that in 2.0.1 it is also possible to have several schemas with the same database connection, which is needed for our virtual wikis setup. Is that correct?
Regards
Fred Toussi HSQLDB Project
On Mon, 06 Dec 2010 02:26 +0100, "Sergiu Dumitriu"<[email protected]> wrote:
Hi devs,
The default standalone distribution currently uses HSQLDB 1.8 as the database engine, wrapped by Hibernate 3.2.6 as the ORM engine. Both are a bit deprecated.
One option is to use the 2.0 version of HSQLDB, which brings lots of improvements. Unfortunately, a direct replacement of the old jar with the new one doesn't work. It complains when trying to open an existing database, and even with a new database it complains when using boolean fields in queries, which means all searchDocuments calls will fail because of the "doc.hidden<> true" condition.
Another option is to use H2, which is a reimplementation of HSQLDB, originally intended to be version 2 of HSQLDB. On their http://www.h2database.com/html/features.html#comparison feature comparison, H2 seems to have more meta-features than HSQLDB2.
A third option which was already partially implemented is to use Derby.
Personally I prefer H2, which seems to be more maintained than the others, with new releases every 2-3 weeks, and also has better performance and more features.
-- Sergiu Dumitriu http://purl.org/net/sergiu/
HSQLDB 2.0.1 RC3 was released today. Code has been updated to handle databases that were created with 1.8.0.x without shutdown, but the version check still remains until further tests are done for the 2.0.1 GA version. Currently, you can manually change the version string inside the db.properties file from version=1.8.0 to version=1.8.1 before opening the database with version 2.0.1 engine. If you find any issues with upgrade to HSQLDB 2.0.1 and Hibernate 3.6.0, please let me know and I will follow it up. Fred On Mon, 06 Dec 2010 18:30 +0000, "Fred Toussi" <[email protected]> wrote:
Hi Sergiu,
Focusing on the issues:
1. Upgrade without proper shutdown. I think we could allow this, and advise users to perform a SHUTDOWN. I'll check the implications and may have a solution in a few days. 2. Regarding BOOLEAN and BIT. The 2.0.x supports both. The new Hibernate 3.6.0 dialect should generate BOOLEAN. I think it will work fine with database upgrades, but you should test and let me know if there's an issue. 3. Version 2.0.x supports multiple schemas. All such features of 1.8.x have been kept. 4. You may want to use the new online database backup feature, which is very popular among users.
Fred
On Mon, 06 Dec 2010 17:03 +0100, "Sergiu Dumitriu" <[email protected]> wrote:
Hi Fred, thanks for your input on this.
On 12/06/2010 04:19 AM, Fred Toussi wrote:
The correct version of HSQLDB for upgrades is 2.0.1.
Yep, 2.0 is just the major version number, we're always using the latest version available on the central repository.
By default it is compatible with databases generated with older versions. Upgrades have been very extensively tested (especially by the OpenOffice.org community). There are even connection property flags to make it work the same as 1.8.0 when necessary.
Well, first error I got was:
java.sql.SQLException: old version database must be shutdown
This might be caused by the fact that we're not closing the database on shutdown, so it's a bad usage on our side. Do you have a quick fix for this?
The second error is the boolean comparison error, which might be caused by the fact that the old database was created with a BOOLEAN column, while the new version uses BIT. Any hints on that? Will an upgrade for Hiberate automatically take care of this?
If you use Hibernate 3.6.0 with 2.0.1, you should get good results.
HSQLDB is very actively maintained. The SVN head is kept as production quality and every few weeks there has been a snapshot or release candidate.
OK, I said that H2 was more actively maintained because between 1.8 and 2.0 there have been several years. I know that the complete core rewrite and the new transaction mechanisms took a lot of time, but for those not following the progress of HSQLDB, it looked like the project wasn't as active as it should be.
Regarding your other statements, H2 was a separate project from the start and could never be the version 2 of HSQLDB.
Right, I expressed myself incorrectly.
The feature matrix on the H2 site contains a couple of features that are not built in HSQLDB but can be done with addons (for example Lucene search can be done with Hibernate). In fact HSQLDB has a wide range of features that are not found in H2 or Derby, for example SQL routines and multi-statement triggers. The performance tests there do not look convincing either as most of the reported time is used inserting bulk data in a smallish database. When database size goes over 100 MB or so, the results are very different.
HSQLDB has a feature that would be uniquely useful to content management, which is a physically separate lob store. This preforms very well and can handle thousands of very large lobs, all in a single file. In short HSQLDB 2.0.1 is very fast and effective for database sizes up to about 500MB plus multiple gigabytes of lobs.
OK, thanks for this information. I think that in 2.0.1 it is also possible to have several schemas with the same database connection, which is needed for our virtual wikis setup. Is that correct?
Regards
Fred Toussi HSQLDB Project
On Mon, 06 Dec 2010 02:26 +0100, "Sergiu Dumitriu"<[email protected]> wrote:
Hi devs,
The default standalone distribution currently uses HSQLDB 1.8 as the database engine, wrapped by Hibernate 3.2.6 as the ORM engine. Both are a bit deprecated.
One option is to use the 2.0 version of HSQLDB, which brings lots of improvements. Unfortunately, a direct replacement of the old jar with the new one doesn't work. It complains when trying to open an existing database, and even with a new database it complains when using boolean fields in queries, which means all searchDocuments calls will fail because of the "doc.hidden<> true" condition.
Another option is to use H2, which is a reimplementation of HSQLDB, originally intended to be version 2 of HSQLDB. On their http://www.h2database.com/html/features.html#comparison feature comparison, H2 seems to have more meta-features than HSQLDB2.
A third option which was already partially implemented is to use Derby.
Personally I prefer H2, which seems to be more maintained than the others, with new releases every 2-3 weeks, and also has better performance and more features.
-- Sergiu Dumitriu http://purl.org/net/sergiu/
_______________________________________________ devs mailing list [email protected] http://lists.xwiki.org/mailman/listinfo/devs
participants (2)
-
Fred Toussi -
Sergiu Dumitriu