Free cookie consent management tool by TermsFeed Policy Generator

source: trunk/sources/HeuristicLab.Services.Hive.DataAccess/3.3/SQL Scripts/Migrate to Filestream.sql @ 9665

Last change on this file since 9665 was 9665, checked in by ascheibe, 9 years ago

#2030 merged hive performance branch back into trunk

File size: 1.8 KB
Line 
1USE [HeuristicLab.Hive-3.3]
2
3EXEC sp_configure filestream_access_level, 2
4GO
5RECONFIGURE
6GO
7
8/**********************************************************/
9
10/* Move old Task Data */
11
12CREATE TABLE [dbo].[TaskDataTemp](
13  [TaskId] UniqueIdentifier RowGuidCol NOT NULL,
14  [Data] VarBinary(MAX) Filestream NOT NULL,
15  [LastUpdate] DateTime NOT NULL,
16  CONSTRAINT [PK_dbo.TaskDataTemp] PRIMARY KEY ([TaskId])
17)
18
19INSERT INTO dbo.TaskDataTemp (TaskId, Data, LastUpdate)
20SELECT TaskId, Data, LastUpdate
21FROM dbo.TaskData
22
23DELETE FROM dbo.TaskData
24
25/* Alter TaskId and Data Column */
26
27ALTER TABLE dbo.TaskData
28ALTER COLUMN [TaskId] ADD RowGuidCol
29
30ALTER TABLE dbo.TaskData
31DROP COLUMN Data
32
33ALTER TABLE dbo.TaskData
34ADD [Data] VarBinary(MAX) Filestream NOT NULL
35
36/* Insert data */
37
38INSERT INTO dbo.TaskData (TaskId, Data, LastUpdate)
39SELECT TaskId, Data, LastUpdate
40FROM dbo.TaskDataTemp
41
42DROP TABLE dbo.TaskDataTemp
43
44
45/**********************************************************/
46
47/* Move old Plugin Data */
48
49CREATE TABLE [dbo].[PluginDataTemp](
50  [PluginDataId] UniqueIdentifier RowGuidCol NOT NULL,
51  [PluginId] UniqueIdentifier NOT NULL,
52  [Data] VarBinary(MAX) FileStream NOT NULL,
53  [FileName] VarChar(MAX) NOT NULL,
54  CONSTRAINT [PK_dbo.PluginDataTemp] PRIMARY KEY ([PluginDataId])
55)
56
57INSERT INTO dbo.PluginDataTemp (PluginDataId, PluginId, Data, [FileName])
58SELECT PluginDataId, PluginId, Data, [FileName]
59FROM dbo.PluginData
60
61DELETE FROM dbo.PluginData
62
63/* Alter Data Column */
64
65ALTER TABLE dbo.PluginData
66DROP COLUMN Data
67
68ALTER TABLE dbo.PluginData
69ADD [Data] VarBinary(MAX) Filestream NOT NULL
70
71/* Insert data */
72
73INSERT INTO dbo.PluginData (PluginDataId, PluginId, Data, [FileName])
74SELECT PluginDataId, PluginId, Data, [FileName]
75FROM dbo.PluginDataTemp
76
77DROP TABLE dbo.PluginDataTemp
Note: See TracBrowser for help on using the repository browser.