Hi, Nikita,
On Aug 31, Nikita Malyavin wrote:
> revision-id: 82a49812507 (mariadb-11.0.1-228-g82a49812507)
> parent(s): 23b15d24eb3
> author: Nikita Malyavin
> committer: Nikita Malyavin
> timestamp: 2023-08-30 22:49:36 +0400
> message:
>
> MDEV-22597 Add views for periods in information_schema
>
> Two new information_schema views are added:
> * PERIOD table -- columns TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,
> PERIOD_NAME, START_COLUMN_NAME, END_COLUMN_NAME.
> * KEY_PERIOD_USAGE -- works similar to KEY_COLUMN_USAGE, but for periods.
> Columns CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME,
> TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, PERIOD_NAME
>
> Two new columns are added to the COLUMNS view:
> IS_SYSTEM_TIME_PERIOD_START, IS_SYSTEM_TIME_PERIOD_END - contain YES/NO.
>
> diff --git a/mysql-test/main/information_schema-big.result b/mysql-test/main/information_schema-big.result
> index 94c8d274fad..ad0451293c9 100644
> --- a/mysql-test/main/information_schema-big.result
> +++ b/mysql-test/main/information_schema-big.result
good, you didn't forget to update big and embedded tests
> diff --git a/mysql-test/suite/period/r/i_s_notembedded.result b/mysql-test/suite/period/r/i_s_notembedded.result
> new file mode 100644
> index 00000000000..879376cc8b4
> --- /dev/null
> +++ b/mysql-test/suite/period/r/i_s_notembedded.result
> @@ -0,0 +1,52 @@
> +select * from information_schema.periods;
> +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PERIOD START_COLUMN_NAME END_COLUMN_NAME
> +create or replace table t1 (id int primary key, s timestamp(6), e timestamp(6),
> +period for mytime(s,e));
> +create or replace table t2 (id int primary key, s timestamp(6), e timestamp(6),
> +period for mytime(s,e)) with system versioning;
> +show columns from t1;
> +Field Type Null Key Default Extra
> +id int(11) NO PRI NULL
> +s timestamp(6) NO NULL
> +e timestamp(6) NO NULL
> +select * from information_schema.periods where table_schema = 'test';
> +TABLE_CATALOG TABLE_SCHEMA TABLE_NAME PERIOD START_COLUMN_NAME END_COLUMN_NAME
> +def test t2 SYSTEM_TIME row_start row_end
I don't think so, they're INVISIBLE_SYSTEM. for all practical purposes they
don't exist as columns in the table. Shouldn't be shown either.
But don't remove this test, let's keep it to show that row_start/row_end are
*not* shown here.
And add table t3 with full standard definition of period for system_time.
it should be shown all right.
> +def test t2 mytime s e
> +def test t1 mytime s e
> +create user periods_hidden@localhost;
...
> diff --git a/sql/sql_show.cc b/sql/sql_show.cc
> index 1dc60a01f38..876d6c6f244 100644
> --- a/sql/sql_show.cc
> +++ b/sql/sql_show.cc
> @@ -6165,11 +6166,83 @@ static void store_variable_type(THD *thd, const sp_variable *spvar,
> }
> }
>
> +static int store_schema_period_record(THD *thd, TABLE_LIST *tl,
> + TABLE *schema_table,
> + const LEX_CSTRING *db_name,
> + const LEX_CSTRING *table_name,
> + const TABLE_SHARE::period_info_t &period)
> +{
> + TABLE_SHARE *s= tl->table->s;
> + const CHARSET_INFO *cs= system_charset_info;
> +
> + int err= schema_table->field[0]->store(STRING_WITH_LEN("def"), cs);
> + if(!err) err= schema_table->field[1]->store(db_name, cs);
> + if(!err) err= schema_table->field[2]->store(table_name, cs);
> + if(!err) err= schema_table->field[3]->store(period.name, cs);
well, ok. but as you've seen elsewhere these go without checks, as these
calls generally cannot fail, they don't even allocate any memory.
> +
> + if (err) return err;
> +
> + int period_field= 4;
> + for (auto *field: {period.start_field(s), period.end_field(s)})
> + {
> +#ifndef NO_EMBEDDED_ACCESS_CHECKS
> + bool col_granted= get_column_grant(thd, &tl->grant,
> + db_name->str, table_name->str,
> + field->field_name.str) & COL_ACLS;
> + if (col_granted)
> +#endif
> + {
> + schema_table->field[period_field]->set_notnull();
> + err= schema_table->field[period_field]->store(field->field_name, cs);
> + }
> +
> + if (err)
> + return err;
> + period_field++;
> + }
> +
> + return schema_table_store_record(thd, schema_table);
> +}
> +
> +
> +static int
> +get_schema_period_records(THD *thd, TABLE_LIST *tl,
> + TABLE *schema_table, //!< @ref
> + //!< Show::periods_fields_info
> + bool res,
> + const LEX_CSTRING *db_name,
> + const LEX_CSTRING *table_name)
> +{
> + TABLE *table= tl->table;
> +
> + if (!table || (!table->s->period.name && !table->versioned()))
> + return 0;
> +
> +#ifndef NO_EMBEDDED_ACCESS_CHECKS
> + check_access(thd, SELECT_ACL, db_name->str,
> + &tl->grant.privilege, 0, 0, MY_TEST(tl->schema_table));
> + if (is_temporary_table(tl))
> + {
> + tl->grant.privilege|= TMP_TABLE_ACLS;
> + }
> +#endif
> + int err= 0;
> + if (table->versioned())
> + err= store_schema_period_record(thd, tl, schema_table, db_name, table_name,
> + table->s->vers);
> + if (!err && table->s->period.name)
> + err= store_schema_period_record(thd, tl, schema_table, db_name, table_name,
> + table->s->period);
> +
> + return err;
> +}
>
> -static int get_schema_column_record(THD *thd, TABLE_LIST *tables,
> - TABLE *table, bool res,
> - const LEX_CSTRING *db_name,
> - const LEX_CSTRING *table_name)
> +static
> +int get_schema_column_record(THD *thd, TABLE_LIST *tables,
> + TABLE *table, //!< @ref Show::columns_fields_info
> + bool res,
> + const LEX_CSTRING *db_name,
> + const LEX_CSTRING *table_name)
> {
> LEX *lex= thd->lex;
> const char *wild= lex->wild ? lex->wild->ptr() : NullS;
> @@ -7513,30 +7592,41 @@ static int get_schema_triggers_record(THD *thd, TABLE_LIST *tables,
> DBUG_RETURN(0);
> }
>
> +static int
> +store_key_column_usage(TABLE *table, const LEX_CSTRING &db_name,
> + const LEX_CSTRING &table_name,
> + const LEX_CSTRING &key_name,
> + const LEX_CSTRING &col_name)
> +{
> + CHARSET_INFO *cs= system_charset_info;
> + static const LEX_CSTRING def{STRING_WITH_LEN("def")};
> + const LEX_CSTRING *values[] { &def, &db_name, &key_name,
> + &def, &db_name, &table_name, &col_name };
> + int err = 0;
> + for (uint i = 0; i < array_elements(values) && !err; i++)
> + table->field[i]->store(values[i], cs);
you forgot `err=` here :)
but as I wrote above, it's not needed here anyway
> + return 0;
> +}
>
> static void
> store_key_column_usage(TABLE *table, const LEX_CSTRING *db_name,
> const LEX_CSTRING *table_name, const char *key_name,
> - size_t key_len, const char *con_type, size_t con_len,
> + size_t key_len, const char *col_name, size_t col_len,
> longlong idx)
> {
> - CHARSET_INFO *cs= system_charset_info;
> - table->field[0]->store(STRING_WITH_LEN("def"), cs);
> - table->field[1]->store(db_name->str, db_name->length, cs);
> - table->field[2]->store(key_name, key_len, cs);
> - table->field[3]->store(STRING_WITH_LEN("def"), cs);
> - table->field[4]->store(db_name->str, db_name->length, cs);
> - table->field[5]->store(table_name->str, table_name->length, cs);
> - table->field[6]->store(con_type, con_len, cs);
> + store_key_column_usage(table, *db_name, *table_name, {key_name, key_len},
> + {col_name, col_len});
pretty, but better, please, put it back. You can use the old
store_key_column_usage from your get_schema_key_period_usage_record()
just the same. There's no need to have two store_key_column_usage()
functions.
> table->field[7]->store((longlong) idx, TRUE);
> }
>
>
> -static int get_schema_key_column_usage_record(THD *thd,
> - TABLE_LIST *tables,
> - TABLE *table, bool res,
> - const LEX_CSTRING *db_name,
> - const LEX_CSTRING *table_name)
> +static int
> +get_schema_key_column_usage_record(THD *thd, TABLE_LIST *tables,
> + TABLE *table,
> + //!< @ref Show::key_column_usage_fields_info
> + bool res,
> + const LEX_CSTRING *db_name,
> + const LEX_CSTRING *table_name)
> {
> DBUG_ENTER("get_schema_key_column_usage_record");
> if (res)
> @@ -9703,6 +9824,18 @@ ST_FIELD_INFO key_column_usage_fields_info[]=
> CEnd()
> };
>
> +ST_FIELD_INFO key_period_usage_fields_info[]=
> +{
> + Column("CONSTRAINT_CATALOG", Catalog(), NOT_NULL, OPEN_FULL_TABLE),
> + Column("CONSTRAINT_SCHEMA", Name(), NOT_NULL, OPEN_FULL_TABLE),
> + Column("CONSTRAINT_NAME", Name(), NOT_NULL, OPEN_FULL_TABLE),
> + Column("TABLE_CATALOG", Catalog(), NOT_NULL, OPEN_FULL_TABLE),
> + Column("TABLE_SCHEMA", Name(), NOT_NULL, OPEN_FULL_TABLE),
> + Column("TABLE_NAME", Name(), NOT_NULL, OPEN_FULL_TABLE),
> + Column("PERIOD_NAME", Name(), NOT_NULL, OPEN_FULL_TABLE),
Cannot they all be OPEN_FRM_ONLY?
> + CEnd()
> +};
> +
>
> ST_FIELD_INFO table_names_fields_info[]=
> {
> @@ -10208,6 +10358,8 @@ ST_SCHEMA_TABLE schema_tables[]=
> {0, 0, 0, 0, 0, 0, 0, 0, 0, 0}
> };
>
> +static_assert(array_elements(schema_tables) == SCH_ENUM_SIZE + 1,
> + "Update enum_schema_tables as well.");
+1
>
> int initialize_schema_table(st_plugin_int *plugin)
> {
Regards,
Sergei
VP of MariaDB Server Engineering
and security(a)mariadb.org