Akom's Tech Ruminations

Various tech outbursts - code and solutions to practical problems

motion drops MySQL support, but you can get around it with a shell script Linux

Posted by Admin • Monday, September 24. 2012 • Category: Linux

I use motion as a surveillance system for a sizable office (9 cameras). Motion is good. But the current package of motion in Ubuntu 12.04 is no longer compiled with MySQL support (used to have it in 10.04). Compiling motion from source really didn't sound appealing... but I do want to add events to a database for my own in-house motion history browser. What to do?

Motion supports command execution on events, such as on_movie_end and on_picture_save... which makes it really easy to do a one-liner like this:
on_movie_end bash -c "echo insert into security(camera, filename, frame, file_type, time_stamp, event_time_stamp, event_id) values('%t', '%f', '%q', '%n', '%Y-%m-%d %T', '%C', '%v') | mysql -uUSER -pPASS"
if you'd like to do something fancier, you can break it out into a shell script:

#!/bin/bash

logger -t test "$0 executing with $*"
sql="insert into security(camera, filename, frame, file_type, time_stamp, event_time_stamp, event_id) values('$1', '$2', '$3', '$4', '$5', '$6', '$7')"
echo $sql | mysql -u$U -p$P motion
 
and invoke like this:
on_movie_end U=USER P=PASS bash /usr/local/sbin/motion-insert-event.sh '%t' '%f' '%q' '%n' '%Y-%m-%d %T' '%C' '%v


(same for on_picture_save, if needed). You can also daisy-chain commands with semicolon if you have multiple. Also, note that the query above is for my table, yours may have different columns. The rest are my specific examples of usage:

My specific usage

To answer some questions, I'm including specific examples.

Config

# First, relevant configuration
# Create per-day subdirectories so it's not a huge mess:
jpeg_filename %Y-%m-%d/img-C%t/%H%M%S-%q
movie_filename %Y-%m-%d/movie-C%t/%H%M%S
snapshot_filename %Y-%m-%d/snap-C%t/%H%M%S-snapshot
timelapse_filename %Y-%m-%d/timelapse-C%t

output_normal best # I want to use these images as thumbnails in my webapp

Still Photos

(The lines wrap obviously)


on_picture_save bash /usr/local/sbin/motion-insert-event.sh '%t' '%f' '%q' '%n' '%Y-%m-%d %T' '%C' '%v'; mkdir -p `dirname %f`/thumb ; convert -resize 80 %f `dirname %f`/thumb/`basename %f` ;  sh /usr/local/sbin/motion-update-filesize.sh %f
 
What this does:
  1. Runs a tiny shell script to do a mysql insert (see top of post)
  2. Creates a thumbnail in the "thumb" subdirectory for use by my web-app
  3. Updates the value in the filesize column in my table (motion itself doesn't supply this value). This is a simple bash script I wrote, nothing fancy

Movies


on_movie_end  bash /usr/local/sbin/motion-insert-event.sh '%t' '%f' '%q' '%n' '%Y-%m-%d %T' '%C' '%v'; bash /usr/local/sbin/motion-update-filesize.sh %f
 
What this does:
  1. Inserts event again
  2. updates filesize again
Pretty basic. Now as far as a custom web-app, I did write several, but never got around to putting them up on sourceforge. Additionally, I have a cronjob that deletes items older than X days (I only have so much space and I record over 2GB per day from 8 cameras). It also updates the table and sets the "deleted" column to 1 for the deleted files. You could equivalently archive it to some other disk or to something like Amazon Glacier, and mark it archived.

0 Trackbacks

  1. No Trackbacks

4 Comments

Display comments as (Linear | Threaded)
  1. This is a great idea as I dont want to recompile either!!!

    As motion triggers, what do you do with all the images? Is it a case of writing a web app to handle the images and see what is going on?

    I was going to run a python script to do do the DB INSERT like you did, and then possibly move some images around as you would end up with millions of images in a single DIR? Then write some PHP to view the database entries and images.

    I'm not very far forward with Motion...

    thanks

    Gordon
  2. Added some answers to your questions to the post.
  3. Hello,
    Glad to see your post.

    So if i understand correctly, you are using motion and mysql.
    Does it mean that you have a sort of php/sql/html websurveillance system?


    Looking forward to reading you,

    jchvvs
  4. I wrote serveral front-ends for content originated from motion. They are usually in-house custom to the needs of the business, thus I don't open source them. Basically, it's a MVC app that lets you query pre-recorded content by time/size/date/camera, and to play them back in the browser. It also lets you view the live feeds in real time. The app is not all that complex. With mysql, querying becomes trivial. There is also a cron job to drop old content and mark it deleted in DB.

    Hope this helps.

Add Comment


You can use [geshi lang=lang_name [,ln={y|n}]][/geshi] tags to embed source code snippets.
Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
Standard emoticons like :-) and ;-) are converted to images.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA

What is the primary language of this blog? (Anti-SPAM question)


Submitted comments will be subject to moderation before being displayed.