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>

9 files changed, 101 insertions, 0 deletions

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