--! qt:dataset:part
-- create database with multiple tables, views.
-- Use both partitioned and non-partitioned tables, as well as
-- tables with specific storage locations
-- verify the drop the database with cascade works and that the directories
-- outside the database's default storage are removed as part of the drop

CREATE DATABASE db5;
SHOW DATABASES;
USE db5;

set hive.stats.dbclass=fs;
dfs ${system:test.dfs.mkdir} ${system:test.tmp.dir}/dbcascade/temp;
dfs -rmr ${system:test.tmp.dir}/dbcascade;
dfs ${system:test.dfs.mkdir} ${system:test.tmp.dir}/dbcascade;

-- add a table, view
CREATE TABLE temp_tbl_n0 (id INT, name STRING);
LOAD DATA LOCAL INPATH '../../data/files/kv1.txt' INTO TABLE temp_tbl_n0;
CREATE VIEW temp_tbl_view AS SELECT * FROM temp_tbl_n0;

dfs ${system:test.dfs.mkdir} ${system:test.tmp.dir}/dbcascade/temp_tbl2;
-- add a table, view with a different storage location
CREATE TABLE temp_tbl2_n0 (id INT, name STRING) LOCATION 'file:${system:test.tmp.dir}/dbcascade/temp_tbl2';
LOAD DATA LOCAL INPATH '../../data/files/kv1.txt' into table temp_tbl2_n0;
CREATE VIEW temp_tbl2_view AS SELECT * FROM temp_tbl2_n0;

-- add a partitioned table, view
CREATE TABLE part_tab (id INT, name STRING)  PARTITIONED BY (ds string);
LOAD DATA LOCAL INPATH '../../data/files/kv1.txt' INTO TABLE part_tab PARTITION (ds='2008-04-09');
LOAD DATA LOCAL INPATH '../../data/files/kv1.txt' INTO TABLE part_tab PARTITION (ds='2009-04-09');

dfs ${system:test.dfs.mkdir} ${system:test.tmp.dir}/dbcascade/part_tab2;
-- add a partitioned table, view with a different storage location
CREATE TABLE part_tab2 (id INT, name STRING)  PARTITIONED BY (ds string)
		LOCATION 'file:${system:test.tmp.dir}/dbcascade/part_tab2';
LOAD DATA LOCAL INPATH '../../data/files/kv1.txt' INTO TABLE part_tab2 PARTITION (ds='2008-04-09');
LOAD DATA LOCAL INPATH '../../data/files/kv1.txt' INTO TABLE part_tab2 PARTITION (ds='2009-04-09');

dfs ${system:test.dfs.mkdir} ${system:test.tmp.dir}/dbcascade/part_tab3;
dfs ${system:test.dfs.mkdir} ${system:test.tmp.dir}/dbcascade/part_tab3_p1;
-- add a partitioned table, view with a different storage location
CREATE TABLE part_tab3 (id INT, name STRING)  PARTITIONED BY (ds string)
		LOCATION 'file:${system:test.tmp.dir}/dbcascade/part_tab3';
ALTER TABLE part_tab3 ADD PARTITION  (ds='2007-04-09') LOCATION 'file:${system:test.tmp.dir}/dbcascade/part_tab3_p1';
LOAD DATA LOCAL INPATH '../../data/files/kv1.txt' INTO TABLE part_tab3 PARTITION (ds='2008-04-09');
LOAD DATA LOCAL INPATH '../../data/files/kv1.txt' INTO TABLE part_tab3 PARTITION (ds='2009-04-09');


dfs ${system:test.dfs.mkdir} ${system:test.tmp.dir}/dbcascade/extab1;
dfs -touchz ${system:test.tmp.dir}/dbcascade/extab1/file1.txt;
-- add an external table
CREATE EXTERNAL TABLE extab1(id INT, name STRING) ROW FORMAT
              DELIMITED FIELDS TERMINATED BY ''
              LINES TERMINATED BY '\n'
              STORED AS TEXTFILE
              LOCATION 'file:${system:test.tmp.dir}/dbcascade/extab1';

-- add a table
CREATE TABLE temp_tbl3 (id INT, name STRING);
LOAD DATA LOCAL INPATH '../../data/files/kv1.txt' into table temp_tbl3;

-- drop the database with cascade
DROP DATABASE db5 CASCADE;

dfs -test -d ${system:test.tmp.dir}/dbcascade/extab1;
dfs -rmr ${system:test.tmp.dir}/dbcascade;