File, string compare SQLiteFTS

General
1

Greetings,

What would be the best way to compare some strings as in the end I get all of them as Base64 encoded and then I decode them and write them on the Database .

The problem, data is coming from a couchDB export and the way the export is done, same data comes duplicate between 3 - 20 times and that is a waste of resources and creates confusion as well . on this part I was thinking to check the strings if they are identical then just keep the first one found and dismiss the rest .

The second problem is for the actual note.

Example , I have the note “test a” written on 2018, on 2019 they have another note which is carried from previous so “test a” and they add c “test ac” now , I need to search if the new note has test a and c was added then delete the old note and add the new one , as the purpose of the record is overriding as per request , but the couchdb or at least the way we got the export , it carries on the old data, duplicates and adds the new content as new record. Audit purposes would be great here but we don’t need it in this case. I was looking on FTS5 option on SQLite but any other ideas are more than welcomed.

Thanks in advance.

2

The possible solutions are related to the database structure you are creating.
I suppose you are creating a new SQLite database.

If you insert these strings in a column with the UNIQUE attribute, the database itself can avoid duplicates.
To avoid errors when inserting an already inserted string you can use the upsert-clause with DO NOTHING.

[quote=489377:@Aurelian Negrea]The second problem is for the actual note.

Example , I have the note “test a” written on 2018, on 2019 they have another note which is carried from previous so “test a” and they add c “test ac” now , I need to search if the new note has test a and c was added then delete the old note and add the new one , as the purpose of the record is overriding as per request[/quote]

Again, some details on the database structure you are creating would help.

Best regards.