Monday, October 15, 2007

A Visual Explanation of SQL Joins

I thought Ligaya Turmelle's post on SQL joins was a great primer for novice developers. Since SQL joins appear to be set-based, the use of Venn diagrams to explain them seems, at first blush, to be a natural fit. However, like the commenters to her post, I found that the Venn diagrams didn't quite match the SQL join syntax reality in my testing.

I love the concept, though, so let's see if we can make it work. Assume we have the following two tables. Table A is on the left, and Table B is on the right. We'll populate them with four records each.

id name       id  name
-- ---- -- ----
1 Pirate 1 Rutabaga
2 Monkey 2 Pirate
3 Ninja 3 Darth Vader
4 Spaghetti 4 Ninja

Let's join these tables by the name field in a few different ways and see if we can get a conceptual match to those nifty Venn diagrams.

SELECT * FROM TableA
INNER JOIN TableB
ON TableA.name = TableB.name

id name id name
-- ---- -- ----
1 Pirate 2 Pirate
3 Ninja 4 Ninja

Inner join produces only the set of records that match in both Table A and Table B.

Venn diagram of SQL inner join
SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name

id name id name
-- ---- -- ----
1 Pirate 2 Pirate
2 Monkey null null
3 Ninja 4 Ninja
4 Spaghetti null null
null null 1 Rutabaga
null null 3 Darth Vader

Full outer join produces the set of all records in Table A and Table B, with matching records from both sides where available. If there is no match, the missing side will contain null.

Venn diagram of SQL cartesian join

SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name

id name id name
-- ---- -- ----
1 Pirate 2 Pirate
2 Monkey null null
3 Ninja 4 Ninja
4 Spaghetti null null

Left outer join produces a complete set of records from Table A, with the matching records (where available) in Table B. If there is no match, the right side will contain null.

Venn diagram of SQL left join
SELECT * FROM TableA
LEFT OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableB.id IS null

id name id name
-- ---- -- ----
2 Monkey null null
4 Spaghetti null null

To produce the set of records only in Table A, but not in Table B, we perform the same left outer join, then exclude the records we don't want from the right side via a where clause.

join-left-outer.png
SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.name = TableB.name
WHERE TableA.id IS null
OR TableB.id IS null

id name id name
-- ---- -- ----
2 Monkey null null
4 Spaghetti null null
null null 1 Rutabaga
null null 3 Darth Vader

To produce the set of records unique to Table A and Table B, we perform the same full outer join, then exclude the records we don't want from both sides via a where clause.

join-outer.png

There's also a cartesian product or cross join, which as far as I can tell, can't be expressed as a Venn diagram:

SELECT * FROM TableA
CROSS JOIN TableB

This joins "everything to everything", resulting in 4 x 4 = 16 rows, far more than we had in the original sets. If you do the math, you can see why this is a very dangerous join to run against large tables.

Thursday, October 11, 2007

Understanding Domain Name System (DNS)

Domain Name System (DNS) makes it possible to refer to Internet Protocol (IP) based systems (hosts) by human-friendly names (domain names). Name Resolution is the act of determining the IP address (or addresses) of a given host name.

Benefits of DNS
  • Domain names can be logical and easily remembered.
  • Should the IP address for a host change, the domain name can still resolve transparently to the user or application.
The structure of Domain Names
  • Domain names are separated by dots, with the topmost element on the right. Eg: www.yahoo.com . IP addresses have topmost element on the left.
  • Each element may be up to 63 characters long. The entire name may be atmost 255 characters long.
  • The right most element in a domain name is called the Top-Level Domain (TLD). Referring the above example (www.yahoo.com), TLD is 'com'.
  • If a domain name is not shortened, it is called the Fully Qualified Domain Name (FQDN). For example, briefcase.yahoo.com can be specified by a machine in the yahoo.com domain as either briefcase.yahoo.com (FQDN) or as briefcase.
Host names map to IP addresses in a many-to-many relationship. A host name may have one or more IP addresses. Conversely, an IP address may have multiple host names associated with it.

Hosts that are designed to perform email routing are known as mail exchangers. These machines should have special purpose records in DNS called Mail eXchanger (MX) records. When a SMTP server or mail server, needs to send mail to a remote domain, it does a DNS lookup for the Mail Exchanger (MX) of that remote domain. A domain can and should have multiple mail exchangers. Mail that cannot be sent to one mail exchanger, can instead be delivered to an alternative server, thus providing failsafe redundancy.

Different types of Domain Name Servers
  1. Root Name server - Each top level domain (such as in,edu,com etc) has one or more root name servers which are responsible for determining where the individual records are held. These servers are fairly static and every machine on the internet has the capability of reaching any of them. A root name server is allocated like just one to three per country. For example, India has only 2 root name servers.
  2. Authoritative Name Servers - These are the servers that the Root name servers sent queries to. These servers hold the actual information on an individual domain. This information is stored in a file called a zone file. Zone files are updated versions of the original HOSTS.TXT file.
  3. Resolving Name Server - These are the servers that do most of the work when you are trying to get to a machine with a certain host name. Besides being responsible for looking up data, they also temporarily store the data for host names that they have searched out in a cache, which allows them to speed up the resolution for host names that are frequently visited.
Zone
A zone keeps the information about the domain database. It does this by maintaining two types of files:
Zone File - It is used to map host names to address, to identify the mail servers, and to provide other domain information.
Reverse Zone File - This file is responsible for mapping IP address to host names, which is exactly the opposite of what the zone file does.

Note: The zone file and the reverse zone file has to be maintained by the user.

Name Server Hierarchy
Master Name Server - Also called primary server. This contains the master copy of data for a zone.
Slave Name Server - Also known as secondary server. This provides a backup to the master name server. All slave servers maintain synchronization with their master name server.
A zone may have multiple slave servers. But there will be only one master name server per zone.

Apache : Name-based Vs IP Based Virtual Hosting

Often when, you attend interviews for network administration related jobs , the one question you may encounter while discussing about web servers is the difference between name-based and IP based virtual hosting. Here I will explain the difference between the two.

In IP-based virtual hosting, you are running more than one web site on the same server machine, but each web site has its own IP address. In order to do this, you have to first tell your operating system about the multiple IP addresses. See here configuring multiple IP addresses on a single NIC . You also need to put each IP in your DNS, so that it will resolve to the names that you want to give those addresses .

In Name-based virtual hosting, you host multiple websites on the same IP address. But for this to succeed, you have to put more than one DNS record for your IP address in the DNS database. This is done using CNAME tag in BIND. You can have as many CNAME(s) as you like pointing to a particular machine. Of course, you also have to uncomment the NameVirtualHost section in httpd.conf file and point it to the IP address of your machine.

#FILE: httpd.conf
...
NameVirtualHost 192.168.0.1
...

Setting up multiple IP addresses on a single NIC

In linux, you can bind multiple IP addresses on a single NIC. This is usually done in case you are using your linux machine as a webserver and is hosting multiple domains and you want to bind each domain to a unique IP address. This is how it is done.
Let us assume that you already have a NIC which is bound with a static IP address. Then you will have a file called /etc/sysconfig/network-scripts/ifcfg-eth0 .My ifcfg-eth0 file has the following entries:
# File: ifcfg-eth0
DEVICE=eth0
ONBOOT=yes
BOOTPROTO=static
IPADDR=192.168.0.1
NETMASK=255.255.255.0
BROADCAST=192.168.0.255
NETWORK=192.168.0.0
HWADDR=00:80:48:34:C2:84
Now to bind another IP address to the same NIC, I create a copy of the above file ifcfg-eth0 and name it as ifcfg-eth0:1
# cd /etc/sysconfig/networking-scripts
# cp ifcfg-eth0 ifcfg-eth0:1
Now just change the values of the DEVICE and IPADDR in the file as follows:
# File: ifcfg-eth0:1
DEVICE=eth0:1
ONBOOT=yes
BOOTPROTO=static
IPADDR=192.168.0.5
NETMASK=255.255.255.0
BROADCAST=192.168.0.255
NETWORK=192.168.0.0
HWADDR=00:80:48:34:C2:84
And lastly, restart the networking service. If you are using RedHat, then it is as simple as :
# service network restart

How to install a Network card in linux

There are different ways of installing a network card in linux - and that too depending on the linux distribution that you are using. I will explain each one of these methods here.
1) The Manual method
First open the computer case and insert the network card into an empty PCI slot. Then boot up your machine to load linux. In linux login as root and then navigate to the directory /lib/modules/kernel_version_number/net/ . Here you will find the modules supported by your system. Assuming that you have a 3Com ethernet card, in which case, the module name is 3c59x , you have to add this in the /etc/modules.conf file to let the machine detect the card each time the machine boots.
#File: /etc/modules.conf
alias eth0 3c59x
Note: If you have only one network card, it is known by the name eth0, the succeeding network cards in your computer go by the name eth1, eth2 ... and so on.
Now you have to load the module into the kernel.
root# /sbin/insmod -v 3c59x
Next configure an IP address for the network card using ifconfig or netconfig or any other method if your machine gets its IP address from a DHCP server. Eg:
root# ifconfig eth0 192.168.1.5 netmask 255.255.255.0 broadcast 192.168.1.255
2) The Easy way
RedHat/Fedora distributions of linux ships with Kudzu a device detection program which runs during systems initialization (/etc/rc.d/init.d/kudzu). This can detect a newly installed NIC and load the appropriate driver. Then use the program /usr/sbin/netconfig to configure the IP address and network settings. The configuration will be stored so that it will be utilized upon system boot.


How to Assign an IP address

Computers may be assigned a static IP address or assigned one dynamically (via DHCP). Here I will explain the steps needed to assign an IP address to your NIC.
Choose one of the following methods:

=> Dynamic Host Configuration Protocol (DHCP) is a protocol used by networked computers (clients) to obtain IP addresses and other parameters such as the default gateway, subnet mask, and IP addresses of DNS servers from a DHCP server.
Command line :
/sbin/ifconfig eth0 192.168.1.3 netmask 255.255.255.0 broadcast 192.168.1.255
GUI tool : You can use the GUI tool /usr/bin/neat - Gnome GUI network administration tool. It handles all interfaces and configures for both static assignment as well as dynamic assignment using DHCP.

Console tool : /usr/sbin/netconfig (Only seems to work for the first network interface eth0 but not eth1,...)

The ifconfig command does NOT store this information permanently. Upon reboot this information is lost. (Manually add the commands to the end of the file /etc/rc.d/rc.local to execute them upon boot.) The command netconfig and /usr/bin/neat make permanent changes to system network configuration files located in /etc/sysconfig/network-scripts/ , so that this information is retained.
The Red Hat configuration tools store the configuration information in the file /etc/sysconfig/network. They will also allow one to configure routing information.
# File: /etc/sysconfig/network
# Static IP address Configuration:
NETWORKING=yes
HOSTNAME=my-hostname # Hostname is defined here and by command hostname
FORWARD_IPV4=true # True for NAT firewall gateways and linux routers. False for
# everyone else - desktops and servers.
GATEWAY="XXX.XXX.XXX.YYY" # Used if your network is connected to another
# network or the internet.

# Gateway not defined here for DHCP.

# Or for DHCP configuration: in the same file /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=my-hostname # Hostname is defined here and by command hostname
# Gateway is assigned by DHCP.
# File: /etc/sysconfig/network-scripts/ifcfg-eth0
# Static IP address configuration:
DEVICE=eth0
BOOTPROTO=static
BROADCAST=XXX.XXX.XXX.255
IPADDR=XXX.XXX.XXX.XXX
NETMASK=255.255.255.0
NETWORK=XXX.XXX.XXX.0
ONBOOT=yes
# OR for DHCP configuration:
DEVICE=eth0
ONBOOT=yes
BOOTPROTO=dhcp
Used by script /etc/sysconfig/network-scripts/ifup to bring the various network interfaces on-line.
To disable DHCP change BOOTPROTO=dhcp to BOOTPROTO=none
In order for updated information in any of these files to take effect, one must issue the command:
root# service network restart

Tuesday, July 31, 2007

The Future of The Web

The next-generation Net won’t just be more portable and personal. It’ll also harness the power of people, making it even easier to zero in on precisely what you’re looking for.

The web’s most common method of interaction is the tried-and-true link: “Click here and we’ll show you a different page.” But the future of online will be fast, two-way communication, the roots of which are beginning to take hold. New technologies will soon give us speedy, uninterrupted access to the Web wherever we wander. We’ll see innovative Web applications that allow us to access information anywhere and work seamlessly with colleagues around the globe. People will gain more power online—rather than simply reading the news, they’ll be able to go out and uncover some stories of their own. And new sites and services will offer information targeted precisely to your needs, rendering one-size-fits-all sites obsolete.

The Web Gets Down to Work

New web services—ones that mimic desktop applications but work entirely within a browser window—appear constantly. But the Web apps you’ll eventually use will focus on productivity and mobility, instead of simply giving you the same functions you’d find in a desktop application.

“Web applications are terrific for situations where you want to share and collaborate,” says Google product manager Bret Taylor. “That’s where we see the most benefit: for consumers planning the annual family reunion or a group of colleagues putting together a sales proposal.”

Brandon Schauer, design strategist for Web consulting firm Adaptive Path, says the next phase of Web applications will focus on practical uses: “things that the rest of the world might have a reason to interact with, not just the Generation Y people who have time to click around,” he says.

One business-focused Web application, Coghead, has been in development since 2003 and is likely to launch soon. It’s a beefy-looking app that allows nonprogrammers to build their own custom applications for tasks like inventory control, with data stored entirely online. Coghead CEO Paul McNamara says the application will be aimed at small to medium-size businesses, and at people who have some level of technical ability—“people who do macros in Microsoft Excel, work in Microsoft Access, or Adobe Dream-weaver,” explains McNamara.

Another Web application that reflects that trend toward productivity is weSpendMoney. It’s one of the first offerings to store users’ financial data exclusively online, unlike more traditional desktop applications. Pedro Sousa, one of the developers, says that future versions of the application will allow users to view their data on the tiny screens of Web-enabled cell phones, too.

A focus on mobility is a common theme among Web apps. “At some point, applications as advanced as Google Earth will be able to run on devices as small as a cell phone,” says Google’s Taylor. “Users will be able to search and collaborate more effectively no matter where they are.”

Another category that will gain in popularity is what Adaptive Path’s Schauer calls “workarounds.” Examples include Kayak.com, a site that uses a Web app to help people deal with the aggravation of shopping for airline tickets, and VideoEgg, which compresses video via a plug-in, thereby skirting poky uploads caused by slow upstream connections.

Social networking sites like MySpace are huge, but sites that aren’t purely social will use people connections to solve problems. Schauer says sites that use social networks in this way “plug into what the Web has always been great at, which is getting you together with people who share the same interests but may be miles away.” Examples include Last.fm and Pandora, which ascertain your musical preferences and play songs from additional artists you might like. These sites also let you find and play “stations” that have been created by others. Another similar site is Soundflavor.

Search Engines With Real Savvy

Today most search engines depend primarily on algorithmic processing: results that are ordered by popularity. But better systems are beginning to supplement the blunt-force approach. “We want to do a better job of understanding the user’s intent and the content provider’s intentions,” says Peter Norvig, director of research for Google. “We mostly rely on matching keywords, but we’d like to get closer to matching the intent.”

Microsoft is another company investing heavily in research on search technology. “We’re working on all kinds of things that will go away from ‘here’s ten links on a page,’” says Adam Sohn, a director in Microsoft’s online services group, which is responsible for the Windows Live portal. “If someone is searching for ‘Jaguar,’ he explains, “the smarts to distinguish between ‘he’s looking for a car and ‘a big cat in the jungle’—that’s coming.”

Search engines can also deliver improved, more personalized results by adding better sources of information. “A search engine would be very good at telling me who won the FIFA World Cup, but bad at telling who’s the best nanny in the neighborhood,” says Sohn. So search engines are adding social networking features for sharing information within small groups.

Social-network searching will extend to other areas, too. Sohn says most video sites encourage the people who upload clips and those who view them to add tags. “Over time, especially with video, there will be this social input, where people add tags to other people’s video. Then you get this sort of community-reinforced set of searchable attributes.”

Soliciting input will also help provide searchers with more personalized results. Norvig says Google should do a better job of helping people use the search engine the way it is by offering proactive suggestions—for example, “It looks like you’re trying to do this kind of search; here’s how you do it.” Sohn says Microsoft is building two-way feedback mechanisms that will ask users how useful they found the search result.

Both Norvig and Sohn agree that one issue search engines will be addressing is how to present search results. Most search sites have many sections drawing on separate databases. “[We have] one look for Web sites, one for news, one for images,” Norvig says of Google’s site. “We want to find a way to combine all of that information.” Microsoft’s Sohn uses the example of combining results from Windows Live’s QnA (question and answer) section with its main search section. “We need to build the connection between the two services. It’s not a multiyear thing; it’s in the next 12 to 18 months.”

New Clout for Everyday People

Even with throttled bandwidth, people are uploading 65,000 new videos to YouTube each day. More than 52 million blogs are covering everything from the best burger in Bangalore to the latest finance scandal. Think that’s impressive? Amateurs will find new venues that will give them even greater influence.

Jay Rosen, an associate professor of journalism at New York University and writer of the PressThink blog, says that amateur and professional journalists can work together to produce some-thing greater than either could produce separately. “Bloggers are good at filtering and organizing information,” he says.

“Sometimes they get involved in [reporting on] things, but often it’s accidental. They’re collating what’s out there.” NewAssignment.net, combines the efforts of amateurs and professionals. Members will suggest, debate, and research stories; professional reporters will complete selected stories.

The Web will continue to reshape itself to serve not just professionals and geeks but everyone, whether they have an opinion, a gripe, or simply a job that needs to be done.