Database file system, a practice

A record of my experiment. I will make it more complete later.

1. table design.
A file system entity is either a file or a directory. a file belongs to a directory. root directory belongs to itself.
1.1 ERD

1.2 Creation and Initialization
CREATE TABLE [dbo].[DBFile](
    [ID] [bigint] identity(0,1) NOT NULL,
    [Name] [varchar](250) NOT NULL,
    [Type] [smallint] NOT NULL,
    [Parent] [bigint] NULL,
    [Content] [varbinary](max) NULL,
 CONSTRAINT [PK_DBFile] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[DBFile]  WITH CHECK ADD  CONSTRAINT [FK_DBFile_DBFile] FOREIGN KEY([Parent])
REFERENCES [dbo].[DBFile] ([ID])
GO

ALTER TABLE [dbo].[DBFile] CHECK CONSTRAINT [FK_DBFile_DBFile]
GO

--initiate the root record
set IDENTITY_INSERT dbfile on
insert into dbfile (id,[Name],parent,type) values(0,'/',0,0)
set IDENTITY_INSERT dbfile off

2. interfaces and implementation
2.1create new file
create procedure usp_DBFile_Ins @parent bigint=0, @fileName varchar(200), @isDirectory bit=0, @fileId bigint output
as
begin
insert into dbfile ([Name],parent,type) values(@fileName,@parent,case when @isDirectory=0 then 1 else 0 end)
set @fileId= @@identity
end
2.2 update file's content
create procedure usp_DBFile_upd @fileId bigint, @content varbinary(max)
as
begin
update DBFile set Content=@content where ID=@fileId
if @@ROWCOUNT=0
begin
    RAISERROR ('Specified file ID is not existing.',1,1)
    return -1
end
return 0

end
2.3 type file content
create procedure usp_TypeFile @fileId bigint
as
begin
select ID fileId,name fileName,convert(varchar(max),content) from DBFile where ID=@fileId and [TYPE] = 1
if @@ROWCOUNT<1 br="br">    RAISERROR ('Specified ID is not a file.',1,1)
end

2.4 list directory/file

create procedure usp_ListDir @fileID bigint
as
begin
WITH dirFiles
as
(
--first level directory or file
select ID,name,case when [TYPE]=1 then 'File' else 'Directory' end FileType from DBFile where ID=@fileID
union all
select af.ID,af.name,case when af.[TYPE]=1 then 'File' else 'Directory' end FileType
from DBFile af join dirFiles df on af.Parent=df.ID
where af.ID>0
)
select * from dirFiles
end

2.5 Delete file

3. test codes

exec usp_DBFile_Ins 0,'C:',1
exec usp_DBFile_Ins 0,'D:',1
exec usp_DBFile_Ins 1,'testFile.txt',0

select * from dbfile

declare @content varbinary(100)
set @content=convert(varbinary(100),'test content')
exec usp_DBFile_upd 3,@content
exec usp_TypeFile 3

select convert(varchar(100),convert(varbinary(max),'test content'))

exec usp_ListDir 0

declare @fileId bigint
declare @content varbinary(100)
set @content=convert(varbinary(100),'test content. this file is on drive d.')
exec usp_DBFile_Ins 2,'testFileOnD.txt',0,@fileId output
select @fileId
exec usp_DBFile_upd @fileId,@content
exec usp_TypeFile 7
exec usp_ListDir 2