SWAT Blog

How To query contiguous data ranges – Oracle

January 22nd, 2011 by Shailendra Parate

I recently presented a way of querying contiguous data ranges using SQL Server In this post I am going to tackle the same problem but solving it in Oracle instead.
Often times we are confronted with problems that seem very easy to start with but once you get your teeth into it, you realise it wasn’t as easy as initially thought. In this blog, I am attempting to work with data ranges that are not easily groupable.
Problem Statement: Using the data structure listed below, group the data ranges by dataKey when the dataKey is contigous, when breaks in data key are identified, create a new reporting output line. Query input-

grp dataRange dataKey Comments
A 1000 1
A 1001 1
A 1002 1
A 1003 1
A 1004 1
A 1005 2 — Notice dataKey is not contiguous
A 1006 2
A 1007 1
A 1008 1
A 1009 1
A 1010 1
A 1011 1

Desired Query output-

dataKey DataRangeBreakDown
1 A [1000 .. 1004]
2 A [1005 .. 1006]
1 A [1007 .. 1011]

Approach: This is an interesting puzzle, because we cannot simply group the data by “dataKey” column, doing so would ignore the breaks in data. The key to solving this refreshing problem is by “somehow” introducing a pseudo-column which does the heavy lifting for us. Table below illustrates the new pseudo-column, if we introduce this column using SQL, rest is just simple group by. Before peeking at the final solution, I would encourage you to have a go, but if you can’t for any reason I have presented final solution later in the blog.

grp dataRange dataKey New Column
A 1000 1 0
A 1001 1 0
A 1002 1 0
A 1003 1 0
A 1004 1 0
A 1005 2 1
A 1006 2 1
A 1007 1 2
A 1008 1 2
A 1009 1 2
A 1010 1 2
A 1011 1 2

Setup Data:

 DROP TABLE tmpDataRange; CREATE TABLE
  tmpDataRange AS SELECT 'A' grp, 1000 AS dataRange, 1 AS dataKey FROM Dual UNION ALL
  SELECT 'A', 1001, 1 FROM Dual UNION ALL SELECT 'A', 1002, 1 FROM Dual UNION ALL SELECT
  'A', 1003, 1 FROM Dual UNION ALL SELECT 'A', 1004, 1 FROM Dual UNION ALL SELECT 'A',
  1005, 2 FROM Dual UNION ALL SELECT 'A', 1006, 2 FROM Dual UNION ALL SELECT 'A', 1007, 1
  FROM Dual UNION ALL SELECT 'A', 1008, 1 FROM Dual UNION ALL SELECT 'A', 1009, 1 FROM
  Dual UNION ALL SELECT 'A', 1010, 1 FROM Dual UNION ALL SELECT 'A', 1011, 1 FROM Dual ;
  

Solution:
Welcome to the solution. I have tackled the problem using the aproach above using Common Table Expressions (CTE) feature. I think CTEs are fantastic even for writing daily garden variety queries as they make the query so much more understandable by being written in the way we digest other information, i.e. Top to Bottom. Overview of solution query:
Data0 – Instantiate the problem statement input data
Data1 – Assign incremental row numbers to input data
Data2 – Self join the data to the previous row
Data3 – This is where the “magic” happens – Build a cumulative data column which increments on every dataKey break
Data4 – Is concerned with grouping and presenting the data in format requested Presented below is the final solution:

 WITH data0 AS (SELECT grp, dataRange, dataKey FROM
  tmpDataRange), data1 AS (SELECT grp, dataRange, dataKey, LAG(dataKey) OVER(ORDER BY
  dataRange ASC) lagDataKey FROM data0), data2 AS (SELECT grp, dataRange, dataKey, CASE
  WHEN dataKey=NVL(lagDataKey,dataKey) THEN 0 ELSE 1 END cumu FROM data1), data3 AS
  (SELECT grp, dataRange, dataKey, SUM(cumu) OVER(ORDER BY dataRange) cumulativeWindow
  FROM data2), data4 AS (SELECT cumulativeWindow, dataKey, MAX(grp) || ' [' ||
  TO_CHAR(MIN(dataRange)) ||' .. '|| TO_CHAR(MAX(dataRange)) ||']' DataRangeBreakDown
  FROM data3 GROUP BY cumulativeWindow, dataKey) SELECT dataKey, DataRangeBreakDown FROM
  data4; 

Variations: One of the great things about this solution is that it is data-type agnostic. Feel free to change the data-type of the dataRange column to DATE for example as shown below and it still works.

 DROP TABLE tmpDataRange; CREATE TABLE tmpDataRange AS SELECT 'A' grp,
  SYSDATE AS dataRange, 1 AS dataKey FROM Dual UNION ALL SELECT 'A', SYSDATE+1, 1 FROM
  Dual UNION ALL SELECT 'A', SYSDATE+2, 1 FROM Dual UNION ALL SELECT 'A', SYSDATE+3, 1
  FROM Dual UNION ALL SELECT 'A', SYSDATE+4, 1 FROM Dual UNION ALL SELECT 'A', SYSDATE+5,
  2 FROM Dual UNION ALL SELECT 'A', SYSDATE+6, 2 FROM Dual UNION ALL SELECT 'A',
  SYSDATE+7, 1 FROM Dual UNION ALL SELECT 'A', SYSDATE+8, 1 FROM Dual UNION ALL SELECT
  'A', SYSDATE+9, 1 FROM Dual UNION ALL SELECT 'A', SYSDATE+10, 1 FROM Dual UNION ALL
  SELECT 'A', SYSDATE+11, 1 FROM Dual ; 

Would love to hear your thoughts on this, or if you have a better solution why not share it with everyone.

How To query contiguous data ranges – SQL Server

January 12th, 2011 by Shailendra Parate

Often times we are confronted with problems that seem very easy to start with but once you get your teeth into it, you realise it wasn’t as easy as initially thought.
In this blog, I am attempting to work with data ranges that are not easily groupable.

Problem Statement:


Using the data structure listed below, group the data ranges by dataKey when the dataKey is contigous, when breaks in data key are identified, create a new reporting output line.
Query input-

grp dataRange dataKey Comments
A 1000 1
A 1001 1
A 1002 1
A 1003 1
A 1004 1
A 1005 2 — Notice dataKey is not contiguous
A 1006 2
A 1007 1
A 1008 1
A 1009 1
A 1010 1
A 1011 1

Desired Query output-

dataKey DataRangeBreakDown
1 A [1000 .. 1004]
2 A [1005 .. 1006]
1 A [1007 .. 1011]

Approach:


This is an interesting puzzle, because we cannot simply group the data by “dataKey” column, doing so would ignore the breaks in data. The key to solving this refreshing problem is by “somehow” introducing a pseudo-column which does the heavy lifting for us.
Table below illustrates the new pseudo-column, if we introduce this column using SQL, rest is just simple group by.

Before peeking at the final solution, I would encourage you to have a go, but if you can’t for any reason I have presented final solution later in the blog.

grp dataRange dataKey New Column
A 1000 1 0
A 1001 1 0
A 1002 1 0
A 1003 1 0
A 1004 1 0
A 1005 2 1
A 1006 2 1
A 1007 1 2
A 1008 1 2
A 1009 1 2
A 1010 1 2
A 1011 1 2

Setup Data:

IF OBJECT_ID('tempDB..#tmpDataRange') IS NOT NULL
DROP TABLE #tmpDataRange;
WITH testData AS (
 SELECT 'A' grp, 1000 AS dataRange, 1 AS dataKey UNION ALL
 SELECT 'A', 1001, 1 UNION ALL
 SELECT 'A', 1002, 1 UNION ALL
 SELECT 'A', 1003, 1 UNION ALL
 SELECT 'A', 1004, 1 UNION ALL
 SELECT 'A', 1005, 2 UNION ALL
 SELECT 'A', 1006, 2 UNION ALL
 SELECT 'A', 1007, 1 UNION ALL
 SELECT 'A', 1008, 1 UNION ALL
 SELECT 'A', 1009, 1 UNION ALL
 SELECT 'A', 1010, 1 UNION ALL
 SELECT 'A', 1011, 1
 )
SELECT grp, dataRange, dataKey
INTO #tmpDataRange
FROM testData;

Solution:


Welcome to the solution.
I have tackled the problem using the aproach above using Common Table Expressions (CTE) feature of SQL Server 2005+ Engine. CTEs are great new addition to query toolkit, besides allowing us to do fancy stuff like hierarchical queries, I think CTEs are fantastic even for writing daily garden variety queries as they make the query so much more understandable by being written in the way we digest other information, i.e. Top to Bottom.
Overview of solution query:
Data0 – Instantiate the problem statement input data
Data1 – Assign incremental row numbers to input data
Data2 – Self join the data to the previous row
Data3 – This is where the “magic” happens – Build a cumulative data column which increments on every dataKey break
Data4 and Data5 – Are concerned with grouping and presenting the data in format requested

Presented below is the final solution:

WITH
data0 AS (SELECT grp, dataRange, dataKey FROM #tmpDataRange),
data1 AS (SELECT UPPER(grp) grp,dataRange,dataKey, ROW_NUMBER()OVER(ORDER BY dataRange ASC) rNum FROM data0),
data2 AS (SELECT A.grp,A.dataRange,A.dataKey,A.rNum,
 CASE WHEN A.dataKey=ISNULL(B.dataKey,A.dataKey)THEN 0 ELSE 1 END cumuWindow
 FROM data1 A LEFT JOIN data1 B ON A.rNum=b.rNum+1),
data3 AS (SELECT o.grp,o.dataRange,o.dataKey,
 (SELECT SUM(i.cumuWindow) FROM data2 i WHEREi.rNum <= o.rNum) cumuWindow
 FROM data2 o),
data4 AS (SELECT dataKey,cumuWindow,MIN(grp) grp, MIN(dataRange)mn, MAX(dataRange) mx
 FROM data3
 GROUP BY dataKey,cumuWindow)
SELECT dataKey, grp + '['+CAST(mn AS VARCHAR(10))+'..'+CAST(mx ASVARCHAR(10))+']' DataRangeBreakDown
FROM data4

Variations:
One of the great things about this solution is that it is data-type agnostic. Feel free to change the data-type of the dataRange column to DATE for example as shown below and it still works.

IF OBJECT_ID('tempDB..#tmpDataRange') IS NOT NULL
DROP TABLE #tmpDataRange;
WITH testData AS (
 SELECT 'A' grp, GETDATE() AS dataRange, 1 AS dataKey UNION ALL
 SELECT 'A', GETDATE()+1, 1 UNION ALL
 SELECT 'A', GETDATE()+2, 1 UNION ALL
 SELECT 'A', GETDATE()+3, 1 UNION ALL
 SELECT 'A', GETDATE()+4, 1 UNION ALL
 SELECT 'A', GETDATE()+5, 2 UNION ALL
 SELECT 'A', GETDATE()+6, 2 UNION ALL
 SELECT 'A', GETDATE()+7, 1 UNION ALL
 SELECT 'A', GETDATE()+8, 1 UNION ALL
 SELECT 'A', GETDATE()+9, 1 UNION ALL
 SELECT 'A', GETDATE()+10, 1 UNION ALL
 SELECT 'A', GETDATE()+11, 1
 )
SELECT grp, dataRange, dataKey
INTO #tmpDataRange
FROM testData;

Would love to hear your thoughts on this, or if you have a better solution why not share it with everyone.

I am sure you would like to know how the same problem can be solved in Oracle. Find it here

Step By Step configuration of DNS server

December 28th, 2010 by Bhagwan Dass

Note:- Before Start
Set ip-address manually (I use 172.17.0.250)
Set hostname FQDN (I use server.pdc.home)
No need to install caching-nameserver if you are using fedora 9 to 14.

The Installation has been performed on the following Linux OS.

Server: Fedora 6 to 14, rhel 5.0, to 5.5 & Centos 5.0, to 5.5.

Note: Please be careful about the firewall and selinux policies before continuing with the configuration. Disable the Firewall & Selinux or create rules.

1. Installation of DNS Server.

[root@linux8 ~]# yum install bind* caching-nameserver*

2.Make sure that the host names are set properly

[root@linux8 ~]# vi /etc/hosts

127.0.0.1 localhost.localdomain localhost
::1 localhost6.localdomain6 localhost6
172.17.0.250 server.pdc.home server #(ip address & fqdn)

[root@linux8 ~]# vi /etc/sysconfig/network

NETWORKING=yes
NETWORKING_IPV6=no
HOSTNAME=server.pdc.home

[root@linux8 ~]# hostname server.pdc.home

3. DNS resolve entry

[root@linux8 ~]# vi /etc/resolv.conf

search server.pdc.home
nameserver 172.17.0.250

4. Do the following configuration to setup DNS properly.
(a)

[root@linux8 ~]# vi /etc/named.conf

options {
directory “/var/named”;
dump-file “/var/named/data/cache_dump.db”;
statistics-file “/var/named/data/named_stats.txt”;
memstatistics-file “/var/named/data/named_mem_stats.txt”;
query-source port 53;
};
zone “pdc.home” IN {
type master;
file “pdc.home.forward”;
allow-update { none; };
};

zone “0.17.172.in-addr.arpa” IN {
type master;
file “pdc.home.reverse”;
allow-update { none; };
};

(b) Create the zone file in the proper location and also create a reverse zone file.

[root@linux8 ~]# cd  /var/named
[root@linux8 ~]# cp  localhost.zone  pdc.home.forward
[root@linux8 ~]# cp  named.local  pdc.home.reverse
[root@linux8 ~]# vi  pdc.home.forward

$TTL 86400
@ IN SOA server.pdc.home. root.server.pdc.home. (
42 ; serial (d. adams)
3H ; refresh
15M ; retry
1W ; expiry
1D ) ; minimum

IN NS server.pdc.home.
IN A 127.0.0.1
server IN A 172.17.0.250

[root@linux8 ~]# vi pdc.home.reverse

$TTL 86400
@ IN SOA server.pdc.home. root.server.pdc.home. (
1997022700 ; Serial
28800 ; Refresh
14400 ; Retry
3600000 ; Expire
86400 ) ; Minimum
IN NS server.pdc.home.
250 IN PTR server.pdc.home.

(c) Restart the named service

[root@linux8 ~]# service named  restart
[root@linux8 ~]# chkconfig named on      #for permanent service on
[root@linux8 ~]# bind-chroot-admin -d
[root@linux8 ~]# bind-chroot-admin -e    #for binding with chroot Security

The DNS Server is ready now, it’s time to test.

[root@linux8 ~]# dig server.pdc.home

Answer Should look like below:

; <> DiG 9.3.4-P1 <> server.pdc.home
;; global options: printcmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 33213
;; flags: qr aa rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 1, ADDITIONAL: 0

;; QUESTION SECTION:
;server.pdc.home. IN A

;; ANSWER SECTION:
server.pdc.home. 86400 IN A 172.17.0.250

;; AUTHORITY SECTION:
pdc.home. 86400 IN NS server.pdc.home.

;; Query time: 0 msec
;; SERVER: 172.17.0.250#53(172.17.0.250)
;; WHEN: Mon Mar 9 13:39:47 2009
;; MSG SIZE rcvd: 63

[root@linux8 ~]# dig  -x 172.17.0.250

Answer Should look like below:

; <> DiG 9.3.4-P1 <> -x 172.17.0.250
;; global options: printcmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 34497
;; flags: qr aa rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 1, ADDITIONAL: 1

;; QUESTION SECTION:
;250.0.17.172.in-addr.arpa. IN PTR

;; ANSWER SECTION:
250.0.17.172.in-addr.arpa. 86400 IN PTR server.pdc.home.

;; AUTHORITY SECTION:
0.17.172.in-addr.arpa. 86400 IN NS server.pdc.home.

;; ADDITIONAL SECTION:
server.pdc.home. 86400 IN A 172.17.0.250

;; Query time: 0 msec
;; SERVER: 172.17.0.250#53(172.17.0.250)
;; WHEN: Mon Mar 9 13:41:37 2009
;; MSG SIZE rcvd: 103

Step by step Configuration of Secure FTP Server on Linux

December 28th, 2010 by Bhagwan Dass

The Installation has been performed on the following Linux OS.
Fedora 6 to 14, rhel 5.0,to 5.5 & Centos 5.0 to 5.5
STEP 1: Setup VSFTPD

[root@linux8 ~]# yum install vsftpd*

STEP 2: Configure VSFTPD

[root@linux8 ~]# vi /etc/vsftpd.conf

Make changes as below:

anonymous_enable=NO
local_enable=YES
write_enable=YES
local_umask=022
dirmessage_enable=YES
xferlog_enable=YES
connect_from_port_20=YES
xferlog_std_format=YES

#You may fully customise the login banner string
ftpd_banner=Welcome to Minor Addition FTP.

listen=YES
pam_service_name=vsftpd
userlist_enable=YES
tcp_wrappers=YES (save & Exit)

STEP 3: Enable SSH2 Encription on FTP Server

To enable TLS/SSL security controls

[root@linux8 ~]#ldd /urs/sbin/vsftpd | grep ssl

Output of this command should give out like below

libssl.so.6 => /lib/libssl.so6 (0*001bf000)

STEP 4: Generate certificate key for encryption<

[root@linux8 ~]#cd /etc/pki/tls/certs
[root@linux8 ~]#make vsftpd.pem

This command creates certificate for 2 years. After 2 years make it again.

Country Name : IN
State of province name(full name) : Atul Sharma
Locality Name (eg,city) : Guildford
Organization Name : Tactical Value.
Organizational Unit Name : Centos Server
Common Name (eg,your name or your server’s hostname) : ixlinux1
Email Address : sysadmin@minoraddition.com

Note: The vsftpd.pem file should be secured so only owner has access to the file. This file does not affect the server if it is running as a non privileged account, as the keys are loaded before dropping into non privileged mode.

[root@linux8 ~]#cat /etc/pki/tls/certs/vsftpd.pem
[root@linux8 ~]#openssl x509 -in /etc/pki/tls/certs/vsftpd.pem -noout -text
[root@linux8 ~]#chmod 600 /etc/pki/tls/certs/vsftpd.pem

The configuration file needs to enter some entries. Open vsftpd.conf & enter below written lines at the end of the file.

[root@linux8 ~]#vi /etc/vsftpd/vsftpd.conf

ssh2 configureation
ssl_enable=YES
allow_anon_ssl=NO
force_local_data_ssl=NO
force_local_logins_ssl=YES

ssl_tlsv1=YES
ssl_sslv2=NO
ssl_sslv3=NO

rsa_cert_file=/etc/pki/tls/certs/vsftpd.pem

Now we have to restart service

[root@linux8 ~]# /etc/init.d/vsftpd restart

This is all about secure ftp server. For access to secure ftp server you have to create users on the same PC.
The Linux clients use gftp, filezilla.
The windows clients use filezilla.
Use SSH2 & Port 22 while making connection.

Move a Subversion Repository from One Remote Server to Another

December 28th, 2010 by Bhagwan Dass

The servers were in remote locations and all I had was ssh access.

Step One: Back up your old repository
This is done with the svnadmin command.

svnadmin dump /srv/svn/repository_name &gt; reponame.dmp

Step Two: Create the new Repository on new svn server
This is same as creating a new project on your svn server

svnadmin create /srv/svn/repository_name

Step Three: Transfer the old Repository dump on your new svn server
You can do this by any way you want. I choose scp.

scp reponame.dmp root@remote-server:/opt/new_repo_name

Last Step: Import your old repository into the new one

root@server:~#cd /opt
root@server:/opt#svnadmin load /srv/svn/new_repository_name &lt; reponame.dmp

It’s very simple. My .dmp file was about 2GB so it took hours to transfer. But if you have physical access to the servers this could be a few minutes job.

Redmine installation on Ubuntu 10.04

December 10th, 2010 by Bhagwan Dass

STEP 1: Install Ruby, build essentials, openssl, postgress and subversion

apt-get install wget build-essential ruby1.8 ruby1.8-dev irb1.8
rdoc1.8 zlib1g-dev libopenssl-ruby1.8 libzlib-ruby libssl-dev
libpq-dev postgresql subversion

STEP 2: Make symbolic links to the installed Ruby:

ln -s /usr/bin/ruby1.8 /usr/bin/ruby
ln -s /usr/bin/irb1.8 /usr/bin/irb

STEP 3: Download and install rubygems

wget http://rubyforge.org/frs/download.php/70696/rubygems-1.3.7.tgz
tar -xf rubygems*.tgz
cd rubygems*
ruby setup.rb
ln -s /usr/bin/gem1.8 /usr/bin/gem

STEP 4: Install other required gems

gem install -v=1.0.1 rack
gem install fastthread
gem install -v=2.3.5 rails
gem install postgres

STEP 5: Install Passenger and Nginx

cd /opt
wget http://rubyforge.org/frs/download.php/71376/passenger-2.2.15.tar.gz
tar xzvf passenger*.gz

STEP 6: Run the Phusion Passenger installer for Nginx:

cd passenger*/bin
./passenger-install-nginx-module

It will ask you few configuration things for nginx installation
Press Enter
Press 1
Press Enter

STEP 7: Configure Nginx

Nginx is now installed in /opt/nginx, but we need a way of controlling it. Create a file called #/etc/init.d/nginx and copy the following script contents into it:

vi /etc/init.d/nginx

Paste below code into the file:

#!/bin/sh
### BEGIN INIT INFO
# Provides:          nginx
# Required-Start:    $all
# Required-Stop:     $all
# Default-Start:     2 3 4 5
# Default-Stop:      0 1 6
# Short-Description: starts the nginx web server
# Description:       starts nginx using start-stop-daemon
### END INIT INFO
PATH=/opt/nginx/sbin:/sbin:/bin:/usr/sbin:/usr/bin
DAEMON=/opt/nginx/sbin/nginx
NAME=nginx
DESC=nginx
test -x $DAEMON || exit 0
# Include nginx defaults if available
if [ -f /etc/default/nginx ] ; then
. /etc/default/nginx
fi
set -e
case "$1" in
start)
echo -n "Starting $DESC: "
start-stop-daemon --start --quiet --pidfile /opt/nginx/logs/$NAME.pid 
--exec $DAEMON -- $DAEMON_OPTS
echo "$NAME."
;;
stop)
echo -n "Stopping $DESC: "
start-stop-daemon --stop --quiet --pidfile /opt/nginx/logs/$NAME.pid 
--exec $DAEMON
echo "$NAME."
;;
restart|force-reload)
echo -n "Restarting $DESC: "
start-stop-daemon --stop --quiet --pidfile 
/opt/nginx/logs/$NAME.pid --exec $DAEMON
sleep 1
start-stop-daemon --start --quiet --pidfile 
/opt/nginx/logs/$NAME.pid --exec $DAEMON -- $DAEMON_OPTS
echo "$NAME."
;;
reload)
echo -n "Reloading $DESC configuration:"
start-stop-daemon --stop --signal HUP --quiet --pidfile /opt/nginx/logs/$NAME.pid 
--exec $DAEMON
echo "$NAME."
;;
*)
N=/etc/init.d/$NAME
echo "Usage: $N {start|stop|restart|reload|force-reload}" &gt;&amp;2
exit 1
;;
esac
exit 0

Save & exit:

Run the following command to set system startup links:

chmod +x /etc/init.d/nginx
/usr/sbin/update-rc.d -f nginx defaults

If you want to change the default port 80 to 8080 then edit

vi /opt/nginx/conf/nginx.conf
listen 127.0.0.1:8080;

STEP 8: Installing and Configuring Redmine:-

Download Redmine

mkdir -p /srv/www/redmine
cd /srv/www/redmine/
svn co http://redmine.rubyforge.org/svn/branches/1.0-stable redmine

STEP 9: Create and Configure the Database

Switch to the postgres user and start up the psql shell by issuing the following commands:

su - postgres
psql

Run these commands in the psql shell to set up the database for Redmine. Specify a strong password in place of “secret”.

CREATE ROLE redmine LOGIN ENCRYPTED PASSWORD 'secret' NOINHERIT VALID UNTIL 'infinity';
CREATE DATABASE redmine WITH ENCODING='UTF8' OWNER=redmine TEMPLATE=template0;
ALTER DATABASE "redmine" SET datestyle="ISO,MDY";
q
exit
cd redmine

STEP 10: Create the file config/database.yml with the following contents:

cd config/
vi database.yml

paste these lines into the database.yml file

production:
adapter: postgresql
database: redmine
host: localhost
username: redmine
password: secret
encoding: utf8
schema_search_path: public

Run the following commands to complete database configuration:

chmod 600 config/database.yml
rake config/initializers/session_store.rb
RAILS_ENV=production rake db:migrate
RAILS_ENV=production rake redmine:load_default_data

STEP 11: Configure Email Service

Run commands to install exim4 and configure it for outgoing Internet email delivery. You can use Exim installation if you already have SMTP server configured that accepts unauthenticated locally sent mail, although you will still need to create Redmine’s email configuration file.

apt-get install exim4
dpkg-reconfigure exim4-config

Select “internet site” as the type of mail configuration to use.
Specify your systems’s fully qualified domain name as the system mail name.
Enter “127.0.0.1” when asked for the IP address to listen on for SMTP connections. For purposes of allowing Redmine to send mail, we only want to listen on localhost.
Enter “localhost.localdomain” and your fully qualified domain name when asked for the list of recipient domains.
Relay domains and machines should be left blank.
Specify “No” when asked about DNS queries.
When asked about maildirs versus mbox format, you may choose either. Maildirs are increasingly preferred by many modern mail tools.
Specify “No” when asked whether to split the configuration into smaller files.

Create the file config/email.yml and copy in the following contents. Be sure to replace the domain field with your fully qualified domain name.

File: config/email.yml

production:
delivery_method: :smtp
smtp_settings:
address: 127.0.0.1
port: 25
domain: redmine
authentication: :none

This completes email configuration for your Redmine installation.

STEP 12: Final Configuration and Testing:-

Create a “redmine” user to manage the installation. Run the following commands to set ownership and permissions on Redmine files, assign a strong password for Redmine user:

adduser redmine
cd /srv/www/redmine/
chown -R redmine:redmine *
cd redmine
chmod -R 755 files log tmp public/plugin_assets

#Edit the file /opt/nginx/conf/nginx.conf, setting the “user” parameter to “redmine”:

vim /opt/nginx/conf/nginx.conf
user  redmine;

Also, add a server section after the first example server as follows. If #you’re proxying to nginx from another web server, be sure to change the listen #directive to “listen 8080;” instead of the default.

server {
listen 80;
server_name  192.168.1.12;
root /srv/www/redmine/redmine/public/;
access_log /srv/www/redmine/redmine/log/access.log;
error_log /srv/www/redmine/redmine/log/error.log;
index index.html;
location / {
passenger_enabled on;
allow all;
}
}
/etc/init.d/nginx start

Redmine installation should be accessible at
http://localhost

Install SVN SERVER (subversion) on CENTOS 5

December 9th, 2010 by Bhagwan Dass

Prerequisites

  • Fresh installation of CentOS 5.
  • Make sure you should login as root.
  • Make sure you should have apache2 installed (httpd).

Installation Steps
First we’ll install Apache configuration directives for serving Subversion repositories through Apache HTTP Server.

[root@linux10 ~]#yum install subversion mod_dav_svn

Instruct Apache to load the mod_dav_svn module using the LoadModule directive by editing http.conf file

[root@linux10 ~]#vi /etc/httpd/conf/httpd.conf

If the following two lines are not present, add them:

LoadModule dav_svn_module modules/mod_dav_svn.so
LoadModule authz_svn_module modules/mod_authz_svn.so

Create a directory where all svn repositories will be located:

[root@linux10 ~]#mkdir /srv/svn

Create your first Repository

[root@linux10 ~]#svnadmin create --fs-type fsfs /srv/svn/your_project_name

Set the correct file permissions for apache.

[root@linux10 ~]#chown -R apache.apache /srv/svn/your_project_name

Tell Apache where to find the new repository. Here we create an additional Apache configuration file specifically for the repositories.

[root@linux10 ~]#vi /etc/httpd/conf.d/subversion.conf

Add the following directive in the subversion.conf file

DAV svn
SVNPath /srv/svn/your_project_name
AuthType Basic
AuthName "your_project_name Repository"
AuthzSVNAccessFile /srv/svn/svn-acl-conf
AuthUserFile /srv/svn/your_project_name.htpasswd
Require valid-user

Add a repository user

[root@linux10 ~]#touch /srv/svn/your_project_name.htpasswd
[root@linux10 ~]#htpasswd -m /srv/svn/your_project_name.htpasswd  username

Create the Access Control List for the SVN Repository

[root@linux10 ~]#vi /srv/svn/svn-acl-conf

Add the following directives in the file. Where username represents the username of the repository user you created earlier.

[your_project_name:/]
username = rw

Final step, restart Apache server.

[root@linux10 ~]#service httpd restart

After browsing your project, if you gets the below message that means you have done it successfully.

http://localhost/svn/your_project_name

Revision 0: /
Powered by Subversion version 1.4.2 (r22196).

Convert Outlook PST to MBOX in Snow Leopard

June 6th, 2010 by Atul Sharma


Here are the steps that I tried successfully on Snow Leopard:

  1. Download libpst source from http://www.five-ten-sg.com/libpst/packages/. Extract it in ~/Downloads directory.
  2. Download boost source from http://sourceforge.net/projects/boost/files/boost/. (will be downloaded to ~/Downloads directory by default)
  3. Open Terminal and run following commands: (replace X_XX_X and x.x.xx with the version of boost and libpst that you’ve downloaded):
    $su - root
    #cd /usr/local
    #tar --bzip2 -xf ~/Downloads/boost_X_XX_X.tar.bz2
    #cd  /usr/local/boost_X_XX_X
    #./bootstrap.sh --prefix=/usr/local/
    #./bjam install
    #cd  ~/Downloads/libpst-x.x.xx
    #./configure
    #make
    #make install
    
  4. Run this command on terminal – in the directory you want the MBOX created
  5. $readpst -r /path/pstfile.pst

Fix large Eclipse tabs and menu bar in Ubuntu

June 6th, 2010 by Atul Sharma

Run following command in terminal to edit .gtkrc-2.0 file in your home directory:

$ gedit ~/.gtkrc-2.0

Copy paste below in .gtkrc file and save :

style "gtkcompact" {
GtkButton::default_border={0,0,0,0}
GtkButton::default_outside_border={0,0,0,0}
GtkButtonBox::child_min_width=0
GtkButtonBox::child_min_heigth=0
GtkButtonBox::child_internal_pad_x=0
GtkButtonBox::child_internal_pad_y=0
GtkMenu::vertical-padding=1
GtkMenuBar::internal_padding=0
GtkMenuItem::horizontal_padding=4
GtkToolbar::internal-padding=0
GtkToolbar::space-size=0
GtkOptionMenu::indicator_size=0
GtkOptionMenu::indicator_spacing=0
GtkPaned::handle_size=4
GtkRange::trough_border=0
GtkRange::stepper_spacing=0
GtkScale::value_spacing=0
GtkScrolledWindow::scrollbar_spacing=0
GtkTreeView::vertical-separator=0
GtkTreeView::horizontal-separator=0
GtkTreeView::fixed-height-mode=TRUE
GtkWidget::focus_padding=0
}
class "GtkWidget" style "gtkcompact"

Restart Eclipse – tab size and menu bar height should now be fine.

I have tried this in Ubuntu 9.10 and 10.04 – should work for other distros as well.

For installing Weblogic 10.3 on Snow Leopard

June 5th, 2010 by Atul Sharma

Hack to validate JVM while installing Weblogic 10.3 on Snow Leopard:

cd /System/Library/Frameworks/JavaVM.framework/Versions/1.6.0/Home/sudo mkdir jre
cd jresudo ln -s ../lib libsudo ln -s ../bin bin
  • © 2004-2015 Special Work & Technology Limited