Monthly Archives: March 2016

Helper db.js for Cordova-sqlite-storage

Posted by admin on March 30, 2016
javascript / No Comments

Espino316 dbhelper-cordova-sqlite-storage is a helper for plugin Cordova-sqlite-storage (https://github.com/litehelpers/Cordova-sqlite-storage).
The goal is make more simple the use of the plugin (already excellent), because I like to use shorter more maintainable code.

**Important** In order to use this component, you must first install Cordova-sqlite-storage (https://github.com/litehelpers/Cordova-sqlite-storage).

Usage

Include

<script src="db.js"></script>

Declaration

var db = new DbHelper("mydb");
// mydb Es el nombre de la db

Script execution (i.e. create initial structure):

db.execScript ( "sqlscripts/setup.sql" );

In sqlscripts/setup:

CREATE TABLE IF NOT EXISTS
	schools (
		school_id int PRIMARY KEY ASC,
		school_name text
	);

Simple query:

// Variable to store the sql
var sql = "SELECT * FROM schools WHERE school_id = :schoolId";

// Variable to store the parameters, if any
var params = {};
params['schoolId'] = 9901;

// Variable for store the function to apply
// Expects an object, assoc array.

// prints:
// [{ school_id: "9001", "school_name":"PABLO LIVAS"},{ school_id: "9002", "school_name":"ADOLFO PRIETO"}]
var fn = function ( result ) {
    console.log( result );
};

// Call query
db.query(
    sql,
    params,
    fn
);

/*  Anothe example */
// We can also declare params simply with ?

// Variable to store the sql
var sql = "SELECT * FROM schools WHERE school_id = ?";

// Variable to store the parameters, if any
var params = [9901];

// Variable for store the function to apply
// Expects an object, assoc array.

// prints something like:
// [{ school_id: "9001", "school_name":"PABLO LIVAS"},{ school_id: "9002", "school_name":"ADOLFO PRIETO"}]
var fn = function ( result ) {
    console.log( result );
};

// Call query
db.query(
    sql,
    params,
    fn
);

To query with no parameters:

db.query(
  sql,
  null,
  fn
);

Inserts and updates:

The helper uses “upserts” statements. If record exists, updates, else, inserts.

// Name of the table
var tableName = "schools";

// Object with data
var tableData = {
    "shool_id": 9003,
    "school_name": "MOISEIS SAENS";
};

// Array with list of key fields
var keyFields = ["school_id"];

// Call upsert
db.upsert(tableName, tableData, keyFields);

Batch inserts / updates:

Same as previous function, but the data is an array of objects.

// Table name
var tableName = "schools";

// Object array (the data)
var tableData = [
    {
        "shool_id": 9003,
        "school_name": "MOISEIS SAENS";
    },
    {
        "shool_id": 9004,
        "school_name": "JERONIMO SILLER";
    }
];

// Array with a list of the key fields
var keyFields = ["school_id"];

// Call bulkUpsert
db.bulkUpsert(tableName, tableData, keyFields);

The use is simple, useful, short and elegant.

Tags: , , , , , ,

7 functions extendind Strings in Javascript that will save you a lot of code and are very elegant

Posted by admin on March 29, 2016
javascript / No Comments

These 7 basic string functions are easy to implement adding them to the String object, are very useful and the resulting code is a lot more legible, maintainable and elegant.

1 y 2: Encode y Decode Uri

I use these functions a lot and very often forgot to add the suffix “Component” to them. It’s kind of unnatural. With these simple extensions, my problems just went away.

/**
 * Encodes the string to URI format
 * Usage:
 *  var str = "This is a string";
 *  str = str.encodeUri();
 *  console.log( str );
 *  // Outputs "This%20is%20a%20string"
 */
String.prototype.encodeUri = function () {
  return encodeURIComponent( this );
}; // end encodeUri

/**
 * Decodes the string from Uri format
 * Usage:
 *  var str = "This%20is%20a%20string";
 *  str = str.decodeUri();
 *  console.log( str );
 *  // Outputs "This is a string";
 */
String.prototype.decodeUri = function () {
  return decodeURIComponent( this );
}; // end decodeUri

3 y 4: EscapeRegEx & ReplaceAll

For some strange reason, when replacing one string for another only the first match is replaced. This is a problem, but easily solved with these two functions and adding these extensions, use is much simpler.

/**
 * Creates a regular expression string from the string,
 * Prepending "\" special characters "escaping" the chain
 * Usage:
 *  var str = "Hi!";
 *  str = str.escapeRegExp();
 *  console.log( str );
 *  // Outputs: "Hi\!"
 */
String.prototype.escapeRegExp = function () {
    return this.replace(/([.*+?^=!:${}()|\[\]\/\\])/g, "\\$1");
}; // end function escapeRegExp

/**
 * Replace all instances of "find" by "replace"
 * in the string
 * Usage:
 *  str = "Hello my name is $nombre, $nombre is my name";
 *  str = str.replaceAll( "$nombre", "Luis" );
 *  console.log( str );
 *  // Outputs: "Hello my name is Luis, Luis is my name"
 */
String.prototype.replaceAll = function ( find, replace ) {
  return this.replace(new RegExp(find.escapeRegExp(), 'g'), replace);
}; // end function replaceAll

5, 6 y 7: Right, Left & Contains

If VB was your first language you surely miss these functions. To implement for all strings is quite simple, saves keystrokes and the result is very intuitive:

/**
 * Returns "n" charactes to the left
 * Usage:
 *  var str = "ABCDE";
 *  str = str.left(3);
 *  console.log( str );
 *  // Outputs: "ABC"
 */
String.prototype.left = function ( n ){
	if (n <= 0)
	    return "";
	else if (n > this.length)
	    return this;
	else
	    return this.substring(0,n);
}; // end function left

/**
 * Returns "n" characteres to the right
 * Uso:
 *  var str = "ABCDE";
 *  str = str.right(3);
 *  console.log( str );
 *  // Outputs: "CDE"
 */
String.prototype.right = function ( n ) {
    if (n <= 0)
       return "";
    else if (n > this.length)
       return this;
    else {
       var iLen = this.length;
       return this.substring(iLen, iLen - n);
    }
}; // end function right

/**
 * Returns true if the string contains the parameter "str"
 * Usage:
 *  var str = "Hello world!";
 *  var lookFor = "world";
 *  var inString = str.contrains(lookFor);
 *  console.log(inString);
 *  // Outputs: true
 */
String.prototype.contains = function ( str ) {
  return (
    this.indexOf( str ) > -1
  );
}; // end function contains

Running this script at the beginning of your code will allow you to use these extensions, will make your life easier and allows you to have a clean and elegant code. Happy Coding! ๐Ÿ™‚

Tags: , , , , , , , ,

Connect Sql Server 5.6 from Php in Linux CentOS 6

Posted by admin on March 28, 2016
sql server / No Comments

To connect to SQL Server from Linux use this command to install the driver:

$ sudo yum install -y php-mssql

For example, to php5.6:

$ sudo yum install -y php56w-mssql

This command installs freetds , a collection of libraries to connect to SQL Server from Linux. With this library we can not only connect from Php, but from the command line with the tsql program.

After installing, do not forget to restart apache:

$ sudo apachectl restart

Happy coding ๐Ÿ™‚

Tags: , , , , , , ,

Sql Server object creation scripts, the right way

Posted by admin on March 28, 2016
sql server / No Comments

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 / No Comments

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: , , , , ,