Change table publication_settings > setting_value datatype from TEXT to MEDIUMTEXT

For developing the plugin optimetaCitations (GitHub - TIBHannover/optimetaCitations: OPTIMETA OJS Plugin - Citations Plugin), I need to change the datatype of setting_value (publication_settings) from TEXT to MEDIUMTEXT.

I use the publication_settings to save my parsed citations. As I noticed in my tests, the datatype TEXT is to short for large citation lists.

This datatype should also be checked/changed in the installing the plugin, updates and upgrades of OJS and the plugin.

Could someone (@NateWr / @Dulip_Withanage) help me with this? Thanks

What application are you using?
OJS 3.3.0 and 3.2.1

Hi @gaziyucel,

I see two solutions:

Solution 1: Change the column type in OJS

Since we’re already using TEXT, I don’t think there are any real performance impacts going to MEDIUMTEXT instead. (There are one or two places where we’re looking up e.g. publication_id by setting_value, which will be slow, but no slower with MEDIUMTEXT than TEXT.)

However, we don’t have a requirement for this change other than your request, so it would be up to you to make the change. Here’s how you’d do it:

  • For 3.2.1-x and 3.3.0-x: We’re not making database changes in either of these branches, so you’d need to make and maintain the database change manually. Off the top of my head, there are no migrations that run in that range that would downgrade the column length back to TEXT (potentially chopping off longer content!) but don’t quote me on that. (Test carefully when upgrading.)
  • For 3.4.0-x and newer, if you’d like to propose this change, please open a pull request:
    • Make the change consistently for all ..._settings tables, not just publication_settings.
    • Adjust the column type in the migration scripts that create these tables during installation (lib/pkp/classes/migration/install/*.php and classes/migration/install/*.php).
    • Create a migration script that applies these same changes during upgrade.

(I know this probably sounds like a lot of work, but it’s not that bad, and I can provide some guidance.)

Solution 2: Use a new table managed by your plugin

There are a few examples of plugins that do this, e.g.:

You’d be able to create a schema however you like and it wouldn’t be tied to the OJS release process.

Thanks,
Alec Smecher
Public Knowledge Project Team

Hi @asmecher,

thank you for your reply.

I thought about solution 2 and discussed this with Dulip also, but this would not be my preferred way. I prefer sticking with the OJS schema and use a custom schema if this is absolutely necessary.

  • For 3.2.1-x and 3.3.0-x: is there an example of a plugin you know of which makes these changes? This would make my life easier searching.

  • For version 3.4.0-x, I’ll do so with an pull request. Where can I find this version, is this the main branch of GitHub - pkp/ojs: Open Journal Systems? I will plan to do this at the end of August or in September.

A quick search gave me the following tables which needs to me changed:

announcement_settings
announcement_type_settings
author_settings
category_settings
citation_settings
controlled_vocab_entry_settings
data_object_tombstone_settings
email_templates_settings
event_log_settings
filter_settings
genre_settings
issue_galley_settings
issue_settings
journal_settings
library_file_settings
metadata_description_settings
navigation_menu_item_assignment_settings
navigation_menu_item_settings
notification_settings
notification_subscription_settings
plugin_settings
publication_galley_settings
publication_settings
review_form_element_settings
review_form_settings
section_settings
site_settings
static_page_settings
submission_file_settings
submission_settings
subscription_type_settings
user_group_settings
user_settings

Hi @asmecher,

I think I found the solution for 3.2.1 and 3.3.0

Added following method to
“\plugins\generic\optimetaCitations\OptimetaCitationsPlugin.inc.php”:

public function getInstallMigration()
{
  import('plugins.generic.optimetaCitations.classes.Install.PluginMigration');
  return new Optimeta\Citations\Install\PluginMigration();
}

Added a class as follows to
“\plugins\generic\optimetaCitations\classes\Install\PluginMigration.inc.php”:

namespace Optimeta\Citations\Install;

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Capsule\Manager as Capsule;

class PluginMigration extends Migration
{
    /**
     * @desc Run the migrations
     * @return void
     */
    public function up()
    {
        // publication metadata
        Capsule::schema()->create('publication_settings', function (Blueprint $table) {
            $table->bigInteger('publication_id');
            $table->string('locale', 14)->default('');
            $table->string('setting_name', 255);
            $table->mediumtext('setting_value')->nullable();
            $table->index(['publication_id'], 'publication_settings_publication_id');
            $table->unique(['publication_id', 'locale', 'setting_name'], 'publication_settings_pkey');
        });
    }
}

Hi @gaziyucel,

I don’t recommend making changes to the 3.2.x or 3.3.x core database tables as code to be shipped in a plugin; the database structure for those releases is “frozen” and any changes to those should be undertaken as a DBMS admin task (ALTER TABLE ... in SQL) rather than a part of a plugin.

For 3.4.x, yes, that’s the main branch – it’ll be released late this year or early next. This is where you could propose a pull request that changes setting_value columns to MEDIUMTEXT. At the moment the relevant setting_value columns are defined in these migration classes:

  • lib/pkp/classes/migration/install/ControlledVocabMigration.inc.php
  • lib/pkp/classes/migration/install/ReviewFormsMigration.inc.php
  • lib/pkp/classes/migration/install/LibraryFilesMigration.inc.php
  • lib/pkp/classes/migration/install/LogMigration.inc.php
  • lib/pkp/classes/migration/install/MetadataMigration.inc.php
  • lib/pkp/classes/migration/install/SubmissionFilesMigration.inc.php
  • lib/pkp/classes/migration/install/TombstoneMigration.inc.php
  • lib/pkp/classes/migration/install/CommonMigration.inc.php
  • lib/pkp/classes/migration/install/SubmissionsMigration.inc.php
  • lib/pkp/classes/migration/install/AnnouncementsMigration.inc.php
  • lib/pkp/classes/migration/install/GenresMigration.inc.php
  • lib/pkp/classes/migration/install/CategoriesMigration.inc.php
  • lib/pkp/classes/migration/install/NavigationMenusMigration.inc.php
  • lib/pkp/classes/migration/install/RolesAndUserGroupsMigration.inc.php
  • lib/pkp/classes/migration/install/DoiMigration.inc.php
  • lib/pkp/classes/migration/upgrade/v3_4_0/PKPI7014_DoiMigration.inc.php
  • classes/migration/install/OJSMigration.inc.php

Hope that helps!

Regards,
Alec Smecher
Public Knowledge Project Team

Hi all,

Thanks to @gaziyucel’s contributions (see Extend all setting_value columns in settings table to mediumText · Issue #8157 · pkp/pkp-lib · GitHub), all setting_value columns in 3.4.0 will be MEDIUMTEXT (and not subject to the 64k character limit they currently encounter)!

If you are using 3.3.x and want to manually extend a particular column to MEDIUMTEXT by altering it directly in the database, you will not have trouble later upgrading to 3.4.0.

Regards,
Alec Smecher
Public Knowledge Project Team