Code Coverage |
||||||||||
Lines |
Functions and Methods |
Classes and Traits |
||||||||
| Total | |
23.39% |
29 / 124 |
|
22.22% |
2 / 9 |
CRAP | |
0.00% |
0 / 1 |
| core_kernel_persistence_smoothsql_Utils | |
23.39% |
29 / 124 |
|
22.22% |
2 / 9 |
955.61 | |
0.00% |
0 / 1 |
| sortByLanguage | |
100.00% |
18 / 18 |
|
100.00% |
1 / 1 |
3 | |||
| getFirstLanguage | |
87.50% |
7 / 8 |
|
0.00% |
0 / 1 |
4.03 | |||
| filterByLanguage | |
100.00% |
4 / 4 |
|
100.00% |
1 / 1 |
1 | |||
| identifyFirstLanguage | |
0.00% |
0 / 10 |
|
0.00% |
0 / 1 |
20 | |||
| buildSearchPattern | |
0.00% |
0 / 23 |
|
0.00% |
0 / 1 |
156 | |||
| buildPropertyQuery | |
0.00% |
0 / 15 |
|
0.00% |
0 / 1 |
30 | |||
| buildLanguagePattern | |
0.00% |
0 / 6 |
|
0.00% |
0 / 1 |
6 | |||
| buildUnionQuery | |
0.00% |
0 / 8 |
|
0.00% |
0 / 1 |
20 | |||
| buildFilterQuery | |
0.00% |
0 / 32 |
|
0.00% |
0 / 1 |
110 | |||
| 1 | <?php |
| 2 | |
| 3 | /** |
| 4 | * This program is free software; you can redistribute it and/or |
| 5 | * modify it under the terms of the GNU General Public License |
| 6 | * as published by the Free Software Foundation; under version 2 |
| 7 | * of the License (non-upgradable). |
| 8 | * |
| 9 | * This program is distributed in the hope that it will be useful, |
| 10 | * but WITHOUT ANY WARRANTY; without even the implied warranty of |
| 11 | * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the |
| 12 | * GNU General Public License for more details. |
| 13 | * |
| 14 | * You should have received a copy of the GNU General Public License |
| 15 | * along with this program; if not, write to the Free Software |
| 16 | * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA. |
| 17 | * |
| 18 | * Copyright (c) 2002-2008 (original work) Public Research Centre Henri Tudor & University of Luxembourg |
| 19 | * (under the project TAO & TAO2); |
| 20 | * 2008-2010 (update and modification) Deutsche Institut für Internationale Pädagogische Forschung |
| 21 | * (under the project TAO-TRANSFER); |
| 22 | * 2009-2012 (update and modification) Public Research Centre Henri Tudor |
| 23 | * (under the project TAO-SUSTAIN & TAO-DEV); |
| 24 | * 2012-2017 (update and modification) Open Assessment Technologies SA (under the project TAO-PRODUCT); |
| 25 | */ |
| 26 | |
| 27 | use oat\generis\model\kernel\persistence\smoothsql\search\filter\Filter; |
| 28 | use oat\generis\model\OntologyRdf; |
| 29 | use oat\generis\model\SqlSanitizeHelperTrait; |
| 30 | |
| 31 | /** |
| 32 | * Utility class for package core\kernel\persistence\smoothsql. |
| 33 | * |
| 34 | * @author Jérôme Bogaerts <jerome@taotesting.com> |
| 35 | * @author Cédric Alfonsi <cerdic.alfonsi@tudor.lu> |
| 36 | */ |
| 37 | class core_kernel_persistence_smoothsql_Utils |
| 38 | { |
| 39 | use SqlSanitizeHelperTrait; |
| 40 | |
| 41 | /** |
| 42 | * Sort a given $dataset by language. |
| 43 | * |
| 44 | * @param mixed dataset A PDO dataset. |
| 45 | * @param string langColname The name of the column corresponding to the language of results. |
| 46 | * @return array An array representing the sorted $dataset. |
| 47 | */ |
| 48 | public static function sortByLanguage($persistence, $dataset, $langColname, $selectedLanguage, $defaultLanguage) |
| 49 | { |
| 50 | $returnValue = []; |
| 51 | |
| 52 | $fallbackLanguage = ''; |
| 53 | |
| 54 | $sortedResults = [ |
| 55 | $selectedLanguage => [], |
| 56 | $defaultLanguage => [], |
| 57 | $fallbackLanguage => [] |
| 58 | ]; |
| 59 | |
| 60 | foreach ($dataset as $row) { |
| 61 | $sortedResults[$row[$langColname]][] = [ |
| 62 | 'value' => $persistence->getPlatForm()->getPhpTextValue($row['object']), |
| 63 | 'language' => $row[$langColname] |
| 64 | ]; |
| 65 | } |
| 66 | |
| 67 | $returnValue = array_merge( |
| 68 | $sortedResults[$selectedLanguage], |
| 69 | (count($sortedResults) > 2) ? $sortedResults[$defaultLanguage] : [], |
| 70 | $sortedResults[$fallbackLanguage] |
| 71 | ); |
| 72 | |
| 73 | return $returnValue; |
| 74 | } |
| 75 | |
| 76 | /** |
| 77 | * Get the first language encountered in the $values associative array. |
| 78 | * |
| 79 | * @param array values |
| 80 | * @return array |
| 81 | */ |
| 82 | public static function getFirstLanguage($values) |
| 83 | { |
| 84 | $returnValue = []; |
| 85 | |
| 86 | if (count($values) > 0) { |
| 87 | $previousLanguage = $values[0]['language']; |
| 88 | |
| 89 | foreach ($values as $value) { |
| 90 | if ($value['language'] == $previousLanguage) { |
| 91 | $returnValue[] = $value['value']; |
| 92 | } else { |
| 93 | break; |
| 94 | } |
| 95 | } |
| 96 | } |
| 97 | |
| 98 | return (array) $returnValue; |
| 99 | } |
| 100 | |
| 101 | /** |
| 102 | * Filter a $dataset by language. |
| 103 | * |
| 104 | * @param mixed $dataset |
| 105 | * @param string $langColname |
| 106 | * @return array |
| 107 | */ |
| 108 | public static function filterByLanguage( |
| 109 | common_persistence_SqlPersistence $persistence, |
| 110 | $dataset, |
| 111 | $langColname, |
| 112 | $selectedLanguage, |
| 113 | $defaultLanguage |
| 114 | ) { |
| 115 | $returnValue = []; |
| 116 | |
| 117 | $result = self::sortByLanguage($persistence, $dataset, $langColname, $selectedLanguage, $defaultLanguage); |
| 118 | $returnValue = self::getFirstLanguage($result); |
| 119 | |
| 120 | return $returnValue; |
| 121 | } |
| 122 | |
| 123 | /** |
| 124 | * Short description of method identifyFirstLanguage |
| 125 | * |
| 126 | * @access public |
| 127 | * @author Cédric Alfonsi, <cedric.alfonsi@tudor.lu> |
| 128 | * @param array values |
| 129 | * @return string |
| 130 | */ |
| 131 | public static function identifyFirstLanguage($values) |
| 132 | { |
| 133 | $returnValue = ''; |
| 134 | |
| 135 | if (count($values) > 0) { |
| 136 | $previousLanguage = $values[0]['language']; |
| 137 | $returnValue = $previousLanguage; |
| 138 | |
| 139 | foreach ($values as $value) { |
| 140 | if ($value['language'] == $previousLanguage) { |
| 141 | continue; |
| 142 | } else { |
| 143 | $returnValue = $previousLanguage; |
| 144 | break; |
| 145 | } |
| 146 | } |
| 147 | } |
| 148 | |
| 149 | return $returnValue; |
| 150 | } |
| 151 | |
| 152 | /** |
| 153 | * Build a SQL search pattern on basis of a pattern and a comparison mode. |
| 154 | * |
| 155 | * @param string $pattern A value to compare. |
| 156 | * @param boolean $like The manner to compare values. If set to true, the LIKE SQL operator will be used. If set |
| 157 | * to false, the = (equal) SQL operator will be used. |
| 158 | * @return string |
| 159 | */ |
| 160 | public static function buildSearchPattern(common_persistence_SqlPersistence $persistence, $pattern, $like = true) |
| 161 | { |
| 162 | $returnValue = ''; |
| 163 | |
| 164 | // Take care of RDFS Literals! |
| 165 | if ($pattern instanceof core_kernel_classes_Literal) { |
| 166 | $pattern = $pattern->__toString(); |
| 167 | } |
| 168 | |
| 169 | switch (gettype($pattern)) { |
| 170 | case 'object': |
| 171 | if ($pattern instanceof core_kernel_classes_Resource) { |
| 172 | $returnValue = '= ' . $persistence->quote($pattern->getUri()); |
| 173 | } else { |
| 174 | common_Logger::w('non ressource as search parameter: ' . get_class($pattern), ['GENERIS']); |
| 175 | } |
| 176 | break; |
| 177 | |
| 178 | default: |
| 179 | $patternToken = $pattern; |
| 180 | $wildcard = mb_strpos($patternToken, '*', 0, 'UTF-8') !== false; |
| 181 | $object = trim(str_replace('*', '%', $patternToken)); |
| 182 | |
| 183 | if ($like) { |
| 184 | if (!$wildcard && !preg_match("/^%/", $object)) { |
| 185 | $object = "%" . $object; |
| 186 | } |
| 187 | if (!$wildcard && !preg_match("/%$/", $object)) { |
| 188 | $object = $object . "%"; |
| 189 | } |
| 190 | if (!$wildcard && $object === '%') { |
| 191 | $object = '%%'; |
| 192 | } |
| 193 | $returnValue .= 'LIKE LOWER(' . $persistence->quote($object) . ')'; |
| 194 | } else { |
| 195 | $returnValue .= '= ' . $persistence->quote($patternToken); |
| 196 | } |
| 197 | break; |
| 198 | } |
| 199 | |
| 200 | return $returnValue; |
| 201 | } |
| 202 | |
| 203 | public static function buildPropertyQuery( |
| 204 | core_kernel_persistence_smoothsql_SmoothModel $model, |
| 205 | $propertyUri, |
| 206 | $values, |
| 207 | $like, |
| 208 | $lang = '' |
| 209 | ) { |
| 210 | $persistence = $model->getPersistence(); |
| 211 | |
| 212 | // Deal with predicate... |
| 213 | $predicate = $persistence->quote($propertyUri); |
| 214 | |
| 215 | // Deal with values... |
| 216 | if (is_array($values) === false) { |
| 217 | $values = [$values]; |
| 218 | } |
| 219 | |
| 220 | $valuePatterns = []; |
| 221 | foreach ($values as $val) { |
| 222 | $pattern = $like ? 'LOWER(object) ' : 'object '; |
| 223 | $valuePatterns[] = $pattern . self::buildSearchPattern($persistence, $val, $like); |
| 224 | } |
| 225 | |
| 226 | $sqlValues = implode(' OR ', $valuePatterns); |
| 227 | |
| 228 | // Deal with language... |
| 229 | $sqlLang = ''; |
| 230 | if (empty($lang) === false) { |
| 231 | $sqlLang = ' AND (' . self::buildLanguagePattern($persistence, $lang) . ')'; |
| 232 | } |
| 233 | |
| 234 | $query = "SELECT DISTINCT subject FROM statements WHERE (predicate = ${predicate}) AND (${sqlValues}${sqlLang})" |
| 235 | . ' AND modelid IN (' . implode(',', $model->getReadableModels()) . ')'; |
| 236 | |
| 237 | return $query; |
| 238 | } |
| 239 | |
| 240 | public static function buildLanguagePattern(common_persistence_SqlPersistence $persistence, $lang = '') |
| 241 | { |
| 242 | $languagePattern = ''; |
| 243 | |
| 244 | if (empty($lang) === false) { |
| 245 | $sqlEmpty = $persistence->quote(''); |
| 246 | $sqlLang = $persistence->quote($lang); |
| 247 | $languagePattern = "l_language = ${sqlEmpty} OR l_language = ${sqlLang}"; |
| 248 | } |
| 249 | |
| 250 | return $languagePattern; |
| 251 | } |
| 252 | |
| 253 | public static function buildUnionQuery($propertyQueries) |
| 254 | { |
| 255 | |
| 256 | if (count($propertyQueries) === 0) { |
| 257 | return false; |
| 258 | } elseif (count($propertyQueries) === 1) { |
| 259 | return $propertyQueries[0]; |
| 260 | } else { |
| 261 | // Add parenthesis. |
| 262 | $finalPropertyQueries = []; |
| 263 | foreach ($propertyQueries as $query) { |
| 264 | $finalPropertyQueries[] = "(${query})"; |
| 265 | } |
| 266 | |
| 267 | return implode(' UNION ALL ', $finalPropertyQueries); |
| 268 | } |
| 269 | } |
| 270 | |
| 271 | public static function buildFilterQuery( |
| 272 | core_kernel_persistence_smoothsql_SmoothModel $model, |
| 273 | $classUri, |
| 274 | array $propertyFilters, |
| 275 | $and = true, |
| 276 | $like = true, |
| 277 | $lang = '', |
| 278 | $offset = 0, |
| 279 | $limit = 0, |
| 280 | $order = '', |
| 281 | $orderDir = 'ASC' |
| 282 | ) { |
| 283 | |
| 284 | $orderDir = self::sanitizeOrderDirection($orderDir); |
| 285 | |
| 286 | $persistence = $model->getPersistence(); |
| 287 | |
| 288 | // Deal with target classes. |
| 289 | if (is_array($classUri) === false) { |
| 290 | $classUri = [$classUri]; |
| 291 | } |
| 292 | |
| 293 | $propertyQueries = [self::buildPropertyQuery($model, OntologyRdf::RDF_TYPE, $classUri, false)]; |
| 294 | foreach ($propertyFilters as $propertyUri => $filterValues) { |
| 295 | // no support of Filter object passed in the $propertyFilters array. |
| 296 | if ($filterValues instanceof Filter) { |
| 297 | throw new common_exception_NoImplementation(); |
| 298 | } |
| 299 | $propertyQueries[] = self::buildPropertyQuery($model, $propertyUri, $filterValues, $like, $lang); |
| 300 | } |
| 301 | |
| 302 | $unionQuery = self::buildUnionQuery($propertyQueries); |
| 303 | |
| 304 | if (($propCount = count($propertyFilters)) === 0) { |
| 305 | $query = self::buildPropertyQuery($model, OntologyRdf::RDF_TYPE, $classUri, false, $lang); |
| 306 | } else { |
| 307 | $unionCount = ($and === true) ? ($propCount + 1) : 2; |
| 308 | $query = "SELECT subject FROM (${unionQuery}) AS unionq GROUP BY subject HAVING count(*) >= ${unionCount}"; |
| 309 | } |
| 310 | |
| 311 | // Order... |
| 312 | if (!empty($order)) { |
| 313 | $orderPredicate = $persistence->quote($order); |
| 314 | |
| 315 | $sqlLang = ''; |
| 316 | if (!empty($lang)) { |
| 317 | $sqlEmptyLang = $persistence->quote(''); |
| 318 | $sqlRequestedLang = $persistence->quote($lang); |
| 319 | $sqlLang = " AND (l_language = ${sqlEmptyLang} OR l_language = ${sqlRequestedLang})"; |
| 320 | } |
| 321 | |
| 322 | $orderQueryId = $persistence->getPlatForm()->quoteIdentifier('orderq'); |
| 323 | $orderQuerySubject = $orderQueryId . '.' . $persistence->getPlatForm()->quoteIdentifier('subject'); |
| 324 | $orderQueryObject = $orderQueryId . '.' . $persistence->getPlatForm()->quoteIdentifier('object'); |
| 325 | |
| 326 | $sqlOrderFilter = "mainq.subject = ${orderQuerySubject} AND predicate = ${orderPredicate}${sqlLang}"; |
| 327 | |
| 328 | $query = "SELECT mainq.subject, ${orderQueryObject} FROM (${query}) AS mainq LEFT JOIN "; |
| 329 | $query .= "statements AS ${orderQueryId} ON (${sqlOrderFilter}) ORDER BY ${orderQueryObject} ${orderDir}"; |
| 330 | } |
| 331 | |
| 332 | // Limit... |
| 333 | if ($limit > 0) { |
| 334 | $query = $persistence->getPlatForm()->limitStatement($query, $limit, $offset); |
| 335 | } |
| 336 | |
| 337 | // Suffix order... |
| 338 | if (empty($order) === false) { |
| 339 | $query = "SELECT subject FROM (${query}) as rootq"; |
| 340 | } |
| 341 | |
| 342 | return $query; |
| 343 | } |
| 344 | } |