Moving one journal to another OJS

[tarjelavik]

Hi!
I am planning to move one or two journals, which both have their own 2.1.1. installation, to a new 2.2.2 installation which will host many journals. I want to keep all the histories, the rejected articles, email sent and recieved, users... well everything.

There is four journal on the new installation. How should one go about this move? Will there be a conflict between journals (two journals being called "2")? Should we upgrade to 2.2.2, then try to extract the journal information for the database and import it to the new database? What are the potential problems?

I am no expert when it comes to mysql, luckily I have a very competent IT-staff doing the technical stuff. Thanks in advance!

Sincerly
Tarje Sælen Lavik
University of Bergen Library

[mcrider]

Hi Tarje,

This might be a painful trial-and-error process, but if you want all of the journal data transferred over, it should be possible. One question; is the new installation you want to transfer your journals to already up and running? If not, why not just upgrade your current installation and add the new journals to it (and delete/hide the journals you don't want)?

Otherwise, the idea of upgrading your old system and extracting the relevant data to the new DB seems like a good one. If journals have the same ID's, there will indeed be a conflict, but if you go through your tables and change the old journal id (which should always be marked by a column labelled journal_id) to something unique, you should be fine. There may be other elements of the system that require unique IDs regardless of what journal they belong to, but I'm not sure--I think published articles are one of them. I will pass this post on to the lead developer to see if he has any input.

I'll also add that I have no idea how easy or hard this would be--If you change the relevant DB entries to have unique IDs and merge the two DBs, everything might work easily, but there could very well be tons of data inconsistency problems.

Cheers,
Matt

[asmecher]

Hi Tarje,

Just to flesh out what Matt's explaining: if you need to merge two installations into one, you will indeed need to translate database identifiers. This will be tricky, and if I needed to do it, I'd probably a) look for a tool that might help, and if I couldn't find one, b) code one. Simply translating IDs by hand is too error-prone.

I'd suggest upgrading both of your installs, so that you're dealing with the same version; give the upgrade a while to settle in, so that you're not trying to deal with upgrade problems at the same time as you're trying to merge the two installs. When that's finished, consider the process I outline here:

All the IDs you'll need to translate are described in dbscripts/xml/ojs_schema.xml as <AUTOINCREMENT/> columns. In both installs, these will start at 1 and work their way upwards. I'd suggest taking all the IDs in one of your journals and adding a large number (say, 10000) that's bigger than any ID used in the other database. This will prevent the IDs from conflicting between the two installs. You'll need to do this to each table with an AUTOINCREMENT column, and also to all tables that refer to that column (i.e. the "journal_id" column in the journals table, but also the foreign key journal_id columns in user_settings, journal_settings, plugin_settings, roles, articles, etc). Then make sure you reset the MySQL or PostgreSQL sequence that assigns the numbers to something above the biggest ID; the SQL syntax for this will depend on which database you're using (and what version).

The columns are all logically named, i.e. journal IDs will all appear in columns named journal_id, with one exception: columns named assoc_id will be foreign keys to a number of different tables, depending on the value of assoc_type. (If you simply choose a large number to add to all foreign keys and primary keys, you won't need to worry about this -- simply add the same number to any column called assoc_id.)

The only other consideration is filenames. Submission files etc. are named according to database IDs, so you'll have to consider how to avoid conflicts in these when you merge the two installations together. Article files are named e.g.:

Code: Select all

journals/1/articles/1/submission/original/1-1-1-SM.odt

...which can be interpreted as:

Code: Select all

journals/[journal_id]/articles/[article_id]/submission/original/[article_id]-[file_id]-[revision_id]-SM.odt

Follow the same convention as above to relocate and rename the files.

Again, some scripting will help immensely in getting this done correctly and will save a lot of labour. Flush your cache frequently when testing this out to avoid headaches due to outdated caches hanging around. You can do this most easily by deleting all .php files in the "cache" directory.

Hope this helps somewhat. Please let me know if you have any questions.

Regards,
Alec Smecher
Public Knowledge Project Team

[tarjelavik]

Thank you both for very informative answers!

We opted for separate installations for each journal, since the version at that time did not support themes. Now we have to cut costs and make it easier to make new journals. So, IT upgraded one of three installations and we made this our main OJS. I wish we started out differently, but...

Let say that we at one point publish our 10000th article (not likely, just trying to think about everything ). Would the autoincrement just keep going and assign a number already set during this merge? Would it check and skip numbers already in use? Or could we add, let say 1000, and off-set the counter afterwards? We use postgresql. Stupid question maybe.

[asmecher]

Hi tarjelavik,

Even if you publish more than 10000 articles, you'll still be fine -- it's just a way to get both datasets in the same database without conflicts. Once they're both in the database, you'll need to reset the sequences to start counting from the highest value. For example, if journal 1 uses article_ids 1 to 675, and journal 2 uses numbers 1000 to 1821, you can put the two datasets in the same "articles" table since there is no overlap. There will be a gap between article_id 676 and 999, but this won't cause any problems -- these IDs will simply be unused. The last step will be to reset the sequence PostgreSQL uses to generate article_id values to begin at #1822. Do this e.g. by determining the first available article_id in the table:

Code: Select all

SELECT MAX(article_id)+1 FROM articles;

...then use that value to reset the sequence:

Code: Select all

ALTER SEQUENCE xxxxx RESTART WITH 1822;

You can get the sequence name for a table's ID column by using the "describe" command, i.e.:

Code: Select all

ojs2t=# \d articles
                                              Table "public.articles"
        Column        |            Type             |                           Modifiers                           
----------------------+-----------------------------+---------------------------------------------------------------
 article_id           | integer                     | not null default nextval('articles_article_id_seq'::regclass)
 user_id              | bigint                      | not null
 journal_id           | bigint                      | not null
 section_id           | bigint                      |
 language             | character varying(10)       | default 'en'::character varying
 comments_to_ed       | text                        |
 date_submitted       | timestamp without time zone |
 last_modified        | timestamp without time zone |
 date_status_modified | timestamp without time zone |
 status               | smallint                    | not null default 1
 submission_progress  | smallint                    | not null default 1
 current_round        | smallint                    | not null default 1
 submission_file_id   | bigint                      |
 revised_file_id      | bigint                      |
 review_file_id       | bigint                      |
 editor_file_id       | bigint                      |
 copyedit_file_id     | bigint                      |
 pages                | character varying(255)      |
 fast_tracked         | smallint                    | not null default 0
 hide_author          | smallint                    | not null default 0
 comments_status      | smallint                    | not null default 0
Indexes:
    "articles_pkey" PRIMARY KEY, btree (article_id)
    "articles_journal_id" btree (journal_id)
    "articles_section_id" btree (section_id)
    "articles_user_id" btree (user_id)

ojs2t=#

As you see in the line for the "article_id" column, the name of the sequence is "articles_article_id_seq".

Regards,
Alec Smecher
Public Knowledge Project Team

[otwr]

Sorry to resurrect this thread, but I'm about to do this very thing and I was wondering if anything has changed in the last few years. An additional wrinkle is that I also need to merge user information.

[jmacgreg]

Hi otwr,

Unfortunately, not much has changed in the past few years -- for example, there is no "entire journal" export option yet available to migrate one journal, including submission history and configuration options, to another OJS platform. If you don't require your complete submission history to be migrated, you may find that it is easier to copy and paste your original journal configurations from eg. Journal Setup Steps 1-5, Journal Sections, Languages, etc.; and subsequently export and then import your articles and issues using the Articles & Issues XML plugin.

At least when it comes to user information, you can use the Users import/export plugin to export your users from one journal and import into your other journal. You will have to ensure though that there aren't any username or email conflicts -- OJS requires that all user emails and usernames be unique. You can always change the usernames/emails in the resulting XML file that is exported from the original.

You can find more information on both XML import/epxort plugins here.

Cheers,
James

[otwr]

Well, I have near-success at least with the database now, I've written a script to apply updates to all of the auto_increment fields. There are a few instances of identically-named *_id fields:

Code: Select all

WARN: duplicate AI field article_event_log.log_id (conflict with article_email_log.log_id)
WARN: duplicate AI field books_for_review_authors.author_id (conflict with authors.author_id)
WARN: duplicate AI field comments.comment_id (conflict with article_comments.comment_id)
WARN: duplicate AI field email_templates_default.email_id (conflict with email_templates.email_id)
WARN: duplicate AI field notes.note_id (conflict with article_notes.note_id)
WARN: duplicate AI field subscription_types.type_id (conflict with announcement_types.type_id)
WARN: duplicate AI field temporary_files.file_id (conflict with article_files.file_id)


So I abandoned my initial idea of adjusting each auto_increment field separately, instead it finds the largest AI value in the target database and adds that to all of the a_i and assoc_id values in the source.

For users, I had too many unresolvable conflicts (same username, different email; different usernames for same email; same person, multiple accounts) so I added a suffix to all the merged-in usernames and replaced '@' with '+e@' in all the merged-in emails (smith, smith@foo.com becomes smith_e, smith+e@foo.com). I can work on merging the duplicate users later.

The next step is to adjust the filenames. I assume the [revision_id] is not from the database, so I'm leaving that unaltered. So far, after a test of one article, it seems to have worked.

EDIT:
Changing usernames invalidates the password hash. I modified the encryptCredentials function in classes/security/Validation.inc.php:

Code: Select all

$valueToEncrypt = preg_replace('/_e$/','',$username) . $password;


which should not have an appreciable effect on security, and allows keeping the password hashes from the merged-in database.

[otwr]

Further note: the `submission_id` column in the `authors` table also needs to be incremented; it becomes the `article_id`. I missed this because there is no autoincremented submission_id elsewhere in the database.

[asmecher]

Hi otwr,

There are a few conventions like this that are in flux; we're attempting to reuse as much code as possible between the applications and since the naming conventions for publications are different (articles in OJS; papers in OCS; and monographs in OMP) we occasionally use a standardized term (submission_id) to refer to all three. It is our hope that we can make this consistent and clear e.g. by using a submissions table for all three with a standard submission_id column to identify it. Meanwhile you're unfortunately going to run into a few quirks like this.

Regards,
Alec Smecher
Public Knowledge Project Team

[otwr]

Yeah. Came across another hiccup; in the articles table there are four *_file_id fields that also need to be incremented. And in the uploaded files, it turns out that one *doesn't* increment the file names under the submissions directory. I think. Certainly OJS couldn't find the renamed ones, so I put in a hack to look for both.

I'm just documenting my adventures here so the next person to try this will have some idea what to do, but all in all this has demonstrated for me that merging OJS (2.3.6) instances is fairly painful and to be avoided. Hopefully future versions will provide for full export/import.

If you're interested, the use case is that we're a university library running OJS and we were approached by a department that had been running their own instance. They weren't happy with some aspects of hosting their own, so they approached us to see if we could bring in their journals. I did initially offer to set them up with their own OJS instance, but they were quite firm on the integration aspect.

[ramon]

Hello otwr,

Were you successful at merging the databases?
What was your environment setting?
How did you deal with user id's?
Usually, single installations will have overlapping ID's entries for user, journal, submission, configuration, and many more.
How did you handle the article naming in the submission's folder?

Did you write a shell script? From your posts it looks like you wrote a PHP script to deal with this.
Can you share what you've done?

I posted a new thread on this subject, as I didn't find this one at first (knew it existed, but my search was unsuccessful), but it should probably continue here.

_______________________________
Ramón Martins Sodoma da Fonseca
Analista em C&T
Coordenação de Tecnologias de Informação
Instituto Brasileiro de Informação em Ciência e Tecnologia - IBICT
Ministério da Ciência e Tecnologia - MCT

[otwr]

Hi,

Yes, the merge is functioning now but I wouldn't recommend this to anyone, especially if you're trying to merge thirteen(!) separate OJS instances. There are a number of complicating factors.

1) The instances have to be running the same version of OJS, and the databases should have *identical* schemas (I had problems with MySQL columns in the target and merged databases not being in the same order, the result of a historical botched upgrade).

2) It would help if the set of plugins on each instance is the same.

3) The databases: Basically, all *_id columns are autoincremented, so as you know separate databases will have overlapping keys (humble suggestion for future development: use GUIDs for indexes instead?). My approach was to identify the highest autoincrement number (regardless of what the table/column was) in the entire target database (my number was 182176 -- this is mostly because there is a table for system log messages which has a lot of rows), then wrote a script to add that number to all of the indexes and foreign keys that I could find in the to-be-merged instance, then go over all of the tables and insert their rows into the target database. Unfortunately there are some foreign keys that aren't obvious, so it took quite a while to identify all of them. There are some tables that shouldn't be merged (email templates, plugin configurations, site settings, sessions).

3a) The users table is quite problematic if you have shared users across instances, since the system does not allow duplicate usernames (of course) or duplicate email addresses (not as obvious). In my case, I had quite a few instances of users using both instances with the same email address but different usernames, and of course there were some cases of the same username having different emails. Eventually I mangled the email addresses and usernames in a reversible way so that the tables would merge, then hacked the core code to reverse the mangling when necessary. It is a bad hack.

4) Files. The files are organized like {configured_files_directory}/ journals/{journal_id}/articles/{article_id}/public/{article_id}-{another_number}-{some_other_stuff}.pdf . All of those have to be renamed with incremented article_ids and journal_ids before copying them into the target installation. And then there are the "public/journals/{journal_id}" directories, which also need to be renamed before merging.

So after all that, you may or may not have a functioning merged OJS. There will probably need to be some reconfiguration of journal settings and manual fixing of usernames/emails. And in your case, go back to Step 1 and repeat twelve times . Wouldn't wish it on my worst enemy, really.

I do have some half-baked, hacky scripts that sort-of worked; but as I was learning by doing, and since I thought I had been successful when I in reality had introduced subtle inconsistencies that I then had to fix manually, I don't recommend them except as a starting-off point to write your own. If I haven't scared you off by now, PM me and I'll try to clean them up a bit.

Edit: I forgot to mention the environment; it's Red Hat EL5 with MySQL and PHP 5.2.10.

[ramon]

Dear OTWR,

Thanks for the detailed explanations.
I'll pm you with an email address so you can send us the scripts for testing.

_______________________________
Ramón Martins Sodoma da Fonseca
Analista em C&T
Coordenação de Tecnologias de Informação
Instituto Brasileiro de Informação em Ciência e Tecnologia - IBICT
Ministério da Ciência e Tecnologia - MCT