Moving from MySQL to PostgreSQL

Using MySQL, MSSQL  for a long time and moving to PostgreSQL would be confusing and tiresome process for some of the developers, as there are some commonly used queries, practices are different in Postgres. Some of these changes seem strange and take sometime to find the existing queries, understand and fix them to make it work again.

Here I’ve compiled some of the changes to get started in fixing the Queries and write new Queries.

MySQL PostgreSQL
Comment Uses Non standard # to begin a comment Line PostgreSQL use ‘–‘ (double dash), as this is the ANSI standard, and both databases understand it.
Quotes Uses ‘ or ” to quote values. in WHERE clause you can use both. This is not ANSI standard for db uses only single quotes for this. in WHERE clause you can only use ‘ single quotes. Double quotes are used to quote system identifiers; field names, table names, etc.
accent/Backtick uses ` (accent mark or backtick) to quote system identifiers This is non standard. so not used/allowed
String Comparison string comparisons are case-insensitive string comparisons are case-sensitive

  • Need to use correct case in queries
  • Need to use case conversion function to search
  • To search / compare case-insensitive use operator, like ILIKE or ~*
Database, table, field and columns names Database, table, field and columns names can be case-sensitive or not, depending on which OS you are using Database, table, field and columns names are case-independent, unless they are created with double-quotes around their name, in which case they are case-sensitive.
Date Handling Both differ most in handling of dates, and the names of functions that handle dates.
Logical Operators uses C-language operators for logic. Ex: ‘foo’ || ‘bar’ means ‘foo’ OR ‘bar’, ‘foo’ && ‘bar’ means ‘foo’ and ‘bar’. This violates database standards and rules in a significant way Follows the standard, uses || for string concatenation Ex: ‘foo’ || ‘bar’ = ‘foobar’.

 

 

Installing and Configuring Laravel in Ubuntu

Laravel installation needs the Composer to be installed first. As the Composer will resolve the dependencies needs for the Laravel.

Install Composer
Composer is a popular dependency management tool for PHP, created mainly to facilitate installation and updates for project dependencies. It will check which other packages a specific project depends on and install them for you, using the appropriate versions according to the project requirements.
We will use composer to install Laravel and if any dependencies.

First do an update of Ubuntu package manager
sudo apt-get update

Now we need curl to download Composer.
Also we need php5-cli for installing and running Composer from command line.

sudo apt-get install curl php5-cli git

Now to download and install Composer as a system-wide command named composer, under /usr/local/bin execute the following command
curl -sS https://getcomposer.org/installer | sudo php -- --install-dir=/usr/local/bin --filename=composer

After the installation check it, everything works fine.
execute the command
composer
if you see the composer version and other info on the command parameters, composer installation is perfect.

Laravel installation

To install Laravel, we shall use the laravel installer, as it is the officially recommended/preferred method
So, execute the command to install
composer global require "laravel/installer=~1.1"
This installs the Laravel globally

Laravel has following requirements
PHP >= 5.4
Mcrypt PHP Extension
OpenSSL PHP Extension
Mbstring PHP Extension
Tokenizer PHP Extension
PHP_JSON extension (for some OS distros, we need to install manually)

 

That’s it. we are ready to go. Laravel is setup in your system. You can create new projects using Laravel. for that refer Laravel documentation.

If you use Apache for serving your website, you would need to enable mod_rewrite module.

By default, the Laravel comes with the public/.htaccess file that is used to allow URLs without index.php. If this default file comes with Laravel, does not work with your Apache installation, then create a new .htaccess file with the following contents or make sure your .htaccesss has to he following contents

Options +FollowSymLinks
 RewriteEngine On
RewriteCond %{REQUEST_FILENAME} !-d
 RewriteCond %{REQUEST_FILENAME} !-f
 RewriteRule ^ index.php [L]

 

Fetch / Read all email addresses from a web page using JavaScript

Recently I was looking some consultants mails ids and found some sites, in which the list of job consultants’ mail ids were listed with their names, address and email ids. But it was a tedious job to go thru the websites, classified portals pages to copy each and every mail id and save it to Excel sheet for further reference/process.
So I came up with this small script, which can be run from the Firebug or Chrome console.
just copy and paste this snippet into the console and call the findEmails function.
Calling the script is very easy. call the script as findEmails(). Voila. Worked as a charm.


function findEmails(){
var StrObj = document.body.innerText; var separateEmailsBy = ", ";
var email = ""; // if no match, use this
var emailsArray = StrObj.match(/([a-zA-Z0-9._-]+@[a-zA-Z0-9._-]+\.[a-zA-Z0-9._-]+)/gi);
if (emailsArray) {
email = "";
for (var i = 0; i < emailsArray.length; i++) { if (i != 0) email += separateEmailsBy; email += emailsArray[i]; } } return email; }

you may need to tweak a little bit to make it work for all email address patterns.

Snippet to Dynamically add rows to a table

This code snippet will help you to add rows dynamically in a html table.


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Insert Table Row using DOM</title>
<script language="javascript">
function addRow()
{
var tbody = document.getElementById("table1").getElementsByTagName("tbody")[0];
var row = document.createElement("TR");
var cell1 = document.createElement("TD");
var inp1 =? document.createElement("INPUT");
inp1.setAttribute("type","text");
inp1.setAttribute("value","New row");
cell1.appendChild(inp1);
var cell2 = document.createElement("TD");
cell2.innerHTML = "label3";
var cell3 = document.createElement("TD");
cell3.innerHTML = "label4";
row.appendChild(cell1);
row.appendChild(cell2);
row.appendChild(cell3);
tbody.appendChild(row);
//alert(row.innerHTML);
}
</script>
</head>
<body>
<tableid="table1">
<tbody>
<tr>
<td><inputtype=textvalue="Original Row"></td>
<td>label1</td>
<td> label2</td>
</tr>
</tbody>
</table>
<inputtype="button"value="Insert Row"onClick= "addRow();">
</body>
</html>

Most of the code is self explanatory, so am leaving it unexplained. If anybody needs help, post your questions, will help you.

Get computed Width of an HTML Element

This function will help you to get the computed width(actual width) of an HTML Element/Object. For example you may be using a div to show some contents inside that. But want to calculate the actual width of the div after loading the contents inside it. There comes this function to help you.
function getComputedWidth(theElt){
var browserName=navigator.appName;
if (browserName=="Microsoft Internet Explorer"){
var is_ie=true;
} else {
var is_ie=false;
}
if(is_ie){
tmphght = document.getElementById(theElt).offsetWidth;
}
else{
docObj = document.getElementById(theElt);
var tmphght1 = document.defaultView.getComputedStyle(docObj, "").getPropertyValue("width");
tmphght = tmphght1.split('px');
tmphght = tmphght[0];
}
return tmphght;
}
< div id="demo" onclick="alert(getComputedWidth('demo'));">hello there< /div>

To know the computed height of an element see here

Get computed Height of an HTML Element

This function will help you to get the computed height (actual height) of an HTML Element/Object. For example you may be using a div to show some contents inside that. But want to calculate the actual height of the div after loading the contents inside it. There comes this function to help you.


function getComputedHeight(theElt){
var browserName=navigator.appName;
if (browserName=="Microsoft Internet Explorer"){
var is_ie=true;
} else {
var is_ie=false;
}
if(is_ie){
tmphght = document.getElementById(theElt).offsetHeight;
}
else{
docObj = document.getElementById(theElt);
var tmphght1 = document.defaultView.getComputedStyle(docObj, "").getPropertyValue("height");
tmphght = tmphght1.split('px');
tmphght = tmphght[0];
}
return tmphght;
}

< div id=”demo” onclick=”alert(getComputedHeight(‘demo’));”> hello there< /div>
Thanks to Richard A who made the code complete to run as a demo.

To know the computed width of an element see here

Structured Process of Developing a Web Application

A small presentation that will help the beginners to understand the process of developing a Web application.
I did this presentation for the my Team @ Office to give them the idea of the whole process.

Advanced Techniques in PHP – Part 1

Here I tried to explain two advanced techniques in PHP.
1. Backtracing
2. Method Chaining

Webservice – a brief Introduction

Recently I prepared this presentation for a small lecture for my team, to give info about web services.
Hope this will be helpful to others too. I tried to explain what is an Webservice, how and where it is used, How it works, Necessary thing to know to develop a webserice, etc.

SVN Quick Installation Guide in Ubuntu

For all of the installation and configuration process, you need to login as root.

Installation

apt-get install subversion (this will install the subversion package) Install any other dependencies if any as required for your system.

Server Configuration

The following steps assume that, the necessary packages are installed. Now we will create the folders and the repository. The SVN repository can be placed in several places in the server. Most common places are /var/svn/ , /svr/svn/,? /home/svn/ etc.

First of all create a new user group named ?subversion?
addgrp subversion
Then add youself and the www-data ( the apache user) to this groups. You can do this by doing the following steps

1 Choose System > Administration > Users and Groups from your Ubuntu menu.
2 Select the Group tab
3 Click the ‘Add Group’ button
4 Name the group ‘subversion’
5 Add yourself and www-data (the Apache user) to this group .

Note: As default, the user configuration does not show system users. To see the Apache user in this list, do the following
a) Start gconf settings editor: Alt+F2 gconf-editor Enter.
b) In the tree, locate /apps/gnome-system-tools/users.
c) Select the “showall” check-box.

Now logout and login again in order to the above changes to be applied.
Now we will create the folder where you can place the SVN repository.

mkdir /home/svn
cd /home/svn
mkdir projectname
chown -R www-data:subversion
projectname
chmod -R g+rws
projectname<-This command will set the gid for proper permissions on all new files added to your SVN repository.

Now issue the following command to create the SVN repository
svnadmin create /home/svn/projectname
chmod -R g+rws projectname Issue this command again, because svnadmin will create folders and files without the write access to the group.

Importing the Project into SVN

Now go the folder where your project folder is present. Then issue to following command to import your project into SVN repository.
svn import projectnamefile:///home/svn/projectname ?m ?Initial checkin of projectname into SVN?

Creating Users

Now we have created the repository and imported the project into it successfully. Now we have to control the access to the repository by creating users.

If the SVN is just now installed the passwd file will not exist and it should be created first,?? under the /etc/subversion folder. This is the file that will contain the authentication details for the users.

to create the the password file, use the command
htpasswd ?c /etc/subversion/passwd username (Enter the username here)

this will prompt us to enter the password for the user. Enter the password. Now the user is added. To add more users use the same command above without using the ?c switch. For example to add another user, use the following.

htpasswd /etc/subversion/passwd username2 (Enter the second username here)

Checking out from Repository

We have created the repository, imported out project into SVN and created the users also. Now to check with the installation and repository, we have to checkout our project from the repository. Go to any of your working folder(not to the svn repository folder) first. To checkout use the following command

svn co file:///home/svn/projectname (this will checkout the projectname from SVN repository)

That?s it we are done with the our basic things to use SVN.? To access SVN over various protocols like http://, https://. svn:// and svn:+ssh// we can refer ubuntu or SVN documentation. For an example I?ll explain how to access our SVN repository over http:// (WebDAV).

Access the SVN via WebDAV (http://)

To access the SVN repository via WebDAV, we must configure the Apache server.
First we have to install the libapache2-svn package
apt-get install libapache2-svn

Then add the following snippet to the /etc/apache2/mods-available/dav_svn.conf file.

<Location /svn/projectname>
DAV svn
SVNPath /home/svn/
projectname
AuthType Basic
AuthName “
projectname subversion repository”
AuthUserFile /etc/subversion/passwd
<LimitExcept GET PROPFIND OPTIONS REPORT>
Require valid-user
</LimitExcept>
</Location>

Hope this will help somebody who would like to get all the basic, necessary steps to start using the SVN. Let me know your feeback.

For further reference see the following links

Feedback Form