Tags » Postgresql

convert empty string to null in column

ALTER TABLE table_name ALTER COLUMN column_name
SET DATA TYPE DATE USING NULLIF(column_name, ”)::DATE;

link trung bat dong

select *from (select a.link from (select link from sunday_exp2 group by link,quest having count(*)>1) as a left join (select link from sunday_exp2 group by link,quest,answer having count(*)>1) as b on a.link = b.link where b.link is null) as c inner join (select *from sunday_exp2) as d on c.link = d.link… 307 more words

Postgresql

JRuby Vagrant CentOS VirtualBox

My notes for creating a Vagrant CentOS VirtualBox for JRuby development.

This setup uses PostgreSQL, Torque Box and Chef.

# Centos 6.5 Minimal Install in VirtualBox VM (1 Core, 512MB Ram, 10GB Disk)
# VM is named: jruby-centos65
# Root password is set to: vagrant

ifup eth0

sed -i -e 's/^SELINUX=.*/SELINUX=permissive/' /etc/selinux/config

sed -i -e 's/^#\(UseDNS\).*$/\1 no/' /etc/ssh/sshd_config
sed -i -e 's/^#\(TCPKeepAlive.*\)$/\1/' /etc/ssh/sshd_config
sed -i -e 's/^#\(ClientAliveInterval\).*$/\1 120/' /etc/ssh/sshd_config

chkconfig ip6tables off
chkconfig iptables off
chkconfig iscsi off
chkconfig iscsid off
chkconfig postfix off

# Check that this rpm is correct version
yum install http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm

yum -y update

yum groupinstall -y 'development tools'

yum install -y man git vim wget curl ntp screen kernel-devel java-1.7.0-openjdk \
java-1.7.0-openjdk-devel curl-devel sqlite-devel libyaml-devel readline-devel zlib-devel \
 libffi-devel openssl-devel nodejs-devel

chkconfig ntpd on
ntpdate time.centos.org
service ntpd start

# Check that this rpm is correct version
rpm -ivh http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-redhat93-9.3-1.noarch.rpm
yum install postgresql93-server postgresql93-contrib

chkconfig postgresql-9.3 on
service postgresql-9.3 initdb

su - postgres
sed -i -e 's/^\(host.*\)ident$/\1trust/g' 9.3/data/pg_hba.conf
createuser -U postgres -SdRw vagrant
psql -c "ALTER USER vagrant WITH PASSWORD 'Passw0rd'"
exit

service postgresql-9.3 start

cat >/etc/profile.d/java_home.sh<<EOF
export JAVA_HOME=/usr/lib/jvm/java-1.7.0-openjdk.x86_64/
EOF

git clone git://github.com/sstephenson/rbenv.git /opt/rbenv
git clone git://github.com/sstephenson/ruby-build.git /opt/rbenv/plugins/ruby-build

echo 'export RBENV_ROOT=/opt/rbenv' > /etc/profile.d/rbenv.sh
echo 'export PATH="$RBENV_ROOT/bin:$PATH"' >> /etc/profile.d/rbenv.sh
echo 'eval "$(rbenv init -)"' >> /etc/profile.d/rbenv.sh

source /etc/profile.d/rbenv.sh

rbenv install jruby-1.7.12
rbenv global jruby-1.7.12
rbenv rehash

gem install bundler activerecord-jdbcsqlite3-adapter activerecord-jdbcpostgresql-adapter \
 jdbc-postgres warbler warbler-exec jruby-lint torquebox torquebox-server
rbenv rehash

cp /opt/rbenv/versions/jruby-1.7.12/lib/ruby/gems/shared/gems/torquebox-server-3.1.0-java/jboss/bin/init.d/jboss-as-standalone.sh /etc/init.d/jboss-as-standalone

vi /etc/init.d/jboss-as-standalone
# Add the following lines after the functions library
export JRUBY_HOME=/opt/rbenv/versions/jruby-1.7.12

cat >/etc/jboss-as/jboss-as.conf<<EOF
# General configuration for the init.d script
JBOSS_USER=vagrant
JBOSS_HOME=/opt/rbenv/versions/jruby-1.7.12/lib/ruby/gems/shared/gems/torquebox-server-3.1.0-java/jboss
JBOSS_PIDFILE=/var/run/torquebox/torquebox.pid
JBOSS_CONSOLE_LOG=/var/log/torquebox/console.log
JBOSS_CONFIG=standalone.xml
EOF

curl -L https://www.opscode.com/chef/install.sh | bash

sed -i -e 's/HOSTNAME.*/HOSTNAME=jruby-centos65/' /etc/sysconfig/network

cat >/etc/sysconfig/network-scripts/ifcfg-eth0 <<EOF
DEVICE=eth0
TYPE=Ethernet
ONBOOT=yes
NM_CONTROLLED=no
BOOTPROTO=dhcp
EOF

sed -i 's/^\(Defaults.*requiretty\)/#\1/' /etc/sudoers
echo "vagrant ALL=(ALL) NOPASSWD: ALL" >> /etc/sudoers.d/vagrant

useradd vagrant
mkdir -m 0700 -p /home/vagrant/.ssh

cat >/home/vagrant/.ssh/authorized_keys <<EOF
ssh-rsa <MY LAPTOP RSA PUB KEY>
EOF

chmod 600 /home/vagrant/.ssh/authorized_keys

chown -R vagrant:vagrant /home/vagrant/.ssh


# Activate new kernel from updates if one was installed
reboot

# Install the VirtualBox Guest Tools

# Post Cleanup
rm -f /etc/udev/rules.d/70-persistent-net.rules
yum clean all
rm -rf /tmp/*
rm -f /var/log/wtmp /var/log/btmp
history -c
shutdown -h now

<hr>

# Create a Vagrant Box
mkdir -p ~/Development/Vagrant/Boxes
cd ~/Development/Vagrant/Boxes

vagrant package --output jruby-centos65.box --base jruby-centos65

# At this point you can delete the VM from VirtualBox

vagrant box add jruby-centos65 jruby-centos65.box

mkdir -p ~/Development/Vagrant/Centy
cd ~/Development/Vagrant/Centy

cat >Vagrantfile<<EOF
# -*- mode: ruby -*-
# vi: set ft=ruby :

VAGRANTFILE_API_VERSION = "2"

Vagrant.configure(VAGRANTFILE_API_VERSION) do |config|
  config.vm.box = "jruby-centos65"
  config.ssh.private_key_path = "~/.ssh/id_rsa"
end
EOF

# Start the box
vagrant up

… 115 more words
Chef