diff options
author | Justin Clarke Casey | 2009-02-19 18:09:10 +0000 |
---|---|---|
committer | Justin Clarke Casey | 2009-02-19 18:09:10 +0000 |
commit | 07609565617aa7936758acba5fd625877564a10d (patch) | |
tree | 73123e37f14fea5f171cbe04753c21758f25afc1 /OpenSim/Data/MSSQL/Resources/006_UserStore.sql | |
parent | * Okay, so finally got my head around this. Problem is that upstream Prebuild... (diff) | |
download | opensim-SC_OLD-07609565617aa7936758acba5fd625877564a10d.zip opensim-SC_OLD-07609565617aa7936758acba5fd625877564a10d.tar.gz opensim-SC_OLD-07609565617aa7936758acba5fd625877564a10d.tar.bz2 opensim-SC_OLD-07609565617aa7936758acba5fd625877564a10d.tar.xz |
* Apply http://opensimulator.org/mantis/view.php?id=3142
* Changes varchar(36) columns to UUID type in MSSQL - this will be much more efficient
* ===As always, please, please backup your database before applying this patch===
* Thanks Ruud Lathrop (for the patch) and StrawberryFride (for the review)
Diffstat (limited to '')
-rw-r--r-- | OpenSim/Data/MSSQL/Resources/006_UserStore.sql | 57 |
1 files changed, 57 insertions, 0 deletions
diff --git a/OpenSim/Data/MSSQL/Resources/006_UserStore.sql b/OpenSim/Data/MSSQL/Resources/006_UserStore.sql new file mode 100644 index 0000000..67fe581 --- /dev/null +++ b/OpenSim/Data/MSSQL/Resources/006_UserStore.sql | |||
@@ -0,0 +1,57 @@ | |||
1 | BEGIN TRANSACTION | ||
2 | |||
3 | CREATE TABLE dbo.Tmp_users | ||
4 | ( | ||
5 | UUID uniqueidentifier NOT NULL DEFAULT ('00000000-0000-0000-0000-000000000000'), | ||
6 | username varchar(32) NOT NULL, | ||
7 | lastname varchar(32) NOT NULL, | ||
8 | passwordHash varchar(32) NOT NULL, | ||
9 | passwordSalt varchar(32) NOT NULL, | ||
10 | homeRegion bigint NULL DEFAULT (NULL), | ||
11 | homeLocationX float(53) NULL DEFAULT (NULL), | ||
12 | homeLocationY float(53) NULL DEFAULT (NULL), | ||
13 | homeLocationZ float(53) NULL DEFAULT (NULL), | ||
14 | homeLookAtX float(53) NULL DEFAULT (NULL), | ||
15 | homeLookAtY float(53) NULL DEFAULT (NULL), | ||
16 | homeLookAtZ float(53) NULL DEFAULT (NULL), | ||
17 | created int NOT NULL, | ||
18 | lastLogin int NOT NULL, | ||
19 | userInventoryURI varchar(255) NULL DEFAULT (NULL), | ||
20 | userAssetURI varchar(255) NULL DEFAULT (NULL), | ||
21 | profileCanDoMask int NULL DEFAULT (NULL), | ||
22 | profileWantDoMask int NULL DEFAULT (NULL), | ||
23 | profileAboutText ntext NULL, | ||
24 | profileFirstText ntext NULL, | ||
25 | profileImage uniqueidentifier NULL DEFAULT (NULL), | ||
26 | profileFirstImage uniqueidentifier NULL DEFAULT (NULL), | ||
27 | webLoginKey uniqueidentifier NULL DEFAULT (NULL), | ||
28 | homeRegionID uniqueidentifier NOT NULL DEFAULT ('00000000-0000-0000-0000-000000000000'), | ||
29 | userFlags int NOT NULL DEFAULT ((0)), | ||
30 | godLevel int NOT NULL DEFAULT ((0)), | ||
31 | customType varchar(32) NOT NULL DEFAULT (''), | ||
32 | partner uniqueidentifier NOT NULL DEFAULT ('00000000-0000-0000-0000-000000000000'), | ||
33 | email varchar(250) NULL | ||
34 | ) ON [PRIMARY] | ||
35 | TEXTIMAGE_ON [PRIMARY] | ||
36 | |||
37 | IF EXISTS(SELECT * FROM dbo.users) | ||
38 | EXEC('INSERT INTO dbo.Tmp_users (UUID, username, lastname, passwordHash, passwordSalt, homeRegion, homeLocationX, homeLocationY, homeLocationZ, homeLookAtX, homeLookAtY, homeLookAtZ, created, lastLogin, userInventoryURI, userAssetURI, profileCanDoMask, profileWantDoMask, profileAboutText, profileFirstText, profileImage, profileFirstImage, webLoginKey, homeRegionID, userFlags, godLevel, customType, partner, email) | ||
39 | SELECT CONVERT(uniqueidentifier, UUID), username, lastname, passwordHash, passwordSalt, homeRegion, homeLocationX, homeLocationY, homeLocationZ, homeLookAtX, homeLookAtY, homeLookAtZ, created, lastLogin, userInventoryURI, userAssetURI, profileCanDoMask, profileWantDoMask, profileAboutText, profileFirstText, CONVERT(uniqueidentifier, profileImage), CONVERT(uniqueidentifier, profileFirstImage), CONVERT(uniqueidentifier, webLoginKey), CONVERT(uniqueidentifier, homeRegionID), userFlags, godLevel, customType, CONVERT(uniqueidentifier, partner), email FROM dbo.users WITH (HOLDLOCK TABLOCKX)') | ||
40 | |||
41 | DROP TABLE dbo.users | ||
42 | |||
43 | EXECUTE sp_rename N'dbo.Tmp_users', N'users', 'OBJECT' | ||
44 | |||
45 | ALTER TABLE dbo.users ADD CONSTRAINT | ||
46 | PK__users__65A475E737A5467C PRIMARY KEY CLUSTERED | ||
47 | ( | ||
48 | UUID | ||
49 | ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] | ||
50 | |||
51 | CREATE NONCLUSTERED INDEX usernames ON dbo.users | ||
52 | ( | ||
53 | username, | ||
54 | lastname | ||
55 | ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] | ||
56 | |||
57 | COMMIT | ||