Add option for system tables and sqlite_sequence

[hfhbd]

Fixes #3729

[hfhbd]

Open question: Could we add intellij idea support to navigate to these virtual files too to look up the definition?

[AlecKazakova]

yes definitely, I did that for .db files and viewing tables that you're migrating from a .db file.

https://github.com/cashapp/sqldelight/blob/master/sqldelight-idea-plugin/src/main/resources/META-INF/plugin.xml#L32-L38 https://github.com/cashapp/sqldelight/blob/master/sqldelight-compiler/src/main/kotlin/app/cash/sqldelight/core/lang/DatabaseFileViewProviderFactory.kt

[hfhbd]

Wait, I think I just need to add the system tables to the file index and the rest should work out of the box.

[hfhbd]

The project index requires physical files, so I need to create a new file for each system table. Idea is to store them in a systemTables folder to delete this folder each time a new dialect is set. This works well.

Question: Where do we want to store the files? I don't want to expose them to the user, so one option: we put them into the Gradle build directory. Alternative we could put it into a temp folder using Files.temp.

[AlecKazakova]

The project index requires physical files

where are you seeing that?

[hfhbd]

Here: https://jetbrains-platform.slack.com/archives/C5U8BM1MK/p1680169205527879?thread_ts=1680169023.310329&cid=C5U8BM1MK

Or I don't get it.

[AlecKazakova]

we do enough custom stuff that that shouldn't be necessary, all that is really necessary for this to work is that the SqlFileBase.schema function exposes these system tables, which you can do without physical files. If they're in memory files thats ideal since intellij will still be able to display them if you cmd+click on symbols

[hfhbd]

sql-psi uses the SqlCoreEnvironment and here the system tables work because I add the LightVirtualFiles to the CoreFileIndex which is used by SqlFileBase.schema: https://github.com/AlecStrong/sql-psi/blob/433dcd89157cbad80ae69c55df6076d64a12e4a1/core/src/main/kotlin/com/alecstrong/sql/psi/core/SqlCoreEnvironment.kt#L113 But the idea plugin uses its own ProjectFileIndexImpl we can't control, or could we change this too? 🤔 Or do you mean something different?

[AlecKazakova]

I mean I don't think we should mess with the core file index at all. They're not real files, we just want the schemas to exist for sql statements, so we should just update the schemaContributor logic in SqlFileBase to incorporate them

[hfhbd]

Hm, but doesn't this mean we need to pass the system tables definition as constructor parameter here: https://github.com/AlecStrong/sql-psi/blob/d7edd93e46d03f47061ec6de28f56500af23a0cb/core/src/main/kotlin/com/alecstrong/sql/psi/core/SqlParserDefinition.kt#L32

[AlecKazakova]

needs a psi release I'm assuming? Is this ready for review otherwise?

[hfhbd]

Yes, yes.

[hfhbd]

The PR uses https://github.com/AlecKazakova/sql-psi/pull/546, https://github.com/AlecKazakova/sql-psi/pull/547, https://github.com/AlecKazakova/sql-psi/pull/548, https://github.com/AlecKazakova/sql-psi/pull/549

[AlecKazakova]

did you wanna try and get this in for 2.1?

[hfhbd]

What do you mean, if I will update this PR in the next hours to be included in the release? Or what do you plan at all?

[AlecKazakova]

ya if you update today i'll include in that release, otherwise can do later.

i approved it so it'd get merged if its green

[hfhbd]

Well, I still have some troubles with the settings and I need more time. Let's not merge it.

[AlecKazakova]

alright no worries