User Tools

Site Tools


en:user_advanced:greenstone_databases

Information about the databases Greenstone uses

Greenstone comes with several database systems included: gdbm, jdbm, SQLite. When building a collection, you can control which of these is used. In GS3, the default is jdbm.

In terms of user account management, GS3 uses "Java DB" that's bundled with Java (since an early version of Java), see https://en.wikipedia.org/wiki/Apache_Derby. More specifically, Greenstone3 uses JDBC to talk to the user database. So it is possible to work with other relational database systems. However, we find that Java DB meets our needs.

It's also possible to import content from external databases such as MySQL using the DatabasePlugin, for which the DBI perl module is used.

Note that Greenstone databases with gdb and jdb extensions are flat databases, not relational DBs. So that's Greenstone's collection level databases and GS2's user and history databases.

Using Squirrel SQL Client to manage Relational Databases

If you wanted an application with a friendly interface to manage any relational databases that Greenstone creates, then try Squirrel SQL Client, which is for relational databases.

The following instructions on using Squirrel SQL Client are written from a Unix perspective, but it supports Windows too, so do the equivalent windows task at each stage.

1. Download the "optional" (or "standard") zipped version from https://sourceforge.net/projects/squirrel-sql/files/1-stable/3.7.1-plainzip/

2. Extract the zip

3. Give execute permissions to the squirrel-sql.sh in the top level of the extracted folder:

  chmod u+x squirrel-sql.sh
  

4. Copy derbyclient.jar from your Greenstone3/web/WEB-INF/lib to your Squirrel SQL installation folder's lib subfolder.

If you want to connect to the GS3 UserDB:

5. Run the GS3 server, this runs the derby network server for the userDB

6. Use a text editor to open your GS3 installation's packages/tomcat/conf/Catalina/localhost/greenstone3.xml file. Copy the line in there that looks like

  connectionURL="jdbc:derby://localhost:1527//.../web/etc/usersDB"
  

7. Run the squirrel-sql application from a terminal where you've navigated into the Squirrel SQL installation folder:

  ./squirrel-sql.sh
  

8. Click on the "Drivers" tab, leftmost in the interface. (The word Drivers is vertically positioned)

9. Select Apache Derby Client and rightclick > Modify Driver. In the 2nd field, paste the text you copied in step 6 above. Then click OK to close the Modify Driver dialog.

10. Click the Aliases tab, again leftmost in the interface and above the Drivers tab.

11. Press the + button to add a new Alias. Give it a name like GSusersDB. Ensure that Apache Derby Client is the selected driver. Ensure the URL field contains what you pasted in step 9, else paste it here too. Click OK to finish the dialog.

12. A new Alias will have been created with the name you provided, e.g. GSusersDB. Select and rightclick on it > Connect. Type in your GS3 admin username and pwd. And press connect. It will load the contents of your DB.

For further information on using Squirrel SQL, consult its documentation, which can be found off http://squirrel-sql.sourceforge.net/.

Using Squirrel SQL Client to load a MySQL DB

On Windows:

1. Download JDBC driver for MySQL from https://dev.mysql.com/downloads/connector/j/

  • Under Select Operating System, choose Platform Independent.

See https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-installing.html for installation information

2. Unzip and copy the top level jar file, mysql-connector-java-8.0.13.jar and the lib folder into the Squirrel SQL Client installation folder's lib subfolder.

3. Follow the instructions at https://www.cdata.com/kb/tech/mysql-jdbc-squirrel-sql.rst
but when they say configure the "server" for connection properties, configure the "host" instead and set this to 127.0.0.1 instead of the default of localhost. Also set the username (root by default) and pwd connection properties. (Perhaps set the name property of the database too, although this shouldn't be necessary? If testing GreenstoneSQLPlugs, the database name is localsite for GS3 and greenstone2 for GS2.)

4. Make sure to have the MySQL Server running

5. Connect the Squirrel Client to it. https://www.cdata.com/kb/tech/mysql-jdbc-squirrel-sql.rst

en/user_advanced/greenstone_databases.txt · Last modified: 2023/03/13 01:46 by 127.0.0.1