B4J Question Refresh data in SQlite table

Sergey_New · Oct 18, 2021

The SQlite database has a table persons:

id	key
@1@	1
@3@	2
@4@	3

The id column is filled from an external file, the key column is auto-incremented.
After filling the table, you need to order the data in the id column according to the data in the key column:

id	key
@1@	1
@2@	2
@3@	3

There can be several thousand records in a table.
What is the most efficient way to do this?

cklester · Oct 19, 2021

Sergey_New said:
...you need to update all identifiers in the main table, of course, this will update all subordinate tables.

Yeah, I'm confused by that setup and description. ?

I can't see why you would ever need to update identifiers, especially unique ones that determine relationships!

Sergey_New · Oct 19, 2021

cklester said:
I can't see why you would ever need to update identifiers

Just believe it is needed
In my B4A application, the database structure (based on classes) works fine. Just wanted to repeat this using SQLite.
Perhaps a misunderstanding arises from the Google translator

Erel · Oct 20, 2021

First step is to add indices on all the foreign_id columns.

Sergey_New · Oct 20, 2021

Erel said:
add indices on all the foreign_id columns

Made. No changes.

Erel · Oct 20, 2021

Can you upload the updated project?

Sergey_New · Oct 20, 2021

Loaded an empty database as I work with tables in a third party program for SQLite.

Erel · Oct 20, 2021

Many indices are missing.
This leads to full tables scan, each update.

Each and every column that is defined as a foreign key should be indexed.

Sergey_New · Oct 20, 2021

Indexes have been added for all tables associated with the "persons" table. Since the identifier is changed only for this table, why do you need to do this for other tables?

Sergey_New · Oct 20, 2021

Created indexes in code:

B4X:

    db.ExecNonQuery("CREATE INDEX index1 ON persons_citat (foreign_id)")
    db.ExecNonQuery("CREATE INDEX index2 ON persons_events (foreign_id)")
    db.ExecNonQuery("CREATE INDEX index3 ON persons_famc (foreign_id)")
    db.ExecNonQuery("CREATE INDEX index4 ON persons_fams (foreign_id)")
    db.ExecNonQuery("CREATE INDEX index5 ON persons_media (foreign_id)")
    db.ExecNonQuery("CREATE INDEX index6 ON persons_notes (foreign_id)")

No result.

Erel · Oct 20, 2021

You are still missing an index on the names table.

All that I can further suggest is to make it asynchronously:

B4X:

db.AddNonQueryToBatch("UPDATE persons SET id='@' || key || '@'", Null)
    Dim SenderFilter As Object = db.ExecNonQueryBatch("SQL")
    Wait For (SenderFilter) SQL_NonQueryComplete (Success As Boolean)
    Log("NonQuery: " & Success)

This way your app will not freeze.

Sergey_New · Oct 20, 2021

Erel said:
You are still missing an index on the names table.

I don't understand what you are talking about.

aeric · Oct 20, 2021

Instead of creating the table like yours:

SQL:

CREATE TABLE "persons" (
  "id" STRING NOT NULL,
  "sex" STRING,
  "key" integer,
  PRIMARY KEY ("id")
)

I would create it like this:

SQL:

CREATE TABLE "persons" (
    "id"    INTEGER,
    "key"    TEXT NOT NULL UNIQUE,
    "sex"    TEXT,
    PRIMARY KEY("id" AUTOINCREMENT)
)

Sergey_New · Oct 20, 2021

aeric said:
I would create it like this:

It won't help.
I have another idea.
When working in an application, the value of the identifier itself is irrelevant and does not need to be changed. The correct sequence will only be needed when the database is saved back to a text ged-file. This is important for working with the ged-file visually. I will try to go this way

netsistemas · Oct 26, 2021

try to no use foreign key
Create a new field in first table, for get old key and new key... use left join for capture the new key
update all tables with update (left join with new table).

recreate foreign keys
its dongerous it the sistem crack between processs

Sergey_New · Oct 26, 2021

Google translation gives an incomprehensible solution to the issue
Could you make a small example based on two tables?

netsistemas · Oct 26, 2021

Spanish: Translate you.

La verdad es que no se si he entendido bien el problema.

Tienes varias tablas, y quieres modificar la CLAVE en todas.
En la tabla principal, es la clave primaria.
En el resto de tablas, es la clave foranea.

Da igual el motivo por lo que necesitas hacer esto, lo cual cuesta entender, pero tu mandas.

Debes
1º Crear un campo adicioinal en la tabla principal
2º Hacer un update de la clave principal para llevar ese valor, al nuevo campo creado en la tabla principal.
3º Desactivas las actualizaciones en cascada
4º Haces el update de la tabla principal
5º Haces el update del resto de las tablas.
6º Vuelve a crear o activar la integridad referencial.

Al final, lo que vas a hacer ,es lo que hace el sistema, pero lo haces tu a mano, posiblemente sea mas eficiente que el propio sqllite.

Doy por hecho que tienes INDICES ne todas las tablas, de las clave foraneas.

Script para SQL SERVER (En este código falta la integridad referencial. Desconozco como se desactiva o si es posible hacerlo en SQLITE, pero algo vi de pragma que quizás sea lo que puede hacer que lo desactive).

Script SQL SERVER:

--DROP TABLE T1
Create table T1 (KEYOLD NVARCHAR(10), VALUE NVARCHAR(10), PRIMARY KEY(KEYOLD) )
insert into t1(keyold, value) values ('*1*','1')
insert into t1(keyold, value) values ('*2*','2')
insert into t1(keyold, value) values ('*3*','3')

--DROP TABLE T2
CREATE table T2(IDREGISTRO INT, KEYOLDFK NVARCHAR(10), VALOR NVARCHAR(10), PRIMARY KEY (IDREGISTRO))
INSERT INTO T2(IDREGISTRO ,KEYOLDFK, VALOR) VALUES (1,'*1*','TXT')
INSERT INTO T2(IDREGISTRO ,KEYOLDFK, VALOR) VALUES (2,'*2*','TXT')
INSERT INTO T2(IDREGISTRO ,KEYOLDFK, VALOR) VALUES (3,'*3*','TXT')


ALTER TABLE T1 ADD NEWKEY NVARCHAR(10)
UPDATE T1 SET NEWKEY = KEYOLD
UPDATE T1 SET NEWKEY = '*NEW1*' WHERE KEYOLD = '*1*'
UPDATE T1 SET NEWKEY = '*NEW2*' WHERE KEYOLD = '*2*'
UPDATE T1 SET NEWKEY = '*NEW3*' WHERE KEYOLD = '*3*'

UPDATE T2
SET T2.KEYOLDFK = T1.NEWKEY
FROM T2 LEFT JOIN T1 ON T2.KEYOLDFK = T1.KEYOLD

SELECT * FROM T2

Sergey_New · Oct 26, 2021

My language is Russian.
You have correctly understood the essence of the problem. Indexes are available for all foreign keys.
I do not understand points 2º, 4º, 6º.
Above, I posted my project. With a small data file (Kennedy.ged) the application is fast, but with a large data file (Rurik.ged) it is very slow.

netsistemas · Oct 26, 2021

PUNTO 2:
Ya que vas a hacer un UPDATE de la clave primaria, esta la debes guardar en algún sitio, para usarla de referencia en el resto de tablas. Por eso digo que debes crear un nuevo campo, y guardar esa clave que vas a modificar. Puedes hacerlo de otras formas, como hacer una copia de esa tabla, para usarla luego para los UPDATES. Al final, deberas borrar la tabla (DROP TABLA)
Punto 4:
Cambias el valor de la clave primaria, por el nuevo que quieres asignar. El viejo valor que vas a modificar, lo guardaste en el punto 2.
Punto 6:
Como desactivaste las INTEGRIDAD REFERENCIAL, quizás necesites volver a activarlas. (Todo el tema de ON UPDATE CASCADE / DELETE)

Otra posibilidad, es que te olvides de las clave foráneas, y gestiones tu la integridad referencial manualmente. Es decir, si borran el registro de la tabla 1, deberás encargate tu de borrarlo, pero creo que esto no es importante para el problema que tu planteas.
(Ahora mismo, no puedo probar tu proyecto, solo puedo escribir)

Sergey_New · Oct 26, 2021

Sorry, but I don't understand how your solution differs from mine. Maybe later you can test this solution on my project.

B4J Question Refresh data in SQlite table

Well-Known Member

Well-Known Member

Well-Known Member

B4X founder

Well-Known Member

B4X founder

Well-Known Member

Attachments

B4X founder

Well-Known Member

Well-Known Member

B4X founder

Well-Known Member

Expert

Well-Known Member

Active Member

Well-Known Member

Active Member

Well-Known Member

Active Member

Well-Known Member

Similar Threads

Privacy & Transparency

Privacy & Transparency