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

vibesql_executor/index_ddl/
create_index.rs
