Friday, August 13, 2010

setup simple configured SVN with httpd on Fedora 13

To write down the step of configuring my system, I am writing this post as memo.

1) install httpd and SVN
# yum install httpd subversion

2) install mod_dav_svn
# yum install httpd mod_dav_svn

3) create subversion repository
# svnadmin create /home/svn-repos/ubianalysis

4) running following command to allow apache account to access the repository directory
# chown -R apache.apache /home/svn-repos/ubianalysis

5) running the following command to update Selinux context. If command does not work, I use Selinux GUI admin tool to add this. Actually, I added this via SeLinux admin tool found in Administration menu.
# chcon -R -t httpd_sys_content_rw_t /home/svn-repos/ubianalysis

6) go to directory /etc/httpd/conf.d and add following content in subversion.conf

<Location /svn-repos >
DAV svn
SVNParentPath /home/svn-repo/
# Require SSL connection for password protection.
# SSLRequireSSL

AuthType Basic
AuthName "Authorization Realm"
AuthUserFile /etc/httpd/passwd/passwords
Require valid-user
</Location>

7) create password file for httpd since I use Basic authType as shown on above.
#htpasswd -c /etc/httpd/passwd/passwords user1
#htpasswd /etc/httpd/passwd/passwords user2

8) testing the job
#service httpd restart

using web browser to access URL http://localhost//svn-repos/ubianalysis

Wednesday, August 4, 2010

Using user defined variable to simulate DB2 RANK function failure in MySQL server

I recently tried to simulate RANK function from DB2 by using user defined variable. It is very nature for developer to think about using MySQL user defined variable as counter to simulate RANK function. However, it is not successful. I got random rank number in the result set. Below is the sample SQL code.
SET @jia = NULL ;
SELECT id, sum( sales ) AS sales, count( * ) AS sig, @jia := IFNULL( @jia , 0 ) +1 AS rank
FROM brandpromo
GROUP BY id
ORDER BY sales DESC
LIMIT 100 ;
I was only aware that the user defined variable is only connection session scope alive. I did not know that it is dangerous to use user defined variables together with GROUP BY and ORDER BY clause. So, DO NOT use user defined variable together with GROP BY and ORDER BY in MySQL server. At least, be careful when you want to use it in that way.

Actually, I found a blog post that talks about this topic in detail. For more info, we can read this.