Versioning

[lvca]

(This discussion begins on the Discord channel)

I always liked storing timestamps in records, at least a couple: createdOn and lastUpdateOn. Unfortunately, when you squeeze performances these 2 long properties weigh a lot when you have millions or billions of records. Especially if they are not used. I was rather thinking of providing a base type, like type Versioned { long createdOn; long lastUpdatedOn; long deletedOn } and ArcadeDB inheritance by letting users define types that extend Versioned, so they have these 3 properties.

On top of that, the database could behave differently when you're deleting a record whose type extends Versioned: the record would be marked as deleted (fill up deletedOn properties). Also, when updated, it should update the lastUpdatedOn properties under the hood. To achieve full versioning it should be able to create a new record at every update by copying all the properties and linking it to the previous one (as the previous version). In this way, you can always browse versions by crossing edges previous and/or next.

I'm sure it's more complicated than this, and maybe a set of new commands would be needed.

[phillipross]

This sounds like it could be pretty useful, esp if it could be used to handle "soft delete" functionality.
Better to have an established pattern with an opinionated implementation out-of-the-box than to see it repeatedly implemented incorrectly by app developers 😅

[lvca]

If the SQL commands behave differently in the case of the Versioned sub-type, how to execute an actual delete instead of soft delete? Maybe better having dedicated SQL commands? The expert with SQL syntax is @antarcticgiraffe but he's pretty busy working on other projects.

Below is the first draft, but I'm open to any advice/ideas.

Versioned Metadata Properties. We could find a convention, like starting with v for versioned attributes. Or perhaps using _ as a prefix?

long _vCreatedOn;
long _vLastUpdatedOn;
long _vDeletedOn;
long _vNumber;

We could store a new _vNumber of metadata in the record. It would start at 0 and gets incremented at every update.

---

Update a record

VERSION UPDATE ... [ WHERE ... ] SET ...

This clones the current record before the update as an old version. Then updates the current record and increments the _vNumber number and links the previous version with the PreviousVersion edge and vice versa with NextVersion edge.

---

Delete a record:

VERSION DELETE FROM ... [ WHERE ... ]

This actually set the property _vDeletedOn to the current timestamp.

---

Query:

Queries should filter out deleted records (_vDeletedOn not null) and get only the latest version.

How to make this efficient? We could store old versions in ad-hoc buckets. Like with Employee type, we could create an Employee_PastVersions where we store the previous versions copied at update time. In this way, the queries can just exclude those buckets to avoid looking for old versions, but just the latest.

If the users wants to look for past versions, then the involved buckets can be used in the query with SELECT ... FROM bucket:Employee_PastVersions WHERE ...

[gramian]

This is pretty cool. What is the state of this feature?

Alternatively, a special default value for DATETIME properties like

CREATE PROPERTY createdOn DATETIME (default now);

could be a less implementation intensive approach for automatically generated timestamps?

[VikramShityalkar]

@lvca
I would like to add another requirement that is commonly found in Content Management Systems. This requirement includes three aspects:

1. The ability to differentiate between major and minor versions, for example, 0.1 for minor versions and 1.0 for major versions.
2. The ability to restore to the previous version.
3. The ability to filter for approved versions or draft versions. For example, when querying a list of "Post" items, the user should be able to only retrieve approved posts or draft posts, based on their preference. Additionally, the posts could have major and minor versions and the user should be able to filter them accordingly.

[lvca]

Im afraid his is a little too specific, but it could be a vertical module on top of ArcadeDB that takes care of all of this

[emdax]

Would be cool if part of this versioning design there was also a clever way to implement CouchBase style UUID to make graphs and their relationships automatically resilient and durable when moving between systems.

[phillipross]

I agree this would be very handy, though I think there was some question on the discord channel as to what it means to "move between systems"

Maybe more context and definition needs to be specified to better explain. Example use-cases would probably be best to illustrate the need for this 🙂

[psytron]

Move between systems in every way: 1. between running instances of ArcadeDB on different physical servers. 2. between run-time contexts being rendered and real-time manipulated in memory. 3. between differing database systems, flat files, runtime in-memory dataframes and metadata like NetworkX for Ai applications 4. between decentralized systems with content id like IPFS. This list can keep going - A lot of cool opportunity opens up when edges are not locked-in or anchored to auto-incrementing value on one host. The subgraphs become durable and resilient.

[lvca]

The issue with using UUID (or any unique key) for edges is that a traversal would require a lookup in an index that is O(LogN) instead of O(1) as for now. The rid #33:23 contains a physical position (file 33, position in file 23) that is resolved without using the index. If you traverse thousands or more edges, the difference would be huge between e 1000 x O(1) and 1000 X O(LogN). Also, the index performance, may index algorithm, depends on the index size. Even with a balanced tree, the more elements in the index, the more depth you will have in the (balanced) tree. This is the main reason why native graph databases are much faster than RDBMS and any other DBMS that uses an index for traversal.

On the other side, I understand that having UUID would make everything easier from a developer's point of view and import/export graphs elsewhere. I can see some options for this problem:

1. Make export/import of graphs easy

Create new commands to import a graph. For example, you could have a graph as a result of a query (select or match), export it in JSON and then import it into another database with a new command like import graph <json>. The import of the graph will convert the RIDS in the new database to be consistent and optionally could save the original RID in a property @sourceRID if you need to do cross-database traversals.

2. Support UUID on edges by using a central index

Or as an alternative, we could have edges that point to vertices using a UUID (it must be declared in the vertex as a property). This means it would need a central huge index to store the references UUID -> vertex that is cross-type (and bucket). This index must be maintained properly. For example, if you drop a vertex type, it should delete all the entries of vertices relative to the deleted type. Also, the performance of traversal would be pretty poor.

3. As above, but using separate indexes

As an alternative, we could map the types in numbers (keeping track of type creation and assigning an incremental number), so the UUID would be prefixed by the type number. In this way, we can have N indexes, one per type, so it would be much faster and easier to maintain. In case a type is dropped, its UUID index will be dropped too. Also, like with (2) performance will be poor. A little better than (2) but it'd be still much worst than the current RID implementation.

WDYT?

[tolgaulas]

My two cents is #1 should be great for granular imports, we feel the pain with migrations with select dataset. Then we can even import from another database too; #2 can be optional and yes its costly (and delicate; corruption may ruin the show). #3 Resembles our current practices with orientdb where we keep a auto generated uuid along with RIDs which we use to refer to in queries (and in traversals) but using RIDs as UUID is not practical since they will change with import and we use UUIDs to remap the lost edges in case of corruption. (we also keep the pointing rid's uuid when we have a link property).