From 70e305a918fcf193009831d4e89aa8c386df3dc4 Mon Sep 17 00:00:00 2001 From: Fabio Bas Date: Tue, 29 Mar 2016 09:24:08 +0200 Subject: Ported / fixed most old tests --- tests/initdb.sql | 221 +++++++++++++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 221 insertions(+) create mode 100644 tests/initdb.sql (limited to 'tests/initdb.sql') 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; + -- cgit v1.2.3