[Beta] MySQL/MariaDB Database Compatibility #1038

Open
opened 2024-01-26 18:44:46 +00:00 by stutteringp0et · 2 comments
Member

Steps to reproduce the issue

Install on any newer version of MySQL or MariaDB

Expected result

Actual result

System information (as much as possible)

  • OS Name & Version:
  • MySql Version: MariaDB 10.6
  • Apache Version:
  • PHP Version: 8.1
  • Joomla Version: 5
  • JCB Version: 3.1.34
  • Browser:

Additional comments

It's not a Joomla issue, it's a MySQL/MariaDB issue. The error is an SQL error regarding the structure of the SQL statement missing a required value. I haven't been able to apply the prescribed fixes successfully, however.

https://mariadb.com/kb/en/sql-mode/

STRICT_TRANS_TABLES
bla-bla-bla...Statements with invalid or missing data are aborted and rolled back

Unfortunately, even disabling STRICT_TRANS_TABLES doesn't resolve this issue. So I'm still fighting this.

So, it is a JCB issue in that a field is defined NOT NULL but does not have a defined default value, AND it is a MySQL/MariaDB issue in that the configuration to bypass the issue prescribed by the project doesn't seem to work (anymore?).

Looking at install.mysql.utf8.sql, we can see that the addcontributors entry has the NOT NULL flag but no default value.

`add_update_server` TINYINT(1) NOT NULL DEFAULT 0,
`addcontributors` TEXT NOT NULL,
`addfootable` TINYINT(1) NOT NULL DEFAULT 0,

That's the first item in the SQL to have that issue, which is why the installation halts there. Only a few lines later there is "cwrowdin_account_api_key" which also has the issue. I didn't bother to count the number of times this occurs, because it's in the hundreds. The solution remains the same, either fix the issue with the SQL or force the database server to ignore it.

Fixing the SQL would be relatively simple, any item that doesn't have a default value could be null. Just remove the NOT NULL flag because it's going to be blank anyway. Another option would be to insert a default value depending on type. Text = "", *int = 0, etc...

Forcing the DB server to behave like versions past would be the easiest solution, but not all installations may have access to these types of DB configurations.

### Steps to reproduce the issue Install on any newer version of MySQL or MariaDB ### Expected result ### Actual result ### System information (as much as possible) - OS Name & Version: - MySql Version: MariaDB 10.6 - Apache Version: - PHP Version: 8.1 - Joomla Version: 5 - JCB Version: 3.1.34 - Browser: ### Additional comments It's not a Joomla issue, it's a MySQL/MariaDB issue. The error is an SQL error regarding the structure of the SQL statement missing a required value. I haven't been able to apply the prescribed fixes successfully, however. https://mariadb.com/kb/en/sql-mode/ STRICT_TRANS_TABLES bla-bla-bla...Statements with invalid or missing data are aborted and rolled back Unfortunately, even disabling STRICT_TRANS_TABLES doesn't resolve this issue. So I'm still fighting this. So, it is a JCB issue in that a field is defined NOT NULL but does not have a defined default value, AND it is a MySQL/MariaDB issue in that the configuration to bypass the issue prescribed by the project doesn't seem to work (anymore?). Looking at install.mysql.utf8.sql, we can see that the addcontributors entry has the NOT NULL flag but no default value. `add_update_server` TINYINT(1) NOT NULL DEFAULT 0, `addcontributors` TEXT NOT NULL, `addfootable` TINYINT(1) NOT NULL DEFAULT 0, That's the first item in the SQL to have that issue, which is why the installation halts there. Only a few lines later there is "cwrowdin_account_api_key" which also has the issue. I didn't bother to count the number of times this occurs, because it's in the hundreds. The solution remains the same, either fix the issue with the SQL or force the database server to ignore it. Fixing the SQL would be relatively simple, any item that doesn't have a default value could be null. Just remove the NOT NULL flag because it's going to be blank anyway. Another option would be to insert a default value depending on type. Text = "", *int = 0, etc... Forcing the DB server to behave like versions past would be the easiest solution, but not all installations may have access to these types of DB configurations.
Owner

I agree lets fix the SQL statments and lets add the correct either default or null value.

The database stuff is being build in the BETA branch at this one huge method (that has not been refactored to a class yet, but will be):

public function setInstall()
{
	if (($database_tables = CFactory::_('Compiler.Builder.Database.Tables')->allActive()) !== [])
	{
		// set the main db prefix
		$component = CFactory::_('Config')->component_code_name;
		// start building the db
		$db = '';
		foreach ($database_tables as $view => $fields)
		{
			// cast the object to an array TODO we must update all to use the object
			$fields = (array) $fields;
			// build the uninstallation array
			$this->uninstallBuilder[] = "DROP TABLE IF EXISTS `#__"
				. $component . "_" . $view . "`;";

			// setup the table DB string
			$db_ = '';
			$db_ .= "CREATE TABLE IF NOT EXISTS `#__" . $component . "_"
				. $view . "` (";
			// check if the table name has changed
			if (($old_table_name = CFactory::_('Registry')->
				get('builder.update_sql.table_name.' . $view . '.old', null)) !== null)
			{
				$key_ = "RENAMETABLE`#__" . $component . "_" . $old_table_name . "`";
				$value_ = "RENAME TABLE `#__" . $component . "_" . $old_table_name . "` to `#__"
					. $component . "_" . $view . "`;";

				CFactory::_('Compiler.Builder.Update.Mysql')->set($key_, $value_);
			}
			// check if default field was over written
			if (!CFactory::_('Compiler.Builder.Field.Names')->isString($view . '.id'))
			{
				$db_ .= PHP_EOL . Indent::_(1)
					. "`id` INT(11) NOT NULL AUTO_INCREMENT,";
			}
			$db_ .= PHP_EOL . Indent::_(1)
				. "`asset_id` INT(10) unsigned NOT NULL DEFAULT 0 COMMENT 'FK to the #__assets table.',";
			ksort($fields);
			$last_name = 'asset_id';
			foreach ($fields as $field => $data)
			{
				// cast the object to an array TODO we must update all to use the object
				$data = (array) $data;
				// set default
				$default = $data['default'];
				if ($default === 'Other')
				{
					$default = $data['other'];
				}
				// to get just null value add EMPTY to other value.
				if ($default === 'EMPTY')
				{
					$default = $data['null_switch'];
				}
				elseif ($default === 'DATETIME'
					|| $default === 'CURRENT_TIMESTAMP')
				{
					$default = $data['null_switch'] . ' DEFAULT '
						. $default;
				}
				elseif (is_numeric($default))
				{
					$default = $data['null_switch'] . " DEFAULT "
						. $default;
				}
				else
				{
					$default = $data['null_switch'] . " DEFAULT '"
						. $default . "'";
				}

				// set the length (lenght) <-- TYPO :: LVDM :: DON'T TOUCH
				$length = '';
				if (isset($data['lenght']) && $data['lenght'] === 'Other'
					&& isset($data['lenght_other'])
					&& $data['lenght_other'] > 0)
				{
					$length = '(' . $data['lenght_other'] . ')';
				}
				elseif (isset($data['lenght']) && $data['lenght'] > 0)
				{
					$length = '(' . $data['lenght'] . ')';
				}
				// set the field to db
				$db_ .= PHP_EOL . Indent::_(1) . "`" . $field . "` "
					. $data['type'] . $length . " " . $default . ",";
				// check if this a new field that should be added via SQL update
				if (CFactory::_('Registry')->
					get('builder.add_sql.field.' . $view . '.' . $data['ID'], null))
				{
					// to soon....
					// $key_ = "ALTERTABLE`#__" . $component . "_" . $view . "`ADDCOLUMNIFNOTEXISTS`" . $field . "`";
					// $value_ = "ALTER TABLE `#__" . $component . "_" . $view . "` ADD COLUMN IF NOT EXISTS `" . $field . "` " . $data['type']
					//	. length . " " . $default . " AFTER `" . $last_name . "`;";
					$key_ = "ALTERTABLE`#__" . $component . "_" . $view . "`ADD`" . $field . "`";
					$value_ = "ALTER TABLE `#__" . $component . "_" . $view . "` ADD `" . $field . "` " . $data['type']
						. $length . " " . $default . " AFTER `" . $last_name . "`;";

					CFactory::_('Compiler.Builder.Update.Mysql')->set($key_, $value_);
				}
				// check if the field has changed name and/or data type and lenght
				elseif (CFactory::_('Registry')->
					get('builder.update_sql.field.datatype.' . $view . '.' . $field, null)
					|| CFactory::_('Registry')->
					get('builder.update_sql.field.lenght.' . $view . '.' . $field, null)
					|| CFactory::_('Registry')->
					get('builder.update_sql.field.name.' . $view . '.' . $field, null))
				{
					// if the name changed
					if (($oldName = CFactory::_('Registry')->
						get('builder.update_sql.field.name.' . $view . '.' . $field . '.old', null)) === null)
					{
						$oldName = $field;
					}

					// now set the update SQL
					$key_ = "ALTERTABLE`#__" . $component . "_" . $view . "`CHANGE`" . $oldName . "``"
						. $field . "`";
					$value_ = "ALTER TABLE `#__" . $component . "_" . $view . "` CHANGE `" . $oldName . "` `"
						. $field . "` " . $data['type'] . $length . " " . $default . ";";

					CFactory::_('Compiler.Builder.Update.Mysql')->set($key_, $value_);
				}
				// be sure to track the last name used :)
				$last_name = $field;
			}
			// check if default field was overwritten
			if (!CFactory::_('Compiler.Builder.Field.Names')->isString($view . '.params'))
			{
				$db_ .= PHP_EOL . Indent::_(1) . "`params` text NULL,";
			}
			// check if default field was overwritten
			if (!CFactory::_('Compiler.Builder.Field.Names')->isString($view . '.published'))
			{
				$db_ .= PHP_EOL . Indent::_(1)
					. "`published` TINYINT(3) NOT NULL DEFAULT 1,";
			}
			// check if default field was overwritten
			if (!CFactory::_('Compiler.Builder.Field.Names')->isString($view . '.created_by'))
			{
				$db_ .= PHP_EOL . Indent::_(1)
					. "`created_by` INT(10) unsigned NOT NULL DEFAULT 0,";
			}
			// check if default field was overwritten
			if (!CFactory::_('Compiler.Builder.Field.Names')->isString($view . '.modified_by'))
			{
				$db_ .= PHP_EOL . Indent::_(1)
					. "`modified_by` INT(10) unsigned NOT NULL DEFAULT 0,";
			}
			// check if default field was overwritten
			if (!CFactory::_('Compiler.Builder.Field.Names')->isString($view . '.created'))
			{
				$db_ .= PHP_EOL . Indent::_(1)
					. "`created` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',";
			}
			// check if default field was overwritten
			if (!CFactory::_('Compiler.Builder.Field.Names')->isString($view . '.modified'))
			{
				$db_ .= PHP_EOL . Indent::_(1)
					. "`modified` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',";
			}
			// check if default field was overwritten
			if (!CFactory::_('Compiler.Builder.Field.Names')->isString($view . '.checked_out'))
			{
				$db_ .= PHP_EOL . Indent::_(1)
					. "`checked_out` int(11) unsigned NOT NULL DEFAULT 0,";
			}
			// check if default field was overwritten
			if (!CFactory::_('Compiler.Builder.Field.Names')->isString($view . '.checked_out_time'))
			{
				$db_ .= PHP_EOL . Indent::_(1)
					. "`checked_out_time` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',";
			}
			// check if default field was overwritten
			if (!CFactory::_('Compiler.Builder.Field.Names')->isString($view . '.version'))
			{
				$db_ .= PHP_EOL . Indent::_(1)
					. "`version` INT(10) unsigned NOT NULL DEFAULT 1,";
			}
			// check if default field was overwritten
			if (!CFactory::_('Compiler.Builder.Field.Names')->isString($view . '.hits'))
			{
				$db_ .= PHP_EOL . Indent::_(1)
					. "`hits` INT(10) unsigned NOT NULL DEFAULT 0,";
			}
			// check if view has access
			if (CFactory::_('Compiler.Builder.Access.Switch')->exists($view)
				&& !CFactory::_('Compiler.Builder.Field.Names')->isString($view . '.access'))
			{
				$db_ .= PHP_EOL . Indent::_(1)
					. "`access` INT(10) unsigned NOT NULL DEFAULT 0,";
			}
			// check if default field was overwritten
			if (!CFactory::_('Compiler.Builder.Field.Names')->isString($view . '.ordering'))
			{
				$db_ .= PHP_EOL . Indent::_(1)
					. "`ordering` INT(11) NOT NULL DEFAULT 0,";
			}
			// check if metadata is added to this view
			if (CFactory::_('Compiler.Builder.Meta.Data')->isString($view))
			{
				// check if default field was overwritten
				if (!CFactory::_('Compiler.Builder.Field.Names')->isString($view . '.metakey'))
				{
					$db_ .= PHP_EOL . Indent::_(1)
						. "`metakey` TEXT NOT NULL,";
				}
				// check if default field was overwritten
				if (!CFactory::_('Compiler.Builder.Field.Names')->isString($view . '.metadesc'))
				{
					$db_ .= PHP_EOL . Indent::_(1)
						. "`metadesc` TEXT NOT NULL,";
				}
				// check if default field was overwritten
				if (!CFactory::_('Compiler.Builder.Field.Names')->isString($view . '.metadata'))
				{
					$db_ .= PHP_EOL . Indent::_(1)
						. "`metadata` TEXT NOT NULL,";
				}
			}
			// TODO (we may want this to be dynamicly set)
			$db_ .= PHP_EOL . Indent::_(1) . "PRIMARY KEY  (`id`)";
			// check if a key was set for any of the default fields then we should not set it again
			$check_keys_set = [];
			if (CFactory::_('Compiler.Builder.Database.Unique.Keys')->exists($view))
			{
				foreach (CFactory::_('Compiler.Builder.Database.Unique.Keys')->get($view) as $nr => $key)
				{
					$db_ .= "," . PHP_EOL . Indent::_(1)
						. "UNIQUE KEY `idx_" . $key . "` (`" . $key . "`)";
					$check_keys_set[$key] = $key;
				}
			}
			if (CFactory::_('Compiler.Builder.Database.Keys')->exists($view))
			{
				foreach (CFactory::_('Compiler.Builder.Database.Keys')->get($view) as $nr => $key)
				{
					$db_ .= "," . PHP_EOL . Indent::_(1)
						. "KEY `idx_" . $key . "` (`" . $key . "`)";
					$check_keys_set[$key] = $key;
				}
			}
			// check if view has access
			if (!isset($check_keys_set['access'])
				&& CFactory::_('Compiler.Builder.Access.Switch')->exists($view))
			{
				$db_ .= "," . PHP_EOL . Indent::_(1)
					. "KEY `idx_access` (`access`)";
			}
			// check if default field was overwritten
			if (!isset($check_keys_set['checked_out']))
			{
				$db_ .= "," . PHP_EOL . Indent::_(1)
					. "KEY `idx_checkout` (`checked_out`)";
			}
			// check if default field was overwritten
			if (!isset($check_keys_set['created_by']))
			{
				$db_ .= "," . PHP_EOL . Indent::_(1)
					. "KEY `idx_createdby` (`created_by`)";
			}
			// check if default field was overwritten
			if (!isset($check_keys_set['modified_by']))
			{
				$db_ .= "," . PHP_EOL . Indent::_(1)
					. "KEY `idx_modifiedby` (`modified_by`)";
			}
			// check if default field was overwritten
			if (!isset($check_keys_set['published']))
			{
				$db_ .= "," . PHP_EOL . Indent::_(1)
					. "KEY `idx_state` (`published`)";
			}
			// easy bucket
			$easy = [];
			// get the mysql table settings
			foreach (
				CFactory::_('Config')->mysql_table_keys as $_mysqlTableKey => $_mysqlTableVal
			)
			{
				if (($easy[$_mysqlTableKey] = CFactory::_('Compiler.Builder.Mysql.Table.Setting')->
					get($view . '.' . $_mysqlTableKey)) === null)
				{
					$easy[$_mysqlTableKey]
						= CFactory::_('Config')->mysql_table_keys[$_mysqlTableKey]['default'];
				}
			}
			// add a little fix for the row_format
			if (StringHelper::check($easy['row_format']))
			{
				$easy['row_format'] = ' ROW_FORMAT=' . $easy['row_format'];
			}
			// now build db string
			$db_ .= PHP_EOL . ") ENGINE=" . $easy['engine']
				. " AUTO_INCREMENT=0 DEFAULT CHARSET=" . $easy['charset']
				. " DEFAULT COLLATE=" . $easy['collate']
				. $easy['row_format'] . ";";

			// check if this is a new table that should be added via update SQL
			if (CFactory::_('Registry')->
				get('builder.add_sql.adminview.' . $view, null))
			{
				// build the update array
				$key_ = "CREATETABLEIFNOTEXISTS`#__" . $component . "_" . $view . "`";
				CFactory::_('Compiler.Builder.Update.Mysql')->set($key_, $db_);
			}
			// check if the table row_format has changed
			if (StringHelper::check($easy['row_format'])
				&& CFactory::_('Registry')->
				get('builder.update_sql.table_row_format.' . $view, null))
			{
				// build the update array
				$key_ = "ALTERTABLE`#__" . $component . "_" . $view . "`" . trim((string) $easy['row_format']);
				$value_ = "ALTER TABLE `#__" . $component . "_" . $view . "`" . $easy['row_format'] . ";";
				CFactory::_('Compiler.Builder.Update.Mysql')->set($key_, $value_);
			}
			// check if the table engine has changed
			if (CFactory::_('Registry')->
				get('builder.update_sql.table_engine.' . $view, null))
			{
				// build the update array
				$key_ = "ALTERTABLE`#__" . $component . "_" . $view . "`ENGINE=" . $easy['engine'];
				$value_ = "ALTER TABLE `#__" . $component . "_" . $view . "` ENGINE = " . $easy['engine'] . ";";
				CFactory::_('Compiler.Builder.Update.Mysql')->set($key_, $value_);
			}
			// check if the table charset OR collation has changed (must be updated together)
			if (CFactory::_('Registry')->
				get('builder.update_sql.table_charset.' . $view, null)
				|| CFactory::_('Registry')->
				get('builder.update_sql.table_collate.' . $view, null))
			{
				// build the update array
				$key_ = "ALTERTABLE`#__" . $component . "_" . $view . "CONVERTTOCHARACTERSET"
					. $easy['charset'] . "COLLATE" . $easy['collate'];
				$value_ = "ALTER TABLE `#__" . $component . "_" . $view . "` CONVERT TO CHARACTER SET "
					. $easy['charset'] . " COLLATE " . $easy['collate'] . ";";

				CFactory::_('Compiler.Builder.Update.Mysql')->set($key_, $value_);
			}

			// add to main DB string
			$db .= $db_ . PHP_EOL . PHP_EOL;
		}
		// add custom sql dump to the file
		if (isset(CFactory::_('Customcode.Dispenser')->hub['sql'])
			&& ArrayHelper::check(
				CFactory::_('Customcode.Dispenser')->hub['sql']
			))
		{
			foreach (
				CFactory::_('Customcode.Dispenser')->hub['sql'] as $for => $customSql
			)
			{
				$placeholders = array(Placefix::_('component') => $component,
					Placefix::_('view') => $for);
				$db           .= CFactory::_('Placeholder')->update(
						$customSql, $placeholders
					) . PHP_EOL . PHP_EOL;
			}
			unset(CFactory::_('Customcode.Dispenser')->hub['sql']);
		}

		// WHY DO WE NEED AN ASSET TABLE FIX?
		// https://www.mysqltutorial.org/mysql-varchar/
		// https://stackoverflow.com/a/15227917/1429677
		// https://forums.mysql.com/read.php?24,105964,105964
		// https://github.com/vdm-io/Joomla-Component-Builder/issues/616#issuecomment-741502980
		// 30 actions each +-20 characters with 8 groups
		// that makes 4800 characters and the current Joomla
		// column size is varchar(5120)

		// just a little event tracking in classes
		// count actions = setAccessSections
		//                 around line206 (infusion call)
		//                 around line26454 (interpretation function)
		// first fix = setInstall
		//                 around line1600 (infusion call)
		//                 around line10063 (interpretation function)
		// second fix = setUninstallScript
		//                 around line2161 (infusion call)
		//                 around line8030 (interpretation function)

		// check if this component needs larger rules
		// also check if the developer will allow this
		// the access actions length must be checked before this
		// only add this option if set to SQL fix
		if (CFactory::_('Config')->add_assets_table_fix == 1)
		{
			// 400 actions worse case is larger than 65535 characters
			if (CFactory::_('Utilities.Counter')->accessSize > 400)
			{
				$db .= PHP_EOL;
				$db .= PHP_EOL . '--';
				$db .= PHP_EOL
					. '--' . Line::_(
						__LINE__,__CLASS__
					)
					. ' Always insure this column rules is large enough for all the access control values.';
				$db .= PHP_EOL . '--';
				$db .= PHP_EOL
					. "ALTER TABLE `#__assets` CHANGE `rules` `rules` MEDIUMTEXT NOT NULL COMMENT 'JSON encoded access control. Enlarged to MEDIUMTEXT by JCB';";
			}
			// smaller then 400 makes TEXT large enough
			elseif (CFactory::_('Config')->add_assets_table_fix == 1)
			{
				$db .= PHP_EOL;
				$db .= PHP_EOL . '--';
				$db .= PHP_EOL
					. '--' . Line::_(
						__LINE__,__CLASS__
					)
					. ' Always insure this column rules is large enough for all the access control values.';
				$db .= PHP_EOL . '--';
				$db .= PHP_EOL
					. "ALTER TABLE `#__assets` CHANGE `rules` `rules` TEXT NOT NULL COMMENT 'JSON encoded access control. Enlarged to TEXT by JCB';";
			}
		}

		// check if this component needs larger names
		// also check if the developer will allow this
		// the config length must be checked before this
		// only add this option if set to SQL fix
		if (CFactory::_('Config')->add_assets_table_fix && CFactory::_('Config')->add_assets_table_name_fix)
		{
			$db .= PHP_EOL;
			$db .= PHP_EOL . '--';
			$db .= PHP_EOL
				. '--' . Line::_(
					__LINE__,__CLASS__
				)
				. ' Always insure this column name is large enough for long component and view names.';
			$db .= PHP_EOL . '--';
			$db .= PHP_EOL
				. "ALTER TABLE `#__assets` CHANGE `name` `name` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'The unique name for the asset.';";
		}

		return $db;
	}

	return '';
}

What is nice is it all happens right here ;)

I am also sure we can easy move this to a class now... and in the process refine it some more, all its dependencies are already in their own classes:

These are the dependencies:

--------------------------
$this->...[
--------------------------
$this->uninstallBuilder[  <--- [except this one will also need to move to a builder class]
--------------------------
CFactory::_(...)->
--------------------------
CFactory::_('Compiler.Builder.Database.Tables')->
CFactory::_('Config')->
CFactory::_('Registry')->
CFactory::_('Compiler.Builder.Update.Mysql')->
CFactory::_('Compiler.Builder.Field.Names')->
CFactory::_('Compiler.Builder.Access.Switch')->
CFactory::_('Compiler.Builder.Meta.Data')->
CFactory::_('Compiler.Builder.Database.Unique.Keys')->
CFactory::_('Compiler.Builder.Database.Keys')->
CFactory::_('Compiler.Builder.Mysql.Table.Setting')->
CFactory::_('Customcode.Dispenser')->
CFactory::_('Placeholder')->
CFactory::_('Utilities.Counter')->
--------------------------
Helpers::_()
--------------------------
Indent::_(
StringHelper::check(
ArrayHelper::check(
Placefix::_(
Line::_(

Here you can see each class (boring for the most part):

  • CFactory::_('Compiler.Builder.Database.Tables')-> class
  • CFactory::_('Config')-> class
  • CFactory::_('Registry')-> class
  • CFactory::_('Compiler.Builder.Update.Mysql')-> class
  • CFactory::_('Compiler.Builder.Field.Names')-> class
  • CFactory::_('Compiler.Builder.Access.Switch')-> class
  • CFactory::_('Compiler.Builder.Meta.Data')-> class
  • CFactory::_('Compiler.Builder.Database.Unique.Keys')-> class
  • CFactory::_('Compiler.Builder.Database.Keys')-> class
  • CFactory::_('Compiler.Builder.Mysql.Table.Setting')-> class
  • CFactory::_('Customcode.Dispenser')-> class
  • CFactory::_('Placeholder')-> class
  • CFactory::_('Utilities.Counter')-> class

They are already all added to the container via their respective service providers.

I will do this since only I currently have the mapped JCB instance, but on the side of improving the function so that it will meet the new objectives, take a look at the code and tell me where you would change it, and how... if you have time 👍

I agree lets fix the SQL statments and lets add the correct either default or null value. The database stuff is being build in the BETA branch at this one huge method (that has not been refactored to a class yet, but will be): ``` public function setInstall() { if (($database_tables = CFactory::_('Compiler.Builder.Database.Tables')->allActive()) !== []) { // set the main db prefix $component = CFactory::_('Config')->component_code_name; // start building the db $db = ''; foreach ($database_tables as $view => $fields) { // cast the object to an array TODO we must update all to use the object $fields = (array) $fields; // build the uninstallation array $this->uninstallBuilder[] = "DROP TABLE IF EXISTS `#__" . $component . "_" . $view . "`;"; // setup the table DB string $db_ = ''; $db_ .= "CREATE TABLE IF NOT EXISTS `#__" . $component . "_" . $view . "` ("; // check if the table name has changed if (($old_table_name = CFactory::_('Registry')-> get('builder.update_sql.table_name.' . $view . '.old', null)) !== null) { $key_ = "RENAMETABLE`#__" . $component . "_" . $old_table_name . "`"; $value_ = "RENAME TABLE `#__" . $component . "_" . $old_table_name . "` to `#__" . $component . "_" . $view . "`;"; CFactory::_('Compiler.Builder.Update.Mysql')->set($key_, $value_); } // check if default field was over written if (!CFactory::_('Compiler.Builder.Field.Names')->isString($view . '.id')) { $db_ .= PHP_EOL . Indent::_(1) . "`id` INT(11) NOT NULL AUTO_INCREMENT,"; } $db_ .= PHP_EOL . Indent::_(1) . "`asset_id` INT(10) unsigned NOT NULL DEFAULT 0 COMMENT 'FK to the #__assets table.',"; ksort($fields); $last_name = 'asset_id'; foreach ($fields as $field => $data) { // cast the object to an array TODO we must update all to use the object $data = (array) $data; // set default $default = $data['default']; if ($default === 'Other') { $default = $data['other']; } // to get just null value add EMPTY to other value. if ($default === 'EMPTY') { $default = $data['null_switch']; } elseif ($default === 'DATETIME' || $default === 'CURRENT_TIMESTAMP') { $default = $data['null_switch'] . ' DEFAULT ' . $default; } elseif (is_numeric($default)) { $default = $data['null_switch'] . " DEFAULT " . $default; } else { $default = $data['null_switch'] . " DEFAULT '" . $default . "'"; } // set the length (lenght) <-- TYPO :: LVDM :: DON'T TOUCH $length = ''; if (isset($data['lenght']) && $data['lenght'] === 'Other' && isset($data['lenght_other']) && $data['lenght_other'] > 0) { $length = '(' . $data['lenght_other'] . ')'; } elseif (isset($data['lenght']) && $data['lenght'] > 0) { $length = '(' . $data['lenght'] . ')'; } // set the field to db $db_ .= PHP_EOL . Indent::_(1) . "`" . $field . "` " . $data['type'] . $length . " " . $default . ","; // check if this a new field that should be added via SQL update if (CFactory::_('Registry')-> get('builder.add_sql.field.' . $view . '.' . $data['ID'], null)) { // to soon.... // $key_ = "ALTERTABLE`#__" . $component . "_" . $view . "`ADDCOLUMNIFNOTEXISTS`" . $field . "`"; // $value_ = "ALTER TABLE `#__" . $component . "_" . $view . "` ADD COLUMN IF NOT EXISTS `" . $field . "` " . $data['type'] // . length . " " . $default . " AFTER `" . $last_name . "`;"; $key_ = "ALTERTABLE`#__" . $component . "_" . $view . "`ADD`" . $field . "`"; $value_ = "ALTER TABLE `#__" . $component . "_" . $view . "` ADD `" . $field . "` " . $data['type'] . $length . " " . $default . " AFTER `" . $last_name . "`;"; CFactory::_('Compiler.Builder.Update.Mysql')->set($key_, $value_); } // check if the field has changed name and/or data type and lenght elseif (CFactory::_('Registry')-> get('builder.update_sql.field.datatype.' . $view . '.' . $field, null) || CFactory::_('Registry')-> get('builder.update_sql.field.lenght.' . $view . '.' . $field, null) || CFactory::_('Registry')-> get('builder.update_sql.field.name.' . $view . '.' . $field, null)) { // if the name changed if (($oldName = CFactory::_('Registry')-> get('builder.update_sql.field.name.' . $view . '.' . $field . '.old', null)) === null) { $oldName = $field; } // now set the update SQL $key_ = "ALTERTABLE`#__" . $component . "_" . $view . "`CHANGE`" . $oldName . "``" . $field . "`"; $value_ = "ALTER TABLE `#__" . $component . "_" . $view . "` CHANGE `" . $oldName . "` `" . $field . "` " . $data['type'] . $length . " " . $default . ";"; CFactory::_('Compiler.Builder.Update.Mysql')->set($key_, $value_); } // be sure to track the last name used :) $last_name = $field; } // check if default field was overwritten if (!CFactory::_('Compiler.Builder.Field.Names')->isString($view . '.params')) { $db_ .= PHP_EOL . Indent::_(1) . "`params` text NULL,"; } // check if default field was overwritten if (!CFactory::_('Compiler.Builder.Field.Names')->isString($view . '.published')) { $db_ .= PHP_EOL . Indent::_(1) . "`published` TINYINT(3) NOT NULL DEFAULT 1,"; } // check if default field was overwritten if (!CFactory::_('Compiler.Builder.Field.Names')->isString($view . '.created_by')) { $db_ .= PHP_EOL . Indent::_(1) . "`created_by` INT(10) unsigned NOT NULL DEFAULT 0,"; } // check if default field was overwritten if (!CFactory::_('Compiler.Builder.Field.Names')->isString($view . '.modified_by')) { $db_ .= PHP_EOL . Indent::_(1) . "`modified_by` INT(10) unsigned NOT NULL DEFAULT 0,"; } // check if default field was overwritten if (!CFactory::_('Compiler.Builder.Field.Names')->isString($view . '.created')) { $db_ .= PHP_EOL . Indent::_(1) . "`created` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',"; } // check if default field was overwritten if (!CFactory::_('Compiler.Builder.Field.Names')->isString($view . '.modified')) { $db_ .= PHP_EOL . Indent::_(1) . "`modified` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',"; } // check if default field was overwritten if (!CFactory::_('Compiler.Builder.Field.Names')->isString($view . '.checked_out')) { $db_ .= PHP_EOL . Indent::_(1) . "`checked_out` int(11) unsigned NOT NULL DEFAULT 0,"; } // check if default field was overwritten if (!CFactory::_('Compiler.Builder.Field.Names')->isString($view . '.checked_out_time')) { $db_ .= PHP_EOL . Indent::_(1) . "`checked_out_time` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',"; } // check if default field was overwritten if (!CFactory::_('Compiler.Builder.Field.Names')->isString($view . '.version')) { $db_ .= PHP_EOL . Indent::_(1) . "`version` INT(10) unsigned NOT NULL DEFAULT 1,"; } // check if default field was overwritten if (!CFactory::_('Compiler.Builder.Field.Names')->isString($view . '.hits')) { $db_ .= PHP_EOL . Indent::_(1) . "`hits` INT(10) unsigned NOT NULL DEFAULT 0,"; } // check if view has access if (CFactory::_('Compiler.Builder.Access.Switch')->exists($view) && !CFactory::_('Compiler.Builder.Field.Names')->isString($view . '.access')) { $db_ .= PHP_EOL . Indent::_(1) . "`access` INT(10) unsigned NOT NULL DEFAULT 0,"; } // check if default field was overwritten if (!CFactory::_('Compiler.Builder.Field.Names')->isString($view . '.ordering')) { $db_ .= PHP_EOL . Indent::_(1) . "`ordering` INT(11) NOT NULL DEFAULT 0,"; } // check if metadata is added to this view if (CFactory::_('Compiler.Builder.Meta.Data')->isString($view)) { // check if default field was overwritten if (!CFactory::_('Compiler.Builder.Field.Names')->isString($view . '.metakey')) { $db_ .= PHP_EOL . Indent::_(1) . "`metakey` TEXT NOT NULL,"; } // check if default field was overwritten if (!CFactory::_('Compiler.Builder.Field.Names')->isString($view . '.metadesc')) { $db_ .= PHP_EOL . Indent::_(1) . "`metadesc` TEXT NOT NULL,"; } // check if default field was overwritten if (!CFactory::_('Compiler.Builder.Field.Names')->isString($view . '.metadata')) { $db_ .= PHP_EOL . Indent::_(1) . "`metadata` TEXT NOT NULL,"; } } // TODO (we may want this to be dynamicly set) $db_ .= PHP_EOL . Indent::_(1) . "PRIMARY KEY (`id`)"; // check if a key was set for any of the default fields then we should not set it again $check_keys_set = []; if (CFactory::_('Compiler.Builder.Database.Unique.Keys')->exists($view)) { foreach (CFactory::_('Compiler.Builder.Database.Unique.Keys')->get($view) as $nr => $key) { $db_ .= "," . PHP_EOL . Indent::_(1) . "UNIQUE KEY `idx_" . $key . "` (`" . $key . "`)"; $check_keys_set[$key] = $key; } } if (CFactory::_('Compiler.Builder.Database.Keys')->exists($view)) { foreach (CFactory::_('Compiler.Builder.Database.Keys')->get($view) as $nr => $key) { $db_ .= "," . PHP_EOL . Indent::_(1) . "KEY `idx_" . $key . "` (`" . $key . "`)"; $check_keys_set[$key] = $key; } } // check if view has access if (!isset($check_keys_set['access']) && CFactory::_('Compiler.Builder.Access.Switch')->exists($view)) { $db_ .= "," . PHP_EOL . Indent::_(1) . "KEY `idx_access` (`access`)"; } // check if default field was overwritten if (!isset($check_keys_set['checked_out'])) { $db_ .= "," . PHP_EOL . Indent::_(1) . "KEY `idx_checkout` (`checked_out`)"; } // check if default field was overwritten if (!isset($check_keys_set['created_by'])) { $db_ .= "," . PHP_EOL . Indent::_(1) . "KEY `idx_createdby` (`created_by`)"; } // check if default field was overwritten if (!isset($check_keys_set['modified_by'])) { $db_ .= "," . PHP_EOL . Indent::_(1) . "KEY `idx_modifiedby` (`modified_by`)"; } // check if default field was overwritten if (!isset($check_keys_set['published'])) { $db_ .= "," . PHP_EOL . Indent::_(1) . "KEY `idx_state` (`published`)"; } // easy bucket $easy = []; // get the mysql table settings foreach ( CFactory::_('Config')->mysql_table_keys as $_mysqlTableKey => $_mysqlTableVal ) { if (($easy[$_mysqlTableKey] = CFactory::_('Compiler.Builder.Mysql.Table.Setting')-> get($view . '.' . $_mysqlTableKey)) === null) { $easy[$_mysqlTableKey] = CFactory::_('Config')->mysql_table_keys[$_mysqlTableKey]['default']; } } // add a little fix for the row_format if (StringHelper::check($easy['row_format'])) { $easy['row_format'] = ' ROW_FORMAT=' . $easy['row_format']; } // now build db string $db_ .= PHP_EOL . ") ENGINE=" . $easy['engine'] . " AUTO_INCREMENT=0 DEFAULT CHARSET=" . $easy['charset'] . " DEFAULT COLLATE=" . $easy['collate'] . $easy['row_format'] . ";"; // check if this is a new table that should be added via update SQL if (CFactory::_('Registry')-> get('builder.add_sql.adminview.' . $view, null)) { // build the update array $key_ = "CREATETABLEIFNOTEXISTS`#__" . $component . "_" . $view . "`"; CFactory::_('Compiler.Builder.Update.Mysql')->set($key_, $db_); } // check if the table row_format has changed if (StringHelper::check($easy['row_format']) && CFactory::_('Registry')-> get('builder.update_sql.table_row_format.' . $view, null)) { // build the update array $key_ = "ALTERTABLE`#__" . $component . "_" . $view . "`" . trim((string) $easy['row_format']); $value_ = "ALTER TABLE `#__" . $component . "_" . $view . "`" . $easy['row_format'] . ";"; CFactory::_('Compiler.Builder.Update.Mysql')->set($key_, $value_); } // check if the table engine has changed if (CFactory::_('Registry')-> get('builder.update_sql.table_engine.' . $view, null)) { // build the update array $key_ = "ALTERTABLE`#__" . $component . "_" . $view . "`ENGINE=" . $easy['engine']; $value_ = "ALTER TABLE `#__" . $component . "_" . $view . "` ENGINE = " . $easy['engine'] . ";"; CFactory::_('Compiler.Builder.Update.Mysql')->set($key_, $value_); } // check if the table charset OR collation has changed (must be updated together) if (CFactory::_('Registry')-> get('builder.update_sql.table_charset.' . $view, null) || CFactory::_('Registry')-> get('builder.update_sql.table_collate.' . $view, null)) { // build the update array $key_ = "ALTERTABLE`#__" . $component . "_" . $view . "CONVERTTOCHARACTERSET" . $easy['charset'] . "COLLATE" . $easy['collate']; $value_ = "ALTER TABLE `#__" . $component . "_" . $view . "` CONVERT TO CHARACTER SET " . $easy['charset'] . " COLLATE " . $easy['collate'] . ";"; CFactory::_('Compiler.Builder.Update.Mysql')->set($key_, $value_); } // add to main DB string $db .= $db_ . PHP_EOL . PHP_EOL; } // add custom sql dump to the file if (isset(CFactory::_('Customcode.Dispenser')->hub['sql']) && ArrayHelper::check( CFactory::_('Customcode.Dispenser')->hub['sql'] )) { foreach ( CFactory::_('Customcode.Dispenser')->hub['sql'] as $for => $customSql ) { $placeholders = array(Placefix::_('component') => $component, Placefix::_('view') => $for); $db .= CFactory::_('Placeholder')->update( $customSql, $placeholders ) . PHP_EOL . PHP_EOL; } unset(CFactory::_('Customcode.Dispenser')->hub['sql']); } // WHY DO WE NEED AN ASSET TABLE FIX? // https://www.mysqltutorial.org/mysql-varchar/ // https://stackoverflow.com/a/15227917/1429677 // https://forums.mysql.com/read.php?24,105964,105964 // https://github.com/vdm-io/Joomla-Component-Builder/issues/616#issuecomment-741502980 // 30 actions each +-20 characters with 8 groups // that makes 4800 characters and the current Joomla // column size is varchar(5120) // just a little event tracking in classes // count actions = setAccessSections // around line206 (infusion call) // around line26454 (interpretation function) // first fix = setInstall // around line1600 (infusion call) // around line10063 (interpretation function) // second fix = setUninstallScript // around line2161 (infusion call) // around line8030 (interpretation function) // check if this component needs larger rules // also check if the developer will allow this // the access actions length must be checked before this // only add this option if set to SQL fix if (CFactory::_('Config')->add_assets_table_fix == 1) { // 400 actions worse case is larger than 65535 characters if (CFactory::_('Utilities.Counter')->accessSize > 400) { $db .= PHP_EOL; $db .= PHP_EOL . '--'; $db .= PHP_EOL . '--' . Line::_( __LINE__,__CLASS__ ) . ' Always insure this column rules is large enough for all the access control values.'; $db .= PHP_EOL . '--'; $db .= PHP_EOL . "ALTER TABLE `#__assets` CHANGE `rules` `rules` MEDIUMTEXT NOT NULL COMMENT 'JSON encoded access control. Enlarged to MEDIUMTEXT by JCB';"; } // smaller then 400 makes TEXT large enough elseif (CFactory::_('Config')->add_assets_table_fix == 1) { $db .= PHP_EOL; $db .= PHP_EOL . '--'; $db .= PHP_EOL . '--' . Line::_( __LINE__,__CLASS__ ) . ' Always insure this column rules is large enough for all the access control values.'; $db .= PHP_EOL . '--'; $db .= PHP_EOL . "ALTER TABLE `#__assets` CHANGE `rules` `rules` TEXT NOT NULL COMMENT 'JSON encoded access control. Enlarged to TEXT by JCB';"; } } // check if this component needs larger names // also check if the developer will allow this // the config length must be checked before this // only add this option if set to SQL fix if (CFactory::_('Config')->add_assets_table_fix && CFactory::_('Config')->add_assets_table_name_fix) { $db .= PHP_EOL; $db .= PHP_EOL . '--'; $db .= PHP_EOL . '--' . Line::_( __LINE__,__CLASS__ ) . ' Always insure this column name is large enough for long component and view names.'; $db .= PHP_EOL . '--'; $db .= PHP_EOL . "ALTER TABLE `#__assets` CHANGE `name` `name` VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'The unique name for the asset.';"; } return $db; } return ''; } ``` What is nice is it all happens right here ;) I am also sure we can easy move this to a class now... and in the process refine it some more, all its dependencies are already in their own classes: These are the dependencies: ``` -------------------------- $this->...[ -------------------------- $this->uninstallBuilder[ <--- [except this one will also need to move to a builder class] -------------------------- CFactory::_(...)-> -------------------------- CFactory::_('Compiler.Builder.Database.Tables')-> CFactory::_('Config')-> CFactory::_('Registry')-> CFactory::_('Compiler.Builder.Update.Mysql')-> CFactory::_('Compiler.Builder.Field.Names')-> CFactory::_('Compiler.Builder.Access.Switch')-> CFactory::_('Compiler.Builder.Meta.Data')-> CFactory::_('Compiler.Builder.Database.Unique.Keys')-> CFactory::_('Compiler.Builder.Database.Keys')-> CFactory::_('Compiler.Builder.Mysql.Table.Setting')-> CFactory::_('Customcode.Dispenser')-> CFactory::_('Placeholder')-> CFactory::_('Utilities.Counter')-> -------------------------- Helpers::_() -------------------------- Indent::_( StringHelper::check( ArrayHelper::check( Placefix::_( Line::_( ``` Here you can see each class (boring for the most part): - `CFactory::_('Compiler.Builder.Database.Tables')->` [class](https://git.vdm.dev/joomla-beta/com-componentbuilder/src/branch/master/libraries/jcb_powers/VDM.Joomla/src/Componentbuilder/Compiler/Builder/DatabaseTables.php) - `CFactory::_('Config')->` [class](https://git.vdm.dev/joomla-beta/com-componentbuilder/src/branch/master/libraries/jcb_powers/VDM.Joomla/src/Componentbuilder/Compiler/Config.php) - `CFactory::_('Registry')->` [class](https://git.vdm.dev/joomla-beta/com-componentbuilder/src/branch/master/libraries/jcb_powers/VDM.Joomla/src/Componentbuilder/Compiler/Registry.php) - `CFactory::_('Compiler.Builder.Update.Mysql')->` [class](https://git.vdm.dev/joomla-beta/com-componentbuilder/src/branch/master/libraries/jcb_powers/VDM.Joomla/src/Componentbuilder/Compiler/Builder/UpdateMysql.php) - `CFactory::_('Compiler.Builder.Field.Names')->` [class](https://git.vdm.dev/joomla-beta/com-componentbuilder/src/branch/master/libraries/jcb_powers/VDM.Joomla/src/Componentbuilder/Compiler/Builder/FieldNames.php) - `CFactory::_('Compiler.Builder.Access.Switch')->` [class](https://git.vdm.dev/joomla-beta/com-componentbuilder/src/branch/master/libraries/jcb_powers/VDM.Joomla/src/Componentbuilder/Compiler/Builder/AccessSwitch.php) - `CFactory::_('Compiler.Builder.Meta.Data')->` [class](https://git.vdm.dev/joomla-beta/com-componentbuilder/src/branch/master/libraries/jcb_powers/VDM.Joomla/src/Componentbuilder/Compiler/Builder/MetaData.php) - `CFactory::_('Compiler.Builder.Database.Unique.Keys')->` [class](https://git.vdm.dev/joomla-beta/com-componentbuilder/src/branch/master/libraries/jcb_powers/VDM.Joomla/src/Componentbuilder/Compiler/Builder/DatabaseUniqueKeys.php) - `CFactory::_('Compiler.Builder.Database.Keys')->` [class](https://git.vdm.dev/joomla-beta/com-componentbuilder/src/branch/master/libraries/jcb_powers/VDM.Joomla/src/Componentbuilder/Compiler/Builder/DatabaseKeys.php) - `CFactory::_('Compiler.Builder.Mysql.Table.Setting')->` [class](https://git.vdm.dev/joomla-beta/com-componentbuilder/src/branch/master/libraries/jcb_powers/VDM.Joomla/src/Componentbuilder/Compiler/Builder/MysqlTableSetting.php) - `CFactory::_('Customcode.Dispenser')->` [class](https://git.vdm.dev/joomla-beta/com-componentbuilder/src/branch/master/libraries/jcb_powers/VDM.Joomla/src/Componentbuilder/Compiler/Customcode/Dispenser.php) - `CFactory::_('Placeholder')->` [class](https://git.vdm.dev/joomla-beta/com-componentbuilder/src/branch/master/libraries/jcb_powers/VDM.Joomla/src/Componentbuilder/Compiler/Placeholder.php) - `CFactory::_('Utilities.Counter')->` [class](https://git.vdm.dev/joomla-beta/com-componentbuilder/src/branch/master/libraries/jcb_powers/VDM.Joomla/src/Componentbuilder/Compiler/Utilities/Counter.php) They are already all added to the container via their respective service providers. I will do this since only I currently have the mapped JCB instance, but on the side of improving the function so that it will meet the new objectives, **take a look at the code and tell me where you would change it, and how**... if you have time 👍
Llewellyn changed title from MySQL/MariaDB Database Compatibility to [Beta] MySQL/MariaDB Database Compatibility 2024-01-27 07:32:05 +00:00
Llewellyn added the
Beta
label 2024-01-27 07:32:08 +00:00
Owner

@stutteringp0et we have a code sprint planned tomorrow evening at 9pm (GMT +2) where we would like to go over this part of the compiler and speak about all the angles we need to consider.

Would be great if you can be there!

@stutteringp0et we have a [code sprint planned tomorrow evening at 9pm (GMT +2)](https://t.me/Joomla_component_builder?livestream) where we would like to go over this part of the compiler and speak about all the angles we need to consider. Would be great if you can be there!
Sign in to join this conversation.
No Milestone
No project
No Assignees
2 Participants
Notifications
Due Date
The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: joomla/Component-Builder#1038
No description provided.