CREATE DATABASE `nododom_webappdb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci; USE `nododom_webappdb`; -- -------------------------------------------------------- -- -- Tabel structuur voor tabel `nodo_tbl_activities` -- CREATE TABLE `nodo_tbl_activities` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `name` text NOT NULL, `events` text NOT NULL, `sort_order` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=601 ; -- -------------------------------------------------------- -- -- Tabel structuur voor tabel `nodo_tbl_devices` -- CREATE TABLE `nodo_tbl_devices` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(11) unsigned NOT NULL, `naam` text NOT NULL, `label_on` tinytext NOT NULL, `label_off` tinytext NOT NULL, `collapsed` tinyint(1) NOT NULL, `type` tinyint(2) NOT NULL, `toggle` tinyint(1) NOT NULL, `dim` tinyint(2) NOT NULL, `homecode` tinytext NOT NULL COMMENT 'used for kaku', `address` tinyint(4) unsigned NOT NULL COMMENT 'used for kaku/newkaku/wiredout', `user_event_on` tinytext NOT NULL, `user_event_off` tinytext NOT NULL, `rawsignal_on` tinyint(3) unsigned NOT NULL, `rawsignal_off` tinyint(3) unsigned NOT NULL, `status` tinyint(1) NOT NULL COMMENT 'on/off', `dim_value` tinyint(2) NOT NULL, `sort_order` int(11) NOT NULL, `group_id` int(10) unsigned NOT NULL DEFAULT '0', `last_change` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1293 ; -- -------------------------------------------------------- -- -- Tabel structuur voor tabel `nodo_tbl_event_log` -- CREATE TABLE `nodo_tbl_event_log` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `user_id` mediumint(3) unsigned NOT NULL, `nodo_unit_nr` enum('0','1','2','3','4','5','6','7','8','9','10','11','12','13','14','15') NOT NULL, `event` tinytext NOT NULL, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=8477344 ; -- -------------------------------------------------------- -- -- Tabel structuur voor tabel `nodo_tbl_groups` -- CREATE TABLE `nodo_tbl_groups` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `name` tinytext NOT NULL, `devices` tinyint(1) DEFAULT '0', `devices_default` tinyint(1) unsigned DEFAULT '0', `activities` tinyint(1) DEFAULT '0', `activities_default` tinyint(1) unsigned DEFAULT '0', `values` tinyint(1) DEFAULT '0', `values_default` tinyint(1) unsigned DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=10193 ; -- -------------------------------------------------------- -- -- Tabel structuur voor tabel `nodo_tbl_nodo_forward` -- CREATE TABLE `nodo_tbl_nodo_forward` ( `user_id` int(10) unsigned NOT NULL, `forward_to` tinytext NOT NULL, `receive_from` tinytext NOT NULL, `accept_events` text NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Tabel structuur voor tabel `nodo_tbl_notifications` -- CREATE TABLE `nodo_tbl_notifications` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `name` tinytext NOT NULL, `event` tinytext NOT NULL, `recipient` tinytext NOT NULL, `subject` tinytext NOT NULL, `body` text NOT NULL, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=192 ; -- -------------------------------------------------------- -- -- Tabel structuur voor tabel `nodo_tbl_scripts` -- CREATE TABLE `nodo_tbl_scripts` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `file` tinytext NOT NULL, `user_id` int(11) NOT NULL, `script` text NOT NULL, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=197 ; -- -------------------------------------------------------- -- -- Tabel structuur voor tabel `nodo_tbl_sensor` -- CREATE TABLE `nodo_tbl_sensor` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `user_id` int(11) NOT NULL, `nodo_unit_nr` tinyint(4) NOT NULL, `par1` tinyint(4) NOT NULL, `sensor_type` tinyint(1) NOT NULL, `display` tinyint(1) unsigned NOT NULL COMMENT '1-value / 2-state', `collapsed` tinyint(1) unsigned NOT NULL DEFAULT '0', `input_output` tinyint(1) unsigned NOT NULL COMMENT '1 - input / 2 - output', `input_control` tinyint(1) unsigned NOT NULL COMMENT '1 - buttons / 2 - slider', `input_step` float NOT NULL, `input_min_val` float NOT NULL, `input_max_val` float NOT NULL, `formula` tinytext NOT NULL, `round` tinyint(1) unsigned NOT NULL DEFAULT '2', `sensor_prefix` tinytext NOT NULL, `sensor_suffix` tinytext NOT NULL, `sensor_suffix_true` tinytext NOT NULL, `sensor_suffix_false` tinytext NOT NULL, `graph_type` tinyint(1) NOT NULL DEFAULT '1', `graph_line_color` tinytext NOT NULL, `graph_hours` float NOT NULL, `data` float NOT NULL, `last_change` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'last value change', `sort_order` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=881 ; -- -------------------------------------------------------- -- -- Tabel structuur voor tabel `nodo_tbl_sensor_data` -- CREATE TABLE `nodo_tbl_sensor_data` ( `sensor_id` smallint(4) unsigned NOT NULL, `data` float NOT NULL, `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, KEY `sensor_id` (`sensor_id`,`timestamp`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -------------------------------------------------------- -- -- Tabel structuur voor tabel `nodo_tbl_users` -- CREATE TABLE `nodo_tbl_users` ( `id` int(5) NOT NULL AUTO_INCREMENT, `user_login_name` char(50) NOT NULL, `user_password` char(32) NOT NULL, `first_name` tinytext NOT NULL, `last_name` tinytext NOT NULL, `user_group` char(20) NOT NULL DEFAULT 'user', `send_method` tinyint(1) NOT NULL DEFAULT '2', `nodo_ip` tinytext NOT NULL, `nodo_port` int(11) NOT NULL DEFAULT '6636', `nodo_id` tinytext NOT NULL, `nodo_build` tinytext, `nodo_password` char(20) NOT NULL, `cookie` tinytext NOT NULL, `cookie_update` timestamp NULL DEFAULT NULL, `cookie_count` bigint(20) NOT NULL DEFAULT '0', `busy` tinyint(3) unsigned NOT NULL DEFAULT '0', `webapp_theme` tinytext NOT NULL, `webapp_theme_header` tinytext NOT NULL, `webapp_title` tinytext NOT NULL, `default_page` tinyint(1) NOT NULL DEFAULT '1', `active` tinyint(1) NOT NULL, `confirm_code` char(32) NOT NULL, `create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=355 ;