1package sql
2
3import (
4 "context"
5 "time"
6
7 "gorm.io/gorm"
8
9 "git.sr.ht/~gabrielgio/img/pkg/database/repository"
10 "git.sr.ht/~gabrielgio/img/pkg/list"
11)
12
13type (
14 Media struct {
15 gorm.Model
16 Name string `gorm:"not null"`
17 Path string `gorm:"not null;unique"`
18 PathHash string `gorm:"not null;unique"`
19 MIMEType string `gorm:"not null"`
20 }
21
22 MediaEXIF struct {
23 gorm.Model
24 Width *float64
25 Height *float64
26 MediaID uint `gorm:"not null"`
27 Media Media
28 Description *string
29 Camera *string
30 Maker *string
31 Lens *string
32 DateShot *time.Time
33 Exposure *float64
34 Aperture *float64
35 Iso *int64
36 FocalLength *float64
37 Flash *int64
38 Orientation *int64
39 ExposureProgram *int64
40 GPSLatitude *float64
41 GPSLongitude *float64
42 }
43
44 MediaThumbnail struct {
45 gorm.Model
46 Path string
47 MediaID uint `gorm:"not null"`
48 Media Media
49 }
50
51 MediaAlbum struct {
52 gorm.Model
53 ParentID *uint
54 Parent *MediaAlbum
55 Name string
56 Path string `gorm:"not null; unique"`
57 }
58
59 MediaAlbumFile struct {
60 gorm.Model
61 MediaID uint
62 Media Media
63 AlbumID uint
64 Album MediaAlbum
65 }
66
67 MediaRepository struct {
68 db *gorm.DB
69 }
70)
71
72var _ repository.MediaRepository = &MediaRepository{}
73
74func (m *Media) ToModel() *repository.Media {
75 return &repository.Media{
76 ID: m.ID,
77 Path: m.Path,
78 PathHash: m.PathHash,
79 Name: m.Name,
80 MIMEType: m.MIMEType,
81 }
82}
83
84func (m *MediaEXIF) ToModel() *repository.MediaEXIF {
85 return &repository.MediaEXIF{
86 Height: m.Height,
87 Width: m.Width,
88 Description: m.Description,
89 Camera: m.Camera,
90 Maker: m.Maker,
91 Lens: m.Lens,
92 DateShot: m.DateShot,
93 Exposure: m.Exposure,
94 Aperture: m.Aperture,
95 Iso: m.Iso,
96 FocalLength: m.FocalLength,
97 Flash: m.Flash,
98 Orientation: m.Orientation,
99 ExposureProgram: m.ExposureProgram,
100 GPSLatitude: m.GPSLatitude,
101 GPSLongitude: m.GPSLongitude,
102 }
103}
104
105func (a *MediaAlbum) ToModel() *repository.Album {
106 return &repository.Album{
107 ID: a.ID,
108 Name: a.Name,
109 Path: a.Path,
110 }
111}
112
113func (m *MediaThumbnail) ToModel() *repository.MediaThumbnail {
114 return &repository.MediaThumbnail{
115 Path: m.Path,
116 }
117}
118
119func NewMediaRepository(db *gorm.DB) *MediaRepository {
120 return &MediaRepository{
121 db: db,
122 }
123}
124
125func (self *MediaRepository) Create(ctx context.Context, createMedia *repository.CreateMedia) error {
126 media := &Media{
127 Name: createMedia.Name,
128 Path: createMedia.Path,
129 PathHash: createMedia.PathHash,
130 MIMEType: createMedia.MIMEType,
131 }
132
133 result := self.db.
134 WithContext(ctx).
135 Create(media)
136 if result.Error != nil {
137 return result.Error
138 }
139
140 return nil
141}
142
143func (self *MediaRepository) Exists(ctx context.Context, path string) (bool, error) {
144 var exists bool
145 result := self.db.
146 WithContext(ctx).
147 Model(&Media{}).
148 Select("count(id) > 0").
149 Where("path_hash = ?", path).
150 Find(&exists)
151
152 if result.Error != nil {
153 return false, result.Error
154 }
155
156 return exists, nil
157}
158
159func (self *MediaRepository) List(ctx context.Context, pagination *repository.Pagination) ([]*repository.Media, error) {
160 medias := make([]*Media, 0)
161 result := self.db.
162 WithContext(ctx).
163 Model(&Media{}).
164 Offset(pagination.Page * pagination.Size).
165 Limit(pagination.Size).
166 Where("path like '" + pagination.Path + "%'").
167 Order("COALESCE (media_exifs.date_shot, media.created_at) DESC").
168 InnerJoins("INNER JOIN media_exifs ON media_exifs.media_id = media.id").
169 Find(&medias)
170
171 if result.Error != nil {
172 return nil, result.Error
173 }
174
175 m := list.Map(medias, func(s *Media) *repository.Media {
176 return s.ToModel()
177 })
178
179 return m, nil
180}
181
182func (self *MediaRepository) Get(ctx context.Context, pathHash string) (*repository.Media, error) {
183 m := &Media{}
184 result := self.db.
185 WithContext(ctx).
186 Model(&Media{}).
187 Where("path_hash = ?", pathHash).
188 Limit(1).
189 Take(m)
190
191 if result.Error != nil {
192 return nil, result.Error
193 }
194
195 return m.ToModel(), nil
196}
197
198func (self *MediaRepository) GetPath(ctx context.Context, pathHash string) (string, error) {
199 var path string
200 result := self.db.
201 WithContext(ctx).
202 Model(&Media{}).
203 Select("path").
204 Where("path_hash = ?", pathHash).
205 Limit(1).
206 Find(&path)
207
208 if result.Error != nil {
209 return "", result.Error
210 }
211
212 return path, nil
213}
214
215func (self *MediaRepository) GetThumbnailPath(ctx context.Context, pathHash string) (string, error) {
216 var path string
217 result := self.db.
218 WithContext(ctx).
219 Model(&Media{}).
220 Select("media_thumbnails.path").
221 Joins("left join media_thumbnails on media.id = media_thumbnails.media_id").
222 Where("media.path_hash = ?", pathHash).
223 Limit(1).
224 Find(&path)
225
226 if result.Error != nil {
227 return "", result.Error
228 }
229
230 return path, nil
231}
232
233func (m *MediaRepository) GetEXIF(ctx context.Context, mediaID uint) (*repository.MediaEXIF, error) {
234 exif := &MediaEXIF{}
235 result := m.db.
236 WithContext(ctx).
237 Model(&Media{}).
238 Where("media_id = ?", mediaID).
239 Limit(1).
240 Take(m)
241
242 if result.Error != nil {
243 return nil, result.Error
244 }
245
246 return exif.ToModel(), nil
247}
248
249func (s *MediaRepository) CreateEXIF(ctx context.Context, id uint, info *repository.MediaEXIF) error {
250 media := &MediaEXIF{
251 MediaID: id,
252 Width: info.Width,
253 Height: info.Height,
254 Description: info.Description,
255 Camera: info.Camera,
256 Maker: info.Maker,
257 Lens: info.Lens,
258 DateShot: info.DateShot,
259 Exposure: info.Exposure,
260 Aperture: info.Aperture,
261 Iso: info.Iso,
262 FocalLength: info.FocalLength,
263 Flash: info.Flash,
264 Orientation: info.Orientation,
265 ExposureProgram: info.ExposureProgram,
266 GPSLatitude: info.GPSLatitude,
267 GPSLongitude: info.GPSLongitude,
268 }
269
270 result := s.db.
271 WithContext(ctx).
272 Create(media)
273 if result.Error != nil {
274 return result.Error
275 }
276
277 return nil
278}
279
280func (r *MediaRepository) ListEmptyEXIF(ctx context.Context, pagination *repository.Pagination) ([]*repository.Media, error) {
281 medias := make([]*Media, 0)
282 result := r.db.
283 WithContext(ctx).
284 Model(&Media{}).
285 Joins("left join media_exifs on media.id = media_exifs.media_id").
286 Where("media_exifs.media_id IS NULL AND media.path like '" + pagination.Path + "%'").
287 Offset(pagination.Page * pagination.Size).
288 Limit(pagination.Size).
289 Order("media.created_at DESC").
290 Find(&medias)
291
292 if result.Error != nil {
293 return nil, result.Error
294 }
295
296 m := list.Map(medias, func(s *Media) *repository.Media {
297 return s.ToModel()
298 })
299
300 return m, nil
301}
302
303func (r *MediaRepository) ListEmptyThumbnail(ctx context.Context, pagination *repository.Pagination) ([]*repository.Media, error) {
304 medias := make([]*Media, 0)
305 result := r.db.
306 WithContext(ctx).
307 Model(&Media{}).
308 Joins("left join media_thumbnails on media.id = media_thumbnails.media_id").
309 Where("media_thumbnails.media_id IS NULL AND media.path like '" + pagination.Path + "%'").
310 Offset(pagination.Page * pagination.Size).
311 Limit(pagination.Size).
312 Order("media.created_at DESC").
313 Find(&medias)
314
315 if result.Error != nil {
316 return nil, result.Error
317 }
318
319 m := list.Map(medias, func(s *Media) *repository.Media {
320 return s.ToModel()
321 })
322
323 return m, nil
324}
325
326func (m *MediaRepository) GetThumbnail(ctx context.Context, mediaID uint) (*repository.MediaThumbnail, error) {
327 thumbnail := &MediaThumbnail{}
328 result := m.db.
329 WithContext(ctx).
330 Model(&Media{}).
331 Where("media_id = ?", mediaID).
332 Limit(1).
333 Take(m)
334
335 if result.Error != nil {
336 return nil, result.Error
337 }
338
339 return thumbnail.ToModel(), nil
340}
341
342func (m *MediaRepository) CreateThumbnail(ctx context.Context, mediaID uint, thumbnail *repository.MediaThumbnail) error {
343 media := &MediaThumbnail{
344 MediaID: mediaID,
345 Path: thumbnail.Path,
346 }
347
348 result := m.db.
349 WithContext(ctx).
350 Create(media)
351 if result.Error != nil {
352 return result.Error
353 }
354
355 return nil
356}
357
358func (r *MediaRepository) ListEmptyAlbums(ctx context.Context, pagination *repository.Pagination) ([]*repository.Media, error) {
359 medias := make([]*Media, 0)
360 result := r.db.
361 WithContext(ctx).
362 Model(&Media{}).
363 Joins("left join media_album_files on media.id = media_album_files.media_id").
364 Where("media_album_files.media_id IS NULL").
365 Offset(pagination.Page * pagination.Size).
366 Limit(pagination.Size).
367 Order("media.created_at DESC").
368 Find(&medias)
369
370 if result.Error != nil {
371 return nil, result.Error
372 }
373
374 m := list.Map(medias, func(s *Media) *repository.Media {
375 return s.ToModel()
376 })
377
378 return m, nil
379}
380
381func (m *MediaRepository) ExistsAlbumByAbsolutePath(ctx context.Context, path string) (bool, error) {
382 var exists bool
383 result := m.db.
384 WithContext(ctx).
385 Model(&MediaAlbum{}).
386 Select("count(id) > 0").
387 Where("path = ?", path).
388 Find(&exists)
389
390 if result.Error != nil {
391 return false, result.Error
392 }
393
394 return exists, nil
395}
396
397func (r *MediaRepository) GetAlbumByAbsolutePath(ctx context.Context, path string) (*repository.Album, error) {
398 m := &MediaAlbum{}
399 result := r.db.
400 WithContext(ctx).
401 Model(&MediaAlbum{}).
402 Where("path = ?", path).
403 Limit(1).
404 Take(m)
405
406 if result.Error != nil {
407 return nil, result.Error
408 }
409
410 return m.ToModel(), nil
411}
412
413func (r *MediaRepository) GetAlbum(ctx context.Context, albumID uint) (*repository.Album, error) {
414 m := &MediaAlbum{}
415 result := r.db.
416 WithContext(ctx).
417 Model(&MediaAlbum{}).
418 Where("id = ?", albumID).
419 Limit(1).
420 Take(m)
421
422 if result.Error != nil {
423 return nil, result.Error
424 }
425
426 return m.ToModel(), nil
427}
428
429func (m *MediaRepository) CreateAlbum(ctx context.Context, createAlbum *repository.CreateAlbum) (*repository.Album, error) {
430 album := &MediaAlbum{
431 ParentID: createAlbum.ParentID,
432 Name: createAlbum.Name,
433 Path: createAlbum.Path,
434 }
435
436 result := m.db.
437 WithContext(ctx).
438 Create(album)
439 if result.Error != nil {
440 return nil, result.Error
441 }
442
443 return album.ToModel(), nil
444}
445
446func (m *MediaRepository) CreateAlbumFile(ctx context.Context, createAlbumFile *repository.CreateAlbumFile) error {
447 albumFile := &MediaAlbumFile{
448 MediaID: createAlbumFile.MediaID,
449 AlbumID: createAlbumFile.AlbumID,
450 }
451
452 result := m.db.
453 WithContext(ctx).
454 Create(albumFile)
455 if result.Error != nil {
456 return result.Error
457 }
458
459 return nil
460}
461
462func (m *MediaRepository) ListAlbums(ctx context.Context, albumID uint) ([]*repository.Album, error) {
463 albums := make([]*MediaAlbum, 0)
464
465 result := m.db.
466 WithContext(ctx).
467 Model(&MediaAlbum{}).
468 Where("parent_id = ?", albumID).
469 Find(&albums)
470
471 if result.Error != nil {
472 return nil, result.Error
473 }
474
475 return list.Map(albums, func(a *MediaAlbum) *repository.Album {
476 return a.ToModel()
477 }), nil
478}