透過 Script 合併 MPEG2-TS (Transport Stream / TS) 與 ffmpeg 轉檔

因緣際會，處理一些用 M3U 串起來的一堆瑣碎的 .ts 檔案，這些檔案透過 M3U 的格式，可清楚描述第幾秒要播放哪個片段。然而，在一些測試上會略顯麻煩 Orz 所以撰寫一些 script 跟 ffmpeg 來把這些東西在整合成單一檔案 :P

眾多 ts 檔案該怎樣合併成一則？其實單純用 file append 即可：

$ cat 1.ts 2.ts 3.ts > output.ts

所以，假設有一個 M3U 時，可以透過以下指令抓出：

$ grep -v "#" list.m3u | xargs
1.ts 2.ts 3.ts ...

可惜啊，資料比數太 command line 可能會出爆，建議改用 script 來處理，例如 PHP：

<?php
foreach(explode("\n", file_get_contents($m3u_input)) as $line) {
if (empty($line) || $line[0] == '#')
continue;
$raw = file_get_contents($file);
if (!empty($raw))
file_put_contents("$output.raw", $raw, FILE_APPEND);
}

如此一來，就可以把一票 .ts 檔重新建立成單一檔案 .raw 。接著，再用 ffmpeg 轉成想要測試的格式吧！關於 ts 轉 mp4 ，可以透過 ffmepg 處理，其中 ffmpeg 會自己判斷輸出的格式（此例是 mp4），所以有需要也可以用 output.avi：

$ ffmpeg -i input.ts -vcodec copy -acodec copy output.mp4
$ ffmpeg -i input.ts -vcodec copy -acodec copy -bsf:a aac_adtstoasc output.mp4

如果有一票目錄 + M3U 的話，就掃一下目錄建立一票相關的，接著可以用 find 再把這一票轉一轉：

$ find . -name "*.raw" -exec  ffmpeg -i {} -vcodec copy -acodec copy -bsf:a aac_adtstoasc {}.mp4 \;

[SQLite] 跨 database (db file) 進行 Table JOIN

使用 SQLITE - Attach 這個指令即可：

$ ls
a.sqlite3 b.sqlite3
$ sqlite3
sqlite> attach 'a.sqlite3' as adb
sqlite> attach 'b.sqlite3' as bdb
sqlite> .tables
adb.user  bdb.data
sqlite> SELECT adb.user.id, adb.user.name, bdb.data.info FROM adb.user, bdb.data WHERE adb.user.id = bdb.data.id

[SQL] SELECT IN SELECT 以及 Pagination 的使用 @ MySQL 5.6

使用 SQL 語法時，有時會需要從另一張 Table 取出清單，接著對清單內的資料做為基準再進行一次資料的擷取，直觀的想法大概是 SELECT something FROM Table1 WHERE id IN (SELECT id FROM Table2 WHERE ... )。

可惜上述語法是不行的 XD 要改成 JOIN 的做法：

SELECT something FROM Table1, (SELECT id FROM Table2) AS list WHERE Table1.id = list.id;

接著，偶爾會需要 pagination 的需求，加個 LIMIT 的用法，這時候又會想要回報全部有幾筆資料（對於一些搜尋引擎的設計，有些是採用預估的方式），以便前端可以估算有幾筆資料。

最簡單的解法是再用一個 SQL Query 去問 Table2 的 id 資料，但想要更快一點，就來試試 MySQL User-Defined Variables 吧!

SELECT something, @n AS total FROM Table1, (SELECT id, CASE WHEN @n > 0 THEN @n := @n + 1 ELSE @n := 1 END AS n FROM Table2, (SELECT @n := 0) AS init) AS list WHERE Table1.id = list.id;

如此一來，結果都會有個 total 筆數跟著，雖然仍不夠好，但也不錯啦 XD  而搭配 LIMIT OFFSET,COUNT 時，total 的資訊是來自掃 Table2 的資料，所以也能正常顯示：

SELECT something, @n AS total FROM Table1, (SELECT id, CASE WHEN @n > 0 THEN @n := @n + 1 ELSE @n := 1 END AS n FROM Table2, (SELECT @n := 0) AS init) AS list WHERE Table1.id = list.id LIMIT 0,10;

[SQL] 透過 INNER JOIN 更新 Table 新增的欄位數值 @ MySQL 5.6

對於一些當作收集 log 用途的 table tb_log ，隨著時間增加後，通常會再整理另一個 tb_status 的 table，快速查詢各個狀態，設計上就會定期批次從 tb_log 取出資料，存進 tb_status 中。

假設 tb_log 有 5 個欄位，一開始只覺得需要 2 個欄位的資訊，就把 tb_status 設定為 2 個欄位，然而過一陣子後，想多記錄一個欄位時，只好變動 tb_status ，但新增的欄位沒有舊資料，就變成要從 tb_log 取出來再存進 tb_status 了

碰到這種問題，有一個解法就是使用 INNER JOIN 來處理：

1. 先從 tb_status 找出欄位未有值的資料
2. 從 tb_log 組出 tb_status 所需的資料
3. 透過  Update 指令更新

情況敘述：

mysql> describe tb_log;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| f0     | int(11)      | NO   | PRI | NULL    | auto_increment |
| f1     | varchar(8)   | YES  |     | NULL    |                |
| f2     | varchar(8)   | YES  |     | NULL    |                |
| f3     | varchar(8)   | YES  |     | NULL    |                |
| f4     | varchar(8)   | YES  |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+

mysql> describe tb_status;
+--------+--------------+------+-----+---------+----------------+
| Field  | Type         | Null | Key | Default | Extra          |
+--------+--------------+------+-----+---------+----------------+
| f1     | varchar(8)   | YES  | PRI | NULL    |                |
| f2     | varchar(8)   | YES  |     | NULL    |                |
| f3     | varchar(8)   | YES  |     | NULL    |                |
+--------+--------------+------+-----+---------+----------------+

其中 tb_status.f3 則是新建出來，未有資料的。

第一步：先找出 tb_status.f3 是空的（新進資料會有 f3 數值，只有舊資料沒有）

mysql> SELECT f1 WHERE f3 IS NULL;

第二步：從 tb_log 組出 f3 資料，由於 tb_log 是流水帳，且 tb_status 本身也可以從 tb_log 查詢出來的，只需組出 tb_status 需要的欄位即可：

mysql> SELECT f1, f3 FROM tb_log GROUP BY f1;

第三步，把上述兩個資料 JOIN 起來：

SELECT tb1.f1, tb2.f3 FROM
( SELECT f1 WHERE f3 IS NULL ) AS tb1, (SELECT f1, f3 FROM tb_log GROUP BY f1) AS tb2
WHERE tb1.f1 = tb2.f2;

最後，追加更新 tb_status 的用法：

UPDATE tb_status AS tb4
INNER JOIN

(
SELECT tb1.f1, tb2.f3 FROM
( SELECT f1 WHERE f3 IS NULL ) AS tb1,
(SELECT f1, f3 FROM tb_log GROUP BY f1) AS tb2
WHERE tb1.f1 = tb2.f2
) AS tb3

ON tb4.f1 = tb3.f1

SET

tb4.f3 = tb3.f3;

[MongoDB] 透過 MapReduce 進行 Collections Join @ Ubuntu 12.04

使用 NOSQL 時，其先天設計無法提供像 MySQL Table Joins 等招數，此時的解法就是把兩個 tables 倒進一張 table 來解決。

以 MongoDB command line 操作，新增資料：

> use mydb
switched to db mydb
> db.t1.insert({"data":1, "data2":2, "data3":3})
> db.t1.insert({"data":4, "data2":5, "data3":6})
> db.t1.insert({"data":7, "data2":8, "data3":9})
> db.t1.find()
{ "_id" : ObjectId("52f1f9c7bf6317bb2615b216"), "data" : 1, "data2" : 2, "data3" : 3 }
{ "_id" : ObjectId("52f1f9cbbf6317bb2615b217"), "data" : 4, "data2" : 5, "data3" : 6 }
{ "_id" : ObjectId("52f1f9cebf6317bb2615b218"), "data" : 7, "data2" : 8, "data3" : 9 }
> db.t2.insert({"data4":3, "data5":2, "data6":1})
> db.t2.insert({"data4":6, "data5":5, "data6":4})
> db.t2.insert({"data4":9, "data5":8, "data6":7})
> db.t2.find()
{ "_id" : ObjectId("52f1f9d4bf6317bb2615b219"), "data4" : 3, "data5" : 2, "data6" : 1 }
{ "_id" : ObjectId("52f1f9d7bf6317bb2615b21a"), "data4" : 6, "data5" : 5, "data6" : 4 }
{ "_id" : ObjectId("52f1f9dbbf6317bb2615b21b"), "data4" : 9, "data5" : 8, "data6" : 7 }


定義 t1 跟 t2 的 mapper function：

> t1_map = function() {
emit(this.data3, { "data": this.data, "data2": this.data2, "data3": this.data3 });
}
> t2_map = function() {
emit(this.data4, { "data4": this.data4, "data5": this.data5, "data6": this.data6 });
}

定義 reducer function:

> reducer = function(key, values){
var result = {} ;
values.forEach( function(value){
var field;
for(field in value)
if( value.hasOwnProperty(field) )
result[field] = value[field];
});
return result;
}

進行 map-reduce：

> db.tmp.drop()
true
> db.t1.mapReduce(t1_map, reducer , {"out":{"reduce":"tmp"}} )
{
        "result" : "tmp",
        "timeMillis" : 10,
        "counts" : {
                "input" : 3,
                "emit" : 3,
                "reduce" : 0,
                "output" : 3
        },
        "ok" : 1,
}
> db.tmp.find()
{ "_id" : 3, "value" : { "data" : 1, "data2" : 2, "data3" : 3 } }
{ "_id" : 6, "value" : { "data" : 4, "data2" : 5, "data3" : 6 } }
{ "_id" : 9, "value" : { "data" : 7, "data2" : 8, "data3" : 9 } }
> db.t2.mapReduce(t2_map, reducer , {"out":{"reduce":"tmp"}} )
{
        "result" : "tmp",
        "timeMillis" : 6,
        "counts" : {
                "input" : 3,
                "emit" : 3,
                "reduce" : 0,
                "output" : 3
        },
        "ok" : 1,
}
> db.tmp.find()
{ "_id" : 3, "value" : { "data4" : 3, "data5" : 2, "data6" : 1, "data" : 1, "data2" : 2, "data3" : 3 } }
{ "_id" : 6, "value" : { "data4" : 6, "data5" : 5, "data6" : 4, "data" : 4, "data2" : 5, "data3" : 6 } }
{ "_id" : 9, "value" : { "data4" : 9, "data5" : 8, "data6" : 7, "data" : 7, "data2" : 8, "data3" : 9 } }

以上的概念就是 t1 採用 data3 作為 join key，而 t2 採用 data4 ，分別跑 map-reduce 後儲存在 tmp 這張表(collection)，如此 tmp 即為常用的 SQL Join 結果。

以上須留意的是跑 map-reduce 時，要指定 out 為 reduce 形態，細節可以在 out Options 查看，可分成 replace, reduce, merge ，若沒指定為 reduce 的話，上述最後結果僅有 t2 的資料。

此外，在 pymongo 上也能達成，有興趣可以參考 map-reduce-join.py，寫的彈性一點，所以閱讀性較差，用法：

$ python map-reduce-join.py --host localhost --database mydb --reset-result --result tmp --show-result --select-out-1 data data2 data3 --join-key-1 data3 --select-out-2 data4 data5 data6 --join-key-2 data4

結果：

Mapper 1 Code:
                function() {
                        emit(this.data3, {"data": this.data, "data2": this.data2, "data3": this.data3});
                }

Reducer 1 Code:
                function(key, values) {
                        var out = {};
                        values.forEach( function(value) {
                                for ( field in value )
                                        if( value.hasOwnProperty(field) )
                                                out[field] = value[field];

                        });
                        return out
                }

{u'counts': {u'input': 3, u'reduce': 0, u'emit': 3, u'output': 3}, u'timeMillis': 34, u'ok': 1.0, u'result': u'tmp'}
{u'_id': 3.0, u'value': {u'data': 1.0, u'data3': 3.0, u'data2': 2.0}}
{u'_id': 6.0, u'value': {u'data': 4.0, u'data3': 6.0, u'data2': 5.0}}
{u'_id': 9.0, u'value': {u'data': 7.0, u'data3': 9.0, u'data2': 8.0}}
Mapper 2 Code:
                function() {
                        emit(this.data4, {"data4": this.data4, "data5": this.data5, "data6": this.data6});
                }

Reducer 2 Code:
                function(key, values) {
                        var out = {};
                        values.forEach( function(value) {
                                for ( field in value )
                                        if( value.hasOwnProperty(field) )
                                                out[field] = value[field];
                        });
                        return out
                }

{u'counts': {u'input': 3, u'reduce': 0, u'emit': 3, u'output': 3}, u'timeMillis': 5, u'ok': 1.0, u'result': u'tmp'}
{u'_id': 3.0, u'value': {u'data5': 2.0, u'data4': 3.0, u'data6': 1.0, u'data': 1.0, u'data3': 3.0, u'data2': 2.0}}
{u'_id': 6.0, u'value': {u'data5': 5.0, u'data4': 6.0, u'data6': 4.0, u'data': 4.0, u'data3': 6.0, u'data2': 5.0}}
{u'_id': 9.0, u'value': {u'data5': 8.0, u'data4': 9.0, u'data6': 7.0, u'data': 7.0, u'data3': 9.0, u'data2': 8.0}}

[OSX] 使用 ffmpeg 將 mp4 list 依序串接起來 (ffmpeg concat) @ Mac OS X 10.9

最近研究了一下對岸的影音播放，發現有的服務實作時，將一部短片以固定時間進行切割，這樣在服務眾人或是走 P2P 時，可以很方便進行分流，然而，若要把它弄成一個檔案，那就得花點功夫了，好家在用 ffmpeg 就可以搞定囉：How to concatenate (join, merge) media files。

以 mp4 為例：

$ ffmpeg -i file1.mp4 -c copy -bsf:v h264_mp4toannexb -f mpegts '/tmp/tmp1'
$ ffmpeg -i file2.mp4 -c copy -bsf:v h264_mp4toannexb -f mpegts '/tmp/tmp2'
$ ffmpeg -i "concat:/tmp/tmp1|/tmp/tmp2" -c copy -bsf:a aac_adtstoasc output.mp4

接著，嫌太慢就寫個 script 來處理 XDD

$ python ffmpeg-concat-mp4-files.py --files file1.mp4 file2.mp4 --output out.mp4
$ python ffmpeg-concat-mp4-files.py --m3u /tmp/m3u.list --output out.mp4

其中 m3u 是支援 http 的描述，也就是會把網路上的東西用 wget 下載回 tmp 區。因此 ffmpeg-concat-mp4-files.py 需要有 ffmpeg 跟 wget 指令才能。