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

:VERSION 1

CREATE TABLE [UserAccounts] (
  [PrincipalID] uniqueidentifier NOT NULL,
  [ScopeID] uniqueidentifier NOT NULL,
  [FirstName] [varchar](64) NOT NULL,
  [LastName] [varchar](64) NOT NULL,
  [Email] [varchar](64) NULL,
  [ServiceURLs] [text] NULL,
  [Created] [int] default NULL,
  
  PRIMARY KEY CLUSTERED
(
	[PrincipalID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


:VERSION 2

BEGIN TRANSACTION
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[users]') AND type in (N'U'))
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

:VERSION 3

BEGIN TRANSACTION

CREATE UNIQUE INDEX PrincipalID ON UserAccounts(PrincipalID);
CREATE INDEX Email ON UserAccounts(Email);
CREATE INDEX FirstName ON UserAccounts(FirstName);
CREATE INDEX LastName ON UserAccounts(LastName);
CREATE INDEX Name ON UserAccounts(FirstName,LastName);

COMMIT

:VERSION 4

BEGIN TRANSACTION

ALTER TABLE UserAccounts ADD UserLevel integer NOT NULL DEFAULT 0;
ALTER TABLE UserAccounts ADD UserFlags integer NOT NULL DEFAULT 0;
ALTER TABLE UserAccounts ADD UserTitle varchar(64) NOT NULL DEFAULT '';

COMMIT