/**
* 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;
}
