148 lines
8 KiB
JavaScript
148 lines
8 KiB
JavaScript
// @ts-check
|
|
|
|
const {defineEventHandler, getQuery, getValidatedQuery, sendRedirect, createError, getValidatedRouterParams, getCookie, getRequestHeader} = require("h3")
|
|
const {router, db, sync} = require("../passthrough")
|
|
|
|
/** @type {import("../pug-sync")} */
|
|
const pugSync = sync.require("../pug-sync")
|
|
|
|
/** @type {import("./schema")} */
|
|
const schema = sync.require("./schema")
|
|
|
|
const sqls = {
|
|
album_grid: "SELECT item.*, count(*) AS track_count, iif(sum(duration) > 3600, cast(total(duration)/3600 AS INTEGER) || 'h ' || cast(total(duration)/60%60 AS INTEGER) || 'm', cast(total(duration)/60 AS INTEGER) || 'm') AS total_duration FROM item INNER JOIN track USING (account, item_id) {JOIN TAG} WHERE account = ? {WHERE} GROUP BY item_id {ORDER}",
|
|
album_list: "SELECT item.*, count(*) AS track_count, iif(sum(duration) > 3600, cast(total(duration)/3600 AS INTEGER) || 'h ' || cast(total(duration)/60%60 AS INTEGER) || 'm', cast(total(duration)/60 AS INTEGER) || 'm') AS total_duration FROM item INNER JOIN track USING (account, item_id) {JOIN TAG} WHERE account = ? {WHERE} GROUP BY item_id ORDER BY band_url, band_name COLLATE NOCASE, item_title COLLATE NOCASE",
|
|
artist_grid: "SELECT band_name, item_title, count(DISTINCT item_id) AS album_count, group_concat(DISTINCT band_url) AS labels, count(*) AS track_count, iif(sum(duration) > 3600, cast(total(duration)/3600 AS INTEGER) || 'h ' || cast(total(duration)/60%60 AS INTEGER) || 'm', cast(total(duration)/60 AS INTEGER) || 'm') AS total_duration FROM item INNER JOIN track USING (account, item_id) {JOIN TAG} WHERE account = ? {WHERE} GROUP BY band_name ORDER BY band_name COLLATE NOCASE",
|
|
artist_list: "SELECT band_name, item_title, count(DISTINCT item_id) AS album_count, band_url, count(*) AS track_count, iif(sum(duration) > 3600, cast(total(duration)/3600 AS INTEGER) || 'h ' || cast(total(duration)/60%60 AS INTEGER) || 'm', cast(total(duration)/60 AS INTEGER) || 'm') AS total_duration FROM item INNER JOIN track USING (account, item_id) {JOIN TAG} WHERE account = ? {WHERE} GROUP BY band_name ORDER BY band_name COLLATE NOCASE",
|
|
label_grid: "SELECT item_title, iif(count(DISTINCT band_name) = 1, band_name, band_url) AS display_name, band_url, count(DISTINCT item_id) AS album_count, count(DISTINCT band_name) AS artist_count, count(*) AS track_count, iif(sum(duration) > 3600, cast(total(duration)/3600 AS INTEGER) || 'h ' || cast(total(duration)/60%60 AS INTEGER) || 'm', cast(total(duration)/60 AS INTEGER) || 'm') AS total_duration FROM item INNER JOIN track USING (account, item_id) {JOIN TAG} WHERE account = ? {WHERE} GROUP BY band_url ORDER BY display_name COLLATE NOCASE",
|
|
label_list: "SELECT item_title, iif(count(DISTINCT band_name) = 1, band_name, band_url) AS display_name, band_url, count(DISTINCT item_id) AS album_count, count(DISTINCT band_name) AS artist_count, count(*) AS track_count, iif(sum(duration) > 3600, cast(total(duration)/3600 AS INTEGER) || 'h ' || cast(total(duration)/60%60 AS INTEGER) || 'm', cast(total(duration)/60 AS INTEGER) || 'm') AS total_duration FROM item INNER JOIN track USING (account, item_id) {JOIN TAG} WHERE account = ? {WHERE} GROUP BY band_url ORDER BY display_name COLLATE NOCASE",
|
|
tag_grid: "SELECT tag, count(*) AS count FROM item_tag INNER JOIN item USING (account, item_id) WHERE account = ? {WHERE} GROUP BY tag, band_url ORDER BY count DESC",
|
|
tag_list: "SELECT item_title, tag, count(*) AS labels, sum(count) AS albums FROM (SELECT item_title, tag, count(*) AS count FROM item_tag INNER JOIN item USING (account, item_id) WHERE account = ? {WHERE} GROUP BY tag, band_url) GROUP BY tag ORDER BY labels DESC",
|
|
track_list: "SELECT * FROM track INNER JOIN item USING (account, item_id) {JOIN TAG} WHERE account = ? {WHERE} ORDER BY band_url, item_title COLLATE NOCASE, track_number"
|
|
}
|
|
|
|
function loadPreviews(locals, field, number, whereClause, account, filter_field, filter, filter_fuzzy) {
|
|
const params = [account, number]
|
|
let sql = `SELECT ${field}, item_id, item_title, item_type, item_url, item_art_url FROM (SELECT ${field}, item_title, item_id, item_type, item_url, item_art_url, row_number() OVER (PARTITION BY ${field} ORDER BY purchased DESC) AS row_number FROM item {JOIN TAG} WHERE account = ? {WHERE}) WHERE row_number <= ?`
|
|
sql = sql.replace("{WHERE}", whereClause)
|
|
if (whereClause) {
|
|
if (filter_field === "band_url" || filter_fuzzy) {
|
|
params.splice(1, 0, `%${filter}%`)
|
|
} else {
|
|
params.splice(1, 0, filter)
|
|
}
|
|
}
|
|
if (filter_field === "tag" && filter) {
|
|
sql = sql.replace("{JOIN TAG}", "INNER JOIN item_tag USING (account, item_id)")
|
|
} else {
|
|
sql = sql.replace("{JOIN TAG}", "")
|
|
}
|
|
|
|
const previews = db.prepare(sql).all(params)
|
|
// TODO: performance?
|
|
for (const item of locals.items) {
|
|
item.previews = []
|
|
for (const preview of previews) {
|
|
if (preview[field] === item[field]) {
|
|
item.previews.push(preview)
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
pugSync.addGlobals({
|
|
getAlbumCoverAttributes(event, item) {
|
|
/** @type {any} */
|
|
let attributes = {
|
|
"hx-get": `/api/play/${item.item_type}/${item.item_id}`,
|
|
"hx-target": "#player",
|
|
"hx-select": "#player",
|
|
"hx-indicator": "null",
|
|
"hx-push-url": "false"
|
|
}
|
|
if (getCookie(event, "bcex-inline-player-disabled") === "true") {
|
|
attributes = {"target": "_blank"}
|
|
}
|
|
attributes.href = item.item_url
|
|
return attributes
|
|
}
|
|
})
|
|
|
|
router.get("/:account/", defineEventHandler({
|
|
onBeforeResponse: pugSync.compressResponse,
|
|
handler: async event => {
|
|
const {account} = await getValidatedRouterParams(event, schema.schema.account.parse)
|
|
try {
|
|
var {arrange, shape, filter, filter_field, filter_fuzzy, show} = await getValidatedQuery(event, schema.schema.appQuery.parse)
|
|
if (filter_field === "why" && arrange !== "album") throw new Error("filter not compatible with arrangement")
|
|
} catch (e) {
|
|
return sendRedirect(event, "?arrange=album&shape=grid", 302)
|
|
}
|
|
const query = getQuery(event)
|
|
if (arrange === "track") {
|
|
shape = "list"
|
|
query.shape = "list"
|
|
}
|
|
const mode = `${arrange}_${shape}`
|
|
const params = [account]
|
|
let sql = sqls[mode]
|
|
let whereClause = ""
|
|
if (filter_field && filter) {
|
|
let operator = "="
|
|
if (filter_field === "why") {
|
|
operator = "!="
|
|
params.push("")
|
|
sql = sql.replace("{ORDER}", "ORDER BY purchased DESC")
|
|
} else if (filter_field === "band_url" || filter_fuzzy) {
|
|
operator = "LIKE"
|
|
params.push(`%${filter}%`)
|
|
sql = sql.replace("{ORDER}", "ORDER BY item_title COLLATE NOCASE")
|
|
} else {
|
|
params.push(filter)
|
|
sql = sql.replace("{ORDER}", "ORDER BY item_title COLLATE NOCASE")
|
|
}
|
|
whereClause = `AND ${filter_field} ${operator} ?`
|
|
} else {
|
|
sql = sql.replace("{ORDER}", "ORDER BY purchased DESC")
|
|
}
|
|
sql = sql.replace("{WHERE}", whereClause)
|
|
if (filter_field === "tag" && filter) {
|
|
sql = sql.replace("{JOIN TAG}", "INNER JOIN item_tag USING (account, item_id)")
|
|
} else {
|
|
sql = sql.replace("{JOIN TAG}", "")
|
|
}
|
|
if (mode === "tag_grid" && ((!filter_field || !filter) || filter_field === "tag")) {
|
|
sql = `SELECT tag, count(*) AS count FROM (${sql}) GROUP BY tag ORDER BY count DESC`
|
|
}
|
|
try {
|
|
const prepared = db.prepare(sql)
|
|
if (mode === "tag_grid") {
|
|
prepared.raw()
|
|
}
|
|
var items = prepared.all(params)
|
|
} catch (e) {
|
|
console.error(sql, params)
|
|
throw e
|
|
}
|
|
|
|
show ||= getRequestHeader(event, "BCEX-Show")
|
|
const itemWarningLimit = arrange === "track" ? 5000 : 1000
|
|
if (items.length >= itemWarningLimit && !show) {
|
|
return pugSync.render(event, "too-many-items.pug", {itemCount: items.length, account, query})
|
|
}
|
|
|
|
const locals = {
|
|
items,
|
|
account,
|
|
query,
|
|
}
|
|
if (mode === "artist_grid") {
|
|
loadPreviews(locals, "band_name", 4, whereClause, account, filter_field, filter, filter_fuzzy)
|
|
} else if (mode === "label_grid") {
|
|
loadPreviews(locals, "band_url", 6, whereClause, account, filter_field, filter, filter_fuzzy)
|
|
}
|
|
// if there are any untagged items then we don't have full track data
|
|
locals.hasFullTrackData = !db.prepare("SELECT * FROM item LEFT JOIN item_tag USING (account, item_id) WHERE account = ? AND tag IS NULL").get(account)
|
|
return pugSync.render(event, `${arrange}_${shape}.pug`, locals)
|
|
}
|
|
}))
|