Kiss A simpler, smarter web application framework for Ruby

Database Models

SECTIONS
Introduction to Models Configure Database Access Select Database Objects Edit Database Objects Create New Database Objects Delete Database Objects Where's the Model Class File? Relationships Between Models Business Logic in Models Direct Database Access Without Models

Introduction to Models

Each database table gets its own class (called a model in Kiss). To create a new row in the database, we create a new object of the corresponding model class, set its data, and save it. To read rows from the table, we call methods on the model class to specify which rows to select, and the selected rows are automatically converted into Ruby objects of the model class. We can update or delete rows by calling methods on individual model objects or more sweeping methods on the model classes.

Relationships

We can also specify how models are related to each other in a very simple Ruby syntax. Then we can get from any model object to related data from other tables with very simple Ruby method calls.

Custom Model Methods: Reusable Business Logic

We can also define custom methods for each model, allowing us to reuse code that is commonly executed on a particular kind of data.

Ruby Sequel ORM

Kiss uses the Sequel library to access database table rows and turn them into Ruby objects. This chapter will teach some basic Sequel syntax as we go along.

Configure Database Access

First, we need to tell Kiss how to access your database. In your application directory, open the file at config/common.yml and edit the lines under :database: to insert the login information for your database.

MySQL

For MySQL, just edit the database name, username, and password. If your database uses a different encoding than utf8, then be sure to change that too. (By the way, it's a good idea to use a multilingual encoding such as utf8, as you never know when a user might want to store data using accent marks or even entirely different character sets.)

SQLite

If you are using SQLite instead of MySQL:

  1. Change the :adapter: setting from mysql to sqlite.
  2. Remove the :host: line.
  3. Sspecify the path to your database file on the :database: line.
  4. Change the :encoding: setting if necessary.

Select Database Objects

Basic Sequel Methods

Say we have a users table, created with this SQL code:

create table users (
  id int unsigned not null auto_increment,
  
  username varchar(16) not null,
  password varchar(40) not null,

  first_name varchar(50) not null,
  last_name varchar(50) not null,

  email varchar(100) not null,

  city varchar(50) not null,
  state varchar(2) not null,
  zip varchar(10) not null,
  
  unique (id),
  unique (username)
) ENGINE=InnoDB DEFAULT CHARSET utf8;

In our action code, we can select an array of all the user records like this:

users = models[:users].all

To select an array of all the users in California, sorted by last name, then first name, we can do this:

users = models[:users].where(:state => 'CA').order(:last_name,:first_name).all

If we want to select an individual user by their username, we do it like this:

user = models[:users].where(:username => 'bob123' ).first

To select a user by the record's numeric ID, we do this:

user = models[:users][id_number]

Note: dbm is a shorthand alias for modelsdbm is short for "database models."

Select Objects Using Argument Values

It is common in Kiss to specify the numeric ID of data record as an argument to the request URL. For example, to view a user profile, we might request: http://localhost:4000/view_user/5073921, and then the numeric ID 5073921 will be available to our action code as args[0]. We can then select the corresponding user record in two ways:

# the messy way
user = models[:users][args[0]]

# the cleaner shortcut
user = arg_object(:users)

The arg_object method selects a model object from the specified table, using an argument value. arg_object takes an optional second parameter, to specify the index of the argument to use. If you omit this second parameter, it will use the first argument, args[0].

If there is no data record matching the desired ID, arg_object will raise an error. There is an alternative method, arg_object_or_new, which takes the same parameters as arg_object but will create a new model object of the specified table if no record is found with the desired ID.

Select Objects Using Parameter Values

Kiss also has similar methods to select model objects using parameter values. Say we request a user profile with this request: http://localhost:4000/view_user?id=5073921, and then the numeric ID 5073921 will be available to us as params.id. We can then select the corresponding user record in two ways:

# one way
user = models[:users][params.id]

# another way
user = param_object(:users,:id)

The param_object method selects a model object from the specified table, using the value of the specified parameter. param_object always takes a second parameter, to specify which parameter to use.

If there is no data record matching the desired ID, param_object will raise an error. There is an alternative method, param_object_or_new, which takes the same parameters as param_object but will create a new model object of the specified table if no record is found with the desired ID.

Edit Database Objects

After we have selected a data record, we can edit its data easily as shown here:

user.first_name = 'Robert'
user.last_name = 'Frost'
user.save

The first statement sets the object's first_name but does not store this data in the database table. The second statement sets the last_name. The save method in the third statement updates the database record to store the changes on both columns to the table.

We can also call the update method on the model class to update multiple rows at once. This statement changes the first name of every user in California to "George".

models[:user].where(:state => 'CA').update(:first_name => 'George')

Create New Database Objects

There are a few ways to create new database rows. We could use the new method to get an empty object, then populate its fields one at a time and save it:

user = models[:users].new
user.username = 'bob123'
user.first_name = 'Bob'
user.save

Or we could use new to preset data in your new object, and then save it:

user = models[:users].new(
  :username => 'bob123',
  :first_name => 'Bob'
)
user.save

Or we could create an object and save it at in one statement with the create method:

user = models[:users].create(
  :username => 'bob123',
  :first_name => 'Bob'
)

Delete Database Objects

We can delete individual records really, really easily (be careful!):

user.delete    # poof!  gone from Ruby, gone from the database too!

Deleting multiple records isn't that difficult either. This deletes all users with first name "Bob":

models[:users].where(:first_name => 'Bob').delete

Where's the Model Class File?

Notice that we've done all of the above without creating a file to define a model class for the users table. That's because Kiss creates the model class for us automatically when we call models[:users]. This lets you prototype your application more quickly, since you don't have to define an empty model class to start using a new table you've just created.

We can, however, customize the users model class if we need to, by creating a Ruby file named users.rb inside the models directory of our application. The next two sections explain what we might put inside this file.

Relationships Between Models

Let's add a photos table to store information on users' photos, using this SQL code:

create table photos (
  id int unsigned not null auto_increment,

  user_id int unsigned not null,

  filename varchar(16) not null,
  caption text,

  unique (id)
) ENGINE=InnoDB DEFAULT CHARSET utf8;

We could select an array of a user's photos like so:

photos = models[:photos].where(:user_id => user.id).all

But that's a bit messy, especially if we use it repeatedly in multiple actions. Fortunately, Sequel lets us define a much easier syntax. First, we have to tell Sequel how users and photos are related.

One-to-Many (has_many)

Create a file in the models directory of your application named users.rb, and add this line of code:

has_many :photos

This single line tells Sequel that users have a one-to-many relationship with photos. In fact, if you find the above has_many syntax confusing, you can use the word one_to_many instead; they are synonyms.

Now we can select the array of a user's photos just by calling user.photos. That's a lot simpler and cleaner than the longer statement above!

Many-to-One (belongs_to)

We can also add a way to get from a photo object back to its user. Create a file in the models directory of your application named photos.rb and put this code in it:

belongs_to :user

(By the way, you can use many_to_one as a synonym for belongs_to.)

Now we can get the owner of any photo just by calling photo.user. Pretty easy, huh?

Many-to-Many

Sequel also supports a many_to_many  relationship, that works much like the has_many relationship described above, except that you won't use a belongs_to in the other model; you'll use many_to_many on both sides of the relationship.

Named Relationships

Now let's add a messages table to our database with this SQL code:

create table messages (
  id int unsigned not null auto_increment,

  sender_user_id int unsigned not null,
  recipient_user_id int unsigned not null

  sent_at datetime not null,
  read_at datetime not null,

  message text,

  unique (id),
  key (sender_user_id,sent_at),
  key (recipient_user_id,sent_at)
) ENGINE=InnoDB DEFAULT CHARSET utf8;

Notice that each message is connected to two users, the sender and the recipient. To tell Sequel how messages are related to users, we'll need to give Kiss a little more information than in our previous examples. But it's still quite easy to do.

In our messages.rb model class file, let's add:

belongs_to :sender, :class_name => :users, :key => :sender_user_id
belongs_to :recipient, :class_name => :users, :key => :recipient_user_id

These statements give Sequel the information needed to select the users associated with a message via message.sender and message.recipient. The class_name attribute specifies the other table of the relationship, and the key specifies which column connects the two tables.

Now in the users.rb model class file, let's make reverse relationships to messages:

has_many :incoming_messages, :class_name => :messages, :key => :recipient_user_id
has_many :sent_messages, :class_name => messages, :key => :sender_user_id

Can you guess how we'd select a user's inbox and sent mail? That's right: user.incoming_messages and user.sent_messages.

Sequel knows that in belongs_to (many_to_one) relationships, the key column is found in the same table, but in has_many (one_to_many) relationships, the key column will be in the other table specified by the class_name attribute.

Business Logic in Models

To keep our application more secure, we'll use one-way encryption to protect the users' passwords before storing them in the database, instead of storing them in cleartext. We'll have to deal with user passwords in at least three actions:

  1. in user registration when we create the user record,
  2. when the user logs in, and we compare the password entered with the password in the database, and
  3. when the user changes their password.

Rather than repeat the encryption logic in three different action files, it would be more convenient to keep the encryption methods in the users model class. So we'll add this to models/users.rb:

require Digest::SHA1

def self.encrypt_password(cleartext)
  Digest::SHA1.hexdigest(cleartext)
end

def check_password(cleartext)
  self.class.encrypt_password(cleartext) == self.password
end

def set_password(cleartext)
  self.password = self.class.encrypt_password(cleartext)
end

As you can see, we've defined a class method encrypt_password and two instance methods check_password and set_password. We can use these methods to test a password entered by the user, or to set a new password for the user. Here's how to call these methods from an action:

# model instance methods
user.check_password( cleartext_password )
user.set_password( cleartext_password )

# model class method
models[:users].encrypt_password( cleartext_password )

Direct Database Access Without Models

Sequel database models in Kiss provide a great way to simplify database access. But they don't do everything. Sometimes, we want to perform more complicated database queries with joins, subselects, or temporary tables, for performance or other reasons, and we need a way to access the database without going through model classes.

Kiss also provides a standard Sequel::Core database connection, which you can use in your action code via database, or db for short. Here's a simple example, similar to what we did with the users model.

database[:users].where(:state => 'CA').order(:last_name, :first_name).all

By changing models to database, we change the result of this expression from an array of users model objects to an array of simple hashes, with key-value pairs tying each column to its value. These hashes don't have any of the magic of model objects, but they're faster to fetch.

We can also run more complicated queries by passing a string instead of a table name symbol:

database[query_string].all

The results are returned in an array of hashes. If you prefer an array of arrays, use:

database[query_string].all_arrays