diff options
author | Marcin Łojewski <marcin.lojewski@mlojewski.me> | 2018-03-03 00:56:13 +0300 |
---|---|---|
committer | Marcin Łojewski <marcin.lojewski@mlojewski.me> | 2018-03-25 21:57:31 +0300 |
commit | c1cc89f456231ecb853fc7d3a4d47ec6dcaa41f0 (patch) | |
tree | e794c3d3b1f0c41c3751884f77f8f8ae4fca61e1 /lib/Query | |
parent | ed5ec8247957df5ced15dc5ff1fde2394dce1d11 (diff) |
'lib' rewritten.
Diffstat (limited to 'lib/Query')
-rw-r--r-- | lib/Query/DataQuery.php | 267 | ||||
-rw-r--r-- | lib/Query/QueryProvider.php | 195 |
2 files changed, 462 insertions, 0 deletions
diff --git a/lib/Query/DataQuery.php b/lib/Query/DataQuery.php new file mode 100644 index 0000000..8f7f990 --- /dev/null +++ b/lib/Query/DataQuery.php @@ -0,0 +1,267 @@ +<?php +/** + * Nextcloud - user_sql + * + * @copyright 2018 Marcin Łojewski <dev@mlojewski.me> + * @author Marcin Łojewski <dev@mlojewski.me> + * + * This program is free software: you can redistribute it and/or modify + * it under the terms of the GNU Affero General Public License as + * published by the Free Software Foundation, either version 3 of the + * License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU Affero General Public License for more details. + * + * You should have received a copy of the GNU Affero General Public License + * along with this program. If not, see <https://www.gnu.org/licenses/>. + */ + +namespace OCA\UserSQL\Query; + +use Doctrine\DBAL\Driver\Statement; +use OC\DB\Connection; +use OC\DB\ConnectionFactory; +use OCA\UserSQL\Constant\DB; +use OCA\UserSQL\Constant\Query; +use OCA\UserSQL\Properties; +use OCP\ILogger; + +/** + * Used to query a database. + * + * @author Marcin Łojewski <dev@mlojewski.me> + */ +class DataQuery +{ + /** + * @var string The application name. + */ + private $appName; + /** + * @var ILogger The logger instance. + */ + private $logger; + /** + * @var Properties The properties array. + */ + private $properties; + /** + * @var QueryProvider The query provider. + */ + private $queryProvider; + /** + * @var Connection The database connection. + */ + private $connection; + + /** + * The class constructor. + * + * @param string $AppName The application name. + * @param ILogger $logger The logger instance. + * @param Properties $properties The properties array. + * @param QueryProvider $queryProvider The query provider. + */ + public function __construct( + $AppName, ILogger $logger, Properties $properties, + QueryProvider $queryProvider + ) { + $this->appName = $AppName; + $this->logger = $logger; + $this->properties = $properties; + $this->queryProvider = $queryProvider; + $this->connection = false; + } + + /** + * Execute an update query. + * + * @param string $queryName The query name. + * @param array $params The query parameters. + * + * @see Query + * @return bool TRUE on success, FALSE otherwise. + */ + public function update($queryName, $params = []) + { + return $this->execQuery($queryName, $params) !== false; + } + + /** + * Run a given query and return the result. + * + * @param string $queryName The query to execute. + * @param array $params The query parameters to bind. + * @param int $limit Results limit. Defaults to -1 (no limit). + * @param int $offset Results offset. Defaults to 0. + * + * @return Statement|bool Result of query or FALSE on failure. + */ + private function execQuery( + $queryName, $params = [], $limit = -1, $offset = 0 + ) { + if ($this->connection === false) { + $this->connectToDatabase(); + } + + $query = $this->queryProvider[$queryName]; + $result = $this->connection->prepare($query, $limit, $offset); + + foreach ($params as $param => $value) { + $result->bindValue(":" . $param, $value); + } + + $this->logger->debug( + "Executing query:" . $query . ", " . implode(",", $params), + ["app" => $this->appName] + ); + + if ($result->execute() !== true) { + $error = $result->errorInfo(); + $this->logger->error( + "Could not execute the query: " . implode(", ", $error), + ["app" => $this->appName] + ); + return false; + } + + return $result; + } + + /** + * Connect to the database using Nextcloud's DBAL. + */ + private function connectToDatabase() + { + $connectionFactory = new ConnectionFactory( + \OC::$server->getSystemConfig() + ); + + $parameters = array( + "host" => $this->properties[DB::HOSTNAME], + "password" => $this->properties[DB::PASSWORD], + "user" => $this->properties[DB::USERNAME], + "dbname" => $this->properties[DB::DATABASE], + "tablePrefix" => "" + ); + + $this->connection = $connectionFactory->getConnection( + $this->properties[DB::DRIVER], $parameters + ); + + $this->logger->debug( + "Database connection established.", ["app" => $this->appName] + ); + } + + /** + * Fetch a value from the first row and the first column which + * the given query returns. Empty result set is consider to be a failure. + * + * @param string $queryName The query to execute. + * @param array $params The query parameters to bind. + * @param bool $failure Value returned on database query failure. + * Defaults to FALSE. + * + * @return array|bool Queried value or $failure value on failure. + */ + public function queryValue($queryName, $params = [], $failure = false) + { + $result = $this->execQuery($queryName, $params); + if ($result === false) { + return false; + } + + $row = $result->fetch(\PDO::FETCH_COLUMN); + if ($row === false) { + return $failure; + } + + return $row; + } + + /** + * Fetch values from the first column which the given query returns. + * + * @param string $queryName The query to execute. + * @param array $params The query parameters to bind. + * @param int $limit Results limit. Defaults to -1 (no limit). + * @param int $offset Results offset. Defaults to 0. + * + * @return array|bool Queried column or FALSE on failure. + */ + public function queryColumn( + $queryName, $params = [], $limit = -1, $offset = 0 + ) { + $result = $this->execQuery($queryName, $params, $limit, $offset); + if ($result === false) { + return false; + } + + $column = $result->fetchAll(\PDO::FETCH_COLUMN); + return $column; + } + + /** + * Fetch entity returned by the given query. + * + * @param string $queryName The query to execute. + * @param string $entityClass The entity class name. + * @param array $params The query parameters to bind. + * + * @return mixed|null The queried entity, NULL if it does not exists or + * FALSE on failure. + */ + public function queryEntity($queryName, $entityClass, $params = []) + { + $result = $this->execQuery($queryName, $params); + if ($result === false) { + return false; + } + + $result->setFetchMode(\PDO::FETCH_CLASS, $entityClass); + $entity = $result->fetch(); + + if ($entity === false) { + return null; + } + + if (empty($entity) === true) { + $this->logger->debug( + "Empty result for query: " . $queryName, + ["app" => $this->appName] + ); + return null; + } + + return $entity; + } + + /** + * Fetch entities returned by the given query. + * + * @param string $queryName The query to execute. + * @param string $entityClass The entity class name. + * @param array $params The query parameters to bind. + * @param int $limit Results limit. Defaults to -1 (no limit). + * @param int $offset Results offset. Defaults to 0. + * + * @return mixed|null The queried entities or FALSE on failure. + */ + public function queryEntities( + $queryName, $entityClass, $params = [], $limit = -1, $offset = 0 + ) { + $result = $this->execQuery($queryName, $params, $limit, $offset); + if ($result === false) { + return false; + } + + $result->setFetchMode(\PDO::FETCH_CLASS, $entityClass); + $entities = $result->fetchAll(); + + return $entities; + } +} diff --git a/lib/Query/QueryProvider.php b/lib/Query/QueryProvider.php new file mode 100644 index 0000000..4de8084 --- /dev/null +++ b/lib/Query/QueryProvider.php @@ -0,0 +1,195 @@ +<?php +/** + * Nextcloud - user_sql + * + * @copyright 2018 Marcin Łojewski <dev@mlojewski.me> + * @author Marcin Łojewski <dev@mlojewski.me> + * + * This program is free software: you can redistribute it and/or modify + * it under the terms of the GNU Affero General Public License as + * published by the Free Software Foundation, either version 3 of the + * License, or (at your option) any later version. + * + * This program is distributed in the hope that it will be useful, + * but WITHOUT ANY WARRANTY; without even the implied warranty of + * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + * GNU Affero General Public License for more details. + * + * You should have received a copy of the GNU Affero General Public License + * along with this program. If not, see <https://www.gnu.org/licenses/>. + */ + +namespace OCA\UserSQL\Query; + +use OCA\UserSQL\Constant\DB; +use OCA\UserSQL\Constant\Query; +use OCA\UserSQL\Properties; + +/** + * Provides queries array. + * + * @author Marcin Łojewski <dev@mlojewski.me> + */ +class QueryProvider implements \ArrayAccess +{ + /** + * @var Properties The properties array. + */ + private $properties; + /** + * @var array The queries array. + */ + private $queries; + + /** + * The class constructor. + * + * @param Properties $properties The properties array. + */ + public function __construct(Properties $properties) + { + $this->properties = $properties; + $this->loadQueries(); + } + + /** + * Load queries to the array. + */ + private function loadQueries() + { + $group = $this->properties[DB::GROUP_TABLE]; + $userGroup = $this->properties[DB::USER_GROUP_TABLE]; + $user = $this->properties[DB::USER_TABLE]; + + $gAdmin = $this->properties[DB::GROUP_ADMIN_COLUMN]; + $gGID = $this->properties[DB::GROUP_GID_COLUMN]; + $gName = $this->properties[DB::GROUP_NAME_COLUMN]; + + $uAvatar = $this->properties[DB::USER_AVATAR_COLUMN]; + $uEmail = $this->properties[DB::USER_EMAIL_COLUMN]; + $uHome = $this->properties[DB::USER_HOME_COLUMN]; + $uName = $this->properties[DB::USER_NAME_COLUMN]; + $uPassword = $this->properties[DB::USER_PASSWORD_COLUMN]; + $uUID = $this->properties[DB::USER_UID_COLUMN]; + + $ugGID = $this->properties[DB::USER_GROUP_GID_COLUMN]; + $ugUID = $this->properties[DB::USER_GROUP_UID_COLUMN]; + + $gidParam = Query::GID_PARAM; + $nameParam = Query::NAME_PARAM; + $passwordParam = Query::PASSWORD_PARAM; + $searchParam = Query::SEARCH_PARAM; + $uidParam = Query::UID_PARAM; + + $groupColumns + = "$gGID AS gid, " . + "$gName AS name, " . + "$gAdmin AS admin"; + $userColumns + = "$uUID AS uid, " . + "$uName AS name, " . + "$uEmail AS email, " . + "$uHome AS home, " . + "$uAvatar AS avatar"; + + $this->queries = [ + Query::BELONGS_TO_ADMIN => + "SELECT COUNT($gGID) > 0 AS admin " . + "FROM $group, $userGroup " . + "WHERE $ugGID = $gGID " . + "AND $ugUID = :$uidParam " . + "AND $gAdmin", + + Query::COUNT_GROUPS => + "SELECT COUNT($ugGID) " . + "FROM $userGroup " . + "WHERE $ugGID = :$gidParam " . + "AND $ugUID " . + "LIKE :$searchParam", + + Query::COUNT_USERS => + "SELECT COUNT($uUID) AS count " . + "FROM $user " . + "WHERE $uUID LIKE :$searchParam", + + Query::FIND_GROUP => + "SELECT $groupColumns " . + "FROM $group " . + "WHERE $gGID = :$gidParam", + + Query::FIND_GROUP_USERS => + "SELECT $ugUID AS uid " . + "FROM $userGroup " . + "WHERE $ugGID = :$gidParam " . + "AND $ugUID " . + "LIKE :$searchParam " . + "ORDER BY $ugUID", + + Query::FIND_GROUPS => + "SELECT $groupColumns " . + "FROM $group " . + "WHERE $gGID LIKE :$searchParam " . + "ORDER BY $gGID", + + Query::FIND_USER => + "SELECT $userColumns, $uPassword AS password " . + "FROM $user " . + "WHERE $uUID = :$uidParam", + + Query::FIND_USER_GROUPS => + "SELECT $groupColumns " . + "FROM $group, $userGroup " . + "WHERE $ugGID = $gGID " . + "AND $ugUID = :$uidParam " . + "ORDER BY $gGID", + + Query::FIND_USERS => + "SELECT $userColumns " . + "FROM $user " . + "WHERE $uUID LIKE :$searchParam " . + "ORDER BY $uUID", + + Query::SAVE_USER => + "UPDATE $user " . + "SET $uPassword = :$passwordParam, " . + "$uName = :$nameParam " . + "WHERE $uUID = :$uidParam", + ]; + } + + /** + * @inheritdoc + */ + public function offsetExists($offset) + { + return isset($this->queries[$offset]); + } + + /** + * @inheritdoc + */ + public function offsetGet($offset) + { + if (isset($this->queries[$offset])) { + return $this->queries[$offset]; + } else { + return null; + } + } + + /** + * @inheritdoc + */ + public function offsetSet($offset, $value) + { + $this->queries[$offset] = $value; + } + + /** + * @inheritdoc + */ + public function offsetUnset($offset) + { + unset($this->queries[$offset]); + } +} |