Source file queryMusic.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
(*****************************************************************************)
(*                                                                           *)
(*  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 MusicPropsQuery = struct
  module Column = struct
    let id = "props_music_id"
    let properties = "properties"
    let all = [ id; properties ]
    let source = MusicFile.p_id
  end

  let table_name = "skhc_music_properties"
  let p_music_id = Printf.sprintf "%s.%s" table_name Column.id
  let p_properties = Printf.sprintf "%s.%s" table_name Column.properties

  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 LEFT JOIN %s ON %s = %s GROUP BY %s|}
      columns MusicFile.table_name
      (* LEFT JOIN *)
      MusicProperty.table_name MusicProperty.p_music_id MusicFile.p_id
      (* GROUP BY *)
      MusicFile.p_id
end

module MusicPartisQuery = struct
  module Column = struct
    let id = "parts_music_id"
    let roles = "roles"
    let all = [ id; roles ]
    let source = MusicFile.p_id
  end

  let table_name = "skhc_music_properties"
  let p_music_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
        LEFT JOIN %s ON %s = %s
        LEFT JOIN %s ON %s = %s
        GROUP BY %s|}
      columns MusicFile.table_name
      (* LEFT JOIN *)
      MusicParticipant.table_name MusicParticipant.p_music_id MusicFile.p_id
      (* LEFT JOIN *)
      Artist.table_name Artist.p_id MusicParticipant.p_artist_id
      (* GROUP BY *)
      MusicFile.p_id
end