CLwmUtGV

1DROP TABLE IF EXISTS FileLabels CASCADE;
2DROP TABLE IF EXISTS LabelColours CASCADE;
3DROP TABLE IF EXISTS FileAccess CASCADE ;
4DROP TABLE IF EXISTS CatalogFiles CASCADE ;
5DROP TABLE IF EXISTS Files CASCADE;
6DROP TABLE IF EXISTS CatalogChilds CASCADE;
7DROP TABLE IF EXISTS Catalogs CASCADE;
8DROP TABLE IF EXISTS FileAssociations CASCADE;
9DROP TABLE IF EXISTS FileTypes CASCADE;
10DROP TABLE IF EXISTS Applications CASCADE;
11DROP TABLE IF EXISTS Users CASCADE;
12
13--Пользователи
14CREATE TABLE Users (
15    e_mail TEXT PRIMARY KEY,
16    user_name TEXT
17);
18
19-- Типы файлов (пришлось создать отдельную таблицу, чтобы изменять типы)
20CREATE TABLE FileTypes (
21    type_name TEXT PRIMARY KEY  -- будем считать, что имена типов уникальны
22);
23
24-- Приложения (мотивы создания отдельной таблицы такие же, как и с типами)
25CREATE TABLE Applications (
26    app_name TEXT PRIMARY KEY   -- будем считать, что имена приложений уникальны
27);
28
29CREATE TABLE FileAssociations(
30    type_name TEXT REFERENCES FileTypes(type_name) ON DELETE CASCADE,
31    app_name TEXT REFERENCES Applications(app_name) ON DELETE CASCADE,
32    PRIMARY KEY (type_name, app_name)
33);
34
35/* Табличка с инф. о каталогах + id-шник родителя в дереве каталогов */
36CREATE TABLE Catalogs (
37    id SERIAL PRIMARY KEY,
38    name TEXT, 
39    parent_id INT,
40    comment TEXT,
41    owner_mail TEXT,
42    UNIQUE(parent_id, name),
43    UNIQUE(id, owner_mail)
44);
45
46CREATE TABLE Files (
47    url TEXT PRIMARY KEY,
48    size INTEGER CHECK (size >= 0),
49    name TEXT,
50    type TEXT REFERENCES FileTypes(type_name) ON DELETE CASCADE,
51    container_id INT REFERENCES Catalogs(id) ON DELETE CASCADE,
52    owner_mail TEXT REFERENCES Users(e_mail) ON DELETE CASCADE,
53    comment TEXT,
54    UNIQUE (container_id, name),
55    FOREIGN KEY (container_id, owner_mail) REFERENCES Catalogs(id, owner_mail)
56);
57
58CREATE TABLE FileAccess (
59    url TEXT PRIMARY KEY REFERENCES Files(url) ON DELETE CASCADE,
60    user_mail TEXT REFERENCES Users(e_mail) ON DELETE CASCADE
61);
62
63CREATE TABLE LabelColours (
64    label_name TEXT PRIMARY KEY,
65    label_colour TEXT
66);
67
68CREATE TABLE FileLabels (
69    url TEXT REFERENCES Files(url) ON DELETE CASCADE,
70    label_name TEXT REFERENCES LabelColours(label_name) ON DELETE CASCADE,
71    PRIMARY KEY (url, label_name)
72);
73
74
75INSERT INTO Users VALUES ('dsavvinov@gmail.com', 'dsavvinov');
76INSERT INTO Users VALUES ('avo.travel@gmail.com', 'avo');
77
78INSERT INTO FileTypes VALUES ('JPEG');
79INSERT INTO FileTypes VALUES ('MP3');
80
81INSERT INTO Applications VALUES ('AIMP');
82INSERT INTO Applications VALUES ('ACDSee');
83
84INSERT INTO FileAssociations VALUES ('MP3', 'AIMP');
85INSERT INTO FileAssociations VALUES ('JPEG', 'ACDSee');
86
87INSERT INTO Catalogs VALUES ('0', 'root', '0', 'dsavvinov root', 'dsavvinov@gmail.com');
88INSERT INTO Catalogs VALUES ('1', 'root', '1', 'avo root', 'avo.travel@gmail.com');
89INSERT INTO Catalogs VALUES ('2', 'subfolder', '0', 'dsavvinov subfolder', 'dsavvinov@gmail.com');
90-- INSERT INTO Catalogs VALUES ('3', 'subfolder', '0', 'Desc', 'dsavvinov@gmail.com'); -- wrong, not unique subfolders!
91-- INSERT INTO Catalogs VALUES ('3', 'Folder', '0', 'suddenly wrong folder', 'avo.travel@gmail.com'); -- incorrect user!
92
93INSERT INTO Files VALUES ('someurl1', '5', 'cat.jpg', 'JPEG', '0', 'dsavvinov@gmail.com', 'Cute cat!');
94INSERT INTO Files VALUES ('someurl2', '40', 'cat.jpg', 'MP3', '2', 'dsavvinov@gmail.com', 'Nice music');
95INSERT INTO Files VALUES ('omelchenko url', '100', 'basta.mp3', 'MP3', '1', 'avo.travel@gmail.com', 'Favourite track!');
96-- INSERT INTO Files VALUES ('someurl3', '100', 'cat.jpg', 'JPEG', '0', 'dsavvinov@gmail.com', 'Desc1'); -- wrong, not unique files in container
97-- INSERT INTO Files VALUES ('someurl2', '100', 'dog.jpg', 'JPEG', '0', 'dsavvinov@gmail.com', 'Desc1'); -- wrong, not unique URLs
98--INSERT INTO Files VALUES ('someurl3', '42', 'cat.jpg', 'MP3', '1', 'dsavvinov@gmail.com', 'Nice music'); -- incorrect owner!