Parent-Child Hierarchy

The problem is thus: there are various users, each in a vertical hierarchy. For example, kinds of users or roles might be owner, manager, supervisor, and staff. Each user, except owner, has a parent. And each user, except staff, has a child. This way the hierarchy would be owner is parent of manager; manager is parent of supervisor; and supervisor is parent of staff. One user can be assigned one role only. So user John could be one of the roles mentioned. If we want to find the children of John, how can we do so?

First we create an example table in our database.

create table users (user varchar(50) unique, parent varchar(50));

Now let’s put some data in there:

insert into users values ('john', 'amy'), ('amy', null), ('adam', 'amy'), ('jane', 'john'), ('jennifer', 'john'), ('jethro', 'jane');

By looking at the data, we can see that John’s children are Jane, Jennifer, and Jethro. Why Jethro? Because Jane is a child of John, and Jethro is a child of Jane. So in effect, we are looking for all children of John, be they directly his children or the children of his children, and so on. Please note that we assume there are no many-to-many relationships here. So each user can only have one direct parent, while each parent may have many children (many-to-one).

The code in Python is implemented using SQLalchemy to connect to database. But the idea should remain the same no matter what technology you use. I use a very naive recursive implementation here, which should get you started.

def user_children(user=None, kid_list= None, session=None):
    query = session.query(users).filter_by(parent=user)
    children_list = []
    for result in query:
        children_list.append(result.username)
        kid_list.append(result.username)
    for name in children_list:
        user_children(name, kid_list, session)
    return kid_list

def main():
    kid_list = []
    # We assume session stuff for SQLalchemy has already been taken care of
    # prior to following statement
    session = mydb.mysession()
    all_children = user_children('john', kid_list, session)
    session.close()
    return all_children

if __name__ == "__main__":
    main()

When we run the above code for user John, we get Jane, Jennifer, and Jethro.

Similarly, we can implement something which returns all parents of a user. Since there can only be one actual parent of a user, what we want is to continue upward until we get to a user who has no parent. The implementation is as below:

def user_parents(user=None, parent_list= None, session=None):
    query = session.query(users).filter_by(user=user)
    parents_list = []
    for result in query:
        parents_list.append(result.parent)
        # We don't want a NULL or None parent
        if result.parent:
            parent_list.append(result.parent)
    for name in parents_list:
        user_parents(name, parent_list, session)
    return parent_list

def main():
    parent_list = []
    # We assume session stuff for SQLalchemy has already been taken care of
    # prior to following statement
    session = mydb.mysession()
    all_parents = user_parents('jethro', parent_list, session)
    session.close()
    return all_parents

if __name__ == "__main__":
    main()

When we run the above code for user Jethro, we get Jane, John, and Amy.

This seems to work for me. Of course, the data I tested this code on was very small. Don’t look at the efficiency of the code (although any suggestions on how to improve it are always welcome) but look the at general idea of how you might achieve the same results. I will also try to provide code for when there are many-to-many relationships.

Install FreeSwitch in CentOS

Before we proceed, let’s make one thing certain: FreeSwitch Download & Installation Guide is and always will be better than this guide. However, what I try to do here is make things mentioned in the official guide more clear. Since FreeSwitch recommends using the code in trunk, we will follow this recommendation and the Quick and Dirty Install.

Install svn

svn is required to check-out code from FreeSwitch’s trunk. To install it, run the following command:

sudo yum install subversion

Stop Asterisk

If Asterisk is installed and running, you need to stop it first. Run the following commands:

sudo /usr/sbin/asterisk -r
codeghar*CLI> stop now

Install Development Tools

To make sure you have the things you might need to install FreeSwitch, install the following:

sudo yum install gcc gcc-c++ make automake autoconf libtool libtermcap-devel ncurses-devel

Download FreeSwitch

cd /usr/src/
sudo mkdir freeswitch
sudo chmod o+rwx freeswitch (This is unsafe)
cd /usr/src/freeswitch/
wget http://www.freeswitch.org/eg/Makefile
make

Now sit back and let it do its thing. Once that’s done, run the following commands:

make all
cd freeswitch.trunk
sudo make install
sudo make cd-sounds-install
sudo make cd-moh-install

PostgreSQL Tricks

Bash Alias to Login

I create a bash alias for servers I need to connect to via psql. This is a building block for other tricks in this post. Such an alias follows the following pattern:

alias codeghardb='psql --host 192.168.1.5 --dbname cgdb --username cgdbuser -W'

In the above statement, -W prompts for a password. Now whenever I need to use psql for a cgdb database, I just run codeghardb.

Load Data from file using copy command

First off, please understand that there is a difference between copy and \copy command. From what I understand, you should use \copy because it allows a regular user to insert data from file while copy only allows superuser to insert data from file. Also, copy is a server command and \copy is a client command.

You login to database via psql as below (we created an alias in the first step so we don't have to enter these details every time)

codeghardb

and get psql prompt as below

cgdb=>

You can run the following command on the psql prompt:

cgdb=> \copy mytable (columnone, columntwo) from insert.txt with delimiter as '!'

We expect to have two columns in insert.txt, each delimited (or separated) by !, for the above command.

Run script from file in psql

Sometimes you create a SQL script with multiple statements and commands which need to be run through psql. We use the alias we created before, put the copy command we create previously in a file called script.sql. We also need to do some post-loading processing. So our script.sql may look like this:

--First insert the file
\copy mytable (columnone, columntwo) from insert.txt with delimiter as '!'
--Now update so that all empty string columns are made NULL
update mytable set columntwo = NULL where columntwo = ''
--We are done


All statements in the SQL script need to be on their own line. Run the script.sql file as below:

codeghar@codeghar:~$ codeghardb < script.sql

I learned this trick from Run external script in psql.

Hat Tips

Many thanks to COPY - PostgreSQL Wiki; Adding Data with INSERT and COPY; Using PostgreSQL's COPY function effectively; Populating a Database;

Django, Apache, and X-SendFile in Debian

The idea for this post is to allow downloading files only to restricted users. The only constraint for now is that users are logged in. As I add more code to the Django app, I plan to include the ability to specify which users should have access to which files.

This post assumes that you have a working Apache server with VirtualHost file setup for a Django project. I also assume that you would want to access files using a URL scheme such as http://example.com/download/filename.ext where filename.ext represents the file you want to download.

To install X-SendFile in Apache2, run the following:

sudo aptitude install libapache2-mod-xsendfile

And to make sure you enable it, run the following

sudo a2enmod xsendfile

Add the following two lines in your VirtualHost file, like so:

<VirtualHost *:80>
...
XSendFile on
XSendFileAllowAbove on
...
</VirtualHost>

The code for this has been uploaded to Launchpad under the project Django X-SendFile Download or DjangoXSFD for short. Since it’s a work in progress, you can always get the latest code from there.

Hat tips: Djangocon X-Sendfile Lightning Talk; Fast File Transfer with X-send file; How to make a private download area with django?; Having Django serve downloadable files; Fast private file transfers for Drupal;

Django – Logging in Views

I have searched as many places I could looking for an advisable way of logging within views in Django. I found a lot of advice, apps, code, etc. But I did not find anything easy, beautiful, or even understandable for someone with my limited knowledge. So I did what anyone in need would do: create an app of my own which can be used within views.py for any and all apps and projects.

The main idea was to use Python’s logging module but abstract it enough so that any developer can configure it easily. The code has been structured thus: a directory called django-logbook (or even djangologbook) which needs to be placed on Python’s path, preferably in the Django project folder. This directory contains at least three files, with more files included depending on the number of apps using Django-LogBook (my name for the app). These three files are: __init__.py, django-logbook.py, and sample.py. __init__.py is empty and is present so that the logbook directory can be considered a package or module by Python.

django-logbook.py contains the heart of the app. It contains a class calls LogBook which can be instantiated by any app. To make things easier in terms of management, I have added a sample.py which does the instantiation. The idea is to create separate log files for separate apps. Therefore, each app has its own appname.py in the logbook directory. Just copy sample.py and rename it as appname.py. Then in your views.py import the appname.py.

You don’t even have to import the whole appname.py in views.py. All you have to do is import as below:

from PROJECTNAME.logbook.APPNAME import djangologbookentry

Then within your views, you can log as below:

django-logbookentry.info('My info message')
django-logbookentry.error('2009-12-16 23:59', 'OMG an error')

In your appname.py in django-logbook directory, you need to provide three things: unique identification of logger for the app, name of the log file, and level of log messages. By default it is setup to log up to 5 files, each file 200,000 bytes in size.

For detailed configuration, you can view and modify the LogBook class.

The code for Django-LogBook is hosted at Launchpad.

Donate to FLOSS

Someone over at reddit sought advice (A very FOSS thanksgiving) on which F(L)OSS projects should be donated to. Inspired by it, I am creating a list of projects I currently use and think are donation-worthy. They are (in no particular order): LXDE, CentOS, Debian, Pidgin, Fedora, openbox, pfSense, OpenBSD, FSF, Cream, vim, Django, Python, Apache, Ubuntu, OpenSSH, OpenSSL, Linux, FreeBSD, PostgreSQL, GIMP, Firefox, VLC, Audacity.