Repository: matrixorigin/matrixone
Pull request #11955 (2023-09-28 14:48:18)

add variables view
## What type of PR is this?

- [ ] API-change
- [ ] BUG
- [ ] Improvement
- [ ] Documentation
- [x] Feature
- [ ] Test and CI
- [ ] Code Refactoring

## Which issue(s) this PR fixes:

issue #11712 

## What this PR does / why we need it:

支持系统变量视图。
讨论见： https://github.com/matrixorigin/docs/blob/main/design/system_view.md#%E7%B3%BB%E7%BB%9F%E5%8F%98%E9%87%8F-1

数据来自兼容性表。


Patch:
diff --git a/pkg/cnservice/upgrader/new_add_table.go b/pkg/cnservice/upgrader/new_add_table.go
index 3dbefdc6b..6212b22a8 100644
--- a/pkg/cnservice/upgrader/new_add_table.go
+++ b/pkg/cnservice/upgrader/new_add_table.go
@@ -243,6 +243,24 @@ var MoLocksView = &table.Table{
 	CreateTableSql: "drop view `mo_catalog`.`mo_locks`;",
 }
 
+var MoVariablesView = &table.Table{
+	Account:  table.AccountAll,
+	Database: catalog.MO_CATALOG,
+	Table:    "mo_variables",
+	Columns: []table.Column{
+		table.StringColumn("configuration_id", "the id of configuration"),
+		table.StringColumn("account_id", ""),
+		table.StringColumn("account_name", ""),
+		table.StringColumn("dat_name", "database name"),
+		table.StringColumn("variable_name", "the name of variable"),
+		table.StringColumn("variable_value", "the value of variable"),
+		table.StringColumn("system_variables", "is system variable or not"),
+	},
+	CreateViewSql: "CREATE VIEW IF NOT EXISTS `mo_catalog`.`mo_variables` AS SELECT * FROM mo_catalog.mo_mysql_compatibility_mode;",
+	//actually drop view here
+	CreateTableSql: "drop view `mo_catalog`.`mo_variables`;",
+}
+
 var SqlStatementHotspotView = &table.Table{
 	Account:  table.AccountAll,
 	Database: catalog.MO_SYSTEM,
@@ -254,5 +272,5 @@ var SqlStatementHotspotView = &table.Table{
 	CreateTableSql: "DROP VIEW IF EXISTS `system`.`sql_statement_hotspot`;",
 }
 
-var registeredViews = []*table.Table{processlistView, MoLocksView}
-var needUpgradeNewView = []*table.Table{PARTITIONSView, STATISTICSView, MoSessionsView, SqlStatementHotspotView, MoLocksView, MoConfigurationsView}
+var registeredViews = []*table.Table{processlistView, MoLocksView, MoVariablesView}
+var needUpgradeNewView = []*table.Table{PARTITIONSView, STATISTICSView, MoSessionsView, SqlStatementHotspotView, MoLocksView, MoConfigurationsView, MoVariablesView}
diff --git a/pkg/cnservice/upgrader/upgrader.go b/pkg/cnservice/upgrader/upgrader.go
index 0717d0835..cc803f108 100644
--- a/pkg/cnservice/upgrader/upgrader.go
+++ b/pkg/cnservice/upgrader/upgrader.go
@@ -222,6 +222,11 @@ func (u *Upgrader) UpgradeNewViewColumn(ctx context.Context) error {
 			return err
 		}
 
+		//if there is no view, skip
+		if currentSchema == nil {
+			continue
+		}
+
 		diff, err := u.GenerateDiff(currentSchema, tbl)
 		if err != nil {
 			return err
diff --git a/pkg/frontend/authenticate.go b/pkg/frontend/authenticate.go
index f61fc0e5e..7206e8130 100644
--- a/pkg/frontend/authenticate.go
+++ b/pkg/frontend/authenticate.go
@@ -821,6 +821,7 @@ var (
 		"mo_sessions":                 0,
 		"mo_configurations":           0,
 		"mo_locks":                    0,
+		"mo_variables":                0,
 	}
 	configInitVariables = map[string]int8{
 		"save_query_result":      0,
@@ -850,6 +851,7 @@ var (
 		"mo_sessions":                 0,
 		"mo_configurations":           0,
 		"mo_locks":                    0,
+		"mo_variables":                0,
 	}
 	createDbInformationSchemaSql = "create database information_schema;"
 	createAutoTableSql           = fmt.Sprintf(`create table if not exists %s (
@@ -1027,6 +1029,7 @@ var (
 		`CREATE VIEW IF NOT EXISTS mo_sessions AS SELECT * FROM mo_sessions() AS mo_sessions_tmp;`,
 		`CREATE VIEW IF NOT EXISTS mo_configurations AS SELECT * FROM mo_configurations() AS mo_configurations_tmp;`,
 		`CREATE VIEW IF NOT EXISTS mo_locks AS SELECT * FROM mo_locks() AS mo_locks_tmp;`,
+		`CREATE VIEW IF NOT EXISTS mo_variables AS SELECT * FROM mo_catalog.mo_mysql_compatibility_mode;`,
 	}
 
 	//drop tables for the tenant
@@ -1043,9 +1046,9 @@ var (
 		`drop view if exists mo_catalog.mo_sessions;`,
 		`drop view if exists mo_catalog.mo_configurations;`,
 		`drop view if exists mo_catalog.mo_locks;`,
+		`drop view if exists mo_catalog.mo_variables;`,
 	}
 	dropMoPubsSql         = `drop table if exists mo_catalog.mo_pubs;`
-	deleteMoPubsSql       = `delete from mo_catalog.mo_pubs;`
 	dropAutoIcrColSql     = fmt.Sprintf("drop table if exists mo_catalog.`%s`;", catalog.MOAutoIncrTable)
 	dropMoIndexes         = fmt.Sprintf(`drop table if exists %s.%s;`, catalog.MO_CATALOG, catalog.MO_INDEXES)
 	dropMoTablePartitions = fmt.Sprintf(`drop table if exists %s.%s;`, catalog.MO_CATALOG, catalog.MO_TABLE_PARTITIONS)
@@ -4130,14 +4133,6 @@ func doDropAccount(ctx context.Context, ses *Session, da *tree.DropAccount) (err
 			}
 		}
 
-		// delete all publications
-
-		err = bh.Exec(deleteCtx, deleteMoPubsSql)
-
-		if err != nil {
-			return err
-		}
-
 		//drop databases created by user
 		databases = make(map[string]int8)
 		dbSql = "show databases;"


Test patch:
diff --git a/test/distributed/cases/dml/select/sp_table.result b/test/distributed/cases/dml/select/sp_table.result
index 3ac00eca2..639433f04 100644
--- a/test/distributed/cases/dml/select/sp_table.result
+++ b/test/distributed/cases/dml/select/sp_table.result
@@ -23,3 +23,4 @@ mo_tables    r
 mo_user    r
 mo_user_defined_function    r
 mo_user_grant    r
+mo_variables    v
\ No newline at end of file
diff --git a/test/distributed/cases/dml/show/database_statistics.result b/test/distributed/cases/dml/show/database_statistics.result
index e4e7d1242..34e052e5f 100644
--- a/test/distributed/cases/dml/show/database_statistics.result
+++ b/test/distributed/cases/dml/show/database_statistics.result
@@ -349,7 +349,7 @@ Number of tables in mysql
 5
 show table_number from mo_catalog;
 Number of tables in mo_catalog
-18
+19
 show table_number from system_metrics;
 Number of tables in system_metrics
 7
diff --git a/test/distributed/cases/dml/show/show.result b/test/distributed/cases/dml/show/show.result
index a403cf744..6ce0b7607 100644
--- a/test/distributed/cases/dml/show/show.result
+++ b/test/distributed/cases/dml/show/show.result
@@ -241,6 +241,7 @@ mo_database
 mo_columns
 mo_tables
 mo_locks
+mo_variables
 show table_number from mo_catalog;
 Number of tables in mo_catalog
 19
diff --git a/test/distributed/cases/mo_cloud/mo_cloud.result b/test/distributed/cases/mo_cloud/mo_cloud.result
index 615950d26..f34f874da 100644
--- a/test/distributed/cases/mo_cloud/mo_cloud.result
+++ b/test/distributed/cases/mo_cloud/mo_cloud.result
@@ -596,7 +596,7 @@ system_metrics    7    accountadmin
 information_schema    16    accountadmin
 mysql    5    accountadmin
 mo_mo    0    accountadmin
-mo_catalog    18    -
+mo_catalog    19    -
 SELECT mo_catalog.mo_database.datname,mo_catalog.mo_tables.relname,mo_catalog.mo_tables.relkind, if (role_name IS NULL,'-', role_name) AS `owner` FROM mo_catalog.mo_database LEFT JOIN mo_catalog.mo_tables ON mo_catalog.mo_database.datname = mo_catalog.mo_tables.reldatabase LEFT JOIN mo_catalog.mo_role ON mo_catalog.mo_tables.owner=role_id ORDER BY reldatabase, relname;
 datname    relname    relkind    owner
 mo_mo    null    null    -
@@ -635,6 +635,7 @@ mo_catalog    mo_tables    r    -
 mo_catalog    mo_user    r    accountadmin
 mo_catalog    mo_user_defined_function    r    accountadmin
 mo_catalog    mo_user_grant    r    accountadmin
+mo_catalog    mo_variables    v    accountadmin
 mysql    columns_priv    r    accountadmin
 mysql    db    r    accountadmin
 mysql    procs_priv    r    accountadmin
diff --git a/test/distributed/cases/tenant/privilege/create_user_default_role.result b/test/distributed/cases/tenant/privilege/create_user_default_role.result
index b57981300..e42b84b49 100644
--- a/test/distributed/cases/tenant/privilege/create_user_default_role.result
+++ b/test/distributed/cases/tenant/privilege/create_user_default_role.result
@@ -40,6 +40,7 @@ mo_database
 mo_sessions
 mo_configurations
 mo_locks
+mo_variables
 create database t;
 internal error: do not have privilege to execute the statement
 grant create database on account * to r1;
diff --git a/test/distributed/cases/tenant/tenant.result b/test/distributed/cases/tenant/tenant.result
index 7ef22d577..fd20c13e2 100644
--- a/test/distributed/cases/tenant/tenant.result
+++ b/test/distributed/cases/tenant/tenant.result
@@ -29,4 +29,5 @@ account_id    relname    relkind
 0    mo_user    r
 0    mo_user_defined_function    r
 0    mo_user_grant    r
+0    mo_variables    v
 set global enable_privilege_cache = on;
\ No newline at end of file
diff --git a/test/distributed/cases/zz_accesscontrol/create_account.result b/test/distributed/cases/zz_accesscontrol/create_account.result
index d770f2b9c..9ef136cfb 100644
--- a/test/distributed/cases/zz_accesscontrol/create_account.result
+++ b/test/distributed/cases/zz_accesscontrol/create_account.result
@@ -175,6 +175,7 @@ mo_columns
 mo_sessions
 mo_configurations
 mo_locks
+mo_variables
 select user_name,authentication_string,owner from mo_user;
 user_name    authentication_string    owner
 admin    *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9    2