יום ראשון, 16 במרץ 2014

Can't see video (mp4 / mpeg) thumbnail on gThumb

Can't see video (mp4 / mpeg) thumbnail on gThumb ?

Install thumbmailer:
sudo apt-get install libxine1-ffmpeg
sudo apt-get install ffmpegthumbnailer

Convert to mpeg:
ffmpeg -i video-2014-02-28-16-57-10.mp4 video-2014-02-28-16-57-10.mp4.mpg
 for f in *.mp4; do ffmpeg -i "$f" "${f%.wav}.mpg"; done


יום חמישי, 13 במרץ 2014

Select Newset or Most Updated Records from a MySQL Table - Two options

Select Newset or Most Updated Records from a MySQL Table - Two options

CREATE TABLE ttt(
   id      MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
   date    DATETIME NOT NULL,
   aid     MEDIUMINT UNSIGNED NOT NULL,
   status  ENUM('ac', 'na') NOT NULL,
   PRIMARY KEY(id)
) ENGINE InnoDB;

INSERT INTO ttt SET id=1, date='2014-03-10', aid=1, status='ac';
INSERT INTO ttt SET id=2, date='2014-03-11', aid=1, status='na';
INSERT INTO ttt SET id=3, date='2014-03-11', aid=2, status='ac';
INSERT INTO ttt SET id=4, date='2014-03-12', aid=1, status='ac';
INSERT INTO ttt SET id=5, date='2014-03-12', aid=2, status='na';

SELECT
ttt.*
FROM
ttt
LEFT JOIN ttt t2 ON t2.aid=ttt.aid AND t2.id>ttt.id
WHERE
t2.id IS NULL;


SELECT ttt.*
FROM ttt
INNER JOIN
(SELECT MAX(id) as maxid, aid FROM tttGROUP BY aid) t2 ON ttt.id = t2.maxid;

And the results:

mysql> SELECT ttt.* FROM ttt LEFT JOIN ttt t2 ON t2.aid=ttt.aid AND t2.id>ttt.id WHERE t2.id IS NULL;
+----+---------------------+-----+--------+
| id | date                | aid | status |
+----+---------------------+-----+--------+
|  4 | 2014-03-12 00:00:00 |   1 | ac     |
|  5 | 2014-03-12 00:00:00 |   2 | na     |
+----+---------------------+-----+--------+
2 rows in set (0.00 sec)

mysql> SELECT ttt.* FROM ttt LEFT JOIN ttt t2 ON t2.aid=ttt.aid AND t2.id>ttt.id WHERE t2.id IS NULL;
+----+---------------------+-----+--------+
| id | date                | aid | status |
+----+---------------------+-----+--------+
|  4 | 2014-03-12 00:00:00 |   1 | ac     |
|  5 | 2014-03-12 00:00:00 |   2 | na     |
+----+---------------------+-----+--------+
2 rows in set (0.00 sec)