Adding auto_increment after table creation

Say the table is created like this,

mysql> CREATE TABLE customer (id int, fname varchar(20));

The above table would be created without having an index. If there is a need to make id primary key, then

mysql> ALTER TABLE customer ADD PRIMARY KEY (id);

Then adding auto_increment would be as:

mysql> ALTER TABLE customer CHANGE id id int NOT NULL AUTO_INCREMENT;

Trying to assign auto_increment before making it a primary key – if it is not already done during creation, might produce an error. Actually trying to drop the primary key before neutralizing the auto_increment would also might create an error.
The following query would take out the auto_increment part from the table

ALTER TABLE customer CHANGE id id int not null;

And, of course, after this it is possible to drop primary key.

Renaming Tables and Columns in SQL Server

Changing table or colum in SQL Server is as follows,
To change the name of the table:
sp_RENAME ‘tbloldname’, ‘tblnewname’;
To chagne the name of the column:
sp_RENAME ‘tbl.columnoldname’,’columnnewname’,’COLUMN’;

** You might face a problem on changing names of enforced (integrity) columns.

Reading CHM files in Ubuntu

There are a bunch of tools for reading CHM (Microsoft compiled help files) on Ubuntu-Linux.
A number of them are listed on the site http://www.ubuntugeek.com/how-to-view-chm-microsoft-compiled-html-help-files-in-ubuntu.html
I have tried a number of them but I like xCHM one as it keeps the tree navigation easier.
The whole installation stuff is clearly listed on the given link.

jQuery – Simple & Powerful

jQuery seems the new player as far the client side manipulation is the issue. I had always thanked JavaScript for allowing us to do the dirty job on the client side starting from validation to amazing animations and magics – as it was seen on the Google earth, we should due credit.
Then comes Ajax, yup I have loved and played with it and it is so logical. Why do I have to submit the whole page while the intension is to submit only, say, a single value like an email for subscription. Of course it has done even more than that.
And now, jQuery which is a bundle of the two nice tools. JQuery is collection of JavaScript libraries that would make coding easy, fast navigation experience and almost a shortcut to Ajax.
The functions in the jQuery are human friendly and easy to remember.
It is possible to access an element only, or a group of elements or elements under the same class in easier way. Above all uses nicely callback functions – cooool.
All one has to do to start jQuery is to download the library from http://docs.jquery.com/Downloading_jQuery and locate it like any other JavaScript code:
Following this, be ready to plough. The main character in play would be the jquery object itself. Accessing it could be done either by directly calling it or using its alias($) – which is the custom to go. How it does is: first it would select single or group of elements and then apply tasks like editing, hide/show, replacing, assigning new style and many more. Let us use the following simpe XHTML code for illustration

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title> Sample jQuery </title>
<script src="JQuery/jquery-1.3.2.min.js" type="text/javascript" ></script>
<style type="text/css">
.highlight{
background-color: palegreen;
font-family: verdana;
color: black;
font-size: 12px;
width: 300px;
}
.emphasis{
text-weight: bold;
border: solid 1px blue;
width: 300px;
}
</style>

</head>

<body>
<p id="description" class="highlight">This is for the illustration purpose</p>
<p id="attention" class="emphasis">give attention for this</P>
<a href="http://www.devx.com/codemag/Article/40923/0/page/1">More examples</a>
<input type="button" id="toggler" value="toggle" />
</body>
</html>

Basic jQuery statement would have the following format:

<script type="text/javascript">
       $(document).ready( function(){
               logic goes here;
         });
</script>

Here the ‘$’ is an alias for the jQuery object, document is the representation of documents as in DOM. The ready method makes sure if the page is ok to proceed or not.
function() inside the ready() method is the callback function. The beauty begins here, we can define any function of our own as we like.
Lets remove the highlight style from description and assign it emphais
The first part “p#description” would select the paragraph with id description. Had it been “p”, it would select all paragraphs.
In this particular case it is also possible to say (“#description”).
removeClass and addClass are self-explanatory.
The chaining is possible as a dot operated methods because each selection would return an object.
Remember how to toggle in JavaScript? Had to use if .. how about in jQuery.
Lets make a click on the first paragraph to hide and show the second paragraph.

        $("#description").click( function(event){
              $("#attention").toggle();
        });

That is all. A single line would keep our fingers from being tired.

Here, the click method would accept the callback function. This call back function is specific to the call only. Say if I have used (“p”), then when I click on a particular <p>, the click method would be applicable only to the clicked paragraph element.

	$("p#description").click(function(event){
            $(this).hide();$("p#attention").show();
        });
	$("p#attention").click(function(event){
            $(this).hide();$("p#description").show();
        });

The above snippet shows only one paragraph at a time. But it shows them alternatively.

This is just to give a very highlight about jQuery. There are a lot of things especially on the selection area.

The following are are good sources of jQuery tutorial and references
jQuery.com
http://www.devx.com/codemag/Article/40923/0/page/1
http://roshanbh.com.np/2008/03/jquery-benefits-examples-free-ebook.html

Knowing Accessibility of Methods Using Reflection

While programming, one inquiry would be to know the if the methods from the given class could be accessible or not from any object, from derived class, from assembly or from derived & assembly…
Knowing ahead for time these factors would reduce the error theater from running.
Say a class with the following definition is given:

class Person{
     private void privateMethod(){}
     public void publicMethod(){}
     protected void protectedMethod(){}
     internal void internalMehtod(){}
     public static void staticMethod(){}
     public virtual void virtualMethod(){}
     protected internal void internalProtectedMethod(){}
}

So, while trying to access the methods of the Person class and wanted to know accessibility – no problem..
Dot Net has provided the following methods for this specific usage under the implementation of reflection

.Name - tells the name of the method
.IsPublic - checks if the method in the class is public or not (public)
.IsStatic - checks if the method is static (static)
.IsVirtual - checks if the method is virtual (virtual)
.IsAssembly - answers if it can be accessed from assembly (internal)
.IsFamily - answers if it can be accessed from derived class (protected)
.IsFamilyOrAssembly - from derived or assebmbly (protected internal)
.IsFamilyAndAssembly - from derived and assembly 

Look @
http://msdn.microsoft.com/en-us/library/system.reflection.methodbase.isfamily.aspx For example and application

Stored Procedure in MySQL

Using MySQL had one setback – lack of stored procedures. But, we had celebrated the farewell of this hindrance after welcoming MySQL 5.0.
Stored procedure is a collection of valid SQL statements residing in the data server. It may or may not return value based on our need.
Stored procedure should be our first choice when we have a choice to play with them – because:

  • Our application would be fast due to decreased use of network
  • Logics can be used more than once
  • Maintenance is simple – just edit at one place… and even more

I will try to use simple example to demonstrate how to use stored procedure in MySQL. I will use MySQL CLI for the illustration.

Syntax: CREATE PROCEDURE procname(parameters)
BEGIN
logic goes here

END
Example:  simple procedure to collect rows of a table:

Lets create a table first:
USE dbTest;
CREATE TABLE member (Id int not null auto_increment primary key, first_name nvarchar(12), last_name nvarchar(12) );

Say we want to collect all rows from the above table using stored procedure:
DELIMITER **
CREATE PROCEDURE selectAll()
BEGIN
SELECT * FROM member;
END
DELIMITER ;
The above snippet would create a stored procedure named selectAll() on the dbTest database.
DELIMITER ** would change the default delimiter of the CLI which is ‘;’ to **. I have to change the delimiter from ‘;’ to ‘**’ or any other character except ”. Otherwise, when the execution gets ‘;’ in the middle of execution, it will halt – and finally we would return the default delimiter at the end.
Using the stored procedure would be:
CALL selectAll(); or CALL selectAll; –> which will list all the rows of the table.
What about when we have parameters?

Listing Databases and Tables in MySQL

To list databases from terminal
1. Go to terminal and type mysql -uroot -proot –> taking user name as root and password as root to log as administrator
2. type show databases; –> this will list all databases in the server.

To list tables in the given database:
1. First move to the database by typing: use databasename where databasename is the name of the database whose tables would be listed
2. type show tables;
To see the make up of the table:
1. First move to the database
2. Type describe tablename or desc tablename

Using Checks

One of the handy tools regarding the constraint in SQL Server is check.
This simple, but powerful, tool allows us to use column based constraints on the data.
Say you have a table with age column. If you want to add constraint on age for values less than or equal to zero and above 100 – thinking we would live 100 years…
ALTER TABLE tbl ADD CONSTRAINT age_constraint CHECK(age < 100);
That is all.
If I try to run a query like this..
INSERT INTO tbl (age,..) VALUES (-12);
Then the engine would tell me that this cannot happen.

Encapsulating Queries in Object

Here is some more pattern for you. I have provided how to implement singleton pattern for database.
The idea is:
Why don’t we encapsulate our queries to the database in an object?
What we need is to execute a query whether it is update query – like delete, update and insert or retrieval query like select. In both cases, the the accomplishment would be one – executing – with different query parameters.
Which means we need only a single interface per application for that would simply execute our queries independent of the query string.
The encapsulating class for queries (acting as command) shall contain the implementation of method executeQuery().

/* 
Query class: implementing the basic tasks of query
Author: Kaleb B. Woldearegay
Date: 18.Apr.2009

Open for any kind of modification and use.
Please let me know when you use and modify it as I will learn from that 
--Query.php--
*/
	class Query{
		protected $sql;
		protected $db; /*any database implementing IDatabase */
		/*Method executeQuery would accept query string 
and executes it using the database's execute query method */
		public function __construct($database){
			$this->db = $database;
		}		
		protected function executeQuery($sql){
			return $this->db->executeQuery($sql);
		}
	}

Another point here would be, since we have two major tasks to perform against database – namely update queries and retrieval queries – we need two query commands which would be the derived classes of Query class

	/* This class would perform non resulted queries - 
like update, insert and delete
-- Query.php--
 */
	class UpdateQuery extends Query{
		/* constructor would pass the database for query class */
		public function __construct($db){
			parent::__construct($db);
		}
		/* execution of queries would goes here */
		public function executeQuery($sql){
			return parent::executeQuery($sql);		
		}
	}


	/* This class would perform resulted queries - like select */
	class ResultQuery extends Query{
		/* constructor would pass the database for query class */
		private $result; /* result object that would be created from database */
		public function __construct($db){
			parent::__construct($db);
		}
		/* constructor would pass the database for query class */
		public function executeQuery($sql){
                        /* Assigning the object */
			$this->result =  parent::executeQuery($sql); 
			return $this; //return the result query;
		}
		/* Collect the row from the collected result */
		public function getRow(){
			/*using associative array for the rows*/
		       if ( $row=mysql_fetch_array($this->result,MYSQL_ASSOC) ) {

            			return $row;

        		} else if ( $this->size() > 0 ) {

            			mysql_data_seek($this->query,0);

            			return false;

        		} else {

            			return false;

        		}

    		}
		/* Size of the collected result */
    		public function size () {

        		return mysql_num_rows($this->result);

    		}
	}

The hot-to-go part is simple. Any query should be executed! But the type of query is different – some require result set and other don’t. Which is a kind of topping we want on our query pizza. UpdateQuery and ResultQuery would provide the different type of flavors.
So, the client would encapsulate its request in either of the commands and would call an execute method.
But, this has some problem. We would need to know specifically of which type the query is. But what if we have an interface which would accept any command object and invoke its execute method – this seems neat..

/*
Query class: implementing the basic tasks of query
Author: Kaleb B. Woldearegay
Date: 18.Apr.2009

An invoker class would implement the 'command' pattern over 
the database applicaion
the invoker would be called by any client who needs a service 
from database either update or non-update

--Query.Invoke.php--
*/
	class QueryInvoker{
		private $query_obj; /*any query object inheriting 
from Query class */
		public function __construct($query_obj){
			$this->query_obj=$query_obj;
		}


		public function setQuery($query_obj){
			$this->query_obj=$query_obj;
		}
		/*the invoker would accept object [ which could be 
either update query object or non update query object ] which it 
has no any idea, and would invoke its  execute method */
		public function execute($query_obj,$sql){
			return $query_obj->executeQuery($sql);
		}
	

An example showing the interaction would be:

/*this example assumes you would put all your classes in the 
folder named classes - which is a practice of course.
	require('classes/MySQL.Database.php');
	require('classes/Query.php');
	require('classes/Query.Invoke.php');
	$obj_invoker=new QueryInvoker();
	$objDb=Mysql::getInstance();
	$objDb->connect();
	$objUpdate=new ResultQuery($objDb);
	$sql="select * from tbl";
	$result = $obj_invoker->execute($objUpdate,$sql);
        while($row = $result->getRow()){do stuff}

You can get Mysql class in the singleton pattern part of my article.

How to change password in ubuntu

Here is how you would change password in ubuntu

1. Go to preferences->about me
2. On the right top corner of the form you would see the Change Password..
3. Follow the wizard.