ERROR: Database is not yet set up for UserAdmin Dbase.

<?php if (!defined('PmWiki')) exit();
#
# PROGRAM: useradmin-dbase.php
#  AUTHOR: Peter Bowers
#    DATE: 2015-04-29
# PURPOSE: Complimentary script to UserAdmin recipe for pmwiki. Allows users and groups
#          to be stored in a database.
#

/*	=== UserAdmin-Dbase ===
 *	Copyright 2015 Peter Bowers <plbowers@gmail.com>
 *
 *	AuthUser account self-registration and management using database (mysqli)
 *
 *	For more information, please see the online documentation at
 *		http://www.pmwiki.org/wiki/Cookbook/UserAdmin
 *
 *  This script is free software: you can redistribute it and/or modify
 *  it under the terms of the GNU General Public License as published by
 *  the Free Software Foundation, either version 3 of the License, or
 *  (at your option) any later version.
 *
 *  This script is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */

$RecipeInfo['UserAdmin']['Version'] = '2015-05-11';
#echo "DEBUG: useradmin-dbase: Being included<br />\n";

SDV($AuthUserFunctions['useradmindb'], 'AuthUserDbase');
function AuthUserDbase($pagename, $id, $pw, $pwlist, &$authlist) 
{
    global $UserAdmin, $AuthUser;
    static $first = true;

    echo "AUD(pagename=$pagename, id=$id, pw=$pw, pwlist=$pwlist): Entering.<br />\n";
    #echo "DEBUG: authlist=<pre>".print_r($authlist,true)."</pre><br />\n";
    #echo "DEBUG: UserAdmin=<pre>".print_r($UserAdmin,true)."</pre><br />\n";
    $row = $UserAdmin->ReadUser($id);
    #echo "DEBUG: row=<pre>".print_r($row,true)."</pre><br />\n";
    #echo "DEBUG: crypt($pw, $row[userpwhash])="._crypt($pw, $row['userpwhash'])."<br />\n";
    if (!empty($row['userpwhash']) && (_crypt($pw, $row['userpwhash']) == $row['userpwhash'])) {
        $groups = $UserAdmin->ReadGroups($id, true);
        echo "DEBUG: AUD: groups=<pre>".print_r($groups,true)."</pre><br />\n";
        foreach ($groups as $g)
            $authlist[$g] = 1;
        echo "DEBUG: AUD: authlist=<pre>".print_r($authlist,true)."</pre><br />\n";
        #exit; //DEBUG:
        return true;
    }
    #echo "DEBUG: AUD: Returning FALSE<br />\n";
    return false;
}

# If this is uncommented then we can't log in
#if (strncmp($action, 'user', 4)) return;

require_once('useradmin-core.php');

class UserAdminDbase extends UserAdmin {

    var $EnableNestedGroups = true; // allow nested groups

    var $pdo; // database connection
    var $dbprefix = '';
    function __construct($dsn, $user, $pass, $opts=null, $prefix='')
    {
        try {
            $this->pdo = new PDO($dsn, $user, $pass, $opts);
        } catch (PDOException $e) {
            $this->dblog( "Connection failed: ".$e->getMessage(), true);
        }
        if ($prefix) 
            $this->dbprefix = $prefix;
    }
    function dblog($msg, $fatal=true)
    {
        echo $msg;
        if ($fatal) exit;
    }

        /* check that the db structure has been created */
/*
        $sql = "SHOW TABLES LIKE '{$this->dbconfig['prefix']}uadb'";
        $result = $conn->query($sql);
        if ($result && $result->num_rows > 0) {
            $this->conn = $conn;
            return $conn;
        }
        echo "<h1>ERROR: Database is not yet set up for UserAdmin Dbase.</h1>\n";
        exit(1);
*/

    function dbtablename($tbl)
    {
        if ($this->dbprefix)
            $tbl = $this->dbprefix.$tbl;
        return $tbl;
    }
    function dbprepare($sql)
    {
        if (!$result = $this->pdo->prepare($sql)) {
            $errmsg = "DB ERROR: Cannot prepare. SQL=<$sql> error({$this->conn->errno})=$this->conn->error\n";
            $this->dberror($errmsg);
        }
        return $result;
    }
    function dbquery($sql)
    {
        if (!$this->dbconnect())
            exit; // this shouldn't ever really happen
        if (!($stmt = $this->conn->query($sql)) || $this->conn->errno) {
            $errmsg = "DB ERROR: Cannot query. SQL=<$sql> error({$this->conn->errno})=$this->conn->error\n";
            $this->dberror($errmsg);
        }
        return $stmt;
    }
    function dbfetch($result)
    {
        $rtn = mysqli_fetch_assoc($result);
        if ($this->conn->errno) {
            $errmsg = "DB ERROR: Cannot fetch. error({$this->conn->errno})=$this->conn->error\n";
            $this->dberror($errmsg);
        }
        return $rtn;
    }
    function dberror($msg)
    {
        # This would be a good place for logging
        die($msg);
    }

    function ReadUser($username, $readgroup=false) 
    {
        $sql = "SELECT * 
            FROM ".$this->dbtablename('users')." 
            WHERE username = :username ";
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute(array(':username'=>$username));
        } catch (PDOException $e) {
            $this->dblog("ReadUser(): Error: SQL=$sql ERROR=".$e->getMessage());
        }
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        # If I do this "union" of results then WriteUser() doesn't know where the read came from
        #if (!$row) $row = parent::ReadUser($username, $readgroup);
        return $row;
    }
    
    function WriteUser($username, $data, $csum='', $auth='read') 
    {
        $row = $this->ReadUser($username);
        if ($row) {
            # UPDATE
            $sql = "UPDATE ".$this->dbtablename('users')." SET ";
            $sep = '';
            $bindvars = array();
            foreach ($data as $k=>$v) {
                if ($k == 'username') continue;
                $sql .= "$sep$k=:$k";
                $bindvars[":$k"] = $v;
                $sep = ',';
            }
            $sql .= " WHERE username = :username ";
            $bindvars[':username'] = $username; // needed for the WHERE clause
        } else {
            # INSERT
            $sql = "INSERT INTO ".$this->dbtablename('users')." (username";
            $vals= "VALUES (:username";
            $bindvars=array(':username'=>$username);
            foreach ($data as $k=>$v) {
                if ($k == 'username') continue; // already put in above
                $sql .= ", $k";
                $vals.= ", :$k";
                $bindvars[":$k"] = $v;
            }
            $sql .= ") ".$vals.")";
        }
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute($bindvars);
        } catch (PDOException $e) {
            $this->dblog("WriteUser(): Error SQL=$sql ERROR=".$e->getMessage());
        }
        return true;
    }

    function ListGroups($pat=NULL)
    {
        $sql = "SELECT groupname FROM ".$this->dbtablename('groups');
        if ($pat)
            $sql .= " WHERE groupname = :groupname";
        try {
            $stmt = $this->pdo->prepare($sql);
            if ($pat) $stmt->execute(array(':groupname'=>$pat)); // do we really need a "pattern" or is groupname enuf?
            else $stmt->execute();
        } catch (PDOException $e) {
            $this->dblog("ListGroups(): Error SQL=$sql ERROR=".$e->getMessage());
        }
        $rtn = array();
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC))
            $rtn[] = $row['groupname'];
        #echo "DEBUG: ListGroups(): rtn=<pre>".print_r($rtn,true)."</pre><br />\n";
        return $rtn;
    }
    function ListUsers($pat=NULL)
    {
        $sql = "SELECT * 
            FROM ".$this->dbtablename('users');
        if ($pat)
            $sql .= " WHERE username = :username";
        try {
            $stmt = $this->pdo->prepare($sql);
            if ($pat) $stmt->execute(array(':username'=>$pat)); // do we really need a "pattern" or is username enuf?
            else $stmt->execute();
        } catch (PDOException $e) {
            $this->dblog("ListUsers(): Error SQL=$sql ERROR=".$e->getMessage());
        }
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        #echo "DEBUG: ListUsers($pat): rows=<pre>".print_r($rows,true)."</pre><br />\n";
        $rtn = array();
        foreach ($rows as $row)
            $rtn[] = $row['username'];
        return $rtn;
    }
	############################################################################
	## ReadGroups()
	## with username: returns array('@groupA', '@groupB', ...)
	##  w/o username: returns array('@groupA' => array('userA', 'userB', ...), '@groupB' => ...)
	############################################################################
    function ReadGroups($username=NULL, $recursive=false)
    {
        if ($username) {
            $sql = "SELECT groupname  
                    FROM ".$this->dbtablename('group_members')."
                    WHERE username = :username";
            try {
                $stmt = $this->pdo->prepare($sql);
                $stmt->execute(array(':username'=>$username));
            } catch (PDOException $e) {
                $this->dblog("ReadGroups(): Error SQL=$sql ERROR=".$e->getMessage());
            }
            $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
            $grouplist = array();
            foreach($rows as $row) {
                if (!in_array($row['groupname'], $grouplist)) {
                    $grouplist[] = $row['groupname'];
                    if ($recursive)
                        $this->NestedGroupParents($row['groupname'], $grouplist);
                }
            }
            return $grouplist;
        } else {
            $sql = "SELECT groupname  
                    FROM ".$this->dbtablename('groups');
            $rtn = array();
            foreach ($this->pdo->query($sql) as $row) {
                $members = array();
                $rtn[$row['groupname']] = $this->NestedGroupUsers($row['groupname'], $members, $recursive);
            }
            #echo "DEBUG: ReadGroups(): Returning <pre>".print_r($rtn,true)."</pre><br />\n";
            return $rtn;
        }
    }
    function NestedGroupUsers($groupname, &$grouplist, $recursive=false)
    {
        #echo "DEBUG: NestedGroupUsers($groupname): Entering<br />\n";
        $sql = "SELECT username, user_is_group
                FROM ".$this->dbtablename('group_members')."
                WHERE groupname = :groupname";
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute(array(':groupname'=>$groupname));
        } catch (PDOException $e) {
            $this->dblog("NestedGroupUsers(): Error SQL=$sql ERROR=".$e->getMessage());
        }
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        #echo "DEBUG: NGU($groupname): rows=<pre>".print_r($rows,true)."</pre><br />\n";
        $rtn = array();
        foreach($rows as $row) {
            if ($recursive) {
                if ($row['user_is_group']) {
                    if (!in_array($row['username'], $grouplist)) {
                        $grouplist[] = $row['username'];
                        $rtn = array_merge($rtn, $this->NestedGroupUsers($row['username'], $grouplist, $recursive));
                    }
                } else {
                    if (!in_array($row['username'], $rtn))
                        $rtn[] = $row['username'];
                }
            } else {
                if (!in_array($row['username'], $rtn))
                    $rtn[] = $row['username'];
            }
        }
        #echo "DEBUG: recursive=<pre>".print_r($recursive,true)."</pre><br />\n";
        #echo "DEBUG: NGU($groupname): Returning <pre>".print_r($rtn,true)."</pre><br />\n";
        return $rtn;
    }
    function NestedGroupParents($groupname, &$grouplist)
    {
        $sql = "SELECT groupname  
                FROM ".$this->dbtablename('group_members')."
                WHERE username = :groupname
                AND user_is_group = 1";
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute(array(':groupname'=>$groupname));
        } catch (PDOException $e) {
            $this->dblog("NestedGroupParents(): Error SQL=$sql ERROR=".$e->getMessage());
        }
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        foreach($rows as $row) {
            if (!in_array($row['groupname'], $grouplist)) {
                $grouplist[] = $row['groupname'];
                $this->NestedGroupParents($row['groupname'], $grouplist);
            }
        }
    }

	## $add is an array of members to add to group
	## $del is an array of members to remove from group
    function WriteGroup($groupname, $add, $del, $csum='', $auth='read') 
    {
        $rtn = true;
        #echo "DEBUG: add=<pre>".print_r($add,true)."</pre><br />\n";
        #echo "DEBUG: del=<pre>".print_r($del,true)."</pre><br />\n";
        #
        # First check to make sure the group actually exists and, if not, insert it
        #
        $sql = "SELECT * FROM ".$this->dbtablename('groups')."
                WHERE groupname = :groupname";
        try {
            $stmt = $this->pdo->prepare($sql);
            $stmt->execute(array(':groupname'=>$groupname));
            $groupexists = $stmt->fetch(PDO::FETCH_ASSOC);
            $stmt->closeCursor();
        } catch (PDOException $e) {
            $this->dblog("WriteGroup(chkexist): Error SQL=$sql ERROR=".$e->getMessage());
            $rtn = false;
        }
        if ($rtn && !$groupexists) {
            $sql = "INSERT INTO ".$this->dbtablename('groups')."
                    (groupname) VALUES (:groupname)";
            try {
                $stmt = $this->pdo->prepare($sql);
                $stmt->execute(array(':groupname'=>$groupname));
            } catch (PDOException $e) {
                $this->dblog("WriteGroup(addgroup): Error SQL=$sql ERROR=".$e->getMessage());
                $rtn = false;
            }
        }
        #
        # Now delete from and/or insert into group_members as appropriate
        #
        if ($del) {
            $sql = "DELETE FROM ".$this->dbtablename('group_members')."
                    WHERE groupname = :groupname 
                    AND username = :username";
            try {
                $stmt = $this->pdo->prepare($sql);
                foreach ($del as $d)
                    $stmt->execute(array(':groupname'=>$groupname, ':username'=>$d));
            } catch (PDOException $e) {
                $this->dblog("WriteGroup(del): Error SQL=$sql ERROR=".$e->getMessage());
                $rtn = false;
            }
        }
        if ($add) {
            $sql = "INSERT INTO ".$this->dbtablename('group_members')."
                    ( groupname, username, user_is_group )
                    VALUES (:groupname, :username, IF(SUBSTR(:username2,1,1)='@', 1, 0))";
            try {
                $stmt = $this->pdo->prepare($sql);
                foreach ($add as $a)
                    $stmt->execute(array(':groupname'=>$groupname, ':username'=>$a, ':username2'=>$a));
            } catch (PDOException $e) {
                $this->dblog("WriteGroup(add): Error SQL=$sql ERROR=".$e->getMessage());
                $rtn = false;
            }
        }
        return $rtn;
    }

    function HandleInstall($pagename)
    {
        if (!$this->Superuser($pagename)) {
            echo "<h1>You do not have the necessary privileges. Aborting.</h1>\n";
            exit(1);
        }
        /*************************
         * DATABASE STRUCTURE:
         *************************/
        $prefix = $this->dbprefix;
        $db_create_queries = array(
            "CREATE TABLE `${prefix}users` (
              `username` varchar(50) NOT NULL,
              `userpwhash` varchar(100) NOT NULL,
              `useremail` varchar(250) NOT NULL,
              `userrealname` varchar(250) DEFAULT NULL,
              `userfname` varchar(150) DEFAULT NULL,
              `userlname` varchar(150) DEFAULT NULL,
              `userkey` varchar(100) DEFAULT NULL,
              `userkeyreason` varchar(100) DEFAULT NULL,
              `userkeytime` int(15) DEFAULT NULL,
              `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
              PRIMARY KEY (`username`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8", 
            "CREATE TABLE `${prefix}groups` (
              `groupname` varchar(50) NOT NULL,
              `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
              PRIMARY KEY (`groupname`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8", 
            "CREATE TABLE `${prefix}group_members` (
              `groupname` varchar(50) NOT NULL,
              `username` varchar(50) NOT NULL,
              `admin` tinyint(1) NOT NULL DEFAULT '0',
              `user_is_group` tinyint(1) NOT NULL DEFAULT '0',
              `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
              PRIMARY KEY (`groupname`,`username`),
              KEY `groupname` (`groupname`),
              KEY `username` (`username`)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8", 
            "CREATE TABLE `${prefix}uadb` (
                `do_I_exist` varchar(1) DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8",
        );
            try {
                foreach ($db_create_queries as $sql)
                    $this->pdo->exec($sql);
            } catch (PDOException $e) {
                $this->dblog("HandleInstall(): ERROR: SQL=$sql ".$e->getMessage());
            }
        echo "<h2>Success. Database Tables Created</h2>\n";
    }
}

# This should be called from config.php (or farmconfig.php) AFTER including this file
#$UserAdmin = new UserAdminDbase('dbn', 'user', 'pass', array(options...), 'table_name_prefix');