[Munawwar]

Aim

To generate a random password for each user during an INSERT operation.
After a 3 hour struggle I got the code to work.Phew! So I thought I'll write a small tutorial for D.I.C.

Before you start, crete a test database and table. Name the database dbs and create a table with the following SQL command:

CREATE TABLE users(
ID INT PRIMARY KEY AUTO_INCREMENT ,
name VARCHAR(20) ,
password CHAR(7)
);

Start MySQL and fire up the MySQL console. I use wampserver, so I click the icon on the taskbar and navigate to MySQL->MySQL console.
Try writing a sql query:

SELECT * FROM dbs.users;

Result: empty set.
Our aim is to write a MySQL trigger that is executed on every insertion operation on the users table. The trigger should create a random password for each user.

So the first step is to work on the password generation algorithm.
The generation algorithm is simple. All we need is to generate 7 random alphabets.How? Get a random number from 0 to 25, add it with capital A's ASCII value (that's 65), and then cast it to a character.
So in pseudo-code:

alphabet = cast_to_char(65+(randomInteger modulus 26))

To get numeric digits, we add a random number from 0 to 9 to ASCII value of 0 (that's 48):

digit = cast_to_char(48+(randomInteger modulus 10))

To get an alpha-numeric password,

alpha_or_num = randomInteger (mod 10);
if(alpha_or_num<5)
	//Generate a digit
else
	//Generate an alphabet

In SQL we have got the RAND function that generates a random floating point value between the range 0 to 1.

SELECT RAND() AS 'random floating point';

To get a random integer, we multiply the float value with, say 100 (so that we get a two digit integer) and then we round it to the nearest integer.

SELECT ROUND(RAND()*100) AS 'Random integer';

So now we can generate a random alphabet using the SQL MOD function (for modulus operation) and CHAR function (to cast the integer to a character):

SELECT CHAR(65+MOD(ROUND(RAND()*100),26)) AS 'Random alphabet';

Procedures

Procedures are like functions in C/C++/many other programming languages - they will help you to exceute multiple instructions in a single line of code.
Before we continue, we must change the delimiter from semi-colon to some other character sequence like //.This is done so that we can write multiple lines of code.

delimiter //

Now our code/SQL queries should always end with //.

SELECT * FROM dbs.users;//

So lets begin :

CREATE PROCEDURE dbs.randomPass()
BEGIN
	#All the code goes here. Right now this procedure does nothing.
END;//

CREATE,BEGIN and END are pre-defined keywords. To write comments we use #.To write multi-line comments, write between /* and */ like so: /*This is a comment*/
To execute the function:

CALL dbs.randomPass();//

Result: Query OK, 0 rows affected (0.00 sec)
To edit a procedure, you will have to delete the procedure and re-write the procedure.

DROP PROCEDURE dbs.randomPass;//

You can set the delimiter back to semi-colon (if you wish to do so):

delimiter ;

User-defined variables

To make our procedure useful, we should be able to declare variables and do calculations within the BEGIN..END block.
To declare variables we use the following format

DECLARE variable_name data_type;

Examples:

DECLARE someName INT;
DECLARE someOtherName VARCHAR(100);

Set variable default value:

DECLARE someName INT DEFAULT 0;
DECLARE someOtherName VARCHAR(100) DEFAULT "";

We can later set the values of the variables to 'whatever we want' like so:

SET someOtherName="whatever we want";

Increment operations on integers:

DECLARE count INT DEFAULT 0;
SET count=count+1;

We can also set the result of an SQL query into a variable using the INTO keyword:

SELECT count(*) FROM dbs.users INTO count;

The variables that I have shown above have local scope - means they can be only accessed within the BEGIN...END block in which they are decalred. They get destroyed after executing the block.
You can declare variables that won't get destroyed until the end of the session:

SET @count=1; #The @ symbol denotes that this variable is a session variable

Displaying its value:

SELECT @count AS 'count value';

Control structures

You have seen the if,while and for statements in many programming languages, haven't you?
In MySQL we write an IF statement in the following format:

IF expr THEN
	#do something
ELSEIF expr THEN
	#do something
ELSE
	#do something
END IF;

where expr is an expression.
Here ELSEIF and ELSE statements are optional.

Example:

IF count<7 THEN
	SET count=count+1;

The format for a WHILE statement is as follows:

WHILE expr DO
	#Statements here
END WHILE;

Now we can write the complete random password generator procedure - I hope it is self-explanatory:

CREATE PROCEDURE dbs.randomPass(OUT _password VARCHAR(7))
BEGIN
	DECLARE count INT DEFAULT 0;
	DECLARE alphanum INT;
	DECLARE randomCharacter CHAR(1);
	DECLARE password VARCHAR(10) DEFAULT "";
	
	WHILE count<7 DO
		SET count=count+1;
		SELECT ROUND(RAND()*10) INTO alphanum;

		IF alphanum<5 THEN
			#Generate a random digit
			SELECT CHAR(48+MOD(ROUND(RAND()*100),10)) INTO randomCharacter;
			
			#Debug message
			#SELECT alphanum AS 'RAND','less than 5' AS 'flow', randomCharacter AS 'random character';
		ELSE
			#Generate a random digit
			SELECT CHAR(65+MOD(ROUND(RAND()*100),26)) INTO randomCharacter;
			
			#Debug message
			#SELECT alphanum AS 'RAND','more than or equal to 5' AS 'flow', randomCharacter AS 'random character';
		END IF;
		#CONCAT function concatenates two or more strings and returns result
		SELECT CONCAT(password,randomCharacter) INTO password;
	END WHILE;
	
	#Debug message
	#SELECT password AS 'Password';
	SET _password=password;
END; //

Your immediate question will be what's "OUT _password VARCHAR(7)"? Well, procedures can set(in some sense, return) external variables using the OUT keyword, so that we can use the generated password later.

SET @pass="";
CALL dbs.randomPass(@pass);
SELECT @pass AS 'Password';//

Within the procedure @pass will have a local name of _password. At the end of the procedure I set _password to the generated password:

SET _password=password;

That would set @pass to the generated password


Triggers

A trigger is a piece of code that gets executed after an event such as an insertion,deletion or and update operation on a table.

In the above procedure I have commented out the debug messages. You can uncomment it if you wish to. But you MUST remove/comment any such debug messages before using the procedure within a trigger.
If you don't, you will get a 'Not allowed to return a result set from a trigger' error message.

The format for creating a trigger is as follows (taken from MySQL reference manual):

CREATE TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_body

trigger_event can be a database operation like insert,update or delete.
trigger_time can be 'before' or 'after' - indicates to MySQL whether the trigger must be called before or after the database operation.

Example:

CREATE TRIGGER dbs.user_insert BEFORE INSERT ON dbs.users
	FOR EACH ROW BEGIN
		#Code goes here
	END;//

To edit the trigger you will have to drop the trigger and re-write the trigger (AAH! This is a pain!)

drop trigger dbs.user_insert;//

It's now time to finish this:

CREATE TRIGGER dbs.user_insert BEFORE INSERT ON dbs.users
	FOR EACH ROW BEGIN
		CALL dbs.randomPass(NEW.password);
	END;//

Here the procedure sets NEW.password with the generated password, and the trigger ends, after which the table gets updated.
Now anytime you insert a new user name into the table a random password gets saved along with it .

Note: The NEW object contains the information inserted into the table.
That is, say the following insertion is done:

INSERT INTO dbs.users(name) VALUES ('Munawwar');

then New.name (within the trigger) would be 'Munawwar'.

That's all folks!

[chathuraguy]

seems to be nice dude.i would have like much it. thanks

This post has been edited by chathuraguy: 31 March 2011 - 10:34 PM