diff --git a/build.gradle.kts b/build.gradle.kts index 3c6d101..09282a4 100644 --- a/build.gradle.kts +++ b/build.gradle.kts @@ -27,7 +27,7 @@ implementation("com.sun.mail:jakarta.mail:2.0.1") implementation("commons-codec:commons-codec:1.16.1") implementation("commons-io:commons-io:2.15.1") - implementation("io.netty:netty-all:4.1.107.Final") + implementation("io.netty:netty-all:4.1.108.Final") implementation("jakarta.mail:jakarta.mail-api:2.1.3") implementation("net.dv8tion:JDA:5.0.0-beta.21") implementation("org.apache.commons:commons-compress:1.26.1") diff --git a/src/main/kotlin/net/taehui/twilight/system/DB.kt b/src/main/kotlin/net/taehui/twilight/system/DB.kt index d5fb0bb..316bc30 100644 --- a/src/main/kotlin/net/taehui/twilight/system/DB.kt +++ b/src/main/kotlin/net/taehui/twilight/system/DB.kt @@ -29,11 +29,9 @@ object DB : Logger { private fun getAbilityFilter(vararg inputFavorModes: Int): String { - return "(Hit_Points_Mode = ${Component.DEFAULT_HIT_POINTS_MODE} OR Hit_Points_Mode = ${Component.HIGHER_HIT_POINTS_MODE} OR Hit_Points_Mode = ${Component.HIGHEST_HIT_POINTS_MODE} OR Hit_Points_Mode = ${Component.FAILED_HIT_POINTS_MODE}) AND (Judgment_Mode = ${Component.DEFAULT_JUDGMENT_MODE} OR Judgment_Mode = ${Component.HIGHER_JUDGMENT_MODE} OR Judgment_Mode = ${Component.HIGHEST_JUDGMENT_MODE}) AND (${ - inputFavorModes.joinToString( - " OR " - ) { "Input_Favor_Mode = $it" } - }) AND Long_Note_Mode = ${Component.DEFAULT_LONG_NOTE_MODE} AND Note_Modify_Mode = ${Component.DEFAULT_NOTE_MODIFY_MODE}" + return "Judgment_Mode IN (${Component.DEFAULT_JUDGMENT_MODE}, ${Component.HIGHER_JUDGMENT_MODE}, ${Component.HIGHEST_JUDGMENT_MODE}) AND Hit_Points_Mode IN (${Component.DEFAULT_HIT_POINTS_MODE}, ${Component.HIGHER_HIT_POINTS_MODE}, ${Component.HIGHEST_HIT_POINTS_MODE}, ${Component.FAILED_HIT_POINTS_MODE}) AND Long_Note_Mode = ${Component.DEFAULT_LONG_NOTE_MODE} AND Input_Favor_Mode IN (${ + inputFavorModes.joinToString() + }) AND Note_Modify_Mode = ${Component.DEFAULT_NOTE_MODIFY_MODE}" } private val pool = BasicDataSource().apply { @@ -232,7 +230,8 @@ Avatar VARCHAR(20) NOT NULL, Commentary TEXT NOT NULL, PRIMARY KEY (Avatar, Note_ID), - FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE + FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE, + INDEX Note_ID (Note_ID) ) COLLATE=utf8mb4_general_ci ENGINE=InnoDB """.trimIndent() ) @@ -254,7 +253,8 @@ Date DATETIME NOT NULL, Favor BOOLEAN NOT NULL, PRIMARY KEY (Avatar, Note_ID), - FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE + FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE, + INDEX Note_ID (Note_ID) ) COLLATE=utf8mb4_general_ci ENGINE=InnoDB """.trimIndent() ) @@ -327,7 +327,7 @@ """ DELETE FROM tw_favor - WHERE Note_ID = ? AND Avatar = ? + WHERE Avatar = ? AND Note_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, noteID) @@ -384,7 +384,7 @@ SELECT Avatar_Name FROM tn_avatar ORDER BY Last_Date - LIMIT 100 + LIMIT 100enrol """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> @@ -975,7 +975,7 @@ """ SELECT Favor FROM tw_favor - WHERE Note_ID = ? AND Avatar = ? + WHERE Avatar = ? AND Note_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, noteID) @@ -1013,8 +1013,9 @@ db.prepareStatement( """ SELECT tw_comment.Date, Avatar_ID, Avatar_Name, Multiplier, Auto_Mode, Note_Salt_Mode, Audio_Multiplier, Faint_Note_Mode, Judgment_Mode, Hit_Points_Mode, Note_Mobility_Mode, Long_Note_Mode, Input_Favor_Mode, Note_Modify_Mode, Lowest_Judgment_Condition_Mode, Stand, Band, Is_P, Point, Salt, Comment_ID, Is_Paused, Input_Flags - FROM tn_avatar, tw_comment - WHERE tn_avatar.Avatar_ID = tw_comment.Avatar AND Note_ID = ? AND Is_Max = true + FROM tn_avatar + INNER JOIN tw_comment ON tn_avatar.Avatar_ID = tw_comment.Avatar + WHERE Note_ID = ? AND Is_Max = true ORDER BY Stand DESC """.trimIndent() ).use { dbStatement -> @@ -1150,7 +1151,7 @@ """ DELETE FROM tw_commentary - WHERE Note_ID = ? AND Avatar = ? + WHERE Avatar = ? AND Note_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, noteID) @@ -1207,7 +1208,7 @@ """ UPDATE tw_bundle SET Competence = ? - WHERE Name = ? AND Avatar = ? + WHERE Avatar = ? AND Name = ? """.trimIndent() ).use { dbStatement -> dbStatement.setInt(1, qwilightSetBundle.bundleCompetence) @@ -1331,7 +1332,7 @@ """ SELECT Variety FROM tw_bundle - WHERE Name = ? AND Avatar = ? + WHERE Avatar = ? AND Name = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, bundleName) @@ -1349,7 +1350,7 @@ """ DELETE FROM tw_bundle - WHERE Name = ? AND Avatar = ? + WHERE Avatar = ? AND Name = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, bundleName) @@ -1388,8 +1389,9 @@ it.prepareStatement( """ SELECT Avatar_ID, Avatar_Name - FROM tw_ubuntu, tn_avatar - WHERE Avatar = ? AND Ubuntu = Avatar_ID + FROM tw_ubuntu + INNER JOIN tn_avatar ON tw_ubuntu.Ubuntu = tn_avatar.Avatar_ID + WHERE Avatar = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) @@ -1652,7 +1654,7 @@ """ SELECT * FROM tw_bundle - WHERE Name = ? AND Avatar = ? + WHERE Avatar = ? AND Name = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) @@ -1690,7 +1692,7 @@ """ SELECT Competence, Variety, Etc, Name FROM tw_bundle - WHERE Name = ? AND Avatar = ? + WHERE Avatar = ? AND Name = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, bundleName) @@ -1853,7 +1855,7 @@ """ SELECT Ubuntu, Notify_Ubuntu FROM tw_ubuntu LEFT OUTER JOIN tw_avatar - ON Ubuntu = tw_avatar.Avatar + ON tw_ubuntu.Ubuntu = tw_avatar.Avatar WHERE tw_ubuntu.Avatar = ? AND Notify_Ubuntu = 0 """.trimIndent() ).use { dbStatement -> @@ -2260,9 +2262,9 @@ if (isNotWant) { db.prepareStatement( """ - SELECT COUNT(Comment_ID) AS Total_Count, COUNT(DISTINCT tw_note.Note_ID, Avatar) AS Top_Count, COUNT(DISTINCT tw_note.Note_ID) AS Note_Count - FROM tw_note, tw_comment - WHERE tw_note.Note_ID = tw_comment.Note_ID + SELECT COUNT(Comment_ID) AS Total_Count, COUNT(DISTINCT tw_comment.Note_ID, Avatar) AS Top_Count, COUNT(DISTINCT tw_note.Note_ID) AS Note_Count + FROM tw_note + INNER JOIN tw_comment USING(Note_ID) """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> @@ -2277,34 +2279,36 @@ db.prepareStatement( when (src) { 0 -> """ - SELECT COUNT(Comment_ID) AS Total_Count, COUNT(DISTINCT tw_note.Note_ID, Avatar) AS Top_Count, COUNT(DISTINCT tw_note.Note_ID) AS Note_Count - FROM tw_note, tw_comment - WHERE tw_note.Note_ID = tw_comment.Note_ID AND MATCH(Title) AGAINST(? IN BOOLEAN MODE) + SELECT COUNT(Comment_ID) AS Total_Count, COUNT(DISTINCT tw_comment.Note_ID, Avatar) AS Top_Count, COUNT(DISTINCT tw_note.Note_ID) AS Note_Count + INNER JOIN tw_comment USING(Note_ID) + WHERE MATCH(Title) AGAINST(? IN BOOLEAN MODE) """.trimIndent() 1 -> """ - SELECT COUNT(Comment_ID) AS Total_Count, COUNT(DISTINCT tw_note.Note_ID, Avatar) AS Top_Count, COUNT(DISTINCT tw_note.Note_ID) AS Note_Count - FROM tw_note, tw_comment, tn_avatar - WHERE tw_note.Note_ID = tw_comment.Note_ID AND Avatar = Avatar_ID AND MATCH(Avatar_Name) AGAINST(? IN BOOLEAN MODE) + SELECT COUNT(Comment_ID) AS Total_Count, COUNT(DISTINCT tw_comment.Note_ID, Avatar) AS Top_Count, COUNT(DISTINCT tw_note.Note_ID) AS Note_Count + 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) """.trimIndent() 2 -> """ - SELECT COUNT(Comment_ID) AS Total_Count, COUNT(DISTINCT tw_note.Note_ID, Avatar) AS Top_Count, COUNT(DISTINCT tw_note.Note_ID) AS Note_Count - FROM tw_note, tw_comment - WHERE tw_note.Note_ID = tw_comment.Note_ID AND MATCH(Artist) AGAINST(? IN BOOLEAN MODE) + SELECT COUNT(Comment_ID) AS Total_Count, COUNT(DISTINCT tw_comment.Note_ID, Avatar) AS Top_Count, COUNT(DISTINCT tw_note.Note_ID) AS Note_Count + INNER JOIN tw_comment USING(Note_ID) + WHERE MATCH(Artist) AGAINST(? IN BOOLEAN MODE) """.trimIndent() 3 -> """ - SELECT COUNT(Comment_ID) AS Total_Count, COUNT(DISTINCT tw_note.Note_ID, Avatar) AS Top_Count, COUNT(DISTINCT tw_note.Note_ID) AS Note_Count - FROM tw_note, tw_comment - WHERE tw_note.Note_ID = tw_comment.Note_ID AND MATCH(Genre) AGAINST(? IN BOOLEAN MODE) + SELECT COUNT(Comment_ID) AS Total_Count, COUNT(DISTINCT tw_comment.Note_ID, Avatar) AS Top_Count, COUNT(DISTINCT tw_note.Note_ID) AS Note_Count + INNER JOIN tw_comment USING(Note_ID) + WHERE MATCH(Genre) AGAINST(? IN BOOLEAN MODE) """.trimIndent() else -> null } ).use { dbStatement -> if (dbStatement != null) { - dbStatement.setString(1, "+$want*") + dbStatement.setString(1, want) dbStatement.executeQuery().use { rows -> if (rows.next()) { totalCount = rows.getInt("Total_Count") @@ -2356,15 +2360,21 @@ """ SELECT $dataStatement FROM tw_note - WHERE MATCH(Title) AGAINST(? IN BOOLEAN MODE) $fitStatement LIMIT ?, ? + WHERE MATCH(Title) AGAINST(? IN BOOLEAN MODE) + $fitStatement + LIMIT ?, ? """.trimIndent() ) 1 -> db.prepareStatement( """ SELECT $dataStatement - FROM tw_note, tw_comment, tn_avatar - WHERE tw_note.Note_ID = tw_comment.Note_ID AND tw_comment.Avatar = tn_avatar.Avatar_ID AND MATCH(Avatar_Name) AGAINST(? IN BOOLEAN MODE) $fitStatement LIMIT ?, ? + 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) + $fitStatement + LIMIT ?, ? """.trimIndent() ) @@ -2372,7 +2382,9 @@ """ SELECT $dataStatement FROM tw_note - WHERE MATCH(Artist) AGAINST(? IN BOOLEAN MODE) $fitStatement LIMIT ?, ? + WHERE MATCH(Artist) AGAINST(? IN BOOLEAN MODE) + $fitStatement + LIMIT ?, ? """.trimIndent() ) @@ -2380,13 +2392,15 @@ """ SELECT $dataStatement FROM tw_note - WHERE MATCH(Genre) AGAINST(? IN BOOLEAN MODE) $fitStatement LIMIT ?, ? + WHERE MATCH(Genre) AGAINST(? IN BOOLEAN MODE) + $fitStatement + LIMIT ?, ? """.trimIndent() ) else -> null } - dbStatement?.setString(1, "+$want*") + dbStatement?.setString(1, want) dbStatement?.setInt(2, viewUnit * (page - 1)) dbStatement?.setInt(3, viewUnit) } @@ -2496,8 +2510,9 @@ it.prepareStatement( """ SELECT Avatar_ID, Avatar_Name, tw_level.Date - FROM tw_level, tn_avatar - WHERE tw_level.Avatar = tn_avatar.Avatar_ID AND Level_ID = ? + FROM tw_level + INNER JOIN tn_avatar ON tw_level.Avatar = tn_avatar.Avatar_ID + WHERE Level_ID = ? ORDER BY tw_level.Date """.trimIndent() ).use { dbStatement -> @@ -2522,8 +2537,9 @@ it.prepareStatement( """ SELECT SUM(Total_Notes * Stand / 1000000) AS Value - FROM tw_comment, tw_note - WHERE tw_comment.Note_ID = tw_note.Note_ID AND Avatar = ? AND Is_Salt = false + FROM tw_comment + INNER JOIN tw_note USING(Note_ID) + WHERE Avatar = ? AND Is_Salt = false """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) @@ -2573,7 +2589,7 @@ WHERE MATCH(Avatar_Name) AGAINST(? IN BOOLEAN MODE) """.trimIndent() ).use { dbStatement -> - dbStatement.setString(1, "+$wantAvatarName*") + dbStatement.setString(1, wantAvatarName) dbStatement.executeQuery().use { rows -> while (rows.next()) { wantAvatars.add(object { @@ -2623,7 +2639,7 @@ SELECT COUNT(Comment_ID) AS Total_Count, COUNT(DISTINCT Note_ID) AS Top_Count, Avatar_ID, Avatar_Name, Avatar_Intro, Last_Date FROM tw_comment RIGHT OUTER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID - WHERE Avatar_ID = ? + WHERE Avatar = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) @@ -2660,8 +2676,10 @@ db.prepareStatement( """ SELECT SUM(Length) AS Total_Length - FROM tw_note, tw_comment, tn_avatar - WHERE tw_note.Note_ID = tw_comment.Note_ID AND tw_comment.Avatar = tn_avatar.Avatar_ID AND Avatar_ID = ? + FROM tw_comment + INNER JOIN tw_note USING(Note_ID) + INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID + WHERE Avatar = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) @@ -2685,7 +2703,7 @@ """ SELECT COUNT(Comment_ID) AS Count FROM tw_comment - WHERE Date BETWEEN "$yyyyMMDD 00:00:00" AND "$yyyyMMDD 23:59:59" AND Avatar = ? + WHERE Avatar = ? AND Date BETWEEN "$yyyyMMDD 00:00:00" AND "$yyyyMMDD 23:59:59" """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) @@ -2838,8 +2856,9 @@ db.prepareStatement( """ SELECT tw_note.Note_ID, Title, Artist, Genre, Level_Text, Level, COUNT(Comment_ID) AS Count - FROM tw_note, tw_comment - WHERE tw_note.Note_ID = tw_comment.Note_ID AND Avatar = ? AND Input_Mode = ? + FROM tw_comment + INNER JOIN tw_note USING(Note_ID) + WHERE Avatar = ? AND Input_Mode = ? GROUP BY tw_note.Note_ID ORDER BY COUNT DESC LIMIT 50 @@ -2875,8 +2894,9 @@ db.prepareStatement( """ SELECT MAX(Date) AS Date, tw_note.Note_ID, Title, Artist, Genre, Level_Text, Level - FROM tw_note, tw_comment - WHERE tw_note.Note_ID = tw_comment.Note_ID AND Avatar = ? AND Input_Mode = ? + FROM tw_comment + INNER JOIN tw_note USING(Note_ID) + WHERE Avatar = ? AND Input_Mode = ? GROUP BY tw_note.Note_ID ORDER BY Date DESC LIMIT 50 @@ -2973,8 +2993,9 @@ db.prepareStatement( """ SELECT tw_note.Note_ID, Note_ID_128, Note_ID_256, Title, Artist, Genre, Level_Text, Level, Stand - FROM tw_note, tw_comment - WHERE tw_note.Note_ID = tw_comment.Note_ID AND tw_comment.Avatar = ? AND Is_Max = true + FROM tw_note + INNER JOIN tw_comment USING(Note_ID) + WHERE Avatar = ? AND Is_Max = true """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) @@ -3059,8 +3080,9 @@ db.prepareStatement( """ SELECT tw_note.Note_ID, Note_ID_128, Note_ID_256, Title, Artist, Genre, Level_Text, Level, Stand - FROM tw_note, tw_comment - WHERE tw_note.Note_ID = tw_comment.Note_ID AND tw_comment.Avatar = ? AND Is_Max = true + FROM tw_note + INNER JOIN tw_comment USING(Note_ID) + WHERE Avatar = ? AND Is_Max = true """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) @@ -3147,8 +3169,10 @@ it.prepareStatement( """ SELECT tw_note.Note_ID, Note_Variety, Artist, Title, Genre, Level_Text, tw_note.Level, Stand, Avatar_ID, Avatar_Name, Ability_5K * (POWER(Stand / 1000000, 3) - 0.5 * POWER(Stand / 1000000, 2) + 0.5 * (Stand / 1000000)) AS Value - FROM tw_note, tw_comment, tn_avatar - WHERE tw_note.Note_ID = tw_comment.Note_ID AND tw_comment.Avatar = tn_avatar.Avatar_ID AND Ability_5K > 0.0 AND ${ + FROM tw_comment + 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, @@ -3186,8 +3210,10 @@ db.prepareStatement( """ SELECT Ability_5K * (POWER(Stand / 1000000, 3) - 0.5 * POWER(Stand / 1000000, 2) + 0.5 * (Stand / 1000000)) AS Value - FROM tw_note, tw_comment, tn_avatar - WHERE tw_note.Note_ID = tw_comment.Note_ID AND tw_comment.Avatar = tn_avatar.Avatar_ID AND Avatar_ID = ? AND Ability_5K > 0.0 AND ${ + FROM tw_comment + 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, @@ -3216,8 +3242,10 @@ it.prepareStatement( """ SELECT tw_note.Note_ID, Note_Variety, Artist, Title, Genre, Level_Text, tw_note.Level, Stand, Avatar_ID, Avatar_Name, Ability_7K * (POWER(Stand / 1000000, 3) - 0.5 * POWER(Stand / 1000000, 2) + 0.5 * (Stand / 1000000)) AS Value - FROM tw_note, tw_comment, tn_avatar - WHERE tw_note.Note_ID = tw_comment.Note_ID AND tw_comment.Avatar = tn_avatar.Avatar_ID AND Ability_7K > 0.0 AND ${ + FROM tw_comment + 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, @@ -3255,8 +3283,10 @@ db.prepareStatement( """ SELECT Ability_7K * (POWER(Stand / 1000000, 3) - 0.5 * POWER(Stand / 1000000, 2) + 0.5 * (Stand / 1000000)) AS Value - FROM tw_note, tw_comment, tn_avatar - WHERE tw_note.Note_ID = tw_comment.Note_ID AND tw_comment.Avatar = tn_avatar.Avatar_ID AND Avatar_ID = ? AND Ability_7K > 0.0 AND ${ + FROM tw_comment + 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, @@ -3284,9 +3314,10 @@ pool.connection.use { it.prepareStatement( """ - SELECT tw_note.Note_ID, Note_Variety, Artist, Title, Genre, Level_Text, tw_note.Level, Stand, Avatar_ID, Avatar_Name, Ability_9K * (POWER(Stand / 1000000, 3) - 0.5 * POWER(Stand / 1000000, 2) + 0.5 * (Stand / 1000000)) AS Value - FROM tw_note, tw_comment, tn_avatar - WHERE tw_note.Note_ID = tw_comment.Note_ID AND tw_comment.Avatar = tn_avatar.Avatar_ID AND Ability_9K > 0.0 AND ${ + SELECT tw_note.Note_ID, Note_Variety, Artist, Title, Genre, Level_Text, tw_note.Level, Stand, Avatar_ID, Avatar_Name, Ability_9K * (POWER(Stand / 1000000, 3) - 0.5 * POWER(Stand / 1000000, 2) + 0.5 * (Stand / 1000000)) AS Value FROM tw_comment + 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 @@ -3323,8 +3354,10 @@ db.prepareStatement( """ SELECT Ability_9K * (POWER(Stand / 1000000, 3) - 0.5 * POWER(Stand / 1000000, 2) + 0.5 * (Stand / 1000000)) AS Value - FROM tw_note, tw_comment, tn_avatar - WHERE tw_note.Note_ID = tw_comment.Note_ID AND tw_comment.Avatar = tn_avatar.Avatar_ID AND Avatar_ID = ? AND Ability_9K > 0.0 AND ${ + FROM tw_comment + 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 @@ -3371,8 +3404,8 @@ it.prepareStatement( """ SELECT Avatar_ID, Avatar_Name, COUNT(tw_comment.Note_ID) AS Value - FROM tw_comment, tn_avatar - WHERE tw_comment.Avatar = tn_avatar.Avatar_ID + FROM tw_comment + INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID GROUP BY Avatar_ID ORDER BY Value DESC LIMIT 50 @@ -3400,12 +3433,13 @@ it.prepareStatement( """ SELECT Avatar_ID, Avatar_Name, COUNT(tw_comment.Note_ID) AS Value - FROM tw_comment, tn_avatar + 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 tw_comment.Avatar = tn_avatar.Avatar_ID + yyyyMMDDFormat.format( + LocalDate.now().withDayOfMonth(1) + ) + }" <= tw_comment.Date GROUP BY Avatar_ID ORDER BY Value DESC LIMIT 50 @@ -3433,8 +3467,9 @@ it.prepareStatement( """ SELECT Avatar_ID, Avatar_Name, COUNT(DISTINCT tw_comment.Note_ID) AS Value - FROM tw_comment, tn_avatar - WHERE tw_comment.Avatar = tn_avatar.Avatar_ID AND Is_Max = true + FROM tw_comment + INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID + WHERE Is_Max = true GROUP BY Avatar_ID ORDER BY Value DESC LIMIT 50 @@ -3462,12 +3497,13 @@ it.prepareStatement( """ SELECT Avatar_ID, Avatar_Name, COUNT(DISTINCT tw_comment.Note_ID) AS Value - FROM tw_comment, tn_avatar + 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 tw_comment.Avatar = tn_avatar.Avatar_ID 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 @@ -3495,8 +3531,9 @@ it.prepareStatement( """ SELECT Avatar_ID, Avatar_Name, SUM(Stand) AS Value - FROM tw_comment, tn_avatar - WHERE tw_comment.Avatar = tn_avatar.Avatar_ID AND Is_Max = true + FROM tw_comment + INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID + WHERE Is_Max = true GROUP BY Avatar_ID ORDER BY Value DESC LIMIT 50 @@ -3524,12 +3561,13 @@ it.prepareStatement( """ SELECT Avatar_ID, Avatar_Name, SUM(Stand) AS Value - FROM tw_comment, tn_avatar - WHERE "${ - yyyyMMDDFormat.format( - LocalDate.now().withDayOfMonth(1) - ) - }" <= tw_comment.Date AND tw_comment.Avatar = tn_avatar.Avatar_ID AND Is_Max = true + FROM tw_comment + INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID + WHERE Is_Max = true AND "${ + yyyyMMDDFormat.format( + LocalDate.now().withDayOfMonth(1) + ) + }" <= tw_comment.Date GROUP BY Avatar_ID ORDER BY Value DESC LIMIT 50 @@ -3557,8 +3595,9 @@ it.prepareStatement( """ SELECT Avatar_ID, Avatar_Name, MAX(Band) AS Value - FROM tw_comment, tn_avatar - WHERE tw_comment.Avatar = tn_avatar.Avatar_ID AND Is_Max = true + FROM tw_comment + INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID + WHERE Is_Max = true GROUP BY Avatar_ID ORDER BY Value DESC LIMIT 50 @@ -3586,12 +3625,13 @@ it.prepareStatement( """ SELECT Avatar_ID, Avatar_Name, MAX(Band) AS Value - FROM tw_comment, tn_avatar - WHERE "${ - yyyyMMDDFormat.format( - LocalDate.now().withDayOfMonth(1) - ) - }" <= tw_comment.Date AND tw_comment.Avatar = tn_avatar.Avatar_ID AND Is_Max = true + FROM tw_comment + INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID + WHERE Is_Max = true AND "${ + yyyyMMDDFormat.format( + LocalDate.now().withDayOfMonth(1) + ) + }" <= tw_comment.Date GROUP BY Avatar_ID ORDER BY Value DESC LIMIT 50 @@ -3638,8 +3678,10 @@ it.prepareStatement( """ SELECT Avatar_ID, Avatar_Name, 1 + SUM(Total_Notes * Stand / 1000000 / 10000) AS Value - FROM tw_note, tw_comment, tn_avatar - WHERE tw_note.Note_ID = tw_comment.Note_ID AND tw_comment.Avatar = tn_avatar.Avatar_ID AND Is_Salt = false + FROM tw_comment + INNER JOIN tw_note USING(Note_ID) + INNER JOIN tn_avatar ON tw_comment.Avatar = tn_avatar.Avatar_ID + WHERE Is_Salt = false GROUP BY Avatar_ID ORDER BY Value DESC LIMIT 50 @@ -3666,8 +3708,8 @@ val totalDateSet = mutableListOf() val totalDateValues = mutableListOf() - val signUpDateSet = mutableListOf() - val signUpDateValues = mutableListOf() + val enrollDateSet = mutableListOf() + val enrollDateValues = mutableListOf() val avatarDateSet = mutableListOf() val avatarDateValues = mutableListOf() val totalNoteFiles = mutableListOf() @@ -3719,8 +3761,8 @@ ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { - signUpDateSet.add(rows.getString("Etc_Date")) - signUpDateValues.add(rows.getInt("Value")) + enrollDateSet.add(rows.getString("Etc_Date")) + enrollDateValues.add(rows.getInt("Value")) } } } @@ -3750,8 +3792,9 @@ it.prepareStatement( """ SELECT tw_comment.Note_ID, Title, Artist, Genre, Level, Level_Text, COUNT(tw_comment.Note_ID) AS Value - FROM tw_note, tw_comment - WHERE tw_note.Note_ID = tw_comment.Note_ID AND Date BETWEEN "$yyyyMM-01" AND "$yyyyMM-31" + FROM tw_note + INNER JOIN tw_comment USING(Note_ID) + WHERE Date BETWEEN "$yyyyMM-01" AND "$yyyyMM-31" GROUP BY tw_comment.Note_ID ORDER BY Value DESC LIMIT 10; @@ -3829,8 +3872,8 @@ it.prepareStatement( """ SELECT tw_favor.Note_ID, Title, Artist, Genre, Level, Level_Text, SUM(2 * Favor - 1) AS Value - FROM tw_note, tw_favor - WHERE tw_note.Note_ID = tw_favor.Note_ID + FROM tw_note + INNER JOIN tw_favor USING(Note_ID) GROUP BY tw_favor.Note_ID ORDER BY Value DESC LIMIT 10; @@ -3857,8 +3900,9 @@ it.prepareStatement( """ SELECT tw_favor.Note_ID, Title, Artist, Genre, Level, Level_Text, SUM(2 * Favor - 1) AS Value - FROM tw_note, tw_favor - WHERE tw_note.Note_ID = tw_favor.Note_ID AND Date BETWEEN "$yyyyMM-01" AND "$yyyyMM-31" + FROM tw_note + INNER JOIN tw_favor USING(Note_ID) + WHERE Date BETWEEN "$yyyyMM-01" AND "$yyyyMM-31" GROUP BY tw_favor.Note_ID ORDER BY Value DESC LIMIT 10; @@ -3885,8 +3929,8 @@ it.prepareStatement( """ SELECT Input_Mode AS Mode, COUNT(Input_Mode) AS Value - FROM tw_note, tw_comment - WHERE tw_note.Note_ID = tw_comment.Note_ID + FROM tw_note + INNER JOIN tw_comment USING(Note_ID) GROUP BY Mode ORDER BY Value DESC """.trimIndent() @@ -4116,8 +4160,8 @@ object { val totalDateSet = totalDateSet val totalDateValues = totalDateValues - val signUpDateSet = signUpDateSet - val signUpDateValues = signUpDateValues + val enrollDateSet = enrollDateSet + val enrollDateValues = enrollDateValues val avatarDateSet = avatarDateSet val avatarDateValues = avatarDateValues val totalNoteFiles = totalNoteFiles