'lib' rewritten.

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]);
+    }
+}