[GeoNetwork-devel] Replacement of McKoi with H2

GeoNetwork GeoNetwork Developer
1

Hi

As commented in IRC yesterday, I have done some testing with H2 database and GeoNetwork. I tested metadata import/editing/searching and groups/users/categories/settings management and seem ok.

H2 allows to connect to a directly to a database file or run a server. I tested the first option, but has some drawbacks, for example can’t connect to database from another process (like SQL client) while GeoNetwork is running and has the database locked.

If we use a server this is not a problem. To run a server I think we should develop an H2 Activator like now for McKoi to startup the database when starting GeoNetwork.

Should be good to hear some opinions about using H2 (or another database) to replace McKoi.

Regards,
Jose Garcia

2

Hello Jose, I made some test using sqlite [1] and its jdbc driver [2].

Setting the resource is easy
<resource enabled="true">
          <name>main-db</name>
          <provider>jeeves.resources.dbms.DbmsPool</provider>
          <config>
              <user></user>
              <password></password>
              <driver>org.sqlite.JDBC</driver>
              <url>jdbc:sqlite:../web/geonetwork/WEB-INF/db.sqlite</url>
              <poolSize>10</poolSize>
              <reconnectTime>3600</reconnectTime>
          </config>
      </resource>

Create the db using SQL scripts.

2010/5/12 jose garcia <josegar74@anonymised.com>:

Hi
As commented in IRC yesterday, I have done some testing with H2 database and
GeoNetwork. I tested metadata import/editing/searching and
groups/users/categories/settings management and seem ok.

It was ok until I tried to run multiple harvesting tasks. Then I get
some database locked.
java.sql.SQLException: [SQLITE_BUSY] The database file is locked
(database is locked)

Could you test that with H2 and see what happens ?

H2 allows to connect to a directly to a database file or run a server. I
tested the first option, but has some drawbacks, for example can't connect
to database from another process (like SQL client) while GeoNetwork is
running and has the database locked.

The sqlite database is locked only when having transactions. So you
could still access the db with another app when not locked.

If we use a server this is not a problem. To run a server I think we
should develop an H2 Activator like now for McKoi to startup the database
when starting GeoNetwork.
Should be good to hear some opinions about using H2 (or another database) to
replace McKoi.

Sqlite could be also nice to store the spatial index using spatialite
(I did not test that yet but GeoTools provide a driver for that).

I had a look to the license and H2 is licensed under modified MPL1.1
and EPL and sqlite JDBC driver under Apache 2.0.

Ciao.

Francois

[1] http://www.sqlite.org/features.html
[2] http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC

Regards,
Jose Garcia

------------------------------------------------------------------------------

_______________________________________________
GeoNetwork-devel mailing list
GeoNetwork-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geonetwork-devel
GeoNetwork OpenSource is maintained at
http://sourceforge.net/projects/geonetwork

3

Hi Francois

Thanks for testing with SQLite.

Going to test multiple harvesters with H2. The problem with SQLite I think is that is a file based database, so probably only one process can lock it.

I think with H2 using file db should happen the same, but not with H2 server. I’ll test and send the results.

Regards,
Jose Garcia

On Wed, May 12, 2010 at 6:17 PM, Francois Prunayre <fx.prunayre@anonymised.com> wrote:

Hello Jose, I made some test using sqlite [1] and its jdbc driver [2].

Setting the resource is easy

main-db
jeeves.resources.dbms.DbmsPool



org.sqlite.JDBC
jdbc:sqlite:…/web/geonetwork/WEB-INF/db.sqlite
10
3600

Create the db using SQL scripts.

2010/5/12 jose garcia <josegar74@anonymised.com…31…>:

Hi
As commented in IRC yesterday, I have done some testing with H2 database and
GeoNetwork. I tested metadata import/editing/searching and
groups/users/categories/settings management and seem ok.

It was ok until I tried to run multiple harvesting tasks. Then I get
some database locked.
java.sql.SQLException: [SQLITE_BUSY] The database file is locked
(database is locked)

Could you test that with H2 and see what happens ?

H2 allows to connect to a directly to a database file or run a server. I
tested the first option, but has some drawbacks, for example can’t connect
to database from another process (like SQL client) while GeoNetwork is
running and has the database locked.

The sqlite database is locked only when having transactions. So you
could still access the db with another app when not locked.

If we use a server this is not a problem. To run a server I think we
should develop an H2 Activator like now for McKoi to startup the database
when starting GeoNetwork.
Should be good to hear some opinions about using H2 (or another database) to
replace McKoi.

Sqlite could be also nice to store the spatial index using spatialite
(I did not test that yet but GeoTools provide a driver for that).

I had a look to the license and H2 is licensed under modified MPL1.1
and EPL and sqlite JDBC driver under Apache 2.0.

Ciao.

Francois

[1] http://www.sqlite.org/features.html
[2] http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC

Regards,
Jose Garcia

GeoNetwork-devel mailing list
GeoNetwork-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geonetwork-devel
GeoNetwork OpenSource is maintained at
http://sourceforge.net/projects/geonetwork

4

Hi Francois

I ran multiple harvesters with H2 and got this:

Timeout trying to lock table “SETTINGS”; SQL statement:

UPDATE Settings SET value=? WHERE id=? [50200-135]

both using Embedded Mode and Server Mode.

If MVCC=TRUE parameter is specified in connection url then uses Row Level Locking instead of default Table Level Locking (see http://www.h2database.com/html/features.html#comparison):

main-db
jeeves.resources.dbms.DbmsPool

sa

org.h2.Driver
jdbc:h2:geonetwork;MVCC=TRUE
10
3600

With this parameter multiple harvesters run without problem. Also tried creating/deleting metadata while harvesting, working ok.

The drawback seems that this feature is not fully tested yet (see http://www.h2database.com/html/advanced.html#mvcc)

In the manual (see http://www.h2database.com/html/features.html#multiple_connections) states also that Server Mode should be used for multiple application/processes accessing db, but at least for running multiple harvesters seemed ok with Embedded Mode. Anyway I’ll do some more testing.

About the license type not sure the implications.

Regards,
Jose García

On Wed, May 12, 2010 at 9:42 PM, jose garcia <josegar74@anonymised.com> wrote:

Hi Francois

Thanks for testing with SQLite.

Going to test multiple harvesters with H2. The problem with SQLite I think is that is a file based database, so probably only one process can lock it.

I think with H2 using file db should happen the same, but not with H2 server. I’ll test and send the results.

Regards,
Jose Garcia

On Wed, May 12, 2010 at 6:17 PM, Francois Prunayre <fx.prunayre@anonymised.com.> wrote:

Hello Jose, I made some test using sqlite [1] and its jdbc driver [2].

Setting the resource is easy

main-db
jeeves.resources.dbms.DbmsPool



org.sqlite.JDBC
jdbc:sqlite:…/web/geonetwork/WEB-INF/db.sqlite
10
3600

Create the db using SQL scripts.

2010/5/12 jose garcia <josegar74@anonymised.com>:

Hi
As commented in IRC yesterday, I have done some testing with H2 database and
GeoNetwork. I tested metadata import/editing/searching and
groups/users/categories/settings management and seem ok.

It was ok until I tried to run multiple harvesting tasks. Then I get
some database locked.
java.sql.SQLException: [SQLITE_BUSY] The database file is locked
(database is locked)

Could you test that with H2 and see what happens ?

H2 allows to connect to a directly to a database file or run a server. I
tested the first option, but has some drawbacks, for example can’t connect
to database from another process (like SQL client) while GeoNetwork is
running and has the database locked.

The sqlite database is locked only when having transactions. So you
could still access the db with another app when not locked.

If we use a server this is not a problem. To run a server I think we
should develop an H2 Activator like now for McKoi to startup the database
when starting GeoNetwork.
Should be good to hear some opinions about using H2 (or another database) to
replace McKoi.

Sqlite could be also nice to store the spatial index using spatialite
(I did not test that yet but GeoTools provide a driver for that).

I had a look to the license and H2 is licensed under modified MPL1.1
and EPL and sqlite JDBC driver under Apache 2.0.

Ciao.

Francois

[1] http://www.sqlite.org/features.html
[2] http://www.xerial.org/trac/Xerial/wiki/SQLiteJDBC

Regards,
Jose Garcia

GeoNetwork-devel mailing list
GeoNetwork-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geonetwork-devel
GeoNetwork OpenSource is maintained at
http://sourceforge.net/projects/geonetwork