From 03b3f5cbb89b41176270e2c729a47a04a93bc2ac Mon Sep 17 00:00:00 2001 From: Tristan Riehs Date: Tue, 11 Nov 2025 09:51:28 +0100 Subject: Move sql init script Let's put SQL scripts into a dedicated directory. --- sql/init.sql | 30 ++++++++++++++++++++++++++++++ 1 file changed, 30 insertions(+) create mode 100644 sql/init.sql (limited to 'sql/init.sql') diff --git a/sql/init.sql b/sql/init.sql new file mode 100644 index 0000000..339ec55 --- /dev/null +++ b/sql/init.sql @@ -0,0 +1,30 @@ +CREATE TABLE tags ( + id INTEGER NOT NULL PRIMARY KEY, + name VARCHAR(255), + description TEXT +); + +CREATE TABLE files ( + id INTEGER NOT NULL PRIMARY KEY, + name VARCHAR(255), + description TEXT +); + +CREATE TABLE file_tags ( + file INTEGER, + tag INTEGER, + 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; -- cgit v1.2.3