Files
zeekayandHanzo Dev f223c1717d object: a host hands ai its database; ai's schema and suite run on PostgreSQL
UseDB(*dbx.DB) before Bootstrap makes InitAdapter open nothing from config.
SyncSchema(db) is the idempotent table sync a host runs as the open of the store.
On PostgreSQL it types the tables for the server (BIGINT, DOUBLE PRECISION, BOOLEAN,
identity for a lone integer key) and finds them in the connection's own schema, where
dbx.Sync's 32-bit INTEGER/REAL/bool-as-INTEGER and all-schema lookups are wrong.
The null backfill writes FALSE for a bool there.

AI_TEST_SQL_DSN makes UseMemoryDB run a test on a fresh PostgreSQL database (pgx);
object, controllers, routers and log are green on both engines.

Co-authored-by: Hanzo Dev <[email protected]>
2026-11-13 13:58:01 -08:00

180 lines
5.1 KiB
Go

// Copyright 2023-2025 Hanzo AI Inc. All Rights Reserved.
//
// Licensed under the Apache License, Version 2.0 (the "License");
// you may not use this file except in compliance with the License.
// You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
package object
import (
"fmt"
"reflect"
"strings"
"github.com/hanzoai/dbx"
)
// postgres reports whether db speaks PostgreSQL (lib/pq's "postgres" or pgx's "pgx").
func postgres(db *dbx.DB) bool {
switch db.DriverName() {
case "postgres", "pgx":
return true
}
return false
}
// syncModel creates or grows one model's table on db. SQLite is dbx.Sync, exactly
// as before. PostgreSQL cannot use it: Sync types every integer INTEGER (32 bits),
// every float REAL (4 bytes) and every bool INTEGER (which a BOOLEAN-bound Go bool
// is refused by), and it looks for an existing table and its columns by name across
// every schema of the database, where a host keeps one schema per store and another
// store's `task` or `message` would read as this one's. So the same struct is read
// the same way (names, primary keys) and typed for the server.
func syncModel(db *dbx.DB, model any) error {
if !postgres(db) {
return db.Sync(model)
}
t := reflect.TypeOf(model)
if t.Kind() == reflect.Pointer {
t = t.Elem()
}
if t.Kind() != reflect.Struct {
return fmt.Errorf("sync: expected struct, got %s", t.Kind())
}
table := dbx.DefaultFieldMapFunc(t.Name())
type column struct {
name, typ string
pk bool
integer bool
}
var cols []column
for f := range t.Fields() {
if !f.IsExported() || f.Anonymous {
continue
}
tag := f.Tag.Get("db")
if tag == "-" {
continue
}
name, pk := tag, false
switch {
case tag == "pk":
name, pk = "", true
case strings.HasPrefix(tag, "pk,"):
name, pk = tag[3:], true
}
if name == "" {
name = dbx.DefaultFieldMapFunc(f.Name)
}
cols = append(cols, column{name, pgType(f.Type), pk, isInt(f.Type)})
}
// A lone integer key is the row's number, which dbx.Insert leaves unset for the
// database to give (SQLite's rowid does; PostgreSQL needs an identity). BY DEFAULT,
// so a row carried from a file keeps the number it had.
npk := 0
for _, c := range cols {
if c.pk {
npk++
}
}
if npk == 1 {
for i, c := range cols {
if c.pk && c.integer {
cols[i].typ = "BIGINT GENERATED BY DEFAULT AS IDENTITY"
}
}
}
if len(cols) == 0 {
return fmt.Errorf("sync: %s has no exported fields", t.Name())
}
have := map[string]bool{}
rows, err := db.NewQuery("SELECT column_name FROM information_schema.columns WHERE table_schema = current_schema() AND table_name = {:t}").
Bind(dbx.Params{"t": table}).Rows()
if err != nil {
return fmt.Errorf("columns of %s: %w", table, err)
}
for rows.Next() {
var c string
if err := rows.Scan(&c); err != nil {
_ = rows.Close()
return err
}
have[c] = true
}
if err := rows.Close(); err != nil {
return err
}
if len(have) == 0 {
var defs, pks []string
for _, c := range cols {
defs = append(defs, fmt.Sprintf("%s %s", quote(c.name), c.typ))
if c.pk {
pks = append(pks, quote(c.name))
}
}
if len(pks) > 0 {
defs = append(defs, fmt.Sprintf("PRIMARY KEY (%s)", strings.Join(pks, ", ")))
}
_, err := db.NewQuery(fmt.Sprintf("CREATE TABLE IF NOT EXISTS %s (\n %s\n)", quote(table), strings.Join(defs, ",\n "))).Execute()
return err
}
for _, c := range cols {
if have[c.name] {
continue
}
stmt := fmt.Sprintf("ALTER TABLE %s ADD COLUMN IF NOT EXISTS %s %s", quote(table), quote(c.name), c.typ)
if _, err := db.NewQuery(stmt).Execute(); err != nil {
return fmt.Errorf("add column %s.%s: %w", table, c.name, err)
}
}
return nil
}
// pgType is the PostgreSQL column type for a Go field type: dbx.Sync's mapping,
// widened where the server's is narrower (integers 64 bits, floats 8 bytes) and
// typed where SQLite stored a bool as 0/1. JSON-valued fields stay TEXT, as they
// are in the file, so a move copies them as they are.
func pgType(t reflect.Type) string {
if t.Kind() == reflect.Pointer {
t = t.Elem()
}
switch t.Kind() {
case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64,
reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64:
return "BIGINT"
case reflect.Float32, reflect.Float64:
return "DOUBLE PRECISION"
case reflect.Bool:
return "BOOLEAN"
case reflect.Slice:
if t.Elem().Kind() == reflect.Uint8 {
return "BYTEA"
}
}
return "TEXT"
}
func isInt(t reflect.Type) bool {
if t.Kind() == reflect.Pointer {
t = t.Elem()
}
switch t.Kind() {
case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64,
reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64:
return true
}
return false
}
func quote(name string) string { return `"` + strings.ReplaceAll(name, `"`, `""`) + `"` }