[BETA] Dynamic Build gives error No "CREATE TABLE.." were found, please check your sql. #1032

Open
opened 2024-01-23 19:56:57 +00:00 by jjs · 5 comments

Steps to reproduce the issue

Go to Dynamic Build(Beta) tab.

Add a piece of create table from an mysql dump like:
CREATE TABLE #__followlist(idint(6) NOT NULL AUTO_INCREMENT PRIMARY KEY,useridint(6) NOT NULL,followidint(6) NOT NULL,asset_idINT(10) UNSIGNED NOT NULL DEFAULT '0',stateTINYINT(1) NULL DEFAULT 1,ordering INT(11) NULL DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Press Save

Expected result

back-end views created

Actual result

No "CREATE TABLE.." were found, please check your sql.

System information (as much as possible)

  • OS Name & Version: Linux Ubuntu
  • MySql Version: 8.0.28
  • Apache Version: 2.4.56
  • PHP Version: 8.0.28
  • Joomla Version: 3.10.10
  • JCB Version: 3.1.34
  • Browser: Firefox

Additional comments

### Steps to reproduce the issue Go to Dynamic Build(Beta) tab. Add a piece of create table from an mysql dump like: `CREATE TABLE `#__followlist` ( `id` int(6) NOT NULL AUTO_INCREMENT PRIMARY KEY, `userid` int(6) NOT NULL, `followid` int(6) NOT NULL, `asset_id` INT(10) UNSIGNED NOT NULL DEFAULT '0', `state` TINYINT(1) NULL DEFAULT 1, `ordering` INT(11) NULL DEFAULT 0 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;` Press Save ### Expected result back-end views created ### Actual result No "CREATE TABLE.." were found, please check your sql. ### System information (as much as possible) - OS Name & Version: Linux Ubuntu - MySql Version: 8.0.28 - Apache Version: 2.4.56 - PHP Version: 8.0.28 - Joomla Version: 3.10.10 - JCB Version: 3.1.34 - Browser: Firefox ### Additional comments

This problem was reported already here #990, and I am happy to announce that I managed to make it work, I will soon post the changes I made.

This problem was reported already here #990, and I am happy to announce that I managed to make it work, I will soon post the changes I made.
Owner

The Build(Beta) tab project was abandoned for some time now. We want to improve this area, to also take a path to all the field xml files.... but moving JCB to build towards Joomla 4 and 5 became the over bearing focus for a long time now.

This means we will soon start looking at all the peripheral features again... so if you have debugged this and fixed something, please share it here.

So that you know the code that currently is being used to build is found here it also needs refactoring into the powers area of JCB, and will be containerized.

The `Build(Beta) tab` project was abandoned for some time now. We want to improve this area, to also take a path to all the field xml files.... but moving JCB to build towards Joomla 4 and 5 became the over bearing focus for a long time now. This means we will soon start looking at all the peripheral features again... so if you have debugged this and fixed something, please share it here. So that you know the code that currently is being used to build is found [here](https://git.vdm.dev/joomla/Component-Builder/src/branch/staging/admin/helpers/extrusion) it also needs refactoring into the powers area of JCB, and will be containerized.
Tom van der Laan changed title from Dynamic Build gives error No "CREATE TABLE.." were found, please check your sql. to [BETA] Dynamic Build gives error No "CREATE TABLE.." were found, please check your sql. 2024-01-24 15:25:41 +00:00
Tom van der Laan added the
Beta
label 2024-01-24 15:25:44 +00:00
Author

OK great.
Could also be a great addition for site-end views I guess.

OK great. Could also be a great addition for site-end views I guess.

Hi, I managed to advance a little with this section, now the Admin view and the fields are created.
At least 2 rules must be respected.

  • The table name and column names must be enclosed between ` or '
  • Each column name must be on a new row.
    I've used this sql code:
CREATE TABLE '#__mycomponent_location' (
  'field_state' VARCHAR(255),
  'field_city' VARCHAR(255),
) ENGINE=InnoDB;

1. In \libraries\jcb_powers\VDM.Joomla\src\Utilities\GetHelper.php I've changed the between function

FROM

public static function between(string $content, string $start, string $end, string $default = ''): string
	{
		$array = explode($start, $content);
		if (isset($array[1]) && strpos($array[1], $end) !== false)
		{
			$array = explode($end, $array[1]);
			// return string found between
			return $array[0];
		}
		return $default;
	}

TO

public static function between(string $content, string $start, string $end, string $default = ''): string
    {
        $startPos = strpos($content, $start);
      
        if ($startPos !== false) {
            $startPos += strlen($start); // We move the position to the end of the starting string
            $endPos = strpos($content, $end, $startPos); // We find the end position of the desired string
            
            if ($endPos !== false) {
                // We extract the content between the two strings
                $result = substr($content, $startPos, $endPos - $startPos);
                return $result;
            }
        }

        return $default;
    }

2. In \administrator\components\com_componentbuilder\helpers\extrusion\b_builder.php

  • line 199

FROM

if ($fieldType = CFactory::_('Field.Type.Name')->get($field['fieldType']))

TO

if ($fieldType = CFactory::_('Field.Type.Name')->get($field))
  • and in line 289
    we get this error now: Unknown column 'text' in 'where clause' because $fieldId = 'text'.
    Because the function getFieldTypeProperties tries to bring us one of the field types existing in JCB
if ($fieldOptions = ComponentbuilderHelper::getFieldTypeProperties($fieldId, 'id', $settings))

So if I change the above code to this, it will work and create my admin view as well as the columns.

// 24 is the ID of the text field defined in JCB > Fieldtypes
if ($fieldOptions = ComponentbuilderHelper::getFieldTypeProperties(24, 'id', $settings))

But because I added manually the ID (24), all the fields are gonna be of type text.

A small problems that remain after creating the admin-view and the columns/fields

Go to JCB > Fields and "1 (dynamic build)" will appear in the list of fields, you will see from the very beginning that the Type is not set.
Select one of the dynamically created fields and manually set: Type. (all fixed :D )

Probably something should be done so that in the database, in JCB where the field types are defined, the field type is saved on the "dataType1" column, so that the getFieldTypeProperties function query can identify the desired field.

Hi, I managed to advance a little with this section, now the Admin view and the fields are created. At least 2 rules must be respected. - The **table name** and **column names** must be enclosed between **`** or **'** - Each column name must be on a new row. I've used this sql code: ``` CREATE TABLE '#__mycomponent_location' ( 'field_state' VARCHAR(255), 'field_city' VARCHAR(255), ) ENGINE=InnoDB; ``` **1. In** \libraries\jcb_powers\VDM.Joomla\src\Utilities\GetHelper.php I've changed the **between** function **FROM** ``` public static function between(string $content, string $start, string $end, string $default = ''): string { $array = explode($start, $content); if (isset($array[1]) && strpos($array[1], $end) !== false) { $array = explode($end, $array[1]); // return string found between return $array[0]; } return $default; } ``` **TO** ``` public static function between(string $content, string $start, string $end, string $default = ''): string { $startPos = strpos($content, $start); if ($startPos !== false) { $startPos += strlen($start); // We move the position to the end of the starting string $endPos = strpos($content, $end, $startPos); // We find the end position of the desired string if ($endPos !== false) { // We extract the content between the two strings $result = substr($content, $startPos, $endPos - $startPos); return $result; } } return $default; } ``` **2. In** \administrator\components\com_componentbuilder\helpers\extrusion\b_builder.php - line 199 **FROM** ``` if ($fieldType = CFactory::_('Field.Type.Name')->get($field['fieldType'])) ``` **TO** ``` if ($fieldType = CFactory::_('Field.Type.Name')->get($field)) ``` - and in line 289 we get this error now: **Unknown column 'text' in 'where clause'** because $fieldId = 'text'. Because the function getFieldTypeProperties tries to bring us one of the field types existing in JCB ``` if ($fieldOptions = ComponentbuilderHelper::getFieldTypeProperties($fieldId, 'id', $settings)) ``` So if I change the above code to this, it will work and create my admin view as well as the columns. ``` // 24 is the ID of the text field defined in JCB > Fieldtypes if ($fieldOptions = ComponentbuilderHelper::getFieldTypeProperties(24, 'id', $settings)) ``` But because I added manually the ID (24), all the fields are gonna be of type text. ### A small problems that remain after creating the admin-view and the columns/fields Go to JCB > Fields and "1 (dynamic build)" will appear in the list of fields, you will see from the very beginning that the Type is not set. Select one of the dynamically created fields and manually set: Type. (all fixed :D ) Probably something should be done so that in the database, in JCB where the field types are defined, the field type is saved on the "dataType1" column, so that the getFieldTypeProperties function query can identify the desired field.
Owner

I'm inclined to introduce a new between function(class) rather than altering the existing one, given its extensive usage and the critical bugs encountered when modifications were previously attempted during component compilation. The inherent complexities in string manipulation, through functions like explode, strpos, and substr, present varied edge-case challenges.

For substantial components, such as JCB itself, direct modifications to the between method warrant cautious consideration. A pragmatic approach involves integrating the component into a Git repository before implementing any changes. This process should extend to several components: compile each into the repository, introduce the modifications, then recompile. Should there be no discrepancies in the Git repositories post-change, we can proceed. This methodology reflects my standard practice for significant adjustments in JCB: secure the baseline with a Git commit, implement the changes, and meticulously review the outcomes. If the alterations deviate from expectations, I halt the process, refining the approach until achieving the desired results, aiming for clarity and precision in these endeavors.

Regarding the Dynamic Build(Beta) feature, its segregation from the compiler's shared methods and classes is not only feasible but advisable, enhancing its utility and application.

Then for the second point, I would have to run some tests actually :) (been a while since I used this area).

But you must understand that the whole idea of this feature is to try and find the correct field types, and not just build generic text fields.... or at least that is what I would like it to do.

The essence of this feature aims at identifying accurate field types rather than settling for generic text fields. This ambition guides my proposal to utilize xml files for better mapping of views and fields. Recognizing the current limitations, it might be prudent to address this issue through a dedicated code sprint. This would allow us to thoroughly assess its present capabilities and envision a pathway toward enhanced automation.

By a code sprint, I envision a synchronized collaboration, possibly facilitated through audio or video calls (via Telegram or Google Meet), allowing for real-time dialogue about the feature and its existing codebase. Our objective would be to outline a clear, achievable plan, setting the stage for concerted coding efforts toward our agreed-upon goals.

I'm inclined to introduce a new `between` function(class) rather than altering the existing one, given its extensive usage and the critical bugs encountered when modifications were previously attempted during component compilation. The inherent complexities in string manipulation, through functions like `explode`, `strpos`, and `substr`, present varied edge-case challenges. For substantial components, such as JCB itself, direct modifications to the `between` method warrant cautious consideration. A pragmatic approach involves integrating the component into a Git repository before implementing any changes. This process should extend to several components: compile each into the repository, introduce the modifications, then recompile. Should there be no discrepancies in the Git repositories post-change, we can proceed. This methodology reflects my standard practice for significant adjustments in JCB: secure the baseline with a Git commit, implement the changes, and meticulously review the outcomes. If the alterations deviate from expectations, I halt the process, refining the approach until achieving the desired results, aiming for clarity and precision in these endeavors. Regarding the `Dynamic Build(Beta)` feature, its segregation from the compiler's shared methods and classes is not only feasible but advisable, enhancing its utility and application. Then for the second point, I would have to run some tests actually :) (been a while since I used this area). But you must understand that the whole idea of this feature is to try and find the correct field types, and not just build generic text fields.... or at least that is what I would like it to do. The essence of this feature aims at identifying accurate field types rather than settling for generic text fields. This ambition guides my proposal to utilize `xml files` for better mapping of views and fields. Recognizing the current limitations, it might be prudent to address this issue through a dedicated code sprint. This would allow us to thoroughly assess its present capabilities and envision a pathway toward enhanced automation. By a code sprint, I envision a synchronized collaboration, possibly facilitated through audio or video calls (via Telegram or Google Meet), allowing for real-time dialogue about the feature and its existing codebase. Our objective would be to outline a clear, achievable plan, setting the stage for concerted coding efforts toward our agreed-upon goals.
Sign in to join this conversation.
No Milestone
No project
No Assignees
3 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#1032
No description provided.