In academic publishing, it’s common to receive article metadata in formats like Excel, XML, or CSV, with associated PDF files hosted remotely. When integrating such content into Open Journal Systems (OJS) 3.3, referencing PDFs via remote URLs can lead to challenges, including:

• Performance Issues: Relying on external servers can slow down access.
• Availability Concerns: If the remote server is down, the PDF becomes inaccessible.
• Integration Limitations: Features like OJS’s PDF viewer may not function correctly with remote files.

To address these issues, it’s advisable to convert remote galley files into local files within your OJS 3.3 installation. This guide provides a step-by-step approach to achieve this.

Prerequisites

• Backup Your Database: Always ensure you have a recent backup.
• Organize Your Files: Place all PDF files in a structured directory within OJS’s files directory, maintaining a consistent hierarchy.
• Prepare Metadata: Ensure you have article metadata available in Excel, XML, or CSV format.

Step-by-Step Conversion Process

1. Identify Remote Galley Entries

Locate all galley entries in the publication_galleys table that reference remote URLs:

SELECT * FROM publication_galleys WHERE remote_url IS NOT NULL;

2. Insert Records into the files Table

For each remote galley, insert a corresponding record into the files table, using the remote URL as the path:

INSERT INTO files (path, mimetype)
SELECT remote_url, 'application/pdf'
FROM publication_galleys
WHERE remote_url IS NOT NULL AND submission_file_id IS NULL;

3. Create Entries in the submission_files Table

Associate each new file with its corresponding submission:

INSERT INTO submission_files (
submission_id, file_id, source_submission_file_id, genre_id, file_stage,
direct_sales_price, sales_type, viewable,
created_at, updated_at, uploader_user_id, assoc_type, assoc_id
)
SELECT
p.submission_id,
f.file_id,
NULL,
1,
10,
NULL, NULL, 1,
NOW(), NOW(),
1,
521,
g.publication_id
FROM publication_galleys g
JOIN publications p ON g.publication_id = p.publication_id
JOIN files f ON f.path = g.remote_url
WHERE g.remote_url IS NOT NULL AND g.submission_file_id IS NULL;

4. Update the publication_galleys Table

Update each galley to reference the new local file and remove the remote URL:

UPDATE publication_galleys g
JOIN publications p ON g.publication_id = p.publication_id
JOIN submission_files sf ON sf.assoc_id = g.publication_id AND sf.submission_id = p.submission_id
JOIN files f ON f.file_id = sf.file_id AND f.path = g.remote_url
SET g.submission_file_id = sf.submission_file_id,
g.remote_url = NULL
WHERE g.remote_url IS NOT NULL AND g.submission_file_id IS NULL;

5. Set the Original File Name

To maintain proper file naming during downloads, insert entries into the submission_file_settings table:

INSERT INTO submission_file_settings (
submission_file_id, locale, setting_name, setting_value, setting_type
)
SELECT
sf.submission_file_id,
'en_US',
'name',
SUBSTRING_INDEX(f.path, '/', -1),
'string'
FROM submission_files sf
JOIN files f ON f.file_id = sf.file_id
LEFT JOIN submission_file_settings sfs ON sfs.submission_file_id = sf.submission_file_id AND sfs.setting_name = 'name'
WHERE sfs.submission_file_id IS NULL;

6. Adjust File Paths (If Necessary)

If your files are now stored locally, update the files.path entries to reflect their new locations:

UPDATE files
SET path = REPLACE(path, 'http://remote.domain/', '')
WHERE path LIKE 'http://remote.domain/%';

Replace 'http://remote.domain/' with the actual domain used in your remote URLs.

Conclusion

By following these steps, you can effectively migrate remote galley files to local storage within OJS 3.3, enhancing performance, reliability, and integration capabilities.

If you require assistance with bulk imports, data migration, or OJS customization, feel free to contact us. We’re here to support your publishing needs.