PostgreSQL: cursor example

create or replace function test_cursor() returns void as
$$
declare
    _my_cursor(p_co_id text) for
        select * from crm_db.ht_co_addon where co_id = p_co_id;
    _idx int := 0;
    _row record;
begin
    -- Example 1
    for _row in _my_cursor('000000000666') loop
        _row.co_addon_id := _idx;
        _idx := _idx + 1;
    end loop;

    -- Example 2
    open _my_cursor('000000000666')
    loop
        fetch _my_cursor into _row
        exit when not found
        raise notice 'co_addon_id=%', _row.co_addon_id
    end loop
    close _my_cursor
end;
$$ language plpgsql security definer;

PostgreSQL: hstore or a perl like hash table

create or replace function test_hstore() returns void as
$$
declare
    _my_cursor(p_co_id text) for
        select * from crm_db.ht_co_addon where co_id = p_co_id;
    _row record;
    _addon_id2co_addon_id_h hstore;
    _sms_ao text := 'SMS_AO';
begin
    for _row in _my_cursor('000000000666') loop
        if _addon_id2co_addon_id_h is null then
            _addon_id2co_addon_id_h := (_row.addon_id||'=>'||_row.co_addon_id)::hstore;
        else
            _addon_id2co_addon_id_h := _addon_id2co_addon_id_h || (_row.addon_id||'=>'||_row.co_addon_id)::hstore;
        end if;
    end loop;

    raise notice 'SMS_AO: %', _addon_id2co_addon_id_h->_sms_ao;

    for _row in select * from each(_addon_id2co_addon_id_h) loop
        raise notice 'h: %=>%', _row.key, _row.value;
    end loop;
end;
$$ language plpgsql security definer;

Linux: remove files older than x days

E.g. Remove files older than 90 days

find . -mtime +90 -exec rm {} \;

Git merge master into feature branch

Source: http://stackoverflow.com/questions/16955980/git-merge-master-into-feature-branch

You should be able to rebase your branch on master:

git checkout feature1
git rebase master

Manage all conflicts that arise. When you get to the commits with the bugfixes (already in master), git will say that there were no changes and that maybe they were already applied. You then continue the rebase (while skipping the commits already in master) with

git rebase --skip

If you perform a git log on your feature branch, you'll see the bugfix commit appear only once, and in the master portion.

git: change remote repository

Context: I have used bitbucket as a remote git repository so far, and now I want to switch to a corporate git server.

Following the instructions detailled here: http://www.smashingmagazine.com/2014/05/19/moving-git-repository-new-server/
Here are the steps eventually run:

git fetch origin
git remote add new-origin ssh://john.smith@192.168.2.3/volume1/git-server/is.git
git pull new-origin master
git push --all new-origin
git push --tags new-origin
git remote rm origin
git remote rename new-origin origin

Git: Sync remote repository w/ local

From http://stackoverflow.com/questions/6373277/git-sync-local-repo-with-remote-one

cd $HOME/src
git pull -p

NB: git pull runs git fetch with the given parameters and calls git merge to merge the retrieved branch heads into the current branch - git-scm.com/docs/git-pull

-p, --prune After fetching, remove any remote-tracking branches which no longer exist on the remote

Git workflows

https://www.atlassian.com/git/tutorials/comparing-workflows

SSH with keep alive option

ssh -o "ServerAliveInterval 600" yapit

Disable Strict SSH Checking on Source Peer

By appending the following to your ~/.ssh/config file you can disable SSH prompts which ask to add new hosts to the “known hosts file.” Note that the ${DESTPEERHOST} should be the name of the host machine running the destination peer:

Host ${DESTPEERHOST}
CheckHostIP no
StrictHostKeyChecking no
BatchMode yes

SSH to tondu as omnitest without password

• As gallinar, in .ssh/config, comment “Batchmode yes” so as to temporarily get a password question when sshing:

 #BatchMode yes

• As gallinar, in .ssh/config, add the following lines in order to log to tondu as omnitest:

 Host tndu
 HostName tondu
 User omnitest

• As gallinar, if not already done, generate a public key:

 ssh-keygen -t rsa

• As gallinar, copy the public key to the target:

 scp ~/.ssh/id_rsa.pub tondu:/tmp

• As omnitest, on tondu:

 cat /tmp/id_rsa.pub >> ~/.ssh/authorized_keys

• As gallinar, in .ssh/config, uncomment “Batchmode yes”, and remove /tmp/id_rsa.pub on tondu.

Configured ssh on host for no password but still asked

Check $HOME/.ssh and $HOME rights. If writeable for group or world, a password will be asked.

> chmod 700 $HOME/.ssh 
> chmod 600 $HOME/.ssh/authorized_keys

Virtualbox: Shrinking VM Disk Images

Source: http://snippets.khromov.se/shrinking-a-virtualbox-linux-image-with-zerofree/

• Download the SystemRescueCd Live Image
Mount it as a CD on your virtual machine and boot into the live distro
• Find all available partition and LVM (volume groups) with:

fdisk -l

• After we find our partition run zerofree to zero out the free space. In our example we will run it on the LVM root partition

zerofree -v /dev/mapper/vg_rheldev-lv_root

• Finally, boot down your virtual machine, and run VBoxManage on the host to resize the .vdi file

C:\>"c:\Program Files\Oracle\VirtualBox\VBoxManage.exe" modifyhd "c:
\Users\jerome\VirtualBox VMs\Monster\Mobiquithings.vdi" --compact

With this method, my VM shrunk from 200 GiB to 26 GiB

NB: The other method mentioned on the net replacing zerofree with dd like in

dd -if /dev/zero -of /bigemptyfile -bs 4096; rm /bigemptyfile

was not satisfactory for me.

Radius: run time variables

http://wiki.freeradius.org/config/Run%20time%20variables

xemacs: Unable to load any usable fontset

If running XEmacs causes these warnings about fonts:

[jerome@mathusalem] > xemacs
Warning: Missing charsets in String to FontSet conversion
Warning: Cannot convert string "-*-helvetica-bold-r-*-*-*-120-*-*-*-*-iso10646-1,                               -*-helvetica-bold-r-*-*-
*-120-*-*-*-*-iso8859-*,                                *" to type FontSet
Warning: Missing charsets in String to FontSet conversion
Warning: Unable to load any usable fontset
Warning: Missing charsets in String to FontSet conversion
Warning: Unable to load any usable fontset

Then, try running it this way:

[jerome@mathusalem] > LANG=C xemacs

Windows Virtualbox: increase CentOS 6 VDI size

1. Create a new VDI disk under Controller: SATA, sized appropriately





2. Clone existing VDI into new one

C:\Program Files\Oracle\VirtualBox> VBoxManage.exe clonehd "c:\Users\jerome\VirtualBox VMs\Mathusalem\Mathusalem.vdi" --existing "c:\Users\jerome\VirtualBox VMs\Mathusalem\Mathusalem 2.vdi"

0%...10%...20%...30%...40%...50%...60%...70%...80%...90%...100%
Clone hard disk created in format 'VDI'. UUID: 48da07eb-3395-4fad-95e3-c0615b185da2

3. Remove legacy VDI



4. Download GParted ISO from GParted Live CD
5. Mount GParted ISO under Controller: IDE



6. Start the VM, which will boot on the GParted live CD
7. Extend the existing LVM2 partition (/dev/sda2) with the new unallocated space
8. Stop the VM, detach the GParted ISO, and reboot the VM
9. Extend the logical volume into the resized partition
By default, CentOS uses LVM (Logical Volume Manager), which requires an extra step.
Identify the name of the logical volume via df:

df -h



Then force the LVM volume to take 100% of the available space on the partition:

lvextend -l +100%FREE /dev/mapper/vg_mathusalem-lv_root

10. Resize file system

resize2fs /dev/mapper/vg_mathusalem-lv_root


After this step, we are now good to go.

Oracle: split / join

See http://stackoverflow.com/questions/381231/hidden-features-in-oracle

SQL> declare
  2    v_array apex_application_global.vc_arr2;
  3    v_string varchar2(2000);
  4  begin
  5  
  6    -- Convert delimited string to array
  7    v_array := apex_util.string_to_table('alpha,beta,gamma,delta', ',');
  8    for i in 1..v_array.count
  9    loop
 10      dbms_output.put_line(v_array(i));
 11    end loop;
 12  
 13    -- Convert array to delimited string
 14    v_string := apex_util.table_to_string(v_array,'|');
 15    dbms_output.put_line(v_string);
 16  end;
 17  /
alpha
beta
gamma
delta
alpha|beta|gamma|delta

Oracle: dense_rank explanation

RANK, DENSE_RANK, FIRST and LAST Analytic Functions

Oracle: return a row even if select returns no rows

SELECT nvl(data_name, 'nothing') FROM (  
    SELECT data_name
    FROM data_table
    WHERE data_table.type = v_t_id

    UNION ALL

    SELECT NULL AS data_name
    FROM dual
  ) WHERE data_name is not null or ROWNUM = 1;

perl: one-liner with single quotes in

cat /media/host/export_PYF_NCL.csv | perl -e 'while(<>){print "'"'"'$1'"',"'" if /^(\d+);/}'

OSX Yosemite: Write into NTFS USB stick

Suppose you have an NTFS formatted USB stick labelled "USB", then

1. vi /etc/fstab
   
2. Eject then re-insert the USB stick
3. The device does no longer shows up in the Finder under Devices, but you can access it under /Volumes, and it has become writeable