scan-vid/scan-vid.jl

# То же самое, но на julia
# Версия 1.0 (рабочая)

using Logging
using Printf
using Dates
using CRC32c
using JSON
using Base.Threads
using MySQL
using Printf, Dates# using DBInterface

# debuglogger = ConsoleLogger(stderr, Logging.Debug)
# global_logger(debuglogger)

# include( "./Humanize.jl")

# import humanbytes

include("Humanize.jl")

function valid_file(filename)
    valids = [".avi", ".mkv", ".mp4", ".webm", ".mpeg"]
    _, ext = splitext(filename)
    return ext in valids
end


# file_info function returns information from file
function file_info(filename)
    _, ext = splitext(filename)
    info = stat(filename)
    crc = CRC32c.crc32c(open(filename, "r"))
    m = Dict(
        "filename" => filename,
        "size" => info.size,
        "hsize" => humanbytes(info.size),
        "mtime" => info.mtime,
        "ctime" => info.ctime,
        "hmtime" => humandate(info.mtime),
        "hctime" => humandate(info.ctime),
        "ext" => ext,
        "crc"=> Int(crc),
    )
    return m
end

# возвращает индекс видео-потока
function get_video_stream(stream)
    for i in eachindex(stream)
        if stream[i]["codec_type"] == "video"
            return i
        end
    end
    return -1
end

# возвращает индекс аудио-потока, -1 при отсутствии
function get_audio_stream(stream)
    for i in eachindex(stream)
        if stream[i]["codec_type"] == "audio"
            return i
        end
    end
    return -1
end

# Потоки могут быть перепутаны, поэтму нужно сначала
# определить индекс видео-потока
function file_meta(filename)
    cmd = `ffprobe -v quiet -print_format json -show_format -show_streams "$filename"`
    data = read(cmd, String) |> JSON.parse
    n = get_video_stream(data["streams"])
    audio = get_audio_stream(data["streams"]) != -1
    dur = parse(Float64, data["format"]["duration"])
    hdur = Dates.unix2datetime(dur)
    hduration = Dates.format((hdur), "HH:MM:SS")

    res = Dict(
        "duration" => hdur,
        "hduration" => hduration,
	    "width" => data["streams"][n]["width"],
	    "height" => data["streams"][n]["height"],
        "bit_rate" => humanbitrate(data["streams"][n]["avg_frame_rate"]),
        "codec" => data["streams"][n]["codec_name"],
        "audio" => audio,
        "tags" => "",
        "type" => hduration < "00:10:00" ? "clip" : "film",
        "newname" => "",
        "poster" => "",
        # "aspect_ratio" => data["streams"][n]["display_aspect_ratio"],
    )
    return res
end

function check_file(filename)
    # println(filename)
    v2 = file_meta(filename)  
    v1 = file_info(filename)
    result = merge!(v1, v2)
    # println(info)  
    return result
end

function main(host_id::Integer)
    dir = "D:\\vids\\weed"
    @info  "Обработка каталога: $dir"
    
    files = readdir(dir, join=true)
    n = length(files)
    println("всего: $n files")

    conn = DBInterface.connect(MySQL.Connection, "xigmanas", "itman", "X753951x", db="vid")
    @time begin
        # @sync begin
           for file in filter(valid_file, files)
            #    Threads.@spawn begin
                meta = nothing
                    try
                        @elapsed meta = check_file(file)
                    catch e
                        @error "Ошибка обработки файла $file: $e"
                        continue
                    end
                    meta["host_id"] = host_id
                    # println(meta)
                    save_db(conn, meta)
            #    end
           end
        # end
    end

    println("обработано")
    close(conn)
end

function exist(conn, crc::Integer)
    cmd = "select count(*) as cnt from filemeta where crc = $crc"
    result = DBInterface.execute(conn, cmd)   
    data = first(result)
    # println("data     = $data")
    return data.cnt > 0
end

# сохраняет meta в БД
function save_db(conn, m)
    filename = m["filename"]
    if exist(conn, m["crc"])
        @info "уже имеется: $filename"
    else
        # println("сохраняю: $filename")
        host_id = m["host_id"]
        filename = replace(m["filename"], "\\" => "\\\\")
        newname = m["newname"]
        size = m["size"]
        hsize = m["hsize"]
        hduration = m["hduration"]
        width = m["width"]
        height = m["height"]
        bitrate = m["bit_rate"]
        ext = m["ext"]
        crc = m["crc"]
        hctime = m["hctime"]
        hmtime = m["hmtime"]
        poster = m["poster"]
        audio = m["audio"]
        codec = m["codec"]
        tags = m["tags"]
        type = m["type"]

        fields = """
            host_id, filename, newname, size,
            hsize, duration, width,
            height, bitrate, ext, crc,
            ctime, mtime,
            poster, audio, codec, tags,
            type
        """
        values = """
            $host_id, '$filename', '$newname', $size, 
            '$hsize', '$hduration', $width,
            $height, '$bitrate', '$ext', $crc,
            '$hctime', '$hmtime',
            '$poster', $audio, '$codec', '$tags',
            '$type'
        """
        cmd = "insert into filemeta ($fields) values ($values)"
        println("file: ", filename)
        result = DBInterface.execute(conn, cmd) 
        # println("result: ", result)
    end
end

function openDB()
    conn = DBInterface.connect(MySQL.Connection, "xigmanas", "itman", "X753951x", db="sea")
    limit = 2
    ext = ".webp"
    cmd = "select ext, newurl from filemeta where ext = '$ext' limit $limit"
    result = DBInterface.execute(conn, cmd)
    # println(typeof(result))
    for s in result
        println(s.ext, ' ',  s.newurl)
    end
end

#=
 -------- основная программа --------------
=#

# parallel_execution()
# openDB()
main(1)