Add Key-Level TTL Support

[otaviojava]

Creating an issue for Oracle NoSQL Database to add a feature for setting Time-to-Live (TTL) at the key level similar to Redis. Here's the issue description:

In Oracle NoSQL Database, Time-to-Live (TTL) can be set at the row level using a specific column. However, there is currently no direct support for setting TTL at the critical level, similar to how it's done in Redis, Cassandra, Couchbase, Hazelcast, and Infinispan.

Desired Behavior:

We want to propose adding a feature to Oracle NoSQL Database that allows users to set TTL for individual keys. This would provide more fine-grained control over data expiration and make managing data with varying expiration times easier.

Use Case:

A common use case is when you have different keys in the database with varying TTLs. For example, in Redis, you can set TTL for a specific key as follows:

try (Jedis jedis = pool.getResource()) {
    jedis.set("foo", "bar");
    jedis.expire("foo", (int) ttl.getSeconds());
}

This allows users to control the TTL for each key independently, which is helpful for scenarios where not all data should have the same expiration time.

References:

• Redis TTL Documentation
• Cassandra TTL Example
• Managing TTL with Couchbase N1QL

[gmfeinberg]

This feature is available and has been since we introduced TTL
See (for direct Java driver):
https://docs.oracle.com/en/database/other-databases/nosql-database/23.3/java-driver-table/using-time-live.html#GUID-A768A8F9-309A-4018-8CC3-D2D6B8793C59
And for the Java cloud/proxy SDK:
https://oracle.github.io/nosql-java-sdk/oracle/nosql/driver/ops/PutRequest.html#setTTL(oracle.nosql.driver.TimeToLive)

It can also be set in INSERT/UPSERT SQL statements

[dario-vega]

Here is an example using the Java SDK and the put API

import oracle.nosql.driver.TimeToLive;

MapValue value = new MapValue()
    .put("id", "1")
    .put("entity", "person")
    .putFromJson("content", "{\"name\":\"Lucas\",\"location\":\"BR\",\"type\":\"V\",\"age\":22}", null);

PutRequest putRequest = new PutRequest()
    .setValue(value)
    .setTTL(TimeToLive.ofHours(2)) 
    //.setTTL(TimeToLive.ofDays(2))
    .setTableName(tableName);

Retrieving the expiration time using the Java SDK and the get API

MapValue key = new MapValue().put("id", "1");
GetRequest getRequest = new GetRequest()
    .setKey(key)
    .setTableName(tableName);
GetResult getRes = serviceHandle.get(getRequest);
long ex = getRes.getExpirationTime();

Here is an example using SQL (documentation here)

INSERT INTO database VALUES (2,"person", {"age":22,"location":"FR","name":"Dario", "type":"V"})
SET TTL 1 DAYS

UPSERT INTO database VALUES (2,"person", {"age":22,"location":"FR","name":"Dario", "type":"V"}) 
SET TTL 1 DAYS

UPDATE database $p
SET TTL remaining_hours($p) + 3 hours
WHERE id = "2"

UPDATE database $p
SET entity = "person",
SET TTL remaining_days($p) + 2 days
WHERE id = "2"

UPDATE database $p
SET TTL 1 HOURS
WHERE id = "1"

You can use the functions expiration_time , remaining_hours, remaining_days or expiration_time_millis.
To use those functions, you must to use an alias starting with $, see more here

sql-> select id, expiration_time($u) as et, remaining_hours($u) as rh, remaining_days ($u) as rd, expiration_time_millis($u) as et_ms
from database $u;
{
  "id" : "1",
  "et" : "2024-01-24T23:00:00.000Z",
  "rh" : 2,
  "rd" : 0,
  "et_ms" : 1706137200000
}

Note: I will ask to my colleague to add an example for the TTL using the SDK in the documentation

[otaviojava]

Thank you both!

[dario-vega]

Reopening this discussion that could be interesting for others

by default it is showing is:open category:Q&A