.\" Man page generated from reStructuredText.
.
.TH "GAMMU-SMSD-MYSQL" "7" "Oct 03, 2020" "1.42.0" "Gammu"
.SH NAME
gammu-smsd-mysql \- gammu-smsd(1) backend using MySQL database server as a message storage
.
.nr rst2man-indent-level 0
.
.de1 rstReportMargin
\\$1 \\n[an-margin]
level \\n[rst2man-indent-level]
level margin: \\n[rst2man-indent\\n[rst2man-indent-level]]
-
\\n[rst2man-indent0]
\\n[rst2man-indent1]
\\n[rst2man-indent2]
..
.de1 INDENT
.\" .rstReportMargin pre:
. RS \\$1
. nr rst2man-indent\\n[rst2man-indent-level] \\n[an-margin]
. nr rst2man-indent-level +1
.\" .rstReportMargin post:
..
.de UNINDENT
. RE
.\" indent \\n[an-margin]
.\" old: \\n[rst2man-indent\\n[rst2man-indent-level]]
.nr rst2man-indent-level -1
.\" new: \\n[rst2man-indent\\n[rst2man-indent-level]]
.in \\n[rst2man-indent\\n[rst2man-indent-level]]u
..
.SH DESCRIPTION
.sp
MYSQL backend stores all data in a \fI\%MySQL\fP database server, which parameters are
defined by configuration (see gammu\-smsdrc for description of configuration
options).
.sp
For tables description see gammu\-smsd\-tables\&.
.sp
This backend is based on gammu\-smsd\-sql\&.
.SH CONFIGURATION
.sp
Before running gammu\-smsd you need to create necessary tables in the
database, which is described below.
.sp
The configuration file then can look like:
.INDENT 0.0
.INDENT 3.5
.sp
.nf
.ft C
[smsd]
service = sql
driver = native_mysql
host = localhost
.ft P
.fi
.UNINDENT
.UNINDENT
.sp
\fBSEE ALSO:\fP
.INDENT 0.0
.INDENT 3.5
gammu\-smsdrc
.UNINDENT
.UNINDENT
.SH PRIVILEGES
.sp
The user accessing the database does not need much privileges, the following
privileges should be enough:
.INDENT 0.0
.INDENT 3.5
.sp
.nf
.ft C
GRANT USAGE ON *.* TO \(aqsmsd\(aq@\(aqlocalhost\(aq IDENTIFIED BY \(aqpassword\(aq;

GRANT SELECT, INSERT, UPDATE, DELETE ON \(gasmsd\(ga.* TO \(aqsmsd\(aq@\(aqlocalhost\(aq;

CREATE DATABASE smsd;
.ft P
.fi
.UNINDENT
.UNINDENT
.sp
\fBNOTE:\fP
.INDENT 0.0
.INDENT 3.5
For creating the SQL tables you need more privileges, especially for
creating triggers, which are used for some functionality.
.UNINDENT
.UNINDENT
.SH CREATING TABLES FOR MYSQL
.sp
Depending on MySQL version and settings please choose best fitting
script to create tables:
.INDENT 0.0
.IP \(bu 2
\fBmysql.sql\fP, requires MySQL 5.6.5 or newer
.IP \(bu 2
\fBmysql\-legacy.sql\fP supports legacy MySQL versions, but requires neither of
\fINO_ZERO_DATE\fP, \fIANSI\fP or \fISTRICT\fP modes to be set in the server
.UNINDENT
.sp
SQL script \fBmysql.sql\fP for creating tables in MySQL database:
.INDENT 0.0
.INDENT 3.5
.sp
.nf
.ft C
\-\- 
\-\- Database for Gammu SMSD
\-\- 
\-\- In case you get errors about not supported charset, please
\-\- replace utf8mb4 with utf8.

\-\- \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

\-\- 
\-\- Table structure for table \(gagammu\(ga
\-\- 

CREATE TABLE \(gagammu\(ga (
  \(gaVersion\(ga integer NOT NULL default \(aq0\(aq PRIMARY KEY
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

\-\- 
\-\- Dumping data for table \(gagammu\(ga
\-\- 

INSERT INTO \(gagammu\(ga (\(gaVersion\(ga) VALUES (17);

\-\- \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

\-\- 
\-\- Table structure for table \(gainbox\(ga
\-\- 

CREATE TABLE \(gainbox\(ga (
  \(gaUpdatedInDB\(ga timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  \(gaReceivingDateTime\(ga timestamp NOT NULL default CURRENT_TIMESTAMP,
  \(gaText\(ga text NOT NULL,
  \(gaSenderNumber\(ga varchar(20) NOT NULL default \(aq\(aq,
  \(gaCoding\(ga enum(\(aqDefault_No_Compression\(aq,\(aqUnicode_No_Compression\(aq,\(aq8bit\(aq,\(aqDefault_Compression\(aq,\(aqUnicode_Compression\(aq) NOT NULL default \(aqDefault_No_Compression\(aq,
  \(gaUDH\(ga text NOT NULL,
  \(gaSMSCNumber\(ga varchar(20) NOT NULL default \(aq\(aq,
  \(gaClass\(ga integer NOT NULL default \(aq\-1\(aq,
  \(gaTextDecoded\(ga text NOT NULL,
  \(gaID\(ga integer unsigned NOT NULL auto_increment,
  \(gaRecipientID\(ga text NOT NULL,
  \(gaProcessed\(ga enum(\(aqfalse\(aq,\(aqtrue\(aq) NOT NULL default \(aqfalse\(aq,
  \(gaStatus\(ga integer NOT NULL default \(aq\-1\(aq,
  PRIMARY KEY \(gaID\(ga (\(gaID\(ga)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1 ;

\-\- 
\-\- Dumping data for table \(gainbox\(ga
\-\- 


\-\- \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

\-\- 
\-\- Table structure for table \(gaoutbox\(ga
\-\- 

CREATE TABLE \(gaoutbox\(ga (
  \(gaUpdatedInDB\(ga timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  \(gaInsertIntoDB\(ga timestamp NOT NULL default CURRENT_TIMESTAMP,
  \(gaSendingDateTime\(ga timestamp NOT NULL default CURRENT_TIMESTAMP,
  \(gaSendBefore\(ga time NOT NULL DEFAULT \(aq23:59:59\(aq,
  \(gaSendAfter\(ga time NOT NULL DEFAULT \(aq00:00:00\(aq,
  \(gaText\(ga text,
  \(gaDestinationNumber\(ga varchar(20) NOT NULL default \(aq\(aq,
  \(gaCoding\(ga enum(\(aqDefault_No_Compression\(aq,\(aqUnicode_No_Compression\(aq,\(aq8bit\(aq,\(aqDefault_Compression\(aq,\(aqUnicode_Compression\(aq) NOT NULL default \(aqDefault_No_Compression\(aq,
  \(gaUDH\(ga text,
  \(gaClass\(ga integer default \(aq\-1\(aq,
  \(gaTextDecoded\(ga text NOT NULL,
  \(gaID\(ga integer unsigned NOT NULL auto_increment,
  \(gaMultiPart\(ga enum(\(aqfalse\(aq,\(aqtrue\(aq) default \(aqfalse\(aq,
  \(gaRelativeValidity\(ga integer default \(aq\-1\(aq,
  \(gaSenderID\(ga varchar(255),
  \(gaSendingTimeOut\(ga timestamp NULL default CURRENT_TIMESTAMP,
  \(gaDeliveryReport\(ga enum(\(aqdefault\(aq,\(aqyes\(aq,\(aqno\(aq) default \(aqdefault\(aq,
  \(gaCreatorID\(ga text NOT NULL,
  \(gaRetries\(ga int(3) default 0,
  \(gaPriority\(ga integer default 0,
  \(gaStatus\(ga enum(\(aqSendingOK\(aq,\(aqSendingOKNoReport\(aq,\(aqSendingError\(aq,\(aqDeliveryOK\(aq,\(aqDeliveryFailed\(aq,\(aqDeliveryPending\(aq,\(aqDeliveryUnknown\(aq,\(aqError\(aq,\(aqReserved\(aq) NOT NULL default \(aqReserved\(aq,
  \(gaStatusCode\(ga integer NOT NULL default \(aq\-1\(aq,
  PRIMARY KEY \(gaID\(ga (\(gaID\(ga)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

CREATE INDEX outbox_date ON outbox(SendingDateTime, SendingTimeOut);
CREATE INDEX outbox_sender ON outbox(SenderID(250));

\-\- 
\-\- Dumping data for table \(gaoutbox\(ga
\-\- 


\-\- \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

\-\- 
\-\- Table structure for table \(gaoutbox_multipart\(ga
\-\- 

CREATE TABLE \(gaoutbox_multipart\(ga (
  \(gaText\(ga text,
  \(gaCoding\(ga enum(\(aqDefault_No_Compression\(aq,\(aqUnicode_No_Compression\(aq,\(aq8bit\(aq,\(aqDefault_Compression\(aq,\(aqUnicode_Compression\(aq) NOT NULL default \(aqDefault_No_Compression\(aq,
  \(gaUDH\(ga text,
  \(gaClass\(ga integer default \(aq\-1\(aq,
  \(gaTextDecoded\(ga text,
  \(gaID\(ga integer unsigned NOT NULL default \(aq0\(aq,
  \(gaSequencePosition\(ga integer NOT NULL default \(aq1\(aq,
  \(gaStatus\(ga enum(\(aqSendingOK\(aq,\(aqSendingOKNoReport\(aq,\(aqSendingError\(aq,\(aqDeliveryOK\(aq,\(aqDeliveryFailed\(aq,\(aqDeliveryPending\(aq,\(aqDeliveryUnknown\(aq,\(aqError\(aq,\(aqReserved\(aq) NOT NULL default \(aqReserved\(aq,
  \(gaStatusCode\(ga integer NOT NULL default \(aq\-1\(aq,
  PRIMARY KEY (\(gaID\(ga, \(gaSequencePosition\(ga)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

\-\- 
\-\- Dumping data for table \(gaoutbox_multipart\(ga
\-\- 

\-\- \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

\-\- 
\-\- Table structure for table \(gaphones\(ga
\-\- 

CREATE TABLE \(gaphones\(ga (
  \(gaID\(ga text NOT NULL,
  \(gaUpdatedInDB\(ga timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  \(gaInsertIntoDB\(ga timestamp NOT NULL default CURRENT_TIMESTAMP,
  \(gaTimeOut\(ga timestamp NOT NULL default CURRENT_TIMESTAMP,
  \(gaSend\(ga enum(\(aqyes\(aq,\(aqno\(aq) NOT NULL default \(aqno\(aq,
  \(gaReceive\(ga enum(\(aqyes\(aq,\(aqno\(aq) NOT NULL default \(aqno\(aq,
  \(gaIMEI\(ga varchar(35) NOT NULL,
  \(gaIMSI\(ga varchar(35) NOT NULL,
  \(gaNetCode\(ga varchar(10) default \(aqERROR\(aq,
  \(gaNetName\(ga varchar(35) default \(aqERROR\(aq,
  \(gaClient\(ga text NOT NULL,
  \(gaBattery\(ga integer NOT NULL DEFAULT \-1,
  \(gaSignal\(ga integer NOT NULL DEFAULT \-1,
  \(gaSent\(ga int NOT NULL DEFAULT 0,
  \(gaReceived\(ga int NOT NULL DEFAULT 0,
  PRIMARY KEY (\(gaIMEI\(ga)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

\-\- 
\-\- Dumping data for table \(gaphones\(ga
\-\- 

\-\- \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

\-\- 
\-\- Table structure for table \(gasentitems\(ga
\-\- 

CREATE TABLE \(gasentitems\(ga (
  \(gaUpdatedInDB\(ga timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  \(gaInsertIntoDB\(ga timestamp NOT NULL default CURRENT_TIMESTAMP,
  \(gaSendingDateTime\(ga timestamp NOT NULL default CURRENT_TIMESTAMP,
  \(gaDeliveryDateTime\(ga timestamp NULL,
  \(gaText\(ga text NOT NULL,
  \(gaDestinationNumber\(ga varchar(20) NOT NULL default \(aq\(aq,
  \(gaCoding\(ga enum(\(aqDefault_No_Compression\(aq,\(aqUnicode_No_Compression\(aq,\(aq8bit\(aq,\(aqDefault_Compression\(aq,\(aqUnicode_Compression\(aq) NOT NULL default \(aqDefault_No_Compression\(aq,
  \(gaUDH\(ga text NOT NULL,
  \(gaSMSCNumber\(ga varchar(20) NOT NULL default \(aq\(aq,
  \(gaClass\(ga integer NOT NULL default \(aq\-1\(aq,
  \(gaTextDecoded\(ga text NOT NULL,
  \(gaID\(ga integer unsigned NOT NULL default \(aq0\(aq,
  \(gaSenderID\(ga varchar(255) NOT NULL,
  \(gaSequencePosition\(ga integer NOT NULL default \(aq1\(aq,
  \(gaStatus\(ga enum(\(aqSendingOK\(aq,\(aqSendingOKNoReport\(aq,\(aqSendingError\(aq,\(aqDeliveryOK\(aq,\(aqDeliveryFailed\(aq,\(aqDeliveryPending\(aq,\(aqDeliveryUnknown\(aq,\(aqError\(aq) NOT NULL default \(aqSendingOK\(aq,
  \(gaStatusError\(ga integer NOT NULL default \(aq\-1\(aq,
  \(gaTPMR\(ga integer NOT NULL default \(aq\-1\(aq,
  \(gaRelativeValidity\(ga integer NOT NULL default \(aq\-1\(aq,
  \(gaCreatorID\(ga text NOT NULL,
  \(gaStatusCode\(ga integer NOT NULL default \(aq\-1\(aq,
  PRIMARY KEY (\(gaID\(ga, \(gaSequencePosition\(ga)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

CREATE INDEX sentitems_date ON sentitems(DeliveryDateTime);
CREATE INDEX sentitems_tpmr ON sentitems(TPMR);
CREATE INDEX sentitems_dest ON sentitems(DestinationNumber);
CREATE INDEX sentitems_sender ON sentitems(SenderID(250));

\-\- 
\-\- Dumping data for table \(gasentitems\(ga
\-\- 

.ft P
.fi
.UNINDENT
.UNINDENT
.sp
\fBNOTE:\fP
.INDENT 0.0
.INDENT 3.5
You can find the script in \fBdocs/sql/mysql.sql\fP as well.
.UNINDENT
.UNINDENT
.sp
SQL script \fBmysql\-legacy.sql\fP for creating tables in MySQL database:
.INDENT 0.0
.INDENT 3.5
.sp
.nf
.ft C
\-\- 
\-\- Database for Gammu SMSD
\-\- 
\-\- In case you get errors about not supported charset, please
\-\- replace utf8mb4 with utf8.

\-\- \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

\-\- 
\-\- Table structure for table \(gagammu\(ga
\-\- 

CREATE TABLE \(gagammu\(ga (
  \(gaVersion\(ga integer NOT NULL default \(aq0\(aq PRIMARY KEY
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

\-\- 
\-\- Dumping data for table \(gagammu\(ga
\-\- 

INSERT INTO \(gagammu\(ga (\(gaVersion\(ga) VALUES (17);

\-\- \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

\-\- 
\-\- Table structure for table \(gainbox\(ga
\-\- 

CREATE TABLE \(gainbox\(ga (
  \(gaUpdatedInDB\(ga timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  \(gaReceivingDateTime\(ga timestamp NOT NULL default \(aq0000\-00\-00 00:00:00\(aq,
  \(gaText\(ga text NOT NULL,
  \(gaSenderNumber\(ga varchar(20) NOT NULL default \(aq\(aq,
  \(gaCoding\(ga enum(\(aqDefault_No_Compression\(aq,\(aqUnicode_No_Compression\(aq,\(aq8bit\(aq,\(aqDefault_Compression\(aq,\(aqUnicode_Compression\(aq) NOT NULL default \(aqDefault_No_Compression\(aq,
  \(gaUDH\(ga text NOT NULL,
  \(gaSMSCNumber\(ga varchar(20) NOT NULL default \(aq\(aq,
  \(gaClass\(ga integer NOT NULL default \(aq\-1\(aq,
  \(gaTextDecoded\(ga text NOT NULL,
  \(gaID\(ga integer unsigned NOT NULL auto_increment,
  \(gaRecipientID\(ga text NOT NULL,
  \(gaProcessed\(ga enum(\(aqfalse\(aq,\(aqtrue\(aq) NOT NULL default \(aqfalse\(aq,
  \(gaStatus\(ga integer NOT NULL default \(aq\-1\(aq,
  PRIMARY KEY \(gaID\(ga (\(gaID\(ga)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1 ;

\-\- 
\-\- Dumping data for table \(gainbox\(ga
\-\- 


\-\- \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

\-\- 
\-\- Table structure for table \(gaoutbox\(ga
\-\- 

CREATE TABLE \(gaoutbox\(ga (
  \(gaUpdatedInDB\(ga timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  \(gaInsertIntoDB\(ga timestamp NOT NULL default \(aq0000\-00\-00 00:00:00\(aq,
  \(gaSendingDateTime\(ga timestamp NOT NULL default \(aq0000\-00\-00 00:00:00\(aq,
  \(gaSendBefore\(ga time NOT NULL DEFAULT \(aq23:59:59\(aq,
  \(gaSendAfter\(ga time NOT NULL DEFAULT \(aq00:00:00\(aq,
  \(gaText\(ga text,
  \(gaDestinationNumber\(ga varchar(20) NOT NULL default \(aq\(aq,
  \(gaCoding\(ga enum(\(aqDefault_No_Compression\(aq,\(aqUnicode_No_Compression\(aq,\(aq8bit\(aq,\(aqDefault_Compression\(aq,\(aqUnicode_Compression\(aq) NOT NULL default \(aqDefault_No_Compression\(aq,
  \(gaUDH\(ga text,
  \(gaClass\(ga integer default \(aq\-1\(aq,
  \(gaTextDecoded\(ga text NOT NULL,
  \(gaID\(ga integer unsigned NOT NULL auto_increment,
  \(gaMultiPart\(ga enum(\(aqfalse\(aq,\(aqtrue\(aq) default \(aqfalse\(aq,
  \(gaRelativeValidity\(ga integer default \(aq\-1\(aq,
  \(gaSenderID\(ga varchar(255),
  \(gaSendingTimeOut\(ga timestamp NULL default \(aq0000\-00\-00 00:00:00\(aq,
  \(gaDeliveryReport\(ga enum(\(aqdefault\(aq,\(aqyes\(aq,\(aqno\(aq) default \(aqdefault\(aq,
  \(gaCreatorID\(ga text NOT NULL,
  \(gaRetries\(ga int(3) default 0,
  \(gaPriority\(ga integer default 0,
  \(gaStatus\(ga enum(\(aqSendingOK\(aq,\(aqSendingOKNoReport\(aq,\(aqSendingError\(aq,\(aqDeliveryOK\(aq,\(aqDeliveryFailed\(aq,\(aqDeliveryPending\(aq,\(aqDeliveryUnknown\(aq,\(aqError\(aq,\(aqReserved\(aq) NOT NULL default \(aqReserved\(aq,
  \(gaStatusCode\(ga integer NOT NULL default \(aq\-1\(aq,
  PRIMARY KEY \(gaID\(ga (\(gaID\(ga)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

CREATE INDEX outbox_date ON outbox(SendingDateTime, SendingTimeOut);
CREATE INDEX outbox_sender ON outbox(SenderID(250));

\-\- 
\-\- Dumping data for table \(gaoutbox\(ga
\-\- 


\-\- \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

\-\- 
\-\- Table structure for table \(gaoutbox_multipart\(ga
\-\- 

CREATE TABLE \(gaoutbox_multipart\(ga (
  \(gaText\(ga text,
  \(gaCoding\(ga enum(\(aqDefault_No_Compression\(aq,\(aqUnicode_No_Compression\(aq,\(aq8bit\(aq,\(aqDefault_Compression\(aq,\(aqUnicode_Compression\(aq) NOT NULL default \(aqDefault_No_Compression\(aq,
  \(gaUDH\(ga text,
  \(gaClass\(ga integer default \(aq\-1\(aq,
  \(gaTextDecoded\(ga text,
  \(gaID\(ga integer unsigned NOT NULL default \(aq0\(aq,
  \(gaSequencePosition\(ga integer NOT NULL default \(aq1\(aq,
  \(gaStatus\(ga enum(\(aqSendingOK\(aq,\(aqSendingOKNoReport\(aq,\(aqSendingError\(aq,\(aqDeliveryOK\(aq,\(aqDeliveryFailed\(aq,\(aqDeliveryPending\(aq,\(aqDeliveryUnknown\(aq,\(aqError\(aq,\(aqReserved\(aq) NOT NULL default \(aqReserved\(aq,
  \(gaStatusCode\(ga integer NOT NULL default \(aq\-1\(aq,
  PRIMARY KEY (\(gaID\(ga, \(gaSequencePosition\(ga)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

\-\- 
\-\- Dumping data for table \(gaoutbox_multipart\(ga
\-\- 

\-\- \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

\-\- 
\-\- Table structure for table \(gaphones\(ga
\-\- 

CREATE TABLE \(gaphones\(ga (
  \(gaID\(ga text NOT NULL,
  \(gaUpdatedInDB\(ga timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  \(gaInsertIntoDB\(ga timestamp NOT NULL default \(aq0000\-00\-00 00:00:00\(aq,
  \(gaTimeOut\(ga timestamp NOT NULL default \(aq0000\-00\-00 00:00:00\(aq,
  \(gaSend\(ga enum(\(aqyes\(aq,\(aqno\(aq) NOT NULL default \(aqno\(aq,
  \(gaReceive\(ga enum(\(aqyes\(aq,\(aqno\(aq) NOT NULL default \(aqno\(aq,
  \(gaIMEI\(ga varchar(35) NOT NULL,
  \(gaIMSI\(ga varchar(35) NOT NULL,
  \(gaNetCode\(ga varchar(10) default \(aqERROR\(aq,
  \(gaNetName\(ga varchar(35) default \(aqERROR\(aq,
  \(gaClient\(ga text NOT NULL,
  \(gaBattery\(ga integer NOT NULL DEFAULT \-1,
  \(gaSignal\(ga integer NOT NULL DEFAULT \-1,
  \(gaSent\(ga int NOT NULL DEFAULT 0,
  \(gaReceived\(ga int NOT NULL DEFAULT 0,
  PRIMARY KEY (\(gaIMEI\(ga)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

\-\- 
\-\- Dumping data for table \(gaphones\(ga
\-\- 

\-\- \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

\-\- 
\-\- Table structure for table \(gasentitems\(ga
\-\- 

CREATE TABLE \(gasentitems\(ga (
  \(gaUpdatedInDB\(ga timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  \(gaInsertIntoDB\(ga timestamp NOT NULL default \(aq0000\-00\-00 00:00:00\(aq,
  \(gaSendingDateTime\(ga timestamp NOT NULL default \(aq0000\-00\-00 00:00:00\(aq,
  \(gaDeliveryDateTime\(ga timestamp NULL,
  \(gaText\(ga text NOT NULL,
  \(gaDestinationNumber\(ga varchar(20) NOT NULL default \(aq\(aq,
  \(gaCoding\(ga enum(\(aqDefault_No_Compression\(aq,\(aqUnicode_No_Compression\(aq,\(aq8bit\(aq,\(aqDefault_Compression\(aq,\(aqUnicode_Compression\(aq) NOT NULL default \(aqDefault_No_Compression\(aq,
  \(gaUDH\(ga text NOT NULL,
  \(gaSMSCNumber\(ga varchar(20) NOT NULL default \(aq\(aq,
  \(gaClass\(ga integer NOT NULL default \(aq\-1\(aq,
  \(gaTextDecoded\(ga text NOT NULL,
  \(gaID\(ga integer unsigned NOT NULL default \(aq0\(aq,
  \(gaSenderID\(ga varchar(255) NOT NULL,
  \(gaSequencePosition\(ga integer NOT NULL default \(aq1\(aq,
  \(gaStatus\(ga enum(\(aqSendingOK\(aq,\(aqSendingOKNoReport\(aq,\(aqSendingError\(aq,\(aqDeliveryOK\(aq,\(aqDeliveryFailed\(aq,\(aqDeliveryPending\(aq,\(aqDeliveryUnknown\(aq,\(aqError\(aq) NOT NULL default \(aqSendingOK\(aq,
  \(gaStatusError\(ga integer NOT NULL default \(aq\-1\(aq,
  \(gaTPMR\(ga integer NOT NULL default \(aq\-1\(aq,
  \(gaRelativeValidity\(ga integer NOT NULL default \(aq\-1\(aq,
  \(gaCreatorID\(ga text NOT NULL,
  \(gaStatusCode\(ga integer NOT NULL default \(aq\-1\(aq,
  PRIMARY KEY (\(gaID\(ga, \(gaSequencePosition\(ga)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

CREATE INDEX sentitems_date ON sentitems(DeliveryDateTime);
CREATE INDEX sentitems_tpmr ON sentitems(TPMR);
CREATE INDEX sentitems_dest ON sentitems(DestinationNumber);
CREATE INDEX sentitems_sender ON sentitems(SenderID(250));

\-\- 
\-\- Dumping data for table \(gasentitems\(ga
\-\- 


\-\- 
\-\- Triggers for setting default timestamps
\-\- 

DELIMITER //

CREATE TRIGGER inbox_timestamp BEFORE INSERT ON inbox
FOR EACH ROW
BEGIN
    IF NEW.ReceivingDateTime = \(aq0000\-00\-00 00:00:00\(aq THEN
        SET NEW.ReceivingDateTime = CURRENT_TIMESTAMP();
    END IF;
END;//

CREATE TRIGGER outbox_timestamp BEFORE INSERT ON outbox
FOR EACH ROW
BEGIN
    IF NEW.InsertIntoDB = \(aq0000\-00\-00 00:00:00\(aq THEN
        SET NEW.InsertIntoDB = CURRENT_TIMESTAMP();
    END IF;
    IF NEW.SendingDateTime = \(aq0000\-00\-00 00:00:00\(aq THEN
        SET NEW.SendingDateTime = CURRENT_TIMESTAMP();
    END IF;
    IF NEW.SendingTimeOut = \(aq0000\-00\-00 00:00:00\(aq THEN
        SET NEW.SendingTimeOut = CURRENT_TIMESTAMP();
    END IF;
END;//

CREATE TRIGGER phones_timestamp BEFORE INSERT ON phones
FOR EACH ROW
BEGIN
    IF NEW.InsertIntoDB = \(aq0000\-00\-00 00:00:00\(aq THEN
        SET NEW.InsertIntoDB = CURRENT_TIMESTAMP();
    END IF;
    IF NEW.TimeOut = \(aq0000\-00\-00 00:00:00\(aq THEN
        SET NEW.TimeOut = CURRENT_TIMESTAMP();
    END IF;
END;//

CREATE TRIGGER sentitems_timestamp BEFORE INSERT ON sentitems
FOR EACH ROW
BEGIN
    IF NEW.InsertIntoDB = \(aq0000\-00\-00 00:00:00\(aq THEN
        SET NEW.InsertIntoDB = CURRENT_TIMESTAMP();
    END IF;
    IF NEW.SendingDateTime = \(aq0000\-00\-00 00:00:00\(aq THEN
        SET NEW.SendingDateTime = CURRENT_TIMESTAMP();
    END IF;
END;//

DELIMITER ;


.ft P
.fi
.UNINDENT
.UNINDENT
.sp
\fBNOTE:\fP
.INDENT 0.0
.INDENT 3.5
You can find the script in \fBdocs/sql/mysql\-legacy.sql\fP as well.
.UNINDENT
.UNINDENT
.SH UPGRADING TABLES
.sp
The easiest way to upgrade database structure is to backup old one and start
with creating new one based on example above.
.sp
For upgrading existing database, you can use changes described in
smsd\-tables\-history and then manually update \fBVersion\fP field in
\fBgammu\fP table.
.SH AUTHOR
Michal Čihař <michal@cihar.com>
.SH COPYRIGHT
2009-2015, Michal Čihař <michal@cihar.com>
.\" Generated by docutils manpage writer.
.