xref: /dokuwiki/conf/mysql.conf.php.example (revision a771ad3a6d06253269a7ddf2da2fe9eab71dd895)

<?php
/*
 * This is an example configuration for the mysql auth module.
 *
 * This SQL statements are optimized for following table structure.
 * If you use a different one you have to change them accordingly.
 * See comments of every statement for details.
 *
 * TABLE users
 *     uid   login   pass   firstname   lastname   email
 *
 * TABLE groups
 *     gid   name
 *
 * TABLE usergroup
 *     uid   gid
 *
 * To use this configuration you have to copy them to local.php
 * or at least include this file in local.php.
 */

/* Options to configure database access. You need to set up this
 * options carefully, otherwise you won't be able to access you
 * database.
 */
$conf['auth']['mysql']['server']   = '';
$conf['auth']['mysql']['user']     = '';
$conf['auth']['mysql']['password'] = '';
$conf['auth']['mysql']['database'] = '';

/* Normally password encryptionis done by DokuWiki (recommended) but for
 * some reasons it might be usefull to let the database do the encryption.
 * Set 'encryptPass' to '1' and the cleartext password is forwarded to
 * the database, otherwise the encrypted one.
 */
$conf['auth']['mysql']['encryptPass'] = 0;

/* Multiple table operations will be protected by locks. This array tolds
 * the module which tables to lock. If you use any aliases for table names
 * these array must also contain these aliases. Any unamed alias will cause
 * a warning during operation. See the example below.
 */
$conf['auth']['mysql']['TablesToLock']= array("users", "users AS u","groups", "groups AS g", "usergroup", "usergroup AS ug");

/* This statement should return the database index of a given user name.
 * The module will access the index with the name 'id' so a alias might be
 * necessary.
 * following patters will be replaced:
 *   %{user}	user name
 */
$conf['auth']['mysql']['getUserID']   = "SELECT uid AS id FROM users WHERE login='%{user}'";

/* This statement should return the database index of a given group name.
 * The module will access the index with the name 'id' so a alias might be
 * necessary.
 * following patters will be replaced:
 *   %{group}	group name
 */
$conf['auth']['mysql']['getGroupID']  = "SELECT gid AS id FROM groups WHERE name='%{group}'";

/* This statement is used to grant or deny access to the wiki. The result should
 * be a table with exact one line containing at least the password of the user.
 * If the result table is empty or contains more than one row, access will be denied.
 * The module access the password as 'pass' so a alias might be necessary.
 * following patters will be replaced:
 *   %{user}	user name
 *   %{pass}	encrypted or clear text password (depends on 'encryptPass')
 *   %{dgroup}	default group name
 */
$conf['auth']['mysql']['checkPass']   = "SELECT pass
                                         FROM usergroup AS ug
                                         JOIN users AS u ON u.uid=ug.uid
                                         JOIN groups AS g ON g.gid=ug.gid
                                         WHERE login='%{user}'
                                         AND name='%{dgroup}'";

/* This statement is used to get all groups a user is member of. The result should
 * be a table containing all groups the given user is member of. The module access
 * the group name as 'group' so a alias might be nessecary.
 * following patters will be replaced:
 *   %{user}	user name
 */
$conf['auth']['mysql']['getGroups']   = "SELECT name as `group`
                                         FROM groups g, users u, usergroup ug
                                         WHERE u.uid = ug.uid
                                         AND g.gid = ug.gid
                                         AND u.login='%{user}'";

/* This statement should return a table with exact one row containing information
 * about one user. The field needed are:
 * 'pass'  containing the encrypted or clear text password
 * 'name'  the user's full name
 * 'mail'  the user's email address
 * Keep in mind that Dokuwiki will access thise information through the names
 * listed above so aliasses might be neseccary.
 * following patters will be replaced:
 *   %{user}	user name
 */
$conf['auth']['mysql']['getUserInfo'] = "SELECT pass, CONCAT(firstname,' ',lastname) AS name, email AS mail
                                         FROM users
                                         WHERE login='%{user}'";

/* This statement should return a table containing all user login names that meet
 * certain filter criteria. The filter expressions will be added case dependend by
 * the module. At the end a sort expression will be added.
 * Important is that this list contains no double entries fo a user. Each user
 * name is only allowed once in the table.
 * The login name will be accessed as 'user' to a alias might be neseccary.
 * No patterns will be replaced in this statement but following patters will be
 * replaced in the filter expressions:
 *   %{user}	in FilterLogin  user's login name
 *   %{name}	in FilterName   user's full name
 *   %{email}	in FilterEmail  user's email address
 *   %{group}	in FilterGroup  group name
 */
$conf['auth']['mysql']['getUsers']    = "SELECT DISTINCT login AS user
                                         FROM users AS u
                                         LEFT JOIN usergroup AS ug ON u.uid=ug.uid
                                         LEFT JOIN groups AS g ON ug.gid=g.gid";
$conf['auth']['mysql']['FilterLogin'] = "login LIKE '%{user}'";
$conf['auth']['mysql']['FilterName']  = "CONCAT(firstname,' ',lastname) LIKE '%{name}'";
$conf['auth']['mysql']['FilterEmail'] = "email LIKE '%{email}'";
$conf['auth']['mysql']['FilterGroup'] = "name LIKE '%{group}'";
$conf['auth']['mysql']['SortOrder']   = "ORDER BY login";

/* This statement should add a user to the database. Minimum information to
 * store are: login name, password, email address and full name.
 * Following patterns will be replaced:
 *   %{user}	user's login name
 *   %{pass}	password (encrypted or clear text, depends on 'encryptPass')
 *   %{email}	email address
 *   %{name}	user's full name
 */
$conf['auth']['mysql']['addUser']     = "INSERT INTO users
                                         (login, pass, email, firstname, lastname)
                                         VALUES ('%{user}', '%{pass}', '%{email}',
                                         SUBSTRING_INDEX('%{name}',' ', 1),
                                         SUBSTRING_INDEX('%{name}',' ', -1))";

/* This statement should remove a user fom the database.
 * Following patterns will be replaced:
 *   %{user}	user's login name
 *   %{uid}		id of a user dataset
 */
$conf['auth']['mysql']['delUser']     = "DELETE FROM users
                                         WHERE uid='%{uid}'";

/* This statement should add a group to the database.
 * Following patterns will be replaced:
 *   %{group}	group name
 */
$conf['auth']['mysql']['addGroup']    = "INSERT INTO groups (name)
                                         VALUES ('%{group}')";

/* This statement should remove a group fom the database.
 * Following patterns will be replaced:
 *   %{group}	group name
 *   %{gid}		id of a group dataset
 */
$conf['auth']['mysql']['delGroup']    = "DELETE FROM groups
                                         WHERE gid='%{gid}'";

/* This statement should connect a user to a group (a user become member
 * of that group).
 * Following patterns will be replaced:
 *   %{user}	user's login name
 *   %{uid}		id of a user dataset
 *   %{group}	group name
 *   %{gid}		id of a group dataset
 */
$conf['auth']['mysql']['addUserGroup']= "INSERT INTO usergroup (uid, gid)
                                         VALUES ('%{uid}', '%{gid}')";

/* This statement should remove a single connection from a user to a
 * group (a user quits membership of that group).
 * Following patterns will be replaced:
 *   %{user}	user's login name
 *   %{uid}		id of a user dataset
 *   %{group}	group name
 *   %{gid}		id of a group dataset
 */
$conf['auth']['mysql']['delUserGroup']= "DELETE FROM usergroup
                                         WHERE uid='%{uid}'
                                         AND gid='%{gid}'";

/* This statement should remove all connections from a user to any group
 * (a user quits membership of all groups).
 * Following patterns will be replaced:
 *   %{uid}		id of a user dataset
 */
$conf['auth']['mysql']['delUserRefs'] = "DELETE FROM usergroup
                                         WHERE uid='%{uid}'";