Gmail scraping by using mechanize gem

require 'rubygems'

require 'mechanize'

agent = Mechanize.new

page = agent.get 'http://www.gmail.com'

form = page.forms.first

form.Email = 'your gmail id here'

form.Passwd = 'your gmail here'

page = agent.submit form

puts page.inspect

You will get gmail page html source.

Searching in integer Arrays in postgres

SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);

In addition, you can find rows where the array has all values equal to 10000 with:

SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

Alter natively, the generate_subscripts function can be used. For example

 SELECT * FROM
   (SELECT pay_by_quarter,
           generate_subscripts(pay_by_quarter, 1) AS s
      FROM sal_emp) AS foo
 WHERE pay_by_quarter[s] = 10000;

Real time example:

Structure 

contactids, companyids,added_by_search_contactids,added_by_search_companyids => integer array  

1. SELECT * FROM  (SELECT contactids,generate_subscripts(contactids, 1) AS s 

   FROM lists limit 50000 ) AS foo 

   WHERE contactids[s]  IN (select id from contacts where current_details_id IS NULL); 

2. UPDATE lists SET contactids = coalesce(contactids,ARRAY[]::INT[]) - coalesce(ARRAY(select id 

   from contacts where current_details_id IS NULL),ARRAY[]::INT[]),

   added_by_search_contactids = coalesce(added_by_search_contactids,ARRAY[]::INT[]) - 

   coalesce(ARRAY(select id from contacts where current_details_id IS NULL),ARRAY[]::INT[]),

   companyids = coalesce(companyids,ARRAY[]::INT[]) - coalesce(ARRAY(select id from

   companies where current_details_id IS NULL),ARRAY[]::INT[]),

   added_by_search_companyids = coalesce(added_by_search_companyids,ARRAY[]::INT[]) - 

   coalesce(ARRAY(select id from companies where current_details_id IS NULL),ARRAY[]::INT[]) 

   WHERE id IN (SELECT id FROM (SELECT id,contactids,generate_subscripts(contactids, 1)AS s FROM lists

   WHERE current_step >= 4 AND folder_id IS NOT NULL and is_deleted IS FALSE LIMIT 20000 OFFSET 80000) AS foo 

   WHERE contactids[s] = ANY(select id from contacts where current_details_id IS NULL) UNION SELECT id 

   FROM (SELECT id,added_by_search_contactids,generate_subscripts(added_by_search_contactids, 1)AS s FROM lists WHERE 

   current_step >= 4 AND folder_id IS NOT NULL and is_deleted IS FALSE LIMIT 20000 OFFSET 80000) AS foo 

   WHERE added_by_search_contactids[s] = ANY(select id from contacts where current_details_id IS NULL) 

   UNION SELECT id FROM (SELECT id,companyids,generate_subscripts(companyids, 1)AS s FROM lists 

   WHERE current_step >= 4 AND folder_id IS NOT NULL and is_deleted IS FALSE LIMIT 20000 OFFSET 80000) AS foo WHERE 

   companyids[s] = ANY(select id from companies where current_details_id IS NULL) UNION SELECT id FROM 

   (SELECT id,added_by_search_companyids,generate_subscripts(added_by_search_companyids, 1)AS s FROM lists 

   WHERE current_step >= 4 AND folder_id IS NOT NULL and is_deleted IS FALSE LIMIT 20000 OFFSET 80000) AS foo 

   WHERE added_by_search_companyids[s] = ANY(select id from companies where current_details_id IS NULL)) 

  RETURNING lists.id

More information here

http://www.postgresql.org/docs/current/static/arrays.html
http://www.postgresql.org/docs/current/static/functions-srf.html#FUNCTIONS-SRF-SUBSCRIPTS.

If you have  any quires please post your comments here.

Gmail SMTP setting in rails3

SMTP setting for rails3and put new gmail.rb then run.

require 'rubygems'
require 'action_mailer'
require 'tlsmail'

  Net::SMTP.enable_tls(OpenSSL::SSL::VERIFY_NONE)
  ActionMailer::Base.delivery_method = :smtp
  ActionMailer::Base.perform_deliveries = true
  ActionMailer::Base.default_charset = "utf-8"
  ActionMailer::Base.raise_delivery_errors = true


ActionMailer::Base.smtp_settings = {
  :enable_starttls_auto => true,
  :address => "smtp.gmail.com",
  :port => 587,
  :domain => "domainname",
  :authentication => :plain,
  :user_name => "notifications@domainname",
  :password => "password"
}


class Emailer < ActionMailer::Base
  def test_email()
    subject    "Test message"
    from       "Test mail"
    recipients 'recipients@domainname.com'
    body        "Test mail for gmail SMTP setting"
  end
end

Emailer.deliver_test_email()

PostgreSQL row sort with NULL values and empty string at last

ORDER BY NULLIF(first_name, '') DESC NULLS LAST
More examples here

Ubuntu OS process state code

       Ubuntu OS  process state codes are

      1.  D   uninterruptible sleep (usually IO)
      2.  R   runnable (on run queue)
      3.  S   sleeping
      4.  T   traced or stopped
      5.  Z   Defunct ("zombie") process, terminated but not reaped by its parent
      6. X   dead (should never be seen)
      7. W  paging (not valid since the 2.6.xx kernel)

What is the performance difference distinct vs group by SQL command

Don't use distinct command.It performance way is slow. group by command performance very high compare to distinct command.Please see the real time difference in the below example query.

1) select count(*) from (select distinct contact_id from contacts_lists) a;

 count
 65474

 1 row(s)

 Total runtime: 40,800.186 ms

2) select count(*) from (select contact_id from contacts_lists group by contact_id) a;

 count
 65474

 1 row(s)

 Total runtime: 32,302.381 ms

PGError: ERROR: duplicate key value violates unique constraint

Syntax: 

SELECT setval('table_name_id_seq'::regclass, MAX(id)) FROM table_name;

Example of usage:

SELECT setval('personal_contacts_personal_folders_id_seq'::regclass, MAX(id)) FROM personal_contacts_personal_folders;