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
|
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
);
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;
|
