diff --git a/src/main/kotlin/net/taehui/twilight/system/DB.kt b/src/main/kotlin/net/taehui/twilight/system/DB.kt index 42d7347..6f3b51a 100644 --- a/src/main/kotlin/net/taehui/twilight/system/DB.kt +++ b/src/main/kotlin/net/taehui/twilight/system/DB.kt @@ -2287,14 +2287,14 @@ SELECT COUNT(Comment_ID) AS Total_Count FROM tw_comment 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(Comment_ID) AS Total_Count FROM tw_comment INNER JOIN tw_note USING(Note_ID) - WHERE MATCH(Artist) AGAINST(? IN BOOLEAN MODE) + WHERE Artist LIKE ? """.trimIndent() 3 -> """ @@ -2308,7 +2308,7 @@ } ).use { dbStatement -> if (dbStatement != null) { - dbStatement.setString(1, want) + dbStatement.setString(1, if (src == 1 || src == 2) "%$want%" else want) dbStatement.executeQuery().use { rows -> if (rows.next()) { totalCount = rows.getInt("Total_Count") @@ -2318,7 +2318,7 @@ } } } - },logFuture { + }, logFuture { pool.connection.use { db -> if (isNotWant) { db.prepareStatement( @@ -2348,14 +2348,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 MATCH(Avatar_Name) AGAINST(? IN BOOLEAN MODE) AND Is_Max = true + WHERE Avatar_Name LIKE ? 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 MATCH(Artist) AGAINST(? IN BOOLEAN MODE) AND Is_Max = true + WHERE Artist LIKE ? AND Is_Max = true """.trimIndent() 3 -> """ @@ -2369,7 +2369,7 @@ } ).use { dbStatement -> if (dbStatement != null) { - dbStatement.setString(1, want) + dbStatement.setString(1, if (src == 1 || src == 2) "%$want%" else want) dbStatement.executeQuery().use { rows -> if (rows.next()) { topCount = rows.getInt("Top_Count") @@ -2379,7 +2379,7 @@ } } } - },logFuture { + }, logFuture { pool.connection.use { db -> if (isNotWant) { db.prepareStatement( @@ -2409,12 +2409,13 @@ 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 MATCH(Artist) AGAINST(? IN BOOLEAN MODE) + WHERE Artist LIKE ? """.trimIndent() 3 -> """ @@ -2427,7 +2428,7 @@ } ).use { dbStatement -> if (dbStatement != null) { - dbStatement.setString(1, want) + dbStatement.setString(1, if (src == 1 || src == 2) "%$want%" else want) dbStatement.executeQuery().use { rows -> if (rows.next()) { lastPage = max(1.0, ceil(rows.getInt("Note_Count").toDouble() / viewUnit)).toInt() @@ -2439,10 +2440,10 @@ } }, logFuture { val dataStatement = """ - DISTINCT tw_note.Note_ID, Note_Variety, Artist, Title, Genre, Level_Text, tw_note.Level, ( - SELECT COUNT(DISTINCT Note_ID, Avatar) + Note_ID, Note_Variety, Artist, Title, Genre, Level_Text, Level, ( + SELECT COUNT(Note_ID) FROM tw_comment - WHERE tw_note.Note_ID = tw_comment.Note_ID + WHERE tw_note.Note_ID = tw_comment.Note_ID AND Is_Max = true ) AS Top_Count, ( SELECT COUNT(Comment_ID) FROM tw_comment @@ -2450,7 +2451,7 @@ ) AS Total_Count, ( SELECT MAX(Date) FROM tw_comment - WHERE tw_note.Note_ID = tw_comment.Note_ID + WHERE tw_note.Note_ID = tw_comment.Note_ID AND Is_Max = true ) AS Date """.trimIndent() val fitStatement = when (fit) { @@ -2489,7 +2490,7 @@ FROM tw_comment 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 ? $fitStatement LIMIT ?, ? """.trimIndent() @@ -2499,7 +2500,7 @@ """ SELECT $dataStatement FROM tw_note - WHERE MATCH(Artist) AGAINST(? IN BOOLEAN MODE) + WHERE Artist LIKE ? $fitStatement LIMIT ?, ? """.trimIndent() @@ -2517,7 +2518,7 @@ else -> null } - dbStatement?.setString(1, want) + dbStatement?.setString(1, if (src == 1 || src == 2) "%$want%" else want) dbStatement?.setInt(2, viewUnit * (page - 1)) dbStatement?.setInt(3, viewUnit) } @@ -2703,10 +2704,10 @@ """ SELECT Avatar_ID, Avatar_Name, Avatar_Intro FROM tn_avatar - WHERE MATCH(Avatar_Name) AGAINST(? IN BOOLEAN MODE) + WHERE Avatar_Name LIKE ? """.trimIndent() ).use { dbStatement -> - dbStatement.setString(1, wantAvatarName) + dbStatement.setString(1, "%$wantAvatarName%") dbStatement.executeQuery().use { rows -> while (rows.next()) { wantAvatars.add(object { @@ -3290,12 +3291,12 @@ INNER JOIN tw_note USING(Note_ID) INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID WHERE Ability_5K > 0.0 AND ${ - getAbilityFilter( - Component.DEFAULT_INPUT_FAVOR_MODE, - Component.MODE_5_INPUT_FAVOR_MODE, - Component.MODE_5_1_INPUT_FAVOR_MODE - ) - } AND Is_Max = true + getAbilityFilter( + Component.DEFAULT_INPUT_FAVOR_MODE, + Component.MODE_5_INPUT_FAVOR_MODE, + Component.MODE_5_1_INPUT_FAVOR_MODE + ) + } AND Is_Max = true ORDER BY Value DESC """.trimIndent() ).use { dbStatement -> @@ -3331,12 +3332,12 @@ INNER JOIN tw_note USING(Note_ID) INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID WHERE Avatar = ? AND Ability_5K > 0.0 AND ${ - getAbilityFilter( - Component.DEFAULT_INPUT_FAVOR_MODE, - Component.MODE_5_INPUT_FAVOR_MODE, - Component.MODE_5_1_INPUT_FAVOR_MODE - ) - } AND Is_Max = true + getAbilityFilter( + Component.DEFAULT_INPUT_FAVOR_MODE, + Component.MODE_5_INPUT_FAVOR_MODE, + Component.MODE_5_1_INPUT_FAVOR_MODE + ) + } AND Is_Max = true ORDER BY Value DESC LIMIT 50 """.trimIndent() @@ -3363,12 +3364,12 @@ INNER JOIN tw_note USING(Note_ID) INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID WHERE Ability_7K > 0.0 AND ${ - getAbilityFilter( - Component.DEFAULT_INPUT_FAVOR_MODE, - Component.MODE_7_INPUT_FAVOR_MODE, - Component.MODE_7_1_INPUT_FAVOR_MODE - ) - } AND Is_Max = true + getAbilityFilter( + Component.DEFAULT_INPUT_FAVOR_MODE, + Component.MODE_7_INPUT_FAVOR_MODE, + Component.MODE_7_1_INPUT_FAVOR_MODE + ) + } AND Is_Max = true ORDER BY Value DESC """.trimIndent() ).use { dbStatement -> @@ -3404,12 +3405,12 @@ INNER JOIN tw_note USING(Note_ID) INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID WHERE Avatar = ? AND Ability_7K > 0.0 AND ${ - getAbilityFilter( - Component.DEFAULT_INPUT_FAVOR_MODE, - Component.MODE_7_INPUT_FAVOR_MODE, - Component.MODE_7_1_INPUT_FAVOR_MODE - ) - } AND Is_Max = true + getAbilityFilter( + Component.DEFAULT_INPUT_FAVOR_MODE, + Component.MODE_7_INPUT_FAVOR_MODE, + Component.MODE_7_1_INPUT_FAVOR_MODE + ) + } AND Is_Max = true ORDER BY Value DESC LIMIT 50 """.trimIndent() @@ -3436,11 +3437,11 @@ INNER JOIN tw_note USING(Note_ID) INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID WHERE Ability_9K > 0.0 AND ${ - getAbilityFilter( - Component.DEFAULT_INPUT_FAVOR_MODE, - Component.MODE_9_INPUT_FAVOR_MODE - ) - } AND Is_Max = true + getAbilityFilter( + Component.DEFAULT_INPUT_FAVOR_MODE, + Component.MODE_9_INPUT_FAVOR_MODE + ) + } AND Is_Max = true ORDER BY Value DESC """.trimIndent() ).use { dbStatement -> @@ -3476,11 +3477,11 @@ INNER JOIN tw_note USING(Note_ID) INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID WHERE Avatar = ? AND Ability_9K > 0.0 AND ${ - getAbilityFilter( - Component.DEFAULT_INPUT_FAVOR_MODE, - Component.MODE_9_INPUT_FAVOR_MODE - ) - } AND Is_Max = true + getAbilityFilter( + Component.DEFAULT_INPUT_FAVOR_MODE, + Component.MODE_9_INPUT_FAVOR_MODE + ) + } AND Is_Max = true ORDER BY Value DESC LIMIT 50 """.trimIndent() @@ -3554,10 +3555,10 @@ FROM tw_comment INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID WHERE "${ - yyyyMMDDFormat.format( - LocalDate.now().withDayOfMonth(1) - ) - }" <= tw_comment.Date + yyyyMMDDFormat.format( + LocalDate.now().withDayOfMonth(1) + ) + }" <= tw_comment.Date GROUP BY Avatar_ID ORDER BY Value DESC LIMIT 50 @@ -3618,10 +3619,10 @@ FROM tw_comment INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID WHERE "${ - yyyyMMDDFormat.format( - LocalDate.now().withDayOfMonth(1) - ) - }" <= tw_comment.Date AND Is_Max = true + yyyyMMDDFormat.format( + LocalDate.now().withDayOfMonth(1) + ) + }" <= tw_comment.Date AND Is_Max = true GROUP BY Avatar_ID ORDER BY Value DESC LIMIT 50 @@ -3682,10 +3683,10 @@ FROM tw_comment INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID WHERE "${ - yyyyMMDDFormat.format( - LocalDate.now().withDayOfMonth(1) - ) - }" <= tw_comment.Date AND Is_Max = true + yyyyMMDDFormat.format( + LocalDate.now().withDayOfMonth(1) + ) + }" <= tw_comment.Date AND Is_Max = true GROUP BY Avatar_ID ORDER BY Value DESC LIMIT 50 @@ -3746,10 +3747,10 @@ FROM tw_comment INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID WHERE "${ - yyyyMMDDFormat.format( - LocalDate.now().withDayOfMonth(1) - ) - }" <= tw_comment.Date AND Is_Max = true + yyyyMMDDFormat.format( + LocalDate.now().withDayOfMonth(1) + ) + }" <= tw_comment.Date AND Is_Max = true GROUP BY Avatar_ID ORDER BY Value DESC LIMIT 50