.\" Man page generated from reStructuredText.
.
.TH "GAMMU-SMSD-PGSQL" "7" "Oct 03, 2020" "1.42.0" "Gammu"
.SH NAME
gammu-smsd-pgsql \- gammu-smsd(1) backend using PostgreSQL database server as a message storage
.
.nr rst2man-indent-level 0
.
.de1 rstReportMargin
\\$1 \\n[an-margin]
level \\n[rst2man-indent-level]
level margin: \\n[rst2man-indent\\n[rst2man-indent-level]]
-
\\n[rst2man-indent0]
\\n[rst2man-indent1]
\\n[rst2man-indent2]
..
.de1 INDENT
.\" .rstReportMargin pre:
. RS \\$1
. nr rst2man-indent\\n[rst2man-indent-level] \\n[an-margin]
. nr rst2man-indent-level +1
.\" .rstReportMargin post:
..
.de UNINDENT
. RE
.\" indent \\n[an-margin]
.\" old: \\n[rst2man-indent\\n[rst2man-indent-level]]
.nr rst2man-indent-level -1
.\" new: \\n[rst2man-indent\\n[rst2man-indent-level]]
.in \\n[rst2man-indent\\n[rst2man-indent-level]]u
..
.SH DESCRIPTION
.sp
PGSQL backend stores all data in a \fI\%PostgreSQL\fP database server, which
parameters are defined by configuration (see gammu\-smsdrc for description of
configuration options).
.sp
For tables description see gammu\-smsd\-tables\&.
.sp
This backend is based on gammu\-smsd\-sql\&.
.SH CONFIGURATION
.sp
Before running gammu\-smsd you need to create necessary tables in the
database, which is described below.
.sp
The configuration file then can look like:
.INDENT 0.0
.INDENT 3.5
.sp
.nf
.ft C
[smsd]
service = sql
driver = native_pgsql
host = localhost
.ft P
.fi
.UNINDENT
.UNINDENT
.sp
\fBSEE ALSO:\fP
.INDENT 0.0
.INDENT 3.5
gammu\-smsdrc
.UNINDENT
.UNINDENT
.SH CREATING TABLES FOR POSTGRESQL
.sp
SQL script for creating tables in PostgreSQL database:
.INDENT 0.0
.INDENT 3.5
.sp
.nf
.ft C
\-\- 
\-\- Database: "smsd"
\-\- 
\-\- CREATE USER "smsd" WITH NOCREATEDB NOCREATEUSER;
\-\- CREATE DATABASE "smsd" WITH OWNER = "smsd" ENCODING = \(aqUTF8\(aq;
\-\- \econnect "smsd" "smsd"
\-\- COMMENT ON DATABASE "smsd" IS \(aqGammu SMSD Database\(aq;

\-\- \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

\-\-
\-\- Function declaration for updating timestamps
\-\-
CREATE LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION update_timestamp() RETURNS trigger AS $update_timestamp$
  BEGIN
    NEW."UpdatedInDB" := LOCALTIMESTAMP(0);
    RETURN NEW;
  END;
$update_timestamp$ LANGUAGE plpgsql;

\-\- \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

\-\-
\-\- Sequence declarations for tables\(aq primary keys
\-\-

\-\-CREATE SEQUENCE inbox_ID_seq;

\-\-CREATE SEQUENCE outbox_ID_seq;

\-\-CREATE SEQUENCE outbox_multipart_ID_seq;

\-\-CREATE SEQUENCE sentitems_ID_seq;

\-\- \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

\-\-
\-\- Index declarations for tables\(aq primary keys
\-\-

\-\-CREATE UNIQUE INDEX inbox_pkey ON inbox USING btree ("ID");

\-\-CREATE UNIQUE INDEX outbox_pkey ON outbox USING btree ("ID");

\-\-CREATE UNIQUE INDEX outbox_multipart_pkey ON outbox_multipart USING btree ("ID");

\-\-CREATE UNIQUE INDEX sentitems_pkey ON sentitems USING btree ("ID");

\-\- \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

\-\- 
\-\- Table structure for table "gammu"
\-\- 

CREATE TABLE gammu (
  "Version" smallint NOT NULL DEFAULT \(aq0\(aq PRIMARY KEY
);

\-\- 
\-\- Dumping data for table "gammu"
\-\- 

INSERT INTO gammu ("Version") VALUES (17);

\-\- \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

\-\- 
\-\- Table structure for table "inbox"
\-\- 

CREATE TABLE inbox (
  "UpdatedInDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
  "ReceivingDateTime" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
  "Text" text NOT NULL,
  "SenderNumber" varchar(20) NOT NULL DEFAULT \(aq\(aq,
  "Coding" varchar(255) NOT NULL DEFAULT \(aqDefault_No_Compression\(aq,
  "UDH" text NOT NULL,
  "SMSCNumber" varchar(20) NOT NULL DEFAULT \(aq\(aq,
  "Class" integer NOT NULL DEFAULT \(aq\-1\(aq,
  "TextDecoded" text NOT NULL DEFAULT \(aq\(aq,
  "ID" serial PRIMARY KEY,
  "RecipientID" text NOT NULL,
  "Processed" boolean NOT NULL DEFAULT \(aqfalse\(aq,
  "Status" integer NOT NULL DEFAULT \(aq\-1\(aq,
  CHECK ("Coding" IN 
  (\(aqDefault_No_Compression\(aq,\(aqUnicode_No_Compression\(aq,\(aq8bit\(aq,\(aqDefault_Compression\(aq,\(aqUnicode_Compression\(aq)) 
);

\-\- 
\-\- Dumping data for table "inbox"
\-\- 

\-\- \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

\-\-
\-\- Create trigger for table "inbox"
\-\-

CREATE TRIGGER update_timestamp BEFORE UPDATE ON inbox FOR EACH ROW EXECUTE PROCEDURE update_timestamp();

\-\- \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

\-\- 
\-\- Table structure for table "outbox"
\-\- 

CREATE TABLE outbox (
  "UpdatedInDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
  "InsertIntoDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
  "SendingDateTime" timestamp NOT NULL DEFAULT LOCALTIMESTAMP(0),
  "SendBefore" time NOT NULL DEFAULT \(aq23:59:59\(aq,
  "SendAfter" time NOT NULL DEFAULT \(aq00:00:00\(aq,
  "Text" text,
  "DestinationNumber" varchar(20) NOT NULL DEFAULT \(aq\(aq,
  "Coding" varchar(255) NOT NULL DEFAULT \(aqDefault_No_Compression\(aq,
  "UDH" text,
  "Class" integer DEFAULT \(aq\-1\(aq,
  "TextDecoded" text NOT NULL DEFAULT \(aq\(aq,
  "ID" serial PRIMARY KEY,
  "MultiPart" boolean NOT NULL DEFAULT \(aqfalse\(aq,
  "RelativeValidity" integer DEFAULT \(aq\-1\(aq,
  "SenderID" varchar(255),
  "SendingTimeOut" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
  "DeliveryReport" varchar(10) DEFAULT \(aqdefault\(aq,
  "CreatorID" text NOT NULL,
  "Retries" integer DEFAULT \(aq0\(aq,
  "Priority" integer DEFAULT \(aq0\(aq,
  "Status" varchar(255) NOT NULL DEFAULT \(aqReserved\(aq,
  "StatusCode" integer NOT NULL DEFAULT \(aq\-1\(aq,
  CHECK ("Coding" IN 
  (\(aqDefault_No_Compression\(aq,\(aqUnicode_No_Compression\(aq,\(aq8bit\(aq,\(aqDefault_Compression\(aq,\(aqUnicode_Compression\(aq)),
  CHECK ("DeliveryReport" IN (\(aqdefault\(aq,\(aqyes\(aq,\(aqno\(aq)),
  CHECK ("Status" IN 
  (\(aqSendingOK\(aq,\(aqSendingOKNoReport\(aq,\(aqSendingError\(aq,\(aqDeliveryOK\(aq,\(aqDeliveryFailed\(aq,\(aqDeliveryPending\(aq,
  \(aqDeliveryUnknown\(aq,\(aqError\(aq,\(aqReserved\(aq))
);

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

\-\- 
\-\- Dumping data for table "outbox"
\-\- 

\-\- \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

\-\-
\-\- Create trigger for table "outbox"
\-\-

CREATE TRIGGER update_timestamp BEFORE UPDATE ON outbox FOR EACH ROW EXECUTE PROCEDURE update_timestamp();

\-\- \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

\-\- 
\-\- Table structure for table "outbox_multipart"
\-\- 

CREATE TABLE outbox_multipart (
  "Text" text,
  "Coding" varchar(255) NOT NULL DEFAULT \(aqDefault_No_Compression\(aq,
  "UDH" text,
  "Class" integer DEFAULT \(aq\-1\(aq,
  "TextDecoded" text DEFAULT NULL,
  "ID" serial,
  "SequencePosition" integer NOT NULL DEFAULT \(aq1\(aq,
  "Status" varchar(255) NOT NULL DEFAULT \(aqReserved\(aq,
  "StatusCode" integer NOT NULL DEFAULT \(aq\-1\(aq,
  PRIMARY KEY ("ID", "SequencePosition"),
  CHECK ("Coding" IN 
  (\(aqDefault_No_Compression\(aq,\(aqUnicode_No_Compression\(aq,\(aq8bit\(aq,\(aqDefault_Compression\(aq,\(aqUnicode_Compression\(aq)),
  CHECK ("Status" IN 
  (\(aqSendingOK\(aq,\(aqSendingOKNoReport\(aq,\(aqSendingError\(aq,\(aqDeliveryOK\(aq,\(aqDeliveryFailed\(aq,\(aqDeliveryPending\(aq,
  \(aqDeliveryUnknown\(aq,\(aqError\(aq,\(aqReserved\(aq))
);

\-\- 
\-\- Dumping data for table "outbox_multipart"
\-\- 


\-\- \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

\-\- 
\-\- Table structure for table "phones"
\-\- 

CREATE TABLE phones (
  "ID" text NOT NULL,
  "UpdatedInDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
  "InsertIntoDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
  "TimeOut" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
  "Send" boolean NOT NULL DEFAULT \(aqno\(aq,
  "Receive" boolean NOT NULL DEFAULT \(aqno\(aq,
  "IMEI" varchar(35) PRIMARY KEY NOT NULL,
  "IMSI" varchar(35) NOT NULL,
  "NetCode" varchar(10) DEFAULT \(aqERROR\(aq,
  "NetName" varchar(35) DEFAULT \(aqERROR\(aq,
  "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
);

\-\- 
\-\- Dumping data for table "phones"
\-\- 

\-\- \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

\-\-
\-\- Create trigger for table "phones"
\-\-

CREATE TRIGGER update_timestamp BEFORE UPDATE ON phones FOR EACH ROW EXECUTE PROCEDURE update_timestamp();

\-\- \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

\-\- 
\-\- Table structure for table "sentitems"
\-\- 

CREATE TABLE sentitems (
  "UpdatedInDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
  "InsertIntoDB" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
  "SendingDateTime" timestamp(0) WITHOUT time zone NOT NULL DEFAULT LOCALTIMESTAMP(0),
  "DeliveryDateTime" timestamp(0) WITHOUT time zone NULL,
  "Text" text NOT NULL,
  "DestinationNumber" varchar(20) NOT NULL DEFAULT \(aq\(aq,
  "Coding" varchar(255) NOT NULL DEFAULT \(aqDefault_No_Compression\(aq,
  "UDH" text NOT NULL,
  "SMSCNumber" varchar(20) NOT NULL DEFAULT \(aq\(aq,
  "Class" integer NOT NULL DEFAULT \(aq\-1\(aq,
  "TextDecoded" text NOT NULL DEFAULT \(aq\(aq,
  "ID" serial,
  "SenderID" varchar(255) NOT NULL,
  "SequencePosition" integer NOT NULL DEFAULT \(aq1\(aq,
  "Status" varchar(255) NOT NULL DEFAULT \(aqSendingOK\(aq,
  "StatusError" integer NOT NULL DEFAULT \(aq\-1\(aq,
  "TPMR" integer NOT NULL DEFAULT \(aq\-1\(aq,
  "RelativeValidity" integer NOT NULL DEFAULT \(aq\-1\(aq,
  "CreatorID" text NOT NULL,
  "StatusCode" integer NOT NULL DEFAULT \(aq\-1\(aq,
  CHECK ("Status" IN 
  (\(aqSendingOK\(aq,\(aqSendingOKNoReport\(aq,\(aqSendingError\(aq,\(aqDeliveryOK\(aq,\(aqDeliveryFailed\(aq,\(aqDeliveryPending\(aq,
  \(aqDeliveryUnknown\(aq,\(aqError\(aq)),
  CHECK ("Coding" IN 
  (\(aqDefault_No_Compression\(aq,\(aqUnicode_No_Compression\(aq,\(aq8bit\(aq,\(aqDefault_Compression\(aq,\(aqUnicode_Compression\(aq)),
  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");

\-\- 
\-\- Dumping data for table "sentitems"
\-\- 

\-\- \-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-\-

\-\-
\-\- Create trigger for table "sentitems"
\-\-

CREATE TRIGGER update_timestamp BEFORE UPDATE ON sentitems FOR EACH ROW EXECUTE PROCEDURE update_timestamp();


.ft P
.fi
.UNINDENT
.UNINDENT
.sp
\fBNOTE:\fP
.INDENT 0.0
.INDENT 3.5
You can find the script in \fBdocs/sql/pgsql.sql\fP as well.
.UNINDENT
.UNINDENT
.SH UPGRADING TABLES
.sp
The easiest way to upgrade database structure is to backup old one and start
with creating new one based on example above.
.sp
For upgrading existing database, you can use changes described in
smsd\-tables\-history and then manually update \fBVersion\fP field in
\fBgammu\fP table.
.SH AUTHOR
Michal Čihař <michal@cihar.com>
.SH COPYRIGHT
2009-2015, Michal Čihař <michal@cihar.com>
.\" Generated by docutils manpage writer.
.