Add Dictionary::GLUE_OBJECTS to provide table and index metadata (g…

…luesql#924) Add `Dictionary::GLUE_OBJECTS` which provides table and index metadata in the entire database. Support Dictionary::`GLUE_OBJECTS` to serve OBJECT_TYPE, CREATED metadata Add `created` datetime field to `Schema` and `SchemaIndex` Change SHOW INDEXES query to use `GLUE_INDEXES`.
arbfay · Oct 27, 2022 · 9fb4d04 · 9fb4d04
1 parent 79e88ac
commit 9fb4d04
Show file tree

Hide file tree

Showing 12 changed files with 118 additions and 111 deletions.
diff --git a/cli/src/print.rs b/cli/src/print.rs
@@ -1,9 +1,6 @@
 use {
     crate::command::{SetOption, ShowOption},
-    gluesql_core::{
-        ast::ToSql,
-        prelude::{Payload, PayloadVariable},
-    },
+    gluesql_core::prelude::{Payload, PayloadVariable},
     std::{
         fmt::Display,
         fs::File,
@@ -125,18 +122,6 @@ impl<'a, W: Write> Print<W> {
                 let table = self.build_table(table);
                 self.write(table)?;
             }
-            Payload::ShowIndexes(indexes) => {
-                let mut table = self.get_table(vec!["Index Name", "Order", "Description"]);
-                for index in indexes {
-                    table.add_record([
-                        index.name.to_owned(),
-                        index.order.to_string(),
-                        index.expr.to_sql(),
-                    ]);
-                }
-                let table = self.build_table(table);
-                self.write(table)?;
-            }
             Payload::Select { labels, rows } => {
                 let PrintOption {
                     tabular,
@@ -262,10 +247,6 @@ mod tests {
     use {
         super::Print,
         crate::command::{SetOption, ShowOption},
-        gluesql_core::{
-            ast::{BinaryOperator, Expr},
-            data::{SchemaIndex, SchemaIndexOrd},
-        },
     };
 
     #[test]
@@ -426,36 +407,6 @@ mod tests {
 | 5  | kim   | TRUE  |"
         );
 
-        test!(
-            &Payload::ShowIndexes(vec![
-                SchemaIndex {
-                    name: "id_ndx".to_owned(),
-                    order: SchemaIndexOrd::Asc,
-                    expr: Expr::Identifier("id".to_owned())
-                },
-                SchemaIndex {
-                    name: "name_ndx".to_owned(),
-                    order: SchemaIndexOrd::Desc,
-                    expr: Expr::Identifier("name".to_owned())
-                },
-                SchemaIndex {
-                    name: "expr_ndx".to_owned(),
-                    order: SchemaIndexOrd::Both,
-                    expr: Expr::BinaryOp {
-                        left: Box::new(Expr::Identifier("expr1".to_owned())),
-                        op: BinaryOperator::Minus,
-                        right: Box::new(Expr::Identifier("expr2".to_owned()))
-                    }
-                }
-            ],),
-            "
-| Index Name | Order | Description   |
-|------------|-------|---------------|
-| id_ndx     | ASC   | id            |
-| name_ndx   | DESC  | name          |
-| expr_ndx   | BOTH  | expr1 - expr2 |"
-        );
-
         test!(
             &Payload::ShowColumns(vec![
                 ("id".to_owned(), DataType::Int),

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

diff --git a/core/src/data/schema.rs b/core/src/data/schema.rs
@@ -1,5 +1,6 @@
 use {
     crate::ast::{ColumnDef, ColumnOption, ColumnOptionDef, Expr},
+    chrono::NaiveDateTime,
     serde::{Deserialize, Serialize},
     std::fmt::Debug,
     strum_macros::Display,
@@ -18,13 +19,15 @@ pub struct SchemaIndex {
     pub name: String,
     pub expr: Expr,
     pub order: SchemaIndexOrd,
+    pub created: NaiveDateTime,
 }
 
 #[derive(Clone, Debug, Serialize, Deserialize)]
 pub struct Schema {
     pub table_name: String,
     pub column_defs: Vec<ColumnDef>,
     pub indexes: Vec<SchemaIndex>,
+    pub created: NaiveDateTime,
 }
 
 pub trait ColumnDefExt {

diff --git a/core/src/executor/alter/table.rs b/core/src/executor/alter/table.rs
@@ -8,6 +8,7 @@ use {
         result::{Error, IntoControlFlow, MutResult, Result, TrySelf},
         store::{GStore, GStoreMut},
     },
+    chrono::Utc,
     futures::stream::{self, TryStreamExt},
     std::{
         iter,
@@ -110,6 +111,7 @@ pub async fn create_table<T: GStore + GStoreMut>(
             table_name: target_table_name.to_owned(),
             column_defs: target_columns_defs,
             indexes: vec![],
+            created: Utc::now().naive_utc(),
         };
 
         for column_def in &schema.column_defs {

diff --git a/core/src/executor/execute.rs b/core/src/executor/execute.rs
@@ -27,7 +27,10 @@ use {
 use super::alter::alter_table;
 
 #[cfg(feature = "index")]
-use {super::alter::create_index, crate::data::SchemaIndex};
+use {
+    super::alter::create_index,
+    crate::ast::{AstLiteral, BinaryOperator},
+};
 
 #[derive(ThisError, Serialize, Debug, PartialEq)]
 pub enum ExecuteError {
@@ -63,9 +66,6 @@ pub enum Payload {
     Rollback,
 
     ShowVariable(PayloadVariable),
-
-    #[cfg(feature = "index")]
-    ShowIndexes(Vec<SchemaIndex>),
 }
 
 #[derive(Debug, Serialize, Deserialize, PartialEq)]
@@ -358,18 +358,47 @@ pub async fn execute<T: GStore + GStoreMut>(
         }
         #[cfg(feature = "index")]
         Statement::ShowIndexes(table_name) => {
-            let indexes = match storage.fetch_schema(table_name).await {
-                Ok(Some(Schema { indexes, .. })) => indexes,
-                Ok(None) => {
-                    return Err((
-                        storage,
-                        ExecuteError::TableNotFound(table_name.to_owned()).into(),
-                    ));
-                }
-                Err(e) => return Err((storage, e)),
+            let query = Query {
+                body: SetExpr::Select(Box::new(crate::ast::Select {
+                    projection: vec![SelectItem::Wildcard],
+                    from: TableWithJoins {
+                        relation: TableFactor::Dictionary {
+                            dict: Dictionary::GlueIndexes,
+                            alias: TableAlias {
+                                name: "GLUE_INDEXES".to_owned(),
+                                columns: Vec::new(),
+                            },
+                        },
+                        joins: Vec::new(),
+                    },
+                    selection: Some(Expr::BinaryOp {
+                        left: Box::new(Expr::Identifier("TABLE_NAME".to_owned())),
+                        op: BinaryOperator::Eq,
+                        right: Box::new(Expr::Literal(AstLiteral::QuotedString(
+                            table_name.to_owned(),
+                        ))),
+                    }),
+                    group_by: Vec::new(),
+                    having: None,
+                })),
+                order_by: Vec::new(),
+                limit: None,
+                offset: None,
             };
 
-            Ok((storage, Payload::ShowIndexes(indexes)))
+            let (labels, rows) = try_block!(storage, {
+                let (labels, rows) = select_with_labels(&storage, &query, None, true).await?;
+                let rows = rows.try_collect::<Vec<_>>().await?;
+                Ok((labels, rows))
+            });
+
+            match rows.is_empty() {
+                true => Err((
+                    storage,
+                    ExecuteError::TableNotFound(table_name.to_owned()).into(),
+                )),
+                false => Ok((storage, Payload::Select { labels, rows })),
+            }
         }
         Statement::ShowVariable(variable) => match variable {
             Variable::Tables => {

diff --git a/core/src/executor/fetch.rs b/core/src/executor/fetch.rs
@@ -163,12 +163,34 @@ pub async fn fetch_relation_rows<'a>(
         TableFactor::Dictionary { dict, .. } => {
             let rows = {
                 #[derive(Iterator)]
-                enum Rows<I1, I2, I3> {
+                enum Rows<I1, I2, I3, I4> {
                     Tables(I1),
                     TableColumns(I2),
                     Indexes(I3),
+                    Objects(I4),
                 }
                 match dict {
+                    Dictionary::GlueObjects => {
+                        let schemas = storage.fetch_all_schemas().await?;
+                        let rows = schemas.into_iter().flat_map(|schema| {
+                            let table_rows = vec![Ok(Row(vec![
+                                Value::Str(schema.table_name),
+                                Value::Str("TABLE".to_owned()),
+                                Value::Timestamp(schema.created),
+                            ]))];
+                            let index_rows = schema.indexes.into_iter().map(|index| {
+                                Ok(Row(vec![
+                                    Value::Str(index.name.clone()),
+                                    Value::Str("INDEX".to_owned()),
+                                    Value::Timestamp(index.created),
+                                ]))
+                            });
+
+                            table_rows.into_iter().chain(index_rows)
+                        });
+
+                        Rows::Objects(rows)
+                    }
                     Dictionary::GlueTables => {
                         let schemas = storage.fetch_all_schemas().await?;
                         let rows = schemas
@@ -263,6 +285,11 @@ pub async fn fetch_relation_columns(
         TableFactor::Table { name, .. } => fetch_columns(storage, name).await,
         TableFactor::Series { .. } => Ok(vec!["N".to_owned()]),
         TableFactor::Dictionary { dict, .. } => match dict {
+            Dictionary::GlueObjects => Ok(vec![
+                "OBJECT_NAME".to_owned(),
+                "OBJECT_TYPE".to_owned(),
+                "CREATED".to_owned(),
+            ]),
             Dictionary::GlueTables => Ok(vec!["TABLE_NAME".to_owned()]),
             Dictionary::GlueTableColumns => Ok(vec![
                 "TABLE_NAME".to_owned(),

diff --git a/core/src/translate/query.rs b/core/src/translate/query.rs
@@ -194,6 +194,10 @@ fn translate_table_factor(sql_table_factor: &SqlTableFactor) -> Result<TableFact
                     alias: alias_or_name,
                     size: translate_table_args(args)?,
                 }),
+                "GLUE_OBJECTS" => Ok(TableFactor::Dictionary {
+                    dict: Dictionary::GlueObjects,
+                    alias: alias_or_name,
+                }),
                 "GLUE_TABLES" => Ok(TableFactor::Dictionary {
                     dict: Dictionary::GlueTables,
                     alias: alias_or_name,

diff --git a/pkg/javascript/web/src/payload.rs b/pkg/javascript/web/src/payload.rs
@@ -60,23 +60,6 @@ fn convert_payload(payload: Payload) -> Json {
                 "columns": Json::Array(columns),
             })
         }
-        Payload::ShowIndexes(indexes) => {
-            let indexes = indexes
-                .into_iter()
-                .map(|index| {
-                    json!({
-                        "name": index.name,
-                        "order": index.order.to_owned(),
-                        "description": index.expr.to_sql(),
-                    })
-                })
-                .collect();
-
-            json!({
-                "type": "SHOW INDEXES",
-                "indexes": Json::Array(indexes),
-            })
-        }
         Payload::Insert(num) => json!({
             "type": "INSERT",
             "affected": num

diff --git a/storages/sled-storage/src/alter_table.rs b/storages/sled-storage/src/alter_table.rs
@@ -53,6 +53,7 @@ impl AlterTable for SledStorage {
             let Schema {
                 column_defs,
                 indexes,
+                created,
                 ..
             } = old_schema
                 .ok_or_else(|| AlterTableError::TableNotFound(table_name.to_owned()).into())
@@ -62,6 +63,7 @@ impl AlterTable for SledStorage {
                 table_name: new_table_name.to_owned(),
                 column_defs,
                 indexes,
+                created,
             };
 
             bincode::serialize(&old_snapshot)
@@ -158,6 +160,7 @@ impl AlterTable for SledStorage {
             let Schema {
                 column_defs,
                 indexes,
+                created,
                 ..
             } = snapshot
                 .get(txid, None)
@@ -185,6 +188,7 @@ impl AlterTable for SledStorage {
                 table_name: table_name.to_owned(),
                 column_defs,
                 indexes,
+                created,
             };
             let (snapshot, _) = snapshot.update(txid, schema);
             let value = bincode::serialize(&snapshot)
@@ -235,6 +239,8 @@ impl AlterTable for SledStorage {
                 table_name,
                 column_defs,
                 indexes,
+                created,
+                ..
             } = schema_snapshot
                 .get(txid, None)
                 .ok_or_else(|| AlterTableError::TableNotFound(table_name.to_owned()).into())
@@ -308,6 +314,7 @@ impl AlterTable for SledStorage {
                 table_name,
                 column_defs,
                 indexes,
+                created,
             };
             let (schema_snapshot, _) = schema_snapshot.update(txid, schema);
             let schema_value = bincode::serialize(&schema_snapshot)
@@ -362,6 +369,8 @@ impl AlterTable for SledStorage {
                 table_name,
                 column_defs,
                 indexes,
+                created,
+                ..
             } = schema_snapshot
                 .get(txid, None)
                 .ok_or_else(|| AlterTableError::TableNotFound(table_name.to_owned()).into())
@@ -428,6 +437,7 @@ impl AlterTable for SledStorage {
                 table_name,
                 column_defs,
                 indexes,
+                created,
             };
             let (schema_snapshot, _) = schema_snapshot.update(txid, schema);
             let schema_value = bincode::serialize(&schema_snapshot)