Using Databases

Motion can be compiled with both MySQL and PostgreSQL database support. When enabled Motion adds a record to a table in the database as specified by the sql_query. The query contains the fields that are used and the value are given by using conversion specifiers for dynamic data like filename, time, number of detected pixels etc. Motion does not place any binary images in the database and it cannot remove old records.

Motion only adds records to the database when files are created. The database contains records of saved files which means to get a record in the database the feature that enables for example motion detection, timelapse, snapshots etc must be enabled. The sql_log options defines which types of files are logged in the database.

The following sql_log options are common to both MySQL and PostgreSQL.

See the "MySQL" section for detailed information about the database itself.

MySQL

You can use the MySQL database to register each file that is stored by motion.

You need to generate a new database with a name of your own choice. You must enter this name in the config file (mysql_db option). The default value for the option sql_query requires that you create a new database in MySQL with a new table called "security" with the following fields:

insert into security(camera, filename, frame, file_type, time_stamp, text_event) values('%t', '%f', '%q', '%n', '%Y-%m-%d %T', '%C')

• camera (int) - camera (thread) number
• filename (char60) - filename (full path)
• frame (int) - the number of the picture frame
• file_type (int) - file type as a number - see table below.
• time_stamp (timestamp) - timestamp for the picture in native database format
• text_event (timestamp) - The text from the text_event option which by default is compatible with timestamps in SQL.

Note from version 3.2.4 the introduction of sql_query completely redefines the way you setup the SQL feature. It is now 100% flexible and can easily be made compatible with your existing Motion database from earlier versions of Motion.

These are the file type descriptions and the file type numbers stored in the database.

Normal image	1
Snapshot image	2
Motion image (showing only pixels defined as motion)	4
Normal mpeg image	8
Motion mpeg (showing only pixels defined as motion)	16
Timelapse mpeg	32

You can create the table using the following SQL statement.

CREATE TABLE security (camera int, filename char(80) not null, frame int, file_type int, time_stamp timestamp(14), text_event timestamp(14));

If you choose to use text_event for a non-timestamp value you can instead define something like.

CREATE TABLE security (camera int, filename char(80) not null, frame int, file_type int, time_stamp timestamp(14), text_event char(40));

Remember to update grant table to give access to the mysql username you choose for motion.

It would be too much to go into detail about how to setup and use MySQL. After all this is a guide about Motion. However here are some hints and links.

Setting Up a MySQL Based Website - A beginners guide from Linux Planet.

Webmonkey PHP/!MySQL tutorial - Entertaining and easy to read.

The phpMyAdmin homepage. The best and simplest tool to use MySQL (editors opinion). Requires Apache/PHP.

The options for MySQL

PostgreSQL

Same/similar as for MySQL above.

The options for PostgreSQL

-- KennethLavrsen - 29 Jan 2005