Set parameters from UI to SQL editor, React/JS

user_7021567 0
Viewed 593

the idea of this stuff is that user can add parameters to the SQL editor from two inputs, one for the parameter itself and the other one for its value

enter image description here enter image description here

and if user writes in SQL editor, automatically adds inputs that are the parameter and the value.

From SQL editor to inputs it works fine, cause I'm sending a regex. The trouble is located where I try to send the inputs to SQL Editor. I think is more like a logic problem, but I still don't find the solution. The point is that by adding one parameter from the input, it adds more than required, even though I always clean it before adding, but apparently that doesn't affect.

enter image description here

This is the code

import React, { useEffect, useState} from 'react';
import SQLContainerInput from '../Components/SQLContainerInput';

........

function arrayParamsExec(stringSql) {
 const paramsQueryText = [...stringSql.matchAll(/{{(\w+)}}/ig)];
 const newArray = paramsQueryText.map(item => item[1]);
 return newArray;
}

const initalStateCurrentChartInfo = {
  SQLQuery: '',
  dataType: 'TABLE',
  columns: [],
};

const CustomSQLEditor = ({
 fromQuery, // del Redux
}) = {
 const [currentChartInfo, setCurrentChartInfo] = useState(
  initalStateCurrentChartInfo,
 );
 const [params, setParams] = useState([]);
 const [textSql, setTextSql] = useState('');

 useEffect(() => {
   ....
   let sqlDefaultString = '';
   sqlDefaultString = fromQuery.internal_name
    ? `SELECT * FROM \`${fromQuery.internal_name}__${fromQuery.items[0]}\` LIMIT 20`
    : '';
   setCurrentChartInfo({
    ...currentChartInfo,
    SQLQuery: `${sqlQuery}`,
   });
 },[fromQuery]);

 // ------------------params---------------------
 const addProperty = () => {
  setParams([
    ...params,
   { name: '', value: '' },
  ]);
 };

 const updateProperty = (event, index, key) => {
  const newProperties = [...params];
  newProperties[index][key] = event?.target?.value;

  // agregar parámetros al editor SQL
  let sqlParams = textSql;
  if (key === 'name') {
    params.forEach(p => {
      if (p.name && /^\w+$/i.test(p.name)) {
        sqlParams += `{{${p.name}}}`;
      }
    });

    setTextSql('');
    setTextSql(`${sqlParams}`);
  }

  setParams(newProperties);
 };

 const deleteProperty = index => {
  const newProperties = [...params];
  newProperties.splice(index, 1);
  const newTextSQL = replaceAll(textSql, `{{${params[index]?.name}}}`, '');
  setTextSql(newTextSQL);
  setParams(newProperties);
 };

 // ------------------end params---------------------

 const changeTextEditor = (valueEditor) => {
    const namesParams = arrayParamsExec(valueEditor);
    const newProperties = namesParams.map((pName) => {
      const valueNew = params.find(p => p.name === pName);
      return {name: pName, value: valueNew?.value || ''};
    });
    setParams(newProperties);
    setTextSql(valueEditor);
 }

 return (
  <>
   <SQLContainerInput
     button={{
      onClick: handleSubmit,
     }}
     input={{
      value: `${textSql}\n`,
      onChange: changeTextEditor,
     }}
   />
   <DymanicKeyValueInputInput
     properties={params}
     updateProperty={updateProperty}
     deleteProperty={deleteProperty}
     addProperty={addProperty}
   />
  </>
 );
}

Then, I tried as a solution set another value which is textSql, that takes care of placing the concatenated string, and the string coming from redux is fromQuery. The redux string is set in the sqlParams variable, when is added concatenates with the params and then, I clean textSql

 ......
 const updateProperty = (event, index, key) => {
  const newProperties = [...params];
  newProperties[index][key] = event?.target?.value;

  // agregar parámetros al editor SQL
  let sqlParams = currentChartInfo.SQLQuery;
  if (key === 'name') {
    params.forEach(p => {
      if (p.name && /^\w+$/i.test(p.name)) {
        sqlParams += `{{${p.name}}}`;
      }
    });

    setTextSql('');
    setTextSql(`${sqlParams}`);
  }

  setParams(newProperties);
 };
 ......

The trouble in there is that if I directly write from SQL editor, it resets the whole string, I mean, everything that has been written but there it works when I put the params and it's not repeated. I don't find a way to do that, so I'm sorry for the ignorance if I'm doing something wrong.

For example, when I write a large SQL text.

enter image description here

enter image description here

When a parameter is added from the input, it resets.

enter image description here enter image description here

Video with the error demo: https://www.youtube.com/watch?v=rQBPOPyeXlI Repo's url: https://gitlab.com/albert925/parametrosui-a-editor-sql

monaco-editor reactjs javascript
1 Answers

try this:

import { useState, useEffect } from 'react';
import DymanicKeyValueInputInput from './components/DymanicKeyValueInputInput';
import SQLContainerInput from './components/SQLContainerInput';
import { fromQuery } from './bd/data';
import { replaceAll, arrayParamsExec } from './utils/strings';
import { Content, ContentAddButton } from './stylesApp';

const initalStateCurrentChartInfo = {
  SQLQuery: '',
  columns: [],
};

function App() {
  const [currentChartInfo, setCurrentChartInfo] = useState(
    initalStateCurrentChartInfo,
  );
  const [params, setParams] = useState([]);
  const [textSql, setTextSql] = useState('');
  const [endSql, setendSql] = useState('');

  useEffect(() => {
    let sqlQuery = '';
    sqlQuery = fromQuery.internal_name
     ? `SELECT * FROM \`${fromQuery.internal_name}__${fromQuery.items[0]}\` LIMIT 20`
     : '';
    setCurrentChartInfo({
     ...currentChartInfo,
     SQLQuery: `${sqlQuery}`,
    });
    setTextSql(sqlQuery);
  },[fromQuery]);

  useEffect(()=>{
    const endSql = replaceAll(textSql, '\n', '');
    setendSql('')
    setendSql(endSql);
  },[textSql]);

  const cleanSQLString = (string) => {
    //corto la cadena en {{
    //reemplazo {{ por "" en cada item
    //devuelvo el array con los reemplazos
    return string.split("{{").map(item=>item.replace("}}",""));
  }

  // ------------------params---------------------
  const addProperty = () => {
    setParams([
      ...params,
      { name: '', value: '' },
    ]);
  };

  const updateProperty = (event, index, key) => {
    const newProperties = [...params];
    newProperties[index][key] = event?.target?.value;

    let currentSQL = cleanSQLString(endSql);
    // clean the string so that then add parameters to sql
    if (key === 'name' && /^\w+$/i.test(event?.target?.value)) {
      let sqlParams;

      if(currentSQL.length > 1){
        sqlParams = `${currentSQL[0]}`;
      }else{
        sqlParams = `${endSql}`;
      }
      params.forEach(p => {
        if (p.name && /^\w+$/i.test(p.name)) {
          sqlParams += `{{${p.name}}}`;
        }
      });

      setTextSql('');
      setTextSql(`${sqlParams}`);

      /*if(currentSQL.length > 1){
        sqlParams = `${currentSQL[0]}`;
      }else{
        sqlParams = `${endSql}`;
      }
      console.log(sqlParams)
      // get parameter positions by regular expression
      const searchParamRegedix = arrayParamsExec(endSql);
      console.log(searchParamRegedix, 'searchParamRegedix')
      if (searchParamRegedix.paramsQueryText.length > 0) {
        console.log(111)
        // get the position
        const searchPosition = searchParamRegedix.paramsQueryText[index]?.index;
        // remove the old word in braces
        const deleteOldParam = replaceAll(sqlParams, `${searchParamRegedix.list[index]}`, '');
        // the remaining string is removed from the obtained position
        const deleteFirtsLetter = deleteOldParam.substr(searchPosition + 2);
        // the string is removed from the beginning to the position obtained
        const deleteRemaining = deleteOldParam.substr(0, searchPosition + 2)
        // the string of the beginning and end is combined with its parameter
        const contantString = deleteRemaining + event?.target?.value + deleteFirtsLetter;
        // the entire string is overwritten in the state
        setTextSql(contantString);
      }
      else{      
        params.forEach(p => {
          if (p.name && /^\w+$/i.test(p.name)) {
            sqlParams += `{{${p.name}}}`;
          }
        });
  
        setTextSql('');
        setTextSql(`${sqlParams}`);
      }*/

    }

    setParams(newProperties);
  };

  const deleteProperty = index => {
    const newProperties = [...params];
    newProperties.splice(index, 1);
    const newTextSQL = replaceAll(textSql, `{{${params[index]?.name}}}`, '');
    setTextSql(newTextSQL);
    setParams(newProperties);
  };

  // ------------------end params---------------------

  const changeTextEditor = (valueEditor) => {
    const namesParams = arrayParamsExec(valueEditor);
      // keep the value with the parameter with respect to adding a new parameter in the sql editor,
      // but if the parameter word is changed, the value is deleted
      const newProperties = namesParams.list.map((pName) => {
        const valueNew = params.find(p => p.name === pName);
        return {name: pName, value: valueNew?.value || ''};
      });
      setParams(newProperties);
      setTextSql(valueEditor);
  }

  return (
    <Content>
      <div>
        <SQLContainerInput
          input={{
            value: `${endSql}\n`,
            onChange: changeTextEditor,
          }}
        />
      </div>
      <div>
        <h2>PARAMS</h2>
        <ContentAddButton>
          <DymanicKeyValueInputInput
            properties={params}
            updateProperty={updateProperty}
            deleteProperty={deleteProperty}
            addProperty={addProperty}
            id="sqlapiparams"
            className="isMultipleInpustSelects"
            tilesColumns={["", ""]}
            inputsPlaceholder={["My_Parameter", "Type params value"]}
            isIconTransfer
            isIconError
          />
        </ContentAddButton>
      </div>
    </Content>
  );
}

export default App;
Admin1
Related