SpitFire Help Desk

Glossary | Contact | Favorites | Login

Search the Knowledgebase


[Advanced Search]

Browse by Category



SpitFire MySQL Database Schema

The following is a description of the MySQL database schema that is used in creation of the lists used in both the SpitFire X Pro and SpitFire Predictive Pro software.
This information could be useful for writing directly to a list using an outside, ODBC connection to the Dialer's MySQL Server.

Note: The MySQL engine will automatically insert the current timestamp value in the column since its default value is CURRENT_TIMESTAMP but EditTime will have to be populated using an explicit UPDATE query. Similarly, while inserting records in the client table make sure you update the DateEntered field with the current time stamp value by using an explicit UPDATE query.

//This table is no longer used by SPD or SPX
DROP TABLE IF EXISTS `attachments`;
CREATE TABLE  `attachments` (
  `AttachmentID` int(11) NOT NULL auto_increment,
  `OwnerID` int(11) NOT NULL default '0',
  `AttachType` smallint(6) default NULL,
  `AttachName` varchar(255) default NULL,
  `Attachment` longblob,
  `CreateTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `EditTime` timestamp NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`AttachmentID`),
  KEY `OwnerID` (`OwnerID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

//Stores information about call back records.
//This table would also be created in the Spitfire database as well.
//That one is used for callbacks that were set while running in Global Callback mode.
DROP TABLE IF EXISTS `callbacktable`;
CREATE TABLE  `callbacktable` (
  `CallBackID` int(11) NOT NULL auto_increment,
  `ListName` varchar(128) default NULL,
  `Clientid` int(11) NOT NULL default '0',
  `Name` varchar(50) default NULL,
  `VoicePhone` varchar(20) default NULL,
  `SecVoicePhone` varchar(20) default NULL,
  `DateEdited` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `DateEntered` timestamp NOT NULL default '0000-00-00 00:00:00',
  `CallBackTime` timestamp NOT NULL default '0000-00-00 00:00:00',
  `CallBackStatus` varchar(10) default '01',
  `DialerID` varchar(10) default '0',
  `FetchID` varchar(10) default '0',
  `BrokenFlag` varchar(10) default '0',
  `AgentID` varchar(10) default '0',
  `IsPrivateCallBack` varchar(10) default '0',
  PRIMARY KEY  (`CallBackID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

//Stores call history
DROP TABLE IF EXISTS `calls`;
CREATE TABLE  `calls` (
  `CallID` int(11) NOT NULL auto_increment,
  `ClientID` int(11) NOT NULL default '0',
  `agentID` int(11) default NULL,
  `Line` smallint(6) default NULL,
  `Contact` varchar(128) default NULL,
  `ContactType` smallint(6) default NULL,
  `StartTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `EndTime` timestamp NOT NULL default '0000-00-00 00:00:00',
  `LineTime` smallint(6) default NULL,
  `Result` smallint(6) default NULL,
  `Hit` char(3) default NULL,
  `Points` smallint(6) default NULL,
  `Questions` varchar(128) default NULL,
  `Dialer` varchar(255) default NULL,
  `Script` varchar(128) default NULL,
  `Message` varchar(128) default NULL,
  `MsgBody` longtext,
  `ConnectedTime` smallint(6) default NULL,
  `ConnectTime` timestamp NOT NULL default '0000-00-00 00:00:00',
  `xfrStartTime` timestamp NOT NULL default '0000-00-00 00:00:00',
  `xfrEndTime` timestamp NOT NULL default '0000-00-00 00:00:00',
  `xfrConnectTime` timestamp NOT NULL default '0000-00-00 00:00:00',
  `xfrConnectedTime` smallint(6) default NULL,
  `xfrStatus` smallint(6) default NULL,
  `xfrLineTime` smallint(6) default NULL,
  PRIMARY KEY  (`CallID`),
  KEY `ClientID` (`ClientID`),
  KEY `agentID` (`agentID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

//Primary contact table used by the dialer
DROP TABLE IF EXISTS `clients`;
CREATE TABLE  `clients` (
  `ClientID` int(11) NOT NULL auto_increment,
  `Status` smallint(6) default '0',
  `LastResult` varchar(20) default 'NONE',
  `Name` varchar(50) default NULL,
  `Company` varchar(255) default NULL,
  `Address` varchar(50) default NULL,
  `City` varchar(50) default NULL,
  `State` varchar(20) default NULL,
  `ZipCode` varchar(15) default NULL,
  `VoicePhone` varchar(20) default NULL,
  `SecVoicePhone` varchar(20) default NULL,
  `FaxPhone` varchar(20) default NULL,
  `EMail` varchar(60) default NULL,
  `WebSite` varchar(60) default NULL,
  `HomePhone` varchar(20) default NULL,
  `CellPhone` varchar(20) default NULL,
  `Pager` varchar(20) default NULL,
  `DialResult` varchar(60) default '01',
  `Hit` char(3) default 'No',
  `Points` smallint(6) default NULL,
  `Script` varchar(128) default NULL,
  `Message` varchar(128) default NULL,
  `NeedsEdit` char(3) default 'No',
  `MailBox` varchar(20) default NULL,
  `MailBoxPassword` varchar(20) default NULL,
  `MailBoxGreeting1` longblob,
  `MailBoxGreeting2` longblob,
  `SearchFlag` char(3) default NULL,
  `Group` varchar(20) default NULL,
  `Target` varchar(20) default NULL,
  `Spare1` varchar(128) default NULL,
  `Spare2` varchar(128) default NULL,
  `Spare3` varchar(128) default NULL,
  `Spare4` varchar(128) default NULL,
  `Spare5` varchar(128) default NULL,
  `Spare6` varchar(128) default NULL,
  `Spare7` varchar(128) default NULL,
  `Spare8` varchar(128) default NULL,
  `Spare9` varchar(128) default NULL,
  `Spare10` varchar(128) default NULL,
  `Spare11` varchar(128) default NULL,
  `Spare12` varchar(128) default NULL,
  `Spare13` varchar(128) default NULL,
  `Spare14` varchar(128) default NULL,
  `Spare15` varchar(128) default NULL,
  `Spare16` varchar(128) default NULL,
  `Country` varchar(20) default NULL,
  `SelectField` varchar(10) default NULL,
  `Note` longtext,
  `Msg1` longblob,
  `LastName` varchar(50) default NULL,
  `FirstName` varchar(50) default NULL,
  `CallCount` int(11) NOT NULL default '0',
  `DateEdited` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `DateEntered` timestamp NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`ClientID`),
  UNIQUE KEY `VoicePhone` (`VoicePhone`),
  UNIQUE KEY `FaxPhone` (`FaxPhone`),
  KEY `SecVoicePhone` (`SecVoicePhone`),
  KEY `Email` (`EMail`),
  KEY `HomePhone` (`HomePhone`),
  KEY `CellPhone` (`CellPhone`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

//Holds information about the company operating the dialer
DROP TABLE IF EXISTS `company`;
CREATE TABLE  `company` (
  `CompanyID` int(11) NOT NULL auto_increment,
  `Contact` varchar(120) default NULL,
  `Name` varchar(120) default NULL,
  `Address` varchar(120) default NULL,
  `City` varchar(50) default NULL,
  `State` varchar(25) default NULL,
  `Zipcode` varchar(25) default NULL,
  `VoicePhone` varchar(20) default NULL,
  `SecVoicePhone` varchar(20) default NULL,
  `FaxPhone` varchar(20) default NULL,
  `Email` varchar(120) default NULL,
  `WebSite` varchar(120) default NULL,
  `BR1` double default NULL,
  `BR2` double default NULL,
  `BR3` double default NULL,
  `BR4` double default NULL,
  `Terms` varchar(20) default NULL,
  `S1` varchar(120) default NULL,
  `S2` varchar(120) default NULL,
  `CreateTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `EditTime` timestamp NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`CompanyID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


//Stores user field mappings(i.e Spare Fields)
//If creating a new database, add the below 10 rows to the definitions table:

//DefinitionID DefinitionType OurFieldName UserFieldName
//1                 0                     spare1              spare1
//2                 0                     spare2              spare2
//3                 0                     spare3              spare3
//4                 0                     spare4              spare4
//5                 0                     spare5              spare5
//6                 0                     spare6              spare6
//7                 0                     spare7              spare7
//8                 0                     spare8              spare8
//9                 0                     spare9              spare9
//10               0                     spare10            spare10


DROP TABLE IF EXISTS `definitions`;
CREATE TABLE  `definitions` (
  `DefinitionID` int(11) NOT NULL auto_increment,
  `DefinitionType` smallint(6) default NULL,
  `OurFieldName` varchar(20) NOT NULL default '',
  `UserFieldName` varchar(20) default NULL,
  `CreateTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `EditTime` timestamp NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`DefinitionID`),
  KEY `OurFieldName` (`OurFieldName`)
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

//Stores information about dialer instances which are currently connected to this
//database
DROP TABLE IF EXISTS `log`;
CREATE TABLE  `log` (
  `LogID` int(11) NOT NULL auto_increment,
  `LogSession` smallint(6) default NULL,
  `LogTime` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `LogLine` smallint(6) default NULL,
  `LogType` smallint(6) default NULL,
  `LogText` varchar(120) default NULL,
  `LogAgent` varchar(50) default NULL,
  PRIMARY KEY  (`LogID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

//This table no longer used by SPD or SPX
DROP TABLE IF EXISTS `setup`;
CREATE TABLE  `setup` (
  `SetUpID` int(11) NOT NULL auto_increment,
  `VarNum` smallint(6) default NULL,
  `VarGroup` varchar(50) default NULL,
  `VarName` varchar(50) default NULL,
  `VarType` smallint(6) default NULL,
  `VarValue` varchar(255) default NULL,
  `VarUpdate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`SetUpID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

//Stores information used by the dialer during runtime
DROP TABLE IF EXISTS `stats`;
CREATE TABLE  `stats` (
  `minCID` int(11) NOT NULL default '0',
  `status` int(11) NOT NULL default '0',
  `availableRecords` int(11) NOT NULL default '0',
  PRIMARY KEY  (`minCID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

//Only used while importing data in the database
DROP TABLE IF EXISTS `temp`;
CREATE TABLE  `temp` (
  `ClientID` int(11) NOT NULL auto_increment,
  `Status` smallint(6) default NULL,
  `LastResult` varchar(20) default NULL,
  `Name` varchar(50) default NULL,
  `Company` varchar(255) default NULL,
  `Address` varchar(50) default NULL,
  `City` varchar(50) default NULL,
  `State` varchar(20) default NULL,
  `ZipCode` varchar(15) default NULL,
  `VoicePhone` varchar(20) default NULL,
  `SecVoicePhone` varchar(20) default NULL,
  `FaxPhone` varchar(20) default NULL,
  `EMail` varchar(60) default NULL,
  `WebSite` varchar(60) default NULL,
  `HomePhone` varchar(20) default NULL,
  `CellPhone` varchar(20) default NULL,
  `Pager` varchar(20) default NULL,
  `DialResult` varchar(60) default NULL,
  `Hit` char(3) default NULL,
  `Points` smallint(6) default NULL,
  `Script` varchar(128) default NULL,
  `Message` varchar(128) default NULL,
  `NeedsEdit` char(3) default NULL,
  `MailBox` varchar(20) default NULL,
  `MailBoxPassword` varchar(20) default NULL,
  `MailBoxGreeting1` longblob,
  `MailBoxGreeting2` longblob,
  `SearchFlag` char(3) default NULL,
  `Group` varchar(20) default NULL,
  `Target` varchar(20) default NULL,
  `Spare1` varchar(128) default NULL,
  `Spare2` varchar(128) default NULL,
  `Spare3` varchar(128) default NULL,
  `Spare4` varchar(128) default NULL,
  `Spare5` varchar(128) default NULL,
  `Spare6` varchar(128) default NULL,
  `Spare7` varchar(128) default NULL,
  `Spare8` varchar(128) default NULL,
  `Spare9` varchar(128) default NULL,
  `Spare10` varchar(128) default NULL,
  `Spare11` varchar(128) default NULL,
  `Spare12` varchar(128) default NULL,
  `Spare13` varchar(128) default NULL,
  `Spare14` varchar(128) default NULL,
  `Spare15` varchar(128) default NULL,
  `Spare16` varchar(128) default NULL,
  `Country` varchar(20) default NULL,
  `SelectField` varchar(10) default NULL,
  `Note` longtext,
  `Msg1` longblob,
  `LastName` varchar(50) default NULL,
  `FirstName` varchar(50) default NULL,
  `CallCount` int(11) NOT NULL default '0',
  `DateEntered` datetime default NULL,
  `DateEdited` datetime default NULL,
  PRIMARY KEY  (`ClientID`),
  KEY `VoicePhone` (`VoicePhone`),
  KEY `SecVoicePhone` (`SecVoicePhone`),
  KEY `Email` (`EMail`),
  KEY `HomePhone` (`HomePhone`),
  KEY `FaxPhone` (`FaxPhone`),
  KEY `CellPhone` (`CellPhone`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

//This table is no longer used by SPD or SPX
DROP TABLE IF EXISTS `todo`;
CREATE TABLE  `todo` (
  `ToDoID` int(11) NOT NULL auto_increment,
  `Status` smallint(6) default NULL,
  `Mode` smallint(6) default NULL,
  `Type` smallint(6) default NULL,
  `ClientID` int(11) NOT NULL default '0',
  `Name` varchar(50) default NULL,
  `Company` varchar(255) default NULL,
  `ScheduleDate` int(11) default NULL,
  `ScheduleTime` int(11) default NULL,
  `Regarding` varchar(128) default NULL,
  `Message` varchar(255) default NULL,
  `Disposition` varchar(128) default NULL,
  PRIMARY KEY  (`ToDoID`),
  KEY `ClientID` (`ClientID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Related Articles

Attachments

No attachments were found.

Visitor Comments

Article Details

Last Updated
28th of January, 2009

Would you like to...

Print this page  Print this page

Email this page  Email this page

Post a comment  Post a comment

 Subscribe me

Subscribe me  Add to favorites

Remove Highlighting Remove Highlighting

Edit this Article

Quick Edit

Export to PDF


User Opinions

No users have voted.

How would you rate this answer?



Thank you for rating this answer.

Continue

Powered by OPC Marketing, Inc. - © 2008 - 2011 OPC Marketing, Inc - Terms and Conditions