package net.taehui.twilight.system import net.taehui.twilight.* import net.taehui.twilight.BundleIO.BundleVariety import net.taehui.twilight.awilight.Component import net.taehui.twilight.qwilight.QwilightAvatar import org.apache.commons.dbcp2.BasicDataSource import org.apache.commons.io.FileUtils import org.apache.commons.io.FilenameUtils import org.apache.commons.lang3.SystemUtils import java.io.IOException import java.nio.file.Files import java.nio.file.Path import java.sql.PreparedStatement import java.sql.SQLIntegrityConstraintViolationException import java.sql.Timestamp 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.stream.Stream import kotlin.math.pow object DB : Logger { private val pool = BasicDataSource() private val yyyyMMFormat = DateTimeFormatter.ofPattern("yyyy-MM").withZone(ZoneId.systemDefault()) private val yyyyMMDDFormat = DateTimeFormatter.ofPattern("yyyy-MM-dd").withZone(ZoneId.systemDefault()) private fun testDB(isTest: Boolean) { if (SystemUtils.IS_OS_WINDOWS) { Runtime.getRuntime().exec(arrayOf("wsl", "sudo", "service", "mariadb", "start")).waitFor() } else if (isTest) { Runtime.getRuntime().exec(arrayOf("sudo", "service", "mariadb", "start")).waitFor() } } private fun setBaseDB() { pool.connection.use { it.createStatement().use { dbStatement -> dbStatement.executeUpdate( """ CREATE TABLE IF NOT EXISTS tw_note ( Note_ID VARCHAR(139), Note_ID_128 CHAR(32), Note_ID_256 CHAR(64), Note_Variety INTEGER, Artist TEXT, Title TEXT, Genre TEXT, Level_Text TEXT, Level INTEGER, Input_Mode INTEGER, Total_Notes INTEGER, Is_Salt BOOLEAN, Ability_5K REAL, Ability_7K REAL, Ability_9K REAL, Length REAL, PRIMARY KEY (Note_ID) ) ENGINE=InnoDB """.trimIndent() ) dbStatement.executeUpdate( """ CREATE TABLE IF NOT EXISTS tw_comment ( Date TIMESTAMP, Note_ID VARCHAR(139), Avatar VARCHAR(20), 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_P BOOLEAN, Point REAL, Salt INTEGER, Comment_ID CHAR(128), Is_Max BOOLEAN, Is_Paused BOOLEAN, Input_Flags INTEGER, FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE, CHECK (Multiplier >= 0.0), CHECK (Auto_Mode IN (0, 1)), CHECK (Note_Salt_Mode IN (0, 1, 2, 4, 11, 13)), CHECK (Audio_Multiplier >= 0.5 AND Audio_Multiplier <= 2.0), CHECK (Faint_Note_Mode IN (0, 1, 2, 3)), CHECK (Judgment_Mode IN (0, 1, 2, 3, 4)), CHECK (Hit_Points_Mode IN (0, 1, 2, 3, 4, 5)), CHECK (Note_Mobility_Mode IN (0, 1, 3, 4, 5)), CHECK (Long_Note_Mode IN (0, 1)), CHECK (Input_Favor_Mode IN (0, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16)), CHECK (Note_Modify_Mode IN (0, 1)), CHECK (Lowest_Judgment_Condition_Mode IN (0, 1)), CHECK (Stand >= 0 AND Stand <= 1000000), CHECK (Band >= 0), CHECK (Point >= 0.0 AND Point <= 1.0), CHECK (Input_Flags >= 0 AND Input_Flags <= 15) ) ENGINE=InnoDB """.trimIndent() ) dbStatement.executeUpdate( """ CREATE TABLE IF NOT EXISTS tw_bundle ( Avatar VARCHAR(20), Date TIMESTAMP, Name VARCHAR(191), Length LONG, Competence INTEGER, Variety INTEGER, Etc TEXT, 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)) ) ENGINE=InnoDB """.trimIndent() ) dbStatement.executeUpdate( """ CREATE TABLE IF NOT EXISTS tw_ubuntu ( Avatar VARCHAR(20), Ubuntu VARCHAR(20), 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 ) ENGINE=InnoDB """.trimIndent() ) dbStatement.executeUpdate( """ CREATE TABLE IF NOT EXISTS tw_avatar ( Avatar VARCHAR(20), 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, 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)) ) ENGINE=InnoDB """.trimIndent() ) dbStatement.executeUpdate( """ CREATE TABLE IF NOT EXISTS tw_title ( Avatar VARCHAR(20), Title_ID CHAR(36), PRIMARY KEY (Avatar), FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE=InnoDB """.trimIndent() ) dbStatement.executeUpdate( """ CREATE TABLE IF NOT EXISTS tw_edge ( Avatar VARCHAR(20), Edge_ID CHAR(36), PRIMARY KEY (Avatar), FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE=InnoDB """.trimIndent() ) dbStatement.executeUpdate( """ CREATE TABLE IF NOT EXISTS tw_site ( Site_ID CHAR(36), Avatar_ID VARCHAR(20), Avatar_Name VARCHAR(255), Date LONG, Site_Yell TEXT, Platform_ID LONG ) ENGINE=InnoDB """.trimIndent() ) dbStatement.executeUpdate( """ CREATE TABLE IF NOT EXISTS tw_level ( Avatar VARCHAR(20), Level_ID CHAR(36), Date TIMESTAMP, PRIMARY KEY (Avatar, Level_ID), FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE=InnoDB """.trimIndent() ) dbStatement.executeUpdate( """ CREATE TABLE IF NOT EXISTS tw_commentary ( Note_ID VARCHAR(139), Avatar VARCHAR(20), Commentary TEXT, PRIMARY KEY (Avatar, Note_ID), FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE=InnoDB """.trimIndent() ) dbStatement.executeUpdate( """ CREATE TABLE IF NOT EXISTS tw_translated ( Text VARCHAR(766), Target_Language CHAR(2), Translated_Text TEXT, PRIMARY KEY (Text, Target_Language) ) ENGINE=InnoDB """.trimIndent() ) dbStatement.executeUpdate( """ CREATE TABLE IF NOT EXISTS tw_favor ( Note_ID VARCHAR(139), Avatar VARCHAR(20), Date TIMESTAMP, Favor BOOLEAN, PRIMARY KEY (Avatar, Note_ID), FOREIGN KEY (Avatar) REFERENCES tn_avatar(Avatar_ID) ON UPDATE CASCADE ON DELETE CASCADE ) ENGINE=InnoDB """.trimIndent() ) } } } fun getSiteYells(siteID: String): Collection<SiteYell> { pool.connection.use { it.prepareStatement( """ SELECT Avatar_ID, Avatar_Name, Date, Site_Yell, Platform_ID 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") ) ) } return data } } } } fun loadDB(isTest: Boolean) { testDB(isTest) val db = Configure.db pool.url = "jdbc:mariadb://" + db.remote + "/" + db.db pool.username = db.avatar pool.password = db.auth setBaseDB() logInfo("Loaded DB") } fun setFavor(qwilightSetFavor: JSON.QwilightSetFavor, avatarID: String): CompletableFuture<Void> { return logFuture { pool.connection.use { val noteID = qwilightSetFavor.noteID val favor = qwilightSetFavor.favor if (favor != null) { it.prepareStatement( """ REPLACE INTO tw_favor(Note_ID, Avatar, Favor) VALUES(?, ?, ?) """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, noteID) dbStatement.setString(2, avatarID) dbStatement.setBoolean(3, favor) dbStatement.execute() } } else { it.prepareStatement( """ DELETE FROM tw_favor WHERE Note_ID = ? AND Avatar = ? """.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 getWipeNote(noteID: String): CompletableFuture<Int> { return logValueFuture { pool.connection.use { it.prepareStatement( """ SELECT COUNT(Note_ID) AS Count FROM tw_note WHERE Note_ID LIKE ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, "$noteID:%") dbStatement.executeQuery().use { rows -> if (rows.next()) { rows.getInt("Count") } else { 0 } } } } } } fun wipeNote(noteID: String): CompletableFuture<Void> { return logFuture { pool.connection.use { it.prepareStatement( """ DELETE FROM tw_comment WHERE Note_ID LIKE ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, "$noteID:%") val wipedCount = dbStatement.executeUpdate() FileUtils.deleteDirectory(TwilightComponent.COMMENT_ENTRY_PATH.resolve(noteID).toFile()) logInfo("Wiped $wipedCount Comments") } } pool.connection.use { it.prepareStatement( """ DELETE FROM tw_note WHERE Note_ID LIKE ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, "$noteID:%") val wipedCount = dbStatement.executeUpdate() FileUtils.deleteQuietly(TwilightComponent.NOTE_ENTRY_PATH.resolve(noteID).toFile()) logInfo("Wiped $wipedCount Notes") } } } } fun wipeNotes() { 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, Artist, Title FROM tw_note LEFT OUTER JOIN tw_comment ON tw_note.Note_ID = tw_comment.Note_ID WHERE tw_comment.Note_ID IS NULL """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> val noteIDs = mutableSetOf<String>() while (rows.next()) { val noteID = rows.getString("Note_ID") if (!LevelSystem.levelNoteIDs.contains(noteID)) { noteIDs.add(noteID) } } noteIDs } } } } } fun wipeNotes(noteIDs: Collection<String>): CompletableFuture<Void> { return logFuture { logInfo( "Wiped ${ noteIDs.count { noteID -> pool.connection.use { it.prepareStatement( """ DELETE FROM tw_note WHERE Note_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, noteID) dbStatement.execute() true } } } } Notes" ) logInfo( "Wiped ${ noteIDs.count { try { Files.delete(TwilightComponent.NOTE_ENTRY_PATH.resolve(it.substring(0, it.indexOf(':')))) true } catch (e: IOException) { logFault(e) false } } } Note Files") } } fun getWipeComment(avatarID: String): CompletableFuture<Int> { return logValueFuture { pool.connection.use { it.prepareStatement( """ SELECT COUNT(Comment_ID) AS Count FROM tw_comment WHERE Avatar LIKE ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery().use { rows -> if (rows.next()) { rows.getInt("Count") } else { 0 } } } } } } fun wipeComment(avatarID: String): CompletableFuture<Void> { return logFuture { pool.connection.use { it.prepareStatement( """ SELECT Note_ID, Comment_ID FROM tw_comment WHERE Avatar LIKE ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery().use { rows -> var wipedCount = 0 while (rows.next()) { try { var noteID = rows.getString("Note_ID") noteID = noteID.substring(0, noteID.indexOf(':')) Files.delete( TwilightComponent.COMMENT_ENTRY_PATH.resolve(noteID) .resolve("${rows.getString("Comment_ID")}.xz") ) ++wipedCount } catch (e: IOException) { logFault(e) } } logInfo("Wiped $wipedCount Comment Files") } } } pool.connection.use { it.prepareStatement( """ DELETE FROM tw_comment WHERE Avatar LIKE ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) logInfo("Wiped ${dbStatement.executeUpdate()} Comments") } } } } fun getWipeComments(): CompletableFuture<Collection<Path>> { return logValueFuture { Files.newDirectoryStream(TwilightComponent.COMMENT_ENTRY_PATH).use { commentEntryPaths -> commentEntryPaths.map { commentEntryPath -> Files.list(commentEntryPath).use { commentFilePaths -> commentFilePaths.map { commentFilePath -> pool.connection.use { it.prepareStatement( """ SELECT Comment_ID FROM tw_comment WHERE Note_ID LIKE ? AND Comment_ID = ? AND Is_Max = true """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, commentEntryPath.fileName.toString() + ":%") dbStatement.setString( 2, FilenameUtils.removeExtension(commentFilePath.fileName.toString()) ) dbStatement.executeQuery().use { rows -> if (rows.next()) { null } else { commentFilePath } } } } }.toList() } }.flatMap { it.toList() }.filterNotNull() } } } fun wipeComments(commentFilePaths: Collection<Path>): CompletableFuture<Void> { return logFuture { logInfo( "Wiped ${ commentFilePaths.count { try { Files.delete(it) true } catch (e: IOException) { logFault(e) false } } } Comment Files" ) } } fun getWipeBundles(): CompletableFuture<Collection<Path>> { return logValueFuture { Files.newDirectoryStream(TwilightComponent.BUNDLE_ENTRY_PATH).use { bundleEntryPaths -> bundleEntryPaths.map { bundleEntryPath -> Files.list(bundleEntryPath).use { bundleFilePaths -> bundleFilePaths.map { bundleFilePath -> pool.connection.use { it.prepareStatement( """ SELECT Avatar, Name FROM tw_bundle WHERE Avatar = ? AND Name = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, bundleEntryPath.fileName.toString()) dbStatement.setString( 2, FilenameUtils.removeExtension(bundleFilePath.fileName.toString()) ) dbStatement.executeQuery().use { rows -> if (!rows.next()) { bundleFilePath } else { null } } } } }.toList() } }.flatMap { it.toList() }.filterNotNull() } } } fun wipeBundles(bundleFilePaths: Collection<Path>) { logValueFuture { logInfo( "Wiped ${ bundleFilePaths.count { try { Files.delete(it) true } catch (e: IOException) { logFault(e) false } } } Bundle Files" ) } } interface ICommentary { var commentary: String } fun getComment( noteID: String, avatarID: String, language: String, target: Boolean, 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 Note_ID = ? AND Avatar = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, noteID) dbStatement.setString(2, avatarID) dbStatement.executeQuery().use { rows -> if (rows.next()) rows.getBoolean("Favor") else null } } } val ubuntu = if (avatarID.isEmpty() || !target) null else getUbuntu(avatarID).join().map { it[0] } .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_P, Point, Salt, Comment_ID, Is_Paused, Input_Flags FROM tw_comment, tn_avatar WHERE Avatar_ID = Avatar AND tw_comment.Note_ID = ? AND Is_Max = true ORDER BY Stand DESC """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, noteID) dbStatement.executeQuery().use { rows -> while (rows.next()) { if (ubuntu?.contains(rows.getString("Avatar_ID")) != false) { comments.add(object : ICommentary { val avatarID = rows.getString("Avatar_ID") 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") @JvmField val isP = rows.getBoolean("Is_P") 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") }) } } CompletableFuture.allOf(*comments.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 = comments } } } } } } 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 Note_ID = ? AND Avatar = ? """.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 setBundle(qwilightSetBundle: JSON.QwilightSetBundle, avatarID: String) { logValueFuture { pool.connection.use { it.prepareStatement( """ UPDATE tw_bundle SET Competence = ?, Date = Date WHERE Avatar = ? AND Name = ? """.trimIndent() ).use { dbStatement -> dbStatement.setInt(1, qwilightSetBundle.bundleCompetence) dbStatement.setString(2, avatarID) dbStatement.setString(3, qwilightSetBundle.bundleName) 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").time, 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) { logValueFuture { 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) { logValueFuture { pool.connection.use { it.prepareStatement( """ UPDATE tn_avatar SET Last_Date = ? WHERE Avatar_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setTimestamp(1, Timestamp.from(Instant.now())) 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, avatarID) dbStatement.setString(2, bundleName) 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, avatarID) dbStatement.setString(2, bundleName) dbStatement.execute() } } FileUtils.deleteQuietly( TwilightComponent.BUNDLE_ENTRY_PATH.resolve(avatarID).resolve(bundleName + it.fileVariety) .toFile() ) } } } 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 getUbuntu(avatarID: String): CompletableFuture<Collection<Array<String>>> { return logValueFuture { pool.connection.use { it.prepareStatement( """ SELECT Avatar_ID, Avatar_Name FROM tw_ubuntu, tn_avatar WHERE Ubuntu = Avatar_ID AND 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 { 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 -> return 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(Avatar, Level_ID) VALUES (?, ?) """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.setString(2, levelID) 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 { 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 -> return 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, Utility.getDefaultAvatarID(avatarID)) dbStatement.setString(2, Utility.getDefaultAvatarID(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(Avatar, Name, Length, Competence, Variety, Etc) VALUES (?, ?, ?, ?, ?, ?) """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.setString(2, bundleName) dbStatement.setLong(3, bundleLength) dbStatement.setInt( 4, if (bundleVariety == BundleVariety.QWILIGHT) QwilightAvatar.BUNDLE_VOID else getDefaultBundleCompetence( avatarID ) ) dbStatement.setInt(5, bundleVariety.value) dbStatement.setString(6, etc) dbStatement.execute() } } } private fun getDefaultBundleCompetence(avatarID: String): Int { 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 -> return if (rows.next()) rows.getInt("Default_Bundle_Competence") else QwilightAvatar.BUNDLE_VOID } } } } fun isBundleLengthAvailable(avatarID: String, bundleLength: Long): Boolean { 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 -> return rows.next() && bundleLength + rows.getLong("Length") < 5000000000 } } } } 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 { pool.connection.use { dbComponent -> dbComponent.prepareStatement( """ SELECT Last_Date FROM tn_avatar WHERE Avatar_ID = ? """.trimIndent() ).use { it.setString(1, avatarID) it.executeQuery().use { rows -> return (if (rows.next()) rows.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, avatarID) dbStatement.setString(2, bundleName) 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.ordinal) 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.ordinal) dbStatement.setInt(11, targetComputing.totalNotes) dbStatement.setBoolean(12, isSalt) dbStatement.setDouble( 13, if (isSalt) 0.0 else AbilitySystem.getAbility( AbilityClassSystem.AbilityClassVariety.ABILITY_CLASS_5K, noteID128, noteID256 ) ) dbStatement.setDouble( 14, if (isSalt) 0.0 else AbilitySystem.getAbility( AbilityClassSystem.AbilityClassVariety.ABILITY_CLASS_7K, noteID128, noteID256 ) ) dbStatement.setDouble( 15, if (isSalt) 0.0 else AbilitySystem.getAbility( AbilityClassSystem.AbilityClassVariety.ABILITY_CLASS_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) -> 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.ABILITY_CLASS_5K, noteID128, noteID256 ) ) dbStatement.setDouble( 2, if (isSalt) 0.0 else AbilitySystem.getAbility( AbilityClassSystem.AbilityClassVariety.ABILITY_CLASS_7K, noteID128, noteID256 ) ) dbStatement.setDouble( 3, if (isSalt) 0.0 else AbilitySystem.getAbility( AbilityClassSystem.AbilityClassVariety.ABILITY_CLASS_9K, noteID128, noteID256 ) ) dbStatement.setString(4, noteID) dbStatement.execute() } } } } } fun isCrossUbuntu(avatarID: String, ubuntuID: String): Boolean { 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 -> return rows.next() } } } } fun getNotifyUbuntu(avatarID: String): Iterable<String> { 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")) } return 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.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, isP: Boolean, point: Double, salt: Int, commentID: String, avatarID: String, noteID: String, isPaused: Boolean, inputFlags: Int ): Boolean { var isNewStand = true pool.connection.use { it.autoCommit = false it.prepareStatement( """ SELECT Stand 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() && rows.getInt("Stand") > stand) { isNewStand = false } } } if (isNewStand) { it.prepareStatement( """ UPDATE tw_comment SET Date = Date, Is_Max = false WHERE Note_ID = ? AND Avatar = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, noteID) dbStatement.setString(2, avatarID) dbStatement.execute() } } it.prepareStatement( """ INSERT INTO tw_comment(Note_ID, Avatar, Multiplier, Auto_Mode, Note_Salt_Mode, Audio_Multiplier, Faint_Note_Mode, Judgment_Mode, Hit_Points_Mode, Note_Mobility_Mode, Long_Note_Mode, Input_Favor_Mode, Note_Modify_Mode, Lowest_Judgment_Condition_Mode, Stand, Band, Is_P, Point, Salt, Comment_ID, Is_Max, Is_Paused, Input_Flags) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, noteID) dbStatement.setString(2, avatarID) dbStatement.setDouble(3, multiplier) dbStatement.setInt(4, autoMode) dbStatement.setInt(5, noteSaltMode) dbStatement.setDouble(6, audioMultiplier) dbStatement.setInt(7, faintNoteMode) dbStatement.setInt(8, judgmentMode) dbStatement.setInt(9, hitPointsMode) dbStatement.setInt(10, noteMobilityMode) dbStatement.setInt(11, longNoteMode) dbStatement.setInt(12, inputFavorMode) dbStatement.setInt(13, noteModifyMode) dbStatement.setInt(14, lowestJudgmentConditionMode) dbStatement.setInt(15, stand) dbStatement.setInt(16, band) dbStatement.setBoolean(17, isP) dbStatement.setDouble(18, point) dbStatement.setInt(19, salt) dbStatement.setString(20, commentID) dbStatement.setBoolean(21, isNewStand) dbStatement.setBoolean(22, isPaused) dbStatement.setInt(23, inputFlags) dbStatement.execute() } it.commit() } return isNewStand } fun getTitle(qm: Map<String, String>): CompletableFuture<JSON.TwilightWwwTitle?> { return logValueFuture { val avatarID = Utility.getDefaultAvatarID(qm.getOrDefault("avatarID", "")) val language = qm.getOrDefault("language", "en-US") 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 DISTINCT Note_ID FROM tw_comment WHERE Avatar = ? """.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(qm: Map<String, String>): CompletableFuture<Collection<Any>> { return logValueFuture { val avatarID = Utility.getDefaultAvatarID(qm.getOrDefault("avatarID", "")) TitleSystem.getTitles( getLevelIDs(avatarID), getNoteIDs(avatarID), getAvatarLevels(avatarID)[0], qm.getOrDefault("language", "en-US") ) } } fun getEdgeIDs(qm: Map<String, String>): CompletableFuture<Collection<String>> { return logValueFuture { val avatarID = Utility.getDefaultAvatarID(qm.getOrDefault("avatarID", "")) 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): String { pool.connection.use { it.prepareStatement( """ SELECT Edge_ID FROM tw_edge WHERE Avatar = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery() .use { rows -> return if (rows.next()) rows.getString("Edge_ID") else "Default" } } } } fun getNote(qm: Map<String, String>): CompletableFuture<Any> { return logValueFuture { val noteVariety = (qm["noteVariety"] ?: "0").toInt() val want = (qm["want"] ?: "").replace("%".toRegex(), "\\\\%").replace("_".toRegex(), "\\\\_") val src = qm["src"] ?: "0" val fit = qm["fit"] ?: "0" val page = (qm["page"] ?: "1").toInt() val viewUnit = (qm["viewUnit"] ?: "15").toInt() var totalCount = 0 var highestCount = 0 var noteCount = 0 val notes = mutableListOf<Any>() pool.connection.use { db -> val isNotWant = want.isEmpty() val noteVarietyStatement = if (noteVariety > 0) "Note_Variety = $noteVariety" else null val noteVarietyStatement0 = if (noteVarietyStatement != null) " WHERE $noteVarietyStatement" else " " val noteVarietyStatement1 = if (noteVarietyStatement != null) " AND $noteVarietyStatement" else " " if (isNotWant) { db.prepareStatement( """ SELECT COUNT(Comment_ID) AS Total_Count, COUNT(DISTINCT tw_note.Note_ID, Avatar) AS Highest_Count, COUNT(DISTINCT tw_note.Note_ID) AS Note_Count FROM tw_note, tw_comment WHERE tw_note.Note_ID = tw_comment.Note_ID $noteVarietyStatement1 """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> if (rows.next()) { totalCount = rows.getInt("Total_Count") highestCount = rows.getInt("Highest_Count") noteCount = rows.getInt("Note_Count") } } } } else { db.prepareStatement( when (src) { "0" -> """ SELECT COUNT(Comment_ID) AS Total_Count, COUNT(DISTINCT tw_note.Note_ID, Avatar) AS Highest_Count, COUNT(DISTINCT tw_note.Note_ID) AS Note_Count FROM tw_note, tw_comment WHERE tw_note.Note_ID = tw_comment.Note_ID AND Title LIKE ? ESCAPE "\\" """.trimIndent() "1" -> """ SELECT COUNT(Comment_ID) AS Total_Count, COUNT(DISTINCT tw_note.Note_ID, Avatar) AS Highest_Count, COUNT(DISTINCT tw_note.Note_ID) AS Note_Count FROM tw_note, tw_comment, tn_avatar WHERE tw_note.Note_ID = tw_comment.Note_ID AND Avatar = Avatar_ID AND Avatar_Name LIKE ? ESCAPE "\\" """.trimIndent() "2" -> """ SELECT COUNT(Comment_ID) AS Total_Count, COUNT(DISTINCT tw_note.Note_ID, Avatar) AS Highest_Count, COUNT(DISTINCT tw_note.Note_ID) AS Note_Count FROM tw_note, tw_comment WHERE tw_note.Note_ID = tw_comment.Note_ID AND Artist LIKE ? ESCAPE "\\" """.trimIndent() "3" -> """ SELECT COUNT(Comment_ID) AS Total_Count, COUNT(DISTINCT tw_note.Note_ID, Avatar) AS Highest_Count, COUNT(DISTINCT tw_note.Note_ID) AS Note_Count FROM tw_note, tw_comment WHERE tw_note.Note_ID = tw_comment.Note_ID AND Genre LIKE ? ESCAPE "\\" """.trimIndent() else -> null } + noteVarietyStatement1 ).use { dbStatement -> if (dbStatement != null) { dbStatement.setString(1, "%$want%") dbStatement.executeQuery().use { rows -> if (rows.next()) { totalCount = rows.getInt("Total_Count") highestCount = rows.getInt("Highest_Count") noteCount = rows.getInt("Note_Count") } } } } } val asStatement = """ DISTINCT tw_note.Note_ID, Note_Variety, Artist, Title, Genre, tw_note.Level_Text, tw_note.Level, ( SELECT COUNT(DISTINCT Note_ID, Avatar) FROM tw_comment WHERE tw_note.Note_ID = tw_comment.Note_ID ) AS Highest, ( SELECT COUNT(Comment_ID) FROM tw_comment WHERE tw_note.Note_ID = tw_comment.Note_ID ) AS Total, ( SELECT MAX(Date) FROM tw_comment WHERE tw_note.Note_ID = tw_comment.Note_ID ) AS Date """.trimIndent() val fitStatement = when (fit) { "0" -> " ORDER BY Highest DESC " "1" -> " ORDER BY Total DESC " "2" -> " ORDER BY Date DESC " "3" -> " ORDER BY Title " "4" -> " ORDER BY Artist " "5" -> " ORDER BY Genre " "6" -> " ORDER BY Level_Text " else -> " " } val dbStatement: PreparedStatement? if (isNotWant) { dbStatement = db.prepareStatement( "SELECT $asStatement FROM tw_note $noteVarietyStatement0 $fitStatement LIMIT ?, ?" ) dbStatement.setInt(1, viewUnit * (page - 1)) dbStatement.setInt(2, viewUnit) } else { dbStatement = when (src) { "0" -> db.prepareStatement( """ SELECT $asStatement FROM tw_note WHERE Title LIKE ? ESCAPE "\\" $noteVarietyStatement1 $fitStatement LIMIT ?, ? """.trimIndent() ) "1" -> db.prepareStatement( """ SELECT $asStatement FROM tw_note, tw_comment, tn_avatar WHERE tw_note.Note_ID = tw_comment.Note_ID AND Avatar = Avatar_ID AND Avatar_Name LIKE ? ESCAPE "\\" $noteVarietyStatement1 $fitStatement LIMIT ?, ? """.trimIndent() ) "2" -> db.prepareStatement( """ SELECT $asStatement FROM tw_note WHERE Artist LIKE ? ESCAPE "\\" $noteVarietyStatement1 $fitStatement LIMIT ?, ? """.trimIndent() ) "3" -> db.prepareStatement( """ SELECT $asStatement FROM tw_note WHERE Genre LIKE ? ESCAPE "\\" $noteVarietyStatement1 $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 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") val highestCount = rows.getInt("Highest") val totalCount = rows.getInt("Total") val date = rows.getTimestamp("Date") }) } } } object { val totalCount = totalCount val highestCount = highestCount val noteCount = noteCount val notes = notes } } } } fun getLevels(levelGroup: LevelSystem.LevelGroup): CompletableFuture<Collection<Any>> { return logValueFuture { levelGroup.levelItems.mapNotNull { levelItem -> pool.connection.use { 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 { levelItem.noteID.split("/".toRegex()).map { noteID -> pool.connection.use { 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.ordinal val artist = null val title = null val genre = null val levelText = null val level = 0 } } } } } } } } fun getAvatarLevels(avatarID: String): IntArray { pool.connection.use { it.prepareStatement( """ SELECT SUM(Total_Notes * Stand / 1000000) AS Value FROM tw_comment, tw_note WHERE Avatar = ? AND tw_comment.Note_ID = tw_note.Note_ID 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 return intArrayOf(1 + (value / 10000).toInt(), (value % 10000).toInt(), 10000) } } } } fun getAvatar(qm: Map<String, String>): CompletableFuture<Any?> { return logValueFuture { val wantAvatarID = Utility.getDefaultAvatarID(qm.getOrDefault("avatarID", "")) val wantAvatarName = qm.getOrDefault("avatarName", "") var avatarID = "" var avatarName = "" var avatarIntro = "" var totalCount = 0 var totalLength = 0.0 var highestCount = 0 var date = 0L var avatarLevels = intArrayOf() val avatarAbility5K: Double val avatarAbility5KClass: Double val avatarAbility5KPlace: Int val avatarAbility5KCount: Int val avatarAbility7K: Double val avatarAbility7KClass: Double val avatarAbility7KPlace: Int val avatarAbility7KCount: Int val avatarAbility9K: Double val avatarAbility9KClass: Double val avatarAbility9KPlace: Int val avatarAbility9KCount: Int val levels = mutableListOf<Any>() val lasts = mutableListOf<Any>() val favorites = mutableListOf<Any>() val dateSet = mutableListOf<Long>() val dateValues = mutableListOf<Int>() val quitStatusValues = IntArray(7) val abilities5K = mutableListOf<JSON.TwilightWwwAvatarAbility>() val abilities7K = mutableListOf<JSON.TwilightWwwAvatarAbility>() val abilities9K = mutableListOf<JSON.TwilightWwwAvatarAbility>() val wantAvatars = mutableListOf<Any>() pool.connection.use { db -> if (wantAvatarID.isEmpty()) { db.prepareStatement( """ SELECT Avatar_ID, Avatar_Name, Avatar_Intro FROM tn_avatar WHERE Avatar_Name LIKE ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, "%$wantAvatarName%") 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() ?: "" } } } else { db.prepareStatement( """ SELECT Avatar_ID FROM tn_avatar WHERE Avatar_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, wantAvatarID) dbStatement.executeQuery().use { rows -> if (rows.next()) { avatarID = rows.getString("Avatar_ID") } } } } } if (avatarID.isEmpty()) { if (wantAvatars.isEmpty()) { null } else { wantAvatars } } else { val futures = CompletableFuture.allOf(logValueFuture { avatarLevels = getAvatarLevels(avatarID) }, logValueFuture { pool.connection.use { db -> db.prepareStatement( """ SELECT COUNT(Comment_ID) AS Total_Count, COUNT(DISTINCT Note_ID) AS Highest_Count, Avatar_ID, Avatar_Name, Avatar_Intro, Last_Date FROM tn_avatar, tw_comment WHERE Avatar_ID = tw_comment.Avatar AND Avatar_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery().use { rows -> if (rows.next()) { totalCount = rows.getInt("Total_Count") highestCount = rows.getInt("Highest_Count") avatarName = rows.getString("Avatar_Name") avatarIntro = rows.getString("Avatar_Intro") date = rows.getTimestamp("Last_Date")?.toInstant()?.toEpochMilli() ?: 0 } } } } }, logValueFuture { pool.connection.use { db -> db.prepareStatement( """ SELECT SUM(Length) AS Total_Length FROM tn_avatar, tw_comment, tw_note WHERE Avatar_ID = tw_comment.Avatar AND tw_comment.Note_ID = tw_note.Note_ID AND Avatar_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery().use { rows -> if (rows.next()) { totalLength = rows.getDouble("Total_Length") } } } } }, logValueFuture { pool.connection.use { db -> db.prepareStatement( """ SELECT Level_ID, Date FROM tw_level WHERE Avatar = ? ORDER BY Date DESC """.trimIndent() ).use { it.setString(1, avatarID) it.executeQuery().use { rows -> while (rows.next()) { LevelSystem.getLevelItem(rows.getString("Level_ID"))?.let { levelItem -> levels.add(object { val levelID = levelItem.levelID val title = levelItem.title val levelText = levelItem.levelText val level = levelItem.level val date = rows.getTimestamp("Date") }) } } } } } }, logValueFuture { pool.connection.use { db -> db.prepareStatement( """ SELECT MAX(Date) AS Date, tw_note.Note_ID, Note_Variety, Title, Artist, Genre, tw_note.Level_Text, Level FROM tw_comment, tw_note WHERE tw_comment.Note_ID = tw_note.Note_ID AND Avatar = ? GROUP BY tw_note.Note_ID ORDER BY Date DESC LIMIT 10 """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery().use { while (it.next()) { lasts.add(object { val noteID = it.getString("Note_ID") val noteVariety = it.getInt("Note_Variety") 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 date = it.getTimestamp("Date") }) } } } } }, logValueFuture { pool.connection.use { db -> db.prepareStatement( """ SELECT tw_note.Note_ID, Note_Variety, Title, Artist, Genre, tw_note.Level_Text, Level, COUNT(Comment_ID) AS Count FROM tw_comment, tw_note WHERE tw_comment.Note_ID = tw_note.Note_ID AND Avatar = ? GROUP BY tw_note.Note_ID ORDER BY COUNT DESC LIMIT 10 """.trimIndent() ).use { it.setString(1, avatarID) it.executeQuery().use { rows -> while (rows.next()) { favorites.add(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") val totalCount = rows.getInt("Count") }) } } } } }, logValueFuture { pool.connection.use { db -> val highestDateValue = System.currentTimeMillis() for (i in 90 downTo 0) { dateSet.add(highestDateValue - 86400000L * i) db.prepareStatement( """ SELECT COUNT(Comment_ID) AS Count FROM tw_comment WHERE Avatar = ? AND DATE_FORMAT(Date, "%Y-%m-%d") = ? """.trimIndent() ).use { it.setString(1, avatarID) it.setString( 2, yyyyMMDDFormat.format( Instant.ofEpochMilli(highestDateValue - 86400000L * i) .atZone(ZoneId.systemDefault()) ) ) it.executeQuery().use { rows -> if (rows.next()) { dateValues.add(rows.getInt("Count")) } } } } } }, logValueFuture { pool.connection.use { db -> db.prepareStatement( """ SELECT Note_ID, Stand, Point FROM tw_comment WHERE Avatar = ? AND Is_Max = true """.trimIndent() ).use { it.setString(1, avatarID) it.executeQuery().use { rows -> while (rows.next()) { ++quitStatusValues[Utility.getQuitStatusValue( rows.getDouble("Point"), rows.getInt("Stand") )] } } } } }) 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 avatarAbility5KClass = if (avatarAbility5KPlace != -1) { val absTargetAbilityPlace = 10.0 if (avatarAbility5KPlace < absTargetAbilityPlace) { -(avatarAbility5KPlace + 1).toDouble() } else { (avatarAbility5KPlace - absTargetAbilityPlace) / (avatarAbility5KCount - absTargetAbilityPlace) } } else { 1.0 } abilities5K.addAll( (rawAbilities5K.entries.singleOrNull { it.key.first == avatarID } ?: AbstractMap.SimpleEntry( Pair(avatarID, avatarName), ArrayList() )).value.sortedByDescending { it.ability } ) 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 avatarAbility7KClass = if (avatarAbility7KPlace != -1) { val absTargetAbilityPlace = 10.0 if (avatarAbility7KPlace < absTargetAbilityPlace) { -(avatarAbility7KPlace + 1).toDouble() } else { (avatarAbility7KPlace - absTargetAbilityPlace) / (avatarAbility7KCount - absTargetAbilityPlace) } } else { 1.0 } abilities7K.addAll( (rawAbilities7K.entries.singleOrNull { it.key.first == avatarID } ?: AbstractMap.SimpleEntry( Pair(avatarID, avatarName), ArrayList() )).value.sortedByDescending { it.ability } ) 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 avatarAbility9KClass = if (avatarAbility9KPlace != -1) { val absTargetAbilityPlace = 10.0 if (avatarAbility9KPlace < absTargetAbilityPlace) { -(avatarAbility9KPlace + 1).toDouble() } else { (avatarAbility9KPlace - absTargetAbilityPlace) / (avatarAbility9KCount - absTargetAbilityPlace) } } else { 1.0 } abilities9K.addAll( (rawAbilities9K.entries.singleOrNull { it.key.first == avatarID } ?: AbstractMap.SimpleEntry( Pair(avatarID, avatarName), ArrayList() )).value.sortedByDescending { it.ability } ) futures.join() object { var avatarID = avatarID val avatarName = avatarName val avatarIntro = avatarIntro val totalCount = totalCount val totalLength = totalLength val highestCount = highestCount val date = date val avatarLevels = avatarLevels val avatarAbility5K = avatarAbility5K val avatarAbility5KClass = avatarAbility5KClass val avatarAbility5KPlace = avatarAbility5KPlace + 1 val avatarAbility5KCount = avatarAbility5KCount val avatarAbility7K = avatarAbility7K val avatarAbility7KClass = avatarAbility7KClass val avatarAbility7KPlace = avatarAbility7KPlace + 1 val avatarAbility7KCount = avatarAbility7KCount val avatarAbility9K = avatarAbility9K val avatarAbility9KClass = avatarAbility9KClass val avatarAbility9KPlace = avatarAbility9KPlace + 1 val avatarAbility9KCount = avatarAbility9KCount val levels = levels val lasts = lasts val favorites = favorites val dateSet = dateSet val dateValues = dateValues val quitStatusValues = quitStatusValues val abilities5K = abilities5K val abilities7K = abilities7K val abilities9K = abilities9K } } } } private fun getAbilities5K(): 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, tw_note, tn_avatar WHERE tw_comment.Note_ID = tw_note.Note_ID AND Avatar = Avatar_ID AND Is_Max = true AND (Judgment_Mode = 1 OR Judgment_Mode = 2 OR Judgment_Mode = 4) AND (Hit_Points_Mode = 1 OR Hit_Points_Mode = 2 OR Hit_Points_Mode = 5) AND (Input_Favor_Mode = 0 OR Input_Favor_Mode = 5 OR Input_Favor_Mode = 10) AND Ability_5K > 0.0 ORDER BY Value DESC """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { val abilities = abilities5K.computeIfAbsent( Pair(rows.getString("Avatar_ID"), rows.getString("Avatar_Name")) ) { ArrayList() } if (abilities.size < 50) { abilities.add(JSON.TwilightWwwAvatarAbility(rows, 0.95.pow(abilities.size.toDouble()))) } } } } } 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, tw_note, tn_avatar WHERE tw_comment.Note_ID = tw_note.Note_ID AND Avatar = Avatar_ID AND Is_Max = true AND Avatar = ? AND (Judgment_Mode = 1 OR Judgment_Mode = 2 OR Judgment_Mode = 4) AND (Hit_Points_Mode = 1 OR Hit_Points_Mode = 2 OR Hit_Points_Mode = 5) AND (Input_Favor_Mode = 0 OR Input_Favor_Mode = 5 OR Input_Favor_Mode = 10) AND Ability_5K > 0.0 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(): 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, tw_note, tn_avatar WHERE tw_comment.Note_ID = tw_note.Note_ID AND Avatar = Avatar_ID AND Is_Max = true AND (Judgment_Mode = 1 OR Judgment_Mode = 2 OR Judgment_Mode = 4) AND (Hit_Points_Mode = 1 OR Hit_Points_Mode = 2 OR Hit_Points_Mode = 5) AND (Input_Favor_Mode = 0 OR Input_Favor_Mode = 7 OR Input_Favor_Mode = 11) AND Ability_7K > 0.0 ORDER BY Value DESC """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { val abilities = abilities7K.computeIfAbsent( Pair(rows.getString("Avatar_ID"), rows.getString("Avatar_Name")) ) { ArrayList() } if (abilities.size < 50) { abilities.add(JSON.TwilightWwwAvatarAbility(rows, 0.95.pow(abilities.size.toDouble()))) } } } } } 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, tw_note, tn_avatar WHERE tw_comment.Note_ID = tw_note.Note_ID AND Avatar = Avatar_ID AND Is_Max = true AND Avatar = ? AND (Judgment_Mode = 1 OR Judgment_Mode = 2 OR Judgment_Mode = 4) AND (Hit_Points_Mode = 1 OR Hit_Points_Mode = 2 OR Hit_Points_Mode = 5) AND (Input_Favor_Mode = 0 OR Input_Favor_Mode = 7 OR Input_Favor_Mode = 11) AND Ability_7K > 0.0 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(): 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_9K * (POWER(Stand / 1000000, 3) - 0.5 * POWER(Stand / 1000000, 2) + 0.5 * (Stand / 1000000)) AS Value FROM tw_comment, tw_note, tn_avatar WHERE tw_comment.Note_ID = tw_note.Note_ID AND Avatar = Avatar_ID AND Is_Max = true AND (Judgment_Mode = 1 OR Judgment_Mode = 2 OR Judgment_Mode = 4) AND (Hit_Points_Mode = 1 OR Hit_Points_Mode = 2 OR Hit_Points_Mode = 5) AND (Input_Favor_Mode = 0 OR Input_Favor_Mode = 9) AND Ability_9K > 0.0 ORDER BY Value DESC """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { val abilities = abilities5K.computeIfAbsent( Pair(rows.getString("Avatar_ID"), rows.getString("Avatar_Name")) ) { ArrayList() } if (abilities.size < 50) { abilities.add(JSON.TwilightWwwAvatarAbility(rows, 0.95.pow(abilities.size.toDouble()))) } } } } } return abilities5K } 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, tw_note, tn_avatar WHERE tw_comment.Note_ID = tw_note.Note_ID AND Avatar = Avatar_ID AND Is_Max = true AND Avatar = ? AND (Judgment_Mode = 1 OR Judgment_Mode = 2 OR Judgment_Mode = 4) AND (Hit_Points_Mode = 1 OR Hit_Points_Mode = 2 OR Hit_Points_Mode = 5) AND (Input_Favor_Mode = 0 OR Input_Favor_Mode = 9) AND Ability_9K > 0.0 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 getWow(): CompletableFuture<Any> { val totalAvatars = mutableListOf<Any>() val totalAvatarsAt = mutableListOf<Any>() val highestAvatars = mutableListOf<Any>() val highestAvatarsAt = mutableListOf<Any>() val standAvatars = mutableListOf<Any>() val standAvatarsAt = mutableListOf<Any>() val bandAvatars = mutableListOf<Any>() val bandAvatarsAt = mutableListOf<Any>() val ability5KAvatars = mutableListOf<Any>() val ability7KAvatars = mutableListOf<Any>() val ability9KAvatars = mutableListOf<Any>() val levelAvatars = mutableListOf<Any>() val levyingDate = yyyyMMDDFormat.format(LocalDate.now().withDayOfMonth(1)) val defaultFilter = "WHERE Avatar = tn_avatar.Avatar_ID" val dateStatement = "AND '$levyingDate' <= tw_comment.Date" fun getTotalAvatarsStatement(filter: String): String { return """ SELECT Avatar_ID, Avatar_Name, COUNT(tw_comment.Note_ID) AS Value FROM tw_comment, tn_avatar $filter GROUP BY Avatar_ID ORDER BY Value DESC LIMIT 10 """.trimIndent() } fun getHighestAvatarsStatement(filter: String): String { return """ SELECT Avatar_ID, Avatar_Name, COUNT(DISTINCT tw_comment.Note_ID) AS Value FROM tw_comment, tn_avatar $filter GROUP BY Avatar_ID ORDER BY Value DESC LIMIT 10 """.trimIndent() } fun getStandAvatarsStatement(filter: String): String { return """ SELECT Avatar_ID, Avatar_Name, SUM(Stand) AS Value FROM tw_comment, tn_avatar $filter AND Is_Max = true GROUP BY Avatar_ID ORDER BY Value DESC LIMIT 10 """.trimIndent() } fun getBandAvatarsStatement(filter: String): String { return """ SELECT Avatar_ID, Avatar_Name, MAX(Band) AS Value FROM tw_comment, tn_avatar $filter GROUP BY Avatar_ID ORDER BY Value DESC LIMIT 10 """.trimIndent() } return CompletableFuture.allOf( logValueFuture { pool.connection.use { it.prepareStatement(getTotalAvatarsStatement(defaultFilter)).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { totalAvatars.add(object { val avatarID = rows.getString("Avatar_ID") val avatarName = rows.getString("Avatar_Name") val value = rows.getLong("Value") }) } } } } }, logValueFuture { pool.connection.use { it.prepareStatement(getTotalAvatarsStatement("$defaultFilter $dateStatement")) .use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { totalAvatarsAt.add(object { val avatarID = rows.getString("Avatar_ID") val avatarName = rows.getString("Avatar_Name") val value = rows.getLong("Value") }) } } } } }, logValueFuture { pool.connection.use { it.prepareStatement(getHighestAvatarsStatement(defaultFilter)).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { highestAvatars.add(object { val avatarID = rows.getString("Avatar_ID") val avatarName = rows.getString("Avatar_Name") val value = rows.getLong("Value") }) } } } } }, logValueFuture { pool.connection.use { it.prepareStatement( getHighestAvatarsStatement( "$defaultFilter $dateStatement AND Is_Max = true" ) ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { highestAvatarsAt.add(object { val avatarID = rows.getString("Avatar_ID") val avatarName = rows.getString("Avatar_Name") val value = rows.getLong("Value") }) } } } } }, logValueFuture { pool.connection.use { it.prepareStatement(getStandAvatarsStatement(defaultFilter)).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { standAvatars.add(object { val avatarID = rows.getString("Avatar_ID") val avatarName = rows.getString("Avatar_Name") val value = rows.getLong("Value") }) } } } } }, logValueFuture { pool.connection.use { it.prepareStatement(getStandAvatarsStatement("$defaultFilter $dateStatement")) .use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { standAvatarsAt.add(object { val avatarID = rows.getString("Avatar_ID") val avatarName = rows.getString("Avatar_Name") val value = rows.getLong("Value") }) } } } } }, logValueFuture { pool.connection.use { it.prepareStatement(getBandAvatarsStatement(defaultFilter)).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { bandAvatars.add(object { val avatarID = rows.getString("Avatar_ID") val avatarName = rows.getString("Avatar_Name") val value = rows.getLong("Value") }) } } } } }, logValueFuture { pool.connection.use { it.prepareStatement(getBandAvatarsStatement("$defaultFilter $dateStatement")) .use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { bandAvatarsAt.add(object { val avatarID = rows.getString("Avatar_ID") val avatarName = rows.getString("Avatar_Name") val value = rows.getLong("Value") }) } } } } }, logValueFuture { ability5KAvatars.addAll(getAbilities(getAbilities5K()).limit(50).map { (avatar, abilities) -> object { val avatarID = avatar.first val avatarName = avatar.second val value = abilities.first } }.toList()) }, logValueFuture { ability7KAvatars.addAll(getAbilities(getAbilities7K()).limit(50).map { (avatar, abilities) -> object { val avatarID = avatar.first val avatarName = avatar.second val value = abilities.first } }.toList()) }, logValueFuture { ability9KAvatars.addAll(getAbilities(getAbilities9K()).limit(50).map { (avatar, abilities) -> object { val avatarID = avatar.first val avatarName = avatar.second val value = abilities.first } }.toList()) }, logValueFuture { pool.connection.use { it.prepareStatement( """ SELECT Avatar_ID, Avatar_Name, 1 + SUM(Total_Notes * Stand / 1000000 / 10000) AS Value FROM tw_comment, tw_note, tn_avatar WHERE tw_comment.Note_ID = tw_note.Note_ID AND Avatar = Avatar_ID AND Is_Salt = false GROUP BY Avatar_ID ORDER BY Value DESC LIMIT 50 """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { levelAvatars.add(object { val avatarID = rows.getString("Avatar_ID") val avatarName = rows.getString("Avatar_Name") val value = rows.getLong("Value") }) } } } } } ).thenApply { object { val totalAvatars = totalAvatars val totalAvatarsAt = totalAvatarsAt val highestAvatars = highestAvatars val highestAvatarsAt = highestAvatarsAt val standAvatars = standAvatars val standAvatarsAt = standAvatarsAt val bandAvatars = bandAvatars val bandAvatarsAt = bandAvatarsAt val ability5KAvatars = ability5KAvatars val ability7KAvatars = ability7KAvatars val ability9KAvatars = ability9KAvatars val levelAvatars = levelAvatars } } } fun getEtc(language: String): CompletableFuture<Any> { val totalDateSet = mutableListOf<String>() val totalDateValues = mutableListOf<Int>() val signUpDateSet = mutableListOf<String>() val signUpDateValues = mutableListOf<Int>() val avatarDateSet = mutableListOf<String>() val avatarDateValues = mutableListOf<Int>() val totalNoteFileValues = mutableListOf<Any>() val totalTitleValues = mutableListOf<Any>() val totalEdgeValues = mutableListOf<Any>() val favoriteNoteFileValues = mutableListOf<Any>() val favoriteNoteFileAtValues = mutableListOf<Any>() val inputModeValues = mutableListOf<Any>() val autoModeValues = mutableListOf<Any>() val noteSaltModeValues = mutableListOf<Any>() val faintNoteModeValues = mutableListOf<Any>() val judgmentModeValues = mutableListOf<Any>() val hitPointsModeValues = mutableListOf<Any>() val noteMobilityModeValues = mutableListOf<Any>() val longNoteModeValues = mutableListOf<Any>() val inputFavorModeValues = mutableListOf<Any>() val noteModifyModeValues = mutableListOf<Any>() val lowestJudgmentConditionModeValues = mutableListOf<Any>() return CompletableFuture.allOf( logValueFuture { pool.connection.use { it.prepareStatement( """ SELECT DATE_FORMAT(Date, "%Y-%m") AS Etc_Date, COUNT(*) 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")) } } } } }, logValueFuture { pool.connection.use { it.prepareStatement( """ SELECT DATE_FORMAT(Date, "%Y-%m") AS Etc_Date, COUNT(*) AS Value FROM tn_avatar GROUP BY Etc_Date HAVING Etc_Date != "0000-00" """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { signUpDateSet.add(rows.getString("Etc_Date")) signUpDateValues.add(rows.getInt("Value")) } } } } }, logValueFuture { 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")) } } } } }, logValueFuture { 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_note, tw_comment WHERE tw_note.Note_ID = tw_comment.Note_ID AND DATE_FORMAT(Date, "%Y-%m") = ? GROUP BY tw_comment.Note_ID ORDER BY Value DESC LIMIT 10; """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, yyyyMMFormat.format(Instant.now())) dbStatement.executeQuery().use { rows -> while (rows.next()) { totalNoteFileValues.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") }) } } } } }, logValueFuture { 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) { totalTitleValues.add(object { val title = wwwTitle.title val titleColor = wwwTitle.titleColor val value = rows.getInt("Value") }) } } } } } }, logValueFuture { 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()) { totalEdgeValues.add(object { val edge = Base64.getEncoder() .encodeToString(EdgeSystem.getDrawing(rows.getString("Edge_ID"))) val value = rows.getInt("Value") }) } } } } }, logValueFuture { pool.connection.use { it.prepareStatement( """ SELECT tw_favor.Note_ID, Title, Artist, Genre, Level, Level_Text, SUM(2 * Favor - 1) AS Value FROM tw_note, tw_favor WHERE tw_note.Note_ID = tw_favor.Note_ID GROUP BY tw_favor.Note_ID ORDER BY Value DESC LIMIT 10; """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { favoriteNoteFileValues.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") }) } } } } }, logValueFuture { pool.connection.use { it.prepareStatement( """ SELECT tw_favor.Note_ID, Title, Artist, Genre, Level, Level_Text, SUM(2 * Favor - 1) AS Value FROM tw_note, tw_favor WHERE tw_note.Note_ID = tw_favor.Note_ID AND DATE_FORMAT(Date, "%Y-%m") = ? GROUP BY tw_favor.Note_ID ORDER BY Value DESC LIMIT 10; """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, yyyyMMFormat.format(Instant.now())) dbStatement.executeQuery().use { rows -> while (rows.next()) { favoriteNoteFileAtValues.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") }) } } } } }, logValueFuture { pool.connection.use { it.prepareStatement( """ SELECT Input_Mode AS Mode, COUNT(Input_Mode) AS Value FROM tw_comment, tw_note WHERE tw_comment.Note_ID = tw_note.Note_ID GROUP BY Mode ORDER BY Value DESC """.trimIndent() ).use { dbStatement -> dbStatement.executeQuery().use { rows -> while (rows.next()) { inputModeValues.add(object { val mode = rows.getInt("Mode") val value = rows.getInt("Value") }) } } } } }, logValueFuture { 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()) { autoModeValues.add(object { val mode = rows.getInt("Mode") val value = rows.getInt("Value") }) } } } } }, logValueFuture { 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()) { noteSaltModeValues.add(object { val mode = rows.getInt("Mode") val value = rows.getInt("Value") }) } } } } }, logValueFuture { 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()) { faintNoteModeValues.add(object { val mode = rows.getInt("Mode") val value = rows.getInt("Value") }) } } } } }, logValueFuture { 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()) { judgmentModeValues.add(object { val mode = rows.getInt("Mode") val value = rows.getInt("Value") }) } } } } }, logValueFuture { 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()) { hitPointsModeValues.add(object { val mode = rows.getInt("Mode") val value = rows.getInt("Value") }) } } } } }, logValueFuture { 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()) { noteMobilityModeValues.add(object { val mode = rows.getInt("Mode") val value = rows.getInt("Value") }) } } } } }, logValueFuture { 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()) { longNoteModeValues.add(object { val mode = rows.getInt("Mode") val value = rows.getInt("Value") }) } } } } }, logValueFuture { 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()) { inputFavorModeValues.add(object { val mode = rows.getInt("Mode") val value = rows.getInt("Value") }) } } } } }, logValueFuture { 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()) { noteModifyModeValues.add(object { val mode = rows.getInt("Mode") val value = rows.getInt("Value") }) } } } } }, logValueFuture { 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()) { lowestJudgmentConditionModeValues.add(object { val mode = rows.getInt("Mode") val value = rows.getInt("Value") }) } } } } } ).thenApply { object { val totalDateSet = totalDateSet val totalDateValues = totalDateValues val signUpDateSet = signUpDateSet val signUpDateValues = signUpDateValues val avatarDateSet = avatarDateSet val avatarDateValues = avatarDateValues val totalNoteFileValues = totalNoteFileValues val totalTitleValues = totalTitleValues val totalEdgeValues = totalEdgeValues val favoriteNoteFileValues = favoriteNoteFileValues val favoriteNoteFileAtValues = favoriteNoteFileAtValues val inputModeValues = inputModeValues val autoModeValues = autoModeValues val noteSaltModeValues = noteSaltModeValues val faintNoteModeValues = faintNoteModeValues val judgmentModeValues = judgmentModeValues val hitPointsModeValues = hitPointsModeValues val noteMobilityModeValues = noteMobilityModeValues val longNoteModeValues = longNoteModeValues val inputFavorModeValues = inputFavorModeValues val noteModifyModeValues = noteModifyModeValues val lowestJudgmentConditionModeValues = lowestJudgmentConditionModeValues } } } fun setCommentMax(avatarID: String): CompletableFuture<Void> { return logFuture { val noteIDs = mutableListOf<String>() pool.connection.use { it.prepareStatement( """ SELECT DISTINCT Note_ID FROM tw_comment WHERE Avatar = ? """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.executeQuery().use { rows -> while (rows.next()) { noteIDs.add(rows.getString("Note_ID")) } } } } noteIDs.forEach { noteID -> pool.connection.use { it -> it.autoCommit = false var stand = 0 var date: Timestamp? = null it.prepareStatement( """ SELECT Stand, Date FROM tw_comment WHERE Avatar = ? AND Note_ID = ? AND Date = ( SELECT MAX(Date) FROM tw_comment AS target_comment WHERE Avatar = ? AND Note_ID = ? AND Stand = ( SELECT MAX(Stand) FROM tw_comment AS target_comment WHERE Avatar = ? AND Note_ID = ? ) ) """.trimIndent() ).use { dbStatement -> dbStatement.setString(1, avatarID) dbStatement.setString(2, noteID) dbStatement.setString(3, avatarID) dbStatement.setString(4, noteID) dbStatement.setString(5, avatarID) dbStatement.setString(6, noteID) dbStatement.executeQuery().use { if (it.next()) { stand = it.getInt("Stand") date = it.getTimestamp("Date") } } } it.prepareStatement( """ UPDATE tw_comment SET Date = Date, Is_Max = (Stand = ? AND Date = ?) WHERE Avatar = ? AND Note_ID = ? """.trimIndent() ).use { dbStatement -> dbStatement.setInt(1, stand) dbStatement.setTimestamp(2, date) dbStatement.setString(3, avatarID) dbStatement.setString(4, noteID) dbStatement.execute() } it.commit() } } logInfo("Learned") } } }