Read my latest article: 8 things I look for in a Ruby on Rails app (posted Thu, 06 Jul 2017 16:59:00 GMT)

Installing untrusted PL/Ruby for PostgreSQL

Posted by Mon, 22 Aug 2005 12:55:00 GMT

This is going to be short and sweet.

“PL/Ruby is a loadable procedural language for the Postgres database system that enable the Ruby language to create functions and trigger procedures”

Method 1. The standard, safe, PL/Ruby.

Before running this, you need to have all the PostgreSQL headers installed. (se INSTALL in the postgresql directory) make install-all-headers

To install PL/Ruby, you need to download the tarball from here. As you can see, I download it with wget and then install like I would any ruby library. (maybe plruby could become a gem?)

cd /usr/local/src
wget ftp://moulon.inra.fr/pub/ruby/plruby.tar.gz
tar zxvf plruby.tar.gz
cd plruby
ruby extconf.rb
make
make install

Method 2: The untrusted, but super cool PL/Ruby.

Guy Decoux, author of PL/Ruby, was kind enough to share a secret about the PL/Ruby install. (from his email…)

Well plruby normally run with $SAFE = 12, this value if fixed at compile time. 

Now it has an undocumented option, if you compile it with

ruby extconf.rb --with-safe-level=0 ...

 it will run with $SAFE = 0 and you have the equivalent of an untrusted language.

Pretty simple solution, eh?

On my server I was able to run the following:

cd /usr/local/src
wget ftp://moulon.inra.fr/pub/ruby/plruby.tar.gz
tar zxvf plruby.tar.gz
cd plruby
sudo ruby extconf.rb \ --with-pgsql-dir=/usr/local/pgsql-8.0 \ --with-safe-level=0 \ --with-suffix=u
make
make install

Update: the --with-suffix=u was added after someone commented on this. This allows you to install plruby and plrubyu.

Installing PL/Ruby in PostgreSQL Up until now, you haven’t actually installed the language into the database. We’re close though!

All that you need to do is run the following commands to install it to a specific database in your server.

$ psql template1
template1=# CREATE DATABASE plruby;
CREATE DATABASE
template1=# \c plruby
You are now connected to database "plruby".
plruby=#    create function plruby_call_handler() returns language_handler
plruby-#    as '/usr/lib/site_ruby/1.8/i386-linux/plruby.so'
plruby-#    language 'C';
CREATE FUNCTION
plruby=#    create  language 'plruby'
plruby-#    handler plruby_call_handler
plruby-#    lancompiler 'PL/Ruby';
CREATE LANGUAGE
plruby=#

That should be all there is to it!

Where do we go from here?

See my post: PL/Ruby loves RubyGems and DRb

PostgreSQL sequences in Rails

Posted by Sat, 20 Aug 2005 06:05:00 GMT

Rails doesn’t support legacy or custom named sequences at the moment. (as far as I am aware). It’s kind of tricky to have it detect the SEQUENCE name automatically (every time).

In PHP, I used this big ugly query to detect this info:
$sql = $db->prepare("SELECT seq.relname::text
                        FROM pg_class src, pg_class seq, pg_namespace, pg_attribute,
                        pg_depend
                        WHERE
                            pg_depend.refobjsubid = pg_attribute.attnum AND
                            pg_depend.refobjid = src.oid AND
                            seq.oid = pg_depend.objid AND
                            src.relnamespace = pg_namespace.oid AND
                            pg_attribute.attrelid = src.oid AND
                            pg_namespace.nspname = ? AND
                            src.relname = ? AND
                            pg_attribute.attname = ?");

I used this to mimmick the mysql_insert_id function in PHP for PostgreSQL… ( pg_insert_id )

Well, with Rails, I thought that I would build a similar patch, as the current code just assumes the value would be {column}_id_seq.

After hours of playing around and thinking that I figured it all out ... I decided to run a quick test with a non standard sequence name… like this one:

testingdb=# \d legacy.foobar
                                     Table "legacy.foobar" 
  Column   |         Type          |                         Modifiers                          
-----------+-----------------------+------------------------------------------------------------
 foobar_id | integer               | not null default nextval('legacy.old_sequence_name'::text)
 name      | character varying(40) | 
Indexes:
    "foobar_pkey" PRIMARY KEY, btree (foobar_id)

testingdb=# INSERT INTO legacy.foobar (name) VALUES ('abc')
testingdb-# ;
INSERT 17514 1
testingdb=# SELECT * FROM legacy.foobar ;
 foobar_id | name 
-----------+------
       106 | abc
(1 row)

My patch wouldn’t figure that out because the sequence was not created by SERIAL. So, my patch started to feel lame and a total waste of time, because I thought that it was fixing a problem.. that works pretty much as effectively as assuming it is _seq.. but without needing to run a SQL query to determine that. We all (should) know that the field will be named like that when working with SERIAL. So, my patch didn’t buy us anything.

However, Active Record still doesn’t support those funky sequence names. So, I found this ticket #1273.

Their approach was very similar to what caused me to use my long SQL query in the first place because this was suggested to me well over a year ago and I found it to not work in the following situation.

If I have two seperate schemas with the same table name in each like so:

=# \d legacy.people
                                       Table "legacy.people" 
  Column   |         Type          |                           Modifiers                           
-----------+-----------------------+---------------------------------------------------------------
 people_id | integer               | not null default nextval('legacy.people_people_id_seq'::text)
 name      | character varying(50) | 
Indexes:
    "people_pkey" PRIMARY KEY, btree (people_id)

=# \d foo.people
                                       Table "foo.people" 
  Column   |         Type          |                         Modifiers                          
-----------+-----------------------+------------------------------------------------------------
 people_id | integer               | not null default nextval('foo.people_people_id_seq'::text)
 name      | character varying(50) | 
Indexes:
    "people_pkey" PRIMARY KEY, btree (people_id)      

That patch will not work because you can’t call the following query:

# SELECT adsrc FROM pg_attrdef WHERE adrelid = (SELECT oid FROM pg_class WHERE relname = 'people');
ERROR:  more than one row returned by a subquery used as an expression  

... because there are two tables with the same name! (fun, huh?)

Mine would work… but why bother with that huge query? So, I took my ticket out of [PATCH] and decided that I thought it would be best to just assume that sequences are generated with SERIAL ( link ) by default in AR.

Okay, so what can we do about custom SEQUENCE names?

Well, I am proposing the following (and mentioned this in the ticket #2016)...

class LegacyTable < ActiveRecord::Base
  def self.table_name() "legacy.foobar" end

  # new option for this
  set_primary_key "foobar_id", :sequence => "legacy.old_sequence_name" 
end

(or something along those lines)

With this, I can work around these legacy database scenarios with a quick option. Thoughts/opinions?

I decided to post this on my blog as well, because I do know that there are a few skeptical PostgreSQL people out there who read my blog… I want you to know that I am looking out for you. ;-)

I am sick and tired.. and going to sleep now.

Are you a console master?

Posted by Fri, 19 Aug 2005 02:06:00 GMT

1 comment Latest by Lance Wed, 02 Aug 2006 10:58:30 GMT

I have a few questions.

1.) Do you know what ./script/console does?

2.) If not, why not?

3.) If so, do you have any fun tips and tricks to share with the masses?

It occured to me earlier that many people, who might have came from the PHP camp, may have never really tested their object-oriented code from some sort of interactive program. (irb) If you are coming from the Python, Java, etc worlds, interactive testing isn’t anything new. Rails is nice enough to bundle a console script right within it!

I meet people online who have never even tried to run it. There are not many tutorials on the wiki that show console… and in my opinion, its one of the coolest things about Ruby and Rails. (but, I come from the php world…)

So, if you aren’t using it… why not? got a moment? try this from the root path of your Rails application.

./script/console
It start up okay? If so, what is the name of one of your models? Let’s say that I have a model structure like:
class Customer < ActiveRecord::Base
  has_many :orders, :dependent => true
end

class Order < ActiveRecord::Base
  belongs_to :customer
end
From console, you can access your models and do all sorts of fun things.
>> y = Customer.find(16)
=> #<Customer:0x2743ea4 @attributes={"name"=>"Robby", "id"=>"16"}>
>> y.orders
=> [#<Order:0x27416b8 @attributes={"id"=>"18", "amount"=>"12.00", "customer_id"=>"16"}>, #<Order:0x274167c @attributes={"id"=>"19", "amount"=>"12.50", "customer_id"=>"16"}>] 

Pretty neat, huh?

>> o = Order.find(18)
=> #<Order:0x273da68 @attributes={"id"=>"18", "amount"=>"12.00", "customer_id"=>"16"}>
>> o.customer.name
=> "Robby" 

If you are remotely a console wizard, please share some tips and tricks for those who are not sure what to do with it. I personally find myself in console all the time that I am working with Rails, testing stuff out with my models, before I move any of the code to my application.

It sure beats, hitting refresh in your browser all day. :-)

Active Record, I <3 U but I still trust my database server (a tiny bit more)

Posted by Fri, 19 Aug 2005 01:20:00 GMT

While working on a portion of my book, I found myself in ./script/console and was seeing some weird issues when I would use has_many and belongs_to.

Let’s take two simple models.

<pre>
class Order < ActiveRecord::Base
  belongs_to :customer
end

class Customer < ActiveRecord::Base
  has_many :orders
end
</pre>
</code>

After a few test records...

<code>
<pre>
test_dev=# SELECT * FROM customers;  
 id |      name      
----+----------------
  1 | Robby
  2 | Nigel
  3 | Linus
(3 rows)

test_dev=# SELECT * FROM orders;
 id | customer_id | amount 
----+-------------+--------
  1 |           1 |  12.00
  2 |           3 |  12.00
(2 rows)
</pre>
</code>

Nothing completely crazy going on, right?

<typo:code lang="ruby">
Loading development environment.
>> Customer.destroy(3)
=> {"name"=>"Linus", "id"=>"3"}
>>     

=# SELECT * FROM orders;
 id | customer_id | amount 
----+-------------+--------
  1 |           1 |  12.00
  3 |           3 |  12.00
(2 rows)

Wait a minute! I just deleted a customer with an id of 3!

So, what is wrong with this scenario? Can you think of any potential problems that could occur from data like this? The record has a customer_id for a customer that does not exist. This is why we have relational databases in the first place, right? :-)

Here is something that I learned today that I was unaware of. Active Record allows you to pass the has_method declaration the option :dependent.

class Customer < ActiveRecord::Base
  has_many :orders, :dependent => true
end

What is this option? Well, according to the AR documentation, “:dependent – if set to true all the associated object are destroyed alongside this object. May not be set if :exclusively_dependent is also set.”

In a nutshell, this works like ON DELETE CASCADE does in PostgreSQL. So, it will through and delete the orders associated with the customer that I was attempting to destroy.

Up until today, I hadn’t broken myself out of the habit of using the built-in constraints/triggers of PostgreSQL. So, as soon as I did, this issue came up and I learned about :dependent.

test_dev# \d orders
                                Table "public.orders" 
   Column    |     Type      |                       Modifiers                        
-------------+---------------+--------------------------------------------------------
 id          | integer       | not null default nextval('public.orders_id_seq'::text)
 customer_id | integer       | 
 amount      | numeric(10,2) | 
Indexes:
    "orders_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "orders_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(id)

test_dev=# ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey;
ALTER TABLE 

RobbyOnRails:~/Programming/footest robbyrussell$ ./script/console 
Loading development environment.
>> cust = Customer.create(:name => 'Jim')
=> #<Customer:0x275373c @new_record_before_save=true, @new_record=false, @attributes={"name"=>"Jim", "id"=>5}, @errors=#<ActiveRecord::Errors:0x274fa88 @base=#<Customer:0x275373c ...>, @errors={}>>
>> cust.orders.create(:amount => '25.00')
=> #<Order:0x274991c @new_record=false, @attributes={"id"=>4, "amount"=>"25.00", "customer_id"=>5}, @errors=#<ActiveRecord::Errors:0x2746dfc @base=#<Order:0x274991c ...>, @errors={}>>
>>                

test_dev=# SELECT * FROM orders;
 id | customer_id | amount 
----+-------------+--------
  1 |           1 |  12.00
  3 |           4 |  29.00
  4 |           5 |  25.00
(3 rows)

As you can see, I put myself into the hands of Active Record when I ran the DROP CONSTRAINT. Then I tried running the code at the top of the post… and it didn’t work.

According to the docs, if you use :dependent => true, it should delete the foreign table records. If not, it should set the value of the foriegn key field to NULL in the foreign table.

Basically, perform these two SQL queries:
UPDATE orders SET customer_id = NULL WHERE customer_id = 17; 

DELETE FROM customers  WHERE id = 17;

Then, the records are still in the database for those orders, but the customer is deleted. There are arguments for and against doing this sort of thing… but the ability to have the option is always nice. In any event, Active Record would not run the first query,it was just deleting from the customers table. Without my constraint, no error would be returned from PostgreSQL and I started to get some bad data.

Imagine showing a list of orders and trying to display the customer name associated with an order that has no linking customer. Doh! If Active Record sets the customer_id to NULL we can at least have some logic to work with this without having to run some fun SQL queries to figure out which orders do and dont have customers. (we want our applications to have clean data!)

Anyhow… Was this a bug? Should Active Record know to update the records to NULL in this case? I figured that is should be handling this task, especially since it was handling cascading deletes when you passed :dependent => true.

However, I didn’t want to prematurely post a bug report, so I began asking around on #rubyonrails (irc.freenode.net). People made a bunch of suggestions as to how to work around it. I could add a before_destroy method in my model, track the bug down, (re)add an ON DELETE trigger to my table (hah), etc. So, I decided that I would see if I could track down what happens when has_many is used for a model upon #destroy.

After a while of digging and making some tests, I posted a patch and a bug report. (please disregard my first patch… it did not work! heh)

Now that I figured this out, I am going to happy add my constraints back to my tables and go back to playing around. This reminded me of a post I had a few months ago when I mentioned that I thought it was best to put some constraints and logic in the database. I also agree that constraints should be put in the abstraction layer, but we cannot always put all faith in our code either. A few levels of checks doesn’t hurt. :-)

This was a fun little riddle that I took on today. The moral of the story? If you have the ability to use the builtin referential integrity features of PostgreSQL and those other databases, it might be a good idea to do so. Things get overlooked, people login to the database in many ways, and from different programs.

UPDATE: DHH responded to this post and provided a link which discusses Application Database versus Integrated Database

It should be noted that there is an important distinction between the two methods. When I said, “Things get overlooked, people login to the database in many ways, and from different programs” I was basically describing Integration Database. However, I was also thinking of the possibility of someone opening up their MySQL or PostgreSQL GUI and manually removing a record in plain SQL. According to Application Database, the moment that you do that, you basically break this model and cannot expect your application to be fully responsible for the problems that may or may not occur. At this point, you would need to look at your application in terms of Integration Database. Please do correct me if I am wrong on this. :-)

However, with this scenario, my first attempt to move to relying on AR had a minor hiccup, but it was an easy enough fix.

By performing the following command, I am moving towards an Integration Database pattern and that should be recognized when taking this into consideration.

ALTER TABLE orders ADD CONSTRAINT orders_customers_id_fkey 
    FOREIGN KEY (customer_id) REFERENCES customers (id) MATCH FULL; 

Okay, back to work!

Once again. Use constraints! (if you can)

... and thanks to DHH for providing the link and motivating me to make a note of this in my entry.

Foscon Flickr Photos

Posted by Fri, 05 Aug 2005 05:09:00 GMT

Scott Laird uploaded more FOSCON pictures. :-)

(you can even find a picture of me)

Why

My evening at FOSCON

Posted by Thu, 04 Aug 2005 03:08:00 GMT

I will be honest and say that at 6:28 PM, I decided that I was just not going to be able to make it to FOSCON. I was wedding clothes shopping which is taken place in SoCal later this week. When I got into my car I noticed that my friend, Cliff, had left me a few voicemails saying that he would be there. I ran home, dropped off my girlfriend and headed to Free Geek.

I got there just in time to see them introduce DHH. He gave his keynote, which is the same one that he will be delivering tomorrow. Short, sweet and to the point. He took a few questions, like, “Couldn’t this be redone in Perl and wha t would the difference be?” (not word-for-word… but close).. David responded, “Clean code.”

Afterwards, I managed to sneak in a , “Hi, I am Robby” but never managed to get to talk with hime anymore than that. Perhaps I will be able to make it to OSCON tomorrow and get a chance to introduce myself again. :-)

During the pizza eating portion of the evening, I got to talk with the man behind Ruby himself, Matz. We talked for a few minutes. I told him that I was the one who did RubyURL and how I got excited and told my friends when he mentioned my site on his blog.

The next speaker was…umm.. I forgot his name. It’s not on the site. He gave a quick overview of some really cool things that are coming to Ruby on Rails… and that is Rails-powered….FLASH!

Yes, Flash applications on top of Rails. It’s a port of NextStep or something. I didn’t keep up with it all, but what I did gather was quite cool sounding. What was his name…?

UPDATE: His name is… Rich Kilmer.

Glenn Vanderburg then gave a 42.minute talk on MetaProgramming with Ruby. That happened about 3.hours.from_now.ago. It was a nice talk and learned a few things about method_missing that I didn’t know before. I am using it in a few examples in my book, so it was nice to see some gotchas.

The last speaker was why the lucky stiff. WOW. He and thirsty cups (his backup band) gave a 30 minute concert, video, interactive,... I don’t know what the hell to call it. It was the most creative programming tutorials/talks that I have ever seen. He said that he would post the videos and information after tomorrow (when he gives the same tutorial at OSCON).

If you are going to see this… it would be wise to take pictures, video tape it… and bring your laptop for interactive participation. He gave a quick intro into drb and the audience (you) gets to be a part of that. I cannot wait to show my friends those videos.

My friend Cliff, who is a Python fan, seemed to enjoy himself. Maybe… just maybe… he might give Rails a real shot. He has this weird hangup about using tal for views, so I pointed him to Armita.

It was great to meet all those who came up and introduced themselves to me. I was the red head who looked like he hadn’t eaten anything all day. That slice of veggie pizza was well needed. :-)

Thanks to the folks in PDX.rb and Free Geek for putting this event on. I am glad that I made it out.

It’s time for some sleep…

UPDATE You can see some pictures from FOSCON on Flickr, under the tag… foscon

http://flickr.com/photos/tags/foscon/

Older posts: 1 ... 21 22 23 24 25