# suite/funcs_1/t/is_events.test
#
# Check the layout of information_schema.events and some functionality of it.
#
#    This test is not intended for checking storage engine properties
#    Therefore please do not alter $engine_type and $other_engine_type.
#
# Author:
# 2008-02-29 mleich WL#4203 Reorganize and fix the data dictionary tests of
#                           testsuite funcs_1
#

if (`SELECT VERSION() LIKE '%embedded%'`)
{
   --skip Bug#37456 funcs_1: Several tests crash when used with embedded server
}

let $engine_type       = MEMORY;
let $other_engine_type = MyISAM;

let $is_table = EVENTS;

# The table INFORMATION_SCHEMA.EVENTS must exist
eval SHOW TABLES FROM information_schema LIKE '$is_table';

--echo #######################################################################
--echo # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
--echo #######################################################################
# Ensure that every INFORMATION_SCHEMA table can be queried with a SELECT
# statement, just as if it were an ordinary user-defined table.
#
--source suite/funcs_1/datadict/is_table_query.inc


--echo #########################################################################
--echo # Testcase 3.2.12.1: INFORMATION_SCHEMA.EVENTS layout
--echo #########################################################################
# Ensure that the INFORMATION_SCHEMA.EVENTS table has the following columns,
# in the following order:
#
# EVENT_CATALOG       always NULL
# EVENT_SCHEMA        The name of the schema (database) to which this
#                     event belongs.
# EVENT_NAME          The name of the event.
# DEFINER             The user who created the event.
#                     'user_name'@'host_name'  format!
# TIME_ZONE           The time zone in effect when schedule for the event was
#                     last modified
# EVENT_BODY          The language used for the statements in the event's
#                     DO clause. (always SQL)
# EVENT_DEFINITION    The text of the SQL statement making up the event's
#                     DO clause.
# EVENT_TYPE          One of the two values ONE TIME or RECURRING.
# EXECUTE_AT          one-time event: DATETIME value specified in the AT clause
#                                     of the CREATE EVENT statement used to
#                                     create the event
#                     or of the last ALTER EVENT statement that modified
#                     the event.
# INTERVAL_VALUE      recurring events; numeric portion of the event's
#                     EVERY clause.
# INTERVAL_FIELD      recurring events: units portion of the EVERY clause
#                     governing the timing of the event, prefixed with
#                     'INTERVAL_'.  Example: 'INTERVAL_DAY'
# SQL_MODE            The SQL mode in effect at the time the event was created
#                     or altered.
# STARTS              For recurring event whose definition includes a STARTS
#                     clause, this column contains the corresponding
#                     DATETIME value. If there is no STARTS clause affecting
#                     the timing of the event, this column is empty.
# ENDS                For a recurring event whose definition includes a ENDS
#                     clause, this column contains the corresponding DATETIME
#                     value. If there is no ENDS clause affecting the timing
#                     of the event, this column contains NULL.
# STATUS              ENABLED, DISABLED or SLAVESIDE_DISABLED
# ON_COMPLETION       PRESERVE or NOT PRESERVE.
# CREATED             Date and time of event creation
# LAST_ALTERED        Date and time of the last event modification
#                     never modified -> value = CREATED
# LAST_EXECUTED       Date and time of start of last event execution
#                     never executed -> value IS NULL.
# EVENT_COMMENT       The text of a comment. Default: empty string.
# ORIGINATOR          The server ID of the MySQL server on which the event was
#                     created; used in replication. Default: 0
# CHARACTER_SET_CLIENT Session value of the character_set_client system variable
#                      when the event was created.
# COLLATION_CONNECTION Session value of the collation_connection system
#                      variable when the event was created.
# DATABASE_COLLATION  Collation of the database with which the event
#                     is associated.
#
eval DESCRIBE          information_schema.$is_table;
eval SHOW CREATE TABLE information_schema.$is_table;
eval SHOW COLUMNS FROM information_schema.$is_table;

# Note: Retrieval of information within information_schema.columns about
#       information_schema.events is in is_columns_is.test.

# Check event_catalog event_body, event_type, event_type, status, on_completion
SELECT event_catalog, event_name, event_body, event_type, event_type,
       status, on_completion
FROM information_schema.events
WHERE event_catalog IS NOT NULL or
      event_body NOT IN ('SQL') or
      event_type NOT IN ('ONE TIME','RECURRING') or
      status NOT IN ('ENABLED','DISABLED','SLAVESIDE_DISABLED') or
      on_completion NOT IN ('PRESERVE','NOT PRESERVE');

# FIXME:  Check the regression tests and implement tests checking the
#         functionality if missing.


--echo ########################################################################
--echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
--echo #           DDL on INFORMATION_SCHEMA tables are not supported
--echo ########################################################################
# 3.2.1.3:  Ensure that no user may execute an INSERT statement on any
#           INFORMATION_SCHEMA table.
# 3.2.1.4:  Ensure that no user may execute an UPDATE statement on any
#           INFORMATION_SCHEMA table.
# 3.2.1.5:  Ensure that no user may execute a DELETE statement on any
#           INFORMATION_SCHEMA table.
# 3.2.1.8:  Ensure that no user may create an index on an
#           INFORMATION_SCHEMA table.
# 3.2.1.9:  Ensure that no user may alter the definition of an
#           INFORMATION_SCHEMA table.
# 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table.
# 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any
#           other database.
# 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data
#           in an INFORMATION_SCHEMA table.
#
--disable_warnings
DROP DATABASE IF EXISTS db_datadict;
--enable_warnings
CREATE DATABASE db_datadict;
--replace_result $engine_type <engine_type>
eval
CREATE TABLE db_datadict.t1 (f1 BIGINT)
ENGINE = $engine_type;

--error ER_DBACCESS_DENIED_ERROR
INSERT INTO information_schema.events
SELECT * FROM information_schema.events;

--error ER_DBACCESS_DENIED_ERROR
UPDATE information_schema.events SET event_name = '1234567'
WHERE table_name = 't1';

--error ER_DBACCESS_DENIED_ERROR
DELETE FROM information_schema.events WHERE event_catalog IS NULL;
--error ER_DBACCESS_DENIED_ERROR
TRUNCATE information_schema.events;

--error ER_DBACCESS_DENIED_ERROR
CREATE INDEX my_idx_on_events ON information_schema.events(event_name);

--error ER_DBACCESS_DENIED_ERROR
ALTER TABLE information_schema.events DROP PRIMARY KEY;
--error ER_DBACCESS_DENIED_ERROR
ALTER TABLE information_schema.events ADD f1 INT;

--error ER_DBACCESS_DENIED_ERROR
DROP TABLE information_schema.events;

--error ER_DBACCESS_DENIED_ERROR
ALTER TABLE information_schema.events RENAME db_datadict.events;
--error ER_DBACCESS_DENIED_ERROR
ALTER TABLE information_schema.events RENAME information_schema.xevents;

# Cleanup
DROP DATABASE db_datadict;