aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/OpenSim/Data/PGSQL/Resources/UserStore.migrations
diff options
context:
space:
mode:
Diffstat (limited to 'OpenSim/Data/PGSQL/Resources/UserStore.migrations')
-rw-r--r--OpenSim/Data/PGSQL/Resources/UserStore.migrations404
1 files changed, 404 insertions, 0 deletions
diff --git a/OpenSim/Data/PGSQL/Resources/UserStore.migrations b/OpenSim/Data/PGSQL/Resources/UserStore.migrations
new file mode 100644
index 0000000..974d489
--- /dev/null
+++ b/OpenSim/Data/PGSQL/Resources/UserStore.migrations
@@ -0,0 +1,404 @@
1:VERSION 1
2
3CREATE TABLE users (
4 "UUID" varchar(36) NOT NULL default '' Primary Key,
5 "username" varchar(32) NOT NULL,
6 "lastname" varchar(32) NOT NULL,
7 "passwordHash" varchar(32) NOT NULL,
8 "passwordSalt" varchar(32) NOT NULL,
9 "homeRegion" bigint default NULL,
10 "homeLocationX" double precision default NULL,
11 "homeLocationY" double precision default NULL,
12 "homeLocationZ" double precision default NULL,
13 "homeLookAtX" double precision default NULL,
14 "homeLookAtY" double precision default NULL,
15 "homeLookAtZ" double precision default NULL,
16 "created" int NOT NULL,
17 "lastLogin" int NOT NULL,
18 "userInventoryURI" varchar(255) default NULL,
19 "userAssetURI" varchar(255) default NULL,
20 "profileCanDoMask" int default NULL,
21 "profileWantDoMask" int default NULL,
22 "profileAboutText" text,
23 "profileFirstText" text,
24 "profileImage" varchar(36) default NULL,
25 "profileFirstImage" varchar(36) default NULL,
26 "webLoginKey" varchar(36) default NULL
27);
28
29CREATE INDEX "usernames" ON users
30(
31 "username" ASC,
32 "lastname" ASC
33);
34
35
36CREATE TABLE agents (
37 "UUID" varchar(36) NOT NULL Primary Key,
38 "sessionID" varchar(36) NOT NULL,
39 "secureSessionID" varchar(36) NOT NULL,
40 "agentIP" varchar(16) NOT NULL,
41 "agentPort" int NOT NULL,
42 "agentOnline" smallint NOT NULL,
43 "loginTime" int NOT NULL,
44 "logoutTime" int NOT NULL,
45 "currentRegion" varchar(36) NOT NULL,
46 "currentHandle" bigint NOT NULL,
47 "currentPos" varchar(64) NOT NULL
48);
49
50CREATE INDEX session ON agents
51(
52 "sessionID" ASC
53);
54
55CREATE INDEX ssession ON agents
56(
57 "secureSessionID" ASC
58);
59
60
61CREATE TABLE userfriends(
62 "ownerID" varchar(50) NOT NULL,
63 "friendID" varchar(50) NOT NULL,
64 "friendPerms" varchar(50) NOT NULL,
65 "datetimestamp" varchar(50) NOT NULL
66);
67
68CREATE TABLE avatarappearance (
69 "Owner" varchar(36) NOT NULL primary key,
70 "Serial" int NOT NULL,
71 "Visual_Params" bytea NOT NULL,
72 "Texture" bytea NOT NULL,
73 "Avatar_Height" double precision NOT NULL,
74 "Body_Item" varchar(36) NOT NULL,
75 "Body_Asset" varchar(36) NOT NULL,
76 "Skin_Item" varchar(36) NOT NULL,
77 "Skin_Asset" varchar(36) NOT NULL,
78 "Hair_Item" varchar(36) NOT NULL,
79 "Hair_Asset" varchar(36) NOT NULL,
80 "Eyes_Item" varchar(36) NOT NULL,
81 "Eyes_Asset" varchar(36) NOT NULL,
82 "Shirt_Item" varchar(36) NOT NULL,
83 "Shirt_Asset" varchar(36) NOT NULL,
84 "Pants_Item" varchar(36) NOT NULL,
85 "Pants_Asset" varchar(36) NOT NULL,
86 "Shoes_Item" varchar(36) NOT NULL,
87 "Shoes_Asset" varchar(36) NOT NULL,
88 "Socks_Item" varchar(36) NOT NULL,
89 "Socks_Asset" varchar(36) NOT NULL,
90 "Jacket_Item" varchar(36) NOT NULL,
91 "Jacket_Asset" varchar(36) NOT NULL,
92 "Gloves_Item" varchar(36) NOT NULL,
93 "Gloves_Asset" varchar(36) NOT NULL,
94 "Undershirt_Item" varchar(36) NOT NULL,
95 "Undershirt_Asset" varchar(36) NOT NULL,
96 "Underpants_Item" varchar(36) NOT NULL,
97 "Underpants_Asset" varchar(36) NOT NULL,
98 "Skirt_Item" varchar(36) NOT NULL,
99 "Skirt_Asset" varchar(36) NOT NULL
100);
101
102:VERSION 2
103
104BEGIN TRANSACTION;
105
106ALTER TABLE users ADD "homeRegionID" varchar(36) NOT NULL default '00000000-0000-0000-0000-000000000000';
107ALTER TABLE users ADD "userFlags" int NOT NULL default 0;
108ALTER TABLE users ADD "godLevel" int NOT NULL default 0;
109ALTER TABLE users ADD "customType" varchar(32) not null default '';
110ALTER TABLE users ADD "partner" varchar(36) not null default '00000000-0000-0000-0000-000000000000';
111
112COMMIT;
113
114
115:VERSION 3
116
117BEGIN TRANSACTION;
118
119CREATE TABLE avatarattachments (
120 "UUID" varchar(36) NOT NULL
121 , "attachpoint" int NOT NULL
122 , item varchar(36) NOT NULL
123 , asset varchar(36) NOT NULL);
124
125CREATE INDEX IX_avatarattachments ON avatarattachments
126 (
127 "UUID"
128 );
129
130COMMIT;
131
132
133:VERSION 4
134
135BEGIN TRANSACTION;
136
137CREATE TABLE Tmp_userfriends
138 (
139 "ownerID" varchar(36) NOT NULL,
140 "friendID" varchar(36) NOT NULL,
141 "friendPerms" int NOT NULL,
142 "datetimestamp" int NOT NULL
143 );
144
145INSERT INTO Tmp_userfriends ("ownerID", "friendID", "friendPerms", "datetimestamp")
146 SELECT cast("ownerID" as varchar(36)), cast("friendID" as varchar(36)), cast("friendPerms" as int), cast("datetimestamp" as int)
147 FROM userfriends;
148
149DROP TABLE userfriends;
150
151alter table Tmp_userfriends rename to userfriends;
152
153CREATE INDEX IX_userfriends_ownerID ON userfriends
154 (
155 "ownerID"
156 );
157
158CREATE INDEX IX_userfriends_friendID ON userfriends
159 (
160 "friendID"
161 );
162
163COMMIT;
164
165
166:VERSION 5
167
168BEGIN TRANSACTION;
169
170 ALTER TABLE users add "email" varchar(250);
171
172COMMIT;
173
174
175:VERSION 6
176
177BEGIN TRANSACTION;
178
179CREATE TABLE Tmp_users
180 (
181 "UUID" uuid NOT NULL DEFAULT ('00000000-0000-0000-0000-000000000000'),
182 "username" varchar(32) NOT NULL,
183 "lastname" varchar(32) NOT NULL,
184 "passwordHash" varchar(32) NOT NULL,
185 "passwordSalt" varchar(32) NOT NULL,
186 "homeRegion" bigint NULL DEFAULT (NULL),
187 "homeLocationX" double precision NULL DEFAULT (NULL),
188 "homeLocationY" double precision NULL DEFAULT (NULL),
189 "homeLocationZ" double precision NULL DEFAULT (NULL),
190 "homeLookAtX" double precision NULL DEFAULT (NULL),
191 "homeLookAtY" double precision NULL DEFAULT (NULL),
192 "homeLookAtZ" double precision NULL DEFAULT (NULL),
193 "created" int NOT NULL,
194 "lastLogin" int NOT NULL,
195 "userInventoryURI" varchar(255) NULL DEFAULT (NULL),
196 "userAssetURI" varchar(255) NULL DEFAULT (NULL),
197 "profileCanDoMask" int NULL DEFAULT (NULL),
198 "profileWantDoMask" int NULL DEFAULT (NULL),
199 "profileAboutText" text NULL,
200 "profileFirstText" text NULL,
201 "profileImage" uuid NULL DEFAULT (NULL),
202 "profileFirstImage" uuid NULL DEFAULT (NULL),
203 "webLoginKey" uuid NULL DEFAULT (NULL),
204 "homeRegionID" uuid NOT NULL DEFAULT ('00000000-0000-0000-0000-000000000000'),
205 "userFlags" int NOT NULL DEFAULT ((0)),
206 "godLevel" int NOT NULL DEFAULT ((0)),
207 "customType" varchar(32) NOT NULL DEFAULT (''),
208 "partner" uuid NOT NULL DEFAULT ('00000000-0000-0000-0000-000000000000'),
209 email varchar(250) NULL
210 );
211
212INSERT INTO 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)
213 SELECT cast("UUID" as uuid), "username", "lastname", "passwordHash", "passwordSalt", "homeRegion", "homeLocationX", "homeLocationY", "homeLocationZ", "homeLookAtX", "homeLookAtY", "homeLookAtZ", "created", "lastLogin", "userInventoryURI", "userAssetURI", "profileCanDoMask", "profileWantDoMask", "profileAboutText", "profileFirstText", cast("profileImage" as uuid), cast("profileFirstImage" as uuid), cast("webLoginKey" as uuid), cast("homeRegionID" as uuid), "userFlags", "godLevel", "customType", cast("partner" as uuid), email
214 FROM users ;
215
216DROP TABLE users;
217
218alter table Tmp_users rename to users;
219
220ALTER TABLE users ADD CONSTRAINT
221 PK__users__65A475E737A5467C PRIMARY KEY
222 (
223 "UUID"
224 );
225
226CREATE INDEX "usernames" ON users
227 (
228 "username",
229 "lastname"
230 );
231
232COMMIT;
233
234
235:VERSION 7
236
237BEGIN TRANSACTION;
238
239CREATE TABLE Tmp_agents
240 (
241 "UUID" uuid NOT NULL,
242 "sessionID" uuid NOT NULL,
243 "secureSessionID" uuid NOT NULL,
244 "agentIP" varchar(16) NOT NULL,
245 "agentPort" int NOT NULL,
246 "agentOnline" smallint NOT NULL,
247 "loginTime" int NOT NULL,
248 "logoutTime" int NOT NULL,
249 "currentRegion" uuid NOT NULL,
250 "currentHandle" bigint NOT NULL,
251 "currentPos" varchar(64) NOT NULL
252 );
253
254INSERT INTO Tmp_agents ("UUID", "sessionID", "secureSessionID", "agentIP", "agentPort", "agentOnline", "loginTime", "logoutTime", "currentRegion", "currentHandle", "currentPos")
255 SELECT cast("UUID" as uuid), cast("sessionID" as uuid), cast("secureSessionID" as uuid), "agentIP", "agentPort", "agentOnline", "loginTime", "logoutTime", cast("currentRegion" as uuid), "currentHandle", "currentPos"
256 FROM agents ;
257
258DROP TABLE agents;
259
260alter table Tmp_agents rename to agents;
261
262ALTER TABLE agents ADD CONSTRAINT
263 PK__agents__65A475E749C3F6B7 PRIMARY KEY
264 (
265 "UUID"
266 ) ;
267
268CREATE INDEX session ON agents
269 (
270 "sessionID"
271 );
272
273CREATE INDEX ssession ON agents
274 (
275 "secureSessionID"
276 );
277
278COMMIT;
279
280
281:VERSION 8
282
283BEGIN TRANSACTION;
284
285CREATE TABLE Tmp_userfriends
286 (
287 "ownerID" uuid NOT NULL,
288 "friendID" uuid NOT NULL,
289 "friendPerms" int NOT NULL,
290 "datetimestamp" int NOT NULL
291 );
292
293INSERT INTO Tmp_userfriends ("ownerID", "friendID", "friendPerms", "datetimestamp")
294 SELECT cast("ownerID" as uuid), cast( "friendID" as uuid), "friendPerms", "datetimestamp"
295 FROM userfriends;
296
297DROP TABLE userfriends;
298
299alter table Tmp_userfriends rename to userfriends;
300
301CREATE INDEX IX_userfriends_ownerID ON userfriends
302 (
303 "ownerID"
304 );
305
306CREATE INDEX IX_userfriends_friendID ON userfriends
307 (
308 "friendID"
309 );
310
311COMMIT;
312
313
314:VERSION 9
315
316BEGIN TRANSACTION;
317
318CREATE TABLE Tmp_avatarappearance
319 (
320 "Owner" uuid NOT NULL,
321 "Serial" int NOT NULL,
322 "Visual_Params" bytea NOT NULL,
323 "Texture" bytea NOT NULL,
324 "Avatar_Height" double precision NOT NULL,
325 "Body_Item" uuid NOT NULL,
326 "Body_Asset" uuid NOT NULL,
327 "Skin_Item" uuid NOT NULL,
328 "Skin_Asset" uuid NOT NULL,
329 "Hair_Item" uuid NOT NULL,
330 "Hair_Asset" uuid NOT NULL,
331 "Eyes_Item" uuid NOT NULL,
332 "Eyes_Asset" uuid NOT NULL,
333 "Shirt_Item" uuid NOT NULL,
334 "Shirt_Asset" uuid NOT NULL,
335 "Pants_Item" uuid NOT NULL,
336 "Pants_Asset" uuid NOT NULL,
337 "Shoes_Item" uuid NOT NULL,
338 "Shoes_Asset" uuid NOT NULL,
339 "Socks_Item" uuid NOT NULL,
340 "Socks_Asset" uuid NOT NULL,
341 "Jacket_Item" uuid NOT NULL,
342 "Jacket_Asset" uuid NOT NULL,
343 "Gloves_Item" uuid NOT NULL,
344 "Gloves_Asset" uuid NOT NULL,
345 "Undershirt_Item" uuid NOT NULL,
346 "Undershirt_Asset" uuid NOT NULL,
347 "Underpants_Item" uuid NOT NULL,
348 "Underpants_Asset" uuid NOT NULL,
349 "Skirt_Item" uuid NOT NULL,
350 "Skirt_Asset" uuid NOT NULL
351 );
352
353INSERT INTO Tmp_avatarappearance ("Owner", "Serial", "Visual_Params", "Texture", "Avatar_Height", "Body_Item", "Body_Asset", "Skin_Item", "Skin_Asset", "Hair_Item", "Hair_Asset", "Eyes_Item", "Eyes_Asset", "Shirt_Item", "Shirt_Asset", "Pants_Item", "Pants_Asset", "Shoes_Item", "Shoes_Asset", "Socks_Item", "Socks_Asset", "Jacket_Item", "Jacket_Asset", "Gloves_Item", "Gloves_Asset", "Undershirt_Item", "Undershirt_Asset", "Underpants_Item", "Underpants_Asset", "Skirt_Item", "Skirt_Asset")
354 SELECT cast("Owner" as uuid), "Serial", "Visual_Params", "Texture", "Avatar_Height", cast("Body_Item" as uuid), cast("Body_Asset" as uuid), cast("Skin_Item" as uuid), cast("Skin_Asset" as uuid), cast("Hair_Item" as uuid), cast("Hair_Asset" as uuid), cast("Eyes_Item" as uuid), cast("Eyes_Asset" as uuid), cast("Shirt_Item" as uuid), cast("Shirt_Asset" as uuid), cast("Pants_Item" as uuid), cast("Pants_Asset" as uuid), cast("Shoes_Item" as uuid), cast("Shoes_Asset" as uuid), cast("Socks_Item" as uuid), cast("Socks_Asset" as uuid), cast("Jacket_Item" as uuid), cast("Jacket_Asset" as uuid), cast("Gloves_Item" as uuid), cast("Gloves_Asset" as uuid), cast("Undershirt_Item" as uuid), cast("Undershirt_Asset" as uuid), cast("Underpants_Item" as uuid), cast("Underpants_Asset" as uuid), cast("Skirt_Item" as uuid), cast("Skirt_Asset" as uuid)
355 FROM avatarappearance ;
356
357DROP TABLE avatarappearance;
358
359alter table Tmp_avatarappearance rename to avatarappearance;
360
361ALTER TABLE avatarappearance ADD CONSTRAINT
362 PK__avatarap__7DD115CC4E88ABD4 PRIMARY KEY
363 (
364 "Owner"
365 );
366
367COMMIT;
368
369
370:VERSION 10
371
372BEGIN TRANSACTION;
373
374CREATE TABLE Tmp_avatarattachments
375 (
376 "UUID" uuid NOT NULL,
377 "attachpoint" int NOT NULL,
378 item uuid NOT NULL,
379 asset uuid NOT NULL
380 );
381
382INSERT INTO Tmp_avatarattachments ("UUID", "attachpoint", item, asset)
383 SELECT cast("UUID" as uuid), "attachpoint", cast(item as uuid), cast(asset as uuid)
384 FROM avatarattachments ;
385
386DROP TABLE avatarattachments;
387
388alter table Tmp_avatarattachments rename to avatarattachments;
389
390CREATE INDEX IX_avatarattachments ON avatarattachments
391 (
392 "UUID"
393 );
394
395COMMIT;
396
397
398:VERSION 11
399
400BEGIN TRANSACTION;
401
402ALTER TABLE users ADD "scopeID" uuid not null default '00000000-0000-0000-0000-000000000000';
403
404COMMIT;