Source file playlistMusics.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
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
(*****************************************************************************)
(*                                                                           *)
(*  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/.                 *)
(*                                                                           *)
(*****************************************************************************)

type t = { playlist_id : int64; music_id : int64 }

let table_name = "playlist_musics"

module Column = struct
  let playlist_id = "playlist_id"
  let music_id = "music_id"
end

module Prefix = struct
  let p = Printf.sprintf "%s.%s" table_name
  let playlist_id = p Column.playlist_id
  let music_id = p Column.music_id
end

let init ~playlist_id ~music_id = { playlist_id; music_id }

let sql_table =
  let open Column in
  Printf.sprintf
    {|CREATE TABLE IF NOT EXISTS %s (
    %s INTEGER NOT NULL,
    %s INTEGER NOT NULL,
    FOREIGN KEY (%s) REFERENCES %s (%s)
      ON UPDATE CASCADE
      ON DELETE CASCADE,
    FOREIGN KEY (%s) REFERENCES %s (%s)
      ON UPDATE CASCADE
      ON DELETE CASCADE
    );|}
    table_name playlist_id music_id playlist_id Playlist.table_name
    Playlist.primary_key music_id MusicFile.table_name MusicFile.primary_key

let bind_names t =
  let at = "@" in
  Sqlite3.Data.(
    Column.
      [ (at ^ playlist_id, INT t.playlist_id); (at ^ music_id, INT t.music_id) ]
  )

let columns statement offset =
  let playlist_id = Sqlite3.column_int64 statement offset in
  let music_id = Sqlite3.column_int64 statement (offset + 1) in
  ({ playlist_id; music_id }, 2)

let filter_map database func =
  let request = Printf.sprintf "SELECT * from %s;" table_name in
  let statement = Sqlite3.prepare database request in
  let queue = Queue.create () in
  let rc =
    Sqlite3.iter statement ~f:(fun _ ->
        (*HACK: use statement instead of data *)
        let music, _ = columns statement 0 in
        match func music with None -> () | Some music -> Queue.add music queue
    )
  in
  Util.bind_ok database rc @@ fun () -> Ok (List.of_seq (Queue.to_seq queue))

let all database = filter_map database Option.some

let playlist_find_from_id db id =
  let ( let* ) = Util.bind_ok db in
  let playlist_stats =
    Playlist.Stats.column_query ~id:MusicFile.p_id
      ~play_count:MusicFile.p_play_count ~duration:MusicFile.p_duration
      ~size:MusicFile.p_size ~rating:MusicFile.p_rating
  in
  let request =
    Printf.sprintf
      {|SELECT DISTINCT %s.*, %s from %s INNER JOIN %s ON %s = %s
      INNER JOIN %s ON %s = %s
      WHERE %s = ?;|}
      Playlist.table_name playlist_stats Playlist.table_name
      (* INNER JOIN *)
      table_name Playlist.Prefix.id Prefix.playlist_id
      (* INNER JOIN *)
      MusicFile.table_name MusicFile.p_id Prefix.music_id
      (* WHERE *)
      Prefix.playlist_id
  in
  let stmt = Sqlite3.prepare db request in
  let* () = Sqlite3.bind_int64 stmt 1 id in
  let queue = Queue.create () in
  let rc =
    Sqlite3.iter stmt ~f:(fun _ ->
        (*HACK: use statement instead of data *)
        let playlist, offset = Playlist.columns stmt 0 in
        let stats, _ = Playlist.Stats.columns stmt offset in
        Queue.add (playlist, stats) queue
    )
  in
  Util.bind_ok db rc @@ fun () -> Ok (Queue.take_opt queue)

let playlists_filter_map database func =
  let query =
    let playlist_stats =
      Playlist.Stats.column_query ~id:MusicFile.p_id
        ~play_count:MusicFile.p_play_count ~duration:MusicFile.p_duration
        ~size:MusicFile.p_size ~rating:MusicFile.p_rating
    in
    Printf.sprintf
      {|SELECT DISTINCT %s.*, %s from %s INNER JOIN %s ON %s = %s
        INNER JOIN %s ON %s = %s
        GROUP BY %s;|}
      Playlist.table_name playlist_stats Playlist.table_name
      (* INNER JOIN *)
      table_name Playlist.Prefix.id Prefix.playlist_id
      (* INNER JOIN *)
      MusicFile.table_name MusicFile.p_id Prefix.music_id
      (* GROUP BY *)
      Playlist.Prefix.id
  in
  let statement = Sqlite3.prepare database query in
  let queue = Queue.create () in
  let rc =
    Sqlite3.iter statement ~f:(fun _ ->
        let playlist, offset = Playlist.columns statement 0 in
        let stats, _ = Playlist.Stats.columns statement offset in
        match func (playlist, stats) with
        | Some s ->
            Queue.add s queue
        | None ->
            ()
    )
  in
  let list = queue |> Queue.to_seq |> List.of_seq in
  Util.bind_ok database rc @@ fun () -> Ok list

let playlists database = playlists_filter_map database Option.some

let insert database t =
  let ( let* ) = Util.bind_constraint_ok database in
  let request =
    let open Column in
    Printf.sprintf "INSERT INTO %s (%s, %s) VALUES (@%s, @%s);" table_name
      playlist_id music_id playlist_id music_id
  in
  let values = bind_names t in
  let statement = Sqlite3.prepare database request in
  let* () = Sqlite3.bind_names statement values in
  let* () = Sqlite3.step statement in
  let* () = Sqlite3.finalize statement in
  Ok ()

let remove database t =
  let ( let* ) = Util.bind_ok database in
  let statement =
    let open Column in
    Printf.sprintf "DELETE FROM %s WHERE %s = @%s AND %s = @%s;" table_name
      playlist_id playlist_id music_id music_id
  in
  let statement = Sqlite3.prepare database statement in
  let* () = Sqlite3.bind_names statement (bind_names t) in
  let* () = Sqlite3.step statement in
  let* () = Sqlite3.finalize statement in
  Ok ()

let musics database playlist =
  let ( let* ) = Util.bind_ok database in
  let ss = '\x01' in
  let sv = '\x02' in
  let group_concat =
    Util.sql_participants_format ~ss ~id:MusicParticipant.p_artist_id
      ~role:MusicParticipant.p_role ~name:Artist.p_name
  in
  let group_properties =
    Printf.sprintf "group_concat(%s || '%c' || %s, '%c')" MusicProperty.p_key sv
      MusicProperty.p_value ss
  in
  (* Use left join to not exclude musics without tags. *)
  let request =
    Printf.sprintf
      {|
    SELECT DISTINCT %s.*, %s.*, 
    %s name_roles,
    %s properties
    FROM %s 
    LEFT JOIN %s ON %s = %s 
    LEFT JOIN %s ON %s = %s 
    LEFT JOIN %s ON %s = %s 
    LEFT JOIN %s ON %s = %s
    LEFT JOIN %s ON %s = %s
    LEFT JOIN %s ON %s = %s
    LEFT JOIN %s ON %s = %s
    WHERE %s = ?1 
    GROUP BY %s
    ;
  |}
      MusicFile.table_name Album.table_name group_concat group_properties
      MusicFile.table_name AlbumParticipant.table_name MusicFile.p_album_id
      AlbumParticipant.p_album_id Album.table_name MusicFile.p_album_id
      Album.p_id MusicParticipant.table_name MusicFile.p_id
      MusicParticipant.p_music_id Artist.table_name MusicParticipant.p_artist_id
      Artist.p_id MusicProperty.table_name MusicFile.p_id
      MusicProperty.p_music_id table_name Prefix.music_id MusicFile.p_id
      Playlist.table_name Playlist.Prefix.id Prefix.playlist_id
      Playlist.Prefix.id MusicFile.p_id
  in
  let statement = Sqlite3.prepare database request in
  let* () = Sqlite3.bind_int64 statement 1 (Playlist.id playlist) in
  let queue = Queue.create () in
  let rc =
    Sqlite3.iter statement ~f:(fun _ ->
        let music_file, offset = MusicFile.columns statement 0 in
        let album, offset = Album.columns statement offset in
        let participants_blob = Sqlite3.column_text statement offset in
        let properties_blob = Sqlite3.column_text statement (offset + 1) in
        let participants = Util.sql_participants ~ss participants_blob in
        let properties = Util.sql_properties ~ss ~sv properties_blob in
        Queue.add (music_file, album, participants, properties) queue
    )
  in
  let musics = queue |> Queue.to_seq |> List.of_seq in
  Util.bind_ok database rc @@ fun () -> Ok musics

let playlist_id t = t.playlist_id
let music_id t = t.music_id

(* Test *)
let equal : t -> t -> bool = ( = )
let compare : t -> t -> int = Stdlib.compare