mirror of
https://github.com/zitadel/zitadel.git
synced 2025-07-11 22:38:31 +00:00
b9c1cdf4ad
# 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
110 lines
2.5 KiB
Go
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")
|
|
})
|
|
}
|
|
}
|