Forum: Ruby on Rails how to combine SQL queries in self.up

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.
Ff834f7363c1dc9c48b525fc48c67912?d=identicon&s=25 Vipin (Guest)
on 2009-04-29 08:57
(Received via mailing list)
Hi

How can i combine multiple SQL statements into one place in self.up in
a migration.
For the following, method if i combine 3 SQL statements into one
section I get the error.

######### following works fine
def self.up
      execute <<-EOF
  CREATE TABLE pks (SNO TINYINT,BUSNO CHAR(20),EXPTIME TIME, PRIMARY
KEY (SNO));
      EOF
      execute <<-EOF
 INSERT INTO pks (SNO,BUSNO,EXPTIME) VALUES('1','5L',080000);
      EOF
      execute <<-EOF
 INSERT INTO pks (SNO,BUSNO,EXPTIME) VALUES('2','5A',081500);
      EOF

  end

########## it doesn't work

def self.up
      execute <<-EOF
  CREATE TABLE pks (SNO TINYINT,BUSNO CHAR(20),EXPTIME TIME, PRIMARY
KEY (SNO));
 INSERT INTO pks (SNO,BUSNO,EXPTIME) VALUES('1','5L',080000);
 INSERT INTO pks (SNO,BUSNO,EXPTIME) VALUES('2','5A',081500);
      EOF

  end
Dd2d775dea75b381edb1bbf0600a0907?d=identicon&s=25 Marnen Laibow-Koser (marnen)
on 2009-04-29 09:16
Vipin wrote:
> Hi
>
> How can i combine multiple SQL statements into one place in self.up in
> a migration.
[...]

You shouldn't need to, at least in the way you're asking about. Write
your migrations in Ruby, not raw SQL.

Best,
--
Marnen Laibow-Koser
http://www.marnen.org
marnen@marnen.org
Ef0db53920b243d6758c2f6b1306df0d?d=identicon&s=25 Steve Ross (cwd)
on 2009-04-30 02:12
(Received via mailing list)
ActiveRecord is available to you. Inside the self.up, you can do:

AnyArbitraryModel.connection.execute(all_of_your_sql)

Any model in your app will do, as you are bypassing your model's
business rules and going directly to the connection. The return is a
Mysql::Result, which you can look up here:
http://www.tmtm.org/en/ruby/mysql/

Hope this helps.
Ff834f7363c1dc9c48b525fc48c67912?d=identicon&s=25 Vipin (Guest)
on 2009-05-01 05:26
(Received via mailing list)
> You shouldn't need to, at least in the way you're asking about. Write
> your migrations in Ruby, not raw SQL.
>

because there is no other way i guess to have a non integer primary
key in Rails framework
--vipin
Ff834f7363c1dc9c48b525fc48c67912?d=identicon&s=25 Vipin (Guest)
on 2009-05-01 05:29
(Received via mailing list)
> AnyArbitraryModel.connection.execute(all_of_your_sql)
>

but in my case, my whole of the data is static and i want to populate
into the table at the time of migration itself.
It is going to be like creation of table followed by insertion of
1000s of rows into them.

Will it work there too?
If possible can you please explain the syntax of above command in the
context of my problem.

--vipin
Dd2d775dea75b381edb1bbf0600a0907?d=identicon&s=25 Marnen Laibow-Koser (marnen)
on 2009-05-01 08:50
Vipin wrote:
[...]
> because there is no other way i guess to have a non integer primary
> key in Rails framework
> --vipin

Be careful!  Rails *really* wants an integer primary key on most tables.
Although it may be possible to do without it, I understand that problems
may arise (Fred or other experts, can you confirm this?).  My advice
would be to let Rails put in the key that it wants.  It won't hurt
anything.

Best,
--
Marnen Laibow-Koser
http://www.marnen.org
marnen@marnen.org
885ac43bca92fc2b1034356f3283e788?d=identicon&s=25 pharrington (Guest)
on 2009-05-01 18:26
(Received via mailing list)
well...

I assume you're using a MySQL DB; If that is the case you actually
need to specify specific options when connecting to the server to
allow multiple queries in one statement. I've honestly never played
with that before, but
http://dev.mysql.com/doc/refman/5.0/en/c-api-multi...
should be a good start if you still want to go that route.

However, if your main concern is having a non-autoincrementing integer
as the primary key (which is perfectly legitimate), its not hard.
Write the migration is Ruby as normal, except also pass :id => false
to the create_table method. Then run a query to alter table to add the
primary key (you can also use this to add actual DB constraints and
foreign keys and what not, although if you do this alot you're better
off finding/writing a plugin to do this in a more Rubylike way).
Finally in your model, use
set_primary_key :name_of_primary_key_field.

On May 1, 2:50 am, Marnen Laibow-Koser <rails-mailing-l...@andreas-
This topic is locked and can not be replied to.