joomla/Component-Builder
joomla/Component-Builder
39
23
Fork 11
Code Issues 80 Pull Requests Projects 4 Wiki Activity

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

New Issue
Closed
opened 2024-01-23 19:56:57 +00:00 by jjs · 11 comments
jjs commented 2024-01-23 19:56:57 +00:00
Copy Link

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
Copy Link

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.
👍 1
Llewellyn commented 2024-01-24 04:14:53 +00:00
Owner
Copy Link

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
Copy Link

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
Copy Link

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
oxido-jcb-dynamic-build.jpeg oxido-jcb-dynamic-build-fieldd-type.jpeg
👍 1
Llewellyn commented 2024-02-04 17:46:00 +00:00
Owner
Copy Link

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
Copy Link

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
Copy Link

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
Copy Link

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
Copy Link

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
Copy Link

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
Copy Link

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

Okay this is fixed, and the basic option to set the name and lable via the comment area is also implemented.
🎉 2
Robot referenced this issue from a commit 2024-05-10 16:07:45 +00:00
Release of v3.2.1
Robot referenced this issue from a commit 2024-05-10 16:12:54 +00:00
Release of v4.0.0
Robot referenced this issue from a commit 2024-05-10 16:15:25 +00:00
Release of v5.0.0
Robot referenced this issue from a commit 2024-05-10 16:34:27 +00:00
Release of v3.2.1
Robot referenced this issue from a commit 2024-05-10 16:35:57 +00:00
Release of v4.0.0
Robot referenced this issue from a commit 2024-05-10 16:39:50 +00:00
Release of v5.0.0
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels   
Beta

Issues related to the a beta release

  
Bug

   
Documentation

   
enhancement

   
help wanted

   
invalid

   
joomla

Joomla related changes

  
question

   
URGENT

   
wontfix
No Label
Beta
Bug
Documentation
enhancement
help wanted
invalid
joomla
question
URGENT
wontfix
Milestone
Clear milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
Clear assignees
No Assignees
4 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.
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?