csc176

use csc176;

create table articles
(
    article_id int not null auto_increment,
    user_id int not null,
    title varchar(30) not null,
    body text not null,
    primary key (article_id)
);

create table comments
(
    comment_id int not null auto_increment,
    user_id int not null,
    article_id int not null,
    body text not null,
    parent_id int,
    primary key (comment_id)
);

create table users
(
    user_id int not null auto_increment,
    name varchar(30) not null,
    primary key (user_id)
);

alter table articles
add constraint fk_articles_user
foreign key (user_id)
references users (user_id)
on delete cascade;

alter table comments
add constraint fk_comments_users
foreign key (user_id)
references users (user_id)
on delete cascade,

add constraint fk_comments_articles
foreign key (article_id)
references articles (article_id)
on delete cascade,

add constraint fk_comments_comments
foreign key (parent_id)
references comments (comment_id)
on delete cascade;

insert into users (name) values ('Billyjim Cricketwhisker');
insert into users (name) values ('Lou Beasley');
insert into users (name) values ('Aloysius Montegomery');

insert into articles (user_id, title, body) values (1, 'dolor sit amet', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Sed id congue tellus. Pellentesque mollis erat quis dapibus maximus. Integer interdum, leo eu pulvinar accumsan, lorem mi feugiat justo, nec pharetra ante ante at sapien. Fusce varius a eros vitae venenatis. Ut ultricies laoreet volutpat.');
insert into articles (user_id, title, body) values (2, 'phasellus aliquam nisl', 'Quisque quis lorem pretium, hendrerit ligula non, blandit dolor. Pellentesque tincidunt sapien ex, id laoreet velit placerat vel. Suspendisse potenti. Phasellus aliquam nisl quis pharetra feugiat.');
insert into articles (user_id, title, body) values (3, 'ut turpis ex', 'Nunc ut turpis ex. In varius, tortor et euismod scelerisque, augue quam laoreet diam, quis finibus ante urna non ligula. Morbi id sagittis nisi, eu dictum ipsum. ');

insert into comments (user_id, article_id, body, parent_id) values (2, 1, 'That is extremely interesting!', NULL);
insert into comments (user_id, article_id, body, parent_id) values (3, 1, 'I disagree!', 1);

insert into comments (user_id, article_id, body, parent_id) values (1, 2, 'And you call my article uninteresting!', NULL);
insert into comments (user_id, article_id, body, parent_id) values (2, 2, 'Y u mad tho?', 3);
insert into comments (user_id, article_id, body, parent_id) values (3, 2, 'Ya\'ll need to calm down', 4);

insert into comments (user_id, article_id, body, parent_id) values (1, 3, 'Where do you find this stuff??', NULL);

update users set name='Billytim Cricketwillow' where user_id=1;
update articles set title='Best Article Ever' where article_id=1;
update comments set body='u wot?' where comment_id=6;

delete from users where user_id=3;
delete from articles where article_id=3;
delete from comments where comment_id=5;