let sql = "PRAGMA cache_size";

match sqlite_and_generic().verified_stmt(sql) {

Statement::Pragma {

name,

value: None,

is_eq: false,

} => {

assert_eq!("cache_size", name.to_string());

}

_ => unreachable!(),

}

let sql = "PRAGMA cache_size = 10";

match sqlite_and_generic().verified_stmt(sql) {

Statement::Pragma {

name,

value: Some(val),

is_eq: true,

} => {

assert_eq!("cache_size", name.to_string());

assert_eq!("10", val.to_string());

}

_ => unreachable!(),

}

let sql = "PRAGMA cache_size(10)";

match sqlite_and_generic().verified_stmt(sql) {

Statement::Pragma {

name,

value: Some(val),

is_eq: false,

} => {

assert_eq!("cache_size", name.to_string());

assert_eq!("10", val.to_string());

}

_ => unreachable!(),

}

let sql = "PRAGMA table_info = 'sqlite_master'";

match sqlite_and_generic().verified_stmt(sql) {

Statement::Pragma {

name,

value: Some(val),

is_eq: true,

} => {

assert_eq!("table_info", name.to_string());

assert_eq!("'sqlite_master'", val.to_string());

}

_ => unreachable!(),

}

let sql = "PRAGMA table_info(\"sqlite_master\")";

match sqlite_and_generic().verified_stmt(sql) {

Statement::Pragma {

name,

value: Some(val),

is_eq: false,

} => {

assert_eq!("table_info", name.to_string());

assert_eq!("\"sqlite_master\"", val.to_string());

}

_ => unreachable!(),

}

let sql = "PRAGMA table_info = ?";

match sqlite_and_generic().verified_stmt(sql) {

Statement::Pragma {

name,

value: Some(val),

is_eq: true,

} => {

assert_eq!("table_info", name.to_string());

assert_eq!("?", val.to_string());

}

_ => unreachable!(),

}

let sql = "CREATE TABLE t (a INT) WITHOUT ROWID";

match sqlite_and_generic().verified_stmt(sql) {

Statement::CreateTable(CreateTable {

name,

without_rowid: true,

..

}) => {

assert_eq!("t", name.to_string());

}

_ => unreachable!(),

}

let sql = "CREATE VIRTUAL TABLE IF NOT EXISTS t USING module_name (arg1, arg2)";

match sqlite_and_generic().verified_stmt(sql) {

Statement::CreateVirtualTable {

name,

if_not_exists: true,

module_name,

module_args,

} => {

let args = vec![Ident::new("arg1"), Ident::new("arg2")];

assert_eq!("t", name.to_string());

assert_eq!("module_name", module_name.to_string());

assert_eq!(args, module_args);

}

_ => unreachable!(),

}

let sql = "CREATE VIRTUAL TABLE t USING module_name";

sqlite_and_generic().verified_stmt(sql);

let sql = "CREATE TEMPORARY VIEW IF NOT EXISTS myschema.myview AS SELECT foo FROM bar";

match sqlite_and_generic().verified_stmt(sql) {

Statement::CreateView(CreateView {

name,

columns,

query,

or_replace,

materialized,

options,

cluster_by,

comment,

with_no_schema_binding: late_binding,

if_not_exists,

temporary,

..

}) => {

assert_eq!("myschema.myview", name.to_string());

assert_eq!(Vec::<ViewColumnDef>::new(), columns);

assert_eq!("SELECT foo FROM bar", query.to_string());

assert!(!materialized);

assert!(!or_replace);

assert_eq!(options, CreateTableOptions::None);

assert_eq!(cluster_by, vec![]);

assert!(comment.is_none());

assert!(!late_binding);

assert!(if_not_exists);

assert!(temporary);

}

_ => unreachable!(),

}

// Sqlite supports this operator: https://www.sqlite.org/lang_expr.html#binaryops

let input = "SELECT a==b FROM t";

let expected = "SELECT a = b FROM t";

let _ = sqlite_and_generic().one_statement_parses_to(input, expected);

let sql = "CREATE TABLE foo (bar INT PRIMARY KEY AUTOINCREMENT)";

match sqlite_and_generic().verified_stmt(sql) {

Statement::CreateTable(CreateTable { name, columns, .. }) => {

assert_eq!(name.to_string(), "foo");

assert_eq!(

vec![ColumnDef {

name: "bar".into(),

data_type: DataType::Int(None),

options: vec![

ColumnOptionDef {

name: None,

option: ColumnOption::PrimaryKey(PrimaryKeyConstraint {

name: None,

index_name: None,

index_type: None,

columns: vec![],

index_options: vec![],

characteristics: None,

}),

},

ColumnOptionDef {

name: None,

option: ColumnOption::DialectSpecific(vec![Token::make_keyword(

"AUTOINCREMENT"

)]),

},

],

}],

columns

);

}

_ => unreachable!(),

}

let expected_column_def = |kind| ColumnDef {

name: "bar".into(),

data_type: DataType::Int(None),

options: vec![

ColumnOptionDef {

name: None,

option: ColumnOption::PrimaryKey(PrimaryKeyConstraint {

name: None,

index_name: None,

index_type: None,

columns: vec![],

index_options: vec![],

characteristics: None,

}),

},

ColumnOptionDef {

name: None,

option: ColumnOption::DialectSpecific(vec![Token::make_keyword(kind)]),

},

],

};

let sql = "CREATE TABLE foo (bar INT PRIMARY KEY ASC)";

match sqlite_and_generic().verified_stmt(sql) {

Statement::CreateTable(CreateTable { columns, .. }) => {

assert_eq!(vec![expected_column_def("ASC")], columns);

}

_ => unreachable!(),

}

let sql = "CREATE TABLE foo (bar INT PRIMARY KEY DESC)";

match sqlite_and_generic().verified_stmt(sql) {

Statement::CreateTable(CreateTable { columns, .. }) => {

assert_eq!(vec![expected_column_def("DESC")], columns);

}

_ => unreachable!(),

}

let sql = "CREATE TABLE `PRIMARY` (\"KEY\" INT, [INDEX] INT)";

match sqlite().verified_stmt(sql) {

Statement::CreateTable(CreateTable { name, columns, .. }) => {

assert_eq!(name.to_string(), "`PRIMARY`");

assert_eq!(

vec![

ColumnDef {

name: Ident::with_quote('"', "KEY"),

data_type: DataType::Int(None),

options: vec![],

},

ColumnDef {

name: Ident::with_quote('[', "INDEX"),

data_type: DataType::Int(None),

options: vec![],

},

],

columns

);

}

_ => unreachable!(),

}

let sql_default = "CREATE TABLE t1 (a INT, b INT GENERATED ALWAYS AS (a * 2))";

sqlite_and_generic().verified_stmt(sql_default);

let sql_virt = "CREATE TABLE t1 (a INT, b INT GENERATED ALWAYS AS (a * 2) VIRTUAL)";

sqlite_and_generic().verified_stmt(sql_virt);

let sql_stored = "CREATE TABLE t1 (a INT, b INT GENERATED ALWAYS AS (a * 2) STORED)";

sqlite_and_generic().verified_stmt(sql_stored);

sqlite_and_generic().verified_stmt("CREATE TABLE t1 (a INT, b INT AS (a * 2))");

sqlite_and_generic().verified_stmt("CREATE TABLE t1 (a INT, b INT AS (a * 2) VIRTUAL)");

sqlite_and_generic().verified_stmt("CREATE TABLE t1 (a INT, b INT AS (a * 2) STORED)");

for keyword in [

Keyword::ROLLBACK,

Keyword::ABORT,

Keyword::FAIL,

Keyword::IGNORE,

Keyword::REPLACE,

] {

let sql = format!("CREATE TABLE t1 (a INT, b INT ON CONFLICT {keyword:?})");

match sqlite_and_generic().verified_stmt(&sql) {

Statement::CreateTable(CreateTable { columns, .. }) => {

assert_eq!(

vec![ColumnOptionDef {

name: None,

option: ColumnOption::OnConflict(keyword),

}],

columns[1].options

);

}

_ => unreachable!(),

}

}

let sql_err = "CREATE TABLE t1 (a INT, b INT ON CONFLICT BOH)";

let err = sqlite_and_generic()

.parse_sql_statements(sql_err)

.unwrap_err();

assert_eq!(

err,

ParserError::ParserError(

"Expected: one of ROLLBACK or ABORT or FAIL or IGNORE or REPLACE, found: BOH"

.to_string()

)

);

// In postgres, this would be the absolute value operator '@' applied to the column 'xxx'

// But in sqlite, this is a named parameter.

// see https://www.sqlite.org/lang_expr.html#varparam

let sql = "SELECT @xxx";

let ast = sqlite().verified_only_select(sql);

assert_eq!(

ast.projection[0],

UnnamedExpr(Expr::Value(

(Value::Placeholder("@xxx".into())).with_empty_span()

)),

);

let sql = "CREATE TABLE Fruits (id TEXT NOT NULL PRIMARY KEY) STRICT";

if let Statement::CreateTable(CreateTable { name, strict, .. }) = sqlite().verified_stmt(sql) {

assert_eq!(name.to_string(), "Fruits");

assert!(strict);

}

// SQLite supports the SUBSTRING function since v3.34, but does not support the SQL standard

// SUBSTRING(expr FROM start FOR length) syntax.

// https://www.sqlite.org/lang_corefunc.html#substr

sqlite().verified_only_select("SELECT SUBSTRING('SQLITE', 3, 4)");

sqlite().verified_only_select("SELECT SUBSTR('SQLITE', 3, 4)");

sqlite().verified_only_select("SELECT SUBSTRING('SQLITE', 3)");

sqlite().verified_only_select("SELECT SUBSTR('SQLITE', 3)");

for func_name in [

"row_number",

"rank",

"max",

"count",

"user_defined_function",

] {

let sql = format!("SELECT {func_name}(x) FILTER (WHERE y) OVER () FROM t");

let select = sqlite().verified_only_select(&sql);

assert_eq!(select.to_string(), sql);

assert_eq!(

select.projection,

vec![SelectItem::UnnamedExpr(Expr::Function(Function {

name: ObjectName::from(vec![Ident::new(func_name)]),

uses_odbc_syntax: false,

parameters: FunctionArguments::None,

args: FunctionArguments::List(FunctionArgumentList {

duplicate_treatment: None,

args: vec![FunctionArg::Unnamed(FunctionArgExpr::Expr(

Expr::Identifier(Ident::new("x"))

))],

clauses: vec![],

}),

null_treatment: None,

over: Some(WindowType::WindowSpec(WindowSpec {

window_name: None,

partition_by: vec![],

order_by: vec![],

window_frame: None,

})),

filter: Some(Box::new(Expr::Identifier(Ident::new("y")))),

within_group: vec![],

}))]

);

}

let sql = "ATTACH DATABASE 'test.db' AS test";

let verified_stmt = sqlite().verified_stmt(sql);

assert_eq!(sql, format!("{verified_stmt}"));

match verified_stmt {

Statement::AttachDatabase {

schema_name,

database_file_name:

Expr::Value(ValueWithSpan {

value: Value::SingleQuotedString(literal_name),

span: _,

}),

database: true,

} => {

assert_eq!(schema_name.value, "test");

assert_eq!(literal_name, "test.db");

}

_ => unreachable!(),

}

// See https://github.com/sqlparser-rs/sqlparser-rs/issues/1311

assert_eq!(

sqlite().verified_stmt("UPDATE x SET (a, b) = (1, 2)"),

Statement::Update(Update {

optimizer_hints: vec![],

or: None,

assignments: vec![Assignment {

target: AssignmentTarget::Tuple(vec![

ObjectName::from(vec![Ident::new("a"),]),

ObjectName::from(vec![Ident::new("b"),]),

]),

value: Expr::Tuple(vec![

Expr::Value((Value::Number("1".parse().unwrap(), false)).with_empty_span()),

Expr::Value((Value::Number("2".parse().unwrap(), false)).with_empty_span())

])

}],

selection: None,

table: TableWithJoins {

relation: table_from_name(ObjectName::from(vec![Ident::new("x")])),

joins: vec![],

},

from: None,

returning: None,

output: None,

limit: None,

update_token: AttachedToken::empty()

})

);

let sql = "SELECT * FROM t1 WHERE a IN ()";

let select = sqlite().verified_only_select(sql);

if let Expr::InList { list, .. } = select.selection.as_ref().unwrap() {

assert_eq!(list.len(), 0);

} else {

unreachable!()

}

sqlite_with_options(ParserOptions::new().with_trailing_commas(true)).one_statement_parses_to(

"SELECT * FROM t1 WHERE a IN (,)",

"SELECT * FROM t1 WHERE a IN ()",

);

let sql = "SELECT * FROM t1 WHERE a IN (,,)";

let sqlite = sqlite_with_options(ParserOptions::new().with_trailing_commas(true));

assert_eq!(

"sql parser error: Expected: an expression, found: ,",

sqlite.parse_sql_statements(sql).unwrap_err().to_string()

);

sqlite_and_generic().verified_stmt("BEGIN DEFERRED TRANSACTION");

sqlite_and_generic().verified_stmt("BEGIN IMMEDIATE TRANSACTION");

sqlite_and_generic().verified_stmt("BEGIN EXCLUSIVE TRANSACTION");

sqlite_and_generic().verified_stmt("BEGIN DEFERRED");

sqlite_and_generic().verified_stmt("BEGIN IMMEDIATE");

sqlite_and_generic().verified_stmt("BEGIN EXCLUSIVE");

// This relates to the discussion in issue #291. The `$id` should be treated as a placeholder,

// not as an identifier in SQLite dialect.

//

// Reference: https://www.sqlite.org/lang_expr.html#varparam

match sqlite().verified_expr("id = $id") {

Expr::BinaryOp { op, left, right } => {

assert_eq!(op, BinaryOperator::Eq);

assert_eq!(left, Box::new(Expr::Identifier(Ident::new("id"))));

assert_eq!(

right,

Box::new(Expr::Value(

(Placeholder("$id".to_string())).with_empty_span()

))

);

}

_ => unreachable!(),

}

// $$ is a valid placeholder in SQLite

match sqlite().verified_expr("id = $$") {

Expr::BinaryOp { op, left, right } => {

assert_eq!(op, BinaryOperator::Eq);

assert_eq!(left, Box::new(Expr::Identifier(Ident::new("id"))));

assert_eq!(

right,

Box::new(Expr::Value(

(Placeholder("$$".to_string())).with_empty_span()

))

);

}

_ => unreachable!(),

}

assert_eq!(

sqlite().verified_expr("col MATCH 'pattern'"),

Expr::BinaryOp {

op: BinaryOperator::Match,

left: Box::new(Expr::Identifier(Ident::new("col"))),

right: Box::new(Expr::Value(

(Value::SingleQuotedString("pattern".to_string())).with_empty_span()

))

}

);

sqlite().verified_only_select("SELECT * FROM email WHERE email MATCH 'fts5'");

assert_eq!(

sqlite().verified_expr("col REGEXP 'pattern'"),

Expr::BinaryOp {

op: BinaryOperator::Regexp,

left: Box::new(Expr::Identifier(Ident::new("col"))),

right: Box::new(Expr::Value(

(Value::SingleQuotedString("pattern".to_string())).with_empty_span()

))

}

);

sqlite().verified_only_select(r#"SELECT count(*) FROM messages WHERE msg_text REGEXP '\d+'"#);

// Should return an error, not panic

assert!(sqlite().parse_sql_statements("SELECT 1 REGEXP").is_err());

assert!(sqlite().parse_sql_statements("SELECT 1 MATCH").is_err());

match sqlite().verified_stmt("UPDATE foo SET bar = 1 LIMIT 99") {

Statement::Update(Update { limit, .. }) => {

assert_eq!(limit, Some(Expr::value(number("99"))));

}

_ => unreachable!(),

}

match sqlite().verified_stmt("DELETE FROM foo LIMIT 99") {

Statement::Delete(Delete { limit, .. }) => {

assert_eq!(limit, Some(Expr::value(number("99"))));

}

_ => unreachable!(),

}

let statement1 = "CREATE TRIGGER trg_inherit_asset_models AFTER INSERT ON assets FOR EACH ROW BEGIN INSERT INTO users (name) SELECT pam.name FROM users AS pam; END";

match sqlite().verified_stmt(statement1) {

Statement::CreateTrigger(CreateTrigger {

or_alter,

temporary,

or_replace,

is_constraint,

name,

period,

period_before_table,

events,

table_name,

referenced_table_name,

referencing,

trigger_object,

condition,

exec_body: _,

statements_as,

statements: _,

characteristics,

}) => {

assert!(!or_alter);

assert!(!temporary);

assert!(!or_replace);

assert!(!is_constraint);

assert_eq!(name.to_string(), "trg_inherit_asset_models");

assert_eq!(period, Some(TriggerPeriod::After));

assert!(period_before_table);

assert_eq!(events, vec![TriggerEvent::Insert]);

assert_eq!(table_name.to_string(), "assets");

assert!(referenced_table_name.is_none());

assert!(referencing.is_empty());

assert_eq!(

trigger_object,

Some(TriggerObjectKind::ForEach(TriggerObject::Row))

);

assert!(condition.is_none());

assert!(!statements_as);

assert!(characteristics.is_none());

}

_ => unreachable!("Expected CREATE TRIGGER statement"),

}

// Here we check that the variant of CREATE TRIGGER that omits the `FOR EACH ROW` clause,

// which in SQLite may be implicitly assumed, is parsed correctly.

let statement2 = "CREATE TRIGGER log_new_user AFTER INSERT ON users BEGIN INSERT INTO user_log (user_id, action, timestamp) VALUES (NEW.id, 'created', datetime('now')); END";

match sqlite().verified_stmt(statement2) {

Statement::CreateTrigger(CreateTrigger {

or_alter,

temporary,

or_replace,

is_constraint,

name,

period,

period_before_table,

events,

table_name,

referenced_table_name,

referencing,

trigger_object,

condition,

exec_body: _,

statements_as,

statements: _,

characteristics,

}) => {

assert!(!or_alter);

assert!(!temporary);

assert!(!or_replace);

assert!(!is_constraint);

assert_eq!(name.to_string(), "log_new_user");

assert_eq!(period, Some(TriggerPeriod::After));

assert!(period_before_table);

assert_eq!(events, vec![TriggerEvent::Insert]);

assert_eq!(table_name.to_string(), "users");

assert!(referenced_table_name.is_none());

assert!(referencing.is_empty());

assert!(trigger_object.is_none());

assert!(condition.is_none());

assert!(!statements_as);

assert!(characteristics.is_none());

}

_ => unreachable!("Expected CREATE TRIGGER statement"),

}

let statement3 = "CREATE TRIGGER cleanup_orders AFTER DELETE ON customers BEGIN DELETE FROM orders WHERE customer_id = OLD.id; DELETE FROM invoices WHERE customer_id = OLD.id; END";

match sqlite().verified_stmt(statement3) {

Statement::CreateTrigger(CreateTrigger {

or_alter,

temporary,

or_replace,

is_constraint,

name,

period,

period_before_table,

events,

table_name,

referenced_table_name,

referencing,

trigger_object,

condition,

exec_body: _,

statements_as,

statements: _,

characteristics,

}) => {

assert!(!or_alter);

assert!(!temporary);

assert!(!or_replace);

assert!(!is_constraint);

assert_eq!(name.to_string(), "cleanup_orders");

assert_eq!(period, Some(TriggerPeriod::After));

assert!(period_before_table);

assert_eq!(events, vec![TriggerEvent::Delete]);

assert_eq!(table_name.to_string(), "customers");

assert!(referenced_table_name.is_none());

assert!(referencing.is_empty());

assert!(trigger_object.is_none());

assert!(condition.is_none());

assert!(!statements_as);

assert!(characteristics.is_none());

}

_ => unreachable!("Expected CREATE TRIGGER statement"),

}

let statement4 = "CREATE TRIGGER trg_before_update BEFORE UPDATE ON products FOR EACH ROW WHEN NEW.price < 0 BEGIN SELECT RAISE(ABORT, 'Price cannot be negative'); END";

match sqlite().verified_stmt(statement4) {

Statement::CreateTrigger(CreateTrigger {

or_alter,

temporary,

or_replace,

is_constraint,

name,

period,

period_before_table,

events,

table_name,

referenced_table_name,

referencing,

trigger_object,

condition,

exec_body: _,

statements_as,

statements: _,

characteristics,

}) => {

assert!(!or_alter);

assert!(!temporary);

assert!(!or_replace);

assert!(!is_constraint);

assert_eq!(name.to_string(), "trg_before_update");

assert_eq!(period, Some(TriggerPeriod::Before));

assert!(period_before_table);

assert_eq!(events, vec![TriggerEvent::Update(Vec::new())]);

assert_eq!(table_name.to_string(), "products");

assert!(referenced_table_name.is_none());

assert!(referencing.is_empty());

assert_eq!(

trigger_object,

Some(TriggerObjectKind::ForEach(TriggerObject::Row))

);

assert!(condition.is_some());

assert!(!statements_as);

assert!(characteristics.is_none());

}

_ => unreachable!("Expected CREATE TRIGGER statement"),

}

// We test a INSTEAD OF trigger on a view

let statement5 = "CREATE TRIGGER trg_instead_of_insert INSTEAD OF INSERT ON my_view BEGIN INSERT INTO my_table (col1, col2) VALUES (NEW.col1, NEW.col2); END";

match sqlite().verified_stmt(statement5) {

Statement::CreateTrigger(CreateTrigger {

or_alter,

temporary,

or_replace,

is_constraint,

name,

period,

period_before_table,

events,

table_name,

referenced_table_name,

referencing,

trigger_object,

condition,

exec_body: _,

statements_as,

statements: _,

characteristics,

}) => {

assert!(!or_alter);

assert!(!temporary);

assert!(!or_replace);

assert!(!is_constraint);

assert_eq!(name.to_string(), "trg_instead_of_insert");

assert_eq!(period, Some(TriggerPeriod::InsteadOf));

assert!(period_before_table);

assert_eq!(events, vec![TriggerEvent::Insert]);

assert_eq!(table_name.to_string(), "my_view");

assert!(referenced_table_name.is_none());

assert!(referencing.is_empty());

assert!(trigger_object.is_none());

assert!(condition.is_none());

assert!(!statements_as);

assert!(characteristics.is_none());

}

_ => unreachable!("Expected CREATE TRIGGER statement"),

}

// We test a temporary trigger

let statement6 = "CREATE TEMPORARY TRIGGER temp_trigger AFTER INSERT ON temp_table BEGIN UPDATE log_table SET count = count + 1; END";

match sqlite().verified_stmt(statement6) {

Statement::CreateTrigger(CreateTrigger {

or_alter,

temporary,

or_replace,

is_constraint,

name,

period,

period_before_table,

events,

table_name,

referenced_table_name,

referencing,

trigger_object,

condition,

exec_body: _,

statements_as,

statements: _,

characteristics,

}) => {

assert!(!or_alter);

assert!(temporary);

assert!(!or_replace);

assert!(!is_constraint);

assert_eq!(name.to_string(), "temp_trigger");

assert_eq!(period, Some(TriggerPeriod::After));

assert!(period_before_table);

assert_eq!(events, vec![TriggerEvent::Insert]);

assert_eq!(table_name.to_string(), "temp_table");

assert!(referenced_table_name.is_none());

assert!(referencing.is_empty());

assert!(trigger_object.is_none());

assert!(condition.is_none());

assert!(!statements_as);

assert!(characteristics.is_none());

}

_ => unreachable!("Expected CREATE TRIGGER statement"),

}

// We test a trigger defined without a period (BEFORE/AFTER/INSTEAD OF)

let statement7 = "CREATE TRIGGER trg_inherit_asset_models INSERT ON assets FOR EACH ROW BEGIN INSERT INTO users (name) SELECT pam.name FROM users AS pam; END";

sqlite().verified_stmt(statement7);

let statement = "DROP TRIGGER IF EXISTS trg_inherit_asset_models";

match sqlite().verified_stmt(statement) {

Statement::DropTrigger(DropTrigger {

if_exists,

trigger_name,

table_name,

option,

}) => {

assert!(if_exists);

assert_eq!(trigger_name.to_string(), "trg_inherit_asset_models");

assert!(table_name.is_none());

assert!(option.is_none());

}

_ => unreachable!("Expected DROP TRIGGER statement"),

}

TestedDialects::new(vec![

Box::new(SQLiteDialect {}),

Box::new(GenericDialect {}),

])