clean_sonar.rb

require 'java'
# Update with the name of jar you have downloaded
require 'mysql-connector-java-5.0.8-bin.jar'


Java::com.mysql.jdbc.Driver

# Update the JDBC details as needed
jdbc_url = 'jdbc:mysql://localhost:3306/sonar?useUnicode=true&characterEncoding=utf8'
login = 'sonar'
password = 'sonar'

connection = java.sql.DriverManager.get_connection(jdbc_url, login, password)

query =<<-EOS
select project_id, cnt
  from (select project_id,
          count(*) as cnt
        from snapshots
        where islast=1
        group by project_id) as cntsnap
  where cnt > 1;
EOS

statement = connection.create_statement
result_set = statement.execute_query(query)

while result_set.next()
  project_id = result_set.getInt(1)

  another_statement = connection.prepare_statement("select * from snapshots where project_id = ? and islast = 1 order by id")
  another_statement.setInt(1, project_id)
  example_rset = another_statement.execute_query

  # Only delete the first record
  if example_rset.next
    example_to_delete = example_rset.getInt("id")
    delete_statement = connection.prepare_statement("delete from snapshots where id = ?")
    delete_statement.setInt(1, example_to_delete)
    delete_statement.execute_update
    puts "Deleted record #{example_to_delete}"

    delete_statement.close()
  end

  example_rset.close()
  another_statement.close()
end

result_set.close()
statement.close()