Script SQL para criação de tabelas do ARA dynamic no Asterisk 11.

realtime.sql

CREATE DATABASE asterisk;

\c asterisk

CREATE TABLE extensions (
  id serial,
  context character varying(20) default '' NOT NULL,
  exten character varying(20) default '' NOT NULL,
  priority smallint default 0 NOT NULL,
  app character varying(20) default '' NOT NULL,
  appdata character varying(128),
  CONSTRAINT extensions_pk PRIMARY KEY (context, exten, priority)
);

CREATE TABLE musiconhold (
  name varchar(80) NOT NULL,
  directory varchar(255) NOT NULL default '',
  application varchar(255) NOT NULL default '',
  mode varchar(80) NOT NULL default '',
  digit char(1) NOT NULL default '',
  sort varchar(16) NOT NULL default '',
  format varchar(16) NOT NULL default '',
  CONSTRAINT musiconhold_pk PRIMARY KEY (name)
);

CREATE TABLE queues (
  name varchar(128),
  musiconhold varchar(128) default 'default',
  announce varchar(128),
  context varchar(128) default 'public',
  timeout int8 default 0 NOT NULL,
  monitor_join bool default true,
  monitor_format varchar(128) default 'wav49',
  queue_youarenext varchar(128),
  queue_thereare varchar(128),
  queue_callswaiting varchar(128),
  queue_holdtime varchar(128),
  queue_minutes varchar(128),
  queue_seconds varchar(128),
  queue_lessthan varchar(128),
  queue_thankyou varchar(128),
  queue_reporthold varchar(128),
  announce_frequency int8,
  announce_round_seconds int8,
  announce_holdtime varchar(128) default 'no' NOT NULL,
  retry int8,
  wrapuptime int8 default 0 NOT NULL,
  maxlen int8 default 0 NOT NULL,
  servicelevel int8,
  strategy varchar(128),
  joinempty varchar(128),
  leavewhenempty varchar(128),
  eventmemberstatus bool,
  eventwhencalled bool,
  reportholdtime bool,
  memberdelay int8,
  weight int8,
  timeoutrestart bool,
  setinterfacevar bool,
  PRIMARY KEY (name)
) WITHOUT OIDS;

CREATE TABLE queue_members (
  uniqueid serial,
  membername varchar(40) default '',
  queue_name varchar(128) NOT NULL,
  interface varchar(128) NOT NULL,
  penalty int,
  paused int,
  PRIMARY KEY (queue_name, interface)
) WITHOUT OIDS;

CREATE TABLE sip (
  id serial NOT NULL,
  name character varying(80) default '' NOT NULL,
  accountcode character varying(20),
  amaflags character varying(7),
  callgroup character varying(10),
  callerid character varying(80),
  canreinvite character varying(3) default 'no',
  context character varying(80) default 'public',
  defaultip character varying(15),
  dtmfmode character varying(7) default 'rfc2833',
  fromuser character varying(80),
  fromdomain character varying(80),
  host character varying(31) default 'dynamic' NOT NULL,
  insecure character varying(4) default 'port, invite',
  "language" character varying(2) default 'pt_BR',
  mailbox character varying(50),
  md5secret character varying(80),
  nat character varying(5) default 'never' NOT NULL,
  permit character varying(95),
  deny character varying(95),
  mask character varying(95),
  pickupgroup character varying(10),
  port character varying(5) default '' NOT NULL,
  qualify character varying(3) default 'yes',
  restrictcid character varying(1),
  rtptimeout character varying(3),
  rtpholdtimeout character varying(3),
  secret character varying(80),
  "type" character varying default 'friend' NOT NULL,
  username character varying(80) default '' NOT NULL,
  allow character varying(200) default '!all,g729,gsm,ulaw,alaw',
  musiconhold character varying(100),
  regseconds bigint default 0::bigint NOT NULL,
  ipaddr character varying(45) default '' NOT NULL,
  regexten character varying(80) default '' NOT NULL,
  cancallforward character varying(3) default 'yes',
  lastms integer default 0 NOT NULL,
  defaultuser character varying(80) NOT NULL,
  fullcontact character varying(80),
  regserver character varying(30),
  useragent character varying(40),
  callbackextension character varying(40) NOT NULL,
  CONSTRAINT sip_name_uk UNIQUE (name)
);

CREATE TABLE iax (
  name varchar(30) NOT NULL,
  username varchar(30) NOT NULL,
  type varchar(6) default 'friend' NOT NULL,
  secret varchar(50) NOT NULL,
  md5secret varchar(32),
  dbsecret varchar(100),
  notransfer varchar(10),
  inkeys varchar(100),
  outkey varchar(100),
  auth varchar(100) default 'plaintext' NOT NULL,
  accountcode varchar(100),
  amaflags varchar(100),
  callerid varchar(100) NOT NULL,
  context varchar(100) default 'private' NOT NULL,
  defaultip varchar(15),
  host varchar(31) default 'dynamic' NOT NULL,
  language char(5) default 'pt_BR' NOT NULL,
  mailbox varchar(50),
  deny varchar(95),
  permit varchar(95),
  qualify varchar(4) default 'yes' NOT NULL,
  disallow varchar(100),
  allow varchar(100),
  ipaddr varchar(15),
  port integer default 0,
  regseconds integer default 0,
  CONSTRAINT iax_pk PRIMARY KEY (name)
);

COMMENT ON TABLE iax IS 'Tabela Realtime de iax do Asterisk';
COMMENT ON TABLE sip IS 'Tabela Realtime de sip do Asterisk';
COMMENT ON TABLE queues IS 'Tabela Realtime de queue do Asterisk';
COMMENT ON TABLE queue_members IS 'Tabela Realtime de members do Asterisk';
COMMENT ON TABLE musiconhold IS 'Tabela Realtime de music on hold do Asterisk';
COMMENT ON TABLE extensions IS 'Tabela Realtime de extensions do Asterisk';

CREATE USER asterisk WITH PASSWORD 'your_password';
ALTER TABLE extensions OWNER TO asterisk;
ALTER TABLE sip OWNER TO asterisk;
ALTER TABLE iax OWNER TO asterisk;
ALTER TABLE queues OWNER TO asterisk;
ALTER TABLE queue_members OWNER TO asterisk;
ALTER TABLE musiconhold OWNER TO asterisk;