CREATE TABLE tags (
       id integer NOT NULL PRIMARY KEY,
       name varchar(255) NOT NULL,
       description text
);

CREATE TABLE files (
       id integer NOT NULL PRIMARY KEY,
       canonical_name varchar(255) NOT NULL,
       full_name varchar(255) NOT NULL,
       description text,
       date integer NOT NULL,
       sum integer NOT NULL
);

CREATE TABLE file_tags (
       file integer NOT NULL,
       tag integer NOT NULL,
       FOREIGN KEY(file) REFERENCES files(id),
       FOREIGN KEY(tag)  REFERENCES tags(id)
);

-- INSERT INTO tags VALUES(0, 'inria', 'Document lié à Inria');
-- INSERT INTO tags VALUES(1, 'enseirb', 'Document lié à Enseirb');

-- INSERT INTO files VALUES(0, 'contrat_these.pdf', 'Contrat de travail pour la thèse.');

-- INSERT INTO file_tags VALUES(0 ,0);

-- -- Query: all files related to Inria
-- SELECT name, description FROM (
--        files JOIN file_tags ON files.id = file_tags.file
-- ) WHERE file_tags.tag = 0;