Files
craft-anything/crates/formats/tests/sqlite_create_table_comments.rs
RRFZ 534b287732 Keep SQL comments out of SQLite column definitions (R3.12) (#1249) (#1249)
A comma or quote inside a comment in a CREATE TABLE no longer splits or swallows column definitions, a comment before or right after a column name isn't read as part of it, and a comment inside a definition stays out of its type, so INTEGER PRIMARY KEY still marks the rowid alias.
2026-11-09 10:24:10 +08:00

78 lines
3.9 KiB
Rust

use craft_anything_formats::sqlite::{CellValue, SqliteDb, parse_create_columns};
use craft_anything_testkit::sqlite::{Val, table_db, without_rowid_db};
fn text(value: &str) -> CellValue {
CellValue::Text { text: value.into(), truncated: false }
}
fn names_and_types(sql: &str) -> (Vec<String>, Vec<String>) {
parse_create_columns(sql).into_iter().map(|c| (c.name, c.type_name)).unzip()
}
/// SQLite keeps a CREATE TABLE exactly as written, comments included. A comma or a quote inside
/// a comment doesn't end a column, and a comment in front of a column isn't its name.
#[test]
fn comments_between_columns_are_not_columns() {
let sql =
"CREATE TABLE t (\n id INTEGER PRIMARY KEY, -- the id, unique\n name TEXT, -- don't forget\n /* size, in bytes */ size INTEGER\n)";
let (names, types) = names_and_types(sql);
assert_eq!(names, ["id", "name", "size"]);
assert_eq!(types, ["INTEGER", "TEXT", "INTEGER"]);
}
/// A comment inside a column's definition is not part of its type, so INTEGER PRIMARY KEY
/// still makes the column the rowid alias.
#[test]
fn a_comment_inside_a_definition_keeps_the_rowid_alias() {
for sql in [
"CREATE TABLE t (id INTEGER -- key\n PRIMARY KEY, v TEXT)",
"CREATE TABLE t (id INTEGER/* key */PRIMARY KEY, v TEXT)",
"CREATE TABLE t (id INTEGER--key\n PRIMARY KEY, v TEXT)",
"CREATE TABLE t (id/* key */INTEGER PRIMARY KEY, v TEXT)",
"CREATE TABLE t (id--key\n INTEGER PRIMARY KEY, v TEXT)",
] {
assert_eq!(names_and_types(sql), (vec!["id".into(), "v".into()], vec!["INTEGER".into(), "TEXT".into()]), "{sql}");
let data = table_db(sql, &[(3, vec![Val::Null, Val::Text("x".into())])]);
let page = SqliteDb::parse(&data).unwrap().read_rows(2, 0, 10);
assert_eq!(page.rows[0].values, vec![CellValue::Int(3), text("x")], "{sql}");
}
}
/// ALTER TABLE ADD COLUMN copies the new definition, comments and all, into the CREATE TABLE.
/// Older rows then take that column's default.
#[test]
fn an_added_column_with_a_comment_takes_its_default() {
let sql = "CREATE TABLE t (a INTEGER PRIMARY KEY, b TEXT, c TEXT /* added, later */ DEFAULT 'q')";
assert_eq!(names_and_types(sql), (vec!["a".into(), "b".into(), "c".into()], vec!["INTEGER".into(), "TEXT".into(), "TEXT".into()]));
let data = table_db(sql, &[(1, vec![Val::Null, Val::Text("x".into())])]);
let page = SqliteDb::parse(&data).unwrap().read_rows(2, 0, 10);
assert_eq!(page.rows[0].values, vec![CellValue::Int(1), text("x"), text("q")]);
}
/// Comment markers inside quotes are text.
#[test]
fn comment_markers_inside_quotes_are_not_comments() {
let (names, _) = names_and_types("CREATE TABLE t (a TEXT DEFAULT '--', \"b/*\" TEXT, c TEXT DEFAULT '*/,', d)");
assert_eq!(names, ["a", "b/*", "c", "d"]);
}
/// A table constraint stays a constraint when a comment follows its keyword with no space.
#[test]
fn a_constraint_keyword_followed_by_a_comment_is_not_a_column() {
let sql = "CREATE TABLE t (k TEXT, v INT, CONSTRAINT--the key\n pk PRIMARY KEY (k)) WITHOUT ROWID";
assert_eq!(names_and_types(sql).0, ["k", "v"]);
let data = without_rowid_db(sql, &[vec![Val::Text("x".into()), Val::Int(1)]]);
let page = SqliteDb::parse(&data).unwrap().read_rows(2, 0, 10);
assert_eq!(page.rows[0].values, vec![text("x"), CellValue::Int(1)]);
}
/// A WITHOUT ROWID table's key is still found past comments, so rows come back in declaration order.
#[test]
fn without_rowid_keys_are_found_past_comments() {
let sql = "CREATE TABLE t (\n v INT, -- value, not the key\n k TEXT, -- the key's text\n PRIMARY KEY (k)\n) WITHOUT ROWID";
let data = without_rowid_db(sql, &[vec![Val::Text("x".into()), Val::Int(1)]]);
let page = SqliteDb::parse(&data).unwrap().read_rows(2, 0, 10);
assert_eq!(page.rows[0].values, vec![CellValue::Int(1), text("x")]);
assert!(page.notes.is_empty(), "{:?}", page.notes);
}