package net.taehui.twilight.system import CommentOuterClass import com.fasterxml.jackson.annotation.JsonIgnore import net.taehui.twilight.* import net.taehui.twilight.BundleIO.BundleVariety import net.taehui.twilight.Component import net.taehui.twilight.qwilight.QwilightAvatar import org.apache.commons.compress.compressors.xz.XZCompressorInputStream import org.apache.commons.dbcp2.BasicDataSource import org.apache.commons.io.FileUtils import org.apache.commons.io.FilenameUtils import java.io.IOException import java.lang.IllegalArgumentException import java.nio.file.Files import java.nio.file.Path import java.sql.* import java.time.Instant import java.time.LocalDate import java.time.ZoneId import java.time.format.DateTimeFormatter import java.util.* import java.util.concurrent.CompletableFuture import java.util.concurrent.atomic.AtomicInteger import java.util.stream.Stream import java.util.stream.StreamSupport import kotlin.collections.ArrayList import kotlin.math.ceil import kotlin.math.max import kotlin.math.pow object DB : Logger { private fun getAbilityFilter(vararg inputFavorModes: Int): String { 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 { maxTotal = Int.MAX_VALUE } private val yyyyMMFormat = DateTimeFormatter.ofPattern("yyyy-MM").withZone(ZoneId.systemDefault()) private val yyyyMMDDFormat = DateTimeFormatter.ofPattern("yyyy-MM-dd").withZone(ZoneId.systemDefault()) fun getSiteYells(siteID: String): Collection<SiteYell> { 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<SiteYell>() 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( """ CREATE TABLE IF NOT EXISTS tw_note ( Note_ID VARCHAR(139) NOT NULL, Note_ID_128 CHAR(32) NOT NULL, Note_ID_256 CHAR(64) NOT NULL, Note_Variety INTEGER NOT NULL, Artist TEXT NOT NULL, Title TEXT NOT NULL, Genre TEXT NOT NULL, Level_Text TEXT NOT NULL, Level INTEGER NOT NULL, Input_Mode INTEGER NOT NULL, Total_Notes INTEGER NOT NULL, Is_Salt BOOLEAN NOT NULL, Ability_5K REAL NOT NULL, Ability_7K REAL NOT NULL, Ability_9K REAL NOT NULL, Length REAL NOT NULL, PRIMARY KEY (Note_ID), KEY (Note_Variety), FULLTEXT (Artist), FULLTEXT (Title), FULLTEXT (Genre), KEY (Is_Salt), KEY (Ability_5K), KEY (Ability_7K), KEY (Ability_9K) ) COLLATE=utf8mb4_general_ci ENGINE=InnoDB """.trimIndent() ) dbStatement.executeUpdate( """ CREATE TABLE IF NOT EXISTS tw_comment ( Date DATETIME NOT NULL, Note_ID VARCHAR(139) NOT NULL, Avatar VARCHAR(20) NOT NULL, Multiplier REAL, Auto_Mode INTEGER, Note_Salt_Mode INTEGER, Audio_Multiplier REAL, Faint_Note_Mode INTEGER, Judgment_Mode INTEGER, Hit_Points_Mode INTEGER, Note_Mobility_Mode INTEGER, Long_Note_Mode INTEGER, Input_Favor_Mode INTEGER, Note_Modify_Mode INTEGER, Lowest_Judgment_Condition_Mode INTEGER, Stand INTEGER, Band INTEGER, Is_Band1 BOOLEAN, Point REAL, Salt INTEGER, Comment_ID CHAR(128) NOT NULL, Is_Max BOOLEAN, Is_Paused BOOLEAN, Input_Flags INTEGER NOT NULL, Lowest_Audio_Multiplier REAL, Highest_Audio_Multiplier REAL, FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE, KEY (Date), KEY (Note_ID), KEY (Avatar), KEY (Judgment_Mode), KEY (Hit_Points_Mode), KEY (Input_Favor_Mode), KEY (Stand), KEY (Comment_ID), KEY (Is_Max), CHECK (Input_Flags >= 0 AND Input_Flags <= 15) ) COLLATE=utf8mb4_general_ci ENGINE=InnoDB """.trimIndent() ) dbStatement.executeUpdate( """ CREATE TABLE IF NOT EXISTS tw_handled ( Avatar VARCHAR(20) NOT NULL, Note_ID VARCHAR(139) NOT NULL, Handled INTEGER NOT NULL, PRIMARY KEY (Avatar, Note_ID), FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE ) COLLATE=utf8mb4_general_ci ENGINE=InnoDB """.trimIndent() ) dbStatement.executeUpdate( """ CREATE TABLE IF NOT EXISTS tw_bundle ( Avatar VARCHAR(20) NOT NULL, Date DATETIME NOT NULL, Name VARCHAR(191) NOT NULL, Length LONG NOT NULL, Competence INTEGER NOT NULL, Variety INTEGER NOT NULL, Etc TEXT NOT NULL, PRIMARY KEY (Avatar, Name), FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE, CHECK (Competence IN (0, 1, 2)), CHECK (Variety IN (0, 1, 2)) ) COLLATE=utf8mb4_general_ci ENGINE=InnoDB """.trimIndent() ) dbStatement.executeUpdate( """ CREATE TABLE IF NOT EXISTS tw_ubuntu ( Avatar VARCHAR(20) NOT NULL, Ubuntu VARCHAR(20) NOT NULL, PRIMARY KEY (Avatar, Ubuntu), FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE, FOREIGN KEY (Ubuntu) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE ) COLLATE=utf8mb4_general_ci ENGINE=InnoDB """.trimIndent() ) dbStatement.executeUpdate( """ CREATE TABLE IF NOT EXISTS tw_avatar ( Avatar VARCHAR(20) NOT NULL, Silent_Site INTEGER, Notify_Ubuntu INTEGER, Default_Bundle_Competence INTEGER, IO_Competence INTEGER, Notify_Save_Bundle INTEGER, PRIMARY KEY (Avatar), FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE, KEY (Notify_Ubuntu), CHECK (Silent_Site IN (0, 1, 2, 3)), CHECK (Notify_Ubuntu IN (0, 1)), CHECK (Default_Bundle_Competence IN (0, 1, 2, 3)), CHECK (IO_Competence IN (0, 1, 2, 3)), CHECK (Notify_Save_Bundle IN (0, 1, 2, 3)) ) COLLATE=utf8mb4_general_ci ENGINE=InnoDB """.trimIndent() ) dbStatement.executeUpdate( """ CREATE TABLE IF NOT EXISTS tw_title ( Avatar VARCHAR(20) NOT NULL, Title_ID CHAR(36) NOT NULL, PRIMARY KEY (Avatar), FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE ) COLLATE=utf8mb4_general_ci ENGINE=InnoDB """.trimIndent() ) dbStatement.executeUpdate( """ CREATE TABLE IF NOT EXISTS tw_edge ( Avatar VARCHAR(20) NOT NULL, Edge_ID CHAR(36) NOT NULL, PRIMARY KEY (Avatar), FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE, KEY (Edge_ID) ) COLLATE=utf8mb4_general_ci ENGINE=InnoDB """.trimIndent() ) dbStatement.executeUpdate( """ CREATE TABLE IF NOT EXISTS tw_site ( Site_ID CHAR(36) NOT NULL, Avatar_ID VARCHAR(20) NOT NULL, Avatar_Name VARCHAR(255) NOT NULL, Date LONG NOT NULL, Site_Yell TEXT NOT NULL, Platform_ID LONG NOT NULL, Translate BOOLEAN NOT NULL, KEY (Site_ID), KEY (Platform_ID) ) COLLATE=utf8mb4_general_ci ENGINE=InnoDB """.trimIndent() ) dbStatement.executeUpdate( """ CREATE TABLE IF NOT EXISTS tw_level ( Avatar VARCHAR(20) NOT NULL, Level_ID CHAR(36) NOT NULL, Date DATETIME NOT NULL, PRIMARY KEY (Avatar, Level_ID), FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE ) COLLATE=utf8mb4_general_ci ENGINE=InnoDB """.trimIndent() ) dbStatement.executeUpdate( """ CREATE TABLE IF NOT EXISTS tw_commentary ( Note_ID VARCHAR(139) NOT NULL, 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, KEY (Note_ID) ) COLLATE=utf8mb4_general_ci ENGINE=InnoDB """.trimIndent() ) dbStatement.executeUpdate( """ CREATE TABLE IF NOT EXISTS tw_translated ( Text VARCHAR(766) NOT NULL, Target_Language VARCHAR(5) NOT NULL, Translated_Text TEXT NOT NULL, PRIMARY KEY (Text, Target_Language) ) COLLATE=utf8mb4_general_ci ENGINE=InnoDB """.trimIndent() ) dbStatement.executeUpdate( """ CREATE TABLE IF NOT EXISTS tw_favor ( Note_ID VARCHAR(139) NOT NULL, Avatar VARCHAR(20) NOT NULL, 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, KEY (Note_ID) ) COLLATE=utf8mb4_general_ci ENGINE=InnoDB """.trimIndent() ) } } } fun setFavor(qwilightSetFavor: JSON.QwilightSetFavor, avatarID: String): CompletableFuture<Void> { return logFuture { pool.connection.use { val noteID = qwilightSetFavor.noteID val favor = qwilightSetFavor.favor if (favor != null) { it.prepareStatement( """ REPLACE INTO tw_favor VALUES(?, ?, ?, ?) """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, noteID) dbStatement.setString(2, avatarID) dbStatement.setTimestamp(3, Timestamp(System.currentTimeMillis())) dbStatement.setBoolean(4, favor) dbStatement.execute() } } else { it.prepareStatement( """ DELETE FROM tw_favor WHERE Avatar = ? AND Note_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, noteID) dbStatement.setString(2, avatarID) dbStatement.execute() } } } } } fun loadTranslatedText(text: String, targetLanguage: String): CompletableFuture<String> { return logValueFuture { pool.connection.use { it.prepareStatement( """ SELECT Translated_Text FROM tw_translated WHERE Text = ? AND Target_Language = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, text) dbStatement.setString(2, targetLanguage) dbStatement.executeQuery().use { rows -> if (rows.next()) rows.getString("Translated_Text") else "" } } } } } fun saveTranslatedText(text: String, targetLanguage: String, translatedText: String): CompletableFuture<Void> { return logFuture { pool.connection.use { it.prepareStatement( """ REPLACE INTO tw_translated VALUES(?, ?, ?) """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, text) dbStatement.setString(2, targetLanguage) dbStatement.setString(3, translatedText) dbStatement.execute() } } } } fun getQwilightNames(): List<String> { pool.connection.use { it.prepareStatement( """ SELECT Avatar_Name FROM tn_avatar ORDER BY Last_Date LIMIT 100 """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> val data = mutableListOf<String>() while (rows.next()) { data.add(rows.getString("Avatar_Name")) } return data } } } } fun wipeNote(noteID: String, logger: Logger): CompletableFuture<Void> { return logFuture { pool.connection.use { it.prepareStatement( """ DELETE FROM tw_note WHERE Note_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, noteID) logger.logInfo("Wiped ${dbStatement.executeUpdate()} Notes") } it.prepareStatement( """ SELECT Note_ID FROM tw_note WHERE Note_ID LIKE ? """.trimIndent() ).use { dbStatement -> val noteID512 = Utility.getNoteID512(noteID) dbStatement.setString(1, "$noteID512:%") dbStatement.executeQuery().use { rows -> if (!rows.next() && Files.deleteIfExists(TwilightComponent.NOTE_ENTRY_PATH.resolve(noteID512))) { logger.logInfo("Wiped Note File") } else { logger.logInfo("Wiped 0 Note Files") } } } it.prepareStatement( """ DELETE FROM tw_comment WHERE Note_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, noteID) logger.logInfo("Wiped ${dbStatement.executeUpdate()} Comments") } it.prepareStatement( """ SELECT Comment_ID FROM tw_comment WHERE Note_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, noteID) dbStatement.executeQuery().use { rows -> var wipedCount = 0 while (rows.next()) { if (Files.deleteIfExists( TwilightComponent.COMMENT_ENTRY_PATH.resolve(Utility.getNoteID512(noteID)) .resolve("${rows.getString("Comment_ID")}.xz") ) ) { ++wipedCount } } logger.logInfo("Wiped $wipedCount Comment Files") } } } } } fun wipeNotes(): CompletableFuture<Void> { return logFuture { pool.connection.use { it.prepareStatement( """ DELETE FROM tw_note """.trimIndent() ).use { dbStatement -> dbStatement.execute() } } } } fun getWipeNotes(): CompletableFuture<Collection<String>> { return logValueFuture { pool.connection.use { it.prepareStatement( """ SELECT tw_note.Note_ID FROM tw_comment LEFT OUTER JOIN tw_note USING (Note_ID) WHERE tw_comment.Note_ID IS NULL """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> val data = mutableSetOf<String>() while (rows.next()) { val noteID = rows.getString("Note_ID") if (!LevelSystem.levelNoteIDs.contains(noteID)) { data.add(noteID) } } data } } } } } fun pmsNote(noteID: String, logger: Logger): CompletableFuture<Void> { return logFuture { pool.connection.use { it.autoCommit = false try { val pmsNoteID = "${Utility.getNoteID512(noteID)}:1" it.prepareStatement( """ DELETE FROM tw_note WHERE Note_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, noteID) if (dbStatement.executeUpdate() > 0) { logger.logInfo("Wiped Note") } } it.prepareStatement( """ UPDATE tw_comment SET Note_ID = ? WHERE Note_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, pmsNoteID) dbStatement.setString(2, noteID) logger.logInfo("PMSed ${dbStatement.executeUpdate()} Comments") } it.commit() } catch (e: Throwable) { it.rollback() } } } } fun getPMSNotes(): CompletableFuture<Collection<String>> { return logValueFuture { pool.connection.use { it.prepareStatement( """ SELECT Note_ID, Title FROM tw_note WHERE Input_Mode = 12 """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> val data = mutableSetOf<String>() while (rows.next()) { val title = rows.getString("Title") if (title.contains("9B", true) || title.contains("9K", true) || title.contains( "PMS", true ) ) { data.add(rows.getString("Note_ID")) } } data } } } } } fun setCommentMax(noteID: String): CompletableFuture<Void> { data class Comment( val stand: Int, val date: Timestamp, val commentID: String ) return logFuture { pool.connection.use { it.autoCommit = false try { it.prepareStatement( """ UPDATE tw_comment SET Is_Max = false WHERE Note_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, noteID) dbStatement.executeUpdate() } val comments = mutableMapOf<String, Comment>() it.prepareStatement( """ SELECT Avatar, Stand, Date, Comment_ID FROM tw_comment WHERE Note_ID = ? AND Avatar > "" """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, noteID) dbStatement.executeQuery().use { rows -> while (rows.next()) { val avatar = rows.getString("Avatar") val stand = rows.getInt("Stand") val date = rows.getTimestamp("Date") val commentID = rows.getString("Comment_ID") val comment = comments.getOrPut(avatar) { Comment(stand, date, commentID) } if (stand > comment.stand || (stand == comment.stand && date > comment.date)) { comments[avatar] = Comment(stand, date, commentID) } } } } comments.values.forEach { comment -> it.prepareStatement( """ UPDATE tw_comment SET Is_Max = true WHERE Comment_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, comment.commentID) dbStatement.executeUpdate() } } it.commit() } catch (e: Throwable) { it.rollback() } } } } fun getWipeComments(): CompletableFuture<Collection<Path>> { return logValueFuture { val commentIDs = mutableSetOf<String>() pool.connection.use { it.prepareStatement( """ SELECT Comment_ID FROM tw_comment WHERE Is_Max = true """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { commentIDs.add(rows.getString("Comment_ID")) } } } } Files.newDirectoryStream(TwilightComponent.COMMENT_ENTRY_PATH).use { commentEntryPaths -> StreamSupport.stream(commentEntryPaths.spliterator(), true).use { parallel -> parallel.map { commentEntryPath -> Files.list(commentEntryPath).use { commentFilePaths -> commentFilePaths.map { commentFilePath -> if (commentIDs.contains(FilenameUtils.removeExtension(commentFilePath.fileName.toString()))) { null } else { commentFilePath } }.toList() } }.toList().flatten().filterNotNull() } } } } fun wipeComments(commentFilePaths: Collection<Path>): CompletableFuture<Int> { return logValueFuture { commentFilePaths.count { try { Files.deleteIfExists(it) } catch (e: IOException) { logFault(e) false } } } } fun learnComments(futureLearnCommentsStatus: AtomicInteger): CompletableFuture<Void> { return logFuture { pool.connection.use { Files.newDirectoryStream(TwilightComponent.COMMENT_ENTRY_PATH).use { commentEntryPaths -> StreamSupport.stream(commentEntryPaths.spliterator(), true).use { parallel -> parallel.forEach { commentEntryPath -> Files.list(commentEntryPath).use { commentFilePaths -> commentFilePaths.forEach { commentFilePath -> try { val commentID = FilenameUtils.removeExtension(commentFilePath.fileName.toString()) it.prepareStatement( """ SELECT Audio_Multiplier FROM tw_comment WHERE Comment_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, commentID) dbStatement.executeQuery().use { rows -> if (rows.next()) { val audioMultiplier = rows.getDouble("Audio_Multiplier") val comment = XZCompressorInputStream( Files.readAllBytes(commentFilePath).inputStream() ).use { CommentOuterClass.Comment.parseFrom(it) } if (comment.audioMultipliersCount > 0) { it.prepareStatement( """ UPDATE tw_comment SET Lowest_Audio_Multiplier = ?, Highest_Audio_Multiplier = ? WHERE Comment_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setDouble( 1, comment.audioMultipliersList.map { it.audioMultiplier } .plus(audioMultiplier).min() ) dbStatement.setDouble( 2, comment.audioMultipliersList.map { it.audioMultiplier } .plus(audioMultiplier).max() ) dbStatement.setString(3, commentID) dbStatement.executeUpdate() } } else { it.prepareStatement( """ UPDATE tw_comment SET Lowest_Audio_Multiplier = Audio_Multiplier, Highest_Audio_Multiplier = Audio_Multiplier WHERE Comment_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, commentID) dbStatement.executeUpdate() } } } } } } catch (e: Throwable) { logFault(e) } futureLearnCommentsStatus.incrementAndGet() } } } } } } } } fun wipeHandled(): CompletableFuture<Void> { return logFuture { pool.connection.use { it.prepareStatement( """ DELETE FROM tw_handled WHERE Handled != ${Component.Handled.F} """.trimIndent() ).use { dbStatement -> dbStatement.execute() } } } } fun learnHandled(futureLearnHandledStatus: AtomicInteger) { data class Comment( val avatar: String, val noteID: String, val hitPointsMode: Int, val isBand1: Boolean, val isYell1: Boolean, val isDefaultHandled: Boolean ) val comments = mutableListOf<Comment>() pool.connection.use { it.prepareStatement( """ SELECT Auto_Mode, Judgment_Mode, Hit_Points_Mode, Long_Note_Mode, Input_Favor_Mode, Note_Modify_Mode, Lowest_Audio_Multiplier, Avatar, Note_ID, Hit_Points_Mode, Is_Band1, Point FROM tw_comment WHERE Avatar > "" """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { comments.add( Comment( rows.getString("Avatar"), rows.getString("Note_ID"), rows.getInt("Hit_Points_Mode"), rows.getBoolean("Is_Band1"), rows.getDouble("Point") == 1.0, Utility.isStandardHandled( rows.getInt("Auto_Mode"), rows.getInt("Judgment_Mode"), rows.getInt("Hit_Points_Mode"), rows.getInt("Long_Note_Mode"), rows.getInt("Input_Favor_Mode"), rows.getInt("Note_Modify_Mode"), rows.getDouble("Lowest_Audio_Multiplier") ) ) ) } } } } StreamSupport.stream( comments.groupBy { comment -> Pair(comment.avatar, comment.noteID) }.toList().spliterator(), true ).use { parallel -> parallel.forEach { (avatarNoteID, comments) -> val defaultHandledComments = comments.filter { it.isDefaultHandled } if (defaultHandledComments.isEmpty()) { pool.connection.use { it.prepareStatement( """ REPLACE INTO tw_handled VALUES(?, ?, ?) """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarNoteID.first) dbStatement.setString(2, avatarNoteID.second) dbStatement.setInt(3, Component.Handled.ASSIST_CLEAR.value) dbStatement.execute() } } } else { pool.connection.use { it.prepareStatement( """ REPLACE INTO tw_handled VALUES(?, ?, ?) """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarNoteID.first) dbStatement.setString(2, avatarNoteID.second) dbStatement.setInt( 3, (if (defaultHandledComments.any { comment -> comment.isYell1 }) Component.Handled.YELL1 else if (defaultHandledComments.any { comment -> comment.isBand1 }) Component.Handled.BAND1 else { when (defaultHandledComments.maxBy { comment -> when (comment.hitPointsMode) { Component.HIGHEST_HIT_POINTS_MODE, Component.FAILED_HIT_POINTS_MODE -> 2 Component.HIGHER_HIT_POINTS_MODE -> 1 Component.DEFAULT_HIT_POINTS_MODE -> 0 else -> throw IllegalArgumentException(comment.hitPointsMode.toString()) } }.hitPointsMode) { Component.HIGHEST_HIT_POINTS_MODE, Component.FAILED_HIT_POINTS_MODE -> Component.Handled.HIGHEST_CLEAR Component.HIGHER_HIT_POINTS_MODE -> Component.Handled.HIGHER_CLEAR Component.DEFAULT_HIT_POINTS_MODE -> Component.Handled.CLEAR else -> throw NotImplementedError() } }).value ) dbStatement.execute() } } } futureLearnHandledStatus.incrementAndGet() } } } fun getWipeBundles(): CompletableFuture<Collection<Path>> { return logValueFuture { val bundleIDs = mutableSetOf<Pair<String, String>>() pool.connection.use { it.prepareStatement( """ SELECT Avatar, Name FROM tw_bundle """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { bundleIDs.add(Pair(rows.getString("Avatar"), rows.getString("Name"))) } } } } Files.newDirectoryStream(TwilightComponent.BUNDLE_ENTRY_PATH).use { bundleEntryPaths -> StreamSupport.stream(bundleEntryPaths.spliterator(), true) .use { parallel -> parallel.map { bundleEntryPath -> Files.list(bundleEntryPath).use { bundleFilePaths -> bundleFilePaths.map { bundleFilePath -> if (bundleIDs.contains( Pair( bundleEntryPath.fileName.toString(), FilenameUtils.removeExtension(bundleFilePath.fileName.toString()) ) ) ) { null } else { bundleFilePath } }.toList() } }.toList().flatten().filterNotNull() } } } } fun wipeBundles(bundleFilePaths: Collection<Path>): CompletableFuture<Int> { return logValueFuture { bundleFilePaths.count { try { Files.deleteIfExists(it) } catch (e: IOException) { logFault(e) false } } } } interface ICommentary { val avatarID: String var commentary: String } fun getComment( noteID: String, avatarID: String, language: String, viewUnit: Int, isUbuntu: Boolean, ubuntuID: String, logger: Logger ): CompletableFuture<Any> { val comments = mutableListOf<ICommentary>() return logValueFuture { val commentaryMap = mutableMapOf<String, String>() pool.connection.use { db -> db.prepareStatement( """ SELECT Avatar, Commentary FROM tw_commentary WHERE Note_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, noteID) dbStatement.executeQuery().use { rows -> while (rows.next()) { commentaryMap[rows.getString("Avatar")] = rows.getString("Commentary") } } } } val favor = if (avatarID.isEmpty()) null else pool.connection.use { it.prepareStatement( """ SELECT Favor FROM tw_favor WHERE Avatar = ? AND Note_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, noteID) dbStatement.setString(2, avatarID) dbStatement.executeQuery().use { rows -> if (rows.next()) rows.getBoolean("Favor") else null } } } val ubuntuIDs = if (avatarID.isEmpty() || !isUbuntu) null else (if (ubuntuID.isEmpty()) getUbuntuIDs(avatarID).join() .map { it[0] } else listOf(ubuntuID)) .toMutableList().apply { add(avatarID) } var totalFavor = 0 pool.connection.use { it.prepareStatement( """ SELECT Favor FROM tw_favor WHERE Note_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, noteID) dbStatement.executeQuery().use { rows -> while (rows.next()) { totalFavor += if (rows.getBoolean("Favor")) 1 else -1 } } } } totalFavor = 0.coerceAtLeast(totalFavor) pool.connection.use { db -> 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_Band1, Point, Salt, Comment_ID, Is_Paused, Input_Flags 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 -> dbStatement.setString(1, noteID) dbStatement.executeQuery().use { rows -> while (rows.next()) { val rowAvatarID = rows.getString("Avatar_ID") if (ubuntuIDs?.contains(rowAvatarID) != false) { comments.add(object : ICommentary { override val avatarID = rowAvatarID override var commentary = commentaryMap.getOrDefault(this.avatarID, "") val date = rows.getTimestamp("Date") val avatarName = rows.getString("Avatar_Name") val multiplier = rows.getDouble("Multiplier") val autoMode = rows.getInt("Auto_Mode") val noteSaltMode = rows.getInt("Note_Salt_Mode") val audioMultiplier = rows.getDouble("Audio_Multiplier") val faintNoteMode = rows.getInt("Faint_Note_Mode") val judgmentMode = rows.getInt("Judgment_Mode") val hitPointsMode = rows.getInt("Hit_Points_Mode") val noteMobilityMode = rows.getInt("Note_Mobility_Mode") val longNoteMode = rows.getInt("Long_Note_Mode") val inputFavorMode = rows.getInt("Input_Favor_Mode") val noteModifyMode = rows.getInt("Note_Modify_Mode") val lowestJudgmentConditionMode = rows.getInt("Lowest_Judgment_Condition_Mode") val stand = rows.getInt("Stand") val band = rows.getInt("Band") val handled = getHandled(rowAvatarID, noteID) @JvmField val isBand1 = rows.getBoolean("Is_Band1") val point = rows.getDouble("Point") val salt = rows.getInt("Salt") val commentID = rows.getString("Comment_ID") ?: "" @JvmField val isPaused = rows.getBoolean("Is_Paused") val inputFlags = rows.getInt("Input_Flags") }) } } var commentPlace = -1 val commentsMe = mutableListOf<ICommentary>() comments.forEachIndexed { i, comment -> val isMe = comment.avatarID == avatarID if (viewUnit == -1 || commentsMe.size < viewUnit || isMe) { commentsMe.add(comment) if (isMe) { commentPlace = i } } } CompletableFuture.allOf(*commentsMe.filter { it.commentary.isNotEmpty() }.map { Translator.translate( language, it.commentary, logger ).thenApply { translatedText -> it.commentary = translatedText } }.toTypedArray()).join() object { val favor = favor val totalFavor = totalFavor val comments = commentsMe val commentPlace = commentPlace val totalComments = comments.size } } } } } } fun setAvatarTitle(avatarID: String, titleID: String): CompletableFuture<Void> { return logFuture { pool.connection.use { db -> if (titleID.isEmpty()) { db.prepareStatement( """ DELETE FROM tw_title WHERE Avatar = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.execute() } } else { if (TitleSystem.getTitleIDs( getLevelIDs(avatarID), getNoteIDs(avatarID), getAvatarLevels(avatarID)[0] ).stream().anyMatch { it == titleID } ) { db.prepareStatement( """ REPLACE INTO tw_title VALUES(?, ?) """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.setString(2, titleID) dbStatement.execute() } } } } } } fun setCommentary(noteID: String, avatarID: String, commentary: String): CompletableFuture<Boolean> { return logValueFuture { pool.connection.use { val hasComment: Boolean it.prepareStatement( """ SELECT Comment_ID FROM tw_comment WHERE Note_ID = ? AND Avatar = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, noteID) dbStatement.setString(2, avatarID) dbStatement.executeQuery().use { rows -> hasComment = rows.next() } } if (commentary.isBlank()) { if (hasComment) { it.prepareStatement( """ DELETE FROM tw_commentary WHERE Avatar = ? AND Note_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, noteID) dbStatement.setString(2, avatarID) dbStatement.execute() } } true } else { if (hasComment) { it.prepareStatement( """ REPLACE INTO tw_commentary VALUES(?, ?, ?) """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, noteID) dbStatement.setString(2, avatarID) dbStatement.setString(3, commentary) dbStatement.execute() } } hasComment } } } } fun getNotes(): CompletableFuture<Collection<String>> { return logValueFuture { pool.connection.use { it.prepareStatement( """ SELECT Note_ID FROM tw_note """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> val data = mutableListOf<String>() while (rows.next()) { data.add(rows.getString("Note_ID")) } data } } } } } fun setBundle(qwilightSetBundle: JSON.QwilightSetBundle, avatarID: String): CompletableFuture<Void> { return logFuture { pool.connection.use { it.prepareStatement( """ UPDATE tw_bundle SET Competence = ? WHERE Avatar = ? AND Name = ? """.trimIndent() ).use { dbStatement -> dbStatement.setInt(1, qwilightSetBundle.bundleCompetence) dbStatement.setString(2, qwilightSetBundle.bundleName) dbStatement.setString(3, avatarID) dbStatement.execute() } } } } fun getBundles(avatarID: String): CompletableFuture<Pair<Collection<Array<Any>>, Long>?> { return logValueFuture { pool.connection.use { val data = mutableListOf<Array<Any>>() it.prepareStatement( """ SELECT * FROM tw_bundle WHERE Avatar = ? ORDER BY Date DESC """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery().use { rows -> while (rows.next()) { data.add( arrayOf( rows.getTimestamp("Date"), rows.getString("Name"), rows.getLong("Length"), rows.getInt("Competence"), rows.getInt("Variety") ) ) } } } it.prepareStatement( """ SELECT SUM(Length) AS Length FROM tw_bundle WHERE Avatar = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery().use { rows -> if (rows.next()) { Pair(data, rows.getLong("Length")) } else { null } } } } } } fun setConfigure(qwilightSetConfigure: JSON.QwilightSetConfigure, avatarID: String): CompletableFuture<Void> { return logFuture { pool.connection.use { it.prepareStatement( """ REPLACE INTO tw_avatar VALUES(?, ?, ?, ?, ?, ?) """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.setInt(2, qwilightSetConfigure.silentSiteCompetence) dbStatement.setInt(3, qwilightSetConfigure.toNotifyUbuntuCompetence) dbStatement.setInt(4, qwilightSetConfigure.defaultBundleCompetence) dbStatement.setInt(5, qwilightSetConfigure.ioCompetence) dbStatement.setInt(6, qwilightSetConfigure.toNotifySaveBundleCompetence) dbStatement.execute() } } } } fun setLastDate(avatarID: String): CompletableFuture<Void> { return logFuture { pool.connection.use { it.prepareStatement( """ UPDATE tn_avatar SET Last_Date = ? WHERE Avatar_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setTimestamp(1, Timestamp(System.currentTimeMillis())) dbStatement.setString(2, avatarID) dbStatement.execute() } } } } fun wipeUbuntu(avatarID: String, ubuntuID: String): CompletableFuture<Void> { return logFuture { pool.connection.use { it.prepareStatement( """ DELETE FROM tw_ubuntu WHERE Avatar = ? AND Ubuntu = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.setString(2, ubuntuID) dbStatement.execute() } } } } fun wipeBundle(avatarID: String, bundleName: String): CompletableFuture<Void> { return logFuture { var bundleVariety: BundleVariety? = null pool.connection.use { it.prepareStatement( """ SELECT Variety FROM tw_bundle WHERE Avatar = ? AND Name = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, bundleName) dbStatement.setString(2, avatarID) dbStatement.executeQuery().use { rows -> if (rows.next()) { bundleVariety = BundleVariety.getBundleVariety(rows.getInt("Variety")) } } } } bundleVariety?.let { pool.connection.use { db -> db.prepareStatement( """ DELETE FROM tw_bundle WHERE Avatar = ? AND Name = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, bundleName) dbStatement.setString(2, avatarID) dbStatement.execute() } } FileUtils.deleteQuietly( TwilightComponent.BUNDLE_ENTRY_PATH.resolve(avatarID).resolve(bundleName + it.fileVariety) .toFile().absoluteFile ) } } } fun silentSite(siteID: String): CompletableFuture<Boolean> { return logValueFuture { pool.connection.use { it.prepareStatement( """ DELETE FROM tw_site WHERE Site_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, siteID) dbStatement.execute() } } } } fun getUbuntuIDs(avatarID: String): CompletableFuture<Collection<Array<String>>> { return logValueFuture { pool.connection.use { it.prepareStatement( """ SELECT Avatar_ID, Avatar_Name FROM tw_ubuntu INNER JOIN tn_avatar ON tw_ubuntu.Ubuntu = tn_avatar.Avatar_ID WHERE Avatar = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery().use { rows -> val data = mutableListOf<Array<String>>() while (rows.next()) { data.add(arrayOf(rows.getString("Avatar_ID"), rows.getString("Avatar_Name"))) } data } } } } } fun getAvatarName(avatarID: String): String { return pool.connection.use { it.prepareStatement( """ SELECT Avatar_Name FROM tn_avatar WHERE Avatar_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery().use { rows -> if (rows.next()) rows.getString("Avatar_Name") else "" } } } } fun getClearedLevelIDs(avatarID: String): CompletableFuture<Collection<String>> { return logValueFuture { pool.connection.use { it.prepareStatement( """ SELECT Level_ID FROM tw_level WHERE Avatar = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery().use { rows -> val data = mutableListOf<String>() while (rows.next()) { data.add(rows.getString("Level_ID")) } data } } } } } fun setWwwLevel(avatarID: String, levelID: String) { pool.connection.use { it.prepareStatement( """ REPLACE INTO tw_level VALUES (?, ?, ?) """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.setString(2, levelID) dbStatement.setTimestamp(3, Timestamp(System.currentTimeMillis())) dbStatement.execute() } } } fun getSilentSiteCompetence(avatarID: String): CompletableFuture<Int> { return logValueFuture { pool.connection.use { it.prepareStatement( """ SELECT Silent_Site FROM tw_avatar WHERE Avatar = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery() .use { rows -> if (rows.next()) { rows.getInt("Silent_Site") } else { QwilightAvatar.SILENT_SITE_CALLABLE } } } } } } fun getIOCompetence(avatarID: String): CompletableFuture<Int> { return logValueFuture { pool.connection.use { it.prepareStatement( """ SELECT IO_Competence FROM tw_avatar WHERE Avatar = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery() .use { rows -> if (rows.next()) { rows.getInt("IO_Competence") } else { QwilightAvatar.IO_CALLABLE } } } } } } fun getNotifySaveBundle(avatarID: String): Int { return pool.connection.use { it.prepareStatement( """ SELECT Notify_Save_Bundle FROM tw_avatar WHERE Avatar = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery() .use { rows -> if (rows.next()) rows.getInt("Notify_Save_Bundle") else QwilightAvatar.NOTIFY_SAVE_BUNDLE_CALLABLE } } } } fun getConfigure(avatarID: String): CompletableFuture<IntArray> { return logValueFuture { pool.connection.use { it.prepareStatement( """ SELECT Silent_Site, Notify_Ubuntu, Default_Bundle_Competence, IO_Competence, Notify_Save_Bundle FROM tw_avatar WHERE Avatar = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery().use { rows -> if (rows.next()) { intArrayOf( rows.getInt("Silent_Site"), rows.getInt("Notify_Ubuntu"), rows.getInt("Default_Bundle_Competence"), rows.getInt("IO_Competence"), rows.getInt("Notify_Save_Bundle") ) } else { intArrayOf( QwilightAvatar.SILENT_SITE_CALLABLE, QwilightAvatar.NOTIFY_UBUNTU, QwilightAvatar.BUNDLE_VOID, QwilightAvatar.IO_CALLABLE, QwilightAvatar.NOTIFY_SAVE_BUNDLE_CALLABLE ) } } } } } } fun saveUbuntu(avatarID: String, ubuntuID: String): CompletableFuture<Boolean> { return logValueFuture { try { pool.connection.use { it.prepareStatement( """ INSERT INTO tw_ubuntu VALUES(?, ?) """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.setString(2, ubuntuID) dbStatement.execute() true } } } catch (e: SQLIntegrityConstraintViolationException) { false } } } fun saveBundle( avatarID: String, bundleName: String, bundleLength: Long, bundleVariety: BundleVariety, etc: String ) { pool.connection.use { it.prepareStatement( """ INSERT INTO tw_bundle VALUES (?, ?, ?, ?, ?, ?, ?) """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.setTimestamp(2, Timestamp(System.currentTimeMillis())) dbStatement.setString(3, bundleName) dbStatement.setLong(4, bundleLength) dbStatement.setInt( 5, if (bundleVariety == BundleVariety.QWILIGHT) QwilightAvatar.BUNDLE_VOID else getDefaultBundleCompetence( avatarID ) ) dbStatement.setInt(6, bundleVariety.value) dbStatement.setString(7, etc) dbStatement.execute() } } } private fun getDefaultBundleCompetence(avatarID: String): Int { return pool.connection.use { it.prepareStatement( """ SELECT Default_Bundle_Competence FROM tw_avatar WHERE Avatar = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery() .use { rows -> if (rows.next()) rows.getInt("Default_Bundle_Competence") else QwilightAvatar.BUNDLE_VOID } } } } fun isBundleLengthAvailable(avatarID: String, bundleLength: Long): Boolean { return pool.connection.use { it.prepareStatement( """ SELECT SUM(Length) AS Length FROM tw_bundle WHERE Avatar = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery() .use { rows -> rows.next() && bundleLength + rows.getLong("Length") < TwilightComponent.BUNDLE_LENGTH } } } } fun hasBundleBefore(avatarID: String, bundleName: String): CompletableFuture<Boolean> { return logValueFuture { pool.connection.use { it.prepareStatement( """ SELECT * FROM tw_bundle WHERE Avatar = ? AND Name = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.setString(2, bundleName) dbStatement.executeQuery().use { rows -> rows.next() } } } } } fun getLastDate(avatarID: String): Long { return pool.connection.use { dbComponent -> dbComponent.prepareStatement( """ SELECT Last_Date FROM tn_avatar WHERE Avatar_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery().use { (if (it.next()) it.getTimestamp("Last_Date") .toInstant() else Instant.now()).toEpochMilli() } } } } fun getBundle(avatarID: String, bundleName: String): CompletableFuture<Array<Any>?> { return logValueFuture { pool.connection.use { it.prepareStatement( """ SELECT Competence, Variety, Etc, Name FROM tw_bundle WHERE Avatar = ? AND Name = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, bundleName) dbStatement.setString(2, avatarID) dbStatement.executeQuery().use { rows -> if (rows.next()) { arrayOf( rows.getInt("Competence"), rows.getInt("Variety"), rows.getString("Etc"), rows.getString("Name") ) } else { null } } } } } } fun setNote(noteID: String, noteID128: String, noteID256: String, targetComputing: Computing) { pool.connection.use { it.prepareStatement( """ REPLACE INTO tw_note VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) """.trimIndent() ).use { dbStatement -> val isSalt = targetComputing.isSalt dbStatement.setString(1, noteID) dbStatement.setString(2, noteID128) dbStatement.setString(3, noteID256) dbStatement.setInt(4, targetComputing.noteVariety.value) dbStatement.setString(5, targetComputing.artist) dbStatement.setString(6, targetComputing.title) dbStatement.setString(7, targetComputing.genre) dbStatement.setString(8, targetComputing.levelText) dbStatement.setInt(9, targetComputing.level) dbStatement.setInt(10, targetComputing.inputMode.value) dbStatement.setInt(11, targetComputing.totalNotes) dbStatement.setBoolean(12, isSalt) dbStatement.setDouble( 13, if (isSalt) 0.0 else AbilitySystem.getAbility( AbilityClassSystem.AbilityClassVariety.INPUT_MODE_5K, noteID128, noteID256 ) ) dbStatement.setDouble( 14, if (isSalt) 0.0 else AbilitySystem.getAbility( AbilityClassSystem.AbilityClassVariety.INPUT_MODE_7K, noteID128, noteID256 ) ) dbStatement.setDouble( 15, if (isSalt) 0.0 else AbilitySystem.getAbility( AbilityClassSystem.AbilityClassVariety.INPUT_MODE_9K, noteID128, noteID256 ) ) dbStatement.setDouble(16, targetComputing.length) dbStatement.execute() } } } fun learnAbility() { data class NoteID(val noteID: String, val noteID128: String, val noteID256: String, val isSalt: Boolean) val noteIDs = mutableListOf<NoteID>() pool.connection.use { it.prepareStatement( """ SELECT Note_ID, Note_ID_128, Note_ID_256, Is_Salt FROM tw_note """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { noteIDs.add( NoteID( rows.getString("Note_ID"), rows.getString("Note_ID_128"), rows.getString("Note_ID_256"), rows.getBoolean("Is_Salt") ) ) } } } } noteIDs.parallelStream().use { parallel -> parallel.forEach { (noteID, noteID128, noteID256, isSalt) -> pool.connection.use { it.prepareStatement( """ UPDATE tw_note SET Ability_5K = ?, Ability_7K = ?, Ability_9K = ? WHERE Note_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setDouble( 1, if (isSalt) 0.0 else AbilitySystem.getAbility( AbilityClassSystem.AbilityClassVariety.INPUT_MODE_5K, noteID128, noteID256 ) ) dbStatement.setDouble( 2, if (isSalt) 0.0 else AbilitySystem.getAbility( AbilityClassSystem.AbilityClassVariety.INPUT_MODE_7K, noteID128, noteID256 ) ) dbStatement.setDouble( 3, if (isSalt) 0.0 else AbilitySystem.getAbility( AbilityClassSystem.AbilityClassVariety.INPUT_MODE_9K, noteID128, noteID256 ) ) dbStatement.setString(4, noteID) dbStatement.execute() } } } } } fun isCrossUbuntu(avatarID: String, ubuntuID: String): Boolean { return pool.connection.use { it.prepareStatement( """ SELECT * FROM tw_ubuntu WHERE Avatar = ? AND Ubuntu = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, ubuntuID) dbStatement.setString(2, avatarID) dbStatement.executeQuery().use { rows -> rows.next() } } } } fun getNotifyUbuntu(avatarID: String): Iterable<String> { return pool.connection.use { it.prepareStatement( """ SELECT Ubuntu, Notify_Ubuntu FROM tw_ubuntu LEFT OUTER JOIN tw_avatar ON tw_ubuntu.Ubuntu = tw_avatar.Avatar WHERE tw_ubuntu.Avatar = ? AND Notify_Ubuntu = 0 """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery().use { rows -> val data = mutableListOf<String>() while (rows.next()) { data.add(rows.getString("Ubuntu")) } data } } } } fun saveSiteYell(siteID: String, siteYell: SiteYell): CompletableFuture<Void> { return logFuture { pool.connection.use { it.prepareStatement( """ INSERT INTO tw_site VALUES(?, ?, ?, ?, ?, ?, ?) """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, siteID) dbStatement.setString(2, siteYell.avatarID) dbStatement.setString(3, siteYell.avatarName) dbStatement.setLong(4, siteYell.date) dbStatement.setString(5, siteYell.siteYell) dbStatement.setLong(6, siteYell.platformID) dbStatement.setBoolean(7, siteYell.translate) dbStatement.execute() } } } } fun doModifySiteYell(siteYell: SiteYell): CompletableFuture<Void> { return logFuture { pool.connection.use { it.prepareStatement( """ UPDATE tw_site SET Site_Yell = ? WHERE Platform_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, siteYell.siteYell) dbStatement.setLong(2, siteYell.platformID) dbStatement.execute() } } } } fun wipeSiteYell(siteYell: SiteYell): CompletableFuture<Void> { return logFuture { pool.connection.use { it.prepareStatement( """ DELETE FROM tw_site WHERE Platform_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setLong(1, siteYell.platformID) dbStatement.execute() } } } } fun saveComment( multiplier: Double, autoMode: Int, noteSaltMode: Int, audioMultiplier: Double, faintNoteMode: Int, judgmentMode: Int, hitPointsMode: Int, noteMobilityMode: Int, longNoteMode: Int, inputFavorMode: Int, noteModifyMode: Int, lowestJudgmentConditionMode: Int, stand: Int, band: Int, isBand1: Boolean, point: Double, salt: Int, commentIDNew: String, avatarID: String, noteID: String, isPaused: Boolean, inputFlags: Int, lowestAudioMultiplier: Double, highestAudioMultiplier: Double ): String? { var commentID: String? = null pool.connection.use { it.autoCommit = false try { it.prepareStatement( """ SELECT Stand, Comment_ID FROM tw_comment WHERE Note_ID = ? AND Avatar = ? AND Is_Max = true """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, noteID) dbStatement.setString(2, avatarID) dbStatement.executeQuery().use { rows -> if (rows.next()) { if (rows.getInt("Stand") <= stand) { commentID = rows.getString("Comment_ID") } } else { commentID = "" } } } if (!commentID.isNullOrEmpty()) { it.prepareStatement( """ UPDATE tw_comment SET Is_Max = false WHERE Comment_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, commentID) dbStatement.execute() } } it.prepareStatement( """ INSERT INTO tw_comment VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) """.trimIndent() ).use { dbStatement -> dbStatement.setTimestamp(1, Timestamp(System.currentTimeMillis())) dbStatement.setString(2, noteID) dbStatement.setString(3, avatarID) dbStatement.setDouble(4, multiplier) dbStatement.setInt(5, autoMode) dbStatement.setInt(6, noteSaltMode) dbStatement.setDouble(7, audioMultiplier) dbStatement.setInt(8, faintNoteMode) dbStatement.setInt(9, judgmentMode) dbStatement.setInt(10, hitPointsMode) dbStatement.setInt(11, noteMobilityMode) dbStatement.setInt(12, longNoteMode) dbStatement.setInt(13, inputFavorMode) dbStatement.setInt(14, noteModifyMode) dbStatement.setInt(15, lowestJudgmentConditionMode) dbStatement.setInt(16, stand) dbStatement.setInt(17, band) dbStatement.setBoolean(18, isBand1) dbStatement.setDouble(19, point) dbStatement.setInt(20, salt) dbStatement.setString(21, commentIDNew) dbStatement.setBoolean(22, commentID != null) dbStatement.setBoolean(23, isPaused) dbStatement.setInt(24, inputFlags) dbStatement.setDouble(25, lowestAudioMultiplier) dbStatement.setDouble(26, highestAudioMultiplier) dbStatement.execute() } it.commit() } catch (e: Throwable) { it.rollback() } } return commentID } fun saveHandled( avatarID: String, noteID: String, isBand1: Boolean, isYell1: Boolean, autoMode: Int, judgmentMode: Int, hitPointsMode: Int, longNoteMode: Int, inputFavorMode: Int, noteModifyMode: Int, lowestAudioMultiplier: Double ) { var handled = getHandled(avatarID, noteID) if (Utility.isStandardHandled( autoMode, judgmentMode, hitPointsMode, longNoteMode, inputFavorMode, noteModifyMode, lowestAudioMultiplier ) ) { if (handled != Component.Handled.YELL1) { if (isYell1) { handled = Component.Handled.YELL1 } else { if (handled != Component.Handled.BAND1) { if (isBand1) { handled = Component.Handled.BAND1 } else { when (hitPointsMode) { Component.HIGHEST_HIT_POINTS_MODE -> handled = Component.Handled.HIGHEST_CLEAR Component.HIGHER_HIT_POINTS_MODE -> { if (handled != Component.Handled.HIGHEST_CLEAR) { handled = Component.Handled.HIGHER_CLEAR } } Component.DEFAULT_HIT_POINTS_MODE -> { if (handled != Component.Handled.HIGHER_CLEAR && handled != Component.Handled.HIGHEST_CLEAR) { handled = Component.Handled.CLEAR } } } } pool.connection.use { it.prepareStatement( """ REPLACE INTO tw_handled VALUES(?, ?, ?) """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.setString(2, noteID) dbStatement.setInt(3, handled.value) dbStatement.execute() } } } } } } else { if (handled == Component.Handled.NOT || handled == Component.Handled.F) { handled = Component.Handled.ASSIST_CLEAR pool.connection.use { it.prepareStatement( """ REPLACE INTO tw_handled VALUES(?, ?, ?) """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.setString(2, noteID) dbStatement.setInt(3, handled.value) dbStatement.execute() } } } } } fun getTitle(avatarID: String, language: String): CompletableFuture<JSON.TwilightWwwTitle?> { return logValueFuture { pool.connection.use { it.prepareStatement( """ SELECT Title_ID FROM tw_title WHERE Avatar = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery().use { rows -> if (rows.next()) { val titleID = rows.getString("Title_ID") if (titleID != null) { val wwwTitle = TitleSystem.getTitle(language, titleID) if (wwwTitle != null) { JSON.TwilightWwwTitle(wwwTitle.title, wwwTitle.titleColor) } else { null } } else { null } } else { null } } } } } } private fun getLevelIDs(avatarID: String): Collection<String> { val levelIDs = mutableListOf<String>() pool.connection.use { it.prepareStatement( """ SELECT Level_ID FROM tw_level WHERE Avatar = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery().use { rows -> while (rows.next()) { levelIDs.add(rows.getString("Level_ID")) } } } } return levelIDs } private fun getNoteIDs(avatarID: String): Collection<String> { val noteIDs = mutableListOf<String>() pool.connection.use { it.prepareStatement( """ SELECT Note_ID FROM tw_comment INNER JOIN (tw_note) USING (Note_ID) WHERE Avatar = ? AND Is_Max = true """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery().use { rows -> while (rows.next()) { noteIDs.add(rows.getString("Note_ID")) } } } } return noteIDs } fun getTitleItems(avatarID: String): Collection<TitleSystem.TitleItem> { return TitleSystem.getTitleItems( getLevelIDs(avatarID), getNoteIDs(avatarID), getAvatarLevels(avatarID)[0] ) } fun getTitles(avatarID: String, language: String): CompletableFuture<Collection<Any>> { return logValueFuture { TitleSystem.getTitles( getLevelIDs(avatarID), getNoteIDs(avatarID), getAvatarLevels(avatarID)[0], language ) } } fun getEdgeIDs(avatarID: String): CompletableFuture<Collection<String>> { return logValueFuture { EdgeSystem.getEdgeIDs( getLevelIDs(avatarID), getNoteIDs(avatarID), getAvatarLevels(avatarID)[0] ) } } fun setAvatarEdge(avatarID: String, edgeID: String): CompletableFuture<Void> { return logFuture { pool.connection.use { if ( EdgeSystem.getEdgeIDs( getLevelIDs(avatarID), getNoteIDs(avatarID), getAvatarLevels(avatarID)[0] ).contains(edgeID) ) { it.prepareStatement( """ REPLACE INTO tw_edge VALUES(?, ?) """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.setString(2, edgeID) dbStatement.execute() } } } } } fun getAvatarEdge(avatarID: String): CompletableFuture<String> { return logValueFuture { pool.connection.use { it.prepareStatement( """ SELECT Edge_ID FROM tw_edge WHERE Avatar = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery() .use { rows -> if (rows.next()) rows.getString("Edge_ID") else "Default" } } } } } fun getNote(want: String, src: Int, fit: Int, page: Int, viewUnit: Int): CompletableFuture<Any> { val isNotWant = want.isEmpty() var totalCount = 0 var topCount = 0 var lastPage = 0 val notes = mutableListOf<Any>() return CompletableFuture.allOf(logFuture { pool.connection.use { db -> if (isNotWant) { db.prepareStatement( """ SELECT COUNT(Comment_ID) AS Total_Count FROM tw_comment """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> if (rows.next()) { totalCount = rows.getInt("Total_Count") } } } } else { db.prepareStatement( when (src) { 0 -> """ SELECT COUNT(Comment_ID) AS Total_Count FROM tw_comment INNER JOIN tw_note USING(Note_ID) WHERE MATCH(Title) AGAINST(? IN BOOLEAN MODE) """.trimIndent() 1 -> """ 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) """.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) """.trimIndent() 3 -> """ SELECT COUNT(Comment_ID) AS Total_Count FROM tw_comment INNER JOIN tw_note USING(Note_ID) WHERE MATCH(Genre) AGAINST(? IN BOOLEAN MODE) """.trimIndent() else -> null } ).use { dbStatement -> if (dbStatement != null) { dbStatement.setString(1, "$want*") dbStatement.executeQuery().use { rows -> if (rows.next()) { totalCount = rows.getInt("Total_Count") } } } } } } }, logFuture { pool.connection.use { db -> if (isNotWant) { db.prepareStatement( """ SELECT COUNT(Comment_ID) AS Top_Count FROM tw_comment WHERE Is_Max = true """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> if (rows.next()) { topCount = rows.getInt("Top_Count") } } } } else { db.prepareStatement( when (src) { 0 -> """ SELECT COUNT(tw_comment.Note_ID) AS Top_Count FROM tw_comment INNER JOIN tw_note USING(Note_ID) WHERE MATCH(Title) AGAINST(? IN BOOLEAN MODE) AND Is_Max = true """.trimIndent() 1 -> """ 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 """.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 """.trimIndent() 3 -> """ SELECT COUNT(tw_comment.Note_ID) AS Top_Count FROM tw_comment INNER JOIN tw_note USING(Note_ID) WHERE MATCH(Genre) AGAINST(? IN BOOLEAN MODE) AND Is_Max = true """.trimIndent() else -> null } ).use { dbStatement -> if (dbStatement != null) { dbStatement.setString(1, "$want*") dbStatement.executeQuery().use { rows -> if (rows.next()) { topCount = rows.getInt("Top_Count") } } } } } } }, logFuture { pool.connection.use { db -> if (isNotWant) { db.prepareStatement( """ SELECT COUNT(Note_ID) AS Note_Count FROM tw_note """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> if (rows.next()) { lastPage = max(1.0, ceil(rows.getInt("Note_Count").toDouble() / viewUnit)).toInt() } } } } else { db.prepareStatement( when (src) { 0 -> """ SELECT COUNT(Note_ID) AS Note_Count FROM tw_note WHERE MATCH(Title) AGAINST(? IN BOOLEAN MODE) """.trimIndent() 1 -> """ SELECT COUNT(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(Note_ID) AS Note_Count FROM tw_note WHERE MATCH(Artist) AGAINST(? IN BOOLEAN MODE) """.trimIndent() 3 -> """ SELECT COUNT(Note_ID) AS Note_Count FROM tw_note WHERE MATCH(Genre) AGAINST(? IN BOOLEAN MODE) """.trimIndent() else -> null } ).use { dbStatement -> if (dbStatement != null) { dbStatement.setString(1, "$want*") dbStatement.executeQuery().use { rows -> if (rows.next()) { lastPage = max(1.0, ceil(rows.getInt("Note_Count").toDouble() / viewUnit)).toInt() } } } } } } }, logFuture { val dataStatement = """ Note_ID, Note_Variety, Artist, Title, Genre, Level_Text, tw_note.Level, ( SELECT COUNT(Note_ID) FROM tw_comment WHERE tw_note.Note_ID = tw_comment.Note_ID AND Is_Max = true ) AS Top_Count, ( SELECT COUNT(Comment_ID) FROM tw_comment WHERE tw_note.Note_ID = tw_comment.Note_ID ) AS Total_Count, ( SELECT MAX(Date) FROM tw_comment WHERE tw_note.Note_ID = tw_comment.Note_ID AND Is_Max = true ) AS Date """.trimIndent() val fitStatement = when (fit) { 0 -> " ORDER BY Top_Count DESC " 1 -> " ORDER BY Total_Count DESC " 2 -> " ORDER BY Date DESC " 3 -> " ORDER BY Title " 4 -> " ORDER BY Artist " 5 -> " ORDER BY Genre " 6 -> " ORDER BY Level_Text " else -> " " } pool.connection.use { db -> val dbStatement: PreparedStatement? if (isNotWant) { dbStatement = db.prepareStatement( "SELECT $dataStatement FROM tw_note $fitStatement LIMIT ?, ?" ) dbStatement.setInt(1, viewUnit * (page - 1)) dbStatement.setInt(2, viewUnit) } else { dbStatement = when (src) { 0 -> db.prepareStatement( """ SELECT $dataStatement FROM tw_note WHERE MATCH(Title) AGAINST(? IN BOOLEAN MODE) $fitStatement LIMIT ?, ? """.trimIndent() ) 1 -> db.prepareStatement( """ SELECT $dataStatement 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() ) 2 -> db.prepareStatement( """ SELECT $dataStatement FROM tw_note WHERE MATCH(Artist) AGAINST(? IN BOOLEAN MODE) $fitStatement LIMIT ?, ? """.trimIndent() ) 3 -> db.prepareStatement( """ SELECT $dataStatement FROM tw_note WHERE MATCH(Genre) AGAINST(? IN BOOLEAN MODE) $fitStatement LIMIT ?, ? """.trimIndent() ) else -> null } dbStatement?.setString(1, "$want*") dbStatement?.setInt(2, viewUnit * (page - 1)) dbStatement?.setInt(3, viewUnit) } dbStatement?.use { it.executeQuery().use { rows -> while (rows.next()) { notes.add(object { val noteID = rows.getString("Note_ID") val artist = rows.getString("Artist") val title = rows.getString("Title") val genre = rows.getString("Genre") val levelText = rows.getString("Level_Text") val level = rows.getInt("Level") val topCount = rows.getInt("Top_Count") val totalCount = rows.getInt("Total_Count") val date = rows.getTimestamp("Date") }) } } } } }).thenApply { object { val totalCount = totalCount val topCount = topCount val lastPage = lastPage val notes = notes } } } fun getLevels(levelGroup: LevelSystem.LevelGroup): CompletableFuture<Collection<Any>> { return logValueFuture { pool.connection.use { levelGroup.levelItems.mapNotNull { levelItem -> it.prepareStatement( """ SELECT COUNT(Avatar) AS Count FROM tw_level WHERE Level_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, levelItem.levelID) dbStatement.executeQuery().use { rows -> if (rows.next()) { object { val levelID = levelItem.levelID val title = levelItem.title val comment = levelItem.comment val levelText = levelItem.levelText val level = levelItem.level val avatars = rows.getInt("Count") } } else { null } } } } } } } fun getLevelNote(levelItem: LevelSystem.LevelGroup.LevelItem): CompletableFuture<Collection<Any>> { return logValueFuture { pool.connection.use { levelItem.noteID.split("/".toRegex()).map { noteID -> it.prepareStatement( """ SELECT Note_ID, Note_Variety, Artist, Title, Genre, Level_Text, Level FROM tw_note WHERE Note_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, noteID) dbStatement.executeQuery().use { rows -> if (rows.next()) { object { val noteID = rows.getString("Note_ID") val noteVariety = rows.getInt("Note_Variety") val artist = rows.getString("Artist") val title = rows.getString("Title") val genre = rows.getString("Genre") val levelText = rows.getString("Level_Text") val level = rows.getInt("Level") } } else { object { val noteID = noteID val noteVariety = Component.NoteVariety.EVENT_NOTE val artist = null val title = null val genre = null val levelText = null val level = 0 } } } } } } } } fun getClearedAvatars(levelID: String): Collection<Any> { return pool.connection.use { it.prepareStatement( """ SELECT Avatar_ID, Avatar_Name, tw_level.Date 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 -> dbStatement.setString(1, levelID) dbStatement.executeQuery().use { rows -> val data = mutableListOf<Any>() while (rows.next()) { data.add(object { val avatarID = rows.getString("Avatar_ID") val avatarName = rows.getString("Avatar_Name") val date = rows.getTimestamp("Date") }) } data } } } } fun getAvatarLevels(avatarID: String): IntArray { return pool.connection.use { it.prepareStatement( """ SELECT SUM(Total_Notes * Stand / 1000000) AS Value FROM tw_comment INNER JOIN tw_note USING(Note_ID) WHERE Avatar = ? AND Is_Salt = false """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery().use { rows -> val value = if (rows.next()) rows.getDouble("Value") else 0.0 intArrayOf(1 + (value / 10000).toInt(), (value % 10000).toInt(), 10000) } } } } fun getAvatar(want: String): CompletableFuture<Any?> { return logValueFuture { var avatarID = "" var avatarName = "" var avatarIntro = "" var totalCount = 0 var totalLength = 0.0 var topCount = 0 var date = 0L var wwwLevelIDCount = 0 var avatarLevels = intArrayOf() var avatarAbility5K = 0.0 var avatarAbility5KPlace = 0 var avatarAbility5KCount = 0 var avatarAbility7K = 0.0 var avatarAbility7KPlace = 0 var avatarAbility7KCount = 0 var avatarAbility9K = 0.0 var avatarAbility9KPlace = 0 var avatarAbility9KCount = 0 val dateSet = mutableListOf<Long>() val dateValues = mutableListOf<Int>() val quitStatusValues = IntArray(7) val wantAvatars = mutableListOf<Any>() pool.connection.use { if (want.startsWith("!")) { it.prepareStatement( """ SELECT Avatar_ID FROM tn_avatar WHERE Avatar_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, Utility.getDefaultAvatarID(want.substring(1))) dbStatement.executeQuery().use { rows -> if (rows.next()) { avatarID = rows.getString("Avatar_ID") } } } } else { it.prepareStatement( """ SELECT Avatar_ID, Avatar_Name, Avatar_Intro FROM tn_avatar WHERE MATCH(Avatar_Name) AGAINST(? IN BOOLEAN MODE) """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, "$want*") dbStatement.executeQuery().use { rows -> while (rows.next()) { wantAvatars.add(object { val avatarID = rows.getString("Avatar_ID") val avatarName = rows.getString("Avatar_Name") val avatarIntro = rows.getString("Avatar_Intro") override fun toString(): String { return this.avatarID } }) } avatarID = wantAvatars.singleOrNull()?.toString() ?: "" } } } } if (avatarID.isEmpty()) { if (wantAvatars.isEmpty()) { null } else { wantAvatars } } else { val futures = CompletableFuture.allOf(logFuture { avatarLevels = getAvatarLevels(avatarID) }, logFuture { pool.connection.use { db -> db.prepareStatement( """ 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 = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery().use { rows -> if (rows.next()) { totalCount = rows.getInt("Total_Count") topCount = rows.getInt("Top_Count") avatarName = rows.getString("Avatar_Name") avatarIntro = rows.getString("Avatar_Intro") date = rows.getTimestamp("Last_Date")?.toInstant()?.toEpochMilli() ?: 0 } } } } }, logFuture { pool.connection.use { db -> db.prepareStatement( """ SELECT COUNT(Level_ID) AS Count FROM tw_level WHERE Avatar = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery().use { rows -> if (rows.next()) { wwwLevelIDCount = rows.getInt("Count") } } } } }, logFuture { pool.connection.use { db -> db.prepareStatement( """ SELECT SUM(Length) AS Total_Length 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) dbStatement.executeQuery().use { rows -> if (rows.next()) { totalLength = rows.getDouble("Total_Length") } } } } }, logFuture { pool.connection.use { db -> val millis = System.currentTimeMillis() for (i in 90 downTo 0) { val yyyyMMDD = yyyyMMDDFormat.format( Instant.ofEpochMilli(millis - 86400000L * i) .atZone(ZoneId.systemDefault()) ) dateSet.add(millis - 86400000L * i) db.prepareStatement( """ SELECT COUNT(Comment_ID) AS Count FROM tw_comment WHERE Date BETWEEN "$yyyyMMDD 00:00:00" AND "$yyyyMMDD 23:59:59" AND Avatar = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery().use { if (it.next()) { dateValues.add(it.getInt("Count")) } } } } } }, logFuture { pool.connection.use { db -> db.prepareStatement( """ SELECT Note_ID, Stand, Point FROM tw_comment WHERE Avatar = ? AND Is_Max = true """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery().use { while (it.next()) { ++quitStatusValues[Utility.getQuitStatusValue( it.getDouble("Point"), it.getInt("Stand") )] } } } } }, logFuture { val rawAbilities5K = getAbilities5K() val targetAbilities5K = getAbilities(rawAbilities5K).toList() val validAbilities5K = targetAbilities5K.stream().filter { it.second.second }.toList() val targetAbility5K = targetAbilities5K.stream().filter { it.first.first == avatarID }.findAny().orElse( Pair(Pair(avatarID, avatarName), Pair(0.0, false)) ) avatarAbility5K = targetAbility5K.second.first avatarAbility5KPlace = validAbilities5K.indexOf(targetAbility5K) avatarAbility5KCount = validAbilities5K.size }, logFuture { val rawAbilities7K = getAbilities7K() val targetAbilities7K = getAbilities(rawAbilities7K).toList() val validAbilities7K = targetAbilities7K.stream().filter { it.second.second }.toList() val targetAbility7K = targetAbilities7K.stream().filter { it.first.first == avatarID }.findAny().orElse( Pair(Pair(avatarID, avatarName), Pair(0.0, false)) ) avatarAbility7K = targetAbility7K.second.first avatarAbility7KPlace = validAbilities7K.indexOf(targetAbility7K) avatarAbility7KCount = validAbilities7K.size }, logFuture { val rawAbilities9K = getAbilities9K() val targetAbilities9K = getAbilities(rawAbilities9K).toList() val validAbilities9K = targetAbilities9K.stream().filter { it.second.second }.toList() val targetAbility9K = targetAbilities9K.stream().filter { it.first.first == avatarID }.findAny().orElse( Pair(Pair(avatarID, avatarName), Pair(0.0, false)) ) avatarAbility9K = targetAbility9K.second.first avatarAbility9KPlace = validAbilities9K.indexOf(targetAbility9K) avatarAbility9KCount = validAbilities9K.size }) futures.join() object { var avatarID = avatarID val avatarName = avatarName val avatarIntro = avatarIntro val totalCount = totalCount val totalLength = totalLength val topCount = topCount val date = date val wwwLevelIDCount = wwwLevelIDCount val avatarLevels = avatarLevels val avatarAbility5K = avatarAbility5K val avatarAbility5KPlace = avatarAbility5KPlace + 1 val avatarAbility5KCount = avatarAbility5KCount val avatarAbility7K = avatarAbility7K val avatarAbility7KPlace = avatarAbility7KPlace + 1 val avatarAbility7KCount = avatarAbility7KCount val avatarAbility9K = avatarAbility9K val avatarAbility9KPlace = avatarAbility9KPlace + 1 val avatarAbility9KCount = avatarAbility9KCount val levelNames = arrayOf("*").plus(AbilitySystem.getAbilityNames()) val dateSet = dateSet val dateValues = dateValues val quitStatusValues = quitStatusValues } } } } fun getHandled(avatarID: String, noteID: String): Component.Handled { return pool.connection.use { db -> db.prepareStatement( """ SELECT Handled FROM tw_handled WHERE Avatar = ? AND Note_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.setString(2, noteID) dbStatement.executeQuery().use { rows -> if (rows.next()) Component.Handled.getHandled(rows.getInt("Handled")) else Component.Handled.NOT } } } } fun getAvatarFavorites(inputMode: Component.InputMode, avatarID: String): CompletableFuture<Any> { return logValueFuture { pool.connection.use { db -> db.prepareStatement( """ SELECT tw_note.Note_ID, Title, Artist, Genre, Level_Text, Level, COUNT(Comment_ID) AS Count 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 """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.setInt(2, inputMode.value) dbStatement.executeQuery().use { val data = mutableListOf<Any>() while (it.next()) { val noteID = it.getString("Note_ID") data.add(object { val noteID = noteID val artist = it.getString("Artist") val title = it.getString("Title") val genre = it.getString("Genre") val levelText = it.getString("Level_Text") val level = it.getInt("Level") val handled = getHandled(avatarID, noteID) val totalCount = it.getInt("Count") }) } data } } } } } fun getAvatarLasts(inputMode: Component.InputMode, avatarID: String): CompletableFuture<Any> { return logValueFuture { pool.connection.use { db -> db.prepareStatement( """ SELECT MAX(Date) AS Date, tw_note.Note_ID, Title, Artist, Genre, Level_Text, Level 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 """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.setInt(2, inputMode.value) dbStatement.executeQuery().use { val data = mutableListOf<Any>() while (it.next()) { val noteID = it.getString("Note_ID") data.add(object { val noteID = noteID val artist = it.getString("Artist") val title = it.getString("Title") val genre = it.getString("Genre") val levelText = it.getString("Level_Text") val level = it.getInt("Level") val handled = getHandled(avatarID, noteID) val date = it.getTimestamp("Date") }) } data } } } } } fun getAvatarWwwLevels(avatarID: String): CompletableFuture<Any> { return logValueFuture { pool.connection.use { db -> db.prepareStatement( """ SELECT Level_ID, Date FROM tw_level WHERE Avatar = ? ORDER BY Date DESC """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery().use { val data = mutableListOf<Any>() while (it.next()) { LevelSystem.getLevelItem(it.getString("Level_ID"))?.let { levelItem -> data.add(object { val levelID = levelItem.levelID val title = levelItem.title val levelText = levelItem.levelText val level = levelItem.level val date = it.getTimestamp("Date") }) } } data } } } } } fun getAvatarAbility(inputMode: Component.InputMode, avatarID: String): CompletableFuture<Any> { return logValueFuture { (when (inputMode) { Component.InputMode.INPUT_MODE_5_1 -> getAbilities5K(true) Component.InputMode.INPUT_MODE_7_1 -> getAbilities7K(true) Component.InputMode.INPUT_MODE_9 -> getAbilities9K(true) else -> emptyMap() }.entries.singleOrNull { it.key.first == avatarID } ?: AbstractMap.SimpleEntry( Pair(avatarID, ""), ArrayList() )).value.sortedByDescending { it.ability } } } interface IAvatarHandledItem { val noteID: String @get:JsonIgnore val noteID128: String @get:JsonIgnore val noteID256: String val stand: Int val handled: Component.Handled } fun getAvatarHandled(avatarID: String, levelName: String): CompletableFuture<Any> { return logValueFuture { val data = mutableMapOf<String, Any>() val noteIDAvatarHandledItemMap = mutableMapOf<String, IAvatarHandledItem>() pool.connection.use { db -> db.prepareStatement( """ SELECT tw_note.Note_ID, Note_ID_128, Note_ID_256, Title, Artist, Genre, Level_Text, Level, Stand FROM tw_comment INNER JOIN (tw_note) USING (Note_ID) WHERE Avatar = ? AND Is_Max = true """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery().use { while (it.next()) { val noteID = it.getString("Note_ID") noteIDAvatarHandledItemMap[noteID] = object : IAvatarHandledItem { override val noteID = noteID override val noteID128 = it.getString("Note_ID_128") override val noteID256 = it.getString("Note_ID_256") val artist = it.getString("Artist") val title = it.getString("Title") val genre = it.getString("Genre") val levelText = it.getString("Level_Text") val level = it.getInt("Level") override val stand = it.getInt("Stand") override val handled = getHandled(avatarID, noteID) } } } } } fun setLevelID(levelID: String) { val handledAvatarHandledItemMap = (if (levelID == "*") noteIDAvatarHandledItemMap.filter { AbilitySystem.getAbilityNames(it.value.noteID128, it.value.noteID256).contains(levelName) } else noteIDAvatarHandledItemMap.filter { AbilitySystem.getAbilityIDs(it.value.noteID128, it.value.noteID256).contains(levelID) }).values.groupBy { it.handled } data[levelID] = object { val handledBand1Count = handledAvatarHandledItemMap.getOrDefault(Component.Handled.BAND1, emptyList()).size val handledYell1Count = handledAvatarHandledItemMap.getOrDefault(Component.Handled.YELL1, emptyList()).size val handledHighestClearCount = handledAvatarHandledItemMap.getOrDefault(Component.Handled.HIGHEST_CLEAR, emptyList()).size val handledHigherClearCount = handledAvatarHandledItemMap.getOrDefault(Component.Handled.HIGHER_CLEAR, emptyList()).size val handledClearCount = handledAvatarHandledItemMap.getOrDefault(Component.Handled.CLEAR, emptyList()).size val handledAssistClearCount = handledAvatarHandledItemMap.getOrDefault(Component.Handled.ASSIST_CLEAR, emptyList()).size val noteIDCount = AbilitySystem.getNoteIDCount(levelName, levelID) val avatarHandledItems = handledAvatarHandledItemMap.map { Pair( it.key, it.value.sortedByDescending { avatarHandledItem -> avatarHandledItem.stand }.take(50) ) }.toMap() } } if (levelName == "*") { setLevelID("*") } else { arrayOf("*").plus(AbilitySystem.getAbilityIDs(levelName)).forEach { setLevelID(it) } } data } } interface IAvatarLevelVSItem { val noteID: String @get:JsonIgnore val noteID128: String @get:JsonIgnore val noteID256: String val stand: Int var levelVSStand: Int val handled: Component.Handled } fun getAvatarLevelVS(avatarID: String, targetID: String, levelName: String): CompletableFuture<Any> { fun getLevelVSItems( avatarID: String, noteIDAvatarLevelVSMap: MutableMap<String, IAvatarLevelVSItem> ): CompletableFuture<Void> { return logFuture { pool.connection.use { db -> db.prepareStatement( """ SELECT tw_note.Note_ID, Note_ID_128, Note_ID_256, Title, Artist, Genre, Level_Text, Level, Stand FROM tw_comment INNER JOIN (tw_note) USING (Note_ID) WHERE Avatar = ? AND Is_Max = true """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery().use { while (it.next()) { val noteID = it.getString("Note_ID") noteIDAvatarLevelVSMap[noteID] = object : IAvatarLevelVSItem { override val noteID = noteID override val noteID128 = it.getString("Note_ID_128") override val noteID256 = it.getString("Note_ID_256") val artist = it.getString("Artist") val title = it.getString("Title") val genre = it.getString("Genre") val levelText = it.getString("Level_Text") val level = it.getInt("Level") override val stand = it.getInt("Stand") override var levelVSStand = 0 override val handled = getHandled(avatarID, noteID) } } } } } } } val data = mutableMapOf<String, Any>() val noteIDAvatarLevelVSItemMap = mutableMapOf<String, IAvatarLevelVSItem>() val noteIDTargetLevelVSItemMap = mutableMapOf<String, IAvatarLevelVSItem>() return CompletableFuture.allOf( getLevelVSItems(avatarID, noteIDAvatarLevelVSItemMap), getLevelVSItems(targetID, noteIDTargetLevelVSItemMap) ).thenApply { fun setLevelID(levelID: String) { val avatarLevelVSItems = mutableSetOf<IAvatarLevelVSItem>() val targetLevelVSItems = mutableSetOf<IAvatarLevelVSItem>() (if (levelName == "*") noteIDAvatarLevelVSItemMap.keys.intersect(noteIDTargetLevelVSItemMap.keys) else if (levelID == "*") noteIDAvatarLevelVSItemMap.filter { AbilitySystem.getAbilityNames(it.value.noteID128, it.value.noteID256).contains(levelName) }.keys .intersect(noteIDTargetLevelVSItemMap.filter { AbilitySystem.getAbilityNames(it.value.noteID128, it.value.noteID256).contains(levelName) }.keys) else noteIDAvatarLevelVSItemMap.filter { AbilitySystem.getAbilityIDs(it.value.noteID128, it.value.noteID256).contains(levelID) }.keys .intersect(noteIDTargetLevelVSItemMap.filter { AbilitySystem.getAbilityIDs(it.value.noteID128, it.value.noteID256).contains(levelID) }.keys)) .forEach { noteID -> val avatarLevelVSItem = noteIDAvatarLevelVSItemMap[noteID] val targetLevelVSItem = noteIDTargetLevelVSItemMap[noteID] if (avatarLevelVSItem != null && targetLevelVSItem != null) { val levelVSStand = avatarLevelVSItem.stand - targetLevelVSItem.stand if (levelVSStand > 0) { avatarLevelVSItem.levelVSStand = levelVSStand avatarLevelVSItems.add(avatarLevelVSItem) } else if (levelVSStand < 0) { targetLevelVSItem.levelVSStand = levelVSStand targetLevelVSItems.add(targetLevelVSItem) } } } data[levelID] = object { val avatarLevelVSCount = avatarLevelVSItems.size val targetLevelVSCount = targetLevelVSItems.size val avatarLevelVSItems = avatarLevelVSItems.sortedBy { it.levelVSStand }.take(50) val targetLevelVSItems = targetLevelVSItems.sortedByDescending { it.levelVSStand }.take(50) } } if (levelName == "*") { setLevelID("*") } else { arrayOf("*").plus(AbilitySystem.getAbilityIDs(levelName)).forEach { setLevelID(it) } } data } } private fun getAbilities5K(getHandled: Boolean = false): Map<Pair<String, String>, MutableCollection<JSON.TwilightWwwAvatarAbility>> { val abilities5K = mutableMapOf<Pair<String, String>, MutableCollection<JSON.TwilightWwwAvatarAbility>>() 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_5K * (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_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 ORDER BY Value DESC """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { val avatarID = rows.getString("Avatar_ID") val noteID = rows.getString("Note_ID") val abilities = abilities5K.computeIfAbsent( Pair(avatarID, rows.getString("Avatar_Name")) ) { ArrayList() } if (abilities.size < 50) { abilities.add( JSON.TwilightWwwAvatarAbility( rows, 0.95.pow(abilities.size.toDouble()), if (getHandled) getHandled(avatarID, noteID) else Component.Handled.NOT ) ) } } } } } return abilities5K } fun getAvatarAbility5K(avatarID: String): Double { pool.connection.use { db -> db.prepareStatement( """ SELECT Ability_5K * (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 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 ORDER BY Value DESC LIMIT 50 """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery().use { rows -> val abilities = mutableListOf<Double>() while (rows.next()) { abilities.add(rows.getDouble("Value") * 0.95.pow(abilities.size.toDouble())) } return abilities.sumOf { it } } } } } private fun getAbilities7K(getHandled: Boolean = false): Map<Pair<String, String>, MutableCollection<JSON.TwilightWwwAvatarAbility>> { val abilities7K = mutableMapOf<Pair<String, String>, MutableCollection<JSON.TwilightWwwAvatarAbility>>() 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_7K * (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_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 ORDER BY Value DESC """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { val avatarID = rows.getString("Avatar_ID") val noteID = rows.getString("Note_ID") val abilities = abilities7K.computeIfAbsent( Pair(avatarID, rows.getString("Avatar_Name")) ) { ArrayList() } if (abilities.size < 50) { abilities.add( JSON.TwilightWwwAvatarAbility( rows, 0.95.pow(abilities.size.toDouble()), if (getHandled) getHandled(avatarID, noteID) else Component.Handled.NOT ) ) } } } } } return abilities7K } fun getAvatarAbility7K(avatarID: String): Double { pool.connection.use { db -> db.prepareStatement( """ SELECT Ability_7K * (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 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 ORDER BY Value DESC LIMIT 50 """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery().use { rows -> val abilities = mutableListOf<Double>() while (rows.next()) { abilities.add(rows.getDouble("Value") * 0.95.pow(abilities.size.toDouble())) } return abilities.sumOf { it } } } } } private fun getAbilities9K(getHandled: Boolean = false): Map<Pair<String, String>, MutableCollection<JSON.TwilightWwwAvatarAbility>> { val abilities9K = mutableMapOf<Pair<String, String>, MutableCollection<JSON.TwilightWwwAvatarAbility>>() 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_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 ) } AND Is_Max = true ORDER BY Value DESC """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { val avatarID = rows.getString("Avatar_ID") val noteID = rows.getString("Note_ID") val abilities = abilities9K.computeIfAbsent( Pair(avatarID, rows.getString("Avatar_Name")) ) { ArrayList() } if (abilities.size < 50) { abilities.add( JSON.TwilightWwwAvatarAbility( rows, 0.95.pow(abilities.size.toDouble()), if (getHandled) getHandled(avatarID, noteID) else Component.Handled.NOT ) ) } } } } } return abilities9K } fun getAvatarAbility9K(avatarID: String): Double { pool.connection.use { db -> db.prepareStatement( """ SELECT 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 Avatar = ? AND Ability_9K > 0.0 AND ${ getAbilityFilter( Component.DEFAULT_INPUT_FAVOR_MODE, Component.MODE_9_INPUT_FAVOR_MODE ) } AND Is_Max = true ORDER BY Value DESC LIMIT 50 """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery().use { rows -> val abilities = mutableListOf<Double>() while (rows.next()) { abilities.add(rows.getDouble("Value") * 0.95.pow(abilities.size.toDouble())) } return abilities.sumOf { it } } } } } private fun getAbilities(abilities: Map<Pair<String, String>, MutableCollection<JSON.TwilightWwwAvatarAbility>>): Stream<Pair<Pair<String, String>, Pair<Double, Boolean>>> { return abilities.entries.stream().map { (avatar, abilities) -> Pair( avatar, Pair( abilities.sumOf { it.ability }, !abilities.isEmpty() ) ) }.sorted { o1, o2 -> val value = o2.second.first - o1.second.first if (value > 0) { 1 } else if (value < 0) { -1 } else { 0 } } } fun getHallTotalTotal(): CompletableFuture<Any> { return logValueFuture { pool.connection.use { it.prepareStatement( """ SELECT Avatar_ID, Avatar_Name, COUNT(tw_comment.Note_ID) AS Value 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 """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> val data = mutableListOf<Any>() while (rows.next()) { data.add(object { val avatarID = rows.getString("Avatar_ID") val avatarName = rows.getString("Avatar_Name") val value = rows.getLong("Value") }) } data } } } } } fun getHallAtTotal(): CompletableFuture<Any> { return logValueFuture { pool.connection.use { it.prepareStatement( """ SELECT Avatar_ID, Avatar_Name, COUNT(tw_comment.Note_ID) AS Value 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 GROUP BY Avatar_ID ORDER BY Value DESC LIMIT 50 """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> val data = mutableListOf<Any>() while (rows.next()) { data.add(object { val avatarID = rows.getString("Avatar_ID") val avatarName = rows.getString("Avatar_Name") val value = rows.getLong("Value") }) } data } } } } } fun getHallTotalTop(): CompletableFuture<Any> { return logValueFuture { pool.connection.use { it.prepareStatement( """ SELECT Avatar_ID, Avatar_Name, COUNT(Note_ID) AS Value 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 """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> val data = mutableListOf<Any>() while (rows.next()) { data.add(object { val avatarID = rows.getString("Avatar_ID") val avatarName = rows.getString("Avatar_Name") val value = rows.getLong("Value") }) } data } } } } } fun getHallAtTop(): CompletableFuture<Any> { return logValueFuture { pool.connection.use { it.prepareStatement( """ SELECT Avatar_ID, Avatar_Name, COUNT(Note_ID) AS Value 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 GROUP BY Avatar_ID ORDER BY Value DESC LIMIT 50 """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> val data = mutableListOf<Any>() while (rows.next()) { data.add(object { val avatarID = rows.getString("Avatar_ID") val avatarName = rows.getString("Avatar_Name") val value = rows.getLong("Value") }) } data } } } } } fun getHallTotalStand(): CompletableFuture<Any> { return logValueFuture { pool.connection.use { it.prepareStatement( """ SELECT Avatar_ID, Avatar_Name, SUM(Stand) AS Value 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 """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> val data = mutableListOf<Any>() while (rows.next()) { data.add(object { val avatarID = rows.getString("Avatar_ID") val avatarName = rows.getString("Avatar_Name") val value = rows.getLong("Value") }) } data } } } } } fun getHallAtStand(): CompletableFuture<Any> { return logValueFuture { pool.connection.use { it.prepareStatement( """ SELECT Avatar_ID, Avatar_Name, SUM(Stand) AS Value 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 GROUP BY Avatar_ID ORDER BY Value DESC LIMIT 50 """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> val data = mutableListOf<Any>() while (rows.next()) { data.add(object { val avatarID = rows.getString("Avatar_ID") val avatarName = rows.getString("Avatar_Name") val value = rows.getLong("Value") }) } data } } } } } fun getHallTotalBand(): CompletableFuture<Any> { return logValueFuture { pool.connection.use { it.prepareStatement( """ SELECT Avatar_ID, Avatar_Name, MAX(Band) AS Value 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 """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> val data = mutableListOf<Any>() while (rows.next()) { data.add(object { val avatarID = rows.getString("Avatar_ID") val avatarName = rows.getString("Avatar_Name") val value = rows.getLong("Value") }) } data } } } } } fun getHallAtBand(): CompletableFuture<Any> { return logValueFuture { pool.connection.use { it.prepareStatement( """ SELECT Avatar_ID, Avatar_Name, MAX(Band) AS Value 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 GROUP BY Avatar_ID ORDER BY Value DESC LIMIT 50 """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> val data = mutableListOf<Any>() while (rows.next()) { data.add(object { val avatarID = rows.getString("Avatar_ID") val avatarName = rows.getString("Avatar_Name") val value = rows.getLong("Value") }) } data } } } } } fun getHallAbility(inputMode: Component.InputMode): CompletableFuture<Any> { return logValueFuture { getAbilities( when (inputMode) { Component.InputMode.INPUT_MODE_5_1 -> getAbilities5K() Component.InputMode.INPUT_MODE_7_1 -> getAbilities7K() Component.InputMode.INPUT_MODE_9 -> getAbilities9K() else -> emptyMap() } ).limit(50).map { (avatar, abilities) -> object { val avatarID = avatar.first val avatarName = avatar.second val value = abilities.first } }.toArray() } } fun getHallLevel(): CompletableFuture<Any> { return logValueFuture { pool.connection.use { it.prepareStatement( """ SELECT Avatar_ID, Avatar_Name, 1 + SUM(Total_Notes * Stand / 1000000 / 10000) 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 Is_Salt = false GROUP BY Avatar_ID ORDER BY Value DESC LIMIT 50 """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> val data = mutableListOf<Any>() while (rows.next()) { data.add(object { val avatarID = rows.getString("Avatar_ID") val avatarName = rows.getString("Avatar_Name") val value = rows.getLong("Value") }) } data } } } } } fun getEtc(language: String): CompletableFuture<Any> { val yyyyMM = yyyyMMFormat.format(Instant.now()) val totalDateSet = mutableListOf<String>() val totalDateValues = mutableListOf<Int>() val enrollDateSet = mutableListOf<String>() val enrollDateValues = mutableListOf<Int>() val avatarDateSet = mutableListOf<String>() val avatarDateValues = mutableListOf<Int>() val totalNoteFiles = mutableListOf<Any>() val totalTitles = mutableListOf<Any>() val totalEdges = mutableListOf<Any>() val favorites = mutableListOf<Any>() val favoritesAt = mutableListOf<Any>() val inputModes = mutableListOf<Any>() val autoModes = mutableListOf<Any>() val noteSaltModes = mutableListOf<Any>() val faintNoteModes = mutableListOf<Any>() val judgmentModes = mutableListOf<Any>() val hitPointsModes = mutableListOf<Any>() val noteMobilityModes = mutableListOf<Any>() val longNoteModes = mutableListOf<Any>() val inputFavorModes = mutableListOf<Any>() val noteModifyModes = mutableListOf<Any>() val lowestJudgmentConditionModes = mutableListOf<Any>() return CompletableFuture.allOf( logFuture { pool.connection.use { it.prepareStatement( """ SELECT DATE_FORMAT(Date, "%Y-%m") AS Etc_Date, COUNT(Avatar) AS Value FROM tw_comment GROUP BY Etc_Date HAVING Etc_Date != "0000-00" """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { totalDateSet.add(rows.getString("Etc_Date")) totalDateValues.add(rows.getInt("Value")) } } } } }, logFuture { pool.connection.use { it.prepareStatement( """ SELECT DATE_FORMAT(Date, "%Y-%m") AS Etc_Date, COUNT(Avatar_ID) AS Value FROM tn_avatar GROUP BY Etc_Date HAVING Etc_Date != "0000-00" """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { enrollDateSet.add(rows.getString("Etc_Date")) enrollDateValues.add(rows.getInt("Value")) } } } } }, logFuture { pool.connection.use { it.prepareStatement( """ SELECT DATE_FORMAT(Date, "%Y-%m") AS Etc_Date, COUNT(DISTINCT Avatar) AS Value FROM tw_comment GROUP BY Etc_Date HAVING Etc_Date != "0000-00" """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { avatarDateSet.add(rows.getString("Etc_Date")) avatarDateValues.add(rows.getInt("Value")) } } } } }, logFuture { pool.connection.use { it.prepareStatement( """ SELECT tw_comment.Note_ID, Title, Artist, Genre, Level, Level_Text, COUNT(tw_comment.Note_ID) AS Value FROM tw_comment INNER JOIN (tw_note) USING (Note_ID) WHERE Date BETWEEN "$yyyyMM-01" AND "$yyyyMM-31" GROUP BY tw_comment.Note_ID ORDER BY Value DESC LIMIT 10; """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { totalNoteFiles.add(object { val noteID = rows.getString("Note_ID") val title = rows.getString("Title") val artist = rows.getString("Artist") val genre = rows.getString("Genre") val level = rows.getInt("Level") val levelText = rows.getString("Level_Text") val value = rows.getInt("Value") }) } } } } }, logFuture { pool.connection.use { it.prepareStatement( """ SELECT Title_ID, COUNT(Title_ID) AS Value FROM tw_title GROUP BY Title_ID ORDER BY Value DESC LIMIT 10; """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { val wwwTitle = TitleSystem.getTitle(language, rows.getString("Title_ID")) if (wwwTitle != null) { totalTitles.add(object { val title = wwwTitle.title val titleColor = wwwTitle.titleColor val value = rows.getInt("Value") }) } } } } } }, logFuture { pool.connection.use { it.prepareStatement( """ SELECT Edge_ID, COUNT(Edge_ID) AS Value FROM tw_edge WHERE Edge_ID != "Default" GROUP BY Edge_ID ORDER BY Value DESC LIMIT 10; """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { totalEdges.add(object { val edge = Base64.getEncoder() .encodeToString(EdgeSystem.getDrawing(rows.getString("Edge_ID"))) val value = rows.getInt("Value") }) } } } } }, logFuture { pool.connection.use { it.prepareStatement( """ SELECT tw_favor.Note_ID, Title, Artist, Genre, Level, Level_Text, SUM(2 * Favor - 1) AS Value FROM tw_favor INNER JOIN tw_note USING(Note_ID) GROUP BY tw_favor.Note_ID ORDER BY Value DESC LIMIT 10; """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { favorites.add(object { val noteID = rows.getString("Note_ID") val title = rows.getString("Title") val artist = rows.getString("Artist") val genre = rows.getString("Genre") val level = rows.getInt("Level") val levelText = rows.getString("Level_Text") val value = rows.getInt("Value") }) } } } } }, logFuture { pool.connection.use { it.prepareStatement( """ SELECT tw_favor.Note_ID, Title, Artist, Genre, Level, Level_Text, SUM(2 * Favor - 1) AS Value FROM tw_favor INNER JOIN tw_note USING(Note_ID) WHERE Date BETWEEN "$yyyyMM-01" AND "$yyyyMM-31" GROUP BY tw_favor.Note_ID ORDER BY Value DESC LIMIT 10; """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { favoritesAt.add(object { val noteID = rows.getString("Note_ID") val title = rows.getString("Title") val artist = rows.getString("Artist") val genre = rows.getString("Genre") val level = rows.getInt("Level") val levelText = rows.getString("Level_Text") val value = rows.getInt("Value") }) } } } } }, logFuture { pool.connection.use { it.prepareStatement( """ SELECT Input_Mode AS Mode, COUNT(Input_Mode) AS Value FROM tw_comment INNER JOIN tw_note USING(Note_ID) GROUP BY Mode ORDER BY Value DESC """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { inputModes.add(object { val mode = rows.getInt("Mode") val value = rows.getInt("Value") }) } } } } }, logFuture { pool.connection.use { it.prepareStatement( """ SELECT Auto_Mode AS Mode, COUNT(Auto_Mode) AS Value FROM tw_comment GROUP BY Mode ORDER BY Value DESC """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { autoModes.add(object { val mode = rows.getInt("Mode") val value = rows.getInt("Value") }) } } } } }, logFuture { pool.connection.use { it.prepareStatement( """ SELECT Note_Salt_Mode AS Mode, COUNT(Note_Salt_Mode) AS Value FROM tw_comment GROUP BY Mode ORDER BY Value DESC """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { noteSaltModes.add(object { val mode = rows.getInt("Mode") val value = rows.getInt("Value") }) } } } } }, logFuture { pool.connection.use { it.prepareStatement( """ SELECT Faint_Note_Mode AS Mode, COUNT(Faint_Note_Mode) AS Value FROM tw_comment GROUP BY Mode ORDER BY Value DESC """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { faintNoteModes.add(object { val mode = rows.getInt("Mode") val value = rows.getInt("Value") }) } } } } }, logFuture { pool.connection.use { it.prepareStatement( """ SELECT Judgment_Mode AS Mode, COUNT(Judgment_Mode) AS Value FROM tw_comment GROUP BY Mode ORDER BY Value DESC """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { judgmentModes.add(object { val mode = rows.getInt("Mode") val value = rows.getInt("Value") }) } } } } }, logFuture { pool.connection.use { it.prepareStatement( """ SELECT Hit_Points_Mode AS Mode, COUNT(Hit_Points_Mode) AS Value FROM tw_comment GROUP BY Mode ORDER BY Value DESC """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { hitPointsModes.add(object { val mode = rows.getInt("Mode") val value = rows.getInt("Value") }) } } } } }, logFuture { pool.connection.use { it.prepareStatement( """ SELECT Note_Mobility_Mode AS Mode, COUNT(Note_Mobility_Mode) AS Value FROM tw_comment GROUP BY Mode ORDER BY Value DESC """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { noteMobilityModes.add(object { val mode = rows.getInt("Mode") val value = rows.getInt("Value") }) } } } } }, logFuture { pool.connection.use { it.prepareStatement( """ SELECT Long_Note_Mode AS Mode, COUNT(Long_Note_Mode) AS Value FROM tw_comment GROUP BY Mode ORDER BY Value DESC """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { longNoteModes.add(object { val mode = rows.getInt("Mode") val value = rows.getInt("Value") }) } } } } }, logFuture { pool.connection.use { it.prepareStatement( """ SELECT Input_Favor_Mode AS Mode, COUNT(Input_Favor_Mode) AS Value FROM tw_comment GROUP BY Mode ORDER BY Value DESC """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { inputFavorModes.add(object { val mode = rows.getInt("Mode") val value = rows.getInt("Value") }) } } } } }, logFuture { pool.connection.use { it.prepareStatement( """ SELECT Note_Modify_Mode AS Mode, COUNT(Note_Modify_Mode) AS Value FROM tw_comment GROUP BY Mode ORDER BY Value DESC """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { noteModifyModes.add(object { val mode = rows.getInt("Mode") val value = rows.getInt("Value") }) } } } } }, logFuture { pool.connection.use { it.prepareStatement( """ SELECT Lowest_Judgment_Condition_Mode AS Mode, COUNT(Lowest_Judgment_Condition_Mode) AS Value FROM tw_comment GROUP BY Mode ORDER BY Value DESC """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { lowestJudgmentConditionModes.add(object { val mode = rows.getInt("Mode") val value = rows.getInt("Value") }) } } } } } ).thenApply { object { val totalDateSet = totalDateSet val totalDateValues = totalDateValues val enrollDateSet = enrollDateSet val enrollDateValues = enrollDateValues val avatarDateSet = avatarDateSet val avatarDateValues = avatarDateValues val totalNoteFiles = totalNoteFiles val totalTitles = totalTitles val totalEdges = totalEdges val favorites = favorites val favoritesAt = favoritesAt val inputModes = inputModes val autoModes = autoModes val noteSaltModes = noteSaltModes val faintNoteModes = faintNoteModes val judgmentModes = judgmentModes val hitPointsModes = hitPointsModes val noteMobilityModes = noteMobilityModes val longNoteModes = longNoteModes val inputFavorModes = inputFavorModes val noteModifyModes = noteModifyModes val lowestJudgmentConditionModes = lowestJudgmentConditionModes } } } }