github.com/ncruces/go-sqlite3@v0.15.1-0.20240520133447-53eef1510ff0/ext/blobio/blob_test.go (about)

     1  package blobio_test
     2  
     3  import (
     4  	"io"
     5  	"log"
     6  	"os"
     7  	"reflect"
     8  	"testing"
     9  
    10  	"github.com/ncruces/go-sqlite3"
    11  	"github.com/ncruces/go-sqlite3/driver"
    12  	_ "github.com/ncruces/go-sqlite3/embed"
    13  	"github.com/ncruces/go-sqlite3/ext/array"
    14  	"github.com/ncruces/go-sqlite3/ext/blobio"
    15  	_ "github.com/ncruces/go-sqlite3/tests/testcfg"
    16  	_ "github.com/ncruces/go-sqlite3/vfs/memdb"
    17  )
    18  
    19  func Example() {
    20  	// Open the database, registering the extension.
    21  	db, err := driver.Open("file:/test.db?vfs=memdb", func(conn *sqlite3.Conn) error {
    22  		blobio.Register(conn)
    23  		return nil
    24  	})
    25  
    26  	if err != nil {
    27  		log.Fatal(err)
    28  	}
    29  	defer db.Close()
    30  
    31  	_, err = db.Exec(`CREATE TABLE test (col)`)
    32  	if err != nil {
    33  		log.Fatal(err)
    34  	}
    35  
    36  	const message = "Hello BLOB!"
    37  
    38  	// Create the BLOB.
    39  	_, err = db.Exec(`INSERT INTO test VALUES (?)`, sqlite3.ZeroBlob(len(message)))
    40  	if err != nil {
    41  		log.Fatal(err)
    42  	}
    43  
    44  	// Write the BLOB.
    45  	_, err = db.Exec(`SELECT writeblob('main', 'test', 'col', last_insert_rowid(), 0, ?)`, message)
    46  	if err != nil {
    47  		log.Fatal(err)
    48  	}
    49  
    50  	// Read the BLOB.
    51  	_, err = db.Exec(`SELECT openblob('main', 'test', 'col', rowid, false, ?) FROM test`,
    52  		sqlite3.Pointer[blobio.OpenCallback](func(blob *sqlite3.Blob, _ ...sqlite3.Value) error {
    53  			_, err = io.Copy(os.Stdout, blob)
    54  			return err
    55  		}))
    56  	if err != nil {
    57  		log.Fatal(err)
    58  	}
    59  	// Output:
    60  	// Hello BLOB!
    61  }
    62  
    63  func Test_readblob(t *testing.T) {
    64  	t.Parallel()
    65  
    66  	db, err := sqlite3.Open(":memory:")
    67  	if err != nil {
    68  		t.Fatal(err)
    69  	}
    70  	defer db.Close()
    71  
    72  	blobio.Register(db)
    73  	array.Register(db)
    74  
    75  	err = db.Exec(`SELECT readblob()`)
    76  	if err == nil {
    77  		t.Fatal("want error")
    78  	} else {
    79  		t.Log(err)
    80  	}
    81  
    82  	err = db.Exec(`
    83  		CREATE TABLE test1 (col);
    84  		CREATE TABLE test2 (col);
    85  		INSERT INTO test1 VALUES (x'cafe');
    86  		INSERT INTO test2 VALUES (x'babe');
    87  	`)
    88  	if err != nil {
    89  		t.Fatal(err)
    90  	}
    91  
    92  	stmt, _, err := db.Prepare(`SELECT readblob('main', value, 'col', 1, 1, 1) FROM array(?)`)
    93  	if err != nil {
    94  		t.Fatal(err)
    95  	}
    96  	defer stmt.Close()
    97  
    98  	err = stmt.BindPointer(1, []string{"test1", "test2"})
    99  	if err != nil {
   100  		t.Fatal(err)
   101  	}
   102  
   103  	if stmt.Step() {
   104  		got := stmt.ColumnText(0)
   105  		if got != "\xfe" {
   106  			t.Errorf("got %q", got)
   107  		}
   108  	}
   109  
   110  	if stmt.Step() {
   111  		got := stmt.ColumnText(0)
   112  		if got != "\xbe" {
   113  			t.Errorf("got %q", got)
   114  		}
   115  	}
   116  
   117  	err = stmt.Err()
   118  	if err != nil {
   119  		t.Fatal(err)
   120  	}
   121  }
   122  
   123  func Test_openblob(t *testing.T) {
   124  	t.Parallel()
   125  
   126  	db, err := sqlite3.Open(":memory:")
   127  	if err != nil {
   128  		t.Fatal(err)
   129  	}
   130  	defer db.Close()
   131  
   132  	blobio.Register(db)
   133  	array.Register(db)
   134  
   135  	err = db.Exec(`SELECT openblob()`)
   136  	if err == nil {
   137  		t.Fatal("want error")
   138  	} else {
   139  		t.Log(err)
   140  	}
   141  
   142  	err = db.Exec(`
   143  		CREATE TABLE test1 (col);
   144  		CREATE TABLE test2 (col);
   145  		INSERT INTO test1 VALUES (x'cafe');
   146  		INSERT INTO test2 VALUES (x'babe');
   147  	`)
   148  	if err != nil {
   149  		t.Fatal(err)
   150  	}
   151  
   152  	stmt, _, err := db.Prepare(`SELECT openblob('main', value, 'col', 1, false, ?) FROM array(?)`)
   153  	if err != nil {
   154  		t.Fatal(err)
   155  	}
   156  	defer stmt.Close()
   157  
   158  	var got []string
   159  	err = stmt.BindPointer(1, blobio.OpenCallback(func(b *sqlite3.Blob, _ ...sqlite3.Value) error {
   160  		d, err := io.ReadAll(b)
   161  		if err != nil {
   162  			return err
   163  		}
   164  		got = append(got, string(d))
   165  		return nil
   166  	}))
   167  	if err != nil {
   168  		t.Fatal(err)
   169  	}
   170  
   171  	err = stmt.BindPointer(2, []string{"test1", "test2"})
   172  	if err != nil {
   173  		t.Fatal(err)
   174  	}
   175  
   176  	err = stmt.Exec()
   177  	if err != nil {
   178  		t.Fatal(err)
   179  	}
   180  
   181  	want := []string{"\xca\xfe", "\xba\xbe"}
   182  	if !reflect.DeepEqual(got, want) {
   183  		t.Errorf("got %v, want %v", got, want)
   184  	}
   185  }