FLPR

Setting default boolean values in migrations

Posted by Pat Tue, 07 Feb 2006 14:47:00 GMT

I just ran into a strange problem with a migration. It looked like:

 def self.up
    add_column :restaurants, :visa, :boolean, :default => false, :null => false
 end

Turns out that you can’t use :default => false when using add_column (it works fine in a create_table statement). You need to use :default = 0 instead, so the code looks like

 def self.up
    add_column :restaurants, :visa, :boolean, :default => 0, :null => false
 end

I imagine this is because MySQL doesn’t provide actual boolean support. Not quite sure what the problem is, and I don’t have time to look at the code right now, but if you run into that problem, that’s how you solve it.

Posted in PostgreSQL, Rails | Tags migrations, postgresql, rails | no comments

Testing with database constraints

Posted by Pat Mon, 12 Dec 2005 12:38:00 GMT

PostgreSQL calls itself “the world’s most advanced open source database.” I want to use some of the advanced features, dammit! The first, not-so-spectacular-but-still-nice feature that I want to use is foreign key constraints. If you don’t know what they are (read the links!), the gist of it is that you can specify that a field in one table refers to a field in another table. That’s the foreign key part. The constraint is that the record in the other table must exist. Something like:

Table Companies
id integer
name varchar(80)

Table Products
id integer
name varchar(80)
company_id integer  # This is a foreign key

company_id is a foreign key to the id field in the companies table. We haven’t specified it yet, and I’ll show how to do that later, but for now just understand that concept. Any value insert into products(company_id) must exist in companies(id). So for example, if you make products(company_id) = 2982475, and there’s no record in ‘companies’ with id = 2982475, postgresql will give you an error and no insert is made.

Similarly, if you try to delete a record in ‘companies’, and there are records in ‘products’ that reference it, postgresql will again give you an error. <anecdote style=”useless”>This would have been really nice the time I deleted a couple records I “didn’t need” anymore, and then my whole site stopped working. Turns out that there were still a few records somewhere else that referenced these. I should have used console to make the changes…but I didn’t know about it.</anecdote>

This is a good thing because it ensures you maintain referential integrity. Rails is nice and all, but a simple fact of business apps is that the data is far more valuable and has a longer life than the actual app you use to access it. You want to make sure that the database has no chance of having broken references. Bad data at best causes you lots of headaches, and at worst is absolutely worthless.

TDD with fkey constraints

I’m a huge fan of test-driven development. It’s just fun for some reason, and I quickly make lots of progress. Anyway, I wanted to be able to get foreign key constraints into my apps, so I needed to fit them into my process. Basically here’s what I came up with:

1. Write tests 2. Write code to make tests pass 3. Add foreign key constraints 4. Make sure tests still pass

I didn’t know the best way to add foreign key constraints, but then found out about migrations and thought I could give that a shot. I had a tough time getting started, so I made a post to the Rails list and then Robby Russell showed me how. Go read that article to learn how to use constraints in your migrations.

At first my development process went very smoothly. Then it blew up when I created two tables that referenced a single table. The problem is that when you run tests, rake doesn’t actually wipe the db clean like you might expect. Instead it just rolls it back to its initial state, which is to have the fixtures loaded. This means you end up with dirty tables, and then postgresql complains about you violating foreign key constraints. I haven’t explained it well, honestly, but the entire issue is covered here. There are a couple solutions listed there, and I’ll explain what I’ve managed to get working so far.

First solution – delete records in teardown()

The first fix is to include the following code in your test_helper.rb file inside the TestCase definition.

def teardown
  self.class.fixture_table_names.reverse.each do |table_name|
    klass_name = Inflector.classify(table_name.to_s)
    if Object.const_defined?(klass_name)
      klass = Object.const_get(klass_name)
      klass.connection.delete("DELETE FROM #{table_name}", 'Fixture Delete')
    else
      flunk("Cannot find class for table '#{table_name}' to delete fixtures")
    end
  end
end

This overrides the teardown method so that when a test is done completes, all the fixtures will be deleted from the db. The only problem with this solution is that I couldn’t use transactional fixtures with it. Testing worked, it was just a bit slow.

Second solution – Make your models paranoid

I ended up using a different solution, which is to include the following code in test_helper.rb, but this time outside of any class definitions:

# neuter fixture deletions and multiple fixture insertions to work with database constraints
class Fixture
  attr_reader :class_name
end 

class Fixtures
  @@inserted_fixture_list ||= {}
  alias :original_insert_fixtures :insert_fixtures

  def insert_fixtures
    return if @@inserted_fixture_list[values[0].class_name]
    @@inserted_fixture_list[values[0].class_name] = true
    unless ActiveRecord::Base.connection.select_one("select 1 from #{fixture_class_to_table_name(values[0].class_name)}")
      original_insert_fixtures
    end
  end

  def delete_existing_fixtures() end

  # compute a fixture's table name from its (known) class name
  def fixture_class_to_table_name(class_name)
    class_name.gsub(/([a-z])([A-Z])/) { |match| "#{$1}_#{$2}" }.downcase.pluralize
  end
end

I did run into a problem here because it turns out that this code doesn’t delete any fixtures, which becomes obvious once you see that delete_existing_fixtures is empty. If I commented out the redefinition, I got a bunch of warnings about a transaction not being in progress. At this point I realized that I wouldn’t really want to delete any objects that are referenced anyway…I might not want them accessible to the user under normal circumstances, but I definitely don’t want them deleted from the database. So I installed Rick Olson’s acts_as_paranoid module and added that to the models that I wanted to be paranoid in the db. Paranoid models don’t actually get deleted when you call destroy(), they just get a deleted_at field set to the timestamp. Pretty slick.

Go with either of these solutions and you should be able to add foreign key constraints and test away to your heart’s content.

Posted in PostgreSQL, Rails | Tags constraints, postgresql, rails, testing | no comments

Installing the postgres adapter

Posted by Pat Mon, 05 Dec 2005 08:58:00 GMT

In a previous article, we installed the ruby-postgres adapter using the ports system. I’ve spoken with a few people, and they’ve suggested using the postgres gem instead.

Unfortunately you can’t just do a “gem install postgres” to install it, you have to go through a few extra steps in order to get it to build cleanly on FreeBSD. Fortunately, I’ve done it and have all the steps here :)

# gem install postgres

You’ll get a bunch of errors and it’ll say it can’t create the Makefile. Don’t worry about that. We have to manually configure and install the gem.

For whatever reason, the Postgres installation doesn’t put the libpq-fe.h file in its include dir, so we need to symlink it. Then we’ll configure and install the gem.

# ln -s /usr/local/include/libpq-fe.h /usr/local/include/postgresql/server/
# cd /usr/local/lib/ruby/gems/1.8/gems/postgres-0.7.1/
# ruby extconf.rb --with-pgsql-include-dir=/usr/local/include/postgresql/server --with-pgsql-lib-dir=/usr/local/lib/postgresql
# make install

Should be all good to go. You’ll want to remove the ruby18-postgres package if you have it installed.

Posted in FreeBSD, PostgreSQL | Tags freebsd, postgresql, ruby | no comments

Upgrading to PostgreSQL 8.1

Posted by Pat Wed, 16 Nov 2005 13:14:00 GMT

The awesome guys over at PostgreSQL released 8.1, and it’s got a bunch of cool new features/enhancements. After spending a bit of time playing with things, I figured out how to get 8.1 going, and here I’m going to take you through the task of upgrading your FreeBSD system to use PostgreSQL 8.1.

I’m going to list the basic steps so you have an idea of what we’re going to do, then I’ll give you detailed instructions.
1. BACKUP YOUR DATABASES!!
2. Install the new ports
3. Remove old packages and fix dependencies
4. Wipe the old data dir and initialize the new one
5. Create your users, databases, and import your old data

Backup Databases

You should back up your databases often, at least nightly. You should especially back up your databases whenever you’re upgrading Postgres to the latest version. I’ve got a dirty little secret though…I never do. This upgrade is a bit different, and you HAVE to back up your databases, otherwise you lose them. This is because the 8.1 server and client are totally incompatible with the 8.0 data structure, so you’re actually going to delete the entire psql data directory and start all over. Yeah, you might want to have a way of getting your data back in :)

There are a few ways you can backup Postgres, the easiest of which is probably pg_dumpall. Okay, second easiest, because I decided to use this as an opportunity to hack up a ruby backup script that I can use in the future. Honestly, it doesn’t do a whole lot more than pg_dumpall at this point, but it’s something that I can easily modify and use to make regular backups (like I should!). Here’s the code, for your viewing pleasure:

#!/usr/local/bin/ruby

# This is used to set the user when you run pg commands.  Change
# 'pgsql' to your main postgres user, or make it blank if you
# don't need to specify
usrcmd = "-U pgsql"

# Vacuum all databases
`vacuumdb #{usrcmd} -a -f -z`

# This lists all the dbs, but there's some header info to get rid of
dboutput = `psql #{usrcmd} -l`
databases = dboutput.split("\n")
databases.slice!(0..2)
databases.pop

# Loop through all the databases
databases.each do |db|
  dbname = db.split[0] # Gets the database name, first text before a space
  next if dbname == "template0" or dbname == "template1" # Don't back these up
  `pg_dump #{usrcmd} #{dbname} > #{dbname}.sql`
end

The comments should explain everything. Basically we’re just taking all the databases and exporting their contents to a file in the current directory. All the databases will have separate backup files.

A few important notes. The backup script doesn’t do the create database statements, so you’ll have to do that manually for each database. Take a look at the pg_dump help to see how to include the create database statements. Also, this doesn’t backup any of your database users, so you’ll have to do that manually as well. I’m going to enhance this script to include all that stuff, but for now I just wanted to have something to help me quickly upgrade to 8.1.

Making the upgrade

First we build the new client

# cd /usr/ports/databases/postgresql81-client && make install clean

Now build the server

# cd /usr/ports/databases/postgresql81-server && make install clean

Now we’re going to have to delete the old 8.0 packages. You probably have a number of packages on your system that depend on these, so we’ll have to force the deletion. Then we’ll fix the package database to make those packages depend on the new 8.1 package.

Delete old packages (specify the appropriate version if not 8.0.4)

# pkg_deinstall --force postgresql-server-8.0.4
# pkg_deinstall --force postgresql-client-8.0.4

Fix package dependencies

# pkgdb -F

This is going to show you a bunch of stale dependencies, because we removed the postgresql-client.8.0.x package. Most of the time it’s smart enough to suggest postgresql-client-8.1.0, and you can just hit yes, but one time it suggested the server package when it should use the client. If the current package depends on postgresql-client-8.0.x, then obviously you want to update it to use client-8.1.0. So just watch closely, and if it suggests the wrong one, manually enter it.

We just fixed a couple package dependencies, but those ports are still compiled against 8.0.x. This may not cause any problems, but to avoid any headaches, you’ll want to recompile them against 8.1.0. Here we rebuild the postgresql-client, and force any packages that depend on it to rebuild as well. It’s kind of redundant since we’re going to rebuild the client and server ports we just did above, but it’s the easiest way to run one command and make sure everything gets done. It’ll add just a few minutes to the build.

# portupgrade -fr postgresql-client-8.1.0

Set up your databases

I mentioned earlier that 8.1 can’t work with 8.0’s data structure, so here we’re going to erase the data directory and reinitialize it. Feel free to back it up if you want…maybe if you want to revert back to 8.0, it’ll make things easier.

# rm -rf /usr/local/pgsql/data/

Initialize the data dir

# su -l pgsql -c initdb

Start the db server

# /usr/local/etc/rc.d/010.pgsql.sh start

Now you have to create your users and your databases. Look at the createuser and createdb commands to see what options you can specify. First thing I do is create a user, connecting as the pgsql user, and specify that the new user should have a password. I answered no to all the questions.

# createuser -U pgsql -P newuser

After that’s done, create your new database, and specify the newly created user as the owner:

# createdb -U pgsql -O newuser newdb

Now reimport your data into the database:

# psql newdb pgsql < newdb.sql

Upgrade finished

Your system should be all good to go now. If you have any services that rely on PostgreSQL, you should restart them now. For example, I’d restart lighttpd obviously. I’ve also got my mail server configured to use PGSQL, so I’d need to restart that as well. Maybe that’s the subject of a future article :)

Posted in PostgreSQL | Tags postgresql | 7 comments

Setting up PostgreSQL 8

Posted by Pat Thu, 25 Aug 2005 04:12:00 GMT

Done with the hard stuff

So far things have taken quite a bit of time…it’s been a bit of pain, you guys are itching to start developing and deploying your Rails apps. I know, because I’m going through all these steps on a server of my own! From here on out though, things will be a lot easier, and a lot quicker. We can take advantage of the fact that we got the ports system set up nicely, and easily install whatever apps we want.

Now we’re going to set up the database. I use PostgreSQL 8 in all my projects, so that’s what we’ll be installing. Let’s jump right in.

Install Postgres from ports

This is pretty simple stuff. Basically just go into the port dir and start the install.

# cd /usr/ports/databases/postgresql80-server/ && make install clean

Select any of the options you want. I just go with the default selections (only NLS enabled when I installed it).

Okay you’re done. Seriously.

Initialize and start database

This step is pretty simple as well. Before you can use postgres, you have to initialize the database system. You do this by using the initdb command. However, you need to run it as the pgsql user. pgsql is Postgres’s equivalent of MySQL’s root/admin user.

# su -l pgsql -c initdb

The easiest way to start and stop the database server is using an RC script (we’ll use these a lot). The Postgres installation actually places one in /usr/local/etc/rc.d for you, so you don’t have to do anything there. First though, you have to enable it in your rc.conf file. This will also make sure that the database starts up whenever your server boots. Add the following line to /etc/rc.conf:

postgresql_enable="YES"

Save rc.conf, and then start postgresql:

# /usr/local/etc/rc.d/010.pgsql.sh start

Now connect to postgres just to see that everything worked. psql is the client application, template1 is the database you’re connecting to, and pgsql is the username you’re using.

# psql template1 pgsql

You’ll see the postgres prompt, it looks like ‘template1=#’

Creating users, databases, and all that fun stuff

Postgres has, from what I’ve read, the best implementation of ANSI-SQL of any database, open source or otherwise. So if you have a decent grasp on SQL, you should be able to figure most things out.

The best thing to do is just get familiar with the Postgres documentation.
Database Users and Privileges
Managing Databases

We’ll set up a user and a database right now so that you can see how it’s done. It also may give you a little hint into what we’ll be doing in the next article. The user and database name will both be ‘typo’, and we’ll set up a password for the user as well. You can use whatever you like, of course.

# createuser -P -U pgsql typo
# createdb -O typo -U pgsql typo

The -P option of createuser prompts you for a password that will authenticate the new user, -U is the username you’re connecting as (NOT the user you’re creating), and then typo is the name of the new user. I made the password ‘typ0’ and opted not to let the typo user create databases or users.

The -O option of createdb means that the owner will be set to ‘typo’, -U is the user you connect as to create the db, and then typo is the name of the database.

That’s it…on to setting up Rails

Posted in PostgreSQL | Tags postgresql | no comments

Intro to FLPR

Posted by Pat Sat, 20 Aug 2005 07:57:00 GMT

What is FLPR?

If LAMP is the Open Source Web Platform, then FLPR is the Most Excellent Open Source Web Platform. Party on dudes.

FLPR stands for FreeBSD Lighttpd PostgreSQL Rails

Why should I use it?

This is just the opinion of one tiny web developer (but don’t worry, with FLPR, I’m going to take over the world!). There’s tons of debate all over the net on MySQL vs Postgres, FreeBSD vs Linux, Rails vs PHP/ASP/Java/FlavorOfTheWeek.

Here are my thoughts on it. I’m actually going to explain it all in reverse order. As I mentioned, there’s already a ton of exiting content debating the merits of all the various technologies. I’m just going to discuss why I use this particular setup (besides the obvious fact that it makes a cool acronym).

Rails – This is just a sweet, sweet web framework. That’s basically my reasoning for using it. Development is easy and just makes sense. I can code a lot faster in it than Java (my previous lang of choice), and it’s (gasp!) fun.
PostgreSQL – This is about as enterprise a database as you can get and still be free. Free as in beer, free as in speech. Can’t beat that. Views, stored procedures, and it’s been totally solid and lightning quick for me. I feel in general it’s superior to MySQL, and Rails has awesome support for it, so it just makes sense. Plus it’s true open source, vs MySQL’s pseudo-open source ;)
lighttpd – Apache is the de facto for web hosting…except in Rails apps. I’m not even sure why, really. Basically it’s taht Rails has to run as a CGI app, Rails in CGI is slooooow so you have to use FastCGI, and lighttpd + FastCGI is super quick. I’ve just played around with it a bit and it seems pretty nice so far. It’s been very stable (no crashes yet), very fast, and uses less resources than Apache. Most excellent.
FreeBSD – This, as they say on MTV’s Cribs, is where the magic happens. I think this is the best server OS around right now. Solid, fast, secure…and maintaining it is super easy. Best of all, I think Rails was written to run on it, because of how easy it is. Have problems installing Ruby and Rails on another OS? Not on FreeBSD! Installing the pure Postgres adapter is a cakewalk when you install from ports, compared to being a pain in every other OS I’ve tried.

Okay so as I look at that list, there isn’t really a compelling reason to use FLPR over LAMP, or any combination of OS, httpd, database, and language. I’ve used a lot of web languages and a lot of OSes, and it really comes down to feel for me. The combination I discuss here just feels right when developing, and so far it’s been a winner in production.

I think the FLPR combo is absolutely killer for developing and deploying web apps. If you like it, awesome, if not, that’s cool too. Over the next week, I’ll be posting instructions on how to build a FLPR machine from scratch. The point of this will be to build a machine that can host your web applications online, and eventually I hope to incorporate all aspects of a standard internet server (web, ftp, etc).

Why can’t I use Linux?

Cause then you’re a LLPR, and that means your apps fall apart. (I’ll be here all week, don’t forget to tip your waitress)

Posted in General | Tags flpr, freebsd, lighttpd, postgresql, rails | 6 comments