Quieting the SQL Syntax Warnings

Quieting the SQL Syntax Warnings

val db: SQLiteDatabase = TODO("open a database")

val st = db.compileStatement("ATTACH DATABASE ? AS plaintext KEY ''")

By default, Android Studio does not like this SQL statement. It puts a
red undersquiggle below KEY, complaining that it is expecting a ; instead.

According to SQLite, Android Studio is correct.

This error is coming from a SQL “language injection”. Rebecca Franks wrote
a great blog post
about language injections a few weeks ago. There is a “SQLiteDatabase methods”
language injection set up in my Android Studio 4.1.2 installation, and I assume that
it shipped with Studio itself.

However, in my case, I’m right, and Android Studio is wrong.

That is because
in this code I am not using plain SQLite — I am using SQLCipher for Android.
SQLCipher for Android has an extended form of ATTACH DATABASE that takes a KEY
option to supply a passphrase (shown here as the empty string '').

It is unclear why a language injection for android.database.sqlite.SQLiteDatabase
is affecting calls to net.sqlcipher.database.SQLiteDatabase. Perhaps it is because
net.sqlcipher.database.SQLiteDatabase implements androidx.sqlite.db.SupportSQLiteDatabase,
the SQLite indirection API that allows you to use things like SQLCipher for Android
with Room and SQLDelight. Or maybe this is a limitation of the scoping rules for
language injections, and anything named SQLiteDatabase will be affected.

Regardless, the red undersquiggle was annoying me.

I could disable that language injection, but then I lose syntax highlighting and
validation everywhere else. Instead, I want to be able to mark certain statements
as being correct and suppress the warning, akin to how @SuppressLint() works.
However, this is not a Lint check, so @SuppressLint itself does not help here.

Rebecca’s post gave me an idea, though, which turns out to work, in a couple of different ways.

In her post, she shows that you can use a //language= comment to cause a particular
language injection to be applied in a spot it might not normally be applied.
So, while this would result in no errors:

private const val ATTACH_THIS = "ATTACH DATABASE ? AS plaintext KEY ''"

…this would complain about KEY:

private const val ATTACH_THIS = "ATTACH DATABASE ? AS plaintext KEY ''"

In the first snippet, the IDE has no idea that this string is a SQL statement and
so does not apply any language injections. In the second, we teach it that this
statement contains SQL, so the IDE applies the appropriate language injection.

So, one workaround is to pull the offending SQL out of the SQLiteDatabase calls:

val db: SQLiteDatabase = TODO("open a database")

val st = db.compileStatement(ATTACH_THIS)

If we skip the //language=sql comment on the ATTACH_THIS declaration, then
we will not get any complaints about KEY.

Another workaround is to override the language injection at the call site:

val db: SQLiteDatabase = TODO("open a database")

val st = db.compileStatement("ATTACH DATABASE ? AS plaintext KEY ''")

Here, we tell the IDE “Treat this as plain text. Yes, yes, I know, it looks like
it should be SQL. And, yeah, I do not really know if you know what ‘plain text’ is.
Just skip any syntax validation on the string, please.”

(fortunately, the comment is shorter)

At least for now, these workarounds give you fine-grained ability to suppress SQL syntax warnings,
in cases where Android Studio’s SQL parser either has bugs or is confused by extended
SQL syntax.

The AndroidX Tech site contains source code, transitive dependency details, and much more for Google’s androidx artifacts!