From 2fa5694ec9857f208b6fe4d0890fd2ab8ac1b8bf Mon Sep 17 00:00:00 2001
From: StrawberryFride
Date: Wed, 24 Feb 2010 16:42:39 +0000
Subject: MSSQL Additions for Presence Refactor branch. Most functionality
 tested and works, some outstanding issues around login location and border
 crossings on y axis.

Signed-off-by: Melanie <melanie@t-data.com>
---
 OpenSim/Data/MSSQL/Resources/001_AuthStore.sql    | 17 +++++++++++++++++
 OpenSim/Data/MSSQL/Resources/001_Avatar.sql       | 15 +++++++++++++++
 OpenSim/Data/MSSQL/Resources/001_FriendsStore.sql | 11 +++++++++++
 OpenSim/Data/MSSQL/Resources/001_Presence.sql     | 19 +++++++++++++++++++
 OpenSim/Data/MSSQL/Resources/002_AuthStore.sql    |  6 ++++++
 OpenSim/Data/MSSQL/Resources/002_FriendsStore.sql |  6 ++++++
 OpenSim/Data/MSSQL/Resources/002_Presence.sql     |  6 ++++++
 OpenSim/Data/MSSQL/Resources/002_UserAccount.sql  | 12 ++++++++++++
 OpenSim/Data/MSSQL/Resources/007_GridStore.sql    |  9 +++++++++
 9 files changed, 101 insertions(+)
 create mode 100644 OpenSim/Data/MSSQL/Resources/001_AuthStore.sql
 create mode 100644 OpenSim/Data/MSSQL/Resources/001_Avatar.sql
 create mode 100644 OpenSim/Data/MSSQL/Resources/001_FriendsStore.sql
 create mode 100644 OpenSim/Data/MSSQL/Resources/001_Presence.sql
 create mode 100644 OpenSim/Data/MSSQL/Resources/002_AuthStore.sql
 create mode 100644 OpenSim/Data/MSSQL/Resources/002_FriendsStore.sql
 create mode 100644 OpenSim/Data/MSSQL/Resources/002_Presence.sql
 create mode 100644 OpenSim/Data/MSSQL/Resources/002_UserAccount.sql
 create mode 100644 OpenSim/Data/MSSQL/Resources/007_GridStore.sql

(limited to 'OpenSim/Data/MSSQL/Resources')

diff --git a/OpenSim/Data/MSSQL/Resources/001_AuthStore.sql b/OpenSim/Data/MSSQL/Resources/001_AuthStore.sql
new file mode 100644
index 0000000..c70a193
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/001_AuthStore.sql
@@ -0,0 +1,17 @@
+BEGIN TRANSACTION
+
+CREATE TABLE [auth] (
+  [uuid] [uniqueidentifier] NOT NULL default '00000000-0000-0000-0000-000000000000',
+  [passwordHash] [varchar](32) NOT NULL,
+  [passwordSalt] [varchar](32) NOT NULL,
+  [webLoginKey] [varchar](255) NOT NULL,
+  [accountType] VARCHAR(32) NOT NULL DEFAULT 'UserAccount',
+) ON [PRIMARY]
+
+CREATE TABLE [tokens] (
+  [uuid] [uniqueidentifier] NOT NULL default '00000000-0000-0000-0000-000000000000',
+  [token] [varchar](255) NOT NULL,
+  [validity] [datetime] NOT NULL )
+  ON [PRIMARY]
+
+COMMIT
\ No newline at end of file
diff --git a/OpenSim/Data/MSSQL/Resources/001_Avatar.sql b/OpenSim/Data/MSSQL/Resources/001_Avatar.sql
new file mode 100644
index 0000000..48f4c00
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/001_Avatar.sql
@@ -0,0 +1,15 @@
+BEGIN TRANSACTION
+
+CREATE TABLE [Avatars] (
+[PrincipalID] uniqueidentifier NOT NULL, 
+[Name] varchar(32) NOT NULL, 
+[Value] varchar(255) NOT NULL DEFAULT '',
+PRIMARY KEY CLUSTERED 
+(
+	[PrincipalID] ASC, [Name] ASC
+)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
+) ON [PRIMARY]
+
+
+
+COMMIT
\ No newline at end of file
diff --git a/OpenSim/Data/MSSQL/Resources/001_FriendsStore.sql b/OpenSim/Data/MSSQL/Resources/001_FriendsStore.sql
new file mode 100644
index 0000000..f6480f7
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/001_FriendsStore.sql
@@ -0,0 +1,11 @@
+BEGIN TRANSACTION
+
+CREATE TABLE [Friends] (
+[PrincipalID] uniqueidentifier NOT NULL, 
+[FriendID] varchar(255) NOT NULL, 
+[Flags] char(16) NOT NULL DEFAULT '0',
+[Offered] varchar(32) NOT NULL DEFAULT 0)
+ ON [PRIMARY]
+
+
+COMMIT
\ No newline at end of file
diff --git a/OpenSim/Data/MSSQL/Resources/001_Presence.sql b/OpenSim/Data/MSSQL/Resources/001_Presence.sql
new file mode 100644
index 0000000..877881c
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/001_Presence.sql
@@ -0,0 +1,19 @@
+BEGIN TRANSACTION
+
+CREATE TABLE [Presence] (
+[UserID] varchar(255) NOT NULL, 
+[RegionID] uniqueidentifier NOT NULL, 
+[SessionID] uniqueidentifier NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
+[SecureSessionID] uniqueidentifier NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
+[Online] char(5) NOT NULL DEFAULT 'false',
+[Login] char(16) NOT NULL DEFAULT '0',
+[Logout] char(16) NOT NULL DEFAULT '0',
+[Position] char(64) NOT NULL DEFAULT '<0,0,0>',
+[LookAt] char(64) NOT NULL DEFAULT '<0,0,0>',
+[HomeRegionID] uniqueidentifier NOT NULL,
+[HomePosition] CHAR(64) NOT NULL DEFAULT '<0,0,0>',
+[HomeLookAt] CHAR(64) NOT NULL DEFAULT '<0,0,0>',
+)
+ ON [PRIMARY]
+
+COMMIT
\ No newline at end of file
diff --git a/OpenSim/Data/MSSQL/Resources/002_AuthStore.sql b/OpenSim/Data/MSSQL/Resources/002_AuthStore.sql
new file mode 100644
index 0000000..daed955
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/002_AuthStore.sql
@@ -0,0 +1,6 @@
+BEGIN TRANSACTION
+
+INSERT INTO auth (UUID, passwordHash, passwordSalt, webLoginKey, accountType) SELECT [UUID] AS UUID, [passwordHash] AS passwordHash, [passwordSalt] AS passwordSalt, [webLoginKey] AS webLoginKey, 'UserAccount' as [accountType] FROM users;
+
+
+COMMIT
\ No newline at end of file
diff --git a/OpenSim/Data/MSSQL/Resources/002_FriendsStore.sql b/OpenSim/Data/MSSQL/Resources/002_FriendsStore.sql
new file mode 100644
index 0000000..7762a26
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/002_FriendsStore.sql
@@ -0,0 +1,6 @@
+BEGIN TRANSACTION
+
+INSERT INTO Friends (PrincipalID, FriendID, Flags, Offered) SELECT [ownerID], [friendID], [friendPerms], 0 FROM userfriends;
+
+
+COMMIT
\ No newline at end of file
diff --git a/OpenSim/Data/MSSQL/Resources/002_Presence.sql b/OpenSim/Data/MSSQL/Resources/002_Presence.sql
new file mode 100644
index 0000000..a67671d
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/002_Presence.sql
@@ -0,0 +1,6 @@
+BEGIN TRANSACTION
+
+CREATE UNIQUE INDEX SessionID ON Presence(SessionID);
+CREATE INDEX UserID ON Presence(UserID);
+
+COMMIT
\ No newline at end of file
diff --git a/OpenSim/Data/MSSQL/Resources/002_UserAccount.sql b/OpenSim/Data/MSSQL/Resources/002_UserAccount.sql
new file mode 100644
index 0000000..89d1f34
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/002_UserAccount.sql
@@ -0,0 +1,12 @@
+BEGIN TRANSACTION
+
+INSERT INTO UserAccounts (PrincipalID, ScopeID, FirstName, LastName, Email, ServiceURLs, Created) SELECT [UUID] AS PrincipalID, '00000000-0000-0000-0000-000000000000' AS ScopeID, 
+username AS FirstName, 
+lastname AS LastName, 
+email as Email, (
+'AssetServerURI=' +  
+userAssetURI + ' InventoryServerURI='  + userInventoryURI + ' GatewayURI= HomeURI=') AS ServiceURLs, 
+created as Created FROM users;
+
+
+COMMIT
\ No newline at end of file
diff --git a/OpenSim/Data/MSSQL/Resources/007_GridStore.sql b/OpenSim/Data/MSSQL/Resources/007_GridStore.sql
new file mode 100644
index 0000000..0b66d40
--- /dev/null
+++ b/OpenSim/Data/MSSQL/Resources/007_GridStore.sql
@@ -0,0 +1,9 @@
+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
-- 
cgit v1.1