mirror of
https://github.com/hanzoai/base.git
synced 2026-10-21 03:41:50 +00:00
zeekay
d9f7c4da8e
BASE_DB_TIER=sql opened PostgreSQL and then spoke to it in SQLite: strftime, sqlite_master, json_extract, json_each, an INTEGER column holding an int64, an outer join with no ON, backticked identifiers, and a rowid that only one engine has. The tier picked a backend the queries could not reach. Every one of those now comes from hanzoai/orm/dialect, resolved once from the driver the data DB was opened with and reachable as app.Dialect(). Base holds no switch on the engine — it asks for the JSON accessors, the schema catalog, the generated instant and identifier, and the column types, and it asks whether a feature exists at all where one engine has nothing to offer: an insertion-order column, a write-ahead log to fold, a function that renders an instant. Empty means the caller falls back or refuses rather than emitting something the engine will reject. Some differences turned out not to need a dialect at all. An instant is stored as text in a fixed layout, so bucketing the log rollup to the hour is a substring rather than a calendar function, which every engine spells the same way and an index can be built on. Null-safe equality is IS NOT DISTINCT FROM. An int64 column is BIGINT. A join with nothing of its own to match still states a condition. Case-insensitive comparison stays COLLATE NOCASE, because the schema now asks the engine for the collation that name refers to. The proof is a Postgres 18 running the data plane: migrations apply, a collection created over /v1 lands as a table with text, numeric and jsonb columns and its declared index, and records insert, page, sort, filter across scalars, multi-value elements and JSON paths, update and delete. A unique index declared COLLATE NOCASE rejects the second spelling of a handle. Comparing a JSON sub-path to a number is refused there: the extraction yields text, the literal binds as a number, and the filter language does not say which the comparison meant. Compared as text it works.
72 lines
1.9 KiB
Go
72 lines
1.9 KiB
Go
package core
|
|
|
|
import (
|
|
"time"
|
|
|
|
"github.com/hanzoai/base/tools/types"
|
|
"github.com/hanzoai/dbx"
|
|
)
|
|
|
|
// LogQuery returns a new Log select query.
|
|
func (app *BaseApp) LogQuery() *dbx.SelectQuery {
|
|
return app.AuxModelQuery(&Log{})
|
|
}
|
|
|
|
// FindLogById finds a single Log entry by its id.
|
|
func (app *BaseApp) FindLogById(id string) (*Log, error) {
|
|
model := &Log{}
|
|
|
|
err := app.LogQuery().
|
|
AndWhere(dbx.HashExp{"id": id}).
|
|
Limit(1).
|
|
One(model)
|
|
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
|
|
return model, nil
|
|
}
|
|
|
|
// LogHour buckets a stored instant to the hour it falls in. An instant is
|
|
// stored as text in a fixed layout, so the bucket is the leading characters
|
|
// of that text rather than a calendar function — which is one expression on
|
|
// every engine, and one an index can be built on.
|
|
const LogHour = `(substr([[created]], 1, 13) || ':00:00')`
|
|
|
|
// LogsStatsItem defines the total number of logs for a specific time period.
|
|
type LogsStatsItem struct {
|
|
Date types.DateTime `db:"date" json:"date"`
|
|
Total int `db:"total" json:"total"`
|
|
}
|
|
|
|
// LogsStats returns hourly grouped logs statistics.
|
|
func (app *BaseApp) LogsStats(expr dbx.Expression) ([]*LogsStatsItem, error) {
|
|
result := []*LogsStatsItem{}
|
|
|
|
query := app.LogQuery().
|
|
Select("count(id) as total", LogHour+" as date").
|
|
GroupBy("date")
|
|
|
|
if expr != nil {
|
|
query.AndWhere(expr)
|
|
}
|
|
|
|
err := query.All(&result)
|
|
|
|
return result, err
|
|
}
|
|
|
|
// DeleteOldLogs delete all logs that are created before createdBefore.
|
|
//
|
|
// For better performance the logs delete is executed as plain SQL statement,
|
|
// aka. no delete model hook events will be fired.
|
|
func (app *BaseApp) DeleteOldLogs(createdBefore time.Time) error {
|
|
formattedDate := createdBefore.UTC().Format(types.DefaultDateLayout)
|
|
expr := dbx.NewExp("[[created]] <= {:date}", dbx.Params{"date": formattedDate})
|
|
|
|
_, err := app.auxNonconcurrentDB.Delete((&Log{}).TableName(), expr).Execute()
|
|
|
|
return err
|
|
}
|