SQLite ON Conflict action

[benners]

I am creating a database to use with an  installer (I'm rewriting to remove the reliance on ini files) created in AutoIt that allows for various programs to be installed unattendedly.

One of the options is an Install Order which determines the order of installation. I have set the "InstallOrder" column to contain unique values (integers) and I am tring to work out how to auto increment when conflicting values are inserted into the DB. This is not a primary key or auto incremented.

I have been playing around with the DB in SQLite Expert and using the code below, I can update the values as I want.

UPDATE 'Apps' SET InstallOrder = Installorder + 1 WHERE InstallOrder >= 1

The 1 will be replaced by the variable value entered for the InstallOrder. I just need to know if there is anywhere this code can be run from. Either adding code to the sql string or a clause in the DB. Still a beginner with SQLite so it may not be possible.

The autoit function is shaping up like this. The SQL string might be utter tosh from the ON CONFLICT part but prior to that it works.

Func _DB_InsertApp($s_Installer, $s_Switches, $i_Order, $s_Icon, $s_Name, $s_Version, $s_Description, $s_Users)
    Local $s_SQL = "INSERT INTO Apps VALUES (NULL," & _
            _SQLite_FastEscape($s_Installer) & "," & _
            _SQLite_FastEscape($s_Switches) & "," & _
            Number($i_Order) & "," & _
            _SQLite_FastEscape($s_Icon) & "," & _
            _SQLite_FastEscape($s_Name) & "," & _
            _SQLite_FastEscape($s_Version) & "," & _
            _SQLite_FastEscape($s_Description) & "," & _
            _SQLite_FastEscape($s_Users) & ") ON CONFLICT(INSTALLORDER) DO UPDATE Apps SET InstallOrder=InstallOrder+1 WHERE InstallOrder>=" & Number($i_Order) & ";"

    If _SQLite_Exec(-1, $s_SQL) = $SQLITE_OK Then Return

    Return SetError(@error, @extended, _SQLite_ErrMsg())
EndFunc   ;==>_DB_InsertApp

 

[jchd]

You can use an AFTER INSERT trigger to do that.

[Andreik]

You need something like this:

CREATE TRIGGER InstallOrder BEFORE INSERT ON Apps 
BEGIN
    UPDATE Apps 
    SET InstallOrder = InstallOrder + 1 
    WHERE InstallOrder >= NEW.InstallOrder 
        AND EXISTS(SELECT 1 FROM Apps WHERE InstallOrder = NEW.InstallOrder);
END

 

EDIT: probably it's a good idea to create a trigger for UPDATE also.

Edited December 22, 2019 by Andreik

[jchd]

I'd favor an after insert version:

CREATE TRIGGER InstallOrder AFTER INSERT ON Apps 
BEGIN
    UPDATE Apps 
    SET InstallOrder = (select max(InstallOrder) from Apps) + 1 
    WHERE rowid = NEW.rowid;
END

(unless there is something I didn't get).  Supply 0 as value or  don't mention it in the list of fields in the insert and set a default value = 0.

[Andreik]

It's not very clear what OP asked for. I understand that he want to update all InstallOrders after a specific (inserted) InstallOrder if the current InstallOrder already exists and I realize from his example that InstallOrder is not always the last. This is why I opted for a BEFORE insert trigger.

[jchd]

Then things are now not as clear to me either.  Let's give a chance to the OP to brush mud and explain a little more details about his/her use case.

[benners]

Thanks for the replies. I'll try to be more specific.

I used to use ini files to store the information about the installers for a bunch of programs that install unattendedly. As I had some spare time (which disappears when I open AutoIt), I thought I would combine them into a datbase with a gui front end to add records.

Some programs or updates may need to be installed before others so I have the InstallOrder column (used to be sorted in an array when using the inis). The idea is I'll add a program to the list and select an integer that determines when it is installed in the whole sequence once the list is sorted. If I have 10 apps, install order 1 to 10 and I decide to add another that needs to be at number 8. As I have already got a number 8 and the column entries are set to be unique there will be a conflict.

The idea is to get a list of the numbers that are already used and create a list of available numbers in a drop down list. There might be 1,2,3,4,6,7 so 5 is free. If the user selects 5, all is well but if number 7 is entered into the drop down, A prompt will feedback that that number is taken and offer to insert the selected setup program, thus pushing number 7 to 8 and so on.

The process would be to query if the number (7) was available, If not run the code below to increment the numbers (7 +) in the table up one to allow insertion.

UPDATE 'Apps' SET InstallOrder = Installorder + 1 WHERE InstallOrder >= 1

Then insert the data into the table. I think the code supplied by Andreik in post #3 will work better as the column will be incremented before the new record is added and I shouldn't need to run the code each time as the DB will take care of it with the trigger.

I have attached a pic that may shed light on the end use. As you can see there are gaps in the sequence and some duplicated numbers.

[jchd]

15 minutes ago, benners said:

UPDATE 'Apps' SET InstallOrder = Installorder + 1 WHERE InstallOrder >= 1

You probably meant InstallOrder >= 7

What you need is now clearer and indeed a before insert trigger is fine for doing that correctly.

[benners]

Yep the 1 or 7 will change depending on the selected number.  Hopefully the bit of code in the trigger will sort that. Just need to figure how to apply it to the DB in SQLiteExpert now.

Cheers

NEW.InstallOrder

[jchd]

Select desired table, open tab "Design" then sub-tab "Triggers", then "Add" button ...

[benners]

I have been trying that. I wasn't sure how to add the when expression part. I tried a few variations but nothing showed in the Trigger tab as expected. I also used the SQL tab to execute the full code. The execution was succesful but the trigger doesn't show up in the triggers tab as I assumed it would, but it does show in the DDL (code below). A Bit of a struggle when your bumbling around. I think the SQliteExpert help file is not for noobs.

CREATE TABLE [Apps](
  [ID] INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE, 
  [Installer] TEXT NOT NULL, 
  [Switches] TEXT, 
  [InstallOrder] INTEGER NOT NULL UNIQUE, 
  [AppIcon] TEXT, 
  [AppName] TEXT NOT NULL, 
  [AppVersion] TEXT, 
  [AppDescription] TEXT, 
  [AppUsers] TEXT NOT NULL);

CREATE TRIGGER InstallOrder BEFORE INSERT ON Apps 
BEGIN
    UPDATE Apps 
    SET InstallOrder = InstallOrder + 1 
    WHERE InstallOrder >= NEW.InstallOrder 
        AND EXISTS(SELECT 1 FROM Apps WHERE InstallOrder = NEW.InstallOrder);
END;

 

[Andreik]

I never used SQLite Expert but you don't need to write anything in WHEN Expression field instead in the next tab Trigger Body you should complete the update statement. Maybe looking at the pathway, how your trigger is created you will understand the fields above better. In your case Trigger action and WHEN Expression are omitted.

 

Edited December 26, 2019 by Andreik

[benners]

I have just tested your code Andreik and it works on the database. when I opened it in SQLiteExpert the trigger now shows 😣. Must have been with closing and opening. It also gives my an idea how to add triggers in the future as the when expression if filled out also.

Thanks all.

Would it be better to remove the unique contraint and run the trigger after insert/update? I have read that After triggers are prefered before

"before".

Edited December 26, 2019 by benners

[jchd]

The issue is that you entered the trigger body in the WHEN clause instead of the body itself.

This is how your trigger should look like:

CREATE TRIGGER [trInsInstallOrder] BEFORE INSERT ON [Apps] FOR EACH ROW
WHEN EXISTS (SELECT 1
               FROM   [Apps] [A]
               WHERE  [A].[InstallOrder] = [new].[InstallOrder])
BEGIN
  UPDATE
    [Apps]
  SET
    [InstallOrder] = [InstallOrder] + 1
  WHERE
    [InstallOrder] >= [new].[InstallOrder];
END;

This way, the trigger body only runs whenever the increment has to be performed.