SQL API / Configuring the Client

 

The customer can create a database or share a part of the existing database. In the case of tables, the Customer may use the structure proposed by SerwerSMS (the code for creating the tables below) or, if necessary, configure access to the database and table by filling in the Customer Panel fields related to the connection to the database, table name and structure. The IP address from which the connection will be made in order to download messages for sending, updating reports and adding answers is: 94.152.131.145.

For a table with messages to send, it is necessary to specify fields:
- table name
- record id
- number
- sender
- message
- smsid
- delivery report
- delivery report time
- delivery report code
- description of the delivery report
- flags
- parts

 

Default table structure for sending messages:

CREATE TABLE IF NOT EXISTS `SerwerSMS_MT` (
  `id` int(10) unsigned NOT NULL,
  `number` varchar(20) COLLATE utf8_polish_ci NOT NULL,
  `sender` varchar(20) COLLATE utf8_polish_ci NOT NULL,
  `message` text COLLATE utf8_polish_ci NOT NULL,
  `date` datetime NOT NULL,
  `flags` varchar(50) COLLATE utf8_polish_ci NOT NULL,
  `smsid` varchar(20) COLLATE utf8_polish_ci NOT NULL,
  `dlr` enum('-1','0','1','2') COLLATE utf8_polish_ci NOT NULL DEFAULT '0',
  `dlr_date` datetime NOT NULL,
  `dlr_code` int(10) unsigned NOT NULL,
  `dlr_description` varchar(100) COLLATE utf8_polish_ci NOT NULL,
  `parts` tinyint(1)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
ALTER TABLE `SerwerSMS_MT`  ADD PRIMARY KEY (`id`),  ADD KEY `smsid` (`smsid`),  ADD KEY `new` (`smsid`,`date`);
ALTER TABLE `SerwerSMS_MT`  MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT;

Default table structure for message reception (optional):

CREATE TABLE IF NOT EXISTS `SerwerSMS_MO` (
  `id` int(10) unsigned NOT NULL,
  `number` varchar(20) COLLATE utf8_polish_ci NOT NULL,
  `sender` varchar(20) COLLATE utf8_polish_ci NOT NULL,
  `message` text COLLATE utf8_polish_ci NOT NULL,
  `date` datetime NOT NULL,
  `type` enum('ECO','ND','NDI') COLLATE utf8_polish_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
ALTER TABLE `SerwerSMS_MO` ADD PRIMARY KEY (`id`);
ALTER TABLE `SerwerSMS_MO` MODIFY `id` int(10) unsigned NOT NULL AUTO_INCREMENT;

Table structure for MSSQL for sending messages:CREATE TABLE SerwerSMS_MT(
  id int PRIMARY KEY NOT NULL IDENTITY(1,1),
  number varchar(20) NOT NULL,
  sender varchar(20) NOT NULL,
  message text NOT NULL,
  date datetime NOT NULL,
  flags varchar(50) NOT NULL,
  smsid varchar(20) NOT NULL,
  dlr VARCHAR(2) NOT NULL CHECK (dlr IN('-1', '0', '1', '2')) DEFAULT '0',
  dlr_date datetime NOT NULL,
  dlr_code int NOT NULL,
  dlr_description varchar(100) NOT NULL,
  parts tinyint
)

Struktura tabeli dla MSSQL do odbioru wiadomoĊ›ci (optional):

CREATE TABLE SerwerSMS_MO (
  id int PRIMARY KEY NOT NULL IDENTITY(1,1),
  number varchar(20) NOT NULL,
  sender varchar(20) NOT NULL,
  message text NOT NULL,
  date datetime NOT NULL,
  type VARCHAR(5) NOT NULL CHECK (type IN('ECO+','ND','NDI')) DEFAULT '0',
)