diff --git a/src/main/kotlin/dao/AudioDao.kt b/src/main/kotlin/dao/AudioDao.kt index dc2c2a7..a306032 100644 --- a/src/main/kotlin/dao/AudioDao.kt +++ b/src/main/kotlin/dao/AudioDao.kt @@ -5,6 +5,7 @@ package net.piedpiper.bremer.dao import net.piedpiper.bremer.entity.AlbumEntity +import net.piedpiper.bremer.entity.ArtistEntity import net.piedpiper.bremer.entity.AudioEntity import net.piedpiper.bremer.entity.AudioNameEntity import net.piedpiper.bremer.utils.SelectSQLBuilder @@ -48,6 +49,8 @@ @JvmStatic fun findOneById(): String = SelectSQLBuilder(AudioEntity::class) .leftOuterJoin(AlbumEntity::class, "audio.album_id = album.id") + .leftOuterJoin(ArtistEntity::class, "album.artist_id = artist.id", AlbumEntity::class) + .leftOuterJoin(AudioNameEntity::class, "audio.id = audio_name.audio_id") .toSql() .WHERE("audio.id = #{id}") .toString() @@ -55,6 +58,8 @@ @JvmStatic fun findOneBySlug(): String = SelectSQLBuilder(AudioEntity::class) .leftOuterJoin(AlbumEntity::class, "audio.album_id = album.id") + .leftOuterJoin(ArtistEntity::class, "album.artist_id = artist.id", AlbumEntity::class) + .leftOuterJoin(AudioNameEntity::class, "audio.id = audio_name.audio_id") .toSql() .WHERE("audio.slug = #{slug}") .toString() @@ -63,6 +68,8 @@ fun findAllByAlbumIdIn(@Param("albumIds") albumIds: List): String = SelectSQLBuilder(AudioEntity::class) .leftOuterJoin(AlbumEntity::class, "audio.album_id = album.id") + .leftOuterJoin(ArtistEntity::class, "album.artist_id = artist.id", AlbumEntity::class) + .leftOuterJoin(AudioNameEntity::class, "audio.id = audio_name.audio_id") .toSql() .WHERE_IN("audio.album_id", "albumIds", albumIds.size) .toString() @@ -71,6 +78,8 @@ fun findAllByIdIn(@Param("ids") ids: List): String = SelectSQLBuilder(AudioEntity::class) .leftOuterJoin(AlbumEntity::class, "audio.album_id = album.id") + .leftOuterJoin(ArtistEntity::class, "album.artist_id = artist.id", AlbumEntity::class) + .leftOuterJoin(AudioNameEntity::class, "audio.id = audio_name.audio_id") .toSql() .WHERE_IN("audio.id", "ids", ids.size) .toString() @@ -79,6 +88,8 @@ fun findAllBySlugIn(@Param("slugs") slugs: List): String = SelectSQLBuilder(AudioEntity::class) .leftOuterJoin(AlbumEntity::class, "audio.album_id = album.id") + .leftOuterJoin(ArtistEntity::class, "album.artist_id = artist.id", AlbumEntity::class) + .leftOuterJoin(AudioNameEntity::class, "audio.id = audio_name.audio_id") .toSql() .WHERE_IN("audio.slug", "slugs", slugs.size) .toString() @@ -90,12 +101,14 @@ ): String = SelectSQLBuilder(AudioEntity::class) .leftOuterJoin(AlbumEntity::class, "audio.album_id = album.id") + .leftOuterJoin(ArtistEntity::class, "album.artist_id = artist.id", AlbumEntity::class) .leftOuterJoin(AudioNameEntity::class, "audio.id = audio_name.audio_id") .toSql() .WHERE( - "audio.name LIKE CONCAT('%', #{nameLike}, '%') OR " + - "audio_name.name LIKE CONCAT('%', #{nameLike}, '%')" + "audio.name LIKE CONCAT('%', #{nameLike}, '%') OR " + + "audio_name.name LIKE CONCAT('%', #{nameLike}, '%')" ) + .ORDER_BY("audio.album_id, audio.sequence") .LIMIT("#{limit}") .toString() } diff --git a/src/main/kotlin/entity/Audio.kt b/src/main/kotlin/entity/Audio.kt index 4892970..f8f9f72 100644 --- a/src/main/kotlin/entity/Audio.kt +++ b/src/main/kotlin/entity/Audio.kt @@ -18,6 +18,8 @@ var name: String = "", @property:Column("album_id") var albumId: Long = 0L, + @property:Column("sequence") + var sequence: Int = 0, @property:Column("path") var path: String = "", // join diff --git a/src/main/kotlin/model/api/Audio.kt b/src/main/kotlin/model/api/Audio.kt index 3f0059d..61171c8 100644 --- a/src/main/kotlin/model/api/Audio.kt +++ b/src/main/kotlin/model/api/Audio.kt @@ -19,6 +19,7 @@ val slug: String = entity.slug val artist: String? = entity?.album?.artist?.name val album: String? = entity?.album?.name + var sequence: Int? = if (entity?.sequence == 0) { entity?.sequence } else { null } var aliasNames: List? = entity?.aliasNames?.map { it.name } } } \ No newline at end of file diff --git a/src/main/kotlin/model/api/Playlist.kt b/src/main/kotlin/model/api/Playlist.kt index dea3de4..1e3b06a 100644 --- a/src/main/kotlin/model/api/Playlist.kt +++ b/src/main/kotlin/model/api/Playlist.kt @@ -7,7 +7,6 @@ import com.fasterxml.jackson.annotation.JsonInclude import com.fasterxml.jackson.annotation.JsonProperty import jakarta.validation.constraints.NotBlank -import jakarta.validation.constraints.NotEmpty import net.piedpiper.bremer.entity.AudioEntity import net.piedpiper.bremer.entity.PlaylistEntity @@ -27,7 +26,6 @@ @field:NotBlank val name: String = "", - @field:NotEmpty @field:JsonProperty("audio_slugs") val audioSlugs: List = mutableListOf() ) diff --git a/src/main/kotlin/service/app/PlaylistAppService.kt b/src/main/kotlin/service/app/PlaylistAppService.kt index 8fde97c..ab3e100 100644 --- a/src/main/kotlin/service/app/PlaylistAppService.kt +++ b/src/main/kotlin/service/app/PlaylistAppService.kt @@ -83,19 +83,20 @@ id: Long = 0L, slug: String? = null ): PlaylistEntity = - audioDomainService.findAllByOrderedSlugs(request.audioSlugs) - .associate { it.slug to it.id } + request.audioSlugs?.let { + audioDomainService.findAllByOrderedSlugs(it) + }?.associate { it.slug to it.id } .let { audioIdMap -> PlaylistEntity( id = id, slug = slug ?: UUID.randomUUID().toString(), name = request.name, - playlistAudio = request.audioSlugs.mapIndexed { idx, slug -> + playlistAudio = request.audioSlugs?.mapIndexed { idx, slug -> PlaylistAudioEntity( - audioId = audioIdMap[slug]!!, + audioId = audioIdMap?.get(slug) ?: 0L, sequence = idx ) - } + } ?: emptyList() ) } diff --git a/src/main/kotlin/service/domain/AudioDomainService.kt b/src/main/kotlin/service/domain/AudioDomainService.kt index 62cb8b4..954c2d8 100644 --- a/src/main/kotlin/service/domain/AudioDomainService.kt +++ b/src/main/kotlin/service/domain/AudioDomainService.kt @@ -35,6 +35,9 @@ @Transactional fun findAllByOrderedSlugs(slugs: List): List { + if (slugs.isEmpty()) { + return emptyList() + } val slug2idx = slugs.mapIndexed { index, slug -> slug to index }.toMap() return audioRepository.findAllBySlugIn(slugs) .sortedBy { slug2idx[it.slug] } diff --git a/src/main/kotlin/utils/SqlUtils.kt b/src/main/kotlin/utils/SqlUtils.kt index 95a1263..49745e7 100644 --- a/src/main/kotlin/utils/SqlUtils.kt +++ b/src/main/kotlin/utils/SqlUtils.kt @@ -16,6 +16,7 @@ class SelectSQLBuilder(tableClass: KClass) { private val tableName: String = tableClass.findAnnotation()?.name ?: throw IllegalArgumentException() + private val joinTableMap : MutableMap = mutableMapOf() private val sql: SQL = SQL().FROM(tableName) private var columns: String = tableClass.memberProperties @@ -25,12 +26,33 @@ fun leftOuterJoin( tableClass: KClass, cond: String, - prefix: String? = null + ): SelectSQLBuilder { + return leftOuterJoin(tableClass, cond, Unit::class); + } + + fun leftOuterJoin( + tableClass: KClass, + cond: String, + parentTableClass: KClass ): SelectSQLBuilder { val joinTableName = tableClass.findAnnotation
()?.name ?: throw IllegalArgumentException() + val col : String + if (parentTableClass == Unit::class) { + col = joinTableName + } else { + val joinParentTableName = parentTableClass.findAnnotation
()?.name + ?: throw IllegalArgumentException() + if (joinTableMap.containsKey(joinParentTableName)) { + col = "${joinTableMap[joinParentTableName]}__${joinTableName}" + joinTableMap.put(joinTableName, col) + } else { + throw IllegalArgumentException() + } + } + joinTableMap.put(joinTableName, col) columns += "," + tableClass.memberProperties.mapNotNull { it.findAnnotation()?.name } - .joinToString(separator = ",") { "${joinTableName}.${it} AS ${prefix ?: joinTableName}__${it}" } + .joinToString(separator = ",") { "${joinTableName}.${it} AS ${col}__${it}" } sql.LEFT_OUTER_JOIN("${joinTableName} ON ${cond}") return this } diff --git a/src/main/resources/mapper/mapper.xml b/src/main/resources/mapper/mapper.xml index 39ff746..c9d9730 100644 --- a/src/main/resources/mapper/mapper.xml +++ b/src/main/resources/mapper/mapper.xml @@ -7,6 +7,7 @@ + @@ -25,6 +26,7 @@ + diff --git a/src/main/resources/static/bremer/login.html b/src/main/resources/static/bremer/login.html index 7dc8666..1951708 100644 --- a/src/main/resources/static/bremer/login.html +++ b/src/main/resources/static/bremer/login.html @@ -1,7 +1,6 @@ - @@ -24,7 +23,7 @@
ID
-
パスワード
+
パスワード