Propose increase size of stockmaster StockID and Description

[dalers]

I'm still working through importing stock items but an issue has arisen with the stock ID (part number), description (title) and long description (detail) being imported are larger than the webERP column sizes.

The maximum column widths in the data being imported are:

• StockID: 36 characters vs 20 in webERP (50 char absolute max in Parts&Vendors)
• Description: 100 characters vs 50 in webERP (255 char absolute max in Parts&Vendors)

LongDescription is "text" which iiuc is 65K bytes, which is PNDetail in Parts&Vendors and has an max of 255 char so no issue. Fwiw, the absolute max for PNPartNumber (StockID) is 50 char and PNTitle (Description) is 255 char.

Q1. Are there any technical reasons why these column widths can't simply be increased to accomodate the data being imported? Would the only issues be table and report formating when scripts assume data will be shorter?

Q2. If there are no insurmountable technical reasons, would you be in favor of (or tolerant of) allowing the larger sizes in webERP?

Cheers,
Dale

Fwiw, posts that are somewhat related and in a general PLM theme include:

• Z_ImportPartCodes.php fails Z_ImportPartCodes.php fails #741

• Schema is missing an Item "Notes" field Schema missing stock item "Notes" #592

• How to Import BOMs How to Import BOMs #591

• What is purpose of Manufacturers and Brand Management? What is purpose of Manufacturers and Brand Management? #668

• BOM item quantity needs more decimal places for accuracy BOM item quantity needs more decimal places for accuracy #702

• Sales Orden Items with CSV with obsolete items error
  (specifically Sales Orden Items with CSV with obsolete items error #808 (comment))

[dalers]

Fyi, I just discovered a stock ID can't include a "space" character, That calls for the classic "space to underscore" global replacement!

[pakricard]

Q1. Are there any technical reasons why these column widths can't simply be increased to accomodate the data being imported? Would the only issues be table and report formating when scripts assume data will be shorter?

I can't foresee any technical issue, besides that StockID appears in many tables and a zillion queries and reports. All of them should be checked. It is not a complex change, but a long and tedious one.

Q2. If there are no insurmountable technical reasons, would you be in favor of (or tolerant of) allowing the larger sizes in webERP?

OK for me. As a reference, OpenCart uses model (our StockID) as varchar(64) and product name (our description) as varchar(255).
Our long description should be kept as text, to allow long, detailed, technical descriptions.

R

[dalers]

@pakricard

Our long description should be kept as text, to allow long, detailed, technical descriptions.

Interesting. Iiuc LongDescription is 65K char (8-bit, less if multi-byte) and you are using it more for ad hoc notes than a "long description" per se (e.g. the "Long Description" that might be required on a shipping document). My initial expectation was that LongDescription might be at most 5x the Description, or max 100 char. However, it seems at least for you, LongDescription can be MUCH longer.

Does anyone know off the top of their head if there are scripts that assume LongDescription is much shorter, or assume a specific purpose?

If everyone is treating LongDescription as 65K of ad hoc text, perhaps the legend in the GUI should be "Notes" instead of "Long Description" to better imply the intended use. What do you think?

[pakricard]

Hi:
We are using this field to store a longer description that the description, and some technical details. Mainly what appears on any website as product description.

[dalers]

Back to stockid, using ChangeColumnSize() to change stockid results in a foreign key error at least on MariaDb.

I see other functions in UpgradeDB_mariadb.php disable foreign key checking, should CangeColumnSize() do this also or is there a better way?

[dalers]

I wrapped the query to change the column size in ChangeColumnSize() with DB_[Ignore|Reinstate]ForeignKeys() but still get error:

Warning: mysqli_query(): (HY000/1833): Cannot change column 'stockid': used in a foreign key constraint 'weberp_kayden/ custitem _ibfk_1' of table 'weberp_kayden/custitem' in C:\xampp\htdocs\weberp\includes\ConnectDB_mariadb.php on line 63

Any suggestions? Fwiw I'm using MariaDB 10.4.

function ChangeColumnSize($Column, $Table, $Type, $Null, $Default, $Size) {
	$SQL = "SELECT CHARACTER_MAXIMUM_LENGTH
		FROM information_schema.columns
		WHERE TABLE_SCHEMA='" . $_SESSION['DatabaseName'] . "'
			AND TABLE_NAME='" . $Table . "'
			AND COLUMN_NAME='" . $Column . "'";
	$Result = DB_query($SQL);
	$MyRow = DB_fetch_row($Result);
	if ($MyRow[0] <> $Size) {
		DB_IgnoreForeignKeys();
		$Response = executeSQL("ALTER TABLE " . $Table . " CHANGE COLUMN " . $Column . " " . $Column . " " . $Type . " " . $Null . " DEFAULT '" . $Default . "'", false);
		DB_ReinstateForeignKeys();
		if ($Response == 0) {
			OutputResult(__('The column') . ' ' . $Column . ' ' . __('has been changed'), 'success');
		} else {
			OutputResult(__('The column') . ' ' . $Column . ' ' . __('could not be changed'), 'error');
		}
	} else {
		OutputResult(__('The column') . ' ' . $Column . ' ' . __('is already changed'), 'info');
	}
}

My update file looks like this (34.php). Note ChangeColumnSize() for description works.

<?php

// increase StockID to 64 char
ChangeColumnSize('stockid', 'stockmaster', 'VARCHAR(64)', ' NOT NULL ', '', '64');
// increase stock Description to 255 char
ChangeColumnSize('description', 'stockmaster', 'VARCHAR(255)', ' NOT NULL ', '', '255');

if ($_SESSION['Updates']['Errors'] == 0) {
	UpdateDBNo(basename(__FILE__, '.php'), __('Increase size of stockID and Description'));
}

After the update runs:

[dalers]

Can anyone say why the foreign key containts in the custitem table have embedded spaces in their names?

Iiuc these are the ONLY foreign key constraints with spaces in their names and are, in the least, confusing special cases:

• "[space]custitem[space]_ibfk_1"
• "[space]custitem[space]_ibfk_2"

Would it be possible to remove the spaces? What would be involved?

weberp/install/sql/tables/custitem.sql:

CREATE TABLE `custitem` (
  `debtorno` char(10) NOT NULL DEFAULT '',
  `stockid` varchar(20) NOT NULL DEFAULT '',
  `cust_part` varchar(20) NOT NULL DEFAULT '',
  `cust_description` varchar(30) NOT NULL DEFAULT '',
  `customersuom` char(50) NOT NULL DEFAULT '',
  `conversionfactor` double NOT NULL DEFAULT '1',
  PRIMARY KEY (`debtorno`,`stockid`),
  KEY `StockID` (`stockid`),
  KEY `Debtorno` (`debtorno`),
  CONSTRAINT ` custitem _ibfk_1` FOREIGN KEY (`stockid`) REFERENCES `stockmaster` (`stockid`),
  CONSTRAINT ` custitem _ibfk_2` FOREIGN KEY (`debtorno`) REFERENCES `debtorsmaster` (`debtorno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

[pakricard]

I think it is a typo error that should be corrected.
It comes from commit 81252b5 last August.

R

[dalers]

Thanks @pakricard for investigating. I followd the commit back and iiuc the typo(?) was in the initial commit of custitem.sql and the new installer.

@timschofield, since this was your code, do you see any reason not to "correct" the fk names and remove the spaces?

[dalers]

iiuc, I suspect this also means databases migrated forwards have custitem table fk's without spaces in their names and only databases created with the installer have the extra spaces.

I suspect though this difference is benign and no one notices. I stumbled across the difference wanting to change the size of stockid and description columns, but it's only an annoyance - but a significant annoyance, especially because I'm also discovering different tools made hide or deal with leading spaces differently,

[dalers]

Edit. Made original post a reply to @pakricard's instead to get back to the original topic - increasing stockid and description sizes.

It seems I was naive thinking the only table with a foreign key to the stockmaster stockid column was the custitem table.

After copy/pasting the name of the fk directly from mariadb cli output I was able to drop the constraint from the custitem table but then attempting to change the size of stockid resulted in an fk error in the bom table. After investigating, it seems there are many tables containing an explit fk relationship to stockmaster stockid.

Btw, I don't know if the character at issue was actually a space or if it only appeared as a space and was actually e.g. a tab character.

I don't think it would be difficult to use an upgrade xxx.php file to delete all the fk constraints in all the tables, adjust the sizes of parent and child tables and then re-create the fks, but before doing that, can anyone think of any issues? @pakricard @timschofield? Can it be presumed that all scripts that worked with a shorter stockid and description will continue to work as as expected when the stockid and description are shorter (i.e. comply with the current column size)?

If there is no technical reason why stockid and description size can't be increased (albeit will be more work than originally expected), I think the choice comes down to either:

• increase the size of master data that already exists to support data being imported from a PLM data system and data being exported to an e-store data
• add new columns for "long" stockid and description (e.g. stockid_long and description_long) and update scripts as needed to use the new long columns.

Larger values makes webERP more versatile which presumably adds value, while adding new "long" columns (which I a suspect is a well-known pattern and not something I just dreamed up) creates an additional layer of indirection, requires more scripts to be updated and increases the chaos factor.

What do you think? WDYT?

Drop fk's

MariaDB [weberp_kayden]> alter table custitem drop foreign key ` custitem _ibfk_2`;
Query OK, 0 rows affected (0.007 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [weberp_kayden]> alter table custitem drop foreign key ` custitem _ibfk_1`;
Query OK, 0 rows affected (0.014 sec)
Records: 0  Duplicates: 0  Warnings: 0

Try to change stockid size again

    -> CHANGE COLUMN stockid stockid VARCHAR(64) NOT NULL DEFAULT '';
ERROR 1833 (HY000): Cannot change column 'stockid': used in a foreign key constraint 'weberp_kayden/bom_ibfk_1' of table 'weberp_kayden/bom'

What? How many tables do have a foreign key constraint to stockid?
(note the custitem table with its two constraints is not included below because I have already deleted them).

MariaDB [weberp_kayden]> SELECT
    ->     TABLE_NAME AS 'Referencing Table',
    ->     COLUMN_NAME AS 'Foreign Key Column',
    ->     CONSTRAINT_NAME AS 'Constraint Name'
    -> FROM
    ->     information_schema.KEY_COLUMN_USAGE
    -> WHERE
    ->     REFERENCED_TABLE_NAME = 'stockmaster'
    ->     AND REFERENCED_COLUMN_NAME = 'stockid'
    ->     AND TABLE_SCHEMA = DATABASE(); -- Limits results to your current webERP database
+-----------------------------+--------------------+------------------------------------+
| Referencing Table           | Foreign Key Column | Constraint Name                    |
+-----------------------------+--------------------+------------------------------------+
| bom                         | parent             | bom_ibfk_1                         |
| bom                         | component          | bom_ibfk_2                         |
| contractbom                 | stockid            | contractbom_ibfk_3                 |
| locstock                    | stockid            | locstock_ibfk_2                    |
| loctransfers                | stockid            | loctransfers_ibfk_3                |
| mrpdemands                  | stockid            | mrpdemands_ibfk_2                  |
| offers                      | stockid            | offers_ibfk_2                      |
| orderdeliverydifferenceslog | stockid            | orderdeliverydifferenceslog_ibfk_1 |
| pickreqdetails              | stockid            | pickreqdetails_ibfk_1              |
| prices                      | stockid            | prices_ibfk_1                      |
| purchdata                   | stockid            | purchdata_ibfk_1                   |
| recurrsalesorderdetails     | stkcode            | recurrsalesorderdetails_ibfk_2     |
| salescatprod                | stockid            | salescatprod_ibfk_1                |
| salesorderdetails           | stkcode            | salesorderdetails_ibfk_2           |
| stockcheckfreeze            | stockid            | stockcheckfreeze_ibfk_1            |
| stockcounts                 | stockid            | stockcounts_ibfk_1                 |
| stockitemproperties         | stockid            | stockitemproperties_ibfk_1         |
| stockitemproperties         | stockid            | stockitemproperties_ibfk_3         |
| stockitemproperties         | stockid            | stockitemproperties_ibfk_5         |
| stockmoves                  | stockid            | stockmoves_ibfk_1                  |
| stockrequestitems           | stockid            | stockrequestitems_ibfk_2           |
| stockrequestitems           | stockid            | stockrequestitems_ibfk_4           |
| stockserialitems            | stockid            | stockserialitems_ibfk_1            |
| woitems                     | stockid            | woitems_ibfk_1                     |
| worequirements              | stockid            | worequirements_ibfk_2              |
+-----------------------------+--------------------+------------------------------------+
25 rows in set (0.004 sec)

MariaDB [weberp_kayden]>

[dalers]

P.S. There are 35 tables that have a "stockid" column, which I presume includes the 26 tables with an explicit foreign key constraint and means there are an additional 9 tables with an implicit foreign key relationship (treated as an fk by webERP code but without an explicit constraint).

Does my analysis seem correct?

MariaDB [weberp_kayden]> SELECT
    ->     TABLE_NAME,
    ->     COLUMN_NAME,
    ->     DATA_TYPE
    -> FROM
    ->     information_schema.COLUMNS
    -> WHERE
    ->     COLUMN_NAME = 'stockid'
    ->     AND TABLE_SCHEMA = DATABASE()
    ->     AND TABLE_NAME != 'stockmaster';
+------------------------------+-------------+-----------+
| TABLE_NAME                   | COLUMN_NAME | DATA_TYPE |
+------------------------------+-------------+-----------+
| assetmanager                 | stockid     | varchar   |
| contractbom                  | stockid     | varchar   |
| custitem                     | stockid     | varchar   |
| ediitemmapping               | stockid     | varchar   |
| employees                    | stockid     | varchar   |
| lastcostrollup               | stockid     | char      |
| locstock                     | stockid     | varchar   |
| loctransfercancellations     | stockid     | varchar   |
| loctransfers                 | stockid     | varchar   |
| mrpdemands                   | stockid     | varchar   |
| offers                       | stockid     | varchar   |
| orderdeliverydifferenceslog  | stockid     | varchar   |
| pickreqdetails               | stockid     | varchar   |
| pickserialdetails            | stockid     | varchar   |
| pricematrix                  | stockid     | varchar   |
| prices                       | stockid     | varchar   |
| purchdata                    | stockid     | char      |
| relateditems                 | stockid     | varchar   |
| salesanalysis                | stockid     | varchar   |
| salescatprod                 | stockid     | varchar   |
| sellthroughsupport           | stockid     | varchar   |
| shipmentcharges              | stockid     | varchar   |
| stockcheckfreeze             | stockid     | varchar   |
| stockcounts                  | stockid     | varchar   |
| stockdescriptiontranslations | stockid     | varchar   |
| stockitemproperties          | stockid     | varchar   |
| stockmoves                   | stockid     | varchar   |
| stockrequestitems            | stockid     | varchar   |
| stockserialitems             | stockid     | varchar   |
| stockserialmoves             | stockid     | varchar   |
| supplierdiscounts            | stockid     | varchar   |
| tenderitems                  | stockid     | varchar   |
| woitems                      | stockid     | char      |
| worequirements               | stockid     | varchar   |
| woserialnos                  | stockid     | varchar   |
+------------------------------+-------------+-----------+
35 rows in set (0.016 sec)

[pakricard]

Sounds right to me. Some tables use stkcode to refer to stockid (I guess for historical reasons, some developers preferred one or another).

Probably is time to unify all synonyms to stockid. Probably also unify all of them to varcahr, not char, to avoid type mismatches as PHP gets stricter on this issue at every new version.

R

[dalers]

Probably is time to unify all synonyms to stockid. Probably also unify all of them to varcahr, not char, to avoid type mismatches as PHP gets stricter on this issue at every new version.

It would not be difficult to make the types consistent (and I will do so in the PR, assuming we continue on this path), but imho there isn't enough benefit to unify the synonyms - there are only two occurances of stkcode and the fk's in the bom table ("parent" and "component") need different names to identify use in the context of a BOM.

[dalers]

Also, there could be more implicit fk relationships where the child column is something other than "stockid". I expect the likelihood of this is low enough to not demand an exhaustive review, but still there is no guarantee.

[dalers]

Fyi, found what appears to be the stockid foreign keys in two tables themselves being used as foreign keys in other tables. #835

Warning: mysqli_query(): (HY000/1833): Cannot change column 'stockid': used in a foreign key constraint 'weberp_kayden/stockserialmoves_ibfk_2' of table 'weberp_kayden/stockserialmoves' in C:\xampp\htdocs\weberp\includes\ConnectDB_mariadb.php on line 63

Warning: mysqli_query(): (HY000/1833): Cannot change column 'stockid': used in a foreign key constraint 'worequirements_ibfk_3' of table 'weberp_kayden.worequirements' in C:\xampp\htdocs\weberp\includes\ConnectDB_mariadb.php on line 63

[dalers]

@timschofield @pakricard

Progress Update - I have changed the size of stockmaster.stockid and all explicit foreign key constraint child columns and so far so good (fwiw, the code is in the drs/plm branch in my webERP fork)

Implicit relationships to stockmaster.stockid will need brute force. I think I've found all the potential changes but some of them will be unrelated and I would very much appreciate your comments (otherwise I will need to search for and analyze all the code in all the files that uses each column, which will taking a huge amount of time and I could easily misunderstand the code leading to errors).

Trying to use the simplest feedback approach, I think a wiki page might work well and is in keeping with the purpose of the wiki being for fine-tuning requirements before starting development.

https://github.com/timschofield/webERP/wiki/PLM-Features-Increasing-stockid-size

Please edit the page and add comments where applicable. A wiki page will allow multiple people to comment on the same potential change instead of replying seperately like in a discussion or issue. If you start a comment with your Github "@name" (e.g. "@dalers - this column is not related to stockid") will make it easy to see who said what (the page history will also show who said what but much more cumbersomely).

Fwiw, I also updated the parent "PLM Features" wiki page with an outline of intended schema changes: https://github.com/timschofield/webERP/wiki/PLM-Features#database-changes

[dalers]

@timschofield @pakricard How would I create a compound foreign key relationship using the include/UpgradeDB functions?

I need to re-add the stockserialmoves_ibfk_2 fk constraint in the stockserialmoves table exactly as it is now (after first deleting it in order to change the size of the child column).

Interesting, the parent of the constraint is stockserialnumbers.stockid, which doesn't have a foreign key constraint to stockmaster.stockid.

However, I'm assuming that how it is now is correct.

[dalers]

Fyi, Tim solved the question of how to create a compound constraint in issue #841 (comment)

AddConstraint('stockserialmoves', 'stockserialmoves_ibfk_2', array('stockid', 'serialno'), 'stockserialitems', array('stockid', 'serialno'));

[dalers]

I think I'm done :-)

I would appreciate it if you could review the update script in the drs/plm branch of my fork https://github.com/dalers/webERP/blob/drs/plm/sql/updates/42.php - in particular where there are comments "TODO review by someone with more understanding of the code than the author" ;-)

The script runs correctly on a vanilla installation (db created by the installer late fall 2025), Is there anything you would like done before I submit a PR with the update?

Update procedure:

1. drop foreign key constraints on stockmaster.stockid
   1.1 drop grandchild fk constraints
   1.2 drop child fk constraints

2. change size of "stockid" parent, child and grandchild foreign key columns from 20 char to 64 char
   2.1 change size grandchild fk columns
   2.2 change size child fk columns
   2.3 change size parent fk column (stockmaster.stockid)

3. add (re-add) fk constraints
   3.1 add child fk constraints
   3.2 add grandchild fk constraints

4. change size of IMPLICIT stockid child foreign key columns from 20 char to 64 char
   4.1 columns named stockid
   4.2 columns named stkcode
   4.3 relevent columns defined as "varchar(20)"
   4.4 relevent columns with "code" in their name
   4.5 relevent columns with "id" in their name

5. increase size of stockmaster.description from 50 char to 255 char

There are search screenshots for step 4 in the wiki https://github.com/timschofield/webERP/wiki/PLM-Features-Increasing-stockid-size

[pakricard]

Hi @dalers :

I executed update 43.php and got:

Warning: mysqli_query(): (HY000/1833): Cannot change column 'stockid': used in a foreign key constraint 'test_erp/stk_ibfk_1' of table 'test_erp/employees' in /includes/ConnectDB_mariadb.php on line 63

Warning: mysqli_query(): (HY000/1832): Cannot change column 'stockid': used in a foreign key constraint 'test_erp/stk_ibfk_1' in /includes/ConnectDB_mariadb.php on line 63

I fixed this adding

DropConstraint('employees', 'stk_ibfk_1');
...

...
AddConstraint('employees', 'stk_ibfk_1', 'stockid', 'stockmaster', 'stockid');

Regarding custitem.cust_part: Should be increased as well to varchar(64) for consistency?
If so, probably custitem.cust_description should be increased too.

[timschofield]

I noticed that I don't have this constraint on my employees table (presumably why I didn't get this error). Maybe we need to check that all tables that should have constraints on the stockid field actually do have it?

Tim

[pakricard]

Tim:

I got this constraint from sql\mysql\upgrade4.15-4.15.1.sql (2019).

So, if the installation is somehow recent or older, we might have different DB schemas. I think this is a weak point we still have with the double system of maintaining the schema (fresh install vs updates) as it can lead to similar issues.

R

[timschofield]

Hi @dalers while looking at the above comment from @pakricard I noticed that the table schemas in install/sql/tables/ need updating for the new stockid length.

Tim

[timschofield]

While we can get away with not updating the table schemas between releases, it is not a very desirable option. When a releases is done, somebody (normally me) has to then trawl through the update files and update the schemas where they need updating. For this reason I always try to do the updates to the schema as we go along.

Tim

[dalers]

@timschofield

trawl through the update files and update the schemas where they need updating.

I wonder if it would be possible to write a script that could create a db using the table schemas, apply the update to the db snd then extract the updated table schemas back to individual table schema files.

[timschofield]

I did try automatically creating the schema files, but it just proved too hard (for me anyway) to create files such that the installer would work under all mysql sql-modes. Updating them as we go along is normally quite easy, but I accept that it is harder for a big update like yours.

Tim

[dalers]

I'm not understanding the big picture.

1. What is the benefit of individual table schema and initial content files vs a single "db.sql" file?

2. After I write and test an update xx.php file, would it be possible to query the updated db with SQL queries to extract the table schema and content for any affected tables and use it to overwrite individual table files? (either manually or added to the update xx.php file)

I checked the Installer page in the wiki but it doesn't describe the design of the new installer or its justification (other than to say the UX can be more polished and professional).

[timschofield]

1. What is the benefit of individual table schema and initial content files vs a single "db.sql" file?

My tests when I was creating the installer showed that the individual files were a lot quicker than a monolithic file to execute, and one of the issues with the old installer was that it kept timing out. With the separate files this went away.

2. After I write and test an update xx.php file, would it be possible to query the updated db with SQL queries to extract the table schema and content for any affected tables and use it to overwrite individual table files? (either manually or  added to the update xx.php file)

This feels like a huge amount of work for something so simple. When I did the stock item notes commit, creating the stockitemnotes.sql file took me less than 60 seconds to do. I accept that for your PR increasing the size of the stockid field it is a bigger job, but this is a once in a decade type change affecting so many tables.

Tim

[dalers]

This feels like a huge amount of work for something so simple.

I'm just not getting a) why it would be a huge amount of work (although I readily admit not having actually created such a script or even worked through the process manually, so don't actually know...), or b) why wouldn't you want to automate a simple manual process and eliminate the possibility of accidential errors, it seems low hanging fruit.

[timschofield]

I spent a lot of time initially trying to automate the production of the individual schemas. There was always a ton of edge cases which meant however hard I tried there was always manual changes that needed doing after runniung the script. Someone smarter than me might be able to achieve this, but in the end I couldn't see the point in spending weeks trying to automate a task that could be done manually in under a minute most of the time.

However you are welcome to try yourself.

Tim

[dalers]

welcome to try yourself

Thanks! :-) I just need to understand it first though.

Can we continue the general installer discussion in a seperate new Discuss topic #865 (assuming you also think there is a benefit).

For me, preventing ways that can cause webERP to create a broken db has a lot of merit, as does reducing developer effort on housekeeping activities.

[timschofield]

Reply moved to #865 (comment) to not distract from the original topic here.