/** * Short description of method getPropertiesValues * * @access public * @author Joel Bout, <*****@*****.**> * @param Resource resource * @param array properties * @return array */ public function getPropertiesValues(core_kernel_classes_Resource $resource, $properties) { $returnValue = array(); // check whenever or not properties is empty if (count($properties) == 0) { return array(); } /*foreach($properties as $property){ $returnValue[$property->getUri()] = $this->getPropertyValues($resource, $property); }*/ $predicatesQuery = ''; //build the predicate query //$predicatesQuery = implode(',', $properties); foreach ($properties as $property) { $uri = is_string($property) ? $property : $property->getUri(); $returnValue[$uri] = array(); $predicatesQuery .= ", " . $this->getPersistence()->quote($uri); } $predicatesQuery = substr($predicatesQuery, 1); $platform = $this->getPersistence()->getPlatForm(); //the unique sql query $query = 'SELECT predicate, object, l_language FROM statements WHERE subject = ' . $this->getPersistence()->quote($resource->getUri()) . ' AND predicate IN (' . $predicatesQuery . ') AND (' . $platform->isNullCondition('l_language') . ' OR l_language = ' . $this->getPersistence()->quote(DEFAULT_LANG) . ' OR l_language = ' . $this->getPersistence()->quote(\common_session_SessionManager::getSession()->getDataLanguage()) . ') AND ' . $this->getModelReadSqlCondition(); $result = $this->getPersistence()->query($query); $rows = $result->fetchAll(); $sortedByLg = core_kernel_persistence_smoothsql_Utils::sortByLanguage($this->getPersistence(), $rows, 'l_language'); $identifiedLg = core_kernel_persistence_smoothsql_Utils::identifyFirstLanguage($sortedByLg); foreach ($rows as $row) { $value = $platform->getPhpTextValue($row['object']); $returnValue[$row['predicate']][] = common_Utils::isUri($value) ? new core_kernel_classes_Resource($value) : new core_kernel_classes_Literal($value); } return (array) $returnValue; }
/** * (non-PHPdoc) * @see core_kernel_persistence_ClassInterface::searchInstances() */ public function searchInstances(\core_kernel_classes_Class $resource, $propertyFilters = array(), $options = array()) { $returnValue = array(); $dbWrapper = \core_kernel_classes_DbWrapper::singleton(); // 'like' option. $like = true; if (isset($options['like'])) { $like = $options['like'] == true; } // 'chaining' option. $chaining = 'and'; if (!empty($options['chaining'])) { $chainingValue = strtolower($options['chaining']); if ($chainingValue == 'and' || $chainingValue == 'or') { $chaining = $chainingValue; } } // 'recursive' option. $recursive = 0; if (isset($options['recursive'])) { $recursive = intval($options['recursive']); } // 'limit' option. If not provided, we wet it to null as well. $limit = null; if (isset($options['limit'])) { $limit = intval($options['limit']); } // 'offset' option. If not provided, we set it to 0. $offset = 0; if (isset($options['offset'])) { $offset = intval($options['offset']); if (empty($limit)) { // offset requires a limit $limit = 1000000; } } // 'order' and 'orderdir' options. $order = null; $orderdir = 'ASC'; if (!empty($options['order'])) { $order = $options['order']; if (!empty($options['orderdir'])) { $orderdirValue = strtolower($options['orderdir']); if ($orderdirValue == 'asc' || $orderdirValue == 'desc') { $orderdir = $orderdirValue; } } } // 'lang' option. $lang = ''; if (!empty($options['lang'])) { $lang = $options['lang']; } // 'additionalClasses' option. $classes = array($resource); if (!empty($options['additionalClasses'])) { $classes = array_merge($classes, $options['additionalClasses']); } $queries = array(); foreach ($classes as $class) { $query = ''; // Get information about where and how to query the database. $baseTableName = ''; $referencer = ResourceReferencer::singleton(); $classLocations = $referencer->classLocations($class); if (isset($classLocations[0])) { // table to query located. $baseTableName = $classLocations[0]['table']; $propsTableName = $baseTableName . 'props'; $baseConditions = array(); // Conditions to apply on base table. $propsConditions = array(); // Conditions to apply on properties table. foreach ($propertyFilters as $propUri => $pattern) { $property = new \core_kernel_classes_Property($propUri); $propertyLocations = $referencer->propertyLocation($property); $patterns = !is_array($pattern) ? array($pattern) : $pattern; if (in_array($baseTableName, $propertyLocations)) { // The property to search is stored in the base table. $propShortName = HardapiUtils::getShortName($property); $subConditions = array(); foreach ($patterns as $pattern) { $searchPattern = \core_kernel_persistence_smoothsql_Utils::buildSearchPattern($pattern, $like); $subConditions[] = '"b"."' . $propShortName . '" ' . $searchPattern; } $baseConditions[] = '(' . implode(' OR ', $subConditions) . ')'; } else { // The property to search is stored in the properties table // or might be simply unknown. $pUri = $dbWrapper->quote($propUri); $condition = '("p"."property_uri" = ' . $pUri . ' AND ('; $subConditions = array(); foreach ($patterns as $pattern) { $searchPattern = \core_kernel_persistence_smoothsql_Utils::buildSearchPattern($pattern, $like); $subCondition = 'COALESCE("p"."property_value", "p"."property_foreign_uri") ' . $searchPattern; // Deal with the language. if (preg_match('/^[a-zA-Z]{2,4}$/', $lang)) { $quotedLang = $dbWrapper->quote($lang); $langToken = ' AND ("p"."l_language" = \'\' OR "p"."l_language" = ' . $quotedLang . ')'; $subCondition .= $langToken; } $subConditions[] = $subCondition; } $condition .= implode(' OR ', $subConditions) . '))'; $propsConditions[] = $condition; } // else do nothing, the property is not referenced anywhere. } $quotedTblName = $dbWrapper->quote($baseTableName); $query .= 'SELECT "b"."id", "b"."uri", ' . $quotedTblName . ' AS "tblname" FROM "' . $baseTableName . '" "b" '; if (empty($propsConditions) && !empty($baseConditions)) { // Scenario with only scalar values. $query .= 'WHERE '; $finalCondition = array(); foreach ($baseConditions as $bC) { $finalCondition[] = '(' . $bC . ')'; } $query .= implode(' ' . strtoupper($chaining) . ' ', $finalCondition); } else { if (!empty($propsConditions)) { // Mixed approach scenario. if ($chaining == 'and') { for ($i = 0; $i < count($propsConditions); $i++) { $joinName = 'bp' . $i; $query .= 'INNER JOIN('; $query .= 'SELECT "b"."id", "b"."uri" FROM "' . $baseTableName . '" "b" '; $query .= 'INNER JOIN "' . $propsTableName . '" "p" ON ("b"."id" = "p"."instance_id" AND ('; $query .= $propsConditions[$i]; $query .= '))'; $query .= ') AS "' . $joinName . '" ON ("b"."id" = "' . $joinName . '"."id")'; } if (!empty($baseConditions)) { $query .= ' WHERE '; $query .= implode(' AND ', $baseConditions); } } else { $query .= 'INNER JOIN('; $query .= 'SELECT "b"."id", "b"."uri" FROM "' . $baseTableName . '" "b" '; $query .= 'INNER JOIN "' . $propsTableName . '" "p" ON ("b"."id" = "p"."instance_id" AND('; $query .= implode(' OR ', array_merge($propsConditions, $baseConditions)); $query .= '))'; $query .= ') AS "bp" ON ("b"."id" = "bp"."id")'; } } } $queries[] = $query; } } if (!empty($queries)) { $finalQuery = implode(' UNION ', $queries); //TODO this work with mysql pgsql but not with MSSQL, I remove it and see no issue //$finalQuery .= ' GROUP BY "b"."id", "b"."uri", "tblname"'; $finalQuery .= ' GROUP BY "b"."id", "b"."uri"'; if (!empty($limit) || !empty($offset)) { $finalQuery = $dbWrapper->limitStatement($finalQuery, $limit, $offset); } try { $result = $dbWrapper->query($finalQuery); $idUris = array(); while ($row = $result->fetch()) { if (empty($idUris[$row['tblname']])) { $idUris[$row['tblname']] = array(); } $idUris[$row['tblname']][$row['id']] = $row['uri']; } // Order if needed. if (!empty($order) && !empty($idUris)) { $queries = array(); foreach ($idUris as $tblname => $value) { $ids = array_keys($value); $ids = implode(', ', $ids); $orderProp = new \core_kernel_classes_Property($order); $propertyLocation = $referencer->propertyLocation($orderProp); $orderPropShortName = HardapiUtils::getShortName($orderProp); $propstblname = $tblname . 'props'; $sqlQuery = 'SELECT "id", "uri", "property_value" FROM ('; $sqlQuery .= 'SELECT "b"."id", "b"."uri", "p"."property_uri" AS "property_uri", COALESCE("p"."property_value", "p"."property_foreign_uri") AS "property_value" FROM "' . $tblname . '" "b" '; $sqlQuery .= 'INNER JOIN "' . $propstblname . '" "p" '; // Deal with the language. $langToken = ''; if (preg_match('/^[a-zA-Z]{2,4}$/', $lang)) { $quotedLang = $dbWrapper->quote($lang); $langToken = ' AND ("p".l_language" = \'\' OR "p".l_language" = ' . $quotedLang . ')'; } $sqlQuery .= 'ON ("b"."id" IN(' . $ids . ') AND "b"."id" = "p"."instance_id"' . $langToken . ') '; if (in_array($baseTableName, $propertyLocation)) { $sqlQuery .= 'UNION '; $sqlQuery .= 'SELECT "b"."id", "b"."uri", \'' . $order . '\' AS "property_uri", "b"."' . $orderPropShortName . '" AS "property_value" '; $sqlQuery .= 'FROM "' . $tblname . '" "b" '; $sqlQuery .= 'JOIN "' . $propstblname . '" "p" '; $sqlQuery .= 'ON ("b"."id" IN(' . $ids . ') AND "b"."id" = "p"."instance_id") '; } $sqlQuery .= ') AS "s"'; $queries[] = $sqlQuery; } $finalQuery = implode(' UNION ', $queries); $finalQuery = 'SELECT "uri", "property_value" FROM (' . $finalQuery; $finalQuery .= ') AS "search" GROUP BY "uri", "property_value" ORDER by "property_value"'; $idUris = array(); $sqlResult = $dbWrapper->query($finalQuery); while ($row = $sqlResult->fetch()) { $instance = new \core_kernel_classes_Resource($row['uri']); $returnValue[$instance->getUri()] = $instance; } } else { foreach ($idUris as $tblname => $value) { foreach ($value as $uri) { $instance = new \core_kernel_classes_Resource($uri); $returnValue[$instance->getUri()] = $instance; } } } } catch (\PDOException $e) { throw new Exception("Unable to search instances for the resource {$resource->getUri()} : " . $e->getMessage()); } } return (array) $returnValue; }
/** * Short description of method removePropertyValueByLg * * @access public * @author Joel Bout, <*****@*****.**> * @param Resource resource * @param Property property * @param string lg * @param array options * @return boolean */ public function removePropertyValueByLg(\core_kernel_classes_Resource $resource, \core_kernel_classes_Property $property, $lg, $options = array()) { $returnValue = (bool) false; $dbWrapper = \core_kernel_classes_DbWrapper::singleton(); // Optional params $pattern = isset($options['pattern']) && !is_null($options['pattern']) ? $options['pattern'] : null; $like = isset($options['like']) && $options['like'] == true ? true : false; // Get the table name $tableName = ResourceReferencer::singleton()->resourceLocation($resource); if ($property->isLgDependent()) { $resourceId = HardapiUtils::getResourceIdByTable($resource, $tableName); if ($resourceId) { $propsTableName = $tableName . 'props'; $query = 'DELETE FROM "' . $propsTableName . '" WHERE "property_uri" = \'' . $property->getUri() . '\' AND "instance_id" = \'' . $resourceId . '\' AND "l_language" = \'' . $lg . '\' '; //build additionnal conditions: $additionalConditions = array(); if (!is_null($pattern)) { if (is_string($pattern)) { $searchPattern = \core_kernel_persistence_smoothsql_Utils::buildSearchPattern($pattern, $like); $additionalConditions[] = ' ("property_value" ' . $searchPattern . ' OR "property_foreign_uri" ' . $searchPattern . ') '; } else { if (is_array($pattern)) { if (count($pattern) > 0) { $multiCondition = "("; foreach ($pattern as $i => $patternToken) { $searchPattern = \core_kernel_persistence_smoothsql_Utils::buildSearchPattern($patternToken, $like); if ($i > 0) { $multiCondition .= " OR "; } $multiCondition .= ' ("property_value" ' . $searchPattern . ' OR "property_foreign_uri" ' . $searchPattern . ') '; } $additionalConditions[] = "{$multiCondition}) "; } } } } foreach ($additionalConditions as $i => $additionalCondition) { $query .= " AND ( {$additionalCondition} ) "; } try { $result = $dbWrapper->exec($query); $returnValue = true; } catch (\PDOException $e) { throw new Exception("Unable to delete property values (multiple) for the instance {$resource->getUri()} : " . $e->getMessage()); } } } return (bool) $returnValue; }
/** * * @param core_kernel_classes_Class $resource * @param array $propertyFilters * @param array $options * @return string */ public function getFilteredQuery(core_kernel_classes_Class $resource, $propertyFilters = array(), $options = array()) { $rdftypes = array(); // Check recursivity... if (isset($options['recursive']) && $options['recursive']) { foreach ($this->getSubClasses($resource, $options['recursive']) as $subClass) { $rdftypes[] = $subClass->getUri(); } } // Check additional classes... if (isset($options['additionalClasses'])) { foreach ($options['additionalClasses'] as $aC) { $rdftypes[] = $aC instanceof core_kernel_classes_Resource ? $aC->getUri() : $aC; $rdftypes = array_unique($rdftypes); } } // Add the class type of the given class if (!in_array($resource->getUri(), $rdftypes)) { $rdftypes[] = $resource->getUri(); } $and = isset($options['chaining']) === false ? true : (strtolower($options['chaining']) === 'and' ? true : false); $like = isset($options['like']) === false ? true : $options['like']; $lang = isset($options['lang']) === false ? '' : $options['lang']; $offset = isset($options['offset']) === false ? 0 : $options['offset']; $limit = isset($options['limit']) === false ? 0 : $options['limit']; $order = isset($options['order']) === false ? '' : $options['order']; $orderdir = isset($options['orderdir']) === false ? 'ASC' : $options['orderdir']; $query = core_kernel_persistence_smoothsql_Utils::buildFilterQuery($this->getModel(), $rdftypes, $propertyFilters, $and, $like, $lang, $offset, $limit, $order, $orderdir); return $query; }