SpacetimeDB/crates/sqltest/test/tutorial.slt

# Original doc of the dialect at https://www.sqlite.org/sqllogictest/doc/trunk/about.wiki

# The purpose of this test suite is to validate the logic behind the evaluation of SQL statements,
# not the ability to handle extreme values, check performance, etc.
# Use a small range of values: small integers, short strings, and floating point numbers that use only the most significant bits of an a 32-bit IEEE float.

# Typically you start creating the schemas.

# A statetment that must execute end in `ok`.
statement ok
create table t(v1 int not null, v2 int not null, v3 int not null)


statement ok
insert into t values(1,4,2), (2,3,3), (3,4,4), (4,3,5)

# A statetment that must fail end in `error`
statement error
insert into failure

# Ensure that the statement errors and that the error
# message contains 'Multiple object drop not supported'
onlyif sqlite
statement error DROP VIEW foo, bar
DROP VIEW foo, bar;

# Ensure that the statement errors and that the error
# message contains 'DROP VIEW foo, bar'
onlyif sqlite
statement error DROP VIEW foo, bar
DROP VIEW foo, bar;


# Then you run queries. Use `----` to separate the output

# `III` means `3 columns of INTEGER`. So `I` is the datatype.
# The options are
#  T: Text
#  I: I32
#  R: F32
# `rowsort` do sorting in the test suite
# to account for the fact SQL have not intrinsic ordering
query III rowsort
select * from t
----
1	4	2
2	3	3
3	4	4
4	3	5

# You can implement execution that is conditional to the DB engine (sqlite, spacetime) using:

# .. execute in all engines except
skipif postgresql
query I
select v1 from t WHERE v1 = 1
----
1

# .. execute only on the specified engine
onlyif sqlite
query I
select v1 from t WHERE v1 = 1
----
    1

statement ok
drop table t