/trunk/src/sqlite/test/attach3.test

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