141 lines
4.5 KiB
Transact-SQL
141 lines
4.5 KiB
Transact-SQL
SET ANSI_NULLS ON;
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON;
|
|
GO
|
|
|
|
IF OBJECT_ID(N'dbo.UserFile', N'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.UserFile
|
|
(
|
|
UserFileID bigint IDENTITY(1,1) NOT NULL CONSTRAINT PK_UserFile PRIMARY KEY,
|
|
UserID int NOT NULL,
|
|
ProjectID int NULL,
|
|
EntityType nvarchar(100) NOT NULL,
|
|
EntityID int NOT NULL,
|
|
FileName nvarchar(300) NOT NULL,
|
|
OriginalFileName nvarchar(300) NULL,
|
|
ContentType nvarchar(200) NULL,
|
|
FileSizeBytes bigint NOT NULL,
|
|
StoragePath nvarchar(1000) NOT NULL,
|
|
UploadedDateUTC datetime2 NOT NULL CONSTRAINT DF_UserFile_UploadedDateUTC DEFAULT SYSUTCDATETIME(),
|
|
DeletedDateUTC datetime2 NULL,
|
|
IsDeleted bit NOT NULL CONSTRAINT DF_UserFile_IsDeleted DEFAULT 0,
|
|
CONSTRAINT FK_UserFile_AppUser FOREIGN KEY (UserID) REFERENCES dbo.AppUser(UserID),
|
|
CONSTRAINT FK_UserFile_Project FOREIGN KEY (ProjectID) REFERENCES dbo.Projects(ProjectID),
|
|
CONSTRAINT CK_UserFile_FileSizeBytes CHECK (FileSizeBytes >= 0)
|
|
);
|
|
END;
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_UserFile_User_Active' AND object_id = OBJECT_ID(N'dbo.UserFile'))
|
|
CREATE INDEX IX_UserFile_User_Active ON dbo.UserFile(UserID, IsDeleted, UploadedDateUTC DESC) INCLUDE (FileSizeBytes, ProjectID, EntityType, EntityID);
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_UserFile_Project_Entity' AND object_id = OBJECT_ID(N'dbo.UserFile'))
|
|
CREATE INDEX IX_UserFile_Project_Entity ON dbo.UserFile(ProjectID, EntityType, EntityID, IsDeleted);
|
|
GO
|
|
|
|
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name = N'IX_UserFile_StoragePath' AND object_id = OBJECT_ID(N'dbo.UserFile'))
|
|
CREATE INDEX IX_UserFile_StoragePath ON dbo.UserFile(StoragePath, IsDeleted);
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.Project_GetOwnerUserID
|
|
@ProjectID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT TOP (1) UserID
|
|
FROM dbo.ProjectUserAccess
|
|
WHERE ProjectID = @ProjectID
|
|
AND AccessRole = N'Owner'
|
|
AND IsActive = 1
|
|
ORDER BY ProjectUserAccessID;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.UserFile_Create
|
|
@UserID int,
|
|
@ProjectID int = NULL,
|
|
@EntityType nvarchar(100),
|
|
@EntityID int,
|
|
@FileName nvarchar(300),
|
|
@OriginalFileName nvarchar(300) = NULL,
|
|
@ContentType nvarchar(200) = NULL,
|
|
@FileSizeBytes bigint,
|
|
@StoragePath nvarchar(1000)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
INSERT dbo.UserFile (UserID, ProjectID, EntityType, EntityID, FileName, OriginalFileName, ContentType, FileSizeBytes, StoragePath)
|
|
VALUES (@UserID, @ProjectID, @EntityType, @EntityID, @FileName, @OriginalFileName, @ContentType, @FileSizeBytes, @StoragePath);
|
|
|
|
SELECT CAST(SCOPE_IDENTITY() AS bigint);
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.UserFile_MarkDeletedByStoragePath
|
|
@StoragePath nvarchar(1000)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
UPDATE dbo.UserFile
|
|
SET IsDeleted = 1,
|
|
DeletedDateUTC = COALESCE(DeletedDateUTC, SYSUTCDATETIME())
|
|
WHERE StoragePath = @StoragePath
|
|
AND IsDeleted = 0;
|
|
END;
|
|
GO
|
|
|
|
CREATE OR ALTER PROCEDURE dbo.UserFile_GetStorageUsageForOwner
|
|
@UserID int
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT CAST(COALESCE(SUM(FileSizeBytes), 0) AS bigint)
|
|
FROM dbo.UserFile
|
|
WHERE UserID = @UserID
|
|
AND IsDeleted = 0;
|
|
END;
|
|
GO
|
|
|
|
INSERT INTO dbo.UserFile (UserID, ProjectID, EntityType, EntityID, FileName, OriginalFileName, ContentType, FileSizeBytes, StoragePath, UploadedDateUTC)
|
|
SELECT ownerAccess.UserID,
|
|
b.ProjectID,
|
|
N'SceneAttachment',
|
|
a.SceneAttachmentID,
|
|
RIGHT(a.FilePath, CHARINDEX(N'/', REVERSE(a.FilePath + N'/')) - 1),
|
|
RIGHT(a.FilePath, CHARINDEX(N'/', REVERSE(a.FilePath + N'/')) - 1),
|
|
NULL,
|
|
0,
|
|
a.FilePath,
|
|
COALESCE(a.CreatedDateUTC, a.CreatedDate, SYSUTCDATETIME())
|
|
FROM dbo.SceneAttachments a
|
|
INNER JOIN dbo.Scenes s ON s.SceneID = a.SceneID
|
|
INNER JOIN dbo.Chapters c ON c.ChapterID = s.ChapterID
|
|
INNER JOIN dbo.Books b ON b.BookID = c.BookID
|
|
OUTER APPLY
|
|
(
|
|
SELECT TOP (1) UserID
|
|
FROM dbo.ProjectUserAccess
|
|
WHERE ProjectID = b.ProjectID
|
|
AND AccessRole = N'Owner'
|
|
AND IsActive = 1
|
|
ORDER BY ProjectUserAccessID
|
|
) ownerAccess
|
|
WHERE a.FilePath IS NOT NULL
|
|
AND a.FilePath LIKE N'/uploads/scenes/%'
|
|
AND ownerAccess.UserID IS NOT NULL
|
|
AND NOT EXISTS
|
|
(
|
|
SELECT 1
|
|
FROM dbo.UserFile existing
|
|
WHERE existing.StoragePath = a.FilePath
|
|
AND existing.EntityType = N'SceneAttachment'
|
|
AND existing.EntityID = a.SceneAttachmentID
|
|
);
|
|
GO
|