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.

sql_log_image

• Type: Boolean
• Range / Valid values: on, off
• Default: on
• Option Topic

Log to the database when creating motion triggered image file.

Configuration option common to MySQL and PostgreSQL. Motion must be built with MySQL or PostgreSQL support to use this feature.

sql_log_mpeg

• Type: Boolean
• Range / Valid values: on, off
• Default: off
• Option Topic

Log to the database when creating motion triggered mpeg file.

Configuration option common to MySQL and PostgreSQL. Motion must be built with MySQL or PostgreSQL support to use this feature.

sql_log_snapshot

• Type: Boolean
• Range / Valid values: on, off
• Default: on
• Option Topic

Log to the database when creating a snapshot image file.

Configuration option common to MySQL and PostgreSQL. Motion must be built with MySQL or PostgreSQL support to use this feature.

sql_log_timelapse

• Type: Boolean
• Range / Valid values: on, off
• Default: off
• Option Topic

Log to the database when creating timelapse mpeg file

Configuration option common to MySQL and PostgreSQL. Motion must be built with MySQL or PostgreSQL support to use this feature.

sql_query

• Type: String
• Range / Valid values: Max 4095 characters
• Default: insert into security(camera, filename, frame, file_type, time_stamp, text_event) values('%t', '%f', '%q', '%n', '%Y-%m-%d %T', '%C')
• Option Topic

SQL query string that is sent to the database. The values for each field are given by using convertion specifiers

Most common conversion specifiers

• %Y = year, %m = month, %d = date
• %H = hour, %M = minute, %S = second
• %v = event
• %q = frame number
• %t = thread (camera) number
• %D = changed pixels
• %N = noise level
• %i and %J = width and height of motion area
• %K and %L = X and Y coordinates of motion center
• %C = value defined by text_event
• %f = filename with full path
• %n = number indicating filetype

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

mysql_db

• Type: String
• Range / Valid values: Max 4095 characters
• Default: Not defined
• Option Topic

Name of the MySQL database.

MySQL CONFIG FILE OPTION. Motion must be built with MySQL libraries to use this feature.

If you compiled motion with MySQL support you will need to set the mysql options if you want motion to log events to the database.

mysql_host

• Type: String
• Range / Valid values: Max 4095 characters
• Default: localhost
• Option Topic

IP address or domain name for the MySQL server. Use "localhost" if motion and MySQL runs on the same server.

MySQL CONFIG FILE OPTION. Motion must be built with MySQL libraries to use this feature.

mysql_password

• Type: String
• Range / Valid values: Max 4095 characters
• Default: Not defined
• Option Topic

The MySQL password.

MySQL CONFIG FILE OPTION. Motion must be built with MySQL libraries to use this feature.

mysql_user

• Type: String
• Range / Valid values: Max 4095 characters
• Default: Not defined
• Option Topic

The MySQL user name.

MySQL CONFIG FILE OPTION. Motion must be built with MySQL libraries to use this feature.

PostgreSQL

Same/similar as for MySQL above.

The options for PostgreSQL

pgsql_db

• Type: String
• Range / Valid values: Max 4095 characters
• Default: Not defined
• Option Topic

Name of the PostgreSQL database.

PostgreSQL CONFIG FILE OPTION. Motion must be built with PostgreSQL libraries to use this feature.

If you compiled motion with PostgreSQL support you will need to set all the pgsql_ options if you want motion to log events to the database.

pgsql_host

• Type: String
• Range / Valid values: Max 4095 characters
• Default: localhost
• Option Topic

IP address or domain name for the PostgreSQL server. Use "localhost" if motion and PostgreSQL runs on the same server.

PostgreSQL CONFIG FILE OPTION. Motion must be built with pgsql_db libraries to use this feature.

pgsql_password

• Type: String
• Range / Valid values: Max 4095 characters
• Default: Not defined
• Option Topic

The PostgreSQL password.

PostgreSQL CONFIG FILE OPTION. Motion must be built with PostgreSQL libraries to use this feature.

pgsql_port

• Type: Integer
• Range / Valid values: 0 - 65535
• Default: 5432
• Option Topic

The PostgreSQL server port number.

PostgreSQL CONFIG FILE OPTION. Motion must be built with PostgreSQL libraries to use this feature.

pgsql_user

• Type: String
• Range / Valid values: Max 4095 characters
• Default: Not defined
• Option Topic

The PostgreSQL user name.

PostgreSQL CONFIG FILE OPTION. Motion must be built with PostgreSQL libraries to use this feature.

-- KennethLavrsen - 29 Jan 2005