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

FreeBSD Manual Pages

  
 
  

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
       parameters  are defined by configuration	(see gammu-smsdrc for descrip-
       tion 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
	  library does not support this	platform.

CONFIGURATION
       Before  running	gammu-smsd  you	need to	create necessary tables	in the
       database. 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:

       o sqlite3 - for SQLite 3

       o mysql - for MySQL

       o pgsql - for PostgeSQL

       o 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-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-DBI(7)

NAME | DESCRIPTION | CONFIGURATION | SUPPORTED DRIVERS | CREATING TABLES FOR SQLITE | UPGRADING TABLES | AUTHOR | COPYRIGHT

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

home | help