database.html 12.1 KB
Newer Older
Matt Tucker's avatar
Matt Tucker committed
1
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
2

Matt Tucker's avatar
Matt Tucker committed
3 4
<html>
<head>
5
<title>Openfire: Database Installation Guide</title>
6
<link href="style.css" rel="stylesheet" type="text/css">
Matt Tucker's avatar
Matt Tucker committed
7 8
</head>
<body>
9 10 11 12 13 14 15 16 17 18 19 20 21 22

<div id="pageContainer">

<a name="top"></a>

	<div id="pageHeader">
		<div id="logo"></div>
		<h1>Database Installation Guide</h1>
	</div>
	<div class="navigation">
		<a href="index.html">&laquo; Back to documentation index</a>
	</div>
	
	<div id="pageBody">
Matt Tucker's avatar
Matt Tucker committed
23

24
<p>Openfire stores all data in a back-end database. If you choose to not use the embedded
Matt Tucker's avatar
Matt Tucker committed
25
database, you must setup and manage an external database for the application.
Matt Tucker's avatar
Matt Tucker committed
26 27 28
This document provides setup instructions for all of the currently supported databases.
</p>
<p>
29 30
JDBC 2.0 drivers are required for Openfire to communicate with your database. Suggested
drivers for particular databases are noted below where applicable. Openfire bundles JDBC
31
drivers for MySQL, Postgres, Microsoft SQL Server, and HSQLDB.
Matt Tucker's avatar
Matt Tucker committed
32 33 34 35 36 37 38
</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>
39
All database schemas can be found in the <tt>resources/database</tt> directory of the
40
Openfire installation.
Matt Tucker's avatar
Matt Tucker committed
41 42 43 44 45 46 47 48 49 50 51 52
</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>
53
	<li> <a href="#sqlserver">Microsoft SQLServer</a>
Matt Tucker's avatar
Matt Tucker committed
54 55
	<li> <a href="#postgres">PostgreSQL</a>
	<li> <a href="#db2">IBM DB2</a>
56
    <li> <a href="#hsql">HSQLDB</a>
Matt Tucker's avatar
Matt Tucker committed
57 58 59 60 61 62
</ul>

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

63
	The JDBC driver for MySQL is bundled with Openfire, so you do not need
Matt Tucker's avatar
Matt Tucker committed
64 65
    to download and install a separate driver.
    
Matt Tucker's avatar
Matt Tucker committed
66
	<p>
67
	In the Openfire setup tool, use the following values:<p><ul>
Matt Tucker's avatar
Matt Tucker committed
68
		<li>driver: <tt>com.mysql.jdbc.Driver</tt>
Matt Tucker's avatar
Matt Tucker committed
69 70 71 72 73 74 75 76 77
		<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>
78
		<li>Make sure that you are using MySQL 4.1.18 or later (5.x recommended) <a href="#note1">&sup1;</a>.
79
		<li>Create a database for the Openfire tables:<br>
Matt Tucker's avatar
Matt Tucker committed
80
	    <code>mysqladmin create [databaseName]</code><br>
81
	    (note: "databaseName" can be something like 'openfire')
Matt Tucker's avatar
Matt Tucker committed
82

83
	    <li>Import the schema file from the <tt>resources/database</tt> directory of the installation folder:<br>
84
	    Unix/Linux: <code>cat openfire_mysql.sql | mysql [databaseName];</code>
Matt Tucker's avatar
Matt Tucker committed
85
		<br>   
86
        Windows: <code>type openfire_mysql.sql | mysql [databaseName];</code>
Matt Tucker's avatar
Matt Tucker committed
87
		
88
		<li>Start the Openfire setup tool, and use the appropriate JDBC connection
Matt Tucker's avatar
Matt Tucker committed
89 90
			settings.
	</ol>
91 92 93

    <p><a name="note1">&sup1;</a> Character fields larger than 255 are not supported by versions prior
    to MySQL 4.1.18. If you cannot upgrade MySQL to the latest version, you will then need to change the
94
    database scripts. In particular, replace VARCHAR(1024) with VARCHAR(255) in the 
95
    resources/database/openfire_mysql.sql script.</p>
Matt Tucker's avatar
Matt Tucker committed
96 97 98 99 100
	
    <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
101 102
    which can be enabled by adding <tt>?useUnicode=true&characterEncoding=UTF-8&characterSetResults=UTF-8</tt>
    to the URL of the JDBC driver. You can edit the conf/openfire.xml file to add this value.
103 104 105

    <p>Note: If the mechanism you use to configure a JDBC URL is XML-based, you will need to use the XML character
    literal &amp;amp; to separate configuration parameters, as the ampersand is a reserved character for XML.</p>
Matt Tucker's avatar
Matt Tucker committed
106
	
107
    <p><b>Further Help</b><p>
Matt Tucker's avatar
Matt Tucker committed
108 109 110 111

	If you need help setting up MySQL, refer to the 
	following sites:<p>
	
112
	<a href="http://dev.mysql.com/doc/refman/5.1/en/connector-j-reference-configuration-properties.html"> MySQL Reference Manual </a><br>
113
    </p>
Matt Tucker's avatar
Matt Tucker committed
114 115 116 117 118 119
</ul>

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

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

Matt Tucker's avatar
Matt Tucker committed
120 121 122
	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>. 
     
123 124
    Various versions of the thin drivers are avaialble, but the "ojdbc14.jar" driver is recommended for use with Openfire.
    Copy the JDBC driver to the <tt>lib/</tt> directory of your Openfire installation.
Matt Tucker's avatar
Matt Tucker committed
125
    
Matt Tucker's avatar
Matt Tucker committed
126
	<p>
127
	In the Openfire web-based setup tool, use the following values:<p>
Matt Tucker's avatar
Matt Tucker committed
128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143
    
    <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>
144
	Next import the schema from the <tt>resources/database</tt> directory of the installation
Matt Tucker's avatar
Matt Tucker committed
145
    using sqlplus (or your favorite Oracle tool such
146
	as Toad). For sqlplus: copy the "openfire_oracle.sql" file to the same
Matt Tucker's avatar
Matt Tucker committed
147 148
	directory where sqlplus is installed (should be something like 
	/Oracle/Ora81/bin/). Next, log into sqlplus and then execute the command:<br>
149
		<code>@ openfire_oracle</code><br>
150
	That will import the schema file. Your database is now setup.</p>
Matt Tucker's avatar
Matt Tucker committed
151 152
</ul>

153 154 155
<p><h2><a name="sqlserver">SQL Server</a></h2><p>

<ul>
156
    <p><b>SQL Server</b></p>
157
    <b>JDBC Drivers</b><p>
Matt Tucker's avatar
Matt Tucker committed
158

159
    Openfire includes the <a href="http://www.freetds.org/">Free TDS</a> JDBC driver, which
160
    is the recommended way to connect to SQL Server databases. To
161
    connect to SQL Server, simply use the following values in the Openfire
Matt Tucker's avatar
Matt Tucker committed
162
    web-based setup tool:<p>
Matt Tucker's avatar
Matt Tucker committed
163 164

    	<ul>
165 166
		<li>driver -- <tt>net.sourceforge.jtds.jdbc.Driver</tt>
		<li>server -- <tt>jdbc:jtds:sqlserver://[host-name]:[port-number]/[database-name];appName=jive</tt>
Matt Tucker's avatar
Matt Tucker committed
167 168
	</ul>
	<p>
169 170 171
	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 [database-name] is the name of the database (this parameter is optional).
Matt Tucker's avatar
Matt Tucker committed
172 173 174 175 176

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

	<ol>
		<li>Create a new database using Enterprise Manager if you do not already have
Gaston Dombiak's avatar
Gaston Dombiak committed
177
	   		a database you want to use. You may wish to name the database "openfire".
Matt Tucker's avatar
Matt Tucker committed
178 179 180 181
		<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.
182
		<li>Select the database you want to use for Openfire
Matt Tucker's avatar
Matt Tucker committed
183
		    from the DB drop down (the one you created in step 1 if you're using a new database).
184
		<li>Open the openfire_sqlserver.sql file.
Matt Tucker's avatar
Matt Tucker committed
185 186
		<li>Press F5 to run the script. The script will run and create the
			necessary tables.
187
		<li>Proceed to the Openfire setup tool and use the appropriate JDBC settings
Matt Tucker's avatar
Matt Tucker committed
188
			when prompted.
Matt Tucker's avatar
Matt Tucker committed
189
   </ol>
Matt Tucker's avatar
Matt Tucker committed
190 191 192 193 194 195 196
</ul>     

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

<ul>
	<b>JDBC Drivers</b>
	<p>
197
	The JDBC driver for Postgres is bundled with Openfire, so you do not need
Matt Tucker's avatar
Matt Tucker committed
198
    to download and install a separate driver.
Matt Tucker's avatar
Matt Tucker committed
199
    <p>
200
	In the Openfire web-based setup tool, use the following values:<p><ul>
Matt Tucker's avatar
Matt Tucker committed
201 202 203 204 205 206 207 208 209 210 211 212 213
		<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>
214
	  	<code>createdb -E UNICODE openfire</code>
215
	  <li>Import the schema from the <tt>resources/database</tt> directory of the installation.
Matt Tucker's avatar
Matt Tucker committed
216
        Use psql (or your favorite Postgres sql 
217
	  	utility) to import the Openfire database schema:
Matt Tucker's avatar
Matt Tucker committed
218
	  	<br>
219 220
	  	<code>psql -d openfire -f openfire_postgresql.sql</code>
	  <li>Proceed to Openfire setup and use the appropriate JDBC settings when
Matt Tucker's avatar
Matt Tucker committed
221 222 223 224 225 226 227 228 229
	  	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
230
	located in the <nobr>"%DB2PATH%\java2"</nobr> directory. Copy the JDBC driver to the <tt>lib/</tt>
231
    directory of your Openfire installation.
Matt Tucker's avatar
Matt Tucker committed
232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256
	<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 
257
	Unix and import the schema in the <tt>resources/database</tt> directory of the
Matt Tucker's avatar
Matt Tucker committed
258
    installation with:<p>
259
		<code>db2 -tvf openfire_db2.sql</code>
Matt Tucker's avatar
Matt Tucker committed
260 261 262 263 264 265 266 267 268
	<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>

269
<p><h2><a name="hsql">HSQLDB</a></h2><p><ul>
Matt Tucker's avatar
Matt Tucker committed
270 271 272

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

	<b>JDBC Drivers</b>
	<p>
280
	Because hsqldb is embedded in Openfire, there is no need to download the
Matt Tucker's avatar
Matt Tucker committed
281 282 283 284 285 286 287 288 289 290
    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>
291
	Create a database for the Openfire tables. This assumes you have HSQLDB installed
292
    in its own directory and working correctly.
Matt Tucker's avatar
Matt Tucker committed
293 294
	<ol>
		<li>
295
		Run the HSQLDB Database Manager by navigating to the folder where
Matt Tucker's avatar
Matt Tucker committed
296 297 298 299 300 301 302 303 304
	    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
305
	    in the Openfire distribution - this is referring to the hsqldb.jar from the HSQLDB
Matt Tucker's avatar
Matt Tucker committed
306 307 308 309 310 311 312
	    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.
	
313
	    <li>Now copy and paste the contents of <tt>openfire_hsql.sql</tt> from
314
        the <tt>resources/database</tt> directory of the installation into the
Matt Tucker's avatar
Matt Tucker committed
315 316 317 318 319 320
	    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
321
		don't use a blank password, as Openfire will not accept
Matt Tucker's avatar
Matt Tucker committed
322 323 324 325
		one.
	</ol>
</ul>

326 327 328 329
	</div>

</div>

Matt Tucker's avatar
Matt Tucker committed
330 331
</body>
</html>