Modify customfield_value from varchar(2500) to varchar(10000)

[restodo]

Hello,

I'm asking to the developers If there is a chance to change the following 4 files in the upgrade package to increase the customfield_value varchar from 2500 to 10000 because 2500 characters isn't enough, at least in my case I'm using a Tab for Technical Specs and some products has a long specs.


/install.sql

-- --------------------------------------------------------
--
-- Table structure `#__virtuemart_product_customfields`
--

CREATE TABLE IF NOT EXISTS `#__virtuemart_product_customfields` (
  `virtuemart_customfield_id` INT(1) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'field id',
  `virtuemart_product_id` int(1) NOT NULL DEFAULT '0',
  `virtuemart_custom_id` int(1) NOT NULL DEFAULT '1' COMMENT 'custom group id',
  `customfield_value` varchar(10000) COMMENT 'field value',
  `customfield_price` decimal(15,6) COMMENT 'price',
 

/script.virtuemart.php

         $this->alterTable('#__virtuemart_product_customfields',array(
            'custom_value' => ' `customfield_value` varchar(10000) COMMENT \'field value\'',
            'custom_price' => ' `customfield_price` DECIMAL(15,6) COMMENT \'price\'',
            'custom_param' => ' `customfield_params` text COMMENT \'Param for Plugins\''
         ));


/administrator/components/com_virtuemart/install/install.sql

-- --------------------------------------------------------
--
-- Table structure `#__virtuemart_product_customfields`
--

CREATE TABLE IF NOT EXISTS `#__virtuemart_product_customfields` (
  `virtuemart_customfield_id` INT(1) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'field id',
  `virtuemart_product_id` int(1) NOT NULL DEFAULT '0',
  `virtuemart_custom_id` int(1) NOT NULL DEFAULT '1' COMMENT 'custom group id',
  `customfield_value` varchar(10000) COMMENT 'field value',
 
 
/administrator/components/com_virtuemart/install/script.virtuemart.php

         $this->alterTable('#__virtuemart_product_customfields',array(
            'custom_value' => ' `customfield_value` varchar(10000) COMMENT \'field value\'',
            'custom_price' => ' `customfield_price` DECIMAL(15,6) COMMENT \'price\'',
            'custom_param' => ' `customfield_params` text COMMENT \'Param for Plugins\''
         ));
         

[Studio 42]

This is not a good idea.
customfield_value can be searchable and 2500 is already TOO BIG !
You can add same field again if you have long description in customfield or use an editor  plugin for eg., this use params and can be 65555 chars because params are not searchable and can be bigger without slowdown any shops( and doing params field bigger have not a real impact on speed.
If i had to vote, i hade limit it to 500 chars max.

[restodo]

This is not a good idea.
customfield_value can be searchable and 2500 is already TOO BIG !
You can add same field again if you have long description in customfield or use an editor  plugin for eg., this use params and can be 65555 chars because params are not searchable and can be bigger without slowdown any shops( and doing params field bigger have not a real impact on speed.
If i had to vote, i hade limit it to 500 chars max.

Thanks for your answer.
I don't follow.. Technical Specs is a Tab near Description and the only way I've found to create it was using a Custom fields.
If I create a new field, assuming you mean in my Db, how can I put in a Tab ?


Greets!!

[Studio 42]

Use same customfield and add new ones in your product(you can add 100 if you need), this get groupped in the same tab(in your case) one after one.
If the template do it right, you only see the main customfield title only 1 time in front.
Eg. If you have a customfield named EDITOR and new EDITOR customfield
You see in front
EDITOR (content 1)
EDITOR (content 2)
EDITOR (content 3)
...

[jankoo]

this is soooo F*CKING stupid idea.. this update totally destroyed our eshop.. it just cut all the values in table for thousands of products...  you stupid idiots !!!!!

did you ever using the VM for REAL eshop? now i cant have tech.specs in one custom field but i have to make 20? what a great idea !!! whats the difference than in searching if i will have the same content divided into 20 customfields?

every stupid simple eshop cms let me use for description and custom fields as much text as i want but [abusive comment removed] dont because of searching that i even never using for customfields..

[Studio 42]

this is soooo F*CKING stupid idea.. this update totally destroyed our eshop.. it just cut all the values in table for thousands of products...  you stupid idiots !!!!!

did you ever using the VM for REAL eshop? now i cant have tech.specs in one custom field but i have to make 20? what a great idea !!! whats the difference than in searching if i will have the same content divided into 20 customfields?

every stupid simple eshop cms let me use for description and custom fields as much text as i want but [abusive comment removed] dont  because of searching that i even never using for customfields..

You hacked the field and you complain ?
You have to know that 95% shops use only customfields as text, and most shop do not search for full text, but only for some customfields as filter. So if you have 20 field that not suite the type(eg a color) this get not included in the query.
If you don't understand the logic of mysql search, you never understand why.
So speack with a SQL spemildt and perhaps you  are the "stupid idiots" ?
Simply think about this : why mysql field are limited in size and have index ?
If you know the answer, you stop to complain

[Studio 42]

A sample search for 2 letters using like %sc%,
in customfield value > 0.008seconds
in customfields params > 0.055seconds
Perhaps you understand better ?

[jankoo]

sure i understand.. so its a great concept but unusable because we cant have described the product because there is NO place where we can put all the infos we need.. what a epic idea.. have fastest eshop ever but without products..

[jankoo]

and i was asking about difference in search seed between:
1. 10.000 letters text in one custom field value or
2. 4 custom fields with 2500 letters texts

please explain me.. this i really dont understand

[jankoo]

and.. i totally dont understand the structure of the product_customfields table.

why the "editor" or "text" content go to the "customfield_value" (varchar2500) and "customfield_params" (text) is EMPTY



what is this text table for? in some products i found in "customfield_params" parameters like "wPrice=0|wImage=1|wDescr=0|width="0"|height="0"|"
that seems like a content for varchar



sry.. but i dont get this one too..

[Studio 42]

customfield_params is used to add plugins params.
Eg if you have 1000 childs in one of my plugin, you need from 10000 to 20000 chars., but this is not a big problem, this are parameters
But customfield_params is not searchable(or should never be) so it's not important, it can be Mysql long text field, this not change something, because mysql do not save it in the table directly, but only a reference to the text and most time the text is never put into the memory.
If you use Mysql text field, then mysql have to search for the pointer and look in this, and only after, return the value, it's why text should not be used for search if possible.

and i was asking about difference in search seed between:
1. 10.000 letters text in one custom field value or
2. 4 custom fields with 2500 letters texts

No one search for this text(or i never had a user had this request), but for the other custom_value and this is important.
The query check for the customfield_id eg.for customfield color,  can be 10. and after search for the value : RED, but more this field is big and more the sql engine need time to find it.
So if you have a shop with 1000 customfields, you dont really see the diference, but if you have 100000 customfields then yes the result can be 10X slower or more using TEXT and 2 or 3 time slower in case of big varchar.
If varchar was only 64 chars,  then this can be faster too, so currenty, it's a compromise of the two(size + speed)

[jankoo]

sry.. but i dont see this as compromise AT ALL!!. compromise is the solution what everyone can use. in this solution there is NOTHING for me. (and you should change your "95% of users" because i know there are MANY peoples who had this issue over YEARS.. but as nobody from VM came with solution they are probably on different CMS already)..

1. im asking about the difference between searching the same content in one big or few smaller fields is because you recommend it (and because you all write that cut to 2500 was for fast search)

Use same customfield and add new ones in your product(you can add 100 if you need), this get groupped in the same tab(in your case) one after one.
If the template do it right, you only see the main customfield title only 1 time in front.
Eg. If you have a customfield named EDITOR and new EDITOR customfield
You see in front
EDITOR (content 1)
EDITOR (content 2)
EDITOR (content 3)

im sry.. but this seems as very stupid idea to me.. but. can you let me know the speed difference in this 2 searches? im just curious...

2. i dont need to search in this fields at all.. thats what im talking about. and tell me. for what is the option "searchable" in the customfield settings? if i set it off you dont need to care about seed of my searching.. or no? this will be compromise.. if i can choose if i want to search in the field or no. compromise is not to cut it to 2500.. WE USE IT FOR text, tables, etc.. real content.. not "red" and "blue".. that are params that dont need the space.. i understand that.. BUT WHERE we have to store these infos? what is than "editor" and "text" customfields for anyway? i just dont understand whats sooo hard to understand that we NEED to have description and infos in products that we need. whats soo hard to understand on that? no one EVER here give me the solution, just talkings about how great and seed is it.


3. can you give me a solution where to store HUGE infos if i need it? even if i want to have there bible

[Studio 42]

One solution is my multi purpose plugin https://shop.st42.fr/en/products/shortcodes.htm
Sample http://pro.st42.fr/fr/shortcodes/tout-les-shortcodes.html (scroll down to HTML DESCRIPTION )
https://shop.st42.fr/en/shorcodes-english-doc#editor
But if you really need to write complex things, its not for you. I used a simple editor, because Most joomla editors cannot be initalised properly with ajax(and use many resource).

Another solution, is to derivate the core text input plugin or any existing plugin, so the value is saved as parameter(and can use 65555 Chars or 64 Kb).
This 2 solutions works if you dont need to searchable and is really the best in your case.

Last solution, is to check with the core team, if they have lust to modify the editor customfield type, so you can choose to save your text as custom params(and why not using MEDIUMTEXT  for the params so you can save 16 Mb per customfield in the params).

[Milbo]

Really, Patrick described it quite well. You just use the wrong Customfield for your purpose. Imho, when you use the customfield textfield, then the text is stored as param. So you can add the bible to any product.

Furthermore. The bigger configuration is usually done at the product and not in the customfield prototype. And yes Patrick, we should limit it even more, but.... there are old plugins adding there values there. That was actually the reason that we kept that size.