SQLite: Avoid to store records based on some fields

  1. 2 months ago

    Emile S

    Jun 14 Europe (France, Strasbourg)

    Hi all,

    I have a blank in my brain.

    I want to avoid to store many Records based on two unique Fields.

    For example, I have two fields: one holds a date (SQLDate) and the second the Hour. Iwant to be able to store only one record for these (other fields may be different, but in that case, the latest vvalue will be saved).

    I remember that I can read unique values from the data base (say one entry for each country to get the total number of "know" countries (a country that have an entry), but I totally forgot how to keep only one Record based on some fields.

  2. Dave S

    Jun 14 San Diego, California USA

    show the code you are using now, and I will see if I can "fix" it.... the solution depends on the methods you are using now

  3. Jay M

    Jun 14 Pre-Release Testers, Xojo Pro NC, USA

    Use a Replace statement (or "Insert or Replace") with a unique key defined on the two columns.

  4. Derk J

    Jun 14 Pre-Release Testers, Xojo Pro

    Just “REPLACE INTO” will create or overwrite

  5. Derk J

    Jun 14 Pre-Release Testers, Xojo Pro

    Setting a a column sqldate in the table as UNIQUE and hour as UNIQUE will require them to be unique if you want to store them, othwise will trow an error

  6. Emile S

    Jun 14 Europe (France, Strasbourg)

    Derk:
    REPLACE INTO: I do not have a UNIQUE ID at this time (unless I search the Data Base before to write). [1]

    Set a Field as UNIQUE: I will check this.

    [1] I may have to scan the DB before letting the user type data that will be saved later, so I may store a unique ID in RowTag then.

    Used code:
    My actual code comes from the LR and do nothing in regards to my question.

    I used a DataBaseRecor to store the Record on screen, ignoring I was doing something wrong (for my project/application). That code simply add a Record in the Data Base file (with an automatic Unique ID). I realized my error at read time when I get the first Record that contains the first saved data. I fired my application to read what I stored in the Data Base and found many “different” entries for the same date / hour (what I do not wanted). *

    Comment:
    * This let me ask myself what I have done in this regards for a software that is in production for those last 6 years ! ;)
    (I suppose that nobody tried to insert a person name twice by pure hazard: I get the running db from time to time and get an eye on it regularly and never saw an ‘illegal’ duplicate).

  7. Emile S

    Jun 14 Europe (France, Strasbourg)

    New used code to create the TABLE:

    sql_cmd = "CREATE TABLE IF NOT EXISTS Agenda(A_Date TEXT UNIQUE, An_Hour TEXT UNIQUE, An_Entry TEXT NOT NULL, ID INTEGER NOT NULL, PRIMARY KEY(ID));"

    I get an error about UNIQUE (MsgBox) after the first record the first run, then, after trashing the .sqlite file, I do not get error(s)… if the text data are the same.

    If I modify the comments (in An_Entry), I get:

    DB Error: UNIQUE constraint failed: Agenda.An_Hour

    and this is bad.

  8. Jean-Yves P

    Jun 14 Pre-Release Testers, Xojo Pro Europe (France, Besançon)
    Edited 2 months ago

    anhour can't be unique: if you store an event today at 20h00 and tomorrow at 20h00 you get an error.
    adate can't be unique; if you store an event today at 20h and today at 21h you get an error...

    you must save the date and hour in the same field, a timestamp (in postgres) or an sqldatetime in xojo (stored as text in sqlite db)

  9. Derk J

    Jun 14 Pre-Release Testers, Xojo Pro
    Edited 2 months ago

    @Emile S New used code to create the TABLE:

    sql_cmd = "CREATE TABLE IF NOT EXISTS Agenda(A_Date TEXT UNIQUE, An_Hour TEXT UNIQUE, An_Entry TEXT NOT NULL, ID INTEGER NOT NULL, PRIMARY KEY(ID));"

    I get an error about UNIQUE (MsgBox) after the first record the first run, then, after trashing the .sqlite file, I do not get error(s)… if the text data are the same.

    If I modify the comments (in An_Entry), I get:

    DB Error: UNIQUE constraint failed: Agenda.An_Hour

    and this is bad.

    You should be able to ALTER TABLE and change the table.
    But say you use the above, that table Agenda should simply not allow you to have duplicate A_Date or An_Hour or both.
    If you only want to NOT allow Both to be the same:
    http://www.sqlitetutorial.net/sqlite-unique-constraint/

    Use:

    sql_cmd = "CREATE TABLE IF NOT EXISTS Agenda(A_Date TEXT, An_Hour TEXT, An_Entry TEXT NOT NULL, ID INTEGER NOT NULL, PRIMARY KEY(ID), UNIQUE(A_Date,An_Hour));"

    Here you have a UNIQUE combination instead of 1 of the 2.

  10. Derk J

    Jun 14 Pre-Release Testers, Xojo Pro

    @Jean-YvesPochez anhour can't be unique: if you store an event today at 20h00 and tomorrow at 20h00 you get an error.
    adate can't be unique; if you store an event today at 20h and today at 21h you get an error...

    you must save the date and hour in the same field, a timestamp (in postgres) or an sqldatetime in xojo (stored as text in sqlite db)

    That's in no way required. Only making it more difficult.

  11. Jean-Yves P

    Jun 14 Pre-Release Testers, Xojo Pro Europe (France, Besançon)

    saving date and time in different fields will make it more difficult to find something between 2 datetime.

  12. Derk J

    Jun 14 Pre-Release Testers, Xojo Pro

    @Jean-YvesPochez saving date and time in different fields will make it more difficult to find something between 2 datetime.

    Well that should be not that hard:

    
    WHERE A_Date = [thedate] AND A_Hour BETWEEN 10 AND 20;
    
  13. Jean-Yves P

    Jun 14 Pre-Release Testers, Xojo Pro Europe (France, Besançon)
    Edited 2 months ago

    and from 27th feb 2000 10h00 to 1st march 2000 17h30 ?

  14. Derk J

    Jun 14 Pre-Release Testers, Xojo Pro
    Edited 2 months ago
    WHERE A_Date BETWEEN ‘2000-02-27’ AND ‘2000-03-01’ AND A_Hour BETWEEN 10 AND 17,5;
    

    Something like that..
    Not sure if @Emile S is looking for full hours or minutes too.

  15. Antonio R

    Jun 15 Pre-Release Testers, Xojo Pro Europe (Italy)

    SQLite has a lot of useful date and time function. It's better to use only a field.
    In any case an unique constrain on 2 or more field is a different than a unique constrain on each field.
    You can create indexes on any amount of fields (primary, unique or normal)

    Be careful when you use insert or replace: replace will replace your entire record so you have to fill every field. A lot of developers are surprised when the lost some data using replace.

  16. Jean-Yves P

    Jun 15 Pre-Release Testers, Xojo Pro Europe (France, Besançon)

    @Derk J WHERE A_Date BETWEEN ‘2000-02-27’ AND ‘2000-03-01’ AND A_Hour BETWEEN 10 AND 17,5;

    Something like that..
    Not sure if @Emile S is looking for full hours or minutes too.

    won't give you an event at 22h on 28th
    if you separate fields, you can search on a same day easily, but not if it overlaps over days
    then searching for overlapping days, and hours is very tricky.
    with one timestamp field, it's straightforward.
    here also you have to take into account the 29th feb, one year out of 4 eventually ...

  17. Emile S

    Jun 15 Europe (France, Strasbourg)

    Thank you all for your advices.

    @Derk J You should be able to ALTER TABLE and change the table.

    It is far more easy:

    at an early stage of development (and sometimes until late in the beta stage), I just trash the .sqlite file and modify y Create_SQLite Method. *

    Here, I am at the beginning, doing test, seeking surprise (and that was one), and so on.

    Searching a meeting by date / hour was super easy (as Derk wrote) and reliable.
    I do not tried to seek all Mettings for the same hour or same day (what for if I provide a view by day ?)

    (Stupid ?) Question:
    What is the use to search meetings between two dates ?

    * I implement the import/export methods somewhere between alpha and beta for this, so I will not have to risk my (sometimes) precious data nor use ALTER TABLE.
    This is also a must if REPLACE is mis-used ;) an error is done so fast.
    I usually add Import/Export when I have enough to type a record of-r two manually… ;)

    I just added:

    UNIQUE(A_Date, An_Hour)

    to my TABLE and it worked fine, anytime I try to add a brand new text with the same Date and Time (say 2019-06-15/ 10:00) it was silently rejected (excepted the first one, of course).

    Thanks all.

    @Emile S what I have done in this regards

    When I select a day and an hour (in another software), at OK click time, I checked if that “meeting” ** is free; else I issue a warning.

    ** In that specific case, a room # is involved, so the test was for the day, the hour and the room #.
    How fast I am able to forget amaze me; good for books /magazine / video / movie; bad for developments…

or Sign Up to reply!