Forum: Ruby Finding out if a Mysql database contains a certain table

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
ngw (Guest)
on 2006-02-19 18:31
(Received via mailing list)
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 :p
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
Caleb T. (Guest)
on 2006-02-19 19:32
(Received via mailing list)
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 :p
> 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
David V. (Guest)
on 2006-02-19 20:48
(Received via mailing list)
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.
Gregory B. (Guest)
on 2006-02-20 03:01
(Received via mailing list)
On 2/19/06, ngw <removed_email_address@domain.invalid> 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
This topic is locked and can not be replied to.