forked from baron/baron-sso
102 lines
2.6 KiB
Go
102 lines
2.6 KiB
Go
package repository
|
|
|
|
import (
|
|
"context"
|
|
|
|
"gorm.io/gorm"
|
|
)
|
|
|
|
func CountOrphanUserTenantMemberships(ctx context.Context, db *gorm.DB) (int64, error) {
|
|
var count int64
|
|
err := db.WithContext(ctx).Raw(`
|
|
SELECT COUNT(*)
|
|
FROM users AS u
|
|
WHERE u.deleted_at IS NULL
|
|
AND (
|
|
u.tenant_id IS NOT NULL
|
|
AND NOT EXISTS (
|
|
SELECT 1
|
|
FROM tenants AS t
|
|
WHERE t.id = u.tenant_id
|
|
AND t.deleted_at IS NULL
|
|
)
|
|
)
|
|
`).Scan(&count).Error
|
|
return count, err
|
|
}
|
|
|
|
func ClearOrphanUserTenantMemberships(ctx context.Context, db *gorm.DB) (int64, error) {
|
|
userResult := db.WithContext(ctx).Exec(`
|
|
WITH orphan_users AS (
|
|
SELECT u.id AS user_id,
|
|
replacement.id AS replacement_tenant_id
|
|
FROM users AS u
|
|
JOIN tenants AS deleted_tenant
|
|
ON deleted_tenant.id = u.tenant_id
|
|
AND deleted_tenant.deleted_at IS NOT NULL
|
|
JOIN LATERAL (
|
|
WITH RECURSIVE ancestors AS (
|
|
SELECT parent.id, parent.parent_id, parent.deleted_at, 1 AS depth
|
|
FROM tenants AS parent
|
|
WHERE parent.id = deleted_tenant.parent_id
|
|
UNION ALL
|
|
SELECT parent.id, parent.parent_id, parent.deleted_at, ancestors.depth + 1
|
|
FROM tenants AS parent
|
|
JOIN ancestors ON parent.id = ancestors.parent_id
|
|
WHERE ancestors.parent_id IS NOT NULL
|
|
AND ancestors.parent_id <> ancestors.id
|
|
)
|
|
SELECT id
|
|
FROM ancestors
|
|
WHERE deleted_at IS NULL
|
|
ORDER BY depth
|
|
LIMIT 1
|
|
) AS replacement ON true
|
|
WHERE u.deleted_at IS NULL
|
|
AND u.tenant_id IS NOT NULL
|
|
)
|
|
UPDATE users AS u
|
|
SET tenant_id = ou.replacement_tenant_id,
|
|
updated_at = NOW()
|
|
FROM orphan_users AS ou
|
|
WHERE u.id = ou.user_id
|
|
`)
|
|
if userResult.Error != nil {
|
|
return userResult.RowsAffected, userResult.Error
|
|
}
|
|
|
|
loginResult := db.WithContext(ctx).Exec(`
|
|
WITH orphan_login_ids AS (
|
|
SELECT uli.id AS login_id,
|
|
replacement.id AS replacement_tenant_id
|
|
FROM user_login_ids AS uli
|
|
JOIN tenants AS deleted_tenant
|
|
ON deleted_tenant.id = uli.tenant_id
|
|
AND deleted_tenant.deleted_at IS NOT NULL
|
|
JOIN LATERAL (
|
|
WITH RECURSIVE ancestors AS (
|
|
SELECT parent.id, parent.parent_id, parent.deleted_at, 1 AS depth
|
|
FROM tenants AS parent
|
|
WHERE parent.id = deleted_tenant.parent_id
|
|
UNION ALL
|
|
SELECT parent.id, parent.parent_id, parent.deleted_at, ancestors.depth + 1
|
|
FROM tenants AS parent
|
|
JOIN ancestors ON parent.id = ancestors.parent_id
|
|
WHERE ancestors.parent_id IS NOT NULL
|
|
AND ancestors.parent_id <> ancestors.id
|
|
)
|
|
SELECT id
|
|
FROM ancestors
|
|
WHERE deleted_at IS NULL
|
|
ORDER BY depth
|
|
LIMIT 1
|
|
) AS replacement ON true
|
|
)
|
|
UPDATE user_login_ids AS uli
|
|
SET tenant_id = oli.replacement_tenant_id
|
|
FROM orphan_login_ids AS oli
|
|
WHERE uli.id = oli.login_id
|
|
`)
|
|
return userResult.RowsAffected + loginResult.RowsAffected, loginResult.Error
|
|
}
|