1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
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
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
|
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Creating the database</title>
<link rel="stylesheet" href="common/style.css" type="text/css" />
<meta name="generator" content="DocBook XSL Stylesheets V1.73.0" />
<meta name="keywords" content="webissues, issue tracking, bug tracking, team collaboration, server" />
<link rel="start" href="index.html" title="The WebIssues Server Manual" />
<link rel="up" href="installation.html" title="Chapter 2. Installation Guide" />
<link rel="prev" href="upgrade_server.html" title="Upgrading the WebIssues Server" />
<link rel="next" href="security.html" title="Security settings" />
</head>
<body>
<div id="header">
<div id="header-bar">
<img id="header-logo" src="common/webissues.png" alt="WebIssues" />
<div id="header-title">The <span class="application">WebIssues Server</span> Manual</div>
<div id="header-abstract">Issue tracking and team collaboration system</div>
</div>
<div id="header-links"><a accesskey="h" href="index.html">Home</a> » <a accesskey="u" href="installation.html">Installation Guide</a></div>
</div>
<div class="navlinks">
<div class="navleft">« <a accesskey="p" href="upgrade_server.html">Upgrading the <span class="application">WebIssues Server</span></a></div>
<div class="navright"><a accesskey="n" href="security.html">Security settings</a> »</div>
</div>
<div class="sect1" lang="en" xml:lang="en">
<div class="titlepage">
<div>
<div>
<h2 class="title" style="clear: both"><a id="database"></a>Creating the database</h2>
</div>
</div>
</div>
<p>This chapter contains general notes about database configuration and basic instructions for creating the database using the standard command line tools. If you use a different tool for managing the databases, refer to the relevant documentation for more information.</p>
<div class="sect2" lang="en" xml:lang="en">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a id="db_general"></a>General notes</h3>
</div>
</div>
</div>
<p>You can use an existing database instead of creating a new one. Just ensure that the tables used by the <span class="application">WebIssues Server</span> have a unique prefix to avoid clashes with other tables. This way you can also install multiple instances of the server sharing a single database.</p>
<p>The database user used by the <span class="application">WebIssues Server</span> to login to the database must have sufficient privileges to create tables. The simplest solution for MySQL is to grant <code class="literal">ALL PRIVILEGES</code> to the database; for PostgreSQL and SQL Server assign the user to the database owner role. Refer to the database documentation for more information about privileges.</p>
<div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
<h3 class="title">Note</h3>
<p><span class="application">WebIssues</span> uses the Unicode standard to store all text. If you use PostgreSQL, you must create the database with <code class="literal">UNICODE</code> encoding selected. If you use MySQL version 4.1 or later or SQL Server, <span class="application">WebIssues</span> will automatically use the appropriate encoding.</p>
<p>If you use MySQL 4.0 or earlier, text is stored using the UTF-8 encoding even though the database doesn't support it, so Unicode characters will be seen as sequences of bytes. This may lead to problems when accessing the database from other applications and when transferring or upgrading the database. You may find instructions for upgrading the MySQL database from version 4.0 or earlier in the <a class="ulink" href="http://dev.mysql.com/doc/refman/4.1/en/charset-conversion.html" target="_top">MySQL manual</a>.</p>
</div>
</div>
<div class="sect2" lang="en" xml:lang="en">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a id="db_mysql"></a>MySQL</h3>
</div>
</div>
</div>
<div class="orderedlist">
<ol type="1">
<li>
<p>Create the database (<em class="replaceable"><code>dbauser</code></em> is the login of the database administrator, <em class="replaceable"><code>database</code></em> is the name of the database to create):</p>
<p>
<span class="command">
<strong>mysqladmin -u <em class="replaceable"><code>dbauser</code></em> -p create <em class="replaceable"><code>database</code></em></strong>
</span>
</p>
<p>You will be prompted for the database administrator's password.</p>
</li>
<li>
<p>Connect to the database server:</p>
<p>
<span class="command">
<strong>mysql -u <em class="replaceable"><code>dbauser</code></em> -p</strong>
</span>
</p>
</li>
<li>
<p>From the MySQL command prompt execute the following query to create a user with access to the database (<em class="replaceable"><code>user</code></em> is the user's login, <em class="replaceable"><code>password</code></em> is the user's password and <em class="replaceable"><code>database</code></em> is the name of the database):</p>
<p>
<span class="command">
<strong>GRANT ALL PRIVILEGES ON <em class="replaceable"><code>database</code></em>.* TO '<em class="replaceable"><code>user</code></em>'@'localhost' IDENTIFIED BY '<em class="replaceable"><code>password</code></em>';</strong>
</span>
</p>
</li>
<li>
<p>Execute the following query to activate the new permissions:</p>
<p>
<span class="command">
<strong>FLUSH PRIVILEGES;</strong>
</span>
</p>
</li>
</ol>
</div>
</div>
<div class="sect2" lang="en" xml:lang="en">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a id="db_mysql"></a>PostgreSQL</h3>
</div>
</div>
</div>
<div class="orderedlist">
<ol type="1">
<li>
<p>Create the user account (<em class="replaceable"><code>user</code></em> is the user's login):</p>
<p>
<span class="command">
<strong>createuser --pwprompt --encrypted --no-adduser --no-createdb <em class="replaceable"><code>user</code></em></strong>
</span>
</p>
<p>You will be prompted for the password for the new user. Note that you have to log in as a user with appropriate permissions to manage the PostgreSQL server.</p>
</li>
<li>
<p>Create the database (<em class="replaceable"><code>user</code></em> is the user's login, <em class="replaceable"><code>database</code></em> is the name of the database to create):</p>
<p>
<span class="command">
<strong>createdb --encoding=UNICODE --owner=<em class="replaceable"><code>user</code></em> <em class="replaceable"><code>database</code></em></strong>
</span>
</p>
</li>
</ol>
</div>
</div>
<div class="sect2" lang="en" xml:lang="en">
<div class="titlepage">
<div>
<div>
<h3 class="title"><a id="db_mysql"></a>SQL Server</h3>
</div>
</div>
</div>
<div class="orderedlist">
<ol type="1">
<li>
<p>Connect to the database server:</p>
<p>
<span class="command">
<strong>sqlcmd.exe -U <em class="replaceable"><code>dbauser</code></em> -P <em class="replaceable"><code>dbapassword</code></em></strong>
</span>
</p>
<p>Use <span class="command"><strong>osql.exe</strong></span> instead if you have SQL Server 2000. Replace <em class="replaceable"><code>dbauser</code></em> and <em class="replaceable"><code>dbapassword</code></em> with the login and password of the database administrator.</p>
</li>
<li>
<p>Execute the following queries to create a user's login and the database (<em class="replaceable"><code>user</code></em> is the user's login, <em class="replaceable"><code>password</code></em> is the user's password and <em class="replaceable"><code>database</code></em> is the name of the database to create):</p>
<div class="literallayout">
<p><span class="command"><strong>CREATE LOGIN <em class="replaceable"><code>user</code></em> WITH PASSWORD = '<em class="replaceable"><code>password</code></em>';</strong></span><br />
<span class="command"><strong>GO</strong></span><br />
<span class="command"><strong>CREATE DATABASE <em class="replaceable"><code>database</code></em>;</strong></span><br />
<span class="command"><strong>GO</strong></span></p>
</div>
</li>
<li>
<p>Execute the following queries to assign appropriate permissions for the database to the user (<em class="replaceable"><code>user</code></em> is the user's login, <em class="replaceable"><code>database</code></em> is the name of the database):</p>
<div class="literallayout">
<p><span class="command"><strong>USE <em class="replaceable"><code>database</code></em>;</strong></span><br />
<span class="command"><strong>GO</strong></span><br />
<span class="command"><strong>EXEC sp_grantdbaccess '<em class="replaceable"><code>user</code></em>';</strong></span><br />
<span class="command"><strong>GO</strong></span><br />
<span class="command"><strong>EXEC sp_addrolemember 'db_owner', '<em class="replaceable"><code>user</code></em>';</strong></span><br />
<span class="command"><strong>GO</strong></span></p>
</div>
</li>
</ol>
</div>
</div>
</div>
<div class="navlinks">
<div class="navleft">« <a accesskey="p" href="upgrade_server.html">Upgrading the <span class="application">WebIssues Server</span></a></div>
<div class="navright"><a accesskey="n" href="security.html">Security settings</a> »</div>
</div>
<div id="copyright">
<p class="copyright">Copyright © 2007-2008 WebIssues Team</p>
</div>
</body>
</html>
|
