Rewritten database layout and permission/role management

1 files changed, 82 insertions, 9 deletions

diff --git a/src/main/kotlin/DatabaseController.kt b/src/main/kotlin/DatabaseController.kt
index 0a7026c..a51c00d 100644
--- a/src/main/kotlin/DatabaseController.kt
+++ b/src/main/kotlin/DatabaseController.kt
@@ -11,27 +11,45 @@ class DatabaseController(dbFileLocation: String = "main.db") {
     private val log = Logger.getLogger(this.javaClass.name)
 
     /**
-     * Database table for the file location indexing
+     * Database table indexing the file locations
      */
     object FileLocation : Table() {
         val id = integer("id").autoIncrement().primaryKey()
-        val location = text("location")
+        val location = text("location").uniqueIndex()
+        val username = varchar("username", 24)
     }
 
     /**
-     * Database table to index the users with their regarding passwords
+     * Database table indexing the users with their regarding passwords
      */
     object UserData : Table() {
         val id = integer("id").autoIncrement().primaryKey()
         val username = varchar("username", 24).uniqueIndex()
         val password = varchar("password", 64)
-        val role = varchar("role", 64).default("USER")
+    }
+
+    /**
+     * Database table indexing the users with their regarding role (multi line per user)
+     */
+    object UserRoles : Table() {
+        val id = integer("id").autoIncrement().primaryKey()
+        val userId = integer("userId").references(UserData.id)
+        val roleId = integer("role").references(RolesData.id)
+    }
+
+    /**
+     * Database table declaring available roles
+     */
+    object RolesData : Table() {
+        val id = integer("id").autoIncrement().primaryKey()
+        val role = varchar("roles", 16)
     }
 
     /**
      * Database table storing general data/states
      */
     object General : Table() {
+        val id = integer("id").autoIncrement().primaryKey()
         val initialUse = integer("initialUse").default(1).primaryKey()
     }
 
@@ -41,7 +59,7 @@ class DatabaseController(dbFileLocation: String = "main.db") {
 
         // Add tables
         transaction {
-            SchemaUtils.createMissingTablesAndColumns(FileLocation, UserData, General)
+            SchemaUtils.createMissingTablesAndColumns(FileLocation, UserData, UserRoles, RolesData, General)
         }
     }
 
@@ -51,10 +69,14 @@ class DatabaseController(dbFileLocation: String = "main.db") {
     fun createUser(usernameString: String, passwordString: String, roleString: String) {
         transaction {
             try {
-                UserData.insert {
+                val usersId = UserData.insert {
                     it[username] = usernameString
                     it[password] = BCrypt.withDefaults().hashToString(12, passwordString.toCharArray())
-                    it[role] = roleString
+                }[UserData.id]
+
+                UserRoles.insert { roles ->
+                    roles[userId] = usersId!!
+                    roles[roleId] = RolesData.select { RolesData.role eq roleString }.map { it[RolesData.id] }[0]
                 }
             } catch (_: org.jetbrains.exposed.exceptions.ExposedSQLException) {
                 log.warning("User already exists!")
@@ -77,8 +99,59 @@ class DatabaseController(dbFileLocation: String = "main.db") {
      */
     fun getRole(usernameString: String): Roles {
         return transaction {
-            val role = UserData.select { UserData.username eq usernameString }.map { it[UserData.role] }[0]
-            if (role == "ADMIN") Roles.ADMIN else Roles.USER
+            val userId = UserData.select { UserData.username eq usernameString }.map { it[UserData.id] }[0]
+            val userRoleId = UserRoles.select { UserRoles.userId eq userId }.map { it[UserRoles.roleId] }[0]
+            val userRole = RolesData.select { RolesData.id eq userRoleId }.map { it[RolesData.role] }[0]
+            if (userRole == "ADMIN") Roles.ADMIN else Roles.USER
+        }
+    }
+
+    /**
+     * Adds the uploaded file to the database
+     */
+    fun addFile(fileLocation: String, usernameString: String) {
+        transaction {
+            try {
+                FileLocation.insert {
+                    it[location] = fileLocation
+                    it[username] = usernameString
+                }
+            } catch (_: org.jetbrains.exposed.exceptions.ExposedSQLException) {
+                log.warning("File already exists!")
+            }
+        }
+    }
+
+    /**
+     * Initializes the database
+     */
+    fun initDatabase() {
+        val initialUseRow = transaction { General.selectAll().map { it[General.initialUse] } }
+        if (initialUseRow.isEmpty() || initialUseRow[0] == 1) {
+            transaction {
+                RolesData.insert {
+                    it[role] = "ADMIN"
+                }
+                RolesData.insert {
+                    it[role] = "USER"
+                }
+                RolesData.insert {
+                    it[role] = "GUEST"
+                }
+
+                databaseController.createUser("melvin", "supersecure", "ADMIN")
+
+                UserRoles.insert {
+                    it[userId] = 1
+                    it[roleId] = 1
+                }
+
+                General.insert {
+                    it[initialUse] = 0
+                }
+            }
+        } else {
+            log.info("Already initialized Database.")
         }
     }
 }