github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/kv/kvserver/protectedts/ptstorage/sql.go (about)

     1  // Copyright 2019 The Cockroach Authors.
     2  //
     3  // Use of this software is governed by the Business Source License
     4  // included in the file licenses/BSL.txt.
     5  //
     6  // As of the Change Date specified in that file, in accordance with
     7  // the Business Source License, use of this software will be governed
     8  // by the Apache License, Version 2.0, included in the file
     9  // licenses/APL.txt.
    10  
    11  package ptstorage
    12  
    13  const (
    14  
    15  	// currentMetaCTE is used by all queries which access the meta row.
    16  	// The query returns a default row if there currently is no meta row.
    17  	// At the time of writing, there will never be a physical row in the meta
    18  	// table with version zero.
    19  	currentMetaCTE = `
    20  SELECT
    21      version, num_records, num_spans, total_bytes
    22  FROM
    23      system.protected_ts_meta
    24  UNION ALL
    25      SELECT 0 AS version, 0 AS num_records, 0 AS num_spans, 0 AS total_bytes
    26  ORDER BY
    27      version DESC
    28  LIMIT
    29      1
    30  `
    31  
    32  	protectQuery = `
    33  WITH
    34      current_meta AS (` + currentMetaCTE + `),
    35      checks AS (` + protectChecksCTE + `),
    36      updated_meta AS (` + protectUpsertMetaCTE + `),
    37      new_record AS (` + protectInsertRecordCTE + `)
    38  SELECT
    39      failed,
    40      num_spans AS prev_spans,
    41      total_bytes AS prev_total_bytes,
    42      version AS prev_version
    43  FROM
    44      checks, current_meta;`
    45  
    46  	protectChecksCTE = `
    47  SELECT
    48      new_version, 
    49      new_num_records,
    50      new_num_spans, 
    51      new_total_bytes,
    52      (
    53         new_num_spans > $1
    54         OR new_total_bytes > $2
    55         OR EXISTS(SELECT * FROM system.protected_ts_records WHERE id = $4)
    56      ) AS failed
    57  FROM (
    58      SELECT
    59          version + 1 AS new_version,
    60          num_records + 1 AS new_num_records, 
    61          num_spans + $3 AS new_num_spans, 
    62          total_bytes + length($9) + length($6) + coalesce(length($7:::BYTES),0) AS new_total_bytes
    63      FROM
    64          current_meta
    65  )
    66  `
    67  
    68  	protectUpsertMetaCTE = `
    69  UPSERT
    70  INTO
    71      system.protected_ts_meta
    72  (version, num_records, num_spans, total_bytes)
    73  (
    74      SELECT
    75          new_version, new_num_records, new_num_spans, new_total_bytes
    76      FROM
    77          checks
    78      WHERE
    79          NOT failed
    80  )
    81  RETURNING
    82      version, num_records, num_spans, total_bytes
    83  `
    84  
    85  	protectInsertRecordCTE = `
    86  INSERT
    87  INTO
    88      system.protected_ts_records (id, ts, meta_type, meta, num_spans, spans)
    89  (
    90      SELECT
    91          $4, $5, $6, $7, $8, $9
    92      WHERE
    93          NOT EXISTS(SELECT * FROM checks WHERE failed)
    94  )
    95  RETURNING
    96      id
    97  `
    98  
    99  	getRecordsQueryBase = `
   100  SELECT
   101      id, ts, meta_type, meta, spans, verified
   102  FROM
   103      system.protected_ts_records`
   104  
   105  	getRecordsQuery = getRecordsQueryBase + ";"
   106  	getRecordQuery  = getRecordsQueryBase + `
   107  WHERE
   108      id = $1;`
   109  
   110  	markVerifiedQuery = `
   111  UPDATE
   112      system.protected_ts_records
   113  SET
   114      verified = true
   115  WHERE
   116      id = $1
   117  RETURNING
   118      true
   119  `
   120  
   121  	releaseQuery = `
   122  WITH
   123      current_meta AS (` + currentMetaCTE + `),
   124      record AS (` + releaseSelectRecordCTE + `),
   125      updated_meta AS (` + releaseUpsertMetaCTE + `)
   126  DELETE FROM
   127      system.protected_ts_records AS r
   128  WHERE
   129      EXISTS(SELECT NULL FROM record WHERE r.id = record.id)
   130  RETURNING
   131      NULL;`
   132  
   133  	// Collect the number of spans for the record identified by $1.
   134  	releaseSelectRecordCTE = `
   135  SELECT
   136      id,
   137      num_spans AS record_spans,
   138      length(spans) + length(meta_type) + coalesce(length(meta),0) AS record_bytes
   139  FROM
   140      system.protected_ts_records
   141  WHERE
   142      id = $1
   143  `
   144  
   145  	// Updates the meta row if there was a record.
   146  	releaseUpsertMetaCTE = `
   147  UPSERT
   148  INTO
   149      system.protected_ts_meta (version, num_records, num_spans, total_bytes)
   150  (
   151      SELECT
   152          version, num_records, num_spans, total_bytes
   153      FROM
   154          (
   155              SELECT
   156                  version + 1 AS version,
   157                  num_records - 1 AS num_records,
   158                  num_spans - record_spans AS num_spans,
   159                  total_bytes - record_bytes AS total_bytes
   160              FROM
   161                  current_meta RIGHT JOIN record ON true
   162          )
   163  )
   164  RETURNING
   165      1
   166  `
   167  
   168  	getMetadataQuery = `
   169  WITH
   170      current_meta AS (` + currentMetaCTE + `)
   171  SELECT
   172      version, num_records, num_spans, total_bytes
   173  FROM
   174      current_meta;`
   175  )