/*
SQLyog Enterprise - MySQL GUI v6.15
MySQL - 5.1.22-rc-log : Database - rbr_test
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

create database if not exists `rbr_test`;

/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*Table structure for table `innodbtest` */

CREATE TABLE `innodbtest` (
  `IDB_INT` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `IDB_DTTM` datetime NOT NULL,
  `IDB_CHAR` char(30) NOT NULL,
  `IDB_VARCHAR` varchar(100) NOT NULL,
  `IDB_TEXT` text,
  PRIMARY KEY (`IDB_INT`)
) ENGINE=InnoDB AUTO_INCREMENT=14001 DEFAULT CHARSET=latin1;

/*Table structure for table `memorytest` */

CREATE TABLE `memorytest` (
  `MEM_INT` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `MEM_DTTM` datetime NOT NULL,
  `MEM_CHAR` char(30) NOT NULL,
  `MEM_VARCHAR` varchar(100) NOT NULL,
  PRIMARY KEY (`MEM_INT`)
) ENGINE=MEMORY AUTO_INCREMENT=2501 DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

/*Table structure for table `myisamtest` */

CREATE TABLE `myisamtest` (
  `MYI_INT` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `MYI_DTTM` datetime NOT NULL,
  `MYI_CHAR` char(30) NOT NULL,
  `MYI_VARCHAR` varchar(100) NOT NULL,
  `MYI_TEXT` text NOT NULL,
  PRIMARY KEY (`MYI_INT`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC;

/* Procedure structure for procedure `p_rbr_writetoalltables` */

DELIMITER $$

/*!50003 CREATE DEFINER=`deweyg`@`%` PROCEDURE `p_rbr_writetoalltables`()
    MODIFIES SQL DATA
    COMMENT 'writes to all tables when called by event scheduler'
BEGIN
Declare vNow Datetime Default Now();
Declare vChar Char(30) Default 'my name is sally';
Declare vVarChar VarChar(100) Default 'Who wants some of this';
Declare vText Text Default 'now is the time for all good men to come to the aid of their country';
Drop TEMPORARY TABLE IF EXISTS `ttLabelValue`;
CREATE TEMPORARY TABLE `ttLabelValue` (
        `ttlabel` CHAR(30),
        `ttvalue` CHAR(255)  )
        ENGINE = MEMORY;
Drop view if exists v3, v;
create view v3 as select 1 n union all select 1 union all select 1;
create view v as select 1 n from v3 a, v3 b union all select 1;
set @n = 0;
insert ttLabelValue select (now()-interval @n:=@n+1 second) lab, @N Val from v a,v b,v c, v d;  -- ,v e,v
-- Truncate Table memorytest;
insert into memorytest 
	( MEM_DTTM, MEM_CHAR, MEM_VARCHAR) Select 
	vNow, vChar, vVarChar from ttLabelValue Limit 500;
insert into myisamtest
	(MYI_DTTM, MYI_CHAR, MYI_VARCHAR, MYI_TEXT) values
	(vNow, vChar, vVarChar, vText);
insert into innodbtest 
	(IDB_DTTM, IDB_CHAR, IDB_VARCHAR, IDB_TEXT) Select 
	vNow, ttlabel, ttvalue, vText from ttLabelValue Limit 10000;
/*	code for the even which calls this proc every 1 minute is:
-- DELIMITER $$
Create EVENT WriteTablesEachMin
ON SCHEDULE EVERY 1 MINUTE
ON COMPLETION PRESERVE
DO 
Begin
call p_rbr_writetoalltables();
END$$
-- DELIMITER ;
*/
END */$$
DELIMITER ;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;