-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathdatabase.sql
More file actions
52 lines (47 loc) · 1.85 KB
/
Copy pathdatabase.sql
File metadata and controls
52 lines (47 loc) · 1.85 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
CREATE DATABASE IF NOT EXISTS syncode_db;
USE syncode_db;
-- 1. Users (Profile and Authentication)
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
avatar_url VARCHAR(255),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 2. Rooms (Coding spaces for collaboration)
CREATE TABLE rooms (
id INT AUTO_INCREMENT PRIMARY KEY,
room_name VARCHAR(100) NOT NULL UNIQUE,
description TEXT, -- Information about the room purpose or current ongoing project
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 3. Projects (Actual code saved by users)
CREATE TABLE projects (
id INT AUTO_INCREMENT PRIMARY KEY,
project_name VARCHAR(100) NOT NULL,
owner_id INT NOT NULL,
last_content MEDIUMTEXT, -- Most recent version
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
);
-- 4. Code History (Snapshots/Versions for each project)
CREATE TABLE code_history (
id INT AUTO_INCREMENT PRIMARY KEY,
project_id INT NOT NULL,
user_id INT NOT NULL, -- Author of the change
content_snapshot MEDIUMTEXT NOT NULL,
version_label VARCHAR(50), -- Description
saved_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
-- 5. Chat messages (Optional chat recovery)
CREATE TABLE messages (
id INT AUTO_INCREMENT PRIMARY KEY,
room_id INT NOT NULL,
user_id INT NOT NULL,
content TEXT NOT NULL,
sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (room_id) REFERENCES rooms(id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);