<?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 Mallika Perepa, Chris Pollett
* @license https://www.gnu.org/licenses/ GPL3
* @link https://www.seekquarry.com/
* @copyright 2009 - 2026
* @filesource
*/
namespace seekquarry\yioop\models;
use seekquarry\yioop as B;
use seekquarry\yioop\configs as C;
use seekquarry\yioop\library as L;
use seekquarry\yioop\library\av_processing\AudioConverter;
use seekquarry\yioop\library\av_processing\VideoExtractor;
use seekquarry\yioop\library\MediaConstants;
use seekquarry\yioop\library\version_control\VersionManager;
use seekquarry\yioop\library\wiki\WikiParser;
use seekquarry\yioop\library\processors\TextProcessor;
use seekquarry\yioop\library\processors\ImageProcessor;
use seekquarry\yioop\library\processors\EpubProcessor;
use seekquarry\yioop\library\processors\PdfProcessor;
use seekquarry\yioop\library\processors\VideoProcessor;
use seekquarry\yioop\models\ImpressionModel;
use seekquarry\yioop\library\wiki as LW;
/**
* GroupModel stores groups and their members in the database. A group
* is a set of users who share a feed and a set of wiki pages.
*
* It creates a group, deletes a group, and renames it. It adds a user
* to a group and removes one. It stores what a member may do in the
* group: read it, write to it, or moderate it. It stores whether a
* group keeps its contents encrypted, and it holds the key. It counts
* how many members have flagged a post, and says when that count is
* high enough to hide the post.
*
* Two models extend this one. WikiModel adds the wiki pages of a
* group. FeedModel adds the feed of a group.
*
* SocialComponent uses this model to draw the manage groups screen and
* to join and leave a group. AccountaccessComponent uses it to show
* which groups a user belongs to.
*
* @author Mallika Perepa (creator), Chris Pollett (rewrite)
*/
class GroupModel extends Model implements MediaConstants
{
/**
* getGroupPageResourcesFolders returns the folder and thumb folder
* associated with the resources of a wiki page. Also returns base folders
* of these which may be different if there is a sub_path.
* @param int $group_id group identifier of group wiki page belongs to
* @param int $page_id identifier for page want folder paths for
* @param string $sub_path file system path within the resource folder to
* get the folder name for
* @param bool $create if folder doesn't exist whether to create it or not
* @param bool $check_redirect whether to check the default group page
* folder for a redirect to a different folder
* @param bool $check_exists if true and $create false, then check if the
* file path exists and return false if it doesn't. If false, and prefix
* path doesn't exist, this flag will cause it to be created
* @return array (page_folder, thumb_folder, base_page_folder,
* base_thumb_folder)
*/
public function getGroupPageResourcesFolders($group_id, $page_id,
$sub_path = "", $create = false, $check_redirect = true,
$check_exists = true)
{
$redirect_filename = "redirect.txt";
$sub_path = str_replace("..", "", $sub_path);
$sub_path = str_replace("/./", "/", $sub_path);
$sub_path = ($sub_path == "/" || $sub_path == "") ? "" : "/$sub_path";
$group_page_folder = L\crawlHash(
"group" . $group_id . $page_id . C\p('AUTH_KEY'));
$old_thumb_page_folder = L\crawlHash(
"thumb" . $group_id . $page_id . C\p('AUTH_KEY'));
$group_prefix = substr($group_page_folder, 0, 3);
$old_thumb_prefix = substr($old_thumb_page_folder, 0, 3);
$resource_path = C\APP_DIR . "/resources";
$group_prefix_path = $resource_path . "/$group_prefix";
$old_thumb_prefix_path = $resource_path . "/$old_thumb_prefix";
$group_path = "$group_prefix_path/$group_page_folder";
$redirect_path = $group_path . "/$redirect_filename";
$thumb_path = "$group_prefix_path/t$group_page_folder";
$old_thumb_path = "$old_thumb_prefix_path/$old_thumb_page_folder";
$redirected = false;
if (!$check_exists || file_exists($group_path)) {
if (!$check_exists && !file_exists($group_prefix_path)) {
L\makePath($group_prefix_path);
}
if ($check_redirect && file_exists($redirect_path)) {
$tmp_path = trim(file_get_contents($redirect_path));
if (!is_dir($tmp_path) && $create &&
is_dir(dirname($tmp_path))) {
@mkdir($tmp_path);
}
if (!is_dir($tmp_path)) {
/* a resource path was set for this page but its folder
does not exist; when asked to create it could not be
made under an existing parent. Either way, report the
failure rather than silently falling back to the
page's default folder, which would hide the
misconfiguration and store resources where the
operator did not ask */
return false;
}
$group_path = $tmp_path;
$group_hash = L\crawlHash($group_path);
$group_prefix = substr($group_hash, 0, 3);
$group_prefix_path = $resource_path . "/$group_prefix";
$no_redirect_thumb_path = $thumb_path;
$thumb_path = "$group_prefix_path/t$group_hash";
if (!file_exists($group_prefix_path) &&
file_exists($group_path . $sub_path) && $create) {
L\makePath($group_prefix_path);
}
$redirected = true;
}
if (!$check_exists || (file_exists($group_path . $sub_path) &&
file_exists($thumb_path . $sub_path))) {
return [$group_path . $sub_path, $thumb_path . $sub_path,
$group_path, $thumb_path];
} else if (!$create) {
if (file_exists($group_path . $sub_path)) {
/* if path exists always let thumb path be created
as used for count files
*/
if (!file_exists($thumb_path)) {
L\makePath($thumb_path);
}
$thumb_path = file_exists($thumb_path) ? $thumb_path :
false;
return [$group_path . $sub_path, false,
$group_path, $thumb_path];
}
return false;
}
} elseif (!$create) {
return false;
}
/*
The naming convention for the thumb folder directory has evolved.
The current version allows one to compute the thumb folder
based on the group-page resource folder. It also satisfies
that if a two pages have redirects to the same resource folder
their thumb folders will be the same. The code below also
attempts to move pre-existing thumbs folders of old yioop
version to the new locations
*/
if (file_exists($group_path) || L\makePath($group_path)) {
if ($check_redirect && !$redirected &&
file_exists($group_path . "/$redirect_filename")) {
$tmp_path = trim(file_get_contents($group_path .
"/$redirect_filename"));
if (!is_dir($tmp_path) && $create &&
is_dir(dirname($tmp_path))) {
@mkdir($tmp_path);
}
if (!is_dir($tmp_path)) {
/* the resource path was set but cannot be reached and,
when asked, could not be created, so do not quietly
keep using the page's default folder */
return false;
}
$group_path = $tmp_path;
$group_hash = L\crawlHash($group_path);
$group_prefix = substr($group_hash, 0, 3);
$group_prefix_path = $resource_path."/$group_prefix";
$no_redirect_thumb_path = $thumb_path;
$thumb_path = "$group_prefix_path/t$group_hash";
if (!file_exists($group_prefix_path)) {
L\makePath($group_prefix_path);
}
}
if (!file_exists($thumb_path)) {
if (!empty($no_redirect_thumb_path) &&
file_exists($no_redirect_thumb_path)) {
rename($no_redirect_thumb_path, $thumb_path);
} else if (file_exists($old_thumb_path)) {
rename($old_thumb_path, $thumb_path);
}
}
$full_group_path = $group_path . $sub_path;
$full_thumb_path = $thumb_path . $sub_path;
if ((file_exists($full_group_path) || L\makePath($full_group_path))
&& (file_exists($full_thumb_path) ||
L\makePath($full_thumb_path))) {
return [$full_group_path, $full_thumb_path, $group_path,
$thumb_path];
}
}
return false;
}
/**
* getPageInfoByName return the page id, page string, and discussion thread
* id of the most recent revision of a wiki page
* @param int $group_id group identifier of group wiki page belongs to
* @param string $name title of wiki page to look up
* @param string $locale_tag IANA language tag of page to lookup
* @param string $mode if "edit" we assume we are looking up the page so
* that it can be edited and so we return the most recent non-parsed
* revision of the page. Otherwise, we assume the page is meant to be
* read and so we return the variant of the page where wiki markup has
* already been replaced with HTML
* @return array (page_id, page, discussion_id) of desired wiki page
*/
public function getPageInfoByName($group_id, $name, $locale_tag, $mode)
{
$db = $this->db;
if (in_array($mode, ['api', 'edit', 'source'])) {
$sql = "SELECT HP.PAGE_ID AS ID, HP.PAGE AS PAGE,
HP.EDIT_COMMENT AS EDIT_COMMENT, HP.PUBDATE AS PUBDATE,
GP.DISCUSS_THREAD AS DISCUSS_THREAD FROM GROUP_PAGE GP,
GROUP_PAGE_HISTORY HP WHERE GP.GROUP_ID = ?
AND GP.TITLE = ? AND GP.LOCALE_TAG = ? AND HP.PAGE_ID = GP.ID
ORDER BY HP.PUBDATE DESC " . $db->limitOffset(0, 1);
} else {
$sql = "SELECT ID, PAGE, DISCUSS_THREAD, LAST_MODIFIED FROM
GROUP_PAGE WHERE GROUP_ID = ? AND TITLE = ? AND LOCALE_TAG = ?";
}
$result = $db->execute($sql, [$group_id, $name, $locale_tag]);
if (!$result) {
return false;
}
$row = $db->fetchArray($result);
if (!$row) {
return false;
}
return $row;
}
/**
* NEEDS_THUMBS_DIR directory to tell WikiThumbDetailJob that a wiki
* resource needs a thumb
*/
const NEEDS_THUMBS_DIR = C\APP_DIR . "/resources/needs_thumbs";
/**
* RESOURCE_LIST_YIELD how many resources to process between cooperative
* yields while assembling a media list, so a large list does not freeze the
* single-process server. Small enough that each uninterrupted stretch stays
* brief, large enough that the yields add little overhead.
*/
const RESOURCE_LIST_YIELD = 64;
/**
* RECORDING_BITS_PER_SECOND how many bits a second a recording made over
* into mp4 is given. Enough for clear speech without making the file large.
*/
const RECORDING_BITS_PER_SECOND = "64k";
/**
* MAX_CALL_EVENT_TIME_TRIES how many times addCallEvent tries a
* later time when an insert is refused for sharing the microsecond
* that is half a call event's primary key. A handful is far more
* than two people in a call reach at once, and it bounds the loop
* so a run of collisions cannot hold a request open.
*/
const MAX_CALL_EVENT_TIME_TRIES = 8;
/**
* NEEDS_DESCRIPTION_FILE file to tell DescriptionUpdateJob that a wiki
* resource folder might have files that need descriptions
*/
const NEEDS_DESCRIPTION_FILE =
C\APP_DIR . "/resources/needs_descriptions.txt";
/**
* search_table_column_map stores associations of the form name of field for
* web forms => database column names/abbreviations In this case, things
* will in general map to the SOCIAL_GROUPS, o USER_GROUP or GROUP_ITEM
* tables in the Yioop database
* @var array
*/
public $search_table_column_map = ["access" => "G.MEMBER_ACCESS",
"created_time" => "G.CREATED_TIME",
"group_id" => "G.GROUP_ID", "post_id" => "GI.ID",
"join_date" => "UG.JOIN_DATE",
"name" => "G.GROUP_NAME", "owner" => "O.USER_NAME",
"pub_date" => "GI.PUBDATE", "parent_id"=>"GI.PARENT_ID",
"register" => "G.REGISTER_TYPE", "status"=>"UG.STATUS",
"user_id"=>"O.USER_ID", "voting" => "G.VOTE_ACCESS",
"lifetime" => "G.POST_LIFETIME",
"key" => "G.GROUP_ID"];
/**
* any_fields stores these fields if present in $search_array (used by @see
* getRows() ), but with value "-1", will be skipped as part of the where
* clause but will be used for order by clause
* @var array
*/
public $any_fields = ["access", "register", "voting", "lifetime"];
/**
* selectCallback used to determine the select clause for SOCIAL_GROUPS
* table when do query to marshal group objects for the controller mainly in
* mangeGroups
* @param mixed $args We use $args[1] to say whether in browse mode or not.
* browse mode is for groups a user could join rather than ones already
* joined
* @return string SQL select-clause text used by the SOCIAL_GROUPS query —
* either the literal "*" when no args supplied, or a comma-separated
* column list adapted for browse vs membership mode
*/
public function selectCallback($args = null)
{
if (!is_array($args) || count($args) < 2) {
return "*";
}
list($user_id, $browse, ) = $args;
if ($browse) {
$join_date = "";
$status = "";
} else {
$join_date = ", UG.JOIN_DATE AS JOIN_DATE";
$status = " UG.STATUS AS STATUS,";
}
$select = "DISTINCT G.GROUP_ID AS GROUP_ID,
G.GROUP_NAME AS GROUP_NAME, G.OWNER_ID AS OWNER_ID,
O.USER_NAME AS OWNER, REGISTER_TYPE, $status
G.MEMBER_ACCESS, VOTE_ACCESS, POST_LIFETIME $join_date,
G.CREATED_TIME GROUP_CREATION_DATE";
return $select;
}
/**
* fromCallback {@inheritDoc}
* @param mixed $args any additional arguments which should be used to
* determine these tables (in this case none)
*/
public function fromCallback($args = null)
{
return "SOCIAL_GROUPS G, USER_GROUP UG, USERS O";
}
/**
* whereCallback used to restrict getRows in which rows it returns. Rows in
* this case corresponding to Yioop groups. The restrictions added are to
* restrict to those group available to a given user_id and whether or not
* the user wants groups subscribed to, or groups that could be subscribed
* to
* @param array $args first two elements are the $user_id of the user and
* the $browse flag which says whether or not user is browsing through
* all groups to which he could subscribe and read or just those groups
* to which he is already subscribed.
* @return string a SQL WHERE clause suitable to perform the above
* restrictions
*/
public function whereCallback($args = null)
{
$db = $this->db;
if (!is_array($args) || count($args) < 2) {
return "";
}
list($user_id, $browse, ) = $args;
if ($browse) {
$where =
" UG.GROUP_ID=G.GROUP_ID AND G.OWNER_ID=O.USER_ID AND NOT ".
" EXISTS (SELECT * FROM USER_GROUP UG2 WHERE UG2.USER_ID = ".
$db->escapeString($user_id)." AND UG2.GROUP_ID = G.GROUP_ID ".
" AND (UG2.STATUS = " . C\ACTIVE_STATUS . " OR UG2.STATUS = ".
C\SUSPENDED_STATUS . " ))";
} else {
$where = " UG.USER_ID='".$db->escapeString($user_id).
"' AND UG.GROUP_ID=G.GROUP_ID AND G.OWNER_ID=O.USER_ID";
}
$where .= " AND G.GROUP_NAME NOT LIKE '" . C\PERSONAL_GROUP_PREFIX .
"%' ";
return $where;
}
/**
* getGroupUsers get an array of users that belong to a group
* @param string $group_id the group_id to get users for
* @param string $filter to LIKE filter users
* @param array $sorts directions on how to sort the columns of the results
* format is column_name => direction
* @param int $limit first user to get
* @param int $num number of users to return
* @param bool $subscribed_only when true, only members who still have this
* group's mail switched on (RECEIVE_MAIL) are returned; used when
* choosing recipients for the group's bulk mail
* @return array of USERS rows
*/
public function getGroupUsers($group_id, $filter = "", $sorts = [],
$limit = "", $num = C\NUM_RESULTS_PER_PAGE,
$subscribed_only = false)
{
$db = $this->db;
if ($limit !== "") {
$limit = $db->limitOffset($limit, $num);
}
$like = "";
$param_array = [$group_id];
if ($filter != "") {
$like = "AND U.USER_NAME LIKE ?";
$param_array[] = "%" . $filter . "%";
}
$order_by = "";
if (!empty($sorts)) {
$sort_fields = ["JOIN_DATE", "STATUS", "USER_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";
}
}
}
$users = [];
$mail_filter = $subscribed_only ? " AND UG.RECEIVE_MAIL <> 0 " : "";
$sql = "SELECT UG.USER_ID, U.USER_NAME AS USER_NAME,".
" UG.GROUP_ID, G.OWNER_ID, U.EMAIL, UG.STATUS AS STATUS,".
" UG.JOIN_DATE AS JOIN_DATE".
" FROM USER_GROUP UG, USERS U, SOCIAL_GROUPS G".
" WHERE UG.GROUP_ID = ? AND UG.USER_ID = U.USER_ID AND" .
" G.GROUP_ID = UG.GROUP_ID $mail_filter $like $order_by $limit";
$result = $db->execute($sql, $param_array);
$i = 0;
while ($users[$i] = $db->fetchArray($result)) {
$i++;
}
unset($users[$i]); /* last one will be null */
return $users;
}
/**
* countGroupUsers get the number of users which belong to a group and whose
* user_name matches a filter
* @param int $group_id id of the group to get a count of
* @param string $filter to filter usernames by
* @return int count of matching users
*/
public function countGroupUsers($group_id, $filter="")
{
$db = $this->db;
$users = [];
$like = "";
$users = "";
$param_array = [$group_id];
if ($filter != "") {
$like = "AND UG.USER_ID = U.USER_ID AND U.USER_NAME LIKE ?";
$users = ", USERS U";
$param_array[] = "%" . $filter . "%";
}
$sql = "SELECT COUNT(DISTINCT UG.USER_ID) AS NUM ".
" FROM USER_GROUP UG $users".
" WHERE UG.GROUP_ID = ? $like";
$result = $db->execute($sql, $param_array);
if ($result) {
$row = $db->fetchArray($result);
}
return $row['NUM'];
}
/**
* countGroupsOwnedByUser returns how many groups a given user owns.
* @param int $user_id user to count owned groups for
* @return int number of groups whose owner is $user_id
*/
public function countGroupsOwnedByUser($user_id)
{
$db = $this->db;
$sql = "SELECT COUNT(*) AS NUM FROM SOCIAL_GROUPS " .
"WHERE OWNER_ID = ?";
$result = $db->execute($sql, [$user_id]);
$row = ($result) ? $db->fetchArray($result) : ['NUM' => 0];
return $row['NUM'];
}
/**
* addGroup add a group name to the database using provided string
* @param string $group_name the group name to be added
* @param int $user_id user identifier of who owns the group
* @param int $register flag that says what kinds of registration are
* allowed for this group INVITE_ONLY_JOIN, REQUEST_JOIN, PUBLIC_JOIN,
* or some group fee amount in credits 100, 200, 500, 1000, 2000
* @param int $member flag that says how members other than the owner can
* access this group GROUP_READ, GROUP_READ_COMMENT (can comment on
* threads but not start. i.e., a blog), GROUP_READ_WRITE, (can read,
* comment, start threads), GROUP_READ_WIKI, (can read, comment, start
* threads, and edit the wiki)
* @param int $voting flag that says how members can vote on each others
* posts: NON_VOTING_GROUP, UP_VOTING_GROUP, UP_DOWN_VOTING_GROUP
* @param int $post_lifetime specifies the time in seconds that posts should
* live before they expire and are deleted
* @param int $encryption 0 means don't encrypt group, 1 means encrypt group
* @param int $render_engine what parser to use when drawing wiki pages or
* posts (mediawiki == C\MEDIAWIKI_ENGINE , markdown ==
* C\MARKDOWN_ENGINE)
* @param bool $skip_impression whether to skip impression logging or not
* @param int $input_timestamp optional UNIX timestamp to use for impression
* tracking; if set to -1 or omitted, the current time will be used
* @return int id of group added
*/
public function addGroup($group_name, $user_id,
$register = C\REQUEST_JOIN, $member = C\GROUP_READ,
$voting = C\NON_VOTING_GROUP, $post_lifetime = C\FOREVER,
$encryption = 0, $render_engine = C\MEDIAWIKI_ENGINE,
$skip_impression = false, $input_timestamp = -1)
{
$db = $this->db;
$private_db = $this->private_db;
$timestamp = L\microTimestamp();
$options = ($encryption ? C\GROUP_OPTION_ENCRYPTED : 0) |
C\GROUP_OPTION_PAGE_SOURCE_ALLOWED |
C\GROUP_OPTION_PAGE_LIST_ALLOWED |
C\GROUP_OPTION_PAGE_CUSTOMIZE_ALLOWED;
$sql = "INSERT INTO SOCIAL_GROUPS (GROUP_NAME, CREATED_TIME, OWNER_ID,
REGISTER_TYPE, MEMBER_ACCESS, VOTE_ACCESS, POST_LIFETIME,
OPTIONS, RENDER_ENGINE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
$db->execute($sql, [$group_name, $timestamp, $user_id,
$register, $member, $voting, $post_lifetime, $options,
$render_engine]);
$sql = "SELECT G.GROUP_ID AS GROUP_ID FROM ".
" SOCIAL_GROUPS G WHERE G.GROUP_NAME = ?";
$result = $db->execute($sql, [$group_name]);
if (!$row = $db->fetchArray($result)) {
$last_id = -1;
}
$last_id = $row['GROUP_ID'];
$now = time();
$sql= "INSERT INTO USER_GROUP (USER_ID, GROUP_ID, STATUS,
JOIN_DATE) VALUES
($user_id, $last_id, " . C\ACTIVE_STATUS . ", $now)";
$db->execute($sql);
if ($skip_impression === false) {
ImpressionModel::initWithDb($user_id, $last_id, C\GROUP_IMPRESSION,
$db, $input_timestamp);
ImpressionModel::initWithDb(C\PUBLIC_GROUP_ID, $last_id,
C\GROUP_IMPRESSION, $db, $input_timestamp);
}
if ($encryption != 0) {
$sql = "INSERT INTO TYPE_KEYS (TYPE_ID, KEY_NAME) VALUES (?, ?)";
/* AES 256 is 32 bytes long (8*32 bits) */
$encrypt_key = base64_encode(openssl_random_pseudo_bytes(32));
$private_db->execute($sql, [$last_id, $encrypt_key]);
}
return $last_id;
}
/**
* updateGroup takes the passed associated array $group representing changes
* fields of a SOCIAL_GROUPS row, and executes an UPDATE statement to
* persist those changes fields to the database.
* @param array $group associative array with a GROUP_ID as well as the
* fields to update
*/
public function updateGroup($group)
{
$db = $this->db;
$group_id = $group['GROUP_ID'];
unset($group['GROUP_ID']);
unset($group['GROUP_NAME']);
unset($group['OWNER']); /* column not in table */
unset($group['STATUS']); /* column not in table */
unset($group['JOIN_DATE']); /* column not in table */
$group['OPTIONS'] =
(!empty($group['ENCRYPTION']) ?
C\GROUP_OPTION_ENCRYPTED : 0) |
(!empty($group['PAGE_SOURCE_ALLOWED']) ?
C\GROUP_OPTION_PAGE_SOURCE_ALLOWED : 0) |
(($group['PAGE_LIST_ALLOWED'] ?? 0) == 1 ?
C\GROUP_OPTION_PAGE_LIST_ALLOWED : 0) |
(($group['PAGE_LIST_ALLOWED'] ?? 0) ==
C\GROUP_OPTION_PAGE_LIST_ARTICLES_SETTING ?
C\GROUP_OPTION_PAGE_LIST_ARTICLES : 0) |
(!empty($group['PAGE_CUSTOMIZE_ALLOWED']) ?
C\GROUP_OPTION_PAGE_CUSTOMIZE_ALLOWED : 0);
unset($group['ENCRYPTION']);
unset($group['PAGE_SOURCE_ALLOWED']);
unset($group['PAGE_LIST_ALLOWED']);
unset($group['PAGE_CUSTOMIZE_ALLOWED']);
$sql = "UPDATE SOCIAL_GROUPS SET ";
$comma ="";
$params = [];
foreach ($group as $field => $value) {
$sql .= "$comma $field=? ";
$comma = ",";
$params[] = $value;
}
$sql .= " WHERE GROUP_ID=?";
$params[] = $group_id;
$db->execute($sql, $params);
}
/**
* checkUserGroup check is a user given by $user_id belongs to a group given
* by $group_id. If the field $status is sent then check if belongs to the
* group with $status access (active, editor, invited, request, banned)
* @param int $user_id user to look up
* @param int $group_id group to check if member of
* @param int $status membership type
* @return int whether or not is a member
*/
public function checkUserGroup($user_id, $group_id, $status = -1)
{
$db = $this->db;
if (intval($user_id) != $user_id) {
return C\NOT_MEMBER_STATUS;
}
$params = [$user_id, $group_id];
$sql = "SELECT STATUS FROM USER_GROUP WHERE
USER_ID=? AND GROUP_ID=?";
if ($status >=0) {
$sql .= " AND STATUS=?";
$params[] = $status;
}
$result = $db->execute($sql, $params);
if (!$row = $db->fetchArray($result)) {
return false;
}
return $row['STATUS'];
}
/**
* getMailSubscription looks up whether one user currently wants to receive
* a given group's mail. Returns null when the user is not a member of the
* group, so a caller can tell a member who opted out apart from someone who
* is not in the group at all.
* @param int $user_id the user to look up
* @param int $group_id the group to look up
* @return mixed 1 if the user receives the group's mail, 0 if they have
* turned it off, or null if the user is not a member
*/
public function getMailSubscription($user_id, $group_id)
{
$db = $this->db;
$sql = "SELECT RECEIVE_MAIL FROM USER_GROUP " .
"WHERE USER_ID = ? AND GROUP_ID = ?";
$result = $db->execute($sql, [$user_id, $group_id]);
if (!$result || !($row = $db->fetchArray($result))) {
return null;
}
return intval($row['RECEIVE_MAIL']);
}
/**
* setMailSubscription turns one user's mail from a given group on or off by
* setting the RECEIVE_MAIL flag on their membership row. Does nothing when
* the user is not a member of the group, since there is no membership row
* to change.
* @param int $user_id the user whose preference is changing
* @param int $group_id the group the preference is for
* @param int $receive 1 to receive the group's mail, 0 to stop it
*/
public function setMailSubscription($user_id, $group_id, $receive)
{
$db = $this->db;
$receive = $receive ? 1 : 0;
$sql = "UPDATE USER_GROUP SET RECEIVE_MAIL = ? " .
"WHERE USER_ID = ? AND GROUP_ID = ?";
$db->execute($sql, [$receive, $user_id, $group_id]);
}
/**
* updateStatusUserGroup change the status of a user in a group
* @param int $user_id of user to change
* @param int $group_id of group to change status for
* @param int $status what the new status should be
*/
public function updateStatusUserGroup($user_id, $group_id, $status)
{
$db = $this->db;
$sql = "UPDATE USER_GROUP SET STATUS=? WHERE
GROUP_ID=? AND USER_ID=?";
$db->execute($sql, [$status, $group_id, $user_id]);
}
/**
* groupsStartingWith gives the groups whose name begins with the
* letters somebody has typed, in alphabetical order. The manage
* users screen offers group names as a person types, and this is
* where those names come from. A name is matched from its start
* rather than anywhere in it, so what is offered extends what was
* typed, and upper and lower case are treated alike. The groups
* standing for one person's own messages are left out, since nobody
* joins those by name.
*
* @param string $beginning the letters typed so far, already trimmed
* @param int $how_many the most names to give back
* @return array rows holding GROUP_ID and GROUP_NAME, alphabetical
* by name, empty where nothing begins with those letters
*/
public function groupsStartingWith($beginning, $how_many)
{
$db = $this->db;
$beginning = trim((string)$beginning);
if ($beginning === "") {
return [];
}
/* Anything in the letters that the pattern language reads as a
wildcard is made ordinary, so a typed underscore looks for
itself rather than for any one letter. */
$pattern = str_replace(["\\", "%", "_"],
["\\\\", "\\%", "\\_"], $beginning) . "%";
$personal = C\PERSONAL_GROUP_PREFIX . "%";
$sql = "SELECT GROUP_ID, GROUP_NAME FROM SOCIAL_GROUPS " .
"WHERE LOWER(GROUP_NAME) LIKE LOWER(?) " .
"AND GROUP_NAME NOT LIKE ? ORDER BY GROUP_NAME " .
$db->limitOffset(0, $how_many);
$result = $db->execute($sql, [$pattern, $personal]);
if (!$result) {
return [];
}
$found = [];
while ($row = $db->fetchArray($result)) {
$found[] = $row;
}
return $found;
}
/**
* getGroupId gives the number of the group that answers to a name.
* Group names are meant to be unique, so at most one group can answer
* to any name.
*
* Where no group answers to the name this gives -1 rather than
* nothing, so a caller must compare the result against zero. Reading
* it as a yes or no takes -1 for a group that exists, and a caller
* that did so wrote a membership row pointing at no group at all.
*
* @param string $group_name name to look a group up by
* @return int the group's number, or -1 where no group answers to
* that name
*/
public function getGroupId($group_name)
{
$db = $this->db;
$sql = "SELECT G.GROUP_ID AS GROUP_ID FROM ".
"SOCIAL_GROUPS G WHERE G.GROUP_NAME = ? ";
$result = $db->execute($sql, [$group_name]);
if (!$row = $db->fetchArray($result)) {
return -1;
}
return $row['GROUP_ID'];
}
/**
* getDomainRoutes returns the per-domain landing routes: which social
* group's Main wiki page each custom-routed secure domain serves as its
* landing page. Domains without a row use the site default landing behavior
* and do not appear in the returned array.
* @return array associative array of lowercased domain => name of the group
* that domain serves
*/
public function getDomainRoutes()
{
$db = $this->db;
$routes = [];
$sql = "SELECT DR.DOMAIN AS DOMAIN, G.GROUP_NAME AS GROUP_NAME " .
"FROM DOMAIN_ROUTE DR, SOCIAL_GROUPS G " .
"WHERE DR.GROUP_ID = G.GROUP_ID";
$result = $db->execute($sql);
if (!$result) {
return $routes;
}
while ($row = $db->fetchArray($result)) {
$routes[$row['DOMAIN']] = $row['GROUP_NAME'];
}
return $routes;
}
/**
* setDomainRoute sets the landing route for a secure domain: requests for
* that domain with no controller selected serve the Main wiki page of the
* given group. Replaces any previous route for the domain.
* @param string $domain lowercased domain name to route
* @param int $group_id id of the group whose Main page to serve
*/
public function setDomainRoute($domain, $group_id)
{
$db = $this->db;
$db->execute("DELETE FROM DOMAIN_ROUTE WHERE DOMAIN = ?",
[$domain]);
$db->execute("INSERT INTO DOMAIN_ROUTE (DOMAIN, GROUP_ID) " .
"VALUES (?, ?)", [$domain, $group_id]);
}
/**
* deleteDomainRoute removes any custom landing route for a secure domain,
* returning it to the site default landing behavior.
* @param string $domain lowercased domain name to unroute
*/
public function deleteDomainRoute($domain)
{
$this->db->execute("DELETE FROM DOMAIN_ROUTE WHERE DOMAIN = ?",
[$domain]);
}
/**
* getDomainRouteGroupId looks up the group whose Main wiki page the given
* domain serves as its landing page, if a custom route has been set for it.
* @param string $domain lowercased domain name to look up
* @return int group id the domain routes to, or 0 when the domain has no
* custom route (or the route table does not exist yet)
*/
public function getDomainRouteGroupId($domain)
{
$db = $this->db;
$sql = "SELECT GROUP_ID FROM DOMAIN_ROUTE WHERE DOMAIN = ?";
$result = $db->execute($sql, [$domain]);
if (!$result || !($row = $db->fetchArray($result))) {
return 0;
}
return intval($row['GROUP_ID']);
}
/**
* deleteDomainAppearance removes any per-domain appearance overrides for a
* secure domain, returning it to the global Appearance settings.
* @param string $domain lowercased domain name to clear appearance for
*/
public function deleteDomainAppearance($domain)
{
$this->db->execute(
"DELETE FROM DOMAIN_APPEARANCE WHERE DOMAIN = ?",
[$domain]);
}
/**
* getDomainAppearance returns the per-domain appearance overrides for one
* secure domain. The stored value is a serialized associative array of
* appearance fields (colors, logos, favicon, background image, auxiliary
* CSS name and contents, landing page, searchbar path).
* @param string $domain lowercased domain name to look up
* @return array appearance field => value overrides for the domain, or an
* empty array when the domain has no saved appearance (or the table
* does not exist yet)
*/
public function getDomainAppearance($domain)
{
$db = $this->db;
$sql = "SELECT APPEARANCE FROM DOMAIN_APPEARANCE " .
"WHERE DOMAIN = ?";
$result = $db->execute($sql, [$domain]);
if (!$result || !($row = $db->fetchArray($result))) {
return [];
}
$appearance = unserialize($row['APPEARANCE']);
return is_array($appearance) ? $appearance : [];
}
/**
* setDomainAppearance saves the per-domain appearance overrides for one
* secure domain, replacing any previous overrides for that domain. The
* appearance array is serialized into a single row.
* @param string $domain lowercased domain name to store appearance for
* @param array $appearance appearance field => value overrides to store for
* the domain
*/
public function setDomainAppearance($domain, $appearance)
{
$db = $this->db;
$db->execute("DELETE FROM DOMAIN_APPEARANCE WHERE DOMAIN = ?",
[$domain]);
$db->execute("INSERT INTO DOMAIN_APPEARANCE (DOMAIN, " .
"APPEARANCE) VALUES (?, ?)",
[$domain, serialize($appearance)]);
}
/**
* getPersonalGroupId get group id of personal group of a User.
* @param string $user_id to use to look up a group_id
* @return int group_id of personal group of that user
*/
public function getPersonalGroupId($user_id)
{
return $this->getGroupId(C\PERSONAL_GROUP_PREFIX . $user_id);
}
/**
* getGroupName get group id associated with group name (so group names
* better
* be unique)
* @param int $group_id to use to look up a group name
* @return string group_name corresponding to the id.
*/
public function getGroupName($group_id)
{
$db = $this->db;
$sql = "SELECT G.GROUP_NAME AS GROUP_NAME FROM ".
"SOCIAL_GROUPS G WHERE G.GROUP_ID = ? ";
$result = $db->execute($sql, [$group_id]);
if (!$row = $db->fetchArray($result)) {
return false;
}
return $row['GROUP_NAME'];
}
/**
* isGroupEncrypted check whether group's encryption is enabled or not
* @param int $group_id to check for encryption value
* @return boolean whether the group is encrypted or not
*/
public function isGroupEncrypted($group_id)
{
$db = $this->db;
$sql = "SELECT OPTIONS FROM SOCIAL_GROUPS WHERE GROUP_ID = ? ";
$result = $db->execute($sql, [$group_id]);
if (!$row = $db->fetchArray($result)) {
return false;
}
$encryption =
(intval($row['OPTIONS']) & C\GROUP_OPTION_ENCRYPTED);
return ($encryption != 0);
}
/**
* getRenderEngine return the engine code to be used by the group's wiki
* parser 0 == mediawiki, 1 == markdown
* @param int $group_id to check for render_engine code of
* @return int code for parser want
*/
public function getRenderEngine($group_id)
{
$db = $this->db;
$sql = "SELECT RENDER_ENGINE FROM SOCIAL_GROUPS WHERE GROUP_ID = ? ";
$result = $db->execute($sql, [$group_id]);
if (!$row = $db->fetchArray($result)) {
return 0;
}
$render_code = intval($row['RENDER_ENGINE']);
return $render_code;
}
/**
* deleteGroup delete a group from the database and any associated data in
* GROUP_ITEM and USER_GROUP tables.
* @param string $group_id id of the group to delete
*/
public function deleteGroup($group_id)
{
$db = $this->db;
$private_db = $this->private_db;
$params = [$group_id];
$sql = "DELETE FROM SOCIAL_GROUPS WHERE GROUP_ID=?";
$db->execute($sql, $params);
$sql = "DELETE FROM GROUP_ITEM WHERE GROUP_ID=?";
$db->execute($sql, $params);
$sql = "SELECT ID FROM GROUP_PAGE WHERE GROUP_ID=?";
$result = $db->execute($sql, $params);
if ($result) {
while ($row = $db->fetchArray($result)) {
list($folder, $thumb_folder, ) =
$this->getGroupPageResourcesFolders($group_id,
$row['ID'], "", false, false);
if (file_exists($folder)) {
$db->unlinkRecursive($folder);
}
if (file_exists($thumb_folder)) {
$db->unlinkRecursive($thumb_folder);
}
}
}
$sql = "DELETE FROM GROUP_PAGE WHERE GROUP_ID=?";
$db->execute($sql, $params);
$sql = "DELETE FROM GROUP_PAGE_HISTORY WHERE GROUP_ID=?";
$db->execute($sql, $params);
$sql = "DELETE FROM TYPE_KEYS WHERE TYPE_ID=?";
$private_db->execute($sql, $params);
}
/**
* getRegisterType return the type of the registration for a group given by
* $group_id This says who is allowed to register for the group (i.e., is it
* by invitation only, by request, or anyone can join)
* @param int $group_id which group to find the type of
* @return int the numeric code for the registration type
*/
public function getRegisterType($group_id)
{
$db = $this->db;
$groups = [];
$sql = "SELECT REGISTER_TYPE FROM SOCIAL_GROUPS G WHERE GROUP_ID=?";
$result = $db->execute($sql, [$group_id]);
if (!$result) {
return false;
}
$row = $db->fetchArray($result);
if (!$row) {
return false;
}
return $row['REGISTER_TYPE'];
}
/**
* getGroupById returns information about the group with id $group_id
* provided that the requesting user $user_id has access to it
* @param int $group_id id of group to look up
* @param int $user_id user asking for group info
* @param bool $require_root_or_member require the $user_id to be in the
* group or root
* @return array row from group table or false (if no access or doesn't
* exists)
*/
public function getGroupById($group_id, $user_id,
$require_root_or_member = false)
{
$db = $this->db;
if (empty($group_id)) {
return false;
}
/* Postgres strict about types so being safe */
if (!is_numeric($user_id)) {
$user_id = C\PUBLIC_USER_ID;
}
$where = " WHERE ";
$params = [":group_id" => $group_id];
if ($user_id != C\ROOT_ID) {
if ($require_root_or_member) {
$where .= " (UG.USER_ID = :user_id) AND ";
} else {
$where .= " (UG.USER_ID = :user_id OR G.REGISTER_TYPE IN (".
C\PUBLIC_BROWSE_REQUEST_JOIN . ",". C\PUBLIC_JOIN .
")) AND ";
}
$params[":user_id"] = $user_id;
}
$where .= " UG.GROUP_ID= :group_id".
" AND UG.GROUP_ID=G.GROUP_ID AND OWNER_ID = O.USER_ID";
$sql = "SELECT G.GROUP_ID AS GROUP_ID,
G.GROUP_NAME AS GROUP_NAME,
G.OWNER_ID AS OWNER_ID, O.USER_NAME AS OWNER, REGISTER_TYPE,
UG.STATUS AS STATUS, G.MEMBER_ACCESS AS MEMBER_ACCESS,
G.VOTE_ACCESS AS VOTE_ACCESS, G.POST_LIFETIME AS POST_LIFETIME,
UG.JOIN_DATE AS JOIN_DATE, G.OPTIONS AS OPTIONS,
G.RENDER_ENGINE AS RENDER_ENGINE,
G.GROUP_THEME AS GROUP_THEME, G.PAGE_HEADER AS PAGE_HEADER,
G.PAGE_FOOTER AS PAGE_FOOTER
FROM SOCIAL_GROUPS G, USERS O, USER_GROUP UG $where " .
$db->limitOffset(1);
$result = $db->execute($sql, $params);
$group = false;
if ($result) {
$group = $db->fetchArray($result);
if (!$group) {
return false;
}
$group['ENCRYPTION'] =
(intval($group['OPTIONS']) & C\GROUP_OPTION_ENCRYPTED)
? 1 : 0;
$group['PAGE_SOURCE_ALLOWED'] = (intval($group['OPTIONS']) &
C\GROUP_OPTION_PAGE_SOURCE_ALLOWED) ? 1 : 0;
/* The list is open to all, open only where the address names
a category, or closed, which two bits say between them. */
$group['PAGE_LIST_ALLOWED'] = 0;
if (intval($group['OPTIONS']) &
C\GROUP_OPTION_PAGE_LIST_ALLOWED) {
$group['PAGE_LIST_ALLOWED'] = 1;
} else if (intval($group['OPTIONS']) &
C\GROUP_OPTION_PAGE_LIST_ARTICLES) {
$group['PAGE_LIST_ALLOWED'] =
C\GROUP_OPTION_PAGE_LIST_ARTICLES_SETTING;
}
$group['PAGE_CUSTOMIZE_ALLOWED'] = (intval($group['OPTIONS']) &
C\GROUP_OPTION_PAGE_CUSTOMIZE_ALLOWED) ? 1 : 0;
if (!$require_root_or_member) {
$sql = "SELECT STATUS FROM USER_GROUP WHERE
USER_ID=? AND GROUP_ID=? ".$db->limitOffset(1);
$params = [$user_id, $group_id];
$result = $db->execute($sql, $params);
if ($result) {
$row = $db->fetchArray($result);
if ($row) {
$group['STATUS'] = $row['STATUS'];
} else {
$group['STATUS'] = C\NOT_MEMBER_STATUS;
}
} else {
$group['STATUS'] = C\NOT_MEMBER_STATUS;
}
}
}
if (!$group) {
return false;
}
return $group;
}
/**
* getUserGroups get a list of all groups which user_id belongs to. Group
* names are not localized since these are created by end user admins of the
* search engine
* @param int $user_id to get groups for
* @param string $filter to LIKE filter groups
* @param array $sorts directions on how to sort the columns of the results
* format is column_name => direction
* @param int $limit first user to get
* @param int $num number of users to return
* @param bool $include_personal whether to include $user_id's personal
* group
* @param array $allowed_statuses a list of group membership statuses that
* to restrict the returned list to
* @return array an array of group_id, group_name pairs
*/
public function getUserGroups($user_id, $filter, $sorts, $limit,
$num = C\NUM_RESULTS_PER_PAGE, $include_personal = false,
$allowed_statuses = [C\ACTIVE_STATUS, C\EDITOR_STATUS])
{
$db = $this->db;
$groups = [];
$like = "";
$param_array = [$user_id];
$exclude_personal_group = ($include_personal) ? " " :
" AND G.GROUP_NAME NOT LIKE '%" . C\PERSONAL_GROUP_PREFIX . "%'";
$status_where = "";
$status_delim = " AND (";
foreach ($allowed_statuses as $allowed_status) {
$status_where .= $status_delim . " UG.STATUS = $allowed_status";
$status_delim = " OR ";
}
if (!empty($status_where)) {
$status_where .= ") ";
}
if ($filter != "") {
$like = "AND G.GROUP_NAME LIKE ?";
$param_array[] = "%".$filter."%";
}
$order_by = "";
if (!empty($sorts)) {
$sort_fields = ["GROUP_NAME", "STATUS"];
$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";
}
}
}
$limit = $db->limitOffset($limit, $num);
$sql = "SELECT UG.GROUP_ID AS GROUP_ID, UG.USER_ID AS USER_ID," .
" G.GROUP_NAME AS GROUP_NAME, UG.STATUS AS STATUS, ".
" G.OWNER_ID " .
" FROM USER_GROUP UG, SOCIAL_GROUPS G" .
" WHERE USER_ID = ? AND UG.GROUP_ID = G.GROUP_ID $status_where" .
" $exclude_personal_group $like $order_by $limit";
$result = $db->execute($sql, $param_array);
$groups = [];
while ($group = $db->fetchArray($result)) {
$groups[] = $group;
}
return $groups;
}
/**
* getGroupUserIds get user id's of all members of a group determined by a
* group_id
* @param int $group_id id of group to get members for
* @return array of the group's user ids
*/
public function getGroupUserIds($group_id)
{
$db = $this->db;
$sql = "SELECT USER_ID FROM USER_GROUP WHERE GROUP_ID = ?";
$result = $db->execute($sql, [$group_id]);
if (empty($result)) {
return false;
}
$user_ids = [];
while ($row = $db->fetchArray($result)) {
if (!empty($row['USER_ID'])) {
$user_ids[] = $row['USER_ID'];
}
}
return $user_ids;
}
/**
* isUserIdInContacts checks is a given $user_id is in the list of users of
* the personcal group of a $contact_user_id
* @param int $user_id user id to check if in other personal group
* @param int $contact_user_id user id to check personal group of
* @return bool whether $user_id was in list of contacts or not
*/
public function isUserIdInContacts($user_id, $contact_user_id)
{
$db = $this->db;
$sql = "SELECT UG.STATUS FROM USER_GROUP UG, ".
"SOCIAL_GROUPS G WHERE UG.USER_ID = ? AND " .
"G.GROUP_NAME = ? AND UG.GROUP_ID = G.GROUP_ID";
$contact_group = C\PERSONAL_GROUP_PREFIX . $contact_user_id;
$result = $db->execute($sql, [$user_id, $contact_group]);
if (empty($result) || empty($db->fetchArray($result))) {
return false;
}
return true;
}
/**
* countUserGroups get a count of the number of groups to which user_id
* belongs.
* @param int $user_id to get groups for
* @param string $filter to LIKE filter groups
* @param bool $include_personal whether to include $user_id's personal
* group
* @param array $allowed_statuses a list of group membership statuses that
* to restrict the returned list to
* @return int number of groups of the filtered type for the user
*/
public function countUserGroups($user_id, $filter = "",
$include_personal = false, $allowed_statuses = [C\ACTIVE_STATUS,
C\EDITOR_STATUS])
{
$db = $this->db;
$users = [];
$like = "";
$param_array = [$user_id];
if ($filter != "") {
$like = "AND G.GROUP_NAME LIKE ?";
$param_array[] = "%".$filter."%";
}
$status_where = "";
$status_delim = " AND (";
foreach ($allowed_statuses as $allowed_status) {
$status_where .= $status_delim . " UG.STATUS = $allowed_status";
$status_delim = " OR ";
}
if (!empty($status_where)) {
$status_where .= ") ";
}
$sql = "SELECT COUNT(DISTINCT G.GROUP_ID) AS NUM ".
" FROM USER_GROUP UG, SOCIAL_GROUPS G".
" WHERE UG.USER_ID = ? AND UG.GROUP_ID = G.GROUP_ID $status_where".
" $like";
$result = $db->execute($sql, $param_array);
if ($result) {
$row = $db->fetchArray($result);
}
$subtract_personal = ($include_personal) ? 0 : -1;
return ($row['NUM'] ?? 0) + $subtract_personal;
}
/**
* getGroupKey get the key of a private/encrypted group.
* @param int $group_id to get key for
* @return string key of the group
*/
public function getGroupKey($group_id)
{
$private_db = $this->private_db;
$sql = "SELECT KEY_NAME FROM TYPE_KEYS WHERE TYPE_ID=?";
$result = $private_db->execute($sql, [$group_id]);
if ($result) {
$row = $private_db->fetchArray($result);
}
return empty($row['KEY_NAME']) ? false :
base64_decode($row['KEY_NAME']);
}
/**
* addUserGroup add an allowed user to an existing group
* @param string $user_id the id of the user to add
* @param string $group_id the group id of the group to add the user to
* @param int $status what should be the membership status of the added
* user. Should be one of ACTIVE_STATUS, INACTIVE_STATUS,
* SUSPENDED_STATUS, INVITED_STATUS
*/
public function addUserGroup($user_id, $group_id, $status = C\ACTIVE_STATUS)
{
$join_date = time();
$db = $this->db;
$sql = "INSERT INTO USER_GROUP " .
"(USER_ID, GROUP_ID, STATUS, JOIN_DATE) VALUES (?, ?, ?, ?)";
$db->execute($sql, [$user_id, $group_id, $status, $join_date]);
}
/**
* deletableUser checks if a user belongs to a group but is not the owner of
* that group Such a user could be deleted from the group
* @param int $user_id which user to look up
* @param int $group_id which group to look up for
* @return bool where user is deletable
*/
public function deletableUser($user_id, $group_id)
{
$db = $this->db;
$sql = "SELECT COUNT(*) AS NUM FROM USER_GROUP UG, SOCIAL_GROUPS G WHERE
UG.USER_ID != G.OWNER_ID AND UG.USER_ID=? AND UG.GROUP_ID=?";
$result = $db->execute($sql, [$user_id, $group_id]);
if (!$row = $db->fetchArray($result)) {
return false;
}
if ($row['NUM'] <= 0) {
return false;
}
return true;
}
/**
* deleteUserGroup delete a user from a group by user id an group id
* @param string $user_id the user id of the user to delete
* @param string $group_id the group id of the group to delete
*/
public function deleteUserGroup($user_id, $group_id)
{
$db = $this->db;
$sql = "DELETE FROM USER_GROUP WHERE USER_ID=? AND GROUP_ID=?";
$db->execute($sql, [$user_id, $group_id]);
}
/**
* getRelationshipId returns the id of the relationship between wiki pages
* with the given name
* @param string $relationship_type the relationship type to get id about
* @return array row from PAGE_RELATIONSHIP table or false (if no access or
* doesn't exist)
*/
public function getRelationshipId($relationship_type)
{
$db = $this->db;
$sql = "SELECT ID AS RELATIONSHIP_ID FROM " .
"PAGE_RELATIONSHIP WHERE NAME=? " . $db->limitOffset(1);
$result = $db->execute($sql, [$relationship_type]);
if (!$result) {
return false;
}
if (!($row = $db->fetchArray($result))) {
return false;
}
return $row['RELATIONSHIP_ID'];
}
/**
* encrypt encrypts data based on provided key.
* @param string $data what data to encrypt
* @param string $key what key to use to encrypt
* @param string $cipher_method a cipher encrypt/decrypt method supported by
* OpenSSL
* @return string $out_data the encrypted string
*/
public function encrypt($data, $key, $cipher_method = 'aes-256-cbc')
{
$iv = openssl_random_pseudo_bytes(
openssl_cipher_iv_length($cipher_method ));
$encrypted = openssl_encrypt($data, $cipher_method, $key, 0, $iv);
/* append value of $IV to the encrypted data with a separator
so this can be used during decryption. Use base 64 to make
friendly to a wide variety of DBMSs such as postgres.
*/
return base64_encode($iv . str_repeat("0", 10) . $encrypted);
}
/**
* decrypt decrypts data based on provided key.
* @param string $data what data to decrypt
* @param string $key what key to use to decrypt
* @param string $cipher_method a cipher encrypt/decrypt method supported by
* OpenSSL
* @return string $out_data the decrypted string
*/
public function decrypt($data, $key, $cipher_method = 'aes-256-cbc')
{
$out_data = "";
$data = base64_decode($data);
$pos = strpos($data, str_repeat("0", 10));
if ($pos !== false) {
$data_parts = explode(str_repeat("0", 10), $data);
$out_data = openssl_decrypt($data_parts[1], $cipher_method,
$key, 0, $data_parts[0]);
}
return $out_data;
}
/**
* encryptFlag encrypts flag value based on provided key.
* @param string $data what data to encrypt
* @param string $key what key to use to encrypt
* @param string $cipher_method a cipher encrypt/decrypt method supported by
* OpenSSL
* @return string $out_data the encrypted string
*/
public function encryptFlag($data, $key, $cipher_method = 'AES-256-ECB')
{
$block_length = 16;
$padding = $block_length - (strlen($data) % $block_length);
$data = $data . str_repeat(chr($padding), $padding);
$key = pack('H*', $key);
$cipher_method = strtolower($cipher_method);
$encrypted = openssl_encrypt($data, $cipher_method, $key,
OPENSSL_RAW_DATA|OPENSSL_NO_PADDING);
return base64_encode($encrypted);
}
/**
* decryptFlag decrypts flag value based on provided key.
* @param string $encrypted_data base64-encoded ciphertext to decrypt
* @param string $key what key to use to decrypt
* @param string $cipher_method a cipher encrypt/decrypt method supported by
* OpenSSL
* @return string $out_data the decrypted string
*/
public function decryptFlag($encrypted_data, $key,
$cipher_method = 'AES-256-ECB')
{
$encrypted_data = base64_decode($encrypted_data);
$key = pack('H*', $key);
$cipher_method = strtolower($cipher_method);
$out_data = openssl_decrypt($encrypted_data, $cipher_method, $key,
OPENSSL_RAW_DATA|OPENSSL_NO_PADDING);
$padding = ord(substr($out_data, -1));
$out_data = substr($out_data, 0, -$padding);
return $out_data;
}
/**
* deleteMessageCopy lets go of one person's copy of a message. A message
* between two people is kept twice, once in each person's own message
* group, so one copy can go without touching the other. A copy may be let
* go by whoever wrote it, and by the person whose group it sits in, which
* is how a message someone else sent is taken off one's own list after the
* sender has already taken theirs off.
* @param int $post_id which message
* @param int $user_id who is letting it go
* @param int $group_id that person's own message group
* @return int how many copies were let go, zero when the message is neither
* theirs to have written nor theirs to hold
*/
public function deleteMessageCopy($post_id, $user_id, $group_id)
{
$db = $this->db;
$sql = "DELETE FROM GROUP_ITEM WHERE ID=? AND
(USER_ID=? OR GROUP_ID=?)";
$result = $db->execute($sql, [$post_id, $user_id, $group_id]);
$affected_rows = $db->affectedRows();
if ($result) {
ImpressionModel::deleteWithDb($user_id, $post_id,
C\THREAD_IMPRESSION, $db);
ImpressionModel::deleteWithDb(C\PUBLIC_USER_ID, $post_id,
C\THREAD_IMPRESSION, $db);
}
return $affected_rows;
}
/**
* deleteConversationCopies lets go of every message one person holds in a
* conversation with someone else, leaving the other person's copies as they
* were. The thread the messages hang from is kept, so the two can go on
* writing to each other with an empty conversation between them.
* @param int $thread_id the conversation's thread
* @param int $group_id the person's own message group
* @return int how many messages were let go
*/
public function deleteConversationCopies($thread_id, $group_id)
{
$db = $this->db;
/* The row a conversation hangs from carries no words, and the
conversation itself skips any row without them, so keeping
the wordless rows keeps the thread whichever of the two
people's groups it sits in. */
$sql = "DELETE FROM GROUP_ITEM WHERE PARENT_ID=? AND GROUP_ID=?
AND DESCRIPTION != ''";
$db->execute($sql, [$thread_id, $group_id]);
return $db->affectedRows();
}
/**
* getFlagCount retrieves flag column value from the database
* @param int $post_id of thread whose flag value needs to be fetched
* @return mixed the FLAG column value (int for plaintext groups, base64
* ciphertext for encrypted groups), false on query failure or no row,
* or null when the row exists with NULL flag
*/
public function getFlagCount($post_id)
{
$db = $this->db;
$sql = "SELECT FLAG FROM GROUP_ITEM WHERE ID = ?";
$stmt = $db->execute($sql, [$post_id]);
if (!$stmt) {
return false;
}
if (!($row = $db->fetchArray($stmt))) {
return false;
}
return $row['FLAG'];
}
/**
* alreadyFlagged says whether one user has already flagged one
* post, so that a person cannot flag the same post twice.
* @param int $user_id user whose flag is being checked
* @param int $post_id to check if the thread is already flagged
* @return bool true if a row in GROUP_ITEM_FLAG exists for the (user, post)
* pair; false on query failure or no match
*/
public function alreadyFlagged($user_id, $post_id)
{
$db = $this->db;
$sql = "SELECT COUNT(*) AS NUM FROM GROUP_ITEM_FLAG WHERE USER_ID = ?
AND ITEM_ID = ?";
$result = $db->execute($sql, [$user_id, $post_id]);
if (!$result) {
return false;
}
$row = $db->fetchArray($result);
if (!$row || !isset($row['NUM'])) {
return false;
}
return ($row['NUM'] > 0);
}
/**
* getParentGroupId get the group id of the parent thread
* @param int $post_id denotes the thread
* @return mixed GROUP_ID of the parent thread, or false on query failure /
* no parent row
*/
public function getParentGroupId($post_id)
{
$db = $this->db;
$sql = "SELECT GROUP_ID FROM GROUP_ITEM WHERE ID =
(SELECT PARENT_ITEM_ID FROM GROUP_ITEM WHERE ID = ?)";
$stmt = $db->execute($sql, [$post_id]);
if (!$stmt) {
return false;
}
if (!($row = $db->fetchArray($stmt))) {
return false;
}
return $row['GROUP_ID'];
}
/**
* getThresholdValue get the minimum number of times a post has to be
* flagged to be reviewed by the moderator
* @param int $group_id to get threshold value for the given group
* @return mixed integer MODERATION_FLAG_THRESHOLD for plaintext groups, or
* its encrypted-flag equivalent for encrypted groups
*/
public function getThresholdValue($group_id)
{
if (!$this->isGroupEncrypted($group_id)) {
return C\p('MODERATION_FLAG_THRESHOLD');
}
$key = $this->getGroupKey($group_id);
$threshold_value = 0;
for ($i = C\p('MODERATION_FLAG_THRESHOLD'); $i > 0; $i--) {
$threshold_value = $this->encryptFlag(strval($threshold_value), $key);
}
return $threshold_value;
}
/**
* alreadyVoted returns true or false depending on whether a given user has
* voted on a given post or not
* @param int $user_id id of user to check if voted
* @param int $post_id id of GROUP_ITEM to see if voted on
* @return bool whether or not the user has voted on that item
*/
public function alreadyVoted($user_id, $post_id)
{
$db = $this->db;
$sql = "SELECT COUNT(*) AS NUM FROM GROUP_ITEM_VOTE WHERE USER_ID = ?
AND ITEM_ID = ?";
$result = $db->execute($sql, [$user_id, $post_id]);
if (!$result) {
return false;
}
$row = $db->fetchArray($result);
if (!$row || !isset($row['NUM'])) {
return false;
}
return ($row['NUM'] > 0);
}
/**
* voteUp casts one up vote by a user to a post
* @param int $user_id id of user to cast vote for
* @param int $post_id id of post on which to cast vote
*/
public function voteUp($user_id, $post_id)
{
$sql = "INSERT INTO GROUP_ITEM_VOTE VALUES (?, ?)";
$this->db->execute($sql, [$user_id, $post_id]);
$sql = "UPDATE GROUP_ITEM SET UPS = UPS + 1 WHERE ID=?";
$this->db->execute($sql, [$post_id]);
}
/**
* voteDown casts one up vote by a user to a post
* @param int $user_id id of user to cast vote for
* @param int $post_id id of post on which to cast vote
*/
public function voteDown($user_id, $post_id)
{
$sql = "INSERT INTO GROUP_ITEM_VOTE VALUES (?, ?)";
$this->db->execute($sql, [$user_id, $post_id]);
$sql = "UPDATE GROUP_ITEM SET DOWNS = DOWNS + 1 WHERE ID=?";
$this->db->execute($sql, [$post_id]);
}
/**
* getTemplateMap returns an array page_id => page_name of all templates for
* a given Group for a given language
* @param int $group_id id of group to produce template map for
* @param string $locale_tag of language to produce template map for
* @return array page_id => page_name for each template for the given group
* in the provided language
*/
public function getTemplateMap($group_id, $locale_tag)
{
$db = $this->db;
$sql = "SELECT GP.ID AS ID, GP.TITLE AS PAGE_NAME
FROM GROUP_PAGE GP, GROUP_PAGE_LINK GPL
WHERE GPL.LINK_TYPE_ID = ? AND GPL.FROM_ID = GP.ID AND
GP.LOCALE_TAG= ? AND GPL.TO_ID = ?";
$result = $db->execute($sql,
[C\WIKI_TEMPLATE_LINK, $locale_tag, $group_id]);
$templates = [];
if ($result) {
while ($template = $db->fetchArray($result)) {
$templates['t'.$template['ID']] = $template['PAGE_NAME'];
}
}
return $templates;
}
/**
* hasThumbBeside says whether a small picture standing for a file has been
* made and kept beside it. Such a picture is stored under the file's own
* name with a picture ending added, so a document has one only once the
* site has drawn its front page. The site writes these as webp; a moving
* one is an animated webp under a name saying so, and jpg is looked
* for last for the sake of thumbnails made before webp
* was used. Where none has been made, a page showing the file
* as a thumb has nothing to draw and falls back to showing the
* file itself.
* @param int $group_id which group the page belongs to
* @param int $page_id which page the file is kept with
* @param string $sub_path folder within the page's files, if any
* @param string $resource_name what the file is called
* @return bool true when a picture stands beside the file
*/
public function hasThumbBeside($group_id, $page_id, $sub_path,
$resource_name)
{
$folders = $this->getGroupPageResourcesFolders($group_id, $page_id,
$sub_path);
if (empty($folders[1])) {
return false;
}
foreach ([".webp", C\MOVING_THUMB_ENDING, ".jpg"] as $ending) {
if (file_exists($folders[1] . "/" . $resource_name .
$ending)) {
return true;
}
}
return false;
}
/**
* convertSpreadsheetRectangle used to convert a pair of spreadsheet
* coordinates into a pair of integer rectangular coordinates. For example
* [A3, B4] into [[0,2], [1, 3]]
* @param array $spreadsheet_coords a pair of spreadsheet cell coordinates
* @return array rectangular integer pair corresponding to these coordinates
*/
public function convertSpreadsheetRectangle($spreadsheet_coords)
{
if ($spreadsheet_coords &&
is_array($spreadsheet_coords) && count($spreadsheet_coords) <= 2) {
if (count($spreadsheet_coords) == 1) {
$spreadsheet_coords[] = $spreadsheet_coords[0];
}
} else {
return false;
}
$rect = [];
for ($i = 0; $i < 2; $i++) {
$num_matches = preg_match("/^([A-Z]+)(\d+)$/",
$spreadsheet_coords[$i], $cell_parts);
if ($num_matches == 0 || count($cell_parts) != 3) {
return false;
}
if (!isset($cell_parts[1])) {
return false;
}
$column_string = $cell_parts[1];
$len = strlen($column_string);
$column = 0;
$shift = 1;
for ($j = 0; $j < $len; $j++) {
$column += (ord($column_string[$j]) - 65) * $shift;
$shift = 26;
}
$rect[$i] = [(int)$cell_parts[2] - 1, $column];
}
return $rect;
}
/**
* spreadsheetRectangleData given a pair of coordinates [top-left, bottom-
* right] in a spreadsheet returns the rectangular portion of the data in
* the spreadsheet corresponding to these coordinates.
* @param array $rectangle coordinates to use when getting data out of
* spreadsheet
* @param array $data 2D array of spreadsheet data
* @return array 2D array corresponding to the portion of the spreadsheet
* defined by the rectangle given.
*/
public function spreadsheetRectangleData($rectangle, $data)
{
if (!$rectangle) {
return $data;
}
$out_data = [];
$eval_data = $data;
$at_row = 0;
for ($i = 0; $i <= $rectangle[1][0]; $i++) {
$column = 0;
for ($j = 0; $j <= $rectangle[1][1]; $j++) {
if (!empty($data[$i][$j]) && $data[$i][$j][0] == '=') {
list(, $eval_data[$at_row][$column]) = $this->evaluateCell(
$data[$i][$j], 1, $eval_data);
} else {
$eval_data[$at_row][$column] = $data[$i][$j] ?? 0;
}
if ($i >= $rectangle[0][0] && $j >= $rectangle[0][1]) {
$out_data[$at_row][$column] = $eval_data[$at_row][$column];
$column++;
}
}
if ($i >= $rectangle[0][0]) {
$at_row++;
}
}
return $out_data;
}
/**
* evaluateCell used to evaluate a cell of a CSV spreadsheet. This code runs
* on the server. In scripts folder there is almost identical Javascript
* code in spreadsheet.js that runs on the client.
* @param string $cell_expression a string representing a formula to
* calculate from a spreadsheet file
* @param int $location character position in cell_expression to start
* evaluating from
* @param array $data 2D array of spreadsheet rows the cell lives in;
* referenced cells (e.g. A1) are resolved against this
* @param int $operator_pos a position in the array of binary operators
* ['*', '/', '+', '-', '%'] (used to compute operators with preference)
* @return array [new_loc, the value of the cell or the String 'NaN' if the
* expression was not evaluatable]
*/
public function evaluateCell($cell_expression, $location, $data,
$operator_pos = 0)
{
$out = [$location, false];
$operators = ['%', '-','+', '/', '*'];
if ($location >= strlen($cell_expression)) {
return $out;
}
if ($operator_pos >= count($operators)) {
$out = $this->evaluateFactor($cell_expression, $location, $data);
return $out;
}
$operator = $operators[$operator_pos];
$left_out = $this->evaluateCell($cell_expression, $location, $data,
$operator_pos + 1);
if ($left_out[0] >= strlen($cell_expression)) {
return $left_out;
}
$left_out[0] = $this->skipWhitespace($cell_expression, $left_out[0]);
if ($cell_expression[$left_out[0]] != $operator) {
return $left_out;
}
$right_out = $this->evaluateCell($cell_expression, $left_out[0] + 1,
$data, $operator_pos);
$out[0] = $this->skipWhitespace($cell_expression, $right_out[0]);
$out[1] = eval("return ". $left_out[1] . $operator . $right_out[1] .
";");
return $out;
}
/**
* evaluateFactor used to evaluate the left hand factor of a binary operator
* appearing in a CSV spreadsheet cell
* @param string $cell_expression cell formula to evaluate
* @param int $location start offset in cell expression of factor protion
* that this method needs to evaluate
* @param array $data array of spreadsheet data to be used for evaluation
* @return array [new_loc, the value of sub-expression]
*/
public function evaluateFactor($cell_expression, $location, $data)
{
$out = [$location, false];
if ($location >= strlen($cell_expression)) {
return $out;
}
$location = $this->skipWhitespace($cell_expression, $location);
$out = $this->evalFunctionInvocation($cell_expression, $location,
$data);
if ($out[1] !== false) {
return $out;
}
$out = $this->evalParenthesizedExpression($cell_expression, $location,
$data);
if ($out[1] !== false) {
return $out;
}
$out = $this->evalRangeExpression($cell_expression, $location, $data);
if ($out[1] !== false) {
return $out;
}
$out = $this->evalNegatedExpression($cell_expression, $location, $data);
if ($out[1] !== false) {
return $out;
}
$out = $this->evalNumericExpression($cell_expression, $location, $data);
if ($out[1] !== false) {
return $out;
}
$out = $this->evalCellNameExpression($cell_expression, $location,
$data);
if ($out[1] !== false) {
return $out;
}
$out = $this->evalStringExpression($cell_expression, $location, $data);
if ($out[1] !== false) {
return $out;
}
return $out;
}
/**
* evalFunctionInvocation used to evaluate a function call appearing in a
* CSV spreadsheet cell formula
* @param string $cell_expression cell formula to evaluate
* @param int $location start offset in cell expression of function call
* that this method needs to evaluate
* @param array $data array of spreadsheet data to be used for evaluation
* @return array [new_loc, the value of sub-expression]
*/
public function evalFunctionInvocation($cell_expression, $location,
$data)
{
$out = [$location, false];
$rest = substr($cell_expression, $location, C\NAME_LEN);
$pattern = "/^(avg|ceil|cell|col|exp|floor|log|min|max|pow|" .
"row|sqrt|sum|username)\\(/i";
$num_matches = preg_match($pattern, $rest, $matches);
if (!$num_matches) {
return $out;
}
$location += strlen($matches[0]);
$arg_values = $this->evaluateArgListExpression($cell_expression,
$location, $data);
if ($arg_values[1] == "NaN") {
return $arg_values;
}
if ($arg_values[1] == false) {
$arg_values[1] = [];
}
$num_args = count($arg_values[1]);
if ((in_array($matches[1], ["ceil", "floor", "exp", "log", "sqrt"])
&& $num_args != 1) ||
(in_array($matches[1], ["cell", "pow"]) && $num_args != 2) ||
(in_array($matches[1], ["min", "max"]) && $num_args == 0) ||
(in_array($matches[1], ["col", "row"]) && $num_args != 4) ||
$cell_expression[$arg_values[0]] != ')') {
$out[0] = $arg_values[0];
$out[1] = "NaN";
return $out;
}
$out[0] = $arg_values[0] + 1;
switch ($matches[1]) {
case "ceil":
$out[1] = ceil($arg_values[1][0]);
break;
case "cell":
$args = $arg_values[1];
$row_col = $this->cellNameAsRowColumn("{$args[1]}{$args[0]}");
$out[1] = $data[$row_col[0] - 1][$row_col[1]];
break;
case "col":
$args = $arg_values[1];
$tmp = $this->cellNameAsRowColumn("{$args[2]}0");
$start = max($tmp[1] - 1, 0);
$tmp = $this->cellNameAsRowColumn("{$args[3]}0");
$end = min($tmp[1] - 1, count($data[0]) - 1);
$out[1] = -1;
for ($j = $start; $j <= $end; $j++) {
if ($data[max($args[1] - 1, 0)][$j] == $args[0]) {
$out[1] = $this->letterRepresentation($j);
break;
}
}
break;
case "exp":
$out[1] = exp($arg_values[1][0]);
break;
case "floor":
$out[1] = floor($arg_values[1][0]);
case "log":
$out[1] = log($arg);
break;
case "min":
$minnands = $arg_values[1];
$min = $minnands[0];
for ($i = 0; $i < count($minnands); $i++) {
if ($minnands[i] < $min) {
$min = $minnands[$i];
}
}
$out[1] = $min;
break;
case "max":
$maxands = $arg_values[1];
$max = $maxands[0];
for ($i = 0; $i < count($maxands); $i++) {
if ($maxands[$i] > $max) {
$max = $maxands[$i];
}
}
$out[1] = $max;
break;
case "pow":
$out[1] = pow($arg_values[1][0], $arg_values[1][1]);
break;
case "row":
$args = $arg_values[1];
$tmp = $this->cellNameAsRowColumn("{$args[1]}0");
$col = $tmp[1];
$start = max($args[2] - 1, 0);
$end = min($args[3] - 1, count($data) - 1);
$out[1] = -1;
for ($i = $start; $i <= $end; $i++) {
if ($data[i][$col] == $args[0]) {
$out[1] = $i + 1;
break;
}
}
break;
case "sqrt":
$out[1] = sqrt($arg_values[1][0]);
break;
case "sum":
case "avg":
$sum = 0;
$summands = $arg_values[1];
for ($i = 0; $i < count($summands); $i++) {
$summand = $summands[$i];
$sum += $summand;
}
$out[1] = ($matches[1] == 'sum') ? $sum :
$sum/count($summands);
break;
case "username":
$out[1] = (empty($_SESSION['USER_NAME'])) ?
'anonymous': $_SESSION['USER_NAME'];
break;
}
return $out;
}
/**
* evalParenthesizedExpression used to evaluate a spreadsheet expression
* surrounded by parentheses appearing in a CSV spreadsheet cell formula
* @param string $cell_expression cell formula to evaluate
* @param int $location start offset in cell expression of parentheses
* expression that this method needs to evaluate
* @param array $data array of spreadsheet data to be used for evaluation
* @return array [new_loc, the value of sub-expression]
*/
public function evalParenthesizedExpression($cell_expression, $location,
$data)
{
$out = [$location, false];
if ($location >= strlen($cell_expression)) {
return $out;
}
$location = $this->skipWhitespace($cell_expression, $location);
if ($cell_expression[$location] == "(") {
$out = $this->evaluateCell($cell_expression, $location + 1, $data);
if ($cell_expression[$out[0]] != ')') {
$out[1] = "NaN";
return $out;
}
$out[0] = $this->skipWhitespace($cell_expression, $out[0] + 1);
return $out;
}
return $out;
}
/**
* evaluateArgListExpression used to evaluate the expressions in a list of
* arguments to a function call appearing in a CSV spreadsheet cell formula
* @param string $cell_expression cell formula to evaluate
* @param int $location start offset in cell expression of argument list
* that this method needs to evaluate
* @param array $data array of spreadsheet data to be used for evaluation
* @return array [new_loc, array of arg-list]
*/
public function evaluateArgListExpression($cell_expression, $location,
$data)
{
$out = [$location, false];
if ($location >= strlen($cell_expression)) {
return $out;
}
$location = $this->skipWhitespace($cell_expression, $location);
$more_args = true;
$out = [$location, []];
while ($more_args) {
$more_args = false;
$sub_out = $this->evaluateCell($cell_expression, $location);
if ($sub_out[1] == 'NaN' || $sub_out[1] === false) {
return $sub_out;
}
if (is_array($sub_out[1])) {
for ($i = s0; $i < count($sub_out[1]); $i++) {
array_push($out[1], $sub_out[1][$i]);
}
} else {
array_push($out[1], $sub_out[1]);
}
$location = self.skipWhitespace($cell_expression, $sub_out[0]);
$out[0] = $location;
if ($location < strlen($cell_expression) &&
$cell_expression[$location] == ',') {
$more_args = true;
$location++;
}
}
return $out;
}
/**
* evalRangeExpression used to convert range expressions,
* cell_name1:cell_name2 into a sequence of cells, cell_name1, ...,
* cell_name2 so that it may be used as part of an argument list to a
* function call appearing in a CSV spreadsheet cell formula
* @param string $cell_expression cell formula to evaluate
* @param int $location start offset in cell expression of range expression
* that this method needs to evaluate
* @param array $data array of spreadsheet data to be used for evaluation
* @return array [new_loc, array of range cells]
*/
public function evalRangeExpression($cell_expression, $location, $data)
{
$out = [$location, false];
if ($location >= strlen($cell_expression)) {
return $out;
}
$location = $this->skipWhitespace($cell_expression, $location);
$rest = substr($cell_expression, $location, C\NAME_LEN);
$num_matches = preg_match("/^([A-Z]+)(\d+)\s*\:\s*([A-Z]+)(\d+)/",
$rest, $matches);
$col_flag = (!empty($matches[3]) && $matches[1] == $matches[3]);
if ($num_matches && ($col_flag || $matches[2] == $matches[4])) {
$out[0] = $this->skipWhitespace($cell_expression, $location +
strlen($matches[0]));
$out[1] = [];
if ($col_flag) {
for ($i = intval($matches[2]); $i <= intval($matches[4]);
$i++) {
$row_col = $this->cellNameAsRowColumn("{$matches[1]}$i");
if (!isset($data[$row_col[0] - 1]) ||
!isset($data[$row_col[0] - 1][$row_col[1]]) ||
$data[$row_col[0] - 1][$row_col[1]] == 'NaN') {
$out[1] = "NaN";
return $out;
}
$cell_value = $data[$row_col[0] - 1][$row_col[1]];
if (!empty($cell_value[0]) && $cell_value[0] == '=') {
$cell_value = $this->evaluateCell($cell_value, 1,
$data);
$cell_value = $cell_value[1];
}
array_push($out[1], $cell_value);
}
} else {
$row_col1 = $this->cellNameAsRowColumn("" . $matches[1]
. $matches[2]);
$row_col2 = $this->cellNameAsRowColumn("" . $matches[3]
. $matches[4]);
$i = $row_col1[0];
for ($j = $row_col1[1]; $j <= $row_col2[1]; $j++) {
if (!isset($data[$i - 1]) || !isset($data[$i - 1][$j])
|| $data[$i - 1][$j] == 'NaN') {
$out[1] = "NaN";
return $out;
}
$cell_value = $data[$i - 1][$j];
if (!empty($cell_value[0]) && $cell_value[0] == '=') {
$cell_value = $this->evaluateCell($cell_value, 1,
$data);
$cell_value = $cell_value[1];
}
array_push($out[1], $cell_value);
}
}
}
return $out;
}
/**
* evalNegatedExpression reads an expression with a minus sign in
* front of it, as a formula in a spreadsheet cell may hold, and
* works out what it comes to.
* @param string $cell_expression cell formula to evaluate
* @param int $location start offset in cell expression of negated
* expression that this method needs to evaluate
* @param array $data array of spreadsheet data to be used for evaluation
* @return array [new_loc, the value of sub-expression]
*/
public function evalNegatedExpression($cell_expression, $location, $data)
{
$out = [$location, false];
if ($location >= strlen($cell_expression)) {
return $out;
}
$location = $this->skipWhitespace($cell_expression, $location);
if ($cell_expression[$location] == "-") {
$sub_out = $this->evaluateCell($cell_expression, $location + 1);
if ($sub_out[1] == 'NaN' || $sub_out[1] == false) {
return $sub_out;
}
$out[0] = $this->skipWhitespace($cell_expression, $sub_out[0]);
$out[1] = - $sub_out[1];
return $out;
}
return $out;
}
/**
* evalNumericExpression used to parse a integer or float expression
* appearing in a CSV spreadsheet cell formula
* @param string $cell_expression cell formula to evaluate
* @param int $location start offset in cell expression of integer or float
* expression that this method needs to evaluate
* @param array $data array of spreadsheet data to be used for evaluation
* @return array [new_loc, the value of sub-expression]
*/
public function evalNumericExpression($cell_expression, $location, $data)
{
$out = [$location, false];
if ($location >= strlen($cell_expression)) {
return $out;
}
$location = $this->skipWhitespace($cell_expression, $location);
$rest = substr($cell_expression, $location, C\NAME_LEN);
$num_matches = preg_match("/^\-?\d+(\.\d*)?|^\-?\.\d+/", $rest,
$matches);
if ($num_matches) {
$out[0] = $this->skipWhitespace($cell_expression, $location +
strlen($matches[0]));
$out[1] = (preg_match('/\./', $matches[0]))? floatval($matches[0]):
intval($matches[0]);
return $out;
}
return $out;
}
/**
* evalCellNameExpression used to parse an expression of the form letter
* sequence followed by number sequence corresponding to the name of a
* spreadsheet cell appearing in a CSV spreadsheet cell formula
* @param string $cell_expression cell formula to evaluate
* @param int $location start offset in cell expression of cell name
* expression that this method needs to evaluate
* @param array $data array of spreadsheet data to be used for evaluation
* @return array [new_loc, the value of sub-expression]
*/
public function evalCellNameExpression($cell_expression, $location, $data)
{
$out = [$location, false];
if ($location >= strlen($cell_expression)) {
return $out;
}
$location = $this->skipWhitespace($cell_expression, $location);
$rest = substr($cell_expression, $location, C\NAME_LEN);
$value = preg_match("/^[A-Z]+\d+/", $rest, $matches);
if ($value) {
$out[0] = $this->skipWhitespace($cell_expression, $location +
strlen($matches[0]) );
$row_col = $this->cellNameAsRowColumn(trim($matches[0]));
$out[1] = $data[$row_col[0] - 1][$row_col[1]];
}
return $out;
}
/**
* evalStringExpression used to parse a string expression, "some string" or
* 'some string', appearing in a CSV spreadsheet cell formula
* @param string $cell_expression cell formula to evaluate
* @param int $location start offset in cell expression of string expression
* that this method needs to evaluate
* @param array $data array of spreadsheet data to be used for evaluation
* @return array [new_loc, the value of sub-expression]
*/
public function evalStringExpression($cell_expression, $location, $data)
{
$out = [$location, $false];
if ($location >= strlen($cell_expression)) {
return out;
}
$location = $this->skipWhitespace($cell_expression, $location);
$rest = substr($cell_expression, $location, C\NAME_LEN);
$value = preg_match('/^\"([^\"]*)\"/', $rest, $matches);
if (!$value) {
$value = preg_match("/^\'([^\']*)\'/", $rest, $matches);
}
if ($value) {
$out[0] = $this->skipWhitespace($cell_expression, $location +
strlen($matches[0]));
$out[1] = $matches[1];
return $out;
}
return $out;
}
/**
* cellNameAsRowColumn converts a string of the form letter sequence
* followed by number sequence to an array of int's of the form [row,
* column]
* @param string $cell_name name of spreadsheet cell to convert
* @return array [row, column] name corresponds to
*/
public function cellNameAsRowColumn($cell_name)
{
if (!preg_match('/^([A-Z]+)(\d+)$/', $cell_name, $matches)) {
return null;
}
$column_string = $matches[1];
$len = strlen($column_string);
$column = 0;
$old_column = 0;
$shift = 26;
for ($i = $len - 1; $i >= 0; $i--) {
$column = $old_column + (ord($column_string[$i]) - 65);
$old_column = $shift * $column;
}
return [intval($matches[2]), $column];
}
/**
* letterRepresentation converts a decimal number to a base 26 number string
* using A-Z for 0-25. Used where drawing column headers for spreadsheet
* @param int $number the value to convert to base 26
* @return string result of conversion
*/
public function letterRepresentation($number)
{
$pre_letter;
$out = "";
do {
$pre_letter = $number % 26;
$number = floor($number/26);
$out .= chr(65 + $pre_letter);
} while ($number > 25);
return $out;
}
/**
* convertVoiceMessageToMp4 turns a voice message into the one kind
* every browser can play. It works on sound alone; a video an end
* user uploads goes another way. A browser writes whichever kind it
* can and no one kind is written by all of them, so a recording that
* arrives in another kind is made over into mp4 here and the one
* that arrived is let go. The name given back is the name the
* recording now has.
* @param string $file_name what the recording arrived as
* @param string $folder where it was put
* @param string $mime_type what kind it arrived in
* @param object $vcs keeps the folder's versions
* @param int $timestamp when it arrived
* @return string the name the recording now has, unchanged when nothing
* needed doing
*/
public function convertVoiceMessageToMp4($file_name, $folder, $mime_type,
$vcs, $timestamp)
{
/* A browser names the kind with what it was encoded with after
it, as in audio/webm;codecs=opus, so only the part before
that is compared. */
$bare_kind = trim(strtok((string)$mime_type, ";"));
$is_sound = str_starts_with($bare_kind, "audio/") ||
in_array($bare_kind, ['video/webm', 'video/ogg']);
if (!$is_sound) {
return $file_name;
}
$arrived = "$folder/$file_name";
if (!file_exists($arrived)) {
return $file_name;
}
if ($this->holdsAPicture($arrived)) {
return $file_name;
}
/* Every recording is made over, whatever kind it arrived in.
Each browser writes its own kind, and even where two write
the same one they do not write it the same way, so one
browser's recording would not play in another. Making all of
them over here leaves one kind, written one way. */
$made_name = pathinfo($file_name, PATHINFO_FILENAME) . ".mp4";
$made = "$folder/$made_name";
/* Where the recording already bears the name it will be given,
the new one is built beside it and put in its place after, so
nothing reads a file being written over. */
$building = ($made === $arrived) ? $made . ".building.mp4" : $made;
$went_wrong = 0;
try {
AudioConverter::convert($arrived, $building);
} catch (\Exception $trouble) {
$went_wrong = 1;
}
if ($went_wrong != 0 || !file_exists($building)) {
if (file_exists($building) && $building !== $arrived) {
unlink($building);
}
return $file_name;
}
if ($building !== $made) {
rename($building, $made);
}
$vcs->createVersion($made, "", $timestamp);
if ($made !== $arrived) {
$vcs->headDelete($arrived);
if (file_exists($arrived)) {
unlink($arrived);
}
}
return $made_name;
}
/**
* holdsAPicture says whether a file carries moving pictures rather
* than sound alone. convertVoiceMessageToMp4 asks before it makes a
* file over, since making a video over would leave its sound and
* throw its pictures away.
*
* A browser recording a voice writes a file that names itself a
* video kind even where it holds no pictures, so what a file calls
* itself does not settle this and the file itself is read.
*
* @param string $path the file to look at
* @return bool true where the file carries moving pictures
*/
private function holdsAPicture($path)
{
try {
$reader = VideoExtractor::open($path);
return $reader->durationSeconds() > 0;
} catch (\Throwable $trouble) {
return false;
}
}
/**
* readersLastWritten says when the readers that make a small picture
* of a document were last written, as a time. A picture older than
* that was made by an older reader, which may have got something
* wrong that the reader now gets right, so it is made again.
*
* @return int when the newest of those readers was last written
*/
public static function readersLastWritten()
{
static $when = null;
if ($when !== null) {
return $when;
}
$when = 0;
foreach (glob(C\BASE_DIR . "/library/processors/*.php") as $one) {
$when = max($when, (int)filemtime($one));
}
return $when;
}
/**
* makeThumbStripExif makes a small picture for a file of a kind
* that one can be made for, and takes the camera information out of
* a photograph while it is there. A media list calls it for each
* file it shows that has no picture yet.
*
* A picture that is already there is the answer, and nothing is
* read or loaded, since a list of a hundred documents would
* otherwise load a reader for each of them every time it is shown.
*
* @param string $file_name The name of the file.
* @param string $folder The folder the file sits in.
* @param string $thumb_folder The folder the pictures are kept in.
* @param string $mime_type What kind of file it is, worked out from
* the name where the caller does not say.
* @return bool True where a picture is there afterwards.
*/
function makeThumbStripExif($file_name, $folder, $thumb_folder,
$mime_type = "")
{
/* A listing can name a file whose bytes never arrived, such as
an item a feed promises that was never downloaded. There is
nothing to draw a picture of, so the answer is no, said
without the read below warning about it. */
if (!file_exists("$folder/$file_name")) {
return false;
}
if ($mime_type == "") {
$mime_type = L\mimeType($file_name);
}
$image_types = ['image/png', 'image/gif', 'image/jpeg', 'image/webp',
'image/bmp'];
$video_types = [
'video/mp4', 'video/webm', 'video/ogg', 'video/avi',
'video/quicktime'];
if (!in_array($mime_type, $image_types) && !in_array($mime_type,
$video_types) && $mime_type != 'application/pdf' &&
$mime_type != 'application/epub+zip' &&
!in_array($mime_type, ['text/html', 'text/plain',
'text/csv'])) {
return false;
}
/* A thumb that is already there is the answer, and saying so
here keeps a media list from loading a reader for a kind of
file it does not need to open. A page of a hundred documents
otherwise pays for a reader of each on every listing. */
foreach (['webp', 'jpg', 'gif'] as $ending) {
$held = "$thumb_folder/$file_name.$ending";
if (!file_exists($held)) {
continue;
}
/* A picture made by an older reader is made again. The
readers are improved between releases, and a picture made
by the reader that shipped a year ago keeps whatever that
reader got wrong until somebody asks for it by hand. The
readers' own files say when they were last written, so a
picture older than they are was made by an older one. */
if (filemtime($held) >= self::readersLastWritten()) {
return true;
}
unlink($held);
}
if (in_array($mime_type, $image_types)) {
$file_string = file_get_contents("$folder/$file_name");
/* A file can carry an image's name without holding an image:
one saved under the wrong name, or one that arrived
damaged. Reading it then says so by handing back false,
and everything below here needs an image to work on, so
there is nothing to make a thumbnail of and we stop. The
site's own error handler is set aside over the read, as it
is over the exif read below, because it reports what is
being asked about rather than letting the read answer. */
set_error_handler(null);
$image = @imagecreatefromstring($file_string);
restore_error_handler();
if ($image === false) {
return false;
}
if (function_exists("exif_read_data")) {
set_error_handler(null);
ob_start();
$exif = @exif_read_data("$folder/$file_name");
ob_end_clean();
restore_error_handler();
if (!empty($exif['Orientation'])) {
$image = match ($exif['Orientation']) {
8 => imagerotate($image, 90, 0),
3 => imagerotate($image, 180, 0),
6 => imagerotate($image, -90, 0),
default => $image,
};
}
/* writing the jpeg with strip exif data */
if (!empty($exif)) {
imagejpeg($image, "$folder/$file_name", 100);
}
}
$thumb_string = ImageProcessor::createThumb($image);
if ($thumb_string) {
file_put_contents("$thumb_folder/$file_name.webp",
$thumb_string);
clearstatcache("$thumb_folder/$file_name.webp");
}
return true;
}
if ($mime_type == 'application/pdf') {
PdfProcessor::createThumb($folder, $thumb_folder, $file_name);
return true;
}
if ($mime_type == 'application/epub+zip') {
EpubProcessor::createThumb($folder, $thumb_folder, $file_name);
return true;
}
if (in_array($mime_type, ['text/html', 'text/plain',
'text/csv'])) {
TextProcessor::createThumb($folder, $thumb_folder, $file_name);
return true;
}
if (in_array($mime_type, $video_types)) {
VideoProcessor::createThumbs($folder, $thumb_folder, $file_name);
return true;
}
return false;
}
/**
* setGroupAppearance sets the look a group gives a page of its own that
* names none: the theme it is drawn with, and the pages to stand above and
* below it. A page that names its own goes on using that; these are what a
* page falls back to, and what every page uses where the group does not let
* a page name its own.
* @param int $group_id which group to set
* @param string $theme the theme a page is drawn with
* @param string $page_header name of the page to stand above
* @param string $page_footer name of the page to stand below
*/
public function setGroupAppearance($group_id, $theme, $page_header,
$page_footer)
{
$db = $this->db;
$sql = "UPDATE SOCIAL_GROUPS SET GROUP_THEME = ?, PAGE_HEADER = ?,
PAGE_FOOTER = ? WHERE GROUP_ID = ?";
$db->execute($sql, [trim($theme), trim($page_header),
trim($page_footer), $group_id]);
}
/**
* getGroupCategories gives the names a group's pages are filed under, so a
* page that collects articles can offer them to choose from rather than
* asking a writer to spell one. Only names something in the group is
* actually filed under are given, since an empty name would collect
* nothing.
* @param int $group_id which group to look within
* @return array the names, in the order a reader would read them
*/
public function getGroupCategories($group_id)
{
$db = $this->db;
/* An ordinary link from one page to another is itself kept as a
relationship, the one numbered below zero, and every wiki link
uses it. It says nothing about what a page is about, so it is
not one of the names a group files under. */
$sql = "SELECT DISTINCT P.NAME AS NAME FROM PAGE_RELATIONSHIP P,
GROUP_PAGE_LINK L, GROUP_PAGE G WHERE L.LINK_TYPE_ID = P.ID
AND G.ID = L.FROM_ID AND G.GROUP_ID = ? AND P.ID > 0
ORDER BY P.NAME ASC";
$result = $db->execute($sql, [$group_id]);
$names = [];
if ($result) {
while ($row = $db->fetchArray($result)) {
if (!empty($row['NAME'])) {
$names[$row['NAME']] = $row['NAME'];
}
}
}
return $names;
}
/**
* getGroupBots get an array of bots that belong to a group
* @param string $group_id the group_id to get bots for
* @return array of bot rows
*/
public function getGroupBots($group_id)
{
$db = $this->db;
$sql = "SELECT CB.USER_ID, U.USER_NAME, CB.BOT_TOKEN, CB.CALLBACK_URL".
" FROM USER_GROUP UG, USERS U, CHAT_BOT CB".
" WHERE UG.GROUP_ID = ? AND UG.USER_ID = U.USER_ID AND" .
" CB.USER_ID = UG.USER_ID " .
$db->limitOffset(C\GROUP_BOT_FOLLOWERS);
$result = $db->execute($sql, [$group_id]);
$bots = [];
while ($bot = $db->fetchArray($result)) {
$bots[] = $bot;
}
return $bots;
}
/**
* createGroupCall inserts a new GROUP_CALL row recording that a group call
* has begun, and resets the per-call event sequence to zero.
* @param string $call_group_id stable call identifier from getCallGroupID
* @param int $initiator_id user id of the caller who started the call
* @param int $time Unix timestamp the call started; -1 substitutes time()
* @return float|int the call's recorded start time (the resolved value of
* $time, useful as a baseline for subsequent nextCallEvent calls)
*/
public function createGroupCall($call_group_id, $initiator_id,
$time = -1)
{
$db = $this->db;
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_USER" => C\p('DB_USER'), "DB_PASSWORD" => C\p('DB_PASSWORD'),
"DB_NAME" => C\p('DB_NAME')];
$time = ($time == -1) ? L\microTimestamp() : $time;
$sql = "INSERT INTO GROUP_CALL (CALL_HASH_ID, INITIATOR_ID, CALL_TIME)
VALUES (?, ?, ?)";
$sql = $db->insertIgnore($sql, $dbinfo);
$result = $db->execute($sql, [$call_group_id, $initiator_id, $time]);
return $time;
}
/**
* deleteGroupCall removes the GROUP_CALL row identified by $call_hash_id.
* @param string $call_hash_id hash id of the call session to delete
*/
public function deleteGroupCall($call_hash_id)
{
$db = $this->db;
$sql = "DELETE FROM GROUP_CALL WHERE CALL_HASH_ID = ?";
$db->execute($sql, [$call_hash_id]);
}
/**
* refreshGroupCall moves the CALL_TIME of the call named by
* $call_hash_id up to the given time, so a call that is still being
* rung keeps counting as active rather than aging out before the
* other side picks up. The caller's browser polls while it rings;
* each poll refreshes the time here. Where no call row matches the
* hash nothing is changed. Only the time moves; the initiator and
* the events already stored stay as they were, so this differs from
* deleting the call and starting a new one, which would drop the
* other side's view of the same call.
* @param string $call_hash_id hash id of the call to keep alive
* @param string|float $time the microtime to move CALL_TIME up to
* @return void
*/
public function refreshGroupCall($call_hash_id, $time)
{
$db = $this->db;
$sql = "UPDATE GROUP_CALL SET CALL_TIME = ?
WHERE CALL_HASH_ID = ?";
$db->execute($sql, [$time, $call_hash_id]);
}
/**
* getGroupCallTime returns the CALL_TIME of the group call identified by
* $call_hash_id.
* @param string $call_hash_id hash id of the call session to look up
* @return string the call's start time as recorded by createGroupCall, or
* the literal "-1" when no row matches the given hash
*/
public function getGroupCallTime($call_hash_id)
{
$db = $this->db;
$sql = "SELECT CALL_TIME FROM GROUP_CALL WHERE CALL_HASH_ID = ?";
$result = $db->execute($sql, [$call_hash_id]);
$call_time = "-1";
if ($result) {
$row = $db->fetchArray($result);
if (!empty($row)) {
$call_time = $row["CALL_TIME"];
}
}
return $call_time;
}
/**
* The phases a call between two people passes through, and what each
* one means. A call is in exactly one of these at any moment, and
* every screen that shows something about a call reads its phase
* rather than working it out again from rows and events.
*
* none: no call between the two.
* ringing: one has called and the other has not taken it yet.
* connected: both are in the call and it is going on.
*/
const CALL_PHASES = ['none', 'ringing', 'connected'];
/**
* callPhase says which phase a call between two people is in.
*
* The messages screen asks this to decide whether to ring a name, and
* the caller's own screen asks it to decide whether to go on ringing.
* A call with no row of its own is over, whatever events it left; a
* call whose two sides have exchanged an answer is connected; and a
* call that has a row but no answer yet is ringing, for as long as a
* caller rings and no longer.
*
* @param string $call_hash_id names the call, being a hash of the two
* people in it
* @return string one of the phases in CALL_PHASES
*/
public function callPhase($call_hash_id)
{
$call_time = $this->getGroupCallTime($call_hash_id);
if (floatval($call_time) < 0) {
return 'none';
}
if ($this->numCallEventsOfType($call_hash_id, 'client-answer',
floatval($call_time)) > 0) {
return 'connected';
}
$now = floatval(L\microTimestamp());
if ($now - floatval($call_time) <= C\MAX_CALL_RING_TIME) {
return 'ringing';
}
return 'none';
}
/**
* numCallEventsOfType counts how many events of one kind a call has
* had since a given moment.
*
* callPhase asks this to tell a call that has been answered from one
* still ringing, since an answer is the event that moves a call from
* the one to the other. It counts from the moment the call began,
* because two people who call each other twice share one name for
* both calls: counting every answer ever recorded made the second
* call read as answered before anyone had picked it up.
*
* @param string $call_hash_id names the call
* @param string $event_type the kind of event to count
* @param float $since count only events from this moment onward, as
* seconds since 1970 with a fraction; zero counts them all
* @return int how many events of that kind the call has had
*/
public function numCallEventsOfType($call_hash_id, $event_type,
$since = 0)
{
$db = $this->db;
$sql = "SELECT EVENT FROM GROUP_CALL_EVENTS WHERE CALL_HASH_ID = ?
AND EVENT_TIME >= ?";
$result = $db->execute($sql, [$call_hash_id, $since]);
$seen = 0;
if (empty($result)) {
return $seen;
}
while ($row = $db->fetchArray($result)) {
$event = unserialize($row['EVENT']);
if (is_array($event) && ($event['type'] ?? "") == $event_type) {
$seen++;
}
}
return $seen;
}
/**
* isActiveCall says whether a call between two people is going on now.
*
* The messages screen asks this before it tells a reader that somebody
* is calling, and the event handler asks it to tell an event on a
* running call from one that has to open a new one. A call with no row
* of its own is over, whatever events it left behind; a call that has
* a row is going on while it is younger than the time a caller rings
* for, and after that only while its two sides keep sending events.
*
* @param string $call_hash_id names the call, being a hash of the two
* people in it
* @return bool whether the call is going on now
*/
public function isActiveCall($call_hash_id)
{
$call_time = $this->getGroupCallTime($call_hash_id);
if (floatval($call_time) < 0) {
return false;
}
$now = L\microTimestamp();
$age = floatval($now) - floatval($call_time);
if ($age <= C\MAX_CALL_RING_TIME) {
return true;
}
return $this->numCallEvents($call_hash_id,
floatval($now) - C\MAX_CALL_EVENT_AGE) > 0;
}
/**
* callsRingingFor says which of the given calls are live and were
* started by somebody other than the reader, which is what it means
* for a call to be ringing at the reader's end. The messages screen
* works out a call's identifier for each of a reader's contacts and
* asks this, so it can mark the contact whose call is waiting to be
* picked up. A call the reader started themselves is left out, since
* a person is not rung by their own call.
* @param array $call_hash_ids stable call identifiers to ask about
* @param int $reader_id id of the user the calls would be ringing at
* @return array those identifiers that are ringing, as keys with the
* id of the caller for a value
*/
public function callsRingingFor($call_hash_ids, $reader_id)
{
$ringing = [];
if (empty($call_hash_ids)) {
return $ringing;
}
$db = $this->db;
$marks = implode(",", array_fill(0, count($call_hash_ids), "?"));
$sql = "SELECT CALL_HASH_ID, INITIATOR_ID, CALL_TIME " .
"FROM GROUP_CALL WHERE CALL_HASH_ID IN ($marks) " .
"AND INITIATOR_ID != ?";
$result = $db->execute($sql,
array_merge(array_values($call_hash_ids), [$reader_id]));
if (empty($result)) {
return $ringing;
}
while ($row = $db->fetchArray($result)) {
if ($this->isActiveCall($row["CALL_HASH_ID"])) {
$ringing[$row["CALL_HASH_ID"]] = $row["INITIATOR_ID"];
}
}
return $ringing;
}
/**
* numCallEvents counts GROUP_CALL_EVENTS rows newer than $timestamp for the
* given call. Used by isActiveCall to decide whether a call whose own age
* exceeded MAX_CALL_EVENT_AGE has continuing activity worth keeping alive.
* @param string $call_hash_id stable call identifier
* @param string|float $timestamp microtime cutoff; only events at or after
* this timestamp are counted
* @return int number of recent events (0 when none or on DB error)
*/
public function numCallEvents($call_hash_id, $timestamp)
{
$db = $this->db;
$sql = "SELECT COUNT(*) AS NUM_EVENTS FROM GROUP_CALL_EVENTS
WHERE CALL_HASH_ID = ? AND EVENT_TIME >= ?";
$result = $db->execute($sql, [$call_hash_id, $timestamp]);
if ($row = $db->fetchArray($result)) {
return $row['NUM_EVENTS'];
}
return 0;
}
/**
* nextCallEvent returns the next call event (after $timestamp) for the
* given user in the given call, excluding events sent by that user
* themselves.
*
* The time it returns is written out to the microsecond, the whole
* of what the column holds. Read back as a bare number a stored time
* came out shorter than it went in, so .220926 reached a caller as
* .2209; passed back as the next cutoff, that coarser time let the
* row it came from match once more and the same event was handed
* back again, and a call never moved past its first answer.
* @param string $call_group_id stable call identifier
* @param int $user_id viewer; events with SENDER_ID == $user_id are skipped
* @param string|float $timestamp microtime cutoff; only events strictly
* newer are returned
* @return mixed associative array with EVENT_TIME and unserialized EVENT
* keys for the next matching row; false when nothing newer is available
* or the query fails
*/
public function nextCallEvent($call_group_id, $user_id, $timestamp)
{
$db = $this->db;
$sql = "SELECT EVENT_TIME, EVENT FROM GROUP_CALL_EVENTS
WHERE CALL_HASH_ID = ? AND EVENT_TIME > ? AND SENDER_ID <> ?
ORDER BY EVENT_TIME ASC ". $db->limitOffset(1);
$out = false;
if ($result = $db->execute($sql, [$call_group_id, $timestamp,
$user_id])) {
$out = $db->fetchArray($result);
if ($out) {
$out['EVENT_TIME'] = sprintf('%.6f',
(float)$out['EVENT_TIME']);
$out['EVENT'] = unserialize($out['EVENT']);
}
}
return $out;
}
/**
* addCallEvent records a call event (e.g. ICE candidate, the description
* of a call that two browsers swap offer/answer,
* mute/unmute) for the given call, by inserting a row into
* GROUP_CALL_EVENTS.
* @param string $call_group_id stable call identifier
* @param int $sender_id user who produced the event
* @param string $event_type event tag understood by the call UI (e.g.
* "offer", "answer", "candidate")
* @param mixed $data event payload; serialized before being stored in the
* EVENT column
*/
public function addCallEvent($call_group_id, $sender_id, $event_type,
$data)
{
$db = $this->db;
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_USER" => C\p('DB_USER'), "DB_PASSWORD" => C\p('DB_PASSWORD'),
"DB_NAME" => C\p('DB_NAME')];
$event = serialize([
'type' => $event_type,
'data' => $data,
]);
$sql = "INSERT INTO GROUP_CALL_EVENTS (CALL_HASH_ID, SENDER_ID,
EVENT_TIME, EVENT) VALUES (?, ?, ?, ?)";
$sql = $db->insertIgnore($sql, $dbinfo);
/* EVENT_TIME is half the primary key, so two events timed to the
same microsecond collide and the later insert is ignored,
losing that candidate. Two people share one call, so under a
web server that runs each request in its own process their two
events can be timed together, and reading the newest time to
step past it would not close the gap: both would read the same
newest time and pick the same next one. So the insert is tried
and its result read; where nothing was written the time is
moved on a microsecond and the insert tried again, up to a
small bound, so each event lands at a distinct, rising time
whatever else is writing at once. */
$micro_time = L\microTimestamp();
$tries = 0;
do {
$db->execute($sql, [$call_group_id, $sender_id, $micro_time,
$event]);
if ($db->affectedRows() > 0) {
return;
}
$micro_time = $this->afterCallEventTime($call_group_id,
$micro_time);
$tries++;
} while ($tries < self::MAX_CALL_EVENT_TIME_TRIES);
}
/**
* afterCallEventTime gives a time later than both the one passed and
* the newest event already stored for a call, so an event whose
* insert was refused for sharing a time can be tried again past
* whatever took that time. The newest stored time is read here so a
* retry steps past an event another writer landed in the meantime,
* not merely past the time this writer first tried.
*
* @param string $call_group_id stable call identifier
* @param string|float $tried the time the refused insert used
* @return string a time a microsecond past the later of the tried
* time and the newest stored event, in fixed six-digit form
*/
public function afterCallEventTime($call_group_id, $tried)
{
$newest = $this->newestCallEventTime($call_group_id);
$micro_time = $tried;
if ($newest !== "" && (float)$newest > (float)$micro_time) {
$micro_time = $newest;
}
$parts = explode(".", sprintf('%.6f', (float)$micro_time));
$seconds = intval($parts[0]);
$micro_seconds = intval($parts[1]) + 1;
if ($micro_seconds >= 1000000) {
$seconds += intdiv($micro_seconds, 1000000);
$micro_seconds = $micro_seconds % 1000000;
}
return sprintf('%d.%06d', $seconds, $micro_seconds);
}
/**
* newestCallEventTime returns the EVENT_TIME of the most recent
* event stored for a call, so a new event can be timed past it and
* no two events share the time that is half their primary key.
*
* @param string $call_group_id stable call identifier
* @return string the newest EVENT_TIME held for the call, or the
* empty string when the call has no events yet
*/
public function newestCallEventTime($call_group_id)
{
$db = $this->db;
$sql = "SELECT EVENT_TIME FROM GROUP_CALL_EVENTS
WHERE CALL_HASH_ID = ? ORDER BY EVENT_TIME DESC " .
$db->limitOffset(1);
$newest = "";
if ($result = $db->execute($sql, [$call_group_id])) {
$row = $db->fetchArray($result);
if ($row) {
$newest = $row['EVENT_TIME'];
}
}
return $newest;
}
/**
* cullCallEvents deletes GROUP_CALL_EVENTS rows older than
* MAX_CALL_EVENT_AGE for the given call. Called periodically to keep the
* events table from growing without bound.
* @param string $call_group_id stable call identifier
* @param int $time microtime cutoff (-1 substitutes microTimestamp());
* events older than $time minus MAX_CALL_EVENT_AGE are removed
*/
public function cullCallEvents($call_group_id, $time = -1)
{
$db = $this->db;
$time = ($time == -1) ? L\microTimestamp() : $time;
$timestamp = strval(
floatval($time) - C\MAX_CALL_EVENT_AGE);
$sql = "DELETE FROM GROUP_CALL_EVENTS WHERE CALL_HASH_ID = ? AND
EVENT_TIME < ?";
$result = $db->execute($sql, [$call_group_id, $timestamp]);
}
}
(c) 2026 Seekquarry - Open Source Search Engine Software