diff options
author | Geir Nøklebye | 2017-05-08 00:40:15 +0100 |
---|---|---|
committer | UbitUmarov | 2017-05-09 12:00:45 +0100 |
commit | 03a38a80ab6a9ec4c55c79e4c671078231b69c6f (patch) | |
tree | 7492cda5e8be0acfc1fa35209449bd96f8bf8857 | |
parent | remove file bin/Mono.Posix.dll that causes problems with mono (diff) | |
download | opensim-SC_OLD-03a38a80ab6a9ec4c55c79e4c671078231b69c6f.zip opensim-SC_OLD-03a38a80ab6a9ec4c55c79e4c671078231b69c6f.tar.gz opensim-SC_OLD-03a38a80ab6a9ec4c55c79e4c671078231b69c6f.tar.bz2 opensim-SC_OLD-03a38a80ab6a9ec4c55c79e4c671078231b69c6f.tar.xz |
PGSQL: Rewrote EstateStore.migrations as it errored out in version 8 leaving the database without any estate tables on first run. It also lacked primary keys on estate_map and estate_settings. Syntax requires Postgresql 9.5 or higher.
Signed-off-by: UbitUmarov <ajlduarte@sapo.pt>
-rw-r--r-- | OpenSim/Data/PGSQL/Resources/EstateStore.migrations | 403 |
1 files changed, 104 insertions, 299 deletions
diff --git a/OpenSim/Data/PGSQL/Resources/EstateStore.migrations b/OpenSim/Data/PGSQL/Resources/EstateStore.migrations index 59270f8..63b70bd 100644 --- a/OpenSim/Data/PGSQL/Resources/EstateStore.migrations +++ b/OpenSim/Data/PGSQL/Resources/EstateStore.migrations | |||
@@ -1,307 +1,112 @@ | |||
1 | :VERSION 1 | 1 | :VERSION 12 |
2 | 2 | ||
3 | BEGIN TRANSACTION; | 3 | BEGIN TRANSACTION; |
4 | 4 | ||
5 | CREATE TABLE estate_managers( | 5 | -- ---------------------------- |
6 | "EstateID" int NOT NULL Primary Key, | 6 | -- Table structure for estate_groups |
7 | uuid varchar(36) NOT NULL | 7 | -- ---------------------------- |
8 | ); | 8 | CREATE TABLE IF NOT EXISTS "public"."estate_groups" ( |
9 | 9 | "EstateID" int4 NOT NULL, | |
10 | CREATE TABLE estate_groups( | 10 | "uuid" uuid NOT NULL |
11 | "EstateID" int NOT NULL, | 11 | ) |
12 | uuid varchar(36) NOT NULL | 12 | WITH (OIDS=FALSE); |
13 | ); | 13 | |
14 | 14 | -- Indexes structure for table estate_groups | |
15 | 15 | -- ---------------------------- | |
16 | CREATE TABLE estate_users( | 16 | CREATE INDEX IF NOT EXISTS "ix_estate_groups" ON "public"."estate_groups" USING btree("EstateID" "pg_catalog"."int4_ops" ASC NULLS LAST); |
17 | "EstateID" int NOT NULL, | 17 | |
18 | uuid varchar(36) NOT NULL | 18 | -- ---------------------------- |
19 | ); | 19 | -- Table structure for estate_managers |
20 | 20 | -- ---------------------------- | |
21 | 21 | CREATE TABLE IF NOT EXISTS "public"."estate_managers" ( | |
22 | CREATE TABLE estateban( | 22 | "EstateID" int4 NOT NULL, |
23 | "EstateID" int NOT NULL, | 23 | "uuid" uuid NOT NULL |
24 | "bannedUUID" varchar(36) NOT NULL, | 24 | ) |
25 | "bannedIp" varchar(16) NOT NULL, | 25 | WITH (OIDS=FALSE); |
26 | "bannedIpHostMask" varchar(16) NOT NULL, | 26 | |
27 | "bannedNameMask" varchar(64) NULL DEFAULT NULL | 27 | -- Indexes structure for table estate_managers |
28 | ); | 28 | -- ---------------------------- |
29 | 29 | CREATE INDEX IF NOT EXISTS "ix_estate_managers" ON "public"."estate_managers" USING btree("EstateID" "pg_catalog"."int4_ops" ASC NULLS LAST); | |
30 | Create Sequence estate_settings_id increment by 100 start with 100; | 30 | |
31 | 31 | -- ---------------------------- | |
32 | CREATE TABLE estate_settings( | 32 | -- Table structure for estate_map |
33 | "EstateID" integer DEFAULT nextval('estate_settings_id') NOT NULL, | 33 | -- ---------------------------- |
34 | "EstateName" varchar(64) NULL DEFAULT (NULL), | 34 | CREATE TABLE IF NOT EXISTS "public"."estate_map" ( |
35 | "AbuseEmailToEstateOwner" boolean NOT NULL, | 35 | "RegionID" uuid NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'::uuid, |
36 | "DenyAnonymous" boolean NOT NULL, | 36 | "EstateID" int4 NOT NULL |
37 | "ResetHomeOnTeleport" boolean NOT NULL, | 37 | ) |
38 | "FixedSun" boolean NOT NULL, | 38 | WITH (OIDS=FALSE); |
39 | "DenyTransacted" boolean NOT NULL, | 39 | |
40 | "BlockDwell" boolean NOT NULL, | 40 | -- Primary key structure for table estate_map |
41 | "DenyIdentified" boolean NOT NULL, | 41 | -- ---------------------------- |
42 | "AllowVoice" boolean NOT NULL, | 42 | ALTER TABLE "public"."estate_map" ADD PRIMARY KEY ("RegionID") NOT DEFERRABLE INITIALLY IMMEDIATE; |
43 | "UseGlobalTime" boolean NOT NULL, | 43 | |
44 | "PricePerMeter" int NOT NULL, | 44 | -- ---------------------------- |
45 | "TaxFree" boolean NOT NULL, | 45 | -- Table structure for estate_settings |
46 | "AllowDirectTeleport" boolean NOT NULL, | 46 | -- ---------------------------- |
47 | "RedirectGridX" int NOT NULL, | 47 | CREATE TABLE IF NOT EXISTS "public"."estate_settings" ( |
48 | "RedirectGridY" int NOT NULL, | 48 | "EstateID" int4 NOT NULL DEFAULT nextval('estate_settings_id'::regclass), |
49 | "ParentEstateID" int NOT NULL, | 49 | "EstateName" varchar(64) DEFAULT NULL::character varying COLLATE "default", |
50 | "SunPosition" double precision NOT NULL, | 50 | "AbuseEmailToEstateOwner" bool NOT NULL, |
51 | "EstateSkipScripts" boolean NOT NULL, | 51 | "DenyAnonymous" bool NOT NULL, |
52 | "BillableFactor" double precision NOT NULL, | 52 | "ResetHomeOnTeleport" bool NOT NULL, |
53 | "PublicAccess" boolean NOT NULL, | 53 | "FixedSun" bool NOT NULL, |
54 | "AbuseEmail" varchar(255) NOT NULL, | 54 | "DenyTransacted" bool NOT NULL, |
55 | "EstateOwner" varchar(36) NOT NULL, | 55 | "BlockDwell" bool NOT NULL, |
56 | "DenyMinors" boolean NOT NULL | 56 | "DenyIdentified" bool NOT NULL, |
57 | ); | 57 | "AllowVoice" bool NOT NULL, |
58 | 58 | "UseGlobalTime" bool NOT NULL, | |
59 | 59 | "PricePerMeter" int4 NOT NULL, | |
60 | CREATE TABLE estate_map( | 60 | "TaxFree" bool NOT NULL, |
61 | "RegionID" varchar(36) NOT NULL DEFAULT ('00000000-0000-0000-0000-000000000000'), | 61 | "AllowDirectTeleport" bool NOT NULL, |
62 | "EstateID" int NOT NULL | 62 | "RedirectGridX" int4 NOT NULL, |
63 | ); | 63 | "RedirectGridY" int4 NOT NULL, |
64 | 64 | "ParentEstateID" int4 NOT NULL, | |
65 | COMMIT; | 65 | "SunPosition" float8 NOT NULL, |
66 | 66 | "EstateSkipScripts" bool NOT NULL, | |
67 | :VERSION 2 | 67 | "BillableFactor" float8 NOT NULL, |
68 | 68 | "PublicAccess" bool NOT NULL, | |
69 | BEGIN TRANSACTION; | 69 | "AbuseEmail" varchar(255) NOT NULL COLLATE "default", |
70 | |||
71 | CREATE INDEX IX_estate_managers ON estate_managers | ||
72 | ( | ||
73 | "EstateID" | ||
74 | ); | ||
75 | |||
76 | |||
77 | CREATE INDEX IX_estate_groups ON estate_groups | ||
78 | ( | ||
79 | "EstateID" | ||
80 | ); | ||
81 | |||
82 | |||
83 | CREATE INDEX IX_estate_users ON estate_users | ||
84 | ( | ||
85 | "EstateID" | ||
86 | ); | ||
87 | |||
88 | COMMIT; | ||
89 | |||
90 | :VERSION 3 | ||
91 | |||
92 | BEGIN TRANSACTION; | ||
93 | |||
94 | CREATE TABLE Tmp_estateban | ||
95 | ( | ||
96 | "EstateID" int NOT NULL, | ||
97 | "bannedUUID" varchar(36) NOT NULL, | ||
98 | "bannedIp" varchar(16) NULL, | ||
99 | "bannedIpHostMask" varchar(16) NULL, | ||
100 | "bannedNameMask" varchar(64) NULL | ||
101 | ); | ||
102 | |||
103 | INSERT INTO Tmp_estateban ("EstateID", "bannedUUID", "bannedIp", "bannedIpHostMask", "bannedNameMask") | ||
104 | SELECT "EstateID", "bannedUUID", "bannedIp", "bannedIpHostMask", "bannedNameMask" FROM estateban; | ||
105 | |||
106 | DROP TABLE estateban; | ||
107 | |||
108 | Alter table Tmp_estateban | ||
109 | rename to estateban; | ||
110 | |||
111 | CREATE INDEX IX_estateban ON estateban | ||
112 | ( | ||
113 | "EstateID" | ||
114 | ); | ||
115 | |||
116 | COMMIT; | ||
117 | |||
118 | |||
119 | :VERSION 4 | ||
120 | |||
121 | BEGIN TRANSACTION; | ||
122 | |||
123 | CREATE TABLE Tmp_estate_managers | ||
124 | ( | ||
125 | "EstateID" int NOT NULL, | ||
126 | uuid uuid NOT NULL | ||
127 | ); | ||
128 | |||
129 | INSERT INTO Tmp_estate_managers ("EstateID", uuid) | ||
130 | SELECT "EstateID", cast(uuid as uuid) FROM estate_managers; | ||
131 | |||
132 | DROP TABLE estate_managers; | ||
133 | |||
134 | Alter table Tmp_estate_managers | ||
135 | rename to estate_managers; | ||
136 | |||
137 | CREATE INDEX IX_estate_managers ON estate_managers | ||
138 | ( | ||
139 | "EstateID" | ||
140 | ); | ||
141 | |||
142 | COMMIT; | ||
143 | |||
144 | |||
145 | :VERSION 5 | ||
146 | |||
147 | BEGIN TRANSACTION; | ||
148 | |||
149 | CREATE TABLE Tmp_estate_groups | ||
150 | ( | ||
151 | "EstateID" int NOT NULL, | ||
152 | uuid uuid NOT NULL | ||
153 | ) ; | ||
154 | |||
155 | INSERT INTO Tmp_estate_groups ("EstateID", uuid) | ||
156 | SELECT "EstateID", cast(uuid as uuid) FROM estate_groups; | ||
157 | |||
158 | DROP TABLE estate_groups; | ||
159 | |||
160 | Alter table Tmp_estate_groups | ||
161 | rename to estate_groups; | ||
162 | |||
163 | CREATE INDEX IX_estate_groups ON estate_groups | ||
164 | ( | ||
165 | "EstateID" | ||
166 | ); | ||
167 | |||
168 | COMMIT; | ||
169 | |||
170 | |||
171 | :VERSION 6 | ||
172 | |||
173 | BEGIN TRANSACTION; | ||
174 | |||
175 | CREATE TABLE Tmp_estate_users | ||
176 | ( | ||
177 | "EstateID" int NOT NULL, | ||
178 | uuid uuid NOT NULL | ||
179 | ); | ||
180 | |||
181 | INSERT INTO Tmp_estate_users ("EstateID", uuid) | ||
182 | SELECT "EstateID", cast(uuid as uuid) FROM estate_users ; | ||
183 | |||
184 | DROP TABLE estate_users; | ||
185 | |||
186 | Alter table Tmp_estate_users | ||
187 | rename to estate_users; | ||
188 | |||
189 | CREATE INDEX IX_estate_users ON estate_users | ||
190 | ( | ||
191 | "EstateID" | ||
192 | ); | ||
193 | |||
194 | COMMIT; | ||
195 | |||
196 | |||
197 | :VERSION 7 | ||
198 | |||
199 | BEGIN TRANSACTION; | ||
200 | |||
201 | CREATE TABLE Tmp_estateban | ||
202 | ( | ||
203 | "EstateID" int NOT NULL, | ||
204 | "bannedUUID" uuid NOT NULL, | ||
205 | "bannedIp" varchar(16) NULL, | ||
206 | "bannedIpHostMask" varchar(16) NULL, | ||
207 | "bannedNameMask" varchar(64) NULL | ||
208 | ); | ||
209 | |||
210 | INSERT INTO Tmp_estateban ("EstateID", "bannedUUID", "bannedIp", "bannedIpHostMask", "bannedNameMask") | ||
211 | SELECT "EstateID", cast("bannedUUID" as uuid), "bannedIp", "bannedIpHostMask", "bannedNameMask" FROM estateban ; | ||
212 | |||
213 | DROP TABLE estateban; | ||
214 | |||
215 | Alter table Tmp_estateban | ||
216 | rename to estateban; | ||
217 | |||
218 | CREATE INDEX IX_estateban ON estateban | ||
219 | ( | ||
220 | "EstateID" | ||
221 | ); | ||
222 | |||
223 | COMMIT; | ||
224 | |||
225 | |||
226 | :VERSION 8 | ||
227 | |||
228 | BEGIN TRANSACTION; | ||
229 | |||
230 | CREATE TABLE Tmp_estate_settings | ||
231 | ( | ||
232 | "EstateID" integer default nextval('estate_settings_id') NOT NULL, | ||
233 | "EstateName" varchar(64) NULL DEFAULT (NULL), | ||
234 | "AbuseEmailToEstateOwner" boolean NOT NULL, | ||
235 | "DenyAnonymous" boolean NOT NULL, | ||
236 | "ResetHomeOnTeleport" boolean NOT NULL, | ||
237 | "FixedSun" boolean NOT NULL, | ||
238 | "DenyTransacted" boolean NOT NULL, | ||
239 | "BlockDwell" boolean NOT NULL, | ||
240 | "DenyIdentified" boolean NOT NULL, | ||
241 | "AllowVoice" boolean NOT NULL, | ||
242 | "UseGlobalTime" boolean NOT NULL, | ||
243 | "PricePerMeter" int NOT NULL, | ||
244 | "TaxFree" boolean NOT NULL, | ||
245 | "AllowDirectTeleport" boolean NOT NULL, | ||
246 | "RedirectGridX" int NOT NULL, | ||
247 | "RedirectGridY" int NOT NULL, | ||
248 | "ParentEstateID" int NOT NULL, | ||
249 | "SunPosition" double precision NOT NULL, | ||
250 | "EstateSkipScripts" boolean NOT NULL, | ||
251 | "BillableFactor" double precision NOT NULL, | ||
252 | "PublicAccess" boolean NOT NULL, | ||
253 | "AbuseEmail" varchar(255) NOT NULL, | ||
254 | "EstateOwner" uuid NOT NULL, | 70 | "EstateOwner" uuid NOT NULL, |
255 | "DenyMinors" boolean NOT NULL | 71 | "DenyMinors" bool NOT NULL, |
256 | ); | 72 | "AllowLandmark" bool NOT NULL DEFAULT true, |
257 | 73 | "AllowParcelChanges" bool NOT NULL DEFAULT true, | |
258 | INSERT INTO Tmp_estate_settings ("EstateID", "EstateName", "AbuseEmailToEstateOwner", "DenyAnonymous", "ResetHomeOnTeleport", "FixedSun", "DenyTransacted", "BlockDwell", "DenyIdentified", "AllowVoice", "UseGlobalTime", "PricePerMeter", "TaxFree", "AllowDirectTeleport", "RedirectGridX", "RedirectGridY", "ParentEstateID", "SunPosition", "EstateSkipScripts", "BillableFactor", "PublicAccess", "AbuseEmail", "EstateOwner", "DenyMinors") | 74 | "AllowSetHome" bool NOT NULL DEFAULT true |
259 | SELECT "EstateID", "EstateName", "AbuseEmailToEstateOwner", "DenyAnonymous", "ResetHomeOnTeleport", "FixedSun", "DenyTransacted", "BlockDwell", "DenyIdentified", "AllowVoice", "UseGlobalTime", "PricePerMeter", "TaxFree", "AllowDirectTeleport", "RedirectGridX", "RedirectGridY", "ParentEstateID", "SunPosition", "EstateSkipScripts", "BillableFactor", "PublicAccess", "AbuseEmail", cast("EstateOwner" as uuid), "DenyMinors" FROM estate_settings ; | 75 | ) |
260 | 76 | WITH (OIDS=FALSE); | |
261 | DROP TABLE estate_settings; | 77 | |
262 | 78 | -- Primary key structure for table estate_settings | |
263 | 79 | -- ---------------------------- | |
264 | Alter table Tmp_estate_settings | 80 | ALTER TABLE "public"."estate_settings" ADD PRIMARY KEY ("EstateID") NOT DEFERRABLE INITIALLY IMMEDIATE; |
265 | rename to estate_settings; | 81 | |
266 | 82 | -- ---------------------------- | |
267 | 83 | -- Table structure for estate_users | |
268 | Create index on estate_settings (lower("EstateName")); | 84 | -- ---------------------------- |
269 | 85 | CREATE TABLE IF NOT EXISTS "public"."estate_users" ( | |
270 | COMMIT; | 86 | "EstateID" int4 NOT NULL, |
271 | 87 | "uuid" uuid NOT NULL | |
272 | 88 | ) | |
273 | :VERSION 9 | 89 | WITH (OIDS=FALSE); |
274 | 90 | ||
275 | BEGIN TRANSACTION; | 91 | -- Indexes structure for table estate_users |
276 | 92 | -- ---------------------------- | |
277 | CREATE TABLE Tmp_estate_map | 93 | CREATE INDEX IF NOT EXISTS "ix_estate_users" ON "public"."estate_users" USING btree("EstateID" "pg_catalog"."int4_ops" ASC NULLS LAST); |
278 | ( | 94 | |
279 | "RegionID" uuid NOT NULL DEFAULT ('00000000-0000-0000-0000-000000000000'), | 95 | -- ---------------------------- |
280 | "EstateID" int NOT NULL | 96 | -- Table structure for estateban |
281 | ); | 97 | -- ---------------------------- |
282 | 98 | CREATE TABLE IF NOT EXISTS "public"."estateban" ( | |
283 | INSERT INTO Tmp_estate_map ("RegionID", "EstateID") | 99 | "EstateID" int4 NOT NULL, |
284 | SELECT cast("RegionID" as uuid), "EstateID" FROM estate_map ; | 100 | "bannedUUID" uuid NOT NULL, |
285 | 101 | "bannedIp" varchar(16) COLLATE "default", | |
286 | DROP TABLE estate_map; | 102 | "bannedIpHostMask" varchar(16) COLLATE "default", |
287 | 103 | "bannedNameMask" varchar(64) COLLATE "default" | |
288 | Alter table Tmp_estate_map | 104 | ) |
289 | rename to estate_map; | 105 | WITH (OIDS=FALSE); |
290 | |||
291 | COMMIT; | ||
292 | 106 | ||
293 | :VERSION 10 | 107 | -- Indexes structure for table estateban |
108 | -- ---------------------------- | ||
109 | CREATE INDEX IF NOT EXISTS "ix_estateban" ON "public"."estateban" USING btree("EstateID" "pg_catalog"."int4_ops" ASC NULLS LAST); | ||
294 | 110 | ||
295 | BEGIN TRANSACTION; | ||
296 | ALTER TABLE estate_settings ADD COLUMN "AllowLandmark" boolean NOT NULL default true; | ||
297 | ALTER TABLE estate_settings ADD COLUMN "AllowParcelChanges" boolean NOT NULL default true; | ||
298 | ALTER TABLE estate_settings ADD COLUMN "AllowSetHome" boolean NOT NULL default true; | ||
299 | COMMIT; | 111 | COMMIT; |
300 | 112 | ||
301 | :VERSION 11 | ||
302 | |||
303 | Begin transaction; | ||
304 | |||
305 | |||
306 | Commit; | ||
307 | |||