varchar(2500) not enough for customfield_value and fieldtypes in general

[sirius]

Hello,

as we can use the editor with a custom field, the content of the customfield_value can rapidly grow.
I face this situation with a specification table for a product that contain 2943 characters and so this is too much for the customfield_value varchar(2500) in the "_virtuemart_product_customfields" table.

If you look at the _virtuemart_customs table, the custom_value is with a varchar(4095)

So it means to me that we can create some custom fields with a default value that can be up to 4095 characters, but can't use them on the products, because only 2500 is allowed to be stored on the products.

I think this would be ok tu push this to 4095 without any issue, at least the same value but not less, perhaps more for both as with VARCHAR we can set the max size to anything between 1 and 65,535.

And at the same time, for the product description (product_desc), the type is TEXT, so with a fixed value of 65535 and that's a huge number for a product description, a VARCHAR would be more appropriate right?



What the devs would suggest on this ?

opinions ?

Thanks 

[pinochico]

Bad idea about customfields since the beginning of this functionality - unfortunately.

If I need to filter products by customfield value, I need it to be fast.
To make it fast, I need the value to be indexed
In order to be indexed, VARCHAR cannot be 2500
or god forbid maybe 65,000.

Or I don't understand what you write about, it can happen because my google translte translated it wrong

[sirius]

For sure I agree 65,535 is not part of the game , that's just the end of the range accepted by a VARCHAR type.
This is equivalent to 13 x A4 pages full of text with no spaces
Test here : https://www.blindtextgenerator.com/lorem-ipsum

In order to be indexed, VARCHAR cannot be 2500

It depend of what you mean by "to be indexed" bool result of searchable value, so id indexed by the key ?
The possible types of fields are very different : String, Property, Boolean, Date, Hour, Image, Editor, Textarea, Plugin, Group, Variant, Related Categories etc... the indexation is very dependant of the type.
For the speed, customfield_value has already an index key, and the custom field itself must be set to searchable first i guess.

But the customfield_value currently are with a type of VARCHAR(2500) so it should match the varchar (at least) of the custom_value created as the default value of the custom field.
If not the value is truncated, and some is missing on the product page.

[Studio 42]

sirus, you can use multiple customfields to add text, so it's not important.
The custom field params can handel 65535 chars, so if you need big text, create a simple plugin and save the datas in a parameter.
Check the Vm core textinput customfield plugin to have a similar sample code to use.

[sirius]

Hi Studio 42,
Nop unfortunately I can't, because my custom field is on the editor type, because users need wysiwyg (jce editor) to put the specs.
And with my template my custom filed named as "Caractéristiques" appears on an tab next to the description tab on the product page and all others added as tabs.
If I do with several, my tabs would become like : Description | Caractéristiques | Caractéristiques | Other tab etc

for me it's way simpler to change from customfield_value varchar(2500) to varchar(5000)
But again I don't understand the the inconsistency between the varchar on the table _virtuemart_customs

Code Select Expand

custom_value                 varchar(4095) utf8_general_ci Oui NULL DEFAULT VALUE

and the one when the value is saved on the product on table _virtuemart_product_customfields

Code Select Expand

customfield_value      varchar(2500) utf8_general_ci Oui NULL field value

The default maximum value of the field is greater than the maximum value that can be saved on the field assigned to the product.

[pinochico]

Because in old version VM was

custom_value - 2000
customfield_value - 2500

If I think right, then custom value must be <= as customfield_value, because right idea  for customgield type Y is
custom_value is only value for basic setup customfield, but customfield_value is individual value from every products and then can be bigger then default value from customfield setup.

That is my logic and that we have on our shops.

If in the newest VM is other, then must be a little mistake or inattention, I think

[sirius]

custom_value is only value for basic setup customfield, but customfield_value is individual value from every products and then can be bigger then default value from customfield setup.

As you said
But if  customfield_value >= 2500 { can't be stored in db, so truncated text from 2500 characters} 
For me at setup time of a custom field, the field "Default value" is here to not have to retype the value each time we assign the CF to a product, and this field at this time allows up to 4095 characters.
Then if I want a default text for this field that is 4500 chars long, when I will assign this CF to a product and save it, I will only have the first 2500 chars saved.
Sounds not logic to me, If I set a default value of 4500 chars long or put it manually at the assignation time, I want it all on my product page.

That's why (yes again  ) I think that customfield_value varchar(2500) should be at least equal if not greater than 4095 of the custom_value.

[Studio 42]

NO the value should be 2500 chars for product custom field value and custom value for performance reason.

[sirius]

So ok
anyway both should be the same.

Thank you for your answers 

[Milbo]

"That's why (yes again  ) I think that customfield_value varchar(2500) should be at least equal if not greater than 4095 of the custom_value."
4095 chars of ASCII, not UTF8.

[sirius]

Arggg Milbo,
you nailed me


(anyway still not enough for my own use)

[Studio 42]

(anyway still not enough for my own use)

Use a plugin and save the text as plugin params. You can then use near 65536 chars
Is i said, params are never used for search, but values yes. So it's stupid to change varchar(2500). It should be 255 chars only!

[Milbo]

yepp, right. It was already a mistake to use 2500. I would use now 255, too. so the index would work really fast.

[pinochico]

heureka after 7 years

Thanks

[sirius]

Hi 

today's menu

#__virtuemart_order_histories.comments
Fieldtype : VARCHAR(15359)  Such a huge comment on an order ! The number seems so arbitrary it looks like a typo. Recommended would be TEXT or a smaller varchar, the biggest for me on past ten years is 595 chars.

#__virtuemart_order_histories.o_hash
Fieldtype :  VARCHAR(33)  this hash is always 32 long, so why not set it with CHAR(32) ? (and same for all o_hash occurrences)

#__virtuemart_order_items.oi_hash
Fieldtype :  VARCHAR(33)  this hash is always 32 long, so why not set it with CHAR(32) ? (and same for all oi_hash occurrences)

#__virtuemart_products.product_gtin
Fieldtype :  VARCHAR(64)  OK for this one it's a bit more contentious, but given that the GTIN is officially a maximum of 14 characters in length (ITF-14 for multipacks) I think a VARCHAR(14) would be more appropriate.