B4J Question Refresh data in SQlite table

[Sergey_New]

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]

...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]

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]

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

 

[Sergey_New]

add indices on all the foreign_id columns

Made. No changes.

 

[Erel]

Can you upload the updated project?

 

[Sergey_New]

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

 

[Erel]

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]

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]

Created indexes in code:

    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]

You are still missing an index on the names table.

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

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]

You are still missing an index on the names table.

I don't understand what you are talking about.

 

[aeric]

Instead of creating the table like yours:

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

I would create it like this:

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

 

[Sergey_New]

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]

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]

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

 

[netsistemas]

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).

--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]

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]

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]

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