Rails and Databases

• You’ll first need to create your database in whatever fashion you normally work with your RDBMS…
• One rails convention that is commonly used is to separate the words in your database name with underscores.
• The content of the table also plays an important part in what it is named. So if we are going to store Albums in a table it should be called “albums” rails will expect this too.
• Another common convention is to follow the name with _development or _production to let rails know what database your working with.
• Begin to create your tables in whatever fashion you normally work with your RDBMS…
• Every table should have an “id” field to serve as the primary key and it should always be called “id” this is part of the rails sensible defaults.

Connecting Rails to Your Database

• After you create a site you’ll have access to a database.yml file which is located in your “config” sub directory. This file is what is known as a YAML (Yaml Ain’t Markup Language) pronounced “yamal” file which is mostly made up of short phrases where variables are defined.
• The .yml file makes some assumptions based on the name of your application that your database will follow the same name.
• You’ll need to add passwords to each database entry in the .yml file that you will want to access. You don’t need passwords for database entries you wont access.
• To test that your application is able to connect to your database you can use the “rake” command: rake db:migrate
• rake produces a file in the “db” folder called “schema.rb” which is known as a migration (a technique in rails which is a way to write sql using ruby)

how do we get database information into an object?

• • if you know about inheritance you could write one ruby class which would know how to interact with your database then all of your models could inherit that behavior, and this is what the ActiveRecord::Base inheritance does for your models

Creating Databases

• it is important to pay attention to the column names in your database tables
• sensible defaults play a huge role in how rails interacts with your database tables
• it is a good idea to create a field in your table that gives information about when a record was created or updated, rails makes this easy through sensible defaults. Make sure that you name the column: “created_at” and make it a DATETIME field type or “updated_at“. ActiveRecord will automatically add the current local time and date into this field when a new record is created for “created_at” and every time a record is updated for “updated_at”.

Relational Databases in rails

• when we relate the information in two tables together we then say our database is relational.
• relationship types and how to make them in rails:
  • 1 to Many: One particular object/model (artist) related to many different objects/model instances (albums) how do we make this relationship in rails?
    • <model_name> has_many:<model_instance> (i.e. Artist has_many:albums)
    • <model_name> belongs_to:<model_name> (i.e. Album belongs_to:artist)
  • Many to Many: Another type of join but less common in development
    • generally you’ll want a “join table” between your two many to many tables, this join table provides a link between the two tables.
    • the two tables are joined with the foreign key in this join table.
    • the join table doesn’t have a model though, this is important to remember.
    • the table should be named using the name of both tables separated by an underscore so for example albums_features, one album has many features and many features belong to many albums, also this name has to place the two joined tables in alphabetical order so albums comes before features in the name.
    • you’ll also need to modify your model for each of the joined tables and put something like this in: has_and_belongs_to_many :<joined table>
    • you can then simply refer to the model/table name to get the array of items that are in the join table. For example album.features
• how do you get your database ready to accept a relationship type in rails?
  • you create a foreign key in your dependent table in the form of “<model_name>_id” (i.e. artist_id)
  • once you have created a relationship between your tables you have access to the information contained in this field through the use of the model name such as: album.artist or artist.albums

Working with Databases

• ActiveRecord::Base inheritance is important for models because it has all the information about connecting to databases.
  • some of the methods of active record base are:
    • .find_by_sql(’<sql_statment>’)
    • .find(:all) //in its basic format will take the symbol :all to represent SQL statements…
    • .find(:all, :conditions => “release_date <= ‘1978-01-01′”) //the rails way of using a condition to locate specific data in the database
    • .find(:all, :order => ‘<field> ASC or DESC’) //rails way of ordering field data by using the :order symbol
    • .find(:all, :limit => 2) //rails will limit our found set to 2 records this is the same as using the SQL LIMIT statement
    • .find(:all, :limit => 2, :offset => 2) //rails will limit our found set to 2 records this is the same as using the SQL LIMIT statement. Also you can use the :offset the same as you would use the SQL OFFSET statement
    • .find(:first, :conditions => “release_date <= ‘1978-01-01′”))//find the first record that matches your condition and only return the first record
      
    • .find_by_id(<id_number>)//search for a record with a specific id number
• you can loop through your data using a for in loop such as: for <local_variable> in @<instance_variable> … <actions> end
• make sure that your config->database.yml file contains a password and user name for the appropriate database you’ll be using.
• CRUD is an important acronym for rails development and it stands for Create Read Update Delete
• in rails “id” column is very important because it helps to distinguish one record from the next.
• SQL Select - Takes care of the read part of CRUD
  • Example of Select:
    select * from <table_name>
    where <conditional_statement>
    order by <what to order by> ASC or DESC

• SQL Insert - Takes care of the create part of CRUD
  • Example of Insert:
    insert into <table_name> (<columns>,<columns>)
    values (<values>, <values>);
• SQL Update - Takes care of the update part of CRUD
  • Example of Update:
    update <table_name>
    set <column_name> = <value_to_set>
    where <column_to_match> = <value>;
• SQL Delete - Takes care of the delete part of CRUD
  • Example of Delete:
    delete from <table>
    where <column_to_match> = <condition>;
• if we want to match on somthing specific in SQL we can use the = sign by itself such as: <field_name> = ‘<string>’;
• if we want to match on part of something in SQL we can use the LIKE operator such as: <field_name> LIKE ‘%<string>%’;

• Searching for dynamic values (the R in CRUD)

  • we could hard code in what we want to search by but in the real world this won’t cut it.
  • there are a couple of ways we can search for dynamic data
    • we could have a variable and set its value above our sql statement to a default value and then we would pass the entire statement as an array to the :conditions symbol for example:
      • release_date = ‘2007-01-01′
        artist = ‘Beatles’
        .find(:all, :conditions => ["release_date <= ? AND artist LIKE ?", release_date, '%'+artist+'%'])
  • You might want to allow the user to specify the value to search for as a URL parameter:
    • you can do this using the params hash in a find method: .find(params['id'])
    • make sure that you create a link with the id attached to the URL

• Creating new records (the C in CRUD)

  • when you work with rails you don’t create a new record using a SQL insert statement, instead you create an object/instance of your model which will already have all the attributes (fields) in it, you set these attributes to the values you want and then simply save the model and active record will check to see if the record already exists in the database and if it doesn’t it will create a new one.
  • for example:
    • @albums = Album.new
      @album.title = ‘Beggers Banquet’
      @album.artist = ‘The Rolling Stones’
      @album.release_date = ‘1968-01-01 12:00:00′
      @album.genre = ‘Rock’
      @album.save

• Updating records (the U in CRUD)

  • first and foremost you’ll want your update form to point it’s action attribute to an update action in the controller: <%= form_tag(:controller => ‘<name>’, :action => ‘update’) %>
  • you also want to send along the “id” of the record you want to edit: <%= form_tag(:controller => ‘<name>’, :action => ‘update’, :id => @<instance_name>.id) %>
  • in your update action of the controller you’ll want to use the .find() method of your model to get the correct record by id for example:
    • @album = Album.find(params[:album])
  • in your update action of the controller you’ll want to use the .update_attributes() method of the model to update the attributes of the object instead of simply saving. For example:
    • @album.update_attributes(params[:album])

• Deleting records (the D in CRUD)

  • you first and foremost need to find the record that you want to delete
  • in order to delete the record that was found you need to invoke the active record method .destroy()
  • you’ll probably want to re-direct the user to a new page after the destroy method has run. You can simply use the redirect_to() method.
  • you should always use the :confirm option of the link_to() method for any destroy methods.
  • note that if you use the :confirm option you’ll need to place your other options for link_to() into a hash:
    • <%= link_to(’Destroy’, {:action => ‘destroy’, :id => @<instance_variable>.id}, :confirm => ‘Are you sure?’ %>)