· 5 years ago · Sep 20, 2020, 09:14 PM
1-- ----------------------------------------------------------------------
2-- The SQL table structure for a hierachical file system.
3-- ----------------------------------------------------------------------
4-- TODO Test the max size of the file we can upload the server
5-- TODO Add columns 'created', 'changed' and 'disabled' columns.
6-- TODO Use the !Inline to devlop app centric behavior.
7-- ----------------------------------------------------------------------
8-- Start the local sql server database to test the script.
9-- Open a CMD and type the following lines:
10--
11-- cd "C:\Program Files\Microsoft SQL Server\130\Tools\Binn"
12-- SqlLocalDB.exe start MSSQLLocaldb
13--
14-- Use these commands to start, stop or see info about the server:
15--
16-- SqlLocalDB.exe start MSSQLLocaldb
17-- SqlLocalDB.exe stop MSSQLLocaldb
18-- SqlLocalDB.exe info MSSQLLocaldb
19--
20-- Use the 'info' command to see the connection-string to use with SQL
21-- Server Management Studio (SSMS).
22-- ----------------------------------------------------------------------
23-- Delete tables if they already exist.
24IF OBJECT_ID(N'dbo.Container', N'U') IS NOT NULL
25BEGIN
26 DROP TABLE Container;
27 DROP TABLE Token;
28 DROP TABLE TokenType;
29 DROP TABLE Client;
30 DROP TABLE Project;
31END
32-- ----------------------------------------------------------------------
33-- Create the tables
34
35-- The token data types.
36CREATE TABLE TokenType(
37 id BIGINT IDENTITY(1,1) NOT NULL,
38 title NVARCHAR(512) NOT NULL,
39 extension NVARCHAR(512) NOT NULL DEFAULT '',
40 contenttype NVARCHAR(512) NOT NULL DEFAULT 'text/plain',
41 PRIMARY KEY (id),
42 UNIQUE (title)
43);
44
45-- The tokens.
46-- A token is a file, a piece of a file or a container.
47-- Contains all files uploaded or created.
48--
49-- If the token is '!Inline' then fetch the contents from 'contents'.
50-- If the token is '!Container' then fetch the contents from the Container table.
51-- Otherwise fetch the data on disk via 'filename'.
52--
53-- Using 'partname' we can refer to only part of a file or piece of data.
54--
55-- TODO Turn contents into a BLOB ?
56CREATE TABLE Token(
57 id BIGINT IDENTITY(1,1) NOT NULL,
58 tokentypeid BIGINT NOT NULL, -- !Inline, !Container, Binary, etc
59 filename NVARCHAR(512) NOT NULL DEFAULT '', -- location on disk on server
60 partname NVARCHAR(512) NOT NULL DEFAULT '', -- Only part of a file, like 'Slide 1'
61 contents NVARCHAR(MAX) NOT NULL DEFAULT '', -- The inline contents.
62 PRIMARY KEY (id),
63 INDEX token_tokentypeid_key NONCLUSTERED (tokentypeid),
64 INDEX token_filename_key NONCLUSTERED (filename)
65);
66
67-- The list of rules for the hierachical structure.
68-- The list of associations between tokens.
69-- The rules are binary in the way that each rule connects one token to one other token.
70-- Both 'tokenid' and 'childid' referes to Token.id.
71CREATE TABLE Container(
72 id BIGINT IDENTITY(1,1) NOT NULL,
73 tokenid BIGINT NOT NULL, -- the container
74 childid BIGINT NOT NULL, -- the contained object
75 PRIMARY KEY (id),
76 INDEX Container_tokenid_key NONCLUSTERED (tokenid),
77 INDEX Container_childid_key NONCLUSTERED (childid),
78 INDEX Container_tokenid_childid_key NONCLUSTERED (tokenid, childid)
79);
80
81-- The users
82CREATE TABLE Client(
83 id BIGINT IDENTITY(1,1) NOT NULL,
84 nick NVARCHAR(512) NOT NULL DEFAULT '',
85 email NVARCHAR(512) NOT NULL DEFAULT '',
86 level TINYINT NOT NULL DEFAULT 0,
87 PRIMARY KEY (id),
88 UNIQUE (email)
89);
90
91-- The projects of the users
92CREATE TABLE Project(
93 id BIGINT IDENTITY(1,1) NOT NULL,
94 clientid BIGINT NOT NULL DEFAULT 0, -- the user
95 containerid BIGINT NOT NULL DEFAULT 0, -- the container
96 title NVARCHAR(512) NOT NULL DEFAULT '',
97 PRIMARY KEY (id),
98 INDEX Project_clientid_key NONCLUSTERED (clientid),
99 INDEX Project_containerid_key NONCLUSTERED (containerid),
100 INDEX Project_clientid_containerid_key NONCLUSTERED (clientid, containerid)
101);
102
103-- ----------------------------------------------------------------------
104-- Insert data into the tables
105
106-- Initialize the token types.
107INSERT INTO TokenType (title, extension, contenttype) VALUES
108('!Inline', '*.txt', 'text/plain'), -- id = 1
109('!Container', '', 'multipart/mixed'), -- id = 2
110('Text', '*.txt', 'text/plain'), -- id = 3
111('Binary', '*.*', 'application/octet-stream'),
112('CSV', '*.txt', 'text/csv'),
113('Image (PNG)', '*.png', 'image/png'),
114('Microsoft PowerPoint (OpenXML)', '*.pptx', 'application/vnd.openxmlformats-officedocument.presentationml.presentation'),
115('Microsoft Excel (OpenXML)', '*.xlsx', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'),
116('Microsoft Word (OpenXML)', '*.docx', 'application/vnd.openxmlformats-officedocument.wordprocessingml.document');
117
118-- Create 5 containers (tokentypeid = 2)
119INSERT INTO Token(tokentypeid, contents) VALUES (2, 'A new container'); -- id = 1
120INSERT INTO Token(tokentypeid, contents) VALUES (2, 'A second container'); -- id = 2
121INSERT INTO Token(tokentypeid, contents) VALUES (2, 'A third container'); -- id = 3
122INSERT INTO Token(tokentypeid, contents) VALUES (2, 'Empty container'); -- id = 4
123INSERT INTO Token(tokentypeid, contents) VALUES (2, 'Lonely container'); -- id = 5
124-- Create 5 inline files (tokentypeid = 1)
125INSERT INTO Token(tokentypeid, contents) VALUES (1, 'This is file 1'); -- id = 6
126INSERT INTO Token(tokentypeid, contents) VALUES (1, 'This is file 2'); -- id = 7
127INSERT INTO Token(tokentypeid, contents) VALUES (1, 'This is file 3'); -- id = 8
128INSERT INTO Token(tokentypeid, contents) VALUES (1, 'This is file 4'); -- id = 9
129INSERT INTO Token(tokentypeid, contents) VALUES (1, 'This is file 5'); -- id = 10
130-- Create a real file (tokentypeid = 3)
131INSERT INTO Token(tokentypeid, filename) VALUES (3, '/readme.txt'); -- id = 11
132
133-- Insert container 3 and 4 into the first container.
134INSERT INTO Container(tokenid, childid) VALUES (1, 3);
135INSERT INTO Container(tokenid, childid) VALUES (1, 4);
136
137-- Insert readme.txt into the first two containers
138INSERT INTO Container(tokenid, childid) VALUES (1, 11);
139INSERT INTO Container(tokenid, childid) VALUES (2, 11);
140
141-- Insert file 1 into the first three containers.
142INSERT INTO Container(tokenid, childid) VALUES (1, 6);
143INSERT INTO Container(tokenid, childid) VALUES (2, 6);
144INSERT INTO Container(tokenid, childid) VALUES (3, 6);
145
146-- Insert more files into the first three containers.
147INSERT INTO Container(tokenid, childid) VALUES (1, 7);
148INSERT INTO Container(tokenid, childid) VALUES (2, 8);
149INSERT INTO Container(tokenid, childid) VALUES (2, 9);
150INSERT INTO Container(tokenid, childid) VALUES (3, 10);
151
152INSERT INTO Client(nick, email) VALUES ('user1', 'hrgell@hotmail.com');
153INSERT INTO Project(clientid, containerid, title) VALUES (1, 1, 'First project');
154-- ----------------------------------------------------------------------
155-- Show the data
156
157SELECT 'Show all tokens' AS title;
158SELECT DISTINCT p.id, pt.title 'type', p.contents, p.filename
159FROM Token p
160INNER JOIN TokenType pt ON (p.tokentypeid = pt.id);
161
162SELECT 'Show all tokens of type !Container' AS title;
163SELECT DISTINCT p.id, p.contents 'container'
164FROM Token p
165WHERE p.tokentypeid = 2;
166
167SELECT 'Show tokens of type !Container that are not contained' AS title;
168SELECT DISTINCT p.id, p.contents 'container'
169FROM Token p
170LEFT JOIN Container f1 ON (f1.tokenid = p.id)
171LEFT JOIN Container f2 ON (f2.childid = p.id)
172WHERE p.tokentypeid = 2 AND f1.tokenid IS NULL AND f2.tokenid IS NULL;
173
174SELECT 'Show all containers' AS title;
175SELECT p.id, p.contents 'container', c.id 'id2', ct.title 'type', c.contents, c.filename
176FROM Container f
177INNER JOIN Token p ON (f.tokenid = p.id)
178INNER JOIN Token c ON (f.childid = c.id)
179INNER JOIN TokenType ct ON (c.tokentypeid = ct.id);
180
181SELECT 'Show child containers' AS title;
182SELECT c.id, c.contents 'container', p.id 'id2', p.contents 'location'
183FROM Container f
184INNER JOIN Token p ON (f.tokenid = p.id)
185INNER JOIN Token c ON (f.childid = c.id)
186WHERE p.tokentypeid = 2 AND c.tokentypeid = 2;
187
188SELECT 'Show containers that are not child containers' AS title;
189SELECT DISTINCT p.id, p.contents 'container'
190FROM Container f
191INNER JOIN Token p ON (f.tokenid = p.id)
192WHERE NOT EXISTS (SELECT id FROM Container f2 WHERE f2.childid = p.id);
193
194SELECT 'Show all projects for all clients' AS title;
195SELECT prj.id 'projectid', prj.title, u.id 'userid', u.email, f.id 'containerid', p.id, pt.title 'type', p.contents, p.filename
196FROM Client u
197INNER JOIN Project prj ON (prj.clientid = u.id)
198LEFT JOIN Container f ON (f.id = prj.containerid)
199LEFT JOIN Token p ON (p.id = f.tokenid)
200LEFT JOIN TokenType pt ON (p.tokentypeid = pt.id);
201
202-- TODO Insert a more complex project and show information about the projects of all users.
203
204SELECT 'Show the immediate contents of the first users first project' AS title;
205DECLARE @userid bigint, @containerid bigint;
206SELECT @userid = (SELECT TOP 1 Client.id FROM Client);
207SELECT @containerid = (SELECT TOP 1 Project.containerid FROM Project WHERE Project.clientid = @userid);
208SELECT c.id, c.contents, c.filename, ct.title 'type'
209FROM Container f
210INNER JOIN Token p ON (p.id = f.tokenid)
211INNER JOIN Token c ON (c.id = f.childid)
212INNER JOIN TokenType ct ON (c.tokentypeid = ct.id)
213WHERE p.id = @containerid;
214