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

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

#2030 added description to db migration script

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