mirror of
https://github.com/hanzoai/ai.git
synced 2026-11-20 14:13:23 +00:00
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]>
180 lines
5.1 KiB
Go
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, `"`, `""`) + `"` }
|