Skip to main content

fraiseql_cli/commands/
generate_views.rs

1//! Generate DDL for Arrow views (va_*, tv_*, ta_*)
2//!
3//! This command generates SQL DDL statements for creating Arrow-optimized views
4//! from a compiled FraiseQL schema. It supports multiple view types:
5//!
6//! - `va_*` (Vector Arrow views) - For vector search and analytics
7//! - `tv_*` (Table Vector views) - For materialized table vectors
8//! - `ta_*` (Table Arrow views) - For Arrow Flight table streaming
9//!
10//! The command validates the schema, entity, and view configuration before generation.
11
12use std::{fs, path::Path};
13
14use anyhow::{Context, Result};
15use fraiseql_core::schema::CompiledSchema;
16
17use crate::output::OutputFormatter;
18
19/// Refresh strategy for view updates
20#[derive(Debug, Clone, Copy, PartialEq, Eq)]
21#[non_exhaustive]
22pub enum RefreshStrategy {
23    /// Update via database triggers on fact table changes
24    TriggerBased,
25    /// Update on a scheduled interval
26    Scheduled,
27}
28
29impl RefreshStrategy {
30    /// Parse from string
31    ///
32    /// # Errors
33    ///
34    /// Returns an error if the string does not match a known refresh strategy.
35    pub fn parse(s: &str) -> std::result::Result<Self, String> {
36        match s.to_lowercase().as_str() {
37            "trigger-based" | "trigger" => Ok(Self::TriggerBased),
38            "scheduled" => Ok(Self::Scheduled),
39            _ => Err(format!("Invalid refresh strategy '{s}', expected: trigger-based, scheduled")),
40        }
41    }
42}
43
44impl std::fmt::Display for RefreshStrategy {
45    fn fmt(&self, f: &mut std::fmt::Formatter<'_>) -> std::fmt::Result {
46        match self {
47            Self::TriggerBased => write!(f, "trigger-based"),
48            Self::Scheduled => write!(f, "scheduled"),
49        }
50    }
51}
52
53/// Configuration for view generation
54#[derive(Debug, Clone)]
55pub struct GenerateViewsConfig {
56    /// Path to schema.json file
57    pub schema_path: String,
58    /// Entity name (e.g., "User", "Order")
59    pub entity: String,
60    /// View name (e.g., "tv_user_profile", "ta_orders")
61    pub view: String,
62    /// Refresh strategy for view updates
63    pub refresh_strategy: RefreshStrategy,
64    /// Output file path (or None for stdout)
65    pub output: Option<String>,
66    /// Include helper/composition views
67    pub include_composition_views: bool,
68    /// Include monitoring functions (performance tracking, etc.)
69    pub include_monitoring: bool,
70    /// Validate only, don't write file
71    pub validate_only: bool,
72    /// Show generation steps
73    pub verbose: bool,
74}
75
76/// Run the generate-views command
77///
78/// # Arguments
79///
80/// * `config` - Generation configuration
81///
82/// # Errors
83///
84/// Returns error if:
85/// - Schema file doesn't exist or can't be read
86/// - JSON parsing fails
87/// - Entity doesn't exist in schema
88/// - View name validation fails
89/// - `--validate` is given and the generated DDL fails to execute against `PostgreSQL` (or no
90///   `DATABASE_URL` is available to execute it)
91/// - Output file can't be written
92pub async fn run(config: GenerateViewsConfig, formatter: &OutputFormatter) -> Result<()> {
93    if config.verbose {
94        formatter.progress("Generating views...");
95        formatter.progress(&format!("   Schema: {}", config.schema_path));
96        formatter.progress(&format!("   Entity: {}", config.entity));
97        formatter.progress(&format!("   View: {}", config.view));
98        formatter.progress(&format!("   Refresh strategy: {}", config.refresh_strategy));
99    }
100
101    // 1. Load schema
102    let schema_path = Path::new(&config.schema_path);
103    if !schema_path.exists() {
104        anyhow::bail!("Schema file not found: {}", config.schema_path);
105    }
106
107    let schema_json = fs::read_to_string(schema_path).context("Failed to read schema.json")?;
108
109    // 2. Parse compiled schema
110    if config.verbose {
111        formatter.progress("   ok: Reading schema...");
112    }
113    let schema =
114        CompiledSchema::from_json(&schema_json, false).context("Failed to parse schema.json")?;
115
116    // 3. Validate entity exists in schema
117    if config.verbose {
118        formatter.progress("   ok: Validating entity...");
119    }
120    let sql_source = resolve_entity_sql_source(&schema, &config.entity)?;
121
122    // 4. Validate view name
123    if config.verbose {
124        formatter.progress("   ok: Validating view name...");
125    }
126    let view_type = validate_view_name(&config.view)?;
127
128    if config.verbose {
129        formatter.progress(&format!("   ok: View type: {view_type}"));
130    }
131
132    // 5. Generate SQL DDL
133    if config.verbose {
134        formatter.progress("   ok: Generating SQL DDL...");
135    }
136    let sql = generate_view_sql(
137        &config.entity,
138        &sql_source,
139        &config.view,
140        view_type,
141        config.refresh_strategy,
142        config.include_composition_views,
143        config.include_monitoring,
144    );
145
146    // 6. If validate-only, execute the DDL for real and stop here. A validator that inspects
147    //    nothing cannot fail — the previous implementation printed "View DDL is valid" for files
148    //    PostgreSQL rejected (#821).
149    if config.validate_only {
150        validate_ddl_against_database(&sql).await?;
151        println!("✓ View DDL is valid (executed against PostgreSQL, rolled back)");
152        println!("  Entity: {}", config.entity);
153        println!("  View: {}", config.view);
154        println!("  Type: {view_type}");
155        println!("  Refresh strategy: {}", config.refresh_strategy);
156        println!("  Lines: {}", sql.lines().count());
157        return Ok(());
158    }
159
160    // 7. Write output
161    if config.verbose {
162        formatter.progress("   ok: Writing output...");
163    }
164    let output_path = config.output.unwrap_or_else(|| format!("{}.sql", config.view));
165
166    fs::write(&output_path, sql.clone()).context("Failed to write output file")?;
167
168    // 8. Success message
169    println!("✓ View DDL generated successfully");
170    println!("  Entity: {}", config.entity);
171    println!("  View: {}", config.view);
172    println!("  Type: {view_type}");
173    println!("  Output: {output_path}");
174    println!("  Lines: {}", sql.lines().count());
175
176    if config.include_composition_views {
177        println!("  ✓ Includes composition views");
178    }
179
180    if config.include_monitoring {
181        println!("  ✓ Includes monitoring functions");
182    }
183
184    if config.verbose {
185        formatter.progress("\nGenerated SQL preview (first 5 lines):");
186        for line in sql.lines().take(5) {
187            formatter.progress(&format!("  {line}"));
188        }
189    }
190
191    Ok(())
192}
193
194/// Execute the generated DDL inside a transaction that is always rolled back.
195///
196/// This is what makes `--validate` honest: the statements run against a real
197/// `PostgreSQL` (the same engine that will execute the file), so a syntax
198/// error, a missing source relation, or a column mismatch fails the command.
199/// Nothing is committed. Requires `DATABASE_URL`, like `fraiseql setup`.
200///
201/// # Errors
202///
203/// Returns an error when `DATABASE_URL` is unset, the connection fails, or any
204/// statement in the generated DDL is rejected by the database.
205async fn validate_ddl_against_database(sql: &str) -> Result<()> {
206    let db_url = std::env::var("DATABASE_URL").map_err(|_| {
207        anyhow::anyhow!(
208            "--validate executes the generated DDL against PostgreSQL and needs DATABASE_URL \
209             (everything runs in a rolled-back transaction; a scratch database is fine)"
210        )
211    })?;
212    let (mut client, connection) = tokio_postgres::connect(&db_url, tokio_postgres::NoTls)
213        .await
214        .context("--validate: failed to connect to DATABASE_URL")?;
215    tokio::spawn(async move {
216        let _ = connection.await;
217    });
218
219    let txn = client.transaction().await.context("--validate: failed to open transaction")?;
220    txn.batch_execute(sql)
221        .await
222        .context("generated DDL was rejected by PostgreSQL — the file is NOT valid")?;
223    txn.rollback().await.context("--validate: rollback failed")?;
224    Ok(())
225}
226
227/// Look up an entity in the schema and return its SQL source table/view name.
228///
229/// # Errors
230///
231/// Returns an error if the entity does not exist in the schema.
232fn resolve_entity_sql_source(schema: &CompiledSchema, entity: &str) -> Result<String> {
233    if let Some(type_def) = schema.types.iter().find(|t| t.name == entity) {
234        Ok(type_def.sql_source.as_str().to_string())
235    } else {
236        let available = schema.types.iter().map(|t| t.name.as_str()).collect::<Vec<_>>().join(", ");
237        anyhow::bail!("Entity '{entity}' not found in schema. Available types: {available}")
238    }
239}
240
241/// Determine view type from view name and validate naming convention
242///
243/// Valid prefixes:
244/// - `va_` - Vector Arrow view
245/// - `tv_` - Table Vector view
246/// - `ta_` - Table Arrow view
247pub(crate) fn validate_view_name(view_name: &str) -> Result<&'static str> {
248    if view_name.starts_with("va_") {
249        Ok("Vector Arrow (va_)")
250    } else if view_name.starts_with("tv_") {
251        Ok("Table Vector (tv_)")
252    } else if view_name.starts_with("ta_") {
253        Ok("Table Arrow (ta_)")
254    } else {
255        anyhow::bail!("Invalid view name '{view_name}'. Must start with va_, tv_, or ta_")
256    }
257}
258
259/// Generate SQL DDL for the view
260///
261/// # Arguments
262///
263/// * `entity` - Entity/type name from schema
264/// * `sql_source` - Actual SQL table/view name from the schema (e.g., `v_user`)
265/// * `view_name` - Full view name (e.g., "tv_user_profile")
266/// * `view_type` - View type string for documentation
267/// * `refresh_strategy` - How the view is kept up-to-date
268/// * `include_composition_views` - Whether to include helper views
269/// * `include_monitoring` - Whether to include monitoring functions
270pub(crate) fn generate_view_sql(
271    entity: &str,
272    sql_source: &str,
273    view_name: &str,
274    view_type: &str,
275    refresh_strategy: RefreshStrategy,
276    include_composition_views: bool,
277    include_monitoring: bool,
278) -> String {
279    let mut sql = String::new();
280
281    // Header
282    sql.push_str("-- Auto-generated Arrow view DDL\n");
283    sql.push_str(&format!("-- Entity: {entity}\n"));
284    sql.push_str(&format!("-- View: {view_name}\n"));
285    sql.push_str(&format!("-- Type: {view_type}\n"));
286    sql.push_str(&format!("-- Refresh strategy: {refresh_strategy}\n"));
287    sql.push_str("-- Generated by: fraiseql generate-views\n\n");
288
289    // Drop existing view if it exists. The drop must match the object class:
290    // tv_ targets are MATERIALIZED views, and a plain `DROP VIEW` on one errors
291    // ("… is not a view"), making the file non-idempotent (#821 adjunct).
292    if view_name.starts_with("tv_") {
293        sql.push_str(&format!("DROP MATERIALIZED VIEW IF EXISTS {view_name} CASCADE;\n\n"));
294    } else {
295        sql.push_str(&format!("DROP VIEW IF EXISTS {view_name} CASCADE;\n\n"));
296    }
297
298    // Main view definition.
299    // `validate_view_name` guarantees the prefix is one of va_/tv_/ta_ before
300    // this function is called, so the wildcard arm is unreachable.
301    #[allow(clippy::unreachable)]
302    // Reason: `validate_view_name` (called upstream of this function) restricts
303    // the prefix to `va`/`tv`/`ta`; any other value would be a programmer
304    // error, not user input.
305    match view_name.split('_').next() {
306        Some("va") => {
307            generate_vector_arrow_view(&mut sql, entity, sql_source, view_name);
308        },
309        Some("tv") => {
310            generate_table_vector_view(&mut sql, entity, sql_source, view_name);
311        },
312        Some("ta") => {
313            generate_table_arrow_view(&mut sql, entity, sql_source, view_name);
314        },
315        _ => unreachable!("view name validated by validate_view_name before generate_view_sql"),
316    }
317
318    // Composition views (optional)
319    if include_composition_views {
320        sql.push_str("\n-- Composition views\n");
321        generate_composition_views(&mut sql, view_name);
322    }
323
324    // Monitoring functions (optional)
325    if include_monitoring {
326        sql.push_str("\n-- Monitoring functions\n");
327        generate_monitoring_functions(&mut sql, view_name);
328    }
329
330    sql
331}
332
333/// Generate a Vector Arrow (va_*) view for vector search and analytics
334fn generate_vector_arrow_view(sql: &mut String, entity: &str, sql_source: &str, view_name: &str) {
335    sql.push_str(&format!("CREATE VIEW {view_name} AS\n"));
336    sql.push_str("SELECT\n");
337    sql.push_str("    id,\n");
338    sql.push_str(&format!("    -- {entity} entity fields\n"));
339    sql.push_str("    created_at,\n");
340    sql.push_str("    updated_at\n");
341    sql.push_str(&format!("FROM {sql_source}\n"));
342    sql.push_str("WHERE archived_at IS NULL;\n");
343}
344
345/// Generate a Table Vector (tv_*) view for materialized table vectors
346fn generate_table_vector_view(sql: &mut String, entity: &str, sql_source: &str, view_name: &str) {
347    sql.push_str(&format!("CREATE MATERIALIZED VIEW {view_name} AS\n"));
348    sql.push_str("SELECT\n");
349    sql.push_str("    id,\n");
350    sql.push_str(&format!("    -- {entity} entity vector representation\n"));
351    sql.push_str("    CURRENT_TIMESTAMP as materialized_at\n");
352    sql.push_str(&format!("FROM {sql_source}\n"));
353    sql.push_str("WHERE archived_at IS NULL;\n");
354    sql.push('\n');
355    let base_name = view_name.trim_start_matches("tv_");
356    sql.push_str(&format!("CREATE INDEX idx_{base_name}_id ON {view_name} (id);\n"));
357}
358
359/// Generate a Table Arrow (ta_*) view for Arrow Flight streaming
360fn generate_table_arrow_view(sql: &mut String, entity: &str, sql_source: &str, view_name: &str) {
361    sql.push_str(&format!("CREATE VIEW {view_name} AS\n"));
362    sql.push_str("SELECT\n");
363    sql.push_str("    id,\n");
364    sql.push_str(&format!("    -- {entity} entity fields optimized for Arrow\n"));
365    sql.push_str("    created_at,\n");
366    sql.push_str("    updated_at\n");
367    sql.push_str(&format!("FROM {sql_source}\n"));
368    sql.push_str("WHERE archived_at IS NULL\n");
369    sql.push_str("ORDER BY id;\n");
370}
371
372/// Generate helper composition views.
373///
374/// They read the **generated** view (not the entity source), so the main
375/// view's `DROP … CASCADE` removes them on re-run and the whole file stays
376/// idempotent. A previous version emitted the literal placeholder `{}` for the
377/// relation name — both statements were PostgreSQL syntax errors on every
378/// invocation, and `--validate` reported the file valid anyway (#821).
379fn generate_composition_views(sql: &mut String, view_name: &str) {
380    let base_name = view_name
381        .trim_start_matches("va_")
382        .trim_start_matches("tv_")
383        .trim_start_matches("ta_");
384
385    // The tv_ view exposes `materialized_at` (see `generate_table_vector_view`);
386    // va_/ta_ views project the source's `updated_at`.
387    let ts_column = if view_name.starts_with("tv_") {
388        "materialized_at"
389    } else {
390        "updated_at"
391    };
392
393    // Recent items view
394    sql.push_str(&format!("CREATE VIEW {base_name}_recent AS\n"));
395    sql.push_str(&format!("SELECT * FROM {view_name}\n"));
396    sql.push_str(&format!("WHERE {ts_column} > NOW() - INTERVAL '7 days'\n"));
397    sql.push_str(&format!("ORDER BY {ts_column} DESC;\n\n"));
398
399    // Count view
400    sql.push_str(&format!("CREATE VIEW {base_name}_count AS\n"));
401    sql.push_str(&format!("SELECT COUNT(*) as total FROM {view_name};\n"));
402}
403
404/// Generate monitoring functions for the view.
405///
406/// STABLE, not IMMUTABLE: the function reads a relation, and IMMUTABLE would
407/// license the planner to constant-fold the row count inside a cached or
408/// prepared plan (#821 adjunct).
409fn generate_monitoring_functions(sql: &mut String, view_name: &str) {
410    let func_name = format!("monitor_{view_name}");
411
412    sql.push_str(&format!("CREATE OR REPLACE FUNCTION {func_name}()\n"));
413    sql.push_str("RETURNS TABLE (\n");
414    sql.push_str("    metric_name TEXT,\n");
415    sql.push_str("    metric_value BIGINT\n");
416    sql.push_str(") AS $$\n");
417    sql.push_str("BEGIN\n");
418    sql.push_str("    RETURN QUERY\n");
419    sql.push_str(&format!("    SELECT 'row_count'::TEXT, COUNT(*)::BIGINT FROM {view_name};\n"));
420    sql.push_str("END;\n");
421    sql.push_str("$$ LANGUAGE plpgsql STABLE;\n");
422}