-
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Randomize Tech.sql
115 lines (94 loc) · 3.92 KB
/
Randomize Tech.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
CREATE TABLE "RandomizedTechnologies" (
"TechnologyType" TEXT NOT NULL,
PRIMARY KEY(TechnologyType)
);
CREATE TABLE "SwappedTechnologies" (
"Original" TEXT NOT NULL UNIQUE,
"Swapped" TEXT NOT NULL UNIQUE,
PRIMARY KEY (Original, Swapped)
);
CREATE TABLE BackupTechnologies AS SELECT TechnologyType, Cost, EraType, UITreeRow FROM Technologies;
CREATE TRIGGER OnTechnologyQuotes BEFORE INSERT ON TechnologyQuotes
BEGIN
INSERT INTO BackupTechnologies (TechnologyType, Cost, EraType, UITreeRow)
SELECT TechnologyType, Cost, EraType, UITreeRow FROM Technologies
WHERE NOT EXISTS(SELECT * FROM TechnologyQuotes);
INSERT INTO RandomizedTechnologies (TechnologyType)
SELECT TechnologyType FROM Technologies
WHERE NOT EXISTS(SELECT * FROM TechnologyQuotes)
ORDER BY RANDOM();
INSERT INTO SwappedTechnologies (Original, Swapped)
SELECT t.TechnologyType, r.TechnologyType FROM Technologies t
JOIN RandomizedTechnologies r ON t.RowId = r.RowId
WHERE NOT EXISTS(SELECT * FROM TechnologyQuotes);
UPDATE Technologies SET
Cost = (
SELECT b.Cost FROM BackupTechnologies b JOIN SwappedTechnologies s ON s.Original = Technologies.TechnologyType AND b.TechnologyType = s.Swapped
),
EraType = (
SELECT b.EraType FROM BackupTechnologies b JOIN SwappedTechnologies s ON s.Original = Technologies.TechnologyType AND b.TechnologyType = s.Swapped
),
UITreeRow = (
SELECT b.UITreeRow FROM BackupTechnologies b JOIN SwappedTechnologies s ON s.Original = Technologies.TechnologyType AND b.TechnologyType = s.Swapped
)
WHERE NOT EXISTS(SELECT * FROM TechnologyQuotes);
UPDATE TechnologyPrereqs SET
Technology = IFNULL(
(SELECT ' ' || s.Original FROM SwappedTechnologies s WHERE s.Swapped = TechnologyPrereqs.Technology),
TechnologyPrereqs.Technology),
PrereqTech = IFNULL(
(SELECT ' ' || s.Original FROM SwappedTechnologies s WHERE s.Swapped = TechnologyPrereqs.PrereqTech)
, TechnologyPrereqs.PrereqTech)
WHERE NOT EXISTS(SELECT * FROM TechnologyQuotes);
UPDATE TechnologyPrereqs SET
Technology = TRIM(Technology),
PrereqTech = TRIM(PrereqTech)
WHERE NOT EXISTS(SELECT * FROM TechnologyQuotes);
END;
CREATE TABLE "RandomizedCivics" (
"CivicType" TEXT NOT NULL,
PRIMARY KEY(CivicType)
);
CREATE TABLE "SwappedCivics" (
"Original" TEXT NOT NULL UNIQUE,
"Swapped" TEXT NOT NULL UNIQUE,
PRIMARY KEY (Original, Swapped)
);
CREATE TABLE BackupCivics AS SELECT CivicType, Cost, EraType, UITreeRow FROM Civics;
CREATE TRIGGER OnCivicQuotes BEFORE INSERT ON CivicQuotes
BEGIN
INSERT INTO BackupCivics (CivicType, Cost, EraType, UITreeRow)
SELECT CivicType, Cost, EraType, UITreeRow FROM Civics
WHERE NOT EXISTS(SELECT * FROM CivicQuotes);
INSERT INTO RandomizedCivics (CivicType)
SELECT CivicType FROM Civics
WHERE NOT EXISTS(SELECT * FROM CivicQuotes)
ORDER BY RANDOM();
INSERT INTO SwappedCivics (Original, Swapped)
SELECT t.CivicType, r.CivicType FROM Civics t
JOIN RandomizedCivics r ON t.RowId = r.RowId
WHERE NOT EXISTS(SELECT * FROM CivicQuotes);
UPDATE Civics SET
Cost = (
SELECT b.Cost FROM BackupCivics b JOIN SwappedCivics s ON s.Original = Civics.CivicType AND b.CivicType = s.Swapped
),
EraType = (
SELECT b.EraType FROM BackupCivics b JOIN SwappedCivics s ON s.Original = Civics.CivicType AND b.CivicType = s.Swapped
),
UITreeRow = (
SELECT b.UITreeRow FROM BackupCivics b JOIN SwappedCivics s ON s.Original = Civics.CivicType AND b.CivicType = s.Swapped
)
WHERE NOT EXISTS(SELECT * FROM CivicQuotes);
UPDATE CivicPrereqs SET
Civic = IFNULL(
(SELECT ' ' || s.Original FROM SwappedCivics s WHERE s.Swapped = CivicPrereqs.Civic),
CivicPrereqs.Civic),
PrereqCivic = IFNULL(
(SELECT ' ' || s.Original FROM SwappedCivics s WHERE s.Swapped = CivicPrereqs.PrereqCivic)
, CivicPrereqs.PrereqCivic)
WHERE NOT EXISTS(SELECT * FROM CivicQuotes);
UPDATE CivicPrereqs SET
Civic = TRIM(Civic),
PrereqCivic = TRIM(PrereqCivic)
WHERE NOT EXISTS(SELECT * FROM CivicQuotes);
END;