Parsing JSON data of the timeline from the SQLServer table column, and create thumbnail based on it with FFmpeg.
Quick and dirty scripts using Golang.
JSON data:
[{"time":"00:00.0","slide":"1.png","index":"ゼロ秒.タイトル.0","memo":"","ratio":"0.5"},
{"time":"00:10.0","slide":"2.png","index":"十秒.タイトル.10","memo":"","ratio":""},
{"time":"00:12.0","slide":"3.png","index":"十二秒.タイトル.12","memo":"","ratio":"0.9"},
{"time":"00:28.0","slide":"4.png","index":"二十八秒.タイトル.28","memo":"","ratio":""},
{"time":"00:38.0","slide":"5.png","index":"三十八秒.タイトル.38","memo":"","ratio":""},
{"time":"00:46.0","slide":"6.png","index":"四十六秒.タイトル.46","memo":"","ratio":""},
{"time":"00:50.0","slide":"7.png","index":"五十秒.タイトル.50","memo":"","ratio":"0.5"},
{"time":"00:53.0","slide":"8.png","index":"五十三秒.タイトル.53","memo":"","ratio":""},
{"time":"00:58.0","slide":"9.png","index":"五十八秒.タイトル.58","memo":"","ratio":""}] |
package main
import (
"database/sql"
"encoding/json"
"encoding/xml"
"fmt"
"io/ioutil"
"log"
"os"
"os/exec"
"strings"
_ "github.com/denisenkom/go-mssqldb"
)
var (
server = ""
port = 1433
user = ""
password = ""
database = ""
uploadpath = ""
currentpath, _ = os.Executable()
)
const (
Separator = os.PathSeparator
ListSeparator = os.PathListSeparator
)
func ReadConnectionSetting() {
// connectionSettings.config
// <?xml version="1.0"?>
// <connectionStrings>
// <add name="yms" connectionString="Server=127.0.0.1; Database=yms; User Id=ye; password=123456;" providerName="System.Data.SqlClient" />
// </connectionStrings>
type Add struct {
Name string `xml:"name,attr"`
ConnectionString string `xml:"connectionString,attr"`
ProviderName string `xml:"providerName,attr"`
}
type connectionStrings struct {
XMLName xml.Name `xml:"connectionStrings"`
Adds []Add `xml:"add"`
}
mydir, err := os.Getwd()
if err != nil {
fmt.Println(err)
}
xmlFile, err := os.Open(mydir + "\\connectionSettings.config")
if err != nil {
fmt.Println(err)
}
// defer the closing of our xmlFile so that we can parse it later on
defer xmlFile.Close()
// read our opened xmlFile as a byte array.
byteValue, _ := ioutil.ReadAll(xmlFile)
// we initialize our Users array
var connectionStr connectionStrings
// we unmarshal our byteArray which contains our
// xmlFiles content into 'users' which we defined above
xml.Unmarshal(byteValue, &connectionStr)
//fmt.Printf("name:: %q\n", connectionStr.Adds[0].Name)
//fmt.Printf("connectionString:: %q\n", connectionStr.Adds[0].ConnectionString)
Configs := strings.Split(connectionStr.Adds[0].ConnectionString, ";")
for _, config := range Configs {
conf := strings.Split(config, "=")
if strings.Contains(config, "Server") {
server = conf[1]
}
if strings.Contains(config, "Database") {
database = conf[1]
}
if strings.Contains(config, "User Id") {
user = conf[1]
}
if strings.Contains(config, "password") {
password = conf[1]
}
}
// server = ""
port = 1433
// user = ""
// password = ""
// database = ""
}
//ReadAppSetting read baseDir from appSettings.config
func ReadAppSetting() {
//uploadpath = `Z:/data/yms/video/uploads`
// appSettings.config
// <?xml version="1.0"?>
// <appSettings>
// <add key="baseDir" value="Z:/data/yms/" />
// ...
// </appSettings>
type Add struct {
Key string `xml:"key,attr"`
Value string `xml:"value,attr"`
}
type appSettings struct {
XMLName xml.Name `xml:"appSettings"`
Adds []Add `xml:"add"`
}
mydir, err := os.Getwd()
if err != nil {
fmt.Println(err)
}
xmlFile, err := os.Open(mydir + "\\appSettings.config")
if err != nil {
fmt.Println(err)
}
// defer the closing of our xmlFile so that we can parse it later on
defer xmlFile.Close()
// read our opened xmlFile as a byte array.
byteValue, _ := ioutil.ReadAll(xmlFile)
var appSetting appSettings
xml.Unmarshal(byteValue, &appSetting)
for _, add := range appSetting.Adds {
if add.Key == "baseDir" {
uploadpath = add.Value
}
}
uploadpath += "video/uploads"
}
// ReadAllAutoCutflg read all autoCutflg
func ReadAllAutoCutflg(db *sql.DB) (int, error) {
tsql := fmt.Sprintf("SELECT contentsId, contentsPath, videoFilename, timelineJson FROM Contents WHERE autoCutflg=1;")
rows, err := db.Query(tsql)
if err != nil {
fmt.Println("Error reading rows: " + err.Error())
return -1, err
}
defer rows.Close()
count := 0
for rows.Next() {
var contentsID, contentsPath, videoFilename, timelineJson string
err := rows.Scan(&contentsID, &contentsPath, &videoFilename, &timelineJson)
if err != nil {
fmt.Println("Error reading rows: " + err.Error())
return -1, err
}
type Timetable struct {
Time string
Slide string
Index string
Memo string
Ratio string
}
var timetable []Timetable
json.Unmarshal([]byte(timelineJson), &timetable)
for i, s := range timetable {
if len(s.Index) > 0 && len(videoFilename) > 0 {
ffmpeg(videoFilename, contentsPath, i, s.Time, s.Index)
}
}
count++
UpdateAutoCutflg(db, contentsID)
}
return count, nil
}
func ffmpeg(videoFilename string, contentsPath string, i int, time string, index string) {
//ffmpeg.exe
ffmpegdir, _ := os.Getwd()
path := fmt.Sprintf("%s/%s%s_PicIndex/", uploadpath, contentsPath, strings.ReplaceAll(videoFilename, ".tmp.mp4", "")[1:])
if _, err := os.Stat(path); os.IsNotExist(err) {
os.Mkdir(path, 0777)
}
dest := fmt.Sprintf("%s%d.jpg", path, i)
//fmt.Printf(ffmpegdir+"\\ffmpeg.exe -ss %s -i %s -f image2 -frames:v 1 -y %s\n\n", time, uploadpath+"/"+contentsPath+videoFilename, dest)
args := fmt.Sprintf("-ss %s -i %s -f image2 -frames:v 1 -y %s", time, uploadpath+"/"+contentsPath+videoFilename, dest)
cmd := exec.Command(ffmpegdir+"\\ffmpeg.exe", strings.Split(args, " ")...)
cmd.Start()
fmt.Printf("%s\n", dest)
}
// UpdateAutoCutflg update autoCutflg
func UpdateAutoCutflg(db *sql.DB, contentsid string) (int64, error) {
tsql := fmt.Sprintf("UPDATE Contents SET autoCutflg=0 WHERE autoCutflg=%s;", contentsid)
result, err := db.Exec(tsql)
if err != nil {
fmt.Println("Error updating row: " + err.Error())
return -1, err
}
return result.LastInsertId()
}
func main() {
ReadConnectionSetting()
fmt.Printf("server=%s;user id=%s;password=%s;port=%d;database=%s;encrypt=disable\n\n", server, user, password, port, database)
ReadAppSetting()
connString := fmt.Sprintf("server=%s;user id=%s;password=%s;port=%d;database=%s;encrypt=disable", server, user, password, port, database)
conn, err := sql.Open("mssql", connString)
if err != nil {
log.Fatal("Open connection failed:", err.Error())
}
//fmt.Printf("Connected!\n")
defer conn.Close()
ReadAllAutoCutflg(conn)
} |