Server : Apache/2.4.41 (Ubuntu) System : Linux journalup 5.4.0-198-generic #218-Ubuntu SMP Fri Sep 27 20:18:53 UTC 2024 x86_64 User : www-data ( 33) PHP Version : 7.4.33 Disable Function : pcntl_alarm,pcntl_fork,pcntl_waitpid,pcntl_wait,pcntl_wifexited,pcntl_wifstopped,pcntl_wifsignaled,pcntl_wifcontinued,pcntl_wexitstatus,pcntl_wtermsig,pcntl_wstopsig,pcntl_signal,pcntl_signal_get_handler,pcntl_signal_dispatch,pcntl_get_last_error,pcntl_strerror,pcntl_sigprocmask,pcntl_sigwaitinfo,pcntl_sigtimedwait,pcntl_exec,pcntl_getpriority,pcntl_setpriority,pcntl_async_signals,pcntl_unshare, Directory : /var/www/html/dbscripts/xml/upgrade/
Upload File :
Current File : /var/www/html/dbscripts/xml/upgrade/3.2.0_versioning.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE data SYSTEM "../../../lib/pkp/dtd/xmlData.dtd">
<!--
* dbscripts/xml/upgrade/3.2.0_versioning.xml
*
* Copyright (c) 2013-2020 Simon Fraser University
* Copyright (c) 2003-2020 John Willinsky
* Distributed under the GNU GPL v3. For full terms see the file docs/COPYING.
*
* Copy submission data to temporary tables so it can be updated
* after the publication table modifications are made.
*
-->
<data>
<sql>
<!-- change date_published from datetime to date before migrating -->
<query driver="mysql,mysqli">ALTER TABLE temp_published_submissions MODIFY COLUMN date_published DATE NULL</query>
<query driver="postgres,postgres64,postgres7,postgres8,postgres9">ALTER TABLE temp_published_submissions ALTER COLUMN date_published TYPE DATE</query>
<!-- migrate data to new publications table -->
<query>
INSERT INTO publications (access_status, date_published, last_modified, locale, section_id, seq, submission_id, status, version)
SELECT COALESCE(ps.access_status, 0), COALESCE(ps.date_published, NULL), s.last_modified, s.locale, s.section_id, COALESCE(ps.seq, 0), s.submission_id, s.status, 1
FROM temp_submissions as s
LEFT JOIN temp_published_submissions as ps
ON (s.submission_id = ps.submission_id)
GROUP BY s.submission_id, ps.access_status, ps.date_published, s.last_modified, s.section_id, ps.seq, s.locale, s.status;
</query>
<query>
UPDATE publications as p
SET primary_contact_id = (
SELECT a.author_id
FROM temp_authors as a
WHERE a.submission_id = p.submission_id
AND a.primary_contact = 1
LIMIT 1
)
</query>
<!-- set the current_publication_id column in submissions -->
<query>
UPDATE submissions as s
SET current_publication_id = (
SELECT p.publication_id
FROM publications as p
WHERE s.submission_id = p.submission_id
)
</query>
<!-- migrate submission_settings to publication_settings -->
<query>
INSERT INTO publication_settings(publication_id, locale, setting_name, setting_value)
SELECT s.current_publication_id, ss.locale, ss.setting_name, ss.setting_value
FROM submissions as s
LEFT JOIN submission_settings as ss
ON (ss.submission_id = s.submission_id)
WHERE (
setting_name IN ('abstract', 'cleanTitle', 'copyrightHolder', 'copyrightYear', 'coverage', 'coverImage', 'licenseUrl', 'locale', 'prefix', 'rights', 'source', 'subtitle', 'title', 'type')
OR setting_name LIKE '%pub-id%'
)
</query>
<query>
DELETE FROM submission_settings
WHERE (
setting_name IN ('abstract', 'cleanTitle', 'copyrightHolder', 'copyrightYear', 'coverage', 'coverImage', 'coverImageAltText', 'licenseUrl', 'locale', 'prefix', 'rights', 'source', 'subtitle', 'title', 'type')
OR setting_name LIKE '%pub-id%'
)
</query>
<!-- migrate some data from submissions to publication_settings -->
<query>
INSERT INTO publication_settings(publication_id, setting_name, setting_value)
SELECT s.current_publication_id, 'language', ts.language
FROM submissions as s
LEFT JOIN temp_submissions as ts
ON s.submission_id = ts.submission_id
WHERE ts.language != ''
</query>
<query>
INSERT INTO publication_settings(publication_id, setting_name, setting_value)
SELECT s.current_publication_id, 'citationsRaw', ts.citations
FROM submissions as s
LEFT JOIN temp_submissions as ts
ON s.submission_id = ts.submission_id
WHERE ts.citations IS NOT NULL
</query>
<query>
INSERT INTO publication_settings(publication_id, setting_name, setting_value)
SELECT s.current_publication_id, 'pages', ts.pages
FROM submissions as s
LEFT JOIN temp_submissions as ts
ON s.submission_id = ts.submission_id
WHERE ts.pages IS NOT NULL
</query>
<query>
INSERT INTO publication_settings(publication_id, setting_name, setting_value)
SELECT s.current_publication_id, 'hide_author', ts.hide_author
FROM submissions as s
LEFT JOIN temp_submissions as ts
ON s.submission_id = ts.submission_id
WHERE ts.hide_author IS NOT NULL
</query>
<!-- migrate issue_id from published_submissions to publication settings -->
<query>
INSERT INTO publication_settings(publication_id, setting_name, setting_value)
SELECT s.current_publication_id, 'issueId', ps.issue_id
FROM temp_published_submissions as ps
LEFT JOIN submissions as s
ON ps.submission_id = s.submission_id
WHERE s.current_publication_id IS NOT NULL
</query>
<!-- move some author data to settings table -->
<query driver="postgres,postgres64,postgres7,postgres8,postgres9">ALTER TABLE author_settings ALTER COLUMN setting_type DROP NOT NULL</query>
<query>
INSERT INTO author_settings(author_id, setting_name, setting_value)
SELECT ta.author_id, 'country', ta.country
FROM temp_authors as ta
</query>
<query>
INSERT INTO author_settings(author_id, setting_name, setting_value)
SELECT ta.author_id, 'url', ta.url
FROM temp_authors as ta
WHERE ta.url IS NOT NULL
</query>
<!-- connect authors to publications -->
<query driver="postgres,postgres64,postgres7,postgres8,postgres9">
UPDATE authors
SET publication_id = s.current_publication_id
FROM submissions s, temp_authors ta
WHERE authors.author_id = ta.author_id AND s.submission_id = ta.submission_id
</query>
<query driver="mysql,mysqli">
UPDATE authors as a
SET a.publication_id = (
SELECT s.current_publication_id
FROM submissions as s
LEFT JOIN temp_authors as ta
ON s.submission_id = ta.submission_id
WHERE a.author_id = ta.author_id
)
</query>
<!-- move submission_galleys to publication_galleys -->
<query>
INSERT INTO publication_galleys(galley_id, locale, publication_id, label, file_id, seq, remote_url, is_approved)
SELECT tsg.galley_id, tsg.locale, s.current_publication_id, tsg.label, tsg.file_id, tsg.seq, tsg.remote_url, 1
FROM submissions as s, temp_submission_galleys as tsg
WHERE s.submission_id = tsg.submission_id
</query>
<query>
INSERT INTO publication_galley_settings(galley_id, locale, setting_name, setting_value)
SELECT tsgs.galley_id, tsgs.locale, tsgs.setting_name, tsgs.setting_value
FROM submission_galley_settings as tsgs
WHERE tsgs.galley_id IS NOT NULL
</query>
<!-- connect controlled vocab metadata to publications, 1048585 = ASSOC_TYPE_SUBMISSION, 1048588 = ASSOC_TYPE_PUBLICATION -->
<query driver="postgres,postgres64,postgres7,postgres8,postgres9">
UPDATE controlled_vocabs
SET assoc_type = 1048588, assoc_id = s.current_publication_id
FROM submissions s
WHERE assoc_type = 1048585 AND assoc_id = s.submission_id
</query>
<query driver="mysql,mysqli">
UPDATE controlled_vocabs AS cv,
submissions AS s
SET cv.assoc_type = 1048588, cv.assoc_id = s.current_publication_id
WHERE cv.assoc_type = 1048585 AND s.submission_id = cv.assoc_id
</query>
<!-- connect citations to publications -->
<!-- To avoid collisions per pkp/pkp-lib#5795, first flip to negative, then clean up. -->
<query driver="postgres,postgres64,postgres7,postgres8,postgres9">
UPDATE citations
SET publication_id = -s.current_publication_id
FROM submissions s
JOIN temp_citations as tc ON (s.submission_id = tc.submission_id)
WHERE citations.citation_id = tc.citation_id
</query>
<query driver="postgres,postgres64,postgres7,postgres8,postgres9"><![CDATA[
UPDATE citations SET publication_id = -publication_id WHERE publication_id < 0
]]></query>
<query driver="mysql,mysqli">
UPDATE citations as c
SET c.publication_id = (
SELECT s.current_publication_id
FROM submissions as s
JOIN temp_citations as tc ON s.submission_id = tc.submission_id
WHERE c.citation_id = tc.citation_id
) ORDER BY c.publication_id ASC
</query>
<!-- update the DOI setting names -->
<query>
UPDATE plugin_settings
SET setting_name='doiPublicationSuffixPattern'
WHERE setting_name='doiSubmissionSuffixPattern'
AND plugin_name='doipubidplugin'
</query>
<query>
UPDATE plugin_settings
SET setting_name='enablePublicationDoi'
WHERE setting_name='enableSubmissionDoi'
AND plugin_name='doipubidplugin'
</query>
<!-- update the URN setting names -->
<query>
UPDATE plugin_settings
SET setting_name='urnPublicationSuffixPattern'
WHERE setting_name='urnSubmissionSuffixPattern'
AND plugin_name='urnpubidplugin'
</query>
<query>
UPDATE plugin_settings
SET setting_name='enablePublicationURN'
WHERE setting_name='enableSubmissionURN'
AND plugin_name='urnpubidplugin'
</query>
<!-- remove deprecated and temporary tables. should be last commands -->
<drop table="submission_galleys" />
<drop table="submission_galley_settings" />
<drop table="published_submissions" />
<drop table="temp_authors" />
<drop table="temp_submissions" />
<drop table="temp_submission_galleys" />
<drop table="temp_published_submissions" />
<drop table="temp_citations" />
</sql>
</data>
