05 Jul

Generate random test data for MySQL using routines

At times you’ll find yourself responsible for generating test data for newly created tables for testing or sampling purpose. There are tools that will generate random data for you but they’re not free. At-times you’ll write scripts to generate data but those will be table specific.

I hate generating dummy data, yes I do and I assume you do too! I think that’s the major reason I wrote these MySQL functions and procedures for Generating dummy test data.

People can manage these things with a simple perl / shell script with loops but again that always need your time.
Why can’t MySQL generate data for it’s own table when MySQL better knows the table than anyone else !! :)

Below are a set of functions and a stored procedure that will make our life easy and of-course it’s free ;).

Download the sql code: Generate dummy data for MySQL.

How to install and generate dummy data:
mysql -uUSER -pPASSWORD DATABASE < populate_dummy_data.txt

How use installed functions to generate test data:
- To generate test data of 1000 rows for sakila.film table execute following sql command:
call populate('sakila','film',1000,'N');

MySQL set of functions to get random values generated for individual data-types.

## MySQL function to generate random string of specified length
DROP function if exists get_string;
delimiter $$
CREATE FUNCTION get_string(in_strlen int) RETURNS VARCHAR(500) DETERMINISTIC
BEGIN
set @var:='';
while(in_strlen>0) do
set @var:=concat(@var,IFNULL(ELT(1+FLOOR(RAND() * 53), 'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z',' ','A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z'),'Kedar'));
set in_strlen:=in_strlen-1;
end while;
RETURN @var;
END $$
delimiter ;

## MySQL function to generate random Enum-ID from specified enum definition
DELIMITER $$
DROP FUNCTION IF EXISTS get_enum $$
CREATE FUNCTION get_enum(col_type varchar(100)) RETURNS VARCHAR(100) DETERMINISTIC
RETURN if((@var:=ceil(rand()*10)) > (length(col_type)-length(replace(col_type,',',''))+1),(length(col_type)-length(replace(col_type,',',''))+1),@var);
$$
DELIMITER ;

## MySQL function to generate random float value from specified precision and scale.
DELIMITER $$
DROP FUNCTION IF EXISTS get_float $$
CREATE FUNCTION get_float(in_precision int, in_scale int) RETURNS VARCHAR(100) DETERMINISTIC
RETURN round(rand()*pow(10,(in_precision-in_scale)),in_scale)
$$
DELIMITER ;

## MySQL function to generate random date (of year 2012).
DELIMITER $$
DROP FUNCTION IF EXISTS get_date $$
CREATE FUNCTION get_date() RETURNS VARCHAR(10) DETERMINISTIC
RETURN DATE(FROM_UNIXTIME(RAND() * (1356892200 - 1325356200) + 1325356200))
$$
DELIMITER ;

## MySQL function to generate random time.
DELIMITER $$
DROP FUNCTION IF EXISTS get_time $$
CREATE FUNCTION get_time() RETURNS INTEGER DETERMINISTIC
RETURN TIME(FROM_UNIXTIME(RAND() * (1356892200 - 1325356200) + 1325356200))
$$
DELIMITER ;

## MySQL function to generate random int.
DELIMITER $$
DROP FUNCTION IF EXISTS get_int $$
CREATE FUNCTION get_int() RETURNS INTEGER DETERMINISTIC
RETURN floor(rand()*10000000)
$$
DELIMITER ;

## MySQL function to generate random tinyint.
DELIMITER $$
DROP FUNCTION IF EXISTS get_tinyint $$
CREATE FUNCTION get_tinyint() RETURNS INTEGER DETERMINISTIC
RETURN floor(rand()*100)
$$
DELIMITER ;

## MySQL function to generate random varchar column of specified length(alpha-numeric string).
DELIMITER $$
DROP FUNCTION IF EXISTS get_varchar $$
CREATE FUNCTION get_varchar(in_length int) RETURNS VARCHAR(500) DETERMINISTIC
RETURN SUBSTRING(MD5(RAND()) FROM 1 FOR in_length)
$$
DELIMITER ;

## MySQL function to generate random datetime value (any datetime of year 2012).
DELIMITER $$
DROP FUNCTION IF EXISTS get_datetime $$
CREATE FUNCTION get_datetime() RETURNS VARCHAR(20) DETERMINISTIC
RETURN FROM_UNIXTIME(RAND() * (1356892200 - 1325356200) + 1325356200)
$$
DELIMITER ;

The MySQL Stored procedure that populates MySQL tables with dummy data:

DELIMITER $$
DROP PROCEDURE IF EXISTS populate $$
CREATE PROCEDURE populate(in_db varchar(20), in_table varchar(20), in_rows int, in_debug char(1))
BEGIN
/*
|
| Developer: Kedar Vaijanapurkar
| USAGE: call populate('DATABASE-NAME','TABLE-NAME',NUMBER-OF-ROWS,DEBUG-MODE);
| EXAMPLE: call populate('sakila','film',100,'N');
| Debug-mode will print an SQL that's executed and iterated.
|
*/

DECLARE col_name VARCHAR(100);
DECLARE col_type VARCHAR(100);
DECLARE col_datatype VARCHAR(100);
DECLARE col_maxlen VARCHAR(100);
DECLARE col_extra VARCHAR(100);
DECLARE col_num_precision VARCHAR(100);
DECLARE col_num_scale VARCHAR(100);
DECLARE func_query VARCHAR(1000);
DECLARE i INT;

DECLARE done INT DEFAULT 0;
DECLARE cur_datatype cursor FOR
SELECT column_name,COLUMN_TYPE,data_type,CHARACTER_MAXIMUM_LENGTH,EXTRA,NUMERIC_PRECISION,NUMERIC_SCALE FROM information_schema.columns WHERE table_name=in_table AND table_schema=in_db;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET func_query='';

OPEN cur_datatype;
datatype_loop: loop
FETCH cur_datatype INTO col_name, col_type, col_datatype, col_maxlen, col_extra, col_num_precision, col_num_scale;
#SELECT CONCAT(col_name,"-", col_type,"-", col_datatype,"-", IFNULL(col_maxlen,'NULL'),"-", IFNULL(col_extra,'NULL')) AS VALS;

IF (done = 1) THEN
leave datatype_loop;
END IF;

CASE
WHEN col_extra='auto_increment' THEN SET func_query=concat(func_query,'NULL, ');
WHEN col_datatype in ('int','bigint') THEN SET func_query=concat(func_query,'get_int(), ');
WHEN col_datatype in ('varchar','char') THEN SET func_query=concat(func_query,'get_string(',ifnull(col_maxlen,0),'), ');
WHEN col_datatype in ('tinyint', 'smallint','year') or col_datatype='mediumint' THEN SET func_query=concat(func_query,'get_tinyint(), ');
WHEN col_datatype in ('datetime','timestamp') THEN SET func_query=concat(func_query,'get_datetime(), ');
WHEN col_datatype in ('float', 'decimal') THEN SET func_query=concat(func_query,'get_float(',col_num_precision,',',col_num_scale,'), ');
WHEN col_datatype in ('enum','set') THEN SET func_query=concat(func_query,'get_enum("',col_type,'"), ');
ELSE SET func_query=concat(func_query,'get_varchar(',ifnull(col_maxlen,0),'), ');
END CASE;
end loop datatype_loop;
close cur_datatype;

SET func_query=trim(trailing ', ' FROM func_query);
SET @func_query=concat("INSERT INTO ", in_db,".",in_table," VALUES (",func_query,");");
IF in_debug='Y' THEN
select @func_query;
END IF;
SET i=in_rows;
populate :loop
WHILE (i>0) DO
PREPARE t_stmt FROM @func_query;
EXECUTE t_stmt;
SET i=i-1;
END WHILE;
LEAVE populate;
END LOOP populate;
SELECT "Kedar Vaijanapurkar" AS "Developed by";
END
$$
DELIMITER ;

Let me know if you like it and share if you find it useful. Please report any bug.
Below is the code for review:

Edit: I'd like to mention Ronald Speelman's post [http://moinne.com/blog/ronald/mysql/howto-generate-meaningful-test-data-using-a-mysql-function] of generating meaningful test data is great and it's something I'd like to work towards.

26 May

Men in Black-3 (MiB-3) – Xtreme Movie Review

man in black-3

“I just learned that Agent K has been assassinated by Boris from future, an alien criminal in 1969. I must travel through a time and stop him and save young Agent K. And I have only 24 hours to do the stuff and return home safely!! ”

O Yeah!! Will Smith Just told me the base line script of MIB-3 and that’s what the movie is all about. But that’s not the end, for MIB lovers the movie has got much more to offer.

men in black-3

It took 10 years for Men In Black to get along for MIB-3 but finally Will Smith AKA Agent J is back with good touch of comedy, well written sci-fi story and action movie. What the movie doesn’t yet show in demo videos is the creepy and “yuk” moments that girls and kids may not enjoy. I enjoyed it though :)

If you’re looking for a quick notes to “Go for MIB-3 or not”, here are the points:
– You’re a fan of Will and/or MIB, you ought to watch this.
– You’re a fan of sci-fi type movies, this one is right for you.
– You haven’t seen MIB-1 and MIB-2, you can still go for MIB-3. Just read the following 2 points and go for it.
1. Neurolyzer: A device that erases your memory to the preset time on it.
2. Agent J is Will Smith and Agent K is his partner to tackle the aliens and protect earth.

Now, When there is a Will, there is Smith.
:) What I took from the movie is Jokes, a lot of funny and comedy dialogue sequences that keep your lips in a “smiled” position.

Jokes apart; The movie starts with Boris (played nicely by Jemaine Clement), an alien escaping from the prison of moon. Boris aims to time travel to 1969 to assassinate Agent K, the time when Agent K had captured him and chopped his hand off.

I got surprised to see Nicole, the PCD girl, taking a small but creepy and funny role being lover of Boris and helping him escape from the Moon-Jail, with no more than 2 dialogues! Anyways the story continues…

During the regular life Agent J notices that everything has changed suddenly and he couldn’t find his partner Agent K. Agent J learns that Agent K has been already dead in a complex story sequence and decides to go to past 1969 to stop the Future Boris to kill Past Agent K and stop changing the history. Yep that’s MIB-3 :)

The way Will (Agent J) travels through time is a great animation and movie sequence when he jumps from Chrysler building. In past times Agent K has to put some security gadget on Apollo 11 to secure earth from the attack of Boris-kind-of-aliens while Boris from Future and Boris from Past try to stop Past Agent K.

Bike lovers ‘d like the bike of Boris from future.

The past involves Griffin, another alien who has a gift of seeing future, who actually provides the security gadget to Agent K. Another important character is a security-officer who helps Agent J & K to pass through security for the Apollo 11 and helps him in installing the gadget. That’s where Will (Agent J) learns about his past.
Anyways, Agent J and K successfully install the security gadget and kill both Boris from Future and Past – the happy sci-fi ending.

Alice Eve plays the young Agent O and looks beautiful, a good reason for Agent K to fall in love :)
I saw it in Hindi and here are a few really catchy dialogues from MIB-3:

Agent K to Agent J: Tumhari salary itni nahi hai ki tum ye kaam kar sako.

Agent K to Agent J: Ye ek raaz hai jo kum salary walon ko nahi bataya jata.

Agent K to Agent J: Uski biwi ne muze jala hua khana khilaya tha fir bhi wo mera dost hai.

A few I found from script which are better fun in English:
Agent J: You gotta send me back to 1969…
Alien: First, we gotta get high.
Agent J: My man, for real!
Alien: No, I mean really high.

The movie runs for approx 1.30 hour and never bores you even for a minute. The script is well written with constant witty dialogues and comedy sequences and well directed with good animation.
I saw it in 2D and watching it in 3D’d have made it creepier and nicer :)
It’s full of fun and time-pass entertainment. Go watch it, you won’t be disappointed.
Let me know what do you think about the movie too :)

Here is an official trailer for you:

03 May

Load columnar text file into MySQL table

Ever come accoss a situation where you have to load a flat text file into MySQL with data provided in single column!
Say, your data to be loaded in table looks something like this:

Company1
Street Name
City, MyState 8582
(999)999-999
another_Company
Another Street 2
New City, NWSTATE 8582
(111)111-1111

So how’d you go about loading a text file into MySQL where field values are given in a single column!!
In the above sample, data has fields like company-name, address-1, address-2 and phone given in a text file line by line.
Ofcourse you can write a piece of code in your choice of language: perl, shell, python…
But can you do this by sheer power of MySQL by using some commands or functions only?

Well, normally you will have to have a script for such data to be inserted into database. We always prefer the raw data / text file in the csv / tab separated (columns) file(s), which you can directly load to MySQL table as explained in my previous article: http://kedar.nitty-witty.com/blog/load-delimited-data-csv-excel-into-mysql-server

But that’s not the case here. And hence I have a workaround to share. With little more efforts you can also automate / scriptize this. [ I’m feeling bit lazy now. ;) ]

Follow these steps:
– Save above data in a file [say c:/main.txt considering windows]
– Connect to MySQL through commad prompt

– Create a main table to load all data to process later.

drop table if exists main; create table main ( id integer not null auto_increment primary key,alldata varchar(100), type int );

– Load data to MySQL table:

load data local infile 'C:/mainfile.txt' into table main lines terminated by '\r\n' (alldata) set id=null;
set @var=0;update main set type=if(id mod 4 = 1,@var:=@var+1,@var);

– Create table as per your requirement:

create table company_details (
company varchar(100),
street varchar(100),
citystatezip varchar(100),
phone varchar(100) );

– Generate insert statement:

select concat("insert into company_details (company,street,citystatezip,phone) values (", my_values, ");") from (select group_concat('"',alldata,'"') my_values from main group by type) X;

you may alternatively redirect this data to a txt file and load it back to mysql!

put above query in sql :
mysql -uroot -ppassword database < generate_insert_query.sql > inserts.sql

Finally load thus generated inserts to MySQL database table.
mysql -uroot -ppassword database < inserts.sql

I hope this helps many.

23 Apr

MySQL – Alter table-change datatype of column with foreign key

Schema alteration is a big headache especially when it has already reached to production and things get worse when the relations are involved.
Below is a short example for altering tables having foreign key constraints.

SQL for tables used in example:
create database temp;use temp;
drop table if exists city; drop table if exists country;
create table country ( c_id mediumint not null auto_increment primary key, c_name varchar(10)) engine=innodb;
insert into country (c_name) values ('india'),('uk'),('us');
create table city (city_id mediumint not null auto_increment primary key, country_id mediumint, city_name varchar(20), constraint fk_country_id foreign key (country_id) references country(c_id)) engine=innodb;
insert into city (country_id, city_name) values (1,'mumbai'), (2,'ahmedabad'), (2,'new york'), (2, 'la'), (3, 'london'), (3,'Birmingham');

We want to change the datatype of primary key c_id from mediumint to int.
Now, while you proceed to change the datatype of column in relation, you observe a typical mysql error.

mysql> alter table country modify column c_id int not null auto_increment;
ERROR 1025 (HY000): Error on rename of '.\temp\#sql-248_4' to '.\temp\country' (errno: 150)

“Error on the rename” doesn’t suggest you anything though you have a way out to know the actual error:

1) On your shell prompt fire:
$] perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed

For more details you may look into last foreign key error section of innodb status:

2) In this case specifically you should check innodb status:
mysql> show engine innodb status;
...
-------------------------
LATEST FOREIGN KEY ERROR
-------------------------
120422 13:11:47 Error in foreign key constraint of table temp/city:
there is no index in referenced table which would contain
the columns as the first columns, or the data types in the
referenced table do not match the ones in table. Constraint:
,
CONSTRAINT "fk_country_id" FOREIGN KEY ("country_id") REFERENCES "country" ("c_id")
The index in the foreign key in table is "fk_country_id"
...

This clearly explains you the reason for error in table alteration. InnoDB does not permit the creation of a foreign key constraint where a column references a nonmatching column type. This restriction applies while you alter the parent or child table with Foreign Key Constraint.
So how ‘d you proceed? Here is one way to do it.

The steps to follow are:
– Alter all the referencing tables and remove Foreign Key relations.
– Alter all the referencing and referenced tables and modify column to new datatype.
– Alter all referencing tables create Foreign Key relations.

To ease out the task execute following SQL and you will get your queries that will help you changing the datatype step by step. You have to provide the parent table name and column that’s being referenced along with the datatype you want to modify to.

#COMMAND FOR DROP FOREIGNKEY RELATION
select
concat('ALTER TABLE ', table_name,' DROP FOREIGN KEY ',constraint_name,';') drop_fk_syntax
from information_schema.key_column_usage where constraint_schema = database() and referenced_table_name ='country' and referenced_column_name ='c_id';

+--------------------------------------------------+
| drop_fk_syntax |
+--------------------------------------------------+
| ALTER TABLE city DROP FOREIGN KEY fk_country_id; |
+--------------------------------------------------+

#COMMAND FOR APPLY COLUMN DEFINITION CHANGE TO PARENT TABLE
SELECT CONCAT('ALTER TABLE ', referenced_table_name,' MODIFY COLUMN ', referenced_column_name, ' bigint not null auto_increment;') modify_column
from information_schema.key_column_usage where constraint_schema = database() and referenced_table_name ='country' and referenced_column_name ='c_id';
+---------------------------------------------------------------------------+
| modify_column |
+---------------------------------------------------------------------------+
| ALTER TABLE country MODIFY COLUMN c_id bigint not null auto_increment; |
+---------------------------------------------------------------------------+

#COMMAND FOR APPLY COLUMN DEFINITION CHANGE TO CHILD TABLES
select concat('ALTER TABLE ', table_name,' MODIFY COLUMN ', column_name, ' bigint not null;') modify_column
from information_schema.key_column_usage where constraint_schema = database() and referenced_table_name ='country' and referenced_column_name ='c_id';

+------------------------------------------------------------+
| modify_column |
+------------------------------------------------------------+
| ALTER TABLE city MODIFY COLUMN country_id bigint not null; |
+------------------------------------------------------------+

#COMMAND FOR RESTORING FOREIGNKEY RELATION
select
concat('ALTER TABLE ',table_name,' ADD CONSTRAINT ',constraint_name ,' FOREIGN KEY (', column_name, ') REFERENCES ', referenced_table_name , '(', referenced_column_name,');') create_fk_syntax
from information_schema.key_column_usage where constraint_schema = database() and referenced_table_name ='country' and referenced_column_name ='c_id';

+--------------------------------------------------------------------------------------------------+
| create_fk_syntax |
+--------------------------------------------------------------------------------------------------+
| ALTER TABLE city ADD CONSTRAINT fk_country_id FOREIGN KEY (country_id) REFERENCES country(c_id); |
+--------------------------------------------------------------------------------------------------+

Execute above queries in sequence to modify your schema.

Always remember: “backup saves your life like no one else”.
Share your better ways.

17 Mar

Selenium vs QTP differences and usage-quick reference

This article will describe you about QTP vs Selenium point by point in a easy to understand way. In the field of Software Quality Assurance (QA) automation tools, Selenium and QuickTest Professional (QTP) are often compared for their features, usability usecases and differences. Whether Selenium or QTP is a better automation tool or tool of your choice!!

Both are distinctively unique and good in their functionality and successful tool individually. To have a quick look, below is a tabular comparison of Selenium and QTP on different parameters.

Selenium versus QTP

Parameter Selenium QTP
Licensing Cost It is open source. So, there’s no licensing or renewal cost for this tool. It’s free of cost. User needs license for QTP which is very costly.
Additional Plug-ins It supports addition of plug-ins to achieve desired results that are not provided by Selenium Core.

Since, selenium is open source, plug-ins are also available free of cost.

It also supports add-ons, but user needs to purchase license for them.
Efforts and Skill User needs to have good amount of Java skill and more coding effort is required to implement the functions. It requires less effort to create a script, as it has a very good user friendly script development environment.
Actual End-user Simulation Selenium performs actions in the background on the
browser i.e. user can execute tests with the browser minimized.
QTP executes scripts equivalent to a person performing those steps manually on the application. So, browsers can’t be minimized (user cannot perform any other action on the machine while test is running).
Operating System Selenium supports more number of OS like Windows, Linux and Macintosh. QTP supports only Windows.
Application Type It can be used to test only web based applications. It can test web-based as well as desktop applications.
Browsers It supports IE, Firefox, Safari, Opera and few more. It supports only IE and Firefox.
Script Development Language Scripts can be developed in any of the languages like Java, Ruby, Python, C#, Perl, Groovy and many more. Scripts can be developed only in VBScript or JavaScript.
Technical Support Since it is an open source, it has no official tech support. QTP offers very good technical support via phone, mail, web forum.
Test Development Environment Test scripts can be developed in various IDEs like Eclipse, Visual Studio, Netbeans etc. Test scripts can be developed only in QTP.

Download a quick Selenium-vs-QTP in PDF Format.

Thanks for reading.

-- Kedar Vaijanapurkar --