github.com/authzed/spicedb@v1.32.1-0.20240520085336-ebda56537386/internal/datastore/mysql/query_builder.go (about)

     1  package mysql
     2  
     3  import (
     4  	"github.com/authzed/spicedb/internal/datastore/mysql/migrations"
     5  
     6  	sq "github.com/Masterminds/squirrel"
     7  )
     8  
     9  // QueryBuilder captures all parameterizable queries used
    10  // by the MySQL datastore implementation
    11  type QueryBuilder struct {
    12  	GetLastRevision  sq.SelectBuilder
    13  	GetRevisionRange sq.SelectBuilder
    14  
    15  	WriteNamespaceQuery        sq.InsertBuilder
    16  	ReadNamespaceQuery         sq.SelectBuilder
    17  	DeleteNamespaceQuery       sq.UpdateBuilder
    18  	DeleteNamespaceTuplesQuery sq.UpdateBuilder
    19  
    20  	QueryTuplesWithIdsQuery sq.SelectBuilder
    21  	QueryTuplesQuery        sq.SelectBuilder
    22  	DeleteTupleQuery        sq.UpdateBuilder
    23  	QueryTupleExistsQuery   sq.SelectBuilder
    24  	WriteTupleQuery         sq.InsertBuilder
    25  	QueryChangedQuery       sq.SelectBuilder
    26  	CountTupleQuery         sq.SelectBuilder
    27  
    28  	WriteCaveatQuery  sq.InsertBuilder
    29  	ReadCaveatQuery   sq.SelectBuilder
    30  	ListCaveatsQuery  sq.SelectBuilder
    31  	DeleteCaveatQuery sq.UpdateBuilder
    32  }
    33  
    34  // NewQueryBuilder returns a new QueryBuilder instance. The migration
    35  // driver is used to determine the names of the tables.
    36  func NewQueryBuilder(driver *migrations.MySQLDriver) *QueryBuilder {
    37  	builder := QueryBuilder{}
    38  
    39  	// transaction builders
    40  	builder.GetLastRevision = getLastRevision(driver.RelationTupleTransaction())
    41  	builder.GetRevisionRange = getRevisionRange(driver.RelationTupleTransaction())
    42  
    43  	// namespace builders
    44  	builder.WriteNamespaceQuery = writeNamespace(driver.Namespace())
    45  	builder.ReadNamespaceQuery = readNamespace(driver.Namespace())
    46  	builder.DeleteNamespaceQuery = deleteNamespace(driver.Namespace())
    47  
    48  	// tuple builders
    49  	builder.QueryTuplesWithIdsQuery = queryTuplesWithIds(driver.RelationTuple())
    50  	builder.DeleteNamespaceTuplesQuery = deleteNamespaceTuples(driver.RelationTuple())
    51  	builder.QueryTuplesQuery = queryTuples(driver.RelationTuple())
    52  	builder.DeleteTupleQuery = deleteTuple(driver.RelationTuple())
    53  	builder.QueryTupleExistsQuery = queryTupleExists(driver.RelationTuple())
    54  	builder.WriteTupleQuery = writeTuple(driver.RelationTuple())
    55  	builder.QueryChangedQuery = queryChanged(driver.RelationTuple())
    56  	builder.CountTupleQuery = countTuples(driver.RelationTuple())
    57  
    58  	// caveat builders
    59  	builder.ReadCaveatQuery = readCaveat(driver.Caveat())
    60  	builder.ListCaveatsQuery = listCaveats(driver.Caveat())
    61  	builder.WriteCaveatQuery = writeCaveat(driver.Caveat())
    62  	builder.DeleteCaveatQuery = deleteCaveat(driver.Caveat())
    63  
    64  	return &builder
    65  }
    66  
    67  func listCaveats(tableCaveat string) sq.SelectBuilder {
    68  	return sb.Select(colCaveatDefinition, colCreatedTxn).From(tableCaveat).OrderBy(colName)
    69  }
    70  
    71  func deleteCaveat(tableCaveat string) sq.UpdateBuilder {
    72  	return sb.Update(tableCaveat).Where(sq.Eq{colDeletedTxn: liveDeletedTxnID})
    73  }
    74  
    75  func writeCaveat(tableCaveat string) sq.InsertBuilder {
    76  	return sb.Insert(tableCaveat).Columns(
    77  		colName,
    78  		colCaveatDefinition,
    79  		colCreatedTxn,
    80  	)
    81  }
    82  
    83  func readCaveat(tableCaveat string) sq.SelectBuilder {
    84  	return sb.Select(colCaveatDefinition, colCreatedTxn).From(tableCaveat)
    85  }
    86  
    87  func getLastRevision(tableTransaction string) sq.SelectBuilder {
    88  	return sb.Select("MAX(id)").From(tableTransaction).Limit(1)
    89  }
    90  
    91  func getRevisionRange(tableTransaction string) sq.SelectBuilder {
    92  	return sb.Select("MIN(id)", "MAX(id)").From(tableTransaction)
    93  }
    94  
    95  func writeNamespace(tableNamespace string) sq.InsertBuilder {
    96  	return sb.Insert(tableNamespace).Columns(
    97  		colNamespace,
    98  		colConfig,
    99  		colCreatedTxn,
   100  	)
   101  }
   102  
   103  func readNamespace(tableNamespace string) sq.SelectBuilder {
   104  	return sb.Select(colConfig, colCreatedTxn).From(tableNamespace)
   105  }
   106  
   107  func deleteNamespace(tableNamespace string) sq.UpdateBuilder {
   108  	return sb.Update(tableNamespace).Where(sq.Eq{colDeletedTxn: liveDeletedTxnID})
   109  }
   110  
   111  func deleteNamespaceTuples(tableTuple string) sq.UpdateBuilder {
   112  	return sb.Update(tableTuple).Where(sq.Eq{colDeletedTxn: liveDeletedTxnID})
   113  }
   114  
   115  func queryTuplesWithIds(tableTuple string) sq.SelectBuilder {
   116  	return sb.Select(
   117  		colID,
   118  		colNamespace,
   119  		colObjectID,
   120  		colRelation,
   121  		colUsersetNamespace,
   122  		colUsersetObjectID,
   123  		colUsersetRelation,
   124  		colCaveatName,
   125  		colCaveatContext,
   126  	).From(tableTuple)
   127  }
   128  
   129  func queryTuples(tableTuple string) sq.SelectBuilder {
   130  	return sb.Select(
   131  		colNamespace,
   132  		colObjectID,
   133  		colRelation,
   134  		colUsersetNamespace,
   135  		colUsersetObjectID,
   136  		colUsersetRelation,
   137  		colCaveatName,
   138  		colCaveatContext,
   139  	).From(tableTuple)
   140  }
   141  
   142  func countTuples(tableTuple string) sq.SelectBuilder {
   143  	return sb.Select(
   144  		"count(*)",
   145  	).From(tableTuple)
   146  }
   147  
   148  func deleteTuple(tableTuple string) sq.UpdateBuilder {
   149  	return sb.Update(tableTuple).Where(sq.Eq{colDeletedTxn: liveDeletedTxnID})
   150  }
   151  
   152  func queryTupleExists(tableTuple string) sq.SelectBuilder {
   153  	return sb.Select(colID).From(tableTuple)
   154  }
   155  
   156  func writeTuple(tableTuple string) sq.InsertBuilder {
   157  	return sb.Insert(tableTuple).Columns(
   158  		colNamespace,
   159  		colObjectID,
   160  		colRelation,
   161  		colUsersetNamespace,
   162  		colUsersetObjectID,
   163  		colUsersetRelation,
   164  		colCaveatName,
   165  		colCaveatContext,
   166  		colCreatedTxn,
   167  	)
   168  }
   169  
   170  func queryChanged(tableTuple string) sq.SelectBuilder {
   171  	return sb.Select(
   172  		colNamespace,
   173  		colObjectID,
   174  		colRelation,
   175  		colUsersetNamespace,
   176  		colUsersetObjectID,
   177  		colUsersetRelation,
   178  		colCaveatName,
   179  		colCaveatContext,
   180  		colCreatedTxn,
   181  		colDeletedTxn,
   182  	).From(tableTuple)
   183  }