use rustf_schema::types::{
AutoGenerate, Field, FieldType, ForeignKeyAction, Table, TypeParam,
};
use rustf_schema::Schema;
#[derive(Clone, Copy, Debug, PartialEq, Eq)]
pub enum Dialect {
Postgres,
MySql,
Sqlite,
}
pub fn generate_sql_schema(schema: &Schema, dialect: Dialect) -> anyhow::Result<String> {
let mut sql = String::new();
sql.push_str("-- Generated SQL schema\n");
if let Some(meta) = &schema.meta {
sql.push_str(&format!(
"-- Database: {} v{}\n",
meta.database_name, meta.version
));
if let Some(desc) = &meta.description {
sql.push_str(&format!("-- {}\n", desc));
}
}
sql.push_str(&format!("-- Dialect: {}\n", dialect.name()));
sql.push_str("-- DO NOT EDIT - Auto-generated from schema\n\n");
let mut entries: Vec<(&String, &Table)> = schema.tables.iter().collect();
entries.sort_by(|(a, _), (b, _)| a.cmp(b));
let (views, base_tables): (Vec<_>, Vec<_>) = entries
.into_iter()
.partition(|(_, t)| is_view_type(t.element_type.as_deref()));
let mut foreign_keys = String::new();
for (_name, table) in &base_tables {
render_table(table, dialect, &mut sql, &mut foreign_keys);
}
if !foreign_keys.is_empty() {
sql.push_str("-- Foreign keys\n");
sql.push_str(&foreign_keys);
}
if !views.is_empty() {
sql.push_str("-- Views\n");
for (_name, table) in order_views(&views) {
match table.element_type.as_deref() {
Some("materialized_view") => render_view(table, &mut sql, true),
_ => render_view(table, &mut sql, false),
}
}
}
Ok(sql)
}
fn is_view_type(element_type: Option<&str>) -> bool {
matches!(element_type, Some("view") | Some("materialized_view"))
}
fn order_views<'a>(views: &[(&'a String, &'a Table)]) -> Vec<(&'a String, &'a Table)> {
let names: Vec<&str> = views.iter().map(|(_, t)| t.table.as_str()).collect();
let deps: Vec<Vec<usize>> = views
.iter()
.map(|(_, t)| {
let body = t
.view
.as_ref()
.and_then(|v| v.sql.as_deref())
.unwrap_or("")
.to_ascii_lowercase();
names
.iter()
.enumerate()
.filter(|(_, dep_name)| {
t.table != **dep_name && body_references(&body, &dep_name.to_ascii_lowercase())
})
.map(|(j, _)| j)
.collect()
})
.collect();
let mut emitted = vec![false; views.len()];
let mut ordered = Vec::with_capacity(views.len());
while ordered.len() < views.len() {
let next = (0..views.len()).find(|&i| {
!emitted[i] && deps[i].iter().all(|&d| emitted[d])
});
let pick = match next {
Some(i) => i,
None => (0..views.len()).find(|&i| !emitted[i]).unwrap(),
};
emitted[pick] = true;
ordered.push(views[pick]);
}
ordered
}
fn body_references(body: &str, name: &str) -> bool {
if name.is_empty() {
return false;
}
let mut from = 0;
while let Some(pos) = body[from..].find(name) {
let start = from + pos;
let end = start + name.len();
let before_ok = start == 0
|| !body.as_bytes()[start - 1].is_ascii_alphanumeric() && body.as_bytes()[start - 1] != b'_';
let after_ok = end == body.len()
|| !body.as_bytes()[end].is_ascii_alphanumeric() && body.as_bytes()[end] != b'_';
if before_ok && after_ok {
return true;
}
from = start + name.len();
}
false
}
impl Dialect {
fn name(&self) -> &'static str {
match self {
Dialect::Postgres => "PostgreSQL",
Dialect::MySql => "MySQL",
Dialect::Sqlite => "SQLite",
}
}
}
fn render_table(table: &Table, dialect: Dialect, sql: &mut String, foreign_keys: &mut String) {
sql.push_str(&format!("-- Table: {}\n", table.table));
if let Some(desc) = &table.description {
sql.push_str(&format!("-- {}\n", desc));
}
sql.push_str(&format!("CREATE TABLE {} (\n", table.table));
let mut fields: Vec<(&String, &Field)> = table.fields.iter().collect();
fields.sort_by(|(an, a), (bn, b)| {
let a_pk = a.constraints.primary_key == Some(true);
let b_pk = b.constraints.primary_key == Some(true);
b_pk.cmp(&a_pk).then_with(|| an.cmp(bn))
});
let mut defs = Vec::new();
for (field_name, field) in &fields {
defs.push(render_column(field_name, field, dialect));
if let Some(fk) = &field.constraints.foreign_key {
foreign_keys.push_str(&render_foreign_key(&table.table, field_name, fk, field, dialect));
}
}
sql.push_str(&defs.join(",\n"));
sql.push_str("\n);\n\n");
}
fn render_view(table: &Table, sql: &mut String, materialized: bool) {
let kind = if materialized {
"MATERIALIZED VIEW"
} else {
"VIEW"
};
sql.push_str(&format!("-- {}: {}\n", kind, table.table));
if let Some(desc) = &table.description {
sql.push_str(&format!("-- {}\n", desc));
}
let body = table.view.as_ref().and_then(|v| v.sql.as_deref());
let body = match body {
Some(b) if !b.trim().is_empty() => b.trim(),
_ => {
sql.push_str(&format!(
"-- ERROR: view '{}' has no `view.sql` body; cannot generate DDL.\n\n",
table.table
));
return;
}
};
let or_replace = !materialized
&& table.view.as_ref().map(|v| v.or_replace).unwrap_or(false);
let create = if or_replace {
"CREATE OR REPLACE"
} else {
"CREATE"
};
sql.push_str(&format!(
"{} {} {} AS\n{};\n\n",
create, kind, table.table, body
));
}
fn render_column(name: &str, field: &Field, dialect: Dialect) -> String {
let is_pk = field.constraints.primary_key == Some(true);
let is_auto = matches!(
&field.constraints.auto,
Some(AutoGenerate::Boolean(true)) | Some(AutoGenerate::Type(_))
);
if dialect == Dialect::Sqlite && is_pk && is_auto {
return format!(" {} INTEGER PRIMARY KEY AUTOINCREMENT", name);
}
let sql_type = sql_type(field, dialect, is_auto);
let mut def = format!(" {} {}", name, sql_type);
let not_null = field.constraints.required == Some(true)
|| field.constraints.nullable == Some(false)
|| is_pk;
if not_null {
def.push_str(" NOT NULL");
}
if is_auto && dialect == Dialect::MySql {
def.push_str(" AUTO_INCREMENT");
}
if is_pk {
def.push_str(" PRIMARY KEY");
}
if field.constraints.unique == Some(true) && !is_pk {
def.push_str(" UNIQUE");
}
if let Some(default) = &field.constraints.default {
if let Some(rendered) = render_default(default, dialect) {
def.push_str(&format!(" DEFAULT {}", rendered));
}
}
if let FieldType::Enum { values, .. } = &field.field_type {
if dialect != Dialect::MySql {
let list = values
.iter()
.map(|v| format!("'{}'", v))
.collect::<Vec<_>>()
.join(", ");
def.push_str(&format!(" CHECK ({} IN ({}))", name, list));
}
}
def
}
fn render_foreign_key(
table: &str,
column: &str,
fk: &str,
field: &Field,
dialect: Dialect,
) -> String {
let (ref_table, ref_col) = match fk.split_once('.') {
Some((t, c)) => (t, c),
None => (fk, "id"),
};
let mut stmt = format!(
"ALTER TABLE {} ADD FOREIGN KEY ({}) REFERENCES {} ({})",
table, column, ref_table, ref_col
);
if let Some(action) = &field.constraints.on_delete {
stmt.push_str(&format!(" ON DELETE {}", fk_action(action)));
}
if let Some(action) = &field.constraints.on_update {
stmt.push_str(&format!(" ON UPDATE {}", fk_action(action)));
}
stmt.push_str(";\n");
if dialect == Dialect::Sqlite {
return format!(
"-- NOTE: SQLite cannot add FKs via ALTER TABLE; declare inline instead.\n-- {}",
stmt
);
}
stmt
}
fn fk_action(action: &ForeignKeyAction) -> &'static str {
match action {
ForeignKeyAction::Cascade => "CASCADE",
ForeignKeyAction::Restrict => "RESTRICT",
ForeignKeyAction::SetNull => "SET NULL",
ForeignKeyAction::SetDefault => "SET DEFAULT",
ForeignKeyAction::NoAction => "NO ACTION",
}
}
fn render_default(value: &serde_json::Value, dialect: Dialect) -> Option<String> {
match value {
serde_json::Value::String(s) => Some(format!("'{}'", s.replace('\'', "''"))),
serde_json::Value::Number(n) => Some(n.to_string()),
serde_json::Value::Bool(b) => Some(match dialect {
Dialect::MySql => if *b { "1".into() } else { "0".into() },
_ => b.to_string(),
}),
_ => None,
}
}
fn sql_type(field: &Field, dialect: Dialect, is_auto: bool) -> String {
match &field.field_type {
FieldType::Simple(t) => simple_type(t, dialect, is_auto),
FieldType::Parameterized { base_type, params } => {
parameterized_type(base_type, params, dialect)
}
FieldType::Enum { values, .. } => match dialect {
Dialect::MySql => {
let list = values
.iter()
.map(|v| format!("'{}'", v))
.collect::<Vec<_>>()
.join(", ");
format!("ENUM({})", list)
}
_ => "TEXT".to_string(),
},
FieldType::Json { .. } => json_type(dialect),
}
}
fn simple_type(t: &str, dialect: Dialect, is_auto: bool) -> String {
let t = t.to_ascii_lowercase();
if is_auto && dialect == Dialect::Postgres {
return match t.as_str() {
"bigint" | "int8" => "BIGSERIAL".to_string(),
"smallint" | "int2" | "tinyint" => "SMALLSERIAL".to_string(),
_ => "SERIAL".to_string(),
};
}
match t.as_str() {
"tinyint" => pick(dialect, "TINYINT", "SMALLINT", "INTEGER"),
"smallint" | "int2" => pick(dialect, "SMALLINT", "SMALLINT", "INTEGER"),
"mediumint" => pick(dialect, "MEDIUMINT", "INTEGER", "INTEGER"),
"int" | "integer" | "int4" => pick(dialect, "INT", "INTEGER", "INTEGER"),
"bigint" | "int8" => pick(dialect, "BIGINT", "BIGINT", "INTEGER"),
"serial" => pick(dialect, "INT", "SERIAL", "INTEGER"),
"boolean" | "bool" => pick(dialect, "TINYINT(1)", "BOOLEAN", "BOOLEAN"),
"decimal" | "numeric" => "DECIMAL".to_string(),
"float" => pick(dialect, "FLOAT", "REAL", "REAL"),
"double" | "double precision" => pick(dialect, "DOUBLE", "DOUBLE PRECISION", "REAL"),
"string" | "text" | "mediumtext" | "longtext" | "tinytext" => "TEXT".to_string(),
"date" => "DATE".to_string(),
"time" => "TIME".to_string(),
"datetime" => pick(dialect, "DATETIME", "TIMESTAMP", "TIMESTAMP"),
"timestamp" => "TIMESTAMP".to_string(),
"json" => json_type(dialect),
"jsonb" => pick(dialect, "JSON", "JSONB", "TEXT"),
"uuid" => pick(dialect, "CHAR(36)", "UUID", "TEXT"),
"blob" | "binary" | "bytea" => pick(dialect, "BLOB", "BYTEA", "BLOB"),
other => {
let _ = other;
"TEXT".to_string()
}
}
}
fn parameterized_type(base: &str, params: &[TypeParam], dialect: Dialect) -> String {
match base.to_ascii_lowercase().as_str() {
"string" | "varchar" => match params.first() {
Some(TypeParam::Number(len)) => format!("VARCHAR({})", len),
_ => "VARCHAR(255)".to_string(),
},
"char" => match params.first() {
Some(TypeParam::Number(len)) => format!("CHAR({})", len),
_ => "CHAR".to_string(),
},
"decimal" | "numeric" => match (params.first(), params.get(1)) {
(Some(TypeParam::Number(p)), Some(TypeParam::Number(s))) => {
format!("DECIMAL({},{})", p, s)
}
(Some(TypeParam::Number(p)), None) => format!("DECIMAL({})", p),
_ => "DECIMAL".to_string(),
},
"tinyint" | "smallint" | "mediumint" | "int" | "integer" | "bigint" => {
simple_type(base, dialect, false)
}
_ => "TEXT".to_string(),
}
}
fn json_type(dialect: Dialect) -> String {
pick(dialect, "JSON", "JSONB", "TEXT")
}
fn pick(dialect: Dialect, mysql: &str, postgres: &str, sqlite: &str) -> String {
match dialect {
Dialect::MySql => mysql,
Dialect::Postgres => postgres,
Dialect::Sqlite => sqlite,
}
.to_string()
}
#[cfg(test)]
mod tests {
use super::*;
use rustf_schema::SchemaParser;
fn schema(yaml: &str) -> Schema {
Schema {
tables: SchemaParser::parse_yaml(yaml).expect("parse yaml"),
meta: None,
}
}
const YAML: &str = r#"
accounts:
table: accounts
version: 1
fields:
id:
type: mediumint
primary_key: true
auto: true
required: true
status:
type: enum
values: [active, closed]
required: true
owner_id:
type: mediumint
foreign_key: accounts.id
verified:
type: tinyint
"#;
#[test]
fn mysql_uses_native_types_and_enum() {
let sql = generate_sql_schema(&schema(YAML), Dialect::MySql).unwrap();
assert!(sql.contains("id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY"), "{sql}");
assert!(sql.contains("verified TINYINT"), "{sql}");
assert!(sql.contains("status ENUM('active', 'closed')"), "{sql}");
assert!(sql.contains("ALTER TABLE accounts ADD FOREIGN KEY (owner_id) REFERENCES accounts (id)"), "{sql}");
assert!(!sql.contains("column_name"), "{sql}");
}
#[test]
fn postgres_enum_check_uses_real_column_name() {
let sql = generate_sql_schema(&schema(YAML), Dialect::Postgres).unwrap();
assert!(sql.contains("id SERIAL"), "{sql}");
assert!(sql.contains("verified SMALLINT"), "{sql}");
assert!(sql.contains("status TEXT NOT NULL CHECK (status IN ('active', 'closed'))"), "{sql}");
assert!(!sql.contains("column_name"), "{sql}");
}
#[test]
fn sqlite_autoincrement_and_no_alter_fk() {
let sql = generate_sql_schema(&schema(YAML), Dialect::Sqlite).unwrap();
assert!(sql.contains("id INTEGER PRIMARY KEY AUTOINCREMENT"), "{sql}");
assert!(sql.contains("verified INTEGER"), "{sql}");
assert!(sql.contains("-- NOTE: SQLite cannot add FKs"), "{sql}");
}
const VIEW_YAML: &str = r#"
active_orders:
table: active_orders
element_type: view
version: 1
description: "Open orders joined to users."
view:
or_replace: true
sql: |
SELECT o.id, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'open'
fields:
id:
type: int
email:
type: string(255)
"#;
#[test]
fn view_emits_create_view_not_create_table() {
let sql = generate_sql_schema(&schema(VIEW_YAML), Dialect::Postgres).unwrap();
assert!(sql.contains("CREATE OR REPLACE VIEW active_orders AS"), "{sql}");
assert!(!sql.contains("CREATE TABLE active_orders"), "{sql}");
assert!(sql.contains("WHERE o.status = 'open'"), "{sql}");
}
#[test]
fn materialized_view_never_uses_or_replace() {
let yaml = VIEW_YAML.replace("element_type: view", "element_type: materialized_view");
let sql = generate_sql_schema(&schema(&yaml), Dialect::Postgres).unwrap();
assert!(sql.contains("CREATE MATERIALIZED VIEW active_orders AS"), "{sql}");
assert!(!sql.contains("OR REPLACE"), "{sql}");
}
#[test]
fn view_without_body_emits_error_comment_not_table() {
let yaml = r#"
broken_view:
table: broken_view
element_type: view
version: 1
fields:
id:
type: int
"#;
let sql = generate_sql_schema(&schema(yaml), Dialect::Postgres).unwrap();
assert!(sql.contains("ERROR: view 'broken_view' has no `view.sql`"), "{sql}");
assert!(!sql.contains("CREATE TABLE broken_view"), "{sql}");
}
#[test]
fn tables_emitted_before_views() {
let yaml = format!("{}{}", TABLE_FOR_VIEW, VIEW_YAML);
let sql = generate_sql_schema(&schema(&yaml), Dialect::Postgres).unwrap();
let table_pos = sql.find("CREATE TABLE orders").unwrap();
let view_pos = sql.find("VIEW active_orders").unwrap();
assert!(table_pos < view_pos, "table must precede view:\n{sql}");
}
#[test]
fn view_depending_on_view_is_ordered_after_it() {
let yaml = r#"
active_orders:
table: active_orders
element_type: view
version: 1
view:
sql: "SELECT id FROM orders WHERE status = 'open'"
vip_orders:
table: vip_orders
element_type: view
version: 1
view:
sql: "SELECT id FROM active_orders WHERE vip = true"
"#;
let sql = generate_sql_schema(&schema(yaml), Dialect::Postgres).unwrap();
let active = sql.find("VIEW active_orders").unwrap();
let vip = sql.find("VIEW vip_orders").unwrap();
assert!(active < vip, "dependency view must come first:\n{sql}");
}
const TABLE_FOR_VIEW: &str = r#"
orders:
table: orders
version: 1
fields:
id:
type: int
primary_key: true
auto: true
status:
type: string(20)
"#;
#[test]
fn columns_are_deterministically_ordered_pk_first() {
let sql = generate_sql_schema(&schema(YAML), Dialect::MySql).unwrap();
let id = sql.find("id MEDIUMINT").unwrap();
let owner = sql.find("owner_id").unwrap();
let status = sql.find("status ").unwrap();
assert!(id < owner && owner < status, "{sql}");
}
}