vibesql_executor/index_ddl/
create_index.rs

1//! CREATE INDEX statement execution
2
3use vibesql_ast::CreateIndexStmt;
4use vibesql_storage::{
5    index::{extract_mbr_from_sql_value, SpatialIndex, SpatialIndexEntry},
6    Database, SpatialIndexMetadata,
7};
8
9use crate::{errors::ExecutorError, privilege_checker::PrivilegeChecker};
10
11/// Executor for CREATE INDEX statements
12pub struct CreateIndexExecutor;
13
14impl CreateIndexExecutor {
15    /// Execute a CREATE INDEX statement
16    ///
17    /// # Arguments
18    ///
19    /// * `stmt` - The CREATE INDEX statement AST node
20    /// * `database` - The database to create the index in
21    ///
22    /// # Returns
23    ///
24    /// Success message or error
25    ///
26    /// # Examples
27    ///
28    /// ```
29    /// use vibesql_ast::{CreateIndexStmt, IndexColumn, OrderDirection};
30    /// use vibesql_executor::CreateIndexExecutor;
31    /// use vibesql_storage::Database;
32    ///
33    /// let mut db = Database::new();
34    /// // First create a table
35    /// // ... (table creation code) ...
36    ///
37    /// let stmt = CreateIndexStmt {
38    ///     index_name: "idx_users_email".to_string(),
39    ///     if_not_exists: false,
40    ///     table_name: "users".to_string(),
41    ///     index_type: vibesql_ast::IndexType::BTree { unique: false },
42    ///     columns: vec![IndexColumn {
43    ///         column_name: "email".to_string(),
44    ///         direction: OrderDirection::Asc,
45    ///         prefix_length: None,
46    ///     }],
47    /// };
48    ///
49    /// let result = CreateIndexExecutor::execute(&stmt, &mut db);
50    /// // assert!(result.is_ok());
51    /// ```
52    pub fn execute(
53        stmt: &CreateIndexStmt,
54        database: &mut Database,
55    ) -> Result<String, ExecutorError> {
56        // Parse qualified table name (schema.table or just table)
57        let (schema_name, table_name) =
58            if let Some((schema_part, table_part)) = stmt.table_name.split_once('.') {
59                (schema_part.to_string(), table_part.to_string())
60            } else {
61                (database.catalog.get_current_schema().to_string(), stmt.table_name.clone())
62            };
63
64        // Check CREATE privilege on the schema
65        PrivilegeChecker::check_create(database, &schema_name)?;
66
67        // Build fully qualified table name for catalog lookups
68        let qualified_table_name = format!("{}.{}", schema_name, table_name);
69
70        // Check if table exists
71        if !database.catalog.table_exists(&qualified_table_name) {
72            return Err(ExecutorError::TableNotFound(qualified_table_name.clone()));
73        }
74
75        // Get table schema to validate columns
76        let table_schema = database
77            .catalog
78            .get_table(&qualified_table_name)
79            .ok_or_else(|| ExecutorError::TableNotFound(qualified_table_name.clone()))?;
80
81        // Validate that all indexed columns exist in the table
82        for index_col in &stmt.columns {
83            if table_schema.get_column(&index_col.column_name).is_none() {
84                let available_columns =
85                    table_schema.columns.iter().map(|c| c.name.clone()).collect();
86                return Err(ExecutorError::ColumnNotFound {
87                    column_name: index_col.column_name.clone(),
88                    table_name: qualified_table_name.clone(),
89                    searched_tables: vec![qualified_table_name.clone()],
90                    available_columns,
91                });
92            }
93        }
94
95        // Validate prefix length specifications
96        for index_col in &stmt.columns {
97            if let Some(prefix_len) = index_col.prefix_length {
98                // Prefix length must be positive
99                if prefix_len == 0 {
100                    return Err(ExecutorError::InvalidIndexDefinition(format!(
101                        "Prefix length must be greater than 0 for column '{}'",
102                        index_col.column_name
103                    )));
104                }
105
106                // Prefix length should only be used with string columns
107                let column = table_schema.get_column(&index_col.column_name).unwrap(); // Safe: already validated above
108                match column.data_type {
109                    vibesql_types::DataType::Varchar { .. }
110                    | vibesql_types::DataType::Character { .. } => {
111                        // Valid string types for prefix indexing
112                    }
113                    _ => {
114                        return Err(ExecutorError::InvalidIndexDefinition(
115                            format!(
116                                "Prefix length can only be specified for string columns, but column '{}' has type {:?}",
117                                index_col.column_name, column.data_type
118                            ),
119                        ));
120                    }
121                }
122
123                // Reasonable upper limit check (64KB = 65536 characters)
124                // This prevents accidental extremely large prefix specifications
125                const MAX_PREFIX_LENGTH: u64 = 65536;
126                if prefix_len > MAX_PREFIX_LENGTH {
127                    return Err(ExecutorError::InvalidIndexDefinition(format!(
128                        "Prefix length {} is too large for column '{}' (maximum: {})",
129                        prefix_len, index_col.column_name, MAX_PREFIX_LENGTH
130                    )));
131                }
132            }
133        }
134
135        // Check if index already exists (either B-tree or spatial)
136        let index_name = &stmt.index_name;
137        let index_exists =
138            database.index_exists(index_name) || database.spatial_index_exists(index_name);
139
140        if index_exists {
141            if stmt.if_not_exists {
142                // IF NOT EXISTS: silently succeed if index already exists
143                return Ok(format!("Index '{}' already exists (skipped)", index_name));
144            } else {
145                return Err(ExecutorError::IndexAlreadyExists(index_name.clone()));
146            }
147        }
148
149        // Create the index based on type
150        match &stmt.index_type {
151            vibesql_ast::IndexType::BTree { unique } => {
152                // Add to catalog first (use unqualified table name as stored in catalog)
153                let index_metadata = vibesql_catalog::IndexMetadata::new(
154                    index_name.clone(),
155                    table_name.clone(),
156                    vibesql_catalog::IndexType::BTree,
157                    stmt.columns
158                        .iter()
159                        .map(|col| vibesql_catalog::IndexedColumn {
160                            column_name: col.column_name.clone(),
161                            order: match col.direction {
162                                vibesql_ast::OrderDirection::Asc => {
163                                    vibesql_catalog::SortOrder::Ascending
164                                }
165                                vibesql_ast::OrderDirection::Desc => {
166                                    vibesql_catalog::SortOrder::Descending
167                                }
168                            },
169                            prefix_length: col.prefix_length,
170                        })
171                        .collect(),
172                    *unique,
173                );
174                database.catalog.add_index(index_metadata)?;
175
176                // B-tree index (use unqualified name for storage, database handles qualification internally)
177                database.create_index(
178                    index_name.clone(),
179                    table_name.clone(),
180                    *unique,
181                    stmt.columns.clone(),
182                )?;
183
184                Ok(format!(
185                    "Index '{}' created successfully on table '{}'",
186                    index_name, qualified_table_name
187                ))
188            }
189            vibesql_ast::IndexType::Fulltext => Err(ExecutorError::UnsupportedFeature(
190                "FULLTEXT indexes are not yet implemented".to_string(),
191            )),
192            vibesql_ast::IndexType::Spatial => {
193                // Spatial index validation: must be exactly 1 column
194                if stmt.columns.len() != 1 {
195                    return Err(ExecutorError::InvalidIndexDefinition(
196                        "SPATIAL indexes must be defined on exactly one column".to_string(),
197                    ));
198                }
199
200                let column_name = &stmt.columns[0].column_name;
201
202                // Get the column index
203                let col_idx = table_schema.get_column_index(column_name).ok_or_else(|| {
204                    ExecutorError::ColumnNotFound {
205                        column_name: column_name.clone(),
206                        table_name: qualified_table_name.clone(),
207                        searched_tables: vec![qualified_table_name.clone()],
208                        available_columns: table_schema
209                            .columns
210                            .iter()
211                            .map(|c| c.name.clone())
212                            .collect(),
213                    }
214                })?;
215
216                // Extract MBRs from all existing rows (use unqualified name, database handles qualification)
217                let table = database
218                    .get_table(&table_name)
219                    .ok_or_else(|| ExecutorError::TableNotFound(qualified_table_name.clone()))?;
220
221                let mut entries = Vec::new();
222                for (row_idx, row) in table.scan().iter().enumerate() {
223                    let geom_value = &row.values[col_idx];
224
225                    // Extract MBR from geometry value (skip NULLs and invalid geometries)
226                    if let Some(mbr) = extract_mbr_from_sql_value(geom_value) {
227                        entries.push(SpatialIndexEntry { row_id: row_idx, mbr });
228                    }
229                }
230
231                // Build spatial index via bulk_load (more efficient than incremental inserts)
232                let spatial_index = SpatialIndex::bulk_load(column_name.clone(), entries);
233
234                // Add to catalog first (use unqualified table name as stored in catalog)
235                let index_metadata = vibesql_catalog::IndexMetadata::new(
236                    index_name.clone(),
237                    table_name.clone(),
238                    vibesql_catalog::IndexType::RTree,
239                    vec![vibesql_catalog::IndexedColumn {
240                        column_name: column_name.clone(),
241                        order: vibesql_catalog::SortOrder::Ascending,
242                        prefix_length: None, // Spatial indexes don't support prefix indexing
243                    }],
244                    false,
245                );
246                database.catalog.add_index(index_metadata)?;
247
248                // Store in database (use unqualified table name for storage metadata)
249                let metadata = SpatialIndexMetadata {
250                    index_name: index_name.clone(),
251                    table_name: table_name.clone(),
252                    column_name: column_name.clone(),
253                    created_at: Some(chrono::Utc::now()),
254                };
255
256                database.create_spatial_index(metadata, spatial_index)?;
257
258                Ok(format!(
259                    "Spatial index '{}' created successfully on table '{}'",
260                    index_name, qualified_table_name
261                ))
262            }
263            vibesql_ast::IndexType::IVFFlat { metric, lists } => {
264                // IVFFlat index validation: must be exactly 1 column (vector column)
265                if stmt.columns.len() != 1 {
266                    return Err(ExecutorError::InvalidIndexDefinition(
267                        "IVFFlat indexes must be defined on exactly one vector column".to_string(),
268                    ));
269                }
270
271                let column_name = &stmt.columns[0].column_name;
272
273                // Get the column index and validate it's a vector type
274                let col_idx = table_schema.get_column_index(column_name).ok_or_else(|| {
275                    ExecutorError::ColumnNotFound {
276                        column_name: column_name.clone(),
277                        table_name: qualified_table_name.clone(),
278                        searched_tables: vec![qualified_table_name.clone()],
279                        available_columns: table_schema
280                            .columns
281                            .iter()
282                            .map(|c| c.name.clone())
283                            .collect(),
284                    }
285                })?;
286
287                // Validate column type is VECTOR
288                let col_type = &table_schema.columns[col_idx].data_type;
289                let dimensions = match col_type {
290                    vibesql_types::DataType::Vector { dimensions } => *dimensions as usize,
291                    _ => {
292                        return Err(ExecutorError::InvalidIndexDefinition(format!(
293                            "IVFFlat indexes can only be created on VECTOR columns, but '{}' has type {:?}",
294                            column_name, col_type
295                        )));
296                    }
297                };
298
299                // Convert AST metric to catalog metric
300                let catalog_metric = match metric {
301                    vibesql_ast::VectorDistanceMetric::L2 => {
302                        vibesql_catalog::VectorDistanceMetric::L2
303                    }
304                    vibesql_ast::VectorDistanceMetric::Cosine => {
305                        vibesql_catalog::VectorDistanceMetric::Cosine
306                    }
307                    vibesql_ast::VectorDistanceMetric::InnerProduct => {
308                        vibesql_catalog::VectorDistanceMetric::InnerProduct
309                    }
310                };
311
312                // Add to catalog first
313                let index_metadata = vibesql_catalog::IndexMetadata::new(
314                    index_name.clone(),
315                    table_name.clone(),
316                    vibesql_catalog::IndexType::IVFFlat { metric: catalog_metric, lists: *lists },
317                    vec![vibesql_catalog::IndexedColumn {
318                        column_name: column_name.clone(),
319                        order: vibesql_catalog::SortOrder::Ascending, // Not meaningful for vector indexes
320                        prefix_length: None,
321                    }],
322                    false, // IVFFlat indexes are never unique
323                );
324                database.catalog.add_index(index_metadata)?;
325
326                // Create the IVFFlat index in storage
327                database.create_ivfflat_index(
328                    index_name.clone(),
329                    table_name.clone(),
330                    column_name.clone(),
331                    col_idx,
332                    dimensions,
333                    *lists as usize,
334                    *metric,
335                )?;
336
337                Ok(format!(
338                    "IVFFlat index '{}' created successfully on table '{}' column '{}'",
339                    index_name, qualified_table_name, column_name
340                ))
341            }
342            vibesql_ast::IndexType::Hnsw { metric, m, ef_construction } => {
343                // HNSW index validation: must be exactly 1 column (vector column)
344                if stmt.columns.len() != 1 {
345                    return Err(ExecutorError::InvalidIndexDefinition(
346                        "HNSW indexes must be defined on exactly one vector column".to_string(),
347                    ));
348                }
349
350                let column_name = &stmt.columns[0].column_name;
351
352                // Get the column index and validate it's a vector type
353                let col_idx = table_schema.get_column_index(column_name).ok_or_else(|| {
354                    ExecutorError::ColumnNotFound {
355                        column_name: column_name.clone(),
356                        table_name: qualified_table_name.clone(),
357                        searched_tables: vec![qualified_table_name.clone()],
358                        available_columns: table_schema
359                            .columns
360                            .iter()
361                            .map(|c| c.name.clone())
362                            .collect(),
363                    }
364                })?;
365
366                // Validate column type is VECTOR
367                let col_type = &table_schema.columns[col_idx].data_type;
368                let dimensions = match col_type {
369                    vibesql_types::DataType::Vector { dimensions } => *dimensions as usize,
370                    _ => {
371                        return Err(ExecutorError::InvalidIndexDefinition(format!(
372                            "HNSW indexes can only be created on VECTOR columns, but '{}' has type {:?}",
373                            column_name, col_type
374                        )));
375                    }
376                };
377
378                // Convert AST metric to catalog metric
379                let catalog_metric = match metric {
380                    vibesql_ast::VectorDistanceMetric::L2 => {
381                        vibesql_catalog::VectorDistanceMetric::L2
382                    }
383                    vibesql_ast::VectorDistanceMetric::Cosine => {
384                        vibesql_catalog::VectorDistanceMetric::Cosine
385                    }
386                    vibesql_ast::VectorDistanceMetric::InnerProduct => {
387                        vibesql_catalog::VectorDistanceMetric::InnerProduct
388                    }
389                };
390
391                // Add to catalog first
392                let index_metadata = vibesql_catalog::IndexMetadata::new(
393                    index_name.clone(),
394                    table_name.clone(),
395                    vibesql_catalog::IndexType::Hnsw {
396                        metric: catalog_metric,
397                        m: *m,
398                        ef_construction: *ef_construction,
399                    },
400                    vec![vibesql_catalog::IndexedColumn {
401                        column_name: column_name.clone(),
402                        order: vibesql_catalog::SortOrder::Ascending, // Not meaningful for vector indexes
403                        prefix_length: None,
404                    }],
405                    false, // HNSW indexes are never unique
406                );
407                database.catalog.add_index(index_metadata)?;
408
409                // Create the HNSW index in storage
410                database.create_hnsw_index(
411                    index_name.clone(),
412                    table_name.clone(),
413                    column_name.clone(),
414                    col_idx,
415                    dimensions,
416                    *m,
417                    *ef_construction,
418                    *metric,
419                )?;
420
421                Ok(format!(
422                    "HNSW index '{}' created successfully on table '{}' column '{}'",
423                    index_name, qualified_table_name, column_name
424                ))
425            }
426        }
427    }
428}
429
430#[cfg(test)]
431mod tests {
432    use vibesql_ast::{ColumnDef, CreateTableStmt, IndexColumn, OrderDirection};
433    use vibesql_storage::Row;
434    use vibesql_types::{DataType, SqlValue};
435
436    use super::*;
437    use crate::CreateTableExecutor;
438
439    fn create_test_table(db: &mut Database) {
440        let stmt = CreateTableStmt {
441            table_name: "users".to_string(),
442            columns: vec![
443                ColumnDef {
444                    name: "id".to_string(),
445                    data_type: DataType::Integer,
446                    nullable: false,
447                    constraints: vec![],
448                    default_value: None,
449                    comment: None,
450                },
451                ColumnDef {
452                    name: "email".to_string(),
453                    data_type: DataType::Varchar { max_length: Some(255) },
454                    nullable: false,
455                    constraints: vec![],
456                    default_value: None,
457                    comment: None,
458                },
459                ColumnDef {
460                    name: "name".to_string(),
461                    data_type: DataType::Varchar { max_length: Some(100) },
462                    nullable: true,
463                    constraints: vec![],
464                    default_value: None,
465                    comment: None,
466                },
467            ],
468            table_constraints: vec![],
469            table_options: vec![],
470        };
471
472        CreateTableExecutor::execute(&stmt, db).unwrap();
473    }
474
475    #[test]
476    fn test_create_simple_index() {
477        let mut db = Database::new();
478        create_test_table(&mut db);
479
480        let stmt = CreateIndexStmt {
481            index_name: "idx_users_email".to_string(),
482            if_not_exists: false,
483            table_name: "users".to_string(),
484            index_type: vibesql_ast::IndexType::BTree { unique: false },
485            columns: vec![IndexColumn {
486                column_name: "email".to_string(),
487                direction: OrderDirection::Asc,
488                prefix_length: None,
489            }],
490        };
491
492        let result = CreateIndexExecutor::execute(&stmt, &mut db);
493        assert!(result.is_ok());
494        assert_eq!(
495            result.unwrap(),
496            "Index 'idx_users_email' created successfully on table 'public.users'"
497        );
498
499        // Verify index exists
500        assert!(db.index_exists("idx_users_email"));
501    }
502
503    #[test]
504    fn test_create_unique_index() {
505        let mut db = Database::new();
506        create_test_table(&mut db);
507
508        let stmt = CreateIndexStmt {
509            index_name: "idx_users_email_unique".to_string(),
510            if_not_exists: false,
511            table_name: "users".to_string(),
512            index_type: vibesql_ast::IndexType::BTree { unique: true },
513            columns: vec![IndexColumn {
514                column_name: "email".to_string(),
515                direction: OrderDirection::Asc,
516                prefix_length: None,
517            }],
518        };
519
520        let result = CreateIndexExecutor::execute(&stmt, &mut db);
521        assert!(result.is_ok());
522        assert!(db.index_exists("idx_users_email_unique"));
523    }
524
525    #[test]
526    fn test_create_multi_column_index() {
527        let mut db = Database::new();
528        create_test_table(&mut db);
529
530        let stmt = CreateIndexStmt {
531            index_name: "idx_users_email_name".to_string(),
532            if_not_exists: false,
533            table_name: "users".to_string(),
534            index_type: vibesql_ast::IndexType::BTree { unique: false },
535            columns: vec![
536                IndexColumn {
537                    column_name: "email".to_string(),
538                    direction: OrderDirection::Asc,
539                    prefix_length: None,
540                },
541                IndexColumn {
542                    column_name: "name".to_string(),
543                    direction: OrderDirection::Desc,
544                    prefix_length: None,
545                },
546            ],
547        };
548
549        let result = CreateIndexExecutor::execute(&stmt, &mut db);
550        assert!(result.is_ok());
551    }
552
553    #[test]
554    fn test_create_index_duplicate_name() {
555        let mut db = Database::new();
556        create_test_table(&mut db);
557
558        let stmt = CreateIndexStmt {
559            index_name: "idx_users_email".to_string(),
560            if_not_exists: false,
561            table_name: "users".to_string(),
562            index_type: vibesql_ast::IndexType::BTree { unique: false },
563            columns: vec![IndexColumn {
564                column_name: "email".to_string(),
565                direction: OrderDirection::Asc,
566                prefix_length: None,
567            }],
568        };
569
570        // First creation succeeds
571        let result = CreateIndexExecutor::execute(&stmt, &mut db);
572        assert!(result.is_ok());
573
574        // Second creation fails
575        let result = CreateIndexExecutor::execute(&stmt, &mut db);
576        assert!(result.is_err());
577        assert!(matches!(result, Err(ExecutorError::IndexAlreadyExists(_))));
578    }
579
580    #[test]
581    fn test_create_index_on_nonexistent_table() {
582        let mut db = Database::new();
583
584        let stmt = CreateIndexStmt {
585            index_name: "idx_nonexistent".to_string(),
586            if_not_exists: false,
587            table_name: "nonexistent_table".to_string(),
588            index_type: vibesql_ast::IndexType::BTree { unique: false },
589            columns: vec![IndexColumn {
590                column_name: "id".to_string(),
591                direction: OrderDirection::Asc,
592                prefix_length: None,
593            }],
594        };
595
596        let result = CreateIndexExecutor::execute(&stmt, &mut db);
597        assert!(result.is_err());
598        assert!(matches!(result, Err(ExecutorError::TableNotFound(_))));
599    }
600
601    #[test]
602    fn test_create_index_on_nonexistent_column() {
603        let mut db = Database::new();
604        create_test_table(&mut db);
605
606        let stmt = CreateIndexStmt {
607            index_name: "idx_users_nonexistent".to_string(),
608            if_not_exists: false,
609            table_name: "users".to_string(),
610            index_type: vibesql_ast::IndexType::BTree { unique: false },
611            columns: vec![IndexColumn {
612                column_name: "nonexistent_column".to_string(),
613                direction: OrderDirection::Asc,
614                prefix_length: None,
615            }],
616        };
617
618        let result = CreateIndexExecutor::execute(&stmt, &mut db);
619        assert!(result.is_err());
620        assert!(matches!(result, Err(ExecutorError::ColumnNotFound { .. })));
621    }
622
623    #[test]
624    fn test_create_index_if_not_exists_when_not_exists() {
625        let mut db = Database::new();
626        create_test_table(&mut db);
627
628        let stmt = CreateIndexStmt {
629            index_name: "idx_users_email".to_string(),
630            if_not_exists: true,
631            table_name: "users".to_string(),
632            index_type: vibesql_ast::IndexType::BTree { unique: false },
633            columns: vec![IndexColumn {
634                column_name: "email".to_string(),
635                direction: OrderDirection::Asc,
636                prefix_length: None,
637            }],
638        };
639
640        let result = CreateIndexExecutor::execute(&stmt, &mut db);
641        assert!(result.is_ok());
642        assert_eq!(
643            result.unwrap(),
644            "Index 'idx_users_email' created successfully on table 'public.users'"
645        );
646        assert!(db.index_exists("idx_users_email"));
647    }
648
649    #[test]
650    fn test_create_index_if_not_exists_when_exists() {
651        let mut db = Database::new();
652        create_test_table(&mut db);
653
654        // First creation
655        let stmt = CreateIndexStmt {
656            index_name: "idx_users_email".to_string(),
657            if_not_exists: false,
658            table_name: "users".to_string(),
659            index_type: vibesql_ast::IndexType::BTree { unique: false },
660            columns: vec![IndexColumn {
661                column_name: "email".to_string(),
662                direction: OrderDirection::Asc,
663                prefix_length: None,
664            }],
665        };
666        CreateIndexExecutor::execute(&stmt, &mut db).unwrap();
667
668        // Second creation with IF NOT EXISTS should succeed
669        let stmt_with_if_not_exists = CreateIndexStmt {
670            index_name: "idx_users_email".to_string(),
671            if_not_exists: true,
672            table_name: "users".to_string(),
673            index_type: vibesql_ast::IndexType::BTree { unique: false },
674            columns: vec![IndexColumn {
675                column_name: "email".to_string(),
676                direction: OrderDirection::Asc,
677                prefix_length: None,
678            }],
679        };
680        let result = CreateIndexExecutor::execute(&stmt_with_if_not_exists, &mut db);
681        assert!(result.is_ok());
682        assert!(db.index_exists("idx_users_email"));
683    }
684
685    #[test]
686    fn test_create_index_with_schema_qualified_table() {
687        let mut db = Database::new();
688        create_test_table(&mut db);
689
690        // Create index using schema-qualified table name (with default public schema)
691        let index_stmt = CreateIndexStmt {
692            index_name: "idx_users_email_qualified".to_string(),
693            if_not_exists: false,
694            table_name: "public.users".to_string(), // Explicitly qualify with public schema
695            index_type: vibesql_ast::IndexType::BTree { unique: false },
696            columns: vec![IndexColumn {
697                column_name: "email".to_string(),
698                direction: OrderDirection::Asc,
699                prefix_length: None,
700            }],
701        };
702
703        let result = CreateIndexExecutor::execute(&index_stmt, &mut db);
704        assert!(result.is_ok());
705        assert_eq!(
706            result.unwrap(),
707            "Index 'idx_users_email_qualified' created successfully on table 'public.users'"
708        );
709
710        // Verify index exists
711        assert!(db.index_exists("idx_users_email_qualified"));
712    }
713
714    #[test]
715    fn test_create_index_on_nonexistent_schema_qualified_table() {
716        let mut db = Database::new();
717
718        // Create a custom schema
719        db.catalog.create_schema("test_schema".to_string()).unwrap();
720
721        // Try to create index on non-existent table
722        let index_stmt = CreateIndexStmt {
723            index_name: "idx_nonexistent".to_string(),
724            if_not_exists: false,
725            table_name: "test_schema.nonexistent_table".to_string(),
726            index_type: vibesql_ast::IndexType::BTree { unique: false },
727            columns: vec![IndexColumn {
728                column_name: "id".to_string(),
729                direction: OrderDirection::Asc,
730                prefix_length: None,
731            }],
732        };
733
734        let result = CreateIndexExecutor::execute(&index_stmt, &mut db);
735        assert!(result.is_err());
736        assert!(matches!(result, Err(ExecutorError::TableNotFound(_))));
737    }
738
739    // ========================================================================
740    // IVFFlat Index Tests
741    // ========================================================================
742
743    fn create_vector_table(db: &mut Database) {
744        let stmt = CreateTableStmt {
745            table_name: "documents".to_string(),
746            columns: vec![
747                ColumnDef {
748                    name: "id".to_string(),
749                    data_type: DataType::Integer,
750                    nullable: false,
751                    constraints: vec![],
752                    default_value: None,
753                    comment: None,
754                },
755                ColumnDef {
756                    name: "embedding".to_string(),
757                    data_type: DataType::Vector { dimensions: 3 },
758                    nullable: true,
759                    constraints: vec![],
760                    default_value: None,
761                    comment: None,
762                },
763                ColumnDef {
764                    name: "content".to_string(),
765                    data_type: DataType::Varchar { max_length: Some(1000) },
766                    nullable: true,
767                    constraints: vec![],
768                    default_value: None,
769                    comment: None,
770                },
771            ],
772            table_constraints: vec![],
773            table_options: vec![],
774        };
775
776        CreateTableExecutor::execute(&stmt, db).unwrap();
777    }
778
779    #[test]
780    fn test_create_ivfflat_index_l2() {
781        let mut db = Database::new();
782        create_vector_table(&mut db);
783
784        let stmt = CreateIndexStmt {
785            index_name: "idx_documents_embedding".to_string(),
786            if_not_exists: false,
787            table_name: "documents".to_string(),
788            index_type: vibesql_ast::IndexType::IVFFlat {
789                metric: vibesql_ast::VectorDistanceMetric::L2,
790                lists: 4,
791            },
792            columns: vec![IndexColumn {
793                column_name: "embedding".to_string(),
794                direction: OrderDirection::Asc,
795                prefix_length: None,
796            }],
797        };
798
799        let result = CreateIndexExecutor::execute(&stmt, &mut db);
800        assert!(result.is_ok(), "IVFFlat index creation failed: {:?}", result.err());
801        assert!(result
802            .unwrap()
803            .contains("IVFFlat index 'idx_documents_embedding' created successfully"));
804        assert!(db.index_exists("idx_documents_embedding"));
805    }
806
807    #[test]
808    fn test_create_ivfflat_index_cosine() {
809        let mut db = Database::new();
810        create_vector_table(&mut db);
811
812        let stmt = CreateIndexStmt {
813            index_name: "idx_documents_cosine".to_string(),
814            if_not_exists: false,
815            table_name: "documents".to_string(),
816            index_type: vibesql_ast::IndexType::IVFFlat {
817                metric: vibesql_ast::VectorDistanceMetric::Cosine,
818                lists: 4,
819            },
820            columns: vec![IndexColumn {
821                column_name: "embedding".to_string(),
822                direction: OrderDirection::Asc,
823                prefix_length: None,
824            }],
825        };
826
827        let result = CreateIndexExecutor::execute(&stmt, &mut db);
828        assert!(result.is_ok());
829        assert!(db.index_exists("idx_documents_cosine"));
830    }
831
832    #[test]
833    fn test_create_ivfflat_index_inner_product() {
834        let mut db = Database::new();
835        create_vector_table(&mut db);
836
837        let stmt = CreateIndexStmt {
838            index_name: "idx_documents_ip".to_string(),
839            if_not_exists: false,
840            table_name: "documents".to_string(),
841            index_type: vibesql_ast::IndexType::IVFFlat {
842                metric: vibesql_ast::VectorDistanceMetric::InnerProduct,
843                lists: 4,
844            },
845            columns: vec![IndexColumn {
846                column_name: "embedding".to_string(),
847                direction: OrderDirection::Asc,
848                prefix_length: None,
849            }],
850        };
851
852        let result = CreateIndexExecutor::execute(&stmt, &mut db);
853        assert!(result.is_ok());
854        assert!(db.index_exists("idx_documents_ip"));
855    }
856
857    #[test]
858    fn test_create_ivfflat_index_on_non_vector_column() {
859        let mut db = Database::new();
860        create_vector_table(&mut db);
861
862        // Try to create IVFFlat index on a non-vector column
863        let stmt = CreateIndexStmt {
864            index_name: "idx_documents_content".to_string(),
865            if_not_exists: false,
866            table_name: "documents".to_string(),
867            index_type: vibesql_ast::IndexType::IVFFlat {
868                metric: vibesql_ast::VectorDistanceMetric::L2,
869                lists: 4,
870            },
871            columns: vec![IndexColumn {
872                column_name: "content".to_string(),
873                direction: OrderDirection::Asc,
874                prefix_length: None,
875            }],
876        };
877
878        let result = CreateIndexExecutor::execute(&stmt, &mut db);
879        assert!(result.is_err());
880        assert!(matches!(result, Err(ExecutorError::InvalidIndexDefinition(_))));
881    }
882
883    #[test]
884    fn test_create_ivfflat_index_multiple_columns_fails() {
885        let mut db = Database::new();
886        create_vector_table(&mut db);
887
888        // IVFFlat indexes must be on exactly one column
889        let stmt = CreateIndexStmt {
890            index_name: "idx_documents_multi".to_string(),
891            if_not_exists: false,
892            table_name: "documents".to_string(),
893            index_type: vibesql_ast::IndexType::IVFFlat {
894                metric: vibesql_ast::VectorDistanceMetric::L2,
895                lists: 4,
896            },
897            columns: vec![
898                IndexColumn {
899                    column_name: "embedding".to_string(),
900                    direction: OrderDirection::Asc,
901                    prefix_length: None,
902                },
903                IndexColumn {
904                    column_name: "id".to_string(),
905                    direction: OrderDirection::Asc,
906                    prefix_length: None,
907                },
908            ],
909        };
910
911        let result = CreateIndexExecutor::execute(&stmt, &mut db);
912        assert!(result.is_err());
913        assert!(matches!(result, Err(ExecutorError::InvalidIndexDefinition(_))));
914    }
915
916    #[test]
917    fn test_create_ivfflat_index_if_not_exists() {
918        let mut db = Database::new();
919        create_vector_table(&mut db);
920
921        let stmt = CreateIndexStmt {
922            index_name: "idx_documents_embedding".to_string(),
923            if_not_exists: true,
924            table_name: "documents".to_string(),
925            index_type: vibesql_ast::IndexType::IVFFlat {
926                metric: vibesql_ast::VectorDistanceMetric::L2,
927                lists: 4,
928            },
929            columns: vec![IndexColumn {
930                column_name: "embedding".to_string(),
931                direction: OrderDirection::Asc,
932                prefix_length: None,
933            }],
934        };
935
936        // First creation
937        let result = CreateIndexExecutor::execute(&stmt, &mut db);
938        assert!(result.is_ok());
939
940        // Second creation with IF NOT EXISTS should succeed
941        let result2 = CreateIndexExecutor::execute(&stmt, &mut db);
942        assert!(result2.is_ok());
943        assert!(result2.unwrap().contains("already exists"));
944    }
945
946    #[test]
947    fn test_ivfflat_index_search() {
948        let mut db = Database::new();
949        create_vector_table(&mut db);
950
951        // Insert test vector data
952        // Row 0: [1.0, 0.0, 0.0] - should be closest to query [0.9, 0.1, 0.0]
953        db.insert_row(
954            "documents",
955            Row::new(vec![
956                SqlValue::Integer(1),
957                SqlValue::Vector(vec![1.0, 0.0, 0.0]),
958                SqlValue::Varchar("doc1".to_string()),
959            ]),
960        )
961        .unwrap();
962
963        // Row 1: [0.0, 1.0, 0.0]
964        db.insert_row(
965            "documents",
966            Row::new(vec![
967                SqlValue::Integer(2),
968                SqlValue::Vector(vec![0.0, 1.0, 0.0]),
969                SqlValue::Varchar("doc2".to_string()),
970            ]),
971        )
972        .unwrap();
973
974        // Row 2: [0.0, 0.0, 1.0]
975        db.insert_row(
976            "documents",
977            Row::new(vec![
978                SqlValue::Integer(3),
979                SqlValue::Vector(vec![0.0, 0.0, 1.0]),
980                SqlValue::Varchar("doc3".to_string()),
981            ]),
982        )
983        .unwrap();
984
985        // Row 3: [0.5, 0.5, 0.0] - second closest to query
986        db.insert_row(
987            "documents",
988            Row::new(vec![
989                SqlValue::Integer(4),
990                SqlValue::Vector(vec![0.5, 0.5, 0.0]),
991                SqlValue::Varchar("doc4".to_string()),
992            ]),
993        )
994        .unwrap();
995
996        // Create IVFFlat index (should build on existing data)
997        let stmt = CreateIndexStmt {
998            index_name: "idx_embedding".to_string(),
999            if_not_exists: false,
1000            table_name: "documents".to_string(),
1001            index_type: vibesql_ast::IndexType::IVFFlat {
1002                metric: vibesql_ast::VectorDistanceMetric::L2,
1003                lists: 2, // 2 clusters for small test data
1004            },
1005            columns: vec![IndexColumn {
1006                column_name: "embedding".to_string(),
1007                direction: OrderDirection::Asc,
1008                prefix_length: None,
1009            }],
1010        };
1011
1012        let result = CreateIndexExecutor::execute(&stmt, &mut db);
1013        assert!(result.is_ok(), "Index creation failed: {:?}", result.err());
1014
1015        // Test search API
1016        // Query vector near [1.0, 0.0, 0.0]
1017        let query_vector = vec![0.9, 0.1, 0.0];
1018        let results = db.search_ivfflat_index("idx_embedding", &query_vector, 2);
1019        assert!(results.is_ok(), "Search should succeed: {:?}", results.err());
1020
1021        let neighbors = results.unwrap();
1022        // Should find at least the nearest vectors
1023        assert!(!neighbors.is_empty(), "Should find at least one neighbor");
1024
1025        // The closest vector should be [1.0, 0.0, 0.0] (row 0)
1026        let (first_row_id, first_distance) = neighbors[0];
1027        assert!(first_distance >= 0.0, "Distance should be non-negative");
1028        // Since we inserted [1.0, 0.0, 0.0] at row 0, it should be closest
1029        assert_eq!(first_row_id, 0, "First result should be the closest vector");
1030    }
1031
1032    #[test]
1033    fn test_ivfflat_get_indexes_for_table() {
1034        let mut db = Database::new();
1035        create_vector_table(&mut db);
1036
1037        // Create IVFFlat index
1038        let stmt = CreateIndexStmt {
1039            index_name: "idx_vec".to_string(),
1040            if_not_exists: false,
1041            table_name: "documents".to_string(),
1042            index_type: vibesql_ast::IndexType::IVFFlat {
1043                metric: vibesql_ast::VectorDistanceMetric::Cosine,
1044                lists: 2,
1045            },
1046            columns: vec![IndexColumn {
1047                column_name: "embedding".to_string(),
1048                direction: OrderDirection::Asc,
1049                prefix_length: None,
1050            }],
1051        };
1052
1053        let result = CreateIndexExecutor::execute(&stmt, &mut db);
1054        assert!(result.is_ok());
1055
1056        // Test getting IVFFlat indexes for the table
1057        let ivfflat_indexes = db.get_ivfflat_indexes_for_table("documents");
1058        assert_eq!(ivfflat_indexes.len(), 1, "Should have one IVFFlat index");
1059
1060        let (metadata, index) = &ivfflat_indexes[0];
1061        assert!(metadata.index_name.to_uppercase().contains("IDX_VEC"));
1062        assert_eq!(index.metric(), vibesql_ast::VectorDistanceMetric::Cosine);
1063    }
1064
1065    #[test]
1066    fn test_ivfflat_set_probes() {
1067        let mut db = Database::new();
1068        create_vector_table(&mut db);
1069
1070        // Create IVFFlat index
1071        let stmt = CreateIndexStmt {
1072            index_name: "idx_probes".to_string(),
1073            if_not_exists: false,
1074            table_name: "documents".to_string(),
1075            index_type: vibesql_ast::IndexType::IVFFlat {
1076                metric: vibesql_ast::VectorDistanceMetric::L2,
1077                lists: 4,
1078            },
1079            columns: vec![IndexColumn {
1080                column_name: "embedding".to_string(),
1081                direction: OrderDirection::Asc,
1082                prefix_length: None,
1083            }],
1084        };
1085
1086        let result = CreateIndexExecutor::execute(&stmt, &mut db);
1087        assert!(result.is_ok());
1088
1089        // Set probes to search more clusters (improves recall at cost of speed)
1090        let set_probes_result = db.set_ivfflat_probes("idx_probes", 3);
1091        assert!(set_probes_result.is_ok());
1092
1093        // Verify the index can still be searched
1094        let query_vector = vec![0.5, 0.5, 0.5];
1095        let search_result = db.search_ivfflat_index("idx_probes", &query_vector, 3);
1096        assert!(search_result.is_ok());
1097    }
1098}
vibesql_executor/index_ddl/create_index.rs

vibesql_executor/index_ddl/
create_index.rs
