how to use SetCollation

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 / go-sqlite

how to use SetCollation #64