Q: multiple update #70

antarey · 2023-03-07T11:37:12Z

Good afternoon.
I have a table with keys and parameter values.

CREATE TABLE variable_list (variable_name TEXT UNIQUE ON CONFLICT ROLLBACK,
variable_text TEXT,extention_type INTEGER,   id INTEGER PRIMARY KEY AUTOINCREMENT);

There is a data table

CREATE TABLE extention_files_detail (source_path TEXT,source_text BLOB, id INTEGER PRIMARY KEY AUTOINCREMENT);
INSERT INTO extention_files_detail (source_path,source_text)
VALUES ({SYSTEMNAME_ALL_LOWER}.xml',
'<?xml version=''1.0'' encoding=''utf-8''?>
<extension type="component" method="upgrade">
    <name>COM_{SYSTEMNAME_ALL_UPPER}</name>
    <creationDate>{DATE}</creationDate>
    <author>{AUTHOR}</author>
...
');

I need to update the extention_files_detal table using the key - value from the variable_list table
My code.

update extention_files_detal set 
    source_path = regexp_replace(source_path,vars.variable_name, vars.variable_text),
    source_text = regexp_replace(source_text,vars.variable_name, vars.variable_text)
from (SELECT variable_name,variable_text FROM variable_list) as vars
where regexp_like(source_path, vars.variable_name) = 1 or regexp_like(source_text, vars.variable_name) = 1;

Variables are {SYSTEMNAME_ALL_LOWER},{DATE} ... Value - any text
If the extention_files_detal record contains only one key-value pair, then everything works. If there are several key-value pairs, no update occurs. How to update for multiple values?

The text was updated successfully, but these errors were encountered:

nalgeon · 2023-03-08T11:19:47Z

Could you please provide the exact data for all the tables?

antarey · 2023-03-08T13:24:32Z

Could you please provide the exact data for all the tables?

How to provide data? Sql script or completely database?

nalgeon · 2023-03-08T18:52:37Z

An SQL script with enough data to reproduce the problem (the less data the better).

antarey · 2023-03-09T06:35:49Z

An SQL script with enough data to reproduce the problem (the less data the better).
-- table: extention_files_detal

CREATE TABLE IF NOT EXISTS extention_files_detal (
    source_path    TEXT,
    source_text    BLOB,
    extention_type INTEGER,
    condition      TEXT,
    id             INTEGER PRIMARY KEY ON CONFLICT ROLLBACK AUTOINCREMENT
);

INSERT INTO extention_files_detal (
                                      source_path,
                                      source_text,
                                      extention_type,
                                      condition,
                                      id
                                  )
                                  VALUES (
                                      '{SYSTEMNAME_ALL_LOWER}.xml',
                                      '<?xml version=''1.0'' encoding=''utf-8''?>
<extension type="component" method="upgrade">
    <name>COM_{SYSTEMNAME_ALL_UPPER}</name>
    <creationDate>{DATE}</creationDate>
    <author>{AUTHOR}</author>
    <authorEmail>{AUTHOR_EMAIL}</authorEmail>
    <authorUrl>{AUTHOR_URL}</authorUrl>
    <copyright>{COPYRIGHT}</copyright>
    <license>{LICENSE}</license>
    <version>{VERSION}</version>
    <description>COM_{SYSTEMNAME_ALL_UPPER}_XML_DESCRIPTION</description>

</extension>
',
                                      1,
                                      'main',
                                      1
                                  );

INSERT INTO extention_files_detal (
                                      source_path,
                                      source_text,
                                      extention_type,
                                      condition,
                                      id
                                  )
                                  VALUES (
                                      'src\form\{SYSTEMNAME_ALL_LOWER}.xml',
                                      '<?xml version=''1.0'' encoding=''utf-8''?>
<extension type="component" method="upgrade">
    <name>COM_{SYSTEMNAME_ALL_UPPER}</name>
    <creationDate>{DATE}</creationDate>
    <author>{AUTHOR}</author>
    <authorEmail>{AUTHOR_EMAIL}</authorEmail>
    <authorUrl>{AUTHOR_URL}</authorUrl>
    <copyright>{COPYRIGHT}</copyright>
    <license>{LICENSE}</license>
    <version>{VERSION}</version>
    <description>COM_{SYSTEMNAME_ALL_UPPER}_XML_DESCRIPTION</description>

</extension>
',
                                      1,
                                      'main',
                                      2
                                  );

-- table: variable_list

CREATE TABLE IF NOT EXISTS variable_list (
    variable_name  TEXT    UNIQUE ON CONFLICT ROLLBACK,
    variable_text  TEXT,
    extention_type INTEGER NOT NULL
                           DEFAULT (1),
    id             INTEGER PRIMARY KEY AUTOINCREMENT
);

INSERT INTO variable_list (
                              variable_name,
                              variable_text,
                              extention_type,
                              id
                          )
                          VALUES (
                              '{AUTHOR_EMAIL}',
                              'admin@gmail.com',
                              1,
                              34
                          );

INSERT INTO variable_list (
                              variable_name,
                              variable_text,
                              extention_type,
                              id
                          )
                          VALUES (
                              '{AUTHOR_URL}',
                              'https://testsite.com',
                              1,
                              35
                          );

INSERT INTO variable_list (
                              variable_name,
                              variable_text,
                              extention_type,
                              id
                          )
                          VALUES (
                              '{AUTHOR}',
                              'antarey',
                              1,
                              36
                          );

INSERT INTO variable_list (
                              variable_name,
                              variable_text,
                              extention_type,
                              id
                          )
                          VALUES (
                              '{COPYRIGHT}',
                              'Copyright (C) 2023. All rights reserved.',
                              1,
                              37
                          );

INSERT INTO variable_list (
                              variable_name,
                              variable_text,
                              extention_type,
                              id
                          )
                          VALUES (
                              '{DATE}',
                              '07.03.2023',
                              1,
                              38
                          );

INSERT INTO variable_list (
                              variable_name,
                              variable_text,
                              extention_type,
                              id
                          )
                          VALUES (
                              '{LICENSE}',
                              'GNU General Public License version 2 or later; see LICENSE.txt',
                              1,
                              49
                          );

INSERT INTO variable_list (
                              variable_name,
                              variable_text,
                              extention_type,
                              id
                          )
                          VALUES (
                              '{SYSTEMNAME_ALL_LOWER}',
                              'test_comp',
                              1,
                              55
                          );

INSERT INTO variable_list (
                              variable_name,
                              variable_text,
                              extention_type,
                              id
                          )
                          VALUES (
                              '{SYSTEMNAME_ALL_UPPER}',
                              'TEST_COMP',
                              1,
                              56
                          );

INSERT INTO variable_list (
                              variable_name,
                              variable_text,
                              extention_type,
                              id
                          )
                          VALUES (
                              '{SYSTEMNAME_FIRST_UPPER}',
                              'Test_comp',
                              1,
                              57
                          );

INSERT INTO variable_list (
                              variable_name,
                              variable_text,
                              extention_type,
                              id
                          )
                          VALUES (
                              '{SYSTEMNAME_XML_DESCRIPTION}',
                              'test_comp',
                              1,
                              58
                          );

INSERT INTO variable_list (
                              variable_name,
                              variable_text,
                              extention_type,
                              id
                          )
                          VALUES (
                              '{VENDOR_FIRST_UPPER}',
                              'Antarey',
                              1,
                              59
                          );

INSERT INTO variable_list (
                              variable_name,
                              variable_text,
                              extention_type,
                              id
                          )
                          VALUES (
                              '{VERSION}',
                              '1.0.0',
                              1,
                              60
                          );

antarey · 2023-03-09T11:00:56Z

I tried many different variations of the code.
Code

WITH RECURSIVE vl_list(var_source_text,var_name,var_text) AS (
select efd.source_text,vl.variable_name,vl.variable_text
from variable_list vl
join extention_files_detal efd on regexp_like(efd.source_text,vl.variable_name) = 1 
)
select regexp_replace(var_source_text,var_name,var_text),var_name,var_text from vl_list

displays the corresponding number of lines and replaces the corresponding variable in each line.
Code

WITH RECURSIVE vl_list(var_source_text,var_name,var_text) AS (
select efd.source_text,vl.variable_name,vl.variable_text
from variable_list vl
join extention_files_detal efd on regexp_like(efd.source_text,vl.variable_name) = 1 
)
update extention_files_detal set source_text = regexp_replace(var_source_text,var_name,var_text) from vl_list

for some reason it does not replace all variables

nalgeon · 2023-03-09T11:55:14Z

The problem is not related to the regexp extension. UPDATE FROM simply cannot perform multiple updates on a single row in the target table. Quoting SQLite docs:

If the join between the target table and the FROM clause results in multiple output rows for the same target table row, then only one of those output rows is used for updating the target table. The output row selected is arbitrary and might change from one release of SQLite to the next, or from one run to the next.

antarey · 2023-03-09T14:01:56Z

Why doesn't recursion work then?

nalgeon · 2023-03-09T14:36:18Z

Because at the end of the day you are doing the same update from.

nalgeon · 2023-03-09T14:48:03Z

And you don't need the regexp extension for this task at all. A simple replace() will do.

What you can do, however, is use the define extension to dynamically evaluate a prepared set of updates.

For example. Prepare a separate update query for each variable:

alter table variable_list add column var_stmt as (
  "update extention_files_detal set source_text = " ||
  "replace(source_text, '" || variable_name || "', '" || variable_text || "')"  
);

Execute the queries:

select eval(var_stmt) from variable_list;

Results:

sqlite> select source_text from extention_files_detal;
<?xml version='1.0' encoding='utf-8'?>
<extension type="component" method="upgrade">
    <name>COM_TEST_COMP</name>
    <creationDate>07.03.2023</creationDate>
    <author>antarey</author>
    <authorEmail>admin@gmail.com</authorEmail>
    <authorUrl>https://testsite.com</authorUrl>
    <copyright>Copyright (C) 2023. All rights reserved.</copyright>
    <license>GNU General Public License version 2 or later; see LICENSE.txt</license>
    <version>1.0.0</version>
    <description>COM_TEST_COMP_XML_DESCRIPTION</description>

</extension>

<?xml version='1.0' encoding='utf-8'?>
<extension type="component" method="upgrade">
    <name>COM_TEST_COMP</name>
    <creationDate>07.03.2023</creationDate>
    <author>antarey</author>
    <authorEmail>admin@gmail.com</authorEmail>
    <authorUrl>https://testsite.com</authorUrl>
    <copyright>Copyright (C) 2023. All rights reserved.</copyright>
    <license>GNU General Public License version 2 or later; see LICENSE.txt</license>
    <version>1.0.0</version>
    <description>COM_TEST_COMP_XML_DESCRIPTION</description>

</extension>

antarey · 2023-03-09T17:16:44Z

I cannot do without your library.
I will use regular expressions.
I will check tomorrow.

Thank you.

antarey · 2023-03-10T09:56:54Z

How the code will be correct
alter table variable_list add column var_stmt
using regexp_substr and updating the source_path field?
Do I need to create a new field?

alter table variable_list add column var_stmt as (
  "update extention_files_detal set source_text = regexp_substr(source_text, '" || variable_name || "', '" || variable_text || "' ), source_path = regexp_substr(source_path, '" || variable_name || "', '" || variable_text || "');"  
);

throws an error on select eval(var_stmt) from variable_list;
And such a question - select eval(var_stmt) from variable_list; - should be called every time the variable_list table is updated?

nalgeon · 2023-03-10T16:36:30Z

The queries I've provided are correct. What is the specific error you are getting?

antarey · 2023-03-10T18:26:32Z

Your query is correct. I converted it into two fields and regexp_substr.

alter table variable_list add column var_stmt as (
  "update extention_files_detal set source_text = regexp_substr(source_text, '" || variable_name || "', '" || variable_text || "' ), source_path = regexp_substr(source_path, '" || variable_name || "', '" || variable_text || "');"  
);

What did I write wrong?

nalgeon · 2023-03-10T19:50:45Z

The function to use is replace, not regexp_substr.

antarey · 2023-03-10T20:38:07Z

Is it possible to use regexp_substr?

nalgeon · 2023-03-11T02:46:03Z

regexp_substr does not replace anything. replace does.

antarey · 2023-03-13T06:24:50Z

Sorry, I wrote the wrong function. But - simple replace will not perform the following replacement regexp_replace(source_text,'(+PHP_CODE_1{)([\s\S]*?)(}+)','%2')

antarey · 2023-03-13T08:00:29Z

Last question.
select eval(var_stmt) from variable_list - should be called every time the variable_list and extention_files_detal tables is updated?
Thank you for your help.

nalgeon · 2023-03-13T08:40:15Z

Yes. Although for variable_list you can run it on newly inserted records by specifying the id.

antarey · 2023-03-13T10:12:39Z

Thank you

nalgeon · 2023-03-13T12:35:22Z

Sure! I'm glad we figured it all out.

nalgeon closed this as completed Mar 13, 2023

LifeCANvs mentioned this issue Jun 10, 2024

upstream issues LifeCANvs/LCvs-Lib-SQLite-ext#1

Open

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Q: multiple update #70

Q: multiple update #70

antarey commented Mar 7, 2023 •

edited

Loading

nalgeon commented Mar 8, 2023

antarey commented Mar 8, 2023

nalgeon commented Mar 8, 2023

antarey commented Mar 9, 2023

antarey commented Mar 9, 2023 •

edited by nalgeon

Loading

nalgeon commented Mar 9, 2023

antarey commented Mar 9, 2023 •

edited by nalgeon

Loading

nalgeon commented Mar 9, 2023

nalgeon commented Mar 9, 2023

antarey commented Mar 9, 2023

antarey commented Mar 10, 2023 •

edited by nalgeon

Loading

nalgeon commented Mar 10, 2023

antarey commented Mar 10, 2023

nalgeon commented Mar 10, 2023

antarey commented Mar 10, 2023

nalgeon commented Mar 11, 2023

antarey commented Mar 13, 2023 •

edited by nalgeon

Loading

antarey commented Mar 13, 2023

nalgeon commented Mar 13, 2023

antarey commented Mar 13, 2023

nalgeon commented Mar 13, 2023

Q: multiple update #70

Q: multiple update #70

Comments

antarey commented Mar 7, 2023 • edited Loading

nalgeon commented Mar 8, 2023

antarey commented Mar 8, 2023

nalgeon commented Mar 8, 2023

antarey commented Mar 9, 2023

antarey commented Mar 9, 2023 • edited by nalgeon Loading

nalgeon commented Mar 9, 2023

antarey commented Mar 9, 2023 • edited by nalgeon Loading

nalgeon commented Mar 9, 2023

nalgeon commented Mar 9, 2023

antarey commented Mar 9, 2023

antarey commented Mar 10, 2023 • edited by nalgeon Loading

nalgeon commented Mar 10, 2023

antarey commented Mar 10, 2023

nalgeon commented Mar 10, 2023

antarey commented Mar 10, 2023

nalgeon commented Mar 11, 2023

antarey commented Mar 13, 2023 • edited by nalgeon Loading

antarey commented Mar 13, 2023

nalgeon commented Mar 13, 2023

antarey commented Mar 13, 2023

nalgeon commented Mar 13, 2023

antarey commented Mar 7, 2023 •

edited

Loading

antarey commented Mar 9, 2023 •

edited by nalgeon

Loading

antarey commented Mar 9, 2023 •

edited by nalgeon

Loading

antarey commented Mar 10, 2023 •

edited by nalgeon

Loading

antarey commented Mar 13, 2023 •

edited by nalgeon

Loading