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

     1  package sqlite3_test
     2  
     3  import (
     4  	"fmt"
     5  	"log"
     6  	"unicode"
     7  
     8  	"github.com/ncruces/go-sqlite3"
     9  	_ "github.com/ncruces/go-sqlite3/embed"
    10  )
    11  
    12  func ExampleConn_CreateWindowFunction() {
    13  	db, err := sqlite3.Open(":memory:")
    14  	if err != nil {
    15  		log.Fatal(err)
    16  	}
    17  	defer db.Close()
    18  
    19  	err = db.Exec(`CREATE TABLE words (word VARCHAR(10))`)
    20  	if err != nil {
    21  		log.Fatal(err)
    22  	}
    23  
    24  	err = db.Exec(`INSERT INTO words (word) VALUES ('côte'), ('cote'), ('coter'), ('coté'), ('cotée'), ('côté')`)
    25  	if err != nil {
    26  		log.Fatal(err)
    27  	}
    28  
    29  	err = db.CreateWindowFunction("count_ascii", 1, sqlite3.DETERMINISTIC|sqlite3.INNOCUOUS, newASCIICounter)
    30  	if err != nil {
    31  		log.Fatal(err)
    32  	}
    33  
    34  	stmt, _, err := db.Prepare(`SELECT count_ascii(word) OVER (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) FROM words`)
    35  	if err != nil {
    36  		log.Fatal(err)
    37  	}
    38  	defer stmt.Close()
    39  
    40  	for stmt.Step() {
    41  		fmt.Println(stmt.ColumnInt(0))
    42  	}
    43  	if err := stmt.Err(); err != nil {
    44  		log.Fatal(err)
    45  	}
    46  	// Output:
    47  	// 1
    48  	// 2
    49  	// 2
    50  	// 1
    51  	// 0
    52  	// 0
    53  }
    54  
    55  type countASCII struct{ result int }
    56  
    57  func newASCIICounter() sqlite3.AggregateFunction {
    58  	return &countASCII{}
    59  }
    60  
    61  func (f *countASCII) Value(ctx sqlite3.Context) {
    62  	ctx.ResultInt(f.result)
    63  }
    64  
    65  func (f *countASCII) Step(ctx sqlite3.Context, arg ...sqlite3.Value) {
    66  	if f.isASCII(arg[0]) {
    67  		f.result++
    68  	}
    69  }
    70  
    71  func (f *countASCII) Inverse(ctx sqlite3.Context, arg ...sqlite3.Value) {
    72  	if f.isASCII(arg[0]) {
    73  		f.result--
    74  	}
    75  }
    76  
    77  func (f *countASCII) isASCII(arg sqlite3.Value) bool {
    78  	if arg.Type() != sqlite3.TEXT {
    79  		return false
    80  	}
    81  	for _, c := range arg.RawBlob() {
    82  		if c > unicode.MaxASCII {
    83  			return false
    84  		}
    85  	}
    86  	return true
    87  }