sql5 3.2.18

SQLite compatible database with CJK FTS5 full-text search
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280

//! 約束驗證（Constraint Checker）
//!
//! 支援 SQLite 相容的四種約束：
//!   - NOT NULL：欄位值不可為 NULL
//!   - UNIQUE：欄位值在整張表中不可重複（允許多個 NULL）
//!   - CHECK：自訂運算式必須為真
//!   - FOREIGN KEY：參照另一張表的主鍵（基礎版）
//!
//! 約束在 INSERT / UPDATE 時檢查，違反時回傳 Err。

use crate::table::row::{Row, Value};
use crate::table::schema::Schema;

// ── 約束定義（執行期） ────────────────────────────────────────────────────

#[derive(Debug, Clone)]
pub enum Constraint {
    NotNull  { column: String },
    Unique   { columns: Vec<String> },
    Check    { expr: crate::parser::ast::Expr },  // CHECK 運算式 AST
    ForeignKey {
        columns:    Vec<String>,
        ref_table:  String,
        ref_columns: Vec<String>,
    },
}

/// 一張表的完整約束組合
#[derive(Debug, Clone, Default)]
pub struct TableConstraints {
    pub constraints: Vec<Constraint>,
}

impl TableConstraints {
    pub fn new() -> Self { Self::default() }

    pub fn add(&mut self, c: Constraint) { self.constraints.push(c); }

    /// 從 AST 的 ColumnDef 自動建立 NOT NULL 與 UNIQUE 約束
    pub fn from_schema(schema: &Schema) -> Self {
        let mut tc = TableConstraints::new();
        for col in &schema.columns {
            if !col.nullable {
                tc.add(Constraint::NotNull { column: col.name.clone() });
            }
        }
        tc
    }
}

// ── 驗證 ──────────────────────────────────────────────────────────────────

/// 驗證一列是否符合所有約束
/// `existing_rows`：目前表中的所有資料（用於 UNIQUE 檢查）
pub fn check_row(
    row: &Row,
    schema: &Schema,
    constraints: &TableConstraints,
    existing_rows: &[Row],
) -> Result<(), String> {
    for constraint in &constraints.constraints {
        match constraint {
            Constraint::NotNull { column } => {
                let idx = schema.index_of(column)
                    .ok_or_else(|| format!("column '{}' not found", column))?;
                if matches!(row.values.get(idx), Some(Value::Null) | None) {
                    return Err(format!("NOT NULL constraint failed: {}", column));
                }
            }

            Constraint::Unique { columns } => {
                let idxs: Vec<usize> = columns.iter()
                    .map(|c| schema.index_of(c)
                        .ok_or_else(|| format!("column '{}' not found", c)))
                    .collect::<Result<_, _>>()?;

                // NULL 不參與 UNIQUE 比較（SQLite 行為）
                let new_vals: Vec<&Value> = idxs.iter()
                    .map(|&i| row.values.get(i).unwrap_or(&Value::Null))
                    .collect();
                if new_vals.iter().any(|v| matches!(v, Value::Null)) { continue; }

                for existing in existing_rows {
                    let ex_vals: Vec<&Value> = idxs.iter()
                        .map(|&i| existing.values.get(i).unwrap_or(&Value::Null))
                        .collect();
                    if ex_vals == new_vals {
                        return Err(format!("UNIQUE constraint failed: {}",
                            columns.join(", ")));
                    }
                }
            }

            Constraint::Check { expr } => {
                // 評估 CHECK 表達式
                if let Ok(val) = crate::planner::executor::eval_expr(expr, row, &schema.columns.iter().map(|c| c.name.clone()).collect::<Vec<_>>()) {
                    if !crate::planner::executor::is_truthy(&val) {
                        return Err("CHECK constraint failed".to_string());
                    }
                }
            }

            Constraint::ForeignKey { columns, ref_table, ref_columns } => {
                // 基礎版：記錄約束定義，實際驗證需要跨表查詢
                // 完整實作需要 Executor 的 get_table 存取
                let _ = (columns, ref_table, ref_columns);
            }
        }
    }
    Ok(())
}

/// 驗證刪除/更新時 FK 不被違反（基礎版：僅記錄）
pub fn check_fk_on_delete(
    _deleted_key: &Value,
    _constraints: &[TableConstraints],
) -> Result<(), String> {
    // TODO: 遍歷所有有 FK 指向此表的 TableConstraints
    Ok(())
}

// ── 從 AST ColumnDef 建立約束 ─────────────────────────────────────────────

pub fn constraints_from_ast(
    stmt: &crate::parser::ast::CreateTableStmt,
) -> TableConstraints {
    use crate::parser::ast::{ColumnConstraint, TableConstraint};
    let mut tc = TableConstraints::new();

    for col in &stmt.columns {
        for constraint in &col.constraints {
            match constraint {
                ColumnConstraint::NotNull => {
                    tc.add(Constraint::NotNull { column: col.name.clone() });
                }
                ColumnConstraint::Unique => {
                    tc.add(Constraint::Unique { columns: vec![col.name.clone()] });
                }
                ColumnConstraint::PrimaryKey { .. } => {
                    // PRIMARY KEY 隱含 NOT NULL
                    tc.add(Constraint::NotNull { column: col.name.clone() });
                }
                ColumnConstraint::Default(_) => {} // 預設值由 executor 處理
                ColumnConstraint::Check(expr) => {
                    tc.add(Constraint::Check { expr: expr.clone() });
                }
                ColumnConstraint::References { table, column } => {
                    let ref_col = column.clone().unwrap_or_else(|| "id".to_string());
                    tc.add(Constraint::ForeignKey {
                        columns: vec![col.name.clone()],
                        ref_table: table.clone(),
                        ref_columns: vec![ref_col],
                    });
                }
            }
        }
    }

    for tc_ast in &stmt.constraints {
        match tc_ast {
            TableConstraint::PrimaryKey(cols) => {
                for col in cols {
                    tc.add(Constraint::NotNull { column: col.clone() });
                }
                tc.add(Constraint::Unique { columns: cols.clone() });
            }
            TableConstraint::Unique(cols) => {
                tc.add(Constraint::Unique { columns: cols.clone() });
            }
        }
    }

    tc
}

// ── 測試 ─────────────────────────────────────────────────────────────────

#[cfg(test)]
mod tests {
    use super::*;
    use crate::table::schema::{Column, DataType, Schema};

    fn schema() -> Schema {
        Schema::new(vec![
            Column::new("id",    DataType::Integer),
            Column::new("email", DataType::Text),
            Column::new("age",   DataType::Integer),
        ])
    }

    fn row(id: i64, email: &str, age: i64) -> Row {
        Row::new(vec![Value::Integer(id), Value::Text(email.into()), Value::Integer(age)])
    }

    fn null_row() -> Row {
        Row::new(vec![Value::Integer(1), Value::Null, Value::Integer(20)])
    }

    #[test]
    fn not_null_passes() {
        let schema = schema();
        let mut tc = TableConstraints::new();
        tc.add(Constraint::NotNull { column: "email".into() });
        let r = row(1, "alice@example.com", 30);
        assert!(check_row(&r, &schema, &tc, &[]).is_ok());
    }

    #[test]
    fn not_null_fails() {
        let schema = schema();
        let mut tc = TableConstraints::new();
        tc.add(Constraint::NotNull { column: "email".into() });
        assert!(check_row(&null_row(), &schema, &tc, &[]).is_err());
    }

    #[test]
    fn unique_passes() {
        let schema = schema();
        let mut tc = TableConstraints::new();
        tc.add(Constraint::Unique { columns: vec!["email".into()] });
        let existing = vec![row(1, "alice@example.com", 30)];
        let new_row  = row(2, "bob@example.com", 25);
        assert!(check_row(&new_row, &schema, &tc, &existing).is_ok());
    }

    #[test]
    fn unique_fails_on_duplicate() {
        let schema = schema();
        let mut tc = TableConstraints::new();
        tc.add(Constraint::Unique { columns: vec!["email".into()] });
        let existing = vec![row(1, "alice@example.com", 30)];
        let dup_row  = row(2, "alice@example.com", 25); // 重複 email
        assert!(check_row(&dup_row, &schema, &tc, &existing).is_err());
    }

    #[test]
    fn unique_allows_multiple_nulls() {
        let schema = schema();
        let mut tc = TableConstraints::new();
        tc.add(Constraint::Unique { columns: vec!["email".into()] });
        let existing = vec![null_row()];
        let another_null = null_row();
        // 兩個 NULL 不違反 UNIQUE（SQLite 行為）
        assert!(check_row(&another_null, &schema, &tc, &existing).is_ok());
    }

    #[test]
    fn composite_unique() {
        let schema = schema();
        let mut tc = TableConstraints::new();
        tc.add(Constraint::Unique { columns: vec!["id".into(), "email".into()] });
        let existing = vec![row(1, "alice@example.com", 30)];
        // 同 id 不同 email → OK
        let r2 = row(1, "bob@example.com", 25);
        assert!(check_row(&r2, &schema, &tc, &existing).is_ok());
        // 完全相同 → Err
        let dup = row(1, "alice@example.com", 99);
        assert!(check_row(&dup, &schema, &tc, &existing).is_err());
    }

    #[test]
    fn from_ast_not_null() {
        use crate::parser::parse;
        let stmts = parse("CREATE TABLE t (id INTEGER NOT NULL, name TEXT)").unwrap();
        if let crate::parser::ast::Statement::CreateTable(stmt) = &stmts[0] {
            let tc = constraints_from_ast(stmt);
            assert!(tc.constraints.iter().any(|c| matches!(c, Constraint::NotNull { column } if column == "id")));
        }
    }

    #[test]
    fn from_ast_unique() {
        use crate::parser::parse;
        let stmts = parse("CREATE TABLE t (id INTEGER, email TEXT UNIQUE)").unwrap();
        if let crate::parser::ast::Statement::CreateTable(stmt) = &stmts[0] {
            let tc = constraints_from_ast(stmt);
            assert!(tc.constraints.iter().any(|c| matches!(c, Constraint::Unique { .. })));
        }
    }
}