Uploaded image for project: 'Couchbase Documentation'
  1. Couchbase Documentation
  2. DOC-6236

[MB-35633] N1QL Expiration support

    XMLWordPrintable

Details

    • DOC-2020-S7-Apr19, DOC-2020-S8-May03
    • 1

    Description

      Support expiration through the N1QL.

      • INSERT
      • INSERT INTO SELECT
      • UPSERT
      • UPSERT INTO SELECT
      • UPDATE
      • SELECT
      • MERGE

      By default (Existing/feature)
      INSERT/UPSERT/MERGE-INSERT expiration will be 0 (no expiration).
      UPDATE/MERGE-UPDATE expiration will be 0 (no expiration). If user want preserve existing must set the value again.

      UPDATE default AS d USE KEYS "k02" SET d.comment = "xyz", META(d).expiration = META(d).expiration;

      https://docs.google.com/document/d/10Vt7uRi7FyB-6hstp2OXADHC3UZRhFxE5VACU3a16fs/edit

      Use cases:

       
      		 
      DELETE FROM default USE KEYS ["k01","k01-k01","k02","k02-k02", "k11", "k11-k11", "k12", "k12-k12", "k21", "k22"];
      		 
      INSERT INTO default (KEY, VALUE) VALUES ("k01", {"a": "k01"});
      		 
      INSERT INTO default (KEY, VALUE, OPTIONS) VALUES ("k02", {"a": "k02"}, {"expiration": 3000});
      		 
      INSERT INTO default VALUES ("k11", {"a": "k11"}) RETURNING META().expiration;
      		 
      INSERT INTO default VALUES ("k12", {"a": "k12"}, {"expiration": 5*24*60*60}) RETURNING META().expiration;
      		 
      INSERT INTO default VALUES ("k21", {"a": "k21"}), VALUES ("k22", {"a": "k22"}, {"expiration": 10*24*60*60});
      		 
      INSERT INTO default (KEY id, VALUE doc)  SELECT CONCAT(META(d).id,"-",d.a) AS id, d AS doc FROM default AS d USE KEYS "k01";
      		 
      INSERT INTO default (KEY id, VALUE doc, OPTIONS {expiration})  SELECT CONCAT(META(d).id,"-",d.a) AS id, d AS doc, META(d).expiration FROM default AS d USE KEYS "k02";
      		 
      INSERT INTO default (KEY id)  SELECT CONCAT(META(d).id,"-",d.a) AS id FROM default AS d USE KEYS "k11";
      		 
      INSERT INTO default (KEY id, OPTIONS {expiration})  SELECT CONCAT(META(d).id,"-",d.a) AS id, META(d).expiration FROM default AS d USE KEYS "k12";
      		 
       
      		 
      SELECT META(d).id, META(d).expiration FROM default AS d USE KEYS ["k01","k01-k01","k02","k02-k02", "k11", "k11-k11", "k12", "k12-k12", "k21", "k22"] ORDER BY META(d).id;
      		 
      DELETE FROM default USE KEYS ["k01","k01-k01","k02","k02-k02", "k11", "k11-k11", "k12", "k12-k12", "k21", "k22"];
      		 
       
      		 
       
      		 
      DELETE FROM default USE KEYS ["k01","k01-k01","k02","k02-k02", "k11", "k11-k11", "k12", "k12-k12", "k21", "k22"];
      		 
      UPSERT INTO default (KEY, VALUE) VALUES ("k01", {"a": "k01"});
      		 
      UPSERT INTO default (KEY, VALUE, OPTIONS) VALUES ("k02", {"a": "k02"}, {"expiration": 3000});
      		 
      UPSERT INTO default VALUES ("k11", {"a": "k11"}) RETURNING META().expiration;
      		 
      UPSERT INTO default VALUES ("k12", {"a": "k12"}, {"expiration": 5*24*60*60}) RETURNING META().expiration;
      		 
      UPSERT INTO default VALUES ("k21", {"a": "k21"}), VALUES ("k22", {"a": "k22"}, {"expiration": 10*24*60*60});
      		 
      UPSERT INTO default (KEY id, VALUE doc)  SELECT CONCAT(META(d).id,"-",d.a) AS id, d AS doc FROM default AS d USE KEYS "k01";
      		 
      UPSERT INTO default (KEY id, VALUE doc, OPTIONS {expiration})  SELECT CONCAT(META(d).id,"-",d.a) AS id, d AS doc, META(d).expiration FROM default AS d USE KEYS "k02";
      		 
      UPSERT INTO default (KEY id)  SELECT CONCAT(META(d).id,"-",d.a) AS id FROM default AS d USE KEYS "k11";
      		 
      UPSERT INTO default (KEY id, OPTIONS {expiration})  SELECT CONCAT(META(d).id,"-",d.a) AS id, META(d).expiration FROM default AS d USE KEYS "k12";
      		 
       
      		 
      SELECT META(d).id, META(d).expiration FROM default AS d USE KEYS ["k01","k01-k01","k02","k02-k02", "k11", "k11-k11", "k12", "k12-k12", "k21", "k22"] ORDER BY META(d).id;
      		 
      DELETE FROM default USE KEYS ["k01","k01-k01","k02","k02-k02", "k11", "k11-k11", "k12", "k12-k12", "k21", "k22"];
      		 
       
      		 
       
      		 
      DELETE FROM default USE KEYS ["k02"];
      		 
      INSERT INTO default VALUES ("k02", {"a": "k02"}, {"expiration": 3000});
      		 
      UPDATE default AS d USE KEYS "k02" SET d.comment = "xyz";
      		 
      SELECT META(d).id, META(d).expiration FROM default AS d USE KEYS "k02";
      		 
      UPDATE default AS d USE KEYS "k02" SET META(d).expiration = 24*60*60;
      		 
      SELECT META(d).id, META(d).expiration FROM default AS d USE KEYS "k02";
      		 
      UPDATE default AS d USE KEYS "k02" SET  META(d).expiration = META(d).expiration + 1000 RETURNING META(d).expiration;
      		 
      SELECT META(d).id, META(d).expiration FROM default AS d USE KEYS "k02";
      		 
      UPDATE default AS d USE KEYS "k02" SET META(d).expiration = 0 RETURNING META(d).expiration;
      		 
      SELECT META(d).id, META(d).expiration FROM default AS d USE KEYS "k02";
      		 
      UPDATE default AS d USE KEYS "k02" SET META(d).expiration = 24*60*60;
      		 
      UPDATE default AS d USE KEYS "k02" SET META(d).expiration = 24*60*60, d.xyz = "a" RETURNING META(d).expiration;
      		 
      SELECT META(d).id, META(d).expiration, d.* FROM default AS d USE KEYS "k02";
      		 
      DELETE FROM default USE KEYS ["k02"];
      		 
       
      		 
       
      		 
       
      		 
      DELETE FROM default USE KEYS ['k100', 'k101', 'k50', 'k01', 'k02', 'k03', 'k11', 'k12', 'k13', 'k14'];
      		 
      INSERT INTO default VALUES ("k100", {"ids": ["k01", "k02", "k03", "k11", "k12", "k13", "k14"]}, {"expiration": 2000}), VALUES ("k101", {"ids": ["k50", "k50", "k50", "k50", "k50", "k50", "k50"]}, {"expiration": 5000}), VALUES ("k50", {"a": "k50"});
      		 
       
      		 
      MERGE INTO default AS o USING default AS s USE KEYS ["k100", "k101"] ON KEY s.ids[0] WHEN MATCHED THEN UPDATE SET o.comment = "xyz" WHEN NOT MATCHED THEN INSERT {"a":s.ids[0]} RETURNING META(o).expiration, META(o).id;
      		 
      SELECT META(o).id, META(o).expiration FROM default AS o USE KEYS ['k01', 'k50'];
      		 
       
      		 
      MERGE INTO default AS o USING default AS s USE KEYS ["k100", "k101"] ON s.ids[1] = META(o).id WHEN MATCHED THEN UPDATE SET META(o).expiration  = META(s).expiration WHEN NOT MATCHED THEN INSERT (s.ids[1], {"a":s.ids[1]}) RETURNING META(o).expiration, META(o).id;
      		 
      SELECT META(o).id, META(o).expiration FROM default AS o USE KEYS ['k02', 'k50'];
      		 
       
      		 
      MERGE INTO default AS o USING default AS s USE KEYS ["k100", "k101"] ON s.ids[2] = META(o).id WHEN MATCHED THEN UPDATE SET META(o).expiration = 1000 WHEN NOT MATCHED THEN INSERT (s.ids[2], {"a":s.ids[2]}, {"expiration": 3000}) RETURNING META(o).expiration, META(o).id;
      		 
      SELECT META(o).id, META(o).expiration FROM default AS o USE KEYS ['k03', 'k50'];
      		 
       
      		 
      MERGE INTO default AS o USING default AS s USE KEYS ["k100", "k101"] ON s.ids[3] = META(o).id WHEN MATCHED THEN UPDATE SET META(o).expiration = 3000 WHEN NOT MATCHED THEN INSERT (KEY s.ids[3]) RETURNING META(o).expiration, META(o).id;
      		 
      SELECT META(o).id, META(o).expiration FROM default AS o USE KEYS ['k11', 'k50'];
      		 
       
      		 
      MERGE INTO default AS o USING default AS s USE KEYS ["k100", "k101"] ON s.ids[4] = META(o).id WHEN MATCHED THEN UPDATE SET META(o).expiration = META(s).expiration + 1000 WHEN NOT MATCHED THEN INSERT (KEY s.ids[4], VALUE {"a":s.ids[4]}) RETURNING META(o).expiration, META(o).id;
      		 
      SELECT META(o).id, META(o).expiration FROM default AS o USE KEYS ['k12', 'k50'];
      		 
       
      		 
      MERGE INTO default AS o USING default AS s USE KEYS ["k100", "k101"] ON s.ids[5] = META(o).id WHEN MATCHED THEN UPDATE SET META(o).expiration = META(s).expiration WHEN NOT MATCHED THEN INSERT (KEY s.ids[5],  OPTIONS {"expiration": 3000} ) RETURNING META(o).expiration, META(o).id;
      		 
      SELECT META(o).id, META(o).expiration FROM default AS o USE KEYS ['k13', 'k50'];
      		 
       
      		 
      MERGE INTO default AS o USING default AS s USE KEYS ["k100", "k101"] ON s.ids[6] = META(o).id  WHEN MATCHED THEN UPDATE SET o.comment = "xyz" WHEN NOT MATCHED THEN INSERT (KEY s.ids[6], VALUE {"a":s.ids[6]}, OPTIONS {META(s).expiration}) RETURNING META(o).expiration, META(o).id;
      		 
      SELECT META(o).id, META(o).expiration FROM default AS o USE KEYS ['k14', 'k50'];
      		 
       
      		 
      DELETE FROM orders USE KEYS ['k100', 'k101', 'k50', 'k01', 'k02', 'k03', 'k11', 'k12', 'k13', 'k14']
      		 
       

      Attachments

        Issue Links

          Clones

          Improvement - An improvement or enhancement to an existing feature or task. MB-36533 N1QL Expiration support

          • Major - Major loss of function.
          • Closed
          is duplicated by

          Story - Created by Jira Software - do not edit or delete. Issue type for a user story. DOC-6138 DOC [MB-36533] N1QL Expiration support

          • Major - Major loss of function.
          • Resolved
          links to

          Web Link release/6.5 changes implemented by #1255

          No reviews matched the request. Check your Options in the drop-down menu of this sections header.

          Activity

            People

              simon.dew Simon Dew
              Sitaram.Vemulapalli Sitaram Vemulapalli
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Gerrit Reviews

                  There are no open Gerrit changes

                  PagerDuty