Increase metadata character limits

ydedy · January 24, 2020, 10:49am

Hi,

I am heavily relying on the metadata from tasks to gather information through the API for external applications I am developing at my workplace.

The issue I have is that the field is limited to 255 characters which is too short for me.

Is there a workaround? I was thinking about artificially changing the field type/limit on the database directly but I suppose that must be rather dangerous to do it this way.

Thanks for the help !

FxFx · December 13, 2022, 10:25am

I have the same / or nearly the same question. So i add it here:

How can one use userMetaDataModel or extra database Field with more than 255 Characters? 65536 e. g.?

Any hint is appreciated.

Thanks, Fx

FxFx · December 24, 2022, 4:17pm

I’ve investigated that further. The problem ist that the field value in the table user_has_metadata is defined to 255 characters (default).

How would one fix this from within Kanboard without knowledge which database is in use?

Copy table to a temp table (create table … as select …)
drop table
recreate table with larger size for value (datatype TEXT) field
Insert from temp table into the new one
drop temp table

I can do that with sql statements but this would lead to database specific scripts…

I’am aiming to fix this issue, because i want user specific css (bug here is the smal value field in the core):

github.com/PapeCoding/kanboard-CustomUserCSS

Adding few styles into window

opened 03:20PM - 28 Apr 21 UTC

korlikiangel

I was trying to add few styles using your plugin but got this error after i am t…rying to save this: ``` #tooltip-container {width: 35%;} .task-form-bottom { margin-top: -3%; width: 300px;} .form-actions { padding: 0;} .task-board { margin-bottom: 4px; padding: 8px; border-radius: 3px; border: none; border-radius: 3px; border-bottom: none;} ``` Internal Error: SQL Error: SQLSTATE[22001]: String data, right truncated: 7 ERROR: value too long for type character varying(255)

Any other ideas to have custom css on a per user / profile basis from within Kanboard?

Cheers, Fx

creecros · December 24, 2022, 5:16pm

i dont know, customizer has user specific themes/css, so im always meh on this topic.

FxFx · December 25, 2022, 9:37am

CustomUserCSS ist not Customizer PlugIn. It has no theme or special theme needs.

That PlugIn adds a Metafield to the user. The user can add his own CSS in that field. But that field is limited to 255 chars which is far away from enough to do things with css.

Question is how can one add a field with lets say 65536 char lengt?

creecros · December 25, 2022, 1:56pm

just because something is not one thing, does not mean its unrelated. you want more than 255 chars. with customizer, you can simply upload a css document, with no char limit. seems like one solution to a problem.

FxFx · December 26, 2022, 12:21pm

You’re right.

It’s a work around, but to complicated for a normal user.

I would have css in that field (screenshot) not an include to a file, where the user may modify some colors. Guided by comments in the css in his own profile with normal user rights.

creecros · December 27, 2022, 2:45pm

well, your other option would be to make a plugin, include a schema that alters the value columns. id be wary of installing said plugin, as this could eventually have consequences down the road with migrations.

if you include a schema for all DB types, then the plugin would automatically use the appropriate schema, so you dont need knowledge of the DB type.

https://docs.kanboard.org/v1/plugins/schema_migrations/

FxFx · December 28, 2022, 10:24am

I think the best approach would be to expand metadatfields of type “Text” to more than 255 character (65535 e. g.)

But this will lead to changes to the core.

creecros · December 28, 2022, 6:30pm

SQLITE the datatype is ‘TEXT’
After testing, i can put way more than 255 chars into field no issue.
as far as i can tell, nothing in Kanboards Core is limiting this to 255 chars, for SQLITE.

FYI, 858330 was the max amount Chars I could have it save:
test4

It’s MySql and postgress where 255 chars is clearly defined as the limit.

for a breif time, the settings table value was also defined as varchar(255), but was changed to type ‘Text’, most likely not long after the CustomCss option was added:

github.com

kanboard/kanboard/blob/7c0a239ec399124207b86fcd6b22ade38c5c7574/app/Schema/Mysql.php#L247


      
          {
              $pdo->exec('ALTER TABLE `comments` ADD COLUMN `date_modification` BIGINT(20)');
              $pdo->exec('UPDATE `comments` SET `date_modification` = `date_creation` WHERE `date_modification` IS NULL');
          }
          
          
function version_118(PDO $pdo)
          {
              $pdo->exec('ALTER TABLE `users` ADD COLUMN `api_access_token` VARCHAR(255) DEFAULT NULL');
          }
          
          
function version_117(PDO $pdo)
          {
              $pdo->exec("ALTER TABLE `settings` MODIFY `value` TEXT");
          }
          
          
function version_116(PDO $pdo)
          {
              $pdo->exec("ALTER TABLE tasks ADD COLUMN external_provider VARCHAR(255)");
              $pdo->exec("ALTER TABLE tasks ADD COLUMN external_uri VARCHAR(255)");
          }

so, yes, you could do the same in the core for the user_has_metadata value, or you could include a schema with this plugin that makes the modification. There are other options, but it seems like we are hell bent on modifying databases, so there you go.

As for SQLITE, if you have a limit of 255, i’d say look at your setup and what you have going on, maybe you set a limit.

edit:
I’ll include a couple other options that do not involve modifying the databse, because, well…why not:

1.) input css into the field, instead of saving directly to the DB, output a file with the css, and only save the filename into the user_has_metadata field.

2.) Don’t use that table, use the settings table, it’s already setup for you

creecros · December 28, 2022, 7:36pm

Since this question is unrelated to the CustomUserCSS plugin, i would say you get a different answer, but its a vague question so, really you have 3 answers.

1.) use the settings table instead, not sure if that works for you though.

2.) use SQLITE, not sure if that works for you though

3.) Modify the tables you need. Is it dangerous? I don’t know, maybe. I think the real issue will come the day you upgrade kanboard after having modified the table, the migrations could fail. Possibly not tho.

FxFx · December 28, 2022, 8:08pm

Hi creecros,

thanks for your heavy investigation on that. Great.

And yes you are right: the database is a mySQL one. And i think this is an issue. We can define that it is not, but i think if we want to have an abstraction of the underlying db we should make sure that metadata should serve more than 255 chars for a text field if one is not able to define more without cirumevent the abstraction of the db.

And that is what the Author of CustomCSS did. He simply used the metadata text field, which ist the right thing to choose, but it does not help an he can not have more than that amount of chars per meta field.

Anyway. I think this should be changed in the core. Is it a bug? Depends. Maybe not. I accept all views on this. Personally i think: 255 chars for an abstracted database metafield of a type text is not sufficent. Even more when there is no “large text field” option in the abstracxtion layer.

my2cents.

No complains! I’am fine. And thanks to you again. I’am impressed by our detailed, good and also absolut correct response and view on this one!

Cheers, Fx

creecros · December 28, 2022, 8:18pm

I definately would not consider it a bug. Metadata is not intended to be data, necessarily, but is 255 chars enough? Maybe, maybe not, definately an opinionated question that will warrant different responses. As for the plugin “CustomUserCSS”, the author is using a table intended for “Metadata” to store “Data”. The settings table is where I would save that, personally, as the table is most definately already setup to store “Data”.

Just to be clear though, i’m not advocating for or against altering the database in general, whether in the core or in a plugin. The OP wanted a workaround, via not altering the database, which is why I was offering outside the box solutions other than simply altering the database. I 100% agree, the easiest solution of all, is to simply alter the database, in the core.

FxFx · December 30, 2022, 12:59pm

Hi creecros,

i read we are aligned here

Regarding the settings table: is it possible to store data per user in that table? If so: do you have a hint to archvie that?

Thanks, Fx

creecros · December 30, 2022, 1:46pm

yes.

use the user id in the key. i.e. [userid_customcss => value]

i can go into more detail if needed.

FxFx · December 30, 2022, 4:32pm

I check that out as soon as possible und give feedback.

Thanks!

Cheers, Fx

creecros · December 30, 2022, 7:02pm

you could actually use this as a decent guide. i did the same here, but used a unique color id instead.

github.com

aljawaid/ColorManager/blob/9da75bb98f74345e276d1b21ce4bdc61e3942ff4/Controller/ColorManagerController.php#L90


      
                          $custom_colors = $this->configModel->get('kbcolour_ids','');

                          if ($custom_colors == '') {

                              $custom_colors_array = array();

                          } else {

                              $custom_colors_array = explode(',', $custom_colors);

                          }

                          array_push($custom_colors_array, $color_id);

                          $custom_colors_array = array_unique($custom_colors_array);

                          if (is_array($custom_colors_array)) { $custom_colors_string = implode(',', $custom_colors_array); } else { $custom_colors_string = $color_id; }

                          $this->configModel->save(['kbcolour_ids' => $custom_colors_string]);

                          $this->configModel->save(['kbcolour_name_'.$color_id => $values['color_name']]);

                          $this->configModel->save(['kbcolour_backgroundcolor_'.$color_id => $values['background_color']]);

                          $this->configModel->save(['kbcolour_bordercolor_'.$color_id => $values['border_color']]);

                      }

                  }

                  

                  $this->response->redirect($this->helper->url->to('ColorManagerController', 'show', ['plugin' => 'ColorManager', 'url' => t('color-manager')]));

                  

              }

          }

metamagik does the same, but that code can be hard to follow.

FxFx · December 31, 2022, 10:55am

At the moment i’am trying to finalize a little theme i’ve made and sync my needs with some plugins (ColorManager, DueDate e.g.) but i’am still not happy enough to release it to my colleagues.

And documentation…

When i’am done with that, i will have a closer look.

Could be a nice little project to dive deeper into php. I’am not used to that programming language until now.’

Happy new year to all of you!

Cheers, Fx

Topic		Replies	Views
Some of the biggest performance issues are in the board view, not the SQLite database backend	0	1010	October 12, 2019
Help - Possible Permission Issue - Adding Task / Removing or Modifying Column --- SOLVED Installation Issues	5	939	September 24, 2020
Big use of Kanboard Questions	3	668	November 30, 2021
Unable to edit task even if it said successful? Questions	23	419	April 19, 2023
Error Log file too large	5	210	April 17, 2022

Increase metadata character limits

Related Topics