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

FreeBSD Manual Pages

  
 
  

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

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

DESCRIPTION
       SQL service stores all its data in database. It can use	one  of	 these
       SQL backends (configuration option Driver in smsd section):

       o native_mysql for gammu-smsd-mysql

       o native_pgsql for gammu-smsd-pgsql

       o odbc for gammu-smsd-odbc

       o

	 drivers supported by DBI for gammu-smsd-dbi, which include:

		o sqlite3 - for	SQLite 3

		o mysql	- for MySQL

		o pgsql	- for PostgeSQL

		o freetds - for	MS SQL Server or Sybase

SQL CONNECTION PARAMETERS
       Common for all backends:

       o User -	user connecting	to database

       o Password - password for connecting to database

       o Host -	database host or data source name

       o Database - database name

       o Driver	- native_mysql,	native_pgsql, odbc or DBI one

       o SQL - SQL dialect to use

       Specific	for DBI:

       o DriversPath - path to DBI drivers

       o DBDir - sqlite/sqlite3	directory with database

       SEE ALSO:
	  The variables	are fully described in gammurc documentation.

TABLES
       New in version 1.37.1.

       You  can	 customize name	of all tables in the [tables]. The SQL queries
       will reflect this, so it's enough to change table name in this section.

       gammu  Name of the gammu-table table.

       inbox  Name of the inbox	table.

       sentitems
	      Name of the sentitems table.

       outbox Name of the outbox table.

       outbox_multipart
	      Name of the outbox_multipart table.

       phones Name of the phones table.

       You can change any table	name using these:

	  [tables]
	  inbox	= special_inbox

SQL QUERIES
       Almost all queries are configurable. You	can edit them  in  [sql]  sec-
       tion.  There are	several	variables used in SQL queries. We can separate
       them into three groups:

       o phone specific, which can be used in every query, see Phone  Specific
	 Parameters

       o SMS  specific,	which can be used in queries which works with SMS mes-
	 sages,	see SMS	Specific Parameters

       o query specific, which are numeric and are  specific  only  for	 given
	 query (or set of queries), see	Configurable queries

   Phone Specific Parameters
       %I     IMEI of phone

       %S     SIM IMSI

       %P     PHONE ID (hostname)

       %N     client name (eg. Gammu 1.12.3)

       %O     network code

       %M     network name

   SMS Specific	Parameters
       %R     remote number [1]

       %C     delivery datetime

       %e     delivery status on receiving or status error on sending

       %t     message reference

       %d     receiving	datetime for received sms

       %E     encoded text of SMS

       %c     SMS coding (ie 8bit or UnicodeNoCompression)

       %F     sms centre number

       %u     UDH header

       %x     class

       %T     decoded SMS text

       %A     CreatorID	of SMS (sending	sms)

       %V     relative validity

       [1]  Sender  number  for	received messages (insert to inbox or delivery
	    notifications), destination	otherwise.

CONFIGURABLE QUERIES
       All configurable	queries	can be set in [sql] section. Sequence of  rows
       in selects are mandatory.

       All  default  queries  noted  here are noted for	MySQL. Actual time and
       time addition are selected for default queries during initialization.

       delete_phone
	      Deletes phone from database.

	      Default value:

		 DELETE	FROM phones WHERE IMEI = %I

       insert_phone
	      Inserts phone to database.

	      Default value:

		 INSERT	INTO phones (IMEI, ID, Send, Receive, InsertIntoDB, TimeOut, Client, Battery, Signal)
		 VALUES	(%I, %P, %1, %2, NOW(),	(NOW() + INTERVAL 10 SECOND) + 0, %N, -1, -1)

	      Query specific parameters:

	      %1     enable send (yes or no) - configuration option Send

	      %2     enable receive (yes or no)	 -  configuration  option  Re-
		     ceive

       save_inbox_sms_select
	      Select message for update	delivery status.

	      Default value:

		 SELECT	ID, Status, SendingDateTime, DeliveryDateTime, SMSCNumber FROM sentitems
		 WHERE DeliveryDateTime	IS NULL	AND SenderID = %P AND TPMR = %t	AND DestinationNumber =	%R

       save_inbox_sms_update_delivered
	      Update message delivery status if	message	was delivered.

	      Default value:

		 UPDATE	sentitems SET DeliveryDateTime = %C, Status = %1, StatusError =	%e WHERE ID = %2 AND TPMR = %t

	      Query specific parameters:

	      %1     delivery status returned by GSM network

	      %2     ID	of message

       save_inbox_sms_update
	      Update message if	there is an delivery error.

	      Default value:

		 UPDATE	sentitems SET Status = %1, StatusError = %e WHERE ID = %2 AND TPMR = %t

	      Query specific parameters:

	      %1     delivery status returned by GSM network

	      %2     ID	of message

       save_inbox_sms_insert
	      Insert received message.

	      Default value:

		 INSERT	INTO inbox (ReceivingDateTime, Text, SenderNumber, Coding, SMSCNumber, UDH,
		 Class,	TextDecoded, RecipientID) VALUES (%d, %E, %R, %c, %F, %u, %x, %T, %P)

       update_received
	      Update statistics	after receiving	message.

	      Default value:

		 UPDATE	phones SET Received = Received + 1 WHERE IMEI =	%I

       refresh_send_status
	      Update messages in outbox.

	      Default value:

		 UPDATE	outbox SET SendingTimeOut = (NOW() + INTERVAL 60 SECOND) + 0
		 WHERE ID = %1 AND (SendingTimeOut < NOW() OR SendingTimeOut IS	NULL)

	      The  default query calculates sending timeout based on LoopSleep
	      value.

	      Query specific parameters:

	      %1     ID	of message

       find_outbox_sms_id
	      Find sms messages	for sending.

	      Default value:

		 SELECT	ID, InsertIntoDB, SendingDateTime, SenderID FROM outbox
		 WHERE SendingDateTime < NOW() AND SendingTimeOut <  NOW() AND
		 SendBefore >= CURTIME() AND SendAfter <= CURTIME() AND
		 ( SenderID is NULL OR SenderID	= '' OR	SenderID = %P )	ORDER BY InsertIntoDB ASC LIMIT	%1

	      Query specific parameters:

	      %1     limit of sms messages sended in one walk in loop

       find_outbox_body
	      Select body of message.

	      Default value:

		 SELECT	Text, Coding, UDH, Class, TextDecoded, ID, DestinationNumber, MultiPart,
		 RelativeValidity, DeliveryReport, CreatorID FROM outbox WHERE ID=%1

	      Query specific parameters:

	      %1     ID	of message

       find_outbox_multipart
	      Select remaining parts of	sms message.

	      Default value:

		 SELECT	Text, Coding, UDH, Class, TextDecoded, ID, SequencePosition
		 FROM outbox_multipart WHERE ID=%1 AND SequencePosition=%2

	      Query specific parameters:

	      %1     ID	of message

	      %2     Number of multipart message

       delete_outbox
	      Remove messages from outbox after	threir successful send.

	      Default value:

		 DELETE	FROM outbox WHERE ID=%1

	      Query specific parameters:

	      %1     ID	of message

       delete_outbox_multipart
	      Remove messages from outbox_multipart  after  threir  successful
	      send.

	      Default value:

		 DELETE	FROM outbox_multipart WHERE ID=%1

	      Query specific parameters:

	      %1     ID	of message

       create_outbox
	      Create message (insert to	outbox).

	      Default value:

		 INSERT	INTO outbox (CreatorID,	SenderID, DeliveryReport, MultiPart,
		 InsertIntoDB, Text, DestinationNumber,	RelativeValidity, Coding, UDH, Class,
		 TextDecoded) VALUES (%1, %P, %2, %3, NOW(), %E, %R, %V, %c, %u, %x, %T)

	      Query specific parameters:

	      %1     creator of	message

	      %2     delivery status report - yes/default

	      %3     multipart - FALSE/TRUE

	      %4     Part (part	number)

	      %5     ID	of message

       create_outbox_multipart
	      Create message remaining parts.

	      Default value:

		 INSERT	INTO outbox_multipart (SequencePosition, Text, Coding, UDH, Class,
		 TextDecoded, ID) VALUES (%4, %E, %c, %u, %x, %T, %5)

	      Query specific parameters:

	      %1     creator of	message

	      %2     delivery status report - yes/default

	      %3     multipart - FALSE/TRUE

	      %4     Part (part	number)

	      %5     ID	of message

       add_sent_info
	      Insert to	sentitems.

	      Default value:

		 INSERT	INTO sentitems (CreatorID,ID,SequencePosition,Status,SendingDateTime,
		 SMSCNumber, TPMR, SenderID,Text,DestinationNumber,Coding,UDH,Class,TextDecoded,
		 InsertIntoDB,RelativeValidity)
		 VALUES	(%A, %1, %2, %3, NOW(),	%F, %4,	%P, %E,	%R, %c,	%u, %x,	%T, %5,	%V)

	      Query specific parameters:

	      %1     ID	of sms message

	      %2     part number (for multipart	sms)

	      %3     message  state (SendingError, Error, SendingOK, SendingO-
		     KNoReport)

	      %4     message reference (TPMR)

	      %5     time when inserted	in db

       update_sent
	      Update sent statistics after sending message.

	      Default value:

		 UPDATE	phones SET Sent= Sent +	1 WHERE	IMEI = %I

       refresh_phone_status
	      Update phone status (battery, signal).

	      Default value:

		 UPDATE	phones SET TimeOut= (NOW() + INTERVAL 10 SECOND) + 0,
		 Battery = %1, Signal =	%2 WHERE IMEI =	%I

	      Query specific parameters:

	      %1     battery percent

	      %2     signal percent

       update_retries
	      Update number of retries for outbox message. The interval	can be
	      configured by RetryTimeout.

		 UPDATE	outbox SET SendngTimeOut = (NOW() + INTERVAL 600 SECOND) + 0,
		 Retries = %2 WHERE ID = %1

	      Query specific parameters:

	      %1     message ID

	      %2     number of retries

AUTHOR
       Michal AihaA <michal@cihar.com>

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

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

NAME | DESCRIPTION | SQL CONNECTION PARAMETERS | TABLES | SQL QUERIES | CONFIGURABLE QUERIES | AUTHOR | COPYRIGHT

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

home | help