|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
|
|
4
|
|
|
class DataIntegrityMoveFieldUpOrDownClassHierarchy extends BuildTask |
|
5
|
|
|
{ |
|
6
|
|
|
|
|
7
|
|
|
|
|
8
|
|
|
/** |
|
9
|
|
|
* standard SS variable |
|
10
|
|
|
* @var String |
|
11
|
|
|
*/ |
|
12
|
|
|
protected $title = "Move data field up or down class (table) hierarchy."; |
|
13
|
|
|
|
|
14
|
|
|
/** |
|
15
|
|
|
* standard SS variable |
|
16
|
|
|
* @var String |
|
17
|
|
|
*/ |
|
18
|
|
|
protected $description = " |
|
19
|
|
|
This is useful in case you change the hierarchy of classes |
|
20
|
|
|
and as a consequence your data ends up in the wrong table. |
|
21
|
|
|
To run this task you will first need to run a dev/build - |
|
22
|
|
|
after that all the eligible fields will be listed |
|
23
|
|
|
and the task gives you the ability to move each field individually as required. |
|
24
|
|
|
"; |
|
25
|
|
|
|
|
26
|
|
|
|
|
27
|
|
|
public function run($request) |
|
28
|
|
|
{ |
|
29
|
|
|
ini_set('max_execution_time', 3000); |
|
30
|
|
|
$oldTable = $request->getVar("oldtable"); |
|
31
|
|
|
$newTable = $request->getVar("newtable"); |
|
32
|
|
|
$field = $request->getVar("field"); |
|
33
|
|
|
$forreal = $request->getVar("forreal"); |
|
34
|
|
|
if ($oldTable && $newTable && $field) { |
|
35
|
|
|
if (class_exists($oldTable)) { |
|
36
|
|
|
if (class_exists($newTable)) { |
|
37
|
|
|
$oldFields = array_keys(DB::fieldList($oldTable)); |
|
|
|
|
|
|
38
|
|
|
$newFields = array_keys(DB::fieldList($newTable)); |
|
|
|
|
|
|
39
|
|
|
$jointFields = array_intersect($oldFields, $newFields); |
|
40
|
|
|
if (in_array($field, $jointFields)) { |
|
41
|
|
|
if ($forreal) { |
|
42
|
|
|
DB::alteration_message("Moving $field from $oldTable to $newTable", "deleted"); |
|
43
|
|
|
$sql = " |
|
44
|
|
|
UPDATE \"".$newTable."\" |
|
45
|
|
|
INNER JOIN \"".$oldTable."\" |
|
46
|
|
|
ON \"".$newTable."\".\"ID\" = \"".$oldTable."\".\"ID\" |
|
47
|
|
|
SET \"".$newTable."\".\"".$field."\" = \"".$oldTable."\".\"".$field."\" |
|
48
|
|
|
WHERE |
|
49
|
|
|
\"".$newTable."\".\"".$field."\" = 0 OR |
|
50
|
|
|
\"".$newTable."\".\"".$field."\" IS NULL OR |
|
51
|
|
|
\"".$newTable."\".\"".$field."\" = '0.00' OR |
|
52
|
|
|
\"".$newTable."\".\"".$field."\" = '' |
|
53
|
|
|
;"; |
|
54
|
|
|
DB::query($sql); |
|
55
|
|
|
$sql = " |
|
56
|
|
|
INSERT IGNORE INTO \"".$newTable."\" (ID, \"$field\") |
|
57
|
|
|
SELECT \"".$oldTable."\".ID, \"".$oldTable."\".\"$field\" |
|
58
|
|
|
FROM \"".$oldTable."\" |
|
59
|
|
|
LEFT JOIN \"".$newTable."\" |
|
60
|
|
|
ON \"".$newTable."\".\"ID\" = \"".$oldTable."\".\"ID\" |
|
61
|
|
|
WHERE |
|
62
|
|
|
\"".$newTable."\".\"ID\" IS NULL |
|
63
|
|
|
;"; |
|
64
|
|
|
DB::query($sql); |
|
65
|
|
|
$this->deleteField($oldTable, $field); |
|
66
|
|
|
} else { |
|
67
|
|
|
DB::alteration_message("TESTING a move of $field from $oldTable to $newTable"); |
|
68
|
|
|
$sql = " |
|
69
|
|
|
SELECT |
|
70
|
|
|
COUNT(\"".$newTable."\".\"ID\") AS C |
|
71
|
|
|
FROM \"".$oldTable."\" |
|
72
|
|
|
INNER JOIN \"".$newTable."\" |
|
73
|
|
|
ON \"".$newTable."\".\"ID\" = \"".$oldTable."\".\"ID\" |
|
74
|
|
|
;"; |
|
75
|
|
|
$matchingRowCount = DB::query($sql)->value(); |
|
76
|
|
|
$sql = " |
|
77
|
|
|
SELECT |
|
78
|
|
|
\"".$newTable."\".\"ID\" |
|
79
|
|
|
FROM \"".$oldTable."\" |
|
80
|
|
|
INNER JOIN \"".$newTable."\" |
|
81
|
|
|
ON \"".$newTable."\".\"ID\" = \"".$oldTable."\".\"ID\" |
|
82
|
|
|
;"; |
|
83
|
|
|
$rows = DB::query($sql); |
|
84
|
|
|
$matchingRows = array(); |
|
85
|
|
|
foreach ($rows as $row) { |
|
86
|
|
|
$matchingRows[$row["ID"]] = $row["ID"]; |
|
87
|
|
|
} |
|
88
|
|
|
|
|
89
|
|
|
$sql = " |
|
90
|
|
|
SELECT |
|
91
|
|
|
\"".$newTable."\".\"ID\", |
|
92
|
|
|
\"".$newTable."\".\"".$field."\" AS NEW".$field.", |
|
93
|
|
|
\"".$oldTable."\".\"".$field."\" AS OLD".$field." |
|
94
|
|
|
FROM \"".$oldTable."\" |
|
95
|
|
|
INNER JOIN \"".$newTable."\" |
|
96
|
|
|
ON \"".$newTable."\".\"ID\" = \"".$oldTable."\".\"ID\" |
|
97
|
|
|
WHERE |
|
98
|
|
|
( |
|
99
|
|
|
\"".$newTable."\".\"".$field."\" <> \"".$oldTable."\".\"".$field."\" |
|
100
|
|
|
) |
|
101
|
|
|
OR |
|
102
|
|
|
( |
|
103
|
|
|
(\"".$newTable."\".\"".$field."\" IS NULL AND \"".$oldTable."\".\"".$field."\" IS NOT NULL) |
|
104
|
|
|
OR |
|
105
|
|
|
(\"".$newTable."\".\"".$field."\" IS NOT NULL AND \"".$oldTable."\".\"".$field."\" IS NULL) |
|
106
|
|
|
) |
|
107
|
|
|
;"; |
|
108
|
|
|
$rows = DB::query($sql); |
|
109
|
|
|
if ($rows->numRecords()) { |
|
110
|
|
|
echo "<h3>DIFFERENCES in MATCHING ROWS ($matchingRowCount)</h3><table border=\"1\"><thead><tr><th>ID</th><th>OLD</th><th>NEW</th><th>ACTION</th></tr></thead><tbody>"; |
|
111
|
|
|
foreach ($rows as $row) { |
|
112
|
|
|
$action = "do nothing"; |
|
113
|
|
|
if (!$row["NEW".$field] || $row["NEW".$field] == '0.00') { |
|
114
|
|
|
$action = "override"; |
|
115
|
|
|
} |
|
116
|
|
|
echo "<tr><td>".$row["ID"]."</td><td>".$row["OLD".$field]."</td><td>".$row["NEW".$field]."</td><td>".$action."</td></tr>"; |
|
117
|
|
|
} |
|
118
|
|
|
echo "</tbody></table>"; |
|
119
|
|
|
} else { |
|
120
|
|
|
echo "<p>No differences!</p>"; |
|
121
|
|
|
} |
|
122
|
|
|
$sql = " |
|
123
|
|
|
SELECT |
|
124
|
|
|
COUNT(\"".$oldTable."\".\"ID\") AS C |
|
125
|
|
|
FROM \"".$oldTable."\" |
|
126
|
|
|
LEFT JOIN \"".$newTable."\" |
|
127
|
|
|
ON \"".$newTable."\".\"ID\" = \"".$oldTable."\".\"ID\" |
|
128
|
|
|
WHERE \"".$newTable."\".\"ID\" IS NULL; |
|
129
|
|
|
;"; |
|
130
|
|
|
$nonMatchingRowCount = DB::query($sql)->value(); |
|
131
|
|
|
echo "<h3>Number of rows to insert: ".$nonMatchingRowCount."</h3>"; |
|
132
|
|
|
echo "<h2><a href=\"".$this->Link()."?oldtable=$oldTable&newtable=$newTable&field=$field&forreal=1\">move now!</a></h2>"; |
|
133
|
|
|
} |
|
134
|
|
|
} |
|
135
|
|
|
} else { |
|
136
|
|
|
user_error("Field is not in both tables. We recommend that you run a <em>dev/build</em> first as this may solve the problem...."); |
|
137
|
|
|
} |
|
138
|
|
|
} else { |
|
139
|
|
|
user_error("Specificy valid oldtable using get var"); |
|
140
|
|
|
} |
|
141
|
|
|
} |
|
142
|
|
|
echo "<hr />"; |
|
143
|
|
|
$tablesToCheck = DB::query('SHOW tables'); |
|
144
|
|
|
$array = array(); |
|
145
|
|
|
$completed = array(); |
|
146
|
|
|
foreach ($tablesToCheck as $tableToCheck) { |
|
147
|
|
|
$tableToCheck = array_pop($tableToCheck); |
|
148
|
|
|
$fieldsToCheck = array_keys(DB::fieldList($tableToCheck)); |
|
|
|
|
|
|
149
|
|
|
$fieldsToCheck = array_diff($fieldsToCheck, array("ID")); |
|
150
|
|
|
$array[$tableToCheck] = $fieldsToCheck; |
|
151
|
|
|
} |
|
152
|
|
|
$testArray1 = $array; |
|
153
|
|
|
$testArray2 = $array; |
|
154
|
|
|
$link = array(); |
|
155
|
|
|
foreach ($testArray1 as $testTable1 => $testFields1) { |
|
156
|
|
|
foreach ($testArray2 as $testTable2 => $testFields2) { |
|
157
|
|
|
if (class_exists($testTable1)) { |
|
158
|
|
|
$parentArray1 = class_parents($testTable1); |
|
159
|
|
|
} else { |
|
160
|
|
|
$parentArray1 = array("MATCH"); |
|
161
|
|
|
} |
|
162
|
|
|
if (class_exists($testTable2)) { |
|
163
|
|
|
$parentArray2 = class_parents($testTable2); |
|
164
|
|
|
} else { |
|
165
|
|
|
$parentArray2 = array("MATCH"); |
|
166
|
|
|
} |
|
167
|
|
|
if (in_array($testTable2, $parentArray1) || in_array($testTable1, $parentArray2)) { |
|
168
|
|
|
$interSect = array_intersect($testFields1, $testFields2); |
|
169
|
|
|
if (count($interSect)) { |
|
170
|
|
|
if ( |
|
171
|
|
|
( |
|
172
|
|
|
isset($completed[$testTable1."_".$testTable2]) || |
|
173
|
|
|
isset($completed[$testTable2."_".$testTable1]) |
|
174
|
|
|
) |
|
175
|
|
|
&& ( |
|
176
|
|
|
(isset($completed[$testTable1."_".$testTable2]) ? count($completed[$testTable1."_".$testTable2]) : rand(0, 9999999)) == count($interSect) || |
|
177
|
|
|
(isset($completed[$testTable2."_".$testTable1]) ? count($completed[$testTable2."_".$testTable1]) : rand(0, 9999999)) == count($interSect) |
|
178
|
|
|
) |
|
179
|
|
|
) { |
|
180
|
|
|
//do nothing |
|
181
|
|
|
} else { |
|
182
|
|
|
$completed[$testTable1."_".$testTable2] = $interSect; |
|
183
|
|
|
|
|
184
|
|
|
$link["movetoparent"] = array(); |
|
185
|
|
|
if (in_array("DataObject", $parentArray1)) { |
|
186
|
|
|
$modelFields1 = array_keys((array)Config::inst()->get($testTable1, "db", Config::UNINHERITED)) + |
|
187
|
|
|
$hasOneArray = array_keys((array)Config::inst()->get($testTable1, "has_one", Config::UNINHERITED)); |
|
188
|
|
|
$hasOneArray = array_map( |
|
189
|
|
|
function ($val) { |
|
190
|
|
|
return $val."ID"; |
|
191
|
|
|
}, |
|
192
|
|
|
$hasOneArray |
|
193
|
|
|
); |
|
194
|
|
|
$modelFields1 + $hasOneArray; |
|
195
|
|
|
//$modelFields1 = array_keys((array)Injector::inst()->get($testTable1)->db()) + array_keys((array)Injector::inst()->get($testTable1)->has_one()); |
|
196
|
|
View Code Duplication |
foreach ($interSect as $moveableField) { |
|
|
|
|
|
|
197
|
|
|
if (in_array($moveableField, $modelFields1)) { |
|
198
|
|
|
$link["movetoparent"][$moveableField] = "<a href=\"".$this->Link()."?oldtable=$testTable2&newtable=$testTable1&field=$moveableField\">move from $testTable2 into $testTable1</a>"; |
|
199
|
|
|
; |
|
200
|
|
|
} |
|
201
|
|
|
} |
|
202
|
|
|
} |
|
203
|
|
|
$link["movetochild"] = array(); |
|
204
|
|
|
if (in_array("DataObject", $parentArray1)) { |
|
205
|
|
|
$modelFields2 = array_keys((array)Config::inst()->get($testTable2, "db", Config::UNINHERITED)) + array_keys((array)Config::inst()->get($testTable2, "has_one", Config::UNINHERITED)); |
|
206
|
|
|
$hasOneArray = array_keys((array)Config::inst()->get($testTable2, "has_one", Config::UNINHERITED)); |
|
207
|
|
|
$hasOneArray = array_map( |
|
208
|
|
|
function ($val) { |
|
209
|
|
|
return $val."ID"; |
|
210
|
|
|
}, |
|
211
|
|
|
$hasOneArray |
|
212
|
|
|
); |
|
213
|
|
|
$modelFields2 + $hasOneArray; |
|
214
|
|
|
//$modelFields2 = array_keys((array)Injector::inst()->get($testTable2)->db()) + array_keys((array)Injector::inst()->get($testTable2)->has_one()); |
|
215
|
|
View Code Duplication |
foreach ($interSect as $moveableField) { |
|
|
|
|
|
|
216
|
|
|
if (in_array($moveableField, $modelFields2)) { |
|
217
|
|
|
$link["movetochild"][$moveableField] = "<a href=\"".$this->Link()."?oldtable=$testTable1&newtable=$testTable2&field=$moveableField\">move from $testTable1 into $testTable2</a>"; |
|
218
|
|
|
} |
|
219
|
|
|
} |
|
220
|
|
|
} |
|
221
|
|
|
$str = "$testTable1 <> $testTable2<br /><ul>"; |
|
222
|
|
|
foreach ($interSect as $moveableField) { |
|
223
|
|
|
$str .= "<li>$moveableField: "; |
|
224
|
|
|
|
|
225
|
|
|
if (isset($link["movetoparent"][$moveableField])) { |
|
226
|
|
|
$str .= $link["movetoparent"][$moveableField]; |
|
227
|
|
|
} |
|
228
|
|
|
if (isset($link["movetoparent"][$moveableField]) && isset($link["movetochild"][$moveableField])) { |
|
229
|
|
|
$str .= " ||| "; |
|
230
|
|
|
} |
|
231
|
|
|
if (isset($link["movetochild"][$moveableField])) { |
|
232
|
|
|
$str .= $link["movetochild"][$moveableField]; |
|
233
|
|
|
} |
|
234
|
|
|
$str .= "</li>"; |
|
235
|
|
|
} |
|
236
|
|
|
$str .= "</ul>"; |
|
237
|
|
|
DB::alteration_message($str); |
|
238
|
|
|
} |
|
239
|
|
|
} |
|
240
|
|
|
} |
|
241
|
|
|
} |
|
242
|
|
|
} |
|
243
|
|
|
echo "<h1>======================== THE END ====================== </h1>"; |
|
244
|
|
|
} |
|
245
|
|
|
|
|
246
|
|
|
/** |
|
247
|
|
|
* |
|
248
|
|
|
* |
|
249
|
|
|
* @return string |
|
250
|
|
|
*/ |
|
251
|
|
|
protected function Link() |
|
252
|
|
|
{ |
|
253
|
|
|
return "/dev/tasks/DataIntegrityMoveFieldUpOrDownClassHierarchy/"; |
|
254
|
|
|
} |
|
255
|
|
|
|
|
256
|
|
|
/** |
|
257
|
|
|
* |
|
258
|
|
|
* @param string $table |
|
259
|
|
|
* @param string $field |
|
260
|
|
|
* |
|
261
|
|
|
* @return boolean |
|
262
|
|
|
*/ |
|
263
|
|
|
private function deleteField($table, $field) |
|
264
|
|
|
{ |
|
265
|
|
|
$fields = array_keys(DB::fieldList($table)); |
|
|
|
|
|
|
266
|
|
View Code Duplication |
if (!DB::query("SHOW TABLES LIKE '".$table."'")->value()) { |
|
|
|
|
|
|
267
|
|
|
DB::alteration_message("tried to delete $table.$field but TABLE does not exist", "deleted"); |
|
268
|
|
|
return false; |
|
269
|
|
|
} |
|
270
|
|
|
if (!class_exists($table)) { |
|
271
|
|
|
DB::alteration_message("tried to delete $table.$field but CLASS does not exist", "deleted"); |
|
272
|
|
|
return false; |
|
273
|
|
|
} |
|
274
|
|
View Code Duplication |
if (!in_array($field, $fields)) { |
|
|
|
|
|
|
275
|
|
|
DB::alteration_message("tried to delete $table.$field but FIELD does not exist", "deleted"); |
|
276
|
|
|
return false; |
|
277
|
|
|
} else { |
|
278
|
|
|
DB::alteration_message("Deleting $field in $table", "deleted"); |
|
279
|
|
|
DB::query('ALTER TABLE "'.$table.'" DROP "'.$field.'";'); |
|
280
|
|
|
$obj = singleton($table); |
|
281
|
|
|
//to do: make this more reliable - checking for versioning rather than SiteTree |
|
282
|
|
|
if ($obj instanceof SiteTree) { |
|
283
|
|
|
DB::query('ALTER TABLE "'.$table.'_Live" DROP "'.$field.'";'); |
|
284
|
|
|
DB::alteration_message("Deleted $field in {$table}_Live", "deleted"); |
|
285
|
|
|
DB::query('ALTER TABLE "'.$table.'_versions" DROP "'.$field.'";'); |
|
286
|
|
|
DB::alteration_message("Deleted $field in {$table}_versions", "deleted"); |
|
287
|
|
|
} |
|
288
|
|
|
return true; |
|
289
|
|
|
} |
|
290
|
|
|
} |
|
291
|
|
|
} |
|
292
|
|
|
|
sunnysideup /
silverstripe-dataintegritytests
Loading history...
This method has been deprecated. The supplier of the class has supplied an explanatory message.
The explanatory message should give you some clue as to whether and when the method will be removed from the class and what other method or class to use instead.