zitadel/internal/query/resource_counts_test.go
Tim Möhlmann b9c1cdf4ad
feat(projections): resource counters (#9979)
# Which Problems Are Solved

Add the ability to keep track of the current counts of projection
resources. We want to prevent calling `SELECT COUNT(*)` on tables, as
that forces a full scan and sudden spikes of DB resource uses.

# How the Problems Are Solved

- A resource_counts table is added
- Triggers that increment and decrement the counted values on inserts
and deletes
- Triggers that delete all counts of a table when the source table is
TRUNCATEd. This is not in the business logic, but prevents wrong counts
in case someone want to force a re-projection.
- Triggers that delete all counts if the parent resource is deleted
- Script to pre-populate the resource_counts table when a new source
table is added.

The triggers are reusable for any type of resource, in case we choose to
add more in the future.
Counts are aggregated by a given parent. Currently only `instance` and
`organization` are defined as possible parent. This can later be
extended to other types, such as `project`, should the need arise.

I deliberately chose to use `parent_id` to distinguish from the
de-factor `resource_owner` which is usually an organization ID. For
example:

- For users the parent is an organization and the `parent_id` matches
`resource_owner`.
- For organizations the parent is an instance, but the `resource_owner`
is the `org_id`. In this case the `parent_id` is the `instance_id`.
- Applications would have a similar problem, where the parent is a
project, but the `resource_owner` is the `org_id`


# Additional Context

Closes https://github.com/zitadel/zitadel/issues/9957
2025-06-03 14:15:30 +00:00

110 lines
2.5 KiB
Go

package query
import (
"context"
_ "embed"
"regexp"
"testing"
"time"
"github.com/DATA-DOG/go-sqlmock"
"github.com/stretchr/testify/assert"
"github.com/stretchr/testify/require"
"github.com/zitadel/zitadel/internal/database"
"github.com/zitadel/zitadel/internal/domain"
)
func TestQueries_ListResourceCounts(t *testing.T) {
columns := []string{"id", "instance_id", "table_name", "parent_type", "parent_id", "resource_name", "updated_at", "amount"}
type args struct {
lastID int
limit int
}
tests := []struct {
name string
args args
expects func(sqlmock.Sqlmock)
wantResult []ResourceCount
wantErr bool
}{
{
name: "query error",
args: args{
lastID: 0,
limit: 10,
},
expects: func(mock sqlmock.Sqlmock) {
mock.ExpectQuery(regexp.QuoteMeta(resourceCountsListQuery)).
WithArgs(0, 10).
WillReturnError(assert.AnError)
},
wantErr: true,
},
{
name: "success",
args: args{
lastID: 0,
limit: 10,
},
expects: func(mock sqlmock.Sqlmock) {
mock.ExpectQuery(regexp.QuoteMeta(resourceCountsListQuery)).
WithArgs(0, 10).
WillReturnRows(
sqlmock.NewRows(columns).
AddRow(1, "instance_1", "table", "instance", "parent_1", "resource_name", time.Unix(1, 2), 5).
AddRow(2, "instance_2", "table", "instance", "parent_2", "resource_name", time.Unix(1, 2), 6),
)
},
wantResult: []ResourceCount{
{
ID: 1,
InstanceID: "instance_1",
TableName: "table",
ParentType: domain.CountParentTypeInstance,
ParentID: "parent_1",
Resource: "resource_name",
UpdatedAt: time.Unix(1, 2),
Amount: 5,
},
{
ID: 2,
InstanceID: "instance_2",
TableName: "table",
ParentType: domain.CountParentTypeInstance,
ParentID: "parent_2",
Resource: "resource_name",
UpdatedAt: time.Unix(1, 2),
Amount: 6,
},
},
},
}
for _, tt := range tests {
t.Run(tt.name, func(t *testing.T) {
db, mock, err := sqlmock.New()
require.NoError(t, err)
defer func() {
err := mock.ExpectationsWereMet()
require.NoError(t, err)
}()
defer db.Close()
tt.expects(mock)
mock.ExpectClose()
q := &Queries{
client: &database.DB{
DB: db,
},
}
gotResult, err := q.ListResourceCounts(context.Background(), tt.args.lastID, tt.args.limit)
if tt.wantErr {
require.Error(t, err)
return
}
require.NoError(t, err)
assert.Equal(t, tt.wantResult, gotResult, "ListResourceCounts() result mismatch")
})
}
}