attach.test

sqlite-3.4.1,嵌入式数据库.是一个功能强大的开源数据库,给学习和研发以及小型公司的发展带来了全所未有的好处.

} {1 {no such table: t2}}
do_test attach-3.3 {
  catchsql {
    ATTACH DATABASE 'test2.db' AS db2;
    SELECT * FROM t2
  }
} {0 {21 x 22 y}}

# Even though 'db' has started a transaction, it should not yet have
# a lock on test2.db so 'db2' should be readable.
do_test attach-3.4 {
  execsql BEGIN
  catchsql {
    SELECT * FROM t2;
  } db2;
} {0 {21 x 22 y}}

# Reading from test2.db from db within a transaction should not
# prevent test2.db from being read by db2.
do_test attach-3.5 {
  execsql {SELECT * FROM t2}
btree_breakpoint
  catchsql {
    SELECT * FROM t2;
  } db2;
} {0 {21 x 22 y}}

# Making a change to test2.db through db  causes test2.db to get
# a reserved lock.  It should still be accessible through db2.
do_test attach-3.6 {
  execsql {
    UPDATE t2 SET x=x+1 WHERE x=50;
  }
  catchsql {
    SELECT * FROM t2;
  } db2;
} {0 {21 x 22 y}}

do_test attach-3.7 {
  execsql ROLLBACK
  execsql {SELECT * FROM t2} db2
} {21 x 22 y}

# Start transactions on both db and db2.  Once again, just because
# we make a change to test2.db using db2, only a RESERVED lock is
# obtained, so test2.db should still be readable using db.
#
do_test attach-3.8 {
  execsql BEGIN
  execsql BEGIN db2
  execsql {UPDATE t2 SET x=0 WHERE 0} db2
  catchsql {SELECT * FROM t2}
} {0 {21 x 22 y}}

# It is also still accessible from db2.
do_test attach-3.9 {
  catchsql {SELECT * FROM t2} db2
} {0 {21 x 22 y}}

do_test attach-3.10 {
  execsql {SELECT * FROM t1}
} {1 2 3 4}

do_test attach-3.11 {
  catchsql {UPDATE t1 SET a=a+1}
} {0 {}}
do_test attach-3.12 {
  execsql {SELECT * FROM t1}
} {2 2 4 4}

# db2 has a RESERVED lock on test2.db, so db cannot write to any tables
# in test2.db.
do_test attach-3.13 {
  catchsql {UPDATE t2 SET x=x+1 WHERE x=50}
} {1 {database is locked}}

# Change for version 3. Transaction is no longer rolled back
# for a locked database.
execsql {ROLLBACK}

# db is able to reread its schema because db2 still only holds a
# reserved lock.
do_test attach-3.14 {
  catchsql {SELECT * FROM t1}
} {0 {1 2 3 4}}
do_test attach-3.15 {
  execsql COMMIT db2
  execsql {SELECT * FROM t1}
} {1 2 3 4}

#set btree_trace 1

# Ticket #323
do_test attach-4.1 {
  execsql {DETACH db2}
  db2 close
  sqlite3 db2 test2.db
  execsql {
    CREATE TABLE t3(x,y);
    CREATE UNIQUE INDEX t3i1 ON t3(x);
    INSERT INTO t3 VALUES(1,2);
    SELECT * FROM t3;
  } db2;
} {1 2}
do_test attach-4.2 {
  execsql {
    CREATE TABLE t3(a,b);
    CREATE UNIQUE INDEX t3i1b ON t3(a);
    INSERT INTO t3 VALUES(9,10);
    SELECT * FROM t3;
  }
} {9 10}
do_test attach-4.3 {
  execsql {
    ATTACH DATABASE 'test2.db' AS db2;
    SELECT * FROM db2.t3;
  }
} {1 2}
do_test attach-4.4 {
  execsql {
    SELECT * FROM main.t3;
  }
} {9 10}
do_test attach-4.5 {
  execsql {
    INSERT INTO db2.t3 VALUES(9,10);
    SELECT * FROM db2.t3;
  }
} {1 2 9 10}
execsql {
  DETACH db2;
}
ifcapable {trigger} {
  do_test attach-4.6 {
    execsql {
      CREATE TABLE t4(x);
      CREATE TRIGGER t3r3 AFTER INSERT ON t3 BEGIN
        INSERT INTO t4 VALUES('db2.' || NEW.x);
      END;
      INSERT INTO t3 VALUES(6,7);
      SELECT * FROM t4;
    } db2
  } {db2.6}
  do_test attach-4.7 {
    execsql {
      CREATE TABLE t4(y);
      CREATE TRIGGER t3r3 AFTER INSERT ON t3 BEGIN
        INSERT INTO t4 VALUES('main.' || NEW.a);
      END;
      INSERT INTO main.t3 VALUES(11,12);
      SELECT * FROM main.t4;
    }
  } {main.11}
}
ifcapable {!trigger} {
  # When we do not have trigger support, set up the table like they
  # would have been had triggers been there.  The tests that follow need
  # this setup.
  execsql {
    CREATE TABLE t4(x);
    INSERT INTO t3 VALUES(6,7);
    INSERT INTO t4 VALUES('db2.6');
    INSERT INTO t4 VALUES('db2.13');
  } db2
  execsql {
    CREATE TABLE t4(y);
    INSERT INTO main.t3 VALUES(11,12);
    INSERT INTO t4 VALUES('main.11');
  }
}


# This one is tricky.  On the UNION ALL select, we have to make sure
# the schema for both main and db2 is valid before starting to execute
# the first query of the UNION ALL.  If we wait to test the validity of
# the schema for main until after the first query has run, that test will
# fail and the query will abort but we will have already output some
# results.  When the query is retried, the results will be repeated.
#
ifcapable compound {
do_test attach-4.8 {
  execsql {
    ATTACH DATABASE 'test2.db' AS db2;
    INSERT INTO db2.t3 VALUES(13,14);
    SELECT * FROM db2.t4 UNION ALL SELECT * FROM main.t4;
  }
} {db2.6 db2.13 main.11}

do_test attach-4.9 {
  ifcapable {!trigger} {execsql {INSERT INTO main.t4 VALUES('main.15')}}
  execsql {
    INSERT INTO main.t3 VALUES(15,16);
    SELECT * FROM db2.t4 UNION ALL SELECT * FROM main.t4;
  }
} {db2.6 db2.13 main.11 main.15}
} ;# ifcapable compound

ifcapable !compound {
  ifcapable {!trigger} {execsql {INSERT INTO main.t4 VALUES('main.15')}}
  execsql {
    ATTACH DATABASE 'test2.db' AS db2;
    INSERT INTO db2.t3 VALUES(13,14);
    INSERT INTO main.t3 VALUES(15,16);
  } 
} ;# ifcapable !compound

ifcapable view {
do_test attach-4.10 {
  execsql {
    DETACH DATABASE db2;
  }
  execsql {
    CREATE VIEW v3 AS SELECT x*100+y FROM t3;
    SELECT * FROM v3;
  } db2
} {102 910 607 1314}
do_test attach-4.11 {
  execsql {
    CREATE VIEW v3 AS SELECT a*100+b FROM t3;
    SELECT * FROM v3;
  }
} {910 1112 1516}
do_test attach-4.12 {
  execsql {
    ATTACH DATABASE 'test2.db' AS db2;
    SELECT * FROM db2.v3;
  }
} {102 910 607 1314}
do_test attach-4.13 {
  execsql {
    SELECT * FROM main.v3;
  }
} {910 1112 1516}
} ;# ifcapable view

# Tests for the sqliteFix...() routines in attach.c
#
ifcapable {trigger} {
do_test attach-5.1 {
  db close
  sqlite3 db test.db
  db2 close
  file delete -force test2.db
  sqlite3 db2 test2.db
  catchsql {
    ATTACH DATABASE 'test.db' AS orig;
    CREATE TRIGGER r1 AFTER INSERT ON orig.t1 BEGIN
      SELECT 'no-op';
    END;
  } db2
} {1 {trigger r1 cannot reference objects in database orig}}
do_test attach-5.2 {
  catchsql {
    CREATE TABLE t5(x,y);
    CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
      SELECT 'no-op';
    END;
  } db2
} {0 {}}
do_test attach-5.3 {
  catchsql {
    DROP TRIGGER r5;
    CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
      SELECT 'no-op' FROM orig.t1;
    END;
  } db2
} {1 {trigger r5 cannot reference objects in database orig}}
ifcapable tempdb {
  do_test attach-5.4 {
    catchsql {
      CREATE TEMP TABLE t6(p,q,r);
      CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
        SELECT 'no-op' FROM temp.t6;
      END;
    } db2
  } {1 {trigger r5 cannot reference objects in database temp}}
}
ifcapable subquery {
  do_test attach-5.5 {
    catchsql {
      CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
        SELECT 'no-op' || (SELECT * FROM temp.t6);
      END;
    } db2
  } {1 {trigger r5 cannot reference objects in database temp}}
  do_test attach-5.6 {
    catchsql {
      CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
        SELECT 'no-op' FROM t1 WHERE x<(SELECT min(x) FROM temp.t6);
      END;
    } db2
  } {1 {trigger r5 cannot reference objects in database temp}}
  do_test attach-5.7 {
    catchsql {
      CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
        SELECT 'no-op' FROM t1 GROUP BY 1 HAVING x<(SELECT min(x) FROM temp.t6);
      END;
    } db2
  } {1 {trigger r5 cannot reference objects in database temp}}
  do_test attach-5.7 {
    catchsql {
      CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
        SELECT max(1,x,(SELECT min(x) FROM temp.t6)) FROM t1;
      END;
    } db2
  } {1 {trigger r5 cannot reference objects in database temp}}
  do_test attach-5.8 {
    catchsql {
      CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
        INSERT INTO t1 VALUES((SELECT min(x) FROM temp.t6),5);
      END;
    } db2
  } {1 {trigger r5 cannot reference objects in database temp}}
  do_test attach-5.9 {
    catchsql {
      CREATE TRIGGER r5 AFTER INSERT ON t5 BEGIN
        DELETE FROM t1 WHERE x<(SELECT min(x) FROM temp.t6);
      END;
    } db2
  } {1 {trigger r5 cannot reference objects in database temp}}
} ;# endif subquery
} ;# endif trigger

# Check to make sure we get a sensible error if unable to open
# the file that we are trying to attach.
#
do_test attach-6.1 {
  catchsql {
    ATTACH DATABASE 'no-such-file' AS nosuch;
  }
} {0 {}}
if {$tcl_platform(platform)=="unix"} {
  do_test attach-6.2 {
    sqlite3 dbx cannot-read
    dbx eval {CREATE TABLE t1(a,b,c)}
    dbx close
    file attributes cannot-read -permission 0000
    if {[file writable cannot-read]} {
      puts "\n**** Tests do not work when run as root ****"
      file delete -force cannot-read
      exit 1
    }
    catchsql {
      ATTACH DATABASE 'cannot-read' AS noread;
    }
  } {1 {unable to open database: cannot-read}}
  file delete -force cannot-read
}

# Check the error message if we try to access a database that has
# not been attached.
do_test attach-6.3 {
  catchsql {
    CREATE TABLE no_such_db.t1(a, b, c);
  }
} {1 {unknown database no_such_db}}
for {set i 2} {$i<=15} {incr i} {
  catch {db$i close}
}
db close
file delete -force test2.db
file delete -force no-such-file

do_test attach-7.1 {
  file delete -force test.db test.db-journal
  sqlite3 db test.db
  catchsql {
    DETACH RAISE ( IGNORE ) IN ( SELECT "AAAAAA" . * ORDER BY 
    REGISTER LIMIT "AAAAAA" . "AAAAAA" OFFSET RAISE ( IGNORE ) NOT NULL )
  }
} {1 {invalid name: "RAISE ( IGNORE ) IN ( SELECT "AAAAAA" . * ORDER BY 
    REGISTER LIMIT "AAAAAA" . "AAAAAA" OFFSET RAISE ( IGNORE ) NOT NULL )"}}
finish_test