Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages

  
 
  

home | help
GAMMU-SMSD-MYSQL(7)		     Gammu		   GAMMU-SMSD-MYSQL(7)

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

DESCRIPTION
       MYSQL backend stores all	data in	a MySQL	database server, which parame-
       ters  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.

CONFIGURATION
       Before running gammu-smsd you need to create necessary  tables  in  the
       database, which is described below.

       The configuration file then can look like:

	  [smsd]
	  service = sql
	  driver = native_mysql
	  host = localhost

       SEE ALSO:
	  gammu-smsdrc

PRIVILEGES
       The user	accessing the database does not	need much privileges, the fol-
       lowing privileges should	be enough:

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

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

	  CREATE DATABASE smsd;

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

CREATING TABLES	FOR MYSQL
       Depending  on  MySQL  version  and  settings please choose best fitting
       script to create	tables:

       o mysql.sql, requires MySQL 5.6.5 or newer

       o mysql-legacy.sql supports legacy MySQL	versions, but requires neither
	 of NO_ZERO_DATE, ANSI or STRICT modes to be set in the	server

       SQL script mysql.sql for	creating tables	in MySQL database:

	  --
	  -- Database for Gammu	SMSD
	  --
	  -- In	case you get errors about not supported	charset, please
	  -- replace utf8mb4 with utf8.

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

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

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

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

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

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

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

	  CREATE TABLE `inbox` (
	    `UpdatedInDB` timestamp NOT	NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
	    `ReceivingDateTime`	timestamp NOT NULL default CURRENT_TIMESTAMP,
	    `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,
	    `ID` integer unsigned NOT NULL auto_increment,
	    `RecipientID` text NOT NULL,
	    `Processed`	enum('false','true') NOT NULL default 'false',
	    `Status` integer NOT NULL default '-1',
	    PRIMARY KEY	`ID` (`ID`)
	  ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 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 CURRENT_TIMESTAMP,
	    `SendingDateTime` timestamp	NOT NULL default CURRENT_TIMESTAMP,
	    `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,
	    `ID` integer unsigned NOT NULL auto_increment,
	    `MultiPart`	enum('false','true') default 'false',
	    `RelativeValidity` integer default '-1',
	    `SenderID` varchar(255),
	    `SendingTimeOut` timestamp NULL default CURRENT_TIMESTAMP,
	    `DeliveryReport` enum('default','yes','no')	default	'default',
	    `CreatorID`	text NOT NULL,
	    `Retries` int(3) default 0,
	    `Priority` integer default 0,
	    `Status` enum('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending','DeliveryUnknown','Error','Reserved') NOT NULL	default	'Reserved',
	    `StatusCode` integer NOT NULL default '-1',
	    PRIMARY KEY	`ID` (`ID`)
	  ) 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 `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,
	    `ID` integer unsigned NOT NULL default '0',
	    `SequencePosition` integer NOT NULL	default	'1',
	    `Status` enum('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending','DeliveryUnknown','Error','Reserved') NOT NULL	default	'Reserved',
	    `StatusCode` integer NOT NULL default '-1',
	    PRIMARY KEY	(`ID`, `SequencePosition`)
	  ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

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

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

	  --
	  -- 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 CURRENT_TIMESTAMP,
	    `TimeOut` timestamp	NOT NULL default CURRENT_TIMESTAMP,
	    `Send` enum('yes','no') NOT	NULL default 'no',
	    `Receive` enum('yes','no') NOT NULL	default	'no',
	    `IMEI` varchar(35) NOT NULL,
	    `IMSI` varchar(35) NOT NULL,
	    `NetCode` varchar(10) default 'ERROR',
	    `NetName` varchar(35) default 'ERROR',
	    `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=utf8mb4;

	  --
	  -- 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 CURRENT_TIMESTAMP,
	    `SendingDateTime` timestamp	NOT NULL default CURRENT_TIMESTAMP,
	    `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,
	    `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,
	    `StatusCode` integer NOT NULL default '-1',
	    PRIMARY KEY	(`ID`, `SequencePosition`)
	  ) 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 `sentitems`
	  --

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

       SQL script mysql-legacy.sql for creating	tables in MySQL	database:

	  --
	  -- Database for Gammu	SMSD
	  --
	  -- In	case you get errors about not supported	charset, please
	  -- replace utf8mb4 with utf8.

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

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

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

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

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

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

	  --
	  -- 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,
	    `ID` integer unsigned NOT NULL auto_increment,
	    `RecipientID` text NOT NULL,
	    `Processed`	enum('false','true') NOT NULL default 'false',
	    `Status` integer NOT NULL default '-1',
	    PRIMARY KEY	`ID` (`ID`)
	  ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 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,
	    `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,
	    `Retries` int(3) default 0,
	    `Priority` integer default 0,
	    `Status` enum('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending','DeliveryUnknown','Error','Reserved') NOT NULL	default	'Reserved',
	    `StatusCode` integer NOT NULL default '-1',
	    PRIMARY KEY	`ID` (`ID`)
	  ) 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 `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,
	    `ID` integer unsigned NOT NULL default '0',
	    `SequencePosition` integer NOT NULL	default	'1',
	    `Status` enum('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending','DeliveryUnknown','Error','Reserved') NOT NULL	default	'Reserved',
	    `StatusCode` integer NOT NULL default '-1',
	    PRIMARY KEY	(`ID`, `SequencePosition`)
	  ) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

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

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

	  --
	  -- 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,
	    `IMSI` varchar(35) NOT NULL,
	    `NetCode` varchar(10) default 'ERROR',
	    `NetName` varchar(35) default 'ERROR',
	    `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=utf8mb4;

	  --
	  -- 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,
	    `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,
	    `StatusCode` integer NOT NULL default '-1',
	    PRIMARY KEY	(`ID`, `SequencePosition`)
	  ) 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 `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-legacy.sql as well.

UPGRADING TABLES
       The  easiest way	to upgrade database structure is to backup old one and
       start with creating new one based on example above.

       For upgrading existing database,	 you  can  use	changes	 described  in
       smsd-tables-history and then manually update Version field in gammu ta-
       ble.

AUTHOR
       Michal AihaA <michal@cihar.com>

COPYRIGHT
       2009-2015, Michal AihaA <michal@cihar.com>

1.41.0				 Sep 27, 2019		   GAMMU-SMSD-MYSQL(7)

NAME | DESCRIPTION | CONFIGURATION | PRIVILEGES | CREATING TABLES FOR MYSQL | UPGRADING TABLES | AUTHOR | COPYRIGHT

Want to link to this manual page? Use this URL:
<https://www.freebsd.org/cgi/man.cgi?query=gammu-smsd-mysql&sektion=7&manpath=FreeBSD+12.2-RELEASE+and+Ports>

home | help