Using Postgresql along with MySql

I came across a scenario where my existing application database which was residing in MySql wanted to create/communicate to tables present in PostgreSql. Though it is not advisable but i had no other option. so i googled a lot, went through n number of blogs and finally found a solution which suited my requirement this worked for me.

Install PostgreSql has explained in my previous post.

Configure your database.yml

development:
  adapter: mysql2
  encoding: utf8
  database: test_development
  username: root
  password: *******
  host: 127.0.0.1


development_postgres:
  adapter: postgresql
  database: test_dev
  username: pgs_root
  password: ********
  host: 127.0.0.1


Create the database

rake db:create RAILS_ENV=development_postgres

This will create a database named test_dev in PostgreSQL

Specify the connection in model so that the application understands where the table resides

establish_connection :development_postgres

if you wish to have it environment specific, write

establish_connection Rails.env+"_postgres"


In migration file, specify the connection

def self.connection
  modelname.connection
end
def self.up
      
end

   This self.connection allows the application to communicate with database specified in the model. This has to be written only if you want to run migration belonging to postgres, failing to specify migration will run in default database i.e mysql

Establishing relationship between the tables present in two databases

for ex. table named "foo" exists in postgres and it belongs to "boo" present in mysql.

In foo.rb

establish_connection Rails.env  (relationship  database connection)
belongs_to :boo
establish_connection Rails.env+"_postgres" (database connection)

In boo.rb

establish_connection Rails.env+"_postgres"
has_many  :foo
establish_connection Rails.env

This establishes HasManyBelongsTo relationship between boo & foo.
Note:Always Specify the relationship database connection first & table connection at the end. All the migration no. will be stored in schema_migration table present in mysql.


This is the only approach which worked for me, if there is a better solution other than the above please let me know.

Installing Postgresql & using it in rails 3 application

If your are going to build a portal application which is going to hold large data then trust me "Postgresql" is the best option.

1. Install PostgreSql

sudo apt-get install postgresql postgresql-contrib libpq-dev

2. After the installation is complete, create a user (like we create root user in mysql)

sudo -u postgres createuser pgs_root

This will create a user named pgs_root.

3. Now add password to the user

sudo -u postgres psql postgres
running this command will take you to psql prompt

postgres=# \passsword <user>

whatever password you wish to add must be entered twice, then type \q to exit psql prompt.

3. Configure the postgresql.conf file to make PostgreSQL listen to localhost or listen on an external IP or something, change this line to either the IP or '*' or 'localhost'.

/etc/postgresql/8.4/main/postgresql.conf (here 8.4 is the postgresql version, change it if you have installed different version)

under "CONNECTIONS AND AUTHENTICATION" uncomment the following line

listen_addresses = 'localhost'

check whether you have the following line in your /etc/postgresql/8.4/main/pg_hba.conf


# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD


# "local" is for Unix domain socket connections only
local   all         all                               ident
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5

5. Now postgreSQL setup is complete. To use in the application add the "pg" gem in Gemfile

gem "pg"

run bundle install

6.  In order for your application to connect with database present in PostgreSQL, configure it in the database.yml

   development:
    adapter: postgresql
    database: test_postgres
    username: pgs_root
    password: *******
    host: 127.0.0.1

7.  Create the database

    rake db:create

This will create a database named test_postgres in PostgreSQL and it is ready to use.

There is a nice GUI tool for Postgresql named pgadmin3. To install:

sudo apt-get update
sudo apt-get install pgadmin3

All the best...!!!

Using rubyzip in Rails 3

While downloading large files its better to zip it at the server end so that the download becomes much faster.

There is a gem called rubyzip to perform this action.

Add this in your Gemfile

gem "rubyzip", :require => 'zip/zip'

run bundle install

Suppose i have a "folder" which 'has_many' files. So when i try to download the folder, the entire files linked to folder must be downloaded as a zip file. In Folders controller

def download_folder
  @folder = Folder.find(params[:id])
  temp = Tempfile.new("zip-file-#{Time.now}")
  Zip::ZipOutputStream.open(temp.path) do |z|
    @folder.libraries.each do |file|
      z.put_next_entry(file.uploaded_file_file_name)
      z.print IO.read(file.uploaded_file.path)
    end
  end
  send_file temp.path, :type => 'application/zip', :disposition => 'attachment', :filename => "#{@folder.name}.zip"
  temp.delete() #To remove the tempfile
end


Thats it!!!...