-- source include/have_ndb.inc

--disable_warnings
DROP TABLE IF EXISTS t1,t2;
--enable_warnings

############## Duplcate Statement Testing #########

--echo **** Begin CDD Free Space  Testing ****
--echo **** Database/test setup ****

CREATE LOGFILE GROUP lg1
ADD UNDOFILE 'undofile.dat'
INITIAL_SIZE 16M
UNDO_BUFFER_SIZE = 1M
ENGINE=NDB;

ALTER LOGFILE GROUP lg1
ADD UNDOFILE 'undofile02.dat'
INITIAL_SIZE 4M ENGINE NDB;

CREATE TABLESPACE ts1
ADD DATAFILE 'datafile.dat'
USE LOGFILE GROUP lg1
INITIAL_SIZE 6M
ENGINE NDB;

CREATE TABLE t1
     (pk1 MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
      word CHAR(20))
TABLESPACE ts1 STORAGE DISK
ENGINE=NDB;

--echo **** Show space before test starts ****

--disable_query_log
SELECT TABLESPACE_NAME, FILE_NAME,
        (TOTAL_EXTENTS * EXTENT_SIZE)
         AS 'Total Extent Size',
        (FREE_EXTENTS * EXTENT_SIZE)
         AS 'Total Free In Bytes',
        (((FREE_EXTENTS * EXTENT_SIZE)*100)/(TOTAL_EXTENTS * EXTENT_SIZE))
         AS '% Free Space'        FROM INFORMATION_SCHEMA.FILES
        WHERE ENGINE="ndbcluster" and FILE_TYPE = 'DATAFILE';
--enable_query_log

--echo **** Insert small amount of data ****

LOAD DATA INFILE '../std_data_ln/words.dat' INTO TABLE t1 (word);

--echo **** Show what has been used from small insert ****

--disable_query_log
SELECT TABLESPACE_NAME, FILE_NAME,
       (TOTAL_EXTENTS * EXTENT_SIZE)
        AS 'Total Extent Size',
       (FREE_EXTENTS * EXTENT_SIZE)
        AS 'Total Free In Bytes',
       (((FREE_EXTENTS * EXTENT_SIZE)*100)/(TOTAL_EXTENTS * EXTENT_SIZE))
        AS '% Free Space'        FROM INFORMATION_SCHEMA.FILES
       WHERE ENGINE="ndbcluster" and FILE_TYPE = 'DATAFILE';
--enable_query_log

--echo **** Show Record Count t1 ****

SELECT COUNT(*) FROM t1;

--echo **** Next set of data loaded  ****

LOAD DATA INFILE '../std_data_ln/words.dat' INTO TABLE t1 (word);

--echo **** Show free after ****

--disable_query_log
SELECT TABLESPACE_NAME, FILE_NAME,
       (TOTAL_EXTENTS * EXTENT_SIZE)
        AS 'Total Extent Size',
       (FREE_EXTENTS * EXTENT_SIZE)
        AS 'Total Free In Bytes',
       (((FREE_EXTENTS * EXTENT_SIZE)*100)/(TOTAL_EXTENTS * EXTENT_SIZE))
        AS '% Free Space'
       FROM INFORMATION_SCHEMA.FILES
       WHERE ENGINE="ndbcluster" and FILE_TYPE = 'DATAFILE';
--enable_query_log

--echo **** Show Record Count t1 ****

SELECT COUNT(*) FROM t1;

--echo **** Add more rows ****

LOAD DATA INFILE '../std_data_ln/words.dat' INTO TABLE t1 (word);

--echo **** Show space once rows have been added ****

--disable_query_log
SELECT TABLESPACE_NAME, FILE_NAME,
       (TOTAL_EXTENTS * EXTENT_SIZE)
        AS 'Total Extent Size',
       (FREE_EXTENTS * EXTENT_SIZE)
        AS 'Total Free In Bytes',
       (((FREE_EXTENTS * EXTENT_SIZE)*100)/(TOTAL_EXTENTS * EXTENT_SIZE))
        AS '% Free Space'
       FROM INFORMATION_SCHEMA.FILES
       WHERE ENGINE="ndbcluster" and FILE_TYPE = 'DATAFILE';
--enable_query_log

--echo **** Show Record Count t1 ****

SELECT COUNT(*) FROM t1;

--echo **** Add more rows ****
let $j= 500;
--disable_query_log
while ($j)
{
  LOAD DATA INFILE '../std_data_ln/words.dat' INTO TABLE t1 (word);
  dec $j;
}
--enable_query_log
--echo **** Show free space ****

--disable_query_log
SELECT TABLESPACE_NAME, FILE_NAME,
       (TOTAL_EXTENTS * EXTENT_SIZE)
        AS 'Total Extent Size',
       (FREE_EXTENTS * EXTENT_SIZE)
        AS 'Total Free In Bytes',
       (((FREE_EXTENTS * EXTENT_SIZE)*100)/(TOTAL_EXTENTS * EXTENT_SIZE))
        AS '% Free Space' 
       FROM INFORMATION_SCHEMA.FILES
       WHERE ENGINE="ndbcluster" and FILE_TYPE = 'DATAFILE';
--enable_query_log

--echo **** Show Record Count t1 ****

SELECT COUNT(*) FROM t1;

--echo **** Extend by adding new data file ****

ALTER TABLESPACE ts1
ADD DATAFILE 'datafile2.dat'
INITIAL_SIZE 12M
ENGINE NDB;

--echo **** Now show space since we have extended ****

--disable_query_log
SELECT TABLESPACE_NAME, FILE_NAME,
       (TOTAL_EXTENTS * EXTENT_SIZE)
        AS 'Total Extent Size',
       (FREE_EXTENTS * EXTENT_SIZE)
        AS 'Total Free In Bytes',
       (((FREE_EXTENTS * EXTENT_SIZE)*100)/(TOTAL_EXTENTS * EXTENT_SIZE))
         AS '% Free Space'
       FROM INFORMATION_SCHEMA.FILES
       WHERE ENGINE="ndbcluster" and FILE_TYPE = 'DATAFILE';
--enable_query_log

--echo **** Add new table ****

CREATE TABLE t2
     (pk1 MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
      word CHAR(20))
TABLESPACE ts1 STORAGE DISK
ENGINE=NDB;

--echo **** Add more rows ****
let $j= 900;
--disable_query_log
while ($j)
{ 
  --error 0, 1114
  LOAD DATA INFILE '../std_data_ln/words.dat' INTO TABLE t1 (word);
  --error 0, 1114,1205
  LOAD DATA INFILE '../std_data_ln/words.dat' INTO TABLE t2 (word);
  dec $j;
}
--enable_query_log

--echo **** Show free space ****

--disable_query_log
SELECT TABLESPACE_NAME,  FILE_NAME,
       (TOTAL_EXTENTS * EXTENT_SIZE)
        AS 'Total Extent Size',
       (FREE_EXTENTS * EXTENT_SIZE)
        AS 'Total Free In Bytes',
       (((FREE_EXTENTS * EXTENT_SIZE)*100)/(TOTAL_EXTENTS * EXTENT_SIZE))
        AS '% Free Space'        FROM INFORMATION_SCHEMA.FILES
       WHERE ENGINE="ndbcluster" and FILE_TYPE = 'DATAFILE';
--enable_query_log

--echo **** Show Record Count t1 & t2 ****

SELECT COUNT(*) FROM t1;
SELECT COUNT(*) FROM t2;

--echo **** Drop table t1 ****

DROP TABLE t1;

--echo **** Show free space, I expect more space ****

--disable_query_log
SELECT TABLESPACE_NAME, FILE_NAME,
       (TOTAL_EXTENTS * EXTENT_SIZE)
        AS 'Total Extent Size',
       (FREE_EXTENTS * EXTENT_SIZE) 
        AS 'Total Free In Bytes',   
       (((FREE_EXTENTS * EXTENT_SIZE)*100)/(TOTAL_EXTENTS * EXTENT_SIZE)) 
        AS '% Free Space'
       FROM INFORMATION_SCHEMA.FILES
       WHERE ENGINE="ndbcluster" and FILE_TYPE = 'DATAFILE';
--enable_query_log

--echo **** Add more rows ****

let $j= 1000;
--disable_query_log
while ($j)
{
  LOAD DATA INFILE '../std_data_ln/words.dat' INTO TABLE t2 (word);
  dec $j;
}
--enable_query_log
--echo **** Show free space ****

--disable_query_log
SELECT TABLESPACE_NAME, FILE_NAME,
       (TOTAL_EXTENTS * EXTENT_SIZE)
        AS 'Total Extent Size',
       (FREE_EXTENTS * EXTENT_SIZE)
        AS 'Total Free In Bytes',
       (((FREE_EXTENTS * EXTENT_SIZE)*100)/(TOTAL_EXTENTS * EXTENT_SIZE))
         AS '% Free Space'
       FROM INFORMATION_SCHEMA.FILES
       WHERE ENGINE="ndbcluster" and FILE_TYPE = 'DATAFILE';
--enable_query_log

--echo **** Show Record Count t2 ****

SELECT COUNT(*) FROM t2;

--echo **** Add more rows ****

let $j= 1000;
--disable_query_log
while ($j)
{
  LOAD DATA INFILE '../std_data_ln/words.dat' INTO TABLE t2 (word);
  dec $j;
}
--enable_query_log
--echo **** Show free space ****

--disable_query_log
SELECT TABLESPACE_NAME, 
       FILE_NAME, 
       (TOTAL_EXTENTS * EXTENT_SIZE) 
        AS 'Total Extent Size',
       (FREE_EXTENTS * EXTENT_SIZE) 
        AS 'Total Free In Bytes',        
       (((FREE_EXTENTS * EXTENT_SIZE)*100)/(TOTAL_EXTENTS * EXTENT_SIZE))        
        AS '% Free Space' 
       FROM INFORMATION_SCHEMA.FILES        
       WHERE ENGINE="ndbcluster" and FILE_TYPE = 'DATAFILE';
--enable_query_log

--echo **** Show Record Count t2 ****
SELECT COUNT(*) FROM t2;

--echo **** Add more rows ****

let $j= 2000;
--disable_query_log
while ($j)
{
  LOAD DATA INFILE '../std_data_ln/words.dat' INTO TABLE t2 (word);
  dec $j;
}
--enable_query_log
--echo **** Show free space ****

--disable_query_log
SELECT TABLESPACE_NAME,
       FILE_NAME,
       (TOTAL_EXTENTS * EXTENT_SIZE)
        AS 'Total Extent Size',
       (FREE_EXTENTS * EXTENT_SIZE)
        AS 'Total Free In Bytes',
       (((FREE_EXTENTS * EXTENT_SIZE)*100)/(TOTAL_EXTENTS * EXTENT_SIZE))
        AS '% Free Space'
       FROM INFORMATION_SCHEMA.FILES
       WHERE ENGINE="ndbcluster" and FILE_TYPE = 'DATAFILE';
--enable_query_log

--echo **** Show Record Count t2 ****

SELECT COUNT(*) FROM t2;

--echo **** Add more rows ****

let $j= 2000;
--disable_query_log
while ($j)
{
 --error 0,1114 
 LOAD DATA INFILE '../std_data_ln/words.dat' INTO TABLE t2 (word);
  dec $j;
}
--enable_query_log

--echo **** Show free space ****

--disable_query_log
SELECT TABLESPACE_NAME,
       FILE_NAME,
       (TOTAL_EXTENTS * EXTENT_SIZE)
        AS 'Total Extent Size',
       (FREE_EXTENTS * EXTENT_SIZE)
        AS 'Total Free In Bytes',
       (((FREE_EXTENTS * EXTENT_SIZE)*100)/(TOTAL_EXTENTS * EXTENT_SIZE))
        AS '% Free Space'
       FROM INFORMATION_SCHEMA.FILES
       WHERE ENGINE="ndbcluster" and FILE_TYPE = 'DATAFILE';
--enable_query_log

--echo **** Show Record Count t2 ****

SELECT COUNT(*) FROM t2;

--echo **** Test cleanup ****

DROP TABLE t2;

ALTER TABLESPACE ts1
DROP DATAFILE 'datafile2.dat'
ENGINE NDB;

ALTER TABLESPACE ts1
DROP DATAFILE 'datafile.dat'
ENGINE=NDB;

DROP TABLESPACE ts1
ENGINE=NDB;

DROP LOGFILE GROUP lg1
ENGINE=NDB;

# End of 5.1 test