<?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
*
* This file contains global functions connected to upgrading the database
* between different versions of Yioop
*
* @author Chris Pollett chris@pollett.org
* @license https://www.gnu.org/licenses/ GPL3
* @link https://www.seekquarry.com/
* @copyright 2009 - 2026
* @filesource
*/
namespace seekquarry\yioop\library;
use seekquarry\yioop\configs as C;
use seekquarry\yioop\models as M;
use seekquarry\yioop\models\datasources as D;
use seekquarry\yioop\library\mail\DkimKey;
use seekquarry\yioop\library\mail\MailSiteFactory;
/**
* Upgrades a Version 0 version of the Yioop database to a Version 1 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion1(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("CREATE TABLE VERSION (ID $integer PRIMARY KEY)");
$db->execute("INSERT INTO VERSION VALUES (1)");
$db->execute("CREATE TABLE USER_SESSION( USER_ID $integer PRIMARY KEY, ".
"SESSION VARCHAR(".C\MAX_GROUP_POST_LEN."))");
$db->execute("CREATE TABLE SESSION_USER( ".
"SESSION_ID VARCHAR(".C\SESSION_ID_LEN.") PRIMARY KEY, ".
"USER_ID $integer, EXPIRES $integer)");
}
/**
* Upgrades a Version 1 version of the Yioop database to a Version 2 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion2(&$db)
{
$db->execute("ALTER TABLE USERS ADD UNIQUE ( USER_NAME )" );
$db->execute("INSERT INTO LOCALE VALUES (
17, 'kn', 'ಕನ್ನಡ', 'lr-tb')");
$db->execute("INSERT INTO LOCALE VALUES (
18, 'hi', 'हिन्दी', 'lr-tb')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES (3, 5,
'Modifier les rôles')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES (4, 5,
'Modifier les indexes')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES (5, 5,
'Mélanger les indexes')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES (6, 5,
'Les filtres de recherche')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES (7, 5,
'Modifier les lieux')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES (8, 5,
'Configurer')");
}
/**
* Upgrades a Version 2 version of the Yioop database to a Version 3 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion3(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("INSERT INTO LOCALE VALUES (19, 'tr', 'Türkçe', 'lr-tb')");
$db->execute("INSERT INTO ROLE_ACTIVITY VALUES (1, 10)");
$db->execute("CREATE TABLE MACHINE (
NAME VARCHAR(16) PRIMARY KEY, URL VARCHAR(".C\MAX_URL_LEN.") UNIQUE,
HAS_QUEUE_SERVER BOOLEAN, NUM_FETCHERS $integer)");
$db->execute("DELETE FROM ACTIVITY WHERE ACTIVITY_ID>5 AND " .
"ACTIVITY_ID<11");
$db->execute(
"DELETE FROM TRANSLATION WHERE TRANSLATION_ID>5 AND " .
"TRANSLATION_ID<11");
$db->execute("DELETE FROM TRANSLATION_LOCALE ".
"WHERE TRANSLATION_ID>5 AND TRANSLATION_ID<11");
$db->execute("INSERT INTO ACTIVITY VALUES (6, 6, 'pageOptions')");
$db->execute("INSERT INTO ACTIVITY VALUES (7, 7, 'searchFilters')");
$db->execute("INSERT INTO ACTIVITY VALUES (8, 8, 'manageMachines')");
$db->execute("INSERT INTO ACTIVITY VALUES (9, 9, 'manageLocales')");
$db->execute("INSERT INTO ACTIVITY VALUES (10, 10, 'configure')");
$db->execute(
"INSERT INTO TRANSLATION VALUES (6, 'db_activity_file_options')");
$db->execute(
"INSERT INTO TRANSLATION VALUES (7,'db_activity_search_filters')");
$db->execute(
"INSERT INTO TRANSLATION VALUES(8,'db_activity_manage_machines')");
$db->execute(
"INSERT INTO TRANSLATION VALUES (9,'db_activity_manage_locales')");
$db->execute(
"INSERT INTO TRANSLATION VALUES (10, 'db_activity_configure')");
$db->execute(
"INSERT INTO TRANSLATION_LOCALE VALUES (6, 1, 'Page Options')");
$db->execute(
"INSERT INTO TRANSLATION_LOCALE VALUES (7, 1, 'Search Filters')");
$db->execute(
"INSERT INTO TRANSLATION_LOCALE VALUES (8, 1, 'Manage Machines')");
$db->execute(
"INSERT INTO TRANSLATION_LOCALE VALUES (9, 1, 'Manage Locales')");
$db->execute(
"INSERT INTO TRANSLATION_LOCALE VALUES (10, 1, 'Configure')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES (6, 5,
'Options de fichier')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES (7, 5,
'Les filtres de recherche')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES (8, 5,
'Modifier les ordinateurs')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES (9, 5,
'Modifier les lieux')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES (10, 5,
'Configurer')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES (
9, 9, 'ローケル管理')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES (10, 9, '設定')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES (
9, 10, '로케일 관리')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES (10, 10, '구성')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES (9, 15,
'Quản lý miền địa phương')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES (10, 15,
'Sắp xếp hoạt động dựa theo hoạch định')");
}
/**
* Upgrades a Version 3 version of the Yioop database to a Version 4 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion4(&$db)
{
$db->execute("ALTER TABLE MACHINE ADD COLUMN PARENT VARCHAR(".
C\NAME_LEN.")");
}
/**
* Upgrades a Version 4 version of the Yioop database to a Version 5 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion5(&$db)
{
$static_page_path = C\LOCALE_DIR . "/" . C\p('DEFAULT_LOCALE') . "/pages";
if (!file_exists($static_page_path)) {
mkdir($static_page_path);
}
$default_bot_txt_path = "$static_page_path/bot.thtml";
$old_bot_txt_path = C\WORK_DIRECTORY . "/bot.txt";
if (file_exists($old_bot_txt_path) && !file_exists($default_bot_txt_path)){
rename($old_bot_txt_path, $default_bot_txt_path);
}
$db->setWorldPermissionsRecursive($static_page_path);
}
/**
* Upgrades a Version 5 version of the Yioop database to a Version 6 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion6(&$db)
{
if (!file_exists(C\PREP_DIR)) {
mkdir(C\PREP_DIR);
}
$db->setWorldPermissionsRecursive(C\PREP_DIR);
}
/**
* Upgrades a Version 6 version of the Yioop database to a Version 7 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion7(&$db)
{
$db->execute("DELETE FROM ACTIVITY WHERE ACTIVITY_ID=7");
$db->execute("INSERT INTO ACTIVITY VALUES (7, 7, 'resultsEditor')");
$db->execute("DELETE FROM TRANSLATION WHERE TRANSLATION_ID=7");
$db->execute("DELETE FROM TRANSLATION_LOCALE WHERE TRANSLATION_ID=7");
$db->execute(
"INSERT INTO TRANSLATION VALUES (7,'db_activity_results_editor')");
$db->execute(
"INSERT INTO TRANSLATION_LOCALE VALUES (7, 1, 'Results Editor')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES (7, 5,
'Éditeur de résultats')");
}
/**
* Upgrades a Version 7 version of the Yioop database to a Version 8 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion8(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("INSERT INTO LOCALE VALUES (20, 'fa', 'فارسی', 'rl-tb')");
$db->execute("CREATE TABLE ACTIVE_FETCHER (NAME VARCHAR(".C\NAME_LEN.")," .
" FETCHER_ID $integer)");
$db->execute("CREATE TABLE CRON_TIME " .
"(TIMESTAMP INT(" . C\TIMESTAMP_LEN . "))");
$db->execute("INSERT INTO CRON_TIME VALUES ('" . time() . "')");
upgradeLocales();
}
/**
* Upgrades a Version 8 version of the Yioop database to a Version 9 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion9(&$db)
{
$db->execute("INSERT INTO ROLE_ACTIVITY VALUES (1, 11)");
$db->execute("DELETE FROM ACTIVITY WHERE ACTIVITY_ID >= 8");
$db->execute("DELETE FROM TRANSLATION WHERE TRANSLATION_ID >= 8");
$db->execute("DELETE FROM TRANSLATION_LOCALE WHERE TRANSLATION_ID >= 8");
$db->execute("INSERT INTO ACTIVITY VALUES (8, 8, 'searchSources')");
$db->execute("INSERT INTO ACTIVITY VALUES (9, 9, 'manageMachines')");
$db->execute("INSERT INTO ACTIVITY VALUES (10, 10, 'manageLocales')");
$db->execute("INSERT INTO ACTIVITY VALUES (11, 11, 'configure')");
$db->execute("INSERT INTO TRANSLATION VALUES(8,
'db_activity_search_services')");
$db->execute("INSERT INTO TRANSLATION VALUES(9,
'db_activity_manage_machines')");
$db->execute("INSERT INTO TRANSLATION VALUES (10,
'db_activity_manage_locales')");
$db->execute("INSERT INTO TRANSLATION VALUES (11,
'db_activity_configure')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES (8, 1,
'Search Sources')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES (9, 1,
'Manage Machines')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES (10, 1,
'Manage Locales')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES (11, 1,
'Configure')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES (8, 5,
'Sources de recherche')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES (9, 5,
'Modifier les ordinateurs')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES (10, 5,
'Modifier les lieux')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES (11, 5,
'Configurer')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES (10, 9,
'ローケル管理')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES (11, 9,
'設定')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES (10,
10, '로케일 관리')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES (11,
10, '구성')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES (10, 15,
'Quản lý miền địa phương')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES (11, 15,
'Sắp xếp hoạt động dựa theo hoạch định')");
}
/**
* Upgrades a Version 9 version of the Yioop database to a Version 10 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion10(&$db)
{
$db->execute("CREATE TABLE MEDIA_SOURCE (TIMESTAMP INT(11) PRIMARY KEY,
NAME VARCHAR(16) UNIQUE, TYPE VARCHAR(16),
SOURCE_URL VARCHAR(256), THUMB_URL VARCHAR(256)
)");
$db->execute("INSERT INTO MEDIA_SOURCE VALUES ('1342634195',
'YouTube', 'video', 'http://www.youtube.com/watch?v={}&',
'http://img.youtube.com/vi/{}/2.jpg')");
$db->execute("INSERT INTO MEDIA_SOURCE VALUES ('1342634196',
'MetaCafe', 'video', 'http://www.metacafe.com/watch/{}/',
'http://www.metacafe.com/thumb/{}.jpg')");
$db->execute("INSERT INTO MEDIA_SOURCE VALUES ('1342634197',
'DailyMotion', 'video', 'http://www.dailymotion.com/video/{}',
'http://www.dailymotion.com/thumbnail/video/{}')");
}
/**
* Upgrades a Version 10 version of the Yioop database to a Version 11 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion11(&$db)
{
$db->execute("DROP TABLE CRON_TIME");
$db->execute("ALTER TABLE ROLE_ACTIVITY ADD CONSTRAINT
PK_RA PRIMARY KEY(ROLE_ID, ACTIVITY_ID)");
$db->execute("CREATE TABLE SUBSEARCH (LOCALE_STRING VARCHAR(16) " .
"PRIMARY KEY, FOLDER_NAME VARCHAR(16), INDEX_IDENTIFIER CHAR(13))");
}
/**
* Upgrades a Version 11 version of the Yioop database to a Version 12 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion12(&$db)
{
$db->execute("INSERT INTO CRAWL_MIXES VALUES (2, 'images')");
$db->execute("INSERT INTO MIX_GROUPS VALUES(2, 0, 1)");
$db->execute("INSERT INTO MIX_COMPONENTS VALUES(2, 0, 1, 1,
'media:image')");
$db->execute("INSERT INTO CRAWL_MIXES VALUES (3, 'videos')");
$db->execute("INSERT INTO MIX_GROUPS VALUES(3, 0, 1)");
$db->execute("INSERT INTO MIX_COMPONENTS VALUES(3, 0, 1, 1,
'media:video')");
$db->execute("INSERT INTO SUBSEARCH VALUES('db_subsearch_images',
'images','m:2',50)");
$db->execute("INSERT INTO TRANSLATION VALUES " .
"(1002,'db_subsearch_images')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES
(1002, 1, 'Images' )");
$db->execute("INSERT INTO SUBSEARCH VALUES ('db_subsearch_videos',
'videos','m:3',10)");
$db->execute("INSERT INTO TRANSLATION VALUES " .
"(1003,'db_subsearch_videos')");
$db->execute("INSERT INTO TRANSLATION_LOCALE VALUES
(1003, 1, 'Videos' )");
}
/**
* Upgrades a Version 12 version of the Yioop database to a Version 13 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion13(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("CREATE TABLE FEED_ITEM (GUID VARCHAR(11) PRIMARY KEY,
TITLE VARCHAR(512), LINK VARCHAR(256), DESCRIPTION VARCHAR(4096),
PUBDATE $integer, SOURCE_NAME VARCHAR(16))");
if (!file_exists(C\WORK_DIRECTORY . "/feeds")) {
mkdir(C\WORK_DIRECTORY . "/feeds");
}
upgradeLocales(); //force locale upgrade
}
/**
* Upgrades a Version 13 version of the Yioop database to a Version 14 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion14(&$db)
{
$db->execute("ALTER TABLE MEDIA_SOURCE ADD LANGUAGE VARCHAR(7)");
}
/**
* Upgrades a Version 14 version of the Yioop database to a Version 15 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion15(&$db)
{
$db->execute("DELETE FROM MIX_COMPONENTS WHERE MIX_TIMESTAMP=2
AND GROUP_ID=0");
$db->execute("INSERT INTO MIX_COMPONENTS VALUES(
2, 0, 1, 1, 'media:image site:doc')");
$db->execute("DELETE FROM MIX_COMPONENTS WHERE MIX_TIMESTAMP=3
AND GROUP_ID=0");
$db->execute("INSERT INTO MIX_COMPONENTS VALUES(
3, 0, 1, 1, 'media:video site:doc')");
$db->execute("INSERT INTO LOCALE VALUES (21, 'te',
'తెలుగు', 'lr-tb')");
upgradeLocales();
}
/**
* Upgrades a Version 15 version of the Yioop database to a Version 16 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion16(&$db)
{
addActivityAtId($db, 'db_activity_manage_classifiers',
"manageClassifiers", 4);
updateTranslationForStringId($db, 'db_activity_manage_classifiers',
'en-US', 'Manage Classifiers');
updateTranslationForStringId($db, 'db_activity_manage_classifiers',
'fr-FR', 'Manage Classifiers');
updateTranslationForStringId($db, 'db_activity_manage_groups', 'fr-FR',
'Classificateurs');
$old_archives_path = C\CACHE_DIR . "/archives";
$new_archives_path = C\ARCHIVES_DIR;
if (file_exists($old_archives_path)) {
rename($old_archives_path, $new_archives_path);
} else if (!file_exists($new_archives_path)) {
mkdir($new_archives_path);
}
$db->setWorldPermissionsRecursive($new_archives_path);
$new_classifiers_path = C\CLASSIFIERS_DIR;
if (!file_exists($new_classifiers_path)) {
mkdir($new_classifiers_path);
}
$db->setWorldPermissionsRecursive($new_classifiers_path);
upgradeLocales();
}
/**
* Upgrades a Version 16 version of the Yioop database to a Version 17 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion17(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$auto_increment = $db->autoIncrement($dbinfo);
$db->execute("CREATE TABLE GROUPS (GROUP_ID $integer PRIMARY KEY
$auto_increment ,GROUP_NAME VARCHAR(128), CREATED_TIME INT(11),
CREATOR_ID INT(11))");
$db->execute("CREATE TABLE USER_GROUP (USER_ID $integer,
GROUP_ID $integer, PRIMARY KEY (GROUP_ID, USER_ID) )");
addActivityAtId($db, 'db_activity_manage_groups', "manageGroups", 4);
updateTranslationForStringId($db, 'db_activity_manage_groups', 'en-US',
'Manage Groups');
updateTranslationForStringId($db, 'db_activity_manage_groups', 'fr-FR',
'Modifier les groupes');
upgradeLocales();
}
/**
* Upgrades a Version 17 version of the Yioop database to a Version 18 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion18(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$auto_increment = $db->autoIncrement($dbinfo);
$db->execute("CREATE TABLE ACCESS (NAME VARCHAR(16), ID $integer,
TYPE VARCHAR(16))");
$db->execute("CREATE TABLE BLOG_DESCRIPTION (TIMESTAMP INT(11) UNIQUE,
DESCRIPTION VARCHAR(4096))");
addActivityAtId($db, 'db_activity_blogs_pages', "blogPages", 6);
updateTranslationForStringId($db, 'db_activity_blogs_pages', 'en-US',
'Blogs and Pages');
updateTranslationForStringId($db, 'db_activity_blogs_pages', 'fr-FR',
'les blogs et les pages');
upgradeLocales();
}
/**
* Upgrades a Version 18 version of the Yioop database to a Version 19 version
* This update has been superseded by the Version20 update and so its contents
* have been eliminated.
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion19(&$db)
{
}
/**
* Upgrades a Version 19 version of the Yioop database to a Version 20 version
* This is a major upgrade as the user table have changed. This also acts
* as a cumulative since version 0.98. It involves a web form that has only
* been localized to English
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion20(&$db)
{
if (!isset($_REQUEST['v20step'])) {
$_REQUEST['v20step'] = 1;
}
$upgrade_check_file = C\WORK_DIRECTORY . "/v20check.txt";
if (!file_exists($upgrade_check_file)) {
$upgrade_password = substr(sha1(microtime(true).C\p('AUTH_KEY')), 0, 8);
file_put_contents($upgrade_check_file, $upgrade_password);
} else {
$v20check = trim(file_get_contents($upgrade_check_file));
if (isset($_REQUEST['v20step']) && $_REQUEST['v20step'] == 2 &&
(!isset($_REQUEST['upgrade_code'])||
$v20check != trim($_REQUEST['upgrade_code']))) {
$_REQUEST['v20step'] = 1;
$data['SCRIPT'] = "doMessage('<h1 class=\"red\" >".
"v20check.txt not typed in correctly!</h1>')";
}
}
switch ($_REQUEST['v20step']) {
case "2":
$profile_model = new M\ProfileModel(C\p('DB_NAME'), false);
$profile_model->db = $db;
$save_tables = ["ACTIVE_FETCHER", "CURRENT_WEB_INDEX",
"FEED_ITEM", "MACHINE", "MEDIA_SOURCE", "SUBSEARCH",
"VERSION"];
$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')];
$creation_time = microTimestamp();
$profile = $profile_model->getProfile(C\WORK_DIRECTORY);
$new_profile = $profile;
$new_profile['MAIL_SERVER']= "";
$new_profile['MAIL_PORT']= "";
$new_profile['MAIL_USERNAME']= "";
$new_profile['MAIL_PASSWORD']= "";
$new_profile['MAIL_SECURITY']= "";
$new_profile['REGISTRATION_TYPE'] = 'disable_registration';
$new_profile['WORD_SUGGEST'] = true;
$profile_model->updateProfile(C\WORK_DIRECTORY, $new_profile,
$profile);
//get current users
//(assume can fit in memory and doesn't take long)
$users = [];
$user_tables_sql = ["SELECT USER_NAME FROM USER",
"SELECT USER_NAME, FIRST_NAME, LAST_NAME, EMAIL FROM USERS"];
$i = 0;
foreach ($user_tables_sql as $sql) {
$result = $db->execute($sql);
if ($result) {
while($users[$i] = $db->fetchArray($result)) {
$setup_user_fields = [];
if ($users[$i]["USER_NAME"] == "root" ||
$users[$i]["USER_NAME"] == "public") {
continue;
}
$users[$i]["FIRST_NAME"] =
(isset($users[$i]["FIRST_NAME"])) ?
$users[$i]["FIRST_NAME"] : "FIRST_$i";
$users[$i]["LAST_NAME"] =
(isset($users[$i]["LAST_NAME"])) ?
$users[$i]["LAST_NAME"] : "LAST_$i";
$users[$i]["EMAIL"] =
(isset($users[$i]["EMAIL"])) ?
$users[$i]["EMAIL"] : "user$i@dev.null";
$users[$i]["PASSWORD"] = $v20check;
$users[$i]["STATUS"] = C\INACTIVE_STATUS;
$users[$i]["CREATION_TIME"] = $creation_time;
$users[$i]["UPS"] = 0;
$users[$i]["DOWNS"] = 0;
$i++;
}
unset($users[$i]);
$result = null;
}
}
$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')];
$profile_model->initializeSql($db, $dbinfo);
$database_tables = array_diff(
array_keys($profile_model->create_statements),
$save_tables);
$database_tables = array_merge($database_tables,
["BLOG_DESCRIPTION", "USER_OLD", "ACCESS"]);
foreach ($database_tables as $table) {
if (!in_array($table, $save_tables)){
$db->execute("DROP TABLE ".$table);
}
}
if ($profile_model->migrateDatabaseIfNecessary(
$dbinfo, $save_tables)) {
$user_model = new M\UserModel(C\p('DB_NAME'), false);
$user_model->db = $db;
foreach ($users as $user) {
$user_model->addUser($user["USER_NAME"], $user["PASSWORD"],
$user["FIRST_NAME"], $user["LAST_NAME"],
$user["EMAIL"], $user["STATUS"]);
}
$user = [];
$user['USER_ID'] = C\ROOT_ID;
$user['PASSWORD'] = $v20check;
$user_model->updateUser($user);
$db->execute("DELETE FROM VERSION WHERE ID < 19");
$db->execute("UPDATE VERSION SET ID=20 WHERE ID=19");
return;
}
$data['SCRIPT'] = "doMessage('<h1 class=\"red\" >".
"Couldn't migrate database tables from defaults!</h1>')";
// no break
case "1":
// no break
default:
?>
<!DOCTYPE html>
<html lang='en-US'>
<head>
<title>Yioop Upgrade Detected</title>
<meta name="ROBOTS" content="NOINDEX,NOFOLLOW" >
<meta name="Author" content="Christopher Pollett" >
<meta charset="utf-8" >
<?php if ($_SERVER["MOBILE"]) {?>
<meta name="viewport" content="width=device-width,initial-scale=1">
<?php } ?>
<link rel="stylesheet" type="text/css"
href="<?= C\SHORT_BASE_URL ?>/css/search.css" >
</head>
<body class="html-ltr <?php if ($_SERVER["MOBILE"]) {e('mobile'); }
?>" >
<div id="message" ></div>
<div class='small-margin-current-activity'>
<h1 class='center green'>Yioop Upgrade Detected!</h1>
<p>Upgrading to Version 1 of Yioop from an earlier version
is a major upgrade. The way passwords are stored and the
organization of the Yioop database has changed. Here is
what is preserved by this upgrade:</p>
<ol>
<li>Existing crawls and archive data.</li>
<li>Machines known if this instance is a name server.</li>
<li>Media sources and subsearches.</li>
<li>Feed items.</li>
</ol>
<p>Here is what happens during the upgrade which might
result in data loss:</p>
<ol>
<li>Root and user account passwords are changed to the contents of
v20check.txt.</li>
<li>User accounts other than root are marked as inactived,
so will have tobe activated under Manage Users before that person
can sign in.</li>
<li>All roles except Admin and User are deleted. Root
will be given Admin role, all other users will receive
User role.</li>
<li>All existing groups are deleted.</li>
<li>Existing crawl mixes will be deleted.</li>
<li>Any customized translations that begin with the prefix db_.
Other still in use translations will be preserved.</li>
</ol>
<p>If given the above you don't want to upgrade, merely replace
this folder with the contents of your old Yioop instance and
you should be able to continue to use Yioop as before.</p>
<p>If you decide to proceed with the upgrade, please back up
both your existing database and work directory.</p>
<form method="post" action="?">
<p><label for="upgrade-code">
<b>In the field below enter the string found in the file:<br>
<span class="green"><?= C\WORK_DIRECTORY . "/v20check.txt"?></span>
</b></label></p>
<input id='upgrade-code' class="extra-wide-field"
name="upgrade_code" type="text" >
<input type="hidden" name="v20step" value="2" >
<button class="button-box" type="submit">Upgrade</button>
</form>
<?php
break;
}
?>
</div>
<script src="<?= C\SHORT_BASE_URL ?>/scripts/basic.js" ></script>
<script>
<?php
if (isset($data['SCRIPT'])) {
e($data['SCRIPT']);
}
?></script>
</body>
</html>
<?php
exit();
}
/**
* Upgrades a Version 20 version of the Yioop database to a Version 21 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion21(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("CREATE TABLE GROUP_THREAD_VIEWS(
THREAD_ID $integer PRIMARY KEY, NUM_VIEWS $integer)");
$db->execute("ALTER TABLE MEDIA_SOURCE RENAME TO MEDIA_SOURCE_OLD");
$db->execute("CREATE TABLE MEDIA_SOURCE (TIMESTAMP NUMERIC(11) PRIMARY KEY,
NAME VARCHAR(64) UNIQUE, TYPE VARCHAR(16), SOURCE_URL VARCHAR(256),
AUX_INFO VARCHAR(512), LANGUAGE VARCHAR(7))");
D\DatasourceManager::copyTable("MEDIA_SOURCE_OLD", $db, "MEDIA_SOURCE",
$db);
$db->execute("DROP TABLE MEDIA_SOURCE_OLD");
}
/**
* Upgrades a Version 21 version of the Yioop database to a Version 22 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion22(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("INSERT INTO GROUP_THREAD_VIEWS
SELECT DISTINCT PARENT_ID, 1 FROM GROUP_ITEM WHERE
NOT EXISTS (SELECT THREAD_ID
FROM GROUP_THREAD_VIEWS WHERE THREAD_ID=PARENT_ID)");
$db->execute("ALTER TABLE LOCALE ADD ACTIVE $integer DEFAULT 1");
}
/**
* Upgrades a Version 22 version of the Yioop database to a Version 23 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion23(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("ALTER TABLE GROUPS ADD POST_LIFETIME $integer DEFAULT ".
FOREVER);
}
/**
* Upgrades a Version 23 version of the Yioop database to a Version 24 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion24(&$db)
{
$profile_model = new M\ProfileModel(C\p('DB_NAME'), false);
$profile_model->db = $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')];
$profile_model->initializeSql($db, $dbinfo);
foreach ($profile_model->create_statements as $object_name => $statement) {
if (stristr($object_name, "_INDEX")) {
if (!$db->execute($statement)) {
echo $statement." ERROR!";
exit();
}
} else {
if (!$db->execute("ALTER TABLE $object_name RENAME TO " .
$object_name . "_OLD")) {
echo "RENAME $object_name ERROR!";
exit();
}
if (!$db->execute($statement)) {
echo $statement." ERROR!";
exit();
}
D\DatasourceManager::copyTable($object_name."_OLD", $db,
$object_name, $db);
$db->execute("DROP TABLE ".$object_name."_OLD");
}
}
}
/**
* Upgrades a Version 24 version of the Yioop database to a Version 25 version
* This version upgrade includes creation of Help group that holds help pages.
* Help Group is created with GROUP_ID=HELP_GROUP_ID. If a Group with
* Group_ID=HELP_GROUP_ID already exists,
* then that GROUP is moved to the end of the GROUPS table(Max group id is
* used).
*
* @param object $db data source to use to upgrade
*/
function upgradeDatabaseVersion25(&$db)
{
/** For reading HELP_GROUP_ID**/
$sql = "SELECT COUNT(*) AS NUM FROM GROUPS WHERE GROUP_ID=" .
C\HELP_GROUP_ID;
$result = $db->execute($sql);
$row = ($db->fetchArray($result));
$is_taken = intval($row['NUM']);
if ($is_taken > 0) {
//Get the max group Id , increment it to push the old group
$sql = "SELECT MAX(GROUP_ID) AS MAX_GROUP_ID FROM GROUPS";
$result = $db->execute($sql);
$row = $db->fetchArray($result);
$max_group_id = $row['MAX_GROUP_ID'] + 1;
$tables_to_update_group_id = ["GROUPS", "GROUP_ITEM",
"GROUP_PAGE", "GROUP_PAGE_HISTORY", "USER_GROUP"];
foreach ($tables_to_update_group_id as $table) {
$sql = "UPDATE $table "
. "Set GROUP_ID=$max_group_id "
. "WHERE "
. "GROUP_ID=" . C\HELP_GROUP_ID;
$db->execute($sql);
}
}
//Insert the Help Group
$creation_time = microTimestamp();
$sql = "INSERT INTO GROUPS VALUES(" . C\HELP_GROUP_ID . ",'Help','"
. $creation_time . "','" . C\ROOT_ID . "',
'" . C\PUBLIC_BROWSE_REQUEST_JOIN . "', '" . C\GROUP_READ_WIKI . "',
" . C\UP_DOWN_VOTING_GROUP . ", " . C\FOREVER . ")";
$db->execute($sql);
$now = time();
$db->execute("INSERT INTO USER_GROUP VALUES (" . C\ROOT_ID . ", " .
C\HELP_GROUP_ID . ", " . ACTIVE_STATUS . ", $now)");
$db->execute("INSERT INTO USER_GROUP VALUES (" . C\PUBLIC_USER_ID . ", " .
C\HELP_GROUP_ID . ", " . C\ACTIVE_STATUS . ", $now)");
//Insert into Groups
$help_pages = getWikiHelpPages();
foreach ($help_pages as $page_name => $page_content) {
$page_content = str_replace("&", "&", $page_content);
$page_content = @htmlentities($page_content, ENT_QUOTES, "UTF-8");
$group_model = new M\GroupModel(C\p('DB_NAME'), false);
$group_model->db = $db;
$group_model->setPageName(C\ROOT_ID, C\HELP_GROUP_ID, $page_name,
$page_content, "en-US", "Creating Default Pages", "$page_name "
. "Help Page Created!", "Discuss the page in this thread!");
}
}
/**
* Upgrades a Version 25 version of the Yioop database to a Version 26 version
* This version upgrade includes updation fo the Help pages in the database to
* work with the changes to the way Hyperlinks are specified in wiki markup.
* The changes were implemented to point all articles with page names
* containing %20 to be able to work with '_' and vice versa.
* @param object $db data source to use to upgrade
*/
function upgradeDatabaseVersion26(&$db)
{
//Delete all existing pages in Help group
$params = [C\HELP_GROUP_ID];
$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);
//Insert the Help Group pages with corrected titles
$creation_time = microTimestamp();
$sql = "INSERT INTO GROUPS VALUES(" . C\HELP_GROUP_ID . ",'Help','"
. $creation_time . "','" . C\ROOT_ID . "',
'" . C\PUBLIC_BROWSE_REQUEST_JOIN . "', '" . C\GROUP_READ_WIKI . "',
" . C\UP_DOWN_VOTING_GROUP . ", " . C\FOREVER . ")";
$db->execute($sql);
$now = time();
$db->execute("INSERT INTO USER_GROUP VALUES (" . C\ROOT_ID . ", " .
C\HELP_GROUP_ID . ", " . C\ACTIVE_STATUS . ", $now)");
$db->execute("INSERT INTO USER_GROUP VALUES (" . C\PUBLIC_USER_ID . ", " .
C\HELP_GROUP_ID . ", " . C\ACTIVE_STATUS . ", $now)");
//Insert into Groups
$help_pages = getWikiHelpPages();
foreach ($help_pages as $page_name => $page_content) {
$page_content = str_replace("&", "&", $page_content);
$page_content = @htmlentities($page_content, ENT_QUOTES, "UTF-8");
$group_model = new M\GroupModel(C\p('DB_NAME'), false);
$group_model->db = $db;
$group_model->setPageName(C\ROOT_ID, C\HELP_GROUP_ID, $page_name,
$page_content, "en-US", "Creating Default Pages", "$page_name "
. "Help Page Created!", "Discuss the page in this thread!");
}
}
/**
* Upgrades a Version 26 version of the Yioop database to a Version 27 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion27(&$db)
{
$db->execute("ALTER TABLE GROUP_ITEM ADD COLUMN EDIT_DATE
NUMERIC(".C\TIMESTAMP_LEN.")");
}
/**
* Upgrades a Version 27 version of the Yioop database to a Version 28 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion28(&$db)
{
$db->execute("ALTER TABLE FEED_ITEM ADD COLUMN IMAGE_LINK
VARCHAR(".C\MAX_URL_LEN.")");
}
/**
* Upgrades a Version 28 version of the Yioop database to a Version 29 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion29(&$db)
{
$sql = "INSERT INTO LOCALE (LOCALE_NAME, LOCALE_TAG,
WRITING_MODE, ACTIVE) VALUES (?, ?, ?, ?)";
$db->execute($sql, ["Nederlands", "nl", "lr-tb", 1]);
$nl_translations = [
'db_activity_manage_account' => 'Account Beheren',
'db_activity_manage_users' => 'Gebruikers beheren',
'db_activity_manage_roles' => 'Rollen beheren',
'db_activity_manage_groups' => 'Groepen beheren',
'db_activity_manage_crawl' => 'Beheer Crawl',
'db_activity_mix_crawls' => 'Mix Crawls',
'db_activity_group_feeds' => 'Feeds en Wikis',
'db_activity_manage_classifiers' => 'Beheer Classifiers',
'db_activity_file_options' => 'Opties voor de pagina',
'db_activity_results_editor' => 'Resultaten Editor',
'db_activity_search_services' => 'Zoek Bronnen',
'db_activity_manage_machines' => 'Beheer Machines',
'db_activity_manage_locales' => 'Beheer varianten',
'db_activity_server_settings' => 'Server Settings',
'db_activity_security' => 'Veiligheid',
'db_activity_configure' => 'Configureren',
'db_subsearch_images' => 'Beelden',
'db_subsearch_videos' => 'Videos',
'db_subsearch_news' => 'Nieuws',
];
foreach ($nl_translations as $identifier => $translation) {
updateTranslationForStringId($db, $identifier, 'nl', $translation);
}
}
/**
* Upgrades a Version 29 version of the Yioop database to a Version 30 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion30(&$db)
{
$db->execute("CREATE TABLE MEDIA_UPDATER_PROPERTIES(
NAME VARCHAR(". C\NAME_LEN .") NOT NULL, VALUE VARCHAR(".
C\NAME_LEN.") NOT NULL)");
$db->execute("INSERT INTO MEDIA_UPDATER_PROPERTIES
VALUES ('PRESENT_ON_NAME_SERVER','true')");
}
/**
* Upgrades a Version 30 version of the Yioop database to a Version 31 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion31(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("DROP TABLE MEDIA_UPDATER_PROPERTIES");
$db->execute("DROP TABLE ACTIVE_FETCHER");
$db->execute("CREATE TABLE ACTIVE_PROCESS (NAME VARCHAR(". C\NAME_LEN.
"), ID $integer, TYPE VARCHAR(" . C\NAME_LEN . "))");
$profile_model = new M\ProfileModel(C\p('DB_NAME'), false);
$profile['MEDIA_MODE'] = "name_server";
$profile_model->updateProfile(C\WORK_DIRECTORY, [], $profile);
}
/**
* Upgrades a Version 31 version of the Yioop database to a Version 32 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion32(&$db)
{
$role_model = new M\RoleModel(C\p('DB_NAME'), false);
$role_model->db = $db;
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$auto_increment = $db->autoIncrement($dbinfo);
$db->execute("ALTER TABLE USERS ADD COLUMN IS_ADVERTISER $integer");
$db->execute("CREATE TABLE ADVERTISEMENTS(ADV_ID $integer
PRIMARY KEY $auto_increment, USER_ID $integer,
ADV_NAME VARCHAR(". C\ADVERTISEMENT_NAME_LEN ."),
ADV_DESCRIPTION VARCHAR(". C\ADVERTISEMENT_TEXT_LEN ."),
ADV_DESTINATION VARCHAR(". C\ADVERTISEMENT_DESTINATION_LEN ."),
ADV_KEYWORDS VARCHAR(". C\ADVERTISEMENT_KEYWORD_LEN ."),
STATUS $integer, ADV_BUDGET $integer,
ADV_DATE VARCHAR(". C\ADVERTISEMENT_DATE_LEN ."))");
$db->execute("CREATE TABLE ADVERTISEMENT_KEYWORD
(ID $integer PRIMARY KEY $auto_increment,
KEYWORD VARCHAR(". C\ADVERTISEMENT_KEYWORD_LEN ."),
BID_AMOUNT $integer,
BID_DATE VARCHAR(". C\ADVERTISEMENT_DATE_LEN ."))");
$db->execute("CREATE TABLE ADVERTISEMENT_METADATA
(ADV_ID $integer, CLICKS $integer, IMPRESSIONS $integer)");
addActivityAtId($db, 'db_activity_manage_advertisements',
"manageAdvertisements", 17);
updateTranslationForStringId($db, 'db_activity_manage_advertisements',
'en-US', 'Manage Advertisements');
$role_model->addRole('Business User');
$role_id = $role_model->getRoleId('Business User');
$role_model->addActivityRole($role_id, 17);
}
/**
* Upgrades a Version 32 version of the Yioop database to a Version 33 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion33(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$auto_increment = $db->autoIncrement($dbinfo);
$db->execute("DROP TABLE ADVERTISEMENTS");
$db->execute("DROP TABLE ADVERTISEMENT_KEYWORD");
$db->execute("DROP TABLE ADVERTISEMENT_METADATA");
$db->execute("CREATE TABLE ADVERTISEMENT(AD_ID $integer
PRIMARY KEY $auto_increment, USER_ID $integer,
AD_NAME VARCHAR(". C\ADVERTISEMENT_NAME_LEN ."),
AD_DESCRIPTION VARCHAR(". C\ADVERTISEMENT_TEXT_LEN ."),
AD_DESTINATION VARCHAR(". C\ADVERTISEMENT_DESTINATION_LEN ."),
AD_KEYWORDS VARCHAR(". C\ADVERTISEMENT_KEYWORD_LEN ."),
STATUS $integer, AD_BUDGET $integer,
AD_DATE VARCHAR(". C\ADVERTISEMENT_DATE_LEN ."))");
$db->execute("CREATE TABLE ADVERTISEMENT_KEYWORD
(ID $integer PRIMARY KEY $auto_increment,
KEYWORD VARCHAR(". C\ADVERTISEMENT_KEYWORD_LEN ."),
BID_AMOUNT $integer,
BID_DATE VARCHAR(". C\ADVERTISEMENT_DATE_LEN ."))");
$db->execute("CREATE TABLE ADVERTISEMENT_METADATA
(AD_ID $integer, CLICKS $integer, IMPRESSIONS $integer)");
}
/**
* Upgrades a Version 33 version of the Yioop database to a Version 34 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion34(&$db)
{
addActivityAtId($db, 'db_activity_appearance',
"appearance", 16);
updateTranslationForStringId($db, 'db_activity_appearance',
'en-US', 'Appearance');
updateTranslationForStringId($db, 'db_activity_appearance',
'fr-FR', 'Aspect');
updateTranslationForStringId($db, 'db_activity_appearance',
'nl', 'Verschijning');
}
/**
* Upgrades a Version 34 version of the Yioop database to a Version 35 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion35(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("CREATE TABLE CREDIT_LEDGER
(USER_ID $integer, AMOUNT $integer, TYPE VARCHAR(" .
C\NAME_LEN . "), BALANCE $integer, TIMESTAMP NUMERIC(" .
C\TIMESTAMP_LEN . "))");
addActivityAtId($db, 'db_activity_manage_credits',
"manageCredits", 18);
updateTranslationForStringId($db, 'db_activity_manage_credits',
'en-US', 'Manage Credits');
$role_model = new M\RoleModel(C\p('DB_NAME'), false);
$role_model->db = $db;
$activity_model = new M\ActivityModel(C\p('DB_NAME'), false);
$activity_model->db = $db;
$role_id = $role_model->getRoleId('Business User');
$activity_id = $activity_model->getActivityIdFromMethodName(
"manageCredits");
$role_model->addActivityRole($role_id, $activity_id);
$activity_id = $activity_model->getActivityIdFromMethodName(
"manageAdvertisements");
$role_model->addActivityRole($role_id, $activity_id);
}
/**
* Upgrades a Version 35 version of the Yioop database to a Version 36 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion36(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$auto_increment = $db->autoIncrement($dbinfo);
$db->execute("DROP TABLE ADVERTISEMENT");
$db->execute("DROP TABLE ADVERTISEMENT_KEYWORD");
$db->execute("DROP TABLE ADVERTISEMENT_METADATA");
$db->execute("DELETE FROM CREDIT_LEDGER");
$db->execute("CREATE TABLE ADVERTISEMENT (ID $integer
PRIMARY KEY $auto_increment, USER_ID $integer,
NAME VARCHAR(". C\ADVERTISEMENT_NAME_LEN ."),
DESCRIPTION VARCHAR(". C\ADVERTISEMENT_TEXT_LEN ."),
DESTINATION VARCHAR(". C\ADVERTISEMENT_DESTINATION_LEN ."),
KEYWORDS VARCHAR(". C\ADVERTISEMENT_KEYWORD_LEN ."),
STATUS $integer, BUDGET $integer, CLICKS $integer,
IMPRESSIONS $integer, START_DATE VARCHAR(".
C\ADVERTISEMENT_DATE_LEN ."), END_DATE VARCHAR(".
C\ADVERTISEMENT_DATE_LEN ."))");
$db->execute("CREATE TABLE ACCEPTED_AD_BIDS
(AD_ID $integer, KEYWORD VARCHAR(". C\ADVERTISEMENT_KEYWORD_LEN
."), BID_AMOUNT $integer, BID_DATE VARCHAR(".
C\ADVERTISEMENT_DATE_LEN ."))");
}
/**
* Upgrades a Version 36 version of the Yioop database to a Version 37 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion37(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("CREATE TABLE ITEM_IMPRESSION(
USER_ID $integer, ITEM_ID $integer, ITEM_TYPE $integer,
VIEW_DATE NUMERIC(" . C\TIMESTAMP_LEN . "))");
$db->execute("CREATE TABLE ITEM_IMPRESSION_SUMMARY(
USER_ID $integer, ITEM_ID $integer, ITEM_TYPE $integer,
NUM_ALL_TIME $integer, NUM_YEAR $integer, NUM_MONTH $integer,
NUM_DAY $integer, PRIMARY KEY(USER_ID, ITEM_ID, ITEM_TYPE))");
$db->execute("CREATE TABLE GROUP_PAGE_LINK(
LINK_TYPE_ID $integer, FROM_ID $integer,
TO_ID $integer)");
$db->execute("INSERT INTO ITEM_IMPRESSION_SUMMARY
SELECT ".C\PUBLIC_USER_ID.", THREAD_ID, ".C\THREAD_IMPRESSION.",
NUM_VIEWS, 0, 0, 0 FROM GROUP_THREAD_VIEWS");
}
/**
* Upgrades a Version 37 version of the Yioop database to a Version 38 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion38(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("DROP TABLE GROUP_THREAD_VIEWS");
$db->execute("CREATE TABLE ITEM_IMPRESSION_SUMMARY_OLD(
USER_ID $integer, ITEM_ID $integer, ITEM_TYPE $integer,
NUM_ALL_TIME $integer, NUM_YEAR $integer, NUM_MONTH $integer,
NUM_DAY $integer, PRIMARY KEY(USER_ID, ITEM_ID, ITEM_TYPE,
UPDATE_TIMESTAMP))");
D\DatasourceManager::copyTable("ITEM_IMPRESSION_SUMMARY", $db,
"ITEM_IMPRESSION_SUMMARY_OLD", $db);
$db->execute("DROP TABLE ITEM_IMPRESSION_SUMMARY");
$db->execute("CREATE TABLE ITEM_IMPRESSION_SUMMARY(
USER_ID $integer, ITEM_ID $integer, ITEM_TYPE $integer,
UPDATE_PERIOD $integer,
UPDATE_TIMESTAMP NUMERIC(" . C\TIMESTAMP_LEN . "),
NUM_VIEWS $integer, PRIMARY KEY(USER_ID, ITEM_ID, ITEM_TYPE,
UPDATE_PERIOD))");
$db->execute("INSERT INTO ITEM_IMPRESSION_SUMMARY
SELECT USER_ID, ITEM_ID, ITEM_TYPE, ".C\FOREVER.", 0, NUM_ALL_TIME AS
NUM_VIEWS FROM ITEM_IMPRESSION_SUMMARY_OLD");
}
/**
* Upgrades a Version 38 version of the Yioop database to a Version 39 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion39(&$db)
{
$role_model = new M\RoleModel(C\p('DB_NAME'), false);
$role_model->db = $db;
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$db->execute("CREATE TABLE CMS_DETECTORS (TIMESTAMP NUMERIC(" .
C\TIMESTAMP_LEN . ") PRIMARY KEY NOT NULL, NAME VARCHAR(" .
C\TITLE_LEN . "), HEADER VARCHAR(" . C\TITLE_LEN . ")," .
"IMPORTANT_CONTENT VARCHAR(" . C\TITLE_LEN . "))");
addActivityAtId($db, 'db_activity_cms_detectors',
"cmsDetectors", 20);
updateTranslationForStringId($db, 'db_activity_cms_detectors',
'en-US', 'CMS Detectors');
$role_id = $role_model->getRoleId('Admin');
$role_model->addActivityRole($role_id, 20);
}
/**
* Upgrades a Version 39 version of the Yioop database to a Version 40 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion40(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("CREATE TABLE GROUP_PAGE_PRE_LINK(
LINK_TYPE_ID $integer, FROM_ID $integer,
TO_GROUP_ID $integer, TO_PAGE_NAME VARCHAR(" . C\TITLE_LEN .
"))");
$db->execute("CREATE INDEX GP_PRE_INDEX ON GROUP_PAGE_PRE_LINK
(TO_GROUP_ID, TO_PAGE_NAME)");
}
/**
* Upgrades a Version 40 version of the Yioop database to a Version 41 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion41(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$auto_increment = $db->autoIncrement($dbinfo);
$serial = $db->serialType($dbinfo);
$db->execute("CREATE TABLE QUERY_ITEM (ID $serial PRIMARY KEY
$auto_increment, QUERY_HASH CHAR(".
C\TIMESTAMP_LEN . ") UNIQUE, QUERY VARCHAR(" . C\MAX_QUERY_LEN
."), CREATION NUMERIC(" . C\TIMESTAMP_LEN . "))");
$db->execute("CREATE INDEX QI_QUERY_HASH_INDEX ON
QUERY_ITEM (QUERY_HASH)");
}
/**
* Upgrades a Version 41 version of the Yioop database to a Version 42 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion42(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("DROP TABLE ITEM_IMPRESSION_SUMMARY");
$db->execute("CREATE TABLE ITEM_IMPRESSION_SUMMARY(
USER_ID $integer, ITEM_ID $integer, ITEM_TYPE $integer,
UPDATE_PERIOD $integer,
UPDATE_TIMESTAMP NUMERIC(" . C\TIMESTAMP_LEN . "),
NUM_VIEWS $integer, PRIMARY KEY(USER_ID, ITEM_ID, ITEM_TYPE,
UPDATE_PERIOD, UPDATE_TIMESTAMP))");
}
/**
* Upgrades a Version 42 version of the Yioop database to a Version 43 version
* @param object $db datasource to use to upgrade.
*/
function upgradeDatabaseVersion43(&$db)
{
$db->execute("INSERT INTO ITEM_IMPRESSION_SUMMARY
SELECT DISTINCT I.USER_ID AS USER_ID,
I.ITEM_ID AS ITEM_ID, I.ITEM_TYPE AS ITEM_TYPE,
" . C\FOREVER . " AS UPDATE_PERIOD,
0 AS UPDATE_TIMESTAMP,
0 AS NUM_VIEWS
FROM ITEM_IMPRESSION I WHERE I.ITEM_ID > 0 AND NOT EXISTS
(SELECT * FROM ITEM_IMPRESSION_SUMMARY IIS
WHERE IIS.USER_ID = I.USER_ID AND
IIS.ITEM_TYPE= I.ITEM_TYPE AND IIS.ITEM_ID= I.ITEM_ID
AND IIS.UPDATE_PERIOD = ".C\FOREVER."
)");
}
/**
* Upgrades a Version 43 version of the Yioop database to a Version 44 version
* @param object $db datasource to use to upgrade.
*/
function upgradeDatabaseVersion44(&$db)
{
$role_model = new M\RoleModel(C\p('DB_NAME'), false);
$role_model->db = $db;
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$auto_increment = $db->autoIncrement($dbinfo);
$serial = $db->serialType($dbinfo);
$sql = "SELECT ACTIVITY_ID FROM ACTIVITY ".
"WHERE METHOD_NAME = 'cmsDetectors'";
$result = $db->execute($sql, []);
if ($result) {
$row = $db->fetchArray($result);
if (!empty($row['ACTIVITY_ID'])) {
$sql = "DELETE FROM ROLE_ACTIVITY WHERE ACTIVITY_ID=?";
$db->execute($sql, [$row['ACTIVITY_ID']]);
$sql = "DELETE FROM ACTIVITY WHERE ACTIVITY_ID=?";
$db->execute($sql, [$row['ACTIVITY_ID']]);
}
}
$sql = "SELECT TRANSLATION_ID FROM TRANSLATION ".
"WHERE IDENTIFIER_STRING = 'db_activity_cms_detectors' " .
$db->limitOffset(1);
$result = $db->execute($sql, []);
if ($result) {
$row = $db->fetchArray($result);
$translate_id = $row['TRANSLATION_ID'];
$sql = "DELETE FROM TRANSLATION WHERE TRANSLATION_ID=?";
$db->execute($sql, [$translate_id]);
$sql = "DELETE FROM TRANSLATION_LOCALE WHERE TRANSLATION_ID=?";
$db->execute($sql, [$translate_id]);
}
$sql = "SELECT ACTIVITY_ID FROM ACTIVITY ".
"WHERE METHOD_NAME = 'scrapers'";
$result = $db->execute($sql, []);
if ($result) {
$row = $db->fetchArray($result);
if ($row) { //activity already exists
return;
}
}
$db->execute("CREATE TABLE SCRAPER (ID $serial PRIMARY KEY
$auto_increment, NAME VARCHAR(" .
C\TITLE_LEN . ") UNIQUE, SIGNATURE VARCHAR(" . C\TITLE_LEN . ")," .
"SCRAPE_RULES VARCHAR(" . C\TITLE_LEN . "))");
addActivityAtId($db, 'db_activity_scrapers',
"scrapers", 20);
updateTranslationForStringId($db, 'db_activity_scrapers',
'en-US', 'Web Scrapers');
$role_id = $role_model->getRoleId('Admin');
$role_model->addActivityRole($role_id, 20);
}
/**
* Upgrades a Version 44 version of the Yioop database to a Version 45 version
* @param object $db datasource to use to upgrade.
*/
function upgradeDatabaseVersion45(&$db)
{
$sql = "DELETE FROM SCRAPER";
$db->execute($sql);
$sql = "INSERT INTO SCRAPER(NAME, SIGNATURE, SCRAPE_RULES) VALUES (?,?,?)";
$scrapers = [
["YIOOP", "/html/head/*[contains(@href,".
"'c=resource&a=get&f=css&n=auxiliary.css')]",
"//div[contains(@class, 'body-container')]###" .
"//*[contains(@id, 'message')]###//*[contains(@id, 'help')]###" .
"//*[contains(@id, 'MathJax')]###" .
"//*[contains(@class, 'component-container')]###" .
"//*[contains(@class, 'top-bar')]###".
"//*[contains(@class, 'query-statistics')]###" .
"//*[contains(@class, 'admin-collapse')]###" .
"//option[not(contains(@selected, 'selected'))]###" .
"//*[contains(@id, 'suggest')]###//*[contains(@id, 'spell')]"],
["DRUPAL", "/html/head/*[contains(@href, '/sites/all/themes') or " .
"contains(@href, '/sites/default/files') or ".
"contains(@content, 'Drupal')]",
"//div[@id='page']|//main" .
"###//*[contains(@id,'comments')]" .
"###//*[contains(@id,'respond')]" .
"###//*[contains(@class,'bottomcontainerBox')]" .
"###//*[contains(@class,'post-by')]" .
"###//*[contains(@class,'entry meta-clear')]"],
["MEDIAWIKI", "//meta[contains(@content, 'MediaWiki')]",
"//*[contains(@id, 'mw-content-text')]###".
"//*[contains(@class, 'nmbox')]###" .
"//*[contains(@class, 'hatnote')]###" .
"//*[contains(@class, 'infobox')]"],
["VBULLETIN", "/html/head/*[contains(@href,'vbulletin')]",
"//div[contains(@class, 'body_wrapper')]###" .
"//*[contains(@id, 'above')]###//*[contains(@id, 'below')]###" .
"//*[contains(@id, 'breadcrumb')]###" .
"//*[contains(@id, 'notices')]###" .
"//*[contains(@id, 'footer')]###".
"//*[contains(@id, 'forum_info_options')]###" ."
//*[contains(@class, 'threadlisthead')]###" ."
//*[contains(@class, 'threaddetails')]###".
"//*[contains(@id, 'pagination')]###".
"//*[contains(@class, 'threadstats')]###".
"//*[contains(@class, 'threadlastpost')]###".
"//span[contains(@class, 'label')]"],
["WORDPRESS", "/html/head/*[contains(@href, 'wp-content')".
" or contains(@href, 'wp-includes')]",
"//div[starts-with(@id, 'post-') and " .
"'post-' = translate(@id, '0123456789', '') and " .
"string-length(@id) >4]|//div[contains(@class, 'homepagewrapper')]".
"###//*[contains(@id, 'entry-comments')]###" .
"//*[contains(@class, 'sharedaddy')]###" .
"//*[contains(@class, 'blog-subscribe')]###".
"//*[contains(@id, 'entry-side')]"]
];
foreach ($scrapers as $scraper) {
$db->execute($sql, $scraper);
}
}
/**
* Upgrades a Version 45 version of the Yioop database to a Version 46 version
* @param object $db datasource to use to upgrade.
*/
function upgradeDatabaseVersion46(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$serial = $db->serialType($dbinfo);
$auto_increment = $db->autoIncrement($dbinfo);
$db->execute("CREATE TABLE PAGE_RELATIONSHIP (
ID $serial PRIMARY KEY $auto_increment, NAME VARCHAR(" .
C\NAME_LEN . ") UNIQUE)");
$db->execute("INSERT INTO PAGE_RELATIONSHIP VALUES (-1,
'generic_links')");
}
/**
* Upgrades a Version 46 version of the Yioop database to a Version 47 version
* @param object $db datasource to use to upgrade.
*/
function upgradeDatabaseVersion47(&$db)
{
$role_model = new M\RoleModel(C\p('DB_NAME'), false);
$role_model->db = $db;
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("CREATE TABLE CHAT_BOT (
USER_ID $integer PRIMARY KEY, BOT_TOKEN CHAR(". C\TIMESTAMP_LEN .
") UNIQUE, CALLBACK_URL VARCHAR(" . C\MAX_URL_LEN . "))");
$role_model->addRole('Bot User');
}
/**
* Upgrades a Version 47 version of the Yioop database to a Version 48 version
* @param object $db datasource to use to upgrade.
*/
function upgradeDatabaseVersion48(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("CREATE TABLE ITEM_IMPRESSION_SUMMARY_OLD(
USER_ID $integer, ITEM_ID $integer, ITEM_TYPE $integer,
UPDATE_PERIOD $integer,
UPDATE_TIMESTAMP NUMERIC(" . C\TIMESTAMP_LEN . "),
NUM_VIEWS $integer, PRIMARY KEY(USER_ID, ITEM_ID, ITEM_TYPE,
UPDATE_PERIOD, UPDATE_TIMESTAMP))");
D\DatasourceManager::copyTable("ITEM_IMPRESSION_SUMMARY", $db,
"ITEM_IMPRESSION_SUMMARY_OLD", $db);
$db->execute("DROP TABLE ITEM_IMPRESSION_SUMMARY");
$db->execute("CREATE TABLE ITEM_IMPRESSION_SUMMARY(
USER_ID $integer, ITEM_ID $integer, ITEM_TYPE $integer,
UPDATE_PERIOD $integer, UPDATE_TIMESTAMP NUMERIC(" .
C\TIMESTAMP_LEN . "), NUM_VIEWS $integer,
FUZZY_NUM_VIEWS $integer DEFAULT -1, TMP_NUM_VIEWS $integer DEFAULT -1,
PRIMARY KEY(USER_ID, ITEM_ID, ITEM_TYPE,
UPDATE_PERIOD, UPDATE_TIMESTAMP))");
$db->execute("INSERT INTO ITEM_IMPRESSION_SUMMARY (USER_ID,
ITEM_ID, ITEM_TYPE, UPDATE_PERIOD, UPDATE_TIMESTAMP, NUM_VIEWS)
SELECT USER_ID, ITEM_ID, ITEM_TYPE, UPDATE_PERIOD, UPDATE_TIMESTAMP,
NUM_VIEWS FROM ITEM_IMPRESSION_SUMMARY_OLD");
}
/**
* Upgrades a Version 48 version of the Yioop database to a Version 49 version
* @param object $db datasource to use to upgrade.
*/
function upgradeDatabaseVersion49(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("DROP TABLE GROUP_PAGE_LINK");
$db->execute("DROP TABLE GROUP_PAGE_PRE_LINK");
$db->execute("CREATE TABLE GROUP_PAGE_LINK( LINK_TYPE_ID $integer,
FROM_ID $integer, TO_ID $integer,
PRIMARY KEY(LINK_TYPE_ID, FROM_ID, TO_ID))");
$db->execute("CREATE TABLE GROUP_PAGE_PRE_LINK(LINK_TYPE_ID $integer,
FROM_ID $integer, TO_GROUP_ID $integer, TO_PAGE_NAME VARCHAR(" .
C\TITLE_LEN ."), PRIMARY KEY(LINK_TYPE_ID, FROM_ID, TO_GROUP_ID,
TO_PAGE_NAME))");
}
/**
* Upgrades a Version 49 version of the Yioop database to a Version 50 version
* @param object $db datasource to use to upgrade.
*/
function upgradeDatabaseVersion50(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$auto_increment = $db->autoIncrement($dbinfo);
$serial = $db->serialType($dbinfo);
$private_db_class = C\NS_DATASOURCES .
ucfirst(C\p('PRIVATE_DBMS'))."Manager";
$private_dbinfo = ["DBMS" => C\p('PRIVATE_DBMS'),
"DB_HOST" => C\p('PRIVATE_DB_HOST'),
"DB_NAME" => C\p('PRIVATE_DB_NAME'),
"DB_PASSWORD" => C\p('PRIVATE_DB_PASSWORD')];
$private_db = new $private_db_class();
$private_db->connect(C\p('PRIVATE_DB_HOST'), C\p('PRIVATE_DB_USER'),
C\p('PRIVATE_DB_PASSWORD'), C\p('PRIVATE_DB_NAME'));
$private_auto_increment = $private_db->autoIncrement($private_dbinfo);
$private_serial = $private_db->serialType($private_dbinfo);
$private_db->execute("CREATE TABLE TYPE_KEYS (KEY_ID
$private_serial PRIMARY KEY $private_auto_increment,
TYPE_ID $integer, KEY_NAME VARCHAR(30) )");
$db->execute("CREATE TABLE GROUPS_OLD (GROUP_ID $serial PRIMARY KEY
$auto_increment, GROUP_NAME VARCHAR(" . C\SHORT_TITLE_LEN."),
CREATED_TIME VARCHAR(" . C\MICROSECOND_TIMESTAMP_LEN . "),
OWNER_ID $integer, REGISTER_TYPE $integer,
MEMBER_ACCESS $integer, VOTE_ACCESS $integer DEFAULT ".
C\NON_VOTING_GROUP . ", POST_LIFETIME $integer DEFAULT ".
C\FOREVER . ")");
D\DatasourceManager::copyTable("GROUPS", $db,
"GROUPS_OLD", $db);
$db->execute("DROP TABLE GROUPS");
$db->execute("CREATE TABLE GROUPS (GROUP_ID $serial PRIMARY KEY
$auto_increment, GROUP_NAME VARCHAR(" . C\SHORT_TITLE_LEN."),
CREATED_TIME VARCHAR(" . C\MICROSECOND_TIMESTAMP_LEN . "),
OWNER_ID $integer, REGISTER_TYPE $integer,
MEMBER_ACCESS $integer, VOTE_ACCESS $integer DEFAULT ".
C\NON_VOTING_GROUP . ", POST_LIFETIME $integer DEFAULT ".
C\FOREVER . ", ENCRYPTION $integer DEFAULT 0)");
$db->execute("INSERT INTO GROUPS (GROUP_ID,
GROUP_NAME, CREATED_TIME, OWNER_ID, REGISTER_TYPE, MEMBER_ACCESS,
VOTE_ACCESS, POST_LIFETIME)
SELECT GROUP_ID, GROUP_NAME, CREATED_TIME, OWNER_ID, REGISTER_TYPE,
MEMBER_ACCESS, VOTE_ACCESS, POST_LIFETIME FROM GROUPS_OLD");
}
/**
* Upgrades a Version 50 version of the Yioop database to a Version 51 version
* @param object $db datasource to use to upgrade.
*/
function upgradeDatabaseVersion51(&$db)
{
$role_model = new M\RoleModel(C\p('DB_NAME'), false);
$role_model->db = $db;
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$auto_increment = $db->autoIncrement($dbinfo);
$serial = $db->serialType($dbinfo);
$sql = "SELECT ACTIVITY_ID FROM ACTIVITY ".
"WHERE METHOD_NAME = 'botStory'";
$result = $db->execute($sql, []);
if ($result) {
$row = $db->fetchArray($result);
if ($row) { //activity already exists
return;
}
}
addActivityAtId($db, 'db_activity_botstory',
"botStory", 21);
updateTranslationForStringId($db, 'db_activity_botstory',
'en-US', 'Bot Story');
$role_id = $role_model->getRoleId('Bot User');
$role_model->addActivityRole($role_id, 21);
$sql="CREATE TABLE INTENT(INTENT_ID $integer PRIMARY KEY $auto_increment,
INTENT VARCHAR(" . C\MAX_DESCRIPTION_LEN . "),USER_ID $integer)";
$db->execute($sql);
$db->execute("CREATE TABLE EXPRESSION(EXPRESSION_ID $integer
PRIMARY KEY $auto_increment,
EXPRESSION VARCHAR(" . C\MAX_DESCRIPTION_LEN ."),
INTENT_ID $integer)");
$db->execute("CREATE TABLE ENTITY(ENTITY_ID $integer
PRIMARY KEY $auto_increment,
ENTITY_NAME VARCHAR(" . C\MAX_DESCRIPTION_LEN ."),
USER_ID $integer)");
$db->execute("CREATE TABLE INTENT_EXPRESSION_ENTITY(ID $integer
PRIMARY KEY $auto_increment,
INTENT_ID $integer, EXPRESSION_ID $integer, ENTITY_ID $integer,
ENTITY_VALUE VARCHAR(" . C\MAX_DESCRIPTION_LEN ."),
PRIMARY KEY(INTENT_ID,EXPRESSION_ID,ENTITY_ID))");
$db->execute("CREATE TABLE BOT_TERM_FREQUENCY(TERM VARCHAR
(" . C\MAX_DESCRIPTION_LEN . "), FREQUENCY $integer,
INTENT_ID $integer, PRIMARY KEY(TERM, INTENT_ID))");
}
/**
* Upgrades a Version 51 version of the Yioop database to a Version 52 version
* @param object $db datasource to use to upgrade.
*/
function upgradeDatabaseVersion52(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("CREATE TABLE ITEM_IMPRESSION_STAT(ITEM_ID $integer,
ITEM_TYPE $integer, UPDATE_PERIOD $integer, SUM $integer DEFAULT -1,
FUZZY_NUM_VIEWS $integer DEFAULT -1)");
}
/**
* Upgrades a Version 52 version of the Yioop database to a Version 53 version
* @param object $db datasource to use to upgrade.
*/
function upgradeDatabaseVersion53(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("CREATE TABLE AVERAGE_RATING (AVG_RATING $integer)");
$db->execute("INSERT INTO AVERAGE_RATING VALUES (0)");
$db->execute("CREATE TABLE IDF (WORD_ID $integer, ITEM_IMP FLOAT,
USER_IMP FLOAT)");
$db->execute("CREATE TABLE IDF_TEMP (WORD_ID $integer, ITEM_IMP
FLOAT, USER_IMP FLOAT)");
$db->execute("CREATE TABLE ITEM_BIAS(ITEM_ID $integer,
ITEM_BIAS FLOAT)");
$db->execute("CREATE TABLE ITEM_WORD_FREQUENCY (ITEM_ID $integer,
WORD_ID $integer, FREQUENCY $integer, LOGFREQUENCY FLOAT)");
$db->execute("CREATE TABLE ITEM_WORD_WEIGHTS(WORD_ID $integer,
ITEM_ID $integer, WEIGHT FLOAT)");
$db->execute("CREATE TABLE LIVE_TABLE (TRENDING $integer,
THREADS $integer, GROUPS $integer)");
$db->execute("INSERT INTO LIVE_TABLE VALUES (1,1,1)");
$db->execute("CREATE TABLE PREDICTED_TABLE(USER_ID $integer,
ITEM_ID $integer, USER_BIAS FLOAT, ITEM_BIAS FLOAT, RATING FLOAT)");
$db->execute("CREATE TABLE RECOMMENDATION_LIST_GROUPS_1
(USER_ID $integer, GROUP_1 $integer, GROUP_2
$integer, GROUP_3 v, RECOMMEND_TYPE VARCHAR)");
$db->execute("CREATE TABLE RECOMMENDATION_LIST_GROUPS_2
(USER_ID $integer, GROUP_1 $integer, GROUP_2
$integer, GROUP_3 $integer, RECOMMEND_TYPE VARCHAR)");
$db->execute("CREATE TABLE RECOMMENDATION_LIST_THREADS_1
(USER_ID $integer, GROUP_1 $integer, GROUP_2 $integer, GROUP_3 $integer,
RECOMMEND_TYPE VARCHAR)");
$db->execute("CREATE TABLE RECOMMENDATION_LIST_THREADS_2
(USER_ID $integer, GROUP_1 $integer, GROUP_2 $integer, GROUP_3 $integer,
RECOMMEND_TYPE VARCHAR)");
$db->execute("CREATE TABLE RECOMMENDATION_TRENDING_THREADS_1
(USER_ID $integer, GROUP_1 $integer, GROUP_2 $integer, GROUP_3 $integer,
RECOMMEND_TYPE VARCHAR)");
$db->execute("CREATE TABLE RECOMMENDATION_TRENDING_THREADS_2
(USER_ID $integer, GROUP_1 $integer, GROUP_2 $integer, GROUP_3 $integer,
RECOMMEND_TYPE VARCHAR)");
$db->execute("CREATE TABLE SIMILARITY
(USER_ID $integer, ITEM_ID $integer, COSINE_SIMILARITY FLOAT)");
$db->execute("CREATE TABLE USER_BIAS
(USER_ID $integer, USER_BIAS FLOAT)");
$db->execute("CREATE TABLE USER_WORD_FREQUENCY
(FREQUENCY $integer, USER_ID $integer, WORD_ID $integer,
LOGFREQUENCY FLOAT)");
$db->execute("CREATE TABLE USER_WORD_FREQUENCY_TEMP
(FREQUENCY $integer, USER_ID $integer, WORD_ID $integer,
LOGFREQUENCY FLOAT)");
$db->execute("CREATE TABLE USER_WORD_WEIGHTS
(WORD_ID $integer, USER_ID $integer, WEIGHT FLOAT)");
$db->execute("CREATE TABLE USER_WORD_WEIGHTS_TEMP
(WORD_ID $integer, USER_ID $integer, WEIGHT FLOAT)");
$db->execute("CREATE TABLE WORDS
(WORD_ID $integer, WORD VARCHAR)");
$db->execute("CREATE TABLE WORDS_TEMP (WORD_ID $integer)");
$db->execute("CREATE TABLE SIMILARITY_TEMP
(USER_ID $integer, ITEM_ID $integer, COSINE_SIMILARITY FLOAT)");
$db->execute("CREATE TABLE USER_THREAD_GROUP
(USER_ID $integer, ITEM_ID $integer, GROUP_ID $integer)");
}
/**
* Upgrades a Version 53 version of the Yioop database to a Version 54 version
* @param object $db datasource to use to upgrade.
*/
function upgradeDatabaseVersion54(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$former_tables_which_should_be_deleted = [
"AVERAGE_RATING", "IDF", "IDF_TEMP", "ITEM_BIAS", "ITEM_RECOMMENDATION",
"ITEM_TERM_FREQUENCY", "ITEM_WORD_FREQUENCY", "ITEM_TERM_WEIGHTS",
"ITEM_USER_IDF", "ITEM_WORD_WEIGHTS", "LIVE_TABLE",
"PREDICTED_TABLE", "RECOMMENDATION_LIST_GROUPS_1",
"RECOMMENDATION_LIST_GROUPS_2", "RECOMMENDATION_LIST_GROUPS_3",
"RECOMMENDATION_LIST_THREADS_1", "RECOMMENDATION_LIST_THREADS_2",
"RECOMMENDATION_LIST_THREADS_3", "RECOMMENDATION_TRENDING_THREADS_1",
"RECOMMENDATION_TRENDING_THREADS_2",
"RECOMMENDATION_TRENDING_THREADS_3", "SIMILARITY", "USER_BIAS",
"USER_ITEM_SIMILARITY", "USER_TERM_FREQUENCY", "USER_TERM_WEIGHTS",
"USER_WORD_FREQUENCY", "USER_WORD_FREQUENCY_TEMP",
"USER_WORD_WEIGHTS", "USER_WORD_WEIGHTS_TEMP", "WORDS",
"WORD_TEMP", "SIMILARITY_TEMP", "USER_THREAD_GROUP"
];
foreach ($former_tables_which_should_be_deleted as $table) {
$db->execute("DROP TABLE IF EXISTS $table");
}
$new_tables_and_indexes_sql = [
"CREATE TABLE ITEM_RECOMMENDATION (ITEM_ID $integer,
USER_ID $integer, ITEM_TYPE $integer,
SCORE FLOAT, TIMESTAMP NUMERIC(" . C\TIMESTAMP_LEN . "))",
"CREATE INDEX IR_USER_ID_INDEX ON ITEM_RECOMMENDATION(USER_ID)",
"CREATE TABLE ITEM_TERM_FREQUENCY (ITEM_ID $integer, TERM_ID $integer,
FREQUENCY $integer, LOG_FREQUENCY FLOAT,
PRIMARY KEY(TERM_ID, TERM_ID))",
"CREATE INDEX ITF_TERM_ID_INDEX ON ITEM_TERM_FREQUENCY(TERM_ID)",
"CREATE TABLE ITEM_TERM_WEIGHTS (TERM_ID $integer, ITEM_ID $integer,
WEIGHT FLOAT, PRIMARY KEY(TERM_ID, ITEM_ID))",
"CREATE TABLE USER_ITEM_SIMILARITY (USER_ID $integer,
THREAD_ID $integer, SIMILARITY FLOAT, GROUP_MEMBER $integer,
PRIMARY KEY(USER_ID, THREAD_ID))",
"CREATE TABLE USER_TERM_FREQUENCY (USER_ID $integer, TERM_ID $integer,
FREQUENCY $integer, LOG_FREQUENCY FLOAT,
PRIMARY KEY(USER_ID, TERM_ID))",
"CREATE INDEX UTF_TERM_ID_INDEX ON USER_TERM_FREQUENCY(TERM_ID)",
"CREATE TABLE USER_TERM_WEIGHTS (TERM_ID $integer, USER_ID $integer,
WEIGHT FLOAT, PRIMARY KEY(TERM_ID, USER_ID))" ];
foreach ($new_tables_and_indexes_sql as $sql) {
$db->execute($sql);
}
}
/**
* Upgrades a Version 54 version of the Yioop database to a Version 55 version
* @param object $db datasource to use to upgrade.
*/
function upgradeDatabaseVersion55(&$db)
{
$db->execute("CREATE TABLE LEXICON(
TERM VARCHAR(". C\LONG_NAME_LEN ."),
LOCALE VARCHAR(" . C\NAME_LEN . "),
PART_OF_SPEECH VARCHAR(16), PRIMARY KEY(TERM, LOCALE))");
// Retrieve the locales added to the Locale table
$sql = "SELECT LOCALE_TAG from LOCALE";
$result = $db->execute($sql);
if ($result) {
$locales = $db->fetchArray($result);
}
/*
* Go through the locales, check of there is a lexicon,
* if present then add it to the Lexicon database.
* as (term, part_of_speech, locale)
*/
foreach ($locales as $locale) {
$folder_name = $locale;
if (strstr($locale, "-")) {
$locale_name = explode("-", $locale);
$folder_name = $locale_name . "_" . $locale_name;
}
$lexicon_file = C\LOCALE_DIR . "/" . $folder_name .
"/resources/lexicon.txt.gz";
if (file_exists($lexicon_file)) {
$lines = gzfile($lexicon_file);
$insert_values = "";
$count = 0;
foreach ($lines as $line) {
$line = trim($line, " ");
$line = explode(" ", $line);
$insert_values .= '(' . trim($line[0]) . ',' . $locale[0] .
',' . trim($line[1]) . '),';
$count++;
if ($count >= C\NUM_LEX_BULK_INSERTS) {
$insert_values = rtrim($insert_values, ',');
$query = "INSERT INTO LEXICON (TERM, LOCALE, PART_OF_SPEECH)
VALUES {$insert_values}";
$db->exec($query);
$insert_values = "";
$count = 0;
}
}
if ($count > 0) {
$insert_values = rtrim($insert_values, ',');
$query = "INSERT INTO LEXICON (TERM, LOCALE, PART_OF_SPEECH)
VALUES $insert_values";
$db->exec($query);
}
}
}
}
/**
* Upgrades a Version 56 version of the Yioop database to a Version 5 version
* @param object $db datasource to use to upgrade.
*/
function upgradeDatabaseVersion57(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$auto_increment = $db->autoIncrement($dbinfo);
$serial = $db->serialType($dbinfo);
if (ucfirst(C\p('DBMS')) == "Sqlite3" || str_starts_with(C\p('DB_HOST'),
'sqlite')) {
$db->execute("ALTER TABLE USERS RENAME TO USERS_OLD");
$db->execute("CREATE TABLE USERS(USER_ID $serial PRIMARY KEY
$auto_increment, FIRST_NAME VARCHAR(" . C\NAME_LEN."),
LAST_NAME VARCHAR(" . C\NAME_LEN . "), USER_NAME VARCHAR(" .
C\NAME_LEN .") UNIQUE, EMAIL VARCHAR(" . C\LONG_NAME_LEN . "),
PASSWORD VARCHAR(" . C\LONG_NAME_LEN . "), STATUS $integer,
HASH VARCHAR(" . C\LONG_NAME_LEN . "),
CREATION_TIME VARCHAR(" . C\MICROSECOND_TIMESTAMP_LEN .
"), UPS $integer DEFAULT 0,
DOWNS $integer DEFAULT 0)");
$db->execute("INSERT INTO USERS (USER_ID, FIRST_NAME, LAST_NAME,
USER_NAME, EMAIL, PASSWORD, STATUS, HASH, CREATION_TIME, UPS,
DOWNS)
SELECT USER_ID, FIRST_NAME, LAST_NAME, USER_NAME, EMAIL, PASSWORD,
STATUS, HASH, CREATION_TIME, UPS, DOWNS
FROM USERS_OLD");
} else {
// only one of these should do anything
$db->execute("ALTER TABLE USERS DROP COLUMN IS_ADVERTISER");
$db->execute("ALTER TABLE USERS DROP COLUMN USES_STORE");
}
}
/**
* Upgrades a Version 57 version of the Yioop database to a Version 58 version
* @param object $db datasource to use to upgrade.
*/
function upgradeDatabaseVersion58(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("ALTER TABLE MACHINE RENAME TO MACHINE_OLD");
$db->execute("CREATE TABLE MACHINE (NAME VARCHAR(" . C\NAME_LEN
.") PRIMARY KEY, URL VARCHAR(" . C\MAX_URL_LEN .
"), CHANNEL $integer, NUM_FETCHERS $integer, PARENT VARCHAR(" .
C\NAME_LEN . ") )");
D\DatasourceManager::copyTable("MACHINE_OLD", $db, "MACHINE",
$db);
$db->execute("DROP TABLE MACHINE_OLD");
}
/**
* Upgrades a Version 58 version of the Yioop database to a Version 59 version
* @param object $db datasource to use to upgrade.
*/
function upgradeDatabaseVersion59(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("DROP TABLE USER_SESSION");
$db->execute("CREATE TABLE USER_SESSION (
USER_ID $integer PRIMARY KEY, SESSION VARCHAR(" .
C\MAX_USER_SESSION_SIZE . ") )" );
}
/**
* Upgrades a Version 59 version of the Yioop database to a Version 60 version
* @param object $db datasource to use to upgrade.
* @return bool false when an INSERT into the new SCRAPER table fails
* during data migration; void on the success path
*/
function upgradeDatabaseVersion60(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$auto_increment = $db->autoIncrement($dbinfo);
$serial = $db->serialType($dbinfo);
$integer = $db->integerType($dbinfo);
$db->execute("DELETE FROM MEDIA_SOURCE WHERE TYPE='video'");
$db->execute("ALTER TABLE SCRAPER RENAME TO SCRAPER_OLD");
$db->execute("CREATE TABLE SCRAPER (ID $serial PRIMARY KEY " .
"$auto_increment, NAME VARCHAR(" . C\TITLE_LEN . "), " .
"PRIORITY $integer DEFAULT 0, " .
"SIGNATURE VARCHAR(" . C\MAX_URL_LEN . ")," .
"TEXT_PATH VARCHAR(" . C\MAX_URL_LEN . ") DEFAULT ''," .
"DELETE_PATHS VARCHAR(" . (10 *C\MAX_URL_LEN) . ") DEFAULT '', " .
"EXTRACT_FIELDS VARCHAR(" . (10 * C\MAX_URL_LEN) . ") DEFAULT '')");
$sql = "SELECT * FROM SCRAPER_OLD";
if (($result = $db->execute($sql)) !== false) {
while ($row = $db->fetchArray($result)) {
if (!str_contains($row['SCRAPE_RULES'], "###")) {
$row["TEXT_PATH"] = $row['SCRAPE_RULES'];
$row["DELETE_PATHS"] = "";
} else {
list($row["TEXT_PATH"], $paths) =
explode("###", $row['SCRAPE_RULES'], 2);
$row['DELETE_PATHS'] = trim(implode("\n", explode("###",
$paths)));
}
$row['PRIORITY'] = 0;
$row['EXTRACT_FIELDS'] = "";
unset($row['SCRAPE_RULES']);
$statement = "INSERT INTO SCRAPER (";
$comma = "";
foreach ($row as $col => $value) {
$statement .= $comma . $col;
$comma = ",";
}
$statement .= ") VALUES (";
$comma = "";
foreach ($row as $col => $value) {
$statement .= $comma . " '" . $db->escapeString($value) .
"'";
$comma = ",";
}
$statement .= ")";
if (($db->execute($statement)) === false) {
return false;
}
}
}
$sql = "INSERT INTO SCRAPER(NAME, SIGNATURE, TEXT_PATH, DELETE_PATHS,
EXTRACT_FIELDS) VALUES (?, ?, ?, ?, ?)";
$scrapers = [
["VIDEO SITE",
"//meta[@property='og:type' and contains(@content, 'video')]",
"", "", "IS_VIDEO=//meta[@property='og:type' and" .
" contains(@content, 'video')]/@content\n" .
"SITE_NAME=//meta[@property='og:site_name']/@content\n" .
"DURATION=//meta[@property='video:duration']/@content\n" .
"THUMB_URL=//meta[@property='og:image']/@content"],
];
foreach ($scrapers as $scraper) {
$db->execute($sql, $scraper);
}
$db->execute("DROP TABLE SCRAPER_OLD");
}
/**
* Upgrades a Version 60 version of the Yioop database to a Version 61 version
* @param object $db datasource to use to upgrade.
*/
function upgradeDatabaseVersion61(&$db)
{
$db->execute("ALTER TABLE MEDIA_SOURCE RENAME TO MEDIA_SOURCE_OLD");
$db->execute("CREATE TABLE MEDIA_SOURCE (
TIMESTAMP NUMERIC(" . C\TIMESTAMP_LEN . ") PRIMARY KEY,
NAME VARCHAR(" . C\LONG_NAME_LEN . "),
TYPE VARCHAR(" . C\NAME_LEN . "),
CATEGORY VARCHAR(" . C\NAME_LEN . ") DEFAULT 'news',
SOURCE_URL VARCHAR(" . C\MAX_URL_LEN . "), AUX_INFO VARCHAR(".
C\MAX_URL_LEN . "), LANGUAGE VARCHAR(" . C\NAME_LEN . "))");
$db->execute("INSERT INTO MEDIA_SOURCE (TIMESTAMP, NAME, TYPE,
SOURCE_URL, AUX_INFO, LANGUAGE)
SELECT TIMESTAMP, NAME, TYPE, SOURCE_URL, AUX_INFO, LANGUAGE
FROM MEDIA_SOURCE_OLD");
$db->execute("DROP TABLE MEDIA_SOURCE_OLD");
}
/**
* Upgrades a Version 61 version of the Yioop database to a Version 62 version
* @param object $db datasource to use to upgrade.
*/
function upgradeDatabaseVersion62(&$db)
{
$db->execute("CREATE TABLE TRENDING_TERM (
TERM VARCHAR(" . C\TITLE_LEN . "),
OCCURRENCES FLOAT DEFAULT 0,
UPDATE_PERIOD NUMERIC,
TIMESTAMP NUMERIC(" . C\TIMESTAMP_LEN . "),
LANGUAGE VARCHAR(" . C\NAME_LEN . ")
)");
}
/**
* Upgrades a Version 63 version of the Yioop database to a Version 64 version
* @param object $db datasource to use to upgrade.
*/
function upgradeDatabaseVersion64(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("DROP TABLE ITEM_IMPRESSION_STAT");
$db->execute("CREATE TABLE ITEM_IMPRESSION_STAT(ITEM_ID $integer,
ITEM_TYPE $integer, UPDATE_PERIOD $integer,
NUM_VIEWS $integer DEFAULT -1,
FUZZY_NUM_VIEWS $integer DEFAULT -1)");
}
/**
* Upgrades a Version 64 version of the Yioop database to a Version 65 version
* @param object $db datasource to use to upgrade.
*/
function upgradeDatabaseVersion65(&$db)
{
$db->execute("ALTER TABLE TRENDING_TERM ADD COLUMN CATEGORY VARCHAR(" .
C\TITLE_LEN . ") DEFAULT 'news'");
//Create a Group for Search Result Edits
$group_model = new M\GroupModel(C\p('DB_NAME'), false);
$group_model->db = $db;
$group_id = $group_model->getGroupId('Search');
if ($group_id <= 0) {
$group_model->addGroup('Search', C\ROOT_ID, ["root"],
C\PUBLIC_BROWSE_REQUEST_JOIN, C\GROUP_READ_WIKI,
C\UP_DOWN_VOTING_GROUP, C\FOREVER, 0);
$group_id = $group_model->getGroupId('Search');
if ($group_id <= 0) {
return; // upgrade fail
}
}
$group_model->addUserGroup(C\PUBLIC_USER_ID, $group_id);
$local_config_file = C\BASE_DIR . "/configs/LocalConfig.php";
if (file_exists($local_config_file)) {
$local_config = file_get_contents($local_config_file);
} else {
$local_config = "<";
$local_config .= <<< EOD
?php
/**
* Local configuration overrides
* @package configs
*/
namespace seekquarry\yioop\configs;
EOD;
}
if (strstr($local_config, 'SEARCH_GROUP_ID') === false) {
$local_config .= "\nnsdefine('SEARCH_GROUP_ID', $group_id);\n";
file_put_contents($local_config_file, $local_config);
}
}
/**
* Upgrades a Version 65 version of the Yioop database to a Version 66 version
* @param object $db datasource to use to upgrade.
*/
function upgradeDatabaseVersion66(&$db)
{
$db->execute("ALTER TABLE GROUP_ITEM ADD COLUMN URL VARCHAR(" .
C\TITLE_LEN . ") DEFAULT ''");
}
/**
* Upgrades a Version 66 version of the Yioop database to a Version 67 version
* @param object $db datasource to use to upgrade.
*/
function upgradeDatabaseVersion67(&$db)
{
$db->execute("ALTER TABLE ROLE_ACTIVITY ADD COLUMN " .
"ALLOWED_ARGUMENTS VARCHAR(" . C\MAX_URL_LEN . ") DEFAULT 'all'");
$db->execute("ALTER TABLE SUBSEARCH ADD COLUMN " .
"DEFAULT_QUERY VARCHAR(" . C\TITLE_LEN . ") DEFAULT ''");
}
/**
* Upgrades a Version 67 version of the Yioop database to a Version 68 version
* @param object $db datasource to use to upgrade.
*/
function upgradeDatabaseVersion68(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("ALTER TABLE MIX_COMPONENTS ADD COLUMN " .
"DIRECTION $integer DEFAULT 1");
$db->execute("DELETE FROM MIX_COMPONENTS WHERE TIMESTAMP = 4 AND
FRAGMENT_ID = 0");
$db->execute("INSERT INTO MIX_COMPONENTS(TIMESTAMP,
FRAGMENT_ID, CRAWL_TIMESTAMP, WEIGHT, DIRECTION, KEYWORDS)
VALUES (4, 0, 100, 1, -1, 'media:news')");
$db->execute("DROP TABLE FEED_ITEM");
if (file_exists(C\WORK_DIRECTORY . "/feeds")) {
$db->unlinkRecursive(C\WORK_DIRECTORY . "/feeds");
}
}
/**
* Upgrades a Version 68 version of the Yioop database to a Version 69 version
* @param object $db datasource to use to upgrade.
*/
function upgradeDatabaseVersion69(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$auto_increment = $db->autoIncrement($dbinfo);
$serial = $db->serialType($dbinfo);
$integer = $db->integerType($dbinfo);
$db->execute("ALTER TABLE USERS RENAME TO USERS_OLD");
$db->execute("CREATE TABLE USERS(USER_ID $serial PRIMARY KEY
$auto_increment, FIRST_NAME VARCHAR(" . C\NAME_LEN."),
LAST_NAME VARCHAR(" . C\NAME_LEN . "), USER_NAME VARCHAR(" .
C\NAME_LEN .") UNIQUE, EMAIL VARCHAR(" . C\LONG_NAME_LEN . "),
PASSWORD VARCHAR(" . C\LONG_NAME_LEN . "), STATUS $integer,
HASH VARCHAR(" . C\LONG_NAME_LEN . "),
CREATION_TIME VARCHAR(" . C\MICROSECOND_TIMESTAMP_LEN .
"), UPS $integer DEFAULT 0, DOWNS $integer DEFAULT 0)");
$db->execute("INSERT INTO USERS (USER_ID, FIRST_NAME, LAST_NAME,
USER_NAME, EMAIL, PASSWORD, STATUS, HASH, CREATION_TIME, UPS, DOWNS)
SELECT USER_ID, FIRST_NAME, LAST_NAME, USER_NAME, EMAIL, PASSWORD,
STATUS, HASH, CREATION_TIME, UPS, DOWNS
FROM USERS_OLD");
}
/**
* Upgrades a Version 69 version of the Yioop database to a Version 70 version
* @param object $db datasource to use to upgrade.
*/
function upgradeDatabaseVersion70(&$db)
{
$is_mysql = (stristr(C\p('DBMS'), 'Mysql') !== false);
$groups = ($is_mysql) ? "`GROUPS`" : "GROUPS";
if ($is_mysql) {
$db->execute("ALTER DATABASE " . C\p('DB_NAME') .
" CHARACTER SET utf8mb4");
}
$db->execute("ALTER TABLE $groups RENAME TO SOCIAL_GROUPS");
}
/**
* Upgrades a Version 70 version of the Yioop database to a Version 71 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion71(&$db)
{
$role_model = new M\RoleModel(C\p('DB_NAME'), false);
$role_model->db = $db;
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$auto_increment = $db->autoIncrement($dbinfo);
addActivityAtId($db, 'db_activity_user_messages',
"userMessages", 11);
$role_id = $role_model->getRoleId('User');
$role_model->addActivityRole($role_id, 11);
$role_id = $role_model->getRoleId('Admin');
$role_model->addActivityRole($role_id, 11);
$messages_translations = [ "ar" => 'رسائل', "bn" => 'বার্তাগুলি',
"de" => 'Meldungen', "en-US" => 'Messages', "es" => 'Mensajes',
"fa" => 'پیام', "fr-FR" => 'Messages', "he" => 'הודעות',
"hi" => 'संदेश', "id" => 'Pesan', "it" => 'Messaggi', "ja" => 'メッセージ',
"kn" => 'ಸಂದೇಶಗಳುು', "ko" => '메시지', "nl" => 'Berichten',
"pl" => 'Wiadomości', "pt" => 'Mensagens', "ru" => 'Сообщения',
"te" => 'సందేశాలుు', "th" => 'ข้อความ', "tl" => 'Mga Mensahe',
"tr" => 'Ileti', "vi-VN" => 'Thư', "zh-CN" => '私信', ];
foreach ($messages_translations as $locale => $translation) {
updateTranslationForStringId($db, 'db_activity_user_messages',
$locale, $translation);
}
}
/**
* Upgrades a Version 71 version of the Yioop database to a Version 72 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion72(&$db)
{
$db->execute("ALTER TABLE USERS ADD COLUMN AD_CREDITS_SERVICE_ACCOUNT
VARCHAR(" . C\AD_CREDITS_SERVICE_ACCOUNT_LEN . ")");
}
/**
* Upgrades a Version 72 version of the Yioop database to a Version 73 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion73(&$db)
{
$db->execute("ALTER TABLE GROUP_PAGE ADD COLUMN LAST_MODIFIED
NUMERIC(".C\TIMESTAMP_LEN.")");
}
/**
* Upgrades a Version 73 version of the Yioop database to a Version 74 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion74(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$blob = $db->blobType($dbinfo);
$db->execute("DROP TABLE IF EXISTS ITEM_RECOMMENDATION");
$db->execute("DROP TABLE IF EXISTS ITEM_TERM_FREQUENCY");
$db->execute("DROP TABLE IF EXISTS ITEM_TERM_WEIGHTS");
$db->execute("DROP TABLE IF EXISTS USER_ITEM_SIMILARITY");
$db->execute("DROP TABLE IF EXISTS USER_TERM_FREQUENCY");
$db->execute("DROP TABLE IF EXISTS USER_TERM_WEIGHTS");
$db->execute("CREATE TABLE IF NOT EXISTS RECOMMENDATION_TERM_EMBEDDING " .
"(ID $integer NOT NULL, ITEM_TYPE $integer NOT NULL, VECTOR $blob, " .
"PRIMARY KEY(ID, ITEM_TYPE))");
$db->execute("CREATE TABLE IF NOT EXISTS RECOMMENDATION_ITEM_EMBEDDING " .
"(ID $integer NOT NULL, ITEM_TYPE $integer NOT NULL, VECTOR $blob, " .
"PARENT_ID $integer, PRIMARY KEY(ID, ITEM_TYPE))");
$db->execute("CREATE TABLE IF NOT EXISTS GROUP_ITEM_RECOMMENDATION " .
"(USER_ID $integer NOT NULL, ITEM_ID $integer NOT NULL, " .
"ITEM_TYPE $integer NOT NULL, SCORE FLOAT, TIMESTAMP " .
"NUMERIC(" . C\TIMESTAMP_LEN . "))");
$db->execute("CREATE TABLE IF NOT EXISTS GROUP_RESOURCE_RECOMMENDATION " .
"(USER_ID $integer NOT NULL, GROUP_ID $integer NOT NULL, " .
"PAGE_ID $integer NOT NULL, RESOURCE_PATH VARCHAR(255), SCORE FLOAT, " .
"TIMESTAMP NUMERIC(" . C\TIMESTAMP_LEN . "), RESOURCE_ID $integer)");
}
/**
* Upgrades a Version 74 version of the Yioop database to a Version 75 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion75(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$db->execute("CREATE INDEX GI_RECOMMENDATION_INDEX ON " .
"GROUP_ITEM_RECOMMENDATION (USER_ID, ITEM_TYPE)");
$db->execute("CREATE INDEX GR_RECOMMENDATION_INDEX ON " .
"GROUP_RESOURCE_RECOMMENDATION (USER_ID)");
}
/**
* Upgrades a Version 75 version of the Yioop database to a Version 76 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion76(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("DROP TABLE RECOMMENDATION_TERM_EMBEDDING");
$db->execute("DROP TABLE RECOMMENDATION_ITEM_EMBEDDING");
$db->execute("CREATE TABLE RECOMMENDATION_TERM_EMBEDDING " .
"(ID $integer NOT NULL, ITEM_TYPE $integer NOT NULL, VECTOR VARCHAR(" .
32 * C\EMBEDDING_VECTOR_SIZE . "), " .
"PRIMARY KEY(ID, ITEM_TYPE))");
$db->execute("CREATE TABLE RECOMMENDATION_ITEM_EMBEDDING " .
"(ID $integer NOT NULL, ITEM_TYPE $integer NOT NULL, VECTOR VARCHAR(" .
32 * C\EMBEDDING_VECTOR_SIZE . "), " .
"PARENT_ID $integer, PRIMARY KEY(ID, ITEM_TYPE))");
}
/**
* Upgrades a Version 76 version of the Yioop database to a Version 77 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion77(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("DROP TABLE RECOMMENDATION_TERM_EMBEDDING");
$db->execute("CREATE TABLE RECOMMENDATION_TERM_EMBEDDING " .
"(ID VARCHAR(16) NOT NULL, ITEM_TYPE $integer NOT NULL, VECTOR VARCHAR("
. 32 * C\EMBEDDING_VECTOR_SIZE . "), PRIMARY KEY(ID, ITEM_TYPE))");
}
/**
* Upgrades a Version 77 version of the Yioop database to a Version 78 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion78(&$db)
{
$db->execute("ALTER TABLE QUERY_ITEM ADD COLUMN
AUX_HASH VARCHAR(". C\TIMESTAMP_LEN . ") DEFAULT ''");
$db->execute("CREATE INDEX QI_AUX_HASH_INDEX ON QUERY_ITEM (AUX_HASH)");
}
/**
* Upgrades a Version 78 version of the Yioop database to a Version 79 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion79(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$result = $db->execute("SELECT GROUP_NAME FROM SOCIAL_GROUPS
WHERE GROUP_ID = ". C\MODERATION_GROUP_ID);
if ($result && !empty($db->fetchArray($result))) {
echo "MODERATION_GROUP_ID already in use, cannot create moderation ".
"group to complete upgrade. Please choose a set a new value for ".
"MODERATION_GROUP_ID constant in src/configs/LocalConfig.php ".
"and then click reload to complete the upgrade.";
exit();
}
$db->execute("CREATE TABLE GROUP_ITEM_FLAG(" .
"USER_ID $integer, ITEM_ID $integer)");
$db->execute("ALTER TABLE GROUP_ITEM
ADD FLAG $integer DEFAULT 0");
$db->execute("ALTER TABLE GROUP_ITEM
ADD PARENT_ITEM_ID $integer DEFAULT 0");
$creation_time = microTimestamp();
$db->execute("INSERT INTO SOCIAL_GROUPS VALUES(" . C\MODERATION_GROUP_ID .
", 'Moderation','" . $creation_time . "','". C\ROOT_ID."', '" .
C\INVITE_ONLY_JOIN . "', '" . C\GROUP_READ . "', " .C\NON_VOTING_GROUP .
", " . C\FOREVER . ", 0)");
$now = time();
$db->execute("INSERT INTO USER_GROUP VALUES (" . C\ROOT_ID . ", " .
C\MODERATION_GROUP_ID . ", " . C\ACTIVE_STATUS . ", $now)");
$db->execute("INSERT INTO USER_GROUP VALUES (" . C\PUBLIC_USER_ID . ", " .
C\MODERATION_GROUP_ID . ", " . C\ACTIVE_STATUS . ", $now)");
}
/**
* Upgrades a Version 79 version of the Yioop database to a Version 80 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion80(&$db)
{
$sql = "DELETE FROM SCRAPER WHERE NAME = 'MEDIAWIKI'";
$db->execute($sql);
$sql = "INSERT INTO SCRAPER(NAME, SIGNATURE, TEXT_PATH, DELETE_PATHS,
EXTRACT_FIELDS) VALUES (?, ?, ?, ?, ?)";
$scraper =
["MEDIAWIKI", "//meta[contains(@content, 'MediaWiki')]",
"//main|//*[@id='content']",
"//*[contains(@class, 'cdx-button')]\n" .
"//*[contains(@class, 'vector-dropdown')]\n" .
"//*[contains(@class, 'vector-page-toolbar')]\n" .
"//*[contains(@class, 'vector-body-before-content')]\n" .
"//*[contains(@class, 'nmbox')]\n" .
"//*[contains(@class, 'hatnote')]\n" .
"//*[contains(@class, 'infobox')]\n" .
"//*[contains(@class, 'haudio')]\n" .
"//*[contains(@class, 'IPA')]\n" .
"//*[contains(@class, 'rt-commentedText')]\n" .
"//a[contains(., 'Full') and contains(., 'article')]\n",
""];
$db->execute($sql, $scraper);
}
/**
* Upgrades a Version 80 version of the Yioop database to a Version 81 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion81(&$db)
{
$profile_model = new M\ProfileModel(C\p('DB_NAME'), false);
$profile_model->db = $db;
$profile = $profile_model->getProfile(C\WORK_DIRECTORY);
$new_profile = $profile;
$new_profile['WIKI_BONUS']= 0.5;
$new_profile['NUM_SLASHES_BONUS']= 0.5;
$profile_model->updateProfile(C\WORK_DIRECTORY, $new_profile,
$profile);
}
/**
* Upgrades a Version 81 version of the Yioop database to a Version 82 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion82(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$auto_increment = $db->autoIncrement($dbinfo);
$serial = $db->serialType($dbinfo);
$db->execute("DROP TABLE IF EXISTS GROUP_EDITORS");
$db->execute("CREATE TABLE GROUP_EDITORS (
ID $serial PRIMARY KEY $auto_increment,
GROUP_ID $integer NOT NULL,
USER_ID $integer NOT NULL,
FOREIGN KEY (GROUP_ID) REFERENCES SOCIAL_GROUPS(GROUP_ID)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (USER_ID) REFERENCES USERS(USER_ID)
ON DELETE CASCADE ON UPDATE CASCADE
)");
}
/**
* Upgrades a Version 82 version of the Yioop database to a Version 83 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion83(&$db)
{
$db->execute("DROP TABLE IF EXISTS GROUP_EDITORS");
}
/**
* Upgrades a Version 83 version of the Yioop database to a Version 84 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion84(&$db)
{
$db->execute("ALTER TABLE SOCIAL_GROUPS RENAME ENCRYPTION TO OPTIONS");
}
/**
* Upgrades a Version 84 version of the Yioop database to a Version 85 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion85(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("CREATE TABLE GROUP_CALL(
CALL_HASH_ID $integer, CALL_TIME
NUMERIC(" . C\MICROSECOND_TIMESTAMP_LEN . "),
INITIATOR_ID $integer,
PRIMARY KEY(CALL_HASH_ID, CALL_TIME))");
$db->execute("CREATE TABLE GROUP_CALL_EVENTS(
CALL_HASH_ID $integer, SENDER_ID $integer, EVENT_TIME
NUMERIC(" . C\MICROSECOND_TIMESTAMP_LEN . "),
EVENT VARCHAR(". C\MAX_GROUP_POST_LEN . "),
PRIMARY KEY(CALL_HASH_ID, EVENT_TIME))");
}
/**
* Upgrades a Version 85 version of the Yioop database to a Version 86 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion86(&$db)
{
$db->execute("DROP TABLE ITEM_IMPRESSION");
}
/**
* Upgrades a Version 86 version of the Yioop database to a Version 87 version
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion87(&$db)
{
$role_model = new M\RoleModel(C\p('DB_NAME'), false);
$role_model->db = $db;
addActivityAtId($db, 'db_activity_user_mail',
"userMail", 12);
$role_id = $role_model->getRoleId('User');
$role_model->addActivityRole($role_id, 12);
$role_id = $role_model->getRoleId('Admin');
$role_model->addActivityRole($role_id, 12);
updateTranslationForStringId($db, 'db_activity_user_mail',
'en-US', 'Mail');
}
/**
* Upgrades a Version 87 version of the Yioop database to a Version 88
* version. Creates the two tables that back item 52 of the v10 plan:
* - MAIL_IMAP_ACCOUNT (public DB) holds per-user external IMAP
* account configuration plus the encrypted password payload
* - MAIL_SECRET (private DB) holds the libsodium master key used
* to encrypt and decrypt those password payloads
* The key row in MAIL_SECRET is created lazily on first encrypt(),
* not at upgrade time, so an instance that never enables external
* IMAP never generates a key it doesn't need.
*
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion88(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$auto_increment = $db->autoIncrement($dbinfo);
$serial = $db->serialType($dbinfo);
$db->execute("CREATE TABLE MAIL_IMAP_ACCOUNT (
ID $serial PRIMARY KEY $auto_increment,
USER_ID $integer,
DISPLAY_NAME VARCHAR(" . C\MAIL_DISPLAY_NAME_LEN . "),
HOST VARCHAR(" . C\MAIL_HOST_LEN . "),
PORT $integer,
USERNAME VARCHAR(" . C\MAIL_USERNAME_LEN . "),
PASSWORD_NONCE VARCHAR(" . C\MAIL_PASSWORD_NONCE_LEN . "),
PASSWORD_CIPHERTEXT VARCHAR(" .
C\MAIL_PASSWORD_CIPHERTEXT_LEN . "),
TLS_MODE VARCHAR(" . C\MAIL_TLS_MODE_LEN . "),
DEFAULT_FOLDER VARCHAR(" . C\MAIL_DISPLAY_NAME_LEN . "),
CREATED_AT $integer,
UPDATED_AT $integer)");
$db->execute("CREATE INDEX MAIL_IMAP_ACCOUNT_USER_IDX " .
"ON MAIL_IMAP_ACCOUNT (USER_ID)");
$private_db_class = C\NS_DATASOURCES . ucfirst(C\p('PRIVATE_DBMS')) .
"Manager";
$private_db = new $private_db_class();
$private_db->connect(C\p('PRIVATE_DB_HOST'), C\p('PRIVATE_DB_USER'),
C\p('PRIVATE_DB_PASSWORD'), C\p('PRIVATE_DB_NAME'));
$private_db->execute("CREATE TABLE MAIL_SECRET (
KEY_ID INTEGER PRIMARY KEY,
KEY_VALUE VARCHAR(" . C\MAIL_SECRET_KEY_LEN . "))");
}
/**
* Upgrades a Version 88 version of the Yioop database to a Version 89
* version. Renames MAIL_IMAP_ACCOUNT to MAIL_ACCOUNT now that the row
* holds outgoing (SMTP) settings alongside the incoming (IMAP) ones,
* and adds the columns that back the per-account "allow self-signed
* certificate" option and the SMTP server configuration. The SMTP
* password is stored encrypted the same way the IMAP password is
* (CredentialCipher nonce + ciphertext pair); the master key in
* MAIL_SECRET is shared.
*
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion89(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("DROP INDEX MAIL_IMAP_ACCOUNT_USER_IDX");
$db->execute("ALTER TABLE MAIL_IMAP_ACCOUNT RENAME TO MAIL_ACCOUNT");
$db->execute("CREATE INDEX MAIL_ACCOUNT_USER_IDX " .
"ON MAIL_ACCOUNT (USER_ID)");
$db->execute("ALTER TABLE MAIL_ACCOUNT ADD COLUMN " .
"ALLOW_SELF_SIGNED $integer DEFAULT 0");
$db->execute("ALTER TABLE MAIL_ACCOUNT ADD COLUMN " .
"SMTP_HOST VARCHAR(" . C\MAIL_HOST_LEN . ")");
$db->execute("ALTER TABLE MAIL_ACCOUNT ADD COLUMN " .
"SMTP_PORT $integer");
$db->execute("ALTER TABLE MAIL_ACCOUNT ADD COLUMN " .
"SMTP_USERNAME VARCHAR(" . C\MAIL_USERNAME_LEN . ")");
$db->execute("ALTER TABLE MAIL_ACCOUNT ADD COLUMN " .
"SMTP_PASSWORD_NONCE VARCHAR(" .
C\MAIL_PASSWORD_NONCE_LEN . ")");
$db->execute("ALTER TABLE MAIL_ACCOUNT ADD COLUMN " .
"SMTP_PASSWORD_CIPHERTEXT VARCHAR(" .
C\MAIL_PASSWORD_CIPHERTEXT_LEN . ")");
$db->execute("ALTER TABLE MAIL_ACCOUNT ADD COLUMN " .
"SMTP_TLS_MODE VARCHAR(" . C\MAIL_TLS_MODE_LEN . ")");
}
/**
* Upgrades a Version 89 version of the Yioop database to a Version 90
* version. Creates the MAIL_SCHEDULED table that backs scheduled-send:
* each row holds the composer payload for a message the user has
* queued for future delivery, the wall-clock time it should fire,
* the account it should send through, and the dispatch state machine
* fields (status, last error, attempt count). The MailScheduledModel
* reads and writes this table; the MailScheduledDispatcher consumes
* due rows via SELECT...WHERE SCHEDULED_AT <= NOW(). Two indexes
* target the two hot queries: the per-user Scheduled folder listing,
* and the dispatcher's "what's due now" sweep.
*
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion90(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$auto_increment = $db->autoIncrement($dbinfo);
$serial = $db->serialType($dbinfo);
$db->execute("CREATE TABLE MAIL_SCHEDULED (
ID $serial PRIMARY KEY $auto_increment,
USER_ID $integer,
ACCOUNT_ID $integer,
SCHEDULED_AT $integer,
CREATED_AT $integer,
STATUS VARCHAR(" . C\MAIL_TLS_MODE_LEN . "),
LAST_ERROR VARCHAR(" . C\MAIL_LAST_ERROR_LEN . "),
ATTEMPT_COUNT $integer DEFAULT 0,
SUBJECT VARCHAR(" . C\MAIL_SUBJECT_LEN . "),
TO_LIST TEXT,
CC_LIST TEXT,
BCC_LIST TEXT,
BODY_TEXT TEXT,
BODY_HTML TEXT,
IN_REPLY_TO VARCHAR(" . C\MAIL_SUBJECT_LEN . "),
REFERENCES_HEADER TEXT)");
$db->execute("CREATE INDEX MAIL_SCHEDULED_USER_IDX " .
"ON MAIL_SCHEDULED (USER_ID, SCHEDULED_AT)");
$db->execute("CREATE INDEX MAIL_SCHEDULED_DUE_IDX " .
"ON MAIL_SCHEDULED (SCHEDULED_AT, STATUS)");
}
/**
* Upgrades a Version 90 version of the Yioop database to a Version 91
* version. Adds a PROVIDER column to MAIL_ACCOUNT so the create-
* account flow can record which preset (Gmail, Yahoo, etc.) was used.
* The column drives three pieces of subsequent behavior:
*
* - the account dropdown surfaces the provider label instead of
* "Custom" for known providers (UI affordance);
* - the From address derivation knows to combine a bare username
* with the provider's email domain (so cpollett + Gmail =
* cpollett@gmail.com, not cpollett@imap.gmail.com);
* - the create form hides host/port/TLS fields for known
* providers since their values are fixed by the provider.
*
* Existing rows leave PROVIDER NULL and render as Custom in the
* dropdown -- a soft migration that avoids guessing the provider
* for a self-hosted account just because its SMTP host happens to
* contain a known domain substring.
*
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion91(&$db)
{
$db->execute("ALTER TABLE MAIL_ACCOUNT ADD COLUMN " .
"PROVIDER VARCHAR(" . C\MAIL_PROVIDER_LEN . ")");
}
/**
* Upgrades a Version 91 version of the Yioop database to a
* Version 92 version. Retroactively creates WORK_DIRECTORY/mail
* on in-the-wild installs that predate the MailSite work; fresh
* installs already get this directory from
* ProfileModel::makeWorkDirectory at install time. Follows the
* same idempotent pattern used at v13 for the feeds directory:
* file_exists guard, plain mkdir, no schema changes.
*
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion92(&$db)
{
if (!file_exists(C\WORK_DIRECTORY . "/mail")) {
mkdir(C\WORK_DIRECTORY . "/mail");
}
}
/**
* Upgrades a Version 92 version of the Yioop database to a
* Version 93 version. Idempotent self-heal for MAIL_ACCOUNT.
* PROVIDER: v91 already adds this column, but at least one
* in-the-wild install ended up with VERSION row >= 91 yet no
* PROVIDER column on MAIL_ACCOUNT -- the v91 ALTER never
* actually ran. Cause is unknown (possible explanations
* include a multi-tab admin reload race during the
* v89-to-v91 upgrade walk, or a manual VERSION bump during
* development). Rather than diagnose the historical race,
* this function probes for the column and adds it when
* missing, so installs that already have it are no-ops and
* installs that don't get healed.
*
* The probe runs SELECT PROVIDER FROM MAIL_ACCOUNT.
* On success (column present, table possibly empty) it
* returns a result set and we return without altering. On
* error (column missing or table missing) execute() returns
* false; we then attempt the ALTER, which itself returns
* false harmlessly if the column already exists (the table-
* missing case is handled by ProfileModel::makeWorkDirectory
* on fresh installs and the v89 ALTER on upgrades).
*
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion93(&$db)
{
$probe = $db->execute(
"SELECT PROVIDER FROM MAIL_ACCOUNT");
if ($probe !== false) {
return;
}
$db->execute("ALTER TABLE MAIL_ACCOUNT ADD COLUMN " .
"PROVIDER VARCHAR(" . C\MAIL_PROVIDER_LEN . ")");
}
/**
* Upgrades a Version 93 version of the Yioop database to a
* Version 94 version. Adds the two tables backing the
* MailSite-clone media job (item 48 follow-up, v10 todo): a
* MAIL_CLONE_JOB row tracks each clone-job submission and its
* resume state; MAIL_CLONE_SEEN records the source-side
* (UIDVALIDITY, UID) pairs already imported so a tick crash or
* a deliberate re-run is idempotent. The DATABASE_VERSION bump
* gates this migration, so it runs once and the tables are
* created unconditionally. The column definitions mirror the
* canonical shape in ProfileModel::createTables so fresh
* installs and upgraded installs converge on identical schema.
*
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion94(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$serial = $db->serialType($dbinfo);
$auto_increment = $db->autoIncrement($dbinfo);
$db->execute("CREATE TABLE MAIL_CLONE_JOB (
ID $serial PRIMARY KEY $auto_increment,
USER_ID $integer,
SRC_ACCOUNT $integer,
DESTINATION_USER VARCHAR(" . C\NAME_LEN . "),
MODE VARCHAR(" . C\MAIL_TLS_MODE_LEN . "),
STATUS VARCHAR(" . C\MAIL_TLS_MODE_LEN . "),
DRY_RUN $integer DEFAULT 0,
FOLDERS_DONE TEXT,
CURRENT_FOLDER VARCHAR(" .
C\MAIL_FOLDER_NAME_LEN . "),
CURRENT_UID $integer DEFAULT 0,
CURRENT_UID_PARTIAL_OFFSET $integer DEFAULT 0,
CURRENT_UID_PARTIAL_SIZE $integer DEFAULT 0,
IMPORTED $integer DEFAULT 0,
SKIPPED $integer DEFAULT 0,
FAILED $integer DEFAULT 0,
LAST_ERROR VARCHAR(" . C\MAIL_LAST_ERROR_LEN . "),
STARTED_AT $integer,
UPDATED_AT $integer)");
$db->execute("CREATE INDEX MAIL_CLONE_JOB_USER_IDX ON
MAIL_CLONE_JOB (USER_ID, STATUS)");
$db->execute("CREATE TABLE MAIL_CLONE_SEEN (
JOB_ID $integer,
SRC_UIDVALIDITY $integer,
SRC_UID $integer,
PRIMARY KEY (JOB_ID, SRC_UIDVALIDITY, SRC_UID))");
}
/**
* Adds the MAIL_CLONE_ERROR table introduced in DATABASE_VERSION
* 95 so per-message clone failures can be attributed to specific
* UIDs, distinguished from later-rescued attempts, and surfaced
* in the clone status page as a scrollable detail list rather
* than a single LAST_ERROR string on MAIL_CLONE_JOB.
*
* The column definitions mirror the canonical shape in
* ProfileModel::createTables so fresh installs and upgraded
* installs converge on identical schema. The DATABASE_VERSION
* bump gates this migration, so the table is created
* unconditionally on the single run.
*
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion95(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$serial = $db->serialType($dbinfo);
$auto_increment = $db->autoIncrement($dbinfo);
$db->execute("CREATE TABLE MAIL_CLONE_ERROR (
ID $serial PRIMARY KEY $auto_increment,
JOB_ID $integer,
FOLDER VARCHAR(" . C\MAIL_FOLDER_NAME_LEN . "),
SRC_UIDVALIDITY $integer DEFAULT 0,
SRC_UID $integer DEFAULT 0,
ERROR_KIND VARCHAR(" .
C\MAIL_CLONE_ERROR_KIND_LEN . "),
MESSAGE VARCHAR(" . C\MAIL_LAST_ERROR_LEN . "),
OCCURRED_AT $integer,
RESOLVED_AT $integer DEFAULT 0,
RESOLVED_VIA VARCHAR(" .
C\MAIL_CLONE_ERROR_KIND_LEN . "))");
$db->execute("CREATE INDEX MAIL_CLONE_ERROR_JOB_IDX ON
MAIL_CLONE_ERROR (JOB_ID, RESOLVED_AT)");
$db->execute("CREATE INDEX MAIL_CLONE_ERROR_UID_IDX ON
MAIL_CLONE_ERROR (JOB_ID, SRC_UIDVALIDITY, SRC_UID)");
}
/**
* Drops the now-unused LAST_ERROR column from MAIL_CLONE_JOB in
* DATABASE_VERSION 96. Per-message clone failures moved to the
* MAIL_CLONE_ERROR table in version 95, leaving MAIL_CLONE_JOB's
* LAST_ERROR written only as an empty string and never read, so
* it is removed to keep the schema honest.
*
* Rather than ALTER TABLE ... DROP COLUMN (unsupported or quirky
* across the SQLite/MySQL/Postgres/Oracle/DB2 backends Yioop
* targets), this rebuilds the table: create a same-shape table
* minus the column, copy the surviving columns across by name,
* then swap it in. Naming the columns in the INSERT ... SELECT
* (rather than DatasourceManager::copyTable, which copies every
* column positionally) is what actually drops LAST_ERROR. The
* probe makes the migration a no-op if the column is already
* absent, so re-runs and fresh installs are safe.
*
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion96(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$serial = $db->serialType($dbinfo);
$auto_increment = $db->autoIncrement($dbinfo);
$probe = $db->execute(
"SELECT LAST_ERROR FROM MAIL_CLONE_JOB");
if ($probe === false) {
return;
}
$columns = "ID, USER_ID, SRC_ACCOUNT, DESTINATION_USER, " .
"MODE, STATUS, DRY_RUN, FOLDERS_DONE, CURRENT_FOLDER, " .
"CURRENT_UID, CURRENT_UID_PARTIAL_OFFSET, " .
"CURRENT_UID_PARTIAL_SIZE, IMPORTED, SKIPPED, FAILED, " .
"STARTED_AT, UPDATED_AT";
$db->execute("CREATE TABLE MAIL_CLONE_JOB_NEW (
ID $serial PRIMARY KEY $auto_increment,
USER_ID $integer,
SRC_ACCOUNT $integer,
DESTINATION_USER VARCHAR(" . C\NAME_LEN . "),
MODE VARCHAR(" . C\MAIL_TLS_MODE_LEN . "),
STATUS VARCHAR(" . C\MAIL_TLS_MODE_LEN . "),
DRY_RUN $integer DEFAULT 0,
FOLDERS_DONE TEXT,
CURRENT_FOLDER VARCHAR(" .
C\MAIL_FOLDER_NAME_LEN . "),
CURRENT_UID $integer DEFAULT 0,
CURRENT_UID_PARTIAL_OFFSET $integer DEFAULT 0,
CURRENT_UID_PARTIAL_SIZE $integer DEFAULT 0,
IMPORTED $integer DEFAULT 0,
SKIPPED $integer DEFAULT 0,
FAILED $integer DEFAULT 0,
STARTED_AT $integer,
UPDATED_AT $integer)");
$db->execute("INSERT INTO MAIL_CLONE_JOB_NEW ($columns) " .
"SELECT $columns FROM MAIL_CLONE_JOB");
$db->execute("DROP TABLE MAIL_CLONE_JOB");
$db->execute("ALTER TABLE MAIL_CLONE_JOB_NEW RENAME TO " .
"MAIL_CLONE_JOB");
$db->execute("CREATE INDEX MAIL_CLONE_JOB_USER_IDX ON
MAIL_CLONE_JOB (USER_ID, STATUS)");
}
/**
* Adds the FOLDER_CAP column to MAIL_CLONE_JOB in
* DATABASE_VERSION 97. FOLDER_CAP records the optional
* "clone only the newest N messages per folder" limit chosen
* when the job was started (0 means no limit, the historical
* behavior). Unlike a column drop, ALTER TABLE ... ADD COLUMN
* with a constant default is portable across the
* SQLite/MySQL/Postgres/Oracle/DB2 backends Yioop targets, so
* this is a plain ADD rather than a table rebuild. A probe
* SELECT makes the migration a no-op when the column is
* already present, so re-runs and fresh installs are safe.
*
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion97(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$probe = $db->execute(
"SELECT FOLDER_CAP FROM MAIL_CLONE_JOB");
if ($probe !== false) {
return;
}
$db->execute("ALTER TABLE MAIL_CLONE_JOB ADD COLUMN " .
"FOLDER_CAP $integer DEFAULT 0");
}
/**
* Adds the SORT_ORDER column to MAIL_ACCOUNT in DATABASE_VERSION
* 98. SORT_ORDER records the user's drag-chosen ordering of their
* mail accounts in the side column; 0 is the default and preserves
* the historical creation-time ordering until the user rearranges.
* As with the FOLDER_CAP migration, ALTER TABLE ... ADD COLUMN with
* a constant default is portable across the SQLite/MySQL/Postgres/
* Oracle/DB2 backends Yioop targets, so this is a plain ADD rather
* than a table rebuild. A probe SELECT makes the migration a no-op
* when the column is already present, so re-runs and fresh installs
* are safe.
*
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion98(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$probe = $db->execute(
"SELECT SORT_ORDER FROM MAIL_ACCOUNT");
if ($probe !== false) {
return;
}
$db->execute("ALTER TABLE MAIL_ACCOUNT ADD COLUMN " .
"SORT_ORDER $integer DEFAULT 0");
}
/**
* Creates the MAIL_ALIAS table in DATABASE_VERSION 99. An alias is
* an alternate local-part that delivers to a Yioop user's mailbox
* across every configured local mail domain, and that the user (or
* an admin) can pick as a From address when composing. The ALIAS
* column is uniquely indexed because an alias shares the username
* namespace: no two users may claim the same alias, and an alias
* may not collide with an existing account name. The
* DATABASE_VERSION bump gates this migration, so the table and
* its indexes are created unconditionally on the single run.
*
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion99(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$serial = $db->serialType($dbinfo);
$auto_increment = $db->autoIncrement($dbinfo);
$db->execute("CREATE TABLE MAIL_ALIAS (
ID $serial PRIMARY KEY $auto_increment,
USER_ID $integer,
ALIAS VARCHAR(" . C\NAME_LEN . "),
CREATED_AT $integer)");
$db->execute("CREATE UNIQUE INDEX MAIL_ALIAS_NAME_IDX ON
MAIL_ALIAS (ALIAS)");
$db->execute("CREATE INDEX MAIL_ALIAS_USER_IDX ON
MAIL_ALIAS (USER_ID)");
}
/**
* Creates the MAIL_SENDER_ALLOW table in DATABASE_VERSION 100.
* Each row records that one user has chosen to trust a particular
* sender address, so that under the "Spam Insecure" delivery
* posture a message arriving without TLS from that sender is still
* filed in the user's INBOX rather than Junk. The list is
* per-user: trusting a sender affects only the user who marked it.
* A unique index on (USER_ID, SENDER) keeps one row per user and
* sender while letting different users trust the same address. The
* DATABASE_VERSION bump gates this migration, so the table and its
* index are created unconditionally on the single run.
*
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion100(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$serial = $db->serialType($dbinfo);
$auto_increment = $db->autoIncrement($dbinfo);
$db->execute("CREATE TABLE MAIL_SENDER_ALLOW (
ID $serial PRIMARY KEY $auto_increment,
USER_ID $integer,
SENDER VARCHAR(" . C\MAIL_USERNAME_LEN . "),
CREATED_AT $integer)");
$db->execute("CREATE UNIQUE INDEX MAIL_SENDER_ALLOW_IDX ON
MAIL_SENDER_ALLOW (USER_ID, SENDER)");
}
/**
* Generates the server's DKIM signing key pair in
* DATABASE_VERSION 101 for installs that predate DKIM support, so
* existing sites get a key without reinstalling. Fresh installs
* get the key at profile-creation time instead; this migration is
* a no-op when the key already exists. The key is not stored in
* the database (it is a file in SECURITY_DIR), so this migration
* touches no tables; the $db parameter is unused but kept for the
* uniform migration signature.
*
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion101(&$db)
{
DkimKey::ensureKeyPair();
}
/**
* Adds a DOMAIN column to MAIL_ALIAS in DATABASE_VERSION 102 so that
* an alias is a full address (local-part plus mail domain) rather
* than a bare local-part valid at every configured domain. Existing
* rows, which predate the column, are backfilled with the first
* configured mail domain (the primary), so they keep resolving as
* before. The uniqueness key moves from ALIAS alone to the
* (ALIAS, DOMAIN) pair, letting the same local-part be claimed at
* two different domains while still preventing two users from
* claiming the identical address. A probe SELECT makes the column
* add idempotent for safe re-runs.
*
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion102(&$db)
{
$probe = $db->execute("SELECT DOMAIN FROM MAIL_ALIAS");
if ($probe === false) {
$db->execute("ALTER TABLE MAIL_ALIAS ADD COLUMN DOMAIN " .
"VARCHAR(" . C\MAIL_HOST_LEN . ")");
}
$domains = MailSiteFactory::localDomains();
$primary = empty($domains) ? 'localhost' : $domains[0];
$db->execute("UPDATE MAIL_ALIAS SET DOMAIN = ? " .
"WHERE DOMAIN IS NULL OR DOMAIN = ''", [$primary]);
$db->execute("DROP INDEX MAIL_ALIAS_NAME_IDX");
$db->execute("CREATE UNIQUE INDEX MAIL_ALIAS_NAME_IDX ON " .
"MAIL_ALIAS (ALIAS, DOMAIN)");
}
/**
* Adds the DOMAIN_ROUTE table introduced in DATABASE_VERSION 103.
* It records, per secure domain, the social group whose Main wiki
* page that domain serves as its landing page; domains without a
* row keep the site default landing behavior. The column
* definitions mirror the canonical shape in
* ProfileModel::createTables so fresh installs and upgraded
* installs converge on identical schema.
*
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion103(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("CREATE TABLE DOMAIN_ROUTE (
DOMAIN VARCHAR(" . C\MAIL_HOST_LEN . ") PRIMARY KEY,
GROUP_ID $integer)");
}
/**
* Adds the DOMAIN_APPEARANCE table introduced in
* DATABASE_VERSION 104. It records, per secure domain, a
* serialized array of that domain's appearance overrides
* (colors, logos, favicon, background image, auxiliary CSS,
* landing page, searchbar path); domains without a row use the
* global Appearance settings. The column definition mirrors the
* canonical shape in ProfileModel::createTables so fresh
* installs and upgraded installs converge on identical schema.
*
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion104(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$appearance_text = (stristr($dbinfo['DBMS'], "mysql") !== false)
? " TEXT " : " VARCHAR(" . (10 * C\MAX_URL_LEN) . ") ";
$db->execute("CREATE TABLE DOMAIN_APPEARANCE (
DOMAIN VARCHAR(" . C\MAIL_HOST_LEN . ") PRIMARY KEY,
APPEARANCE $appearance_text)");
}
/**
* Upgrades a Version 104 database to a Version 105 database. Adds the
* table that maps a session id to the signed-in user it belongs to, so
* a session can be restored after the server drops it from memory or
* restarts, keeping the person signed in.
*
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion105(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("CREATE TABLE SESSION_USER( " .
"SESSION_ID VARCHAR(" . C\SESSION_ID_LEN . ") PRIMARY KEY, " .
"USER_ID $integer, EXPIRES $integer)");
}
/**
* Upgrades a Version 105 database to a Version 106 database. Gives each
* group its own render-engine column and frees the OPTIONS field to be
* a set of yes/no flags. OPTIONS used to pack the render engine and the
* encrypted flag together; now it holds only flag bits (encrypted, and
* whether the public may view a page's source and history). This adds
* the new column, copies each group's render engine out of OPTIONS into
* it, and rewrites OPTIONS to keep the encrypted bit and turn the
* public-source-and-history bit on (the existing default), so group
* behaviour is unchanged after the upgrade.
*
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion106(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("ALTER TABLE SOCIAL_GROUPS ADD RENDER_ENGINE " .
"$integer DEFAULT " . C\MEDIAWIKI_ENGINE);
$result = $db->execute("SELECT GROUP_ID, OPTIONS FROM SOCIAL_GROUPS");
$groups = [];
while ($result && $row = $db->fetchArray($result)) {
$groups[] = $row;
}
foreach ($groups as $group) {
$options = intval($group['OPTIONS']);
$render_engine = $options >> 1;
$encrypted = $options & C\GROUP_OPTION_ENCRYPTED;
$new_options = $encrypted | C\GROUP_OPTION_PAGE_SOURCE_ALLOWED;
$db->execute("UPDATE SOCIAL_GROUPS SET RENDER_ENGINE = ?, " .
"OPTIONS = ? WHERE GROUP_ID = ?",
[$render_engine, $new_options, $group['GROUP_ID']]);
}
}
/**
* Upgrades a Version 106 database to a Version 107 database. Turns on
* the new "public may view the page listing" flag for every existing
* group, so the listing stays visible exactly as it was before this
* option existed. The work is done in PHP rather than with a database
* bit operation so it behaves the same on every supported database.
*
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion107(&$db)
{
$result = $db->execute("SELECT GROUP_ID, OPTIONS FROM SOCIAL_GROUPS");
$groups = [];
while ($result && $row = $db->fetchArray($result)) {
$groups[] = $row;
}
foreach ($groups as $group) {
$new_options = intval($group['OPTIONS']) |
C\GROUP_OPTION_PAGE_LIST_ALLOWED;
$db->execute("UPDATE SOCIAL_GROUPS SET OPTIONS = ? " .
"WHERE GROUP_ID = ?", [$new_options, $group['GROUP_ID']]);
}
}
/**
* Upgrades a Version 107 database to a Version 108 database. Adds a
* per-membership "should this group send me mail" flag to the
* USER_GROUP table, switched on (1) for every existing membership so
* people keep getting group mail exactly as they did before this
* setting existed. A member can later turn it off to stop a group's
* bulk mail without having to leave the group.
*
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion108(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("ALTER TABLE USER_GROUP ADD RECEIVE_MAIL " .
"$integer DEFAULT 1");
}
/**
* Upgrades a Version 108 database to a Version 109 database. Adds the
* table that records when a member has chosen to stop a particular
* discussion thread from mailing them. A row here means "do not mail
* this user about this thread"; with no row the member follows the
* thread as before, so existing behaviour is unchanged after the
* upgrade.
*
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion109(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("CREATE TABLE THREAD_UNSUBSCRIBE (" .
"USER_ID $integer, THREAD_ID $integer, " .
"PRIMARY KEY (THREAD_ID, USER_ID))");
}
/**
* Upgrades a Yioop database from version 109 to version 110.
*
* Thread mail switched from opt-out to opt-in: a member now follows a
* thread only after they post to it or ask to follow it, rather than
* being mailed by default. The old THREAD_UNSUBSCRIBE table recorded the
* opposite (members who had turned a thread's mail off), so it is dropped
* and replaced by THREAD_FOLLOW, whose rows say "mail me about this
* thread". Any old opt-out rows are discarded because they no longer have
* a meaning under the new rule.
*
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion110(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("DROP TABLE THREAD_UNSUBSCRIBE");
$db->execute("CREATE TABLE THREAD_FOLLOW (" .
"USER_ID $integer, THREAD_ID $integer, " .
"PRIMARY KEY (THREAD_ID, USER_ID))");
}
/**
* Adds the MAIL_SUPPRESSION table in DATABASE_VERSION 111 so existing
* installs gain the list of email addresses that have asked to receive
* no further mail from this site. Fresh installs get the table when their
* profile is created instead. The send path checks this list and skips
* any address on it, matching the address without regard to case.
*
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion111(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$serial = $db->serialType($dbinfo);
$auto_increment = $db->autoIncrement($dbinfo);
$db->execute("CREATE TABLE MAIL_SUPPRESSION (
ID $serial PRIMARY KEY $auto_increment,
EMAIL VARCHAR(" . C\MAIL_USERNAME_LEN . "),
CREATED_AT $integer)");
$db->execute("CREATE UNIQUE INDEX MAIL_SUPPRESSION_IDX ON
MAIL_SUPPRESSION (EMAIL)");
}
/**
* Upgrades a Version 111 database to a Version 112 database. Replaces the
* table that remembers which signed-in user a session id belongs to. This
* table used to be called SESSION_USER, but that name is a reserved word
* in PostgreSQL and several other databases, so the table could never be
* created or read there. The replacement table AUTH_SESSION has the same
* columns. The old SESSION_USER table is dropped on the databases where it
* was actually created (the name is only usable, and so only present, on
* SQLite and MySQL). The USER_SESSION table, which holds each user's saved
* session data, is also emptied so everyone simply signs in again and
* starts with a clean session after the upgrade.
*
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion112(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$lower_dbms = strtolower(C\p('DBMS'));
if (in_array($lower_dbms, ["mysql", "sqlite", "sqlite3"])) {
$db->execute("DROP TABLE IF EXISTS SESSION_USER");
}
$db->execute("DELETE FROM USER_SESSION");
$db->execute("CREATE TABLE AUTH_SESSION (
SESSION_ID VARCHAR(" . C\SESSION_ID_LEN . ") PRIMARY KEY,
USER_ID $integer, EXPIRES $integer)");
}
/**
* Upgrades a Version 113 database. This adds the SIGNIN_CODE table that
* backs emailed one-time sign-in codes: it stores, per user, the hash of
* the current short-lived single-use sign-in code, when that code
* expires, and how many wrong guesses it has taken.
*
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion113(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("CREATE TABLE SIGNIN_CODE (
USER_ID $integer PRIMARY KEY,
CODE_HASH VARCHAR(" . C\LONG_NAME_LEN . "),
EXPIRES $integer, TRIES $integer)");
}
/**
* Upgrades a Version 114 database. This adds the two columns that back
* question-based account recovery: the personal question a member typed,
* stored as plain text so it can be shown back to them at recovery time,
* and the hash of the answer they gave to it.
*
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion114(&$db)
{
$db->execute("ALTER TABLE USERS ADD COLUMN RECOVERY_QUESTION
VARCHAR(" . C\RECOVERY_QUESTION_LEN . ") DEFAULT ''");
$db->execute("ALTER TABLE USERS ADD COLUMN RECOVERY_ANSWER
VARCHAR(" . C\LONG_NAME_LEN . ") DEFAULT ''");
}
/**
* Adds the table that keeps each person's Git application code. This is a
* per-person code, stored in the private database, that lets someone push
* to a Git repository wiki page without sending their account password. The
* code carries an expiry time after which it no longer works.
*
* @param object $db datasource to use to upgrade.
*/
function upgradeDatabaseVersion115(&$db)
{
$private_db_class = C\NS_DATASOURCES .
ucfirst(C\p('PRIVATE_DBMS'))."Manager";
$private_dbinfo = ["DBMS" => C\p('PRIVATE_DBMS'),
"DB_HOST" => C\p('PRIVATE_DB_HOST'),
"DB_NAME" => C\p('PRIVATE_DB_NAME'),
"DB_PASSWORD" => C\p('PRIVATE_DB_PASSWORD')];
$private_db = new $private_db_class();
$private_db->connect(C\p('PRIVATE_DB_HOST'), C\p('PRIVATE_DB_USER'),
C\p('PRIVATE_DB_PASSWORD'), C\p('PRIVATE_DB_NAME'));
$integer = $private_db->integerType($private_dbinfo);
$private_db->execute("CREATE TABLE GIT_APP_CODE (USER_ID $integer " .
"PRIMARY KEY, APP_CODE VARCHAR(" . C\LONG_NAME_LEN . "), " .
"EXPIRES $integer)");
}
/**
* Upgrades a Version 115 database to a Version 116 database. The combined
* Server Settings screen was split into two admin activities, so an install
* made before the split needs the new User, Roles, Groups activity added
* next to the renamed Servers activity. This inserts that activity in the
* slot right after Servers (granting it to the Admin role), gives it its
* English display name, and shortens the Servers activity's English display
* name to match the split. Activity ordering follows ACTIVITY_ID, so placing
* it right after Servers keeps the same menu order a fresh install has.
*
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion116(&$db)
{
$activity_model = new M\ActivityModel(C\p('DB_NAME'), false);
$activity_model->db = $db;
$servers_activity_id =
$activity_model->getActivityIdFromMethodName("serverSettings");
$new_activity_id = $servers_activity_id + 1;
/* Shift every activity at or after the new slot up by one, highest id
first so each single-row move lands on an id no row holds yet. A bulk
"id = id + 1" update would collide with the unique ACTIVITY_ID on
SQLite; moving one row at a time in descending order avoids that on
SQLite, MySQL, and Postgres alike. ROLE_ACTIVITY (the only other table
keyed by activity) is moved in lockstep so every role keeps the
activities it could reach. */
$result = $db->execute("SELECT ACTIVITY_ID FROM ACTIVITY " .
"WHERE ACTIVITY_ID >= ? ORDER BY ACTIVITY_ID DESC",
[$new_activity_id]);
$shift_ids = [];
while ($row = $db->fetchArray($result)) {
$shift_ids[] = $row['ACTIVITY_ID'];
}
foreach ($shift_ids as $old_id) {
$db->execute("UPDATE ACTIVITY SET ACTIVITY_ID = ? " .
"WHERE ACTIVITY_ID = ?", [$old_id + 1, $old_id]);
$db->execute("UPDATE ROLE_ACTIVITY SET ACTIVITY_ID = ? " .
"WHERE ACTIVITY_ID = ?", [$old_id + 1, $old_id]);
}
/* Give the new activity a translation id past the highest in use, insert
it in the freed slot, and grant it to the Admin role only, matching
the admin-only Servers activity it sits beside. */
$result = $db->execute(
"SELECT MAX(TRANSLATION_ID) AS TOP_ID FROM TRANSLATION");
$row = $db->fetchArray($result);
$translation_id = $row['TOP_ID'] + 1;
$db->execute("INSERT INTO ACTIVITY VALUES (?, ?, ?)",
[$new_activity_id, $translation_id, 'userRolesGroups']);
$db->execute("INSERT INTO TRANSLATION VALUES (?, ?)",
[$translation_id, 'db_activity_user_roles_groups']);
$db->execute("INSERT INTO ROLE_ACTIVITY VALUES (?, ?, 'all')",
[C\ADMIN_ROLE, $new_activity_id]);
updateTranslationForStringId($db, 'db_activity_user_roles_groups',
'en-US', 'User, Roles, Groups');
updateTranslationForStringId($db, 'db_activity_server_settings',
'en-US', 'Servers');
}
/**
* Upgrades a Version 116 database to a Version 117 database. Adds the
* ROLE_LIMITS table that backs the new Role Group Limits settings: one row
* per role holding that role's caps (each defaulting to unlimited), plus the
* role's cost in credits (default 0) and how often that cost is charged
* (default never). It then seeds a row for each role that already exists so
* the settings screen has a value to show and edit for every role. Unlimited
* is stored as -1.
*
* @param object $db datasource to use to upgrade
*/
function upgradeDatabaseVersion117(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("CREATE TABLE ROLE_LIMITS (ROLE_ID $integer PRIMARY KEY, " .
"MAX_GROUPS_OWNED $integer DEFAULT -1, " .
"MAX_GROUP_MEMBERS $integer DEFAULT -1, " .
"MAX_GROUP_WIKI_PAGES $integer DEFAULT -1, " .
"MAX_PAGE_RESOURCE_MEMORY $integer DEFAULT -1, " .
"MAX_GROUP_THREADS $integer DEFAULT -1, " .
"MAX_THREAD_RESOURCES $integer DEFAULT -1, " .
"MAX_THREAD_POSTS $integer DEFAULT -1, " .
"ROLE_COST $integer DEFAULT 0, " .
"CHARGE_FREQUENCY VARCHAR(" . C\NAME_LEN . ") DEFAULT 'never')");
$db->execute("INSERT INTO ROLE_LIMITS (ROLE_ID) " .
"SELECT ROLE_ID FROM ROLE");
}
/**
* Upgrades a Version 117 version of the Yioop database to a Version 118
* version. This adds a Developer role that holds only the Feeds and Wikis
* activity, granted without the no-git-repository modifier the User role
* carries, together with an empty role limits row for it.
*
* @param object &$db datasource for the Yioop database
*/
function upgradeDatabaseVersion118(&$db)
{
$developer_role = C\DEVELOPER_ROLE;
$db->execute("INSERT INTO ROLE VALUES ($developer_role, 'Developer')");
$db->execute("INSERT INTO ROLE_LIMITS (ROLE_ID) VALUES " .
"($developer_role)");
$result = $db->execute("SELECT ACTIVITY_ID FROM ACTIVITY " .
"WHERE METHOD_NAME = 'groupFeeds'");
$row = ($result) ? $db->fetchArray($result) : false;
if (!empty($row)) {
$activity_id = (isset($row['ACTIVITY_ID'])) ? $row['ACTIVITY_ID'] :
$row['activity_id'];
$db->execute("INSERT INTO ROLE_ACTIVITY VALUES " .
"($developer_role, $activity_id, 'all')");
}
}
/**
* Upgrades a Version 118 version of the Yioop database to a Version 119
* version. This renames the serverSettings admin activity to servers so
* its method, activity, and url all read the same; the ACTIVITY row that
* names the old method is updated to the new one.
*
* @param object &$db datasource for the Yioop database
*/
function upgradeDatabaseVersion119(&$db)
{
$db->execute("UPDATE ACTIVITY SET METHOD_NAME = 'servers' " .
"WHERE METHOD_NAME = 'serverSettings'");
}
/**
* Upgrades a Version 119 version of the Yioop database to a Version 120
* version. This adds a per-grant expiry to USER_ROLE so a role a user
* bought as a subscription can lapse once its paid time runs out. The
* price and billing period of a sellable role already live on
* ROLE_LIMITS (ROLE_COST and CHARGE_FREQUENCY). Existing grants never
* expire.
*
* @param object &$db datasource for the Yioop database
*/
function upgradeDatabaseVersion120(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$forever = C\FOREVER;
$db->execute("ALTER TABLE USER_ROLE ADD EXPIRES " .
"$integer DEFAULT $forever");
}
/**
* Upgrades a Version 120 version of the Yioop database to a Version 121
* version. This adds a per-grant "will renew" flag to USER_ROLE so a user
* can cancel a subscribed role and keep it until the paid period ends
* rather than losing it at once. Existing grants keep renewing (flag 1).
*
* @param object &$db datasource for the Yioop database
*/
function upgradeDatabaseVersion121(&$db)
{
$dbinfo = ["DBMS" => C\p('DBMS'), "DB_HOST" => C\p('DB_HOST'),
"DB_NAME" => C\p('DB_NAME'), "DB_PASSWORD" => C\p('DB_PASSWORD')];
$integer = $db->integerType($dbinfo);
$db->execute("ALTER TABLE USER_ROLE ADD WILL_RENEW " .
"$integer DEFAULT 1");
}
/**
* Upgrades a Version 121 version of the Yioop database to a Version 122
* version. Themes used to sit loose in the app css folder among the
* stylesheets Yioop ships, which left no way to tell what an admin had
* added from what came with the software, so they are moved into a
* folder of their own and go on working as before. A search.css there
* is the site's own stylesheet rather than a theme, so
* it is left where it is. This step changes no table, but it comes with
* the version so a site already running picks it up exactly once.
*
* @param object &$db datasource for the Yioop database, unused here as
* this step only moves files
*/
function upgradeDatabaseVersion122(&$db)
{
$css_folder = C\APP_DIR . "/css";
if (!file_exists($css_folder)) {
return;
}
$default_folder = $css_folder . "/" . C\DEFAULT_THEME_FOLDER;
set_error_handler(null);
if (!file_exists($default_folder)) {
@mkdir($default_folder);
@chmod($default_folder, 0777);
}
foreach (glob("$css_folder/*.css") as $theme_file) {
if (basename($theme_file) == "search.css") {
continue;
}
@rename($theme_file,
$default_folder . "/" . basename($theme_file));
}
restore_error_handler();
}
/**
* Folds the themes each routed domain had been keeping in a folder of
* its own into the single folder the site now keeps all of its themes
* in, introduced in version 123. Splitting themes per domain meant a
* domain could not wear a theme another domain had made, and left a wiki
* page's own choice of theme naming a file that was not in the folder
* the page's domain read from, so the page went undressed. A domain
* theme whose name is already taken in the shared folder by different
* rules is brought over with the domain's name added to its own, and
* that domain's record pointed at the new name, so a domain goes on
* looking as it did.
*
* @param object &$db datasource to use to upgrade
*/
function upgradeDatabaseVersion123(&$db)
{
$css_folder = C\APP_DIR . "/css";
if (!file_exists($css_folder)) {
return;
}
$default_folder = $css_folder . "/" . C\DEFAULT_THEME_FOLDER;
set_error_handler(null);
if (!file_exists($default_folder)) {
@mkdir($default_folder);
@chmod($default_folder, 0777);
}
foreach (glob("$css_folder/*", GLOB_ONLYDIR) as $domain_folder) {
$domain = basename($domain_folder);
if ($domain == C\DEFAULT_THEME_FOLDER) {
continue;
}
$renames = [];
foreach (glob("$domain_folder/*.css") as $theme_file) {
$theme_name = substr(basename($theme_file), 0, -4);
$shared_file = "$default_folder/$theme_name.css";
if (!file_exists($shared_file)) {
@rename($theme_file, $shared_file);
continue;
}
if (file_get_contents($shared_file) ==
file_get_contents($theme_file)) {
@unlink($theme_file);
continue;
}
$suffix = preg_replace('/[^A-Za-z0-9_\-]/', "-", $domain);
$new_name = $theme_name . "-" . $suffix;
@rename($theme_file, "$default_folder/$new_name.css");
$renames[$theme_name] = $new_name;
}
@rmdir($domain_folder);
upgradeDomainThemeNames123($db, $domain, $renames);
}
restore_error_handler();
}
/**
* Points one routed domain's record at the new name of the theme it
* wears, for a theme that had to be renamed when the domain's own themes
* were folded into the site's one folder in version 123. Kept apart from
* the folding itself so that moving files and mending records can each
* be read on their own.
*
* @param object &$db datasource to use to upgrade
* @param string $domain routed domain whose record may need mending
* @param array $renames old theme name to new theme name, for those
* themes of this domain that had to be renamed
*/
function upgradeDomainThemeNames123(&$db, $domain, $renames)
{
if (empty($renames)) {
return;
}
$result = $db->execute("SELECT APPEARANCE FROM DOMAIN_APPEARANCE " .
"WHERE DOMAIN = ?", [$domain]);
$row = ($result) ? $db->fetchArray($result) : false;
if (empty($row['APPEARANCE'])) {
return;
}
$appearance = unserialize($row['APPEARANCE']);
if (!is_array($appearance)) {
return;
}
$worn = $appearance['AUXILIARY_CSS_NAME'] ?? "";
if (empty($renames[$worn])) {
return;
}
$appearance['AUXILIARY_CSS_NAME'] = $renames[$worn];
$db->execute("UPDATE DOMAIN_APPEARANCE SET APPEARANCE = ? " .
"WHERE DOMAIN = ?", [serialize($appearance), $domain]);
}
(c) 2026 Seekquarry - Open Source Search Engine Software