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  }