Add sql server as a persistent state storage solution

[Ulimo]

Access requirements:

The solution should only require normal data operation access rights. So no new tables will be created or deleted during runtime.

Data model:

[Streams]
*StreamKey {label: "int, not null"}
UniqueStreamName {label: "nvarchar, not null"}
LastSuccessfulVersion {label: "int, not null"}

[StreamPages]
*PageKey {label: "guid, not null"}
+streamkey {label: "int, not null"}
PageId {label: "int64, not null"}
Payload {label: "binary, not null"}
Version {label: "int, not null"}

Streams 1--* StreamPages

PageKey is the combination of pageid and version in 16 bytes. This is done to be able to fetch multiple pages at once. Ex: WHERE streamkey = 123 AND pagekey in ('f0a6c73c-a30b-476c-8281-b03ba7dda12b', 'f6026e79-a1bc-40aa-b1b0-f8831d49727f')

Operations

Starting new stream

Add a new entry to the streams table.

Starting existing stream

Check the last successful version, and delete any pages for that stream with versions larger than the successful version. All pages for the stream are downloaded without payload. Only pageid and version are fetched. These are stored in memory for fast access with only the latest version of each page.

Fetching a page

When fetching a page, the pageid and version are combined into a guid, which is then filtered on.

SELECT payload FROM StreamPages WHERE streamkey = 123 AND PageKey IN ('f0a6c73c-a30b-476c-8281-b03ba7dda12b')

Compacting the stream

To compact the data old versions of pages are deleted. it is possible to keep track of the replaced versions in memory and delete them on compaction.

This can be done by storing a list of replaced pages during a checkpoint, and on compaction apply a delete on all those pages. This list can also be filled during the starting point of a stream, to add any old page versions to this list to be removed on the next compaction.