Remove added media

[livrasand]

Hello friend, is it possible to add the function to delete the added multimedia, for example, now it is possible to add an image or a video, but is it possible to only delete an image or a video without having to restore the entire publication?

[darioragusa]

Hi, sorry for the delay, it should be possible. When I have some spare time I'll work on it

[livrasand]

Brilliant!! I will wait anxiously

[livrasand]

Hello friend, I took the audacity to start this function, I already have progress, tomorrow I finish.

[livrasand]

Hello, how did it go with this? Have you been able to get media removed?

[darioragusa]

Hi, when you said

I already have progress, tomorrow I finish.

I thought you would make a pull request, then I forgot about it 😅

[livrasand]

Hello friend, I lost our conversation, today I remembered this request and I wanted to come take a look and I see that you answered me. I think the following code could help with that module:

Imports System.IO
Imports System.Data.SQLite

Module DeleteMedia
    Sub DeleteImg(doc As Document, mediaId As Integer)
        Dim dbPath As String = doc.pub.path & "\" & doc.pub.name & ".db"
        If Not File.Exists(dbPath) Then Return

        Using SQLCon As New SQLiteConnection(String.Format("Data Source = {0}", dbPath))
            Dim deleteMultimediaQuery As String = "DELETE FROM Multimedia WHERE MultimediaId = @MediaId"
            Dim deleteDocumentMultimediaQuery As String = "DELETE FROM DocumentMultimedia WHERE DocumentId = @DocumentId AND MultimediaId = @MediaId"

            SQLCon.Open()

            Dim deleteMultimediaCMD As New SQLiteCommand(deleteMultimediaQuery, SQLCon)
            deleteMultimediaCMD.Parameters.AddWithValue("@MediaId", mediaId)
            deleteMultimediaCMD.ExecuteNonQuery()

            Dim deleteDocumentMultimediaCMD As New SQLiteCommand(deleteDocumentMultimediaQuery, SQLCon)
            deleteDocumentMultimediaCMD.Parameters.AddWithValue("@DocumentId", CStr(doc.documentId))
            deleteDocumentMultimediaCMD.Parameters.AddWithValue("@MediaId", mediaId)
            deleteDocumentMultimediaCMD.ExecuteNonQuery()

            SQLCon.Close()
        End Using
    End Sub

    Sub DeleteVideo(doc As Document, mediaId As Integer)
        Dim dbPath As String = doc.pub.path & "\" & doc.pub.name & ".db"
        If Not File.Exists(dbPath) Then Return

        Using SQLCon As New SQLiteConnection(String.Format("Data Source = {0}", dbPath))
            Dim deleteMultimediaQuery As String = "DELETE FROM Multimedia WHERE MultimediaId = @MediaId"
            Dim deleteDocumentMultimediaQuery As String = "DELETE FROM DocumentMultimedia WHERE DocumentId = @DocumentId AND MultimediaId = @MediaId"

            SQLCon.Open()

            Dim deleteMultimediaCMD As New SQLiteCommand(deleteMultimediaQuery, SQLCon)
            deleteMultimediaCMD.Parameters.AddWithValue("@MediaId", mediaId)
            deleteMultimediaCMD.ExecuteNonQuery()

            Dim deleteDocumentMultimediaCMD As New SQLiteCommand(deleteDocumentMultimediaQuery, SQLCon)
            deleteDocumentMultimediaCMD.Parameters.AddWithValue("@DocumentId", CStr(doc.documentId))
            deleteDocumentMultimediaCMD.Parameters.AddWithValue("@MediaId", mediaId)
            deleteDocumentMultimediaCMD.ExecuteNonQuery()

            SQLCon.Close()
        End Using
    End Sub
End Module

The general structure is similar to the add media sub-procedures, but instead of inserting, here we are executing queries to delete records related to media and documents in the database.

[livrasand]

And this should work to get the media data from the database and another function called Main to print the results to the console (you can change it to display it on your main box). In addition, a Multimedia class is defined to store the information of each multimedia element.

Imports System.Data.SQLite

Module ShowMultimedia
    Sub Main()
        Dim dbPath As String = "ruta/a/tu/base/de/datos.db" ' Cambia esto a la ruta de tu base de datos SQLite

        Dim multimediaList As List(Of Multimedia) = GetMultimediaFromDatabase(dbPath)

        For Each media As Multimedia In multimediaList
            Console.WriteLine($"Media ID: {media.MultimediaId}")
            Console.WriteLine($"Data Type: {media.DataType}")
            Console.WriteLine($"Major Type: {media.MajorType}")
            Console.WriteLine($"Minor Type: {media.MinorType}")
            Console.WriteLine($"MIME Type: {media.MimeType}")
            Console.WriteLine($"Caption: {media.Caption}")
            Console.WriteLine($"File Path: {media.FilePath}")
            Console.WriteLine($"Category Type: {media.CategoryType}")
            Console.WriteLine()
        Next
    End Sub

    Function GetMultimediaFromDatabase(dbPath As String) As List(Of Multimedia)
        Dim multimediaList As New List(Of Multimedia)

        If Not System.IO.File.Exists(dbPath) Then
            Return multimediaList
        End If

        Using sqlCon As New SQLiteConnection(String.Format("Data Source = {0}", dbPath))
            Dim query As String = "SELECT MultimediaId, DataType, MajorType, MinorType, MimeType, Caption, FilePath, CategoryType FROM multimedia"

            sqlCon.Open()
            Using command = sqlCon.CreateCommand()
                command.CommandText = query
                Using reader = command.ExecuteReader()
                    While reader.Read()
                        Dim media As New Multimedia()
                        media.MultimediaId = reader.GetInt32(0)
                        media.DataType = reader.GetInt32(1)
                        media.MajorType = reader.GetInt32(2)
                        media.MinorType = reader.GetInt32(3)
                        media.MimeType = reader.GetString(4)
                        media.Caption = reader.GetString(5)
                        media.FilePath = reader.GetString(6)
                        media.CategoryType = reader.GetInt32(7)
                        multimediaList.Add(media)
                    End While
                End Using
            End Using
            sqlCon.Close()
        End Using

        Return multimediaList
    End Function
End Module

Class Multimedia
    Public MultimediaId As Integer
    Public DataType As Integer
    Public MajorType As Integer
    Public MinorType As Integer
    Public MimeType As String
    Public Caption As String
    Public FilePath As String
    Public CategoryType As Integer
End Class

[livrasand]

If you get it done and functional, I'd like to clone your project in Go, so it's available on Windows, macOS, and Linux. I'm sure many users will appreciate it. The Playlist functions may not work as many would expect.