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

NAME
     gammu-smsd-dbi  -	gammu-smsd(1) backend using DBI abstraction layer to use
     any supported database as a message storage

DESCRIPTION
     DBI backend stores all data in any database supported by libdbi, which  pa-
     rameters  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.

     NOTE:
	The DBI driver is currently not supported on Windows because libdbi  li-
	brary does not support this platform.

CONFIGURATION
     Before  running gammu-smsd you need to create necessary tables in the data-
     base. You can use examples given in database  specific  backends  parts  of
     this manual to do that.

     The configuration file then can look like:

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

     SEE ALSO:
	gammu-smsdrc

SUPPORTED DRIVERS
     For  complete  list  of  drivers for libdbi see libdbi-drivers project. The
     drivers for example include:

     * sqlite3 - for SQLite 3

     * mysql - for MySQL

     * pgsql - for PostgeSQL

     * freetds - for MS SQL Server or Sybase

CREATING TABLES FOR SQLITE
     SQL script for creating tables in SQLite database:

	CREATE TABLE gammu (
	  Version INTEGER NOT NULL DEFAULT '0' PRIMARY KEY
	);

	INSERT INTO gammu (Version) VALUES (17);

	CREATE TABLE inbox (
	  UpdatedInDB NUMERIC NOT NULL DEFAULT (datetime('now')),
	  ReceivingDateTime NUMERIC NOT NULL DEFAULT (datetime('now')),
	  Text TEXT NOT NULL,
	  SenderNumber TEXT NOT NULL DEFAULT '',
	  Coding TEXT NOT NULL DEFAULT 'Default_No_Compression',
	  UDH TEXT NOT NULL,
	  SMSCNumber TEXT NOT NULL DEFAULT '',
	  Class INTEGER NOT NULL DEFAULT '-1',
	  TextDecoded TEXT NOT NULL DEFAULT '',
	  ID INTEGER PRIMARY KEY AUTOINCREMENT,
	  RecipientID TEXT NOT NULL,
	  Processed TEXT NOT NULL DEFAULT 'false',
	  Status INTEGER NOT NULL DEFAULT '-1',
	  CHECK (Coding IN
	  ('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression'))
	);

	CREATE TRIGGER update_inbox_time UPDATE ON inbox
	  BEGIN
	    UPDATE inbox SET UpdatedInDB = datetime('now') WHERE ID = old.ID;
	  END;

	CREATE TABLE outbox (
	  UpdatedInDB NUMERIC NOT NULL DEFAULT (datetime('now')),
	  InsertIntoDB NUMERIC NOT NULL DEFAULT (datetime('now')),
	  SendingDateTime NUMERIC NOT NULL DEFAULT (datetime('now')),
	  SendBefore time NOT NULL DEFAULT '23:59:59',
	  SendAfter time NOT NULL DEFAULT '00:00:00',
	  Text TEXT,
	  DestinationNumber TEXT NOT NULL DEFAULT '',
	  Coding TEXT NOT NULL DEFAULT 'Default_No_Compression',
	  UDH TEXT,
	  Class INTEGER DEFAULT '-1',
	  TextDecoded TEXT NOT NULL DEFAULT '',
	  ID INTEGER PRIMARY KEY AUTOINCREMENT,
	  MultiPart TEXT NOT NULL DEFAULT 'false',
	  RelativeValidity INTEGER DEFAULT '-1',
	  SenderID TEXT,
	  SendingTimeOut NUMERIC NOT NULL DEFAULT (datetime('now')),
	  DeliveryReport TEXT DEFAULT 'default',
	  CreatorID TEXT NOT NULL,
	  Retries INTEGER DEFAULT '0',
	  Priority INTEGER DEFAULT '0',
	  Status TEXT NOT NULL DEFAULT 'Reserved',
	  StatusCode INTEGER NOT NULL DEFAULT '-1',
	  CHECK (Coding IN
	  ('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression')),
	  CHECK (DeliveryReport IN ('default','yes','no')),
	  CHECK (Status IN
	  ('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending',
	  'DeliveryUnknown','Error','Reserved'))
	);

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

	CREATE TRIGGER update_outbox_time UPDATE ON outbox
	  BEGIN
	    UPDATE outbox SET UpdatedInDB = datetime('now') WHERE ID = old.ID;
	  END;

	CREATE TABLE outbox_multipart (
	  Text TEXT,
	  Coding TEXT NOT NULL DEFAULT 'Default_No_Compression',
	  UDH TEXT,
	  Class INTEGER DEFAULT '-1',
	  TextDecoded TEXT DEFAULT NULL,
	  ID INTEGER,
	  SequencePosition INTEGER NOT NULL DEFAULT '1',
	  Status TEXT NOT NULL DEFAULT 'Reserved',
	  StatusCode INTEGER NOT NULL DEFAULT '-1',
	  CHECK (Coding IN
	  ('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression')),
	  CHECK (Status IN
	  ('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending',
	  'DeliveryUnknown','Error','Reserved')),
	 PRIMARY KEY (ID, SequencePosition)
	);

	CREATE TABLE phones (
	  ID TEXT NOT NULL,
	  UpdatedInDB NUMERIC NOT NULL DEFAULT (datetime('now')),
	  InsertIntoDB NUMERIC NOT NULL DEFAULT (datetime('now')),
	  TimeOut NUMERIC NOT NULL DEFAULT (datetime('now')),
	  Send TEXT NOT NULL DEFAULT 'no',
	  Receive TEXT NOT NULL DEFAULT 'no',
	  IMEI TEXT PRIMARY KEY NOT NULL,
	  IMSI TEXT NOT NULL,
	  NetCode TEXT DEFAULT 'ERROR',
	  NetName TEXT DEFAULT 'ERROR',
	  Client TEXT NOT NULL,
	  Battery INTEGER NOT NULL DEFAULT -1,
	  Signal INTEGER NOT NULL DEFAULT -1,
	  Sent INTEGER NOT NULL DEFAULT 0,
	  Received INTEGER NOT NULL DEFAULT 0
	);

	CREATE TRIGGER update_phones_time UPDATE ON phones
	  BEGIN
	    UPDATE phones SET UpdatedInDB = datetime('now') WHERE IMEI = old.IMEI;
	  END;

	CREATE TABLE sentitems (
	  UpdatedInDB NUMERIC NOT NULL DEFAULT (datetime('now')),
	  InsertIntoDB NUMERIC NOT NULL DEFAULT (datetime('now')),
	  SendingDateTime NUMERIC NOT NULL DEFAULT (datetime('now')),
	  DeliveryDateTime NUMERIC NULL,
	  Text TEXT NOT NULL,
	  DestinationNumber TEXT NOT NULL DEFAULT '',
	  Coding TEXT NOT NULL DEFAULT 'Default_No_Compression',
	  UDH TEXT NOT NULL,
	  SMSCNumber TEXT NOT NULL DEFAULT '',
	  Class INTEGER NOT NULL DEFAULT '-1',
	  TextDecoded TEXT NOT NULL DEFAULT '',
	  ID INTEGER,
	  SenderID TEXT NOT NULL,
	  SequencePosition INTEGER NOT NULL DEFAULT '1',
	  Status TEXT 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',
	  CHECK (Status IN
	  ('SendingOK','SendingOKNoReport','SendingError','DeliveryOK','DeliveryFailed','DeliveryPending',
	  'DeliveryUnknown','Error')),
	  CHECK (Coding IN
	  ('Default_No_Compression','Unicode_No_Compression','8bit','Default_Compression','Unicode_Compression')) ,
	  PRIMARY KEY (ID, SequencePosition)
	);

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

	CREATE TRIGGER update_sentitems_time UPDATE ON sentitems
	  BEGIN
	    UPDATE sentitems SET UpdatedInDB = datetime('now') WHERE ID = old.ID;
	  END;

     NOTE:
	You can find the script in docs/sql/sqlite.sql as well. There  are  also
	scripts for other databases in same folder.

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-ta-
     bles-history and then manually update Version field in gammu table.

AUTHOR
     Michal AihaA <michal@cihar.com>

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

1.42.0				  Oct 03, 2020		       GAMMU-SMSD-DBI(7)
home | help