2

I have gone though this article and here is the schema I have got from it. This is helpful for my application for maintaining statuses of a user, but how can extend this to maintain one to one chat archive and relations between users, relations mean people belong to specific group for me. I am new to this and need an approach for this.

Requirement :

I want to store messages between user-user in a table, whenever a user want to load messages by a user I want to retrieve them back and send it to user. I want to retrieve all the messages from different users to the user when user has requested. And also want to store class of users. I mean for example user1 and user2 belong to "family" user3, user4, user1 belong to friends etc... This group can be custom name given by the user.

This is what I have tried

CREATE TABLE chatarchive (
   chat_id uuid PRIMARY KEY,
   username text,
   body text
)

CREATE TABLE chatseries (
username text,
    time timeuuid,
    chat_id uuid,
    PRIMARY KEY (username, time)
) WITH CLUSTERING ORDER BY (time ASC)

CREATE TABLE chattimeline (
    to text,
username text,
    time timeuuid,
    chat_id uuid,
    PRIMARY KEY (username, time)
) WITH CLUSTERING ORDER BY (time ASC)

Below is the schema that I currently have,

CREATE TABLE users (
   username text PRIMARY KEY,
   password text
)    

CREATE TABLE friends (
    username text,
    friend text,
    since timestamp,
    PRIMARY KEY (username, friend)
)

CREATE TABLE followers (
    username text,
    follower text,
    since timestamp,
    PRIMARY KEY (username, follower)
)

CREATE TABLE tweets (
    tweet_id uuid PRIMARY KEY,
    username text,
    body text
)
CREATE TABLE userline (
    username text,
    time timeuuid,
    tweet_id uuid,
    PRIMARY KEY (username, time)
) WITH CLUSTERING ORDER BY (time DESC)

CREATE TABLE timeline (
    username text,
    time timeuuid,
    tweet_id uuid,
    PRIMARY KEY (username, time)
) WITH CLUSTERING ORDER BY (time DESC)
6
+100

I created a chat program a while back which is available on github

https://github.com/akc42/MBChat

which has some of the characteristics you are talking about. In particular it provides both rooms which people can enter and discuss on an open way, and whisper boxes where a few people can get together and discuss things.

When people enter a room, or join a whisper, they can see a little bit of the past conversation.

Before explaining the schema a bit more, it is worth just pointing out that I have two methods of validating users. This is because the normal use is a chat extension to an smf forum - so users are already logged in to the forum, and they carry their same identity over to chat (and have some characteristics that are derived from membership groups in the forum). However so chat can be used stand alone without a forum, there is a separate user database which can be queried during the logon phase which identifies the users and their capabilities.

So first that database is at inc/user.sql in the repository.

BEGIN;

CREATE TABLE users (
  uid integer primary key autoincrement NOT NULL,
  time bigint DEFAULT (strftime('%s','now')) NOT NULL,
  name character varying NOT NULL,
  role text NOT NULL DEFAULT 'R', -- A (CEO), L (DIRECTOR), G (DEPT HEAD), H (SPONSOR) R(REGULAR)
  cap integer DEFAULT 0 NOT NULL, -- 1 = blind, 2 = committee secretary, 4 = admin, 8 = mod, 16 = speaker 32 = can't whisper( OR of capabilities).
  password character varying NOT NULL, --raw password
  rooms character varying, -- a ":" separated list of rooms nos which define which rooms the user can go in
  isguest boolean DEFAULT 0 NOT NULL
);
CREATE INDEX userindex ON users(name);
-- Below here you can add the specific users for your set up in the form of INSERT Statements


-- This list is test users to cover the complete range of functions. Note names are converted to lowercase, so only put lowercase names in here
INSERT INTO users(uid,name,role,cap,password,rooms,isguest) VALUES
(1,'alice','A',4,'password','7',0),
(2,'bob','L',3,'password','8',0),
(3,'carol','G',2,'password','7:8:9',0),
(4,'dave','H',0,'password','10',0),
(5,'eileen','R',8,'password','',0),
(6,'fred','R',16,'password','',0),
(7,'gail','R',0,'password','',0),
(8,'harry','R',0,'password','',1),
(9,'irene','R',32,'password','',0);




COMMIT;
VACUUM;
-- set it all up as Write Ahead Log for max performance and minimum contention with other users.
PRAGMA journal_mode=WAL;

However once they have been logged on, these credentials are copied over into the actual chat database that is being used

The following are elements of the schema (taken from data/chat.sql file from that repository) that might help you.

Firstly the the open rooms (actually not all rooms are equal, there is some limitations - see comments)

CREATE TABLE rooms (
  rid integer primary key NOT NULL,
  name varchar(30) NOT NULL,
  type integer NOT NULL -- 0 = Open, 1 = meeting, 2 = guests can't speak, 3 moderated, 4 members(adult) only, 5 guests(child) only, 6 creaky door
) ;

INSERT INTO rooms (rid, name, type) VALUES 
(1, 'The Forum', 0),
(2, 'Operations Gallery', 2), --Guests Can't Speak
(3, 'Dungeon Club', 6), --creaky door
(4, 'Auditorium', 3), -- Moderated Room
(5, 'Blue Room', 4), -- Members Only (in Melinda's Backups this is Adults)
(6, 'Green Room', 5), -- Guest Only (in Melinda's Backups this is Juveniles AKA Baby Backups)
(7, 'The Board Room', 1), --various meeting rooms - need to be on users room list
(8, 'Marketing', 1),
(9, 'Engineering',1),
(10, 'IT Dept', 1),
(11, 'Finance', 1);

Now users themselves are carried over from the previous database (or from the forum and have a matching table)

CREATE TABLE users (
  uid integer primary key NOT NULL,
  time bigint DEFAULT (strftime('%s','now')) NOT NULL,
  name character varying NOT NULL,
  role char(1) NOT NULL default 'R',
  rid integer NOT NULL default 0,
  mod char(1) NOT NULL default 'N',
  question character varying,
  private integer NOT NULL default 0,
  cap integer NOT NULL default 0,
  rooms character_varying 
);

But this is not all - I have another concept - which is that of a Participant in a conversation - with a conversation being just an ID.

CREATE table wid_sequence ( value integer);
INSERT INTO wid_sequence (value) VALUES (1);

CREATE TABLE participant (
  uid integer NOT NULL REFERENCES users (uid) ON DELETE CASCADE ON UPDATE CASCADE,
  wid integer NOT NULL,
  primary key (uid,wid)
);

Not there is a wid_sequence table which my code manually increments when anyone creates a new conversation - (I call it a whisper box, but sometimes it becomes what I call a private room ), and then a record in the participant table whilst the conversation is in place.

Finally, so I have an archive of messages - which I can reference and play back at a later date (either a short list of recent messages when someone enters a room, or someone with the secretary capability can enter a room and obtain a printout of the entire conversation - as minutes of a meeting for instance).

I think it is this table that is your archive that you mention in the question

The schema for this is

CREATE TABLE log (
  lid integer primary key,
  time bigint DEFAULT (strftime('%s','now')) NOT NULL,
  uid integer NOT NULL,
  name character varying NOT NULL,
  role char(1) NOT NULL,
  rid integer NOT NULL,
  type char(2) NOT NULL,
  text character varying
);

The important item here is the type field. It is in essence a replay of the actions as well as the conversation, so we also can replay new conversations starting up (where rid then maps to the wid created from wid_sequence), and people entering and exiting rooms etc.

I trust that gives you an idea how you can proceed. Feel free to examine my code on github and ask any questions that you might have of it.

As a postscript, there is a table I didn't mention called 'parameters' which controls a lot of how things work. One of those is whether encryption needs to be used. It is beyond the scope of this answer to go into that - as it is fairly complicated - but you may need to consider how private you want private conversations to be and whether or not they should be stored encyrpted in your archive.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.