Wednesday, April 10, 2013

what is connection collation in mysql

The main difference is just in performance and sorting accuracy. utf8_general_ci is a bit faster than utf8_unicode_ci. utf8_unicode_ci on the other hand is a bit more accurate for sorting.

The character set for statements that arrive from the client. The
session value of this variable is set using the character set
requested by the client when the client connects to the server. (Many
clients support a --default-character-set option to enable this
character set to be specified explicitly.) The global
value of the variable is used to set the session value in cases when
the client-requested value is unknown or not available, or the server
is configured to ignore client requests:

* The client is from a version of MySQL older than MySQL 4.1, and thus
does not request a character set.

* The client requests a character set not known to the server. For
example, a Japanese-enabled client requests sjis when connecting to a
server not configured with sjis support.

* mysqld was started with the --skip-character-set-client-handshake
option, which causes it to ignore client character set configuration.
This reproduces MySQL 4.0 behavior and is useful should you wish to
upgrade the server without upgrading all the clients.

Several character set and collation system variables relate to a client's interaction with the server. Some of these have been mentioned in earlier sections:
Additional character set and collation system variables are involved in handling traffic for the connection between a client and the server. Every client has connection-related character set and collation system variables.
A connection is what you make when you connect to the server. The client sends SQL statements, such as queries, over the connection to the server. The server sends responses, such as result sets or error messages, over the connection back to the client. This leads to several questions about character set and collation handling for client connections, each of which can be answered in terms of system variables:
  • What character set is the statement in when it leaves the client?
    The server takes the character_set_client system variable to be the character set in which statements are sent by the client.
  • What character set should the server translate a statement to after receiving it?
    For this, the server uses the character_set_connection and collation_connection system variables. It converts statements sent by the client from character_set_client to character_set_connection (except for string literals that have an introducer such as _latin1 or _utf8). collation_connection is important for comparisons of literal strings. For comparisons of strings with column values, collation_connection does not matter because columns have their own collation, which has a higher collation precedence.
  • What character set should the server translate to before shipping result sets back to the client?
    The character_set_results system variable indicates the character set in which the server returns query results to the client. This includes result data such as column values, and result metadata such as column names.
Clients can fine-tune the settings for these variables, or depend on the defaults (in which case, you can skip the rest of this section). If you do not use the defaults, you must change the character settings for each connection to the server.
Two statements affect the connection-related character set variables as a group:
  • SET NAMES 'charset_name' [COLLATE 'collation_name']
    SET NAMES indicates what character set the client will use to send SQL statements to the server. Thus, SET NAMES 'cp1251' tells the server, future incoming messages from this client are in character set cp1251. It also specifies the character set that the server should use for sending results back to the client. (For example, it indicates what character set to use for column values if you use a SELECT statement.)
    A SET NAMES 'charset_name' statement is equivalent to these three statements:
    SET character_set_client = charset_name;
    SET character_set_results = charset_name;
    SET character_set_connection = charset_name;
    
    Setting character_set_connection to charset_name also implicitly sets collation_connection to the default collation for charset_name. It is unnecessary to set that collation explicitly. To specify a particular collation, use the optional COLLATE clause:
    SET NAMES 'charset_name' COLLATE 'collation_name'
    
  • SET CHARACTER SET charset_name
    SET CHARACTER SET is similar to SET NAMES but sets character_set_connection and collation_connection to character_set_database and collation_database. A SET CHARACTER SET charset_name statement is equivalent to these three statements:
    SET character_set_client = charset_name;
    SET character_set_results = charset_name;
    SET collation_connection = @@collation_database;
    
    Setting collation_connection also implicitly sets character_set_connection to the character set associated with the collation (equivalent to executing SET character_set_connection = @@character_set_database). It is unnecessary to set character_set_connection explicitly.
Note
ucs2 cannot be used as a client character set, which means that it does not work for SET NAMES or SET CHARACTER SET.
The MySQL client programs mysql, mysqladmin, mysqlcheck, mysqlimport, and mysqlshow determine the default character set to use as follows:
  • In the absence of other information, the programs use the compiled-in default character set, usually latin1.
  • The programs support a --default-character-set option, which enables users to specify the character set explicitly to override whatever default the client otherwise determines.
When a client connects to the server, it sends the name of the character set that it wants to use. The server uses the name to set the character_set_client, character_set_results, and character_set_connection system variables. In effect, the server performs a SET NAMES operation using the character set name.
With the mysql client, to use a character set different from the default, you could explicitly execute SET NAMES every time you start up. To accomplish the same result more easily, add the --default-character-set option setting to your mysql command line or in your option file. For example, the following option file setting changes the three connection-related character set variables set to koi8r each time you invoke mysql:
[mysql]
default-character-set=koi8r
If you are using the mysql client with auto-reconnect enabled (which is not recommended), it is preferable to use the charset command rather than SET NAMES. For example:
mysql> charset utf8
Charset changed
The charset command issues a SET NAMES statement, and also changes the default character set that mysql uses when it reconnects after the connection has dropped.
Example: Suppose that column1 is defined as CHAR(5) CHARACTER SET latin2. If you do not say SET NAMES or SET CHARACTER SET, then for SELECT column1 FROM t, the server sends back all the values for column1 using the character set that the client specified when it connected. On the other hand, if you say SET NAMES 'latin1' or SET CHARACTER SET latin1 before issuing the SELECT statement, the server converts the latin2 values to latin1 just before sending results back. Conversion may be lossy if there are characters that are not in both character sets.
If you want the server to perform no conversion of result sets or error messages, set character_set_results to NULL or binary:
SET character_set_results = NULL;
To see the values of the character set and collation system variables that apply to your connection, use these statements:
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';
You must also consider the environment within which your MySQL applications execute. See Section 10.1.5, “Configuring the Character Set and Collation for Applications”.
For more information about character sets and error messages, see Section 10.1.6, “Character Set for Error

event delegation and listener handler in javascript what its do why its required for html element

JavaScript events are the bedrock of all interactivity on web pages (I mean serious interactivity, not those dinky CSS drop-down menus). In traditional event handling you add or remove event handlers from each element as needed. However, event handlers can potentially cause of memory leaks and performance degradation — the more you have, the greater the risk. JavaScript event delegation is a simple technique by which you add a single event handler to a parent element in order to avoid having to add event handlers to multiple child elements.

How does it work?

Event delegation makes use of two often overlooked features of JavaScript events: event bubbling and the target element. When an event is triggered on an element, for example a mouse click on a button, the same event is also triggered on all of that element’s ancestors. This process is known as event bubbling; the event bubbles up from the originating element to the top of the DOM tree. The target element of any event is the originating element, the button in our example, and is stored in a property of the event object. Using event delegation it’s possible to add an event handler to an element, wait for an event to bubble up from a child element and easily determine from which element the event originated.

One of the hot methodologies in the JavaScript world is event delegation, and for good reason.  Event delegation allows you to avoid adding event listeners to specific nodes;  instead, the event listener is added to one parent.  That event listener analyzes bubbled events to find a match on child elements.  The base concept is fairly simple but many people don't understand just how event delegation works.  Let me explain the how event delegation works and provide pure JavaScript example of basic event delegation.
Let's say that we have a parent UL element with several child elements:
<ul id="parent-list">
 <li id="post-1">Item 1</li>
 <li id="post-2">Item 2</li>
 <li id="post-3">Item 3</li>
 <li id="post-4">Item 4</li>
 <li id="post-5">Item 5</li>
 <li id="post-6">Item 6</li>
</ul>
Let's also say that something needs to happen when each child element is clicked.  You could add a separate event listener to each individual LI element, but what if LI elements are frequently added and removed from the list?  Adding and removing event listeners would be a nightmare, especially if addition and removal code is in different places within your app.  The better solution is to add an event listener to the parent UL element.  But if you add the event listener to the parent, how will you know which element was clicked?
Simple:  when the event bubbles up to the UL element, you check the event object's target property to gain a reference to the actual clicked node.  Here's a very basic JavaScript snippet which illustrates event delegation:
// Get the element, add a click listener...
document.getElementById("parent-list").addEventListener("click",function(e) {
 // e.target is the clicked element!
 // If it was a list item
 if(e.target && e.target.nodeName == "LI") {
  // List item found!  Output the ID!
  console.log("List item ",e.target.id.replace("post-"),
 " was clicked!");
 }
});
Start by adding a click event listener to the parent element.  When the event listener is triggered, check the event element to ensure it's the type of element to react to.  If it is an LI element, boom:  we have what we need!  If it's not an element that we want, the event can be ignored.  This example is pretty simple -- UL and LI is a straight-forward comparison.  Let's try something more difficult.  Let's have a parent DIV with many children but all we care about is an A tag with the "classA" CSS class:
// Get the parent DIV, add click listener...
document.getElementById("myDiv").addEventListener("click",function(e) {
 // e.target was the clicked element
 if(e.target && e.target.nodeName == "A") {
  // Get the CSS classes
  var classes = e.target.className.split(" ");
  // Search for the CSS class!
  if(classes) {
   // For every CSS class the element has...
   for(var x = 0; x < classes.length; x++) {
    // If it has the CSS class we want...
    if(classes[x] == "classA") {
     // Bingo!
     console.log("Anchor element clicked!");
     
     
     // Now do something here....
     
     
     
    }
   }
  }
  
 }
});
The example above requires not only a tag match but also a CSS class match.  While this is a bit more complex, it's still fairly simple in the grand scheme of things.  For example, if the A element had a SPAN tag in it, the SPAN tag would be the target element.  In that case, we'd need to walk up the DOM tree to find out if it contained an A.classA element we were looking for.
Since most developers use a JavaScript library for their DOM element and event handling, I recommend using the library's method of event delegation, as they capable of advanced delegation and element identification.
Hopefully this helps you visually the concept behind event delegation and convinces you of delegation's power!

Imagine that you have five hundred anchor tags on your page. As you might imagine, adding a click event to every one of those would be time consuming, and unnecessary. On top of that, what happens if, once you click on those anchor tags, we add additional anchor elements to the page? Would those new anchors be bound to the click event as well? The answer is no. You would then have to reattach a listener to those newly created elements.

Enter Event Delegation

Instead, with event delegation, we simply add a single event listener to an ancestor element, maybe something like a “ul.” Then, when the user clicks on one of its child elements, like an anchor tag, we only check to see if the target of the click was, in fact, an anchor tag. If it was, we proceed per usual.
  1. $('ul').click(function(e) {  
  2.       
  3.     if ( $(e.target).is('a') ) {  
  4.         alert('clicked');  
  5.     }  
  6. });  

Advantages

  • Only attach one event listener to the page, rather than five hundred (in our example)
  • Dynamically created elements will still be bound to the event handler.

Why Does this Work?

It works because of the way elements are captured (not IE) and bubble up. For instance, consider the following simple structure.
  1. <ul>  
  2.    <li><a href="http://net.tutsplus.com/tutorials/javascript-ajax/quick-tip-javascript-event-delegation-in-4-minutes/#">Anchor</a></li>  
  3. </ul>  
When you click on the anchor tag, you’re also clicking on the ‘li’ and the ‘ul’ and even the ‘body’ element. This is referred to as bubbling up.

Notes About this Screencast

Please keep in mind that this is just a simple example to explain the functionality. We used jQuery, only because I had four minutes to record! In that particular example (watch the screencast first), we could have used two alternative options:
  1. Pass true as a parameter of the clone() method. This would then clone the element, as well as any event handlers.
  2. Use the live() method instead. However, be careful when using this method: it reattaches the event handler X times. This may not necessarily be needed.
Mostly, this was meant to demonstrate the idea. With regular JavaScript, you could do something like:
  1. // Get some unordered list, which contains anchor tags  
  2. var ul = document.getElementById('items');  
  3.   
  4. // Quick and simple cross-browser event handler - to compensate for IE's attachEvent handler  
  5. function addEvent(obj, evt, fn, capture) {  
  6.     if ( window.attachEvent ) {  
  7.         obj.attachEvent("on" + evt, fn);  
  8.     }  
  9.     else {  
  10.         if ( !capture ) capture = false// capture  
  11.         obj.addEventListener(evt, fn, capture)  
  12.     }  
  13. }  
  14.   
  15. // Check to see if the node that was clicked is an anchor tag. If so, proceed per usual.   
  16. addEvent(ul, "click"function(e) {  
  17.   // Firefox and IE access the target element different. e.target, and event.srcElement, respectively.  
  18.   var target = e ? e.target : window.event.srcElement;  
  19.   if ( target.nodeName.toLowerCase() === 'a' ) {  
  20.      alert("clicked");  
  21.      return false;  
  22.   }  
  23. });