The Chat Application Model Database is designed to provide a robust and structured data storage solution for a chat application. It serves as the backbone for managing user accounts, friend relationships, and chat messaging features within the application. This comprehensive documentation covers the database schema, tables, relationships, sample queries with outputs, and details about the implemented triggers.
The database schema consists of four primary tables: admin
, users
, friends
, and messages
. Each table serves a distinct purpose in supporting various aspects of the chat application.
- Columns:
ID
(INT, Primary Key): Unique identifier for each user.Username
(VARCHAR): User's username.Email
(VARCHAR): User's email address.Status
(VARCHAR): User's status, can be 'Active' or 'Inactive'.AuthenticationTime
(DATETIME): Timestamp of the user's last authentication.
- Columns:
ID
(INT, Foreign Key): User ID.FriendID
(INT, Foreign Key): Friend's ID.
- Columns:
ID
(INT, Primary Key): Unique identifier for each message.FROMUID
(INT, Foreign Key): Sender's ID.ToUID
(INT, Foreign Key): Receiver's ID.MessageText
(TEXT): The content of the message.SentDt
(DATETIME): Timestamp when the message was sent.ReadStatus
(VARCHAR): Message read status, can be 'Read' or 'Unread'.
-
updateAuthenticationTime
Trigger:- Function: Updates the
AuthenticationTime
of the sender when a new message is sent.
- Function: Updates the
-
updateUserKey
Trigger:- Function: Updates the
UserKey
based on the user'sStatus
before each update.
- Function: Updates the
-
updateUserKeyOnStatusChange
Trigger:- Function: Updates
UserKey
when there is a change in the user'sStatus
.
- Function: Updates
-
updateAuthTimeOnFriendAdd
Trigger:- Function: Updates
AuthenticationTime
for users involved in a new friendship.
- Function: Updates
-
updateUserStatusOnNoFriends
Trigger:- Function: Updates user status to 'Inactive' if they have no friends.
-
updateUserStatusOnUnreadMessages
Trigger:- Function: Updates user status to 'Active' if they receive a new unread message.
-
deleteOldMessages
Trigger:- Function: Deletes messages older than 6 months after a new message is inserted.
-
SendMessage
Stored Procedure:- Function: Inserts a new message into the
messages
table.
CALL SendMessage(1, 2, 'Hello, Sheetal!');
- Function: Inserts a new message into the
-
markAllMessagesAsRead
Stored Procedure:- Function: Marks all messages as read for a specific user.
CALL markAllMessagesAsRead(1);
-
deleteUserAndMessages
Stored Procedure:- Function: Deletes a user and their associated messages.
CALL deleteUserAndMessages(2);
-
getFriendList
Stored Procedure:- Function: Retrieves a list of friends for a user.
CALL getFriendList(1);
-
getLatestMessage
Stored Procedure:- Function: Retrieves the latest message for a specific user.
CALL getLatestMessage(1);
-
Retrieve all users and their friendships:
SELECT u.ID AS UserID, u.Username, u.Email, f.FriendID, fu.Username AS FriendUsername, fu.Email AS FriendEmail FROM users u JOIN friends f ON u.ID = f.ID JOIN users fu ON f.FriendID = fu.ID;
Example Output:
UserID Username Email FriendID FriendUsername FriendEmail 1 Sudhanshu [email protected] 2 Sheetal [email protected] 1 Sudhanshu [email protected] 3 Arun [email protected] 2 Sheetal [email protected] 1 Sudhanshu [email protected] 3 Arun [email protected] 1 Sudhanshu [email protected] -
Fetch messages sent by a specific user with sender and receiver details:
SELECT m.ID AS MessageID, m.FROMUID AS SenderID, u1.Username AS SenderUsername, m.ToUID AS ReceiverID, u2.Username AS ReceiverUsername, m.SentDt AS SentDateTime, m.MessageText FROM messages m JOIN users u1 ON m.FROMUID = u1.ID JOIN users u2 ON m.ToUID = u2.ID WHERE m.FROMUID = 1;
Example Output:
MessageID SenderID SenderUsername ReceiverID ReceiverUsername SentDateTime MessageText 1 1 Sudhanshu 2 Sheetal 2023-01-01 12:00 Hello, Sheetal! 2 1 Sudhanshu 3 Arun 2023-01-02 14:30 Hi, Sudhanshu! -
Obtain all messages exchanged between friends:
SELECT m.ID AS MessageID, m.FROMUID, m.ToUID, m .SentDt, m.MessageText FROM messages m JOIN friends f ON (m.FROMUID = f.ID AND m.ToUID = f.FriendID) OR (m.FROMUID = f.FriendID AND m.ToUID = f.ID);
Example Output:
MessageID SenderID ReceiverID SentDateTime MessageText 1 1 2 2023-01-01 12:00 Hello, Sheetal! 2 2 1 2023-01-02 14:30 Hi, Sudhanshu! -
Identify unread messages for a specific user:
SELECT m.ID AS MessageID, m.FROMUID, u.Username AS SenderUsername, m.ToUID, m.SentDt AS SentDateTime, m.MessageText FROM messages m JOIN users u ON m.FROMUID = u.ID WHERE m.ToUID = 1 AND m.ReadStatus = 'Unread';
Example Output:
MessageID SenderID SenderUsername ReceiverID SentDateTime MessageText 3 3 Arun 1 2023-01-03 10:45 Unread message -
List users with a specific status:
SELECT ID AS UserID, Username, Email, Status FROM users WHERE Status = 'Active';
Example Output:
UserID Username Email Status 1 Sudhanshu [email protected] Active 2 Sheetal [email protected] Active -
Retrieve messages sent by users with a specific status:
SELECT m.ID AS MessageID, m.FROMUID, u.Username AS SenderUsername, m.ToUID, m.SentDt AS SentDateTime, m.MessageText FROM messages JOIN users u ON m.FROMUID = u.ID WHERE u.Status = 'Active';
Example Output:
MessageID SenderID SenderUsername ReceiverID SentDateTime MessageText 1 1 Sudhanshu 2 2023-01-01 12:00 Hello, Sheetal! 2 2 Sheetal 1 2023-01-02 14:30 Hi, Sudhanshu! 3 3 Arun 1 2023-01-03 10:45 Unread message
🌟 Thank you for exploring the Chat Application Model Database! 🚀
We believe in the magic of connecting people through technology. May your coding journey be as smooth as a well-optimized SQL query and your friendships as resilient as a robust database schema.
Feel free to reach out for any queries, collaborations, or just to share your success stories. Happy coding, and may your code always compile without errors!
Keep sparking creativity, keep building connections! 🌐✨