Database Structure for ConsoliBYTE PHP QuickBooks Framework

The user table stores authentication information for each Web Connector user/application.

-- 
-- Table structure for table 'quickbooks_user'
-- 

-- Stores usernames and passwords the Web Connector authenticates against

CREATE TABLE quickbooks_user (
  qb_username varchar(40) NOT NULL,                 // The username used by the Web Connector to authenticate
  qb_password varchar(40) NOT NULL,                 // The password used by the Web Connector to authenticate (stored in MD5, SHA1, plain-text, or SHA1-salted by QuickBooks_Utilities::createUser())
  qb_company_file varchar(255) default NULL,        // The QuickBooks company file path to connect to if QuickBooks is closed, i.e. C:/path/to/file.qbw
  qbwc_wait_before_next_update int(10) unsigned NOT NULL default '0',
  qbwc_min_run_every_n_seconds int(10) unsigned NOT NULL default '0',
  `status` char(1) NOT NULL,                        // The user's status, 'e' for enabled or 'd' for disabled
  write_datetime datetime NOT NULL,
  touch_datetime datetime NOT NULL,
  PRIMARY KEY  (qb_username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The ticket table stores login session data for every time the Web Connector logs in.

-- 
-- Table structure for table 'quickbooks_ticket'
-- 

-- Session ticket storage for Web Connector login sessions

CREATE TABLE quickbooks_ticket (
  quickbooks_ticket_id int(10) unsigned NOT NULL auto_increment,
  qb_username varchar(40) NOT NULL,                              // The user who connected
  ticket varchar(32) NOT NULL,                                   // The session/ticket value
  processed int(10) unsigned NOT NULL default '0',               // The number of requests processed
  lasterror_num varchar(16) NOT NULL default '0',                // The last error number which occurred
  lasterror_msg varchar(255) default NULL,                       // The last error message which occurred
  ipaddr varchar(15) NOT NULL,                                   // The IP address of the connecting machine
  write_datetime datetime NOT NULL,                              // When the ticket was created (when the login occurred)
  touch_datetime datetime NOT NULL,                              // When the ticket was last accessed
  PRIMARY KEY  (quickbooks_ticket_id),
  KEY qb_username (qb_username)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

Stores configuration information for integrated applications

-- 
-- Table structure for table 'quickbooks_config'
-- 

CREATE TABLE quickbooks_config (
  quickbooks_config_id int(10) unsigned NOT NULL AUTO_INCREMENT,
  qb_username varchar(40) NOT NULL,
  module varchar(40) NOT NULL,
  cfgkey varchar(40) NOT NULL,
  cfgval varchar(40) NOT NULL,
  cfgtype varchar(40) NOT NULL,
  cfgopts text NOT NULL,
  write_datetime datetime NOT NULL,
  mod_datetime datetime NOT NULL,
  PRIMARY KEY (quickbooks_config_id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Stores connection information for Intuit services (QuickBooks Online Edition, Merchant Services, and IPP/IDS)

-- 
-- Table structure for table 'quickbooks_connection'
--

CREATE TABLE quickbooks_connection (
  quickbooks_connection_id int(10) unsigned NOT NULL AUTO_INCREMENT,
  qb_username varchar(40) NOT NULL,
  certificate varchar(255) DEFAULT NULL,
  application_id int(10) unsigned NOT NULL,
  application_login varchar(40) DEFAULT NULL,
  lasterror_num varchar(32) DEFAULT NULL,
  lasterror_msg varchar(255) DEFAULT NULL,
  connection_ticket varchar(255) DEFAULT NULL,
  connection_datetime datetime NOT NULL,
  write_datetime datetime NOT NULL,
  touch_datetime datetime NOT NULL,
  PRIMARY KEY (quickbooks_connection_id)
)

Stores information about links between your applications primary keys and QuickBooks ListIDs and TxnIDs

-- 
-- Table structure for table 'quickbooks_ident'
-- 

CREATE TABLE quickbooks_ident (
  quickbooks_ident_id int(10) unsigned NOT NULL AUTO_INCREMENT,
  qb_username varchar(40) NOT NULL,                // The username this record has been stored for
  qb_object varchar(40) NOT NULL,                  // The type of QuickBooks object (i.e. Invoice, or SalesReceipt, or ItemInventory, etc.)
  unique_id varchar(40) NOT NULL,                  // The primary key from your application
  qb_ident varchar(40) NOT NULL,                   // The QuickBooks ListID or TxnID value
  editsequence varchar(40) NOT NULL,               // The QuickBooks EditSequence value
  extra text,
  map_datetime datetime NOT NULL,                  // When this association was created
  PRIMARY KEY (quickbooks_ident_id)
) 

Stores log messages

-- 
-- Table structure for table 'quickbooks_log'
--

CREATE TABLE quickbooks_log (
  quickbooks_log_id int(10) unsigned NOT NULL AUTO_INCREMENT,
  quickbooks_ticket_id int(10) unsigned DEFAULT NULL,           // The ticket ID value this log message was created during
  batch int(10) unsigned NOT NULL,                              // 
  msg text NOT NULL,                                            // The log message
  log_datetime datetime NOT NULL,                               // When the log message was created
  PRIMARY KEY (quickbooks_log_id),
  KEY quickbooks_ticket_id (quickbooks_ticket_id),
  KEY batch (batch)
)

Currently unused- in place for future use by SQL Mirror

-- 
-- Table structure for table 'quickbooks_notify'
--

CREATE TABLE quickbooks_notify (
  quickbooks_notify_id int(10) unsigned NOT NULL AUTO_INCREMENT,
  qb_username varchar(40) NOT NULL,
  qb_object varchar(40) NOT NULL,
  unique_id varchar(40) NOT NULL,
  qb_ident varchar(40) NOT NULL,
  errnum int(10) unsigned DEFAULT NULL,
  errmsg text NOT NULL,
  note text NOT NULL,
  priority int(10) unsigned NOT NULL,
  write_datetime datetime NOT NULL,
  mod_datetime datetime NOT NULL,
  PRIMARY KEY (quickbooks_notify_id)
) 

Stores queue records for things that need to be processed by the Web Connector

-- 
-- Table structure for table 'quickbooks_queue'
--

CREATE TABLE quickbooks_queue (
  quickbooks_queue_id int(10) unsigned NOT NULL AUTO_INCREMENT,
  quickbooks_ticket_id int(10) unsigned DEFAULT NULL,           // The ticket this queue item was processed in
  qb_username varchar(40) NOT NULL,                             // The username this queued item is for
  qb_action varchar(32) NOT NULL,                               // The action to perform (i.e. AccountAdd, or InvoiceQuery, or CustomerMod, etc.)
  ident varchar(40) NOT NULL,                                   // Your primary key of the record (i.e. your Account ID#, or your Customer ID#, etc.)
  extra text,                                                   // A serialized representation of any extra data you stored in the queue
  qbxml text,                                                   // Pre-built qbXML requests get stored here. This is used mostly for internal use. If you're using it, you're probably doing something wrong (or have a very, very specialized application which requires it's use). 
  priority int(10) unsigned DEFAULT '0',                        // The priority of the queued action (higher priorities get processed first)
  qb_status char(1) NOT NULL,                                   // The status of the queued item (see the documentation for queue status values)
  msg text,                                                     // If an error occurs, this will hold the error code and error message
  enqueue_datetime datetime NOT NULL,                           // When this item was added to the queue
  dequeue_datetime datetime DEFAULT NULL,                       // When this item was removed from the queue
  PRIMARY KEY (quickbooks_queue_id),
  KEY quickbooks_ticket_id (quickbooks_ticket_id),
  KEY priority (priority),
  KEY qb_username (qb_username,qb_action,ident,qb_status),
  KEY qb_status (qb_status)
)

CREATE TABLE quickbooks_recur (
  quickbooks_recur_id int(10) unsigned NOT NULL AUTO_INCREMENT,
  qb_username varchar(40) NOT NULL,
  qb_action varchar(32) NOT NULL,
  ident varchar(40) NOT NULL,
  extra text,
  qbxml text,
  priority int(10) unsigned DEFAULT '0',
  run_every int(10) unsigned NOT NULL,
  recur_lasttime int(10) unsigned NOT NULL,
  enqueue_datetime datetime NOT NULL,
  PRIMARY KEY (quickbooks_recur_id),
  KEY qb_username (qb_username,qb_action,ident),
  KEY priority (priority)
)