###########################################################################
# Author: Hema
# Date: 2008-04-20
# Purpose: To test the metadata consistency of  object (stored functions)       #   after backup and Restore.
###############################################################################
--source include/have_innodb.inc
--source include/not_embedded.inc
--source include/have_debug.inc
--source /space/hapr29prefix/mysql-test/suite/back-up/include/backup_engine.inc

connect (backup,localhost,root,,);
connect (breakpoints,localhost,root,,);

##############################################################
--echo
--echo starting the test for backup
--echo
##############################################################

--error 0,1
--remove_file $MYSQLTEST_VARDIR/master-data/bup_objectsfs.bak

#Create Database and object stored function for this test.

--disable_warnings
DROP DATABASE IF EXISTS bup_objectsfs;
--enable_warnings

CREATE DATABASE bup_objectsfs;
USE bup_objectsfs;

SET SQL_MODE=TRADITIONAL;

#Create table and load with data.

--echo Creating table


CREATE TABLE players(playerno int not null primary key, name char(10), Bdate date, sex char(2), joined year);

CREATE TABLE teams(teamno int not null primary key, playerno int, division char(10), foreign key(playerno) references players(playerno));

CREATE TABLE matches(Matchno int not null primary key, teamno int , playerno int, won int, lost int, foreign key(teamno) references teams(teamno), foreign key(playerno) references players(playerno));

CREATE TABLE penalties(paymentno int not null primary key,playerno int, paymentdate date, amount float,foreign key(playerno) references players(playerno));

--echo Load Data in tables

INSERT INTO players VALUES(1,'aa1','1948-10-09','M','1960'),(2,'aa2','1950-05-09','M','1965'),(3,'aa3','1953-10-10','F','1970'),(4,'aa4','1970-10-19','M','1985'),(5,'aa5','1972-04-28','F','1994'),(6,'aa6','1945-01-26','M',1960);

INSERT INTO teams VALUES(1,3,'first'),(2,4,'division');

INSERT INTO matches VALUES(1,1,2,3,1),(2,2,1,4,2),(3,1,5,0,1),(4,2,4,3,0),(5,2,5,2,2);
 INSERT INTO matches values(6,1,3,3,1),(7,2,1,4,2),(8,1,5,0,1),(9,2,2,3,0),(10,2,2,2,2) ;

INSERT INTO penalties VALUES(1,5,'1980-10-10',100.23),(2,3,'1967-12-10',123.23), (3,3,'1983-05-25',25.25), (4,4,'1981-11-10',210.23) , (5,5,'1980-12-12',300), (6,1,'1984-07-02',10),(7,1,'1985-10-10',23.90);

#Create stored functions for the table

delimiter ||;
CREATE FUNCTION number_of_players() RETURNS INTEGER BEGIN RETURN (SELECT COUNT(*) FROM players);
end;
||

CREATE FUNCTION number_of_players1() RETURNS INTEGER BEGIN RETURN (SELECT COUNT(*) FROM players WHERE playerno > 2);
end;
||


CREATE FUNCTION fcount() RETURNS INTEGER
BEGIN
  DECLARE v_out INT;
  SELECT count(*) FROM matches INTO v_out;
  RETURN v_out;
END;
||

CREATE FUNCTION dollars(amount float(7,2)) RETURNS float(7,2)
BEGIN
  RETURN amount * (1/0.8);
END;
||

delimiter ;||

#Excercise the objects

SELECT number_of_players();

SELECT number_of_players1();

SELECT fcount();

SELECT paymentno,amount, dollars(amount) FROM penalties;

--echo creating stored functions which is not dependent on table
#BUG#34868 Backup: restore failure if two procedures but this doesn't happen for#  procedures in database, which has atleast one table


CREATE FUNCTION square_area(side tinyint)
RETURNS FLOAT
RETURN side * side;

#excercise function

SELECT square_area(11);

#Show data and create statements.
--echo showing objects and create statements.
--query_vertical SHOW CREATE FUNCTION number_of_players;
--replace_result $ENGINE ENGINE
--query_vertical SHOW CREATE TABLE players;
--replace_result $ENGINE ENGINE
--query_vertical SHOW CREATE TABLE penalties
--replace_column  4 # 5 # 6 #

--query_vertical SHOW FUNCTION STATUS;

#Backup and restore data.
--echo backup data

BACKUP DATABASE bup_objectsfs TO 'bup_objectsfs.bak';
--echo dropping  database.
DROP DATABASE bup_objectsfs;

--echo Restoring Database

# BUG#35118 Backup:Restore fails if previous create Procedure/Function/Trigger  # command fails


RESTORE FROM 'bup_objectsfs.bak';
SELECT @@SQL_MODE;

#show data and create statements
--echo showing objects and create statements
--query_vertical SHOW CREATE DATABASE bup_objectsfs;
--query_vertical SHOW CREATE FUNCTION number_of_players;
--replace_result $ENGINE ENGINE
--query_vertical SHOW CREATE TABLE penalties;
--replace_result $ENGINE ENGINE
--query_vertical SHOW CREATE TABLE matches;
--replace_column  4 # 5 # 6 #

--query_vertical SHOW FUNCTION STATUS;

#Again calling function to check if Sf are backed up and restored properly or not.

INSERT INTO players VALUES(57,'brown','1971-08-17','M','1985'),(95,'Miller','1963-05-14','M','1972');

INSERT INTO matches VALUES(11,2,6,0,3);

SELECT square_area(12);

SELECT number_of_players();

SELECT number_of_players1();

SELECT fcount();

SELECT paymentno,amount, dollars(amount) FROM penalties WHERE paymentno<=3;


#dropping Database again.
DROP DATABASE bup_objectsfs;
--echo change SQL_MODE and then perform RESTORE operation

SET SQL_MODE=ANSI;

RESTORE FROM 'bup_objectsfs.bak';

SELECT @@SQL_MODE;

#show data and create statements
--echo showing objects and create statements
--query_vertical SHOW CREATE DATABASE bup_objectsfs;
--query_vertical SHOW CREATE FUNCTION number_of_players;
--replace_column  4 # 5 # 6 #

--query_vertical SHOW FUNCTION STATUS;

#Again calling function to check if Sf are backed up and restored properly or not.

SELECT square_area(13);

SELECT number_of_players();

SELECT fcount();
DROP DATABASE bup_objectsfs;

--echo change SQL_MODE and then perform RESTORE

SET SQL_MODE=MAXDB;

RESTORE FROM 'bup_objectsfs.bak';

SELECT @@SQL_MODE;

#show data and create statements
--echo showing objects and create statements
--query_vertical SHOW CREATE DATABASE bup_objectsfs;
--query_vertical SHOW CREATE FUNCTION number_of_players;
--replace_column  4 # 5 # 6 #

--query_vertical SHOW FUNCTION STATUS;

#Again calling function to check if Sf are backed up and restored properly or not.
                                                                                    
SELECT square_area(14);

SELECT number_of_players();

SELECT fcount();

SELECT paymentno,amount, dollars(amount) FROM penalties WHERE paymentno >=3;

DROP DATABASE bup_objectsfs;
--echo change SQL_MODE and then perform RESTORE

SET SQL_MODE=' ';

RESTORE FROM 'bup_objectsfs.bak';

SELECT @@SQL_MODE;

#show data and create statements
--echo showing objects and create statements
--query_vertical SHOW CREATE DATABASE bup_objectsfs;
--query_vertical SHOW CREATE FUNCTION number_of_players;
--replace_result $ENGINE ENGINE
--query_vertical SHOW CREATE TABLE players;
--replace_result $ENGINE ENGINE
--query_vertical SHOW CREATE TABLE matches;
--replace_column  4 # 5 # 6 #

--query_vertical SHOW FUNCTION STATUS;

#Again calling function to check if Sf are backed up and restored properly or not.

SELECT square_area(20);

SELECT number_of_players();

SELECT fcount();
SELECT paymentno,amount, dollars(amount) FROM penalties;

# Test cleanup section

--echo
--echo ***  DROP bup_objectsfs DATABASE ****
--echo

DROP DATABASE bup_objectsfs;

--remove_file $MYSQLTEST_VARDIR/master-data/bup_objectsfs.bak