src/third_party/sqlite/src/test/hook.test - cobalt - Git at Google

 # 2004 Jan 14
 #
 # The author disclaims copyright to this source code.  In place of
 # a legal notice, here is a blessing:
 #
 #    May you do good and not evil.
 #    May you find forgiveness for yourself and forgive others.
 #    May you share freely, never taking more than you give.
 #
 #***********************************************************************
 # This file implements regression tests for TCL interface to the
 # SQLite library.
 #
 # The focus of the tests in this file is the  following interface:
 #
 #      sqlite_commit_hook    (tests hook-1..hook-3 inclusive)
 #      sqlite_update_hook    (tests hook-4-*)
 #      sqlite_rollback_hook  (tests hook-5.*)
 #
 # $Id: hook.test,v 1.15 2009/04/07 14:14:23 danielk1977 Exp $

 set testdir [file dirname $argv0]
 source $testdir/tester.tcl

 do_test hook-1.2 {
   db commit_hook
 } {}


 do_test hook-3.1 {
   set commit_cnt 0
   proc commit_hook {} {
     incr ::commit_cnt
     return 0
   }
   db commit_hook ::commit_hook
   db commit_hook
 } {::commit_hook}
 do_test hook-3.2 {
   set commit_cnt
 } {0}
 do_test hook-3.3 {
   execsql {
     CREATE TABLE t2(a,b);
   }
   set commit_cnt
 } {1}
 do_test hook-3.4 {
   execsql {
     INSERT INTO t2 VALUES(1,2);
     INSERT INTO t2 SELECT a+1, b+1 FROM t2;
     INSERT INTO t2 SELECT a+2, b+2 FROM t2;
   }
   set commit_cnt
 } {4}
 do_test hook-3.5 {
   set commit_cnt {}
   proc commit_hook {} {
     set ::commit_cnt [execsql {SELECT * FROM t2}]
     return 0
   }
   execsql {
     INSERT INTO t2 VALUES(5,6);
   }
   set commit_cnt
 } {1 2 2 3 3 4 4 5 5 6}
 do_test hook-3.6 {
   set commit_cnt {}
   proc commit_hook {} {
     set ::commit_cnt [execsql {SELECT * FROM t2}]
     return 1
   }
   catchsql {
     INSERT INTO t2 VALUES(6,7);
   }
 } {1 {constraint failed}}
 do_test hook-3.7 {
   set ::commit_cnt
 } {1 2 2 3 3 4 4 5 5 6 6 7}
 do_test hook-3.8 {
   execsql {SELECT * FROM t2}
 } {1 2 2 3 3 4 4 5 5 6}

 # Test turnning off the commit hook
 #
 do_test hook-3.9 {
   db commit_hook {}
   set ::commit_cnt {}
   execsql {
     INSERT INTO t2 VALUES(7,8);
   }
   set ::commit_cnt
 } {}

 # Ticket #3564.
 #
 do_test hook-3.10 {
   file delete -force test2.db test2.db-journal
   sqlite3 db2 test2.db
   proc commit_hook {} {
     set y [db2 one {SELECT y FROM t3 WHERE y>10}]
     return [expr {$y>10}]
   }
   db2 eval {CREATE TABLE t3(x,y)}
   db2 commit_hook commit_hook
   catchsql {INSERT INTO t3 VALUES(1,2)} db2
   catchsql {INSERT INTO t3 VALUES(11,12)} db2
   catchsql {INSERT INTO t3 VALUES(3,4)} db2
   db2 eval {
     SELECT * FROM t3 ORDER BY x;
   }
 } {1 2 3 4}
 db2 close


 #----------------------------------------------------------------------------
 # Tests for the update-hook.
 #
 # 4.1.* - Very simple tests. Test that the update hook is invoked correctly
 #         for INSERT, DELETE and UPDATE statements, including DELETE
 #         statements with no WHERE clause.
 # 4.2.* - Check that the update-hook is invoked for rows modified by trigger
 #         bodies. Also that the database name is correctly reported when
 #         an attached database is modified.
 # 4.3.* - Do some sorting, grouping, compound queries, population and
 #         depopulation of indices, to make sure the update-hook is not
 #         invoked incorrectly.
 #

 # Simple tests
 do_test hook-4.1.1 {
   catchsql {
     DROP TABLE t1;
   }
   execsql {
     CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
     INSERT INTO t1 VALUES(1, 'one');
     INSERT INTO t1 VALUES(2, 'two');
     INSERT INTO t1 VALUES(3, 'three');
   }
   db update_hook [list lappend ::update_hook]
 } {}
 do_test hook-4.1.2 {
   execsql {
     INSERT INTO t1 VALUES(4, 'four');
     DELETE FROM t1 WHERE b = 'two';
     UPDATE t1 SET b = '' WHERE a = 1 OR a = 3;
     DELETE FROM t1 WHERE 1; -- Avoid the truncate optimization (for now)
   }
   set ::update_hook
 } [list \
     INSERT main t1 4 \
     DELETE main t1 2 \
     UPDATE main t1 1 \
     UPDATE main t1 3 \
     DELETE main t1 1 \
     DELETE main t1 3 \
     DELETE main t1 4 \
 ]

 ifcapable trigger {
   # Update hook is not invoked for changes to sqlite_master
   #
   do_test hook-4.1.3 {
     set ::update_hook {}
     execsql {
       CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN SELECT RAISE(IGNORE); END;
     }
     set ::update_hook
   } {}
   do_test hook-4.1.4 {
     set ::update_hook {}
     execsql {
       DROP TRIGGER r1;
     }
     set ::update_hook
   } {}

   set ::update_hook {}
   do_test hook-4.2.1 {
     catchsql {
       DROP TABLE t2;
     }
     execsql {
       CREATE TABLE t2(c INTEGER PRIMARY KEY, d);
       CREATE TRIGGER t1_trigger AFTER INSERT ON t1 BEGIN
         INSERT INTO t2 VALUES(new.a, new.b);
         UPDATE t2 SET d = d || ' via trigger' WHERE new.a = c;
         DELETE FROM t2 WHERE new.a = c;
       END;
     }
   } {}
   do_test hook-4.2.2 {
     execsql {
       INSERT INTO t1 VALUES(1, 'one');
       INSERT INTO t1 VALUES(2, 'two');
     }
     set ::update_hook
   } [list \
       INSERT main t1 1 \
       INSERT main t2 1 \
       UPDATE main t2 1 \
       DELETE main t2 1 \
       INSERT main t1 2 \
       INSERT main t2 2 \
       UPDATE main t2 2 \
       DELETE main t2 2 \
   ]
 } else {
   execsql {
     INSERT INTO t1 VALUES(1, 'one');
     INSERT INTO t1 VALUES(2, 'two');
   }
 }

 # Update-hook + ATTACH
 set ::update_hook {}
 ifcapable attach {
   do_test hook-4.2.3 {
     file delete -force test2.db
     execsql {
       ATTACH 'test2.db' AS aux;
       CREATE TABLE aux.t3(a INTEGER PRIMARY KEY, b);
       INSERT INTO aux.t3 SELECT * FROM t1;
       UPDATE t3 SET b = 'two or so' WHERE a = 2;
       DELETE FROM t3 WHERE 1; -- Avoid the truncate optimization (for now)
     }
     set ::update_hook
   } [list \
       INSERT aux t3 1 \
       INSERT aux t3 2 \
       UPDATE aux t3 2 \
       DELETE aux t3 1 \
       DELETE aux t3 2 \
   ]
 }

 ifcapable trigger {
   execsql {
     DROP TRIGGER t1_trigger;
   }
 }

 # Test that other vdbe operations involving btree structures do not
 # incorrectly invoke the update-hook.
 set ::update_hook {}
 do_test hook-4.3.1 {
   execsql {
     CREATE INDEX t1_i ON t1(b);
     INSERT INTO t1 VALUES(3, 'three');
     UPDATE t1 SET b = '';
     DELETE FROM t1 WHERE a > 1;
   }
   set ::update_hook
 } [list \
     INSERT main t1 3 \
     UPDATE main t1 1 \
     UPDATE main t1 2 \
     UPDATE main t1 3 \
     DELETE main t1 2 \
     DELETE main t1 3 \
 ]
 set ::update_hook {}
 ifcapable compound&&attach {
   do_test hook-4.3.2 {
     execsql {
       SELECT * FROM t1 UNION SELECT * FROM t3;
       SELECT * FROM t1 UNION ALL SELECT * FROM t3;
       SELECT * FROM t1 INTERSECT SELECT * FROM t3;
       SELECT * FROM t1 EXCEPT SELECT * FROM t3;
       SELECT * FROM t1 ORDER BY b;
       SELECT * FROM t1 GROUP BY b;
     }
     set ::update_hook
   } [list]
 }
 db update_hook {}
 #
 #----------------------------------------------------------------------------

 #----------------------------------------------------------------------------
 # Test the rollback-hook. The rollback-hook is a bit more complicated than
 # either the commit or update hooks because a rollback can happen
 # explicitly (an sql ROLLBACK statement) or implicitly (a constraint or
 # error condition).
 #
 # hook-5.1.* - Test explicit rollbacks.
 # hook-5.2.* - Test implicit rollbacks caused by constraint failure.
 #
 # hook-5.3.* - Test implicit rollbacks caused by IO errors.
 # hook-5.4.* - Test implicit rollbacks caused by malloc() failure.
 # hook-5.5.* - Test hot-journal rollbacks. Or should the rollback hook
 #              not be called for these?
 #

 do_test hook-5.0 {
   # Configure the rollback hook to increment global variable
   # $::rollback_hook each time it is invoked.
   set ::rollback_hook 0
   db rollback_hook [list incr ::rollback_hook]
 } {}

 # Test explicit rollbacks. Not much can really go wrong here.
 #
 do_test hook-5.1.1 {
   set ::rollback_hook 0
   execsql {
     BEGIN;
     ROLLBACK;
   }
   set ::rollback_hook
 } {1}

 # Test implicit rollbacks caused by constraints.
 #
 do_test hook-5.2.1 {
   set ::rollback_hook 0
   catchsql {
     DROP TABLE t1;
     CREATE TABLE t1(a PRIMARY KEY, b);
     INSERT INTO t1 VALUES('one', 'I');
     INSERT INTO t1 VALUES('one', 'I');
   }
   set ::rollback_hook
 } {1}
 do_test hook-5.2.2 {
   # Check that the INSERT transaction above really was rolled back.
   execsql {
     SELECT count(*) FROM t1;
   }
 } {1}

 #
 # End rollback-hook testing.
 #----------------------------------------------------------------------------

 #----------------------------------------------------------------------------
 # Test that if a commit-hook returns non-zero (causing a rollback), the
 # rollback-hook is invoked.
 #
 proc commit_hook {} {
   lappend ::hooks COMMIT
   return 1
 }
 proc rollback_hook {} {
   lappend ::hooks ROLLBACK
 }
 do_test hook-6.1 {
   set ::hooks [list]
   db commit_hook commit_hook
   db rollback_hook rollback_hook
   catchsql {
     BEGIN;
       INSERT INTO t1 VALUES('two', 'II');
     COMMIT;
   }
   execsql { SELECT * FROM t1 }
 } {one I}
 do_test hook-6.2 {
   set ::hooks
 } {COMMIT ROLLBACK}
 unset ::hooks

 finish_test
	# 2004 Jan 14
	#
	# The author disclaims copyright to this source code. In place of
	# a legal notice, here is a blessing:
	#
	# May you do good and not evil.
	# May you find forgiveness for yourself and forgive others.
	# May you share freely, never taking more than you give.
	#
	#***********************************************************************
	# This file implements regression tests for TCL interface to the
	# SQLite library.
	#
	# The focus of the tests in this file is the following interface:
	#
	# sqlite_commit_hook (tests hook-1..hook-3 inclusive)
	# sqlite_update_hook (tests hook-4-*)
	# sqlite_rollback_hook (tests hook-5.*)
	#
	# $Id: hook.test,v 1.15 2009/04/07 14:14:23 danielk1977 Exp $

	set testdir [file dirname $argv0]
	source $testdir/tester.tcl

	do_test hook-1.2 {
	db commit_hook
	} {}


	do_test hook-3.1 {
	set commit_cnt 0
	proc commit_hook {} {
	incr ::commit_cnt
	return 0
	}
	db commit_hook ::commit_hook
	db commit_hook
	} {::commit_hook}
	do_test hook-3.2 {
	set commit_cnt
	} {0}
	do_test hook-3.3 {
	execsql {
	CREATE TABLE t2(a,b);
	}
	set commit_cnt
	} {1}
	do_test hook-3.4 {
	execsql {
	INSERT INTO t2 VALUES(1,2);
	INSERT INTO t2 SELECT a+1, b+1 FROM t2;
	INSERT INTO t2 SELECT a+2, b+2 FROM t2;
	}
	set commit_cnt
	} {4}
	do_test hook-3.5 {
	set commit_cnt {}
	proc commit_hook {} {
	set ::commit_cnt [execsql {SELECT * FROM t2}]
	return 0
	}
	execsql {
	INSERT INTO t2 VALUES(5,6);
	}
	set commit_cnt
	} {1 2 2 3 3 4 4 5 5 6}
	do_test hook-3.6 {
	set commit_cnt {}
	proc commit_hook {} {
	set ::commit_cnt [execsql {SELECT * FROM t2}]
	return 1
	}
	catchsql {
	INSERT INTO t2 VALUES(6,7);
	}
	} {1 {constraint failed}}
	do_test hook-3.7 {
	set ::commit_cnt
	} {1 2 2 3 3 4 4 5 5 6 6 7}
	do_test hook-3.8 {
	execsql {SELECT * FROM t2}
	} {1 2 2 3 3 4 4 5 5 6}

	# Test turnning off the commit hook
	#
	do_test hook-3.9 {
	db commit_hook {}
	set ::commit_cnt {}
	execsql {
	INSERT INTO t2 VALUES(7,8);
	}
	set ::commit_cnt
	} {}

	# Ticket #3564.
	#
	do_test hook-3.10 {
	file delete -force test2.db test2.db-journal
	sqlite3 db2 test2.db
	proc commit_hook {} {
	set y [db2 one {SELECT y FROM t3 WHERE y>10}]
	return [expr {$y>10}]
	}
	db2 eval {CREATE TABLE t3(x,y)}
	db2 commit_hook commit_hook
	catchsql {INSERT INTO t3 VALUES(1,2)} db2
	catchsql {INSERT INTO t3 VALUES(11,12)} db2
	catchsql {INSERT INTO t3 VALUES(3,4)} db2
	db2 eval {
	SELECT * FROM t3 ORDER BY x;
	}
	} {1 2 3 4}
	db2 close


	#----------------------------------------------------------------------------
	# Tests for the update-hook.
	#
	# 4.1.* - Very simple tests. Test that the update hook is invoked correctly
	# for INSERT, DELETE and UPDATE statements, including DELETE
	# statements with no WHERE clause.
	# 4.2.* - Check that the update-hook is invoked for rows modified by trigger
	# bodies. Also that the database name is correctly reported when
	# an attached database is modified.
	# 4.3.* - Do some sorting, grouping, compound queries, population and
	# depopulation of indices, to make sure the update-hook is not
	# invoked incorrectly.
	#

	# Simple tests
	do_test hook-4.1.1 {
	catchsql {
	DROP TABLE t1;
	}
	execsql {
	CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
	INSERT INTO t1 VALUES(1, 'one');
	INSERT INTO t1 VALUES(2, 'two');
	INSERT INTO t1 VALUES(3, 'three');
	}
	db update_hook [list lappend ::update_hook]
	} {}
	do_test hook-4.1.2 {
	execsql {
	INSERT INTO t1 VALUES(4, 'four');
	DELETE FROM t1 WHERE b = 'two';
	UPDATE t1 SET b = '' WHERE a = 1 OR a = 3;
	DELETE FROM t1 WHERE 1; -- Avoid the truncate optimization (for now)
	}
	set ::update_hook
	} [list \
	INSERT main t1 4 \
	DELETE main t1 2 \
	UPDATE main t1 1 \
	UPDATE main t1 3 \
	DELETE main t1 1 \
	DELETE main t1 3 \
	DELETE main t1 4 \
	]

	ifcapable trigger {
	# Update hook is not invoked for changes to sqlite_master
	#
	do_test hook-4.1.3 {
	set ::update_hook {}
	execsql {
	CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN SELECT RAISE(IGNORE); END;
	}
	set ::update_hook
	} {}
	do_test hook-4.1.4 {
	set ::update_hook {}
	execsql {
	DROP TRIGGER r1;
	}
	set ::update_hook
	} {}

	set ::update_hook {}
	do_test hook-4.2.1 {
	catchsql {
	DROP TABLE t2;
	}
	execsql {
	CREATE TABLE t2(c INTEGER PRIMARY KEY, d);
	CREATE TRIGGER t1_trigger AFTER INSERT ON t1 BEGIN
	INSERT INTO t2 VALUES(new.a, new.b);
	UPDATE t2 SET d = d \|\| ' via trigger' WHERE new.a = c;
	DELETE FROM t2 WHERE new.a = c;
	END;
	}
	} {}
	do_test hook-4.2.2 {
	execsql {
	INSERT INTO t1 VALUES(1, 'one');
	INSERT INTO t1 VALUES(2, 'two');
	}
	set ::update_hook
	} [list \
	INSERT main t1 1 \
	INSERT main t2 1 \
	UPDATE main t2 1 \
	DELETE main t2 1 \
	INSERT main t1 2 \
	INSERT main t2 2 \
	UPDATE main t2 2 \
	DELETE main t2 2 \
	]
	} else {
	execsql {
	INSERT INTO t1 VALUES(1, 'one');
	INSERT INTO t1 VALUES(2, 'two');
	}
	}

	# Update-hook + ATTACH
	set ::update_hook {}
	ifcapable attach {
	do_test hook-4.2.3 {
	file delete -force test2.db
	execsql {
	ATTACH 'test2.db' AS aux;
	CREATE TABLE aux.t3(a INTEGER PRIMARY KEY, b);
	INSERT INTO aux.t3 SELECT * FROM t1;
	UPDATE t3 SET b = 'two or so' WHERE a = 2;
	DELETE FROM t3 WHERE 1; -- Avoid the truncate optimization (for now)
	}
	set ::update_hook
	} [list \
	INSERT aux t3 1 \
	INSERT aux t3 2 \
	UPDATE aux t3 2 \
	DELETE aux t3 1 \
	DELETE aux t3 2 \
	]
	}

	ifcapable trigger {
	execsql {
	DROP TRIGGER t1_trigger;
	}
	}

	# Test that other vdbe operations involving btree structures do not
	# incorrectly invoke the update-hook.
	set ::update_hook {}
	do_test hook-4.3.1 {
	execsql {
	CREATE INDEX t1_i ON t1(b);
	INSERT INTO t1 VALUES(3, 'three');
	UPDATE t1 SET b = '';
	DELETE FROM t1 WHERE a > 1;
	}
	set ::update_hook
	} [list \
	INSERT main t1 3 \
	UPDATE main t1 1 \
	UPDATE main t1 2 \
	UPDATE main t1 3 \
	DELETE main t1 2 \
	DELETE main t1 3 \
	]
	set ::update_hook {}
	ifcapable compound&&attach {
	do_test hook-4.3.2 {
	execsql {
	SELECT * FROM t1 UNION SELECT * FROM t3;
	SELECT * FROM t1 UNION ALL SELECT * FROM t3;
	SELECT * FROM t1 INTERSECT SELECT * FROM t3;
	SELECT * FROM t1 EXCEPT SELECT * FROM t3;
	SELECT * FROM t1 ORDER BY b;
	SELECT * FROM t1 GROUP BY b;
	}
	set ::update_hook
	} [list]
	}
	db update_hook {}
	#
	#----------------------------------------------------------------------------

	#----------------------------------------------------------------------------
	# Test the rollback-hook. The rollback-hook is a bit more complicated than
	# either the commit or update hooks because a rollback can happen
	# explicitly (an sql ROLLBACK statement) or implicitly (a constraint or
	# error condition).
	#
	# hook-5.1.* - Test explicit rollbacks.
	# hook-5.2.* - Test implicit rollbacks caused by constraint failure.
	#
	# hook-5.3.* - Test implicit rollbacks caused by IO errors.
	# hook-5.4.* - Test implicit rollbacks caused by malloc() failure.
	# hook-5.5.* - Test hot-journal rollbacks. Or should the rollback hook
	# not be called for these?
	#

	do_test hook-5.0 {
	# Configure the rollback hook to increment global variable
	# $::rollback_hook each time it is invoked.
	set ::rollback_hook 0
	db rollback_hook [list incr ::rollback_hook]
	} {}

	# Test explicit rollbacks. Not much can really go wrong here.
	#
	do_test hook-5.1.1 {
	set ::rollback_hook 0
	execsql {
	BEGIN;
	ROLLBACK;
	}
	set ::rollback_hook
	} {1}

	# Test implicit rollbacks caused by constraints.
	#
	do_test hook-5.2.1 {
	set ::rollback_hook 0
	catchsql {
	DROP TABLE t1;
	CREATE TABLE t1(a PRIMARY KEY, b);
	INSERT INTO t1 VALUES('one', 'I');
	INSERT INTO t1 VALUES('one', 'I');
	}
	set ::rollback_hook
	} {1}
	do_test hook-5.2.2 {
	# Check that the INSERT transaction above really was rolled back.
	execsql {
	SELECT count(*) FROM t1;
	}
	} {1}

	#
	# End rollback-hook testing.
	#----------------------------------------------------------------------------

	#----------------------------------------------------------------------------
	# Test that if a commit-hook returns non-zero (causing a rollback), the
	# rollback-hook is invoked.
	#
	proc commit_hook {} {
	lappend ::hooks COMMIT
	return 1
	}
	proc rollback_hook {} {
	lappend ::hooks ROLLBACK
	}
	do_test hook-6.1 {
	set ::hooks [list]
	db commit_hook commit_hook
	db rollback_hook rollback_hook
	catchsql {
	BEGIN;
	INSERT INTO t1 VALUES('two', 'II');
	COMMIT;
	}
	execsql { SELECT * FROM t1 }
	} {one I}
	do_test hook-6.2 {
	set ::hooks
	} {COMMIT ROLLBACK}
	unset ::hooks

	finish_test