MySQL Stored Procedure.

Those days I’m involving with the new project in my office and In this project we are heavily used mysql store procedures there. Because of that I’m decide to publish some technical post related to mysql store procedures. :)

I’m going to discuss this topic using series of article, in this series I will cover these topics

  •     Introduction about  MySQL Stored Procedure (Current one)
  •     How to handle Variables and Parameters in MySQL Stored Procedures
  •     How to use Conditional Controls in MySQL Stored Procedure
  •     How to handle Loops in MySQL Stored Procedure
  •     How to use Transactions in MySQL Stored Procedure and Sql Exception, SQL Warning Handling
  •     How to use Cursors in MySQL Stored Procedure
  •     How to Debugging MySQL Store Procedure
  •     How Write Unit Testing for MySQL Store Procedure

Ok next we move to identify what is the basic concept behind the store procedure. In this article I’m going to give the answers for these Questions

  1. What is the purpose of Store Procedures?
  2. What is the purpose of using Delimiter?
  3. How to Create Simple Stored Procedure?
  4. How to Call your Stored Procedure?
  5. How Delete Stored Procedure?
  6. How Modify Stored Procedure?

 1.  What is the purpose of Store Procedures?

Stored procedure is a great feature to write the reusable queries, I mean write once and use as much you need. Generally stored procedures contain the bunch of queries which runs one after another.

2. What is the purpose of using Delimiter?

Delimiter is ]character which defines the end of your SQL statement.

Why we define it ? O.o

Semicolon (;) is the default ending notification any SQL statement. But in stored procedure we can have multiple SQL statements so we can not consider semicolon as a ending indication. That’s why we are defining delimiter for each stored procedure in MySQL. Generally I use $$ as delimiter in my stored procedure. if you need you can define your own delimiter in store procedure.

3. How to Create Simple Stored Procedure?

DELIMITER $$

CREATE PROCEDURE `sp_sample_procedure` ()
LANGUAGE SQL
DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'comment goes here.'
BEGIN

-- sql querry (insert/select/update/delete)goes here.

END$$

CREATE PROCEDURE `sp_sample_procedure` () : This sentance will create your procedure.
LANGUAGE: For portability purposes; the default value is SQL.
DETERMINISTIC: If the procedure always returns the same results, given the same input. This is for replication and logging purposes. The default value is NOT DETERMINISTIC.
SQL SECURITY: When we call the procedure it will check for the privileges of the user. DEFINER is the user who has created the procedure and INVOKER is the user who is calling the procedure. Default value is DEFINER.
COMMENT: This is just like a table comment, basic information of the stored procedure.

These are optional characteristics (LANGUAGE,DETERMINISTIC,SQL SECURITY,COMMENT)

4. How to Call your Stored Procedure?

Simply Executing sp name with CALL keyword like below SQL statement.


CALL sp_sample_procedure();

5. How Delete Stored Procedure?

Simply executing Drop statement with sp name we can drop the sp, with drop statement it’s better us the IF EXISTS statement using this statement we can avoid the error coming if given procedure not found. in the execute time in the database.


DROP PROCEDURE IF EXISTS procedure_name;

6. How Modify Stored Procedure?

Same as the alter table we can edit our stored procedure also. we will have ALTER PROCEDURE statement.

DELIMITER $$

ALTER PROCEDURE `sp_sample_procedure` ()
LANGUAGE SQL
DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'comment goes here.'
BEGIN

-- sql querry (insert/select/update/delete)goes here.

END$$

So Here I have completed the first article here. If you don’t want to miss other articles thensubscribe to feed via your email or Follow  on Twitter.

Thanks, Have a nice day ;)

Different BETWEEN Mysql REPLACE and INSERT ON DUPLICATE KEY UPDATE

While me and my friend roshan recently working as a support developers at Australia famous e-commerce website. recently roshan as assign a new bug in this site it’s related to the product synchronize process in the ware house product table and the e-commerce site, his main task was check the quickly the site product table and check with ware house product table product if the either insert new data into a site database, or update an existing record on the site database, Of course, doing a lookup to see if the record exists already and then either updating or inserting would be an expensive process (existing items are defined either by a unique key or a primary key). Luckily, MySQL offers two functions to combat this (each with two very different approaches).

1. REPLACE = DELETE+INSERT
2. INSERT ON DUPLICATE KEY UPDATE = UPDATE + INSERT

1 . REPLACE

This syntax is the same as the INSERT function. When dealing with a record with a unique or primary key, REPLACE will either do a DELETE and then an INSERT, or just an INSERT if use this this function will cause a record to be removed, and inserted at the end. It will cause the indexing to get broken apart, decreasing the efficiency of the table. If, however

REPLACE INTO
ds_product
SET
pID =  3112,
catID =  231,
uniCost = 232.50,
salePrice = 250.23;

2. ON DUPLICATE KEY UPDATE

ON DUPLICATE KEY UPDATE clause to the INSERT function. This one actively hunts down an existing record in the table which has the same UNIQUE or PRIMARY KEY as the one we’re trying to update. If it finds an existing one, you specify a clause for which column(s) you would like to UPDATE. Otherwise, it will do a normal INSERT.

INSERT INTO
ds_product
SET
pID =  3112,
catID =  231,
uniCost = 232.50,
salePrice = 250.23,
ON DUPLICATE KEY UPDATE
uniCost = 232.50,
salePrice = 250.23;

This should be helpful when trying to create database queries that add and update information, without having to go through the extra step.

Thanks Have a Nice Day :)

Strategy Pattern

Today I’m going to look at the Strategy Pattern, it’s one of impotent design pattern in the GOF Pattern, in the my previous post I’m discuss What is a  Design Pattern ?  and the basic back ground related to the design pattern.

Which type of pattern ? 

it’s categories under the Behavioral Pattern.

How GOF(Gang of four) Define it ?

Define a family of algorithms, encapsulate each one, and make them interchangeable. Strategy lets the algorithm vary independently from clients that use it.

How used in the Real World ?

To explain the strategy in the real world, let’s take the example. E-commerce has been a bit of a buzz word in the today world . Trading on the Internet is a hugely complex business and there are many aspects to consider when designing a successful e commerce site. In these websites when the customer buy  items  there are in the processing shopping cart there are many payment mechanisms are introduce to the Customers to do there payment  Ex: Pay pal , Visa , Mobile payment some time according to the country wise there are payment mechanisms are introduce  Ex: AUS  B-Pay. this real world scenario I’m get as the example :)

What is the Current Problem and  How to Solve it ? :O

In this scenario I need to maintain the different payment mechanism in the e commerce site these payment method are need to separate each other because they have different process to do but they do the same action (make the payment) to give the correct solution I need to follow mainly 3 design Principles.

Principles ?

1. I need to Identify the aspects of my application that vary and separate them from what stays the same.

2. Program to an interface not to an implementation.

3. Favor composition over inheritance

Ok next I go through the each principle

How Design According to Principle ?

1. First I’m identify the three Payment types Visa , Pay Pal and Mobile Payment.

2. In these payment method do the same action (process the Customer Payment) but it’s change on the Run time according the Customer selection option there for Using OOP concepts, I’m going use  abstract class or an interface to create the opportunity to dynamic behavioral changes with the help of Polymorphism.

3. After create the Interface I’m going to put more than one class together as class variables that is called composition,  then encapsulate payment methods into their
own set of classes. like Ex: Payment types Visa , Pay Pal

How it Can Design in the Design Level ?

Strategy Pattern Class Diagram

How to Implement it ?

Here I’m used Java Code for do my Sample Implementation.

PaymentMethod Interface

public interface PaymentMethod {
	public boolean pay(double amount);
}

Pay Pal Implementation

import java.util.Date;

public class PayPal implements PaymentMethod {

	private final String name;
	private final String cardNumber;
	private final Date expires;

	public String getName() {
		return name;
	}

	public String getCardNumber() {
		return cardNumber;
	}

	public Date getExpires() {
		return expires;
	}

	public PayPal(String name, String cardNumber, Date expires) {
		super();
		this.name = name;
		this.cardNumber = cardNumber;
		this.expires = expires;
	}

	@Override
	public boolean pay(double amount) {
		return true; // if payment goes through
	}
}

Visa Implementation

import java.util.Date;

public class Visa implements PaymentMethod {

	private final String name;
	private final String cardNumber;
	private final Date expires;

	public String getName() {
		return name;
	}

	public String getCardNumber() {
		return cardNumber;
	}

	public Date getExpires() {
		return expires;
	}

	public Visa(String name, String cardNumber, Date expires) {
		super();
		this.name = name;
		this.cardNumber = cardNumber;
		this.expires = expires;
	}

	@Override
	public boolean pay(double amount) {
		return true; // if payment goes through
	}
}

MobilePayment Implementation

import java.util.Date;

public class MobilePayment implements PaymentMethod {

	private final String serviceProvider;
	private final String mobileNumber;
	private final Date expires;

	public String getServiceProvider() {
		return serviceProvider;
	}

	public String getMobileNumber() {
		return mobileNumber;
	}

	public Date getExpires() {
		return expires;
	}

	public MobilePayment(String serviceProvider, String mobileNumber,
			Date expires) {
		super();
		this.serviceProvider = serviceProvider;
		this.mobileNumber = mobileNumber;
		this.expires = expires;
	}

	@Override
	public boolean pay(double amount) {
		return true; // if payment goes through
	}
}

There I’m implement the Item Class for the do this

Item Class

public class Item {
	private final String code;
	private final String name;
	private final Double price;

	public Item(String code, String name, Double price) {
		this.code = code;
		this.name = name;
		this.price = price;
	}

	public String getCode() {
		return code;
	}

	public String getName() {
		return name;
	}

	public Double getPrice() {
		return price;
	}
}

ShoppingCart Class

import java.util.ArrayList;
import java.util.List;

public class ShoppingCart {
	private final List items;

	public ShoppingCart() {
		items = new ArrayList();
	}

	public void addItem(Item item) {
		items.add(item);
	}

	public double calcTotalCost() {
		double total = 0.0;
		for (Item item : items) {
			total += item.getPrice();
		}
		return total;
	}

	public boolean pay(PaymentMethod method) {
		double totalCost = calcTotalCost();
		return method.pay(totalCost);
	}
}

SamplePayProcessTest Class

import static org.junit.Assert.*;

import java.util.Calendar;
import java.util.Date;

import org.junit.Test;

public class SamplePayProcessTest {
	@Test
	public void payBillUsingVisa() {

	  ShoppingCart instance = new ShoppingCart();

	  Item a = new Item("IT001","T-Shirt", 750.43);
	  instance.addItem(a);

	  Item b = new Item("IT002","hat", 102.99);
	  instance.addItem(b);

	  Date expiryDate = getCardExpireyDate();
	  PaymentMethod visa = new Visa("CaptainDebug", "1234234534564567", expiryDate);

	  boolean result = instance.pay(visa);
	  assertTrue(result);

	}

	private Date getCardExpireyDate() {
	  Calendar cal = Calendar.getInstance();
	  cal.clear();
		cal.set(2015, Calendar.JANUARY, 21);
		return cal.getTime();
	}
}

Conclusion

According to the given Solution The behavior of a class should be defined at runtime, and easily maintaining the each payment behavior if I need new payment method easily I can be plug, without effecting to others.

This is the one of the sample scenario we can address the Strategy Pattern in the real world, ok we will meet next day with another GOF design pattern. Thank you

Have a Nice Day :)

RESTful Web services Part 1

What is REST ?

 REST is an Architectural Style, it’s style of software architecture for distributed hypermedia systems such as World Wide Web.

What is RESTful Web services ?

RESTful Web services is an  Application of REST architectural style to services that utilize Web standards (URIs, HTTP, HTML, XML,  Atom, RDF etc.). This architecture style allows us to setup resources, or individual services, that can be identified by a URL and use the HTTP

RESTful Application Cycle

What is HTTP ?

HTTP is a communications protocol that is used on the internet as a means of back-and-forth conversation between the client computer and the server. The protocol has a set of rules that defines how messages can be sent and how to reply.

Principles of REST    mainly there are five REST principals.

• Give everything an ID
• Standard set of methods
• Link things together
• Multiple representations
• Stateless communications

In this post i’m specify the basic concepts of the REST and REST Web services.  Other things  I’m planing to continue with next post.

Have a Nice day :)

What is a Design Pattern?

What is a Pattern?

A Pattern is a solution to a problem in a context.

Context : is the situation in which the pattern applies.This should be a recurring situation

Problem : refers to the goal you are trying to achieve in this context. But it also refers to any constraints that occur in the context.

Solution : is what you are after: a general design that anyone can apply which resolves the goal and the set of constraints.

This means, a Design Pattern gives you a solution to a common recurring design problem, However,The Design Patterns are not meant to be laws or rules; they are just guidelines that you can even alter to fit to your needs

The first and the most definite Fundamental Design Pattern Catalog is GOF, There are 23 fundamental patterns in it.

Before Start Learning the Design Pattern you need to Knowing the OO basics (Abstraction,Encapsulation, Inheritance, Polymorphism) will
not make you a good designer :) ,Patterns do not give you code. They give general solutions to design problemsPatterns are not invented they are discovered.

To learn patterns faster, they can be classified as,

Creational Patterns: Used to construct objects such that they can be decoupled from their implementing system.

Structural Patterns: Used to form large object structures between many disparate objects.

Behavioral Patterns: Used to manage algorithms, relationships, and responsibilities between objects.

Reference Head First Design Pattern Book

We need to use Design Patterns are a tool, which only be used when it is needed, Otherwise Heavy use of patterns will lead to complexity. Now I thing you have a clear idea about What is Design Pattern ?

Thanks, Have a great day :)