Merge branch 'master' of https://github.com/palmtown/user_sql into palmtown-master

# Conflicts:
#	lib/Backend/UserBackend.php

10 files changed, 75 insertions, 40 deletions

diff --git a/CHANGELOG.md b/CHANGELOG.md
index 66cfb78..9f51e81 100644
--- a/CHANGELOG.md
+++ b/CHANGELOG.md
@@ -10,6 +10,7 @@ and this project adheres to [Semantic Versioning](http://semver.org/spec/v2.0.0.
 - Argon2id support
 - System wide values option
 - Allow email login option
+- UID user table column
 
 ## [4.4.1] - 2020-02-02
 ### Fixed
diff --git a/README.md b/README.md
index 34acacc..76294da 100644
--- a/README.md
+++ b/README.md
@@ -69,7 +69,8 @@ The definition of user table. The table containing user accounts.
 Name | Description | Details
 --- | --- | ---
 **Table name** | The table name. | Mandatory for user backend.
-**Username** | Username column. | Mandatory for user backend.
+**UID** | UID column. | Mandatory for user backend.
+**Username** | Username column. | Optional.
 **Email** | E-mail column. | Mandatory for *Email sync* option.
 **Quota** | Quota column. | Mandatory for *Quota sync* option.
 **Home** | Home path column. | Mandatory for `Query` *Home sync* option.
@@ -119,7 +120,8 @@ If you don't have any database model yet you can use below tables (MySQL):
 ```
 CREATE TABLE sql_user
 (
-  username       VARCHAR(16) PRIMARY KEY,
+  uid            INT         PRIMARY KEY AUTO_INCREMENT,
+  username       VARCHAR(16) NOT NULL UNIQUE,
   display_name   TEXT        NULL,
   email          TEXT        NULL,
   quota          TEXT        NULL,
@@ -244,3 +246,4 @@ Since version 4.0.0 the whole core implementation has been rewritten.
   * Andreas Boehler for releasing the first version of this application
   * Johan Hendriks provided his user_postfixadmin
   * Ed Wildgoose for fixing possible SQL injection vulnerability
+  * Brandon Lee for implementing feature to separate uid from username resolving issues #108 & #121
diff --git a/js/settings.js b/js/settings.js
index da1f8b7..18a3d4d 100644
--- a/js/settings.js
+++ b/js/settings.js
@@ -119,7 +119,7 @@ user_sql.adminSettingsUI = function () {
         );
 
         autocomplete(
-            "#db-table-user-column-uid, #db-table-user-column-email, #db-table-user-column-quota, #db-table-user-column-home, #db-table-user-column-password, #db-table-user-column-name, #db-table-user-column-active, #db-table-user-column-disabled, #db-table-user-column-avatar, #db-table-user-column-salt",
+            "#db-table-user-column-uid, #db-table-user-column-username, #db-table-user-column-email, #db-table-user-column-quota, #db-table-user-column-home, #db-table-user-column-password, #db-table-user-column-name, #db-table-user-column-active, #db-table-user-column-disabled, #db-table-user-column-avatar, #db-table-user-column-salt",
             "/apps/user_sql/settings/autocomplete/table/user"
         );
 
diff --git a/lib/Backend/UserBackend.php b/lib/Backend/UserBackend.php
index 9693ff0..fca2959 100644
--- a/lib/Backend/UserBackend.php
+++ b/lib/Backend/UserBackend.php
@@ -301,15 +301,15 @@ final class UserBackend extends ABackend implements
      * Check if the user's password is correct then return its ID or
      * FALSE on failure.
      *
-     * @param string $uid      The user ID.
+     * @param string $username The username.
      * @param string $password The password.
      *
      * @return string|bool The user ID on success, false otherwise.
      */
-    public function checkPassword(string $uid, string $password)
+    public function checkPassword(string $username, string $password)
     {
         $this->logger->debug(
-            "Entering checkPassword($uid, *)", ["app" => $this->appName]
+            "Entering checkPassword($username, *)", ["app" => $this->appName]
         );
 
         $passwordAlgorithm = $this->getPasswordAlgorithm();
@@ -320,9 +320,9 @@ final class UserBackend extends ABackend implements
         $caseSensitive = empty($this->properties[Opt::CASE_INSENSITIVE_USERNAME]);
         $emailLogin = !empty($this->properties[Opt::EMAIL_LOGIN]);
         if ($emailLogin) {
-            $user = $this->userRepository->findByUidOrEmail($uid, $caseSensitive);
+            $user = $this->userRepository->findByUsernameOrEmail($username, $caseSensitive);
         } else {
-            $user = $this->userRepository->findByUid($uid, $caseSensitive);
+            $user = $this->userRepository->findByUsername($username, $caseSensitive);
         }
 
         if (!($user instanceof User)) {
diff --git a/lib/Constant/DB.php b/lib/Constant/DB.php
index 0442b13..f3365bd 100644
--- a/lib/Constant/DB.php
+++ b/lib/Constant/DB.php
@@ -2,7 +2,7 @@
 /**
  * Nextcloud - user_sql
  *
- * @copyright 2018 Marcin Łojewski <dev@mlojewski.me>
+ * @copyright 2020 Marcin Łojewski <dev@mlojewski.me>
  * @author    Marcin Łojewski <dev@mlojewski.me>
  *
  * This program is free software: you can redistribute it and/or modify
@@ -55,4 +55,5 @@ final class DB
     const USER_QUOTA_COLUMN = "db.table.user.column.quota";
     const USER_SALT_COLUMN = "db.table.user.column.salt";
     const USER_UID_COLUMN = "db.table.user.column.uid";
+    const USER_USERNAME_COLUMN = "db.table.user.column.username";
 }
diff --git a/lib/Constant/Query.php b/lib/Constant/Query.php
index de95984..68ceff8 100644
--- a/lib/Constant/Query.php
+++ b/lib/Constant/Query.php
@@ -34,10 +34,11 @@ final class Query
     const FIND_GROUP = "find_group";
     const FIND_GROUP_USERS = "find_group_users";
     const FIND_GROUPS = "find_groups";
-    const FIND_USER = "find_user";
-    const FIND_USER_BY_UID_OR_EMAIL = "find_user_by_uid_or_email";
-    const FIND_USER_BY_UID_OR_EMAIL_CASE_INSENSITIVE = "find_user_by_uid_or_email_case_insensitive";
-    const FIND_USER_CASE_INSENSITIVE = "find_user_case_insensitive";
+    const FIND_USER_BY_UID = "find_user_by_uid";
+    const FIND_USER_BY_USERNAME = "find_user_by_username";
+    const FIND_USER_BY_USERNAME_CASE_INSENSITIVE = "find_user_by_username_case_insensitive";
+    const FIND_USER_BY_USERNAME_OR_EMAIL = "find_user_by_username_or_email";
+    const FIND_USER_BY_USERNAME_OR_EMAIL_CASE_INSENSITIVE = "find_user_by_username_or_email_case_insensitive";
     const FIND_USER_GROUPS = "find_user_groups";
     const FIND_USERS = "find_users";
     const UPDATE_DISPLAY_NAME = "update_display_name";
@@ -52,4 +53,5 @@ final class Query
     const QUOTA_PARAM = "quota";
     const SEARCH_PARAM = "search";
     const UID_PARAM = "uid";
+    const USERNAME_PARAM = "username";
 }
diff --git a/lib/Model/User.php b/lib/Model/User.php
index 40ebf1c..b551bdf 100644
--- a/lib/Model/User.php
+++ b/lib/Model/User.php
@@ -2,7 +2,7 @@
 /**
  * Nextcloud - user_sql
  *
- * @copyright 2018 Marcin Łojewski <dev@mlojewski.me>
+ * @copyright 2020 Marcin Łojewski <dev@mlojewski.me>
  * @author    Marcin Łojewski <dev@mlojewski.me>
  *
  * This program is free software: you can redistribute it and/or modify
@@ -29,10 +29,14 @@ namespace OCA\UserSQL\Model;
 class User
 {
     /**
-     * @var string The UID (username).
+     * @var mixed The UID.
      */
     public $uid;
     /**
+     * @var string The user's username (login name).
+     */
+    public $username;
+    /**
      * @var string The user's email address.
      */
     public $email;
diff --git a/lib/Query/QueryProvider.php b/lib/Query/QueryProvider.php
index 0ddf0b2..1b5c735 100644
--- a/lib/Query/QueryProvider.php
+++ b/lib/Query/QueryProvider.php
@@ -64,18 +64,19 @@ class QueryProvider implements \ArrayAccess
 
         $gAdmin = $this->properties[DB::GROUP_ADMIN_COLUMN];
         $gGID = $this->properties[DB::GROUP_GID_COLUMN];
-        $gName = $this->properties[DB::GROUP_NAME_COLUMN];
+        $gName = $this->properties[DB::GROUP_NAME_COLUMN] || $this->properties[DB::GROUP_GID_COLUMN];
 
         $uActive = $this->properties[DB::USER_ACTIVE_COLUMN];
         $uAvatar = $this->properties[DB::USER_AVATAR_COLUMN];
         $uDisabled = $this->properties[DB::USER_DISABLED_COLUMN];
         $uEmail = $this->properties[DB::USER_EMAIL_COLUMN];
         $uHome = $this->properties[DB::USER_HOME_COLUMN];
-        $uName = $this->properties[DB::USER_NAME_COLUMN];
+        $uName = $this->properties[DB::USER_NAME_COLUMN] || $this->properties[DB::USER_USERNAME_COLUMN] || $this->properties[DB::USER_UID_COLUMN];
         $uPassword = $this->properties[DB::USER_PASSWORD_COLUMN];
         $uQuota = $this->properties[DB::USER_QUOTA_COLUMN];
         $uSalt = $this->properties[DB::USER_SALT_COLUMN];
         $uUID = $this->properties[DB::USER_UID_COLUMN];
+        $uUsername = $this->properties[DB::USER_USERNAME_COLUMN] || $this->properties[DB::USER_UID_COLUMN];
 
         $ugGID = $this->properties[DB::USER_GROUP_GID_COLUMN];
         $ugUID = $this->properties[DB::USER_GROUP_UID_COLUMN];
@@ -87,16 +88,18 @@ class QueryProvider implements \ArrayAccess
         $quotaParam = Query::QUOTA_PARAM;
         $searchParam = Query::SEARCH_PARAM;
         $uidParam = Query::UID_PARAM;
+        $usernameParam = Query::USERNAME_PARAM;
 
         $reverseActiveOpt = $this->properties[Opt::REVERSE_ACTIVE];
 
         $groupColumns
             = "g.$gGID AS gid, " .
-            (empty($gName) ? "g." . $gGID : "g." . $gName) . " AS name, " .
+            "g.$gName AS name, " .
             (empty($gAdmin) ? "false" : "g." . $gAdmin) . " AS admin";
         $userColumns
             = "u.$uUID AS uid, " .
-            (empty($uName) ? "u." . $uUID : "u." . $uName) . " AS name, " .
+            "u.$uUsername AS username, " .
+            "u.$uName AS name, " .
             (empty($uEmail) ? "null" : "u." . $uEmail) . " AS email, " .
             (empty($uQuota) ? "null" : "u." . $uQuota) . " AS quota, " .
             (empty($uHome) ? "null" : "u." . $uHome) . " AS home, " .
@@ -134,8 +137,7 @@ class QueryProvider implements \ArrayAccess
                 "SELECT ug.$ugUID AS uid " .
                 "FROM $userGroup ug " .
                 "WHERE ug.$ugGID = :$gidParam " .
-                "AND ug.$ugUID " .
-                "LIKE :$searchParam " .
+                "AND ug.$ugUID LIKE :$searchParam " .
                 "ORDER BY ug.$ugUID",
 
             Query::FIND_GROUPS =>
@@ -145,28 +147,34 @@ class QueryProvider implements \ArrayAccess
                 (empty($gName) ? "" : "OR g.$gName LIKE :$searchParam ") .
                 "ORDER BY g.$gGID",
 
-            Query::FIND_USER =>
-                "SELECT $userColumns, u.$uPassword AS password " .
+            Query::FIND_USER_BY_UID =>
+                "SELECT $userColumns " .
                 "FROM $user u " .
                 "WHERE u.$uUID = :$uidParam " .
                 (empty($uDisabled) ? "" : "AND NOT u.$uDisabled"),
 
-            Query::FIND_USER_BY_UID_OR_EMAIL =>
+            Query::FIND_USER_BY_USERNAME =>
+                "SELECT $userColumns, u.$uPassword AS password " .
+                "FROM $user u " .
+                "WHERE u.$uUsername = :$usernameParam " .
+                (empty($uDisabled) ? "" : "AND NOT u.$uDisabled"),
+
+            Query::FIND_USER_BY_USERNAME_CASE_INSENSITIVE =>
                 "SELECT $userColumns, u.$uPassword AS password " .
                 "FROM $user u " .
-                "WHERE u.$uUID = :$uidParam OR u.$uEmail = :$emailParam " .
+                "WHERE lower(u.$uUsername) = lower(:$usernameParam) " .
                 (empty($uDisabled) ? "" : "AND NOT u.$uDisabled"),
 
-            Query::FIND_USER_BY_UID_OR_EMAIL_CASE_INSENSITIVE =>
+            Query::FIND_USER_BY_USERNAME_OR_EMAIL =>
                 "SELECT $userColumns, u.$uPassword AS password " .
                 "FROM $user u " .
-                "WHERE lower(u.$uUID) = lower(:$uidParam) OR lower(u.$uEmail) = lower(:$emailParam) " .
+                "WHERE u.$uUsername = :$usernameParam OR u.$uEmail = :$emailParam " .
                 (empty($uDisabled) ? "" : "AND NOT u.$uDisabled"),
 
-            Query::FIND_USER_CASE_INSENSITIVE =>
+            Query::FIND_USER_BY_USERNAME_OR_EMAIL_CASE_INSENSITIVE =>
                 "SELECT $userColumns, u.$uPassword AS password " .
                 "FROM $user u " .
-                "WHERE lower(u.$uUID) = lower(:$uidParam) " .
+                "WHERE lower(u.$uUsername) = lower(:$usernameParam) OR lower(u.$uEmail) = lower(:$emailParam) " .
                 (empty($uDisabled) ? "" : "AND NOT u.$uDisabled"),
 
             Query::FIND_USER_GROUPS =>
diff --git a/lib/Repository/UserRepository.php b/lib/Repository/UserRepository.php
index f3e3fbb..aa87f8a 100644
--- a/lib/Repository/UserRepository.php
+++ b/lib/Repository/UserRepository.php
@@ -55,22 +55,37 @@ class UserRepository
     /**
      * Get an user entity object.
      *
-     * @param string $uid           The user ID.
+     * @param mixed $uid The user ID.
+     *
+     * @return User The user entity, NULL if it does not exists or
+     *              FALSE on failure.
+     */
+    public function findByUid($uid)
+    {
+        return $this->dataQuery->queryEntity(
+            Query::FIND_USER_BY_UID, User::class, [Query::UID_PARAM => $uid]
+        );
+    }
+
+    /**
+     * Get an user entity object.
+     *
+     * @param string $username      The username.
      * @param bool   $caseSensitive TRUE for case sensitive search,
      *                              FALSE for case insensitive search.
      *
      * @return User The user entity, NULL if it does not exists or
      *              FALSE on failure.
      */
-    public function findByUid($uid, $caseSensitive = true)
+    public function findByUsername($username, $caseSensitive = true)
     {
         if ($caseSensitive) {
             return $this->dataQuery->queryEntity(
-                Query::FIND_USER, User::class, [Query::UID_PARAM => $uid]
+                Query::FIND_USER_BY_USERNAME, User::class, [Query::USERNAME_PARAM => $username]
             );
         } else {
             return $this->dataQuery->queryEntity(
-                Query::FIND_USER_CASE_INSENSITIVE, User::class, [Query::UID_PARAM => $uid]
+                Query::FIND_USER_BY_USERNAME_CASE_INSENSITIVE, User::class, [Query::USERNAME_PARAM => $username]
             );
         }
     }
@@ -78,24 +93,24 @@ class UserRepository
     /**
      * Get an user entity object.
      *
-     * @param string $query         The user ID or email address.
+     * @param string $query         The username or email address.
      * @param bool   $caseSensitive TRUE for case sensitive search,
      *                              FALSE for case insensitive search.
      *
      * @return User The user entity, NULL if it does not exists or
      *              FALSE on failure.
      */
-    public function findByUidOrEmail($query, $caseSensitive = true)
+    public function findByUsernameOrEmail($query, $caseSensitive = true)
     {
         if ($caseSensitive) {
             return $this->dataQuery->queryEntity(
-                Query::FIND_USER_BY_UID_OR_EMAIL, User::class,
-                [Query::UID_PARAM => $query, Query::EMAIL_PARAM => $query]
+                Query::FIND_USER_BY_USERNAME_OR_EMAIL, User::class,
+                [Query::USERNAME_PARAM => $query, Query::EMAIL_PARAM => $query]
             );
         } else {
             return $this->dataQuery->queryEntity(
-                Query::FIND_USER_BY_UID_OR_EMAIL_CASE_INSENSITIVE, User::class,
-                [Query::UID_PARAM => $query, Query::EMAIL_PARAM => $query]
+                Query::FIND_USER_BY_USERNAME_OR_EMAIL_CASE_INSENSITIVE, User::class,
+                [Query::USERNAME_PARAM => $query, Query::EMAIL_PARAM => $query]
             );
         }
     }
diff --git a/templates/admin.php b/templates/admin.php
index add76e1..16be704 100644
--- a/templates/admin.php
+++ b/templates/admin.php
@@ -154,7 +154,8 @@ function print_select_options(
                 print_text_input($l, "db-table-user", "Table name", $_["db.table.user"]); ?>
                 <h3><?php p($l->t("Columns")); ?></h3>
                 <?php
-                print_text_input($l, "db-table-user-column-uid", "Username", $_["db.table.user.column.uid"]);
+                print_text_input($l, "db-table-user-column-uid", "UID", $_["db.table.user.column.uid"]);
+                print_text_input($l, "db-table-user-column-username", "Username", $_["db.table.user.column.username"]);
                 print_text_input($l, "db-table-user-column-email", "Email", $_["db.table.user.column.email"]);
                 print_text_input($l, "db-table-user-column-quota", "Quota", $_["db.table.user.column.quota"]);
                 print_text_input($l, "db-table-user-column-home", "Home", $_["db.table.user.column.home"]);