PostgreSQL create table script

Introduction

To create an SQL SMS Gateway with Ozeki NG, you can use a PostgreSQL database, which can be accessed using an ADO or ODBC driver. All you have to do is create two database tables (in the ozekisms database): ozekimessagein and ozekimessageout. (The tables can have additional columns.) Ozeki NG - SMS Gateway will insert incoming messages into the ozekimessagein table. If you want to send a message, you should insert a record into the ozekimessageout table. The SMS Server periodically checks this table for messages to send.

Creating the database and the tables

Start pgAdmin: Start menu -> All Programs -> PostgreSQL 8.2 -> pgAdmin III. Right-click PostgreSQL Database Server 8.2, and left-click Connect. Then, fill in the password field and connect.

create an ozeki sms database
Figure 1 - Create an Ozeki SMS databese

Create the  ozekisms database. Right-click Databases, then left-click New Database.

fill in the name and the encoding field
Figure 2 - Fill in the Name and the Encoding field

Fill in the Name and the Encoding field, and click OK.

select the ozekisms database
Figure 3 - Creating Tables

Select the ozekisms database, and click the SQL icon for creating tables.

execute query
Figure 4 - Execute query

Copy the following text into the window, and click "Execute query": 
CREATE TABLE ozekimessagein
(
  id serial,
  sender varchar(30),
  receiver varchar(30),
  msg varchar(1024),
  senttime varchar(100),
  receivedtime varchar(100),
  operator varchar(100),
  msgtype varchar(160)
);

CREATE TABLE ozekimessageout
(
  id serial,
  sender varchar(30),
  receiver varchar(30),
  msg varchar(1024),
  senttime varchar(100),
  receivedtime varchar(100),
  status varchar(20),
  operator varchar(100),
  msgtype varchar(160)
);

create a user
Figure 5 - Create a user

You have to create a user. It will be named "sqluser" and its password will be abc123.

new login role
Figure 6 - Creating a Login Role

select the ozekisms
Figure 7 - Enter the Role Name and Password

Select the ozekisms database and click the SQL icon for grant rights.

execute query
Figure 8 - Execute query

Copy the following text into the window, and click "Execute query": 

grant select,update,insert,delete on ozekimessagein to sqluser;
grant select,update,insert,delete on ozekimessageout to sqluser;
grant update on ozekimessagein_id_seq to sqluser;
grant update on ozekimessageout_id_seq to sqluser;

We need the last 2 rows to access the serial ids.

install odbc driver
Figure 9 - Grant rights

When you are finished, you have to install Odbc driver for PostgreSQL.

In Ozeki NG the connection string type will be "Odbc" and the connection string will be: 

DRIVER={PostgreSQL Unicode};Server=localhost;Database=ozekisms;uid=sqluser;Password=abc123;

More information