attach3.test 7.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354
  1. # 2003 July 1
  2. #
  3. # The author disclaims copyright to this source code. In place of
  4. # a legal notice, here is a blessing:
  5. #
  6. # May you do good and not evil.
  7. # May you find forgiveness for yourself and forgive others.
  8. # May you share freely, never taking more than you give.
  9. #
  10. #***********************************************************************
  11. # This file implements regression tests for SQLite library. The
  12. # focus of this script is testing the ATTACH and DETACH commands
  13. # and schema changes to attached databases.
  14. #
  15. # $Id: attach3.test,v 1.18 2007/10/09 08:29:32 danielk1977 Exp $
  16. #
  17. set testdir [file dirname $argv0]
  18. source $testdir/tester.tcl
  19. ifcapable !attach {
  20. finish_test
  21. return
  22. }
  23. # The tests in this file were written before SQLite supported recursive
  24. # trigger invocation, and some tests depend on that to pass. So disable
  25. # recursive triggers for this file.
  26. catchsql { pragma recursive_triggers = off }
  27. # Create tables t1 and t2 in the main database
  28. execsql {
  29. CREATE TABLE t1(a, b);
  30. CREATE TABLE t2(c, d);
  31. }
  32. # Create tables t1 and t2 in database file test2.db
  33. forcedelete test2.db
  34. forcedelete test2.db-journal
  35. sqlite3 db2 test2.db
  36. execsql {
  37. CREATE TABLE t1(a, b);
  38. CREATE TABLE t2(c, d);
  39. } db2
  40. db2 close
  41. # Create a table in the auxilary database.
  42. do_test attach3-1.1 {
  43. execsql {
  44. ATTACH 'test2.db' AS aux;
  45. }
  46. } {}
  47. do_test attach3-1.2 {
  48. execsql {
  49. CREATE TABLE aux.t3(e, f);
  50. }
  51. } {}
  52. do_test attach3-1.3 {
  53. execsql {
  54. SELECT * FROM sqlite_master WHERE name = 't3';
  55. }
  56. } {}
  57. do_test attach3-1.4 {
  58. execsql {
  59. SELECT * FROM aux.sqlite_master WHERE name = 't3';
  60. }
  61. } "table t3 t3 [expr $AUTOVACUUM?5:4] {CREATE TABLE t3(e, f)}"
  62. do_test attach3-1.5 {
  63. execsql {
  64. INSERT INTO t3 VALUES(1, 2);
  65. SELECT * FROM t3;
  66. }
  67. } {1 2}
  68. # Create an index on the auxilary database table.
  69. do_test attach3-2.1 {
  70. execsql {
  71. CREATE INDEX aux.i1 on t3(e);
  72. }
  73. } {}
  74. do_test attach3-2.2 {
  75. execsql {
  76. SELECT * FROM sqlite_master WHERE name = 'i1';
  77. }
  78. } {}
  79. do_test attach3-2.3 {
  80. execsql {
  81. SELECT * FROM aux.sqlite_master WHERE name = 'i1';
  82. }
  83. } "index i1 t3 [expr $AUTOVACUUM?6:5] {CREATE INDEX i1 on t3(e)}"
  84. # Drop the index on the aux database table.
  85. do_test attach3-3.1 {
  86. execsql {
  87. DROP INDEX aux.i1;
  88. SELECT * FROM aux.sqlite_master WHERE name = 'i1';
  89. }
  90. } {}
  91. do_test attach3-3.2 {
  92. execsql {
  93. CREATE INDEX aux.i1 on t3(e);
  94. SELECT * FROM aux.sqlite_master WHERE name = 'i1';
  95. }
  96. } "index i1 t3 [expr $AUTOVACUUM?6:5] {CREATE INDEX i1 on t3(e)}"
  97. do_test attach3-3.3 {
  98. execsql {
  99. DROP INDEX i1;
  100. SELECT * FROM aux.sqlite_master WHERE name = 'i1';
  101. }
  102. } {}
  103. # Drop tables t1 and t2 in the auxilary database.
  104. do_test attach3-4.1 {
  105. execsql {
  106. DROP TABLE aux.t1;
  107. SELECT name FROM aux.sqlite_master;
  108. }
  109. } {t2 t3}
  110. do_test attach3-4.2 {
  111. # This will drop main.t2
  112. execsql {
  113. DROP TABLE t2;
  114. SELECT name FROM aux.sqlite_master;
  115. }
  116. } {t2 t3}
  117. do_test attach3-4.3 {
  118. execsql {
  119. DROP TABLE t2;
  120. SELECT name FROM aux.sqlite_master;
  121. }
  122. } {t3}
  123. # Create a view in the auxilary database.
  124. ifcapable view {
  125. do_test attach3-5.1 {
  126. execsql {
  127. CREATE VIEW aux.v1 AS SELECT * FROM t3;
  128. }
  129. } {}
  130. do_test attach3-5.2 {
  131. execsql {
  132. SELECT * FROM aux.sqlite_master WHERE name = 'v1';
  133. }
  134. } {view v1 v1 0 {CREATE VIEW v1 AS SELECT * FROM t3}}
  135. do_test attach3-5.3 {
  136. execsql {
  137. INSERT INTO aux.t3 VALUES('hello', 'world');
  138. SELECT * FROM v1;
  139. }
  140. } {1 2 hello world}
  141. # Drop the view
  142. do_test attach3-6.1 {
  143. execsql {
  144. DROP VIEW aux.v1;
  145. }
  146. } {}
  147. do_test attach3-6.2 {
  148. execsql {
  149. SELECT * FROM aux.sqlite_master WHERE name = 'v1';
  150. }
  151. } {}
  152. } ;# ifcapable view
  153. ifcapable {trigger} {
  154. # Create a trigger in the auxilary database.
  155. do_test attach3-7.1 {
  156. execsql {
  157. CREATE TRIGGER aux.tr1 AFTER INSERT ON t3 BEGIN
  158. INSERT INTO t3 VALUES(new.e*2, new.f*2);
  159. END;
  160. }
  161. } {}
  162. do_test attach3-7.2 {
  163. execsql {
  164. DELETE FROM t3;
  165. INSERT INTO t3 VALUES(10, 20);
  166. SELECT * FROM t3;
  167. }
  168. } {10 20 20 40}
  169. do_test attach3-5.3 {
  170. execsql {
  171. SELECT * FROM aux.sqlite_master WHERE name = 'tr1';
  172. }
  173. } {trigger tr1 t3 0 {CREATE TRIGGER tr1 AFTER INSERT ON t3 BEGIN
  174. INSERT INTO t3 VALUES(new.e*2, new.f*2);
  175. END}}
  176. # Drop the trigger
  177. do_test attach3-8.1 {
  178. execsql {
  179. DROP TRIGGER aux.tr1;
  180. }
  181. } {}
  182. do_test attach3-8.2 {
  183. execsql {
  184. SELECT * FROM aux.sqlite_master WHERE name = 'tr1';
  185. }
  186. } {}
  187. ifcapable tempdb {
  188. # Try to trick SQLite into dropping the wrong temp trigger.
  189. do_test attach3-9.0 {
  190. execsql {
  191. CREATE TABLE main.t4(a, b, c);
  192. CREATE TABLE aux.t4(a, b, c);
  193. CREATE TEMP TRIGGER tst_trigger BEFORE INSERT ON aux.t4 BEGIN
  194. SELECT 'hello world';
  195. END;
  196. SELECT count(*) FROM sqlite_temp_master;
  197. }
  198. } {1}
  199. do_test attach3-9.1 {
  200. execsql {
  201. DROP TABLE main.t4;
  202. SELECT count(*) FROM sqlite_temp_master;
  203. }
  204. } {1}
  205. do_test attach3-9.2 {
  206. execsql {
  207. DROP TABLE aux.t4;
  208. SELECT count(*) FROM sqlite_temp_master;
  209. }
  210. } {0}
  211. }
  212. } ;# endif trigger
  213. # Make sure the aux.sqlite_master table is read-only
  214. do_test attach3-10.0 {
  215. catchsql {
  216. INSERT INTO aux.sqlite_master VALUES(1, 2, 3, 4, 5);
  217. }
  218. } {1 {table sqlite_master may not be modified}}
  219. # Failure to attach leaves us in a workable state.
  220. # Ticket #811
  221. #
  222. do_test attach3-11.0 {
  223. catchsql {
  224. ATTACH DATABASE '/nodir/nofile.x' AS notadb;
  225. }
  226. } {1 {unable to open database: /nodir/nofile.x}}
  227. do_test attach3-11.1 {
  228. catchsql {
  229. ATTACH DATABASE ':memory:' AS notadb;
  230. }
  231. } {0 {}}
  232. do_test attach3-11.2 {
  233. catchsql {
  234. DETACH DATABASE notadb;
  235. }
  236. } {0 {}}
  237. # Return a list of attached databases
  238. #
  239. proc db_list {} {
  240. set x [execsql {
  241. PRAGMA database_list;
  242. }]
  243. set y {}
  244. foreach {n id file} $x {lappend y $id}
  245. return $y
  246. }
  247. ifcapable schema_pragmas&&tempdb {
  248. ifcapable !trigger {
  249. execsql {create temp table dummy(dummy)}
  250. }
  251. # Ticket #1825
  252. #
  253. do_test attach3-12.1 {
  254. db_list
  255. } {main temp aux}
  256. do_test attach3-12.2 {
  257. execsql {
  258. ATTACH DATABASE ? AS ?
  259. }
  260. db_list
  261. } {main temp aux {}}
  262. do_test attach3-12.3 {
  263. execsql {
  264. DETACH aux
  265. }
  266. db_list
  267. } {main temp {}}
  268. do_test attach3-12.4 {
  269. execsql {
  270. DETACH ?
  271. }
  272. db_list
  273. } {main temp}
  274. do_test attach3-12.5 {
  275. execsql {
  276. ATTACH DATABASE '' AS ''
  277. }
  278. db_list
  279. } {main temp {}}
  280. do_test attach3-12.6 {
  281. execsql {
  282. DETACH ''
  283. }
  284. db_list
  285. } {main temp}
  286. do_test attach3-12.7 {
  287. execsql {
  288. ATTACH DATABASE '' AS ?
  289. }
  290. db_list
  291. } {main temp {}}
  292. do_test attach3-12.8 {
  293. execsql {
  294. DETACH ''
  295. }
  296. db_list
  297. } {main temp}
  298. do_test attach3-12.9 {
  299. execsql {
  300. ATTACH DATABASE '' AS NULL
  301. }
  302. db_list
  303. } {main temp {}}
  304. do_test attach3-12.10 {
  305. breakpoint
  306. execsql {
  307. DETACH ?
  308. }
  309. db_list
  310. } {main temp}
  311. do_test attach3-12.11 {
  312. catchsql {
  313. DETACH NULL
  314. }
  315. } {1 {no such database: }}
  316. do_test attach3-12.12 {
  317. catchsql {
  318. ATTACH null AS null;
  319. ATTACH '' AS '';
  320. }
  321. } {1 {database is already in use}}
  322. do_test attach3-12.13 {
  323. db_list
  324. } {main temp {}}
  325. do_test attach3-12.14 {
  326. execsql {
  327. DETACH '';
  328. }
  329. db_list
  330. } {main temp}
  331. } ;# ifcapable pragma
  332. finish_test