Finding out if a Mysql database contains a certain table

Ruby
1

Hi *,
I need to find out if a MySQL db contains a certain table, if not I have
to create it.
I’m using the SHOW TABLES instruction that returns a Mysql::Result
object, but after that I’m stuck, I don’t know how to process the
resultset without writing awful code :stuck_out_tongue:
I mean, I can resolve the problem by using a “state” variable inside a
block and turn it to true if it finds the table, but I guess there’s a
more appropriate way to handle this kind of problems.

result = dbh.query(“SELECT * FROM album”)
foo = false
result.each {|row| foo = true if row.include?(“Killing Joke”)}
if foo == true
puts “HA!”
end
result.free

This is the code I have now, UGH !

TIA,
ngw

2

On Feb 19, 2006, at 11:28 AM, ngw wrote:

Hi *,
I need to find out if a MySQL db contains a certain table, if not I
have
to create it.
I’m using the SHOW TABLES instruction that returns a Mysql::Result
object, but after that I’m stuck, I don’t know how to process the
resultset without writing awful code :stuck_out_tongue:
I mean, I can resolve the problem by using a “state” variable inside a
block and turn it to true if it finds the table, but I guess there’s a
more appropriate way to handle this kind of problems.

A show tables command just returns a query of table names, 1 per row.

I’m not sure if you need to do something more complex, but in MySQL
it’s valid to do:

CREATE TABLE IF NOT EXISTS

Caleb

3

DÅ?a Nedeľa 19 Február 2006 18:31 Caleb T. napísal:

A show tables command just returns a query of table names, 1 per row.

Sidenote: SHOW TABLES is a MySQLism, so watch out it you can’t rule out
the
data will be migrated to another DB.

David V.

4

On 2/19/06, ngw [email protected] wrote:

I mean, I can resolve the problem by using a “state” variable inside a
block and turn it to true if it finds the table, but I guess there’s a
more appropriate way to handle this kind of problems.

Using Ruport as it is in the svn trunk (should be released today, i’d
do something like this:

require “ruport”

Ruport::Config.source :default,
:dsn => “dbi:mysql:my_dbname”, :user => “myuser”, :password => “pass”

table = “some_table_i’m_looking for”

show_query = Ruport::Query.new(“show tables”)
creation_query = Ruport::Query.new("create table … ")

exit if show_query.result.any? { |r| r[0].eql? table }
creation_query.execute

To demonstrate this, here it is in irb below.
The same approach can be taken with any enumerable result set (Including
DBI’s)

irb(main):001:0> require “ruport”
=> true

irb(main):002:0> Ruport::Config.source :default,
irb(main):003:0* :dsn => “dbi:mysql:bills_development”, :user => “root”
=> nil

irb(main):004:0> show_tables = Ruport::Query.new “show tables”
=> #<Ruport::Query:0xb778678c @cached_data=nil, @raw_data=nil,
@password=nil, @statements=[“show tables”], @user=“root”,
@dsn=“dbi:mysql:bills_development”, @sql=“show tables”,
@cache_enabled=nil>

irb(main):005:0> puts show_tables.result
fields: ( Tables_in_bills_development )
row0: ( users )
=> nil

irb(main):006:0> table = “apple”
=> “apple”

irb(main):007:0> show_tables.result.any? { |r| r[0].eql? table }
=> false

irb(main):008:0> Ruport::Query.new(“create table #{table}”).execute
=> nil

irb(main):009:0> show_tables.result.any? { |r| r[0].eql? table }
=> false

irb(main):013:0> Ruport::Query.new(“create table #{table} ( x int
);”).execute
=> nil

irb(main):014:0> show_tables.result.any? { |r| r[0].eql? table }
=> true