database.html 15.6 KB
Newer Older
Matt Tucker's avatar
Matt Tucker committed
1 2 3 4 5 6 7 8 9
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
	<title>Jive Messenger Database Documentation</title>
  <link href="style.css" rel="stylesheet" type="text/css">
</head>
<body>
<h1><a name="introduction">Jive Messenger Database Setup</a></h1>

Matt Tucker's avatar
Matt Tucker committed
10 11
<p>Jive Messenger stores all data in a back-end database. If you choose to not use the embedded
database, you must setup and manage an external database for the application.
Matt Tucker's avatar
Matt Tucker committed
12 13 14 15
This document provides setup instructions for all of the currently supported databases.
</p>
<p>
JDBC 2.0 drivers are required for Jive Messenger to communicate with your database. Suggested
Matt Tucker's avatar
Matt Tucker committed
16 17
drivers for particular databases are noted below where applicable. Jive Messenger bundles JDBC
drivers for MySQL, Postgres, and HSQLDB.
Matt Tucker's avatar
Matt Tucker committed
18 19 20 21 22 23 24
</p>
<p>
For a full list of available JDBC drivers, please see:
<a href="http://industry.java.sun.com/products/jdbc/drivers">
http://industry.java.sun.com/products/jdbc/drivers</a>.</p>

<p>
25
All database schemas can be found in the <tt>resources/database</tt> directory of the
Matt Tucker's avatar
Matt Tucker committed
26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52
Jive Messenger installation.
</p>

<p>Note that additional databases besides the ones in this list may be supported.
Please check the distribution for a database schema for your database or
consider creating a schema yourself if one isn't provided.
</p>
<p>
Choose your database from the list below for setup details:
</p>
<ul>
	<li> <a href="#mysql">MySQL</a>
	<li> <a href="#oracle">Oracle</a>
	<li> <a href="#sqlserver2000">Microsoft SQLServer</a>
	<li> <a href="#postgres">PostgreSQL</a>
	<li> <a href="#db2">IBM DB2</a>
      <li> <a href="#hsql">HSQL db (Hypersonic)</a>
<!--	
	<li> <a href="#interbase">Interbase 6</a>
	<li> <a href="#cloudscape">Cloudscape</a>
-->
</ul>

<h2><a name="mysql">MySQL</a></h2>
<ul>
	<b>JDBC Drivers</b><p>

Matt Tucker's avatar
Matt Tucker committed
53 54 55
	The JDBC driver for MySQL is bundled with Jive Messenger, so you do not need
    to download and install a separate driver.
    
Matt Tucker's avatar
Matt Tucker committed
56
	<p>
Matt Tucker's avatar
Matt Tucker committed
57
	In the Jive Messenger setup tool, use the following values:<p><ul>
Matt Tucker's avatar
Matt Tucker committed
58
		<li>driver: <tt>com.mysql.jdbc.Driver</tt>
Matt Tucker's avatar
Matt Tucker committed
59 60 61 62 63 64 65 66 67
		<li>server: <tt>jdbc:mysql://[YOUR_HOST]/[DATABASE_NAME]</tt>
	</ul><p>
		
	where [YOUR_HOST] and [DATABASE_NAME] are the actual values for you server.
	In many cases <tt>localhost</tt> is a suitable value for [YOUR_HOST] when
	your database is running on the same server as your webserver.

	<p><b>Setup Instructions</b><p>
	<ol>
Matt Tucker's avatar
Matt Tucker committed
68
		<li>Make sure that you are using MySQL 3.23.2 or later (4.x recommended).
Matt Tucker's avatar
Matt Tucker committed
69
		<li>Create a database for the Jive Messenger tables:<br>
Matt Tucker's avatar
Matt Tucker committed
70
	    <code>mysqladmin create [databaseName]</code><br>
Matt Tucker's avatar
Matt Tucker committed
71
	    (note: "databaseName" can be something like 'messenger')
Matt Tucker's avatar
Matt Tucker committed
72

73 74
	    <li>Import the schema file from the <tt>resources/database</tt> directory of the installation folder:<br>
	    Unix/Linux: <code>cat messenger_mysql.sql | mysql [databaseName];</code>
Matt Tucker's avatar
Matt Tucker committed
75
		<br>   
76
        Windows: <code>type messenger_mysql.sql | mysql [databaseName];</code>
Matt Tucker's avatar
Matt Tucker committed
77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104
		
		<li>Start the Jive Messenger setup tool, and use the appropriate JDBC connection
			settings.
	</ol>
	
    <p><b>Character Encoding Issues</b><p>
    
    MySQL does not have proper Unicode support, which makes supporting data
    in non-Western languages difficult. However, the MySQL JDBC driver has a workaround
    which can be enabled by adding 
    <tt>&lt;mysql&gt;&lt;useUnicode&gt;true&lt;/useUnicode&gt;&lt;/mysql&gt;</tt>
    to the <tt>&lt;database&gt;</tt> section of your <tt>jive_messenger.xml</tt> file.
    When using this setting, you should also set the Jive character encoding
    to utf-8 in the admin tool.
	
	<p><b>Further Help</b><p>

	If you need help setting up MySQL, refer to the 
	following sites:<p>
	
	<a href="http://www.mysql.com/doc.html">http://www.mysql.com/doc.html</a><br>
	<a href="http://www.devshed.com/Server_Side/MySQL">http://www.devshed.com/Server_Side/MySQL</a>           
</ul>

<p><h2><a name="oracle">Oracle</a></h2><p><ul>

	<b>JDBC Drivers</b><p>

Matt Tucker's avatar
Matt Tucker committed
105 106 107 108 109 110
	The Oracle 9i or 10g "thin" drivers are recommended and can be downloaded from 
    <a href="http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html">Oracle's website</a>. 
     
    Various versions of the thin drivers are avaialble, but the "ojdbc14.jar" driver is recommended for use with Jive Messenger.
    Copy the JDBC driver to the <tt>lib/</tt> directory of your Jive Messenger installation.
    
Matt Tucker's avatar
Matt Tucker committed
111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128
	<p>
	In the Jive Messenger web-based setup tool, use the following values:<p>
    
    <ul>
		<li>driver: <tt>oracle.jdbc.driver.OracleDriver</tt>
		<li>server: <tt>jdbc:oracle:thin:@[YOUR_HOST]:1521:[DATABASE_NAME]</tt>
	</ul><p>
		
	where [YOUR_HOST] and [DATABASE_NAME] are the actual values for you server.
	In many cases <code>localhost</code> is a suitable value for [YOUR_HOST] when
	your database is running on the same server as your webserver.

	<p><b>Setup Instructions</b><p>
	
	First, create a "Jive" user or some other user
	that will "own" the Jive tables. This isn't necessary, but doing so
	will allow your tables to exist in a seperate tablespace.
	<p>
129
	Next import the schema from the <tt>resources/database</tt> directory of the installation
Matt Tucker's avatar
Matt Tucker committed
130
    using sqlplus (or your favorite Oracle tool such
131
	as Toad). For sqlplus: copy the "messenger_oracle.sql" file to the same
Matt Tucker's avatar
Matt Tucker committed
132 133
	directory where sqlplus is installed (should be something like 
	/Oracle/Ora81/bin/). Next, log into sqlplus and then execute the command:<br>
134
		<code>@ messenger_oracle</code><br>
Matt Tucker's avatar
Matt Tucker committed
135 136 137 138 139 140 141 142
	That will import the schema file. Your database is now setup.
</ul>

<p><h2><a name="sqlserver2000">SQL Server 2000</a></h2><p>

<ul>	
	<b>JDBC Drivers</b><p>

Matt Tucker's avatar
Matt Tucker committed
143
    Microsoft provides a <a href="http://www.microsoft.com/downloads/details.aspx?FamilyID=07287b11-0502-461a-b138-2aa54bfdc03a&DisplayLang=en">free JDBC driver</a> that works with Jive Messenger.
Matt Tucker's avatar
Matt Tucker committed
144
    After downloading and extracting the drivers, place <tt>msbase.jar</tt>,
Matt Tucker's avatar
Matt Tucker committed
145 146 147
    <tt>mssqlserver.jar</tt>, and <tt>msutil.jar</tt> in the <tt>lib/</tt>
    directory of your Jive Messenger installation. Use the following values in the Jive Messenger
    web-based setup tool:<p>
Matt Tucker's avatar
Matt Tucker committed
148 149 150 151 152 153 154 155 156 157 158 159 160 161

    	<ul>
		<li>driver -- <tt>com.microsoft.jdbc.sqlserver.SQLServerDriver</tt>
		<li>server -- <tt>jdbc:microsoft:sqlserver://[HOST_NAME]:[PORT_NUMBER];databasename=[DB_NAME]</tt>
	</ul>
	<p>
	where [HOST_NAME] is the host name or IP address of your database server,
    and [PORT_NUMBER] is the port that SQLServer is listening on (normally 1433)
    and [DB_NAME] is the name of the database (this parameter is optional).

	<p><b>Setup Instructions</b><p>

	<ol>
		<li>Create a new database using Enterprise Manager if you do not already have
Matt Tucker's avatar
Matt Tucker committed
162
	   		a database you want to use. You may wish to name the database "messenger".
Matt Tucker's avatar
Matt Tucker committed
163 164 165 166 167 168
		<li>Create a user to access the database if you do not already have one you'd
			like to use. Consider making the default database for the user be the
			one you created in step 1.
		<li>Open the Query Analyser and connect to the server.
		<li>Select the database you want to use for Jive Messenger
		    from the DB drop down (the one you created in step 1 if you're using a new database).
Matt Tucker's avatar
Matt Tucker committed
169
		<li>Open the messenger_sqlserver.sql file.
Matt Tucker's avatar
Matt Tucker committed
170 171 172 173
		<li>Press F5 to run the script. The script will run and create the
			necessary tables.
		<li>Proceed to the Jive Messenger setup tool and use the appropriate JDBC settings
			when prompted.
Matt Tucker's avatar
Matt Tucker committed
174 175 176 177 178 179 180 181 182 183 184 185 186
   </ol>
	 
	 <p>Note: if you are using SQL Server 7, the Open Source jTDS drivers should
	 be used instead of the Microsoft JDBC drivers. Download the jTDS driver from 
   <a href="http://jtds.sourceforge.net/">http://jtds.sourceforge.net/</a>
	 and place the jtds-1.0.2.jar (name may be different depending on version
	 downloaded) in the lib/ directory of your Jive Messenger installation. Use
	 the following values in the Jive Messenger setup tool:</p>
	 
	 	<ul>
				<li>driver -- <tt>net.sourceforge.jtds.jdbc.Driver</tt>
				<li>server -- <tt>jdbc:jtds:sqlserver://[HOST_NAME]:[PORT]/[DATABASE]</tt>
	  </ul>	     
Matt Tucker's avatar
Matt Tucker committed
187 188 189 190 191 192 193
</ul>     

<p><h2><a name="postgres">PostgreSQL</a></h2><p>

<ul>
	<b>JDBC Drivers</b>
	<p>
Matt Tucker's avatar
Matt Tucker committed
194 195
	The JDBC driver for Postgres is bundled with Jive Messenger, so you do not need
    to download and install a separate driver.
Matt Tucker's avatar
Matt Tucker committed
196 197 198 199 200 201 202 203 204 205 206 207 208 209 210
    <p>
	In the Jive Messenger web-based setup tool, use the following values:<p><ul>
		<li>driver -- <tt>org.postgresql.Driver</tt>
		<li>server -- <tt>jdbc:postgresql://[HOST_NAME]:[PORT_NUMBER]/dbname</tt>
	</ul><p>
		
	If left out, host defaults to localhost (not 127.0.0.1) and port to 5432.
	
	<p>
	<b>Setup Instructions</b>
	<p>
	<ol>
	  <li>Create the database. For example, using the 
	  	PostgreSQL <code>&quot;createdb&quot;</code> utility:
	  	<br>
Matt Tucker's avatar
Matt Tucker committed
211
	  	<code>createdb -E UNICODE messenger</code>
212
	  <li>Import the schema from the <tt>resources/database</tt> directory of the installation.
Matt Tucker's avatar
Matt Tucker committed
213 214 215
        Use psql (or your favorite Postgres sql 
	  	utility) to import the Jive Messenger database schema:
	  	<br>
Matt Tucker's avatar
Matt Tucker committed
216
	  	<code>psql -d messenger -f messenger_postgresql.sql</code>
Matt Tucker's avatar
Matt Tucker committed
217 218 219 220 221 222 223 224 225 226
	  <li>Proceed to Jive Messenger setup and use the appropriate JDBC settings when
	  	prompted.	  
	</ol>
</ul>

<p><h2><a name="db2">IBM DB2 7</a></h2><p><ul>

	<b>JDBC Drivers</b>
	<p>
	Use the JDBC 1.2 compliant driver, db2java.zip (1293KB),
Matt Tucker's avatar
Matt Tucker committed
227 228
	located in the <nobr>"%DB2PATH%\java2"</nobr> directory. Copy the JDBC driver to the <tt>lib/</tt>
    directory of your Jive Messenger installation.
Matt Tucker's avatar
Matt Tucker committed
229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253
	<p>
	Values for the config file are:
	<ul>
		<li>driver: <code>COM.ibm.db2.jdbc.app.DB2Driver</code>
		<li>server: <code>jdbc:db2:[DB NAME, e.g. JIVE]</code>
	</ul>
	If you are using a remote database you need to use the following values for the config file are:
	<ul>
		<li>driver: <code>COM.ibm.db2.jdbc.net.DB2Driver</code>
		<li>server: <code>jdbc:db2:[DB NAME, e.g. JIVE]</code>
	</ul>
	<p>
	<b>Setup Instructions</b>
	<p>
	You have to create a new database (e.g. JIVE) or use an existing 
	one. Connect to the database using any valid username allowed to create
	tables and indexes. Keep in mind that DB2 UDB applies the user name 
	as the table schema. Use the same user when you configure Jive with
	the administration interface. Commands are as follows:
	<p>
	<code>DB2 CREATE DB JIVE ALIAS JIVE<br>
	DB2 CONNECT TO JIVE USER [DBUSER] USING [DBPASSWORD]</code><br>
	(or you can use the Command Center GUI)
	<p>
	Start the DB2 command window (in Windows), or the user's shell in 
254
	Unix and import the schema in the <tt>resources/database</tt> directory of the
Matt Tucker's avatar
Matt Tucker committed
255
    installation with:<p>
256
		<code>db2 -tvf messenger_db2.sql</code>
Matt Tucker's avatar
Matt Tucker committed
257 258 259 260 261 262 263 264 265
	<p>
	Or, use the DB2 Command Center and run the script
	through the "Replication Sources" folder in the Database tree.
	
	<p>
	The database is now setup.

</ul>

Matt Tucker's avatar
Matt Tucker committed
266
<p><h2><a name="hsql">HSQLDB 1.7.4</a></h2><p><ul>
Matt Tucker's avatar
Matt Tucker committed
267 268 269

    <b>Special Note</b>
    <p>
Matt Tucker's avatar
Matt Tucker committed
270
    Jive Messenger bundles hsqldb as its embedded database. If you choose to use the embedded database,
Matt Tucker's avatar
Matt Tucker committed
271
    it can be configured via the Jive Messenger installer. If you have a stand-alone installation
Matt Tucker's avatar
Matt Tucker committed
272
    of hsqlDB, follow the instructions below to connect to it.
Matt Tucker's avatar
Matt Tucker committed
273 274 275 276
    </p>

	<b>JDBC Drivers</b>
	<p>
Matt Tucker's avatar
Matt Tucker committed
277
	Because hsqldb is embedded in Jive Messenger, there is no need to download the
Matt Tucker's avatar
Matt Tucker committed
278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310
    JDBC driver separately. Values for the config file are:
	<ul>
		<li>driver: <code>org.hsqldb.jdbcDriver</code>
		<li>server: <code>jdbc:hsqldb:[PATH_TO_DB_FILES/DB_NAME]</code>
	</ul>
	<p>
	For example, the server URL might be <tt>jdbc:hsqldb:../database/defaultdb</tt>
	<p>
	<b>Setup Instructions</b>
	<p>
	Create a database for the Jive Messenger
	tables. This assumes you have HSQL installed in its own
	directory and working correctly.
	<ol>
		<li>
		Run the HSQL Database Manager by navigating to the folder where
	    hsqldb.jar is located and type the following
		<ul>
		    <p>
		    <code>
	        java -cp hsqldb.jar org.hsqldb.util.DatabaseManager
	        </code>
	        <p>
		</ul>
	    then hit enter. You will now see a GUI application. Note, you will not see a hsqldb.jar
	    in the Jive Messenger distribution - this is referring to the hsqldb.jar from the HSQL
	    distribution.
	
		<li>
	    When prompted with the connect window, enter <tt>org.hsqldb.jdbcDriver</tt>
	    as the Driver, and specify the URL of your database in
	    the URL field. The default password is already entered.
	
311 312
	    <li>Now copy and paste the contents of <tt>messenger_hsql.sql</tt> from
        the <tt>resources/database</tt> directory of the installation into the
Matt Tucker's avatar
Matt Tucker committed
313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425
	    command window, and hit execute.  If no errors are
	    reported, then your database is setup properly.
	
	    <li>Finally, you should change the username and password login for your
		database: just open the .script file in your
	    database folder and change the appropriate values. Make sure that you
		don't use a blank password, as Jive Messenger will not accept
		one.
	</ol>
</ul>
<!--

<p><h2><a name="interbase">Interbase 6.0</a></h2><p><ul>	
 
    <font color="red"><b>Warning:</b></font> some users have 
    experienced problems trying to make Interbase work with Jive. You may
    wish to try a different database such as MySQL or Postgres if possible.<p>
 
	<b>JDBC Drivers</b>
	<p>
    
	JDBC drivers and other files for Interbase can be downloaded at
	<a href="http://www.borland.com/devsupport/interbase/opensource/" target="_blank">
	http://www.borland.com/devsupport/interbase/opensource/</a>. The JDBC
	JAR file is called interclient.jar, and should be put in the classpath of
	your application server.
	<p>
	Values for the config file are:
	<ul>
		<li>driver: <code>interbase.interclient.Driver</code>
		<li>server: <code>jdbc:interbase://host/fullpath</code>
	</ul>
	
	<p>
	<b>Setup Instructions</b>
	<p>
	<ol>
	  	<li>Create a new database by connecting to your Interbase server with 
		IBConsole then use the Database/Create Database menu option.
		<li>Execute the Jive SQL script using Interactive SQL. This can be 
			started from the Tools/Interactive SQL menu. Within Interactive SQL 
			load the jive_xmpp_interbase.sql script (Query/Load Script) and execute 
			it (Query/Execute). The database will now have been constructed.  
	</ol>
	<p>
	Notes: It would probably be a good idea to create a new user for Jive or 
	at least to change the password. 
	<p>
	To use JDBC with interbase you have to have install the Interbase
	InterServer which enables the InterClient JDBC driver to access the
	database.	
</ul>

<p><h2><a name="cloudscape">Cloudscape 3.6.4</a></h2><p><ul>

	Note: The following procedure will use the embedded version of Cloudscape, 
	but a similar process can be used to setup the client/server version. The 
	downloaded zip file supports both embedded and client/server modes.
	<p>
	
	<p>
	<b>Setup Instructions</b>
	<p>

	Cloudscape and the JDBC drivers can be downloaded at 
	<a href="http://www.informix.com/cloudscape/" target="_blank">
	http://www.informix.com/cloudscape/</a>, the zip file is cloudscape364.zip.
	After downloading, unzip the file into \cloudscape directory and add the 
	following JARs to the CLASSPATH of your system, the lib directory of Jive Messenger,
      your application server,
	or put them in the WEB-INF/lib directory created for the Jive web application.
	<p>

	For Windows:
<pre>
	md \cloudscape
	cd \cloudscape
	jar xvf cloudscape364.zip
	set CLASSPATH=%CLASSPATH%;\cloudscape\lib\cloudscape.jar;\cloudscape\lib\tools.jar
</pre>
	
	
<ol>
	<li> Change into your Cloudscape directory, i.e. cd \cloudscape
	<li> Copy in the file jive_cloudscape_364.sql.
	<li> Using the Cloudscape Interactive JDBC scripting tool (ij), create the 
		database:
<pre>
	java COM.cloudscape.tools.ij
	ij version 3.6 (c) 1997-2001 Informix Software, Inc.
	ij> connect 'jdbc:cloudscape:jiveDB;create=true';
	ij> run 'jive_cloudscape_364.sql';
	ij> select * from jiveuser;
	ij> exit;
</pre>
</ol>

<p>
<b>JDBC Drivers</b>
<p>

Values for the admin tool are:<ul>
	<li>driver: <code>COM.cloudscape.JDBCDriver</code>
	<li>server: <code>jdbc:cloudscape:/cloudscape/jiveDB</code>
	<li>username: <code>APP</code>
	<li>password: <code>APP</code>
</ul>

</ul>
-->

</body>
</html>