From 281955949910eb257b5f7e42e54535ba7812418e Mon Sep 17 00:00:00 2001 From: Melanie Thielker Date: Sun, 14 Sep 2008 13:23:02 +0000 Subject: Mantis #2124 Thank you, RuudL, for a patch that brings MSSQL up to the same implementation level as MySQL. --- OpenSim/Data/MSSQL/Resources/001_AssetStore.sql | 13 ++ OpenSim/Data/MSSQL/Resources/001_GridStore.sql | 37 ++++ .../Data/MSSQL/Resources/001_InventoryStore.sql | 64 ++++++ OpenSim/Data/MSSQL/Resources/001_UserStore.sql | 224 +++++++++++++++++++++ OpenSim/Data/MSSQL/Resources/002_AssetStore.sql | 29 +++ OpenSim/Data/MSSQL/Resources/002_UserStore.sql | 18 ++ OpenSim/Data/MSSQL/Resources/003_GridStore.sql | 22 ++ OpenSim/Data/MSSQL/Resources/003_UserStore.sql | 30 +++ OpenSim/Data/MSSQL/Resources/004_RegionStore.sql | 12 +- OpenSim/Data/MSSQL/Resources/004_UserStore.sql | 58 ++++++ OpenSim/Data/MSSQL/Resources/005_RegionStore.sql | 16 +- 11 files changed, 509 insertions(+), 14 deletions(-) create mode 100644 OpenSim/Data/MSSQL/Resources/001_AssetStore.sql create mode 100644 OpenSim/Data/MSSQL/Resources/001_GridStore.sql create mode 100644 OpenSim/Data/MSSQL/Resources/001_InventoryStore.sql create mode 100644 OpenSim/Data/MSSQL/Resources/001_UserStore.sql create mode 100644 OpenSim/Data/MSSQL/Resources/002_AssetStore.sql create mode 100644 OpenSim/Data/MSSQL/Resources/002_UserStore.sql create mode 100644 OpenSim/Data/MSSQL/Resources/003_GridStore.sql create mode 100644 OpenSim/Data/MSSQL/Resources/003_UserStore.sql create mode 100644 OpenSim/Data/MSSQL/Resources/004_UserStore.sql (limited to 'OpenSim/Data/MSSQL/Resources') diff --git a/OpenSim/Data/MSSQL/Resources/001_AssetStore.sql b/OpenSim/Data/MSSQL/Resources/001_AssetStore.sql new file mode 100644 index 0000000..2b293c7 --- /dev/null +++ b/OpenSim/Data/MSSQL/Resources/001_AssetStore.sql @@ -0,0 +1,13 @@ +CREATE TABLE [assets] ( + [id] [varchar](36) NOT NULL, + [name] [varchar](64) NOT NULL, + [description] [varchar](64) NOT NULL, + [assetType] [tinyint] NOT NULL, + [local] [tinyint] NOT NULL, + [temporary] [tinyint] NOT NULL, + [data] [image] NOT NULL, +PRIMARY KEY CLUSTERED +( + [id] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] diff --git a/OpenSim/Data/MSSQL/Resources/001_GridStore.sql b/OpenSim/Data/MSSQL/Resources/001_GridStore.sql new file mode 100644 index 0000000..ff15f54 --- /dev/null +++ b/OpenSim/Data/MSSQL/Resources/001_GridStore.sql @@ -0,0 +1,37 @@ +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 diff --git a/OpenSim/Data/MSSQL/Resources/001_InventoryStore.sql b/OpenSim/Data/MSSQL/Resources/001_InventoryStore.sql new file mode 100644 index 0000000..836d2d1 --- /dev/null +++ b/OpenSim/Data/MSSQL/Resources/001_InventoryStore.sql @@ -0,0 +1,64 @@ +BEGIN TRANSACTION + +CREATE TABLE [inventoryfolders] ( + [folderID] [varchar](36) NOT NULL default '', + [agentID] [varchar](36) default NULL, + [parentFolderID] [varchar](36) default NULL, + [folderName] [varchar](64) default NULL, + [type] [smallint] NOT NULL default 0, + [version] [int] NOT NULL default 0, + PRIMARY KEY CLUSTERED +( + [folderID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] + +CREATE NONCLUSTERED INDEX [owner] ON [inventoryfolders] +( + [agentID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + +CREATE NONCLUSTERED INDEX [parent] ON [inventoryfolders] +( + [parentFolderID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + + +CREATE TABLE [inventoryitems] ( + [inventoryID] [varchar](36) NOT NULL default '', + [assetID] [varchar](36) default NULL, + [assetType] [int] default NULL, + [parentFolderID] [varchar](36) default NULL, + [avatarID] [varchar](36) default NULL, + [inventoryName] [varchar](64) default NULL, + [inventoryDescription] [varchar](128) default NULL, + [inventoryNextPermissions] [int] default NULL, + [inventoryCurrentPermissions] [int] default NULL, + [invType] [int] default NULL, + [creatorID] [varchar](36) default NULL, + [inventoryBasePermissions] [int] NOT NULL default 0, + [inventoryEveryOnePermissions] [int] NOT NULL default 0, + [salePrice] [int] default NULL, + [saleType] [tinyint] default NULL, + [creationDate] [int] default NULL, + [groupID] [varchar](36) default NULL, + [groupOwned] [bit] default NULL, + [flags] [int] default NULL, + PRIMARY KEY CLUSTERED +( + [inventoryID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] + + +CREATE NONCLUSTERED INDEX [owner] ON [inventoryitems] +( + [avatarID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + +CREATE NONCLUSTERED INDEX [folder] ON [inventoryitems] +( + [parentFolderID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + +COMMIT diff --git a/OpenSim/Data/MSSQL/Resources/001_UserStore.sql b/OpenSim/Data/MSSQL/Resources/001_UserStore.sql new file mode 100644 index 0000000..abd6ff2 --- /dev/null +++ b/OpenSim/Data/MSSQL/Resources/001_UserStore.sql @@ -0,0 +1,224 @@ +CREATE TABLE [users] ( + [UUID] [varchar](36) NOT NULL default '', + [username] [varchar](32) NOT NULL, + [lastname] [varchar](32) NOT NULL, + [passwordHash] [varchar](32) NOT NULL, + [passwordSalt] [varchar](32) NOT NULL, + [homeRegion] [bigint] default NULL, + [homeLocationX] [float] default NULL, + [homeLocationY] [float] default NULL, + [homeLocationZ] [float] default NULL, + [homeLookAtX] [float] default NULL, + [homeLookAtY] [float] default NULL, + [homeLookAtZ] [float] default NULL, + [created] [int] NOT NULL, + [lastLogin] [int] NOT NULL, + [userInventoryURI] [varchar](255) default NULL, + [userAssetURI] [varchar](255) default NULL, + [profileCanDoMask] [int] default NULL, + [profileWantDoMask] [int] default NULL, + [profileAboutText] [ntext], + [profileFirstText] [ntext], + [profileImage] [varchar](36) default NULL, + [profileFirstImage] [varchar](36) default NULL, + [webLoginKey] [varchar](36) default NULL, + PRIMARY KEY CLUSTERED +( + [UUID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] + + +CREATE NONCLUSTERED INDEX [usernames] ON [users] +( + [username] ASC, + [lastname] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + + +CREATE TABLE [agents] ( + [UUID] [varchar](36) NOT NULL, + [sessionID] [varchar](36) NOT NULL, + [secureSessionID] [varchar](36) NOT NULL, + [agentIP] [varchar](16) NOT NULL, + [agentPort] [int] NOT NULL, + [agentOnline] [tinyint] NOT NULL, + [loginTime] [int] NOT NULL, + [logoutTime] [int] NOT NULL, + [currentRegion] [varchar](36) NOT NULL, + [currentHandle] [bigint] NOT NULL, + [currentPos] [varchar](64) NOT NULL, + PRIMARY KEY CLUSTERED +( + [UUID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] + + +CREATE NONCLUSTERED INDEX [session] ON [agents] +( + [sessionID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + +CREATE NONCLUSTERED INDEX [ssession] ON [agents] +( + [secureSessionID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + + +CREATE TABLE [dbo].[userfriends]( + [ownerID] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL, + [friendID] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL, + [friendPerms] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, + [datetimestamp] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL +) ON [PRIMARY] + +CREATE TABLE [avatarappearance] ( + [Owner] [varchar](36) NOT NULL, + [Serial] int NOT NULL, + [Visual_Params] [image] NOT NULL, + [Texture] [image] NOT NULL, + [Avatar_Height] float NOT NULL, + [Body_Item] [varchar](36) NOT NULL, + [Body_Asset] [varchar](36) NOT NULL, + [Skin_Item] [varchar](36) NOT NULL, + [Skin_Asset] [varchar](36) NOT NULL, + [Hair_Item] [varchar](36) NOT NULL, + [Hair_Asset] [varchar](36) NOT NULL, + [Eyes_Item] [varchar](36) NOT NULL, + [Eyes_Asset] [varchar](36) NOT NULL, + [Shirt_Item] [varchar](36) NOT NULL, + [Shirt_Asset] [varchar](36) NOT NULL, + [Pants_Item] [varchar](36) NOT NULL, + [Pants_Asset] [varchar](36) NOT NULL, + [Shoes_Item] [varchar](36) NOT NULL, + [Shoes_Asset] [varchar](36) NOT NULL, + [Socks_Item] [varchar](36) NOT NULL, + [Socks_Asset] [varchar](36) NOT NULL, + [Jacket_Item] [varchar](36) NOT NULL, + [Jacket_Asset] [varchar](36) NOT NULL, + [Gloves_Item] [varchar](36) NOT NULL, + [Gloves_Asset] [varchar](36) NOT NULL, + [Undershirt_Item] [varchar](36) NOT NULL, + [Undershirt_Asset] [varchar](36) NOT NULL, + [Underpants_Item] [varchar](36) NOT NULL, + [Underpants_Asset] [varchar](36) NOT NULL, + [Skirt_Item] [varchar](36) NOT NULL, + [Skirt_Asset] [varchar](36) NOT NULL, + + PRIMARY KEY CLUSTERED ( + [Owner] + ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] +CREATE TABLE [users] ( + [UUID] [varchar](36) NOT NULL default '', + [username] [varchar](32) NOT NULL, + [lastname] [varchar](32) NOT NULL, + [passwordHash] [varchar](32) NOT NULL, + [passwordSalt] [varchar](32) NOT NULL, + [homeRegion] [bigint] default NULL, + [homeLocationX] [float] default NULL, + [homeLocationY] [float] default NULL, + [homeLocationZ] [float] default NULL, + [homeLookAtX] [float] default NULL, + [homeLookAtY] [float] default NULL, + [homeLookAtZ] [float] default NULL, + [created] [int] NOT NULL, + [lastLogin] [int] NOT NULL, + [userInventoryURI] [varchar](255) default NULL, + [userAssetURI] [varchar](255) default NULL, + [profileCanDoMask] [int] default NULL, + [profileWantDoMask] [int] default NULL, + [profileAboutText] [ntext], + [profileFirstText] [ntext], + [profileImage] [varchar](36) default NULL, + [profileFirstImage] [varchar](36) default NULL, + [webLoginKey] [varchar](36) default NULL, + PRIMARY KEY CLUSTERED +( + [UUID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] + + +CREATE NONCLUSTERED INDEX [usernames] ON [users] +( + [username] ASC, + [lastname] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + + +CREATE TABLE [agents] ( + [UUID] [varchar](36) NOT NULL, + [sessionID] [varchar](36) NOT NULL, + [secureSessionID] [varchar](36) NOT NULL, + [agentIP] [varchar](16) NOT NULL, + [agentPort] [int] NOT NULL, + [agentOnline] [tinyint] NOT NULL, + [loginTime] [int] NOT NULL, + [logoutTime] [int] NOT NULL, + [currentRegion] [varchar](36) NOT NULL, + [currentHandle] [bigint] NOT NULL, + [currentPos] [varchar](64) NOT NULL, + PRIMARY KEY CLUSTERED +( + [UUID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] + + +CREATE NONCLUSTERED INDEX [session] ON [agents] +( + [sessionID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + +CREATE NONCLUSTERED INDEX [ssession] ON [agents] +( + [secureSessionID] ASC +)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + + +CREATE TABLE [dbo].[userfriends]( + [ownerID] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL, + [friendID] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL, + [friendPerms] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, + [datetimestamp] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL +) ON [PRIMARY] + +CREATE TABLE [avatarappearance] ( + [Owner] [varchar](36) NOT NULL, + [Serial] int NOT NULL, + [Visual_Params] [image] NOT NULL, + [Texture] [image] NOT NULL, + [Avatar_Height] float NOT NULL, + [Body_Item] [varchar](36) NOT NULL, + [Body_Asset] [varchar](36) NOT NULL, + [Skin_Item] [varchar](36) NOT NULL, + [Skin_Asset] [varchar](36) NOT NULL, + [Hair_Item] [varchar](36) NOT NULL, + [Hair_Asset] [varchar](36) NOT NULL, + [Eyes_Item] [varchar](36) NOT NULL, + [Eyes_Asset] [varchar](36) NOT NULL, + [Shirt_Item] [varchar](36) NOT NULL, + [Shirt_Asset] [varchar](36) NOT NULL, + [Pants_Item] [varchar](36) NOT NULL, + [Pants_Asset] [varchar](36) NOT NULL, + [Shoes_Item] [varchar](36) NOT NULL, + [Shoes_Asset] [varchar](36) NOT NULL, + [Socks_Item] [varchar](36) NOT NULL, + [Socks_Asset] [varchar](36) NOT NULL, + [Jacket_Item] [varchar](36) NOT NULL, + [Jacket_Asset] [varchar](36) NOT NULL, + [Gloves_Item] [varchar](36) NOT NULL, + [Gloves_Asset] [varchar](36) NOT NULL, + [Undershirt_Item] [varchar](36) NOT NULL, + [Undershirt_Asset] [varchar](36) NOT NULL, + [Underpants_Item] [varchar](36) NOT NULL, + [Underpants_Asset] [varchar](36) NOT NULL, + [Skirt_Item] [varchar](36) NOT NULL, + [Skirt_Asset] [varchar](36) NOT NULL, + + PRIMARY KEY CLUSTERED ( + [Owner] + ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] diff --git a/OpenSim/Data/MSSQL/Resources/002_AssetStore.sql b/OpenSim/Data/MSSQL/Resources/002_AssetStore.sql new file mode 100644 index 0000000..3e24543 --- /dev/null +++ b/OpenSim/Data/MSSQL/Resources/002_AssetStore.sql @@ -0,0 +1,29 @@ +BEGIN TRANSACTION + +CREATE TABLE Tmp_assets + ( + id varchar(36) NOT NULL, + name varchar(64) NOT NULL, + description varchar(64) NOT NULL, + assetType tinyint NOT NULL, + local bit NOT NULL, + temporary bit NOT NULL, + data image NOT NULL + ) ON [PRIMARY] + TEXTIMAGE_ON [PRIMARY] + +IF EXISTS(SELECT * FROM assets) + EXEC('INSERT INTO Tmp_assets (id, name, description, assetType, local, temporary, data) + SELECT id, name, description, assetType, CONVERT(bit, local), CONVERT(bit, temporary), data FROM assets WITH (HOLDLOCK TABLOCKX)') + +DROP TABLE assets + +EXECUTE sp_rename N'Tmp_assets', N'assets', 'OBJECT' + +ALTER TABLE dbo.assets ADD CONSTRAINT + PK__assets__id PRIMARY KEY CLUSTERED + ( + id + ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + +COMMIT diff --git a/OpenSim/Data/MSSQL/Resources/002_UserStore.sql b/OpenSim/Data/MSSQL/Resources/002_UserStore.sql new file mode 100644 index 0000000..a3b5868 --- /dev/null +++ b/OpenSim/Data/MSSQL/Resources/002_UserStore.sql @@ -0,0 +1,18 @@ +BEGIN TRANSACTION + +ALTER TABLE users ADD homeRegionID varchar(36) NOT NULL default '00000000-0000-0000-0000-000000000000'; +ALTER TABLE users ADD userFlags int NOT NULL default 0; +ALTER TABLE users ADD godLevel int NOT NULL default 0; +ALTER TABLE users ADD customType varchar(32) not null default ''; +ALTER TABLE users ADD partner varchar(36) not null default '00000000-0000-0000-0000-000000000000'; + +COMMIT +BEGIN TRANSACTION + +ALTER TABLE users ADD homeRegionID varchar(36) NOT NULL default '00000000-0000-0000-0000-000000000000'; +ALTER TABLE users ADD userFlags int NOT NULL default 0; +ALTER TABLE users ADD godLevel int NOT NULL default 0; +ALTER TABLE users ADD customType varchar(32) not null default ''; +ALTER TABLE users ADD partner varchar(36) not null default '00000000-0000-0000-0000-000000000000'; + +COMMIT diff --git a/OpenSim/Data/MSSQL/Resources/003_GridStore.sql b/OpenSim/Data/MSSQL/Resources/003_GridStore.sql new file mode 100644 index 0000000..e080947 --- /dev/null +++ b/OpenSim/Data/MSSQL/Resources/003_GridStore.sql @@ -0,0 +1,22 @@ +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 diff --git a/OpenSim/Data/MSSQL/Resources/003_UserStore.sql b/OpenSim/Data/MSSQL/Resources/003_UserStore.sql new file mode 100644 index 0000000..c2821e6 --- /dev/null +++ b/OpenSim/Data/MSSQL/Resources/003_UserStore.sql @@ -0,0 +1,30 @@ +BEGIN TRANSACTION + +CREATE TABLE [avatarattachments] ( + [UUID] varchar(36) NOT NULL + , [attachpoint] int NOT NULL + , [item] varchar(36) NOT NULL + , [asset] varchar(36) NOT NULL) + +CREATE NONCLUSTERED INDEX IX_avatarattachments ON dbo.avatarattachments + ( + UUID + ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + + +COMMIT +BEGIN TRANSACTION + +CREATE TABLE [avatarattachments] ( + [UUID] varchar(36) NOT NULL + , [attachpoint] int NOT NULL + , [item] varchar(36) NOT NULL + , [asset] varchar(36) NOT NULL) + +CREATE NONCLUSTERED INDEX IX_avatarattachments ON dbo.avatarattachments + ( + UUID + ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + + +COMMIT diff --git a/OpenSim/Data/MSSQL/Resources/004_RegionStore.sql b/OpenSim/Data/MSSQL/Resources/004_RegionStore.sql index 4e64901..15b39a7 100644 --- a/OpenSim/Data/MSSQL/Resources/004_RegionStore.sql +++ b/OpenSim/Data/MSSQL/Resources/004_RegionStore.sql @@ -1,6 +1,6 @@ BEGIN TRANSACTION -CREATE TABLE dbo.Tmp_primitems +CREATE TABLE Tmp_primitems ( itemID varchar(36) NOT NULL, primID varchar(36) NULL, @@ -22,15 +22,15 @@ CREATE TABLE dbo.Tmp_primitems groupPermissions int NULL ) ON [PRIMARY] -IF EXISTS(SELECT * FROM dbo.primitems) - EXEC('INSERT INTO dbo.Tmp_primitems (itemID, primID, assetID, parentFolderID, invType, assetType, name, description, creationDate, creatorID, ownerID, lastOwnerID, groupID, nextPermissions, currentPermissions, basePermissions, everyonePermissions, groupPermissions) +IF EXISTS(SELECT * FROM primitems) + EXEC('INSERT INTO Tmp_primitems (itemID, primID, assetID, parentFolderID, invType, assetType, name, description, creationDate, creatorID, ownerID, lastOwnerID, groupID, nextPermissions, currentPermissions, basePermissions, everyonePermissions, groupPermissions) SELECT CONVERT(varchar(36), itemID), CONVERT(varchar(36), primID), CONVERT(varchar(36), assetID), CONVERT(varchar(36), parentFolderID), invType, assetType, name, description, creationDate, CONVERT(varchar(36), creatorID), CONVERT(varchar(36), ownerID), CONVERT(varchar(36), lastOwnerID), CONVERT(varchar(36), groupID), nextPermissions, currentPermissions, basePermissions, everyonePermissions, groupPermissions') -DROP TABLE dbo.primitems +DROP TABLE primitems -EXECUTE sp_rename N'dbo.Tmp_primitems', N'primitems', 'OBJECT' +EXECUTE sp_rename N'Tmp_primitems', N'primitems', 'OBJECT' -ALTER TABLE dbo.primitems ADD CONSTRAINT +ALTER TABLE primitems ADD CONSTRAINT PK__primitems__0A688BB1 PRIMARY KEY CLUSTERED ( itemID diff --git a/OpenSim/Data/MSSQL/Resources/004_UserStore.sql b/OpenSim/Data/MSSQL/Resources/004_UserStore.sql new file mode 100644 index 0000000..f3e223e --- /dev/null +++ b/OpenSim/Data/MSSQL/Resources/004_UserStore.sql @@ -0,0 +1,58 @@ +BEGIN TRANSACTION + +CREATE TABLE Tmp_userfriends + ( + ownerID varchar(36) NOT NULL, + friendID varchar(36) NOT NULL, + friendPerms int NOT NULL, + datetimestamp int NOT NULL + ) ON [PRIMARY] + +IF EXISTS(SELECT * FROM userfriends) + EXEC('INSERT INTO dbo.Tmp_userfriends (ownerID, friendID, friendPerms, datetimestamp) + SELECT CONVERT(varchar(36), ownerID), CONVERT(varchar(36), friendID), CONVERT(int, friendPerms), CONVERT(int, datetimestamp) FROM dbo.userfriends WITH (HOLDLOCK TABLOCKX)') + +DROP TABLE dbo.userfriends + +EXECUTE sp_rename N'Tmp_userfriends', N'userfriends', 'OBJECT' + +CREATE NONCLUSTERED INDEX IX_userfriends_ownerID ON userfriends + ( + ownerID + ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + +CREATE NONCLUSTERED INDEX IX_userfriends_friendID ON userfriends + ( + friendID + ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + +COMMIT +BEGIN TRANSACTION + +CREATE TABLE Tmp_userfriends + ( + ownerID varchar(36) NOT NULL, + friendID varchar(36) NOT NULL, + friendPerms int NOT NULL, + datetimestamp int NOT NULL + ) ON [PRIMARY] + +IF EXISTS(SELECT * FROM dbo.userfriends) + EXEC('INSERT INTO dbo.Tmp_userfriends (ownerID, friendID, friendPerms, datetimestamp) + SELECT CONVERT(varchar(36), ownerID), CONVERT(varchar(36), friendID), CONVERT(int, friendPerms), CONVERT(int, datetimestamp) FROM dbo.userfriends WITH (HOLDLOCK TABLOCKX)') + +DROP TABLE dbo.userfriends + +EXECUTE sp_rename N'Tmp_userfriends', N'userfriends', 'OBJECT' + +CREATE NONCLUSTERED INDEX IX_userfriends_ownerID ON userfriends + ( + ownerID + ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + +CREATE NONCLUSTERED INDEX IX_userfriends_friendID ON userfriends + ( + friendID + ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] + +COMMIT diff --git a/OpenSim/Data/MSSQL/Resources/005_RegionStore.sql b/OpenSim/Data/MSSQL/Resources/005_RegionStore.sql index 74e9d85..eb0862c 100644 --- a/OpenSim/Data/MSSQL/Resources/005_RegionStore.sql +++ b/OpenSim/Data/MSSQL/Resources/005_RegionStore.sql @@ -1,6 +1,6 @@ BEGIN TRANSACTION -CREATE TABLE dbo.Tmp_primshapes +CREATE TABLE Tmp_primshapes ( UUID varchar(36) NOT NULL, Shape int NULL, @@ -32,18 +32,18 @@ CREATE TABLE dbo.Tmp_primshapes ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] -IF EXISTS(SELECT * FROM dbo.primshapes) - EXEC('INSERT INTO dbo.Tmp_primshapes (UUID, Shape, ScaleX, ScaleY, ScaleZ, PCode, PathBegin, PathEnd, PathScaleX, PathScaleY, PathShearX, PathShearY, PathSkew, PathCurve, PathRadiusOffset, PathRevolutions, PathTaperX, PathTaperY, PathTwist, PathTwistBegin, ProfileBegin, ProfileEnd, ProfileCurve, ProfileHollow, State, Texture, ExtraParams) - SELECT CONVERT(varchar(36), UUID), Shape, ScaleX, ScaleY, ScaleZ, PCode, PathBegin, PathEnd, PathScaleX, PathScaleY, PathShearX, PathShearY, PathSkew, PathCurve, PathRadiusOffset, PathRevolutions, PathTaperX, PathTaperY, PathTwist, PathTwistBegin, ProfileBegin, ProfileEnd, ProfileCurve, ProfileHollow, State, Texture, ExtraParams FROM dbo.primshapes WITH (HOLDLOCK TABLOCKX)') +IF EXISTS(SELECT * FROM primshapes) + EXEC('INSERT INTO Tmp_primshapes (UUID, Shape, ScaleX, ScaleY, ScaleZ, PCode, PathBegin, PathEnd, PathScaleX, PathScaleY, PathShearX, PathShearY, PathSkew, PathCurve, PathRadiusOffset, PathRevolutions, PathTaperX, PathTaperY, PathTwist, PathTwistBegin, ProfileBegin, ProfileEnd, ProfileCurve, ProfileHollow, State, Texture, ExtraParams) + SELECT CONVERT(varchar(36), UUID), Shape, ScaleX, ScaleY, ScaleZ, PCode, PathBegin, PathEnd, PathScaleX, PathScaleY, PathShearX, PathShearY, PathSkew, PathCurve, PathRadiusOffset, PathRevolutions, PathTaperX, PathTaperY, PathTwist, PathTwistBegin, ProfileBegin, ProfileEnd, ProfileCurve, ProfileHollow, State, Texture, ExtraParams FROM primshapes WITH (HOLDLOCK TABLOCKX)') -DROP TABLE dbo.primshapes +DROP TABLE primshapes -EXECUTE sp_rename N'dbo.Tmp_primshapes', N'primshapes', 'OBJECT' +EXECUTE sp_rename N'Tmp_primshapes', N'primshapes', 'OBJECT' -ALTER TABLE dbo.primshapes ADD CONSTRAINT +ALTER TABLE primshapes ADD CONSTRAINT PK__primshapes__0880433F PRIMARY KEY CLUSTERED ( UUID ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] -COMMIT \ No newline at end of file +COMMIT -- cgit v1.1