FlyingZC commented 1 year ago

Background

Hi community. ShardingSphere is committed to providing a multi-source heterogeneous, enhanced database platform and further building an ecosystem around the upper layer of the platform. So ShardingSphere needs to be compatible with the inquiries of the system database of various types of databases, such as MySQL, PostgreSQL, and OpenGAUSS.

We are ready to be divided into two stages to implement this feature:

Phase 1: Mock all system database tables and view query, return to the empty result;

Phase 2: Adapt to the query of each table and view in turn, and return the correct result.

At this stage we only need to complete Phase one, the tasks are split as follows.

Tasks

mysql

The system schemas included in mysql are: information_schema, mysql, performance_schema, sys. information_schema schema has 61 tables; mysql schema has 31 tables; performance_schema has 87 tables; sys has 1 table and 100 views.

They are as follows:

information_schema

[x] CHARACTER_SETS
[x] COLLATION_CHARACTER_SET_APPLICABILITY
[x] COLLATIONS
[x] COLUMN_PRIVILEGES
[x] COLUMNS
[x] ENGINES
[x] EVENTS
[x] FILES
[x] GLOBAL_STATUS
[x] GLOBAL_VARIABLES
[x] INNODB_BUFFER_PAGE
[x] INNODB_BUFFER_PAGE_LRU
[x] INNODB_BUFFER_POOL_STATS
[x] INNODB_CMP
[x] INNODB_CMP_PER_INDEX
[x] INNODB_CMP_PER_INDEX_RESET
[x] INNODB_CMP_RESET
[x] INNODB_CMPMEM
[x] INNODB_CMPMEM_RESET
[x] INNODB_FT_BEING_DELETED
[x] INNODB_FT_CONFIG
[x] INNODB_FT_DEFAULT_STOPWORD
[x] INNODB_FT_DELETED
[x] INNODB_FT_INDEX_CACHE
[x] INNODB_FT_INDEX_TABLE
[x] INNODB_LOCK_WAITS
[x] INNODB_LOCKS
[x] INNODB_METRICS
[x] INNODB_SYS_COLUMNS
[x] INNODB_SYS_DATAFILES
[x] INNODB_SYS_FIELDS
[x] INNODB_SYS_FOREIGN
[x] INNODB_SYS_FOREIGN_COLS
[x] INNODB_SYS_INDEXES
[x] INNODB_SYS_TABLES
[x] INNODB_SYS_TABLESPACES
[x] INNODB_SYS_TABLESTATS
[x] INNODB_SYS_VIRTUAL
[x] INNODB_TEMP_TABLE_INFO
[x] INNODB_TRX
[x] KEY_COLUMN_USAGE
[x] OPTIMIZER_TRACE
[x] PARAMETERS
[x] PARTITIONS
[x] PLUGINS
[x] PROCESSLIST
[x] PROFILING
[x] REFERENTIAL_CONSTRAINTS
[x] ROUTINES
[x] SCHEMA_PRIVILEGES
[x] SCHEMATA
[x] SESSION_STATUS
[x] SESSION_VARIABLES
[x] STATISTICS
[x] TABLE_CONSTRAINTS
[x] TABLE_PRIVILEGES
[x] TABLES
[x] TABLESPACES
[x] TRIGGERS
[x] USER_PRIVILEGES
[x] VIEWS

mysql

[x] columns_priv
[x] db
[x] engine_cost
[x] event
[x] func
[x] general_log
[x] gtid_executed
[x] help_category
[x] help_keyword
[x] help_relation
[x] help_topic
[x] innodb_index_stats
[x] innodb_table_stats
[x] ndb_binlog_index
[x] plugin
[x] proc
[x] procs_priv
[x] proxies_priv
[x] server_cost
[x] servers
[x] slave_master_info
[x] slave_relay_log_info
[x] slave_worker_info
[x] slow_log
[x] tables_priv
[x] time_zone
[x] time_zone_leap_second
[x] time_zone_name
[x] time_zone_transition
[x] time_zone_transition_type
[x] user

performance_schema

[x] accounts
[x] cond_instances
[x] events_stages_current
[x] events_stages_history
[x] events_stages_history_long
[x] events_stages_summary_by_account_by_event_name
[x] events_stages_summary_by_host_by_event_name
[x] events_stages_summary_by_thread_by_event_name
[x] events_stages_summary_by_user_by_event_name
[x] events_stages_summary_global_by_event_name
[x] events_statements_current
[x] events_statements_history
[x] events_statements_history_long
[x] events_statements_summary_by_account_by_event_name
[x] events_statements_summary_by_digest
[x] events_statements_summary_by_host_by_event_name
[x] events_statements_summary_by_program
[x] events_statements_summary_by_thread_by_event_name
[x] events_statements_summary_by_user_by_event_name
[x] events_statements_summary_global_by_event_name
[x] events_transactions_current
[x] events_transactions_history
[x] events_transactions_history_long
[x] events_transactions_summary_by_account_by_event_name
[x] events_transactions_summary_by_host_by_event_name
[x] events_transactions_summary_by_thread_by_event_name
[x] events_transactions_summary_by_user_by_event_name
[x] events_transactions_summary_global_by_event_name
[x] events_waits_current
[x] events_waits_history
[x] events_waits_history_long
[x] events_waits_summary_by_account_by_event_name
[x] events_waits_summary_by_host_by_event_name
[x] events_waits_summary_by_instance
[x] events_waits_summary_by_thread_by_event_name
[x] events_waits_summary_by_user_by_event_name
[x] events_waits_summary_global_by_event_name
[x] file_instances
[x] file_summary_by_event_name
[x] file_summary_by_instance
[x] global_status
[x] global_variables
[x] host_cache
[x] hosts
[x] memory_summary_by_account_by_event_name
[x] memory_summary_by_host_by_event_name
[x] memory_summary_by_thread_by_event_name
[x] memory_summary_by_user_by_event_name
[x] memory_summary_global_by_event_name
[x] metadata_locks
[x] mutex_instances
[x] objects_summary_global_by_type
[x] performance_timers
[x] prepared_statements_instances
[x] replication_applier_configuration
[x] replication_applier_status
[x] replication_applier_status_by_coordinator
[x] replication_applier_status_by_worker
[x] replication_connection_configuration
[x] replication_connection_status
[x] replication_group_member_stats
[x] replication_group_members
[x] rwlock_instances
[x] session_account_connect_attrs
[x] session_connect_attrs
[x] session_status
[x] session_variables
[x] setup_actors
[x] setup_consumers
[x] setup_instruments
[x] setup_objects
[x] setup_timers
[x] socket_instances
[x] socket_summary_by_event_name
[x] socket_summary_by_instance
[x] status_by_account
[x] status_by_host
[x] status_by_thread
[x] status_by_user
[x] table_handles
[x] table_io_waits_summary_by_index_usage
[x] table_io_waits_summary_by_table
[x] table_lock_waits_summary_by_table
[x] threads
[x] user_variables_by_thread
[x] users
[x] variables_by_thread

sys

tables

[x] sys_config

views

[x] host_summary
[x] host_summary_by_file_io
[x] host_summary_by_file_io_type
[x] host_summary_by_stages
[x] host_summary_by_statement_latency
[x] host_summary_by_statement_type
[x] innodb_buffer_stats_by_schema
[x] innodb_buffer_stats_by_table
[x] innodb_lock_waits
[x] io_by_thread_by_latency
[x] io_global_by_file_by_bytes
[x] io_global_by_file_by_latency
[x] io_global_by_wait_by_bytes
[x] io_global_by_wait_by_latency
[x] latest_file_io
[x] memory_by_host_by_current_bytes
[x] memory_by_thread_by_current_bytes
[x] memory_by_user_by_current_bytes
[x] memory_global_by_current_bytes
[x] memory_global_total
[x] metrics
[x] processlist
[x] ps_check_lost_instrumentation
[x] schema_auto_increment_columns
[x] schema_index_statistics
[x] schema_object_overview
[x] schema_redundant_indexes
[x] schema_table_lock_waits
[x] schema_table_statistics
[x] schema_table_statistics_with_buffer
[x] schema_tables_with_full_table_scans
[x] schema_unused_indexes
[x] session
[x] session_ssl_status
[x] statement_analysis
[x] statements_with_errors_or_warnings
[x] statements_with_full_table_scans
[x] statements_with_runtimes_in_95th_percentile
[x] statements_with_sorting
[x] statements_with_temp_tables
[x] user_summary
[x] user_summary_by_file_io
[x] user_summary_by_file_io_type
[x] user_summary_by_stages
[x] user_summary_by_statement_latency
[x] user_summary_by_statement_type
[x] version
[x] wait_classes_global_by_avg_latency
[x] wait_classes_global_by_latency
[x] waits_by_host_by_latency
[x] waits_by_user_by_latency
[x] waits_global_by_latency
[x] x$host_summary
[x] x$host_summary_by_file_io
[x] x$host_summary_by_file_io_type
[x] x$host_summary_by_stages
[x] x$host_summary_by_statement_latency
[x] x$host_summary_by_statement_type
[x] x$innodb_buffer_stats_by_schema
[x] x$innodb_buffer_stats_by_table
[x] x$innodb_lock_waits
[x] x$io_by_thread_by_latency
[x] x$io_global_by_file_by_bytes
[x] x$io_global_by_file_by_latency
[x] x$io_global_by_wait_by_bytes
[x] x$io_global_by_wait_by_latency
[x] x$latest_file_io
[x] x$memory_by_host_by_current_bytes
[x] x$memory_by_thread_by_current_bytes
[x] x$memory_by_user_by_current_bytes
[x] x$memory_global_by_current_bytes
[x] x$memory_global_total
[x] x$processlist
[x] x$ps_digest_95th_percentile_by_avg_us
[x] x$ps_digest_avg_latency_distribution
[x] x$ps_schema_table_statistics_io
[x] x$schema_flattened_keys
[x] x$schema_index_statistics
[x] x$schema_table_lock_waits
[x] x$schema_table_statistics
[x] x$schema_table_statistics_with_buffer
[x] x$schema_tables_with_full_table_scans
[x] x$session
[x] x$statement_analysis
[x] x$statements_with_errors_or_warnings
[x] x$statements_with_full_table_scans
[x] x$statements_with_runtimes_in_95th_percentile
[x] x$statements_with_sorting
[x] x$statements_with_temp_tables
[x] x$user_summary
[x] x$user_summary_by_file_io
[x] x$user_summary_by_file_io_type
[x] x$user_summary_by_stages
[x] x$user_summary_by_statement_latency
[x] x$user_summary_by_statement_type
[x] x$wait_classes_global_by_avg_latency
[x] x$wait_classes_global_by_latency
[x] x$waits_by_host_by_latency
[x] x$waits_by_user_by_latency
[x] x$waits_global_by_latency

PostgreSQL

PostgreSQL contains two system schemas, information_schema and pg_catalog. information_schema has 4 tables and 65 views. pg_catalog has 62 tables and 72 views. They are as follows:

information_schema

tables

[x] sql_features
[x] sql_implementation_info
[x] sql_parts
[x] sql_sizing

views

[x] _pg_foreign_data_wrappers
[x] _pg_foreign_servers
[x] _pg_foreign_table_columns
[x] _pg_foreign_tables
[x] _pg_user_mappings
[x] administrable_role_authorizations
[x] applicable_roles
[x] attributes
[x] character_sets
[x] check_constraint_routine_usage
[x] check_constraints
[x] collation_character_set_applicability
[x] collations
[x] column_column_usage
[x] column_domain_usage
[x] column_options
[x] column_privileges
[x] column_udt_usage
[x] columns
[x] constraint_column_usage
[x] constraint_table_usage
[x] data_type_privileges
[x] domain_constraints
[x] domain_udt_usage
[x] domains
[x] element_types
[x] enabled_roles
[x] foreign_data_wrapper_options
[x] foreign_data_wrappers
[x] foreign_server_options
[x] foreign_servers
[x] foreign_table_options
[x] foreign_tables
[x] information_schema_catalog_name
[x] key_column_usage
[x] parameters
[x] referential_constraints
[x] role_column_grants
[x] role_routine_grants
[x] role_table_grants
[x] role_udt_grants
[x] role_usage_grants
[x] routine_column_usage
[x] routine_privileges
[x] routine_routine_usage
[x] routine_sequence_usage
[x] routine_table_usage
[x] routines
[x] schemata
[x] sequences
[x] table_constraints
[x] table_privileges
[x] tables
[x] transforms
[x] triggered_update_columns
[x] triggers
[x] udt_privileges
[x] usage_privileges
[x] user_defined_types
[x] user_mapping_options
[x] user_mappings
[x] view_column_usage
[x] view_routine_usage
[x] view_table_usage
[x] views

pg_catalog

tables

[x] pg_aggregate
[x] pg_am
[x] pg_amop
[x] pg_amproc
[x] pg_attrdef
[x] pg_attribute
[x] pg_auth_members
[x] pg_authid
[x] pg_cast
[x] pg_class
[x] pg_collation
[x] pg_constraint
[x] pg_conversion
[x] pg_database
[x] pg_db_role_setting
[x] pg_default_acl
[x] pg_depend
[x] pg_description
[x] pg_enum
[x] pg_event_trigger
[x] pg_extension
[x] pg_foreign_data_wrapper
[x] pg_foreign_server
[x] pg_foreign_table
[x] pg_index
[x] pg_inherits
[x] pg_init_privs
[x] pg_language
[x] pg_largeobject
[x] pg_largeobject_metadata
[x] pg_namespace
[x] pg_opclass
[x] pg_operator
[x] pg_opfamily
[x] pg_partitioned_table
[x] pg_policy
[x] pg_proc
[x] pg_publication
[x] pg_publication_rel
[x] pg_range
[x] pg_replication_origin
[x] pg_rewrite
[x] pg_seclabel
[x] pg_sequence
[x] pg_shdepend
[x] pg_shdescription
[x] pg_shseclabel
[x] pg_statistic
[x] pg_statistic_ext
[x] pg_statistic_ext_data
[x] pg_subscription
[x] pg_subscription_rel
[x] pg_tablespace
[x] pg_transform
[x] pg_trigger
[x] pg_ts_config
[x] pg_ts_config_map
[x] pg_ts_dict
[x] pg_ts_parser
[x] pg_ts_template
[x] pg_type
[x] pg_user_mapping

views

[x] pg_available_extension_versions
[x] pg_available_extensions
[x] pg_backend_memory_contexts
[x] pg_config
[x] pg_cursors
[x] pg_file_settings
[x] pg_group
[x] pg_hba_file_rules
[x] pg_indexes
[x] pg_locks
[x] pg_matviews
[x] pg_policies
[x] pg_prepared_statements
[x] pg_prepared_xacts
[x] pg_publication_tables
[x] pg_replication_origin_status
[x] pg_replication_slots
[x] pg_roles
[x] pg_rules
[x] pg_seclabels
[x] pg_sequences
[x] pg_settings
[x] pg_shadow
[x] pg_shmem_allocations
[x] pg_stat_activity
[x] pg_stat_all_indexes
[x] pg_stat_all_tables
[x] pg_stat_archiver
[x] pg_stat_bgwriter
[x] pg_stat_database
[x] pg_stat_database_conflicts
[x] pg_stat_gssapi
[x] pg_stat_progress_analyze
[x] pg_stat_progress_basebackup
[x] pg_stat_progress_cluster
[x] pg_stat_progress_copy
[x] pg_stat_progress_create_index
[x] pg_stat_progress_vacuum
[x] pg_stat_replication
[x] pg_stat_replication_slots
[x] pg_stat_slru
[x] pg_stat_ssl
[x] pg_stat_subscription
[x] pg_stat_sys_indexes
[x] pg_stat_sys_tables
[x] pg_stat_user_functions
[x] pg_stat_user_indexes
[x] pg_stat_user_tables
[x] pg_stat_wal
[x] pg_stat_wal_receiver
[x] pg_stat_xact_all_tables
[x] pg_stat_xact_sys_tables
[x] pg_stat_xact_user_functions
[x] pg_stat_xact_user_tables
[x] pg_statio_all_indexes
[x] pg_statio_all_sequences
[x] pg_statio_all_tables
[x] pg_statio_sys_indexes
[x] pg_statio_sys_sequences
[x] pg_statio_sys_tables
[x] pg_statio_user_indexes
[x] pg_statio_user_sequences
[x] pg_statio_user_tables
[x] pg_stats
[x] pg_stats_ext
[x] pg_stats_ext_exprs
[x] pg_tables
[x] pg_timezone_abbrevs
[x] pg_timezone_names
[x] pg_user
[x] pg_user_mappings
[x] pg_views

openGauss

openGauss has two system schemas, information_schema and pg_cataglog. information_schema has 7 tables, 59 views. pg_cataglog has 115 tables, 125 views. They are as follows:

information_schema

Tables

[x] sql_features
[x] sql_implementation_info
[x] sql_languages
[x] sql_packages
[x] sql_parts
[x] sql_sizing
[x] sql_sizing_profiles

Views

[x] _pg_foreign_data_wrappers
[x] _pg_foreign_servers
[x] _pg_foreign_table_columns
[x] _pg_foreign_tables
[x] _pg_user_mappings
[x] administrable_role_authorizations
[x] applicable_roles
[x] attributes
[x] character_sets
[x] check_constraint_routine_usage
[x] check_constraints
[x] collation_character_set_applicability
[x] collations
[x] column_domain_usage
[x] column_options
[x] column_privileges
[x] column_udt_usage
[x] columns
[x] constraint_column_usage
[x] constraint_table_usage
[x] data_type_privileges
[x] domain_constraints
[x] domain_udt_usage
[x] domains
[x] element_types
[x] enabled_roles
[x] foreign_data_wrapper_options
[x] foreign_data_wrappers
[x] foreign_server_options
[x] foreign_servers
[x] foreign_table_options
[x] foreign_tables
[x] information_schema_catalog_name
[x] key_column_usage
[x] parameters
[x] referential_constraints
[x] role_column_grants
[x] role_routine_grants
[x] role_table_grants
[x] role_udt_grants
[x] role_usage_grants
[x] routine_privileges
[x] routines
[x] schemata
[x] sequences
[x] table_constraints
[x] table_privileges
[x] tables
[x] triggered_update_columns
[x] triggers
[x] udt_privileges
[x] usage_privileges
[x] user_defined_types
[x] user_mapping_options
[x] user_mappings
[x] view_column_usage
[x] view_routine_usage
[x] view_table_usage
[x] views

pg_cataglog

Tables

[x] gs_asp
[x] gs_auditing_policy
[x] gs_auditing_policy_access
[x] gs_auditing_policy_filters
[x] gs_auditing_policy_privileges
[x] gs_client_global_keys
[x] gs_client_global_keys_args
[x] gs_column_keys
[x] gs_column_keys_args
[x] gs_db_privilege
[x] gs_encrypted_columns
[x] gs_encrypted_proc
[x] gs_global_chain
[x] gs_global_config
[x] gs_job_argument
[x] gs_job_attribute
[x] gs_masking_policy
[x] gs_masking_policy_actions
[x] gs_masking_policy_filters
[x] gs_matview
[x] gs_matview_dependency
[x] gs_model_warehouse
[x] gs_obsscaninfo
[x] gs_opt_model
[x] gs_package
[x] gs_policy_label
[x] gs_recyclebin
[x] gs_txn_snapshot
[x] gs_uid
[x] gs_wlm_ec_operator_info
[x] gs_wlm_instance_history
[x] gs_wlm_operator_info
[x] gs_wlm_plan_encoding_table
[x] gs_wlm_plan_operator_info
[x] gs_wlm_session_query_info_all
[x] gs_wlm_user_resource_history
[x] pg_aggregate
[x] pg_am
[x] pg_amop
[x] pg_amproc
[x] pg_app_workloadgroup_mapping
[x] pg_attrdef
[x] pg_attribute
[x] pg_auth_history
[x] pg_auth_members
[x] pg_authid
[x] pg_cast
[x] pg_class
[x] pg_collation
[x] pg_constraint
[x] pg_conversion
[x] pg_database
[x] pg_db_role_setting
[x] pg_default_acl
[x] pg_depend
[x] pg_description
[x] pg_directory
[x] pg_enum
[x] pg_extension
[x] pg_extension_data_source
[x] pg_foreign_data_wrapper
[x] pg_foreign_server
[x] pg_foreign_table
[x] pg_hashbucket
[x] pg_index
[x] pg_inherits
[x] pg_job
[x] pg_job_proc
[x] pg_language
[x] pg_largeobject
[x] pg_largeobject_metadata
[x] pg_namespace
[x] pg_object
[x] pg_obsscaninfo
[x] pg_opclass
[x] pg_operator
[x] pg_opfamily
[x] pg_partition
[x] pg_pltemplate
[x] pg_proc
[x] pg_publication
[x] pg_publication_rel
[x] pg_range
[x] pg_replication_origin
[x] pg_resource_pool
[x] pg_rewrite
[x] pg_rlspolicy
[x] pg_seclabel
[x] pg_shdepend
[x] pg_shdescription
[x] pg_shseclabel
[x] pg_statistic
[x] pg_statistic_ext
[x] pg_subscription
[x] pg_synonym
[x] pg_tablespace
[x] pg_trigger
[x] pg_ts_config
[x] pg_ts_config_map
[x] pg_ts_dict
[x] pg_ts_parser
[x] pg_ts_template
[x] pg_type
[x] pg_user_mapping
[x] pg_user_status
[x] pg_workload_group
[x] pgxc_class
[x] pgxc_group
[x] pgxc_node
[x] pgxc_slice
[x] plan_table_data
[x] statement_history
[x] streaming_cont_query
[x] streaming_reaper_status
[x] streaming_stream

Views

[x] get_global_prepared_xacts
[x] gs_all_control_group_info
[x] gs_auditing
[x] gs_auditing_access
[x] gs_auditing_privilege
[x] gs_cluster_resource_info
[x] gs_comm_proxy_thread_status
[x] gs_db_privileges
[x] gs_file_stat
[x] gs_get_control_group_info
[x] gs_gsc_memory_detail
[x] gs_instance_time
[x] gs_labels
[x] gs_lsc_memory_detail
[x] gs_masking
[x] gs_matviews
[x] gs_os_run_info
[x] gs_redo_stat
[x] gs_session_cpu_statistics
[x] gs_session_memory
[x] gs_session_memory_context
[x] gs_session_memory_detail
[x] gs_session_memory_statistics
[x] gs_session_stat
[x] gs_session_time
[x] gs_shared_memory_detail
[x] gs_sql_count
[x] gs_stat_session_cu
[x] gs_thread_memory_context
[x] gs_total_memory_detail
[x] gs_total_nodegroup_memory_detail
[x] gs_wlm_cgroup_info
[x] gs_wlm_ec_operator_history
[x] gs_wlm_ec_operator_statistics
[x] gs_wlm_operator_history
[x] gs_wlm_operator_statistics
[x] gs_wlm_plan_operator_history
[x] gs_wlm_rebuild_user_resource_pool
[x] gs_wlm_resource_pool
[x] gs_wlm_session_history
[x] gs_wlm_session_info
[x] gs_wlm_session_info_all
[x] gs_wlm_session_statistics
[x] gs_wlm_user_info
[x] gs_wlm_workload_records
[x] mpp_tables
[x] pg_available_extension_versions
[x] pg_available_extensions
[x] pg_comm_delay
[x] pg_comm_recv_stream
[x] pg_comm_send_stream
[x] pg_comm_status
[x] pg_control_group_config
[x] pg_cursors
[x] pg_ext_stats
[x] pg_get_invalid_backends
[x] pg_get_senders_catchup_time
[x] pg_group
[x] pg_gtt_attached_pids
[x] pg_gtt_relstats
[x] pg_gtt_stats
[x] pg_indexes
[x] pg_locks
[x] pg_node_env
[x] pg_os_threads
[x] pg_prepared_statements
[x] pg_prepared_xacts
[x] pg_publication_tables
[x] pg_replication_origin_status
[x] pg_replication_slots
[x] pg_rlspolicies
[x] pg_roles
[x] pg_rules
[x] pg_running_xacts
[x] pg_seclabels
[x] pg_session_iostat
[x] pg_session_wlmstat
[x] pg_settings
[x] pg_shadow
[x] pg_stat_activity
[x] pg_stat_activity_ng
[x] pg_stat_all_indexes
[x] pg_stat_all_tables
[x] pg_stat_bad_block
[x] pg_stat_bgwriter
[x] pg_stat_database
[x] pg_stat_database_conflicts
[x] pg_stat_replication
[x] pg_stat_subscription
[x] pg_stat_sys_indexes
[x] pg_stat_sys_tables
[x] pg_stat_user_functions
[x] pg_stat_user_indexes
[x] pg_stat_user_tables
[x] pg_stat_xact_all_tables
[x] pg_stat_xact_sys_tables
[x] pg_stat_xact_user_functions
[x] pg_stat_xact_user_tables
[x] pg_statio_all_indexes
[x] pg_statio_all_sequences
[x] pg_statio_all_tables
[x] pg_statio_sys_indexes
[x] pg_statio_sys_sequences
[x] pg_statio_sys_tables
[x] pg_statio_user_indexes
[x] pg_statio_user_sequences
[x] pg_statio_user_tables
[x] pg_stats
[x] pg_tables
[x] pg_tde_info
[x] pg_thread_wait_status
[x] pg_timezone_abbrevs
[x] pg_timezone_names
[x] pg_total_memory_detail
[x] pg_total_user_resource_info
[x] pg_total_user_resource_info_oid
[x] pg_user
[x] pg_user_mappings
[x] pg_variable_info
[x] pg_views
[x] pg_wlm_statistics
[x] pgxc_prepared_xacts
[x] pgxc_thread_wait_status
[x] plan_table
[x] sys_dummy

strongduanmu commented 1 year ago

That's great, let's make system database queries even better.

FlyingZC commented 1 year ago

The mock system table metadata example is as follows: https://github.com/apache/shardingsphere/pull/22057 https://github.com/apache/shardingsphere/pull/21512

dongzl commented 1 year ago

Hi @FlyingZC I want to do the mysql schema for MySQL database.

strongduanmu commented 1 year ago

@dongzl Welcome.

photon8231 commented 1 year ago

try to develop the mysql.plugin , thanks

sudip-unb commented 1 year ago

Hi @FlyingZC I am interested to work on PostgreSQL schema.

FlyingZC commented 1 year ago

@photon8231 @sudip-unb Welcome!

QiangYuan0828 commented 1 year ago

Hi, This is Qiang Yuan, a candidate for Gsoc2023. After learning from PR above, I would like to work on performance_schema of MySQL. But I want to make sure I understand the target correctly:

create a .yaml file for each table under infra/common/src/main/resources/schema/mysql/performance_schema/.
update enmu type in infra/common/src/main/java/org/apache/shardingsphere/infra/metadata/database/schema/builder/SystemSchemaBuilderRule.java.
add test cases under infra/common/src/test/java/org/apache/shardingsphere/infra/metadata/database/schema/builder/.
may need to write *.xml files under /test, but I am a little confused about where the location should be and how they will work for testing.

FlyingZC commented 1 year ago

@QiangYuan0828 Welcome!

You can refer to this pr https://github.com/apache/shardingsphere/pull/24860.

You can add the assertion file of the query result in this file test/e2e/sql/src/test/resources/cases/dql/dql-integration-select-system-schema.xml.

And the assertion file under test/resources/cases/dql/dataset/db/ like this:

You can use this naming convention to name assertion files select_${databaseType}_${schemaName}_${tableName}, like select_mysql_information_schema_innodb_sys_fields.xml.

QiangYuan0828 commented 1 year ago

@FlyingZC Got it, thank you so much!

wang-qingyang commented 1 year ago

Hi, This is wangqingyang, I would like to work on pg_catalog of PostgreSQL. After learning from PR above, I didn't see any related issues about Implement PostgreSQL system database query, so I am not sure if the work should be done like this:

create a .yaml file for each table under infra/common/src/main/resources/schema/postgresql/pg_catalog/. like pg_aggregate.yaml

update enmu type in infra/common/src/main/java/org/apache/shardingsphere/infra/metadata/database/schema/builder/SystemSchemaBuilderRule.java

POSTGRESQL_PG_CATALOG("PostgreSQL", "pg_catalog", new HashSet<>(Arrays.asList("pg_class", "pg_database", "pg_inherits", "pg_tablespace", "pg_trigger", "pg_namespace"))),

add test cases under infra/common/src/test/java/org/apache/shardingsphere/infra/metadata/database/schema/builder/SystemSchemaBuilderTest.java

   @Test
   void assertBuildForPostgreSQL() {
       Map<String, ShardingSphereSchema> actual = SystemSchemaBuilder.build("sharding_db", new PostgreSQLDatabaseType());
       assertThat(actual.size(), is(3));
       assertTrue(actual.containsKey("information_schema"));
       assertTrue(actual.containsKey("pg_catalog"));
       assertTrue(actual.containsKey("shardingsphere"));
       assertThat(actual.get("information_schema").getTables().size(), is(3));
       assertThat(actual.get("pg_catalog").getTables().size(), is(6));
       assertThat(actual.get("shardingsphere").getTables().size(), is(2));
   }

infra/common/src/test/java/org/apache/shardingsphere/infra/metadata/database/schema/builder/SystemSchemaBuilderRuleTest.java

@Test
void assertValueOfSchemaPathSuccess() {
   SystemSchemaBuilderRule actualInformationSchema = SystemSchemaBuilderRule.valueOf(new MySQLDatabaseType().getType(), "information_schema");
   assertThat(actualInformationSchema, is(SystemSchemaBuilderRule.MYSQL_INFORMATION_SCHEMA));
   assertThat(actualInformationSchema.getTables().size(), is(61));
   SystemSchemaBuilderRule actualMySQLSchema = SystemSchemaBuilderRule.valueOf(new MySQLDatabaseType().getType(), "mysql");
   assertThat(actualMySQLSchema, is(SystemSchemaBuilderRule.MYSQL_MYSQL));
   assertThat(actualMySQLSchema.getTables().size(), is(31));
   SystemSchemaBuilderRule actualPerformanceSchema = SystemSchemaBuilderRule.valueOf(new MySQLDatabaseType().getType(), "performance_schema");
   assertThat(actualPerformanceSchema, is(SystemSchemaBuilderRule.MYSQL_PERFORMANCE_SCHEMA));
   assertThat(actualPerformanceSchema.getTables().size(), is(87));
}

add the assertion file of the query result in test/e2e/sql/src/test/resources/cases/dql/dataset/db/dql-integration-select-system-schema.xml

<test-case sql="SELECT * FROM pg_catalog.pg_aggregate" db-types="PostgreSQL" scenario-types="db">
    <assertion expected-data-file="select_postgresql_pg_catalog_pg_aggregate.xml" />
</test-case>

add assertion file under test/resources/cases/dql/dataset/db/ like this: select_postgresql_pg_catalog_pg_aggregate.xml

FlyingZC commented 1 year ago

@Wang-qing-yang Welcome! You are right to write code like this.

wang-qingyang commented 1 year ago

@FlyingZC hi, I have already finished the code for _pgaggregate in _pgcatalog for PostgreSQL two days ago , I wrote the code like what I mentioned before, but I couldn't pass maven check locally : mvn clean install -B -T2C -DskipTests -Dmaven.javadoc.skip=true -e. and the erro is : [ERROR] Failed to execute goal org.apache.maven.plugins:maven-checkstyle-plugin:3.2.1:check (validate) on project shardingsphere-infra-common: Failed during checkstyle execution: There is 1 error reported by Checkstyle 9.3 with D:/git/shardingsphere/src/resources/checkstyle.xml ruleset. -> [Help 1] org.apache.maven.lifecycle.LifecycleExecutionException: Failed to execute goal org.apache.maven.plugins:maven-checkstyle-plugin:3.2.1:check (validate) on project shardingsphere-infra-common: Failed during checkstyle execution at org.apache.maven.lifecycle.internal.MojoExecutor.doExecute2 (MojoExecutor.java:347) at org.apache.maven.lifecycle.internal.MojoExecutor.doExecute (MojoExecutor.java:330) at org.apache.maven.lifecycle.internal.MojoExecutor.execute (MojoExecutor.java:213) at org.apache.maven.lifecycle.internal.MojoExecutor.execute (MojoExecutor.java:175) at org.apache.maven.lifecycle.internal.MojoExecutor.access$000 (MojoExecutor.java:76) at org.apache.maven.lifecycle.internal.MojoExecutor$1.run (MojoExecutor.java:163) at org.apache.maven.plugin.DefaultMojosExecutionStrategy.execute (DefaultMojosExecutionStrategy.java:39) at org.apache.maven.lifecycle.internal.MojoExecutor.execute (MojoExecutor.java:160) at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject (LifecycleModuleBuilder.java:105) at org.apache.maven.lifecycle.internal.builder.multithreaded.MultiThreadedBuilder$1.call (MultiThreadedBuilder.java:193) at org.apache.maven.lifecycle.internal.builder.multithreaded.MultiThreadedBuilder$1.call (MultiThreadedBuilder.java:180) at java.util.concurrent.FutureTask.run (FutureTask.java:264) at java.util.concurrent.Executors$RunnableAdapter.call (Executors.java:539) at java.util.concurrent.FutureTask.run (FutureTask.java:264) at java.util.concurrent.ThreadPoolExecutor.runWorker (ThreadPoolExecutor.java:1136) at java.util.concurrent.ThreadPoolExecutor$Worker.run (ThreadPoolExecutor.java:635) at java.lang.Thread.run (Thread.java:833) Caused by: org.apache.maven.plugin.MojoExecutionException: Failed during checkstyle execution at org.apache.maven.plugins.checkstyle.CheckstyleViolationCheckMojo.execute (CheckstyleViolationCheckMojo.java:554) at org.apache.maven.plugin.DefaultBuildPluginManager.executeMojo (DefaultBuildPluginManager.java:126) at org.apache.maven.lifecycle.internal.MojoExecutor.doExecute2 (MojoExecutor.java:342) at org.apache.maven.lifecycle.internal.MojoExecutor.doExecute (MojoExecutor.java:330) at org.apache.maven.lifecycle.internal.MojoExecutor.execute (MojoExecutor.java:213) at org.apache.maven.lifecycle.internal.MojoExecutor.execute (MojoExecutor.java:175) at org.apache.maven.lifecycle.internal.MojoExecutor.access$000 (MojoExecutor.java:76) at org.apache.maven.lifecycle.internal.MojoExecutor$1.run (MojoExecutor.java:163) at org.apache.maven.plugin.DefaultMojosExecutionStrategy.execute (DefaultMojosExecutionStrategy.java:39) at org.apache.maven.lifecycle.internal.MojoExecutor.execute (MojoExecutor.java:160) at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject (LifecycleModuleBuilder.java:105) at org.apache.maven.lifecycle.internal.builder.multithreaded.MultiThreadedBuilder$1.call (MultiThreadedBuilder.java:193) at org.apache.maven.lifecycle.internal.builder.multithreaded.MultiThreadedBuilder$1.call (MultiThreadedBuilder.java:180) at java.util.concurrent.FutureTask.run (FutureTask.java:264) at java.util.concurrent.Executors$RunnableAdapter.call (Executors.java:539) at java.util.concurrent.FutureTask.run (FutureTask.java:264) at java.util.concurrent.ThreadPoolExecutor.runWorker (ThreadPoolExecutor.java:1136) at java.util.concurrent.ThreadPoolExecutor$Worker.run (ThreadPoolExecutor.java:635) at java.lang.Thread.run (Thread.java:833) Caused by: org.apache.maven.plugins.checkstyle.exec.CheckstyleExecutorException: There is 1 error reported by Checkstyle 9.3 with D:/git/shardingsphere/src/resources/checkstyle.xml ruleset. at org.apache.maven.plugins.checkstyle.exec.DefaultCheckstyleExecutor.executeCheckstyle (DefaultCheckstyleExecutor.java:243) at org.apache.maven.plugins.checkstyle.CheckstyleViolationCheckMojo.execute (CheckstyleViolationCheckMojo.java:545) at org.apache.maven.plugin.DefaultBuildPluginManager.executeMojo (DefaultBuildPluginManager.java:126) at org.apache.maven.lifecycle.internal.MojoExecutor.doExecute2 (MojoExecutor.java:342) at org.apache.maven.lifecycle.internal.MojoExecutor.doExecute (MojoExecutor.java:330) at org.apache.maven.lifecycle.internal.MojoExecutor.execute (MojoExecutor.java:213) at org.apache.maven.lifecycle.internal.MojoExecutor.execute (MojoExecutor.java:175) at org.apache.maven.lifecycle.internal.MojoExecutor.access$000 (MojoExecutor.java:76) at org.apache.maven.lifecycle.internal.MojoExecutor$1.run (MojoExecutor.java:163) at org.apache.maven.plugin.DefaultMojosExecutionStrategy.execute (DefaultMojosExecutionStrategy.java:39) at org.apache.maven.lifecycle.internal.MojoExecutor.execute (MojoExecutor.java:160) at org.apache.maven.lifecycle.internal.LifecycleModuleBuilder.buildProject (LifecycleModuleBuilder.java:105) at org.apache.maven.lifecycle.internal.builder.multithreaded.MultiThreadedBuilder$1.call (MultiThreadedBuilder.java:193) at org.apache.maven.lifecycle.internal.builder.multithreaded.MultiThreadedBuilder$1.call (MultiThreadedBuilder.java:180) at java.util.concurrent.FutureTask.run (FutureTask.java:264) at java.util.concurrent.Executors$RunnableAdapter.call (Executors.java:539) at java.util.concurrent.FutureTask.run (FutureTask.java:264) at java.util.concurrent.ThreadPoolExecutor.runWorker (ThreadPoolExecutor.java:1136) at java.util.concurrent.ThreadPoolExecutor$Worker.run (ThreadPoolExecutor.java:635) at java.lang.Thread.run (Thread.java:833) and I tried to find why , but they all didn't work, I also cloned the original file, changed nothing but also could passed mvn clean install -B -T2C -DskipTests -Dmaven.javadoc.skip=true -e, and had the same error, that really confused me :(

and in files like _pgclass.yaml , in pg_class the oid column type should be oid from the document PostgreSQL 15 , and in this file : name: pg_class columns: oid: caseSensitive: true dataType: -5 generated: false name: oid primaryKey: false visible: true the dataType is -5 , but form the pg_type table in PostgreSQL 15, I think the dataType of oid should be 26:

I would like to know if it is right to follow the pg_type table to confirm the dataType in .yaml files for PostgreSQL?

FlyingZC commented 1 year ago

@Wang-qing-yang Hi,you can refer to the logic oforg.apache.shardingsphere.infra.metadata.database.schema.loader.datatype.DataTypeLoader#load and org.apache.shardingsphere.infra.metadata.database.schema.loader.metadata.dialect.PostgreSQLSchemaMetaDataLoader#loadColumnMetaDataMap

QiangYuan0828 commented 1 year ago

Hi, I would like to try thesys schema in Mysql, but not sure if I should treat views as well as tabels in implementation. If there are lots of differents, can I get some references?

FlyingZC commented 1 year ago

Hi, I would like to try thesys schema in Mysql, but not sure if I should treat views as well as tabels in implementation. If there are lots of differents, can I get some references?

@QiangYuan0828 Welcome! Views are the same as tables.

FlyingZC commented 1 year ago

Closed for completion.

apache / shardingsphere

[New Feature] Implement MySQL/PostgreSQL/openGauss system database query #24662

Background

Tasks

mysql

information_schema

mysql

performance_schema

sys

tables

views

PostgreSQL

information_schema

tables

views

pg_catalog

tables

views

openGauss

information_schema

Tables

Views

pg_cataglog

Tables

Views