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

     1  package statement_test
     2  
     3  import (
     4  	"fmt"
     5  	"log"
     6  	"testing"
     7  
     8  	"github.com/ncruces/go-sqlite3"
     9  	_ "github.com/ncruces/go-sqlite3/embed"
    10  	"github.com/ncruces/go-sqlite3/ext/statement"
    11  	_ "github.com/ncruces/go-sqlite3/tests/testcfg"
    12  )
    13  
    14  func Example() {
    15  	db, err := sqlite3.Open(":memory:")
    16  	if err != nil {
    17  		log.Fatal(err)
    18  	}
    19  	defer db.Close()
    20  
    21  	statement.Register(db)
    22  
    23  	err = db.Exec(`
    24  		CREATE VIRTUAL TABLE split_date USING statement((
    25  			SELECT
    26  				strftime('%Y', :date) AS year,
    27  				strftime('%m', :date) AS month,
    28  				strftime('%d', :date) AS day
    29  		))`)
    30  	if err != nil {
    31  		log.Fatal(err)
    32  	}
    33  
    34  	stmt, _, err := db.Prepare(`SELECT * FROM split_date('2022-02-22')`)
    35  	if err != nil {
    36  		log.Fatal(err)
    37  	}
    38  	defer stmt.Close()
    39  
    40  	if stmt.Step() {
    41  		fmt.Printf("Twosday was %d-%d-%d", stmt.ColumnInt(0), stmt.ColumnInt(1), stmt.ColumnInt(2))
    42  	}
    43  	if err := stmt.Reset(); err != nil {
    44  		log.Fatal(err)
    45  	}
    46  
    47  	// Output:
    48  	// Twosday was 2022-2-22
    49  }
    50  
    51  func TestRegister(t *testing.T) {
    52  	t.Parallel()
    53  
    54  	db, err := sqlite3.Open(":memory:")
    55  	if err != nil {
    56  		t.Fatal(err)
    57  	}
    58  	defer db.Close()
    59  
    60  	statement.Register(db)
    61  
    62  	err = db.Exec(`
    63  		CREATE VIRTUAL TABLE arguments USING statement((SELECT ? AS a, ? AS b, ? AS c))
    64  	`)
    65  	if err != nil {
    66  		t.Fatal(err)
    67  	}
    68  
    69  	err = db.Exec(`
    70  		SELECT * from arguments WHERE [2] = 'y' AND [3] = 'z'
    71  	`)
    72  	if err != nil {
    73  		t.Fatal(err)
    74  	}
    75  
    76  	err = db.Exec(`
    77  		CREATE VIRTUAL TABLE hypot USING statement((SELECT sqrt(:x * :x + :y * :y) AS hypotenuse))
    78  	`)
    79  	if err != nil {
    80  		t.Fatal(err)
    81  	}
    82  
    83  	stmt, _, err := db.Prepare(`
    84  		SELECT x, y, * FROM hypot WHERE x = 3 AND y = 4
    85  	`)
    86  	if err != nil {
    87  		t.Fatal(err)
    88  	}
    89  	defer stmt.Close()
    90  
    91  	if stmt.Step() {
    92  		x := stmt.ColumnInt(0)
    93  		y := stmt.ColumnInt(1)
    94  		hypot := stmt.ColumnInt(2)
    95  		if x != 3 || y != 4 || hypot != 5 {
    96  			t.Errorf("hypot(%d, %d) = %d", x, y, hypot)
    97  		}
    98  	}
    99  }
   100  
   101  func TestRegister_errors(t *testing.T) {
   102  	t.Parallel()
   103  
   104  	db, err := sqlite3.Open(":memory:")
   105  	if err != nil {
   106  		t.Fatal(err)
   107  	}
   108  	defer db.Close()
   109  
   110  	statement.Register(db)
   111  
   112  	err = db.Exec(`CREATE VIRTUAL TABLE split_date USING statement()`)
   113  	if err == nil {
   114  		t.Fatal("want error")
   115  	} else {
   116  		t.Log(err)
   117  	}
   118  
   119  	err = db.Exec(`CREATE VIRTUAL TABLE split_date USING statement(SELECT 1, SELECT 2)`)
   120  	if err == nil {
   121  		t.Fatal("want error")
   122  	} else {
   123  		t.Log(err)
   124  	}
   125  
   126  	err = db.Exec(`CREATE VIRTUAL TABLE split_date USING statement((SELECT 1, SELECT 2))`)
   127  	if err == nil {
   128  		t.Fatal("want error")
   129  	} else {
   130  		t.Log(err)
   131  	}
   132  
   133  	err = db.Exec(`CREATE VIRTUAL TABLE split_date USING statement((SELECT 1; SELECT 2))`)
   134  	if err == nil {
   135  		t.Fatal("want error")
   136  	} else {
   137  		t.Log(err)
   138  	}
   139  
   140  	err = db.Exec(`CREATE VIRTUAL TABLE split_date USING statement((CREATE TABLE x(val)))`)
   141  	if err == nil {
   142  		t.Fatal("want error")
   143  	} else {
   144  		t.Log(err)
   145  	}
   146  }