File, string compare SQLiteFTS

  1. 7 days ago

    Aurelian N

    May 20 Xojo Pro

    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. 5 days ago

    Maurizio R

    May 21 Testers, Xojo Pro

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

    @Aurelian N 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 .

    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.

    @Aurelian N 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

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

    Best regards.

or Sign Up to reply!