CHIEFSOFT\ameye
912 lines
42 KiB
PHP
912 lines
42 KiB
PHP
<?php
|
|
|
|
/**
|
|
* @file classes/migration/upgrade/PKPv3_3_0UpgradeMigration.php
|
|
*
|
|
* Copyright (c) 2014-2021 Simon Fraser University
|
|
* Copyright (c) 2000-2021 John Willinsky
|
|
* Distributed under the GNU GPL v3. For full terms see the file docs/COPYING.
|
|
*
|
|
* @class PKPv3_3_0UpgradeMigration
|
|
*
|
|
* @brief Describe database table structures.
|
|
*/
|
|
|
|
namespace PKP\migration\upgrade;
|
|
|
|
use APP\core\Services;
|
|
use APP\facades\Repo;
|
|
use Exception;
|
|
use Illuminate\Database\PostgresConnection;
|
|
use Illuminate\Database\Query\Builder;
|
|
use Illuminate\Database\Query\JoinClause;
|
|
use Illuminate\Database\Schema\Blueprint;
|
|
use Illuminate\Support\Facades\DB;
|
|
use Illuminate\Support\Facades\Schema;
|
|
use PKP\db\XMLDAO;
|
|
use PKP\file\FileManager;
|
|
|
|
abstract class PKPv3_3_0UpgradeMigration extends \PKP\migration\Migration
|
|
{
|
|
private const ASSOC_TYPE_REVIEW_ROUND = 0x000020B; //PKPApplication::ASSOC_TYPE_REVIEW_ROUND
|
|
private const ASSOC_TYPE_REVIEW_RESPONSE = 0x0000204; //PKPApplication::ASSOC_TYPE_REVIEW_RESPONSE
|
|
|
|
private const SUBMISSION_FILE_FAIR_COPY = 7; //SubmissionFile::SUBMISSION_FILE_FAIR_COPY
|
|
private const SUBMISSION_FILE_EDITOR = 8; //SubmissionFile::SUBMISSION_FILE_EDITOR
|
|
private const SUBMISSION_FILE_SUBMISSION = 2; //SubmissionFile::SUBMISSION_FILE_SUBMISSION
|
|
private const SUBMISSION_FILE_NOTE = 3; //SubmissionFile::SUBMISSION_FILE_NOTE
|
|
private const SUBMISSION_FILE_REVIEW_FILE = 4; //SubmissionFile::SUBMISSION_FILE_REVIEW_FILE
|
|
private const SUBMISSION_FILE_REVIEW_ATTACHMENT = 5; //SubmissionFile::SUBMISSION_FILE_REVIEW_ATTACHMENT
|
|
private const SUBMISSION_FILE_REVIEW_REVISION = 15; //SubmissionFile::SUBMISSION_FILE_REVIEW_REVISION
|
|
private const SUBMISSION_FILE_FINAL = 6; //SubmissionFile::SUBMISSION_FILE_FINAL
|
|
private const SUBMISSION_FILE_COPYEDIT = 9; //SubmissionFile::SUBMISSION_FILE_COPYEDIT
|
|
private const SUBMISSION_FILE_DEPENDENT = 17; //SubmissionFile::SUBMISSION_FILE_DEPENDENT
|
|
private const SUBMISSION_FILE_PROOF = 10; //SubmissionFile::SUBMISSION_FILE_PROOF
|
|
private const SUBMISSION_FILE_PRODUCTION_READY = 11; //SubmissionFile::SUBMISSION_FILE_PRODUCTION_READY
|
|
private const SUBMISSION_FILE_ATTACHMENT = 13; //SubmissionFile::SUBMISSION_FILE_ATTACHMENT
|
|
private const SUBMISSION_FILE_QUERY = 18; //SubmissionFile::SUBMISSION_FILE_QUERY
|
|
|
|
abstract protected function getSubmissionPath(): string;
|
|
abstract protected function getContextPath(): string;
|
|
abstract protected function getContextTable(): string;
|
|
abstract protected function getContextKeyField(): string;
|
|
abstract protected function getContextSettingsTable(): string;
|
|
abstract protected function getSectionTable(): string;
|
|
abstract protected function getSerializedSettings(): array;
|
|
|
|
/**
|
|
* Run the migrations.
|
|
*/
|
|
public function up(): void
|
|
{
|
|
if (Schema::hasColumn('submissions', 'locale')) {
|
|
Schema::table('submissions', function (Blueprint $table) {
|
|
// pkp/pkp-lib#3572 Remove OJS 2.x upgrade tools (OPS doesn't have this)
|
|
$table->dropColumn('locale');
|
|
});
|
|
}
|
|
Schema::table('submissions', function (Blueprint $table) {
|
|
// pkp/pkp-lib#6285 submissions.section_id in OMP appears only from 3.2.1
|
|
if (Schema::hasColumn($table->getTable(), 'section_id')) {
|
|
// pkp/pkp-lib#2493 Remove obsolete columns
|
|
$table->dropColumn('section_id');
|
|
};
|
|
});
|
|
Schema::table('publication_settings', function (Blueprint $table) {
|
|
// pkp/pkp-lib#6096 DB field type TEXT is cutting off long content
|
|
$table->mediumText('setting_value')->nullable()->change();
|
|
});
|
|
if (Schema::hasColumn('authors', 'submission_id')) {
|
|
Schema::table('authors', function (Blueprint $table) {
|
|
// pkp/pkp-lib#2493 Remove obsolete columns
|
|
$table->dropColumn('submission_id');
|
|
});
|
|
}
|
|
Schema::table('announcements', function (Blueprint $table) {
|
|
// pkp/pkp-lib#5865 Change announcement expiry format in database
|
|
$table->date('date_expire')->change();
|
|
});
|
|
Schema::table('announcement_settings', function (Blueprint $table) {
|
|
// pkp/pkp-lib#6748 Change announcement setting type to permit nulls
|
|
$table->string('setting_type', 6)->nullable()->change();
|
|
});
|
|
|
|
// Transitional: The stage_id column may have already been added by the ADODB schema toolset
|
|
if (!Schema::hasColumn('email_templates_default', 'stage_id')) {
|
|
Schema::table('email_templates_default', function (Blueprint $table) {
|
|
// pkp/pkp-lib#4796 stage ID as a filter parameter to email templates
|
|
$table->bigInteger('stage_id')->nullable();
|
|
});
|
|
}
|
|
|
|
// pkp/pkp-lib#6301: Indexes may be missing that affect search performance.
|
|
// (These are added for 3.2.1-2 so may or may not be present for this upgrade code.)
|
|
$schemaManager = DB::getDoctrineSchemaManager();
|
|
if (!in_array('submissions_publication_id', array_keys($schemaManager->listTableIndexes('submissions')))) {
|
|
Schema::table('submissions', function (Blueprint $table) {
|
|
$table->index(['submission_id'], 'submissions_publication_id');
|
|
});
|
|
}
|
|
if (!in_array('submission_search_object_submission', array_keys($schemaManager->listTableIndexes('submission_search_objects')))) {
|
|
Schema::table('submission_search_objects', function (Blueprint $table) {
|
|
$table->index(['submission_id'], 'submission_search_object_submission');
|
|
});
|
|
}
|
|
|
|
// pkp/pkp-lib#6093 Don't allow nulls (previously an upgrade workaround)
|
|
Schema::table('announcement_types', function (Blueprint $table) {
|
|
$table->bigInteger('assoc_type')->nullable(false)->change();
|
|
});
|
|
Schema::table('email_templates', function (Blueprint $table) {
|
|
$table->bigInteger('context_id')->default(0)->nullable(false)->change();
|
|
});
|
|
Schema::table('genres', function (Blueprint $table) {
|
|
$table->bigInteger('seq')->nullable(false)->change();
|
|
$table->smallInteger('supplementary')->default(0)->nullable(false)->change();
|
|
});
|
|
Schema::table('event_log', function (Blueprint $table) {
|
|
$table->bigInteger('assoc_type')->nullable(false)->change();
|
|
$table->bigInteger('assoc_id')->nullable(false)->change();
|
|
});
|
|
Schema::table('email_log', function (Blueprint $table) {
|
|
$table->bigInteger('assoc_type')->nullable(false)->change();
|
|
$table->bigInteger('assoc_id')->nullable(false)->change();
|
|
});
|
|
Schema::table('notes', function (Blueprint $table) {
|
|
$table->bigInteger('assoc_type')->nullable(false)->change();
|
|
$table->bigInteger('assoc_id')->nullable(false)->change();
|
|
});
|
|
Schema::table('review_assignments', function (Blueprint $table) {
|
|
$table->bigInteger('review_round_id')->nullable(false)->change();
|
|
});
|
|
Schema::table('authors', function (Blueprint $table) {
|
|
$table->bigInteger('publication_id')->nullable(false)->change();
|
|
});
|
|
DB::unprepared('UPDATE review_assignments SET review_form_id=NULL WHERE review_form_id=0');
|
|
|
|
$this->_populateEmailTemplates();
|
|
$this->_makeRemoteUrlLocalizable();
|
|
|
|
// pkp/pkp-lib#6057: Migrate locale property from publications to submissions
|
|
Schema::table('submissions', function (Blueprint $table) {
|
|
$table->string('locale', 14)->nullable();
|
|
});
|
|
|
|
$updateQuery = DB::table('submissions as s')
|
|
->join('publications as p', 'p.publication_id', '=', 's.current_publication_id');
|
|
if (DB::connection() instanceof PostgresConnection) {
|
|
$updateQuery->updateFrom(['s.locale' => DB::raw('p.locale')]);
|
|
} else {
|
|
$updateQuery->update(['s.locale' => DB::raw('p.locale')]);
|
|
}
|
|
|
|
Schema::table('publications', function (Blueprint $table) {
|
|
$table->dropColumn('locale');
|
|
});
|
|
|
|
// pkp/pkp-lib#6057 Submission files refactor
|
|
$this->migrateSubmissionFiles();
|
|
|
|
$this->_fixCapitalCustomBlockTitles();
|
|
$this->_createCustomBlockTitles();
|
|
|
|
// Remove item views related to submission files and notes,
|
|
// and convert the assoc_id to an integer
|
|
DB::table('item_views')
|
|
->where('assoc_type', '!=', self::ASSOC_TYPE_REVIEW_RESPONSE)
|
|
->delete();
|
|
// PostgreSQL requires an explicit type cast
|
|
if (DB::connection() instanceof PostgresConnection) {
|
|
DB::statement('ALTER TABLE item_views ALTER COLUMN assoc_id TYPE BIGINT USING (assoc_id::INTEGER)');
|
|
} else {
|
|
Schema::table('item_views', function (Blueprint $table) {
|
|
$table->bigInteger('assoc_id')->change();
|
|
});
|
|
}
|
|
|
|
// pkp/pkp-lib#4017 and pkp/pkp-lib#4622
|
|
Schema::create('jobs', function (Blueprint $table) {
|
|
$table->bigIncrements('id');
|
|
$table->string('queue');
|
|
$table->longText('payload');
|
|
$table->unsignedTinyInteger('attempts');
|
|
$table->unsignedInteger('reserved_at')->nullable();
|
|
$table->unsignedInteger('available_at');
|
|
$table->unsignedInteger('created_at');
|
|
$table->index(['queue', 'reserved_at']);
|
|
});
|
|
|
|
// pkp/pkp-lib#6594 Clear context defaults installed for unused languages
|
|
$settingsWithDefaults = [
|
|
'authorInformation',
|
|
'librarianInformation',
|
|
'openAccessPolicy',
|
|
'privacyStatement',
|
|
'readerInformation',
|
|
'submissionChecklist',
|
|
'clockssLicense',
|
|
'lockssLicense',
|
|
];
|
|
|
|
$rows = DB::table($this->getContextSettingsTable() . ' as js')
|
|
->join($this->getContextSettingsTable() . ' as j', 'j.' . $this->getContextKeyField(), '=', 'js.' . $this->getContextKeyField())
|
|
->where('js.setting_name', '=', 'supportedFormLocales')
|
|
->select(['js.' . $this->getContextKeyField() . ' as id', 'js.setting_value as locales'])
|
|
->get();
|
|
foreach ($rows as $row) {
|
|
// account for some locale settings stored as assoc arrays
|
|
$locales = $row->locales;
|
|
if (empty($locales)) {
|
|
$locales = [];
|
|
} elseif (@unserialize($locales) !== false) {
|
|
$locales = unserialize($locales);
|
|
} else {
|
|
$locales = json_decode($locales, true);
|
|
}
|
|
$locales = array_values($locales);
|
|
DB::table($this->getContextSettingsTable())
|
|
->where($this->getContextKeyField(), '=', $row->id)
|
|
->whereIn('setting_name', $settingsWithDefaults)
|
|
->whereNotIn('locale', $locales)
|
|
->delete();
|
|
}
|
|
|
|
Schema::table($this->getContextSettingsTable(), function (Blueprint $table) {
|
|
// pkp/pkp-lib#6096 DB field type TEXT is cutting off long content
|
|
$table->mediumText('setting_value')->nullable()->change();
|
|
});
|
|
if (!Schema::hasColumn($this->getSectionTable(), 'is_inactive')) {
|
|
Schema::table($this->getSectionTable(), function (Blueprint $table) {
|
|
$table->smallInteger('is_inactive')->default(0);
|
|
});
|
|
}
|
|
if (Schema::hasTable('review_forms')) {
|
|
Schema::table('review_forms', function (Blueprint $table) {
|
|
$table->bigInteger('assoc_type')->nullable(false)->change();
|
|
$table->bigInteger('assoc_id')->nullable(false)->change();
|
|
});
|
|
}
|
|
|
|
// pkp/pkp-lib#6807 Make sure all submission last modification dates are set
|
|
DB::statement('UPDATE submissions SET last_modified = NOW() WHERE last_modified IS NULL');
|
|
|
|
$this->_settingsAsJSON();
|
|
|
|
if (Schema::hasColumn('author_settings', 'setting_type')) {
|
|
Schema::table('author_settings', function (Blueprint $table) {
|
|
// pkp/pkp-lib#2493 Remove obsolete columns
|
|
$table->dropColumn('setting_type');
|
|
});
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Reverse the downgrades
|
|
*/
|
|
public function down(): void
|
|
{
|
|
throw new \PKP\install\DowngradeNotSupportedException();
|
|
}
|
|
|
|
/**
|
|
* @brief populate email templates with new records for workflow stage id
|
|
*/
|
|
private function _populateEmailTemplates()
|
|
{
|
|
$xmlDao = new XMLDAO();
|
|
$data = $xmlDao->parseStruct(Repo::emailTemplate()->dao->getMainEmailTemplatesFilename(), ['email']);
|
|
foreach ($data['email'] as $template) {
|
|
$attr = $template['attributes'];
|
|
if (array_key_exists('stage_id', $attr)) {
|
|
DB::table('email_templates_default')->where('email_key', $attr['key'])->update(['stage_id' => $attr['stage_id']]);
|
|
}
|
|
}
|
|
}
|
|
|
|
/**
|
|
* @brief make remoteUrl navigation item type multilingual and drop the url column
|
|
*/
|
|
private function _makeRemoteUrlLocalizable()
|
|
{
|
|
$contexts = DB::table($this->getContextTable() . ' AS c')
|
|
->join($this->getContextSettingsTable() . ' AS s', 's.' . $this->getContextKeyField(), '=', 'c.' . $this->getContextKeyField())
|
|
->where('s.setting_name', '=', 'supportedLocales')
|
|
->select(['c.' . $this->getContextKeyField() . ' AS id', 's.setting_value AS supported_locales'])
|
|
->get();
|
|
|
|
foreach ($contexts as $context) {
|
|
if (($locales = @unserialize($context->supported_locales)) === false) {
|
|
continue;
|
|
}
|
|
|
|
$navigationItems = DB::table('navigation_menu_items')->where('context_id', $context->id)->pluck('url', 'navigation_menu_item_id')->filter()->all();
|
|
foreach ($navigationItems as $navigation_menu_item_id => $url) {
|
|
foreach ($locales as $locale) {
|
|
DB::table('navigation_menu_item_settings')->insert([
|
|
'navigation_menu_item_id' => $navigation_menu_item_id,
|
|
'locale' => $locale,
|
|
'setting_name' => 'remoteUrl',
|
|
'setting_value' => $url,
|
|
'setting_type' => 'string'
|
|
]);
|
|
}
|
|
}
|
|
}
|
|
|
|
$site = DB::table('site')
|
|
->select('supported_locales')
|
|
->first();
|
|
$supportedLocales = @unserialize($site->supported_locales);
|
|
if ($supportedLocales !== false) {
|
|
$navigationItems = DB::table('navigation_menu_items')->where('context_id', '0')->pluck('url', 'navigation_menu_item_id')->filter()->all();
|
|
foreach ($navigationItems as $navigation_menu_item_id => $url) {
|
|
foreach ($supportedLocales as $locale) {
|
|
DB::table('navigation_menu_item_settings')->insert([
|
|
'navigation_menu_item_id' => $navigation_menu_item_id,
|
|
'locale' => $locale,
|
|
'setting_name' => 'remoteUrl',
|
|
'setting_value' => $url,
|
|
'setting_type' => 'string'
|
|
]);
|
|
}
|
|
}
|
|
}
|
|
|
|
Schema::table('navigation_menu_items', function (Blueprint $table) {
|
|
$table->dropColumn('url');
|
|
});
|
|
}
|
|
|
|
/**
|
|
* Migrate submission files after major refactor
|
|
*
|
|
* - Add files table to manage underlying file storage
|
|
* - Replace the use of file_id/revision as a unique id with a single
|
|
* auto-incrementing submission_file_id, and update all references.
|
|
* - Move revisions to a submission_file_revisons table.
|
|
* - Drop unused columns in submission_files table.
|
|
*
|
|
* @see pkp/pkp-lib#6057
|
|
*/
|
|
protected function migrateSubmissionFiles()
|
|
{
|
|
// pkp/pkp-lib#6616 Delete submission_files entries that correspond to nonexistent submissions
|
|
$orphanedIds = DB::table('submission_files AS sf')->leftJoin('submissions AS s', 'sf.submission_id', '=', 's.submission_id')->whereNull('s.submission_id')->pluck('sf.submission_id', 'sf.file_id');
|
|
foreach ($orphanedIds as $fileId => $submissionId) {
|
|
error_log("Removing orphaned submission_files entry ID {$fileId} with submission_id {$submissionId}");
|
|
DB::table('submission_files')->where('file_id', '=', $fileId)->delete();
|
|
}
|
|
|
|
// Add partial index (DBMS-specific)
|
|
if (DB::connection() instanceof PostgresConnection) {
|
|
DB::unprepared("CREATE INDEX event_log_settings_name_value ON event_log_settings (setting_name, setting_value) WHERE setting_name IN ('fileId', 'submissionId')");
|
|
} else {
|
|
DB::unprepared('CREATE INDEX event_log_settings_name_value ON event_log_settings (setting_name(50), setting_value(150))');
|
|
}
|
|
|
|
// Create a new table to track files in file storage
|
|
Schema::create('files', function (Blueprint $table) {
|
|
$table->bigIncrements('file_id');
|
|
$table->string('path', 255);
|
|
$table->string('mimetype', 255);
|
|
});
|
|
|
|
// Create a new table to track submission file revisions
|
|
Schema::create('submission_file_revisions', function (Blueprint $table) {
|
|
$table->bigIncrements('revision_id');
|
|
$table->unsignedBigInteger('submission_file_id');
|
|
$table->unsignedBigInteger('file_id');
|
|
});
|
|
|
|
// Add columns to submission_files table
|
|
Schema::table('submission_files', function (Blueprint $table) {
|
|
$table->unsignedBigInteger('new_file_id')->nullable(); // Renamed and made not nullable at the end of the migration
|
|
});
|
|
|
|
// Drop unique keys that will cause trouble while we're migrating
|
|
Schema::table('review_round_files', function (Blueprint $table) {
|
|
$table->dropIndex('review_round_files_pkey');
|
|
});
|
|
|
|
// Create entry in files and revisions tables for every submission_file
|
|
$fileManager = new FileManager();
|
|
$rows = DB::table('submission_files', 'sf')
|
|
->join('submissions AS s', 'sf.submission_id', '=', 's.submission_id')
|
|
->leftJoin('review_round_files AS rrf', function (JoinClause $j) {
|
|
$j->on('rrf.submission_id', '=', 'sf.submission_id')
|
|
->on('rrf.file_id', '=', 'sf.file_id')
|
|
->on('rrf.revision', '=', 'sf.revision');
|
|
})
|
|
->orderBy('sf.file_id')
|
|
->orderBy('rrf.review_round_id')
|
|
->orderBy('sf.revision')
|
|
->get([
|
|
's.context_id',
|
|
'sf.file_id',
|
|
'sf.revision',
|
|
'sf.submission_id',
|
|
'sf.genre_id',
|
|
'sf.file_type',
|
|
'sf.file_stage',
|
|
'sf.date_uploaded',
|
|
'sf.original_file_name',
|
|
'rrf.review_round_id'
|
|
]);
|
|
$fileService = Services::get('file');
|
|
$lastFileId = null;
|
|
$lastReviewRoundId = null;
|
|
$lastInsertedFileId = DB::table('submission_files')->max('file_id');
|
|
$fileIdMap = [];
|
|
foreach ($rows as $row) {
|
|
// Due to the new database structure, a file_id that spans across N+1 review rounds needs to be forked to a new file_id, to avoid being consolidated later on the upgrade process, which would cause data loss
|
|
// Statistics, logs, etc. will keep being referenced by the "main" file_id, which is not ideal, but also not possible to address.
|
|
// This branch will be accessed once the review_round_id gets changed within a group of the same file_ids
|
|
if ($lastFileId === $row->file_id && $lastReviewRoundId !== $row->review_round_id) {
|
|
$fileIdMap = [
|
|
$row->file_id => ++$lastInsertedFileId,
|
|
'sourceFileId' => $fileIdMap[$row->file_id] ?? $row->file_id
|
|
];
|
|
DB::table('submission_file_settings')->insertUsing(
|
|
['file_id', 'locale', 'setting_name', 'setting_value', 'setting_type'],
|
|
function (Builder $q) use ($row, $lastInsertedFileId) {
|
|
$q->from('submission_file_settings')
|
|
->where('file_id', '=', $row->file_id)
|
|
->select(DB::raw($lastInsertedFileId), 'locale', 'setting_name', 'setting_value', 'setting_type');
|
|
}
|
|
);
|
|
}
|
|
|
|
// Reproduces the removed method SubmissionFile::_generateFileName()
|
|
// genre is %s because it can be blank with review attachments
|
|
$filename = sprintf(
|
|
'%d-%s-%d-%d-%d-%s.%s',
|
|
$row->submission_id,
|
|
$row->genre_id,
|
|
$row->file_id,
|
|
$row->revision,
|
|
$row->file_stage,
|
|
date('Ymd', strtotime($row->date_uploaded)),
|
|
strtolower_codesafe($fileManager->parseFileExtension($row->original_file_name))
|
|
);
|
|
$path = sprintf(
|
|
'%s/%s/%s',
|
|
$this->getContextPath() . '/' . $row->context_id . '/' . $this->getSubmissionPath() . '/' . $row->submission_id,
|
|
$this->_fileStageToPath($row->file_stage),
|
|
$filename
|
|
);
|
|
if (!$fileService->fs->has($path)) {
|
|
error_log("A submission file was expected but not found at {$path}.");
|
|
}
|
|
$newFileId = DB::table('files')->insertGetId([
|
|
'path' => $path,
|
|
'mimetype' => $row->file_type,
|
|
], 'file_id');
|
|
$fileId = $fileIdMap[$row->file_id] ?? $row->file_id;
|
|
$updateData = ['new_file_id' => $newFileId];
|
|
// If the file_id was forked into a new one, we've got to update the references at the review_round_files and the submission_file itself
|
|
if ($fileId !== $row->file_id) {
|
|
$updateData['file_id'] = $fileId;
|
|
$updateData['source_file_id'] = $fileIdMap['sourceFileId'];
|
|
DB::table('review_round_files')
|
|
->where('file_id', '=', $row->file_id)
|
|
->where('revision', '=', $row->revision)
|
|
->update(['file_id' => $fileId]);
|
|
}
|
|
DB::table('submission_files')
|
|
->where('file_id', $row->file_id)
|
|
->where('revision', $row->revision)
|
|
->update($updateData);
|
|
DB::table('submission_file_revisions')->insert([
|
|
'submission_file_id' => $fileId,
|
|
'file_id' => $newFileId,
|
|
]);
|
|
|
|
// Update revision data in event logs
|
|
DB::table('event_log_settings as els')
|
|
->join(
|
|
'event_log_settings as file_setting',
|
|
fn (JoinClause $join) =>
|
|
$join->on('file_setting.log_id', '=', 'els.log_id')
|
|
->where('file_setting.setting_name', '=', 'fileId')
|
|
->where('file_setting.setting_value', '=', (string) $row->file_id)
|
|
)
|
|
->where('els.setting_name', 'fileRevision')
|
|
->where('els.setting_value', '=', $row->revision)
|
|
->update(['els.setting_value' => $newFileId]);
|
|
|
|
$lastFileId = $row->file_id;
|
|
$lastReviewRoundId = $row->review_round_id;
|
|
}
|
|
|
|
// Collect rows that will be deleted because they are old revisions
|
|
// They are identified by the new_file_id column, which is the only unique
|
|
// column on the table at this point.
|
|
$newFileIdsToDelete = [];
|
|
|
|
// Get all the unique file_ids. For each one, determine the latest revision
|
|
// in order to keep it in the table. The others will be flagged for removal
|
|
foreach (DB::table('submission_files')->select('file_id')->distinct()->get() as $row) {
|
|
$submissionFileRows = DB::table('submission_files')
|
|
->where('file_id', '=', $row->file_id)
|
|
->orderBy('revision', 'desc')
|
|
->get([
|
|
'file_id',
|
|
'new_file_id',
|
|
]);
|
|
$latestFileId = $submissionFileRows[0]->new_file_id;
|
|
foreach ($submissionFileRows as $submissionFileRow) {
|
|
if ($submissionFileRow->new_file_id !== $latestFileId) {
|
|
$newFileIdsToDelete[] = $submissionFileRow->new_file_id;
|
|
}
|
|
}
|
|
}
|
|
|
|
// Delete the rows for old revisions (chunked for performance)
|
|
foreach (array_chunk($newFileIdsToDelete, 100) as $chunkFileIds) {
|
|
DB::table('submission_files')
|
|
->whereIn('new_file_id', $chunkFileIds)
|
|
->delete();
|
|
}
|
|
|
|
// Remove all review round files that point to file ids
|
|
// that don't exist, so that the foreign key can be set
|
|
// up successfully.
|
|
// See: https://github.com/pkp/pkp-lib/issues/6337
|
|
DB::table('review_round_files as rrf')
|
|
->leftJoin('submission_files as sf', 'sf.file_id', '=', 'rrf.file_id')
|
|
->whereNotNull('rrf.file_id')
|
|
->whereNull('sf.file_id')
|
|
->delete();
|
|
|
|
// Update review round files
|
|
$rows = DB::table('review_round_files')->get();
|
|
foreach ($rows as $row) {
|
|
// Delete this row if another revision exists for this
|
|
// submission file. This ensures that when the revision
|
|
// column is dropped the submission_file_id column will
|
|
// be unique.
|
|
$count = DB::table('review_round_files')
|
|
->where('file_id', '=', $row->file_id)
|
|
->count();
|
|
if ($count > 1) {
|
|
DB::table('review_round_files')
|
|
->where('file_id', '=', $row->file_id)
|
|
->where('revision', '=', $row->revision)
|
|
->delete();
|
|
continue;
|
|
}
|
|
|
|
// Set assoc_type and assoc_id for all review round files
|
|
// Run this before migration to internal review file stages
|
|
DB::table('submission_files')
|
|
->where('file_id', '=', $row->file_id)
|
|
->whereIn('file_stage', [self::SUBMISSION_FILE_REVIEW_FILE, self::SUBMISSION_FILE_REVIEW_REVISION])
|
|
->update([
|
|
'assoc_type' => self::ASSOC_TYPE_REVIEW_ROUND,
|
|
'assoc_id' => $row->review_round_id,
|
|
]);
|
|
}
|
|
|
|
// Update name of event log params to reflect new file structure
|
|
DB::table('event_log_settings')
|
|
->where('setting_name', 'fileId')
|
|
->update(['setting_name' => 'submissionFileId']);
|
|
DB::table('event_log_settings')
|
|
->where('setting_name', 'fileRevision')
|
|
->update(['setting_name' => 'fileId']);
|
|
|
|
// Update file name of dependent files, see: pkp/pkp-lib#6801
|
|
DB::table('submission_files')
|
|
->select('file_id', 'original_file_name')
|
|
->where('file_stage', '=', self::SUBMISSION_FILE_DEPENDENT)
|
|
->chunkById(1000, function ($dependentFiles) {
|
|
foreach ($dependentFiles as $dependentFile) {
|
|
DB::table('submission_file_settings')
|
|
->where('file_id', '=', $dependentFile->file_id)
|
|
->where('setting_name', '=', 'name')
|
|
->update(['setting_value' => $dependentFile->original_file_name]);
|
|
}
|
|
}, 'file_id');
|
|
|
|
// Restructure submission_files and submission_file_settings tables
|
|
Schema::table('submission_files', function (Blueprint $table) {
|
|
$table->bigInteger('file_id')->nullable(false)->unsigned()->change();
|
|
});
|
|
Schema::table('submission_files', function (Blueprint $table) {
|
|
$table->dropPrimary(); // Drop compound primary key constraint
|
|
});
|
|
Schema::table('submission_files', function (Blueprint $table) {
|
|
$table->renameColumn('file_id', 'submission_file_id');
|
|
$table->renameColumn('new_file_id', 'file_id');
|
|
$table->renameColumn('source_file_id', 'source_submission_file_id');
|
|
$table->renameColumn('date_uploaded', 'created_at');
|
|
$table->renameColumn('date_modified', 'updated_at');
|
|
$table->dropColumn('revision');
|
|
$table->dropColumn('source_revision');
|
|
$table->dropColumn('file_size');
|
|
$table->dropColumn('file_type');
|
|
$table->dropColumn('original_file_name');
|
|
$table->primary('submission_file_id');
|
|
});
|
|
// pkp/pkp-lib#5804
|
|
$schemaManager = DB::getDoctrineSchemaManager();
|
|
if (!in_array('submission_files_stage_assoc', array_keys($schemaManager->listTableIndexes('submission_files')))) {
|
|
Schema::table('submission_files', function (Blueprint $table) {
|
|
$table->index(['file_stage', 'assoc_type', 'assoc_id'], 'submission_files_stage_assoc');
|
|
});
|
|
}
|
|
// Modify column types and attributes in separate migration
|
|
// function to prevent error in postgres with unfound columns
|
|
Schema::table('submission_files', function (Blueprint $table) {
|
|
$table->bigInteger('submission_file_id')->autoIncrement()->unsigned()->change();
|
|
$table->bigInteger('file_id')->nullable(false)->unsigned()->change();
|
|
$table->foreign('file_id')->references('file_id')->on('files');
|
|
});
|
|
Schema::table('submission_file_settings', function (Blueprint $table) {
|
|
$table->renameColumn('file_id', 'submission_file_id');
|
|
$table->string('setting_type', 6)->default('string')->change();
|
|
});
|
|
|
|
// Update columns in related tables
|
|
Schema::table('review_round_files', function (Blueprint $table) {
|
|
$table->renameColumn('file_id', 'submission_file_id');
|
|
$table->dropColumn('revision');
|
|
});
|
|
Schema::table('review_round_files', function (Blueprint $table) {
|
|
$table->bigInteger('submission_file_id')->nullable(false)->unique()->unsigned()->change();
|
|
$table->unique(['submission_id', 'review_round_id', 'submission_file_id'], 'review_round_files_pkey');
|
|
$table->foreign('submission_file_id')->references('submission_file_id')->on('submission_files');
|
|
});
|
|
Schema::table('review_files', function (Blueprint $table) {
|
|
$table->renameColumn('file_id', 'submission_file_id');
|
|
});
|
|
|
|
// pkp/pkp-lib#6616 Delete review_files entries that correspond to nonexistent submission_files
|
|
$orphanedIds = DB::table('review_files AS rf')
|
|
->select('rf.submission_file_id', 'rf.review_id')
|
|
->leftJoin('submission_files AS sf', 'rf.submission_file_id', '=', 'sf.submission_file_id')
|
|
->whereNull('sf.submission_file_id')
|
|
->whereNotNull('rf.submission_file_id')
|
|
->get();
|
|
foreach ($orphanedIds as $orphanedId) {
|
|
$reviewId = $orphanedId->{'review_id'};
|
|
$submissionFileId = $orphanedId->{'submission_file_id'};
|
|
error_log("Removing orphaned review_files entry with review_id ID {$reviewId} and submission_file_id {$submissionFileId}");
|
|
DB::table('review_files')
|
|
->where('submission_file_id', '=', $submissionFileId)
|
|
->where('review_id', '=', $reviewId)
|
|
->delete();
|
|
}
|
|
|
|
Schema::table('review_files', function (Blueprint $table) {
|
|
$table->bigInteger('submission_file_id')->nullable(false)->unsigned()->change();
|
|
$table->dropIndex('review_files_pkey');
|
|
$table->unique(['review_id', 'submission_file_id'], 'review_files_pkey');
|
|
|
|
$table->foreign('submission_file_id')->references('submission_file_id')->on('submission_files');
|
|
});
|
|
Schema::table('submission_file_revisions', function (Blueprint $table) {
|
|
$table->foreign('submission_file_id')->references('submission_file_id')->on('submission_files');
|
|
$table->foreign('file_id')->references('file_id')->on('files');
|
|
});
|
|
|
|
// Postgres leaves the old file_id autoincrement sequence around, so
|
|
// we delete it and apply a new sequence.
|
|
if (DB::connection() instanceof PostgresConnection) {
|
|
DB::statement('DROP SEQUENCE submission_files_file_id_seq CASCADE');
|
|
Schema::table('submission_files', function (Blueprint $table) {
|
|
$table->bigIncrements('submission_file_id')->change();
|
|
});
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Get the directory of a file based on its file stage
|
|
*
|
|
* @param int $fileStage One of SubmissionFile::SUBMISSION_FILE_ constants
|
|
*
|
|
* @return string
|
|
*/
|
|
private function _fileStageToPath($fileStage)
|
|
{
|
|
static $fileStagePathMap = [
|
|
self::SUBMISSION_FILE_SUBMISSION => 'submission',
|
|
self::SUBMISSION_FILE_NOTE => 'note',
|
|
self::SUBMISSION_FILE_REVIEW_FILE => 'submission/review',
|
|
self::SUBMISSION_FILE_REVIEW_ATTACHMENT => 'submission/review/attachment',
|
|
self::SUBMISSION_FILE_REVIEW_REVISION => 'submission/review/revision',
|
|
self::SUBMISSION_FILE_FINAL => 'submission/final',
|
|
self::SUBMISSION_FILE_FAIR_COPY => 'submission/fairCopy',
|
|
self::SUBMISSION_FILE_EDITOR => 'submission/editor',
|
|
self::SUBMISSION_FILE_COPYEDIT => 'submission/copyedit',
|
|
self::SUBMISSION_FILE_DEPENDENT => 'submission/proof',
|
|
self::SUBMISSION_FILE_PROOF => 'submission/proof',
|
|
self::SUBMISSION_FILE_PRODUCTION_READY => 'submission/productionReady',
|
|
self::SUBMISSION_FILE_ATTACHMENT => 'attachment',
|
|
self::SUBMISSION_FILE_QUERY => 'submission/query'
|
|
];
|
|
|
|
if (!isset($fileStagePathMap[$fileStage])) {
|
|
error_log('A file assigned to the file stage ' . $fileStage . ' could not be migrated.');
|
|
}
|
|
|
|
return $fileStagePathMap[$fileStage] ?? null;
|
|
}
|
|
|
|
/**
|
|
* Update block names to be all lowercase
|
|
*
|
|
* In previous versions, a custom block name would be stored in the
|
|
* array of blocks with capitals but the block name in the plugin_settings
|
|
* table is all lowercase. This migration aligns the two places by changing
|
|
* the block names to always use lowercase.
|
|
*
|
|
*/
|
|
private function _fixCapitalCustomBlockTitles()
|
|
{
|
|
$rows = DB::table('plugin_settings')
|
|
->where('plugin_name', 'customblockmanagerplugin')
|
|
->where('setting_name', 'blocks')
|
|
->get();
|
|
foreach ($rows as $row) {
|
|
$updateBlocks = false;
|
|
$blocks = unserialize($row->setting_value);
|
|
foreach ($blocks as $key => $block) {
|
|
$newBlock = strtolower_codesafe($block);
|
|
if ($block !== $newBlock) {
|
|
$blocks[$key] = $newBlock;
|
|
$updateBlocks = true;
|
|
}
|
|
}
|
|
if ($updateBlocks) {
|
|
DB::table('plugin_settings')
|
|
->where('plugin_name', 'customblockmanagerplugin')
|
|
->where('setting_name', 'blocks')
|
|
->where('context_id', $row->context_id)
|
|
->update(['setting_value' => serialize($blocks)]);
|
|
}
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Create titles for custom block plugins
|
|
*
|
|
* This method copies the block names, which are a unique id,
|
|
* into a block setting, `blockTitle`, in the context's
|
|
* primary locale
|
|
*
|
|
* @see https://github.com/pkp/pkp-lib/issues/5619
|
|
*/
|
|
private function _createCustomBlockTitles()
|
|
{
|
|
$rows = DB::table('plugin_settings')
|
|
->where('plugin_name', 'customblockmanagerplugin')
|
|
->where('setting_name', 'blocks')
|
|
->get();
|
|
|
|
$newRows = [];
|
|
foreach ($rows as $row) {
|
|
$locale = DB::table($this->getContextTable())
|
|
->where($this->getContextKeyField(), $row->context_id)
|
|
->first()
|
|
->primary_locale;
|
|
$blocks = unserialize($row->setting_value);
|
|
foreach ($blocks as $block) {
|
|
$newRows[] = [
|
|
'plugin_name' => $block,
|
|
'context_id' => $row->context_id,
|
|
'setting_name' => 'blockTitle',
|
|
'setting_value' => serialize([$locale => $block]),
|
|
'setting_type' => 'object',
|
|
];
|
|
}
|
|
}
|
|
if (!DB::table('plugin_settings')->insert($newRows)) {
|
|
error_log('Failed to create title for custom blocks. This can be fixed manually by editing each custom block and adding a title.');
|
|
}
|
|
}
|
|
|
|
/**
|
|
* @brief reset serialized arrays and convert array and objects to JSON for serialization, see pkp/pkp-lib#5772
|
|
*/
|
|
private function _settingsAsJSON()
|
|
{
|
|
$serializedSettings = $this->getSerializedSettings();
|
|
$processedTables = [];
|
|
foreach ($serializedSettings as $tableName => $settings) {
|
|
$processedTables[] = $tableName;
|
|
foreach ($settings as $setting) {
|
|
DB::table($tableName)->where('setting_name', $setting)->get()->each(function ($row) use ($tableName) {
|
|
$this->_toJSON($row, $tableName, ['setting_name', 'locale'], 'setting_value');
|
|
});
|
|
}
|
|
}
|
|
|
|
// Convert settings where only setting_type column is available
|
|
$tables = DB::getDoctrineSchemaManager()->listTableNames();
|
|
foreach ($tables as $tableName) {
|
|
if (substr($tableName, -9) !== '_settings' || in_array($tableName, $processedTables)) {
|
|
continue;
|
|
}
|
|
if ($tableName === 'plugin_settings') {
|
|
DB::table($tableName)->where('setting_type', 'object')->get()->each(function ($row) use ($tableName) {
|
|
$this->_toJSON($row, $tableName, ['plugin_name', 'context_id', 'setting_name'], 'setting_value');
|
|
});
|
|
} elseif (Schema::hasColumn($tableName, 'setting_type')) {
|
|
try {
|
|
$settings = DB::table($tableName, 's')->where('setting_type', 'object')->get(['setting_name', 'setting_value', 's.*']);
|
|
} catch (Exception $e) {
|
|
error_log("Failed to migrate the settings entity \"{$tableName}\"\n" . $e);
|
|
continue;
|
|
}
|
|
$settings->each(fn ($row) => $this->_toJSON($row, $tableName, ['setting_name', 'locale'], 'setting_value'));
|
|
}
|
|
}
|
|
|
|
// Finally, convert values of other tables dependent from DAO::convertToDB
|
|
if (Schema::hasTable('review_form_responses')) {
|
|
DB::table('review_form_responses')->where('response_type', 'object')->get()->each(function ($row) {
|
|
$this->_toJSON($row, 'review_form_responses', ['review_id'], 'response_value');
|
|
});
|
|
}
|
|
|
|
DB::table('site')->get()->each(function ($row) {
|
|
$localeToConvert = function ($localeType) use ($row) {
|
|
$serializedValue = $row->{$localeType};
|
|
$oldLocaleValue = @unserialize($serializedValue);
|
|
if ($oldLocaleValue === false) {
|
|
return;
|
|
}
|
|
|
|
if (is_array($oldLocaleValue) && $this->_isNumerical($oldLocaleValue)) {
|
|
$oldLocaleValue = array_values($oldLocaleValue);
|
|
}
|
|
|
|
$newLocaleValue = json_encode($oldLocaleValue, JSON_UNESCAPED_UNICODE);
|
|
DB::table('site')->take(1)->update([$localeType => $newLocaleValue]);
|
|
};
|
|
|
|
$localeToConvert('installed_locales');
|
|
$localeToConvert('supported_locales');
|
|
});
|
|
}
|
|
|
|
/**
|
|
* @param object $row row representation
|
|
* @param string $tableName name of a settings table
|
|
* @param array $searchBy additional parameters to the where clause that should be combined with AND operator
|
|
* @param string $valueToConvert column name for values to convert to JSON
|
|
*/
|
|
private function _toJSON($row, $tableName, $searchBy, $valueToConvert)
|
|
{
|
|
// Check if value can be unserialized
|
|
$serializedOldValue = $row->{$valueToConvert};
|
|
$oldValue = @unserialize($serializedOldValue);
|
|
if ($oldValue === false) {
|
|
return;
|
|
}
|
|
|
|
// Reset arrays to avoid keys being mixed up
|
|
if (is_array($oldValue) && $this->_isNumerical($oldValue)) {
|
|
$oldValue = array_values($oldValue);
|
|
}
|
|
$newValue = json_encode($oldValue, JSON_UNESCAPED_UNICODE); // don't convert utf-8 characters to unicode escaped code
|
|
|
|
$id = array_key_first((array)$row); // get first/primary key column
|
|
|
|
// Remove empty filters
|
|
$searchBy = array_filter($searchBy, function ($item) use ($row) {
|
|
if (empty($row->{$item})) {
|
|
return false;
|
|
}
|
|
return true;
|
|
});
|
|
|
|
$queryBuilder = DB::table($tableName)->where($id, $row->{$id});
|
|
foreach ($searchBy as $key => $column) {
|
|
$queryBuilder = $queryBuilder->where($column, $row->{$column});
|
|
}
|
|
$queryBuilder->update([$valueToConvert => $newValue]);
|
|
}
|
|
|
|
/**
|
|
* @param array $array to check
|
|
*
|
|
* @return bool
|
|
*
|
|
* @brief checks unserialized array; returns true if array keys are integers
|
|
* otherwise if keys are mixed and sequence starts from any positive integer it will be serialized as JSON object instead of an array
|
|
* See pkp/pkp-lib#5690 for more details
|
|
*/
|
|
private function _isNumerical($array)
|
|
{
|
|
foreach ($array as $item => $value) {
|
|
if (!is_integer($item)) {
|
|
return false;
|
|
} // is an associative array;
|
|
}
|
|
|
|
return true;
|
|
}
|
|
}
|