sqlite/test/sqldiff1.test

# 2015-05-11
#
# 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.
#
#***********************************************************************
#
# Quick tests for the sqldiff tool
#
set testdir [file dirname $argv0]
source $testdir/tester.tcl

set PROG [test_find_sqldiff]

db close
forcedelete test.db test2.db
sqlite3 db test.db

do_test sqldiff-1.0 {
  db eval {
    CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
    CREATE TABLE t2(a INT PRIMARY KEY, b) WITHOUT ROWID;
    WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<100)
    INSERT INTO t1(a,b) SELECT x, printf('abc-%d-xyz',x) FROM c;
    INSERT INTO t2(a,b) SELECT a, b FROM t1;
  }
  db backup test2.db
  db eval {
    ATTACH 'test2.db' AS x2;
    DELETE FROM x2.t1 WHERE a=49;
    DELETE FROM x2.t2 WHERE a=48;
    INSERT INTO x2.t1(a,b) VALUES(1234,'hello');
    INSERT INTO x2.t2(a,b) VALUES(50.5,'xyzzy');
    INSERT INTO x2.t2(a,b) VALUES(51.5,'');
    INSERT INTO x2.t2(a,b) VALUES(52.5,''||X'0d0a');
    INSERT INTO x2.t2(a,b) VALUES(53.5,'one'||X'0a0d');
    INSERT INTO x2.t2(a,b) VALUES(54.5,'one'||X'0a'||'two');
    CREATE TABLE x2.t3(a,b,c);
    INSERT INTO x2.t3 VALUES(111,222,333);
    CREATE TABLE main.t4(x,y,z);
    INSERT INTO t4 SELECT * FROM t3;
  }
  set line "exec $PROG test.db test2.db"
  unset -nocomplain ::MSG
  catch {eval $line} ::MSG
} {0}
do_test sqldiff-1.1 {
  set ::MSG
} {DELETE FROM t1 WHERE a=49;
INSERT INTO t1(a,b) VALUES(1234,'hello');
DELETE FROM t2 WHERE a=48;
INSERT INTO t2(a,b) VALUES(50.5,'xyzzy');
INSERT INTO t2(a,b) VALUES(51.5,'');
INSERT INTO t2(a,b) VALUES(52.5,''||X'0d0a');
INSERT INTO t2(a,b) VALUES(53.5,'one'||X'0a0d');
INSERT INTO t2(a,b) VALUES(54.5,'one'||X'0a'
||'two');
CREATE TABLE t3(a,b,c);
INSERT INTO t3(rowid,a,b,c) VALUES(1,111,222,333);
DROP TABLE t4;}

db close
forcedelete test.db test2.db
sqlite3 db test.db

do_test sqldiff-2.0 {
  db eval {
    CREATE TABLE t1(a INTEGER PRIMARY KEY);
  }
  db close
  sqlite3 db test2.db
  db eval {
    CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
  }
  db close
  set line "exec $PROG test.db test2.db"
  unset -nocomplain ::MSG
  catch {eval $line} ::MSG
} {0}
do_test sqldiff-2.1 {
  set ::MSG
} {ALTER TABLE t1 ADD COLUMN b;}

# Bug 2026-10-18T20:38:30Z
#
proc sqldiff_apply_test {tn} {
  set patch [exec $::PROG test.db test2.db]
  sqlite3 db test.db
  set rc [catch {db eval $patch} msg]
  db close
  if {$rc} {
    puts "PATCH: [list $patch]"
  }
  do_test $tn.1 [list list $rc $msg] {0 {}}
  do_test $tn.2 {exec $::PROG test.db test2.db} {}
}

forcedelete test.db test2.db
sqlite3 db test.db
do_execsql_test sqldiff-3.0 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b REAL);
}
db backup test2.db
db close
sqlite3 db test2.db
do_execsql_test sqldiff-3.1 {
  INSERT INTO t1 VALUES(1, 0.1+0.2);
  INSERT INTO t1 VALUES(2, 1.0/3.0);
  INSERT INTO t1 VALUES(3, 1.7976931348623157e308);
  INSERT INTO t1 VALUES(4, -1.7976931348623157e308);
  INSERT INTO t1 VALUES(5, 4.9406564584124654e-324);
  INSERT INTO t1 VALUES(6, 123456789.12345678);
}
db close
sqldiff_apply_test sqldiff-3.2
sqlite3 db test.db
do_execsql_test sqldiff-3.3 {
  ATTACH 'test2.db' AS x2;
  SELECT a FROM t1 WHERE b IS NOT (SELECT b FROM x2.t1 AS y WHERE y.a=t1.a);
} {}
db close

forcedelete test.db test2.db
sqlite3 db test.db
do_execsql_test sqldiff-4.0 {
  CREATE TABLE t1(a INTEGER PRIMARY KEY, b REAL);
}
db backup test2.db
db close
sqlite3 db test2.db
do_execsql_test sqldiff-4.1 {
  INSERT INTO t1 VALUES(1, 9e999);
  INSERT INTO t1 VALUES(2, -9e999);
}
db close
sqldiff_apply_test sqldiff-4.2
sqlite3 db test.db
do_execsql_test sqldiff-4.3 {
  SELECT a, b>1e308, b<-1e308 FROM t1 ORDER BY a;
} {1 1 0 2 0 1}
db close

# The following test cases demonstrate that if the source table contains
# an AUTOINCREMENT table but there is no corresponding entry in the
# sqlite_sequence table, then the sqlite_sequence table is not updated
# correctly.  This is a long-standing deficiency in sqldiff, though the
# issue is contrived and does not appear in practice.  If the issue is
# ever fixed, then simply uncomment the following test sequence to verify
# the fix.
#
if 0 {
  forcedelete test.db test2.db
  sqlite3 db test.db
  do_execsql_test sqldiff-5.0 {
    CREATE TABLE a1(id INTEGER PRIMARY KEY AUTOINCREMENT, x);
  }
  db backup test2.db
  db close
  sqlite3 db test2.db
  do_execsql_test sqldiff-5.1 {
    INSERT INTO a1(x) VALUES('one'),('two'),('three');
    DELETE FROM a1 WHERE id=3;
    SELECT seq FROM sqlite_sequence WHERE name='a1';
  } {3}
  db close
  sqldiff_apply_test sqldiff-5.2
  sqlite3 db test.db
  do_execsql_test sqldiff-5.3 {
    SELECT seq FROM sqlite_sequence WHERE name='a1';
  } {3}
  do_execsql_test sqldiff-5.4 {
    INSERT INTO a1(x) VALUES('four');
    SELECT id FROM a1 WHERE x='four';
  } {4}
}

finish_test