diff --git a/src/main/kotlin/net/taehui/twilight/system/DB.kt b/src/main/kotlin/net/taehui/twilight/system/DB.kt index 1a153df..8cac414 100644 --- a/src/main/kotlin/net/taehui/twilight/system/DB.kt +++ b/src/main/kotlin/net/taehui/twilight/system/DB.kt @@ -42,7 +42,46 @@ private val yyyyMMFormat = DateTimeFormatter.ofPattern("yyyy-MM").withZone(ZoneId.systemDefault()) private val yyyyMMDDFormat = DateTimeFormatter.ofPattern("yyyy-MM-dd").withZone(ZoneId.systemDefault()) - private fun setBaseDB() { + fun getSiteYells(siteID: String): Collection { + pool.connection.use { + it.prepareStatement( + """ + SELECT Avatar_ID, Avatar_Name, Date, Site_Yell, Platform_ID, Translate + FROM tw_site + WHERE Site_ID = ? + ORDER BY Date + """.trimIndent() + ).use { dbStatement -> + dbStatement.setString(1, siteID) + dbStatement.executeQuery().use { rows -> + val data = LinkedList() + var siteYellID = -1 + while (rows.next()) { + data.add( + SiteYell( + rows.getString("Avatar_ID"), + rows.getString("Avatar_Name"), + rows.getLong("Date"), + rows.getString("Site_Yell"), + ++siteYellID, + rows.getLong("Platform_ID"), + rows.getBoolean("Translate") + ) + ) + } + return data + } + } + } + } + + fun loadDB() { + val db = Configure.db + pool.url = "jdbc:mariadb://" + db.remote + "/" + db.db + pool.username = db.avatar + pool.password = db.auth + logInfo("Loaded DB") + pool.connection.use { it.createStatement().use { dbStatement -> dbStatement.executeUpdate( @@ -233,7 +272,7 @@ Commentary TEXT NOT NULL, PRIMARY KEY (Avatar, Note_ID), FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE, - INDEX Note_ID (Note_ID) + KEY Note_ID ) COLLATE=utf8mb4_general_ci ENGINE=InnoDB """.trimIndent() ) @@ -256,7 +295,7 @@ Favor BOOLEAN NOT NULL, PRIMARY KEY (Avatar, Note_ID), FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE, - INDEX Note_ID (Note_ID) + KEY Note_ID ) COLLATE=utf8mb4_general_ci ENGINE=InnoDB """.trimIndent() ) @@ -264,48 +303,6 @@ } } - fun getSiteYells(siteID: String): Collection { - pool.connection.use { - it.prepareStatement( - """ - SELECT Avatar_ID, Avatar_Name, Date, Site_Yell, Platform_ID, Translate - FROM tw_site - WHERE Site_ID = ? - ORDER BY Date - """.trimIndent() - ).use { dbStatement -> - dbStatement.setString(1, siteID) - dbStatement.executeQuery().use { rows -> - val data = LinkedList() - var siteYellID = -1 - while (rows.next()) { - data.add( - SiteYell( - rows.getString("Avatar_ID"), - rows.getString("Avatar_Name"), - rows.getLong("Date"), - rows.getString("Site_Yell"), - ++siteYellID, - rows.getLong("Platform_ID"), - rows.getBoolean("Translate") - ) - ) - } - return data - } - } - } - } - - fun loadDB() { - val db = Configure.db - pool.url = "jdbc:mariadb://" + db.remote + "/" + db.db - pool.username = db.avatar - pool.password = db.auth - setBaseDB() - logInfo("Loaded DB") - } - fun setFavor(qwilightSetFavor: JSON.QwilightSetFavor, avatarID: String): CompletableFuture { return logFuture { pool.connection.use { @@ -2287,14 +2284,14 @@ SELECT COUNT(Comment_ID) AS Total_Count FROM tw_comment INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID - WHERE Avatar_Name LIKE ? + WHERE MATCH(Avatar_Name) AGAINST(? IN BOOLEAN MODE) """.trimIndent() 2 -> """ SELECT COUNT(Comment_ID) AS Total_Count FROM tw_comment INNER JOIN tw_note USING(Note_ID) - WHERE Artist LIKE ? + WHERE MATCH(Artist) AGAINST(? IN BOOLEAN MODE) """.trimIndent() 3 -> """ @@ -2308,7 +2305,7 @@ } ).use { dbStatement -> if (dbStatement != null) { - dbStatement.setString(1, if (src == 1 || src == 2) "%$want%" else want) + dbStatement.setString(1, "$want*") dbStatement.executeQuery().use { rows -> if (rows.next()) { totalCount = rows.getInt("Total_Count") @@ -2348,14 +2345,14 @@ SELECT COUNT(tw_comment.Note_ID) AS Top_Count FROM tw_comment INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID - WHERE Avatar_Name LIKE ? AND Is_Max = true + WHERE MATCH(Avatar_Name) AGAINST(? IN BOOLEAN MODE) AND Is_Max = true """.trimIndent() 2 -> """ SELECT COUNT(tw_comment.Note_ID) AS Top_Count FROM tw_comment INNER JOIN tw_note USING(Note_ID) - WHERE Artist LIKE ? AND Is_Max = true + WHERE MATCH(Artist) AGAINST(? IN BOOLEAN MODE) AND Is_Max = true """.trimIndent() 3 -> """ @@ -2369,7 +2366,7 @@ } ).use { dbStatement -> if (dbStatement != null) { - dbStatement.setString(1, if (src == 1 || src == 2) "%$want%" else want) + dbStatement.setString(1, "$want*") dbStatement.executeQuery().use { rows -> if (rows.next()) { topCount = rows.getInt("Top_Count") @@ -2409,13 +2406,12 @@ INNER JOIN tw_note USING(Note_ID) INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID WHERE MATCH(Avatar_Name) AGAINST(? IN BOOLEAN MODE) - WHERE Avatar_Name LIKE ? """.trimIndent() 2 -> """ SELECT COUNT(Note_ID) AS Note_Count FROM tw_note - WHERE Artist LIKE ? + WHERE MATCH(Artist) AGAINST(? IN BOOLEAN MODE) """.trimIndent() 3 -> """ @@ -2428,7 +2424,7 @@ } ).use { dbStatement -> if (dbStatement != null) { - dbStatement.setString(1, if (src == 1 || src == 2) "%$want%" else want) + dbStatement.setString(1, "$want*") dbStatement.executeQuery().use { rows -> if (rows.next()) { lastPage = max(1.0, ceil(rows.getInt("Note_Count").toDouble() / viewUnit)).toInt() @@ -2490,7 +2486,7 @@ FROM tw_comment INNER JOIN tw_note USING(Note_ID) INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID - WHERE Avatar_Name LIKE ? + WHERE MATCH(Avatar_Name) AGAINST(? IN BOOLEAN MODE) $fitStatement LIMIT ?, ? """.trimIndent() @@ -2500,7 +2496,7 @@ """ SELECT $dataStatement FROM tw_note - WHERE Artist LIKE ? + WHERE MATCH(Artist) AGAINST(? IN BOOLEAN MODE) $fitStatement LIMIT ?, ? """.trimIndent() @@ -2518,7 +2514,7 @@ else -> null } - dbStatement?.setString(1, if (src == 1 || src == 2) "%$want%" else want) + dbStatement?.setString(1, "$want*") dbStatement?.setInt(2, viewUnit * (page - 1)) dbStatement?.setInt(3, viewUnit) } @@ -2719,10 +2715,10 @@ """ SELECT Avatar_ID, Avatar_Name, Avatar_Intro FROM tn_avatar - WHERE Avatar_Name LIKE ? + WHERE MATCH(Avatar_Name) AGAINST(? IN BOOLEAN MODE) """.trimIndent() ).use { dbStatement -> - dbStatement.setString(1, "%$want%") + dbStatement.setString(1, "$want*") dbStatement.executeQuery().use { rows -> while (rows.next()) { wantAvatars.add(object {