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

FreeBSD Manual Pages

  
 
  

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

NAME
       gammu-smsd-tables  -  description  of  tables  for database backends of
       gammu-smsd(1)

       The backends themselves are described in	their sections,	this  document
       describes general database structure and	required tables.

       More  SMS  daemons  can	share  single  database. If you	do not specify
       PhoneID in their	configuration, all are treated equally and you have no
       guarantee  which	 one  sends outgoing message. If you configure PhoneID
       and use it when inserting message to the	outbox	table  (gammu-smsd-in-
       ject  does  this), each SMS daemon will have separate outbox queue. See
       also smsd-multi.

RECEIVING OF MESSAGES
       Received	messages are stored in inbox table.

TRANSMITTING OF	MESSAGES
       Transmitted messages are	read from table	outbox and possible subsequent
       parts of	the same message from outbox_multipart.

DESCRIPTION OF TABLES
   gammu
       Table  holding single field Version - version of	a database schema. See
       History of database structure for details what has changed.

   inbox
       Table where received messages will be stored.

       Fields description:

       UpdatedInDB (timestamp)
	      when somebody (daemon, user, etc.) updated it

       ReceivingDateTime (timestamp)
	      when SMS was received

       Text (text)
	      encoded SMS text (for all	SMS)

       SenderNumber (varchar(20))
	      decoded SMS sender number

       Coding	 (enum('Default_No_Compression',     'Unicode_No_Compression',
       '8bit', 'Default_Compression', 'Unicode_Compression'))
	      SMS text coding

       UDH (text)
	      encoded User Data	Header text

       SMSCNumber (varchar(20))
	      decoded SMSC number

       Class (integer)
	      SMS class	or -1 (0 is flash SMS, 1 is normal one)

       TextDecoded (varchar(160))
	      decoded SMS text (for Default Alphabet/Unicode SMS)

       ID (integer unsigned)
	      SMS identificator	(for using with	external applications)

       RecipientID (text)
	      which Gammu daemon has added it

       Processed (enum('false',	'true'))
	      you can use for marking, whether SMS was processed or not

   outbox
       Messages	 enqueued  for sending should be placed	in this	table. If mes-
       sage   is   multipart,	subsequent   parts   are   stored   in	 table
       outbox_multipart.

       Fields description:

       UpdatedInDB (timestamp)
	      when somebody (daemon, user, etc.) updated it

       InsertIntoDB (timestamp)
	      when message was inserted	into database

       SendingDateTime (timestamp)
	      set  it  to  some	 value,	 when want to force sending after some
	      planned time

       SendBefore (time)
	      Send message before specified time, can be used  to  limit  mes-
	      sages from being sent in night. Default value is 23:59:59

	      New in version 1.29.90.

       SendAfter (time)
	      Send message after specified time, can be	used to	limit messages
	      from being sent in night.	Default	value is 00:00:00

	      New in version 1.29.90.

       Text (text)
	      SMS text encoded using hex values	in proper coding. If you  want
	      to use TextDecoded field,	keep this NULL (or empty).

       DestinationNumber (varchar(20))
	      recipient	number

       Coding	  (enum('Default_No_Compression',    'Unicode_No_Compression',
       '8bit', 'Default_Compression', 'Unicode_Compression'))
	      SMS text coding

       UDH (text)
	      User Data	Header encoded using hex values	which will be used for
	      constructing  the	message. Without this, message will be sent as
	      plain text.

       Class (integer)
	      SMS class	or -1 (0 is normal SMS,	1 is flash one)

       TextDecoded (varchar(160))
	      SMS text in "human readable" form

       ID (integer unsigned)
	      SMS/SMS sequence ID

	      Please note that this number has to be unique also for sentitems
	      table, so	reusing	message	IDs might not be a good	idea.

       MultiPart (enum('false','true'))
	      info,  whether  there  are  more	SMS from this sequence in out-
	      box_multipart

       RelativeValidity	(integer)
	      SMS relative validity like encoded using GSM specs

       SenderID	(text)
	      which SMSD instance should send this one sequence,  see  PhoneID
	      and smsd-multi. If blank,	first SMSD who sees this message first
	      will process it.

       SendingTimeOut (timestamp)
	      used by SMSD instance for	own targets

       DeliveryReport (enum('default','yes','no'))
	      when default is used, Delivery Report is used or	not  according
	      to SMSD instance settings; yes forces Delivery Report.

       CreatorID (text)
	      identification of	program	created	the message

       Retries (integer)
	      number of	attempted retries when sending this message

       Priority	(integer)
	      priority of message, messages with higher	priority are processed
	      first

   outbox_multipart
       Data for	outgoing multipart messages.

       Fields description:

       ID (integer unsigned)
	      the same meaning as values in outbox table

       Text (text)
	      the same meaning as values in outbox table

       Coding	 (enum('Default_No_Compression',     'Unicode_No_Compression',
       '8bit', 'Default_Compression', 'Unicode_Compression'))
	      the same meaning as values in outbox table

       UDH (text)
	      the same meaning as values in outbox table

       Class (integer)
	      the same meaning as values in outbox table

       TextDecoded (varchar(160))
	      the same meaning as values in outbox table

       ID (integer unsigned)
	      the same meaning as values in outbox table

       SequencePosition	(integer)
	      info, what is SMS	number in SMS sequence (start at 2, first part
	      is in outbox table).

   phones
       Information about connected phones.  This  table	 is  periodically  re-
       freshed	and  you  can  get information such as battery or signal level
       from here.

       Fields description:

       ID (text)
	      PhoneID value

       UpdatedInDB (timestamp)
	      when this	record has been	updated

       InsertIntoDB (timestamp)
	      when this	record has been	created	 (when	phone  has  been  con-
	      nected)

       TimeOut (timestamp)
	      when this	record expires

       Send (boolean)
	      indicates	 whether SMSD is sending messages, depends on configu-
	      ration directive Send

       Receive (boolean)
	      indicates	whether	SMSD is	receiving messages, depends on config-
	      uration directive	Receive

       IMEI (text)
	      IMEI of phone

       IMSI (text)
	      SIM IMSI

       Client (text)
	      client name, usually string Gammu	with version

       Battery (integer)
	      battery level in percent (or -1 if unknown)

       Signal (integer)
	      signal level in percent (or -1 if	unknown)

       Sent (integer)
	      Number  of  sent SMS messages (SMSD does not reset this counter,
	      so it might overflow).

       Received	(integer)
	      Number of	received  SMS  messages	 (SMSD	does  not  reset  this
	      counter, so it might overflow).

   sentitems
       Log  of sent messages (and unsent ones with error code).	Also if	deliv-
       ery reports are enabled,	message	state is updated after	receiving  de-
       livery report.

       Fields description:

       UpdatedInDB (timestamp)
	      when somebody (daemon, user, etc.) updated it

       InsertIntoDB (timestamp)
	      when message was inserted	into database

       SendingDateTime (timestamp)
	      when message has been sent

       DeliveryDateTime	(timestamp)
	      Time of receiving	delivery report	(if it has been	enabled).

       Status  (enum('SendingOK', 'SendingOKNoReport', 'SendingError', 'Deliv-
       eryOK', 'DeliveryFailed',  'DeliveryPending',  'DeliveryUnknown',  'Er-
       ror'))
	      Status  of message sending. SendingError means that phone	failed
	      to send the message, Error indicates some	other error while pro-
	      cessing message.

	      SendingOK
		     Message has been sent, waiting for	delivery report.

	      SendingOKNoReport
		     Message has been sent without asking for delivery report.

	      SendingError
		     Sending has failed.

	      DeliveryOK
		     Delivery report arrived and reported success.

	      DeliveryFailed
		     Delivery report arrived and reports failure.

	      DeliveryPending
		     Delivery report announced pending deliver.

	      DeliveryUnknown
		     Delivery report reported unknown status.

	      Error  Some  other error happened	during sending (usually	bug in
		     SMSD).

       StatusError (integer)
	      Status of	delivery from delivery report message, codes  are  de-
	      fined in GSM specification 03.40 section 9.2.3.15	(TP-Status).

       Text (text)
	      SMS text encoded using hex values

       DestinationNumber (varchar(20))
	      decoded destination number for SMS

       Coding	  (enum('Default_No_Compression',    'Unicode_No_Compression',
       '8bit', 'Default_Compression', 'Unicode_Compression'))
	      SMS text coding

       UDH (text)
	      User Data	Header encoded using hex values

       SMSCNumber (varchar(20))
	      decoded number of	SMSC, which sent SMS

       Class (integer)
	      SMS class	or -1 (0 is normal SMS,	1 is flash one)

       TextDecoded (varchar(160))
	      SMS text in "human readable" form

       ID (integer unsigned)
	      SMS ID

       SenderID	(text)
	      which SMSD instance sent this one	sequence, see PhoneID

       SequencePosition	(integer)
	      SMS number in SMS	sequence

       TPMR (integer)
	      Message Reference	like in	GSM specs

       RelativeValidity	(integer)
	      SMS relative validity like encoded using GSM specs

       CreatorID (text)
	      copied from CreatorID from outbox	table

HISTORY	OF DATABASE STRUCTURE
       NOTE:
	  Testing versions (see	versioning) do not have	 to  keep  same	 table
	  structure as final releases. Below mentioned versions	are for	infor-
	  mational purposes only, you should always  use  stable  versions  in
	  production environment.

       History of schema versions:

       16

	  o Removed unused daemons, pbk	and pbk_groups tables.

	  o Added primary key to the gammu table.

	  o Added Priority field to the	outbox.

	  o Added IMSI field to	the phones.

	  Changed in version 1.37.90.

       15
	  Added	Retries	field to the outbox.

	  Changed in version 1.36.7.

       14
	  Added	NetCode	and NetName fields.

	  Changed in version 1.34.0.

       13     Added SendBefore and SendAfter fields.

	      Changed in version 1.29.90.

	      Also  PostgreSQL	fields	are  now case sensitive	(same as other
	      backends).

	      Changed in version 1.29.93.

       12     the changes only affect MySQL structure changing default	values
	      for  timestamps  from 0000-00-00 00:00:00	to CURRENT_TIMESTAMP()
	      by using triggers, to update to this version, just execute trig-
	      gers definition at the end of SQL	file.

	      Changed in version 1.28.94.

       11     all fields for storing message text are no longer	limited	to 160
	      chars, but are arbitrary length text fields.

	      Changed in version 1.25.92.

       10     DeliveryDateTime is now NULL  when  message  is  not  delivered,
	      added several indexes

	      Changed in version 1.22.95.

       9      added sent/received counters to phones table

	      Changed in version 1.22.93.

       8      Signal and battery state are now stored in database.

	      Changed in version 1.20.94.

       7      Added CreatorID to several tables.

	      Changed in version 1.07.00.

       6      Many fields in outbox can	now be NULL.

	      Changed in version 1.06.00.

       5      Introduced daemons table and various other changes.

	      Changed in version 1.03.00.

       3      Introduced phones	table and various other	changes.

	      Changed in version 0.98.0.

EXAMPLES
   Creating tables
       SQL scripts to create all needed	tables for most	databases are included
       in Gammu	documentation (docs/sql). As well as some PHP  scripts	inter-
       acting with the database.

       For example to create SQLite tables, issue following command:

	  sqlite3 smsd.db < docs/sql/sqlite.sql

   Injecting a message using SQL
       To send a message, you can either use gammu-smsd-inject,	which does all
       the magic for you, or you can insert the	message	manually. The simplest
       example is short	text message:

	  INSERT INTO outbox (
	      DestinationNumber,
	      TextDecoded,
	      CreatorID,
	      Coding
	  ) VALUES (
	      '800123465',
	      'This is a SQL test message',
	      'Program',
	      'Default_No_Compression'
	  );

       Please  note usage of TextDecoded field,	for Text field,	you would have
       to hex encode the unicode text:

	  INSERT INTO outbox (
	      DestinationNumber,
	      Text,
	      CreatorID,
	      Coding
	  ) VALUES (
	      '800123465',
	      '005400680069007300200069007300200061002000530051004c002000740065007300740020006d006500730073006100670065',
	      'Program',
	      'Default_No_Compression'
	  );

   Injecting long message using	SQL
       Inserting multipart messages is a bit more tricky,  you	need  to  con-
       struct  also  UDH  header  and  store it	hexadecimally written into UDH
       field. Unless  you  have	 a  good  reason  to  do  this	manually,  use
       gammu-smsd-inject,  C  library  (SMSD_InjectSMS())  or  Python  library
       (gammu.smsd.SMSD.InjectSMS()).

       For long	text message, the UDH starts with 050003 followed by byte as a
       message	reference  (you	 can put any hex value there, but it should be
       different for each message, D3 in following example), byte  for	number
       of  messages  (02  in example, it should	be unique for each message you
       send to same phone number) and byte for number of current  message  (01
       for first message, 02 for second, etc.).

       I most cases, the mutlipart message has to be class 1.

       For example long	text message of	two parts could	look like following:

	  INSERT INTO outbox (
	      CreatorID,
	      MultiPart,
	      DestinationNumber,
	      UDH,
	      TextDecoded,
	      Coding,
	      Class
	  ) VALUES (
	      'Gammu 1.23.91',
	      'true',
	      '123465',
	      '050003D30201',
	      'Mqukqirip ya konej eqniu	rejropocejor hugiygydewl tfej nrupxujob	xuemymiyliralj.	Te tvyjuh qaxumur ibewfoiws zuucoz tdygu gelum L ejqigqesykl kya jdytbez',
	      'Default_No_Compression',
	      1
	  )

	  INSERT INTO outbox_multipart (
	      SequencePosition,
	      UDH,
	      Class,
	      TextDecoded,
	      ID,
	      Coding,
	      Class
	  ) VALUES (
	      2,
	      '050003D30202',
	      'u xewz qisubevumxyzk ufuylehyzc.	Nse xobq dfolizygqysj t	bvowsyhyhyemim ovutpapeaempye giuuwbib.',
	      <ID_OF_INSERTED_RECORD_IN_OUBOX_TABLE>,
	      'Default_No_Compression',
	      1
	  )

       NOTE:
	  Adding UDH means that	you have less space for	text, in above example
	  you can use only 153 characters in single message.

AUTHOR
       Michal AihaA <michal@cihar.com>

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

1.38.2				 Mar 28, 2017		  GAMMU-SMSD-TABLES(7)

NAME | RECEIVING OF MESSAGES | TRANSMITTING OF MESSAGES | DESCRIPTION OF TABLES | HISTORY OF DATABASE STRUCTURE | EXAMPLES | AUTHOR | COPYRIGHT

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

home | help