SQL queries in Rails

Rails
1

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 N.

2

The N. 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 N.

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.

3

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 N.

4

The N. 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 N.

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.

5

On Nov 5, 2:07 pm, Phillip K. [email protected]
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 N.

6

The N. wrote:

On Nov 5, 2:07 pm, Phillip K. [email protected]
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

Peace.

7

On Nov 5, 2:40 pm, Phillip K. [email protected]
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 N…

8

The N. 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.

9

Thanks, man! I’m going to try it right now And I’ll post the results
later.

Greetings!

The N.

10

On Nov 5, 3:57 pm, “Gabriel L.” [email protected] 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 N.

11

Gabriel L. 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.

12

On Tue, Nov 4, 2008 at 9:22 PM, Phillip K.
[email protected] 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 L. [email protected]

13

On Nov 5, 9:39 am, Phillip K. [email protected]
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 N.

14

The N. 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.

15

The N. 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.

16

The N. wrote:

On Nov 7, 9:06 am, Phillip K. [email protected]
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.

17

On Thu, Nov 6, 2008 at 4:47 PM, Phillip K.
[email protected] 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 L. [email protected]

18

On Nov 7, 9:06 am, Phillip K. [email protected]
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.