OZEKI OZEKI Phone System
Customizable PBX for Windows

Ozeki Phone System

< Database servers | Setup Ozeki PBX to use SQL >

How to create database tables
to your SQL API

In order to use an SQL API with your Ozeki Phone System XE, first of all you need to create the database tables in your database server to your SQL API. Every database server has its own syntax to create tables. In this guide you will see different create table scripts related to several database servers.

Microsoft SQL Server

Call reporter

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxcalls`
-- -----------------------------------------------------
CREATE TABLE `ozpbxcalls` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `starttime` datetime DEFAULT NULL,
  `source` varchar(150) DEFAULT NULL,
  `callerid` varchar(150) DEFAULT NULL,
  `dialed` varchar(150) DEFAULT NULL,
  `destination` varchar(150) DEFAULT NULL,
  `duration` int(10) DEFAULT NULL,
  `callstate` varchar(150) DEFAULT NULL,
  `recordurl` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`id`)
) 
CREATE INDEX ozpbxcallsindex ON ozpbxcalls (SessionID);

Database authenticator

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxusers`
-- -----------------------------------------------------
CREATE TABLE ozpbxusers
(
    authname varchar(45) PRIMARY KEY,
    username varchar(45) NOT NULL,
    password varchar(45) NOT NULL,
)

SQL OzML

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlin`
-- -----------------------------------------------------
CREATE TABLE ozmlin
(
  id int identity(1,1) PRIMARY KEY,
  callerid varchar(40) NOT NULL,
  scriptid int(10) NOT NULL,
  duration int(10) DEFAULT NULL,
  status varchar(40) DEFAULT NULL,
  recordurl varchar(150) DEFAULT NULL,
  starttime datetime DEFAULT NULL
);

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlout`
-- -----------------------------------------------------
CREATE TABLE ozmlout
(
  id int identity(1,1) PRIMARY KEY,
  dialednumber varchar(40) NOT NULL,
  status varchar(40) DEFAULT NULL,
  duration int(10) DEFAULT NULL,
  scriptid int(10) DEFAULT NULL,
  recordurl varchar(150) DEFAULT NULL,
  starttime datetime DEFAULT NULL
);

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlscripts`
-- -----------------------------------------------------
CREATE TABLE ozmlscripts
(
  scriptid int identity(1,1) PRIMARY KEY,
  ozml varchar(10000) NOT NULL
);

SQL SMS

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxmessageout`
-- -----------------------------------------------------
CREATE TABLE ozpbxmessageout
(
    id int identity(1,1) PRIMARY KEY,
    sender varchar(30) NULL,
    receiver varchar(30) NULL,
    msg text NULL,
    senttime varchar(100) NULL,
    receivedtime varchar(100) NULL,
    reference varchar(100) NULL,
    status varchar(20) NULL,
    msgtype varchar(160) NULL,
    operator varchar(100) NULL,
    errormsg varchar(250) NULL
);

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxmessagein`
-- -----------------------------------------------------
CREATE TABLE ozpbxmessagein (
    id int identity(1,1) PRIMARY KEY,
    sender varchar(30) NULL,
    receiver varchar(30) NULL,
    msg text NULL,
    senttime varchar(100) NULL,
    receivedtime varchar(100) NULL,
    extension varchar(100) NULL
);

Database Logger

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxlog`
-- -----------------------------------------------------

CREATE  TABLE ozpbxlog
(
    id int identity(1,1) PRIMARY KEY,
    Time datetime NOT NULL,
    Thread int NOT NULL,
    LogLevel varchar(20) NOT NULL,
    Logger varchar(255) NOT NULL,
    Message text NOT NULL,
)

Microsoft SQL Express

Call reporter

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxcalls`
-- -----------------------------------------------------
CREATE TABLE `ozpbxcalls` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `starttime` datetime DEFAULT NULL,
  `source` varchar(150) DEFAULT NULL,
  `callerid` varchar(150) DEFAULT NULL,
  `dialed` varchar(150) DEFAULT NULL,
  `destination` varchar(150) DEFAULT NULL,
  `duration` int(10) DEFAULT NULL,
  `callstate` varchar(150) DEFAULT NULL,
  `recordurl` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`id`)
) 

CREATE INDEX ozpbxcallsindex ON ozpbxcalls (SessionID);

Database authenticator

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxusers`
-- -----------------------------------------------------
CREATE TABLE ozpbxusers
(
    authname varchar(45) PRIMARY KEY,
    username varchar(45) NOT NULL,
    password varchar(45) NOT NULL,
)

SQL OzML

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlin`
-- -----------------------------------------------------
CREATE TABLE ozmlin
(
  id int identity(1,1) PRIMARY KEY,
  callerid varchar(40) NOT NULL,
  scriptid int(10) NOT NULL,
  duration int(10) DEFAULT NULL,
  status varchar(40) DEFAULT NULL,
  recordurl varchar(150) DEFAULT NULL,
  starttime datetime DEFAULT NULL
);

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlout`
-- -----------------------------------------------------
CREATE TABLE ozmlout
(
  id int identity(1,1) PRIMARY KEY,
  dialednumber varchar(40) NOT NULL,
  status varchar(40) DEFAULT NULL,
  duration int(10) DEFAULT NULL,
  scriptid int(10) DEFAULT NULL,
  recordurl varchar(150) DEFAULT NULL,
  starttime datetime DEFAULT NULL
);

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlscripts`
-- -----------------------------------------------------
CREATE TABLE ozmlscripts
(
  scriptid int identity(1,1) PRIMARY KEY,
  ozml varchar(10000) NOT NULL
);

SQL SMS

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxmessageout`
-- -----------------------------------------------------
CREATE TABLE ozpbxmessageout
(
    id int identity(1,1) PRIMARY KEY,
    sender varchar(30) NULL,
    receiver varchar(30) NULL,
    msg text NULL,
    senttime varchar(100) NULL,
    receivedtime varchar(100) NULL,
    reference varchar(100) NULL,
    status varchar(20) NULL,
    msgtype varchar(160) NULL,
    operator varchar(100) NULL,
    errormsg varchar(250) NULL
);

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxmessagein`
-- -----------------------------------------------------
CREATE TABLE ozpbxmessagein (
    id int identity(1,1) PRIMARY KEY,
    sender varchar(30) NULL,
    receiver varchar(30) NULL,
    msg text NULL,
    senttime varchar(100) NULL,
    receivedtime varchar(100) NULL,
    extension varchar(100) NULL
);

Database Logger

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxlog`
-- -----------------------------------------------------

CREATE  TABLE ozpbxlog
(
    id int identity(1,1) PRIMARY KEY,
    Time datetime NOT NULL,
    Thread int NOT NULL,
    LogLevel varchar(20) NOT NULL,
    Logger varchar(255) NOT NULL,
    Message text NOT NULL,
)

Oracle

Call reporter

-- -----------------------------------------------------
-- Table OzekiPBX.ozpbxcalls
-- -----------------------------------------------------
CREATE TABLE ozpbxcalls (
  id integer PRIMARY KEY,
  starttime timestamp DEFAULT NULL,
  source varchar(150) DEFAULT NULL,
  callerid varchar(150) DEFAULT NULL,
  dialed varchar(150) DEFAULT NULL,
  destination varchar(150) DEFAULT NULL,
  duration integer DEFAULT NULL,
  callstate varchar(150) DEFAULT NULL,
  recordurl varchar(150) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

CREATE SEQUENCE id_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE ozpbxcalls_insert
BEFORE INSERT ON ozpbxcalls
FOR EACH ROW
BEGIN
    SELECT id_seq.nextval INTO :new.id FROM dual;
END;
/

Database authenticator

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxusers`
-- -----------------------------------------------------
CREATE TABLE ozpbxusers
(
    authname varchar(45) PRIMARY KEY,
    username varchar(45) NOT NULL,
    password varchar(45) NOT NULL,
)

SQL OzML

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlin`
-- -----------------------------------------------------
CREATE TABLE ozmlin
(
  id integer PRIMARY KEY,
  callerid varchar(40) NOT NULL,
  scriptid integer NOT NULL,
  duration integer DEFAULT NULL,
  status varchar(40) DEFAULT NULL,
  recordurl varchar(150) DEFAULT NULL,
  starttime timestamp DEFAULT NULL
);

CREATE SEQUENCE id_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE ozmlin_insert
BEFORE INSERT ON ozmlin
FOR EACH ROW
BEGIN
    SELECT id_seq.nextval INTO :new.id FROM dual;
END;
/

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlout`
-- -----------------------------------------------------
CREATE TABLE ozmlout
(
  id integer PRIMARY KEY,
  dialednumber varchar(40) NOT NULL,
  status varchar(40) DEFAULT NULL,
  duration integer DEFAULT NULL,
  scriptid integer DEFAULT NULL,
  recordurl varchar(150) DEFAULT NULL,
  starttime timestamp DEFAULT NULL
);

CREATE SEQUENCE id_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE ozmlout_insert
BEFORE INSERT ON ozmlout
FOR EACH ROW
BEGIN
    SELECT id_seq.nextval INTO :new.id FROM dual;
END;
/

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlscripts`
-- -----------------------------------------------------
CREATE TABLE ozmlscripts
(
  scriptid integer PRIMARY KEY,
  ozml varchar(10000) NOT NULL
);

CREATE SEQUENCE scriptid_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE ozmlscripts_insert
BEFORE INSERT ON ozmlscripts
FOR EACH ROW
BEGIN
    SELECT scriptid_seq.nextval INTO :new.scriptid FROM dual;
END;
/

SQL SMS

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxmessageout`
-- -----------------------------------------------------
CREATE TABLE ozpbxmessageout
(
    id integer PRIMARY KEY,
    sender varchar(30) default NULL,
    receiver varchar(30) default NULL,
    msg nclob default NULL,
    senttime varchar(100) default NULL,
    receivedtime varchar(100) default NULL,
    reference varchar(100) default NULL,
    status varchar(20) default  NULL,
    msgtype varchar(160) default NULL,
    operator varchar(100) default NULL,
    errormsg varchar(250) default NULL
)

CREATE SEQUENCE id_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE ozpbxmessageout_insert
BEFORE INSERT ON ozpbxmessageout
FOR EACH ROW
BEGIN
    SELECT id_seq.nextval INTO :new.id FROM dual;
END;
/

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxmessagein`
-- -----------------------------------------------------
CREATE TABLE ozpbxmessagein
(
    id integer PRIMARY KEY,
    sender varchar(30) default NULL,
    receiver varchar(30) default NULL,
    msg nclob default NULL,
    senttime varchar(100) default NULL,
    receivedtime varchar(100) default NULL,
    extension varchar(100) default NULL
)

CREATE SEQUENCE id_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE ozpbxmessagein_insert
BEFORE INSERT ON ozpbxmessagein
FOR EACH ROW
BEGIN
    SELECT id_seq.nextval INTO :new.id FROM dual;
END;
/

Database Logger

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxlog`
-- -----------------------------------------------------

CREATE TABLE ozpbxlog
(
    id integer PRIMARY KEY,
    Time timestamp NOT NULL,
    Thread integer NOT NULL,
    LogLevel varchar(20) NOT NULL,
    Logger varchar(255) NOT NULL,
    Message nclob NOT NULL,
)

CREATE SEQUENCE id_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE ozpbxlog_insert
BEFORE INSERT ON ozpbxlog
FOR EACH ROW
BEGIN
    SELECT id_seq.nextval INTO :new.id FROM dual;
END;
/

MySQL

Call reporter

CREATE TABLE `ozpbxcalls` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `starttime` datetime DEFAULT NULL,
  `source` varchar(150) DEFAULT NULL,
  `callerid` varchar(150) DEFAULT NULL,
  `dialed` varchar(150) DEFAULT NULL,
  `destination` varchar(150) DEFAULT NULL,
  `duration` int(10) DEFAULT NULL,
  `callstate` varchar(150) DEFAULT NULL,
  `recordurl` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=54 DEFAULT CHARSET=UTF8;

Database authenticator

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxusers`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ozpbxusers` (
    `authname` varchar(45) NOT NULL,
    `username` varchar(45) NOT NULL,
    `password` varchar(45) NOT NULL,
    PRIMARY KEY (`authname`),
    UNIQUE INDEX `username_UNIQUE` (`username` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

SQL OzML

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlin`
-- -----------------------------------------------------
CREATE TABLE `ozmlin` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `callerid` varchar(40) NOT NULL,
  `scriptid` int(10) NOT NULL,
  `duration` int(10) DEFAULT '0',
  `status` varchar(40) DEFAULT NULL,
  `recordurl` varchar(150) DEFAULT NULL,
  `starttime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`))
ENGINE=InnoDB AUTO_INCREMENT=0
DEFAULT CHARSET=utf8;

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlout`
-- -----------------------------------------------------
CREATE TABLE `ozmlout` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `dialednumber` varchar(40) NOT NULL,
  `status` varchar(40) DEFAULT NULL,
  `duration` int(10) DEFAULT NULL,
  `scriptid` int(10) DEFAULT NULL,
  `recordurl` varchar(150) DEFAULT NULL,
  `starttime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)) 
ENGINE=InnoDB AUTO_INCREMENT=0
DEFAULT CHARSET=utf8;

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlscripts`
-- -----------------------------------------------------
CREATE TABLE `ozmlscripts` (
  `scriptid` int(10) NOT NULL AUTO_INCREMENT,
  `ozml` varchar(10000) NOT NULL,
  PRIMARY KEY (`scriptid`))
ENGINE=InnoDB AUTO_INCREMENT=0
DEFAULT CHARSET=utf8;

SQL SMS

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxmessageout`
-- -----------------------------------------------------
CREATE TABLE `ozpbxmessageout` (
    `id` int(11) auto_increment,
    `sender` varchar(30) default NULL,
    `receiver` varchar(30) default NULL,
    `msg` text default NULL,
    `senttime` varchar(100) default NULL,
    `receivedtime` varchar(100) default NULL,
    `reference` varchar(100) default NULL,
    `status` varchar(20) default NULL,
    `msgtype` varchar(160) default NULL,
    `operator` varchar(100) default NULL,
    `errormsg` varchar(250) default NULL,
    INDEX (`id` ASC),
    PRIMARY KEY (id))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxmessagein`
-- -----------------------------------------------------
CREATE TABLE `ozpbxmessagein` (
    `id` int(11) auto_increment,
    `sender` varchar(30) default NULL,
    `receiver` varchar(30) default NULL,
    `msg` text default NULL,
    `senttime` varchar(100) default NULL,
    `receivedtime` varchar(100) default NULL,
    `extension` varchar(100) default NULL,
    INDEX (`id` ASC),
    PRIMARY KEY (id))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

Database Logger

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxlog`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `ozpbxlog` (
    `id` int(11) AUTO_INCREMENT,
    `Time` datetime NOT NULL,
    `Thread` int(11) NOT NULL,
    `LogLevel` varchar(20) NOT NULL,
    `Logger` varchar(255) NOT NULL,
    `Message` text NOT NULL,
    PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

PostgreSQL

Call reporter

CREATE TABLE `ozpbxcalls` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `starttime` datetime DEFAULT NULL,
  `source` varchar(150) DEFAULT NULL,
  `callerid` varchar(150) DEFAULT NULL,
  `dialed` varchar(150) DEFAULT NULL,
  `destination` varchar(150) DEFAULT NULL,
  `duration` int(10) DEFAULT NULL,
  `callstate` varchar(150) DEFAULT NULL,
  `recordurl` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

Database authenticator

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxusers`
-- -----------------------------------------------------
CREATE TABLE ozpbxusers
(
    authname varchar(45) PRIMARY KEY,
    username varchar(45) NOT NULL,
    password varchar(45) NOT NULL
}

SQL OzML

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlin`
-- -----------------------------------------------------
CREATE TABLE ozmlin
(
  id serial PRIMARY KEY,
  callerid varchar(40) NOT NULL,
  scriptid integer NOT NULL,
  duration integer DEFAULT NULL,
  status varchar(40) DEFAULT NULL,
  recordurl varchar(150) DEFAULT NULL,
  starttime date DEFAULT NULL
)

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlout`
-- -----------------------------------------------------
CREATE TABLE ozmlout
(
  id serial PRIMARY KEY,
  dialednumber varchar(40) NOT NULL,
  status varchar(40) DEFAULT NULL,
  duration integer DEFAULT NULL,
  scriptid integer DEFAULT NULL,
  recordurl varchar(150) DEFAULT NULL,
  starttime date DEFAULT NULL
)

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlscripts`
-- -----------------------------------------------------
CREATE TABLE ozmlscripts
(
  scriptid serial PRIMARY KEY,
  ozml varchar(10000) NOT NULL
)

SQL SMS

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxmessageout`
-- -----------------------------------------------------
CREATE TABLE ozpbxmessageout 
(
    id serial PRIMARY KEY, 
    sender varchar(30) NULL,
    receiver varchar(30) NULL,
    msg text NULL,
    senttime varchar(100) NULL,
    receivedtime varchar(100) NULL,
    reference varchar(100) NULL,
    status varchar(20) NULL,
    msgtype varchar(160) NULL,
    operator varchar(100) NULL,
    errormsg varchar(250) NULL
)

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxmessagein`
-- -----------------------------------------------------
CREATE TABLE ozpbxmessagein
(
    id serial PRIMARY KEY, 
    sender varchar(30) NULL,
    receiver varchar(30) NULL,
    msg text NULL,
    senttime varchar(100) NULL,
    receivedtime varchar(100) NULL,
    extension varchar(100) NULL
)

Database Logger

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxlog`
-- -----------------------------------------------------
CREATE TABLE ozpbxlog
(
    id serial PRIMARY KEY, 
    Time date NOT NULL,
    Thread integer NOT NULL,
    LogLevel varchar(20) NOT NULL,
    Logger varchar(255) NOT NULL,
    Message text NOT NULL
)

Sybase

Call reporter

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxcalls`
-- -----------------------------------------------------
CREATE TABLE `ozpbxcalls` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `starttime` datetime DEFAULT NULL,
  `source` varchar(150) DEFAULT NULL,
  `callerid` varchar(150) DEFAULT NULL,
  `dialed` varchar(150) DEFAULT NULL,
  `destination` varchar(150) DEFAULT NULL,
  `duration` int(10) DEFAULT NULL,
  `callstate` varchar(150) DEFAULT NULL,
  `recordurl` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`id`)
) 

go 
commit work 
go

CREATE INDEX "ozpbxcallsindex" ON "ozpbxcalls" 
( 
	"SessionID" ASC 
)
 
go 
commit work 
go

Database authenticator

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxusers`
-- -----------------------------------------------------
CREATE TABLE "ozpbxusers" (
  "authname" varchar(45) NOT NULL,
  "username" varchar(45) NOT NULL,
  "password" varchar(45) NOT NULL,
  PRIMARY KEY ("authname")
}

go
commit work
go

CREATE INDEX "ozpbxusersindex" ON "ozpbxusers" 
( 
	"username" ASC 
)

go 
commit work 
go

SQL OzML

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlin`
-- -----------------------------------------------------
CREATE TABLE ozmlin
(
  "id" integer NOT NULL autoincrement,
  "callerid" varchar(40) NOT NULL,
  "scriptid" integer NOT NULL,
  "duration" integer DEFAULT NULL,
  "status" varchar(40) DEFAULT NULL,
  "recordurl" varchar(150) DEFAULT NULL,
  "starttime" datetime DEFAULT NULL
  PRIMARY KEY ("id")
)

go 
commit work 
go

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlout`
-- -----------------------------------------------------
CREATE TABLE ozmlout
(
  "id" integer NOT NULL autoincrement,
  "dialednumber" varchar(40) NOT NULL,
  "status" varchar(40) DEFAULT NULL,
  "duration" integer DEFAULT NULL,
  "scriptid" integer DEFAULT NULL,
  "recordurl" varchar(150) DEFAULT NULL,
  "starttime" datetime DEFAULT NULL
  PRIMARY KEY ("id")
)

go 
commit work 
go

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlscripts`
-- -----------------------------------------------------
CREATE TABLE ozmlscripts
(
  "scriptid" integer NOT NULL autoincrement,
  "ozml" varchar(10000) NOT NULL
  PRIMARY KEY ("scriptid")
)

go 
commit work 
go

SQL SMS

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxmessageout`
-- -----------------------------------------------------
CREATE TABLE "ozpbxmessageout" (
	"id" integer NOT NULL DEFAULT autoincrement,
	"sender" varchar(30) NULL,
	"receiver" varchar(30) NULL,
	"msg" text NULL,
	"senttime" varchar(100) NULL,
	"receivedtime" varchar(100) NULL,
	"reference" varchar(100) NULL,
	"status" varchar(20) NULL,
	"msgtype" varchar(160) NULL,
	"operator" varchar(100) NULL,
	"errormsg" varchar(250) NULL,
	PRIMARY KEY ("id")
)

go 
commit work 
go

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxmessagein`
-- -----------------------------------------------------
CREATE TABLE "ozpbxmessagein" (
	"id" integer NOT NULL DEFAULT autoincrement,
	"sender" varchar(30) NULL,
	"receiver" varchar(30) NULL,
	"msg" text NULL,
	"senttime" varchar(100) NULL,
	"receivedtime" varchar(100) NULL,
	"extension" varchar(100) NULL,
	PRIMARY KEY ("id")
)

go 
commit work 
go

CREATE INDEX "ozpbxmessageinindex" ON "ozpbxmessagein" 
( 
	"id" ASC 
) 

go 
commit work 
go

CREATE INDEX "ozpbxmessageoutindex" ON "ozpbxmessageout"
( 
	"id" ASC 
) 

go 
commit work 
go

Database Logger

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxlog`
-- -----------------------------------------------------
CREATE TABLE "ozpbxlog" (
  "id" integer NOT NULL DEFAULT autoincrement,
  "Time" datetime NOT NULL,
  "Thread" integer NOT NULL,
  "LogLevel" varchar(20) NOT NULL,
  "Logger" varchar(255) NOT NULL,
  "Message" text NOT NULL,
  PRIMARY KEY ("id")
)

go 
commit work 
go

DB2

Call reporter

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxcalls`
-- -----------------------------------------------------
CREATE TABLE `ozpbxcalls` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `starttime` datetime DEFAULT NULL,
  `source` varchar(150) DEFAULT NULL,
  `callerid` varchar(150) DEFAULT NULL,
  `dialed` varchar(150) DEFAULT NULL,
  `destination` varchar(150) DEFAULT NULL,
  `duration` int(10) DEFAULT NULL,
  `callstate` varchar(150) DEFAULT NULL,
  `recordurl` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`id`)
) 
 

CREATE UNIQUE INDEX INDEX ozpbxcallsindex ON 
                    ozpbxcalls (SessionID)

Database authenticator

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxusers`
-- -----------------------------------------------------
CREATE TABLE ozpbxusers
   (authname     VARCHAR(45) NOT NULL,
    username     VARCHAR(45) NOT NULL,
    password     VARCHAR(45) NOT NULL,
    PRIMARY KEY (authname)}

SQL OzML

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlin`
-- -----------------------------------------------------
CREATE TABLE ozmlin
 (id integer NOT NULL GENERATED ALWAYS AS 
     IDENTITY (START WITH 1 INCREMENT BY 1),
  callerid varchar(40) NOT NULL,
  scriptid integer NOT NULL,
  duration integer DEFAULT NULL,
  status varchar(40) DEFAULT NULL,
  recordurl varchar(150) DEFAULT NULL,
  starttime timestamp DEFAULT NULL)

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlout`
-- -----------------------------------------------------
CREATE TABLE ozmlout
(
 (id integer NOT NULL GENERATED ALWAYS AS 
     IDENTITY (START WITH 1 INCREMENT BY 1),
  dialednumber varchar(40) NOT NULL,
  status varchar(40) DEFAULT NULL,
  duration integer DEFAULT NULL,
  scriptid integer DEFAULT NULL,
  recordurl varchar(150) DEFAULT NULL,
  starttime timestamp DEFAULT NULL)

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlscripts`
-- -----------------------------------------------------
CREATE TABLE ozmlscripts
(
 (scriptid integer NOT NULL GENERATED ALWAYS AS 
     IDENTITY (START WITH 1 INCREMENT BY 1),
  ozml varchar(10000) NOT NULL)

SQL SMS

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxmessageout`
-- -----------------------------------------------------
CREATE TABLE ozpbxmessageout
   (id integer NOT NULL GENERATED ALWAYS AS 
       IDENTITY (START WITH 1 INCREMENT BY 1),
    sender        VARCHAR(30)  NULL,
    receiver      VARCHAR(30)  NULL,
    msg           CLOB         NULL,
    senttime      VARCHAR(100) NULL,
    receivedtime  VARCHAR(100) NULL,
    reference     VARCHAR(100) NULL,
    status        VARCHAR(20)  NULL,
    msgtype       VARCHAR(160) NULL,
    operator      VARCHAR(100) NULL,
    errormsg      VARCHAR(250) NULL,
    PRIMARY KEY (id))

CREATE UNIQUE INDEX INDEX ozpbxmessageoutindex ON 
                    ozpbxmessageout (id)

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxmessagein`
-- -----------------------------------------------------
CREATE TABLE ozpbxmessagein
   (id integer NOT NULL GENERATED ALWAYS AS 
       IDENTITY (START WITH 1 INCREMENT BY 1),
    sender        VARCHAR(30)  NULL,
    receiver      VARCHAR(30)  NULL,
    msg           CLOB         NULL,
    senttime      VARCHAR(100) NULL,
    receivedtime  VARCHAR(100) NULL,
    extension     VARCHAR(100) NULL,
    PRIMARY KEY (id))

CREATE UNIQUE INDEX INDEX ozpbxmessageinindex ON 
                    ozpbxmessagein (id)

Database Logger

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxlog`
-- -----------------------------------------------------
CREATE TABLE ozpbxlog (
   (id integer NOT NULL GENERATED ALWAYS AS 
       IDENTITY (START WITH 1 INCREMENT BY 1),
    Time          TIMESTAMP    NOT NULL,
    Thread        INTEGER      NOT NULL,
    LogLevel      VARCHAR(20)  NOT NULL,
    Logger        VARCHAR(255) NOT NULL,
    Message       CLOB         NOT NULL,
    PRIMARY KEY (id))

Informix

Call reporter

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxcalls`
-- -----------------------------------------------------
CREATE TABLE `ozpbxcalls` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `starttime` datetime DEFAULT NULL,
  `source` varchar(150) DEFAULT NULL,
  `callerid` varchar(150) DEFAULT NULL,
  `dialed` varchar(150) DEFAULT NULL,
  `destination` varchar(150) DEFAULT NULL,
  `duration` int(10) DEFAULT NULL,
  `callstate` varchar(150) DEFAULT NULL,
  `recordurl` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

Database authenticator

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxusers`
-- -----------------------------------------------------
CREATE TABLE ozpbxusers
(
  authname varchar(45) PRIMARY KEY,
  username varchar(45) NOT NULL,
  password varchar(45) NOT NULL
}

SQL OzML

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlin`
-- -----------------------------------------------------
CREATE TABLE ozmlin
(
  id serial PRIMARY KEY,
  callerId varchar(40) NOT NULL,
  scriptId int NOT NULL,
  duration int DEFAULT NULL,
  status varchar(40) DEFAULT NULL,
  recordURL varchar(150) DEFAULT NULL,
  startTime datetime DEFAULT NULL
)

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlout`
-- -----------------------------------------------------
CREATE TABLE ozmlout
(
  id serial PRIMARY KEY,
  dialedNumber varchar(40) NOT NULL,
  status varchar(40) DEFAULT NULL,
  duration int DEFAULT NULL,
  scriptId int DEFAULT NULL,
  recordUrl varchar(150) DEFAULT NULL,
  startTime datetime DEFAULT NULL
)

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlscripts`
-- -----------------------------------------------------
CREATE TABLE ozmlscripts
(
  scriptid serial PRIMARY KEY,
  ozml varchar(10000) NOT NULL
)

SQL SMS

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxmessageout`
-- -----------------------------------------------------
CREATE TABLE ozpbxmessageout
(
	id serial PRIMARY KEY, 
	sender varchar(30) NULL,
	receiver varchar(30) NULL,
	msg text NULL,
	senttime varchar(100) NULL,
	receivedtime varchar(100) NULL,
	reference varchar(100) NULL,
	status varchar(20) NULL,
	msgtype varchar(160) NULL,
	operator varchar(100) NULL,
	errormsg varchar(250) NULL
)

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxmessagein`
-- -----------------------------------------------------
CREATE TABLE ozpbxmessagein
(
	id serial PRIMARY KEY,
	sender varchar(30) NULL,
	receiver varchar(30) NULL,
	msg text NULL,
	senttime varchar(100) NULL,
	receivedtime varchar(100) NULL,
	extension varchar(100) NULL
)

Database Logger

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxlog`
-- -----------------------------------------------------
CREATE TABLE ozpbxlog
(
	id serial PRIMARY KEY, 
	Time datetime NOT NULL,
	Thread int NOT NULL,
	LogLevel varchar(20) NOT NULL,
	Logger varchar(255) NOT NULL,
	Message text NOT NULL
)

FoxPro

Call reporter

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxcalls`
-- -----------------------------------------------------
CREATE TABLE `ozpbxcalls` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `starttime` datetime DEFAULT NULL,
  `source` varchar(150) DEFAULT NULL,
  `callerid` varchar(150) DEFAULT NULL,
  `dialed` varchar(150) DEFAULT NULL,
  `destination` varchar(150) DEFAULT NULL,
  `duration` int(10) DEFAULT NULL,
  `callstate` varchar(150) DEFAULT NULL,
  `recordurl` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

Database authenticator

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxusers`
-- -----------------------------------------------------
CREATE TABLE ozpbxusers
   (authname C(45) PRIMARY KEY,
    username C(45) NOT NULL,
    password C(45) NOT NULL}

SQL OzML

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlin`
-- -----------------------------------------------------
CREATE TABLE ozmlin
(
  id I AUTOINC NEXTVALUE 1 STEP 1 PRIMARY KEY, 
  callerId C(40) NOT NULL,
  scriptId I NOT NULL,
  duration I DEFAULT NULL,
  status C(40) DEFAULT NULL,
  recordURL C(150) DEFAULT NULL,
  startTime T DEFAULT NULL
)

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlout`
-- -----------------------------------------------------
CREATE TABLE ozmlout
(
  id I AUTOINC NEXTVALUE 1 STEP 1 PRIMARY KEY, 
  dialedNumber C(40) NOT NULL,
  status C(40) DEFAULT NULL,
  duration I DEFAULT NULL,
  scriptId I DEFAULT NULL,
  recordUrl C(150) DEFAULT NULL,
  startTime T DEFAULT NULL
)

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozmlscripts`
-- -----------------------------------------------------
CREATE TABLE ozmlscripts
(
  scriptid I AUTOINC NEXTVALUE 1 STEP 1 PRIMARY KEY, 
  ozml C(10000) NOT NULL
)

SQL SMS

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxmessageout`
-- -----------------------------------------------------
CREATE TABLE ozpbxmessageout 
   (id I AUTOINC NEXTVALUE 1 STEP 1 PRIMARY KEY, 
	sender C(30) NULL,
	receiver C(30) NULL,
	msg N NULL,
	senttime C(100) NULL,
	receivedtime C(100) NULL,
	reference C(100) NULL,
	status C(20) NULL,
	msgtype C(160) NULL,
	operator C(100) NULL,
	errormsg C(250) NULL)

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxmessagein`
-- -----------------------------------------------------
CREATE TABLE ozpbxmessagein
   (id I AUTOINC NEXTVALUE 1 STEP 1 PRIMARY KEY, 
	sender C(30) NULL,
	receiver C(30) NULL,
	msg N NULL,
	senttime C(100) NULL,
	receivedtime C(100) NULL,
	extension C(100) NULL)

Database Logger

-- -----------------------------------------------------
-- Table `OzekiPBX`.`ozpbxlog`
-- -----------------------------------------------------
CREATE TABLE ozpbxlog
   (id I AUTOINC NEXTVALUE 1 STEP 1 PRIMARY KEY, 
    Time T NOT NULL,
    Thread I NOT NULL,
    LogLevel C(20) NOT NULL,
    Logger C(255) NOT NULL,
    Message N NOT NULL)

More information

Create table scripts
How to configure your PBX to connect to MySQL Server
Microsoft SQL Server
Microsoft SQL Express
Microsoft Access
Oracle
MySQL
PostgreSQL
Sybase (SQL Anywhere)
DB2
Informix
Amazon SimpleDB
FoxPro

< Database servers | Setup Ozeki PBX to use SQL >

Home > Online manual > Developers guide > Database servers > Create table scripts

Page: 5423 | 3.145.60.29 | 79.99.42.43 | Login

Privacy | Terms of use

How to create database tables to your SQL API

More information

How to create database tables
to your SQL API