Source file queryArtist.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
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
(*****************************************************************************)
(*                                                                           *)
(*  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 ArtistMusicQuery = struct
  module Column = struct
    let fartist field = Printf.sprintf "a%s" field
    let fmusic field = Printf.sprintf "m%s" field
    let artist_id = fartist Artist.Column.id
    let roles = fartist "roles"
    let music_id = fmusic MusicFile.Column.id
    let album_id = fmusic MusicFile.Column.album_id
    let duration = fmusic MusicFile.Column.duration
    let play_count = fmusic MusicFile.Column.play_count
    let size = fmusic MusicFile.Column.size
    let music_rating = fmusic MusicFile.Column.rating
    let played_at = fmusic MusicFile.Column.played_at
    let artists = List.map fartist Artist.Column.all
    let all = artists @ List.map fmusic MusicFile.Column.all @ [ roles ]

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

  let table_name = "skhc_artists_musics"
  let fp = Printf.sprintf "%s.%s" table_name
  let p_artists = List.map fp Column.artists
  let p_album_id = fp Column.album_id
  let p_artist_id = fp Column.artist_id
  let p_roles = fp Column.roles
  let p_music_id = fp Column.music_id
  let p_duration = fp Column.duration
  let p_play_count = fp Column.play_count
  let p_size = fp Column.size
  let p_music_rating = fp Column.music_rating
  let p_played_at = fp Column.played_at

  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
    let group_by = [ Artist.p_id; MusicFile.p_id ] in
    let group_by = String.concat ", " group_by in
    Printf.sprintf
      {|SELECT DISTINCT %s FROM %s
        INNER JOIN %s ON %s = %s
        INNER JOIN %s ON %s = %s
        GROUP BY %s|}
      columns MusicFile.table_name
      (* INNER JOIN *)
      MusicParticipant.table_name MusicParticipant.p_music_id MusicFile.p_id
      (* INNER JOIN *)
      Artist.table_name Artist.p_id MusicParticipant.p_artist_id
      (* GROUP BY *)
      group_by
end

module ArtistAlbumsQuery = struct
  module Column = struct
    let id = "artist_id"
    let album_id = "album_id"
    let album_name = "album_name"
    let all = [ id; album_id; album_name ]

    let source =
      let fp = Printf.sprintf "%s.%s" in
      [
        fp Artist.table_name Artist.Column.id;
        fp Album.table_name Album.Column.id;
        fp Album.table_name Album.Column.name;
      ]
  end

  let table_name = "skhc_artists_albums"
  let fp = Printf.sprintf "%s.%s" table_name
  let p_artist_id = fp Column.id
  let p_album_name = fp Column.album_name
  let p_album_id = fp Column.album_id

  let sql_table =
    let columns =
      List.map2 (Printf.sprintf "%s as %s") Column.source Column.all
    in
    let columns = String.concat ", " columns in
    Printf.sprintf
      {|
        SELECT DISTINCT %s FROM %s
        INNER JOIN %s ON %s = %s
        INNER JOIN %s ON %s = %s
        WHERE %s = %u
        |}
      columns Artist.table_name
      (* INNER JOIN *)
      AlbumParticipant.table_name AlbumParticipant.p_artist_id Artist.p_id
      (* INNER JOIN *)
      Album.table_name Album.p_id AlbumParticipant.p_album_id
      (* WHERE *)
      AlbumParticipant.p_role
      Role.(to_int AlbumArtist)
end