· 5 years ago · Sep 20, 2020, 06:24 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;
29END
30-- ----------------------------------------------------------------------
31-- Create the tables
32
33-- The token data types.
34CREATE TABLE TokenType(
35 id BIGINT IDENTITY(1,1) NOT NULL,
36 title NVARCHAR(512) NOT NULL,
37 extension NVARCHAR(512) NOT NULL DEFAULT '',
38 contenttype NVARCHAR(512) NOT NULL DEFAULT 'text/plain',
39 PRIMARY KEY (id),
40 UNIQUE (title)
41);
42
43-- The tokens.
44-- A token is a file, a piece of a file or a container.
45-- Contains all files uploaded or created.
46--
47-- If the token is '!Inline' then fetch the contents from 'contents'.
48-- If the token is '!Container' then fetch the contents from the Container table.
49-- Otherwise fetch the data on disk via 'filename'.
50--
51-- Using 'partname' we can refer to only part of a file or piece of data.
52--
53-- TODO Turn contents into a BLOB ?
54CREATE TABLE Token(
55 id BIGINT IDENTITY(1,1) NOT NULL,
56 tokentypeid BIGINT NOT NULL, -- !Inline, !Container, Binary, etc
57 filename NVARCHAR(512) NOT NULL DEFAULT '', -- location on disk on server
58 partname NVARCHAR(512) NOT NULL DEFAULT '', -- Only part of a file, like 'Slide 1'
59 contents NVARCHAR(MAX) NOT NULL DEFAULT '', -- The inline contents.
60 PRIMARY KEY (id),
61 INDEX token_tokentypeid_key NONCLUSTERED (tokentypeid),
62 INDEX token_filename_key NONCLUSTERED (filename)
63);
64
65-- The list of rules for the hierachical structure.
66-- The list of associations between tokens.
67-- The rules are binary in the way that each rule connects one token to one other token.
68-- Both 'tokenid' and 'childid' referes to Token.id.
69CREATE TABLE Container(
70 id BIGINT IDENTITY(1,1) NOT NULL,
71 tokenid BIGINT NOT NULL, -- the container
72 childid BIGINT NOT NULL, -- the contained object
73 PRIMARY KEY (id),
74 INDEX Container_tokenid_key NONCLUSTERED (tokenid),
75 INDEX Container_childid_key NONCLUSTERED (childid),
76 INDEX Container_tokenid_childid_key NONCLUSTERED (tokenid, childid)
77);
78-- ----------------------------------------------------------------------
79-- Insert data into the tables
80
81-- Initialize the token types.
82INSERT INTO TokenType (title, extension, contenttype) VALUES
83('!Inline', '*.txt', 'text/plain'), -- id = 1
84('!Container', '', 'multipart/mixed'), -- id = 2
85('Text', '*.txt', 'text/plain'), -- id = 3
86('Binary', '*.*', 'application/octet-stream'),
87('CSV', '*.txt', 'text/csv'),
88('Image (PNG)', '*.png', 'image/png'),
89('Microsoft PowerPoint (OpenXML)', '*.pptx', 'application/vnd.openxmlformats-officedocument.presentationml.presentation'),
90('Microsoft Excel (OpenXML)', '*.xlsx', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'),
91('Microsoft Word (OpenXML)', '*.docx', 'application/vnd.openxmlformats-officedocument.wordprocessingml.document');
92
93-- Create 5 containers (tokentypeid = 2)
94INSERT INTO Token(tokentypeid, contents) VALUES (2, 'A new container'); -- id = 1
95INSERT INTO Token(tokentypeid, contents) VALUES (2, 'A second container'); -- id = 2
96INSERT INTO Token(tokentypeid, contents) VALUES (2, 'A third container'); -- id = 3
97INSERT INTO Token(tokentypeid, contents) VALUES (2, 'Empty container'); -- id = 4
98INSERT INTO Token(tokentypeid, contents) VALUES (2, 'Lonely container'); -- id = 5
99-- Create 5 inline files (tokentypeid = 1)
100INSERT INTO Token(tokentypeid, contents) VALUES (1, 'This is file 1'); -- id = 6
101INSERT INTO Token(tokentypeid, contents) VALUES (1, 'This is file 2'); -- id = 7
102INSERT INTO Token(tokentypeid, contents) VALUES (1, 'This is file 3'); -- id = 8
103INSERT INTO Token(tokentypeid, contents) VALUES (1, 'This is file 4'); -- id = 9
104INSERT INTO Token(tokentypeid, contents) VALUES (1, 'This is file 5'); -- id = 10
105-- Create a real file (tokentypeid = 3)
106INSERT INTO Token(tokentypeid, filename) VALUES (3, '/readme.txt'); -- id = 11
107
108-- Insert container 3 and 4 into the first container.
109INSERT INTO Container(tokenid, childid) VALUES (1, 3);
110INSERT INTO Container(tokenid, childid) VALUES (1, 4);
111
112-- Insert readme.txt into the first two containers
113INSERT INTO Container(tokenid, childid) VALUES (1, 11);
114INSERT INTO Container(tokenid, childid) VALUES (2, 11);
115
116-- Insert file 1 into the first three containers.
117INSERT INTO Container(tokenid, childid) VALUES (1, 6);
118INSERT INTO Container(tokenid, childid) VALUES (2, 6);
119INSERT INTO Container(tokenid, childid) VALUES (3, 6);
120
121-- Insert more files into the first three containers.
122INSERT INTO Container(tokenid, childid) VALUES (1, 7);
123INSERT INTO Container(tokenid, childid) VALUES (2, 8);
124INSERT INTO Container(tokenid, childid) VALUES (2, 9);
125INSERT INTO Container(tokenid, childid) VALUES (3, 10);
126-- ----------------------------------------------------------------------
127-- Show the data
128SELECT 'Show all tokens of type !Container' AS title;
129SELECT DISTINCT p.id, p.contents 'container'
130FROM Token p
131WHERE p.tokentypeid = 2;
132
133SELECT 'Show tokens of type !Container that are not contained' AS title;
134SELECT DISTINCT p.id, p.contents 'container'
135FROM Token p
136LEFT JOIN Container f1 ON (f1.tokenid = p.id)
137LEFT JOIN Container f2 ON (f2.childid = p.id)
138WHERE p.tokentypeid = 2 AND f1.tokenid IS NULL AND f2.tokenid IS NULL;
139
140SELECT 'Show all containers' AS title;
141SELECT p.id, p.contents 'container', c.id 'id2', ct.title 'type', c.contents, c.filename
142FROM Container f
143INNER JOIN Token p ON (f.tokenid = p.id)
144INNER JOIN Token c ON (f.childid = c.id)
145INNER JOIN TokenType ct ON (c.tokentypeid = ct.id);
146
147SELECT 'Show child containers' AS title;
148SELECT c.id, c.contents 'container', p.id 'id2', p.contents 'location'
149FROM Container f
150inner join Token p ON (f.tokenid = p.id)
151inner join Token c ON (f.childid = c.id)
152WHERE p.tokentypeid = 2 AND c.tokentypeid = 2;
153
154SELECT 'Show containers that are not child containers' AS title;
155SELECT DISTINCT p.id, p.contents 'container'
156FROM Container f
157INNER JOIN Token p ON (f.tokenid = p.id)
158WHERE NOT EXISTS (SELECT id FROM Container f2 WHERE f2.childid = p.id);
159
160