github.com/tailscale/sqlite@v0.0.0-20240515181108-c667cbe57c66/util_test.go (about) 1 package sqlite 2 3 import ( 4 "context" 5 "testing" 6 ) 7 8 func TestDropAll(t *testing.T) { 9 ctx := context.Background() 10 db := openTestDB(t) 11 conn, err := db.Conn(ctx) 12 if err != nil { 13 t.Fatal(err) 14 } 15 defer conn.Close() 16 17 err = ExecScript(conn, ` 18 ATTACH 'file:s1?mode=memory' AS "db two"; 19 BEGIN; 20 CREATE TABLE "db two".customer ( 21 cust_id INTEGER PRIMARY KEY, 22 cust_name TEXT, 23 cust_addr TEXT 24 ); 25 CREATE INDEX "db two".custname ON customer (cust_name); 26 CREATE VIEW "db two".customer_address AS 27 SELECT cust_id, cust_addr FROM "db two".customer; 28 CREATE TRIGGER "db two".cust_addr_chng 29 INSTEAD OF UPDATE OF cust_addr ON "db two".customer_address 30 BEGIN 31 UPDATE customer SET cust_addr=NEW.cust_addr 32 WHERE cust_id=NEW.cust_id; 33 END; 34 35 -- Creates an auto-index we cannot delete. 36 CREATE TABLE "db two".textkey (key TEXT PRIMARY KEY, val INTEGER); 37 38 CREATE TABLE customer ( 39 cust_id INTEGER PRIMARY KEY, 40 cust_name TEXT, 41 cust_addr TEXT 42 ); 43 CREATE INDEX custname ON customer (cust_name); 44 CREATE VIEW customer_address AS 45 SELECT cust_id, cust_addr FROM customer; 46 CREATE TRIGGER cust_addr_chng 47 INSTEAD OF UPDATE OF cust_addr ON customer_address 48 BEGIN 49 UPDATE customer SET cust_addr=NEW.cust_addr 50 WHERE cust_id=NEW.cust_id; 51 END; 52 53 COMMIT;`) 54 if err != nil { 55 t.Fatal(err) 56 } 57 58 if err := DropAll(ctx, conn, "db two"); err != nil { 59 t.Fatal(err) 60 } 61 var count int 62 if err := conn.QueryRowContext(ctx, "SELECT count(*) FROM \"db two\".sqlite_schema").Scan(&count); err != nil { 63 t.Fatal(err) 64 } 65 if count != 0 { 66 t.Fatalf("%d unexpected 'db two' schema entries", count) 67 } 68 if err := conn.QueryRowContext(ctx, "SELECT count(*) FROM main.sqlite_schema").Scan(&count); err != nil { 69 t.Fatal(err) 70 } 71 if count != 4 { 72 t.Fatalf("%d main schema entries, want 4", count) 73 } 74 75 if err := DropAll(ctx, conn, "main"); err != nil { 76 t.Fatal(err) 77 } 78 if err := conn.QueryRowContext(ctx, "SELECT count(*) FROM main.sqlite_schema").Scan(&count); err != nil { 79 t.Fatal(err) 80 } 81 if count != 0 { 82 t.Fatalf("%d unexpected main schema entries", count) 83 } 84 } 85 86 func TestCopyAll(t *testing.T) { 87 ctx := context.Background() 88 db := openTestDB(t) 89 conn, err := db.Conn(ctx) 90 if err != nil { 91 t.Fatal(err) 92 } 93 defer conn.Close() 94 95 err = ExecScript(conn, ` 96 BEGIN; 97 CREATE TABLE customer ( 98 cust_id INTEGER PRIMARY KEY, 99 cust_name TEXT, 100 cust_addr TEXT 101 ); 102 CREATE INDEX custname ON customer (cust_name); 103 CREATE VIEW customer_address AS 104 SELECT cust_id, cust_addr FROM customer; 105 CREATE TRIGGER cust_addr_chng 106 INSTEAD OF UPDATE OF cust_addr ON customer_address 107 BEGIN 108 UPDATE customer SET cust_addr=NEW.cust_addr 109 WHERE cust_id=NEW.cust_id; 110 END; 111 COMMIT; 112 INSERT INTO customer (cust_id, cust_name, cust_addr) VALUES (1, 'joe', 'eldorado'); 113 114 -- Creates an auto-index we should not copy. 115 CREATE TABLE textkey (key TEXT PRIMARY KEY, val INTEGER); 116 117 ATTACH 'file:s1?mode=memory' AS "db two"; 118 `) 119 if err != nil { 120 t.Fatal(err) 121 } 122 123 if err := CopyAll(ctx, conn, "db two", "main"); err != nil { 124 t.Fatal(err) 125 } 126 127 var name string 128 if err := conn.QueryRowContext(ctx, "SELECT cust_name FROM \"db two\".customer WHERE cust_id=1").Scan(&name); err != nil { 129 t.Fatal(err) 130 } 131 if name != "joe" { 132 t.Fatalf("name=%q, want %q", name, "joe") 133 } 134 var count int 135 if err := conn.QueryRowContext(ctx, "SELECT count(*) FROM \"db two\".sqlite_schema").Scan(&count); err != nil { 136 t.Fatal(err) 137 } 138 if count != 6 { 139 t.Fatalf("dst schema count=%d, want 4", count) 140 } 141 }