aleckazakova / sql-psi Goto Github PK
View Code? Open in Web Editor NEWAn extendable parsing environment for sql which outputs PSI
License: Apache License 2.0
An extendable parsing environment for sql which outputs PSI
License: Apache License 2.0
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/
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
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
^^^^^^^^^^^^^^^^^^
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
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.
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'
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
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'
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
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
...
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.)
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'
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)
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.
also means you can get column ambiguity on them
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'
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
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!
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:
getBoolean
for postgres)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.
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
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
.
Dialect: PostgreSQL
See #170 (comment).
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'
WHERE flags & ~:ignore > 0
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.
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';
Unless, I'm overlooking something REPLACE
isn't supported - https://www.sqlitetutorial.net/sqlite-replace-function/
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
^^^
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.
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)
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 )
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 ','
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 '('
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)
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.
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
Dialect: SQLite
failing sql:
SELECT strftime('%Y', database.timestamp / 1000, 'unixepoch') AS year, COUNT(*)
FROM database
GROUP BY year;
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
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:
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'
Dialect: PostgreSQL
https://www.postgresql.org/docs/12/domains.html
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
)
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.
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?
)
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 ?
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.
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'
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'
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
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,
);
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.