# Configuration file for Gammu SMS Daemon
# Gammu library configuration, see gammurc(5)
[gammu]
port = /dev/ttyUSB0
connection = at115200
# Debugging
#logformat = textall
# SMSD configuration, see gammu-smsdrc(5)
[smsd]
#service = files
Service = sql
Driver = native_mysql
LogFile = syslog
User = gammu
Password = password
PC = localhost
Database = gammu
PhoneID = gsm-01
# Increase for debugging information
debuglevel = 2
commtimeout = 1
sendtimeout = 15
statusfrequence = 0
outboxformat = unicode
transmitformat = unicode
RunOnReceive = node /var/spool/gammu/hellomsg.js
# Paths where messages are stored
inboxpath = /var/spool/gammu/inbox/
outboxpath = /var/spool/gammu/outbox/
sentsmspath = /var/spool/gammu/sent/
errorsmspath = /var/spool/gammu/error/
Last active
October 28, 2016 10:32
-
-
Save tomysmile/9258bb875583413f4832a2012f2f150a to your computer and use it in GitHub Desktop.
Gammu - MySQL DB Structure for MySQL 5.7
- SenderID uses varchar(50) instead of 255
- Timestamp can only be used once, so the rest are replaced with DateTime
- Trigger also get replaced from timestamp() to now()
CREATE DATABASE gammu;
GRANT USAGE ON *.* TO 'gammu'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON `gammu`.* TO 'gammu'@'localhost';
FLUSH PRIVILEGES;
bash $> mysql -u root -p gammu < smsd.sql
--
-- 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 (16);
-- --------------------------------------------------------
--
-- Table structure for table `inbox`
--
CREATE TABLE `inbox` (
`UpdatedInDB` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
`ReceivingDateTime` timestamp NULL,
`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',
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 NULL,
`SendingDateTime` timestamp NULL,
`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(50),
`SendingTimeOut` timestamp NULL,
`DeliveryReport` enum('default','yes','no') default 'default',
`CreatorID` text NOT NULL,
`Retries` int(3) default 0,
`Priority` integer default 0,
PRIMARY KEY `ID` (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
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,
`ID` integer unsigned NOT NULL default '0',
`SequencePosition` 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 NULL,
`TimeOut` timestamp NULL,
`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 NULL,
`SendingDateTime` timestamp NULL,
`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(50) 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=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);
--
-- 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 ;
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment