how to use SetCollation

[graf0]

Hi!

Sqlite3 - by default - sort data using ASCII codes - and that's ok if you are using English language.
But for other languages (ie.: Polish) that's not good.
But - you have SetCollaction you can use unicode defualt collation from golang.org/x/text/language package.
Of course you can use particular language as well - but defualt collaction will sort most of utf8 strings right.
I'd like to suggest to add this (or simillar) as example to SetCollaction function & maybe mention it in README.
That's something that is not obvious for developers - i think...

package main

import (
	"fmt"

	"golang.org/x/text/collate"
	"golang.org/x/text/language"
	"zombiezen.com/go/sqlite"
	"zombiezen.com/go/sqlite/sqlitex"
)

func main() {
	conn, err := sqlite.OpenConn(":memory:", sqlite.OpenReadWrite)
	if err != nil {
		panic(err)
	}
	defer conn.Close()

	// use general unicode sort, not language specific
	c := collate.New(language.Und, collate.IgnoreCase)
	conn.SetCollation("NOCASE", c.CompareString)

	// setup table
	sqlitex.ExecuteScript(conn, `
		-- sort name using NOCASE collation
		CREATE TABLE test (name TEXT COLLATE NOCASE);
		-- insert unsorted data
		INSERT INTO test (name) VALUES ('atext'), ('btext'), ('ctext'), ('ątext'), ('ćtext');
	`, nil)

	stmt := conn.Prep("SELECT n FROM t ORDER BY n ASC")
	defer stmt.Finalize()

	for {
		hasRow, err := stmt.Step()
		if err != nil {
			panic(err)
		}
		if !hasRow {
			break
		}
		t := stmt.ColumnText(0)
		fmt.Println(t)
	}
	// should print: atext ątext btext ctext ćtext
}

[zombiezen]

That's a great example, thank you! I've gone ahead and added it to the documentation and it will be available in the next release.