schema-tasks.txt

create table task (
id INT auto_increment primary key,
title VARCHAR(2000) NOT NULL default '',
description TEXT,
created DATETIME default NOW(),
updated DATETIME default NOW()
);

insert into task (title, description) values ('Card 1', 'This is the first task.'); 

create table user (
id INT auto_increment primary key,
name VARCHAR(2000) NOT NULL default '',
bio TEXT,
created DATETIME default NOW(),
seen DATETIME
);

insert into user (name, bio) values ('User 1', 'Default user 1');

create table list (
id INT auto_increment primary key,
name VARCHAR(2000) NOT NULL default '',
created DATETIME default NOW(),
updated DATETIME default NOW()
);

insert into list (name) values ('Task list 1');

create table task_in_list (
task_id INT NOT NULL,
list_id INT NOT NULL,
primary key (task_id, list_id),
foreign key (task_id) references task (id)
on delete cascade,
foreign key (list_id) references list (id)
on delete cascade
);

insert into task_in_list (task_id, list_id) values (1, 1);

create table user_in_task (
task_id INT NOT NULL,
user_id INT NOT NULL,
primary key (task_id, user_id),
foreign key (task_id) references task (id)
on delete cascade,
foreign key (user_id) references user (id)
on delete cascade
);

insert into user_in_task (task_id, user_id) values (1, 1);