User Tools

Site Tools


en:user_advanced:greenstone_databases

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision
Previous revision
en:user_advanced:greenstone_databases [2017/09/08 08:37] – [Using Squirrel SQL Client to manage Relational Databases] anupamaen:user_advanced:greenstone_databases [2023/03/13 01:46] (current) – external edit 127.0.0.1
Line 1: Line 1:
 +
 +
 +
 +====== Information about the databases Greenstone uses ======
 +
 Greenstone comes with several database systems included: [[http://wiki.greenstone.org/doku.php?id=en:user_advanced:collection_database|gdbm, jdbm, SQLite]]. When building a collection, you can control which of these is used. In GS3, the default is jdbm. Greenstone comes with several database systems included: [[http://wiki.greenstone.org/doku.php?id=en:user_advanced:collection_database|gdbm, jdbm, SQLite]]. When building a collection, you can control which of these is used. In GS3, the default is jdbm.
  
Line 7: Line 12:
 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. 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 ===+===== 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. 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.
  
Line 13: Line 18:
  
 1. Download the "optional" (or "standard") zipped version from https://sourceforge.net/projects/squirrel-sql/files/1-stable/3.7.1-plainzip/ 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 2. Extract the zip
 +
 3. Give execute permissions to the squirrel-sql.sh in the top level of the extracted folder: 3. Give execute permissions to the squirrel-sql.sh in the top level of the extracted folder:
     chmod u+x squirrel-sql.sh     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. 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:+ 
 +//If you want to connect to the GS3 UserDB:// 
 5. Run the GS3 server, this runs the derby network server for the 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 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"     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: 7. Run the squirrel-sql application from a terminal where you've navigated into the Squirrel SQL installation folder:
     ./squirrel-sql.sh     ./squirrel-sql.sh
 +    
 8. Click on the "Drivers" tab, leftmost in the interface. (The word Drivers is vertically positioned) 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. 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. 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. 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. 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/. 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 [[http://wiki.greenstone.org/doku.php?id=en:user_advanced:greenstonesqlplugs#getting_and_running_mysql|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.1504859861.txt.gz · Last modified: 2017/09/08 08:37 by anupama