Need advice with app\Schema\Mysql.php

1

I am trying to figure out, how I could add 2 new default-values to the settings-table.

So I searched in app\Schema\Mysql.php and found this:

function version_81(PDO $pdo)
{
    $pdo->exec("INSERT INTO settings VALUES ('subtask_time_tracking', '1')");
    $pdo->exec("INSERT INTO settings VALUES ('cfd_include_closed_tasks', '1')");
}

So I thought … wow this looks easy and added this to the top of said file:

function version_137(PDO $pdo)
{
    $pdo->exec("INSERT INTO settings VALUES ('show_group_memberships_in_userlist', '1')");
    $pdo->exec("INSERT INTO settings VALUES ('show_group_memberships_in_userlist_limit', '0')");
}

Of course I also changed line 11 as follows:

const VERSION = 137;

But when I call my local testboard, I get this error :neutral_face:

Internal Error: Unable to run SQL migrations: Running migration \Schema\version_137, SQLSTATE[21S01]: Insert value list does not match column list: 1136 Column count doesn't match value count at row 1 (You may have to fix it manually)

Obviously I need to do more, to run this migration automatically?!

Looking forward to your answers,
Manfred

2

You don’t need to do any of that for settings.

Use the config model.

https://docs.kanboard.org/en/latest/plugins/metadata.html

Example plugins:
Customizer - uses the config model a lot, to store all the custom settings globally and per user.

Task2pdf - simple usage, stores a couple settings.

MetaMagik - the whole plugin basically uses the metadata model.

GroupAssign - included a setting to allow managers to edit groups, uses the config model to store the setting

MarkdownPlus - includes a setting

If you are hell bent on using the schema, you are doing it all wrong, you add your own schema, and the version would start at 1.

See:
https://docs.kanboard.org/en/latest/plugins/schema_migrations.html

You can look at metamagik or groupassign for example in practice.

3

Hey creecros,
thanks for all your examples :slight_smile: … but my question is not related to developing a plugin, but rather about adding some functionality to the Kanboard-core
( to be precise, this PR https://github.com/kanboard/kanboard/pull/4449 )

This would involve 2 new config-parameters, that should be present in the settings-TABLE in order to set the desired default-values, that can then be changed in a new settings-panel.

That’s why I am trying to solve it with a migration in app\Schema\mysql.php

And from looking at the file, it seems that this has been used in the past for newly introduced config-parameters (like subtask_time_tracking and cfd_include_closed_task)

Here are 2 screenshots:
This is what the new panel looks like without the default-values (which I would like to avoid)

SettingsNoDefault
SettingsNoDefault884×327 17.7 KB

This is how I would like it to look like:

SettingsWithDefault
SettingsWithDefault822×334 17.8 KB

So what’s wrong with my migration-attempt, because it looks exactly like the one from version81.

Looking forward to your answer :slight_smile:

4

It doesn’t change my answer. I would use the config model, still.

1 Like
5

If you are trying to point me at understanding the following concept with the configModel:

$my_foo_config_parameter = $this->configModel->get('my_foo_config_parameter');

Will either return the actual value of my_foo_config_parameter from the settings-table (if present) or else it will be empty … while this:

$my_foo_config_parameter = $this->configModel->get('my_foo_config_parameter', 'bar');

… will either return the actual value of my_foo_config_parameter from the settings-table (if present) or else return my desired default-value of ‘bar’ …

I had already figured that out before, but this doesn’t help me with my current challenge :neutral_face:

From all my tests so far, it looks to me, as if config-parameters need to be present in the settings-table in order for the form to display them. If a given config-parameter is not present in the settings-table this will happen:

  • A checkbox representing that parameter will remain unchecked until is has been checked manually and the form has been saved
  • A number-field representing that parameter will remain empty until some value has been entered manually and the form has been saved
    • You can check that by deleting the value board_public_refresh_interval from the settings-table and then call the “board-settings”-panel, which will render the field “Refresh interval for public board” empty.
    • Said parameter has been added to the settings-table with a default-value of 60 with db-migration version_29, which seems to have happened a long time ago
  • A text-field representing that parameter will remain empty until some value has been entered manually and the form has been saved

So in order to “enforce” default-values for new parameters in the first place, I must make sure that these parameters are already stored in the settings-table.

And as I said before, this seems to be the “official” way to deal with this, from looking at app\Schema\Mysql.php, because new default-parameter-values have been added here before.

So to cut a long story short … what’s wrong with my attempt in adding these values to the settings-table via this code as described above:

function version_137(PDO $pdo)
{
    $pdo->exec("INSERT INTO settings VALUES ('show_group_memberships_in_userlist', '1')");
    $pdo->exec("INSERT INTO settings VALUES ('show_group_memberships_in_userlist_limit', '0')");
}
6

The default value is defined when you attempt to retrieve it.

$this->configModel->get(‘mysetting’, ‘defaultvalue’);
i.e.

If $this->configModel->get(‘mysetting’, ‘unset’) = ‘unset’ then $this->configModel->save(‘mysetting’, 0)

I’m on a cell phone so obviously my syntax is only to give you a rough example, but hopefully that makes sense.

All you need do is ask, and then save to enforce a value.

Also, there is nothing wrong with however you choose to do it. I was only trying to point out the simpler way to do it, or at least simpler in my mind.

7

Ok, now that I have my computer, I will answer your original question.

After version_81, take a look at version_108. 2 columns where added to that table.

You are missing those 2 columns.

i.e:

function version_137(PDO $pdo)
{
    $pdo->exec("INSERT INTO settings VALUES ('show_group_memberships_in_userlist', '1', '0', '0')");
    $pdo->exec("INSERT INTO settings VALUES ('show_group_memberships_in_userlist_limit', '0', '0', '0')");
}

<<<Tested and worked just fine.

8

Wow … it was indeed easy, but I was so “intimidated” with all the new things to learn in Kanboard, that I didn’t realize this was plain PDO and not a Kanboard class that would take care.

I should just have taken a minute to read and try to understand the error-message, but I was kind of blind :crazy_face:

Thanks for your help and patience with me.
I was just eager to solve it this way here, because it’s intended to be a PullRequest against Kanboard and not a plugin … and apparently this is the way that Fred deals with new settings-parameters, so I didn’t want to break that rule here.