DNS: Setup bind9 DNS Server in chroot environment

– Install Bind
# yum -y install bind bind-chroot bind-libs bind-utils caching-nameserver

-Confgure Permision
# chmod 755 /var/named/
# chmod 775 /var/named/chroot/
# chmod 775 /var/named/chroot/var/
# chmod 775 /var/named/chroot/var/named/
# chmod 775 /var/named/chroot/var/run/
# chmod 777 /var/named/chroot/var/run/named/
# cd /var/named/chroot/var/named/
# ln -s ../../ chroot
# cp /usr/share/doc/bind-9.3.6/sample/var/named/named.local /var/named/chroot/var/named/named.local
# cp /usr/share/doc/bind-9.3.6/sample/var/named/named.root /var/named/chroot/var/named/named.root
# touch /var/named/chroot/etc/named.conf

– Setting RNDC
# cd /var/named/chroot/etc
# rndc-confgen > rndc.conf
# chown root:named rndc.conf

– Edit File rndc.key:
# nano /var/named/chroot/etc/rndc.key

# Start of rndc.conf
key "rndckey" {
algorithm hmac-md5;
secret "QXkk0JXZDrgi0dJ0DrETKQ==";
};

#options {
# default-key “rndckey”;
# default-server 127.0.0.1;
# default-port 953;
#};
# End of rndc.conf

# Use with the following in named.conf, adjusting the allow list as needed:
# key “rndckey” {
# algorithm hmac-md5;
# secret “xwINl5E9kGDva0PcJWCZjQ==”;
# };
#
# controls {
# inet 127.0.0.1 port 953
# allow { 127.0.0.1; } keys { “rndckey”; };
# };
# End of named.conf

– Configure /var/named/chroot/etc/named.conf

# nano /var/named/chroot/etc/named.conf


// Red Hat BIND Configuration Tool
// Default initial "Caching Only" name server configuration
// we include the rndckey (copy-paste from rndc.key created earlier)
// include "/var/named/chroot/etc/rndc.key";
key "rndckey" {
algorithm hmac-md5;
secret "QXkk0JXZDrgi0dJ0DrETKQ==";
};

// assume our server has the IP 192.168.0.11 serving the 192.168.0.0/24 subnet
controls {
inet 127.0.0.1 allow { 127.0.0.1; } keys { “rndckey”; };
inet 192.168.0.250 allow { 192.168.0.0/24; } keys { “rndckey”; };
};

options {
directory “/var/named”;
pid-file “/var/run/named/named.pid”;

recursion yes;

allow-recursion {
127.0.0.1;
192.168.0.0/24;
};

// these are the opendns servers (optional)
forwarders {
202.134.0.155;
203.130.193.74;
8.8.8.8;
8.8.4.4;
};

listen-on {
127.0.0.1;
192.168.0.250;
};

/*
* If there is a firewall between you and nameservers you want
* to talk to, you might need to uncomment the query-source
* directive below. Previous versions of BIND always asked
* questions using port 53, but BIND 8.1 uses an unprivileged
* port by default.
*/
// query-source address * port 53;

// so people can’t try to guess what version you’re running
version “REFUSED”;

allow-query {
127.0.0.1;
192.168.0.0/24;
};
};

server 192.168.0.250 {
keys { rndckey; };
};

zone “.” IN {
type hint;
file “named.ca”;
};

//forward zone
zone “example.net” IN {
type master;
file “data/example.net.zone”;
allow-update { none; };
// we assume we have a slave dns server with the IP 192.168.0.251
allow-transfer { 192.168.0.251; };
};

//reserve zone
zone “0.168.192.in-addr.arpa” IN {
type master;
file “data/192.168.0.zone”;
allow-update { none; };
// we assume we have a slave dns server with the IP 192.168.0.251
allow-transfer { 192.168.0.251; };
};

-Setting Forward Lookup Zone
# cd /var/named/chroot/var/named/data/
# touch example.net.zone
# nano example.net.zone

-Edit File example.net.zone:

$ttl 38400
example.net. IN SOA ns.example.net. admin.example.net. (
2012011501 ; Serial
10800 ; Refresh after 3 hours
3600 ; Retry after 1 hour
604800 ; Expire after 1 week
86400 ) ; Minimum TTL of 1 day
;
example.net. IN NS ns.example.net.
ns.example.net. IN A 192.168.0.250

– Setting Reverse Lookup Zone
# cd /var/named/chroot/var/named/data/
# touch 192.168.0.zone
# nano 192.168.0.zone

-Edit File 192.168.0.zone :

$TTL 86400
0.168.192.in-addr.arpa. IN SOA ns.example.net. admin.example.net. (
2012011502
10800
900
604800
3600 )

0.168.192.in-addr.arpa. IN NS ns.example.net.-Restart Bind Service & Setting run level

# service named restart
# chkconfig named on

-Make sure it’s running :
# rndc status

If it is not running link /etc/rndc.conf with /var/named/chroot/etc/rndc.conf and restart named

-Edit /etc/resolv.conf & Restart service
# nano /etc/resolv.conf

search example.net
nameserver 127.0.0.1
nameserver 192.168.0.250
nameserver 192.168.0.251

-Testing DNS Query:
# nslookup google.com

Advertisement

Perform SSH Login Without Password Using ssh-keygen

you probably log in and out of a half dozen remote servers (or these days, local virtual machines) on a daily basis. And if you’re even more like me, you have trouble remembering all of the various usernames, remote addresses and command line options for things like specifying a non-standard connection port or forwarding local ports to the remote machine.

Shell Aliases

Let’s say that you have a remote server named dev.example.com, which hasnot been set up with public/private keys for password-less logins. The username to the remote account is fooey, and to reduce the number of scripted login attempts, you’ve decided to change the default SSH port to 2200 from the normal default of 22. This means that a typical command would look like:

$ ssh fooey@dev.example.com -p 22000
password: *************

Not too bad.

We can make things simpler and more secure by using a public/private key pair; I highly recommend using ssh-copy-id for moving your public keys around. It will save you quite a few folder/file permission headaches.

$ ssh fooey@dev.example.com -p 22000
# Assuming your keys are properly setup…

Now this doesn’t seem all that bad. To cut down on the verbosity you could create a simple alias in your shell as well:

$ alias dev=’fooey@dev.example.com -p 22000′
$ # To connect:
$ ssh dev

This works surprisingly well: Every new server you need to connect to, just add an alias to your .bashrc (or .zshrc if you hang with the cool kids), and voilà.

~/.ssh/config

However, there’s a much more elegant and flexible solution to this problem. Enter the SSH config file:

# contents of $HOME/.ssh/config
Host dev
    HostName dev.example.com
    Port 22000
    User fooey

This means that I can simply  $ ssh dev, and the options will be read from the configuration file. Easy peasy. Let’s see what else we can do with just a few simple configuration directives.

Personally, I use quite a few public/private keypairs for the various servers and services that I use, to ensure that in the event of having one of my keys compromised the dammage is as restricted as possible. For example, I have a key that I use uniquely for my github account. Let’s set it up so that that particular private key is used for all my github-related operations:

Host dev
    HostName dev.example.com
    Port 22000
    User fooey

Host github.com
    IdentityFile ~/.ssh/github.key

The use of IdentityFile allows me to specify exactly which private key I wish to use for authentification with the given host. You can, of course, simply specify this as a command line option for “normal” connections:

 $ ssh -i ~/.ssh/blah.key username@host.com

but the use of a config file with IdentityFile is pretty much your only optionif you want to specify which identity to use for any git commands. This also opens up the very interesting concept of further segmenting your github keys on something like a per-project or per-organization basis:

Host github-project1
    User git
    HostName github.com
    IdentityFile ~/.ssh/github.project1.key

Host github-org
    User git
    HostName github.com
    IdentityFile ~/.ssh/github.org.key

Host github.com
    User git
    IdentityFile ~/.ssh/github.key

Which means that if I want to clone a repository using my organization credentials, I would use the following:

$ git clone git@github-org:orgname/some_repository.git

Going further

As any security-conscious developer would do, I set up firewalls on all of my servers and make them as restrictive as possible; in many cases, this means that the only ports that I leave open are 80/443 (for webservers), and port 22 for SSH (or whatever I might have remapped it to for obfuscation purposes). On the surface, this seems to prevent me from using things like a desktop MySQL GUI client, which expect port 3306 to be open and accessible on the remote server in question. The informed reader will note, however, that a simple local port forward can save you:

$ ssh -f -N -L 9906:127.0.0.1:3306 coolio@database.example.com
# -f puts ssh in background 
# -N makes it not execute a remote command

This will forward all local port 9906 traffic to port 3306 on the remotedev.example.com server, letting me point my desktop GUI to localhost (127.0.0.1:9906) and have it behave exactly as if I had exposed port 3306 on the remote server and connected directly to it.

Now I don’t know about you, but remembering that sequence of flags and options for SSH can be a complete pain. Luckily, our config file can help alleviate that:

Host tunnel
    HostName database.example.com
    IdentityFile ~/.ssh/coolio.example.key
    LocalForward 9906 127.0.0.1:3306
    User coolio

Which means I can simply do:

$ ssh -f -N tunnel

And my local port forwarding will be enabled using all of the configuration directives I set up for the tunnel host. Slick.

Homework

There are quite a few configuration options that you can specify in~/.ssh/config, and I highly suggest consulting the online documentation or the ssh_config man page. Some interesting/useful things that you can do include: change the default number of connection attempts, specify local environment variables to be passed to the remote server upon connection, and even the use of * and ? wildcards for matching hosts.

I hope that some of this is useful to a few of you. Leave a note in the comments if you have any cool tricks for the SSH config file; I’m always on the lookout for fun hacks.

GIT: Setting up a remote repository and doing an initial “push”

There is a great deal of documentation and many posts on Git out there, so this is more of a note to self as I keep forgetting the steps needed to set up a remote repository and doing an initial “push”.

So, firstly setup the remote repository:

ssh git@example.com
mkdir my_project.git
cd my_project.git
git init --bare
git-update-server-info # If planning to serve via HTTP
exit

On local machine:

cd my_project
git init
git add *
git commit -m "My initial commit message"
git remote add origin git@example.com:my_project.git
git push -u origin master

Done!

Team members can now clone and track the remote repository using the following:

git clone git@example.com:my_project.git
cd my_project

Bonus

To have your terminal prompt display what branch you are currently on in green, add the following to your ~/.bash_profile:

function parse_git_branch_and_add_brackets {
  git branch --no-color 2> /dev/null | sed -e '/^[^*]/d' -e 's/* \(.*\)/\ \[\1\]/'
}
PS1="\h:\W \u\[33[0;32m\]\$(parse_git_branch_and_add_brackets) \[33[0m\]\$ "

GIT: Pushing changes to remote repo

You can simply convert your remote repository to bare repository ( There is no working copy in the bare repository – the folder contains only the actual repository data ) .

Execute following command in your remote repository folder:

git config --bool core.bare true 

Then delete all the files except .git in that folder. and then you will be able to perform git push to the remote repository without any errors.

GIT: Common Aliases

Common Aliases

git statusgit addgit commit, and git checkout are such common commands that it is useful to have abbreviations for them.

Add the following to the .gitconfig file in your $HOME directory.

FILE: .gitconfig

[alias]
  co = checkout
  ci = commit
  st = status
  br = branch
  hist = log --pretty=format:\"%h %ad | %s%d [%an]\" --graph --date=short
  type = cat-file -t
  dump = cat-file -p

Python: Returning Rows as Dicts in MySQLdb

Do you want to return a row of data from your database as a dict rather than a tuple? Using MySQLdb? Then here’s how you do it. The traditional way to retrieve a row from a table is;

>>> import MySQLdb
>>> myDb = MySQLdb.connect(user=’andy47′, passwd=’password’, db=’db_name’)
>>> myCurs = myDb.cursor()
>>> myCurs.execute(“SELECT columna, columnb FROM tablea”)
>>> firstRow = myCurs.fetchone()
>>> firstRow
(‘first value’, ‘second value’)

But using the DictCursor cursorclass we get;

>>> import MySQLdb
>>> import MySQLdb.cursors
>>> myDb = MySQLdb.connect(user=’andy47′, passwd=’password’, db=’db_name’, cursorclass=MySQLdb.cursors.DictCursor)
>>> myCurs = myDb.cursor()
>>> myCurs.execute(“SELECT columna, columnb FROM tablea”)
>>> firstRow = myCurs.fetchone()
{‘columna’:’first value’, ‘columnb’:’second value’}

Note the extra import at the beginning, without it Python doesn’t know about MySQLdb’s cursors module. You should also be wary of guessing the order that the items in each row of the DictCursor are returned in. There is no guarantee, for instance, that “columna” will always be the first key and “columnb” the second. When using the DictCursor you should always access values directly by their key. When using the normal cursor class you can access the returned values in sequence (make sure never to use “SELECT *”) as that’s the only way you can associate a value with the column it has come from.

Python: Mysql Database Select, Inset and Update

_mysql module

The _mysql module implements the MySQL C API directly. It is not compatible with the Python DB API interface. Generally, the programmers prefer the object oriented MySQLdb module. We will concern ourself with the latter module. Here we present only one small example with the _mysql module.

#!/usr/bin/python
# -*- coding: utf-8 -*-

import _mysql
import sys


con = None

try:

    con = _mysql.connect('localhost', 'testuser', 
        'test623', 'testdb')
        
    con.query("SELECT VERSION()")
    result = con.use_result()
    
    print "MySQL version: %s" % \
        result.fetch_row()[0]
    
except _mysql.Error, e:
  
    print "Error %d: %s" % (e.args[0], e.args[1])
    sys.exit(1)

finally:
    
    if con:
        con.close()

The example will get and print the version of the MySQL database. For this, we use the SELECT VERSION() SQL statement.

MySQLdb module

MySQLdb is a thin Python wrapper around _mysql. It is compatible with the Python DB API, which makes the code more portable. Using this model is the preferred way of working with the MySQL.

First example

In the first example, we will get the version of the MySQL database.

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb
import sys

con = None

try:

    con = mdb.connect('localhost', 'testuser', 
        'test623', 'testdb');

    cur = con.cursor()
    cur.execute("SELECT VERSION()")

    data = cur.fetchone()
    
    print "Database version : %s " % data
    
except mdb.Error, e:
  
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)
    
finally:    
        
    if con:    
        con.close()

In this script, we connect to the testdb database and execute the SELECT VERSION()statement. This will return the current version of the MySQL database. We print it to the console.

import MySQLdb as mdb

We import the MySQLdb module.

con = None

We initialize the con variable to None. In case we could not create a connection to the database (for example the disk is full), we would not have a connection variable defined. This would lead to an error in the finally clause.

con = mdb.connect('localhost', 'testuser', 
    'test623', 'testdb');

We connect to the database. The connect() method has four parameters. The first parameter is the host, where the MySQL database is located. In our case it is a localhost, e.g. our computer. The second parameter is the database user name. It is followed by the user’s account password. The final parameter is the database name.

cur = con.cursor()
cur.execute("SELECT VERSION()")

From the connection, we get the cursor object. The cursor is used to traverse the records from the result set. We call the execute() method of the cursor and execute the SQL statement.

data = cur.fetchone()

We fetch the data. Since we retrieve only one record, we call the fetchone()method.

print "Database version : %s " % data

We print the data that we have retrieved to the console.

except mdb.Error, e:
  
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)

We check for errors. This is important, since working with databases is error prone.

finally:    
        
    if con:    
        con.close()

In the final step, we release the resources.

$ ./version.py
Database version : 5.5.9 

The output might look like the above.

Creating and populating a table

We create a table and populate it with some data.

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb
import sys

con = mdb.connect('localhost', 'testuser', 'test623', 'testdb');

with con:
    
    cur = con.cursor()
    cur.execute("CREATE TABLE IF NOT EXISTS \
        Writers(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(25))")
    cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Emile Zola')")
    cur.execute("INSERT INTO Writers(Name) VALUES('Truman Capote')")

We create a Writers table and add five authors to it.

cur.execute("CREATE TABLE IF NOT EXISTS \
    Writers(Id INT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(25))")

This SQL statement creates a new database table called Writers. It has two columns. Id and Name.

cur.execute("INSERT INTO Writers(Name) VALUES('Jack London')")
cur.execute("INSERT INTO Writers(Name) VALUES('Honore de Balzac')")
...

We use the INSERT statement to insert authors to the table. Here we add two rows.

mysql> SELECT * FROM Writers;
+----+-------------------+
| Id | Name              |
+----+-------------------+
|  1 | Jack London       |
|  2 | Honore de Balzac  |
|  3 | Lion Feuchtwanger |
|  4 | Emile Zola        |
|  5 | Truman Capote     |
+----+-------------------+
5 rows in set (0.00 sec)

After executing the script, we use the mysql client tool to select all data from the Writers table.

Retrieving data

Now, that we have inserted some data into the database, we want to get it back.

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb
import sys


con = mdb.connect('localhost', 'testuser', 
        'test623', 'testdb');

with con: 

    cur = con.cursor()
    cur.execute("SELECT * FROM Writers")

    rows = cur.fetchall()

    for row in rows:
        print row

In this example, we retrieve all data from the Writers table.

cur.execute("SELECT * FROM Writers")

This SQL statement selects all data from the Writers table.

rows = cur.fetchall()

The fetchall() method gets all records. It returns a result set. Technically, it is a tuple of tuples. Each of the inner tuples represent a row in the table.

for row in rows:
    print row

We print the data to the console, row by row.

$ ./retrieve.py
(1L, 'Jack London')
(2L, 'Honore de Balzac')
(3L, 'Lion Feuchtwanger')
(4L, 'Emile Zola')
(5L, 'Truman Capote')

This is the output of the example.


Returning all data at a time may not be feasible. We can fetch rows one by one.

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb
import sys


con = mdb.connect('localhost', 'testuser', 
    'test623', 'testdb');

with con:

    cur = con.cursor()
    cur.execute("SELECT * FROM Writers")

    numrows = int(cur.rowcount)

    for i in range(numrows):
        row = cur.fetchone()
        print row[0], row[1]

We again print the data from the Writers table to the console. This time, we fetch the rows one by one.

numrows = int(cur.rowcount)

Here we determine the number of rows returned by the SQL statement.

for i in range(numrows):
    row = cur.fetchone()
    print row[0], row[1]

We fetch the rows one by one using the fetchone() method.

$ ./retrieve2.py
1 Jack London
2 Honore de Balzac
3 Lion Feuchtwanger
4 Emile Zola
5 Truman Capote

Output of the example.

The dictionary cursor

There are multiple cursor types in the MySQLdb module. The default cursor returns the data in a tuple of tuples. When we use a dictionary cursor, the data is sent in a form of Python dictionaries. This way we can refer to the data by their column names.

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb
import sys

con = mdb.connect('localhost', 'testuser', 
    'test623', 'testdb')

with con:

    cur = con.cursor(mdb.cursors.DictCursor)
    cur.execute("SELECT * FROM Writers")

    rows = cur.fetchall()

    for row in rows:
        print "%s %s" % (row["Id"], row["Name"])

In this example, we print the contents of the Writers table using the dictionary cursor.

cur = con.cursor(mdb.cursors.DictCursor)

We use the DictCursor dictionary cursor.

rows = cur.fetchall()

We fetch all data.

for row in rows:
        print "%s %s" % (row["Id"], row["Name"])

We refer to the data by column names of the Writers table.

Column headers

Next we will show, how to print column headers with the data from the database table.

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb
import sys


con = mdb.connect('localhost', 'testuser', 
    'test623', 'testdb')

with con:

    cur = con.cursor()
    cur.execute("SELECT * FROM Writers")

    rows = cur.fetchall()

    desc = cur.description

    print "%s %3s" % (desc[0][0], desc[1][0])

    for row in rows:    
        print "%2s %3s" % row

Again, we print the contents of the Writers table to the console. Now, we include the names of the columns too. The column names are considered to be the ‘meta data’. It is obtained from the cursor object.

desc = cur.description

A Cursor Object’s description attribute returns information about each of the result columns of a query.

print "%s %3s" % (desc[0][0], desc[1][0])

Here we print and format the table column names.

for row in rows:    
    print "%2s %3s" % row

And here, we traverse and print the data.

$ ./columnheaders.py
Id Name
 1 Jack London
 2 Honore de Balzac
 3 Lion Feuchtwanger
 4 Emile Zola
 5 Truman Capote

Ouput of the script.

Prepared statements

Now we will concern ourselves with prepared statements. When we write prepared statements, we use placeholders instead of directly writing the values into the statements. Prepared statements increase security and performance. The Python DB API specification suggests 5 different ways how to build prepared statements. MySQLdb module supports one of them, the ANSI printf format codes.

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb
import sys


con = mdb.connect('localhost', 'testuser', 
    'test623', 'testdb')
    
with con:    

    cur = con.cursor()
        
    cur.execute("UPDATE Writers SET Name = %s WHERE Id = %s", 
        ("Guy de Maupasant", "4"))        
    
    print "Number of rows updated: %d" % cur.rowcount

We change the name of an author on the fourth row.

cur.execute("UPDATE Writers SET Name = %s WHERE Id = %s", 
    ("Guy de Maupasant", "4"))   

This is the way to do it. We use the two %s placeholders. Before the SQL statement is executed, the values are bound to their placeholders.

mysql> SELECT Name FROM Writers WHERE Id=4;
+------------------+
| Name             |
+------------------+
| Guy de Maupasant |
+------------------+
1 row in set (0.00 sec)

The author on the fourth row was successfully changed.

Writing images

Some people prefer to put their images into the database, some prefer to keep them on the file system for their applications. Technical difficulties arise when we work with millions of images. Images are binary data. MySQL database has a special data type to store binary data called BLOB (Binary Large Object).

mysql> CREATE TABLE Images(Id INT PRIMARY KEY AUTO_INCREMENT, Data MEDIUMBLOB);
Query OK, 0 rows affected (0.06 sec)

For this example, we create a new table called Images.

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb
import sys

try:
    fin = open("chrome.png")
    img = fin.read()
    fin.close()

except IOError, e:

    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)

 
try:
    conn = mdb.connect(host='localhost',user='testuser',
       passwd='test623', db='testdb')
    cursor = conn.cursor()
    cursor.execute("INSERT INTO Images SET Data='%s'" % \
        mdb.escape_string(img))

    conn.commit()

    cursor.close()
    conn.close()

except mdb.Error, e:
  
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)

In the above script, we read a png image and insert it into the Images table.

fin = open("chrome.png")
img = fin.read()

We open and read an image. The read() function returns the data as string.

cursor.execute("INSERT INTO Images SET Data='%s'" % \
    mdb.escape_string(img))

This string data is inserted into the table. Before doing so, it is processed by theescape_string() method. It escapes a string for use as a query parameter. This is common practice to avoid malicious sql injection attacks.

Reading images

In the previous example, we have inserted an image into the database table. Now we are going to read the image back from the table.

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb 
import sys

try:
    conn = mdb.connect(host='localhost',user='testuser', 
        passwd='test623', db='testdb')

    cursor = conn.cursor()

    cursor.execute("SELECT Data FROM Images LIMIT 1")

    fout = open('image.png','wb')
    fout.write(cursor.fetchone()[0])
    fout.close()

    cursor.close()
    conn.close()

except IOError, e:

    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)

We read one image from the Images table.

cursor.execute("SELECT Data FROM Images LIMIT 1")

We select one record from the table.

fout = open('image.png','wb')

We open a writable binary file.

fout.write(cursor.fetchone()[0])

We fetch the data from the previous SQL statement and write it to the file.

Now we should have an image called image.png in our current directory. We can check if it is the same image, that we have inserted into the table.

Transaction support

transaction is an atomic unit of database operations against the data in one or more databases. The effects of all the SQL statements in a transaction can be either all committed to the database or all rolled back.

For databases that support transactions, the Python interface silently starts a transaction when the cursor is created. The commit() method commits the updates made using that cursor, and the rollback() method discards them. Each method starts a new transaction.

The MySQL database has different types of storage engines. The most common are the MyISAM and the InnoDB engines. The MyISAM is the default one. There is a trade-off between data security and database speed. The MyISAM tables are faster to process and they do not support transactions. The commit() and rollback() methods are not implemented. They do nothing. On the other hand, the InnoDB tables are more safe against the data loss. They support transactions. They are slower to process.

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb
import sys

try:
    conn = mdb.connect('localhost', 'testuser', 
        'test623', 'testdb');

    cursor = conn.cursor()
    
    cursor.execute("UPDATE Writers SET Name = %s WHERE Id = %s", 
        ("Leo Tolstoy", "1"))       
    cursor.execute("UPDATE Writers SET Name = %s WHERE Id = %s", 
        ("Boris Pasternak", "2"))
    cursor.execute("UPDATE Writer SET Name = %s WHERE Id = %s", 
        ("Leonid Leonov", "3"))   

    conn.commit()

    cursor.close()
    conn.close()

except mdb.Error, e:
  
    conn.rollback()
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)

In this script, we try to update three rows. The storage engine of the table is MyISAM.

cursor.execute("UPDATE Writers SET Name = %s WHERE Id = %s", 
    ("Leo Tolstoy", "1"))       
cursor.execute("UPDATE Writers SET Name = %s WHERE Id = %s", 
    ("Boris Pasternak", "2"))

Here we want to change names of authors for rows 1 and 2.

cursor.execute("UPDATE Writer SET Name = %s WHERE Id = %s", 
    ("Leonid Leonov", "3"))   

There is an error in the SQL statement. There is no Writer table.

conn.rollback()  

We can call the rollback() method, but it does nothing.

$ ./isam.py
Error 1146: Table 'testdb.Writer' doesn't exist

mysql> SELECT * FROM Writers;
+----+-------------------+
| Id | Name              |
+----+-------------------+
|  1 | Leo Tolstoy       |
|  2 | Boris Pasternak   |
|  3 | Lion Feuchtwanger |
|  4 | Guy de Maupasant  |
|  5 | Truman Capote     |
+----+-------------------+
5 rows in set (0.00 sec)

Running the script gives an error. But as we see, the first two rows already were changed.

In the last example of this tutorial, we are going to recreate the Writers table. This time, the table will be of InnoDB type. InnoDB MySQL database tables support transactions.

DROP TABLE Writers;

CREATE TABLE IF NOT EXISTS Writers(Id INT PRIMARY KEY AUTO_INCREMENT, 
    Name VARCHAR(25)) ENGINE=INNODB;

INSERT INTO Writers(Name) VALUES('Jack London');
INSERT INTO Writers(Name) VALUES('Honore de Balzac');
INSERT INTO Writers(Name) VALUES('Lion Feuchtwanger');
INSERT INTO Writers(Name) VALUES('Emile Zola');
INSERT INTO Writers(Name) VALUES('Truman Capote');

This is writers.sql file. It is used to recreate the Writers table.

mysql> source writers.sql
Query OK, 0 rows affected (0.03 sec)

Query OK, 0 rows affected (0.10 sec)

Query OK, 1 row affected (0.02 sec)

Query OK, 1 row affected (0.03 sec)

Query OK, 1 row affected (0.02 sec)

Query OK, 1 row affected (0.02 sec)

Query OK, 1 row affected (0.02 sec)

We can use the source commnad to load and execute the sql script.

#!/usr/bin/python
# -*- coding: utf-8 -*-

import MySQLdb as mdb
import sys

try:
    conn = mdb.connect('localhost', 'testuser', 
        'test623', 'testdb');

    cursor = conn.cursor()
    
    cursor.execute("DELETE FROM Writers WHERE Id = 5")  
    cursor.execute("DELETE FROM Writers WHERE Id = 4") 
    cursor.execute("DELETE FROM Writer WHERE Id = 3") 
    
    conn.commit()

except mdb.Error, e:
  
    conn.rollback()
    print "Error %d: %s" % (e.args[0],e.args[1])
    sys.exit(1)

cursor.close()
conn.close()

Now, we are going to execute the above script. We want to delete three rows from the table. The third SQL statement has an error.

$ ./innodb.py
Error 1146: Table 'testdb.Writer' doesn't exist

mysql> SELECT * FROM Writers;
+----+-------------------+
| Id | Name              |
+----+-------------------+
|  1 | Jack London       |
|  2 | Honore de Balzac  |
|  3 | Lion Feuchtwanger |
|  4 | Emile Zola        |
|  5 | Truman Capote     |
+----+-------------------+
5 rows in set (0.00 sec)

The error occured before we have committed the changes to the database. Therollback() method was called and no deletions took place.

Managing Hierarchical Data in MySQL

Introduction

Most users at one time or another have dealt with hierarchical data in a SQL database and no doubt learned that the management of hierarchical data is not what a relational database is intended for. The tables of a relational database are not hierarchical (like XML), but are simply a flat list. Hierarchical data has a parent-child relationship that is not naturally represented in a relational database table.

For our purposes, hierarchical data is a collection of data where each item has a single parent and zero or more children (with the exception of the root item, which has no parent). Hierarchical data can be found in a variety of database applications, including forum and mailing list threads, business organization charts, content management categories, and product categories. For our purposes we will use the following product category hierarchy from an fictional electronics store:

These categories form a hierarchy in much the same way as the other examples cited above. In this article we will examine two models for dealing with hierarchical data in MySQL, starting with the traditional adjacency list model.

The Adjacency List Model

Typically the example categories shown above will be stored in a table like the following (I’m including full CREATE and INSERT statements so you can follow along):

CREATE TABLE category(
        category_id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(20) NOT NULL,
        parent INT DEFAULT NULL
);

INSERT INTO category VALUES(1,'ELECTRONICS',NULL),(2,'TELEVISIONS',1),(3,'TUBE',2),
        (4,'LCD',2),(5,'PLASMA',2),(6,'PORTABLE ELECTRONICS',1),(7,'MP3 PLAYERS',6),(8,'FLASH',7),
        (9,'CD PLAYERS',6),(10,'2 WAY RADIOS',6);

SELECT * FROM category ORDER BY category_id;
+-------------+----------------------+--------+
| category_id | name                 | parent |
+-------------+----------------------+--------+
|           1 | ELECTRONICS          |   NULL |
|           2 | TELEVISIONS          |      1 |
|           3 | TUBE                 |      2 |
|           4 | LCD                  |      2 |
|           5 | PLASMA               |      2 |
|           6 | PORTABLE ELECTRONICS |      1 |
|           7 | MP3 PLAYERS          |      6 |
|           8 | FLASH                |      7 |
|           9 | CD PLAYERS           |      6 |
|          10 | 2 WAY RADIOS         |      6 |
+-------------+----------------------+--------+
10 rows in set (0.00 sec)

In the adjacency list model, each item in the table contains a pointer to its parent. The topmost element, in this case electronics, has a NULL value for its parent. The adjacency list model has the advantage of being quite simple, it is easy to see thatFLASH is a child ofmp3 players, which is a child of portable electronics, which is a child of electronics. While the adjacency list model can be dealt with fairly easily in client-side code, working with the model can be more problematic in pure SQL.

Retrieving a Full Tree

The first common task when dealing with hierarchical data is the display of the entire tree, usually with some form of indentation. The most common way of doing this is in pure SQL is through the use of a self-join:

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';

+-------------+----------------------+--------------+-------+
| lev1        | lev2                 | lev3         | lev4  |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS          | TUBE         | NULL  |
| ELECTRONICS | TELEVISIONS          | LCD          | NULL  |
| ELECTRONICS | TELEVISIONS          | PLASMA       | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS  | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS   | NULL  |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL  |
+-------------+----------------------+--------------+-------+
6 rows in set (0.00 sec)

Finding all the Leaf Nodes

We can find all the leaf nodes in our tree (those with no children) by using a LEFT JOIN query:

SELECT t1.name FROM
category AS t1 LEFT JOIN category as t2
ON t1.category_id = t2.parent
WHERE t2.category_id IS NULL;

+--------------+
| name         |
+--------------+
| TUBE         |
| LCD          |
| PLASMA       |
| FLASH        |
| CD PLAYERS   |
| 2 WAY RADIOS |
+--------------+

Retrieving a Single Path

The self-join also allows us to see the full path through our hierarchies:

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS' AND t4.name = 'FLASH';

+-------------+----------------------+-------------+-------+
| lev1        | lev2                 | lev3        | lev4  |
+-------------+----------------------+-------------+-------+
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |
+-------------+----------------------+-------------+-------+
1 row in set (0.01 sec)

The main limitation of such an approach is that you need one self-join for every level in the hierarchy, and performance will naturally degrade with each level added as the joining grows in complexity.

Limitations of the Adjacency List Model

Working with the adjacency list model in pure SQL can be difficult at best. Before being able to see the full path of a category we have to know the level at which it resides. In addition, special care must be taken when deleting nodes because of the potential for orphaning an entire sub-tree in the process (delete the portable electronics category and all of its children are orphaned). Some of these limitations can be addressed through the use of client-side code or stored procedures. With a procedural language we can start at the bottom of the tree and iterate upwards to return the full tree or a single path. We can also use procedural programming to delete nodes without orphaning entire sub-trees by promoting one child element and re-ordering the remaining children to point to the new parent.

The Nested Set Model

What I would like to focus on in this article is a different approach, commonly referred to as theNested Set Model. In the Nested Set Model, we can look at our hierarchy in a new way, not as nodes and lines, but as nested containers. Try picturing our electronics categories this way:

Notice how our hierarchy is still maintained, as parent categories envelop their children.We represent this form of hierarchy in a table through the use of left and right values to represent the nesting of our nodes:

CREATE TABLE nested_category (
        category_id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(20) NOT NULL,
        lft INT NOT NULL,
        rgt INT NOT NULL
);

INSERT INTO nested_category VALUES(1,'ELECTRONICS',1,20),(2,'TELEVISIONS',2,9),(3,'TUBE',3,4),
 (4,'LCD',5,6),(5,'PLASMA',7,8),(6,'PORTABLE ELECTRONICS',10,19),(7,'MP3 PLAYERS',11,14),(8,'FLASH',12,13),
 (9,'CD PLAYERS',15,16),(10,'2 WAY RADIOS',17,18);

SELECT * FROM nested_category ORDER BY category_id;

+-------------+----------------------+-----+-----+
| category_id | name                 | lft | rgt |
+-------------+----------------------+-----+-----+
|           1 | ELECTRONICS          |   1 |  20 |
|           2 | TELEVISIONS          |   2 |   9 |
|           3 | TUBE                 |   3 |   4 |
|           4 | LCD                  |   5 |   6 |
|           5 | PLASMA               |   7 |   8 |
|           6 | PORTABLE ELECTRONICS |  10 |  19 |
|           7 | MP3 PLAYERS          |  11 |  14 |
|           8 | FLASH                |  12 |  13 |
|           9 | CD PLAYERS           |  15 |  16 |
|          10 | 2 WAY RADIOS         |  17 |  18 |
+-------------+----------------------+-----+-----+

We use lft and rgt because left and right are reserved words in MySQL, seehttp://dev.mysql.com/doc/mysql/en/reserved-words.html for the full list of reserved words.

So how do we determine left and right values? We start numbering at the leftmost side of the outer node and continue to the right:

This design can be applied to a typical tree as well:

When working with a tree, we work from left to right, one layer at a time, descending to each node’s children before assigning a right-hand number and moving on to the right. This approach is called the modified preorder tree traversal algorithm.

Retrieving a Full Tree

We can retrieve the full tree through the use of a self-join that links parents with nodes on the basis that a node’s lft value will always appear between its parent’s lft and rgt values:

SELECT node.name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND parent.name = 'ELECTRONICS'
ORDER BY node.lft;

+----------------------+
| name                 |
+----------------------+
| ELECTRONICS          |
| TELEVISIONS          |
| TUBE                 |
| LCD                  |
| PLASMA               |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS          |
| FLASH                |
| CD PLAYERS           |
| 2 WAY RADIOS         |
+----------------------+

Unlike our previous examples with the adjacency list model, this query will work regardless of the depth of the tree. We do not concern ourselves with the rgt value of the node in our BETWEEN clause because the rgt value will always fall within the same parent as the lft values.

Finding all the Leaf Nodes

Finding all leaf nodes in the nested set model even simpler than the LEFT JOIN method used in the adjacency list model. If you look at the nested_category table, you may notice that the lft and rgt values for leaf nodes are consecutive numbers. To find the leaf nodes, we look for nodes where rgt = lft + 1:

SELECT name
FROM nested_category
WHERE rgt = lft + 1;

+--------------+
| name         |
+--------------+
| TUBE         |
| LCD          |
| PLASMA       |
| FLASH        |
| CD PLAYERS   |
| 2 WAY RADIOS |
+--------------+

Retrieving a Single Path

With the nested set model, we can retrieve a single path without having multiple self-joins:

SELECT parent.name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.name = 'FLASH'
ORDER BY node.lft;

+----------------------+
| name                 |
+----------------------+
| ELECTRONICS          |
| PORTABLE ELECTRONICS |
| MP3 PLAYERS          |
| FLASH                |
+----------------------+

Finding the Depth of the Nodes

We have already looked at how to show the entire tree, but what if we want to also show the depth of each node in the tree, to better identify how each node fits in the hierarchy? This can be done by adding a COUNT function and a GROUP BY clause to our existing query for showing the entire tree:

SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

+----------------------+-------+
| name                 | depth |
+----------------------+-------+
| ELECTRONICS          |     0 |
| TELEVISIONS          |     1 |
| TUBE                 |     2 |
| LCD                  |     2 |
| PLASMA               |     2 |
| PORTABLE ELECTRONICS |     1 |
| MP3 PLAYERS          |     2 |
| FLASH                |     3 |
| CD PLAYERS           |     2 |
| 2 WAY RADIOS         |     2 |
+----------------------+-------+

We can use the depth value to indent our category names with the CONCAT and REPEAT string functions:

SELECT CONCAT( REPEAT(' ', COUNT(parent.name) - 1), node.name) AS name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

+-----------------------+
| name                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  PORTABLE ELECTRONICS |
|   MP3 PLAYERS         |
|    FLASH              |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
+-----------------------+

Of course, in a client-side application you will be more likely to use the depth value directly to display your hierarchy. Web developers could loop through the tree, adding <li></li> and <ul></ul> tags as the depth number increases and decreases.

Depth of a Sub-Tree

When we need depth information for a sub-tree, we cannot limit either the node or parent tables in our self-join because it will corrupt our results. Instead, we add a third self-join, along with a sub-query to determine the depth that will be the new starting point for our sub-tree:

SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
        nested_category AS parent,
        nested_category AS sub_parent,
        (
                SELECT node.name, (COUNT(parent.name) - 1) AS depth
                FROM nested_category AS node,
                nested_category AS parent
                WHERE node.lft BETWEEN parent.lft AND parent.rgt
                AND node.name = 'PORTABLE ELECTRONICS'
                GROUP BY node.name
                ORDER BY node.lft
        )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
        AND sub_parent.name = sub_tree.name
GROUP BY node.name
ORDER BY node.lft;

+----------------------+-------+
| name                 | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS |     0 |
| MP3 PLAYERS          |     1 |
| FLASH                |     2 |
| CD PLAYERS           |     1 |
| 2 WAY RADIOS         |     1 |
+----------------------+-------+

This function can be used with any node name, including the root node. The depth values are always relative to the named node.

 

Find the Immediate Subordinates of a Node

Imagine you are showing a category of electronics products on a retailer web site. When a user clicks on a category, you would want to show the products of that category, as well as list its immediate sub-categories, but not the entire tree of categories beneath it. For this, we need to show the node and its immediate sub-nodes, but no further down the tree. For example, when showing the PORTABLE ELECTRONICS category, we will want to show MP3 PLAYERS, CD PLAYERS, and 2 WAY RADIOS, but not FLASH.

This can be easily accomplished by adding a HAVING clause to our previous query:

SELECT node.name, (COUNT(parent.name) - (sub_tree.depth + 1)) AS depth
FROM nested_category AS node,
        nested_category AS parent,
        nested_category AS sub_parent,
        (
                SELECT node.name, (COUNT(parent.name) - 1) AS depth
                FROM nested_category AS node,
                        nested_category AS parent
                WHERE node.lft BETWEEN parent.lft AND parent.rgt
                        AND node.name = 'PORTABLE ELECTRONICS'
                GROUP BY node.name
                ORDER BY node.lft
        )AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
        AND sub_parent.name = sub_tree.name
GROUP BY node.name
HAVING depth <= 1
ORDER BY node.lft;

+----------------------+-------+
| name                 | depth |
+----------------------+-------+
| PORTABLE ELECTRONICS |     0 |
| MP3 PLAYERS          |     1 |
| CD PLAYERS           |     1 |
| 2 WAY RADIOS         |     1 |
+----------------------+-------+

If you do not wish to show the parent node, change the HAVING depth <= 1 line to HAVING depth = 1.

Aggregate Functions in a Nested Set

Let’s add a table of products that we can use to demonstrate aggregate functions with:

CREATE TABLE product
(
        product_id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(40),
        category_id INT NOT NULL
);

INSERT INTO product(name, category_id) VALUES('20" TV',3),('36" TV',3),
('Super-LCD 42"',4),('Ultra-Plasma 62"',5),('Value Plasma 38"',5),
('Power-MP3 5gb',7),('Super-Player 1gb',8),('Porta CD',9),('CD To go!',9),
('Family Talk 360',10);

SELECT * FROM product;

+------------+-------------------+-------------+
| product_id | name              | category_id |
+------------+-------------------+-------------+
|          1 | 20" TV            |           3 |
|          2 | 36" TV            |           3 |
|          3 | Super-LCD 42"     |           4 |
|          4 | Ultra-Plasma 62"  |           5 |
|          5 | Value Plasma 38"  |           5 |
|          6 | Power-MP3 128mb   |           7 |
|          7 | Super-Shuffle 1gb |           8 |
|          8 | Porta CD          |           9 |
|          9 | CD To go!         |           9 |
|         10 | Family Talk 360   |          10 |
+------------+-------------------+-------------+

Now let’s produce a query that can retrieve our category tree, along with a product count for each category:

SELECT parent.name, COUNT(product.name)
FROM nested_category AS node ,
        nested_category AS parent,
        product
WHERE node.lft BETWEEN parent.lft AND parent.rgt
        AND node.category_id = product.category_id
GROUP BY parent.name
ORDER BY node.lft;

+----------------------+---------------------+
| name                 | COUNT(product.name) |
+----------------------+---------------------+
| ELECTRONICS          |                  10 |
| TELEVISIONS          |                   5 |
| TUBE                 |                   2 |
| LCD                  |                   1 |
| PLASMA               |                   2 |
| PORTABLE ELECTRONICS |                   5 |
| MP3 PLAYERS          |                   2 |
| FLASH                |                   1 |
| CD PLAYERS           |                   2 |
| 2 WAY RADIOS         |                   1 |
+----------------------+---------------------+

This is our typical whole tree query with a COUNT and GROUP BY added, along with a reference to the product table and a join between the node and product table in the WHERE clause. As you can see, there is a count for each category and the count of subcategories is reflected in the parent categories.

Adding New Nodes

Now that we have learned how to query our tree, we should take a look at how to update our tree by adding a new node. Let’s look at our nested set diagram again:

If we wanted to add a new node between the TELEVISIONS and PORTABLE ELECTRONICS nodes, the new node would have lft and rgt values of 10 and 11, and all nodes to its right would have their lft and rgt values increased by two. We would then add the new node with the appropriate lft and rgt values. While this can be done with a stored procedure in MySQL 5, I will assume for the moment that most readers are using 4.1, as it is the latest stable version, and I will isolate my queries with a LOCK TABLES statement instead:

LOCK TABLE nested_category WRITE;

SELECT @myRight := rgt FROM nested_category
WHERE name = 'TELEVISIONS';

UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;

INSERT INTO nested_category(name, lft, rgt) VALUES('GAME CONSOLES', @myRight + 1, @myRight + 2);

UNLOCK TABLES;

We can then check our nesting with our indented tree query:

SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

+-----------------------+
| name                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  GAME CONSOLES        |
|  PORTABLE ELECTRONICS |
|   MP3 PLAYERS         |
|    FLASH              |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
+-----------------------+

If we instead want to add a node as a child of a node that has no existing children, we need to modify our procedure slightly. Let’s add a new FRS node below the 2 WAY RADIOS node:

LOCK TABLE nested_category WRITE;

SELECT @myLeft := lft FROM nested_category

WHERE name = '2 WAY RADIOS';

UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myLeft;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myLeft;

INSERT INTO nested_category(name, lft, rgt) VALUES('FRS', @myLeft + 1, @myLeft + 2);

UNLOCK TABLES;

In this example we expand everything to the right of the left-hand number of our proud new parent node, then place the node to the right of the left-hand value. As you can see, our new node is now properly nested:

SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

+-----------------------+
| name                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  GAME CONSOLES        |
|  PORTABLE ELECTRONICS |
|   MP3 PLAYERS         |
|    FLASH              |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
|    FRS                |
+-----------------------+

Deleting Nodes

The last basic task involved in working with nested sets is the removal of nodes. The course of action you take when deleting a node depends on the node’s position in the hierarchy; deleting leaf nodes is easier than deleting nodes with children because we have to handle the orphaned nodes.

When deleting a leaf node, the process if just the opposite of adding a new node, we delete the node and its width from every node to its right:

LOCK TABLE nested_category WRITE;

SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'GAME CONSOLES';

DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight;

UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight;

UNLOCK TABLES;

And once again, we execute our indented tree query to confirm that our node has been deleted without corrupting the hierarchy:

SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

+-----------------------+
| name                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  PORTABLE ELECTRONICS |
|   MP3 PLAYERS         |
|    FLASH              |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
|    FRS                |
+-----------------------+

This approach works equally well to delete a node and all its children:

LOCK TABLE nested_category WRITE;

SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'MP3 PLAYERS';

DELETE FROM nested_category WHERE lft BETWEEN @myLeft AND @myRight;

UPDATE nested_category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - @myWidth WHERE lft > @myRight;

UNLOCK TABLES;

And once again, we query to see that we have successfully deleted an entire sub-tree:

SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

+-----------------------+
| name                  |
+-----------------------+
| ELECTRONICS           |
|  TELEVISIONS          |
|   TUBE                |
|   LCD                 |
|   PLASMA              |
|  PORTABLE ELECTRONICS |
|   CD PLAYERS          |
|   2 WAY RADIOS        |
|    FRS                |
+-----------------------+

The other scenario we have to deal with is the deletion of a parent node but not the children. In some cases you may wish to just change the name to a placeholder until a replacement is presented, such as when a supervisor is fired. In other cases, the child nodes should all be moved up to the level of the deleted parent:

LOCK TABLE nested_category WRITE;

SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
FROM nested_category
WHERE name = 'PORTABLE ELECTRONICS';

DELETE FROM nested_category WHERE lft = @myLeft;

UPDATE nested_category SET rgt = rgt - 1, lft = lft - 1 WHERE lft BETWEEN @myLeft AND @myRight;
UPDATE nested_category SET rgt = rgt - 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft - 2 WHERE lft > @myRight;

UNLOCK TABLES;

In this case we subtract two from all elements to the right of the node (since without children it would have a width of two), and one from the nodes that are its children (to close the gap created by the loss of the parent’s left value). Once again, we can confirm our elements have been promoted:

SELECT CONCAT( REPEAT( ' ', (COUNT(parent.name) - 1) ), node.name) AS name
FROM nested_category AS node,
        nested_category AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

+---------------+
| name          |
+---------------+
| ELECTRONICS   |
|  TELEVISIONS  |
|   TUBE        |
|   LCD         |
|   PLASMA      |
|  CD PLAYERS   |
|  2 WAY RADIOS |
|   FRS         |
+---------------+

Other scenarios when deleting nodes would include promoting one of the children to the parent position and moving the child nodes under a sibling of the parent node, but for the sake of space these scenarios will not be covered in this article.

Final Thoughts

While I hope the information within this article will be of use to you, the concept of nested sets in SQL has been around for over a decade, and there is a lot of additional information available in books and on the Internet. In my opinion the most comprehensive source of information on managing hierarchical information is a book called Joe Celko’s Trees and Hierarchies in SQL for Smarties, written by a very respected author in the field of advanced SQL, Joe Celko. Joe Celko is often credited with the nested sets model and is by far the most prolific author on the subject. I have found Celko’s book to be an invaluable resource in my own studies and highly recommend it. The book covers advanced topics which I have not covered in this article, and provides additional methods for managing hierarchical data in addition to the Adjacency List and Nested Set models.

In the References / Resources section that follows I have listed some web resources that may be of use in your research of managing hierarchal data, including a pair of PHP related resources that include pre-built PHP libraries for handling nested sets in MySQL. Those of you who currently use the adjacency list model and would like to experiment with the nested set model will find sample code for converting between the two in the Storing Hierarchical Data in a Database resource listed below.

References / Resources

Source: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

Python: Testing if a variable is defined

In order to verify whether a python variable is defined or not. Use the following code which uses try, except

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
# Ensure variable is defined
try:
    x
except NameError:
    x = None

# Test whether variable is defined to be None
if x is None:
    some_fallback_operation()
else:
    some_operation(x)

Python doesn’t have a specific function to test whether a variable is defined, since all variables are expected to have been defined before use – even if initially just assigned the None object. Attempting to access a variable that hasn’t previously been defined will raise an exception.

It is generally considered unusual in Python not to know whether a variable has already been defined. But if you are nevertheless in this situation, you can make sure that a given variable is in fact defined (as None, if nothing else) by attempting to access it inside a ‘try’ block and assigning it the None object should it raise a NameError exception.

Many situations which might be thought would give rise to undefined variables, such as processing configuration files or web forms, can be better handled by employing a dictionary and testing for the presence of a key with the has_key() method.