Sql Server object creation scripts, the right way

Posted by admin on March 28, 2016
sql server / Comments Off on Sql Server object creation scripts, the right way

The right way to write object creation scripts in Sql Server (create tables, procedures, functions, constraints, etc) is as follows:

  1. First, check is the object exists.
    • If it does, delete it.
  2. Then, create the object
  3. Next, repeat for all the objects that depend upon the object we just create

**Important** Always write “USE Database GO” at the beginning of the script.

**Importante** Always include descriptive comment about the objects, always write your comments as if you were explaining to a kid, make them clear and short.

Let’s see an example, table creation script.

USE
    MyDataBase
GO

/* Object: MyTable */
-- If exists, delete it
IF EXISTS (
    SELECT
        *
    FROM
        INFORMATION_SCHEMA.TABLES
    WHERE
        TABLE_NAME = 'MyTable'
)
BEGIN
    DROP TABLE
        MiTabla
END
GO

/*
 * MyTable
 *     - Test table, stores testing data
 * Include relevant information as author, date, project name, reason for creation, etc.
 */
CREATE TABLE
    MyTable(
        MyTableId int NOT NULL,
        Description varchar(30) NOT NULL,
    )
GO

/**
 * PK_MiTabla
 *     - Primary key constraint
 */
ALTER TABLE
   MyTable
ADD CONSTRAINT
   PK_MyTable
PRIMARY KEY (
   MyTableId
)
GO

As we can see, the identation is very important for the code to be clear and legible. I like the following format:

COMMAND
    arguments

With this format I can read the instructions vertically with ease. Now will see an example for a stored procedure. It is really important to document the following:

  1. Functionality: What the procedure does
  2. List of parameters, including type and description
  3. Usage example

There are other important data to include, as change history, where at least we must specify:

  1. Who write the change
  2. Why
  3. When

Write our scripts this way, in case we want to run them in another server, they will not cause conflict if they run twice by mistake, and if the execution is made for somebody else, it will very clear for the other person know what is doing and why.

/* Object: myStoredProcedure */

-- If exists, delete it
IF EXISTS (
	SELECT
		*
	FROM
		INFORMATION_SCHEMA.ROUTINES
	WHERE
		ROUTINE_NAME = 'myStoredProcedure'
		AND ROUTINE_TYPE = 'PROCEDURE'
)
BEGIN
	DROP PROCEDURE
		myStoredProcedure
END
GO

/*
	myStoredProcedure:
	-	Select all records of my table using the key @myParameter
	Parameters:
		@myParameter INT - The table id
			
	Usage:
		EXEC myStoredProcedure 1001
	
	// Other relevant information
        Luis Espino, March 27, 2016, procedure creation
            - Example of creation script for the blog
*/
CREATE PROCEDURE
	myStoredProcedure (
		@myParameter INT
	)
AS
BEGIN
	--	Logic brief description
	--	    Select the records using the parameters
	SELECT
		*
	FROM
		MiTabla
	WHERE
		MiTablaId = @miParametro
END
GO

-- Grant permissions to users that can use the procedure, public in this case
GRANT EXECUTE ON
	myStoredProcedure
TO
	PUBLIC
GO

It’s really easy, the important thing is to keep order and structure in the code.

Upgrading PHP to 5.6 in linux Centos 6

Posted by admin on March 27, 2016
Php / Comments Off on Upgrading PHP to 5.6 in linux Centos 6

Php by default in Centos 6 may be versión 5.3. If you want to make the most of php with useful functions lie hex2bin, you need the 5.6 version.

In order to upgrade, first we must add the repo, then remove the current version and finally install the new version.

This script contains the commands needed to carry out the tasks:

sudo rpm -Uvh http://mirror.webtatic.com/yum/el6/latest.rpm
sudo yum remove php-common
sudo yum install php56w
sudo yum install php56w-mysql
sudo yum install php56w-common
sudo yum install php56w-pdo
sudo yum install php56w-opcache
sudo yum install php56w-pgsql
sudo yum install php56w-pear
sudo yum install php56w-mcrypt
sudo yum install php56w-xml
sudo yum install php56w-xmlrpc
sudo yum install php56w-devel
sudo yum install php56w-pecl-memcache
sudo yum install php56w-gd
sudo yum install php56w-pspell
sudo yum install php56w-snmp
php --version               

With the last line we can verify the success of the upgrade.

Happy coding! 🙂

Tags: , , , , ,