aleckazakova / sql-psi Goto Github PK

An extendable parsing environment for sql which outputs PSI

License: Apache License 2.0

Lex 5.77% Kotlin 93.46% Java 0.10% Python 0.67%

sql-psi's Issues

MYSQL Unknown function for MySQL: last_insert_id

Dialect: MySQL
failing sql:

CREATE TABLE data (
  `id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT;
  `value` VARCHAR(255) NOT NULL
);

insertSomeData:
insert into data (id, value) VALUES (NULL, ?);

lastInsertId:
SELECT LAST_INSERT_ID();

The LAST_INSERT_ID() function should return the AUTO_INCREMENT id of the last row that has been inserted or updated in a table. similar to last_insert_rowid for SQLite.

PLANNING: sql-psi 1.0

The main feature here is to have the individual dialects be their own artifacts, with a net result of being able to do something like this in sqldelight:

sqldelight {
  MyDatabase {
    dialect = "com.cashapp.sql.dialects:sqlite-3.24:1.0.0"
  }
}

sql-psi would become the API and ANSI-SQL grammar which sqldelight depends on, and individual dialects would be able to provide:

parser rules (using https://github.com/AlecStrong/Grammar-Kit-Composer)
exposed functions with types (https://github.com/cashapp/sqldelight/blob/master/sqldelight-compiler/src/main/kotlin/com/squareup/sqldelight/core/lang/util/ExprUtil.kt#L139)
expression extension types (https://github.com/AlecStrong/sql-psi/pull/116/files)
column metadata including:
- type (https://github.com/cashapp/sqldelight/blob/master/sqldelight-compiler/src/main/kotlin/com/squareup/sqldelight/core/lang/psi/sqlTypeName.kt)
- has default (https://github.com/AlecStrong/sql-psi/blob/master/core/src/main/kotlin/com/alecstrong/sql/psi/core/columnDef.kt)
some java SPI for loading the grammar from sqldelight and initializing it (also overriding it with sqldelight-specific rules)
subclass of sqldelight-runtime and SqlResultSet with additional getters (like getBoolean for postgres)
additional runtimes for commonly used adapters (cashapp/sqldelight#1882)
tests that verify the dialect will be compatible with sqldelight

As part of this we probably want to publish tests in sql-psi as well so that individual grammars can just run the existing sql test suite.

MySQL - AFTER not supported

The AFTER keyword is currently not supported. It is described here.

ALTER TABLE table
  ADD COLUMN foo VARCHAR(100) NULL DEFAULT NULL AFTER bar;

<column constraint real> expected, got 'AFTER'

SQLite Window Function Support

Starting with SQLite 3.25, support for window functions is available in SQLite. Currently, SQLDelight's grammar doesn't support these (e.g. SELECT id, row_number() over () from Foo; fails to parse. It looks like an additional 3.25-versioned grammar will need to be created(frustratingly close to 3.24 & UPSERT support!). I'd be open to contributing this but in my (admittedly brief) inspection of the code I'm not sure I have the full gist of what's needed to inherit from the 3.24 grammar.

PostgreSQL type boolean

Dialect: PostgreSQL

failing sql:

CREATE TABLE demo (
    rowname boolean
)

or i guess in sqldelight it will be BOOLEAN ?

https://www.postgresql.org/docs/9.5/datatype-boolean.html

currently this throws

Caused by: java.lang.IllegalArgumentException: Unknown kotlin type for sql type boolean
...

UUID not supported in .sq files

PostgreSQL

failing sql:

HSQL - date/time types cannot be declared with timezone

time and timestamp columns in HSQL can be declared with or without a time zone. http://hsqldb.org/doc/2.0/guide/sqlgeneral-chapt.html#sgc_datetime_types

This syntax does not work at the moment, despite the BNF suggesting support for it. By that I mean that the BNF has a definition that looks like it should work, but simple tests reveal errors.

CREATE TABLE times(
  some_time TIME(6) WITH TIME ZONE,
  some_timestamp TIMESTAMP WITHOUT TIME ZONE,
);

PostgreSQL support SERIAL type

Dialect: PostgreSQL

failing sql:

create table test (
    id        serial    not null
)

https://www.postgresql.org/docs/8.1/datatype.html#DATATYPE-SERIAL

is there a good alternative for autoincrmenting ids for primary keys ?

MySQL - MODIFY not supported

The MODIFY keyword isn't supported, but is valid valid syntax to change a column definition as described here.

Trying to use it results in this error:
'.', ADD or RENAME expected, got 'MODIFY'

Fix snapshot publishing

Snapshots are a great way to let people verify things are fixed before doing a full release, need to get the credentials right to make that happen and then document how to use sqldelight with a snapshot version of sql-psi

DROP TABLE regression

Some regression happened, all dialects are affected.

CREATE TABLE dog (
  name TEXT NOT NULL,
  breed TEXT NOT NULL,
  is_good INTEGER AS Boolean NOT NULL DEFAULT 1
);

dropTable:
DROP TABLE dog;

No table found with name dog
8    DROP TABLE dog
                ^^^

UNIX_TIMESTAMP, TO_SECONDS, JSON_ARRAYAGG MySQL functions not recognized

Dialect: MySQL

failing sql:

CREATE TABLE `models` (
  `build_date` datetime NOT NULL
) DEFAULT CHARSET=latin1;
SELECT UNIX_TIMESTAMP(build_date) FROM models LIMIT 1;
SELECT TO_SECONDS(build_date) FROM models LIMIT 1;

References:

Cannot access ROWID of aliased table in ORDER BY

Dialect: sqlite

failing sql:

CREATE TABLE Test1(id TEXT);
CREATE TABLE Test2(id TEXT);

fetchFirst:
SELECT t1.*, t2.* FROM Test1 t1
JOIN Test2 t2 ON t1.id = t2.id
WHERE t1.id = 'something good'
ORDER BY t1.rowid ASC
LIMIT 1;

In our .sq, we get this error:

line 17:12 - No column found with name rowid
14    SELECT t1.*, t2.* FROM Test1 t1
15    JOIN Test2 t2 ON t1.id = t2.uid
16    WHERE id = 'something good'
17    ORDER BY t1.rowid ASC
                  ^^^^^
18    LIMIT 1

Workaround is to add this to the join JOIN (SELECT rowid AS rowid, id FROM t1) rowids ON t1.id = rowids.id, then you can access rowids.rowid.

PostgreSQL OFFSET

Dialect: PostgreSQL

failing sql:

getLogsForGatewayV2:
    SELECT * FROM logs
        WHERE gateway = :gateway AND ((:skipCommands > 0) OR text NOT LIKE '!%')
        ORDER BY timestamp ASC
        OFFSET (SELECT COUNT(*) FROM logs
            WHERE gateway = :gateway AND ((:skipCommands > 0) OR text NOT LIKE '!%')
      ) - :amount
;

error:

D:/dev/Sibyl/module/logging/src/main/sqldelight/sibyl/db/logs.sq line 17:8 - ',' or LIMIT expected, got 'OFFSET'
14    SELECT * FROM logs
15            WHERE gateway = :gateway AND ((:skipCommands > 0) OR text NOT LIKE '!%')
16            ORDER BY timestamp ASC
17            OFFSET (
              ^^^^^^

if you know a better way to reverse the output so i don't have to reverse it in code later that would also be nice, this seems like ugly SQL

PS: another small thing i stumbled over: OR text NOT LIKE'!%' is valid but OR NOT text LIKE '!%'
both are valid (working) when sent though the IJ idea ultimate db console

Syntax error with bitwise operators

WHERE flags & ~:ignore > 0

Support RETURNING * in CTE

Dialect: PostgreSQL

See #170 (comment).

Support building the current schema from migration files

This is the norm in the server world, there shouldn't be anything prohibiting it from sqldelight. Biggest thing is custom types will have to be definable in migration files. On the sqldelight side there will probably need to be a gradle flag to switch between the two modes.

Postgres timestamp with time zone

Support of timestamp with time zone

Dialect: postgres

https://www.postgresql.org/docs/9.4/datatype-datetime.html

CREATE TABLE Product
(
    id                   SERIAL PRIMARY KEY,
    date_created         timestamp with time zone
);

Error:

'(', ')', ',', <column constraint real> or AS expected, got 'with'

Support data-modifying statements in WITH

Add support for INSERT, UPDATE, and DELETE in WITH.

Per the documentation:

Data-modifying statements in WITH usually have RETURNING clauses, …

So this issue depends on ~~#138~~.

Sqlite support REPLACE function

Unless, I'm overlooking something REPLACE isn't supported - https://www.sqlitetutorial.net/sqlite-replace-function/

ORDER BY can include unprojected columns

also means you can get column ambiguity on them

Regression from SqlDelight 1.3.0 to 1.4.0 - Erroneous 'Multiple columns found' error

Dialect: SQLite

failing sql:

CREATE TABLE foo(
    id TEXT NOT NULL PRIMARY KEY
);

CREATE TABLE bar(
    id TEXT NOT NULL PRIMARY KEY
);

CREATE TABLE baz(
    id TEXT NOT NULL PRIMARY KEY
);

repro:
SELECT id FROM foo
WHERE id IN (
    SELECT foo.id FROM foo
    LEFT JOIN bar ON foo.id = bar.id
    LEFT JOIN baz ON foo.id = baz.id
);

This is a reduced repro of a more complex query that I have in my Android app, however the query above labeled repro works fine with SqlDelight 1.3.0 but when I upgrade to 1.4.0, it fails with the error message:

<path to sq files>/Foo.sq line 17:25 - Multiple columns found with name id
14    SELECT id FROM foo
15    WHERE id IN (
16        SELECT foo.id FROM foo
17        LEFT JOIN bar ON foo.id = bar.id
                               ^^
18        LEFT JOIN baz ON foo.id = baz.id
19    )

<path to sq files>/Foo.sq line 18:25 - Multiple columns found with name id
14    SELECT id FROM foo
15    WHERE id IN (
16        SELECT foo.id FROM foo
17        LEFT JOIN bar ON foo.id = bar.id
18        LEFT JOIN baz ON foo.id = baz.id
                               ^^
19    )

PostgrSQL TIMESTAMP type seems to miss some plumbing

Dialect: PostgreSQL
latest 1.5.0-SNAPSHOT at time of reporting

the following SQL compiles but is not usable at runtime

CREATE TABLE reminders (
    requestedAt  TIMESTAMP  NOT NULL
)

when trying to insert into a table with timestamps

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: column "requestedat" is of type timestamp without time zone but expression is of type character varying
  Hint: You will need to rewrite or cast the expression.
  Position: 51
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
	at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:153)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)
	at com.squareup.sqldelight.sqlite.driver.SqliteJdbcPreparedStatement.execute$sqldelight_jdbc_driver(JdbcDriver.kt:147)
	at com.squareup.sqldelight.sqlite.driver.JdbcDriver.execute(JdbcDriver.kt:57)

i tried to use

    requestedAt  TIMESTAMP AS LocalDateTime NOT NULL

and provide a custom type adapter

val timestampFormat = DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss")
val timestampAdapter = object : ColumnAdapter<LocalDateTime, String> {
    override fun decode(databaseValue: String) = LocalDateTime.parse(databaseValue, timestampFormat)
    override fun encode(value: LocalDateTime) = value.toString(timestampFormat)
}

vut it seems to fail before getting there, the error seems to be something with converting the postgresql output

Postgres jsonb

Support for json and jsonb types in Postgres dialect

CREATE TABLE ProductAttribute
(
    id       SERIAL PRIMARY KEY,
    name     varchar(255),
    terms    jsonb
);

Error:

ProductAttribute.sq line 8:13 - <type name real> expected, got 'jsonb'

It could be supported in the same way as json, so converted to String.

Cannot select rowid

Dialect: SQLite

failing sql:

SELECT rowid, * FROM table ORDER BY rowid DESC LIMIT 1

It seems like an issue selecting rowid specifically. If you just wanted the max rowid, you CAN do:

SELECT max(rowid) FROM table

You could, in theory, use this in a subquery to select the first record, but the syntax remains broken when only selecting rowid (the sql does not compile).

I get:

  SELECT rowid FROM SheetText ORDER BY rowid DESC LIMIT 1

PostgreSQL support upsert

Dialect: PostgreSQL

failing sql:

INSERT INTO feeds (id,  title,   author,  image,  description,  pub_date,  webpage_url,  lang,  display_url)
VALUES            (:id, :title,  :author, :image, :description, :pub_date, :webpage_url, :lang, :display_url)
ON CONFLICT (id) DO UPDATE SET
  title       = :title,
  author      = :author,
  image       = :image,
  description = :description,
  pub_date    = :pub_date
RETURNING id;

error:

',' expected, got 'ON'

PostgreSQL support RETURNING

Dialect: PostgreSQL

failing sql:

DELETE FROM feed_items
WHERE item = :item
RETURNING feed;

error:

<binary like operator real>, BETWEEN, IN, LIMIT or ORDER expected, got 'RETURNING'

MySQL - CHANGE not supported

CHANGE is currently not supported, which is described as a valid argument when altering a table here

ALTER TABLE foo
  CHANGE COLUMN bar bar VARCHAR(200);

Currently trying to use it results in this error:
'.', ADD or RENAME expected, got 'MODIFY'

PostgreSQL boolean argument types

Dialect: PostgreSQL

failing sql:

getLogsForGateway:
    SELECT * FROM logs
        WHERE gateway = :gateway
            AND ((:skipCommands > 0) OR text NOT LIKE '!%')
        ORDER BY timestamp DESC
        LIMIT :amount
;

at runtime this errors with

org.postgresql.util.PSQLException: ERROR: argument of OR must be type boolean, not type bigint

the generated code seems to infer that it is a boolean correctly

  fun getLogsForGateway(
    gateway: String,
    skipCommands: Boolean,
    amount: Long
  ): Query<Logs>

but the implementation generates this for binding the boolean

bindLong(2, if (skipCommands) 1L else 0L)

which i guess is not acceptable as boolean for the PG server
there seems to be no equivalent to stringtype connection option to make the PG server guess and cast if it looks correct

Inner Join throwing SQLiteException

Dialect SQLite

Adding selectOrgView below to the schema of an empty database causes the following Exception:

android.database.sqlite.SQLiteException: unknown error (code 0 SQLITE_OK): Queries can be performed using SQLiteDatabase query or rawQuery methods only.

When I add selectOrgView to the schema after the databse is initially created everything works fine.

failing sql:

CREATE TABLE users (
    id TEXT NOT NULL PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL
);

CREATE TABLE organizations (
    id TEXT NOT NULL PRIMARY KEY,
    user_id TEXT NOT NULL,
    name TEXT NOT NULL
);

selectOrgView:
SELECT first_name, last_name, name FROM users INNER JOIN organizations ON organizations.user_id = users.id WHERE users.id = ?;
VALUES(?);

SQLDelight version: 1.3.0
Platforms: Android/iOS

PostgreSQL type TIMESTAMP not supported

Dialect: PostgreSQL

failing sql:

1    CREATE TABLE reminders (
2        id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
3        timestamp TIMESTAMP NOT NULL,
                   ^^^^^^^^^
4        message TEXT NOT NULL
5    )

it is listed in the "supported" types here: https://cashapp.github.io/sqldelight/jvm_postgresql/types/

Postgres: Numeric is mapped to Long

Dialect: PostgreSQL

https://www.postgresql.org/docs/9.1/datatype-numeric.html

Numeric type is represented as Long, but according to spec it a decimal type with defined precision and should probably be mapped to BigDecimal with the scale set according to schema.

CREATE TABLE Product
(
    id                   SERIAL PRIMARY KEY,
    weight_lbs           NUMERIC(5, 1)
);

data class Product(
  val id: Int,
  val weight_lbs: Long?
)

MySQL INSTR should accept two String arguments, generated code interprets 2nd argument as Long

Dialect: MySQL
I'm using the SQLDelight plugin in my build.gradle with: "com.squareup.sqldelight" version "1.4.1".

failing sql:

CREATE TABLE `models` (
  `model_id` int(11) NOT NULL AUTO_INCREMENT,
  `model_descriptor_id` int(11) NOT NULL,
  PRIMARY KEY (`model_id`)
) DEFAULT CHARSET=latin1;

searchDescription:
SELECT model_id, model_description FROM models WHERE INSTR(model_description, ?) > 0;

Generated Kotlin is:

interface ModelQueries : Transacter {
  fun <T : Any> searchDescription(value: Long?, mapper: (model_id: Int,
      model_description: String?) -> T): Query<T>

  fun searchDescription(value: Long?): Query<SearchDescription>
}

I believe value in the generated searchDescription should be a String not a Long.

Here's example usage (in Kotlin, as a Micronaut gRPC endpoint):

@Singleton
@Suppress("unused")
class ModelSearchEndpoint(val dataSource: DataSource) : ModelSearchServiceGrpcKt.ModelSearchServiceCoroutineImplBase() {

    override suspend fun search(request: ModelSearchRequest): ModelSearchResponse {
        val query = request.query // This value is a String.

        val driver: SqlDriver = dataSource.asJdbcDriver()
        val db = MagixDb(driver)
        val modelQueries: ModelQueries = db.modelQueries
        val models = modelQueries.searchDescription(
                query, // compiler error below references this line
                { model_id, model_description -> Model.newBuilder().setModelId(model_id).setDescription(model_description).build() })
            .executeAsList()

        return ModelSearchResponse.newBuilder().addAllResults(models).build()
    }
}

I get this compiler error:

e: /.../src/main/kotlin/com/myproj/ModelSearchEndpoint.kt: (30, 17): Type mismatch: inferred type is String! but Long? was expected

I'm able to run this SQL directly (with Server version: 5.5.62 MySQL Community Server (GPL)):

mysql> SELECT model_id, model_description FROM models WHERE INSTR(model_description, "par") > 0 LIMIT 10;

I'm just starting to use SQLDelight, and so far am really enjoying being able to both write SQL directly and then call it via nice typed wrappers. So thanks!

MySQL - CURRENT_TIMESTAMP(n) is not supported

When setting the default for a timestamp, it's possible in MySQL to pass a number to the CURRENT_TIMESTAMP function, so something like this is valid.

CREATE TABLE foo(
  created_at timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
);

Currently errors with ')', ',', <column constraint real> or ON expected, got '('

Aliased column name is not recognized in HAVING clause in MySQL

Dialect: MySQL

failing sql:

-- Table definition edited down to the relevant columns:
CREATE TABLE `models` (
  `model_id` int(11) NOT NULL AUTO_INCREMENT,
  `model_descriptor_id` int(11) NOT NULL,
  `build_date` datetime NOT NULL,
  PRIMARY KEY (`model_id`)
) DEFAULT CHARSET=latin1;

searchTest:
SELECT TO_SECONDS(build_date) AS build_date_seconds
FROM models
GROUP BY model_descriptor_id
HAVING build_date_seconds > 0 LIMIT 1;

The query runs fine in the MySQL shell, but I get this error:

/path/to/db/ModelSearch.sq line 7:7 - No column found with name build_date_seconds
4    SELECT TO_SECONDS(build_date) AS build_date_seconds
5    FROM models
6    GROUP BY model_descriptor_id
7    HAVING build_date_seconds > 0 LIMIT 1
            ^^^^^^^^^^^^^^^^^^

Postgres 1-D array

Add support of 1-D arrays https://www.postgresql.org/docs/9.1/arrays.html

Dialect: postgres

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    pay_by_quarter2  integer ARRAY
);

Error:

')', ',', <column constraint real> or AS expected, got '['

')', ',', <column constraint real> or AS expected, got 'ARRAY'

PostgreSQL column-list syntax

Dialect: PostgreSQL

failing sql:

update:
    UPDATE lastSeen SET (userid, timestamp) = (:userid, :timestamp)
    WHERE lastSeen.account = :account AND lastSeen.username = :username
;

example from postgresql docs:

UPDATE weather SET (temp_lo, temp_hi, prcp) = (temp_lo+1, temp_lo+15, DEFAULT)
  WHERE city = 'San Francisco' AND date = '2003-07-03';

Support column alias in GROUP BY

Dialect: SQLite

failing sql:

SELECT strftime('%Y', database.timestamp / 1000, 'unixepoch') AS year, COUNT(*)
FROM database
GROUP BY year;

Handle IndexNotReady

com.intellij.openapi.project.IndexNotReadyException: Please change caller according to com.intellij.openapi.project.IndexNotReadyException documentation
	at com.intellij.openapi.project.IndexNotReadyException.create(IndexNotReadyException.java:81)
	at com.intellij.util.indexing.FileBasedIndexImpl.handleDumbMode(FileBasedIndexImpl.java:715)
	at com.intellij.util.indexing.FileBasedIndexImpl.ensureUpToDate(FileBasedIndexImpl.java:665)
	at com.intellij.psi.stubs.StubIndexImpl.getContainingIds(StubIndexImpl.java:540)
	at com.intellij.psi.stubs.StubIndexImpl.processElements(StubIndexImpl.java:396)
	at com.intellij.psi.stubs.StubIndex.getElements(StubIndex.java:107)
	at com.intellij.psi.stubs.StubIndex.getElements(StubIndex.java:95)
	at com.alecstrong.sql.psi.core.psi.SchemaContributorIndexImpl.get(SchemaContributorIndex.kt:36)
	at com.alecstrong.sql.psi.core.psi.mixins.SqlStmtListMixin.tablesAvailable(SqlStmtListMixin.kt:30)
	at com.alecstrong.sql.psi.core.psi.SqlCompositeElementImpl.tablesAvailable(SqlCompositeElementImpl.kt:22)
	at com.alecstrong.sql.psi.core.psi.SqlSchemaContributorImpl.tablesAvailable(SqlCompositeElementImpl.kt:50)
	at com.alecstrong.sql.psi.core.psi.SqlCompositeElementImpl.tablesAvailable(SqlCompositeElementImpl.kt:22)
	at com.alecstrong.sql.psi.core.psi.SqlCompositeElementImpl.tablesAvailable(SqlCompositeElementImpl.kt:22)
	at com.alecstrong.sql.psi.core.psi.SqlCompositeElementImpl.tablesAvailable(SqlCompositeElementImpl.kt:22)
	at com.alecstrong.sql.psi.core.psi.SqlTableReference.getVariants(SqlTableReference.kt:36)
	at com.alecstrong.sql.psi.core.psi.SqlTableReference$resolved$1.invoke(SqlTableReference.kt:19)
	at com.alecstrong.sql.psi.core.psi.SqlTableReference$resolved$1.invoke(SqlTableReference.kt:11)
	at com.alecstrong.sql.psi.core.ModifiableFileLazy.forFile(ModifiableFileLazy.kt:22)
	at com.alecstrong.sql.psi.core.psi.SqlTableReference.resolve(SqlTableReference.kt:24)
	at com.alecstrong.sql.psi.core.psi.SqlTableReference.resolve(SqlTableReference.kt:11)
	at com.alecstrong.sql.psi.core.psi.mixins.CreateTableMixin$checkForeignKeys$1.invoke(CreateTableMixin.kt:137)
	at com.alecstrong.sql.psi.core.psi.mixins.CreateTableMixin.checkForeignKeys(CreateTableMixin.kt:173)

PostgreSQL support NULL in constraints

Dialect: PostgreSQL

failing sql:

CREATE TABLE feeds (
  id          TEXT        PRIMARY KEY,
  title       TEXT        NOT NULL,
  author      TEXT        NULL,
  image       TEXT        NULL,
  description TEXT        NULL,
  pub_date    timestamp   NOT NULL,
  webpage_url TEXT        NOT NULL UNIQUE,
  lang        TEXT        NOT NULL,
  display_url TEXT        NOT NULL
);

error:

')', ',', <column constraint real> or AS expected, got 'NULL'

Exception during compilation

java.lang.IllegalStateException: @NotNull method com/intellij/openapi/progress/ProgressManager.getInstance must not return null
        at com.intellij.openapi.progress.ProgressManager.$$$reportNull$$$0(ProgressManager.java)
        at com.intellij.openapi.progress.ProgressManager.getInstance(ProgressManager.java:41)
        at com.intellij.psi.impl.source.tree.LazyParseableElement.setChildren(LazyParseableElement.java:223)
        at com.intellij.psi.impl.source.tree.LazyParseableElement.lambda$ensureParsed$0(LazyParseableElement.java:202)
        at com.intellij.psi.impl.DebugUtil.performPsiModification(DebugUtil.java:553)
        at com.intellij.psi.impl.source.tree.LazyParseableElement.ensureParsed(LazyParseableElement.java:191)
        at com.intellij.psi.impl.source.tree.LazyParseableElement.getFirstChildNode(LazyParseableElement.java:245)
        at com.intellij.psi.impl.source.tree.CompositeElement.countChildren(CompositeElement.java:449)
        at com.intellij.psi.impl.source.tree.CompositeElement.getChildrenAsPsiElements(CompositeElement.java:411)
        at com.intellij.psi.impl.source.PsiFileImpl.getChildren(PsiFileImpl.java:713)
        at com.intellij.psi.impl.source.PsiFileImpl.findChildByClass(PsiFileImpl.java:523)
        at com.alecstrong.sqlite.psi.core.SqliteFileBase.getSqlStmtList(SqliteFileBase.kt:33)
        at com.alecstrong.sqlite.psi.core.SqliteFileBase.views(SqliteFileBase.kt:68)
        at com.alecstrong.sqlite.psi.core.SqliteFileBase.<init>(SqliteFileBase.kt:26)

Support domain types

Dialect: PostgreSQL

https://www.postgresql.org/docs/12/domains.html

Example

CREATE DOMAIN http_url AS TEXT
    DEFAULT 'https://example.com'
    NOT NULL
    CHECK ( VALUE != '' );

CREATE TABLE page_rank (
    url http_url,
    rank INT NOT NULL
)

Side Note

This could lead to defining the Kotlin type in SQLDelight's .sq file more succinctly. For example:

CREATE DOMAIN http_url AS TEXT AS okhttp3.HttpUrl

I'll create a separate issue in SQLDelight when this issue is implemented.

Allow functions containing keywords instead of commas

Dialect: HSQLDB, MySQL, PostgreSQL

Some functions allow replacing the commas in an argument list with keywords.

Failing SQL:

SELECT substring('Thomas' from 2 for 3)

SEPARATOR for GROUP_CONCAT MySQL function is not supported

Dialect: MySQL

failing sql:

CREATE TABLE `model_collections` (
  `model_collection_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`model_collection_id`)
) DEFAULT CHARSET=latin1;

search:
SELECT                                                                                              
    GROUP_CONCAT(name SEPARATOR '§') AS collection_names                                            
FROM                                                                                                
    model_collections;

Reference: https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat

Error is:

/path/to/my/db/ModelSearch.sq line 18:22 - '(', ')', '.', <binary like operator real>, BETWEEN or IN expected, got 'SEPARATOR'
17    SELECT
18        GROUP_CONCAT(name SEPARATOR '§') AS collection_names
                            ^^^^^^^^^
19    FROM
20        model_collections

(This is a workaround to using JSON_ARRAYAGG which is not recognized, in #191.)

Postgres - Named arguments not supported

I'd assume this happens for MySQL as well. Given this Player.sq file:

CREATE TABLE hockey_players (
  player_number INTEGER NOT NULL,
  full_name TEXT NOT NULL
);

fullNameSearch:
SELECT *
FROM hockey_players
WHERE full_name = :name;

It fails to parse with this exception

line 9:17 - <expr> expected, got ':'
7    SELECT *
8    FROM hockey_players
9    WHERE full_name = :name

MySQL - Multiple alter statements per alter table

It seems there's an issue adding a column to a MySQL table, using the following syntax

ALTER TABLE foo
  ADD COLUMN bar VARCHAR(100) NULL DEFAULT NULL;

The error give is:
<column constraint real> expected, got ','

PostgreSQL support INTERVAL type from 9.5

Dialect: PostgreSQL

failing sql:

CREATE TABLE items (
  id           TEXT       PRIMARY KEY,
  title        TEXT       NOT NULL,
  description  TEXT       ,
  image        TEXT       ,
  author       TEXT       ,
  duration     INTERVAL   ,
  webpage_url  TEXT       NOT NULL     UNIQUE,
  url          TEXT       ,
  filesize     bigint     ,
  ext          TEXT       NOT NULL
);

error:

line 7:15 - <type name real> expected, got 'INTERVAL'

INTERVAL is supported from 9.5 https://www.postgresql.org/docs/current/datatype-datetime.html

JDBC type: https://jdbc.postgresql.org/documentation/publicapi/org/postgresql/util/PGInterval.html

MySQL - Variable length IN clause is not supported

Sample .sq file to help reproduce, using the MySQL dialect

CREATE TABLE brands (
  id bigint(20) NOT NULL AUTO_INCREMENT,
  token varchar(32) NOT NULL UNIQUE,
  name varchar(255) NOT NULL,
  active TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (id)
);

getBrandsByToken:
SELECT name FROM brands WHERE token IN ?;

Fails with

* What went wrong:
Execution failed for task ':sqldelight:generateMainFaireDatabaseInterface'.
> Failed to compile SqlCompoundSelectStmtImpl(COMPOUND_SELECT_STMT): [] :
  SELECT name FROM brands WHERE token IN ?

And the stacktrace points to this as the cause

Caused by: java.lang.IllegalStateException: Array parameters are not supported outside of SQLite.
        at com.squareup.sqldelight.core.compiler.QueryGenerator.executeBlock(QueryGenerator.kt:56)
        at com.squareup.sqldelight.core.compiler.SelectQueryGenerator.querySubtype(SelectQueryGenerator.kt:211)
        at com.squareup.sqldelight.core.compiler.QueriesTypeGenerator$generateType$$inlined$forEach$lambda$1.invoke(QueriesTypeGenerator.kt:105)
        at com.squareup.sqldelight.core.compiler.QueriesTypeGenerator$generateType$$inlined$forEach$lambda$1.invoke(QueriesTypeGenerator.kt:22)
        at com.squareup.sqldelight.core.compiler.SqlDelightCompilerKt.tryWithElement(SqlDelightCompiler.kt:197)
        ... 104 more

Quoted identifier incorrectly evaluated

Dialect: PostgreSQL

Failing SQL:

CREATE TABLE person (
  name TEXT NOT NULL
);

INSERT INTO person
VALUES ("Veyndan");

When we use a quoted identifier (like "Veyndan" in the example above), I would expect an error with "No column found with name Veyndan", but no such error is currently displayed.

sqlite CAST

https://www.sqlite.org/lang_expr.html#castexpr

aleckazakova / sql-psi Goto Github PK

sql-psi's Issues

Example

Side Note

Recommend Projects

Recommend Topics

Recommend Org