WL#7896: Use DD API to work with triggers

Affects: Server-8.0   —   Status: Complete   —   Priority: Medium

WL#6383 provides new DD API to work with triggers.
The aim of this WL task is to switch to that API
from the current "API" to create/drop/load triggers.

So, scope of this WL is
  * to use the new system table mysql.triggers to store metadata of table's 
  * to use API introduced by WL#6383 to store/load table's trigger definitions;
  * to remove support for .TRG/.TRN files from server code base.

Scope of this WL doesn't include:
  * upgrade of triggers metadata from .TRN/.TRG files to a new Data Dictionary.
  * downgrade procedures to move trigger definition from the table mysql.triggers
to .TRN/.TRG files.

This WL also doesn't solve a task for hiding direct access to a table
mysql.triggers from users. As a consequence, any possible issues
caused by changes of trigger's metadata manually and following execution of
statements SHOW TRIGGERS/SHOW CREATE TRIGGER aren't covered by this worklog.
Prerequisite Req. 1: The table mysql.triggers must exist in the new
Data Dictionary in order to allow storing of trigger's metadata.

Func. Req. 1: The statement CREATE TRIGGER must store a trigger's metadata
into the table mysql.triggers;

Func. Req. 2: The statement DROP TRIGGER must remove a metadata about a trigger
being dropped from the table mysql.triggers;

Func. Req. 3: The table INFORMATION_SCHEMA.TRIGGERS must be filled by reading
trigger's metadata from the Data Dictionary;

Func. Req. 4: The statements SHOW TRIGGERS and SHOW CREATE TRIGGER must output
a trigger's metadata based on a content of the table mysql.triggers;

Func. Req. 5: For case when trigger's metadta has been modified manually
using SQL statements INSERT/UPDATE/DELETE on the table mysql.triggers
the behaviour of the statements SHOW TRIGGERS and SHOW CREATE TRIGGER
is out of scope.

Func. Req. 6: Behaviour of the server mustn't depend on a content of .TRG/.TRN
files, their presence or absence.

Func. Req. 7: MTR tests that depends on .TRG/.TRN files must be rewritten or
This worklog does the following things:

* Provides auxiliary APIs in dd namespace to create, drop, load triggers
  from underlying data dictionary table(s) using dictionary client interface

* Makes use of the auxiliary APIs in the existing triggers subsystem of the
server to implement the triggers functionality. This worklog doesn’t make
any major changes to the existing design of the triggers subsystem in the
server code.

The user observable changes are:

* Non-SUID triggers stopped to be supported since definer user/definer host
have NOT NULL constraint in the table mysql.trigger;

* A text of message for the error code ER_TRG_NO_DEFINER has been changed.
Former it was looked as
"No definer attribute for trigger '%-.192s'.'%-.192s'. The trigger will be
activated under the authorization of the invoker, which may have insufficient
privileges. Please recreate the trigger."
Now it's changed to
"No definer attribute for trigger '%-.192s'.'%-.192s'. It's disallowed to create
trigger without definer."

* Message for the code ER_TRG_NO_DEFINER now is emitted as an error instead of
warning as it was before. This change was inspired by the fact that non-SUID
triggers no more supported;

* Trigger’s metadata now is stored in the new Data Dictionary table

* Support for .TRG/.TRN files is removed from the server codebase. These file
suffixes no more recognized as reserved and files with such suffixes
no more handled in any special way;

* MDL lock for trigger name is acquired while handling CREATE TRIGGER/DROP 
To support triggers this worklog uses the new Data Dictionary API and
the Data Dictionary table mysql.triggers provided by the WL#6383.
The class dd::cache::Dictionary_client is used for work with the Data
Dictionary. Its methods acquire() and update() is used for getting a
dd::Table object that associated with a trigger being created/looked
for/dropped. To find a table name by trigger name the method
Dictionary_client::get_table_name_by_trigger_name() is used for.

The following methods of the class dd::Table are used for
finding/storing/dropping metadata information for a trigger:
    bool has_trigger() const;
    Trigger_const_iterator *triggers() const;
    Trigger *get_trigger(const char *name);
    Trigger *add_trigger(Trigger::enum_action_timing at,
                         Trigger::enum_event_type et);
    Trigger *add_trigger_following(const Trigger *trigger,
                                   Trigger::enum_action_timing  at,
                                   Trigger::enum_event_type et);
    Trigger *add_trigger_preceding(const Trigger *trigger,
                                   Trigger::enum_action_timing at,
                               Trigger::enum_event_type et);
    void drop_trigger(Trigger *trigger);

This worklog does the following things in the existing server code base:
* Code for handling CREATE/DROP TRIGGER is moved to classes derived from
Sql_cmd. Common code used while processing the statements
CREATE TRIGGER/DROP TRIGGER moved to the class Sql_cmd_ddl_trigger_common
that inherited from the class Sql_cmd. Two new classes Sql_cmd_create_trigger,
Sql_cmd_drop_trigger introduced to handle the statements
CREATE TRIGGER/DROP TRIGGER. These classes are inherited from the class

* Text of the whole CREATE TRIGGER statement that used to create trigger no more
stored in the Data Dictionary as it was before. Instead, only a trigger’s body
stored in a separate column of the table mysql.trigger. The full content of the
statement CREATE TRIGGER shown in resulting output of the statement
SHOW CREATE TRIGGER and in output of the command mysqldump is reconstructed
from the metadata stored in the system table mysql.triggers. Such modification
allows us doesn't worry about modification in a statement for trigger creation
when a trigger's table name changed;

* Since the whole text of the statement CREATE TRIGGER no more stored in the
data dictionary and reconstructed on the fly we don’t need storing in a LEX the
pointers to begin/end of ON clause and begin/end of FOLLOWS/PRECEDES clauses
that used to reconstruct the statement CREATE TRIGGER in case a table name
changed or to reconstruct the statement CREATE TRIGGER before write to binlog.
It means that size of LEX is decreased by the size of 4 pointers.

* The columns Trigger name, definer and table name that are output from
the statement SHOW CREATE TRIGGER and the command mysqldump are always
included in backquotes;

* Before this WL a statement CREATE TRIGGER that is used to create a trigger
was stored in a file with suffix .TRG. A statement was stored literally as
it was entered by a user. Since this WL doesn’t store the whole statement and
reconstructs it on the fly some differences exists in result output of
SHOW CREATE TRIGGERS/mysqldump run against the server without and with this WL.
For example, before this WL a table name and a trigger name is output as
it was entered by a user whereas this WL always outputs a table name and
a trigger name in backquotes.

* The order of storing triggers in the data dictionary has been changed.
Before this WL triggers were stored in the order of creation. After this WL
triggers are stored in the Data Dictionary based on internal rules
that doesn’t coincide with order that triggers were created;

* Tests that depend on files .TRG/.TRN were disabled or removed;

* The new source files sql/dd/dd_trigger.h, sql/dd/dd_trigger.cc were added.
These files contains adapter functions declared in the namespace dd
to interact with new dd API. List of new functions used for working with
dd trigger API are listed below:

bool create_trigger(THD *thd, MEM_ROOT *mem_root,
                    ::Trigger *new_trigger,
                    enum_trigger_order_type ordering_clause,
                    const MYSQL_LEX_STRING &referenced_trigger_name);

bool load_triggers(THD *thd,
                   MEM_ROOT *mem_root,
                   const char *schema_name,
                   const char *table_name,
                   List<::Trigger> *triggers);

bool table_has_triggers(THD *thd, const char *schema_name,
                        const char *table_name);

bool check_trigger_exists(THD *thd,
                          const char *schema_name,
                          const char *trigger_name,
                          bool *trigger_exists);

bool drop_trigger(THD *thd, const char *schema_name,
                  const char *table_name,
                  const char *trigger_name,
                  bool *trigger_found);

bool drop_all_triggers(THD *thd,
                       const char *schema_name,
                       const char *table_name,
                       List<::Trigger>* triggers);

bool get_table_name_for_trigger(THD *thd,
                                const char *schema_name,
                                const char *trigger_name,
                                std::string *table_name,
                                bool *trigger_found);

* The source files sql/trigger_loader.h, sql/trigger_loader.cc have been removed
from the code base;

* To support metadata locking for trigger name the following modifications were 
  - additional function acquire_mdl_for_trigger() was added to acquire MDL lock
by schema name and trigger name
  - trigger MDL lock is requested as an exclusive metadata lock from the
namespace MDL_key::TRIGGER with transaction duration
  - the function acquire_mdl_for_trigger() is called from the methods
Sql_cmd_create_trigger::execute(), Sql_cmd_drop_trigger::execute() as part of
handing the statements CREATE TRIGGER, DROP TRIGGER
  - the new standalone function lock_trigger_names() was introduced to acquire
MDL lock for every trigger associated with a table. This function is called
during handling the statement DROP TABLE/DROP DATABASE. The function iterates
over a list of triggers associated with a table (in case of DROP TABLE) or
several tables (in case of DROP DATABASE) and calls the function
acquire_mdl_for_trigger() to get a lock;

* Test cases for trigger MDL were put into the new file trigger_mdl.test.

* Support for trigger mdl fixes the bug BUG#77095.