core/sql/regress/seabase/TEST026

-- @@@ START COPYRIGHT @@@
--
-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements.  See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership.  The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License.  You may obtain a copy of the License at
--
--   http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing,
-- software distributed under the License is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-- KIND, either express or implied.  See the License for the
-- specific language governing permissions and limitations
-- under the License.
--
-- @@@ END COPYRIGHT @@@
--
-- tests for cleanup command

set schema trafodion.sch026;
cleanup table t026t0;
cleanup index t026t1i1;
cleanup table t026t1;
cleanup schema sch026;
create schema sch026;
cleanup metadata;

log LOG026 clear;

drop table if exists t026t0;
drop table if exists t026t1 cascade;

create table t026t0 (a int not null primary key);
create table t026t1 (a int not null primary key, b int not null);
create index t026t1i1 on t026t1(b);

set parserflags 131072;
update "_MD_".objects set valid_def = 'N' where catalog_name = 'TRAFODION' and
     schema_name = 'SCH026' and object_name = 'T026T1' and object_type = 'BT';
update "_MD_".objects set valid_def = 'N' where catalog_name = 'TRAFODION' and
     schema_name = 'SCH026' and object_name = 'T026T1I1' and object_type = 'IX';
reset parserflags 131072;

invoke t026t1;
drop table t026t1;
drop index t026t1i1;
create index t026t1i2 on t026t1(b);
alter table t026t1 rename to t026t1new;
alter table t026t1 add column c int;
alter table t026t1 drop column b;
alter table t026t1 add constraint t026t1c1 unique(b);
alter table t026t1 add constraint t026t1c2 foreign key (b) references t026t0;
alter table t026t1 add constraint t026t1c3 check (b > 0);
alter table t026t1 drop constraint t026t1c3;

cleanup table t026t0;
cleanup index t026t1i1;
cleanup table t026t1;

invoke t026t0;
invoke t026t1;

set parserflags 131072;
update "_MD_".objects set valid_def = 'Y' where catalog_name = 'TRAFODION' and
     schema_name = 'SCH026' and object_name = 'T026T1' and object_type = 'BT';
update "_MD_".objects set valid_def = 'Y' where catalog_name = 'TRAFODION' and
     schema_name = 'SCH026' and object_name = 'T026T1I1' and object_type = 'IX';
reset parserflags 131072;

cqd traf_blob_as_varchar 'OFF';
set schema sch026;
cleanup schema sch026;
create private schema sch026;
create table t026t20(a int not null primary key);
create table t026t2 (a int not null primary key, 
                              b int not null unique, 
                              c int not null references t026t20(a),
                              d blob, 
                              e char(2) check (e > 'a'), 
                              f largeint generated by default as identity not null);
create index t026t2i1 on t026t2(b);
select count(*) from "_MD_".objects where schema_name = 'SCH026' and object_name like 'T026%';
cleanup table t026t2;
select count(*) from "_MD_".objects where schema_name = 'SCH026' and object_name like 'T026%';
cleanup schema sch026;
select count(*) from "_MD_".objects where schema_name = 'SCH026' and object_name like 'T026%';

create private schema sch026;
set schema sch026;
create table t026t20(a int not null primary key);
create table t026t2 (a int not null primary key, 
                              b int not null unique, 
                              c int not null references t026t20(a),
                              d blob, 
                              e char(2) check (e > 'a'), 
                              f largeint generated by default as identity not null);
create index t026t2i1 on t026t2(b);

drop hbase table "TRAFODION.SCH026.T026T2I1";
set parserflags 131072;
cqd hide_indexes 'ALL';
delete from "_MD_".objects where catalog_name = 'TRAFODION' and
     schema_name = 'SCH026' and object_name = 'T026T2' and object_type = 'BT';
delete from "_MD_".objects where catalog_name = 'TRAFODION' and
     schema_name = 'SCH026' and object_type = 'PK';
cqd hide_indexes reset;
reset parserflags 131072;

select count(*) from "_MD_".objects where schema_name = 'SCH026' and object_name like 'T026%';
cleanup table t026t2;
select count(*) from "_MD_".objects where schema_name = 'SCH026' and object_name like 'T026%';
cleanup schema sch026;
select count(*) from "_MD_".objects where schema_name = 'SCH026' and object_name like 'T026%';


-- test for CLEANUP METADATA
cleanup schema sch026;
create schema sch026;
set schema sch026;
create table t026t10(a int);
create table t026t11(a int);
create table t026t12(a int);
cleanup metadata, check;
-- drop hbase table "TRAFODION.SCH026.T026T10" from hbase shell
sh regrhbase.ksh $REGRTSTDIR/TEST026_drop_hbase &> $REGRRUNDIR/TEST026_drop_hbase.log ;
set parserflags 131072;
delete from "_MD_".objects where schema_name = 'SCH026' and object_name = 'T026T11';
set parserflags 1;
delete from table(index_table "_MD_".objects_uniq_idx) where schema_name = 'SCH026' and object_name = 'T026T12';

cleanup metadata, check;
cleanup metadata, check, return details;
cleanup metadata, return details;
cleanup metadata, check;
cleanup schema sch026;

log;