Database governance built for humans and agents — controlling changes and access across every major database.
Go cicddata-governancedata-maskingdata-securitydatabase-accessdatabase-governancedevopsdevsecopsflywaygitopsliquibasemongodbmysqloraclepostgresqlschema-migrationssecuritysql-clientsqlservertidb
package store
import (
"context"
"crypto/sha256"
"database/sql"
"encoding/hex"
"encoding/json"
"fmt"
"strings"
"time"
celast "github.com/google/cel-go/common/ast"
celoperators "github.com/google/cel-go/common/operators"
celoverloads "github.com/google/cel-go/common/overloads"
"github.com/pkg/errors"
"google.golang.org/protobuf/encoding/protojson"
"github.com/bytebase/bytebase/backend/common"
storepb "github.com/bytebase/bytebase/backend/generated-go/store"
"github.com/bytebase/bytebase/backend/store/qb"
)
// SavedQueryMessage is the message for a saved query.
type SavedQueryMessage struct {
ProjectID string
ResourceID string
// The connected database's canonical resource name, or "" for none.
// Validated against the saved query's own project at write time; the
// reference is soft and may dangle afterwards.
Database string
Creator string
Title string
Statement string
// The folder path this saved query lives in ("a/b/c", "" = unfiled).
Folder string
// Bindings are the per-object grants. Empty means private to the creator
// (the admin backstop aside).
Bindings []*storepb.SavedQueryBinding
// Output only fields
Size int64
CreatedAt time.Time
UpdatedAt time.Time
// Whether FindSavedQueryMessage.PrincipalEmail starred this saved query.
Starred bool
}
// FindSavedQueryMessage is the API message for finding sheets.
type FindSavedQueryMessage struct {
// Either ProjectIDs or Workspace is required.
ProjectIDs []string
Workspace string
PrincipalEmail string
// AccessMembers restricts results to saved queries the caller can read:
// their own, plus any whose bindings name one of these principals in the
// stored member form ("users/{email}" and "groups/{email}" — the caller
// and their groups).
// Leave nil to skip the access clause entirely: the auditor List, and
// Search for a caller with project-level bb.savedQueries.get.
//
// Must describe the same caller as PrincipalEmail: the clause reads "own"
// from PrincipalEmail and "granted" from here. Setting one without the
// other narrows the result rather than widening it, but the two disagreeing
// would be a bug.
AccessMembers []string
ResourceID *string
// LoadFull is used if we want to load the full sheet.
LoadFull bool
// OrderByKeys overrides the default title order.
OrderByKeys []*OrderByKey
FilterQ *qb.Query
Limit *int
Offset *int
}
// PatchSavedQueryMessage is the message to patch a saved query.
type PatchSavedQueryMessage struct {
ResourceID string
// ProjectID scopes the write to the project the caller was authorized in:
// a purge reassigns surviving saved queries to the default project, and a
// patch racing it must not land on the reassigned row.
ProjectID string
Title *string
Statement *string
// Database sets the connected database ("" clears it).
Database *string
// Folder re-files the saved query ("" = unfiled).
Folder *string
}
// GetSavedQuery gets a sheet.
func (s *Store) GetSavedQuery(ctx context.Context, find *FindSavedQueryMessage) (*SavedQueryMessage, error) {
sheets, err := s.ListSavedQueries(ctx, find)
if err != nil {
return nil, err
}
if len(sheets) == 0 {
return nil, nil
}
if len(sheets) > 1 {
return nil, errors.Errorf("expected 1 sheet, got %d", len(sheets))
}
sheet := sheets[0]
return sheet, nil
}
// ListSavedQueries returns a list of sheets.
func (s *Store) ListSavedQueries(ctx context.Context, find *FindSavedQueryMessage) ([]*SavedQueryMessage, error) {
if len(find.ProjectIDs) == 0 && find.Workspace == "" {
return nil, errors.Errorf("empty project filter")
}
statementField := fmt.Sprintf("LEFT(saved_query.statement, %d)", common.MaxSheetSize)
if find.LoadFull {
statementField = "saved_query.statement"
}
q := qb.Q().Space(fmt.Sprintf(`
SELECT
saved_query.resource_id,
saved_query.creator,
saved_query.created_at,
saved_query.updated_at,
saved_query.project,
saved_query.payload,
saved_query.folder,
saved_query.name,
saved_query.bindings,
%s,
OCTET_LENGTH(saved_query.statement),
EXISTS (SELECT 1 FROM saved_query_star WHERE saved_query_star.saved_query = saved_query.resource_id AND saved_query_star.principal = ?)
FROM saved_query
WHERE TRUE`, statementField), find.PrincipalEmail)
// The read predicate, pushed into SQL rather than applied to the page
// afterwards: filtering after LIMIT would silently return short pages.
if find.AccessMembers != nil {
access := qb.Q().Space("saved_query.creator = ?", find.PrincipalEmail)
for _, member := range find.AccessMembers {
probe, err := bindingProbe(member)
if err != nil {
return nil, err
}
access.Or("saved_query.bindings @> ?", probe)
}
q.And("(?)", access)
}
if find.Workspace != "" {
q.And("EXISTS (SELECT 1 FROM project WHERE project.resource_id = saved_query.project AND project.workspace = ? AND project.deleted = FALSE)", find.Workspace)
}
if len(find.ProjectIDs) == 1 {
q.And("saved_query.project = ?", find.ProjectIDs[0])
} else if len(find.ProjectIDs) > 1 {
q.And("saved_query.project = ANY(?)", find.ProjectIDs)
}
if filterQ := find.FilterQ; filterQ != nil {
q.And("?", filterQ)
}
if v := find.ResourceID; v != nil {
q.And("saved_query.resource_id = ?", *v)
}
// Default title order; List overrides via order_by. The resource_id
// tiebreak keeps pages stable and follows the last key's direction so
// "update_time desc" matches the
// (creator, updated_at DESC, resource_id DESC) index exactly.
keys := find.OrderByKeys
if len(keys) == 0 {
keys = []*OrderByKey{{Key: "saved_query.name", SortOrder: ASC}}
}
orderBy := []string{}
for _, v := range keys {
orderBy = append(orderBy, fmt.Sprintf("%s %s", v.Key, v.SortOrder.String()))
}
last := keys[len(keys)-1]
orderBy = append(orderBy, fmt.Sprintf("saved_query.resource_id %s", last.SortOrder.String()))
q.Space(fmt.Sprintf("ORDER BY %s", strings.Join(orderBy, ", ")))
if v := find.Limit; v != nil {
q.Space("LIMIT ?", *v)
}
if v := find.Offset; v != nil {
q.Space("OFFSET ?", *v)
}
query, args, err := q.ToSQL()
if err != nil {
return nil, errors.Wrapf(err, "failed to build sql")
}
rows, err := s.GetDB().QueryContext(ctx, query, args...)
if err != nil {
return nil, err
}
defer rows.Close()
var sheets []*SavedQueryMessage
for rows.Next() {
var sheet SavedQueryMessage
var payloadBytes, bindingsBytes []byte
if err := rows.Scan(
&sheet.ResourceID,
&sheet.Creator,
&sheet.CreatedAt,
&sheet.UpdatedAt,
&sheet.ProjectID,
&payloadBytes,
&sheet.Folder,
&sheet.Title,
&bindingsBytes,
&sheet.Statement,
&sheet.Size,
&sheet.Starred,
); err != nil {
return nil, err
}
var payload storepb.SavedQueryPayload
if err := common.ProtojsonUnmarshaler.Unmarshal(payloadBytes, &payload); err != nil {
return nil, errors.Wrapf(err, "failed to unmarshal saved query payload")
}
sheet.Database = payload.Database
bindings, err := unmarshalSavedQueryBindings(bindingsBytes)
if err != nil {
return nil, err
}
sheet.Bindings = bindings
sheets = append(sheets, &sheet)
}
if err := rows.Err(); err != nil {
return nil, err
}
return sheets, nil
}
// bindingProbe builds the jsonb containment operand that matches any binding
// naming this principal, e.g. `[{"members":["users/[email protected]"]}]` — the
// stored member form. `@>` on a jsonb array is "contains an element
// containing this", so one probe per principal, BitmapOr'd by the planner,
// answers "shared with me".
//
// The probe is level-blind on purpose: every current level's bundle grants
// get (bindingGrants in api/v1/saved_query_service.go). A future level that
// does not grant get must add a level term here, or Search would return
// previews GetSavedQuery denies.
func bindingProbe(member string) (string, error) {
probe, err := json.Marshal([]map[string]any{{"members": []string{member}}})
if err != nil {
return "", errors.Wrapf(err, "failed to build binding probe for %q", member)
}
return string(probe), nil
}
// marshalSavedQueryBindings writes the bindings as a protojson array at the
// jsonb root. protojson has no top-level array form, so each element is
// marshalled on its own and the array assembled here; the shape is pinned
// because the GIN containment probes above depend on it.
func marshalSavedQueryBindings(bindings []*storepb.SavedQueryBinding) (string, error) {
elements := make([]json.RawMessage, 0, len(bindings))
for _, binding := range bindings {
element, err := protojson.Marshal(binding)
if err != nil {
return "", errors.Wrapf(err, "failed to marshal saved query binding")
}
elements = append(elements, element)
}
out, err := json.Marshal(elements)
if err != nil {
return "", errors.Wrapf(err, "failed to marshal saved query bindings")
}
return string(out), nil
}
func unmarshalSavedQueryBindings(b []byte) ([]*storepb.SavedQueryBinding, error) {
if len(b) == 0 {
return nil, nil
}
var elements []json.RawMessage
if err := json.Unmarshal(b, &elements); err != nil {
return nil, errors.Wrapf(err, "failed to unmarshal saved query bindings")
}
bindings := make([]*storepb.SavedQueryBinding, 0, len(elements))
for _, element := range elements {
var binding storepb.SavedQueryBinding
if err := common.ProtojsonUnmarshaler.Unmarshal(element, &binding); err != nil {
return nil, errors.Wrapf(err, "failed to unmarshal saved query binding")
}
bindings = append(bindings, &binding)
}
return bindings, nil
}
// SavedQueryPolicyEtag derives the etag from the stored bindings themselves,
// so no column has to be kept in step with them. Two policies with the same
// grants produce the same etag, which is what compare-and-swap needs: a write
// is rejected only when the grants actually moved.
func SavedQueryPolicyEtag(bindings []*storepb.SavedQueryBinding) (string, error) {
marshalled, err := marshalSavedQueryBindings(bindings)
if err != nil {
return "", err
}
sum := sha256.Sum256([]byte(marshalled))
return hex.EncodeToString(sum[:]), nil
}
// SetSavedQueryBindings replaces a saved query's grants under compare-and-swap.
// The row is locked and its current etag compared before the write, so a
// full-replacement write can never silently undo a concurrent revocation.
// Returns ErrSavedQueryEtagMismatch when the caller's etag is stale, and
// reports false when the saved query is gone from the named project -- deleted,
// or re-parented to the default project by a purge.
func (s *Store) SetSavedQueryBindings(ctx context.Context, projectID, resourceID string, bindings []*storepb.SavedQueryBinding, expectedEtag string) (bool, error) {
marshalled, err := marshalSavedQueryBindings(bindings)
if err != nil {
return false, err
}
tx, err := s.GetDB().BeginTx(ctx, nil)
if err != nil {
return false, errors.Wrap(err, "failed to begin transaction")
}
defer tx.Rollback()
// Scoped to the project the caller named, not the saved query's global id.
// A project purge re-parents its members' saved queries to the default
// project, so a write resolved against the old project must land on
// nothing rather than on a row that has since moved out from under it.
var currentBytes []byte
if err := tx.QueryRowContext(ctx,
`SELECT bindings FROM saved_query WHERE resource_id = $1 AND project = $2 FOR UPDATE`,
resourceID, projectID).Scan(¤tBytes); err != nil {
if errors.Is(err, sql.ErrNoRows) {
return false, nil
}
return false, errors.Wrapf(err, "failed to lock saved query %s", resourceID)
}
current, err := unmarshalSavedQueryBindings(currentBytes)
if err != nil {
return false, err
}
currentEtag, err := SavedQueryPolicyEtag(current)
if err != nil {
return false, err
}
if expectedEtag != currentEtag {
return false, ErrSavedQueryEtagMismatch
}
if _, err := tx.ExecContext(ctx,
`UPDATE saved_query SET bindings = $1 WHERE resource_id = $2 AND project = $3`,
marshalled, resourceID, projectID); err != nil {
return false, errors.Wrapf(err, "failed to update bindings for saved query %s", resourceID)
}
if err := tx.Commit(); err != nil {
return false, errors.Wrap(err, "failed to commit transaction")
}
return true, nil
}
// ErrSavedQueryEtagMismatch reports that the policy moved under a
// compare-and-swap write; the caller refetches and reapplies.
var ErrSavedQueryEtagMismatch = errors.New("saved query policy etag mismatch")
// CreateSavedQuery creates a new saved query under an active project.
func (s *Store) CreateSavedQuery(ctx context.Context, create *SavedQueryMessage) (*SavedQueryMessage, error) {
payloadStr, err := protojson.Marshal(&storepb.SavedQueryPayload{Database: create.Database})
if err != nil {
return nil, err
}
tx, err := s.GetDB().BeginTx(ctx, nil)
if err != nil {
return nil, errors.Wrap(err, "failed to begin transaction")
}
defer tx.Rollback()
// A saved query is a new child row, so its project must be active.
if err := requireActiveProject(ctx, tx, create.ProjectID); err != nil {
return nil, err
}
query, args, err := qb.Q().Space(`
INSERT INTO saved_query (
creator,
project,
name,
statement,
folder,
payload
)
VALUES (?, ?, ?, ?, ?, ?)
RETURNING resource_id, created_at, updated_at, OCTET_LENGTH(statement)
`, create.Creator, create.ProjectID, create.Title, create.Statement, create.Folder, payloadStr).ToSQL()
if err != nil {
return nil, errors.Wrapf(err, "failed to build sql")
}
if err := tx.QueryRowContext(ctx, query, args...).Scan(
&create.ResourceID,
&create.CreatedAt,
&create.UpdatedAt,
&create.Size,
); err != nil {
if err == sql.ErrNoRows {
return nil, common.FormatDBErrorEmptyRowWithQuery(query)
}
return nil, err
}
if err := tx.Commit(); err != nil {
return nil, errors.Wrap(err, "failed to commit transaction")
}
return create, nil
}
// PatchSavedQuery updates a sheet.
func (s *Store) PatchSavedQuery(ctx context.Context, patch *PatchSavedQueryMessage) error {
set := qb.Q()
// The DB clock, not the app clock: updated_at is an order_by sort key,
// and creation stamps it with DEFAULT now(), so a skewed app server
// must not interleave edits out of true sequence.
set.Comma("updated_at = now()")
if v := patch.Title; v != nil {
set.Comma("name = ?", *v)
}
if v := patch.Statement; v != nil {
set.Comma("statement = ?", *v)
}
if v := patch.Folder; v != nil {
set.Comma("folder = ?", *v)
}
if v := patch.Database; v != nil {
if *v == "" {
set.Comma("payload = payload - 'database'")
} else {
set.Comma("payload = payload || jsonb_build_object('database', ?::text)", *v)
}
}
query, args, err := qb.Q().Space("UPDATE saved_query SET ? WHERE resource_id = ? AND project = ?", set, patch.ResourceID, patch.ProjectID).ToSQL()
if err != nil {
return errors.Wrapf(err, "failed to build sql")
}
if _, err := s.GetDB().ExecContext(ctx, query, args...); err != nil {
return err
}
return nil
}
// DeleteSavedQuery deletes an existing saved query and reports whether it was
// still there to delete. Star rows are deleted first, in full primary-key
// order — explicitly, not via the FK cascade (which would lock the parent
// first) — so a delete racing a star toggle cannot deadlock.
//
// Both statements scope by the project the caller was authorized in. The row
// delete is the arbiter — when it matches nothing, the transaction rolls back,
// which also restores any stars the first statement removed, and the caller
// gets false to answer NotFound.
//
// Writers on existing rows — delete, patch, star — require the row in the
// authorized project, not an active project. Only creation requires an active
// project.
func (s *Store) DeleteSavedQuery(ctx context.Context, projectID, resourceID string) (bool, error) {
tx, err := s.GetDB().BeginTx(ctx, nil)
if err != nil {
return false, errors.Wrap(err, "failed to begin transaction")
}
defer tx.Rollback()
if _, err := tx.ExecContext(ctx, `
DELETE FROM saved_query_star
WHERE (saved_query, principal) IN (
SELECT saved_query, principal
FROM saved_query_star
WHERE saved_query = $1
AND EXISTS (
SELECT 1 FROM saved_query
WHERE resource_id = $1 AND project = $2
)
ORDER BY saved_query, principal
FOR UPDATE
)
`, resourceID, projectID); err != nil {
return false, errors.Wrapf(err, "failed to delete stars for saved query %s", resourceID)
}
result, err := tx.ExecContext(ctx, `DELETE FROM saved_query WHERE resource_id = $1 AND project = $2`, resourceID, projectID)
if err != nil {
return false, err
}
deleted, err := result.RowsAffected()
if err != nil {
return false, errors.Wrap(err, "failed to count deleted saved queries")
}
if deleted == 0 {
return false, nil
}
return true, tx.Commit()
}
// SetSavedQueryStar stars or unstars a saved query for a principal, and
// reports whether the saved query was still there to star. Toggling or removing
// an existing star locks that row directly. The first star instead locks the
// saved query row before inserting, so it serializes with deletion and verifies
// that the query still belongs to the project the caller was authorized in.
// Removing an existing star stays project-blind: the star is the caller's own
// marker, removable wherever its row went.
func (s *Store) SetSavedQueryStar(ctx context.Context, projectID, savedQueryResourceID, principal string, starred bool) (bool, error) {
tx, err := s.GetDB().BeginTx(ctx, nil)
if err != nil {
return false, errors.Wrap(err, "failed to begin transaction")
}
defer tx.Rollback()
var exists bool
if err := tx.QueryRowContext(ctx, `
SELECT EXISTS (
SELECT 1
FROM saved_query_star
WHERE saved_query = $1 AND principal = $2
FOR UPDATE
)
`, savedQueryResourceID, principal).Scan(&exists); err != nil {
return false, errors.Wrap(err, "failed to lock star row")
}
switch {
case starred && !exists:
var one int
if err := tx.QueryRowContext(ctx, `
SELECT 1
FROM saved_query
WHERE resource_id = $1 AND project = $2
FOR UPDATE
`, savedQueryResourceID, projectID).Scan(&one); err != nil {
if errors.Is(err, sql.ErrNoRows) {
return false, nil
}
return false, errors.Wrapf(err, "failed to lock saved query %s", savedQueryResourceID)
}
if _, err := tx.ExecContext(ctx, `
INSERT INTO saved_query_star (saved_query, principal)
VALUES ($1, $2)
ON CONFLICT (saved_query, principal) DO NOTHING
`, savedQueryResourceID, principal); err != nil {
return false, errors.Wrap(err, "failed to insert star")
}
case !starred && exists:
if _, err := tx.ExecContext(ctx, `
DELETE FROM saved_query_star
WHERE saved_query = $1 AND principal = $2
`, savedQueryResourceID, principal); err != nil {
return false, errors.Wrap(err, "failed to delete star")
}
default:
// Already in the requested state: an unstar of a row that is gone
// needs no parent, so it is reported as applied.
}
if err := tx.Commit(); err != nil {
return false, err
}
return true, nil
}
// BatchUpdateSavedQueryFolder re-files the given saved queries into folder.
// MoveSavedQueryFolder rewrites the folder prefix for one creator's saved
// queries: moving "a/b" to "a/c" also moves "a/b/deep" to "a/c/deep". The
// suffix keeps each descendant's own tail, and ltrim drops the separator when
// the target is empty so a row is unfiled rather than left at "/deep".
//
// The CTE takes every row lock in full primary-key order (resource_id) before
// the update touches anything -- the store's batch rule, so two overlapping
// moves serialize instead of deadlocking -- and one statement needs no
// explicit transaction. A row deleted mid-move simply updates zero rows.
func (s *Store) MoveSavedQueryFolder(ctx context.Context, projectID, creator, source, target string) (int, error) {
if source == "" {
return 0, errors.New("source folder cannot be empty")
}
result, err := s.GetDB().ExecContext(ctx, `
WITH locked AS (
SELECT resource_id FROM saved_query
WHERE project = $1 AND creator = $2 AND (folder = $3 OR folder LIKE $4)
ORDER BY resource_id
FOR UPDATE
)
UPDATE saved_query
SET folder = ltrim($5 || substring(folder from length($3) + 1), '/'),
updated_at = now()
WHERE resource_id IN (SELECT resource_id FROM locked)
`, projectID, creator, source, escapeLikePattern(source)+"/%", target)
if err != nil {
return 0, errors.Wrap(err, "failed to move saved query folder")
}
moved, err := result.RowsAffected()
if err != nil {
return 0, errors.Wrap(err, "failed to count moved saved queries")
}
return int(moved), nil
}
// ListSavedQueryFolderPaths returns the distinct folder paths of the saved
// queries a caller can read, narrowed by filterQ. Access is the same predicate
// SearchSavedQueries applies (nil accessMembers skips the clause, for a
// caller with project-level bb.savedQueries.get), and for the same reason: a
// folder is only useful if its contents are reachable. Scoping these to the
// caller's *own* rows instead would hide a shared saved query filed by its
// creator — the tree is seeded from here, and a row whose folder has no node
// can never be expanded into. workspaceID fences the project to the caller's
// workspace and to active projects, exactly as the List query does.
func (s *Store) ListSavedQueryFolderPaths(ctx context.Context, workspaceID string, projectID string, principalEmail string, accessMembers []string, filterQ *qb.Query) ([]string, error) {
q := qb.Q().Space(`
SELECT DISTINCT folder
FROM saved_query
WHERE TRUE`)
q.And("saved_query.project = ?", projectID)
q.And("EXISTS (SELECT 1 FROM project WHERE project.resource_id = saved_query.project AND project.workspace = ? AND project.deleted = FALSE)", workspaceID)
q.And("saved_query.folder <> ''")
if accessMembers != nil {
access := qb.Q().Space("saved_query.creator = ?", principalEmail)
for _, member := range accessMembers {
probe, err := bindingProbe(member)
if err != nil {
return nil, err
}
access.Or("saved_query.bindings @> ?", probe)
}
q.And("(?)", access)
}
if filterQ != nil {
q.And("?", filterQ)
}
q.Space("ORDER BY folder")
query, args, err := q.ToSQL()
if err != nil {
return nil, errors.Wrapf(err, "failed to build sql")
}
rows, err := s.GetDB().QueryContext(ctx, query, args...)
if err != nil {
return nil, err
}
defer rows.Close()
var folders []string
for rows.Next() {
var folder string
if err := rows.Scan(&folder); err != nil {
return nil, err
}
folders = append(folders, folder)
}
if err := rows.Err(); err != nil {
return nil, err
}
return folders, nil
}
// NormalizeSavedQueryFolder puts a client-supplied folder path into the one
// form that is stored and matched. Boundary slashes are trimmed, so "/team/"
// and "team" name the same folder, and an empty path segment is rejected
// rather than stored: every write path and the `folder ==` filter share this
// function, so a folder that can be stored is always a folder that can be
// found. "" means unfiled.
func NormalizeSavedQueryFolder(folder string) (string, error) {
normalized := strings.Trim(folder, "/")
if strings.Contains(normalized, "//") {
return "", errors.Errorf("invalid folder %q: empty path segment", folder)
}
return normalized, nil
}
func GetSearchSavedQueryFilter(ctx context.Context, s *Store, caller string, accessMembers []string, filter string, allowTitleContains bool) (*qb.Query, error) {
if filter == "" {
return nil, nil
}
ast, err := common.ParseCELFilter(filter)
if err != nil {
return nil, err
}
var getFilter func(expr celast.Expr) (*qb.Query, error)
getUserID := func(name string) (string, error) {
creatorEmail := strings.TrimPrefix(name, "users/")
if creatorEmail == "" {
return "", errors.New("invalid empty creator identifier")
}
user, err := s.GetUserByEmail(ctx, creatorEmail)
if err != nil {
return "", errors.Errorf("failed to get user: %v", err)
}
if user == nil {
return "", errors.Errorf("user with email %s not found", creatorEmail)
}
return user.Email, nil
}
getSavedQueryID := func(name string) (string, error) {
_, savedQueryID, err := common.GetProjectIDSavedQueryID(name)
if err != nil {
return "", errors.Errorf("invalid saved query name %q", name)
}
return savedQueryID, nil
}
parseToSQL := func(variable, value any) (*qb.Query, error) {
switch variable {
case "name":
name, ok := value.(string)
if !ok {
return nil, errors.Errorf("invalid name value %q", value)
}
savedQueryID, err := getSavedQueryID(name)
if err != nil {
return nil, err
}
return qb.Q().Space("saved_query.resource_id = ?", savedQueryID), nil
case "creator":
creator, ok := value.(string)
if !ok {
return nil, errors.Errorf("invalid creator value %v, expect a string", value)
}
userID, err := getUserID(creator)
if err != nil {
return nil, err
}
return qb.Q().Space("saved_query.creator = ?", userID), nil
case "starred":
if starred, ok := value.(bool); ok {
if starred {
return qb.Q().Space("EXISTS (SELECT 1 FROM saved_query_star WHERE saved_query_star.saved_query = saved_query.resource_id AND saved_query_star.principal = ?)", caller), nil
}
return qb.Q().Space("NOT EXISTS (SELECT 1 FROM saved_query_star WHERE saved_query_star.saved_query = saved_query.resource_id AND saved_query_star.principal = ?)", caller), nil
}
return nil, errors.Errorf("invalid starred value %v, expect true or false", value)
case "shared":
// Reached through a binding, which is narrower than "somebody else
// created it": a project-level bb.savedQueries.get holder sees
// saved queries nobody shared with them, and those must not show
// up in a Shared view.
shared, ok := value.(bool)
if !ok {
return nil, errors.Errorf("invalid shared value %v, expect true or false", value)
}
granted := qb.Q().Space("FALSE")
for _, principal := range accessMembers {
probe, err := bindingProbe(principal)
if err != nil {
return nil, err
}
granted.Or("saved_query.bindings @> ?", probe)
}
if shared {
return qb.Q().Space("(saved_query.creator <> ? AND (?))", caller, granted), nil
}
return qb.Q().Space("(NOT (saved_query.creator <> ? AND (?)))", caller, granted), nil
case "folder":
folder, ok := value.(string)
if !ok {
return nil, errors.Errorf("invalid folder value %q", value)
}
folder, err := NormalizeSavedQueryFolder(folder)
if err != nil {
return nil, err
}
return qb.Q().Space("saved_query.folder = ?", folder), nil
default:
return nil, errors.Errorf("unsupport variable %q", variable)
}
}
getFilter = func(expr celast.Expr) (*qb.Query, error) {
q := qb.Q()
switch expr.Kind() {
case celast.CallKind:
functionName := expr.AsCall().FunctionName()
switch functionName {
case celoperators.LogicalOr:
for _, arg := range expr.AsCall().Args() {
qq, err := getFilter(arg)
if err != nil {
return nil, err
}
q.Or("?", qq)
}
return qb.Q().Space("(?)", q), nil
case celoperators.LogicalAnd:
for _, arg := range expr.AsCall().Args() {
qq, err := getFilter(arg)
if err != nil {
return nil, err
}
q.And("?", qq)
}
return qb.Q().Space("(?)", q), nil
case celoperators.Equals:
variable, value := getVariableAndValueFromExpr(expr)
return parseToSQL(variable, value)
case celoverloads.Contains:
if expr.AsCall().Target().Kind() != celast.IdentKind {
return nil, errors.Errorf(`invalid args for %q`, celoverloads.Contains)
}
variable := expr.AsCall().Target().AsIdent()
args := expr.AsCall().Args()
if len(args) != 1 || args[0].Kind() != celast.LiteralKind {
return nil, errors.Errorf(`invalid args for %q`, variable)
}
value := args[0].AsLiteral().Value()
strValue, ok := value.(string)
if !ok {
return nil, errors.Errorf("expect string, got %T, hint: filter literals should be string", value)
}
if strValue == "" {
return nil, errors.Errorf(`empty value for %q`, variable)
}
switch variable {
case "title":
if !allowTitleContains {
return nil, errors.Errorf("unsupport variable %q", variable)
}
return qb.Q().Space("LOWER(saved_query.name) LIKE ? ESCAPE '\\'", containsPattern(strings.ToLower(strValue))), nil
default:
return nil, errors.Errorf("unsupport variable %q", variable)
}
case celoperators.NotEquals:
variable, value := getVariableAndValueFromExpr(expr)
if variable != "creator" {
return nil, errors.Errorf(`only "creator" support "!=" operator`)
}
creator, ok := value.(string)
if !ok {
return nil, errors.Errorf("invalid creator value %v, expect a string", value)
}
userID, err := getUserID(creator)
if err != nil {
return nil, err
}
return qb.Q().Space("saved_query.creator != ?", userID), nil
case celoperators.In:
variable, value := getVariableAndValueFromExpr(expr)
rawList, ok := value.([]any)
if !ok {
return nil, errors.Errorf("invalid %s value %q", variable, value)
}
if len(rawList) == 0 {
return nil, errors.Errorf("empty %s filter", variable)
}
switch variable {
case "name":
savedQueryIDs := []string{}
for _, raw := range rawList {
name, ok := raw.(string)
if !ok {
return nil, errors.Errorf("invalid name value %q", raw)
}
savedQueryID, err := getSavedQueryID(name)
if err != nil {
return nil, err
}
savedQueryIDs = append(savedQueryIDs, savedQueryID)
}
return qb.Q().Space("saved_query.resource_id = ANY(?)", savedQueryIDs), nil
default:
return nil, errors.Errorf(`only "name" supports "in" filter`)
}
default:
return nil, errors.Errorf("unexpected function %v", functionName)
}
default:
return nil, errors.Errorf("unexpected expr kind %v", expr.Kind())
}
}
q, err := getFilter(ast.NativeRep().Expr())
if err != nil {
return nil, err
}
return qb.Q().Space("(?)", q), nil
}
// GetSavedQueryOrders parses an AIP-132 order_by string into OrderByKey
// entries for ListSavedQueries. Supported fields: update_time, create_time,
// title.
func GetSavedQueryOrders(orderBy string) ([]*OrderByKey, error) {
return getOrderByKeys(orderBy, map[string]string{
"update_time": "saved_query.updated_at",
"create_time": "saved_query.created_at",
"title": "saved_query.name",
})
}
func GetListSavedQueryFilter(filter string) (*qb.Query, error) {
if filter == "" {
return nil, nil
}
ast, err := common.ParseCELFilter(filter)
if err != nil {
return nil, err
}
var getFilter func(expr celast.Expr) (*qb.Query, error)
getFilter = func(expr celast.Expr) (*qb.Query, error) {
if expr.Kind() != celast.CallKind {
return nil, errors.Errorf("unexpected expr kind %v", expr.Kind())
}
q := qb.Q()
functionName := expr.AsCall().FunctionName()
switch functionName {
case celoperators.LogicalOr:
for _, arg := range expr.AsCall().Args() {
qq, err := getFilter(arg)
if err != nil {
return nil, err
}
q.Or("?", qq)
}
return qb.Q().Space("(?)", q), nil
case celoperators.LogicalAnd:
for _, arg := range expr.AsCall().Args() {
qq, err := getFilter(arg)
if err != nil {
return nil, err
}
q.And("?", qq)
}
return qb.Q().Space("(?)", q), nil
case celoperators.Equals, celoperators.NotEquals:
variable, value := getVariableAndValueFromExpr(expr)
if variable != "creator" {
return nil, errors.Errorf("unsupported variable %q", variable)
}
creator, ok := value.(string)
if !ok {
return nil, errors.Errorf("invalid creator value %q", value)
}
creatorEmail := strings.TrimPrefix(creator, "users/")
if creatorEmail == "" {
return nil, errors.New("invalid empty creator identifier")
}
if functionName == celoperators.Equals {
return qb.Q().Space("saved_query.creator = ?", creatorEmail), nil
}
return qb.Q().Space("saved_query.creator != ?", creatorEmail), nil
default:
return nil, errors.Errorf("unexpected function %v", functionName)
}
}
q, err := getFilter(ast.NativeRep().Expr())
if err != nil {
return nil, err
}
return qb.Q().Space("(?)", q), nil
}