V1.2 MySQL Create Tables DDL

Discussions about using SIP Sorcery on your own computer/server
Post Reply
Trimline
Posts: 28
Joined: Sat May 15, 2010 7:59 pm

V1.2 MySQL Create Tables DDL

Post by Trimline » Mon Jul 05, 2010 2:32 pm

Included is the DDL for Sip Sorcercery V1.2 MySQL database. You need only change the sipdomains entry either prior to the query escalation or via your SQL editor. Snip below. For those in need, I recommend the free version of Navicat to edit data or tables.

/*
Navicat MySQL Data Transfer
Source Server : MySQL
Sipsorcery Create DB's
*/

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `customers`
-- ----------------------------
CREATE TABLE `customers` (
`id` varchar(36) NOT NULL,
`customerusername` varchar(32) NOT NULL,
`customerpassword` varchar(32) NOT NULL,
`emailaddress` varchar(255) NOT NULL,
`firstname` varchar(64) DEFAULT NULL,
`lastname` varchar(64) DEFAULT NULL,
`city` varchar(64) DEFAULT NULL,
`country` varchar(64) DEFAULT NULL,
`website` varchar(256) DEFAULT NULL,
`active` bit(1) NOT NULL DEFAULT b'1',
`suspended` bit(1) NOT NULL DEFAULT b'0',
`securityquestion` varchar(1024) DEFAULT NULL,
`securityanswer` varchar(256) DEFAULT NULL,
`createdfromipaddress` varchar(15) DEFAULT NULL,
`adminid` varchar(32) DEFAULT NULL,
`adminmemberid` varchar(32) DEFAULT NULL,
`maxexecutioncount` int(11) NOT NULL,
`executioncount` int(11) NOT NULL,
`authorisedapps` varchar(2048) DEFAULT NULL,
`timezone` varchar(128) DEFAULT NULL,
`emailaddressconfirmed` bit(1) NOT NULL DEFAULT b'0',
`inserted` varchar(33) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `customerusername` (`customerusername`),
KEY `customers_custid_index` (`customerusername`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `customersessions` (
`id` varchar(36) NOT NULL,
`sessionid` varchar(96) NOT NULL,
`customerusername` varchar(32) NOT NULL,
`inserted` varchar(33) NOT NULL,
`expired` bit(1) NOT NULL DEFAULT b'0',
`ipaddress` varchar(15) DEFAULT NULL,
`timelimitminutes` int(11) NOT NULL DEFAULT '60',
PRIMARY KEY (`id`),
KEY `customerusername` (`customerusername`),
CONSTRAINT `customersessions_ibfk_1` FOREIGN KEY (`customerusername`) REFERENCES `customers` (`customerusername`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `sipdomains` (
`id` varchar(36) NOT NULL,
`domain` varchar(128) NOT NULL,
`aliaslist` varchar(1024) DEFAULT NULL,
`owner` varchar(32) DEFAULT NULL,
`inserted` varchar(33) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `domain` (`domain`),
KEY `owner` (`owner`),
CONSTRAINT `sipdomains_ibfk_1` FOREIGN KEY (`owner`) REFERENCES `customers` (`customerusername`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of sipdomains
-- ----------------------------
INSERT INTO `sipdomains` VALUES ('7dcc3cf9-7687-4e29-add3-1b97ba545088', 'Your.domain.net', 'your.ip.address;local.address;local;*;', null, '2010-02-09T13:01:21.3540000+00:00');

CREATE TABLE `sipaccounts` (
`id` varchar(36) NOT NULL,
`sipusername` varchar(32) NOT NULL,
`sippassword` varchar(32) NOT NULL,
`owner` varchar(32) NOT NULL,
`adminmemberid` varchar(32) DEFAULT NULL,
`sipdomain` varchar(128) NOT NULL,
`sendnatkeepalives` bit(1) NOT NULL DEFAULT b'1',
`isincomingonly` bit(1) NOT NULL DEFAULT b'0',
`outdialplanname` varchar(64) DEFAULT NULL,
`indialplanname` varchar(64) DEFAULT NULL,
`isuserdisabled` bit(1) NOT NULL DEFAULT b'0',
`isadmindisabled` bit(1) NOT NULL DEFAULT b'0',
`admindisabledreason` varchar(256) DEFAULT NULL,
`networkid` varchar(16) DEFAULT NULL,
`ipaddressacl` varchar(256) DEFAULT NULL,
`inserted` varchar(33) NOT NULL,
`isswitchboardenabled` bit(1) NOT NULL DEFAULT b'1',
PRIMARY KEY (`id`),
UNIQUE KEY `sipusername` (`sipusername`,`sipdomain`),
KEY `owner` (`owner`),
KEY `sipdomain` (`sipdomain`),
CONSTRAINT `sipaccounts_ibfk_1` FOREIGN KEY (`owner`) REFERENCES `customers` (`customerusername`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `sipaccounts_ibfk_2` FOREIGN KEY (`sipdomain`) REFERENCES `sipdomains` (`domain`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `sipregistrarbindings` (
`id` varchar(36) NOT NULL,
`sipaccountid` varchar(36) NOT NULL,
`sipaccountname` varchar(160) NOT NULL,
`owner` varchar(32) NOT NULL,
`adminmemberid` varchar(32) DEFAULT NULL,
`useragent` varchar(1024) DEFAULT NULL,
`contacturi` varchar(767) NOT NULL,
`mangledcontacturi` varchar(767) DEFAULT NULL,
`expiry` int(11) NOT NULL,
`remotesipsocket` varchar(64) NOT NULL,
`proxysipsocket` varchar(64) DEFAULT NULL,
`registrarsipsocket` varchar(64) NOT NULL,
`lastupdate` varchar(33) NOT NULL,
`expirytime` varchar(33) NOT NULL,
PRIMARY KEY (`id`),
KEY `owner` (`owner`),
KEY `regbindings_sipaccid_index` (`sipaccountid`),
KEY `regbindings_contact_index` (`contacturi`(255)),
CONSTRAINT `sipregistrarbindings_ibfk_1` FOREIGN KEY (`sipaccountid`) REFERENCES `sipaccounts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `sipregistrarbindings_ibfk_2` FOREIGN KEY (`owner`) REFERENCES `customers` (`customerusername`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `sipdialplans` (
`id` varchar(36) NOT NULL,
`owner` varchar(32) NOT NULL,
`adminmemberid` varchar(32) DEFAULT NULL,
`dialplanname` varchar(64) NOT NULL DEFAULT 'default',
`traceemailaddress` varchar(256) DEFAULT NULL,
`dialplanscript` varchar(4096) DEFAULT NULL,
`scripttypedescription` varchar(12) NOT NULL DEFAULT 'Ruby',
`inserted` varchar(33) NOT NULL,
`lastupdate` varchar(33) NOT NULL,
`maxexecutioncount` int(11) NOT NULL,
`executioncount` int(11) NOT NULL,
`authorisedapps` varchar(2048) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `owner` (`owner`,`dialplanname`),
CONSTRAINT `sipdialplans_ibfk_1` FOREIGN KEY (`owner`) REFERENCES `customers` (`customerusername`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `sipdialogues` (
`id` varchar(36) NOT NULL DEFAULT '',
`owner` varchar(32) DEFAULT NULL,
`adminmemberid` varchar(32) DEFAULT NULL,
`localtag` varchar(64) DEFAULT NULL,
`remotetag` varchar(64) DEFAULT NULL,
`callid` varchar(128) DEFAULT NULL,
`cseq` int(11) DEFAULT NULL,
`bridgeid` varchar(36) DEFAULT NULL,
`remotetarget` varchar(256) DEFAULT NULL,
`localuserfield` varchar(512) DEFAULT NULL,
`remoteuserfield` varchar(512) DEFAULT NULL,
`proxysipsocket` varchar(64) DEFAULT NULL,
`routeset` varchar(512) DEFAULT NULL,
`cdrid` varchar(36) DEFAULT NULL,
`calldurationlimit` int(11) DEFAULT NULL,
`inserted` varchar(33) DEFAULT NULL,
`hangupat` varchar(33) DEFAULT NULL,
`transfermode` varchar(16) DEFAULT NULL,
`direction` varchar(3) DEFAULT NULL,
`sdp` varchar(2048) DEFAULT NULL,
`remotesdp` varchar(2048) DEFAULT NULL,
`switchboarddescription` varchar(1024) DEFAULT NULL,
`switchboardcallerdescription` varchar(1024) DEFAULT NULL,
`switchboardowner` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `owner` (`owner`),
CONSTRAINT `sipdialogues_ibfk_1` FOREIGN KEY (`owner`) REFERENCES `customers` (`customerusername`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `cdr` (
`id` varchar(36) NOT NULL,
`owner` varchar(32) DEFAULT NULL,
`adminmemberid` varchar(32) DEFAULT NULL,
`inserted` varchar(33) NOT NULL,
`direction` varchar(3) NOT NULL,
`created` varchar(33) NOT NULL,
`dst` varchar(128) DEFAULT NULL,
`dsthost` varchar(128) NOT NULL,
`dsturi` varchar(1024) NOT NULL,
`fromuser` varchar(128) DEFAULT NULL,
`fromname` varchar(128) DEFAULT NULL,
`fromheader` varchar(1024) DEFAULT NULL,
`callid` varchar(256) NOT NULL,
`localsocket` varchar(64) NOT NULL,
`remotesocket` varchar(64) NOT NULL,
`bridgeid` varchar(36) DEFAULT NULL,
`inprogresstime` varchar(33) DEFAULT NULL,
`inprogressstatus` int(11) DEFAULT NULL,
`inprogressreason` varchar(512) DEFAULT NULL,
`ringduration` int(11) DEFAULT NULL,
`answeredtime` varchar(33) DEFAULT NULL,
`answeredstatus` int(11) DEFAULT NULL,
`answeredreason` varchar(512) DEFAULT NULL,
`duration` int(11) DEFAULT NULL,
`hunguptime` varchar(33) DEFAULT NULL,
`hungupreason` varchar(512) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `cdrs_lastname_index` (`created`),
KEY `cdrs_owner_index` (`owner`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `dialplandata` (
`dataowner` varchar(32) NOT NULL,
`datakey` varchar(64) NOT NULL,
`datavalue` varchar(1024) NOT NULL,
PRIMARY KEY (`dataowner`,`datakey`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `sipproviders` (
`id` varchar(36) NOT NULL,
`owner` varchar(32) NOT NULL,
`adminmemberid` varchar(32) DEFAULT NULL,
`providername` varchar(50) NOT NULL,
`providerusername` varchar(32) NOT NULL,
`providerpassword` varchar(32) DEFAULT NULL,
`providerserver` varchar(256) NOT NULL,
`providerauthusername` varchar(32) DEFAULT NULL,
`provideroutboundproxy` varchar(256) DEFAULT NULL,
`providerfrom` varchar(256) DEFAULT NULL,
`customheaders` varchar(1024) DEFAULT NULL,
`registercontact` varchar(256) DEFAULT NULL,
`registerexpiry` int(11) DEFAULT NULL,
`registerserver` varchar(256) DEFAULT NULL,
`registerrealm` varchar(256) DEFAULT NULL,
`registerenabled` bit(1) NOT NULL DEFAULT b'0',
`registeradminenabled` bit(1) NOT NULL DEFAULT b'1',
`registerdisabledreason` varchar(256) DEFAULT NULL,
`inserted` varchar(33) NOT NULL,
`lastupdate` varchar(33) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `owner` (`owner`,`providername`),
CONSTRAINT `sipproviders_ibfk_1` FOREIGN KEY (`owner`) REFERENCES `customers` (`customerusername`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `sipproviderbindings` (
`id` varchar(36) NOT NULL,
`providerid` varchar(36) NOT NULL,
`providername` varchar(50) NOT NULL,
`owner` varchar(32) NOT NULL,
`adminmemberid` varchar(32) DEFAULT NULL,
`registrationfailuremessage` varchar(1024) DEFAULT NULL,
`nextregistrationtime` varchar(33) NOT NULL,
`lastregistertime` varchar(33) DEFAULT NULL,
`lastregisterattempt` varchar(33) DEFAULT NULL,
`isregistered` bit(1) NOT NULL DEFAULT b'0',
`bindingexpiry` int(11) NOT NULL DEFAULT '3600',
`bindinguri` varchar(256) NOT NULL,
`registrarsipsocket` varchar(256) DEFAULT NULL,
`cseq` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `owner` (`owner`),
KEY `providerid` (`providerid`),
KEY `providerbindings_nextregtime_index` (`nextregistrationtime`),
CONSTRAINT `sipproviderbindings_ibfk_1` FOREIGN KEY (`owner`) REFERENCES `customers` (`customerusername`),
CONSTRAINT `sipproviderbindings_ibfk_2` FOREIGN KEY (`providerid`) REFERENCES `sipproviders` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

gxtong
Posts: 3
Joined: Sat Aug 07, 2010 5:06 am

Re: V1.2 MySQL Create Tables DDL

Post by gxtong » Sat Aug 07, 2010 5:14 am

Thanks for your DDL. It works perfect for me. I installed sipsorcery on my local machine successfully.
However when I saved my "Dial Plan" (copied from my sipsorcery plan) I got the following error.
2010-08-06 21:53:16,590 [5] ERROR sipsorcery [(null)] - Exception SQLAssetPersistor Update (for SIPDialPlan). Data too long for column 'dialplanscript' at row 1
The dialplanscript column length is 4096 but the dial plan is about 12k bytes.


Could you tell me how to fix this?


Thanks
Tony

Post Reply