Support GLUE_INDEXES reserved table which provides all index info (#…

…935) e.g. SELECT * FROM GLUE_INDEXES; `GLUE_INDEXES` table provides all the index information from the entire database. It includes both clustered index (primary key) and non-clustered index. Table contains five columns: - TABLE_NAME - INDEX_NAME - ORDER - EXPRESSION - UNIQUENESS
gluesql · Oct 17, 2022 · d888e7a · d888e7a
1 parent e85c908
commit d888e7a
Show file tree

Hide file tree

Showing 6 changed files with 98 additions and 9 deletions.
diff --git a/cli/src/print.rs b/cli/src/print.rs
@@ -262,7 +262,10 @@ mod tests {
     use {
         super::Print,
         crate::command::{SetOption, ShowOption},
-        gluesql_core::{data::SchemaIndex, data::SchemaIndexOrd},
+        gluesql_core::{
+            ast::{BinaryOperator, Expr},
+            data::{SchemaIndex, SchemaIndexOrd},
+        },
     };
 
     #[test]
@@ -298,7 +301,7 @@ mod tests {
     #[test]
     fn print_payload() {
         use gluesql_core::{
-            ast::{BinaryOperator, DataType, Expr},
+            ast::DataType,
             prelude::{Payload, PayloadVariable, Row, Value},
         };
 

diff --git a/core/src/ast/query.rs b/core/src/ast/query.rs
@@ -83,6 +83,7 @@ pub enum TableFactor {
 pub enum Dictionary {
     GlueTables,
     GlueTableColumns,
+    GlueIndexes,
 }
 
 #[derive(Debug, Clone, PartialEq, Eq, Hash, Serialize, Deserialize)]

diff --git a/core/src/executor/fetch.rs b/core/src/executor/fetch.rs
@@ -2,8 +2,8 @@ use {
     super::{context::FilterContext, evaluate_stateless, filter::check_expr},
     crate::{
         ast::{
-            ColumnDef, Dictionary, Expr, IndexItem, Join, Query, Select, SetExpr, TableAlias,
-            TableFactor, TableWithJoins, Values,
+            ColumnDef, ColumnOption, Dictionary, Expr, IndexItem, Join, Query, Select, SetExpr,
+            TableAlias, TableFactor, TableWithJoins, ToSql, Values,
         },
         data::{get_alias, get_index, Key, Row, Value},
         executor::{
@@ -163,9 +163,10 @@ pub async fn fetch_relation_rows<'a>(
         TableFactor::Dictionary { dict, .. } => {
             let rows = {
                 #[derive(Iterator)]
-                enum Rows<I1, I2> {
-                    GlueTables(I1),
-                    GlueTabColumns(I2),
+                enum Rows<I1, I2, I3> {
+                    Tables(I1),
+                    TableColumns(I2),
+                    Indexes(I3),
                 }
                 match dict {
                     Dictionary::GlueTables => {
@@ -174,7 +175,7 @@ pub async fn fetch_relation_rows<'a>(
                             .into_iter()
                             .map(|schema| Ok(Row(vec![Value::Str(schema.table_name)])));
 
-                        Rows::GlueTables(rows)
+                        Rows::Tables(rows)
                     }
                     Dictionary::GlueTableColumns => {
                         let schemas = storage.fetch_all_schemas().await?;
@@ -191,7 +192,48 @@ pub async fn fetch_relation_rows<'a>(
                             )
                         });
 
-                        Rows::GlueTabColumns(rows)
+                        Rows::TableColumns(rows)
+                    }
+                    Dictionary::GlueIndexes => {
+                        let schemas = storage.fetch_all_schemas().await?;
+                        let rows = schemas.into_iter().flat_map(|schema| {
+                            let primary_column = schema.column_defs.iter().find_map(
+                                |ColumnDef { name, options, .. }| {
+                                    options
+                                        .iter()
+                                        .any(|column_option_def| {
+                                            column_option_def.option
+                                                == ColumnOption::Unique { is_primary: true }
+                                        })
+                                        .then_some(name)
+                                },
+                            );
+
+                            let clustered = match primary_column {
+                                Some(column_name) => vec![Ok(Row(vec![
+                                    Value::Str(schema.table_name.clone()),
+                                    Value::Str("PRIMARY".to_owned()),
+                                    Value::Str("BOTH".to_owned()),
+                                    Value::Str(column_name.to_owned()),
+                                    Value::Bool(true),
+                                ]))],
+                                None => Vec::new(),
+                            };
+
+                            let non_clustered = schema.indexes.into_iter().map(move |index| {
+                                Ok(Row(vec![
+                                    Value::Str(schema.table_name.clone()),
+                                    Value::Str(index.name),
+                                    Value::Str(index.order.to_string()),
+                                    Value::Str(index.expr.to_sql()),
+                                    Value::Bool(false),
+                                ]))
+                            });
+
+                            clustered.into_iter().chain(non_clustered)
+                        });
+
+                        Rows::Indexes(rows)
                     }
                 }
             };
@@ -227,6 +269,13 @@ pub async fn fetch_relation_columns(
                 "COLUMN_NAME".to_owned(),
                 "COLUMN_ID".to_owned(),
             ]),
+            Dictionary::GlueIndexes => Ok(vec![
+                "TABLE_NAME".to_owned(),
+                "INDEX_NAME".to_owned(),
+                "ORDER".to_owned(),
+                "EXPRESSION".to_owned(),
+                "UNIQUENESS".to_owned(),
+            ]),
         },
         TableFactor::Derived {
             subquery: Query { body, .. },

diff --git a/core/src/translate/query.rs b/core/src/translate/query.rs
@@ -198,6 +198,10 @@ fn translate_table_factor(sql_table_factor: &SqlTableFactor) -> Result<TableFact
                     dict: Dictionary::GlueTables,
                     alias: alias_or_name,
                 }),
+                "GLUE_INDEXES" => Ok(TableFactor::Dictionary {
+                    dict: Dictionary::GlueIndexes,
+                    alias: alias_or_name,
+                }),
                 "GLUE_TABLE_COLUMNS" => Ok(TableFactor::Dictionary {
                     dict: Dictionary::GlueTableColumns,
                     alias: alias_or_name,

diff --git a/test-suite/src/dictionary_index.rs b/test-suite/src/dictionary_index.rs
@@ -0,0 +1,30 @@
+use {crate::*, gluesql_core::prelude::Value::*};
+
+test_case!(ditionary_index, async move {
+    run!("CREATE TABLE Foo (id INT, name TEXT);");
+    run!("CREATE INDEX Foo_id ON Foo (id)");
+    run!("CREATE INDEX Foo_id_2 ON Foo (id + 2)");
+    test!(
+        "SELECT * FROM GLUE_INDEXES",
+        Ok(select!(
+            TABLE_NAME       | INDEX_NAME            | ORDER             | EXPRESSION         | UNIQUENESS;
+            Str              | Str                   | Str               | Str                | Bool;
+            "Foo".to_owned()   "Foo_id".to_owned()     "BOTH".to_owned()   "id".to_owned()      false;
+            "Foo".to_owned()   "Foo_id_2".to_owned()   "BOTH".to_owned()   "id + 2".to_owned()  false
+        ))
+    );
+
+    run!("CREATE TABLE Bar (id INT PRIMARY KEY, name TEXT);");
+    run!("CREATE INDEX Bar_name_concat ON Bar (name + '_')");
+    test!(
+        "SELECT * FROM GLUE_INDEXES",
+        Ok(select!(
+            TABLE_NAME       | INDEX_NAME                  | ORDER             | EXPRESSION               | UNIQUENESS;
+            Str              | Str                         | Str               | Str                      | Bool;
+            "Bar".to_owned()   "PRIMARY".to_owned()          "BOTH".to_owned()   "id".to_owned()            true;
+            "Bar".to_owned()   "Bar_name_concat".to_owned()  "BOTH".to_owned()   "name + \"_\"".to_owned()  false;
+            "Foo".to_owned()   "Foo_id".to_owned()           "BOTH".to_owned()   "id".to_owned()            false;
+            "Foo".to_owned()   "Foo_id_2".to_owned()         "BOTH".to_owned()   "id + 2".to_owned()        false
+        ))
+    );
+});
diff --git a/test-suite/src/lib.rs b/test-suite/src/lib.rs
@@ -11,6 +11,7 @@ pub mod concat;
 pub mod data_type;
 pub mod default;
 pub mod dictionary;
+pub mod dictionary_index;
 pub mod filter;
 pub mod function;
 pub mod index;
@@ -211,6 +212,7 @@ macro_rules! generate_index_tests {
         glue!(index_order_by, index::order_by);
         glue!(index_order_by_multi, index::order_by_multi);
         glue!(showindexes, index::showindexes);
+        glue!(dictionary_index, dictionary_index::ditionary_index);
     };
 }