Skip to content

Instantly share code, notes, and snippets.

@tychobrailleur
Created August 31, 2011 08:05
Show Gist options
  • Save tychobrailleur/1183053 to your computer and use it in GitHub Desktop.
Save tychobrailleur/1183053 to your computer and use it in GitHub Desktop.
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()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment