SQLite.Swift Expression() initialization for "not null" form?

Question

Please note that this question ONLY relates to the popular SQLite.swift library, stephencelis/SQLite.swift

With SQLite.swift you can

    let a = Expression<String>("a")
    let b = Expression<String>("b")

and so on. But how do you

select a.x, a.y, ifnull(b.q, 'default text'), a.z
from a
left join b on blah

how do you make an expression for an inline sql ifnull clause?

(The doco mentions that Expression has an init(literal:) initializer - maybe it's relevant - but it's undocumented and has unusual binding arguments.)

Please note, I'm completely aware that you could make the value optional

  let q = Expression<String?>("q")

and then just put in the default later;

I am asking how to express "ifnull(b.q, 'default text')" as an Expression (or, learn it is impossible) so that value will actually be used in the SQL expression.

Once again, this question relates only to the library /stephencelis/SQLite.swift

Well, a simple search for "ifnull" in the [SQLite.swift documentation](https://github.com/stephencelis/SQLite.swift/blob/master/Documentation/Index.md#sqliteswift-documentation) gives something. — Gwendal Roué, Apr 28 '17 at 15:39
Hey @GwendalRoué - thanks, but indeed, that line on the doco page doesn't at all explain how, or if, you can use a complex inline select expression. (eg, **select ifnull(b.q, 'default text'), blah, blah**...) Unfortunately I can't find it anywhere - perhaps it's just not handled. — Fattie, Apr 28 '17 at 17:14
So I guess you had to use raw SQL. In another project you may like to give GRDB.swift a try, as a replacement for SQLite.swift. The GRDB query builder has "holes" as well, and it can't generate all SQL queries. But at least you are not "punished" when you have to fallback to raw SQL: row consumption is identical. — Gwendal Roué, Apr 29 '17 at 03:37

score 1 · Accepted Answer · answered Oct 29 '17 at 15:26

I dislike answering my own question,

but honestly the realistic answer here is:

nowadays you have to use GRDB like everyone else, github.com/groue/GRDB.swift

The older SQL-wrapper libraries (as magnificent, awesome, incredible as they were at the time) are honestly just

totally out of date now, technology-wise
not honestly maintained realistically any more

As of late 2017 GRDB is the only real possibility.

score 1 · Answer 2 · answered May 07 '18 at 15:10

Maybe this is too late, but anyway. I've checked the source code, there is function

public func ??<V : Value>(optional: Expression<V?>, defaultValue: V) -> Expression<V> {
    return "ifnull".wrap([optional, defaultValue])
}

So you have to make the value optional

row.get(Expression<Double?>("q")) ?? 0 //this will be equals ifnull(q, 0)

SQLite.Swift Expression() initialization for "not null" form?

2 Answers2