| -- Test116 (CORE) |
| -- @@@ 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 @@@ |
| -- |
| -- Functionality: |
| -- Test transactions support for DDL operations. |
| -- This test requires the ENV variable TM_ENABLE_DDL_TRANS |
| -- is enabled in ms.env and environment restarted. |
| -- Also DDL_TRANSACTIONS CQD must be 'ON'. |
| -- Expected Files: EXPECTED116 |
| |
| |
| ?section main |
| obey TEST116(clean_up); |
| |
| log LOG116 clear; |
| control query default DDL_TRANSACTIONS 'ON'; |
| |
| -- Simple create then rollback |
| ------------------------------ |
| begin work; |
| create table t116t1(a1 int, c1 int, b1 char(6)); |
| create table t116t2(a2 int, c2 int, b2 char(6)); |
| get user hbase objects, match 'TRAFODION.SCH.T116T1'; |
| get user hbase objects, match 'TRAFODION.SCH.T116T2'; |
| |
| rollback work; |
| get user hbase objects, match 'TRAFODION.SCH.T116T1'; |
| get user hbase objects, match 'TRAFODION.SCH.T116T2'; |
| |
| --repeat the same tables again |
| ------------------------------ |
| begin work; |
| create table t116t1(a1 int, c1 int, b1 char(6)); |
| create table t116t2(a2 int, c2 int, b2 char(6)); |
| get user hbase objects, match 'TRAFODION.SCH.T116T1'; |
| get user hbase objects, match 'TRAFODION.SCH.T116T2'; |
| rollback work; |
| get user hbase objects, match 'TRAFODION.SCH.T116T1'; |
| get user hbase objects, match 'TRAFODION.SCH.T116T2'; |
| |
| -- unique constraint |
| --------------------- |
| begin work; |
| create table t116t1(a1 int, c1 int, b1 char(6)); |
| alter table t116t1 add constraint tc1 unique (c1); |
| get user hbase objects, match 'TRAFODION.SCH.T116T1'; |
| get user hbase objects, match 'TRAFODION.SCH.TC1'; |
| rollback work; |
| get user hbase objects, match 'TRAFODION.SCH.T116T1'; |
| get user hbase objects, match 'TRAFODION.SCH.TC1'; |
| |
| --setup two tables |
| ------------------- |
| begin work; |
| create table t116t1(a1 int, c1 int, b1 char(6)); |
| insert into t116t1 values(1,1,'a'); |
| create table t116t2(a1 int, c1 int, b1 char(6)); |
| insert into t116t2 values(1,1,'a'); |
| commit work; |
| |
| --drop table simple case |
| -------------------------- |
| get user hbase objects, match 'TRAFODION.SCH.T116T1'; |
| begin work; |
| drop table t116t1; |
| commit work; |
| get user hbase objects, match 'TRAFODION.SCH.T116T1'; |
| |
| --drop table with rollback |
| ---------------------------- |
| --sh `echo "exists '$TEST_SCHEMA.T116T2'" | regrhbase.ksh >> LOG116`; |
| get user hbase objects, match 'TRAFODION.SCH.T116T2'; |
| begin work; |
| insert into t116t2 values(3,3,'c'); |
| select * from t116t2; |
| drop table t116t2; |
| insert into t116t2 values(1,1,'a'); |
| select * from t116t2; |
| get user hbase objects, match 'TRAFODION.SCH.T116T2'; |
| rollback work; |
| get user hbase objects, match 'TRAFODION.SCH.T116T2'; |
| select * from t116t2; |
| insert into t116t2 values(1,1,'a'); |
| select * from t116t2; |
| |
| --create table1 with dml, drop table2 |
| ------------------------------------- |
| get user hbase objects, match 'TRAFODION.SCH.T116T2'; |
| get user hbase objects, match 'TRAFODION.SCH.T116T3'; |
| begin work; |
| create table t116t3(a1 int, c1 int, b1 char(6)); |
| insert into t116t3 values(2,2,'b'); |
| get user hbase objects, match 'TRAFODION.SCH.T116T2'; |
| get user hbase objects, match 'TRAFODION.SCH.T116T3'; |
| drop table t116t2; |
| commit work; |
| get user hbase objects, match 'TRAFODION.SCH.T116T2'; |
| get user hbase objects, match 'TRAFODION.SCH.T116T3'; |
| |
| --create table1 with dml , drop table2 and rollback |
| --------------------------------------------------- |
| get user hbase objects, match 'TRAFODION.SCH.T116T3'; |
| get user hbase objects, match 'TRAFODION.SCH.T116T4'; |
| begin work; |
| create table t116t4(a1 int, c1 int, b1 char(6)); |
| insert into t116t4 values(2,2,'b'); |
| get user hbase objects, match 'TRAFODION.SCH.T116T3'; |
| get user hbase objects, match 'TRAFODION.SCH.T116T4'; |
| select * from t116t3; |
| drop table t116t3; |
| insert into t116t3 values(2,2,'b'); |
| get user hbase objects, match 'TRAFODION.SCH.T116T3'; |
| rollback work; |
| get user hbase objects, match 'TRAFODION.SCH.T116T3'; |
| get user hbase objects, match 'TRAFODION.SCH.T116T4'; |
| select * from t116t3; |
| insert into t116t3 values(2,2,'b'); |
| |
| -- tests to validate ddl definitions are correctly loaded |
| drop table if exists t116t1; |
| create table t116t1(a int not null, b varchar(2), |
| c varchar(4) character set utf8 not null); |
| create view t116v1 as select * from t116t1; |
| invoke t116t1; |
| begin work; |
| invoke t116t1; |
| invoke t116v1; |
| alter table t116t1 alter column b varchar(3); |
| invoke t116t1; |
| invoke t116v1; |
| commit work; |
| invoke t116t1; |
| invoke t116v1; |
| |
| begin work; |
| invoke t116t1; |
| invoke t116v1; |
| alter table t116t1 alter column b varchar(4); |
| invoke t116t1; |
| invoke t116v1; |
| rollback work; |
| |
| invoke t116t1; |
| invoke t116v1; |
| |
| -- concurrent ddl operations |
| drop table if exists t116t1 cascade; |
| begin work; |
| create table t116t1 (a int); |
| |
| sh sqlci -iTEST116\(concurrent_ddl\) >> LOG116; |
| |
| commit work; |
| invoke t116t1; |
| cleanup table t116t1; |
| |
| -- test table/schema drops and get stmts with read uncommitted access |
| create schema if not exists t116sch1; |
| set schema t116sch1; |
| drop table if exists t116t1; |
| drop table if exists t116t2; |
| |
| create table t116t1 (a int); |
| begin work; |
| get tables in schema t116sch1, match 'T116T1'; |
| drop table t116t1; |
| get tables in schema t116sch1; |
| create table t116t2 (a int); |
| get tables in schema t116sch1; |
| drop schema t116sch1 cascade; |
| get tables in schema t116sch1; |
| select count(*) from "_MD_".objects where schema_name = 'T116SCH1' |
| for read uncommitted access; |
| rollback work; |
| get tables in schema t116sch1; |
| |
| set schema sch; |
| |
| -- ddl with autocommit off |
| set transaction autocommit off; |
| drop table if exists t116t2; |
| commit work; |
| invoke t116t2; |
| create table t116t2 (a int); |
| invoke t116t2; |
| rollback work; |
| invoke t116t2; |
| create table t116t2 (a int); |
| commit work; |
| invoke t116t2; |
| -- ddl not allowed with autocommit off |
| purgedata t116t2; |
| alter table t116t2 alter column a largeint; |
| cleanup table t116t2; |
| rollback work; |
| set transaction autocommit on; |
| drop table if exists t116t2; |
| create table t116t2 (a int) attribute hbase format; |
| set transaction autocommit off; |
| alter table t116t2 alter column a largeint; |
| rollback work; |
| set transaction autocommit on; |
| |
| -- index creation checking |
| -- index creation is done with multiple transactions |
| -- to avoid populating the index within the transaction |
| create table t116t5 (c1 int not null primary key, c2 int, c3 int) ; |
| insert into t116t5 values (1,1,1), (2,2,2), (3,3,2); |
| -- create index - Second attempt should fail |
| create index t116t5_i1 on t116t5(c2); |
| create index t116t5_i1 on t116t5(c2); |
| set parserflags 1; |
| select count(*) from table(index_table t116t5_i1); |
| -- create unique index - Both attempts should fail with the same error |
| create unique index t116t5_i2 on t116t5(c3); |
| create unique index t116t5_i2 on t116t5(c3); |
| -- create unique index with no populate |
| create unique index t116t5_i2 on t116t5(c3) no populate; |
| -- Both attempts should fail with the same error |
| populate index t116t5_i2 on t116t5; |
| populate index t116t5_i2 on t116t5; |
| |
| -- region transactions support |
| create table t116t6 (a int not null primary key, b int); |
| |
| -- scenario 1, concurrent hbase transaction with insert(checkAndPut) |
| begin work; |
| insert into t116t6 values (1,1); |
| |
| -- insert a row using a concurrent hbase xn |
| sh sqlci -iTEST116\(concurrent_hbase_xn_ins\) >> LOG116; |
| |
| -- should not show conflict |
| commit work; |
| select * from t116t6; |
| |
| -- scenario 2, concurrent region transaction with insert(checkAndPut) |
| delete from t116t6; |
| begin work; |
| insert into t116t6 values (1,1); |
| |
| -- insert a row using a concurrent region xn |
| sh sqlci -iTEST116\(concurrent_region_xn_ins\) >> LOG116; |
| |
| -- should show conflict |
| commit work; |
| select * from t116t6; |
| |
| -- scenario 3, concurrent hbase transaction with upsert(put) |
| delete from t116t6; |
| begin work; |
| upsert into t116t6 values (1,1); |
| |
| -- upsert a row using a concurrent hbase xn |
| sh sqlci -iTEST116\(concurrent_hbase_xn_ups\) >> LOG116; |
| |
| -- should not show conflict |
| commit work; |
| select * from t116t6; |
| |
| -- scenario 4, concurrent region transaction with upsert(put) |
| delete from t116t6; |
| begin work; |
| upsert into t116t6 values (1,1); |
| |
| -- insert a row using a concurrent region xn |
| sh sqlci -iTEST116\(concurrent_region_xn_ups\) >> LOG116; |
| |
| -- should show conflict |
| commit work; |
| select * from t116t6; |
| |
| -- scenario 5, concurrent hbase transaction with delete |
| delete from t116t6; |
| insert into t116t6 values (1,1); |
| begin work; |
| delete from t116t6 where a = 1 and b > 0; |
| |
| -- delete a row using a concurrent hbase xn |
| sh sqlci -iTEST116\(concurrent_hbase_xn_del\) >> LOG116; |
| |
| -- should not show conflict |
| commit work; |
| select * from t116t6; |
| |
| -- scenario 6, concurrent region transaction with delete |
| delete from t116t6; |
| insert into t116t6 values (1,1); |
| begin work; |
| delete from t116t6 where a = 1 and b > 0; |
| |
| -- delete a row using a concurrent region xn |
| sh sqlci -iTEST116\(concurrent_region_xn_del\) >> LOG116; |
| |
| -- should show conflict |
| commit work; |
| select * from t116t6; |
| |
| -- scenario 7, concurrent hbase transaction with checkanddelete |
| delete from t116t6; |
| insert into t116t6 values (1,1); |
| begin work; |
| delete from t116t6 where a = 1; |
| |
| -- delete a row using a concurrent hbase xn |
| sh sqlci -iTEST116\(concurrent_hbase_xn_checkanddel\) >> LOG116; |
| |
| -- should not show conflict |
| commit work; |
| select * from t116t6; |
| |
| -- scenario 8, concurrent region transaction with checkanddelete |
| delete from t116t6; |
| insert into t116t6 values (1,1); |
| begin work; |
| delete from t116t6 where a = 1; |
| |
| -- delete a row using a concurrent region xn |
| sh sqlci -iTEST116\(concurrent_region_xn_checkanddel\) >> LOG116; |
| |
| -- should show conflict |
| commit work; |
| select * from t116t6; |
| |
| -- scenario 9, concurrent hbase transaction with checkandupdate |
| delete from t116t6; |
| insert into t116t6 values (1,1); |
| begin work; |
| update t116t6 set b = 333 where a = 1; |
| |
| -- update row using a concurrent hbase xn |
| sh sqlci -iTEST116\(concurrent_hbase_xn_checkandupd\) >> LOG116; |
| |
| -- should not show conflict |
| commit work; |
| select * from t116t6; |
| |
| -- scenario 10, concurrent region transaction with checkandupdate |
| delete from t116t6; |
| insert into t116t6 values (1,1); |
| begin work; |
| update t116t6 set b = 333 where a = 1; |
| |
| -- update row using a concurrent region xn |
| sh sqlci -iTEST116\(concurrent_region_xn_checkandupd\) >> LOG116; |
| |
| -- should show conflict |
| commit work; |
| select * from t116t6; |
| |
| |
| obey TEST116(clean_up); |
| log; |
| exit; |
| |
| ?section concurrent_ddl |
| cqd ddl_transactions 'ON'; |
| begin work; |
| |
| -- should fail |
| create table t116t1 (a int); |
| commit work; |
| |
| ?section concurrent_hbase_xn_ins |
| cqd traf_use_region_xn 'OFF'; |
| prepare s from insert into trafodion.sch.t116t6 values (1,2); |
| explain options 'f' s; |
| execute s; |
| |
| ?section concurrent_hbase_xn_ups |
| cqd traf_use_region_xn 'OFF'; |
| prepare s from upsert into trafodion.sch.t116t6 values (1,2); |
| explain options 'f' s; |
| execute s; |
| |
| ?section concurrent_region_xn_ins |
| cqd traf_use_region_xn 'ON'; |
| prepare s from insert into trafodion.sch.t116t6 values (1,2); |
| explain options 'f' s; |
| execute s; |
| |
| ?section concurrent_region_xn_ups |
| cqd traf_use_region_xn 'ON'; |
| prepare s from upsert into trafodion.sch.t116t6 values (1,2); |
| explain options 'f' s; |
| execute s; |
| |
| ?section concurrent_hbase_xn_del |
| cqd traf_use_region_xn 'OFF'; |
| prepare s from delete from trafodion.sch.t116t6 where a = 1 and b > 0; |
| explain options 'f' s; |
| execute s; |
| |
| ?section concurrent_region_xn_del |
| cqd traf_use_region_xn 'ON'; |
| prepare s from delete from trafodion.sch.t116t6 where a = 1 and b > 0; |
| explain options 'f' s; |
| execute s; |
| |
| ?section concurrent_hbase_xn_checkanddel |
| cqd traf_use_region_xn 'OFF'; |
| prepare s from delete from trafodion.sch.t116t6 where a = 1; |
| explain options 'f' s; |
| execute s; |
| |
| ?section concurrent_region_xn_checkanddel |
| cqd traf_use_region_xn 'ON'; |
| prepare s from delete from trafodion.sch.t116t6 where a = 1; |
| explain options 'f' s; |
| execute s; |
| |
| ?section concurrent_hbase_xn_checkandupd |
| cqd traf_use_region_xn 'OFF'; |
| prepare s from update trafodion.sch.t116t6 set b = 111 where a = 1; |
| explain options 'f' s; |
| execute s; |
| |
| ?section concurrent_region_xn_checkandupd |
| cqd traf_use_region_xn 'ON'; |
| prepare s from update trafodion.sch.t116t6 set b = 111 where a = 1; |
| explain options 'f' s; |
| execute s; |
| |
| ?section clean_up |
| drop table t116t1; |
| drop table t116t2; |
| drop table t116t3; |
| drop table t116t4; |
| drop table t116t5; |
| drop table t116t6; |
| |