OpenSim-Test/OpenSim/Data/MSSQL/Resources/GridStore.migrations

:VERSION 1

BEGIN TRANSACTION

CREATE TABLE [dbo].[regions](
 [regionHandle] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
 [regionName] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
 [uuid] [varchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
 [regionRecvKey] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
 [regionSecret] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
 [regionSendKey] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
 [regionDataURI] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
 [serverIP] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
 [serverPort] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
 [serverURI] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
 [locX] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
 [locY] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
 [locZ] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
 [eastOverrideHandle] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
 [westOverrideHandle] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
 [southOverrideHandle] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
 [northOverrideHandle] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
 [regionAssetURI] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
 [regionAssetRecvKey] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
 [regionAssetSendKey] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
 [regionUserURI] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
 [regionUserRecvKey] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
 [regionUserSendKey] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
 [regionMapTexture] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
 [serverHttpPort] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
 [serverRemotingPort] [varchar](255) COLLATE Latin1_General_CI_AS NULL,
 [owner_uuid] [varchar](36) COLLATE Latin1_General_CI_AS NULL,
PRIMARY KEY CLUSTERED 
(
 [uuid] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

COMMIT


:VERSION 2

BEGIN TRANSACTION

CREATE TABLE Tmp_regions
	(
	uuid varchar(36) COLLATE Latin1_General_CI_AS NOT NULL,
	regionHandle bigint NULL,
	regionName varchar(20) NULL,
	regionRecvKey varchar(128) NULL,
	regionSendKey varchar(128) NULL,
	regionSecret varchar(128) NULL,
	regionDataURI varchar(128) NULL,
	serverIP varchar(64) NULL,
	serverPort int NULL,
	serverURI varchar(255) NULL,
	locX int NULL,
	locY int NULL,
	locZ int NULL,
	eastOverrideHandle bigint NULL,
	westOverrideHandle bigint NULL,
	southOverrideHandle bigint NULL,
	northOverrideHandle bigint NULL,
	regionAssetURI varchar(255) NULL,
	regionAssetRecvKey varchar(128) NULL,
	regionAssetSendKey varchar(128) NULL,
	regionUserURI varchar(255) NULL,
	regionUserRecvKey varchar(128) NULL,
	regionUserSendKey varchar(128) NULL,
	regionMapTexture varchar(36) NULL,
	serverHttpPort int NULL,
	serverRemotingPort int NULL,
	owner_uuid varchar(36) NULL,
	originUUID varchar(36) NOT NULL DEFAULT ('00000000-0000-0000-0000-000000000000')
	)  ON [PRIMARY]

IF EXISTS(SELECT * FROM regions)
	 EXEC('INSERT INTO Tmp_regions (uuid, regionHandle, regionName, regionRecvKey, regionSendKey, regionSecret, regionDataURI, serverIP, serverPort, serverURI, locX, locY, locZ, eastOverrideHandle, westOverrideHandle, southOverrideHandle, northOverrideHandle, regionAssetURI, regionAssetRecvKey, regionAssetSendKey, regionUserURI, regionUserRecvKey, regionUserSendKey, regionMapTexture, serverHttpPort, serverRemotingPort, owner_uuid)
		SELECT CONVERT(varchar(36), uuid), CONVERT(bigint, regionHandle), CONVERT(varchar(20), regionName), CONVERT(varchar(128), regionRecvKey), CONVERT(varchar(128), regionSendKey), CONVERT(varchar(128), regionSecret), CONVERT(varchar(128), regionDataURI), CONVERT(varchar(64), serverIP), CONVERT(int, serverPort), serverURI, CONVERT(int, locX), CONVERT(int, locY), CONVERT(int, locZ), CONVERT(bigint, eastOverrideHandle), CONVERT(bigint, westOverrideHandle), CONVERT(bigint, southOverrideHandle), CONVERT(bigint, northOverrideHandle), regionAssetURI, CONVERT(varchar(128), regionAssetRecvKey), CONVERT(varchar(128), regionAssetSendKey), regionUserURI, CONVERT(varchar(128), regionUserRecvKey), CONVERT(varchar(128), regionUserSendKey), CONVERT(varchar(36), regionMapTexture), CONVERT(int, serverHttpPort), CONVERT(int, serverRemotingPort), owner_uuid FROM regions')

DROP TABLE regions

EXECUTE sp_rename N'Tmp_regions', N'regions', 'OBJECT' 

ALTER TABLE regions ADD CONSTRAINT
	PK__regions__uuid PRIMARY KEY CLUSTERED 
	(
	uuid
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

COMMIT

:VERSION 3

BEGIN TRANSACTION

CREATE NONCLUSTERED INDEX IX_regions_name ON dbo.regions
	(
	regionName
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX IX_regions_handle ON dbo.regions
	(
	regionHandle
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


CREATE NONCLUSTERED INDEX IX_regions_override ON dbo.regions
	(
	eastOverrideHandle,
	westOverrideHandle,
	southOverrideHandle,
	northOverrideHandle
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

COMMIT


:VERSION 4

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION

CREATE TABLE dbo.Tmp_regions
	(
	uuid uniqueidentifier NOT NULL,
	regionHandle bigint NULL,
	regionName varchar(20) NULL,
	regionRecvKey varchar(128) NULL,
	regionSendKey varchar(128) NULL,
	regionSecret varchar(128) NULL,
	regionDataURI varchar(128) NULL,
	serverIP varchar(64) NULL,
	serverPort int NULL,
	serverURI varchar(255) NULL,
	locX int NULL,
	locY int NULL,
	locZ int NULL,
	eastOverrideHandle bigint NULL,
	westOverrideHandle bigint NULL,
	southOverrideHandle bigint NULL,
	northOverrideHandle bigint NULL,
	regionAssetURI varchar(255) NULL,
	regionAssetRecvKey varchar(128) NULL,
	regionAssetSendKey varchar(128) NULL,
	regionUserURI varchar(255) NULL,
	regionUserRecvKey varchar(128) NULL,
	regionUserSendKey varchar(128) NULL,
	regionMapTexture uniqueidentifier NULL,
	serverHttpPort int NULL,
	serverRemotingPort int NULL,
	owner_uuid uniqueidentifier NOT NULL,
	originUUID uniqueidentifier NOT NULL DEFAULT ('00000000-0000-0000-0000-000000000000')
	)  ON [PRIMARY]

IF EXISTS(SELECT * FROM dbo.regions)
	 EXEC('INSERT INTO dbo.Tmp_regions (uuid, regionHandle, regionName, regionRecvKey, regionSendKey, regionSecret, regionDataURI, serverIP, serverPort, serverURI, locX, locY, locZ, eastOverrideHandle, westOverrideHandle, southOverrideHandle, northOverrideHandle, regionAssetURI, regionAssetRecvKey, regionAssetSendKey, regionUserURI, regionUserRecvKey, regionUserSendKey, regionMapTexture, serverHttpPort, serverRemotingPort, owner_uuid, originUUID)
		SELECT CONVERT(uniqueidentifier, uuid), regionHandle, regionName, regionRecvKey, regionSendKey, regionSecret, regionDataURI, serverIP, serverPort, serverURI, locX, locY, locZ, eastOverrideHandle, westOverrideHandle, southOverrideHandle, northOverrideHandle, regionAssetURI, regionAssetRecvKey, regionAssetSendKey, regionUserURI, regionUserRecvKey, regionUserSendKey, CONVERT(uniqueidentifier, regionMapTexture), serverHttpPort, serverRemotingPort, CONVERT(uniqueidentifier, owner_uuid), CONVERT(uniqueidentifier, originUUID) FROM dbo.regions WITH (HOLDLOCK TABLOCKX)')

DROP TABLE dbo.regions

EXECUTE sp_rename N'dbo.Tmp_regions', N'regions', 'OBJECT' 

ALTER TABLE dbo.regions ADD CONSTRAINT
	PK__regions__uuid PRIMARY KEY CLUSTERED 
	(
	uuid
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX IX_regions_name ON dbo.regions
	(
	regionName
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX IX_regions_handle ON dbo.regions
	(
	regionHandle
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX IX_regions_override ON dbo.regions
	(
	eastOverrideHandle,
	westOverrideHandle,
	southOverrideHandle,
	northOverrideHandle
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

COMMIT


:VERSION 5

BEGIN TRANSACTION

ALTER TABLE regions ADD access int default 0;

COMMIT


:VERSION 6

BEGIN TRANSACTION

ALTER TABLE regions ADD scopeid uniqueidentifier default '00000000-0000-0000-0000-000000000000';
ALTER TABLE regions ADD DEFAULT ('00000000-0000-0000-0000-000000000000') FOR [owner_uuid];
ALTER TABLE regions ADD sizeX integer not null default 0;
ALTER TABLE regions ADD sizeY integer not null default 0;

COMMIT


:VERSION 7

BEGIN TRANSACTION

ALTER TABLE regions ADD [flags] integer NOT NULL DEFAULT 0;
CREATE INDEX [flags] ON regions(flags);
ALTER TABLE [regions] ADD [last_seen] integer NOT NULL DEFAULT 0;
ALTER TABLE [regions] ADD [PrincipalID] uniqueidentifier NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000';
ALTER TABLE [regions] ADD [Token] varchar(255) NOT NULL DEFAULT 0;

COMMIT

:VERSION 8

BEGIN TRANSACTION
ALTER TABLE regions ALTER COLUMN regionName VarChar(128) 

DROP INDEX IX_regions_name ON dbo.regions
ALTER TABLE regions ALTER COLUMN regionName VarChar(128) null

CREATE NONCLUSTERED INDEX IX_regions_name ON dbo.regions
	(
	regionName
	) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

COMMIT