From c52c68f314c67c76c7181a6d0828f476290fbd66 Mon Sep 17 00:00:00 2001 From: Sean Dague Date: Wed, 2 Apr 2008 15:24:31 +0000 Subject: whole lot more moving --- OpenSim/Data/MSSQL/Resources/AvatarAppearance.sql | 44 ++++++++++++++++++++++ OpenSim/Data/MSSQL/Resources/CreateAssetsTable.sql | 19 ++++++++++ .../Data/MSSQL/Resources/CreateFoldersTable.sql | 27 +++++++++++++ OpenSim/Data/MSSQL/Resources/CreateItemsTable.sql | 39 +++++++++++++++++++ .../MSSQL/Resources/CreateUserFriendsTable.sql | 14 +++++++ OpenSim/Data/MSSQL/Resources/Mssql-agents.sql | 37 ++++++++++++++++++ OpenSim/Data/MSSQL/Resources/Mssql-logs.sql | 20 ++++++++++ OpenSim/Data/MSSQL/Resources/Mssql-regions.sql | 41 ++++++++++++++++++++ OpenSim/Data/MSSQL/Resources/Mssql-users.sql | 42 +++++++++++++++++++++ 9 files changed, 283 insertions(+) create mode 100644 OpenSim/Data/MSSQL/Resources/AvatarAppearance.sql create mode 100644 OpenSim/Data/MSSQL/Resources/CreateAssetsTable.sql create mode 100644 OpenSim/Data/MSSQL/Resources/CreateFoldersTable.sql create mode 100644 OpenSim/Data/MSSQL/Resources/CreateItemsTable.sql create mode 100644 OpenSim/Data/MSSQL/Resources/CreateUserFriendsTable.sql create mode 100644 OpenSim/Data/MSSQL/Resources/Mssql-agents.sql create mode 100644 OpenSim/Data/MSSQL/Resources/Mssql-logs.sql create mode 100644 OpenSim/Data/MSSQL/Resources/Mssql-regions.sql create mode 100644 OpenSim/Data/MSSQL/Resources/Mssql-users.sql (limited to 'OpenSim/Data/MSSQL/Resources') diff --git a/OpenSim/Data/MSSQL/Resources/AvatarAppearance.sql b/OpenSim/Data/MSSQL/Resources/AvatarAppearance.sql new file mode 100644 index 0000000..ccefba2 --- /dev/null +++ b/OpenSim/Data/MSSQL/Resources/AvatarAppearance.sql @@ -0,0 +1,44 @@ +-- +-- Create schema avatar_appearance +-- + +SET ANSI_NULLS ON +SET QUOTED_IDENTIFIER ON +SET ANSI_PADDING ON + +CREATE TABLE [avatarappearance] ( + [UUID] uniqueidentifier NOT NULL, + [Serial] int NOT NULL, + [WearableItem0] uniqueidentifier NOT NULL, + [WearableAsset0] uniqueidentifier NOT NULL, + [WearableItem1] uniqueidentifier NOT NULL, + [WearableAsset1] uniqueidentifier NOT NULL, + [WearableItem2] uniqueidentifier NOT NULL, + [WearableAsset2] uniqueidentifier NOT NULL, + [WearableItem3] uniqueidentifier NOT NULL, + [WearableAsset3] uniqueidentifier NOT NULL, + [WearableItem4] uniqueidentifier NOT NULL, + [WearableAsset4] uniqueidentifier NOT NULL, + [WearableItem5] uniqueidentifier NOT NULL, + [WearableAsset5] uniqueidentifier NOT NULL, + [WearableItem6] uniqueidentifier NOT NULL, + [WearableAsset6] uniqueidentifier NOT NULL, + [WearableItem7] uniqueidentifier NOT NULL, + [WearableAsset7] uniqueidentifier NOT NULL, + [WearableItem8] uniqueidentifier NOT NULL, + [WearableAsset8] uniqueidentifier NOT NULL, + [WearableItem9] uniqueidentifier NOT NULL, + [WearableAsset9] uniqueidentifier NOT NULL, + [WearableItem10] uniqueidentifier NOT NULL, + [WearableAsset10] uniqueidentifier NOT NULL, + [WearableItem11] uniqueidentifier NOT NULL, + [WearableAsset11] uniqueidentifier NOT NULL, + [WearableItem12] uniqueidentifier NOT NULL, + [WearableAsset12] uniqueidentifier NOT NULL + + PRIMARY KEY CLUSTERED ( + [UUID] + ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] +) ON [PRIMARY] + +SET ANSI_PADDING OFF diff --git a/OpenSim/Data/MSSQL/Resources/CreateAssetsTable.sql b/OpenSim/Data/MSSQL/Resources/CreateAssetsTable.sql new file mode 100644 index 0000000..c7cb21a --- /dev/null +++ b/OpenSim/Data/MSSQL/Resources/CreateAssetsTable.sql @@ -0,0 +1,19 @@ +SET ANSI_NULLS ON +SET QUOTED_IDENTIFIER ON +SET ANSI_PADDING ON +CREATE TABLE [assets] ( + [id] [varchar](36) NOT NULL, + [name] [varchar](64) NOT NULL, + [description] [varchar](64) NOT NULL, + [assetType] [tinyint] NOT NULL, + [invType] [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] + +SET ANSI_PADDING OFF diff --git a/OpenSim/Data/MSSQL/Resources/CreateFoldersTable.sql b/OpenSim/Data/MSSQL/Resources/CreateFoldersTable.sql new file mode 100644 index 0000000..95d183a --- /dev/null +++ b/OpenSim/Data/MSSQL/Resources/CreateFoldersTable.sql @@ -0,0 +1,27 @@ +SET ANSI_NULLS ON +SET QUOTED_IDENTIFIER ON +SET ANSI_PADDING ON +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] + +SET ANSI_PADDING OFF diff --git a/OpenSim/Data/MSSQL/Resources/CreateItemsTable.sql b/OpenSim/Data/MSSQL/Resources/CreateItemsTable.sql new file mode 100644 index 0000000..5bb27ba --- /dev/null +++ b/OpenSim/Data/MSSQL/Resources/CreateItemsTable.sql @@ -0,0 +1,39 @@ +SET ANSI_NULLS ON + +SET QUOTED_IDENTIFIER ON + +SET ANSI_PADDING ON + +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, + 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] + +SET ANSI_PADDING OFF + diff --git a/OpenSim/Data/MSSQL/Resources/CreateUserFriendsTable.sql b/OpenSim/Data/MSSQL/Resources/CreateUserFriendsTable.sql new file mode 100644 index 0000000..6f5885e --- /dev/null +++ b/OpenSim/Data/MSSQL/Resources/CreateUserFriendsTable.sql @@ -0,0 +1,14 @@ +SET ANSI_NULLS ON + +SET QUOTED_IDENTIFIER ON + +SET ANSI_PADDING ON + +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] + +SET ANSI_PADDING OFF diff --git a/OpenSim/Data/MSSQL/Resources/Mssql-agents.sql b/OpenSim/Data/MSSQL/Resources/Mssql-agents.sql new file mode 100644 index 0000000..ad53173 --- /dev/null +++ b/OpenSim/Data/MSSQL/Resources/Mssql-agents.sql @@ -0,0 +1,37 @@ +SET ANSI_NULLS ON + +SET QUOTED_IDENTIFIER ON + +SET ANSI_PADDING ON + +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] + +SET ANSI_PADDING OFF + diff --git a/OpenSim/Data/MSSQL/Resources/Mssql-logs.sql b/OpenSim/Data/MSSQL/Resources/Mssql-logs.sql new file mode 100644 index 0000000..3b747d8 --- /dev/null +++ b/OpenSim/Data/MSSQL/Resources/Mssql-logs.sql @@ -0,0 +1,20 @@ +SET ANSI_NULLS ON + +SET QUOTED_IDENTIFIER ON + +SET ANSI_PADDING ON + +CREATE TABLE [logs] ( + [logID] [int] NOT NULL, + [target] [varchar](36) default NULL, + [server] [varchar](64) default NULL, + [method] [varchar](64) default NULL, + [arguments] [varchar](255) default NULL, + [priority] [int] default NULL, + [message] [ntext], + PRIMARY KEY CLUSTERED +( + [logID] 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/Mssql-regions.sql b/OpenSim/Data/MSSQL/Resources/Mssql-regions.sql new file mode 100644 index 0000000..b29a2ab --- /dev/null +++ b/OpenSim/Data/MSSQL/Resources/Mssql-regions.sql @@ -0,0 +1,41 @@ +SET ANSI_NULLS ON + +SET QUOTED_IDENTIFIER ON + +SET ANSI_PADDING ON + +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] + +SET ANSI_PADDING OFF diff --git a/OpenSim/Data/MSSQL/Resources/Mssql-users.sql b/OpenSim/Data/MSSQL/Resources/Mssql-users.sql new file mode 100644 index 0000000..abcc091 --- /dev/null +++ b/OpenSim/Data/MSSQL/Resources/Mssql-users.sql @@ -0,0 +1,42 @@ +SET ANSI_NULLS ON + +SET QUOTED_IDENTIFIER ON + +SET ANSI_PADDING ON + +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] -- cgit v1.1