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

10
<p>Wildfire stores all data in a back-end database. If you choose to not use the embedded
Matt Tucker's avatar
Matt Tucker committed
11
database, you must setup and manage an external database for the application.
Matt Tucker's avatar
Matt Tucker committed
12 13 14
This document provides setup instructions for all of the currently supported databases.
</p>
<p>
15 16
JDBC 2.0 drivers are required for Wildfire to communicate with your database. Suggested
drivers for particular databases are noted below where applicable. Wildfire bundles JDBC
17
drivers for MySQL, Postgres, Microsoft SQL Server, 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
26
Wildfire installation.
Matt Tucker's avatar
Matt Tucker committed
27 28 29 30 31 32 33 34 35 36 37 38
</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>
39
	<li> <a href="#sqlserver">Microsoft SQLServer</a>
Matt Tucker's avatar
Matt Tucker committed
40 41
	<li> <a href="#postgres">PostgreSQL</a>
	<li> <a href="#db2">IBM DB2</a>
42
    <li> <a href="#hsql">HSQLDB</a>
Matt Tucker's avatar
Matt Tucker committed
43 44 45 46 47 48
</ul>

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

49
	The JDBC driver for MySQL is bundled with Wildfire, so you do not need
Matt Tucker's avatar
Matt Tucker committed
50 51
    to download and install a separate driver.
    
Matt Tucker's avatar
Matt Tucker committed
52
	<p>
53
	In the Wildfire setup tool, use the following values:<p><ul>
Matt Tucker's avatar
Matt Tucker committed
54
		<li>driver: <tt>com.mysql.jdbc.Driver</tt>
Matt Tucker's avatar
Matt Tucker committed
55 56 57 58 59 60 61 62 63
		<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
64
		<li>Make sure that you are using MySQL 3.23.2 or later (4.x recommended).
65
		<li>Create a database for the Wildfire tables:<br>
Matt Tucker's avatar
Matt Tucker committed
66
	    <code>mysqladmin create [databaseName]</code><br>
67
	    (note: "databaseName" can be something like 'wildfire')
Matt Tucker's avatar
Matt Tucker committed
68

69
	    <li>Import the schema file from the <tt>resources/database</tt> directory of the installation folder:<br>
70
	    Unix/Linux: <code>cat wildfire_mysql.sql | mysql [databaseName];</code>
Matt Tucker's avatar
Matt Tucker committed
71
		<br>   
72
        Windows: <code>type wildfire_mysql.sql | mysql [databaseName];</code>
Matt Tucker's avatar
Matt Tucker committed
73
		
74
		<li>Start the Wildfire setup tool, and use the appropriate JDBC connection
Matt Tucker's avatar
Matt Tucker committed
75 76 77 78 79 80 81 82 83
			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>
84
    to the <tt>&lt;database&gt;</tt> section of your <tt>wildfire.xml</tt> file.
Matt Tucker's avatar
Matt Tucker committed
85 86 87 88 89 90 91 92 93
    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>
94 95
	<a href="http://www.devshed.com/Server_Side/MySQL">http://www.devshed.com/Server_Side/MySQL</a>
    </p>
Matt Tucker's avatar
Matt Tucker committed
96 97 98 99 100 101
</ul>

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

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

Matt Tucker's avatar
Matt Tucker committed
102 103 104
	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>. 
     
105 106
    Various versions of the thin drivers are avaialble, but the "ojdbc14.jar" driver is recommended for use with Wildfire.
    Copy the JDBC driver to the <tt>lib/</tt> directory of your Wildfire installation.
Matt Tucker's avatar
Matt Tucker committed
107
    
Matt Tucker's avatar
Matt Tucker committed
108
	<p>
109
	In the Wildfire web-based setup tool, use the following values:<p>
Matt Tucker's avatar
Matt Tucker committed
110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125
    
    <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>
126
	Next import the schema from the <tt>resources/database</tt> directory of the installation
Matt Tucker's avatar
Matt Tucker committed
127
    using sqlplus (or your favorite Oracle tool such
128
	as Toad). For sqlplus: copy the "wildfire_oracle.sql" file to the same
Matt Tucker's avatar
Matt Tucker committed
129 130
	directory where sqlplus is installed (should be something like 
	/Oracle/Ora81/bin/). Next, log into sqlplus and then execute the command:<br>
131
		<code>@ wildfire_oracle</code><br>
132
	That will import the schema file. Your database is now setup.</p>
Matt Tucker's avatar
Matt Tucker committed
133 134
</ul>

135 136 137
<p><h2><a name="sqlserver">SQL Server</a></h2><p>

<ul>
138
    <p><b>SQL Server</b></p>
139
    <b>JDBC Drivers</b><p>
Matt Tucker's avatar
Matt Tucker committed
140

141
    Wildfire includes the <a href="http://www.freetds.org/">Free TDS</a> JDBC driver, which
142
    is the recommended way to connect to SQL Server databases. To
143
    connect to SQL Server, simply use the following values in the Wildfire
Matt Tucker's avatar
Matt Tucker committed
144
    web-based setup tool:<p>
Matt Tucker's avatar
Matt Tucker committed
145 146

    	<ul>
147 148
		<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
149 150
	</ul>
	<p>
151 152 153
	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
154 155 156 157 158

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

	<ol>
		<li>Create a new database using Enterprise Manager if you do not already have
159
	   		a database you want to use. You may wish to name the database "wildfire".
Matt Tucker's avatar
Matt Tucker committed
160 161 162 163
		<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.
164
		<li>Select the database you want to use for Wildfire
Matt Tucker's avatar
Matt Tucker committed
165
		    from the DB drop down (the one you created in step 1 if you're using a new database).
166
		<li>Open the wildfire_sqlserver.sql file.
Matt Tucker's avatar
Matt Tucker committed
167 168
		<li>Press F5 to run the script. The script will run and create the
			necessary tables.
169
		<li>Proceed to the Wildfire setup tool and use the appropriate JDBC settings
Matt Tucker's avatar
Matt Tucker committed
170
			when prompted.
Matt Tucker's avatar
Matt Tucker committed
171
   </ol>
Matt Tucker's avatar
Matt Tucker committed
172 173 174 175 176 177 178
</ul>     

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

<ul>
	<b>JDBC Drivers</b>
	<p>
179
	The JDBC driver for Postgres is bundled with Wildfire, so you do not need
Matt Tucker's avatar
Matt Tucker committed
180
    to download and install a separate driver.
Matt Tucker's avatar
Matt Tucker committed
181
    <p>
182
	In the Wildfire web-based setup tool, use the following values:<p><ul>
Matt Tucker's avatar
Matt Tucker committed
183 184 185 186 187 188 189 190 191 192 193 194 195
		<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>
196
	  	<code>createdb -E UNICODE wildfire</code>
197
	  <li>Import the schema from the <tt>resources/database</tt> directory of the installation.
Matt Tucker's avatar
Matt Tucker committed
198
        Use psql (or your favorite Postgres sql 
199
	  	utility) to import the Wildfire database schema:
Matt Tucker's avatar
Matt Tucker committed
200
	  	<br>
201 202
	  	<code>psql -d wildfire -f wildfire_postgresql.sql</code>
	  <li>Proceed to Wildfire setup and use the appropriate JDBC settings when
Matt Tucker's avatar
Matt Tucker committed
203 204 205 206 207 208 209 210 211
	  	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
212
	located in the <nobr>"%DB2PATH%\java2"</nobr> directory. Copy the JDBC driver to the <tt>lib/</tt>
213
    directory of your Wildfire installation.
Matt Tucker's avatar
Matt Tucker committed
214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238
	<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 
239
	Unix and import the schema in the <tt>resources/database</tt> directory of the
Matt Tucker's avatar
Matt Tucker committed
240
    installation with:<p>
241
		<code>db2 -tvf wildfire_db2.sql</code>
Matt Tucker's avatar
Matt Tucker committed
242 243 244 245 246 247 248 249 250
	<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>

251
<p><h2><a name="hsql">HSQLDB</a></h2><p><ul>
Matt Tucker's avatar
Matt Tucker committed
252 253 254

    <b>Special Note</b>
    <p>
255 256
    Wildfire bundles hsqldb as its embedded database. If you choose to use the embedded database,
    it can be configured via the Wildfire installer. If you have a stand-alone installation
257
    of HSQLDB, follow the instructions below to connect to it.
Matt Tucker's avatar
Matt Tucker committed
258 259 260 261
    </p>

	<b>JDBC Drivers</b>
	<p>
262
	Because hsqldb is embedded in Wildfire, there is no need to download the
Matt Tucker's avatar
Matt Tucker committed
263 264 265 266 267 268 269 270 271 272
    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>
273 274
	Create a database for the Wildfire tables. This assumes you have HSQLDB installed
    in its own directory and working correctly.
Matt Tucker's avatar
Matt Tucker committed
275 276
	<ol>
		<li>
277
		Run the HSQLDB Database Manager by navigating to the folder where
Matt Tucker's avatar
Matt Tucker committed
278 279 280 281 282 283 284 285 286
	    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
287
	    in the Wildfire distribution - this is referring to the hsqldb.jar from the HSQLDB
Matt Tucker's avatar
Matt Tucker committed
288 289 290 291 292 293 294
	    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.
	
295
	    <li>Now copy and paste the contents of <tt>wildfire_hsql.sql</tt> from
296
        the <tt>resources/database</tt> directory of the installation into the
Matt Tucker's avatar
Matt Tucker committed
297 298 299 300 301 302
	    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
303
		don't use a blank password, as Wildfire will not accept
Matt Tucker's avatar
Matt Tucker committed
304 305 306 307 308 309
		one.
	</ol>
</ul>

</body>
</html>