PostgreSQL database

<< Click to Display Table of Contents >>

Navigation:  How to... > Access your database >

PostgreSQL database

Learn how to send and receive SMS messages using a PostgreSQL database with Diafaan SMS Server. Get instructions about the installation of the ODBC driver, create the database with an SQL script and how to connect to the database with the connection string for PostgreSQL.

 

PostgreSQL

PostgreSQL is an open source database server. Diafaan SMS Server can use an ODBC driver to connect to PostgreSQL. For this article PostgreSQL version 8.4.2 was used with the psqlODBC 8.4.2 driver.

 

Install the psqlODBC driver

Before Diafaan SMS Server can send and receive SMS messages from the database you have to make sure the psqlODBC driver is installed on the computer running Diafaan SMS Server. You can find the psqlODBC driver on the PostgreSQL website.

 

Note: Diafaan SMS Server is a 32-bit program and requires the 32-bit version of the ODBC driver, the 64-bit ODBC driver will not work. Windows supports both 32-bits and 64-bits versions of the ODBC drivers at the same time. The 32-bit version of the Windows ODBC manager is located at "%systemdrive%\Windows\SysWoW64\Odbcad32.exe" on 64-bit versions of Windows.

 

Create the SMSServer database

You can use the PostgreSQL SQL shell to create the database tables. Of course it is also possible to use an existing database, but if you want to be sure you can use all options available in Diafaan SMS Server you should use the SQL commands below necessary to create the database tables to send and receive SMS messages and to keep the send log.

 

CREATE DATABASE SMSServer WITH ENCODING 'UTF8';

 

CREATE SEQUENCE messageout_seq;

 

CREATE TABLE MessageOut (

Id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('messageout_seq'),

MessageTo VARCHAR(80),

MessageFrom VARCHAR(80),

MessageText TEXT,

MessageType VARCHAR(80),

MessageGuid TEXT,

MessageInfo TEXT,

Gateway VARCHAR(80),

UserId VARCHAR(80),

UserInfo TEXT,

Priority INTEGER,

Scheduled TIMESTAMP,

ValidityPeriod INTEGER,

IsSent SMALLINT NOT NULL DEFAULT 0,

IsRead SMALLINT NOT NULL DEFAULT 0);

 

CREATE INDEX IDX_IsRead ON MessageOut (IsRead);

 

CREATE SEQUENCE messagein_seq;

 

CREATE TABLE MessageIn (

Id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('messagein_seq'),

SendTime TIMESTAMP,

ReceiveTime TIMESTAMP,

MessageFrom VARCHAR(80),

MessageTo VARCHAR(80),

SMSC VARCHAR(80),

MessageText TEXT,

MessageType VARCHAR(80),

MessageParts INTEGER,

MessagePDU TEXT,

Gateway VARCHAR(80),

UserId VARCHAR(80));

 

CREATE SEQUENCE messagelog_seq;

 

CREATE TABLE MessageLog (

Id INTEGER NOT NULL PRIMARY KEY DEFAULT nextval('messagelog_seq'),

SendTime TIMESTAMP,

ReceiveTime TIMESTAMP,

StatusCode INTEGER,

StatusText VARCHAR(80),

MessageTo VARCHAR(80),

MessageFrom VARCHAR(80),

MessageText TEXT,

MessageType VARCHAR(80),

MessageId VARCHAR(80),

MessageGuid TEXT,

MessageInfo TEXT,

ErrorCode VARCHAR(80),

ErrorText TEXT,

Gateway VARCHAR(80),

MessageParts INTEGER,

MessagePDU TEXT,

Connector VARCHAR(80),

UserId VARCHAR(80),

UserInfo TEXT);

 

CREATE INDEX IDX_MessageId ON MessageLog (MessageId, SendTime);

 

Add the SQL Connector

Now you can add the new database to Diafaan SMS Server. First create a new SQL Connector with the connector wizard and select ODBC as the database type. On the next page you can provide the ODBC connection string, the following connection string should be adapted for your database installation:

 

Driver={PostgreSQL UNICODE};Server=192.168.0.0;Port=5432;Database=smsserver;Uid=postgres;Pwd=password;

 

SelectDatabase

 

Select the database fields

On the next wizard pages you can select the database tables and fields you want Diafaan SMS Server to use. You can only select the most used fields here. Additional database fields can be selected later on the advanced tab page in the connector properties dialog.

 

For more information see Access your database.

 

It is a good idea to change the database flavor option on the same advanced tab page to MySQL (PostgreSQL is not available but MySQL has the same syntax). This will instruct Diafaan SMS Server to use a specific SQL syntax for PostgreSQL enhancing performance somewhat.