To Code is Awesome

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

[html]

[/html]

Declaration

[javascript]
var db = new DbHelper(“mydb”);
// mydb Es el nombre de la db
[/javascript]

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

[javascript]
db.execScript ( “sqlscripts/setup.sql” );
[/javascript]

In sqlscripts/setup:
[sql]
CREATE TABLE IF NOT EXISTS
schools (
school_id int PRIMARY KEY ASC,
school_name text
);
[/sql]

Simple query:

[javascript]
// 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
);
[/javascript]

To query with no parameters:
[javascript]
db.query(
sql,
null,
fn
);
[/javascript]

Inserts and updates:

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

[javascript]
// 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);

[/javascript]

Batch inserts / updates:

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

[javascript]
// 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);
[/javascript]

The use is simple, useful, short and elegant.

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.

[javascript]
/**
* 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
[/javascript]

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.

[javascript]
/**
* 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
[/javascript]

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:

[javascript]
/**
* 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
[/javascript]

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! 🙂

To connect to SQL Server from Linux use this command to install the driver:
[bash]
$ sudo yum install -y php-mssql
[/bash]

For example, to php5.6:
[bash]
$ sudo yum install -y php56w-mssql
[/bash]

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.

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.

[sql]
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

[/sql]
As we can see, the identation is very important for the code to be clear and legible. I like the following format:
[sql]
COMMAND
arguments
[/sql]

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.

[sql]
/* 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
[/sql]

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

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:

[bash]
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
[/bash]

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

Happy coding! 🙂