rollforwardrecovery.out

derby database source code.good for you.

ij> -- reusing container id case
run resource 'createTestProcedures.subsql';
ij> CREATE FUNCTION  PADSTRING (DATA VARCHAR(32000), LENGTH INTEGER) RETURNS VARCHAR(32000) EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.Formatters.padString' LANGUAGE JAVA PARAMETER STYLE JAVA;
0 rows inserted/updated/deleted
ij> CREATE PROCEDURE WAIT_FOR_POST_COMMIT() DYNAMIC RESULT SETS 0 LANGUAGE JAVA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.T_Access.waitForPostCommitToFinish' PARAMETER STYLE JAVA;
0 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE(
    'extinout/mybackup', 0);
0 rows inserted/updated/deleted
ij> create table t1(a int not null primary key) ;
0 rows inserted/updated/deleted
ij> insert into t1 values(1) ;
1 row inserted/updated/deleted
ij> insert into t1 values(2) ;
1 row inserted/updated/deleted
ij> insert into t1 values(3 ) ;
1 row inserted/updated/deleted
ij> drop table t1;
0 rows inserted/updated/deleted
ij> connect 'jdbc:derby:wombat;shutdown=true';
ERROR 08006: Database 'wombat' shutdown.
ij> disconnect;
ij> connect 'jdbc:derby:wombat';
ij> --checkpoint to make sure that 
--the stub is dropped and we use the 
--the same container id which we dropped earlier
CALL SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE();
0 rows inserted/updated/deleted
ij> create table t1(a int not null primary key) ;
0 rows inserted/updated/deleted
ij> insert into t1 values(4) ;
1 row inserted/updated/deleted
ij> insert into t1 values(5);
1 row inserted/updated/deleted
ij> insert into t1 values(6);
1 row inserted/updated/deleted
ij> select * from t1;
A          
-----------
4          
5          
6          
ij> connect 'jdbc:derby:wombat;shutdown=true';
ERROR 08006: Database 'wombat' shutdown.
ij> disconnect;
ij> --performa rollforward recovery
connect 'jdbc:derby:wombat;rollForwardRecoveryFrom=extinout/mybackup/wombat';
ij> select * from t1 ;
A          
-----------
4          
5          
6          
ij> --drop the above tables and create
--again tables with foreign key references and
--make surte they are properly recovered
drop table t1;
0 rows inserted/updated/deleted
ij> create table t1(a int not null);
0 rows inserted/updated/deleted
ij> insert into t1 values(1) ;
1 row inserted/updated/deleted
ij> insert into t1 values(2) ;
1 row inserted/updated/deleted
ij> insert into t1 values(3 ) ;
1 row inserted/updated/deleted
ij> insert into t1 values(4 ) ;
1 row inserted/updated/deleted
ij> insert into t1 values(5 ) ;
1 row inserted/updated/deleted
ij> alter table t1 add constraint uk1 unique(a);
0 rows inserted/updated/deleted
ij> create table t2(b int);
0 rows inserted/updated/deleted
ij> insert into t2 values(1);
1 row inserted/updated/deleted
ij> insert into t2 values(2);
1 row inserted/updated/deleted
ij> insert into t2 values(3);
1 row inserted/updated/deleted
ij> alter table t2 add constraint c1 foreign key (b)
                             references t1(a);
0 rows inserted/updated/deleted
ij> connect 'jdbc:derby:wombat;shutdown=true';
ERROR 08006: Database 'wombat' shutdown.
ij> disconnect;
ij> connect 'jdbc:derby:wombat;rollForwardRecoveryFrom=extinout/mybackup/wombat';
ij> insert into t2 values(4);
1 row inserted/updated/deleted
ij> insert into t2 values(5);
1 row inserted/updated/deleted
ij> select * from t1;
A          
-----------
1          
2          
3          
4          
5          
ij> select * from t2;
B          
-----------
1          
2          
3          
4          
5          
ij> --add a duplicate value to make sure index is intact
insert into t1 values(1);
ERROR 23505: The statement was aborted because it would have caused a duplicate key value in a unique or primary key constraint or unique index identified by 'UK1' defined on 'T1'.
ij> --add a value that does not exits in the parent table
--to make reference indexes are fine.
insert into t2 values(999);
ERROR 23503: INSERT on table 'T2' caused a violation of foreign key constraint 'C1' for key (999).  The statement has been rolled back.
ij> ---REGULAR UNLOGGED CASES , BUT LOGGED WHEN ARCHIVE MODE IS ENABLED.
--compress table 
call SYSCS_UTIL.SYSCS_COMPRESS_TABLE('APP', 'T1', 0);
0 rows inserted/updated/deleted
ij> select * from t1;
A          
-----------
1          
2          
3          
4          
5          
ij> create table t3(c1 int not null);
0 rows inserted/updated/deleted
ij> create table t4(c1 int not null);
0 rows inserted/updated/deleted
ij> --insert
insert into t3 (c1) 
values(1) ,(2) , (3), (4), (5), (6), (7) , (8), (9) , (10) , (11), (12) , (13) , (14) , (15),
(16), (17), (18) , (19) , (20) , (21) , (22) , (23) , (24) , (25) , (26) , (27) , (28) , (29) , (30);
30 rows inserted/updated/deleted
ij> insert into t4 
values(101) ,(102) , (103), (104), (105), (106), (107) , (108), (109) , (110) , (111), (112) , (113), (114),
(115), (116), (117), (118) , (119) , (120) , (121) , (122) , (123) , (124) , (125) , (126) , (127) , (128), (129), (130);
30 rows inserted/updated/deleted
ij> insert into t4 values(1001);
1 row inserted/updated/deleted
ij> alter table t3 add column c2 char(20);
0 rows inserted/updated/deleted
ij> --add constraint
--alter table t3 add column c2 int not null primary key;
--alter table t4 add column c2 int not null;
--alter table t3 add column c3 int not null unique;
connect 'jdbc:derby:wombat;shutdown=true';
ERROR 08006: Database 'wombat' shutdown.
ij> disconnect;
ij> connect 'jdbc:derby:wombat;rollForwardRecoveryFrom=extinout/mybackup/wombat';
ij> select * from t1;
A          
-----------
1