Subversion Repositories ALCASAR

Compare Revisions

Ignore whitespace Rev 1342 → Rev 1349

/conf/gammu-smsd-db-vierge.sql
0,0 → 1,295
-- phpMyAdmin SQL Dump
-- version 3.5.8.2
-- http://www.phpmyadmin.net
--
-- Client: localhost
-- Généré le: Mar 06 Mai 2014 à 10:39
-- Version du serveur: 5.5.25-MariaDB
-- Version de PHP: 5.3.27
 
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
 
 
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
 
--
-- Base de données: `gammu`
--
 
-- --------------------------------------------------------
 
--
-- Structure de la table `daemons`
--
 
CREATE TABLE IF NOT EXISTS `daemons` (
`Start` text NOT NULL,
`Info` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
-- --------------------------------------------------------
 
--
-- Structure de la table `gammu`
--
 
CREATE TABLE IF NOT EXISTS `gammu` (
`Version` int(11) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
--
-- Contenu de la table `gammu`
--
 
INSERT INTO `gammu` (`Version`) VALUES
(13);
 
-- --------------------------------------------------------
 
--
-- Structure de la table `inbox`
--
 
CREATE TABLE IF NOT EXISTS `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` int(11) NOT NULL DEFAULT '-1',
`TextDecoded` text NOT NULL,
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`RecipientID` text NOT NULL,
`Processed` enum('false','true') NOT NULL DEFAULT 'false',
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=18 ;
 
--
-- Déclencheurs `inbox`
--
DROP TRIGGER IF EXISTS `inbox_timestamp`;
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
//
DELIMITER ;
 
-- --------------------------------------------------------
 
--
-- Structure de la table `outbox`
--
 
CREATE TABLE IF NOT EXISTS `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` int(11) DEFAULT '-1',
`TextDecoded` text NOT NULL,
`ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`MultiPart` enum('false','true') DEFAULT 'false',
`RelativeValidity` int(11) DEFAULT '-1',
`SenderID` varchar(255) DEFAULT NULL,
`SendingTimeOut` timestamp NULL DEFAULT '0000-00-00 00:00:00',
`DeliveryReport` enum('default','yes','no') DEFAULT 'default',
`CreatorID` text NOT NULL,
PRIMARY KEY (`ID`),
KEY `outbox_date` (`SendingDateTime`,`SendingTimeOut`),
KEY `outbox_sender` (`SenderID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
 
--
-- Déclencheurs `outbox`
--
DROP TRIGGER IF EXISTS `outbox_timestamp`;
DELIMITER //
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
//
DELIMITER ;
 
-- --------------------------------------------------------
 
--
-- Structure de la table `outbox_multipart`
--
 
CREATE TABLE IF NOT EXISTS `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` int(11) DEFAULT '-1',
`TextDecoded` text,
`ID` int(10) unsigned NOT NULL DEFAULT '0',
`SequencePosition` int(11) NOT NULL DEFAULT '1',
PRIMARY KEY (`ID`,`SequencePosition`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
-- --------------------------------------------------------
 
--
-- Structure de la table `pbk`
--
 
CREATE TABLE IF NOT EXISTS `pbk` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`GroupID` int(11) NOT NULL DEFAULT '-1',
`Name` text NOT NULL,
`Number` text NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
 
-- --------------------------------------------------------
 
--
-- Structure de la table `pbk_groups`
--
 
CREATE TABLE IF NOT EXISTS `pbk_groups` (
`Name` text NOT NULL,
`ID` int(11) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
 
-- --------------------------------------------------------
 
--
-- Structure de la table `phones`
--
 
CREATE TABLE IF NOT EXISTS `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` int(11) NOT NULL DEFAULT '-1',
`Signal` int(11) NOT NULL DEFAULT '-1',
`Sent` int(11) NOT NULL DEFAULT '0',
`Received` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`IMEI`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
 
--
-- Déclencheurs `phones`
--
DROP TRIGGER IF EXISTS `phones_timestamp`;
DELIMITER //
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
//
DELIMITER ;
 
-- --------------------------------------------------------
 
--
-- Structure de la table `sentitems`
--
 
CREATE TABLE IF NOT EXISTS `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 DEFAULT 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` int(11) NOT NULL DEFAULT '-1',
`TextDecoded` text NOT NULL,
`ID` int(10) unsigned NOT NULL DEFAULT '0',
`SenderID` varchar(255) NOT NULL,
`SequencePosition` int(11) NOT NULL DEFAULT '1',
`Status` enum('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending','DeliveryUnknown','Error') NOT NULL DEFAULT 'SendingOK',
`StatusError` int(11) NOT NULL DEFAULT '-1',
`TPMR` int(11) NOT NULL DEFAULT '-1',
`RelativeValidity` int(11) NOT NULL DEFAULT '-1',
`CreatorID` text NOT NULL,
PRIMARY KEY (`ID`,`SequencePosition`),
KEY `sentitems_date` (`DeliveryDateTime`),
KEY `sentitems_tpmr` (`TPMR`),
KEY `sentitems_dest` (`DestinationNumber`),
KEY `sentitems_sender` (`SenderID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
--
-- Déclencheurs `sentitems`
--
DROP TRIGGER IF EXISTS `sentitems_timestamp`;
DELIMITER //
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 ;
 
-- --------------------------------------------------------
 
--
-- Structure de la table `SMS_ban_perm`
--
 
CREATE TABLE IF NOT EXISTS `SMS_ban_perm` (
`SenderNumber` varchar(20) CHARACTER SET utf8 NOT NULL,
`Expiration` varchar(255) CHARACTER SET utf8 NOT NULL,
`Perm` int(11) NOT NULL,
`date_add` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`SenderNumber`)
) ENGINE=MyISAM DEFAULT CHARSET=utf32;
 
 
-- --------------------------------------------------------
 
--
-- Structure de la table `SMS_ban_temp`
--
 
CREATE TABLE IF NOT EXISTS `SMS_ban_temp` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`SenderNumber` varchar(20) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
/conf/logrotate.d/gammu
0,0 → 1,7
/var/log/gammu-smsd/gammu-smsd.log {
monthly
rotate 12
nocreate
missingok
compress
}
/conf/sudoers
6,7 → 6,7
#
 
# Host alias specification
Host_Alias LAN_ORG=172.16.0.0/255.255.0.0,localhost #réseau de l'organisme
Host_Alias LAN_ORG=192.168.182.0/255.255.255.0,localhost #réseau de l'organisme
# User alias specification
User_Alias ADMIN=sysadmin # compte d'admin local de l'organisme
User_Alias ADMWEB=apache # compte lié à l'interface de gestion
25,6 → 25,7
Cmnd_Alias UAM=/usr/local/sbin/alcasar-uamallowed.sh # pour gérer les site de confiance (uamallowed)
Cmnd_Alias SERVICE=/sbin/service,/usr/bin/killall,/sbin/chkconfig,/bin/systemctl # pour gérer les services
Cmnd_Alias SSL=/usr/bin/openssl # pour récupérer les info des certificats
Cmnd_Alias GAMMU=/sbin/pidof,/usr/local/bin/alcasar-sms.sh # permet l'execution de gammu (droit root)
 
# Defaults specification
# Defaults syslog=auth
44,6 → 45,6
# %users ALL=/sbin/mount /cdrom,/sbin/umount /cdrom
# %users localhost=/sbin/shutdown -h now
 
ADMWEB LAN_ORG=(root) NOPASSWD: NET,SYSTEM_BACKUP,SQL,BL,NF,EXPORT,RADDB,LOGOUT,UAM,SERVICE,SSL
ADMIN LAN_ORG=(root) NOPASSWD: NET,/sbin/poweroff,/sbin/shutdown -h now,/sbin/reboot,URPMI,BYPASS,SYSTEM_BACKUP,SQL,EXPORT,SERVICE
ADMWEB LAN_ORG=(root) NOPASSWD: NET,SYSTEM_BACKUP,SQL,BL,NF,EXPORT,RADDB,LOGOUT,UAM,SERVICE,SSL,GAMMU
ADMIN LAN_ORG=(root) NOPASSWD: NET,/sbin/poweroff,/sbin/shutdown -h now,/sbin/reboot,URPMI,BYPASS,SYSTEM_BACKUP,SQL,EXPORT,SERVICE,GAMMU