Added: db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/jdk16/TransactionTable.out
URL: http://svn.apache.org/viewcvs/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/jdk16/TransactionTable.out?rev=386169&view=auto
==============================================================================
--- db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/jdk16/TransactionTable.out (added)
+++ db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/jdk16/TransactionTable.out Wed Mar 15 13:31:53 2006
@@ -0,0 +1,259 @@
+ij> -- testing Transaction table
+maximumdisplaywidth 9000;
+ij> connect 'wombat' as c1;
+ij(C1)> set isolation to rr;
+0 rows inserted/updated/deleted
+ij(C1)> create view xactTable as
+select username, type, status,
+case when first_instant is NULL then 'readonly' else 'not readonly' end as readOnly, cast(sql_text as varchar(512)) sql_text
+ from syscs_diag.transaction_table
+ where type != 'InternalTransaction';
+0 rows inserted/updated/deleted
+ij(C1)> commit;
+ij(C1)> select * from xactTable order by username, sql_text, status, type;
+USERNAME |TYPE |STATUS |READONLY |SQL_TEXT
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+APP |UserTransaction |IDLE |readonly |select * from xactTable order by username, sql_text, status, type
+APP |UserTransaction |IDLE |readonly |NULL
+ij(C1)> create table foo (a int);
+0 rows inserted/updated/deleted
+ij(C1)> create index fooi on foo (a);
+0 rows inserted/updated/deleted
+ij(C1)> select * from xactTable order by username, sql_text, status, type;
+USERNAME |TYPE |STATUS |READONLY |SQL_TEXT
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+APP |UserTransaction |IDLE |readonly |select * from xactTable order by username, sql_text, status, type
+APP |UserTransaction |IDLE |readonly |NULL
+ij(C1)> autocommit off;
+ij(C1)> select * from foo;
+A
+-----------
+ij(C1)> select * from xactTable order by username, sql_text, status, type;
+USERNAME |TYPE |STATUS |READONLY |SQL_TEXT
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+APP |UserTransaction |ACTIVE |readonly |select * from xactTable order by username, sql_text, status, type
+APP |UserTransaction |IDLE |readonly |NULL
+ij(C1)> select type, lockcount as cnt, mode, tablename, lockname, state
+from syscs_diag.lock_table
+where tableType <> 'S'
+order by lockname, mode, cnt, state;
+TYPE |CNT |MODE|TABLENAME |LOCKNAME |STATE
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+TABLE|1 |S |FOO |Tablelock |GRANT
+ij(C1)> commit;
+ij(C1)> select * from xactTable order by username, sql_text, status, type;
+USERNAME |TYPE |STATUS |READONLY |SQL_TEXT
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+APP |UserTransaction |IDLE |readonly |select * from xactTable order by username, sql_text, status, type
+APP |UserTransaction |IDLE |readonly |NULL
+ij(C1)> select type, lockcount as cnt, mode, tablename, lockname, state
+from syscs_diag.lock_table ;
+TYPE |CNT |MODE|TABLENAME |LOCKNAME |STATE
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ij(C1)> insert into foo values (1), (3), (5), (7), (9);
+5 rows inserted/updated/deleted
+ij(C1)> select * from xactTable order by username, sql_text, status, type;
+USERNAME |TYPE |STATUS |READONLY |SQL_TEXT
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+APP |UserTransaction |ACTIVE |not readonly|select * from xactTable order by username, sql_text, status, type
+APP |UserTransaction |IDLE |readonly |NULL
+ij(C1)> select type, lockcount as cnt, mode, tablename, lockname, state
+from syscs_diag.lock_table
+where tableType <> 'S'
+order by lockname, mode, cnt, state;
+TYPE |CNT |MODE|TABLENAME |LOCKNAME |STATE
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ROW |1 |X |FOO |(1,10) |GRANT
+ROW |1 |X |FOO |(1,11) |GRANT
+ROW |1 |X |FOO |(1,7) |GRANT
+ROW |1 |X |FOO |(1,8) |GRANT
+ROW |1 |X |FOO |(1,9) |GRANT
+TABLE|2 |IX |FOO |Tablelock |GRANT
+ij(C1)> commit;
+ij(C1)> select * from xactTable order by username, sql_text, status, type;
+USERNAME |TYPE |STATUS |READONLY |SQL_TEXT
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+APP |UserTransaction |IDLE |readonly |select * from xactTable order by username, sql_text, status, type
+APP |UserTransaction |IDLE |readonly |NULL
+ij(C1)> select type, lockcount as cnt, mode, tablename, lockname, state
+from syscs_diag.lock_table;
+TYPE |CNT |MODE|TABLENAME |LOCKNAME |STATE
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ij(C1)> insert into foo values (6), (10);
+2 rows inserted/updated/deleted
+ij(C1)> -- make another connection
+connect 'wombat' as c2;
+ij(C2)> set isolation to rr;
+0 rows inserted/updated/deleted
+ij(C2)> autocommit off;
+ij(C2)> select * from xactTable order by username, sql_text, status, type;
+USERNAME |TYPE |STATUS |READONLY |SQL_TEXT
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+APP |UserTransaction |IDLE |readonly |select * from xactTable order by username, sql_text, status, type
+APP |UserTransaction |ACTIVE |not readonly|NULL
+APP |UserTransaction |IDLE |readonly |NULL
+ij(C2)> select type, lockcount as cnt, mode, tablename, lockname, state
+from syscs_diag.lock_table
+where tableType <> 'S'
+order by lockname, mode, cnt, state;
+TYPE |CNT |MODE|TABLENAME |LOCKNAME |STATE
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ROW |1 |X |FOO |(1,12) |GRANT
+ROW |1 |X |FOO |(1,13) |GRANT
+TABLE|2 |IX |FOO |Tablelock |GRANT
+ij(C2)> select * from xactTable order by username, sql_text, status, type;
+USERNAME |TYPE |STATUS |READONLY |SQL_TEXT
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+APP |UserTransaction |ACTIVE |readonly |select * from xactTable order by username, sql_text, status, type
+APP |UserTransaction |ACTIVE |not readonly|NULL
+APP |UserTransaction |IDLE |readonly |NULL
+ij(C2)> select type, lockcount as cnt, mode, tablename, lockname, state
+from syscs_diag.lock_table
+where tableType <> 'S'
+order by lockname, mode, cnt, state;
+TYPE |CNT |MODE|TABLENAME |LOCKNAME |STATE
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ROW |1 |X |FOO |(1,12) |GRANT
+ROW |1 |X |FOO |(1,13) |GRANT
+TABLE|2 |IX |FOO |Tablelock |GRANT
+ij(C2)> autocommit off;
+ij(C2)> select * from foo where a < 2;
+A
+-----------
+1
+ij(C2)> select * from xactTable order by username, sql_text, status, type;
+USERNAME |TYPE |STATUS |READONLY |SQL_TEXT
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+APP |UserTransaction |ACTIVE |readonly |select * from xactTable order by username, sql_text, status, type
+APP |UserTransaction |ACTIVE |not readonly|NULL
+APP |UserTransaction |IDLE |readonly |NULL
+ij(C2)> select type, lockcount as cnt, mode, tablename, lockname, state
+from syscs_diag.lock_table
+where tableType <> 'S'
+order by lockname, mode, cnt, state;
+TYPE |CNT |MODE|TABLENAME |LOCKNAME |STATE
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ROW |1 |X |FOO |(1,12) |GRANT
+ROW |1 |X |FOO |(1,13) |GRANT
+ROW |1 |S |FOO |(1,3) |GRANT
+ROW |1 |S |FOO |(1,7) |GRANT
+TABLE|1 |IS |FOO |Tablelock |GRANT
+TABLE|2 |IX |FOO |Tablelock |GRANT
+ij(C2)> insert into foo values (2), (4);
+2 rows inserted/updated/deleted
+ij(C2)> select * from xactTable order by username, sql_text, status, type;
+USERNAME |TYPE |STATUS |READONLY |SQL_TEXT
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+APP |UserTransaction |ACTIVE |not readonly|select * from xactTable order by username, sql_text, status, type
+APP |UserTransaction |ACTIVE |not readonly|NULL
+APP |UserTransaction |IDLE |readonly |NULL
+ij(C2)> select type, lockcount as cnt, mode, tablename, lockname, state
+from syscs_diag.lock_table
+where tableType <> 'S'
+order by lockname, mode, cnt, state;
+TYPE |CNT |MODE|TABLENAME |LOCKNAME |STATE
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ROW |1 |X |FOO |(1,12) |GRANT
+ROW |1 |X |FOO |(1,13) |GRANT
+ROW |1 |X |FOO |(1,14) |GRANT
+ROW |1 |X |FOO |(1,15) |GRANT
+ROW |1 |S |FOO |(1,3) |GRANT
+ROW |1 |S |FOO |(1,7) |GRANT
+TABLE|1 |IS |FOO |Tablelock |GRANT
+TABLE|2 |IX |FOO |Tablelock |GRANT
+TABLE|2 |IX |FOO |Tablelock |GRANT
+ij(C2)> -- this should block and result in a timeout
+select * from foo;
+ERROR 40XL1: A lock could not be obtained within the time requested
+ij(C2)> select * from xactTable order by username, sql_text, status, type;
+USERNAME |TYPE |STATUS |READONLY |SQL_TEXT
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+APP |UserTransaction |IDLE |readonly |select * from xactTable order by username, sql_text, status, type
+APP |UserTransaction |ACTIVE |not readonly|NULL
+APP |UserTransaction |IDLE |readonly |NULL
+ij(C2)> -- when last statement finished rolling back, this transaction should be IDLE;
+select type, lockcount as cnt, mode, tablename, lockname, state
+from syscs_diag.lock_table
+where tableType <> 'S'
+order by lockname, mode, cnt, state;
+TYPE |CNT |MODE|TABLENAME |LOCKNAME |STATE
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ROW |1 |X |FOO |(1,12) |GRANT
+ROW |1 |X |FOO |(1,13) |GRANT
+TABLE|2 |IX |FOO |Tablelock |GRANT
+ij(C2)> -- this should also block
+drop table foo;
+ERROR 40XL1: A lock could not be obtained within the time requested
+ij(C2)> select * from xactTable order by username, sql_text, status, type;
+USERNAME |TYPE |STATUS |READONLY |SQL_TEXT
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+APP |UserTransaction |IDLE |readonly |select * from xactTable order by username, sql_text, status, type
+APP |UserTransaction |ACTIVE |not readonly|NULL
+APP |UserTransaction |IDLE |readonly |NULL
+ij(C2)> select type, lockcount as cnt, mode, tablename, lockname, state
+from syscs_diag.lock_table
+where tableType <> 'S'
+order by lockname, mode, cnt, state;
+TYPE |CNT |MODE|TABLENAME |LOCKNAME |STATE
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ROW |1 |X |FOO |(1,12) |GRANT
+ROW |1 |X |FOO |(1,13) |GRANT
+TABLE|2 |IX |FOO |Tablelock |GRANT
+ij(C2)> commit;
+ij(C2)> disconnect;
+ij> set connection c1;
+ij(C1)> select * from xactTable order by username, sql_text, status, type;
+USERNAME |TYPE |STATUS |READONLY |SQL_TEXT
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+APP |UserTransaction |ACTIVE |not readonly|select * from xactTable order by username, sql_text, status, type
+APP |UserTransaction |IDLE |readonly |NULL
+ij(C1)> select type, lockcount as cnt, mode, tablename, lockname, state
+from syscs_diag.lock_table
+where tableType <> 'S'
+order by lockname, mode, cnt, state;
+TYPE |CNT |MODE|TABLENAME |LOCKNAME |STATE
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ROW |1 |X |FOO |(1,12) |GRANT
+ROW |1 |X |FOO |(1,13) |GRANT
+TABLE|2 |IX |FOO |Tablelock |GRANT
+ij(C1)> drop table foo;
+0 rows inserted/updated/deleted
+ij(C1)> commit;
+ij(C1)> select * from xactTable order by username, sql_text, status, type;
+USERNAME |TYPE |STATUS |READONLY |SQL_TEXT
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+APP |UserTransaction |IDLE |readonly |select * from xactTable order by username, sql_text, status, type
+APP |UserTransaction |IDLE |readonly |NULL
+ij(C1)> select l.type, lockcount as cnt, mode, tablename, lockname, state
+from syscs_diag.lock_table l right outer join syscs_diag.transaction_table t
+ on l.xid = t.xid where l.tableType <> 'S' and t.type='UserTransaction'
+order by lockname, mode, cnt, state;
+TYPE |CNT |MODE|TABLENAME |LOCKNAME |STATE
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ij(C1)> commit;
+ij(C1)> -- ensure the system vti can not be modified.
+drop table syscs_diag.transaction_table;
+ERROR X0Y56: 'DROP TABLE' is not allowed on the System table 'SYSCS_DIAG.TRANSACTION_TABLE'.
+ij(C1)> alter table syscs_diag.transaction_table add column x int;
+ERROR 42X62: 'ALTER TABLE' is not allowed in the 'SYSCS_DIAG' schema.
+ij(C1)> update syscs_diag.transaction_table set xid = NULL;
+ERROR 42Y25: 'SYSCS_DIAG.TRANSACTION_TABLE' is a system table. Users are not allowed to modify the contents of this table.
+ij(C1)> delete from syscs_diag.transaction_table where 1 = 1;
+ERROR 42Y25: 'SYSCS_DIAG.TRANSACTION_TABLE' is a system table. Users are not allowed to modify the contents of this table.
+ij(C1)> insert into syscs_diag.transaction_table(xid) values('bad');
+ERROR 42Y25: 'SYSCS_DIAG.TRANSACTION_TABLE' is a system table. Users are not allowed to modify the contents of this table.
+ij(C1)> call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('SYSCS_DIAG', 'TRANSACTION_TABLE', 1);
+ERROR 38000: The exception 'java.sql.SQLSyntaxErrorException: 'ALTER TABLE' is not allowed in the 'SYSCS_DIAG' schema.' was thrown while evaluating an expression.
+ERROR 42X62: 'ALTER TABLE' is not allowed in the 'SYSCS_DIAG' schema.
+ij(C1)> call SYSCS_UTIL.SYSCS_INPLACE_COMPRESS_TABLE('SYSCS_DIAG', 'TRANSACTION_TABLE', 1, 1, 1);
+0 rows inserted/updated/deleted
+ij(C1)> -- ensure the old syntax still works until it is deprecated
+select xid from new org.apache.derby.diag.TransactionTable() AS t where 1 = 0;
+XID
+---------------
+ij(C1)> update new org.apache.derby.diag.TransactionTable() set xid = NULL;
+ERROR 42X08: The constructor for class 'org.apache.derby.diag.TransactionTable' cannot be used as an external virtual table because the class does not implement 'java.sql.PreparedStatement'.
+ij(C1)> delete from new org.apache.derby.diag.TransactionTable() where 1 = 0;
+ERROR 42X08: The constructor for class 'org.apache.derby.diag.TransactionTable' cannot be used as an external virtual table because the class does not implement 'java.sql.PreparedStatement'.
+ij(C1)> disconnect;
+ij>