Thursday, December 15, 2022

SQL Query Execution Order

 Credit goes to the original author / artist.



Thursday, July 12, 2018

Introduction to Aspect Oriented Programming

A good introduction to Aspect Oriented Programming (AOP) with PHP examples by Glynn Forrest.
https://glynnforrest.com/talks/php-aop/

Saturday, August 26, 2017

Enable both .py and .psp in Apache2

  1. Install the mod_python module for Apache2
  2. Open the default file at /etc/apache2/sites-available/
    (Note that you will need super-user privileges for editing this)
       gedit /etc/apache2/sites-available/default
  3. Go to the section
    
       Options Indexes FollowSymLinks MultiViews
       AllowOverride None
       Order allow,deny
       allow from all   #Uncomment this directive is you want to see apache2's
       #default start page (in /apache2-default) when you go to /
       #RedirectMatch ^/$ /apache2-default/
    
  4. Append this to the end of section
       AddHandler mod_python .py .psp
       PythonHandler mod_python.publisher|.py
       PythonHandler mod_python.psp|.psp
       PythonDebug On
    To read more about Apache directives, visit http://httpd.apache.org/docs/1.3/mod/mod_mime.html
  5. Restart Apache2:
    /etc/init.d/apache2 resart
  6. That’s all folks!
    Now you can play with .py and .psp

Thursday, October 16, 2014

Create a Phar for SwiftMailer

It's very simple to create a phar out of an existing php code tree.
<?php
  $phar = new Phar('swiftmailer.phar', 0, 'swiftmailer.phar');
  // add all files in the project
  $phar->buildFromDirectory(dirname(__FILE__) . '/swiftmailer');
  $phar->setStub($phar->createDefaultStub('swift_required.php'));
?>

Friday, April 4, 2014

MS SQL Server 2000 Row Numbering

select rank=count(*), a1.au_lname, a1.au_fname
   from authors a1, authors a2
   where a1.au_lname + a1.au_fname >= a2.au_lname + a2.au_fname
   group by a1.au_lname, a1.au_fname
   order by rank
source: http://support.microsoft.com/default.aspx?scid=kb;en-us;186133

Tuesday, April 1, 2014

Moving old Subversion repositories to a new server


  1. Dump old repositories to files.
    I used the shell script below, a customization of the script found in http://www.hossainkhan.info/content/shell-script-backup-all-your-svn-repositories
    svn-dump.sh
    #!/bin/sh
    ################################################
    #
    # Backup SVN repos to local folder
    #
    # @author   Hossain Khan
    # @email    contact [at] hossainkhan [dot] info
    # @version  v0.01
    # @reldate  2010-03-27
    ################################################
    #  Copyright (c) 2010 Hossain Khan
    # 
    # Permission is hereby granted, free of charge, to any person
    # obtaining a copy of this software and associated documentation
    # files (the "Software"), to deal in the Software without
    # restriction, including without limitation the rights to use,
    # copy, modify, merge, publish, distribute, sublicense, and/or sell
    # copies of the Software, and to permit persons to whom the
    # Software is furnished to do so, subject to the following
    # conditions:
    # 
    # The above copyright notice and this permission notice shall be
    # included in all copies or substantial portions of the Software.
    # 
    # THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
    # EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
    # OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
    # NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
    # HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
    # WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
    # FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
    # OTHER DEALINGS IN THE SOFTWARE.
    # ----------------------- END OF LICENSE TEXT ----------------------
     
    # Record todays date
    # --------------------
    bakdate=$(date +%Y%m%d%H%M)
    echo "--------------------------------"
    echo "Running SVN backup $bakdate"
    echo "--------------------------------\n"
     
    # From where to backup repos?
    # ---------------------------
    svnrepos="/mnt/datadisk/svnrepo"
    echo "\nGoing to backup all SVN repos located at: $svnrepos \n"
     
    # Where to save the dump?
    # ------------------------
    bakdest="/home/sdu/svn-backups-20140331"
     
    # Location for USB drive? (to copy backup)
    # ----------------------------------------
    baktousb="/media/USB/Extra-BACKUP"
     
    # _________NO-COFIG-REQUIRED-BELOW-THIS-LINE___________
     
    # First go to SVN repo folder
    cd $svnrepos
     
     
    # Just make sure we have write access to backup-folder
    if [ -d "$bakdest" ] && [ -w "$bakdest" ] ; then
      # Now $repo has folder names = project names
      for repo in *; do
        # do svn dump for each project
        echo "Taking backup/svndump for: $repo"
        echo "Executing : svnadmin dump $repo > $bakdest/$repo-$bakdate.svn.dump \n"
        # Now finally execute the backup
        svnadmin dump $repo > $bakdest/$repo-$bakdate.svn.dump
        # You can go an extra mile by applying tar-gz compression to svn-dumps
     
        # We also would like to save the dump to remote place/usb
        if [ -d "$baktousb" ] && [ -w "$baktousb" ] ; then
          # USB/other directory exists, copy the dump there
          echo "Going to copy $repo dump to $baktousb directory...\n"
          cp $bakdest/$repo-$bakdate.svn.dump $baktousb
        fi 
      done
    else
      echo "Unable to continue SVN backup process."
      echo "$bakdest is *NOT* a directory or you do not have write permission."
    fi
     
     
    # End of backup script
    echo "\n\n================================="
    echo " - Backup Complete, THANK YOU :-]"
    

  2. Install, configure and start subversion in the new server.
    help: https://injustfiveminutes.com/2013/01/24/how-to-install-subversion-on-centos-6-x/
    1. yum install subversion
      
      
    2. /usr/sbin/useradd svn
      
      
    3. passwd svn
      
      
    4. cd /var
      
      
    5. mkdir repos_svn
      This is where our repositories will reside
    6. chown -R apache:svn repos_svn/
      You can use cat /etc/passwd | cut -d: -f1 and cat /etc/group |cut -d: -f1 list users & groups
      
      
    7. chmod -R 774 repos_svn/
      
      
    8. chmod -R 774 /var/repos_svn/
      
      
    9. chmod g+s /var/repos_svn/
      
      
    10. Configure the svnserve.conf file. This file can be obtained by creating a test repository.
    11. svnadmin create /var/repos_svn/test
      
      
    12. cp /var/repos_svn/test/conf/svnserve.conf .
      
      
    13. Made the folowign modifications to the svnserve.conf file.

      auth-access = write
      anon-access = none
      realm = SDU
      

      I did not enable the "password-db = passwd" line because I'm going to use sasl2 passwords file instead of the default plain text password file. I will create the sasl2 password file first for testing and later replace it with the file from my old server (in order to migrate existing users).
    14. saslpasswd2 -f /var/repos_svn/sasl2db -u SDU testusername
      
      
    15. svnserve -d --root=/var/repos_svn/ --config-file=/var/repos_svn/svnserve.conf
      Start the subversion daemon with the global configuration file.
    16. netstat -tulpn
      Check whether the daemon is listening on port 3690
    17. Since iptables is "on" by default on CentOS and rejects any traffic on ports except 22, I had to add rules to allow svn port before testing.
      /etc/init.d/iptables start
      chkconfig iptables on
      
      
      iptables --line-numbers -n -L
      # HTTP
      iptables -I INPUT 5 -p tcp --dport 80 -j ACCEPT
      # SVN
      iptables -I INPUT 6 -p tcp --dport 3690 -j ACCEPT
      
      /etc/init.d/iptables save
      
      help: http://www.cyberciti.biz/faq/turn-on-turn-off-firewall-in-linux/
    18. The test repository created was available at svn://192.248.10.122/test


  3. Restore repository dumps.
    svn-restore.sh
    #!/bin/sh
     
    # From where to load repos?
    # ---------------------------
    svnrepos="/var/repos_svn"
    echo "\nGoing to restore all SVN repos to: $svnrepos \n"
     
    # Where to load the dump from?
    # ------------------------
    bakdest="/root/svn-backups"
     
    # _________NO-COFIG-REQUIRED-BELOW-THIS-LINE___________
     
    # First go to SVN repo dumps folder
    cd $bakdest 
     
    # Just make sure we have write access to backup-folder
    if [ -d "$svnrepos" ] && [ -w "$svnrepos" ] ; then
      # Now $repo has folder names = project names
      for repo in *; do
     # do svn dump for each project
     echo "Restoring backup/svndump of: $repo"
     echo "Executing : svnadmin create $svnrepos/$repo \n"
     svnadmin create $svnrepos/$repo
     echo "Executing : svnadmin load $svnrepos/$repo < $repo \n"
     svnadmin load $svnrepos/$repo < $repo
      done
    else
      echo "Unable to continue SVN restoration."
      echo "$svnrepos is *NOT* a directory or you do not have write permission."
    fi
     
     
    # End of backup script
    echo "\n\n================================="
    echo " - Restoration Complete, THANK YOU :-]"
    

  4. Copy the sasl2db password file from old server to the new server
  5. Relocate working copies to point to the new server



Notes

If your subersion client gives the error "Could not obtain the list of SASL mechanisms" try installing the cyrus-sasl-md5 package and restarting the subversion daemon.

If you get the error "SASL(-1): generic failure: unable to find a callback: 2" make sure that the /etc/sasl2/svn.conf file looks like below

/etc/sasl2/svn.conf
pwcheck_method: auxprop
auxprop_plugin: sasldb
sasldb_path: /var/repos_svn/sasl2db
mech_list: DIGEST-MD5

in detail: http://www.question-defense.com/2009/06/16/could-not-obtain-the-list-of-sasl-mechanisms


Additional Resources

A shell script for easily starting and stopping subversion daemon.
svnserve.sh
#!/bin/sh
REPO_ROOT=/var/repos_svn
#SVN_UID=subversion
#SVN_GID=subversion
#. /etc/rc.status
#rc_reset
case "$1" in
   start)
        echo -n "Starting svnserve ... "
        #startproc -u $SVN_UID -g $SVN_GID -e svnserve -d -R -r $REPO_ROOT
        svnserve -d --root=$REPO_ROOT/ --config-file=$REPO_ROOT/svnserve.conf
        echo "[OK]"
        #rc_status -v
        ;;
   stop)
        echo -n "Shutting down svnserve ... "
        #killproc -TERM svnserve
        killall svnserve
        #rc_status -v
        ;;
   restart)
        $0 stop
        $0 start
        #rc_status
        ;;
   *)
        echo "Usage: $0 {start|stop|restart}"
        exit 1
        ;;
esac
#rc_exit
exit 0

A shell script for easily creating new project repositories.
svn-new.sh
#!/bin/bash

if [ "$#" != 1 ]; then
        echo "You need exactly one parameter ."
else
        if [ -d /var/repos_svn/$1 ]; then
                 echo "[Error]: Repository \"$1\" already exists."
        else
                cd /var/repos_svn

                echo "Initializing the project in the repository ..."
                svnadmin create /var/repos_svn/$1
                chown -R www-data:svn repos_svn/
                chmod -R 774 repos_svn/
                echo "Done."

                echo "Please use the convention of directories \\trunk \\tags \\branches for storing your project"
  #echo "You may need to start the svn-server using the command \"/etc/init.d/svnserve start\""
                echo "Please note that you have to Check-Out the project from the repository before working."
                echo "Your repository URL is: svn://192.248.10.122/$1"
                #echo "Don't forget to create the users with: htpasswd /etc/apache2/dav_svn.passwd "
                echo "Don't forget to create users with: \"saslpasswd2 -f /var/repos_svn/sasl2db -u SDU \" command"
        fi
fi


NAT rules for public IP access
I also defined NAT rule to the new server from my primary server to allow it access via a public IP address.

Made the folowign modification to the /etc/sysctl.conf file.

/etc/sysctl.conf
net.ipv4.ip_forward = 1

Issued following commands to configure the NAT forwarding.

sysctl -p /etc/sysctl.conf
iptables -I FORWARD -i eth1 -p tcp -d 192.168.22.143 --dport 3690 -j ACCEPT
# delete the reject rule to allow opening of random port to forward request
iptables -D FORWARD 2
# add NAT rules
iptables -t nat -A PREROUTING -i eth1 -p tcp --dport 3690 -j DNAT --to-destination 192.168.22.143:3690
iptables -t nat -A POSTROUTING -o eth1 -j MASQUERADE
# check whether successful
/etc/init.d/iptables status
# save
/etc/init.d/iptables save

Enable listing of repositories via HTTP

yum install mod_dav_svn

# edit /etc/httpd/conf.d/subversion.conf
<Location /svn>
  DAV svn
  SVNParentPath /var/repos_svn
  SVNListParentPath On
  <LimitExcept GET PROPFIND OPTIONS REPORT>
     Require valid-user
  </LimitExcept>
</Location>
<Location ~ "/svn/.+">
   Require valid-user
</Location>
# restart httpd
service httpd restart

Wednesday, August 28, 2013

NATting with iptables on Ubuntu

/etc/sysctl.conf:
net.ipv4.ip_forward = 1

sysctl -p /etc/sysctl.conf

/etc/init.d/networking restart

# list rules
iptables -L
iptables -t nat -L
iptables -t nat -L -n -v

# flush rules & NATs
iptables --flush
iptables --table nat --flush

iptables -t nat -A PREROUTING -p tcp –-dport 80 -j DNAT –-to-destination 192.168.22.122:80
iptables -t nat -A PREROUTING -p tcp –-dport 3306 -j DNAT –-to-destination 192.168.22.122:3306
iptables -t nat -A PREROUTING -p tcp –-dport 2222 -j DNAT –-to-destination 192.168.22.122:22

iptables -t nat -A POSTROUTING -j MASQUERADE


####### Auto Loading Rules at System Start-up #######

#1 save rules to file
iptables-save > /etc/iptables.rules

#2 find interfaces
iwconfig
ifconfig

#3 edit config file
vi /etc/network/interfaces

#4 add line to relevent interface (iface eth1)
pre-up iptables-restore < /etc/iptables.rules

Thursday, January 10, 2013

See all connected users to MySQL

SHOW PROCESSLIST;

Caveat: you need to execute this command as a user with the PROCESS privilege in order to see all of the other users who are connected to the database. Otherwise, you see only those server threads that belong to the same user as the one making the query.

Thanks to : David Harper - Cambridge, England

Reference: http://dev.mysql.com/doc/refman/5.0/en/show-processlist.html

Wednesday, January 2, 2013

Proxying via Apache

Inside the <virtualhost> section add following.
<Proxy *>
 Order deny,allow
 Allow from all
</Proxy>

ProxyRequests Off
ProxyPreserveHost Off

ProxyPass /proxy/ http://www.newurl.com/
ProxyPassReverse /proxy/ http://www.newurl.com/