oxide_sql_core/migrations/dialect/
sqlite.rs

1//! SQLite dialect for migrations.
2
3use super::MigrationDialect;
4use crate::ast::DataType;
5use crate::migrations::operation::{
6    AlterColumnChange, AlterColumnOp, DropIndexOp, RenameColumnOp, RenameTableOp,
7};
8
9/// SQLite dialect for migration SQL generation.
10#[derive(Debug, Clone, Copy, Default)]
11pub struct SqliteDialect;
12
13impl SqliteDialect {
14    /// Creates a new SQLite dialect.
15    #[must_use]
16    pub const fn new() -> Self {
17        Self
18    }
19}
20
21impl MigrationDialect for SqliteDialect {
22    fn name(&self) -> &'static str {
23        "sqlite"
24    }
25
26    fn map_data_type(&self, dt: &DataType) -> String {
27        // SQLite has dynamic typing with type affinity
28        match dt {
29            DataType::Smallint | DataType::Integer | DataType::Bigint => "INTEGER".to_string(),
30            DataType::Real | DataType::Double => "REAL".to_string(),
31            DataType::Decimal { .. } | DataType::Numeric { .. } => "REAL".to_string(),
32            DataType::Char(_) | DataType::Varchar(_) | DataType::Text => "TEXT".to_string(),
33            DataType::Blob | DataType::Binary(_) | DataType::Varbinary(_) => "BLOB".to_string(),
34            DataType::Date | DataType::Time | DataType::Timestamp | DataType::Datetime => {
35                "TEXT".to_string()
36            }
37            DataType::Boolean => "INTEGER".to_string(), // SQLite has no bool, use 0/1
38            DataType::Custom(name) => name.clone(),
39        }
40    }
41
42    fn autoincrement_keyword(&self) -> String {
43        " AUTOINCREMENT".to_string()
44    }
45
46    fn rename_table(&self, op: &RenameTableOp) -> String {
47        format!(
48            "ALTER TABLE {} RENAME TO {}",
49            self.quote_identifier(&op.old_name),
50            self.quote_identifier(&op.new_name)
51        )
52    }
53
54    fn rename_column(&self, op: &RenameColumnOp) -> String {
55        // SQLite 3.25.0+ supports RENAME COLUMN
56        format!(
57            "ALTER TABLE {} RENAME COLUMN {} TO {}",
58            self.quote_identifier(&op.table),
59            self.quote_identifier(&op.old_name),
60            self.quote_identifier(&op.new_name)
61        )
62    }
63
64    fn alter_column(&self, op: &AlterColumnOp) -> String {
65        // SQLite has very limited ALTER TABLE support.
66        // Most column alterations require recreating the table.
67        // We generate a comment noting this limitation.
68        match &op.change {
69            AlterColumnChange::SetDataType(_) => {
70                format!(
71                    "-- SQLite does not support ALTER COLUMN TYPE directly for {}.{}; \
72                     table recreation required",
73                    op.table, op.column
74                )
75            }
76            AlterColumnChange::SetNullable(_) => {
77                format!(
78                    "-- SQLite does not support ALTER COLUMN NULL/NOT NULL directly for {}.{}; \
79                     table recreation required",
80                    op.table, op.column
81                )
82            }
83            AlterColumnChange::SetDefault(default) => {
84                // SQLite doesn't support ALTER COLUMN SET DEFAULT either
85                format!(
86                    "-- SQLite does not support ALTER COLUMN SET DEFAULT directly for {}.{}; \
87                     would set to: {}",
88                    op.table,
89                    op.column,
90                    self.render_default(default)
91                )
92            }
93            AlterColumnChange::DropDefault => {
94                format!(
95                    "-- SQLite does not support ALTER COLUMN DROP DEFAULT directly for {}.{}; \
96                     table recreation required",
97                    op.table, op.column
98                )
99            }
100        }
101    }
102
103    fn drop_index(&self, op: &DropIndexOp) -> String {
104        let mut sql = String::from("DROP INDEX ");
105        if op.if_exists {
106            sql.push_str("IF EXISTS ");
107        }
108        // SQLite index names are global, not per-table
109        sql.push_str(&self.quote_identifier(&op.name));
110        sql
111    }
112
113    fn drop_foreign_key(&self, op: &super::super::operation::DropForeignKeyOp) -> String {
114        // SQLite does not support DROP CONSTRAINT; requires table recreation
115        format!(
116            "-- SQLite does not support DROP CONSTRAINT; \
117             table recreation required to remove foreign key {} from {}",
118            op.name, op.table
119        )
120    }
121}
122
123#[cfg(test)]
124mod tests {
125    use super::*;
126    use crate::migrations::column_builder::{bigint, boolean, timestamp, varchar};
127    use crate::migrations::operation::{DropTableOp, Operation};
128    use crate::migrations::table_builder::CreateTableBuilder;
129
130    #[test]
131    fn test_sqlite_data_types() {
132        let dialect = SqliteDialect::new();
133        assert_eq!(dialect.map_data_type(&DataType::Integer), "INTEGER");
134        assert_eq!(dialect.map_data_type(&DataType::Bigint), "INTEGER");
135        assert_eq!(dialect.map_data_type(&DataType::Text), "TEXT");
136        assert_eq!(dialect.map_data_type(&DataType::Varchar(Some(255))), "TEXT");
137        assert_eq!(dialect.map_data_type(&DataType::Blob), "BLOB");
138        assert_eq!(dialect.map_data_type(&DataType::Boolean), "INTEGER");
139        assert_eq!(dialect.map_data_type(&DataType::Timestamp), "TEXT");
140    }
141
142    #[test]
143    fn test_create_table_sql() {
144        let dialect = SqliteDialect::new();
145        let op = CreateTableBuilder::new()
146            .name("users")
147            .column(bigint("id").primary_key().autoincrement().build())
148            .column(varchar("username", 255).not_null().unique().build())
149            .column(varchar("email", 255).build())
150            .column(
151                timestamp("created_at")
152                    .not_null()
153                    .default_expr("CURRENT_TIMESTAMP")
154                    .build(),
155            )
156            .build();
157
158        let sql = dialect.create_table(&op);
159        assert!(sql.contains("CREATE TABLE \"users\""));
160        assert!(sql.contains("\"id\" INTEGER PRIMARY KEY AUTOINCREMENT"));
161        assert!(sql.contains("\"username\" TEXT NOT NULL UNIQUE"));
162        assert!(sql.contains("DEFAULT CURRENT_TIMESTAMP"));
163    }
164
165    #[test]
166    fn test_drop_table_sql() {
167        let dialect = SqliteDialect::new();
168
169        let op = DropTableOp {
170            name: "users".to_string(),
171            if_exists: false,
172            cascade: false,
173        };
174        assert_eq!(dialect.drop_table(&op), "DROP TABLE \"users\"");
175
176        let op = DropTableOp {
177            name: "users".to_string(),
178            if_exists: true,
179            cascade: false,
180        };
181        assert_eq!(dialect.drop_table(&op), "DROP TABLE IF EXISTS \"users\"");
182    }
183
184    #[test]
185    fn test_add_column_sql() {
186        let dialect = SqliteDialect::new();
187        let op = Operation::add_column(
188            "users",
189            boolean("active").not_null().default_bool(true).build(),
190        );
191
192        if let Operation::AddColumn(add_op) = op {
193            let sql = dialect.add_column(&add_op);
194            assert!(sql.contains("ALTER TABLE \"users\" ADD COLUMN"));
195            assert!(sql.contains("\"active\" INTEGER NOT NULL DEFAULT TRUE"));
196        }
197    }
198
199    #[test]
200    fn test_rename_table_sql() {
201        let dialect = SqliteDialect::new();
202        let op = RenameTableOp {
203            old_name: "old_users".to_string(),
204            new_name: "users".to_string(),
205        };
206        assert_eq!(
207            dialect.rename_table(&op),
208            "ALTER TABLE \"old_users\" RENAME TO \"users\""
209        );
210    }
211
212    #[test]
213    fn test_rename_column_sql() {
214        let dialect = SqliteDialect::new();
215        let op = RenameColumnOp {
216            table: "users".to_string(),
217            old_name: "name".to_string(),
218            new_name: "full_name".to_string(),
219        };
220        assert_eq!(
221            dialect.rename_column(&op),
222            "ALTER TABLE \"users\" RENAME COLUMN \"name\" TO \"full_name\""
223        );
224    }
225}
oxide_sql_core/migrations/dialect/sqlite.rs

oxide_sql_core/migrations/dialect/
sqlite.rs
