INSERT INTO multiple values

[krzyzanowskim]

I'd love to insert many values in single query so I build array of Setter

var keywordsInserts:[Setter] = [Setter]()
keywordsInserts.append(Word <- "A")
keywordsInserts.append(Word <- "B")
keywordsInserts.append(Word <- "C")

Keywords.insert(keywordsInserts) as Int64?

and this produced that query:

INSERT INTO "Keywords" ("Word", "Word", "Word") VALUES ("A","B","C")

while expected is

INSERT INTO "Keywords" ("Word") VALUES ("A"),("B"),("C")

I must doing something wrong. What should I do to get right query?

[stephencelis]

This sounds great, but we'd need some kind of way to build a type-safe way of handling this:

keywords.insert(Word) { rows in
    for word in ["A", "B", "C"] {
        rows.append(word)
    }
}

Something like the above. The big issue with most of the type-safe shims in SQLite.swift is the translation layers needed between Expression<T>, Expression<T?>, T, and T?. I haven't figured out a scalable way to define a variadic list of expressions and have the compiler to know to use the unwrapped values elsewhere. Because of this, each set of arguments must be handled individually, twice per argument.

func insert<A : Value>(Expression<A>, builder: BulkInsertBuilder<A> -> Void)
func insert<A : Value>(Expression<A?>, builder: BulkInsertBuilder<A?> -> Void)
func insert<A : Value, B : Value>(Expression<A>, Expression<B>, builder: BulkInsertBuilder<(A, B)> -> Void)
func insert<A : Value, B : Value>(Expression<A>, Expression<B?>, builder: BulkInsertBuilder<(A, B?)> -> Void)
func insert<A : Value, B : Value>(Expression<A?>, Expression<B>, builder: BulkInsertBuilder<(A?, B)> -> Void)
func insert<A : Value, B : Value>(Expression<A?>, Expression<B?>, builder: BulkInsertBuilder<(A?, B?)> -> Void)

It grows exponentially by argument count. If there were a way to define it once and have the type system resolve the wrapping/unwrapping, this would work great. As it stands, I can't figure out a scalable solution.

[lukescott]

Perhaps this would work:

keywords.insert(["A", "B", "C"]) { word in
    return [Word <- word]
}

The function would look like:

public func insert<C: CollectionType>(
    or action: OnConflict? = nil,
    rows: C,
    callback: C.Generator.Element -> [Setter]) -> Insert
{
    for row in rows {
        let values = callback(row)
        // ... construct query
    }
}

[lukescott]

Just realized that what I suggested isn't type safe :).

[lukescott]

I know Setter probably doesn't work this way, but I was thinking you could use an Array of tuples:

var rows = [(Setter<Word>)]()
rows.append((Word <- "A"))
rows.append((Word <- "B"))
rows.append((Word <- "C"))
keywords.insert(rows)

That would ensure each row had the same columns. The tricky part is ensuring that insert takes [(Setter<T>,...)].

Alternatively you can just run insert multiple times within a transaction. Most of the overhead is going to be in writing to disk anyway.

[stephencelis]

The tricky part is ensuring that insert takes [(Setter<T>,...)].

Yep. I haven't figured out a maintainable way to build this in Swift yet. We may need to wait for Swift to evolve and have more powerful generic type resolution.

Alternatively you can just run insert multiple times within a transaction. Most of the overhead is going to be in writing to disk anyway.

Yeah. I think that's the preferred approach, for now.

[mikemee]

Added to Feature Requests in the "suspended" section, so we don't lose track of this and/or for repeat requests.

[virtualrizwan]

Is there still a way to insert multiple rows in one go ?

[dbof10]

this is very common use case? no update?

[geoffmacd]

#1048