query_test.go

package dbx

import (
	"testing"

	_ "modernc.org/sqlite"
)

func TestCompileNamedParamsPostgres(t *testing.T) {
	db := NewFromDB(nil, "postgres")
	q := db.NewQuery("SELECT * FROM users WHERE id={:id} OR parent_id={:id} AND status={:status}").
		Bind(Params{"id": 7, "status": "ok"})

	sql, args, err := q.compile()
	if err != nil {
		t.Fatalf("compile error: %v", err)
	}
	wantSQL := "SELECT * FROM users WHERE id=$1 OR parent_id=$1 AND status=$2"
	if sql != wantSQL {
		t.Fatalf("unexpected SQL:\nwant: %s\ngot:  %s", wantSQL, sql)
	}
	if len(args) != 2 || args[0] != 7 || args[1] != "ok" {
		t.Fatalf("unexpected args: %#v", args)
	}
}

func TestCompileMissingParam(t *testing.T) {
	db := NewFromDB(nil, "sqlite3")
	q := db.NewQuery("SELECT * FROM users WHERE id={:id}")

	_, _, err := q.compile()
	if err == nil {
		t.Fatal("expected missing param error")
	}
}

func TestQueryMapRequiresTwoColumns(t *testing.T) {
	db, err := Open("sqlite", ":memory:")
	if err != nil {
		t.Fatalf("open sqlite: %v", err)
	}
	defer db.Close()

	if _, err := db.NewQuery(`CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)`).Execute(); err != nil {
		t.Fatalf("create table: %v", err)
	}
	if _, err := db.Insert("users", Params{"name": "alice"}).Execute(); err != nil {
		t.Fatalf("insert: %v", err)
	}

	out := map[string]string{}
	err = db.Select("id", "name", "id").From("users").Map(&out)
	if err == nil {
		t.Fatal("expected map error for non-2-column query")
	}
}