Source file queryAlbum.ml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
(*****************************************************************************)
(*                                                                           *)
(*  Copyright (C) 2026 Yves Ndiaye                                           *)
(*                                                                           *)
(* This Source Code Form is subject to the terms of the Mozilla Public       *)
(* License, v. 2.0. If a copy of the MPL was not distributed with this       *)
(* file, You can obtain one at https://mozilla.org/MPL/2.0/.                 *)
(*                                                                           *)
(*****************************************************************************)

module AlbumMusicQuery = struct
  module Column = struct
    let falbum field = Printf.sprintf "a%s" field
    let fmusic field = Printf.sprintf "m%s" field
    let albums = List.map falbum Album.Column.all
    let all = albums @ List.map fmusic MusicFile.Column.all

    let source =
      List.map (Printf.sprintf "%s.%s" Album.table_name) Album.Column.all
      @ List.map
          (Printf.sprintf "%s.%s" MusicFile.table_name)
          MusicFile.Column.all

    let music_id = fmusic MusicFile.Column.id
    let size = fmusic MusicFile.Column.size
    let play_count = fmusic MusicFile.Column.play_count
    let rating = fmusic MusicFile.Column.rating
    let duration = fmusic MusicFile.Column.duration
    let played_at = fmusic MusicFile.Column.played_at
  end

  let table_name = "skhc_album_musics"
  let fp = Printf.sprintf "%s.%s" table_name
  let p_albums = List.map fp Column.albums
  let p_all = List.map fp Column.all
  let p_album_id = fp (List.hd Column.albums)
  let p_music_id = fp Column.music_id
  let p_size = fp Column.size
  let p_play_count = fp Column.play_count
  let p_rating = fp Column.rating
  let p_duration = fp Column.duration
  let p_played_at = fp Column.played_at

  let sql_table =
    let columns =
      List.map2 (Printf.sprintf "%s as %s") Column.source Column.all
    in
    let columns = String.concat "," columns in
    (* No need of trainling; since the query will used as a subquery*)
    Printf.sprintf {|SELECT DISTINCT %s FROM %s INNER JOIN %s ON %s = %s|}
      columns Album.table_name
      (* INNER JOIN *)
      MusicFile.table_name MusicFile.p_album_id Album.p_id
end

module AlbumParticipantQuery = struct
  module Column = struct
    let id = Album.Column.id
    let roles = "roles"
    let all = [ id; roles ]
    let source = Album.p_id
  end

  let table_name = "skhc_album_participants"
  let p_album_id = Printf.sprintf "%s.%s" table_name Column.id
  let p_roles = Printf.sprintf "%s.%s" table_name Column.roles

  let sql_table group =
    let columns =
      List.map2 (Printf.sprintf "%s as %s") [ Column.source; group ] Column.all
    in
    let columns = String.concat ", " columns in
    Printf.sprintf
      {|SELECT %s FROM %s
        INNER JOIN %s ON %s = %s
        INNER JOIN %s ON %s = %s
        GROUP BY %s|}
      columns Album.table_name
      (* INNER JOIN *)
      AlbumParticipant.table_name AlbumParticipant.p_album_id Album.p_id
      (* INNER JOIN *)
      Artist.table_name Artist.p_id AlbumParticipant.p_artist_id
      (* GROUP BY *)
      Album.p_id
end