mirror of
https://github.com/hanzoai/orm.git
synced 2026-10-30 15:01:22 +00:00
zeekayandHanzo Dev
101c90e8dc
A filter was json_extract over every live row of every kind: an IAM email lookup took 419 ms at 150,000 users, a token lookup 143 ms, linear in the table. Each tagged field now has idx_<kind>_<path> ON _entities(json_extract(...)) WHERE kind = '<kind>' AND deleted = 0, built in the background on the kind's first query. Statements spell the kind as a literal so the partial index applies, and the last equality filter on an indexed field drives; other indexed terms are written +expr, because with no statistics SQLite chose among the indexes by creation order. idx_entities_deleted is dropped: every unindexed statement walked it across all kinds. Measured with IAM's own store at 1k/10k/50k/150k/300k users: email lookups 1.2/0.9/1.4/1.6/1.5 ms (were 2.8/29/139/419/831), token lookups 0.7/0.6/1.4/1.2/0.9 ms (were 1.0/11/49/143/283). Co-authored-by: Hanzo Dev <[email protected]>
82 lines
2.2 KiB
Go
82 lines
2.2 KiB
Go
package orm
|
|
|
|
import (
|
|
"database/sql"
|
|
"path/filepath"
|
|
"slices"
|
|
"testing"
|
|
"time"
|
|
|
|
ormdb "github.com/hanzoai/orm/db"
|
|
)
|
|
|
|
type indexedAccount struct {
|
|
Model[indexedAccount]
|
|
Owner string `json:"owner" orm:"index"`
|
|
Email string `json:"email,omitempty" orm:"index"`
|
|
Hidden string `json:"-" orm:"index"`
|
|
Plain string `json:"plain"`
|
|
Tier string `json:"tier" orm:"index,default:x"`
|
|
}
|
|
|
|
func init() { Register[indexedAccount]("indexed-account") }
|
|
|
|
// orm:"index" names the JSON key the field is stored under; a field kept out of the
|
|
// document has nothing to index.
|
|
func TestIndexTagsNameTheStoredKeys(t *testing.T) {
|
|
meta := MustLookup("indexed-account")
|
|
if want := []string{"owner", "email", "tier"}; !slices.Equal(meta.Indexes, want) {
|
|
t.Fatalf("Indexes = %v, want %v", meta.Indexes, want)
|
|
}
|
|
if meta.Defaults["Tier"] != "x" {
|
|
t.Fatalf("a second tag part was lost: defaults %v", meta.Defaults)
|
|
}
|
|
}
|
|
|
|
// The first query of a kind builds its indexes, without the query waiting on it.
|
|
func TestTheFirstQueryOfAKindBuildsItsIndexes(t *testing.T) {
|
|
path := filepath.Join(t.TempDir(), "test.db")
|
|
db, err := OpenSQLite(&ormdb.SQLiteDBConfig{Path: path, Config: ormdb.SQLiteConfig{BusyTimeout: 5000, JournalMode: "WAL"}})
|
|
if err != nil {
|
|
t.Fatal(err)
|
|
}
|
|
t.Cleanup(func() { db.Close() })
|
|
|
|
a := New[indexedAccount](db)
|
|
a.Owner, a.Email = "acme", "[email protected]"
|
|
if err := a.Create(); err != nil {
|
|
t.Fatal(err)
|
|
}
|
|
if _, err := TypedQuery[indexedAccount](db).Filter("Email=", "[email protected]").First(); err != nil {
|
|
t.Fatal(err)
|
|
}
|
|
|
|
look, err := sql.Open("sqlite", path)
|
|
if err != nil {
|
|
t.Fatal(err)
|
|
}
|
|
defer look.Close()
|
|
want := []string{"idx_indexed-account_email", "idx_indexed-account_owner", "idx_indexed-account_tier"}
|
|
deadline := time.Now().Add(10 * time.Second)
|
|
for {
|
|
var got []string
|
|
rows, err := look.Query(`SELECT name FROM sqlite_master WHERE type = 'index' AND name LIKE 'idx_indexed-account_%' ORDER BY name`)
|
|
if err != nil {
|
|
t.Fatal(err)
|
|
}
|
|
for rows.Next() {
|
|
var n string
|
|
rows.Scan(&n)
|
|
got = append(got, n)
|
|
}
|
|
rows.Close()
|
|
if slices.Equal(got, want) {
|
|
return
|
|
}
|
|
if time.Now().After(deadline) {
|
|
t.Fatalf("indexes = %v, want %v", got, want)
|
|
}
|
|
time.Sleep(20 * time.Millisecond)
|
|
}
|
|
}
|