<?php
/**
* SeekQuarry/Yioop --
* Open Source Pure PHP Search Engine, Crawler, and Indexer
*
* Copyright (C) 2009 - 2026 Chris Pollett chris@pollett.org
*
* LICENSE:
*
* This program 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 program 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 <https://www.gnu.org/licenses/>.
*
* END LICENSE
*
* @author Chris Pollett chris@pollett.org
* @license https://www.gnu.org/licenses/ GPL3
* @link https://www.seekquarry.com/
* @copyright 2009 - 2026
* @filesource
*/
namespace seekquarry\yioop\models;
use seekquarry\yioop\configs as C;
use seekquarry\yioop\library as L;
/** For getLocaleTag*/
require_once __DIR__.'/../library/LocaleFunctions.php';
/**
* This is class is used to handle
* db results related to Role Administration
*
* @author Chris Pollett
*/
class RoleModel extends Model
{
/**
* Used to map between search role form variables and database columns
* @var array
*/
public $search_table_column_map = ["name"=>"NAME"];
/** {@inheritDoc}
*
* @param mixed $args any additional arguments which should be used to
* determine the columns (ignored in this case)
* @return string a comma separated list of columns suitable for a SQL
* query ("NAME" in this case)
*/
public function selectCallback($args = null)
{
return "NAME";
}
/**
* Get the activities (name, method, id) that a given role can perform
*
* @param string $role_id the rolid_id to get activities for
* @return array list of activity rows (each having ROLE_ID,
* ACTIVITY_ID, METHOD_NAME, ALLOWED_ARGUMENTS, plus the
* localized activity name) for the given role
*/
public function getRoleActivities($role_id)
{
$db = $this->db;
$activities = [];
$locale_tag = L\getLocaleTag();
$sql = "SELECT LOCALE_ID FROM LOCALE WHERE LOCALE_TAG = ? " .
$db->limitOffset(1);
$result = $db->execute($sql, [$locale_tag]);
$row = $db->fetchArray($result);
$locale_id = $row['LOCALE_ID'];
$sql = "SELECT DISTINCT R.ROLE_ID AS ROLE_ID, ".
"RA.ACTIVITY_ID AS ACTIVITY_ID, ".
"A.METHOD_NAME AS METHOD_NAME, ".
"RA.ALLOWED_ARGUMENTS AS ALLOWED_ARGUMENTS, ".
"T.IDENTIFIER_STRING AS IDENTIFIER_STRING, ".
"T.TRANSLATION_ID AS TRANSLATION_ID FROM ".
"ROLE R, ROLE_ACTIVITY RA, ACTIVITY A, TRANSLATION T ".
"WHERE R.ROLE_ID = ? AND ".
"R.ROLE_ID = RA.ROLE_ID AND T.TRANSLATION_ID = A.TRANSLATION_ID ".
"AND RA.ACTIVITY_ID = A.ACTIVITY_ID";
$result = $db->execute($sql, [$role_id]);
$i = 0;
$sub_sql = "SELECT TRANSLATION AS ACTIVITY_NAME ".
"FROM TRANSLATION_LOCALE ".
"WHERE TRANSLATION_ID=? AND LOCALE_ID=? " . $db->limitOffset(1);
// maybe do left join at some point
while ($activities[$i] = $db->fetchArray($result)) {
$id = $activities[$i]['TRANSLATION_ID'];
$result_sub = $db->execute($sub_sql, [$id, $locale_id]);
$translate = $db->fetchArray($result_sub);
if ($translate) {
$activities[$i]['ACTIVITY_NAME'] = $translate['ACTIVITY_NAME'];
} else {
$activities[$i]['ACTIVITY_NAME'] =
$activities['IDENTIFIER_STRING'];
}
$i++;
}
unset($activities[$i]); //last one will be null
return $activities;
}
/**
* Gets all the roles associated with a user id
*
* @param string $user_id the user_id to get roles of
* @param string $filter to SQL LIKE filter roles
* @param array $sorts directions on how to sort the columns of the
* results format is column_name => direction
* @param int $limit starting index of first role to return
* @param int $num number of roles to return
* @return array of role_ids and their names
*/
public function getUserRoles($user_id, $filter, $sorts = [], $limit = 0,
$num = C\NUM_RESULTS_PER_PAGE)
{
$db = $this->db;
$user_id = $db->escapeString($user_id);
$roles = [];
$locale_tag = L\getLocaleTag();
$limit = $db->limitOffset($limit, $num);
$like = "";
$param_array = [$user_id];
if ($filter != "") {
$like = "AND LOWER(R.NAME) LIKE LOWER(?)";
$param_array[] = "%".$filter."%";
}
$order_by = "";
if (!empty($sorts)) {
$sort_fields = ["ROLE_NAME"];
$directions = ["ASC", "DESC"];
foreach ($sorts as $column_name => $direction) {
if (in_array($column_name, $sort_fields) &&
in_array($direction, $directions)) {
if (empty($order_by)) {
$order_by = " ORDER BY ";
} else {
$order_by .= ",";
}
$order_by .= " $column_name $direction";
}
}
}
$sql = "SELECT LOCALE_ID FROM LOCALE ".
"WHERE LOCALE_TAG = ? ". $db->limitOffset(1);
$result = $db->execute($sql, [$locale_tag]);
$row = $db->fetchArray($result);
$locale_id = $row['LOCALE_ID'];
$sql = "SELECT UR.ROLE_ID AS ROLE_ID, R.NAME AS ROLE_NAME ".
" FROM USER_ROLE UR, ROLE R WHERE UR.USER_ID = ? ".
" AND R.ROLE_ID = UR.ROLE_ID $like $order_by $limit";
$result = $db->execute($sql, $param_array);
while ($row = $db->fetchArray($result)) {
$roles[] = $row;
}
return $roles;
}
/**
* Get a count of the number of groups that a user_id has.
*
* @param int $user_id to get roles for
* @param string $filter to SQL LIKE filter roles
* @return int number of roles of the filtered type for the user
*/
public function countUserRoles($user_id, $filter="")
{
$db = $this->db;
$users = [];
$like = "";
$param_array = [$user_id];
if ($filter != "") {
$like = "AND LOWER(R.NAME) LIKE LOWER(?)";
$param_array[] = "%".$filter."%";
}
$sql = "SELECT COUNT(*) AS NUM ".
" FROM USER_ROLE UR, ROLE R".
" WHERE UR.USER_ID = ? AND UR.ROLE_ID = R.ROLE_ID $like";
$result = $db->execute($sql, $param_array);
if ($result) {
$row = $db->fetchArray($result);
}
return $row['NUM'];
}
/**
* Check is a user given by $user_id has the role $role_id
*
* @param int $user_id user to look up
* @param int $role_id role to check if member of
* @return bool whether or not has role
*/
public function checkUserRole($user_id, $role_id)
{
$db = $this->db;
$params = [$user_id, $role_id];
$sql = "SELECT COUNT(*) AS NUM FROM USER_ROLE UR WHERE
UR.USER_ID=? AND UR.ROLE_ID=?";
$result = $db->execute($sql, $params);
if (!$row = $db->fetchArray($result)) {
return false;
}
if ($row['NUM'] <= 0) {
return false;
}
return true;
}
/**
* Adds a role to a given user, optionally with an expiry time after
* which the grant lapses and stops conferring the role's activities.
* A grant that never lapses uses the FOREVER sentinel, the default,
* so admin-granted roles stay permanent.
*
* @param string $user_id the id of the user to add the role to
* @param string $role_id the id of the role to add
* @param int $expires timestamp after which the role lapses, or
* FOREVER for a grant that never lapses
* @param int $will_renew 1 if the account page should spend credits
* to renew this grant when it lapses, 0 if the user cancelled it
* and it should be removed once it lapses
*/
public function addUserRole($user_id, $role_id, $expires = C\FOREVER,
$will_renew = 1)
{
$sql = "INSERT INTO USER_ROLE " .
"(USER_ID, ROLE_ID, EXPIRES, WILL_RENEW) VALUES (?, ?, ?, ?)";
$this->db->execute($sql,
[$user_id, $role_id, $expires, $will_renew]);
}
/**
* Sets whether a role a user already holds should keep renewing. Used
* to cancel a subscription (0, so the grant is removed once it lapses)
* or to resume one before it lapses (1, at no new charge until the
* current paid period ends).
*
* @param string $user_id the id of the user whose grant to update
* @param string $role_id the id of the role whose grant to update
* @param int $will_renew 1 to keep renewing, 0 to stop and let it
* lapse
*/
public function setUserRoleRenew($user_id, $role_id, $will_renew)
{
$sql = "UPDATE USER_ROLE SET WILL_RENEW = ? " .
"WHERE USER_ID = ? AND ROLE_ID = ?";
$this->db->execute($sql, [$will_renew, $user_id, $role_id]);
}
/**
* Deletes a role from a given user
*
* @param string $user_id the id of the user to delete the role from
* @param string $role_id the id of the role to delete
*/
public function deleteUserRole($user_id, $role_id)
{
$sql = "DELETE FROM USER_ROLE WHERE USER_ID=? AND ROLE_ID=?";
$this->db->execute($sql, [$user_id, $role_id]);
}
/**
* Moves the expiry of a role a user already holds to a new time.
* Used when credits are spent to renew a lapsed subscription, pushing
* its expiry forward one billing period at a time.
*
* @param string $user_id the id of the user whose grant to update
* @param string $role_id the id of the role whose grant to update
* @param int $expires new timestamp after which the grant lapses
*/
public function updateUserRoleExpires($user_id, $role_id, $expires)
{
$sql = "UPDATE USER_ROLE SET EXPIRES = ? " .
"WHERE USER_ID = ? AND ROLE_ID = ?";
$this->db->execute($sql, [$expires, $user_id, $role_id]);
}
/**
* Returns the sellable roles a given user currently holds, each with
* its cost, charge frequency, and the time the grant expires. Powers
* the account page's list of a user's subscriptions with their
* unsubscribe links, and the renewal pass that spends credits to
* extend lapsed grants.
*
* @param int $user_id id of the user whose sellable roles to fetch
* @return array rows with ROLE_ID, NAME, ROLE_COST, CHARGE_FREQUENCY,
* EXPIRES, WILL_RENEW for each sellable role the user holds,
* ordered by name
*/
public function getUserSellableRoles($user_id)
{
$db = $this->db;
$sql = "SELECT R.ROLE_ID AS ROLE_ID, R.NAME AS NAME, " .
"RL.ROLE_COST AS ROLE_COST, " .
"RL.CHARGE_FREQUENCY AS CHARGE_FREQUENCY, " .
"UR.EXPIRES AS EXPIRES, UR.WILL_RENEW AS WILL_RENEW " .
"FROM USER_ROLE UR, ROLE R, ROLE_LIMITS RL " .
"WHERE UR.USER_ID = ? AND UR.ROLE_ID = R.ROLE_ID " .
"AND R.ROLE_ID = RL.ROLE_ID AND RL.ROLE_COST > 0 " .
"AND RL.CHARGE_FREQUENCY <> 'never' ORDER BY R.NAME";
$result = $db->execute($sql, [$user_id]);
$roles = [];
if ($result) {
while ($row = $db->fetchArray($result)) {
$roles[] = $row;
}
}
return $roles;
}
/**
* Returns the roles a user may buy: those whose ROLE_LIMITS row has a
* cost above zero and a billing frequency other than "never". Each
* row carries the role's id, name, cost in credits, and charge
* frequency so the purchase form can show its terms; a frequency of
* "once" is a one-time permanent buy, the others are subscriptions.
*
* @return array rows with ROLE_ID, NAME, ROLE_COST, CHARGE_FREQUENCY
* for every sellable role, ordered by name
*/
public function getSellableRoles()
{
$db = $this->db;
$sql = "SELECT R.ROLE_ID AS ROLE_ID, R.NAME AS NAME, " .
"RL.ROLE_COST AS ROLE_COST, " .
"RL.CHARGE_FREQUENCY AS CHARGE_FREQUENCY " .
"FROM ROLE R, ROLE_LIMITS RL " .
"WHERE R.ROLE_ID = RL.ROLE_ID AND RL.ROLE_COST > 0 " .
"AND RL.CHARGE_FREQUENCY <> 'never' ORDER BY R.NAME";
$result = $db->execute($sql);
$roles = [];
if ($result) {
while ($row = $db->fetchArray($result)) {
$roles[] = $row;
}
}
return $roles;
}
/**
* Return the role_id and role_name row corresponding to a given rolename
* @param string $rolename ROLE.NAME to look up (case-insensitive)
* @return array row in role table corresponding to $rolenames
*/
public function getRole($rolename)
{
$db = $this->db;
$sql = "SELECT * FROM ROLE WHERE LOWER(NAME) = LOWER(?) " .
$db->limitOffset(1);
$result = $db->execute($sql, [$rolename]);
if (!$result) {
return false;
}
$row = $db->fetchArray($result);
return $row;
}
/**
* Add a rolename to the database using provided string
*
* @param string $role_name the rolename to be added
*/
public function addRole($role_name)
{
$sql = "INSERT INTO ROLE (NAME) VALUES (?)";
$this->db->execute($sql, [$role_name]);
}
/**
* Add an allowed activity to an existing role
*
* @param string $role_id the role id of the role to add the activity to
* @param string $activity_id the id of the activity to add
* @param string $allowed_arguments $_REQUEST['arg'] values this
* role is allowed to use. 'all' means any allowed argument.
* Otherwise would be comma separated list of allowed arguments:
* For example:
* edit, search, ...
* Can also use
* all, -arg_name1, -arg_name2 ...
* to say all arguments except arg_name1, arg_name2...
*/
public function addActivityRole($role_id, $activity_id,
$allowed_arguments = "all")
{
$sql = "INSERT INTO ROLE_ACTIVITY VALUES (?, ?, ?)";
$this->db->execute($sql, [$role_id, $activity_id, $allowed_arguments]);
}
/**
* Updates the allowed arguments of an Activity within a Role
*
* @param string $role_id the role id of the role to add the activity to
* @param string $activity_id the id of the activity to add
* @param string $allowed_arguments $_REQUEST['arg'] values this
* role is allowed to use. 'all' means any allowed argument.
* Otherwise would be comma separated list of allowed arguments:
* For example:
* edit, search, ...
* Can also use
* all, -arg_name1, -arg_name2 ...
* to say all arguments except arg_name1, arg_name2...
*/
public function updateActivityRoleArguments($role_id, $activity_id,
$allowed_arguments)
{
$sql = "UPDATE ROLE_ACTIVITY SET ALLOWED_ARGUMENTS=? WHERE ROLE_ID = ?".
" AND ACTIVITY_ID = ?";
$this->db->execute($sql, [$allowed_arguments, $role_id, $activity_id]);
}
/**
* Delete a role by its roleid
*
* @param string $role_id - the roleid of the role to delete
*/
public function deleteRole($role_id)
{
$sql = "DELETE FROM ROLE_ACTIVITY WHERE ROLE_ID=?";
$this->db->execute($sql, [$role_id]);
$sql = "DELETE FROM ROLE WHERE ROLE_ID=?";
$this->db->execute($sql, [$role_id]);
}
/**
* Remove an allowed activity from a role
*
* @param string $role_id the roleid of the role to be modified
* @param string $activity_id the activityid of the activity to remove
*/
public function deleteActivityRole($role_id, $activity_id)
{
$sql = "DELETE FROM ROLE_ACTIVITY WHERE ROLE_ID=? AND ACTIVITY_ID=?";
$this->db->execute($sql, [$role_id, $activity_id]);
}
/**
* Get role id associated with role_name (so role_names better be unique)
*
* @param string $role_name to use to look up a role_id
* @return string role_id corresponding to the rolename.
*/
public function getRoleId($role_name)
{
return self::getRoleIdWithDb($role_name, $this->db);
}
/**
* Get role id associated with role_name (so role_names better be unique)
* using a datasource
*
* @param string $role_name to use to look up a role_id
* @param object $db a $database source used to run the role table queries
* on
*
* @return string role_id corresponding to the rolename.
*/
public static function getRoleIdWithDb($role_name, $db)
{
$sql = "SELECT R.ROLE_ID AS ROLE_ID FROM ROLE R WHERE R.NAME = ? ";
$result = $db->execute($sql, [$role_name]);
if (!$row = $db->fetchArray($result)) {
return -1;
}
return $row['ROLE_ID'];
}
/**
* Returns the per-role group limits for every role so the settings screen
* can show and edit each role's caps, cost, and charge frequency. A role
* with no stored limits row comes back with null values, which the screen
* reads as the defaults (unlimited caps, zero cost, never charged).
*
* @return array rows keyed by role id, each holding the role name, the
* five cap columns, ROLE_COST, and CHARGE_FREQUENCY
*/
public function getRoleLimits()
{
$db = $this->db;
$sql = "SELECT R.ROLE_ID AS ROLE_ID, R.NAME AS NAME, " .
"RL.MAX_GROUPS_OWNED AS MAX_GROUPS_OWNED, " .
"RL.MAX_GROUP_MEMBERS AS MAX_GROUP_MEMBERS, " .
"RL.MAX_GROUP_WIKI_PAGES AS MAX_GROUP_WIKI_PAGES, " .
"RL.MAX_PAGE_RESOURCE_MEMORY AS MAX_PAGE_RESOURCE_MEMORY, " .
"RL.MAX_GROUP_THREADS AS MAX_GROUP_THREADS, " .
"RL.MAX_THREAD_RESOURCES AS MAX_THREAD_RESOURCES, " .
"RL.MAX_THREAD_POSTS AS MAX_THREAD_POSTS, " .
"RL.ROLE_COST AS ROLE_COST, " .
"RL.CHARGE_FREQUENCY AS CHARGE_FREQUENCY " .
"FROM ROLE R LEFT JOIN ROLE_LIMITS RL " .
"ON R.ROLE_ID = RL.ROLE_ID ORDER BY R.ROLE_ID";
$result = $db->execute($sql);
$limits = [];
while ($row = $db->fetchArray($result)) {
$limits[$row['ROLE_ID']] = $row;
}
return $limits;
}
/**
* Stores a role's group limits, updating the role's row in place, or
* inserting one if the role has none yet. Each cap is a whole number,
* with -1 meaning unlimited; ROLE_COST is a whole number of credits and
* CHARGE_FREQUENCY is how often that cost is charged.
*
* @param int $role_id role whose caps are being set
* @param array $caps cap column name => value for the limit columns,
* including ROLE_COST and CHARGE_FREQUENCY
*/
public function setRoleLimits($role_id, $caps)
{
$db = $this->db;
$result = $db->execute(
"SELECT ROLE_ID FROM ROLE_LIMITS WHERE ROLE_ID = ?", [$role_id]);
if ($db->fetchArray($result)) {
$db->execute("UPDATE ROLE_LIMITS SET MAX_GROUPS_OWNED = ?, " .
"MAX_GROUP_MEMBERS = ?, MAX_GROUP_WIKI_PAGES = ?, " .
"MAX_PAGE_RESOURCE_MEMORY = ?, MAX_GROUP_THREADS = ?, " .
"MAX_THREAD_RESOURCES = ?, MAX_THREAD_POSTS = ?, " .
"ROLE_COST = ?, CHARGE_FREQUENCY = ? WHERE ROLE_ID = ?",
[$caps['MAX_GROUPS_OWNED'], $caps['MAX_GROUP_MEMBERS'],
$caps['MAX_GROUP_WIKI_PAGES'],
$caps['MAX_PAGE_RESOURCE_MEMORY'],
$caps['MAX_GROUP_THREADS'], $caps['MAX_THREAD_RESOURCES'],
$caps['MAX_THREAD_POSTS'], $caps['ROLE_COST'],
$caps['CHARGE_FREQUENCY'], $role_id]);
} else {
$db->execute("INSERT INTO ROLE_LIMITS (ROLE_ID, " .
"MAX_GROUPS_OWNED, MAX_GROUP_MEMBERS, " .
"MAX_GROUP_WIKI_PAGES, MAX_PAGE_RESOURCE_MEMORY, " .
"MAX_GROUP_THREADS, MAX_THREAD_RESOURCES, " .
"MAX_THREAD_POSTS, ROLE_COST, CHARGE_FREQUENCY) " .
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
[$role_id, $caps['MAX_GROUPS_OWNED'],
$caps['MAX_GROUP_MEMBERS'], $caps['MAX_GROUP_WIKI_PAGES'],
$caps['MAX_PAGE_RESOURCE_MEMORY'],
$caps['MAX_GROUP_THREADS'], $caps['MAX_THREAD_RESOURCES'],
$caps['MAX_THREAD_POSTS'], $caps['ROLE_COST'],
$caps['CHARGE_FREQUENCY']]);
}
}
/**
* Resolves the effective group limits for a user account by taking, for
* each cap, the most generous value across the roles the account holds.
* Unlimited (-1) is the most generous, so one unlimited role lifts that
* cap. A role with no stored limits row counts as unlimited, and an
* account holding no roles is unlimited on every cap.
*
* @param int $user_id account whose effective caps are wanted
* @return array cap column name => effective value (-1 means unlimited)
*/
public function getUserGroupLimits($user_id)
{
$db = $this->db;
$columns = ['MAX_GROUPS_OWNED', 'MAX_GROUP_MEMBERS',
'MAX_GROUP_WIKI_PAGES', 'MAX_PAGE_RESOURCE_MEMORY',
'MAX_GROUP_THREADS', 'MAX_THREAD_RESOURCES',
'MAX_THREAD_POSTS'];
$sql = "SELECT RL.MAX_GROUPS_OWNED AS MAX_GROUPS_OWNED, " .
"RL.MAX_GROUP_MEMBERS AS MAX_GROUP_MEMBERS, " .
"RL.MAX_GROUP_WIKI_PAGES AS MAX_GROUP_WIKI_PAGES, " .
"RL.MAX_PAGE_RESOURCE_MEMORY AS MAX_PAGE_RESOURCE_MEMORY, " .
"RL.MAX_GROUP_THREADS AS MAX_GROUP_THREADS, " .
"RL.MAX_THREAD_RESOURCES AS MAX_THREAD_RESOURCES, " .
"RL.MAX_THREAD_POSTS AS MAX_THREAD_POSTS " .
"FROM USER_ROLE UR LEFT JOIN ROLE_LIMITS RL " .
"ON RL.ROLE_ID = UR.ROLE_ID WHERE UR.USER_ID = ?";
$result = $db->execute($sql, [$user_id]);
$gathered = [];
foreach ($columns as $column) {
$gathered[$column] = [];
}
while ($row = $db->fetchArray($result)) {
foreach ($columns as $column) {
$value = $row[$column];
if ($value === null) {
$value = -1;
}
$gathered[$column][] = (int)$value;
}
}
$effective = [];
foreach ($columns as $column) {
$values = $gathered[$column];
if ($values === [] || in_array(-1, $values, true)) {
$effective[$column] = -1;
} else {
$effective[$column] = max($values);
}
}
return $effective;
}
}
(c) 2026 Seekquarry - Open Source Search Engine Software