Sqlite INSERT or else return existing item

Question

I have the following data structure: ID (int), Name (text), Info (text), List (text).
"List" is a list of enums as string with the intended syntax: "word1,word2,word3"

I'd like to be able to add an item with this content example: ID=null, Name=Abc, Info=xyz, List=word3
With the behavior of this:

• Insert an item if not yet existing.
• Else update the item if it exists (based on "Name") like this: Ignore ID | overwrite Info | check if the list contains each given word, and if doesn't, append it with "," as separator.

Every item added will have only 1 "List" enum, but the entries in the database are supposed to gather them for each entry, so that when they are queried, there are possibly multiple enums.

So the question is, how can I do that? I can imagine that this approach might work as well:

• Insert an item if not yet existing.
• Else return the existing item, let me manipulate it in C# (concat the "List" myself), then do a simple Update.

In this case I wonder, how can I do an "insert or else query"? Or is there a better way to do all of this?

Answer 1

To insert or update the existing, you can do an UPSERT

---

Since your data.List (poorly choosen name by the way) may contain any combination of the enum, you can use flags instead, such as :

public enum Words
{
    word1 = 1,
    word2 = 2,
    word3 = 4
}

And now, you can use bitwise operators to set/check/switch the bits :

// Set only the value Words.word1
var myWord = Words.word1; // MyWords = 1

// Use bitwise OR to add the value Words.word3 to the existing ones :
myWord |= Words.word3;

/*
 *   001
 * | 100
 * ------
 *   101
 */

// Use bitwise AND to check if the value Words.word2 is already set :
if (myWord & Words.word2 == Words.word2) { /* do something */ }

/*
 *   101
 * & 010
 * ------
 *   000
 */

// Use bitwise XOR to switch the bit Words.word3
myWord ^= Words.word3;

/*
 *   101
 * ^ 100
 * ------
 *   001
 */

// And once again, with the previous result :
myWord ^= Words.word3;

/*
 *   001
 * ^ 100
 * ------
 *   101
 */

And then, in your database, you can replace the type of the field List by INT.

UPDATE yourTable
SET List = List | newValue
WHERE id = 42;

---

Another way to do that would be to use normalized database, using 3 tables :

Data : id (int, PK), Name (text), Info (text)

List : id (int, PK), List (text)

DataList : id_data (int, FK), id_list (int, FK) with as primary key the combination of both FK to ensure uniqueness of values