Forum: Ruby on Rails SQL queries in Rails

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.
Ed3050ec0a96c03ef1304e7ad1ff46fb?d=identicon&s=25 The Neurochild (Guest)
on 2008-11-03 23:25
(Received via mailing list)
Hi...

I want to know the equivalent instructions to insert, update, search
and delete a registry. I know Rails can do it easily Thanks to
ActiveRecord, but here's the catch: I'm using PostgreSQL. As I'm using
SQL to do the migration (Including the foreign keys), I need to know
if I can use SQL queries to do those actions. Is there an example out
there?

I uncommented this line in environment.rb:
config.active_record.schema_format = :sql

See you around!

The Neurochild
E3ba60e3dcb813f8abcd7732350e74cf?d=identicon&s=25 Phillip Koebbe (pkoebbe)
on 2008-11-04 15:39
The Neurochild wrote:
> Hi...
>
> I want to know the equivalent instructions to insert, update, search
> and delete a registry. I know Rails can do it easily Thanks to
> ActiveRecord, but here's the catch: I'm using PostgreSQL. As I'm using
> SQL to do the migration (Including the foreign keys), I need to know
> if I can use SQL queries to do those actions. Is there an example out
> there?
>
> I uncommented this line in environment.rb:
> config.active_record.schema_format = :sql
>
> See you around!
>
> The Neurochild

You can use raw SQL in migrations via the execute() method. But you can
also use ActiveRecord objects in migrations, so you can take advantage
of all the goodness that AR gives you. As for foreign keys, you can
write yourself a helper to do that in a migration-friendly way. The
syntax that I use for PostgreSQL is this:

def foreign_key(foreign_table, foreign_column, primary_table,
primary_column = :id)
  execute "
    alter table #{foreign_table.to_s}
      add constraint fk_#{foreign_table.to_s}_#{foreign_column.to_s}
      foreign key (#{foreign_column.to_s}) references
#{primary_table.to_s} (#{primary_column.to_s})
  "
end

def delete_foreign_key(foreign_table, foreign_column)
  execute "alter table #{foreign_table.to_s} drop constraint
fk_#{foreign_table.to_s}_#{foreign_column.to_s}"
end

Put those methods in a file that gets loaded and use them like

foreign_key :orders, :customer_id, :customers

and

delete_foreign_key :orders, :customer_id

Peace.
Ed3050ec0a96c03ef1304e7ad1ff46fb?d=identicon&s=25 The Neurochild (Guest)
on 2008-11-04 20:01
(Received via mailing list)
But what about the datatypes of SQL? First, I have some columns with a
char datatype and I want them that way. Secondly, I saw, when I define
a :string column with a :default value, that value doesn't appear
reflected in the database.

In the last catch, what could be wrong? I'm using Rails 1.2.6 with the
postgres 0.7.9.2008.01.28 adapter (from the ruby-pg project).

Hope you can helpme... if you don't mind.

Greetings.

The Neurochild
E3ba60e3dcb813f8abcd7732350e74cf?d=identicon&s=25 Phillip Koebbe (pkoebbe)
on 2008-11-04 20:07
The Neurochild wrote:
> But what about the datatypes of SQL? First, I have some columns with a
> char datatype and I want them that way. Secondly, I saw, when I define
> a :string column with a :default value, that value doesn't appear
> reflected in the database.
>
> In the last catch, what could be wrong? I'm using Rails 1.2.6 with the
> postgres 0.7.9.2008.01.28 adapter (from the ruby-pg project).
>
> Hope you can helpme... if you don't mind.
>
> Greetings.
>
> The Neurochild

Let's back up for a moment. I'm not sure I understand your dilemma.

First, are you saying that you do your entire migration in raw SQL? Are
you using create_table (or its friends) at all?

Second, what is the problem with PostgreSQL? Your first message referred
to that as a "catch". PostgreSQL is just as fully supported by
ActiveRecord as MySQL is (as far as I know).

Third, if you want to use a char instead of a character varying, you can
override native_database_types to include a :char definition.

Lastly, why don't you post some of your code so we can actually see what
you are referring to.

Peace.
Ed3050ec0a96c03ef1304e7ad1ff46fb?d=identicon&s=25 The Neurochild (Guest)
on 2008-11-04 20:26
(Received via mailing list)
On Nov 5, 2:07 pm, Phillip Koebbe <rails-mailing-l...@andreas-s.net>
wrote:
> if you want to use a char instead of a character varying, you can
> override native_database_types to include a :char definition.

Really? How do I override it? I'm interested. Sorry if I have many
trivial questions, but I'm really new to Rails and all this stuff of
the migrations.

Greetings

The Neurochild
E3ba60e3dcb813f8abcd7732350e74cf?d=identicon&s=25 Phillip Koebbe (pkoebbe)
on 2008-11-04 20:40
The Neurochild wrote:
> On Nov 5, 2:07 pm, Phillip Koebbe <rails-mailing-l...@andreas-s.net>
> wrote:
>> if you want to use a char instead of a character varying, you can
>> override native_database_types to include a :char definition.
>
> Really? How do I override it? I'm interested. Sorry if I have many
> trivial questions, but I'm really new to Rails and all this stuff of
> the migrations.
>

Each database adapter has the ability to define the database types Rails
uses when building DDL. The method used is native_database_types, which
is, in the case of PostgreSQL, found in the PostgreSQL class of the
ConnectionAdapters module of the ActiveRecord module. Override it and
supply what you want. Here is what I use to add bigint support to AR:

class ActiveRecord::ConnectionAdapters::PostgreSQLAdapter
  # in order to add or change the datatypes, this function
  # must be overriden.  Be careful, then, to not remove anything.
  # That carries with it the warning that if Rails Core changes
  # this function, this override will do away with those changes!
  def native_database_types
    {
      :primary_key => "serial primary key",
      :string      => { :name => "character varying", :limit => 255 },
      :text        => { :name => "text" },
      :integer     => { :name => "integer" },
      :float       => { :name => "float" },
      :decimal     => { :name => "decimal" },
      :datetime    => { :name => "timestamp" },
      :timestamp   => { :name => "timestamp" },
      :time        => { :name => "time" },
      :date        => { :name => "date" },
      :binary      => { :name => "bytea" },
      :boolean     => { :name => "boolean" },
      :bigint      => { :name => "int8" }
    }
  end
end

Put that in a file that gets loaded when the Rails environment gets
loaded, and you've overridden the method. In your case, if you want to
add char support, you might do something like:

  def native_database_types
    {
      :primary_key => "serial primary key",
      :char => { :name => "character", :limit => 255 },
      :string      => { :name => "character varying", :limit => 255 },
      :text        => { :name => "text" },
      ...
    }
  end

and then in your migration

t.column :char, :my_column, :limit => 5

in Rails 2.x, you can shorten that to

t.string :my_column

but I haven't figured out how to make that work with the additions I've
made to native_database_types. So I don't know how to achieve

t.char :my_column

which means you still need to use the older way of t.column <blah blah>

Peace.
Ed3050ec0a96c03ef1304e7ad1ff46fb?d=identicon&s=25 The Neurochild (Guest)
on 2008-11-04 21:01
(Received via mailing list)
On Nov 5, 2:40 pm, Phillip Koebbe <rails-mailing-l...@andreas-s.net>
wrote:
>   # this function, this override will do away with those changes!
>       :time        => { :name => "time" },
> add char support, you might do something like:
>
> and then in your migration
>
> t.column :char, :my_column, :limit => 5

Whoa! Awesome! I would like to apply that and the foreign key thing,
but is there a folder where I can save and call my file? Where do you
place it in your case?

I was gonna ask you about how to implement the serial datatype, but
all the explanation you gave me for overriding answered that question.

Later...

The Neurochild.
E3ba60e3dcb813f8abcd7732350e74cf?d=identicon&s=25 Phillip Koebbe (pkoebbe)
on 2008-11-04 21:22
The Neurochild wrote:
> Whoa! Awesome! I would like to apply that and the foreign key thing,
> but is there a folder where I can save and call my file? Where do you
> place it in your case?
>

Different people have different ways of organizing things like this. I
put stuff like this in files in lib/. If I have a number of things that
I'm going to be extending, I separate functionality by file. So if I
have a number of ActiveRecord related extensions or overrides, I will
create a file called custom_ar_extensions.rb in lib/ and then load it
with "require 'custom_ar_extensions'" at the bottom of environment.rb.
There is also a way to do something like this with initializers, but I
haven't yet taken the time to research it.

Peace.
Ed3050ec0a96c03ef1304e7ad1ff46fb?d=identicon&s=25 The Neurochild (Guest)
on 2008-11-04 21:39
(Received via mailing list)
Thanks, man! I'm going to try it right now And I'll post the results
later.

Greetings!

The Neurochild
F4ae71a145a8960a4c29692c96e69dee?d=identicon&s=25 Gabriel Laskar (Guest)
on 2008-11-04 21:57
(Received via mailing list)
On Tue, Nov 4, 2008 at 9:22 PM, Phillip Koebbe
<rails-mailing-list@andreas-s.net> wrote:
> There is also a way to do something like this with initializers, but I
> haven't yet taken the time to research it.
>
In order to have this with initializers, it is more simple,
you just drop the file in the config/initializers/ directory, and it
will be required by default
at boot of the rails application.

The initializers have been realized in order to avoid all the stuff in
environnment.rb.

my .2 cents


--
Gabriel Laskar <bibi.skuk@gmail.com>
Ed3050ec0a96c03ef1304e7ad1ff46fb?d=identicon&s=25 The Neurochild (Guest)
on 2008-11-05 22:34
(Received via mailing list)
On Nov 5, 3:57 pm, "Gabriel Laskar" <bibi.s...@gmail.com> wrote:
> In order to have this with initializers, it is more simple,
> you just drop the file in the config/initializers/ directory, and it
> will be required by default
> at boot of the rails application.
>
> The initializers have been realized in order to avoid all the stuff in
> environnment.rb.

Do I have to create the initializers directory? Because Rails didn't
created it.

I tested the config for PostgreSQL and worked. The only catch is the
schema, instead of :char,  it shows :string. But it was reflected
perfectly when checking pgAdmin III. Is the schema thingy normal? have
you tried?

Greetings!

The Neurochild
E3ba60e3dcb813f8abcd7732350e74cf?d=identicon&s=25 Phillip Koebbe (pkoebbe)
on 2008-11-06 04:14
Gabriel Laskar wrote:
> In order to have this with initializers, it is more simple,
> you just drop the file in the config/initializers/ directory, and it
> will be required by default
> at boot of the rails application.
>
> The initializers have been realized in order to avoid all the stuff in
> environnment.rb.

Thanks, Gabriel. You just saved me from having to figure that out (or
find it myself). Initializers it is then.

Peace.
E3ba60e3dcb813f8abcd7732350e74cf?d=identicon&s=25 Phillip Koebbe (pkoebbe)
on 2008-11-06 04:17
The Neurochild wrote:
>
> Do I have to create the initializers directory? Because Rails didn't
> created it.
>

Initializers come with Rails 2.x (not sure if which point release, maybe
the first one). You'll have to use the environment.rb method in 1.2.x.

> I tested the config for PostgreSQL and worked. The only catch is the
> schema, instead of :char,  it shows :string. But it was reflected
> perfectly when checking pgAdmin III. Is the schema thingy normal? have
> you tried?

What is your schema dump format? Honestly, I have never used schema.rb.
I've read that you can use it to recreate a database, but as of yet, I
have not needed to do that. So, no, I haven't run into that problem. Try
to changing your schema dump format to sql. You do that in
environment.rb as well.

Peace.
Ed3050ec0a96c03ef1304e7ad1ff46fb?d=identicon&s=25 The Neurochild (Guest)
on 2008-11-06 14:21
(Received via mailing list)
On Nov 5, 9:39 am, Phillip Koebbe <rails-mailing-l...@andreas-s.net>
wrote:
> end
> and
>
> delete_foreign_key :orders, :customer_id

I'm not sure, but I think there's something missing: the class name.
Or isn't that necessary here? If yes, can we put a name to it or what
kind of class is it?

It's a shame my ver. doesn't have the initializer thing. I'll try that
right now.

Greetings!

The Neurochild
E3ba60e3dcb813f8abcd7732350e74cf?d=identicon&s=25 Phillip Koebbe (pkoebbe)
on 2008-11-06 15:06
The Neurochild wrote:
>> delete_foreign_key :orders, :customer_id
>
> I'm not sure, but I think there's something missing: the class name.
> Or isn't that necessary here? If yes, can we put a name to it or what
> kind of class is it?
>

You don't need a class since you're just building a sql statement. You
need to deal with tables and columns, not classes. Look back at how I
defined foreign_key and delete_foreign_key. delete_foreign_key assumes a
naming convention that is used in foreign_key. If you use a different
naming convention for your foreign keys, adjust both methods
accordingly.

Peace.
Ed3050ec0a96c03ef1304e7ad1ff46fb?d=identicon&s=25 The Neurochild (Guest)
on 2008-11-06 16:40
(Received via mailing list)
On Nov 7, 9:06 am, Phillip Koebbe <rails-mailing-l...@andreas-s.net>
wrote:
> You don't need a class since you're just building a sql statement. You
> need to deal with tables and columns, not classes. Look back at how I
> defined foreign_key and delete_foreign_key. delete_foreign_key assumes a
> naming convention that is used in foreign_key. If you use a different
> naming convention for your foreign keys, adjust both methods
> accordingly.

So I put both methods on one files and load them in the environment?
That's it.
E3ba60e3dcb813f8abcd7732350e74cf?d=identicon&s=25 Phillip Koebbe (pkoebbe)
on 2008-11-06 16:47
The Neurochild wrote:
> On Nov 7, 9:06 am, Phillip Koebbe <rails-mailing-l...@andreas-s.net>
> wrote:
>> You don't need a class since you're just building a sql statement. You
>> need to deal with tables and columns, not classes. Look back at how I
>> defined foreign_key and delete_foreign_key. delete_foreign_key assumes a
>> naming convention that is used in foreign_key. If you use a different
>> naming convention for your foreign keys, adjust both methods
>> accordingly.
>
> So I put both methods on one files and load them in the environment?
> That's it.

Right. Here's the skinny on how I do it:

I have a file

{RAILS_ROOT}/lib/pg_ext.rb

that contains the methods. Then, at the bottom of
{RAILS_ROOT}/config/environment.rb, I have

require 'pg_ext'

Remember to restart the server or console after making changes to the
environment.

Peace.
F4ae71a145a8960a4c29692c96e69dee?d=identicon&s=25 Gabriel Laskar (Guest)
on 2008-11-07 00:16
(Received via mailing list)
On Thu, Nov 6, 2008 at 4:47 PM, Phillip Koebbe
<rails-mailing-list@andreas-s.net> wrote:
> that contains the methods. Then, at the bottom of
> {RAILS_ROOT}/config/environment.rb, I have
>
> require 'pg_ext'
>
> Remember to restart the server or console after making changes to the
> environment.

You can also use the dependency loading facility of rails.
Write a module in the lib directory :

# #{rails_root}/lib/pg_ext.rb
module PgExt

  def foo
  # ...
  end

end

And simply call `PgExt.foo` in the migration file. No configuration
needed.

--
Gabriel Laskar <bibi.skuk@gmail.com>
This topic is locked and can not be replied to.