I found a few discussions about using filestreams in Terrasoft applications. The last one was at http://www.community.terrasoft.ua/forum/topic/7454. So I decided to share instruction and some scripts, that are needed to setup filestreams for tbl_Files table, as a separate blog post. The solutions is tested in few projects.
It also can be used for tbl_MailMessage and other tables, that contains column with datatype IMAGE.
Why to setup filestreams is also discussed in previous topics.
After making these changes there might be a problem to add new workspace, that contains Files detail using TS Master window or PVC, because the service tbl_Files can not be saved correctly, so some workarounds are needed, for example, You can create workspace with Terrasoft Master window without Files detail, and add this detail manually, if needed.
1. Make DATABASE backup
2. CREATE filestream files
ALTER DATABASE DATABASE_NAME ADD filegroup FileStreamGroup1 CONTAINS filestream
ALTER DATABASE DATABASE_NAME ADD FILE (NAME = FileStreamFile1, FILENAME = 'f:\database\tcr_xxx_332_lt\FileStreamData') TO FILEGROUP FileStreamGroup1;
3. Make COLUMN copy
ALTER TABLE tbl_Files ADD FileDataBackup IMAGE;
UPDATE tbl_Files SET FileDataBackup = FileData;
4. FOR ALL tbl_FileIn.. do this
DROP TRIGGER tr_tbl_FileInAccount_ID
ALTER TABLE dbo.tbl_FileInAccount DROP CONSTRAINT FFileInAccountFileID
ALTER TABLE dbo.tbl_FileInAccount ADD CONSTRAINT FFileInAccountFileID FOREIGN KEY ( FileID ) REFERENCES dbo.tbl_Files ( ID ) ON UPDATE NO ACTION ON DELETE CASCADE
DROP TRIGGER tr_tbl_FileInContact_ID
ALTER TABLE dbo.tbl_FileInContact DROP CONSTRAINT FFileInContactFileID
ALTER TABLE dbo.tbl_FileInContact ADD CONSTRAINT FFileInContactFileID FOREIGN KEY ( FileID ) REFERENCES dbo.tbl_Files ( ID ) ON UPDATE NO ACTION ON DELETE CASCADE
...
5. tbl_FileRight AND tbl_Files instead of TRIGGER
ALTER TABLE dbo.tbl_FilesRight DROP CONSTRAINT FFilesRightRecordID
ALTER TABLE dbo.tbl_FilesRight ADD CONSTRAINT FFilesRightRecordID FOREIGN KEY ( RecordID ) REFERENCES dbo.tbl_Files ( ID ) ON UPDATE NO ACTION ON DELETE CASCADE
DROP TRIGGER tr_tbl_Files_ID
6. CHANGE FileData COLUMN type TO VARBINARY(MAX):
ALTER TABLE tbl_Files DROP COLUMN FileData;
ALTER TABLE tbl_Files ADD FileData VARBINARY(MAX) filestream;
7. Move DATA TO updated COLUMN
UPDATE tbl_Files SET FileData = FileDataBackup
ALTER TABLE tbl_Files DROP COLUMN FileDataBackup
8. DROP vw_Files triggers
DROP TRIGGER tr_vw_Files_ID
DROP TRIGGER tr_vw_Files_IU
9. Recreate files VIEW
ALTER VIEW [dbo].[vw_Files]
AS
SELECT [P].*
FROM [dbo].[tbl_Files] AS [P] WITH(nolock)
WHERE EXISTS (
SELECT * FROM [dbo].[tbl_FilesRight] AS [R] WITH(nolock)
WHERE (([R].[RecordID] = [P].[ID])
AND EXISTS(
SELECT * FROM [dbo].[tbl_UserAdminUnit] AS [U] WITH(nolock)
WHERE ([R].[AdminUnitID] = [U].[AdminUnitID])
AND ([U].[UserName] = SYSTEM_USER))))
GO
10. Make additional triggers OR modifications IN TS client side TO make correct access rights TO the files
