-- source include/have_ndb.inc
-- source include/ndb_default_cluster.inc

# Directory containing the saved backup files
let $backup_data_dir=$MYSQL_TEST_DIR/suite/ndb/backups;

#
# The table structure and data list below
#
##############################################
#####  for basic test with all data type #####
##############################################
# CREATE TABLE  t_num (
#   t_pk                INT PRIMARY KEY,
#   t_test		INT,
#   t_bit               BIT(64),
#   t_tinyint   TINYINT,
#   t_bool      BOOL,
#   t_smallint  SMALLINT,
#   t_mediumint MEDIUMINT,
#   t_int               INT,
#   t_bigint    BIGINT,
#   t_float     FLOAT,
#   t_double    DOUBLE,
#   t_decimal   DECIMAL (37, 16)
# ) ENGINE=NDBCLUSTER;
#
# INSERT INTO t_num VALUE (
#   1,
#   1,
#   b'1010101010101010101010101010101010101010101010101010101010101010',
#   125,
#   1,
#   32765,
#   8388606,
#   2147483647,
#   9223372036854775807,
#   1e+20,
#   1e+150,
#   '331.0000000000'
# );
# INSERT INTO t_num VALUE (
#   2,
#   1,
#   b'1010101010101010101010101010101010101010101010101010101010101010',
#   125,
#   1,
#   32765,
#   8388606,
#   2147483647,
#   9223372036854775807,
#   1e+20,
#   1e+150,
#   '331.0000000000'
# );
# CREATE TABLE  t_datetime (
#   t_pk                INT PRIMARY KEY,
#   t_test		INT,
#   t_date      DATE,
#   t_datetime  DATETIME,
#   t_timestamp TIMESTAMP,
#   t_time      TIME,
#   t_year      YEAR
# ) ENGINE=NDBCLUSTER;
#
# INSERT INTO t_datetime VALUE (
#   1,
#   1,
#   '1998-01-01',
#   '2006-08-10 10:11:12',
#   20021029165106,
#   '19:38:34',
#   2155
# );
# INSERT INTO t_datetime VALUE (
#   2,
#   1,
#   '1998-01-01',
#   '2006-08-10 10:11:12',
#   20021029165106,
#   '19:38:34',
#   2155
# );
#
#  CREATE TABLE  t_string_1 (
#   t_pk                INT PRIMARY KEY,
#   t_test		INT,
#   t_char      CHAR(255),
#   t_varchar   VARCHAR(655),
#   t_binary    BINARY(255),
#   t_varbinary VARBINARY(6553)
# ) ENGINE=NDBCLUSTER;
#
# CREATE TABLE  t_string_2 (
#   t_pk                INT PRIMARY KEY,
#   t_test		INT,
#   t_tinyblob  TINYBLOB,
#   t_tinytext  TINYTEXT,
#   t_blob      BLOB,
#   t_text      TEXT,
#   t_mediumblob        MEDIUMBLOB,
#   t_mediumtext        MEDIUMTEXT,
#   t_longblob  LONGBLOB,
#   t_longtext  LONGTEXT,
#   t_enum      ENUM('001001','001004','001010','001018','001019','001020','001021','001027','001028','001029','001030','001031','001100','002003','002004','002005','002007')DEFAULT '001001' NOT NULL,
#   t_set               SET('a','B')
# ) ENGINE=NDBCLUSTER;
#
# INSERT INTO t_string_1 VALUE (
#   1,
#   1,
#   'abcdefghijklmn',
#   'abcdefghijklmnabcdefghijklmnabcdefghijklmnabcdefghijklmn',
#   0x612020,
#   0x4100
# );
# INSERT INTO t_string_1 VALUE (
#   2,
#   1,
#   'abcdefghijklmn',
#   'abcdefghijklmnabcdefghijklmnabcdefghijklmnabcdefghijklmn',
#   0x612020,
#   0x4100
# );
#
# INSERT INTO t_string_2 VALUE (
#   1,
#   1,
#   'abcdefghijklmnabcdefghijklmn',
#   'abcdefghijklmnabcdefghijklmn',
#   'a123456789b123456789c123456789d123456789e123456789f123456789g123456789',
#   'a123456789b123456789c123456789d123456789e123456789f123456789g123456789',
#   'a123456789b123456789c123456789d123456789e123456789f123456789g123456789',
#   'a123456789b123456789c123456789d123456789e123456789f123456789g123456789',
#   'a123456789b123456789c123456789d123456789e123456789f123456789g123456789',
#   'a123456789b123456789c123456789d123456789e123456789f123456789g123456789',
#   '001001',
#   'a'
# );
#
# INSERT INTO t_string_2 VALUE (
#   2,
#   1,
#   'abcdefghijklmnabcdefghijklmn',
#   'abcdefghijklmnabcdefghijklmn',
#   'a123456789b123456789c123456789d123456789e123456789f123456789g123456789',
#   'a123456789b123456789c123456789d123456789e123456789f123456789g123456789',
#   'a123456789b123456789c123456789d123456789e123456789f123456789g123456789',
#   'a123456789b123456789c123456789d123456789e123456789f123456789g123456789',
#   'a123456789b123456789c123456789d123456789e123456789f123456789g123456789',
#   'a123456789b123456789c123456789d123456789e123456789f123456789g123456789',
#   '001001',
#   'a'
# );
#
# CREATE TABLE t_gis (
#   t_pk                        INT PRIMARY KEY,
#   t_test		INT,
#   t_point             POINT,
#   t_linestring                LINESTRING,
#   t_polygon           POLYGON,
#   t_multipoint                MULTIPOINT,
#   t_multilinestring   MULTILINESTRING,
#   t_multipolygon      MULTIPOLYGON,
#   t_geometrycollection        GEOMETRYCOLLECTION,
#   t_geometry          GEOMETRY
# ) ENGINE=NDBCLUSTER;
#
# INSERT INTO t_gis VALUE (
#   1,
#   1,
#   PointFromText('POINT(10 10)'),
#   LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)'),
#   PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))'),
#   MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)'),
#   MLineFromText('MULTILINESTRING((10 48,10 21,10 0))'),
#   MPolyFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))'),
#   GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))'),
#   MPolyFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))')
# );
#
# INSERT INTO t_gis VALUE (
#   3,
#   1,
#   PointFromText('POINT(10 10)'),
#   LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)'),
#   PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))'),
#   MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)'),
#   MLineFromText('MULTILINESTRING((10 48,10 21,10 0))'),
#   MPolyFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))'),
#   GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))'),
#   MPolyFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))')
# );
#
# INSERT INTO t_gis VALUE (
#   2,
#   1,
#   PointFromWKB(AsWKB(PointFromText('POINT(10 20)'))),
#   LineStringFromWKB(LineString(Point(10, 10), Point(40, 10))),
#   PolyFromWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0)))),
#   MPointFromWKB(MultiPoint(Point(3, 6), Point(4, 10))),
#   MLineFromWKB(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7)))),
#   MPolyFromWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3))))),
#   GeometryFromWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9)))),
#   GeometryFromWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9))))
# );
#
# INSERT INTO t_gis VALUE (
#   4,
#   1,
#   PointFromWKB(AsWKB(PointFromText('POINT(10 20)'))),
#   LineStringFromWKB(LineString(Point(10, 10), Point(40, 10))),
#   PolyFromWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0)))),
#   MPointFromWKB(MultiPoint(Point(3, 6), Point(4, 10))),
#   MLineFromWKB(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7)))),
#   MPolyFromWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3))))),
#   GeometryFromWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9)))),
#   GeometryFromWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9))))
# );
#
#
#######################################
######  for multi entries #############
#######################################
# CREATE TABLE  t_string_3 (
#   t_pk INT NOT NULL PRIMARY KEY,
#   t_longblob  LONGBLOB
# ) ENGINE=NDB;
#
#delimiter |;
#
# CREATE PROCEDURE test_proc1()
# BEGIN
#   DECLARE tmp INT DEFAULT 5000;
#   WHILE tmp > 1 DO
#     INSERT INTO t_string_3 VALUES (
#       tmp,
#       'a123456789b123456789c123456789d123456789e123456789f123456789g123456789');
#     SET tmp = tmp -1;
#   END WHILE; 
# END;
#
# delimiter ;|
#
# INSERT INTO t_string_3 VALUE (
#  1,
#  'a123456789b123456789c123456789d123456789e123456789f123456789g123456789');
#
# call test_proc1();
#
##########################################
#####  for disk data and order index #####
##########################################
# CREATE LOGFILE GROUP lg1
# ADD UNDOFILE 'undofile.dat'
# INITIAL_SIZE 16M
# UNDO_BUFFER_SIZE = 1M
# ENGINE=NDB;
#
# CREATE TABLESPACE ts1
# ADD DATAFILE 'datafile.dat'
# USE LOGFILE GROUP lg1
# INITIAL_SIZE 12M
# ENGINE NDB;
#
# CREATE TABLE t_string_4 (
#   t_pk INT NOT NULL PRIMARY KEY,
#   t_test INT,
#   t_name CHAR(30),
#   t_blob BLOB,
#   t_text TEXT,
#   INDEX (t_pk,t_name)
# )
# TABLESPACE ts1 STORAGE DISK
# ENGINE=NDB;
#
# INSERT INTO t_string_4 VALUE (
#   1,
#   1,
#   'name1',
#   'a123456789b123456789c123456789d123456789e123456789f123456789g123456789',
#   'a123456789b123456789c123456789d123456789e123456789f123456789g123456789');
#  INSERT INTO t_string_4 VALUE (
#    2,
#    1,
#    'name2',
#    'a123456789b123456789c123456789d123456789e123456789f123456789g123456789',
#    'a123456789b123456789c123456789d123456789e123456789f123456789g123456789');
#
##########################################
##############  for long blob ############
##########################################
#
# CREATE TABLE  t_string_5 (
#   t_pk INT NOT NULL PRIMARY KEY,
#   t_longblob  LONGBLOB,
#   t_longtext  LONGTEXT
#  ) ENGINE=NDB;
#
##### b1 length 128k ######
#
# set @b1 = '01234567012345670123456701234567';
# set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
# set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
# set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
# set @b1 = concat(@b1,@b1,@b1,@b1,@b1,@b1,@b1,@b1);
#
# INSERT INTO t_string_5 VALUES(1,@b1,@b1);
# INSERT INTO t_string_5 VALUES(2,@b1,@b1);
#
#######################################
###   Operations during backup     ####
#######################################
#
######## for basic test with all data type ########
#
# DELETE FROM t_num WHERE t_pk=2;
# DELETE FROM t_datetime WHERE t_pk=2;
# DELETE FROM t_string_1 WHERE t_pk=2;
# DELETE FROM t_string_2 WHERE t_pk=2;
# DELETE FROM t_gis WHERE t_pk=3;
# DELETE FROM t_gis WHERE t_pk=4;
#
#
# INSERT INTO t_num VALUE (
#   2,
#   2,
#   b'1010101010101010101010101010101010101010101010101010101010101010',
#   125,
#   1,
#   32765,
#   8388606,
#   2147483647,
#   9223372036854775807,
#   1e+20,
#   1e+150,
#   '331.0000000000'
# );
# INSERT INTO t_datetime VALUE (
#   2,
#   2,
#   '1998-01-01',
#   '2006-08-10 10:11:12',
#   20021029165106,
#   '19:38:34',
#   2155
# );
#
# INSERT INTO t_string_1 VALUE (
#   2,
#   2,
#   'abcdefghijklmn',
#   'abcdefghijklmnabcdefghijklmnabcdefghijklmnabcdefghijklmn',
#   0x612020,
#   0x4100
# );
#
# INSERT INTO t_string_2 VALUE (
#   2,
#   2,
#   'abcdefghijklmnabcdefghijklmn',
#   'abcdefghijklmnabcdefghijklmn',
#   'a123456789b123456789c123456789d123456789e123456789f123456789g123456789',
#   'a123456789b123456789c123456789d123456789e123456789f123456789g123456789',
#   'a123456789b123456789c123456789d123456789e123456789f123456789g123456789',
#   'a123456789b123456789c123456789d123456789e123456789f123456789g123456789',
#   'a123456789b123456789c123456789d123456789e123456789f123456789g123456789',
#   'a123456789b123456789c123456789d123456789e123456789f123456789g123456789',
#   '001001',
#   'a'
# );
#
#
# INSERT INTO t_gis VALUE (
#   3,
#   2,
#   PointFromText('POINT(10 10)'),
#   LineStringFromText('LINESTRING(10 10,20 10,20 20,10 20,10 10)'),
#   PolyFromText('POLYGON((0 0,50 0,50 50,0 50,0 0), (10 10,20 10,20 20,10 20,10 10))'),
#   MPointFromText('MULTIPOINT(1 1,11 11,11 21,21 21)'),
#   MLineFromText('MULTILINESTRING((10 48,10 21,10 0))'),
#   MPolyFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))'),
#   GeomCollFromText('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))'),
#   MPolyFromText('MULTIPOLYGON(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18)),((59 18,67 18,67 13,59 13,59 18)))')
# );
#
#
# INSERT INTO t_gis VALUE (
#   4,
#   2,
#   PointFromWKB(AsWKB(PointFromText('POINT(10 20)'))),
#   LineStringFromWKB(LineString(Point(10, 10), Point(40, 10))),
#   PolyFromWKB(Polygon(LineString(Point(0, 0), Point(30, 0), Point(30, 30), Point(0, 0)))),
#   MPointFromWKB(MultiPoint(Point(3, 6), Point(4, 10))),
#   MLineFromWKB(MultiLineString(LineString(Point(1, 2), Point(3, 5)), LineString(Point(2, 5), Point(5, 8), Point(21, 7)))),
#   MPolyFromWKB(MultiPolygon(Polygon(LineString(Point(0, 3), Point(3, 3), Point(3, 0), Point(0, 3))))),
#   GeometryFromWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9)))),
#   GeometryFromWKB(GeometryCollection(Point(44, 6), LineString(Point(3, 6), Point(7, 9))))
# );
#
#
# UPDATE t_num SET t_test=3 WHERE t_pk=1;
# UPDATE t_datetime SET t_test=3 WHERE t_pk=1;
# UPDATE t_string_1 SET t_test=3 WHERE t_pk=1;
# UPDATE t_string_2 SET t_test=3 WHERE t_pk=1;
# UPDATE t_gis SET t_test=3 WHERE t_pk=1;
# UPDATE t_gis SET t_test=3 WHERE t_pk=2;
#
#
######### multi undo log entries ########
#
# DELETE FROM t_string_3 WHERE t_pk>1;
#
######### disk table and ordered index ########
#
# DELETE FROM t_string_4 WHERE t_pk=2;
# INSERT INTO t_string_4 VALUE (
#   2,
#   2,
#   'name2',
#   'a123456789b123456789c123456789d123456789e123456789f123456789g123456789',
#   'a123456789b123456789c123456789d123456789e123456789f123456789g123456789'
# );
# UPDATE t_string_4 SET t_test=3 WHERE t_pk=1;
#
######## long blob ########
#
# DELETE FROM t_string_5 WHERE t_pk>1;
#
#
#
#
#
#
######################################################
##### Restore backup files (from little endian) ######
######################################################

--disable_warnings
USE test;
DROP TABLE IF EXISTS t_num,t_datetime,t_string_1,t_string_2,t_gis,t_string_3,t_string_4,t_string_5;
--enable_warnings
--exec $NDB_RESTORE --no-defaults -b 1 -n 1 -m -r $backup_data_dir/51_undolog_le >> $NDB_TOOLS_OUTPUT
--exec $NDB_RESTORE --no-defaults -b 1 -n 2 -r $backup_data_dir/51_undolog_le >> $NDB_TOOLS_OUTPUT
--sorted_result
SHOW TABLES;
SHOW CREATE TABLE t_num;
SHOW CREATE TABLE t_datetime;
SHOW CREATE TABLE t_string_1;
SHOW CREATE TABLE t_string_2;
SHOW CREATE TABLE t_gis;
SHOW CREATE TABLE t_string_3;
SHOW CREATE TABLE t_string_4;
SHOW CREATE TABLE t_string_5;
SELECT * FROM t_datetime ORDER by t_pk;
SELECT t_pk,t_test,hex(t_bit),t_tinyint,t_bool,t_smallint,t_mediumint,t_int,t_bigint,t_float,t_double,t_decimal FROM t_num ORDER by t_pk;
SELECT t_pk,t_test,t_char,t_varchar,hex(t_binary),hex(t_varbinary) FROM t_string_1 ORDER by t_pk;
SELECT * FROM t_string_2 ORDER by t_pk;
SELECT t_pk,t_test,AsText(t_point), AsText(t_linestring),AsText(t_polygon) FROM t_gis ORDER by t_pk;
SELECT t_pk,t_test,AsText(t_multipoint), AsText(t_multilinestring),AsText(t_multipolygon) FROM t_gis ORDER by t_pk;
SELECT t_pk,t_test,AsText(t_geometrycollection), AsText(t_geometry) FROM t_gis ORDER by t_pk;
SELECT COUNT(*) FROM t_string_3 ORDER BY t_pk;
SELECT *  FROM t_string_4 ORDER BY t_pk;
SELECT t_pk,LENGTH(t_longblob),LENGTH(t_longtext) FROM t_string_5 ORDER BY t_pk;

DROP TABLE IF EXISTS t_num,t_datetime,t_string_1,t_string_2,t_gis,t_string_3,t_string_4,t_string_5;
ALTER TABLESPACE ts1
DROP DATAFILE 'datafile.dat'
ENGINE = NDB;

DROP TABLESPACE ts1
ENGINE = NDB;

DROP LOGFILE GROUP lg1
ENGINE =NDB;


#
# Restore backup files (from big endian)
#

USE test;
--exec $NDB_RESTORE --no-defaults -b 1 -n 1 -m -r $backup_data_dir/51_undolog_be >> $NDB_TOOLS_OUTPUT
--exec $NDB_RESTORE --no-defaults -b 1 -n 2 -r $backup_data_dir/51_undolog_be >> $NDB_TOOLS_OUTPUT
--sorted_result
SHOW TABLES;
SHOW CREATE TABLE t_num;
SHOW CREATE TABLE t_datetime;
SHOW CREATE TABLE t_string_1;
SHOW CREATE TABLE t_string_2;
SHOW CREATE TABLE t_gis;
SHOW CREATE TABLE t_string_3;
SHOW CREATE TABLE t_string_4;
SHOW CREATE TABLE t_string_5;
SELECT * FROM t_datetime ORDER by t_pk;
SELECT t_pk,t_test,hex(t_bit),t_tinyint,t_bool,t_smallint,t_mediumint,t_int,t_bigint,t_float,t_double,t_decimal FROM t_num ORDER by t_pk;
SELECT t_pk,t_test,t_char,t_varchar,hex(t_binary),hex(t_varbinary) FROM t_string_1 ORDER by t_pk;
SELECT * FROM t_string_2 ORDER by t_pk;
SELECT t_pk,t_test,AsText(t_point), AsText(t_linestring),AsText(t_polygon) FROM t_gis ORDER by t_pk;
SELECT t_pk,t_test,AsText(t_multipoint), AsText(t_multilinestring),AsText(t_multipolygon) FROM t_gis ORDER by t_pk;
SELECT t_pk,t_test,AsText(t_geometrycollection), AsText(t_geometry) FROM t_gis ORDER by t_pk;
SELECT COUNT(*) FROM t_string_3 ORDER BY t_pk;
SELECT *  FROM t_string_4 ORDER BY t_pk;
SELECT t_pk,LENGTH(t_longblob),LENGTH(t_longtext) FROM t_string_5 ORDER BY t_pk;

DROP TABLE IF EXISTS t_num,t_datetime,t_string_1,t_string_2,t_gis,t_string_3,t_string_4,t_string_5;
ALTER TABLESPACE ts1
DROP DATAFILE 'datafile.dat'
ENGINE = NDB;

DROP TABLESPACE ts1
ENGINE = NDB;

DROP LOGFILE GROUP lg1
ENGINE =NDB;

## end of test ##
