7. Examples

7.1 File sync

A perfect use case for ActorDB is a file sync app (Dropbox, EmitCloud, ownCloud,..). A database for a file sync app needs to store:

1. - a file directory structure for every user,
2. - an event log for every user (we will skip this part, its a simple continuous table with an auto increment id),
3. - a list of files our users have uploaded (we must run a distributed file system)

Schema:

-- Users have files and folders. Files always need a parent folder. All folders except root folder have parents.
actor user
CREATE TABLE file (id PRIMARY KEY INTEGER, parent INTEGER NOT NULL, name TEXT NULL, fsid TEXT, FOREIGN KEY (parent) REFERENCES dir(id) ON UPDATE CASCADE)
CREATE TABLE dir (id PRIMERY KEY INTEGER, parent INTEGER, name TEXT NOT NULL, FOREIGN KEY (parent) REFERENCES dir(id) ON UPDATE CASCADE)

-- A distributed filesystem is global. Create a kv to store the position of files.
-- For every file, we also need a table of nodes that store this file.
actor filesystem kv
CREATE TABLE actors (id TEXT PRIMARY KEY, hash INTEGER, crc INTEGER NULL, mime TEXT NULL, size INTEGER NULL) WITHOUT ROWID
CREATE TABLE nodes (id TEXT, node TEXT NOT NULL, FOREIGN KEY(id) REFERENCES actors(id) ON DELETE CASCADE, PRIMARY KEY (id,node)) WITHOUT ROWID

Tables "file" and "dir" are inside an individual actor. When a user is changing his files and we must store that new info, we do not need to modify giant tables that also contain data for thousands of other users.

Storing actual files in our system also requires a database. At least we must keep track which servers have those files. It is also ideal if we can avoid storing the same file more than once. Using the above schema for our filesystem does just that. If our file ID is SHA of the actual file, we do not need to store a file more than once.

Queries:

User uploads a new file, we calculated the file SHA during upload. Check if it already exists:

ACTOR filesystem(MYSHA);
SELECT * FROM actors WHERE id='MYSHA';

If no result, we can create a transaction:

ACTOR filesystem(MYSHA);
INSERT OR REPLACE INTO actors VALUES ('MYSHA',{{hash(MYSHA)}}, 123, "text/plain", 123456);
INSERT OR REPLACE INTO nodes VALUES ('MYSHA', 'myserver1'), ('MYSHA','myserver2'), ('MYSHA','myserver3');
ACTOR user(MYUSER);
INSERT INTO files VALUES (1,2, "myfile.txt", 'MYSHA');

We use insert or replace because in the rare event of the same file being uploaded at the exact same time, our transaction does not fail. After transaction is complete and file is uploaded, the individual servers storing it can check if they won the race.

Renaming a file:

ACTOR user(MYUSER);
UPDATE file SET name='newname' WHERE id=1;

And so on.