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

jjs commented

2024-01-23 19:56:57 +00:00

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

oxido commented

2024-01-23 21:11:06 +00:00

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.

👍 1

Llewellyn commented

2024-01-24 04:14:53 +00:00

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.

👍 1

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

jjs commented

2024-01-25 20:19:11 +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.

oxido commented

2024-02-03 02:50:32 +00:00

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.

oxido-jcb-dynamic-build.jpeg

102 KiB

oxido-jcb-dynamic-build-fieldd-type.jpeg

36 KiB

👍 1

oxido referenced this issue

2024-02-04 00:00:47 +00:00

[BETA] Error: No "CREATE TABLE.." were found, please check your sql. #990

Llewellyn commented

2024-02-04 17:46:00 +00:00

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.

👍 1

oxido commented

2024-03-20 22:33:30 +00:00

I'm still thinking of this problem, and I think that's the only option, to set the fields to be created as a text field by default.
I can't see any solution for this, because you can't set the type of field in the sql query, unless we decide to use an improved/customized sql query, something like this [dropdown], [text], [etc]
And based on that we set the field type.

CREATE TABLE '#__mycomponent_location' (
'field_state' VARCHAR(255) [dropdown],
'field_city' VARCHAR(255) [text],
) ENGINE=InnoDB;

Because I remember that in the yii framework it still works like that when you try to create the CRUD based on the database.
I personally do not see any problem in changing the type of the field after it has been created 👯

I'm still thinking of this problem, and I think that's the only option, to set the fields to be created as a text field by default. I can't see any solution for this, because you can't set the type of field in the sql query, unless we decide to use an improved/customized sql query, something like this [dropdown], [text], [etc] And based on that we set the field type. CREATE TABLE '#__mycomponent_location' ( 'field_state' VARCHAR(255) [dropdown], 'field_city' VARCHAR(255) [text], ) ENGINE=InnoDB; Because I remember that in the yii framework it still works like that when you try to create the CRUD based on the database. I personally do not see any problem in changing the type of the field after it has been created 👯

❤️ 1

vistamedia commented

2024-04-02 14:42:11 +00:00

Member

Hi everyone,

The idea of oxido is excellent to my point view because it allows you to be specific or not. However we should use a standard SQL syntax and the comment is perfect for that. We should also use the exact name of fieldtype.

CREATE TABLE '#__mycomponent_location' (
'field_state' VARCHAR(255) COMMENT 'List',
'field_city' VARCHAR(255) COMMENT 'Text',
) ENGINE=InnoDB;

Hi everyone, The idea of oxido is excellent to my point view because it allows you to be specific or not. However we should use a standard SQL syntax and the comment is perfect for that. We should also use the exact name of fieldtype. CREATE TABLE '#__mycomponent_location' ( 'field_state' VARCHAR(255) COMMENT 'List', 'field_city' VARCHAR(255) COMMENT 'Text', ) ENGINE=InnoDB;

👍 1

Llewellyn commented

2024-04-06 14:36:42 +00:00

Owner

I am nearly finished with the update of this area, and I need to just note that it was still working when I used valid SQL, and going forward it will be even more necessary for valid MySQL or it will not work. I am also starting to add the option to set field related properties, I have not yet completed this... but I am going to update the comment in that area to note the idea we will try to implement.

I am nearly finished with the update of this area, and I need to just note that it was still working when I used valid `SQL`, and going forward it will be even more necessary for valid `MySQL` or it will not work. I am also starting to add the option to set field related properties, I have not yet completed this... but I am going to update the comment in that area to note the idea we will try to implement.

🚀 1

Robot referenced this issue from a commit

2024-04-06 17:49:22 +00:00

Release of v3.2.1-alpha1

Robot referenced this issue from a commit

2024-04-06 21:29:52 +00:00

Release of v4.0.0-alpha8

Robot referenced this issue from a commit

2024-04-06 21:42:13 +00:00

Release of v5.0.0-alpha8

jjs commented

2024-04-07 14:42:47 +00:00

Author

Great!
I tend to believe using a SQL file generated by phpmyadmin can be considered valid SQL.
At least that's what I used in my try.

Great! I tend to believe using a SQL file generated by phpmyadmin can be considered valid SQL. At least that's what I used in my try.

Llewellyn commented

2024-04-07 17:55:11 +00:00

Owner

I tend to believe using a SQL file generated by phpmyadmin can be considered valid SQL

Okay lets say this, I use to use string manipulation to try and extract the columns and the there properties and there I only worked with backticks. So that was me... and so I basically build it to only work with what I thought is valid secure SQL. My Bad!

But now things have changed, I am now giving your sql to the SQL itself and so it will be going via the Joomla API, and no longer via Llewellyn lane.

This means your SQL if valid should work as expected, and yet in the old approach, it might have been valid, and still fail.... because it was not valid according to me (chuckle, chuckle) So test the new release and let me know.

> I tend to believe using a SQL file generated by phpmyadmin can be considered valid SQL Okay lets say this, I use to use string manipulation to try and extract the columns and the there properties and there I only worked with `backticks`. So that was **me**... and so I basically build it to only work with _what I thought is valid secure SQL_. My Bad! But now things have changed, I am now giving your sql to the SQL itself and so it will be [going via the Joomla API](https://git.vdm.dev/joomla/Component-Builder/src/commit/94bc92357efa714eab56877d51a3e1d1a38395b0/libraries/vendor_jcb/VDM.Joomla/src/Componentbuilder/Extrusion/Helper/Mapping.php#L389), and no longer via [Llewellyn lane](https://git.vdm.dev/joomla/Component-Builder/src/commit/ddb07c79d95676cbf094d7c1f9db8fbceeb6e096/libraries/jcb_powers/VDM.Joomla/src/Componentbuilder/Extrusion/Helper/Mapping.php#L276). This means your SQL if valid should work as expected, and yet in the old approach, it might have been valid, and still fail.... because it was not valid according to me (chuckle, chuckle) So test the [new release](https://git.vdm.dev/joomla/pkg-component-builder/archive/v3.2.1-alpha1.zip) and let me know.

👍 1

Llewellyn commented

2024-04-09 13:15:16 +00:00

Owner

Okay this is fixed, and the basic option to set the name and lable via the comment area is also implemented.

🎉 2