Support for upsert

[NilsLattek]

It would be cool if SQLDelight would support the upsert command: https://www.sqlite.org/lang_UPSERT.html

Right now the following sql:

upsert:
INSERT INTO stop (id, address_id, name)
VALUES (?, ?, ?) ON CONFLICT(address_id) DO UPDATE SET name=excluded.name;

will produce this error:

',' expected, got 'ON'
62    INSERT INTO stop (id, address_id, name)
63    VALUES (?, ?, ?) ON CONFLICT(address_id) DO
                       ^^

[AlecKazakova]

yea definitely, my hope is that we can allow specifying the minimum version of sqlite you'll be using which would enable newer language features

[eygraber]

@AlecStrong @JakeWharton is providing a mechanism to allow specifying the minimum version of sqlite what needs to be built, or does support for the newer language features (e.g. upsert) need to be built as well?

For the former, if you can point me in the general direction of what needs to be done, I can take a stab at it.

For the latter, I'd gladly take a stab at it when I have some free time (which I'm sure is your current position on the matter as well).

[JakeWharton]

My rough understanding would be that we need the sqlite-psi to always model the latest that sqlite supports. Then, we track the version somewhere in the core model of SQL Delight and use it to validate the PSI as it's being interpreted. So, for example, as we would see an ON CONFLICT clause we would report an error if you version was declared as less than 3.24.0. These two things can be pursued separately. There's surely already things we model in the PSI that only work on certain versions of sqlite.

…

On Fri, Oct 4, 2019 at 2:47 PM Eliezer Graber ***@***.***> wrote: @AlecStrong <https://github.com/AlecStrong> @JakeWharton <https://github.com/JakeWharton> is providing a mechanism to allow specifying the minimum version of sqlite what needs to be built, or does support for the newer language features (e.g. upsert) need to be built as well? For the former, if you can point me in the general direction of what needs to be done, I can take a stab at it. For the latter, I'd gladly take a stab at it when I have some free time (which I'm sure is your current position on the matter as well). — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#1436?email_source=notifications&email_token=AAAQIEIZME6UNA7TMV6WDT3QM6FVNA5CNFSM4IJVF22KYY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGOEAMSBTI#issuecomment-538517709>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAAQIEOFZLP4FNQGQD2YPK3QM6FVNANCNFSM4IJVF22A> .

[vincent-paing]

I'm on 1.3.0 and it's still is showing error at ON

[angusholder]

Did you set dialect = "sqlite:3.24" in the Gradle SQLDelight config?

[kkovach]

I have a couple questions concerning this or something similar to this. I've added the dialect line to my config. I'm able to compile, but am getting an "android.database.sqlite.SQLiteException: near "ON": syntax error (code 1 SQLITE_ERROR)" error at runtime. My table and insert statements are as follows...

CREATE TABLE user (
    userId INTEGER NOT NULL UNIQUE,
    name TEXT,
    address TEXT,
    city TEXT,
    state TEXT,
    zip TEXT,
    email TEXT,
    phone TEXT,
    url TEXT,
    isDefault INTEGER as Boolean DEFAULT 0
);

insertUser:
INSERT INTO user(userId, name, address, city, state, zip, email, phone, url, isDefault)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
ON CONFLICT(userId) DO
UPDATE SET name=excluded.name, email=excluded.email, address=excluded.address, city=excluded.city,
state=excluded.state, zip=excluded.zip, phone=excluded.phone, url=excluded.url;

Is there some other change or requirement that I missed? I tried reading everything but cannot seem to find what I might be missing?

Also, are you allowed to have multiple columns that can conflict? E.G. CONFLICT(id, userId)

Thanks!

[JakeWharton]

Has Android shipped a version that supports it? This chart is out of date: https://developer.android.com/reference/android/database/sqlite/package-summary Your best bet is to package your own Sqlite build if you want it available on all API levels.

On Tue, May 5, 2020, at 3:40 PM, Kevin Kovach wrote: I have a couple questions concerning this or something similar to this. I've added the dialect live to my config. I'm able to compile, but am getting an "android.database.sqlite.SQLiteException: near "ON": syntax error (code 1 SQLITE_ERROR)" error at runtime. My table and insert statements are as follows... `CREATE TABLE user (

userId INTEGER NOT NULL UNIQUE, name TEXT, address TEXT, city TEXT, state TEXT, zip TEXT, email TEXT, phone TEXT, url TEXT, isDefault INTEGER as Boolean DEFAULT 0 ); insertUser: INSERT INTO user(userId, name, address, city, state, zip, email, phone, url, isDefault) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON CONFLICT(userId) DO UPDATE SET name=excluded.name, email=excluded.email, address=excluded.address, city=excluded.city, state=excluded.state, zip=excluded.zip, phone=excluded.phone, url=excluded.url; `

…

Is there some other change or requirement that I missed? I tried reading everything but cannot seem to find what I might be missing? Also, are you allowed to have multiple columns that can conflict? E.G. CONFLICT(id, userId) Thanks! — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#1436 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAAQIEPUMJH3GA7ZDS6ZEYTRQBTUBANCNFSM4IJVF22A>.

[kkovach]

Sorry, I had read the page you linked. I guess I was hoping that there was something that I was missing to get it working on Android. Are there any/many options for supplying your own SQLite for Android? I've been searching for a couple hours now and have nothing definitive. If you have a suggesting or link on doing so I would appreciate it. Thanks.

[eygraber]

I've used Requery and SqlCipher, and they both work well with upsert.

[kihaki]

Did you set dialect = "sqlite:3.24" in the Gradle SQLDelight config?

Could you point me to where exactly this needs to go? I have tried:

sqldelight { dialect = "sqlite:3.8" }

Unfortunately this fails

Could not set unknown property 'dialect' for extension 'sqldelight' of type com.squareup.sqldelight.gradle.SqlDelightExtension.

I cannot find documentation on this.

[AlecKazakova]

It goes inside your database block:

sqldelight {
MyDatabase {
dialect = “sqlite:3.24:
}
}

[ursusursus]

Btw if I use unbundled sqlite and set dialect to 3.25

sqldelight {
    AppDatabase {
        packageName = "sk.foo.bar"
        dialect = "sqlite:3.25"
    }
}

It does compile and work, however I still get red underlines in the IDE (and DO is not highlighted)

Does the intellij plugin need upgrading?

[AlecKazakova]

did you do a gradle resync?

…

On Sun, Feb 21, 2021 at 11:24 PM ursusursus ***@***.***> wrote: Btw if I use unbundled sqlite and set dialect to 3.25 sqldelight { AppDatabase { packageName = "sk.foo.bar" dialect = "sqlite:3.25" } } It does compile and work, however I still get red underlines in the IDE [image: image] <https://user-images.githubusercontent.com/1503402/108662189-163b4480-74ce-11eb-8b52-309ef5b29af4.png> — You are receiving this because you modified the open/close state. Reply to this email directly, view it on GitHub <#1436 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAMZBQB6VWK57LWBHIL6X2DTAHMBPANCNFSM4IJVF22A> .

[ursusursus]

when exactly? after typing? IDE didn't complain it needs syncing, so unsure

[AlecKazakova]

After changing the dialect in gradle

…

On Mon, Feb 22, 2021 at 10:30 PM ursusursus ***@***.***> wrote: when exactly? after typing? IDE didn't complain it needs syncing, so unsure — You are receiving this because you modified the open/close state. Reply to this email directly, view it on GitHub <#1436 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAMZBQBBTUKMZN6IXBVVFCLTAMOO3ANCNFSM4IJVF22A> .

[ursusursus]

Yea I just did, and still the same issue

Btw windows 10, AS 4.1.2, plugin 1.4.4

[Ditscheridou]

I Mean wouldnt it be a workaround here, that you do something like this:
upsert { UPDATE MyTable SET key_=:value, WHERE key_=:key; INSERT OR IGNORE INTO MyTable(key_) VALUES(:key); }
?

[tonisives]

This doesn't work for postgres dialect:

id "com.squareup.sqldelight" version "1.5.3" apply false

[chardskarth]

Grouped statements arent supported for mysql dialect yet? I wonder where can we verify if its supported or not? 🤔