Provided by: gammu-smsd_1.30.0-2_i386 bug

NAME

       gammu-smsd-mysql - gammu-smsd(1) backend using MySQL database server as
       a message storage

       MYSQL backend stores  all  data  in  a  MySQL  database  server,  which
       parameters   are   defined   by  configuration  (see  gammu-smsdrc  for
       description of configuration options).

       For tables description see gammu-smsd-tables.

       This backend is based on gammu-smsd-sql.

PRIVILEGES

       The user accessing the database does  not  need  much  privileges,  the
       following privleges should be enough:

       GRANT USAGE ON *.* TO 'smsd'@'localhost' IDENTIFIED BY 'password';

       GRANT SELECT, INSERT, UPDATE, DELETE ON `smsd`.* TO 'smsd'@'localhost';

       Note   For creating the SQL tables you need more privileges, especially
              for creating triggers, which are used for some functionality.

EXAMPLE

       SQL script for creating tables in MySQL database:

       -- phpMyAdmin SQL Dump
       -- version 2.8.0.3
       -- http://www.phpmyadmin.net
       --
       -- Host: localhost
       -- Generation Time: Jun 10, 2006 at 11:08 PM
       -- Server version: 5.0.18
       -- PHP Version: 5.1.3
       --
       -- Database: `smsd`
       --

       -- --------------------------------------------------------

       --
       -- Table structure for table `daemons`
       --

       CREATE TABLE `daemons` (
         `Start` text NOT NULL,
         `Info` text NOT NULL
       ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

       --
       -- Dumping data for table `daemons`
       --

       -- --------------------------------------------------------

       --
       -- Table structure for table `gammu`
       --

       CREATE TABLE `gammu` (
         `Version` integer NOT NULL default '0'
       ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

       --
       -- Dumping data for table `gammu`
       --

       INSERT INTO `gammu` (`Version`) VALUES (13);

       -- --------------------------------------------------------

       --
       -- Table structure for table `inbox`
       --

       CREATE TABLE `inbox` (
         `UpdatedInDB` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
         `ReceivingDateTime` timestamp NOT NULL default '0000-00-00 00:00:00',
         `Text` text NOT NULL,
         `SenderNumber` varchar(20) NOT NULL default '',
         `Coding` enum('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression') NOT NULL default 'Default_No_Compression',
         `UDH` text NOT NULL,
         `SMSCNumber` varchar(20) NOT NULL default '',
         `Class` integer NOT NULL default '-1',
         `TextDecoded` text NOT NULL default '',
         `ID` integer unsigned NOT NULL auto_increment,
         `RecipientID` text NOT NULL,
         `Processed` enum('false','true') NOT NULL default 'false',
         PRIMARY KEY `ID` (`ID`)
       ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

       --
       -- Dumping data for table `inbox`
       --

       -- --------------------------------------------------------

       --
       -- Table structure for table `outbox`
       --

       CREATE TABLE `outbox` (
         `UpdatedInDB` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
         `InsertIntoDB` timestamp NOT NULL default '0000-00-00 00:00:00',
         `SendingDateTime` timestamp NOT NULL default '0000-00-00 00:00:00',
         `SendBefore` time NOT NULL DEFAULT '23:59:59',
         `SendAfter` time NOT NULL DEFAULT '00:00:00',
         `Text` text,
         `DestinationNumber` varchar(20) NOT NULL default '',
         `Coding` enum('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression') NOT NULL default 'Default_No_Compression',
         `UDH` text,
         `Class` integer default '-1',
         `TextDecoded` text NOT NULL default '',
         `ID` integer unsigned NOT NULL auto_increment,
         `MultiPart` enum('false','true') default 'false',
         `RelativeValidity` integer default '-1',
         `SenderID` varchar(255),
         `SendingTimeOut` timestamp NULL default '0000-00-00 00:00:00',
         `DeliveryReport` enum('default','yes','no') default 'default',
         `CreatorID` text NOT NULL,
         PRIMARY KEY `ID` (`ID`)
       ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

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

       --
       -- Dumping data for table `outbox`
       --

       -- --------------------------------------------------------

       --
       -- Table structure for table `outbox_multipart`
       --

       CREATE TABLE `outbox_multipart` (
         `Text` text,
         `Coding` enum('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression') NOT NULL default 'Default_No_Compression',
         `UDH` text,
         `Class` integer default '-1',
         `TextDecoded` text default NULL,
         `ID` integer unsigned NOT NULL default '0',
         `SequencePosition` integer NOT NULL default '1',
         PRIMARY KEY (`ID`, `SequencePosition`)
       ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

       --
       -- Dumping data for table `outbox_multipart`
       --

       -- --------------------------------------------------------

       --
       -- Table structure for table `pbk`
       --

       CREATE TABLE `pbk` (
         `ID` integer NOT NULL auto_increment,
         `GroupID` integer NOT NULL default '-1',
         `Name` text NOT NULL,
         `Number` text NOT NULL,
         PRIMARY KEY (`ID`)
       ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

       --
       -- Dumping data for table `pbk`
       --

       -- --------------------------------------------------------

       --
       -- Table structure for table `pbk_groups`
       --

       CREATE TABLE `pbk_groups` (
         `Name` text NOT NULL,
         `ID` integer NOT NULL auto_increment,
         PRIMARY KEY `ID` (`ID`)
       ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

       --
       -- Dumping data for table `pbk_groups`
       --

       -- --------------------------------------------------------

       --
       -- Table structure for table `phones`
       --

       CREATE TABLE `phones` (
         `ID` text NOT NULL,
         `UpdatedInDB` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
         `InsertIntoDB` timestamp NOT NULL default '0000-00-00 00:00:00',
         `TimeOut` timestamp NOT NULL default '0000-00-00 00:00:00',
         `Send` enum('yes','no') NOT NULL default 'no',
         `Receive` enum('yes','no') NOT NULL default 'no',
         `IMEI` varchar(35) NOT NULL,
         `Client` text NOT NULL,
         `Battery` integer NOT NULL DEFAULT -1,
         `Signal` integer NOT NULL DEFAULT -1,
         `Sent` int NOT NULL DEFAULT 0,
         `Received` int NOT NULL DEFAULT 0,
         PRIMARY KEY (`IMEI`)
       ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

       --
       -- Dumping data for table `phones`
       --

       -- --------------------------------------------------------

       --
       -- Table structure for table `sentitems`
       --

       CREATE TABLE `sentitems` (
         `UpdatedInDB` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
         `InsertIntoDB` timestamp NOT NULL default '0000-00-00 00:00:00',
         `SendingDateTime` timestamp NOT NULL default '0000-00-00 00:00:00',
         `DeliveryDateTime` timestamp NULL,
         `Text` text NOT NULL,
         `DestinationNumber` varchar(20) NOT NULL default '',
         `Coding` enum('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression') NOT NULL default 'Default_No_Compression',
         `UDH` text NOT NULL,
         `SMSCNumber` varchar(20) NOT NULL default '',
         `Class` integer NOT NULL default '-1',
         `TextDecoded` text NOT NULL default '',
         `ID` integer unsigned NOT NULL default '0',
         `SenderID` varchar(255) NOT NULL,
         `SequencePosition` integer NOT NULL default '1',
         `Status` enum('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending','DeliveryUnknown','Error') NOT NULL default 'SendingOK',
         `StatusError` integer NOT NULL default '-1',
         `TPMR` integer NOT NULL default '-1',
         `RelativeValidity` integer NOT NULL default '-1',
         `CreatorID` text NOT NULL,
         PRIMARY KEY (`ID`, `SequencePosition`)
       ) ENGINE=MyISAM DEFAULT CHARSET=utf8;

       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);

       --
       -- Dumping data for table `sentitems`
       --

       --
       -- Triggers for setting default timestamps
       --

       DELIMITER //

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

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

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

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

       DELIMITER ;

       Note   You can find the script in docs/sql/mysql.sql as well.

AUTHOR

       Michal iha <michal@cihar.com>

COPYRIGHT

       2009-2011, Michal iha <michal@cihar.com>