VCS-Implementation/tables.sql at main · Arijit2175/VCS-Implementation · GitHub

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
create database if not exists myvcs;
use myvcs;

drop table if exists files;
drop table if exists commits;
drop table if exists branches;

create table if not exists files (
    hash varchar(64) primary key,
    content text not null
) ENGINE=InnoDB;

create table if not exists commits (
    id int auto_increment primary key,
    commit_hash varchar(64) unique not null,
    message text not null,
    timestamp TIMESTAMP default CURRENT_TIMESTAMP,
    parent_commit varchar(64),
    branch_name varchar(50) not null
) ENGINE=InnoDB;

create table if not exists branches (
    name varchar(50) primary key,
    latest_commit varchar(64)
) ENGINE=InnoDB;

alter table commits
    add constraint fk_commits_parent_commit foreign key (parent_commit) references commits(commit_hash) on delete set null,
    add constraint fk_commits_branch_name foreign key (branch_name) references branches(name) on delete cascade;

alter table branches
    add constraint fk_branches_latest_commit foreign key (latest_commit) references commits(commit_hash) on delete set null;

create index idx_files_hash on files(hash);
create index idx_commits_branch_name ON commits(branch_name);