Server Database Model - UserXXX/vCom GitHub Wiki

Database schema

CREATE TABLE UserLists(
    listID      INTEGER NOT NULL,
    lastUpdated TIMESTAMP NOT NULL,
    PRIMARY KEY (listID));

CREATE TABLE Users(
    userID              INTEGER NOT NULL,
    name                VARCHAR2(40) NOT NULL,
    statusMessage       VARCHAR2(160) NOT NULL,
    profileImageData    VARBINARY(131072),
    currentInboxSize    INTEGER NOT NULL,
    friendlistID        INTEGER NOT NULL,
    blocklistID         INTEGER NOT NULL,
    FOREIGN KEY (friendlistID) REFERENCES UserLists,
    FOREIGN KEY (blocklistID) REFERENCES UserLists,
    PRIMARY KEY(userID));

CREATE TABLE UserListMembers(
    listID      INTEGER NOT NULL,
    memberID    INTEGER NOT NULL,
    FOREIGN KEY (listID) REFERENCES UserLists,
    FOREIGN KEY (memberID) REFERENCES Users,
    PRIMARY KEY (listID, memberID));
    
CREATE TABLE Groups(
    groupID         INTEGER NOT NULL,
    name            VARCHAR(40) NOT NULL,
    PRIMARY KEY (groupID));

CREATE TABLE GroupMembers(
    userID  INTEGER NOT NULL,
    groupID INTEGER NOT NULL,
    FOREIGN KEY (userID) REFERENCES Users,
    FOREIGN KEY (groupID) REFERENCES Groups,
    PRIMARY KEY (userID, groupID));

CREATE TABLE GroupAdmins(
    userID  INTEGER NOT NULL,
    groupID INTEGER NOT NULL,
    FOREIGN KEY (userID) REFERENCES Users,
    FOREIGN KEY (groupID) REFERENCES Groups,
    PRIMARY KEY (userID, groupID));

CREATE TABLE Devices(
    userID      INTEGER NOT NULL,
    deviceID    INTEGER NOT NULL,
    FOREIGN KEY (userID) REFERENCES Users,
    PRIMARY KEY (userID, deviceID));

CREATE TABLE Messages(
    userID              INTEGER NOT NULL,
    deviceID            INTEGER NOT NULL,
    messageID           INTEGER NOT NULL,
    groupRecipient      INTEGER,
    sender              INTEGER NOT NULL,
    hasBeenRead         BOOLEAN NOT NULL,
    sendTimestamp       TIMESTAMP NOT NULL,
    messageData         VARBINARY(256) NOT NULL,
    FOREIGN KEY (userID) REFERENCES Users,
    FOREIGN KEY (deviceID) REFERENCES Devices,
    FOREIGN KEY (groupRecipient) REFERENCES Groups,
    FOREIGN KEY (sender) REFERENCES  Users),
    PRIMARY KEY (userID, deviceID, messageID);

CREATE TABLE Passwords(
    passwordKey     VARCHAR2(20),
    passwordData    VARBINARY(1024),
    PRIMARY KEY (passwordKey));

Detailed explanation

UserLists

Stores the identifiers and last updated timestamps of friend- and blocklists.

Users

Stores the data of users. The profileImage may be NULL, in this case a default profile image is used.

UserListMembers

Contains the members for each list of users.

Groups

The chat groups on a server.

GroupMembers

The members associated with a group.

GroupAdmins

Contains the members of a group that have administrative rights. This table is a subset or GroupMembers.

Devices

Contains all device IDs known.

Messages

Contains all messages that are currently stored in the database. If groupRecipient is NULL then this is a unicast message to the user specified via userID, otherwise it is a multicast message to all users in the given group. If the entry is a multicast message, there will be exactly one copy of the original message for each recipient in groupRecipient. There is also a copy for each device a user has registered with. For all identical copies of a message with diffrent recipients (where the recipients are (user, device) tuples) the messageID entry will be equal, this can be used to speed up SQL queries.

Passwords

Contains passwords stored for the user. The security of this table is up to the DBMS and it's administrator. Some passwords will be hashed, but there will also be some that are needed in plain text (e.g. the e-mail account password for admin notification mails).