root/documentation/trunk/packages/dokuwiki-2011-05-25a/conf/mysql.conf.php @ 25050

Revision 25050, 10.7 KB (checked in by jmt12, 9 years ago)

MySQL configuration from Puka, including custom SQL statements for the centralized database

Line 
1<?php
2/*
3 * This is an example configuration for the mysql auth module.
4 *
5 * This SQL statements are optimized for following table structure.
6 * If you use a different one you have to change them accordingly.
7 * See comments of every statement for details.
8 *
9 * TABLE users
10 *     uid   login   pass   firstname   lastname   email
11 *
12 * TABLE groups
13 *     gid   name
14 *
15 * TABLE usergroup
16 *     uid   gid
17 *
18 * To use this configuration you have to copy them to local.protected.php
19 * or at least include this file in local.protected.php.
20 */
21
22/* Options to configure database access. You need to set up this
23 * options carefully, otherwise you won't be able to access you
24 * database.
25 */
26$conf['auth']['mysql']['server']   = 'localhost';
27$conf['auth']['mysql']['user']     = 'greenstonewiki';
28$conf['auth']['mysql']['password'] = 'Ch4ngeM3';
29$conf['auth']['mysql']['database'] = 'gs_services';
30
31/* This option enables debug messages in the mysql module. It is
32 * mostly usefull for system admins.
33 */
34$conf['auth']['mysql']['debug'] = 0;
35
36/* Normally password encryption is done by DokuWiki (recommended) but for
37 * some reasons it might be usefull to let the database do the encryption.
38 * Set 'forwardClearPass' to '1' and the cleartext password is forwarded to
39 * the database, otherwise the encrypted one.
40 */
41$conf['auth']['mysql']['forwardClearPass'] = 0;
42
43/* Multiple table operations will be protected by locks. This array tolds
44 * the module which tables to lock. If you use any aliases for table names
45 * these array must also contain these aliases. Any unamed alias will cause
46 * a warning during operation. See the example below.
47 */
48$conf['auth']['mysql']['TablesToLock']= array("master_users", "master_users AS u",
49                          "groups",       "groups AS g");
50
51/***********************************************************************/
52/*       Basic SQL statements for user authentication (required)       */
53/***********************************************************************/
54
55/* This statement is used to grant or deny access to the wiki. The result
56 * should be a table with exact one line containing at least the password
57 * of the user. If the result table is empty or contains more than one
58 * row, access will be denied.
59 *
60 * The module access the password as 'pass' so a alias might be necessary.
61 *
62 * Following patters will be replaced:
63 *   %{user}    user name
64 *   %{pass}    encrypted or clear text password (depends on 'encryptPass')
65 *   %{dgroup}  default group name
66 */
67$conf['auth']['mysql']['checkPass']   = "SELECT password AS pass
68                                         FROM master_users AS u
69                                         JOIN groups AS g ON g.username=u.username
70                                         WHERE u.username='%{user}'
71                                         AND grp='manual_wiki_%{dgroup}'";
72
73/* This statement should return a table with exact one row containing
74 * information about one user. The field needed are:
75 * 'pass'  containing the encrypted or clear text password
76 * 'name'  the user's full name
77 * 'mail'  the user's email address
78 *
79 * Keep in mind that Dokuwiki will access thise information through the
80 * names listed above so aliasses might be neseccary.
81 *
82 * Following patters will be replaced:
83 *   %{user}    user name
84 */
85$conf['auth']['mysql']['getUserInfo'] = "SELECT password AS pass, full_name AS name, email_address AS mail
86                                         FROM master_users
87                                         WHERE username='%{user}'";
88
89/* This statement is used to get all groups a user is member of. The
90 * result should be a table containing all groups the given user is
91 * member of. The module access the group name as 'group' so a alias
92 * might be nessecary.
93 *
94 * Following patters will be replaced:
95 *   %{user}    user name
96 */
97$conf['auth']['mysql']['getGroups']   = "SELECT SUBSTR(grp, 13) AS `group`
98                                         FROM master_users AS u
99                                         JOIN groups AS g ON g.username=u.username
100                                         WHERE u.username='%{user}'";
101
102/***********************************************************************/
103/*      Additional minimum SQL statements to use the user manager      */
104/***********************************************************************/
105
106/* This statement should return a table containing all user login names
107 * that meet certain filter criteria. The filter expressions will be added
108 * case dependend by the module. At the end a sort expression will be added.
109 * Important is that this list contains no double entries fo a user. Each
110 * user name is only allowed once in the table.
111 *
112 * The login name will be accessed as 'user' to a alias might be neseccary.
113 * No patterns will be replaced in this statement but following patters
114 * will be replaced in the filter expressions:
115 *   %{user}    in FilterLogin  user's login name
116 *   %{name}    in FilterName   user's full name
117 *   %{email}   in FilterEmail  user's email address
118 *   %{group}   in FilterGroup  group name
119 */
120$conf['auth']['mysql']['getUsers']    = "SELECT DISTINCT login AS user
121                                         FROM users AS u
122                                         LEFT JOIN usergroup AS ug ON u.uid=ug.uid
123                                         LEFT JOIN groups AS g ON ug.gid=g.gid";
124$conf['auth']['mysql']['FilterLogin'] = "login LIKE '%{user}'";
125$conf['auth']['mysql']['FilterName']  = "CONCAT(firstname,' ',lastname) LIKE '%{name}'";
126$conf['auth']['mysql']['FilterEmail'] = "email LIKE '%{email}'";
127$conf['auth']['mysql']['FilterGroup'] = "name LIKE '%{group}'";
128$conf['auth']['mysql']['SortOrder']   = "ORDER BY login";
129
130/***********************************************************************/
131/*   Additional SQL statements to add new users with the user manager  */
132/***********************************************************************/
133
134/* This statement should add a user to the database. Minimum information
135 * to store are: login name, password, email address and full name.
136 *
137 * Following patterns will be replaced:
138 *   %{user}    user's login name
139 *   %{pass}    password (encrypted or clear text, depends on 'encryptPass')
140 *   %{email}   email address
141 *   %{name}    user's full name
142 */
143$conf['auth']['mysql']['addUser']     = "INSERT INTO users
144                                         (login, pass, email, firstname, lastname)
145                                         VALUES ('%{user}', '%{pass}', '%{email}',
146                                         SUBSTRING_INDEX('%{name}',' ', 1),
147                                         SUBSTRING_INDEX('%{name}',' ', -1))";
148
149/* This statement should add a group to the database.
150 * Following patterns will be replaced:
151 *   %{group}   group name
152 */
153$conf['auth']['mysql']['addGroup']    = "INSERT INTO groups (name)
154                                         VALUES ('%{group}')";
155
156/* This statement should connect a user to a group (a user become member
157 * of that group).
158 * Following patterns will be replaced:
159 *   %{user}    user's login name
160 *   %{uid}     id of a user dataset
161 *   %{group}   group name
162 *   %{gid}     id of a group dataset
163 */
164$conf['auth']['mysql']['addUserGroup']= "INSERT INTO usergroup (uid, gid)
165                                         VALUES ('%{uid}', '%{gid}')";
166
167/* This statement should remove a group fom the database.
168 * Following patterns will be replaced:
169 *   %{group}   group name
170 *   %{gid}     id of a group dataset
171 */
172$conf['auth']['mysql']['delGroup']    = "DELETE FROM groups
173                                         WHERE gid='%{gid}'";
174
175/* This statement should return the database index of a given user name.
176 * The module will access the index with the name 'id' so a alias might be
177 * necessary.
178 * following patters will be replaced:
179 *   %{user}    user name
180 */
181$conf['auth']['mysql']['getUserID']   = "SELECT uid AS id
182                                         FROM users
183                                         WHERE login='%{user}'";
184
185/***********************************************************************/
186/*   Additional SQL statements to delete users with the user manager   */
187/***********************************************************************/
188
189/* This statement should remove a user fom the database.
190 * Following patterns will be replaced:
191 *   %{user}    user's login name
192 *   %{uid}     id of a user dataset
193 */
194$conf['auth']['mysql']['delUser']     = "DELETE FROM users
195                                         WHERE uid='%{uid}'";
196
197/* This statement should remove all connections from a user to any group
198 * (a user quits membership of all groups).
199 * Following patterns will be replaced:
200 *   %{uid}     id of a user dataset
201 */
202$conf['auth']['mysql']['delUserRefs'] = "DELETE FROM usergroup
203                                         WHERE uid='%{uid}'";
204
205/***********************************************************************/
206/*   Additional SQL statements to modify users with the user manager   */
207/***********************************************************************/
208
209/* This statements should modify a user entry in the database. The
210 * statements UpdateLogin, UpdatePass, UpdateEmail and UpdateName will be
211 * added to updateUser on demand. Only changed parameters will be used.
212 *
213 * Following patterns will be replaced:
214 *   %{user}    user's login name
215 *   %{pass}    password (encrypted or clear text, depends on 'encryptPass')
216 *   %{email}   email address
217 *   %{name}    user's full name
218 *   %{uid}     user id that should be updated
219 */
220$conf['auth']['mysql']['updateUser']  = "UPDATE users SET";
221$conf['auth']['mysql']['UpdateLogin'] = "login='%{user}'";
222$conf['auth']['mysql']['UpdatePass']  = "pass='%{pass}'";
223$conf['auth']['mysql']['UpdateEmail'] = "email='%{email}'";
224$conf['auth']['mysql']['UpdateName']  = "firstname=SUBSTRING_INDEX('%{name}',' ', 1),
225                                         lastname=SUBSTRING_INDEX('%{name}',' ', -1)";
226$conf['auth']['mysql']['UpdateTarget']= "WHERE uid=%{uid}";
227
228/* This statement should remove a single connection from a user to a
229 * group (a user quits membership of that group).
230 *
231 * Following patterns will be replaced:
232 *   %{user}    user's login name
233 *   %{uid}     id of a user dataset
234 *   %{group}   group name
235 *   %{gid}     id of a group dataset
236 */
237$conf['auth']['mysql']['delUserGroup']= "DELETE FROM usergroup
238                                         WHERE uid='%{uid}'
239                                         AND gid='%{gid}'";
240
241/* This statement should return the database index of a given group name.
242 * The module will access the index with the name 'id' so a alias might
243 * be necessary.
244 *
245 * Following patters will be replaced:
246 *   %{group}   group name
247 */
248$conf['auth']['mysql']['getGroupID']  = "SELECT gid AS id
249                                         FROM groups
250                                         WHERE name='%{group}'";
251
252
Note: See TracBrowser for help on using the browser.