-- phpMyAdmin SQL Dump
-- version 3.5.2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Sep 22, 2012 at 08:46 AM
-- Server version: 5.5.25a
-- PHP Version: 5.4.4

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `beehive`
--

-- --------------------------------------------------------

--
-- Table structure for table `command_ref_item`
--
DROP TABLE IF EXISTS `state`;
DROP TABLE IF EXISTS `range_sensor`;
DROP TABLE IF EXISTS `sensor_ref_item`;
DROP TABLE IF EXISTS `command_ref_item`;
DROP TABLE IF EXISTS `protocol_attr`;
DROP TABLE IF EXISTS `device_attr`;
DROP TABLE IF EXISTS `device_macro_item`;
DROP TABLE IF EXISTS `device_macro`;
DROP TABLE IF EXISTS `device_command`;
DROP TABLE IF EXISTS `protocol`;
DROP TABLE IF EXISTS `sensor`;
DROP TABLE IF EXISTS `switch`;
DROP TABLE IF EXISTS `slider`;
DROP TABLE IF EXISTS `device`;





CREATE TABLE IF NOT EXISTS `command_ref_item` (
  `type` varchar(31) COLLATE utf8_bin NOT NULL,
  `oid` bigint(20) NOT NULL AUTO_INCREMENT,
  `target_device_command_oid` bigint(20) DEFAULT NULL,
  `off_switch_oid` bigint(20) DEFAULT NULL,
  `slider_oid` bigint(20) DEFAULT NULL,
  `on_switch_oid` bigint(20) DEFAULT NULL,
  `sensor_oid` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`oid`),
  KEY `FK160435B3EAC16F1D` (`sensor_oid`),
  KEY `FK160435B36B091F71` (`on_switch_oid`),
  KEY `FK160435B3124D9292` (`target_device_command_oid`),
  KEY `FK160435B3BA0B25E1` (`off_switch_oid`),
  KEY `FK160435B3C70CCDEB` (`slider_oid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=15 ;


-- --------------------------------------------------------

--
-- Table structure for table `device`
--

CREATE TABLE IF NOT EXISTS `device` (
  `oid` bigint(20) NOT NULL AUTO_INCREMENT,
  `model` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_bin NOT NULL,
  `vendor` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `account_oid` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`oid`),
  KEY `FKB06B1E5689CBAF07` (`account_oid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=3 ;

-- --------------------------------------------------------

--
-- Table structure for table `device_command`
--

CREATE TABLE IF NOT EXISTS `device_command` (
  `oid` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_bin NOT NULL,
  `sectionId` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `protocol_oid` bigint(20) NOT NULL,
  `device_oid` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`oid`),
  KEY `FKAE9FEE6299B85655` (`device_oid`),
  KEY `FKAE9FEE62BCA9D659` (`protocol_oid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=5 ;

-- --------------------------------------------------------

--
-- Table structure for table `protocol`
--

CREATE TABLE IF NOT EXISTS `protocol` (
  `oid` bigint(20) NOT NULL AUTO_INCREMENT,
  `type` varchar(255) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`oid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=5 ;


-- --------------------------------------------------------

--
-- Table structure for table `protocol_attr`
--

CREATE TABLE IF NOT EXISTS `protocol_attr` (
  `oid` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_bin NOT NULL,
  `value` text COLLATE utf8_bin NOT NULL,
  `protocol_oid` bigint(20) NOT NULL,
  PRIMARY KEY (`oid`),
  KEY `FK2A276338BCA9D659` (`protocol_oid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=13 ;

--
-- Dumping data for table `protocol_attr`
--

-- INSERT INTO `protocol_attr` (`oid`, `name`, `value`, `protocol_oid`) VALUES
-- (1, 'port', 0x39383736, 1),	
-- (2, 'command', 0x3337203031203031203031203032203030203030203030203030203030, 1),
-- (3, 'ipAddress', 0x3139322e3136382e302e323534, 1),
-- (4, 'port', 0x39383736, 2),
-- (5, 'command', 0x3337203031203031203031203031203030203030203030203030203030, 2),
-- (6, 'ipAddress', 0x3139322e3136382e302e323534, 2),
-- (7, 'port', 0x39383736, 3),
-- (8, 'command', 0x3337203031203031203031203032203030203030203030203030203030, 3),
-- (9, 'ipAddress', 0x3139322e3136382e302e323534, 3),
-- (10, 'port', 0x39383736, 4),
-- (11, 'command', 0x3337203031203031203031203030203030203030203030203030203030, 4),
-- (12, 'ipAddress', 0x3139322e3136382e302e323534, 4);

-- --------------------------------------------------------

--
-- Table structure for table `range_sensor`
--

CREATE TABLE IF NOT EXISTS `range_sensor` (
  `max_value` int(11) DEFAULT NULL,
  `min_value` int(11) DEFAULT NULL,
  `oid` bigint(20) NOT NULL,
  PRIMARY KEY (`oid`),
  KEY `FKDE21A8FC8C6C4043` (`oid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


-- --------------------------------------------------------

--
-- Table structure for table `sensor`
--

CREATE TABLE IF NOT EXISTS `sensor` (
  `dtype` varchar(31) COLLATE utf8_bin NOT NULL,
  `oid` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_bin NOT NULL,
  `type` int(11) DEFAULT NULL,
  `account_oid` bigint(20) DEFAULT NULL,
  `device_oid` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`oid`),
  KEY `FKCA0053BA99B85655` (`device_oid`),
  KEY `FKCA0053BA89CBAF07` (`account_oid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=6 ;


-- --------------------------------------------------------

--
-- Table structure for table `sensor_ref_item`
--

CREATE TABLE IF NOT EXISTS `sensor_ref_item` (
  `type` varchar(31) COLLATE utf8_bin NOT NULL,
  `oid` bigint(20) NOT NULL AUTO_INCREMENT,
  `slider_oid` bigint(20) DEFAULT NULL,
  `switch_oid` bigint(20) DEFAULT NULL,
  `target_sensor_oid` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`oid`),
  KEY `FK9D044E24AAFF5E8B` (`target_sensor_oid`),
  KEY `FK9D044E247105E911` (`switch_oid`),
  KEY `FK9D044E24C70CCDEB` (`slider_oid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=6 ;


-- --------------------------------------------------------

--
-- Table structure for table `slider`
--

CREATE TABLE IF NOT EXISTS `slider` (
  `oid` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `device_oid` bigint(20) DEFAULT NULL,
  `account_oid` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`oid`),
  KEY `FKCA6078E189CBAF07` (`account_oid`),
  KEY `FKCA6078E199B85655` (`device_oid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=2 ;


-- --------------------------------------------------------

--
-- Table structure for table `state`
--

CREATE TABLE IF NOT EXISTS `state` (
  `oid` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `value` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `sensor_oid` bigint(20) NOT NULL,
  PRIMARY KEY (`oid`),
  KEY `FK68AC49131441F6E` (`sensor_oid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `switch`
--

CREATE TABLE IF NOT EXISTS `switch` (
  `oid` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `account_oid` bigint(20) DEFAULT NULL,
  `device_oid` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`oid`),
  KEY `FKCAFBB73499B85655` (`device_oid`),
  KEY `FKCAFBB73489CBAF07` (`account_oid`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=5 ;

CREATE TABLE `device_attr` (
  `oid` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `value` varchar(255) NOT NULL,
  `device_oid` bigint(20) NOT NULL,
  PRIMARY KEY (`oid`),
  KEY `FKA400FA3A99B85655` (`device_oid`),
  CONSTRAINT `FKA400FA3A99B85655` FOREIGN KEY (`device_oid`) REFERENCES `device` (`oid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `command_ref_item`
--
ALTER TABLE `command_ref_item`
  ADD CONSTRAINT `FK160435B3C70CCDEB` FOREIGN KEY (`slider_oid`) REFERENCES `slider` (`oid`),
  ADD CONSTRAINT `FK160435B3124D9292` FOREIGN KEY (`target_device_command_oid`) REFERENCES `device_command` (`oid`),
  ADD CONSTRAINT `FK160435B36B091F71` FOREIGN KEY (`on_switch_oid`) REFERENCES `switch` (`oid`),
  ADD CONSTRAINT `FK160435B3BA0B25E1` FOREIGN KEY (`off_switch_oid`) REFERENCES `switch` (`oid`),
  ADD CONSTRAINT `FK160435B3EAC16F1D` FOREIGN KEY (`sensor_oid`) REFERENCES `sensor` (`oid`);

--
-- Constraints for table `device`
--
ALTER TABLE `device`
  ADD CONSTRAINT `FKB06B1E5689CBAF07` FOREIGN KEY (`account_oid`) REFERENCES `account` (`oid`);

--
-- Constraints for table `device_command`
--
ALTER TABLE `device_command`
  ADD CONSTRAINT `FKAE9FEE62BCA9D659` FOREIGN KEY (`protocol_oid`) REFERENCES `protocol` (`oid`),
  ADD CONSTRAINT `FKAE9FEE6299B85655` FOREIGN KEY (`device_oid`) REFERENCES `device` (`oid`);

--
-- Constraints for table `protocol_attr`
--
ALTER TABLE `protocol_attr`
  ADD CONSTRAINT `FK2A276338BCA9D659` FOREIGN KEY (`protocol_oid`) REFERENCES `protocol` (`oid`);

--
-- Constraints for table `range_sensor`
--
ALTER TABLE `range_sensor`
  ADD CONSTRAINT `FKDE21A8FC8C6C4043` FOREIGN KEY (`oid`) REFERENCES `sensor` (`oid`);

--
-- Constraints for table `sensor`
--
ALTER TABLE `sensor`
  ADD CONSTRAINT `FKCA0053BA89CBAF07` FOREIGN KEY (`account_oid`) REFERENCES `account` (`oid`),
  ADD CONSTRAINT `FKCA0053BA99B85655` FOREIGN KEY (`device_oid`) REFERENCES `device` (`oid`);

--
-- Constraints for table `sensor_ref_item`
--
ALTER TABLE `sensor_ref_item`
  ADD CONSTRAINT `FK9D044E24C70CCDEB` FOREIGN KEY (`slider_oid`) REFERENCES `slider` (`oid`),
  ADD CONSTRAINT `FK9D044E247105E911` FOREIGN KEY (`switch_oid`) REFERENCES `switch` (`oid`),
  ADD CONSTRAINT `FK9D044E24AAFF5E8B` FOREIGN KEY (`target_sensor_oid`) REFERENCES `sensor` (`oid`);

--
-- Constraints for table `slider`
--
ALTER TABLE `slider`
  ADD CONSTRAINT `FKCA6078E199B85655` FOREIGN KEY (`device_oid`) REFERENCES `device` (`oid`),
  ADD CONSTRAINT `FKCA6078E189CBAF07` FOREIGN KEY (`account_oid`) REFERENCES `account` (`oid`);

--
-- Constraints for table `state`
--
ALTER TABLE `state`
  ADD CONSTRAINT `FK68AC49131441F6E` FOREIGN KEY (`sensor_oid`) REFERENCES `sensor` (`oid`);

--
-- Constraints for table `switch`
--
ALTER TABLE `switch`
  ADD CONSTRAINT `FKCAFBB73489CBAF07` FOREIGN KEY (`account_oid`) REFERENCES `account` (`oid`),
  ADD CONSTRAINT `FKCAFBB73499B85655` FOREIGN KEY (`device_oid`) REFERENCES `device` (`oid`);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
