Ported / fixed most old tests

1 files changed, 221 insertions, 0 deletions

diff --git a/tests/initdb.sql b/tests/initdb.sql
new file mode 100644
index 00000000..ccd99683
--- /dev/null
+++ b/tests/initdb.sql
@@ -0,0 +1,221 @@
+DROP DATABASE `prado_unitest`;
+CREATE DATABASE `prado_unitest`;
+GRANT ALL ON `prado_unitest`.* TO prado_unitest@localhost identified by 'prado_unitest';
+FLUSH PRIVILEGES;
+
+USE `prado_unitest`;
+
+DROP TABLE IF EXISTS `departments`;
+CREATE TABLE `departments` (
+  `department_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
+  `name` VARCHAR(255) NOT NULL,
+  `description` TEXT NULL,
+  `active` TINYINT(1) NOT NULL DEFAULT 0,
+  `order` SMALLINT(3) NOT NULL DEFAULT 0,
+  PRIMARY KEY (`department_id`)
+)
+AUTO_INCREMENT=1
+ENGINE = INNODB
+CHARACTER SET utf8 COLLATE utf8_general_ci;
+
+INSERT INTO `departments` (`department_id`, `name`, `description`, `active`, `order`) VALUES
+(1, 'Facilities', NULL, 0, 1),
+(2, 'Marketing', NULL, 1, 2),
+(3, 'Sales', NULL, 0, 3),
+(4, 'Human resources', NULL, 1, 4),
+(5, '+GX Service', NULL, 1, 5),
+(6, 'Services', NULL, 1, 6),
+(7, 'Logistics', NULL, 1, 7),
+(8, 'Research and Development', NULL, 1, 8);
+
+DROP TABLE IF EXISTS `department_sections`;
+CREATE TABLE `department_sections` (
+  `department_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
+  `section_id` BIGINT UNSIGNED NOT NULL,
+  `order` SMALLINT(3) NOT NULL DEFAULT 0,
+  PRIMARY KEY (`department_id`, `section_id`)
+)
+AUTO_INCREMENT=1
+ENGINE = INNODB
+CHARACTER SET utf8 COLLATE utf8_general_ci;
+
+INSERT INTO `department_sections` (`department_id`, `section_id`, `order`) VALUES
+(1, 1, 1),
+(1, 2, 2),
+(2, 3, 3),
+(2, 4, 4),
+(2, 5, 5);
+
+DROP TABLE IF EXISTS `simple_users`;
+CREATE TABLE `simple_users` (
+  `username` VARCHAR(255) NOT NULL,
+  PRIMARY KEY (`username`)
+)
+AUTO_INCREMENT=1
+ENGINE = INNODB
+CHARACTER SET utf8 COLLATE utf8_general_ci;
+
+INSERT INTO `simple_users` VALUES
+('tom'),
+('matt'),
+('greg'),
+('mickey'),
+('brad'),
+('zach'),
+('ian'),
+('lola'),
+('david'),
+('sam');
+
+DROP TABLE IF EXISTS `blogs`;
+CREATE TABLE `blogs` (
+  `blog_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
+  `blog_name` VARCHAR(255) NOT NULL,
+  `blog_author` VARCHAR(255) NOT NULL,
+  PRIMARY KEY (`blog_id`)
+)
+AUTO_INCREMENT=1
+ENGINE = INNODB
+CHARACTER SET utf8 COLLATE utf8_general_ci;
+
+INSERT INTO blogs (blog_id, blog_name, blog_author) VALUES
+(1, 'personal blog', 'personal blog');
+
+DROP TABLE IF EXISTS `baserecordtest`;
+CREATE TABLE `baserecordtest` (
+  `baserecordtest_id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`baserecordtest_id`)
+)
+AUTO_INCREMENT=1
+ENGINE = INNODB
+CHARACTER SET utf8 COLLATE utf8_general_ci;
+
+DROP TABLE IF EXISTS `address`;
+CREATE TABLE `address` (
+  `username` VARCHAR(255) NOT NULL,
+  `phone` VARCHAR(255) NOT NULL,
+  `field1_boolean` TINYINT(1) NOT NULL,
+  `field2_date` DATE NOT NULL,
+  `field3_double` DOUBLE NOT NULL,
+  `field4_integer` INT(10) NOT NULL,
+  `field5_text` TEXT NOT NULL,
+  `field6_time` TIME NOT NULL,
+  `field7_timestamp` TIMESTAMP NOT NULL,
+  `field8_money` DECIMAL(19,4) NOT NULL,
+  `field9_numeric` NUMERIC NOT NULL,
+  `int_fk1` INT(10) NOT NULL,
+  `int_fk2` INT(10) NOT NULL,
+  PRIMARY KEY (`username`)
+)
+AUTO_INCREMENT=1
+ENGINE = INNODB
+CHARACTER SET utf8 COLLATE utf8_general_ci;
+
+INSERT INTO address (username, phone) VALUES
+('wei', '1111111'),
+('fabio', '2222222');
+
+DROP TABLE IF EXISTS `Accounts`;
+CREATE TABLE `Accounts` 
+( 
+  Account_Id INTEGER NOT NULL PRIMARY KEY, 
+  Account_FirstName VARCHAR(32) NOT NULL, 
+  Account_LastName VARCHAR(32) NOT NULL, 
+  Account_Email VARCHAR(128), 
+  Account_Banner_Option VARCHAR(255), 
+  Account_Cart_Option INT 
+);
+
+INSERT INTO Accounts VALUES(1,'Joe', 'Dalton', 'Joe.Dalton@somewhere.com', 'Oui', 200);
+INSERT INTO Accounts VALUES(2,'Averel', 'Dalton', 'Averel.Dalton@somewhere.com', 'Oui', 200);
+INSERT INTO Accounts VALUES(3,'William', 'Dalton', null, 'Non', 100);
+INSERT INTO Accounts VALUES(4,'Jack', 'Dalton', 'Jack.Dalton@somewhere.com', 'Non', 100);
+INSERT INTO Accounts VALUES(5,'Gilles', 'Bayon', null, 'Oui', 100);
+
+DROP TABLE IF EXISTS `Users`;
+CREATE TABLE `Users` (
+  `username` varchar(40) NOT NULL,
+  `password` varchar(40) default NULL,
+  `email` varchar(40) default NULL,
+  `first_name` varchar(40) default NULL,
+  `last_name` varchar(40) default NULL,
+  `job_title` varchar(40) default NULL,
+  `work_phone` varchar(40) default NULL,
+  `work_fax` varchar(40) default NULL,
+  `active` tinyint(1) default 1,
+  `department_id` BIGINT UNSIGNED NULL,
+  `salutation` varchar(40) default NULL,
+  `hint_question` varchar(40) default NULL,
+  `hint_answer` varchar(40) default NULL,
+  PRIMARY KEY  (`username`)
+) ENGINE=InnoDB DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
+
+INSERT INTO Users VALUES('admin', '123456', 'Joe.Dalton@somewhere.com', 'Joe', 'Dalton', 'Ceo', '+1 234 567890', '+1 234 567890', 1, 1, 'Dear', 'fav color', 'red');
+
+DROP TABLE IF EXISTS `dynamicparametertest1`;
+CREATE TABLE `dynamicparametertest1` (
+  `testname` varchar(50) NOT NULL,
+  `teststring` varchar(50) NOT NULL,
+  `testinteger` int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+DROP TABLE IF EXISTS `dynamicparametertest2`;
+CREATE TABLE `dynamicparametertest2` (
+  `testname` varchar(50) NOT NULL,
+  `teststring` varchar(50) NOT NULL,
+  `testinteger` int(11) NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+INSERT INTO `dynamicparametertest1` (
+  `testname` ,
+  `teststring` ,
+  `testinteger`
+)
+VALUES
+('staticsql', 'staticsql1', '1'),
+('dynamictable', 'dynamictableparametertest1', '1')
+;
+
+INSERT INTO `dynamicparametertest2` (
+  `testname` ,
+  `teststring` ,
+  `testinteger`
+)
+VALUES
+('staticsql', 'staticsql2', '2'),
+('dynamictable', 'dynamictableparametertest2', '2')
+;
+
+DROP TABLE IF EXISTS `teams`;
+CREATE TABLE `teams` (
+  `name` varchar(50) NOT NULL,
+  `location` varchar(50) NOT NULL,
+  PRIMARY KEY  (`name`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+DROP TABLE IF EXISTS `players`;
+CREATE TABLE `players` (
+  `player_id` bigint(10) NOT NULL AUTO_INCREMENT,
+  `age` SMALLINT(3) NOT NULL,
+  `team_name` varchar(50) NOT NULL,
+  `team` varchar(50) NOT NULL,
+  `skills` bigint(10) NOT NULL,
+  `profile` bigint(10) NOT NULL,
+  PRIMARY KEY  (`player_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+DROP TABLE IF EXISTS `profiles`;
+CREATE TABLE `profiles` (
+  `profile_id` bigint(10) NOT NULL AUTO_INCREMENT,
+  `salary` SMALLINT(3) NOT NULL,
+  `player` bigint(10) NOT NULL ,
+  PRIMARY KEY  (`profile_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+DROP TABLE IF EXISTS `skills`;
+CREATE TABLE `skills` (
+  `skill_id` bigint(10) NOT NULL AUTO_INCREMENT,
+  `name` varchar(50) NOT NULL,
+  PRIMARY KEY  (`skill_id`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+