Enigo Homepage
Client Login
User ID:
Password:

Let's Get Started!

[This tutorial assumes Kansas version 0.0.2 or greater.]

Creating A Kansas Object

This is all that is required to start using Kansas:

  1. require 'kansas'
  3. Vendor = 'Mysql'
  4. Database = 'Test'
  5. Host = 'localhost'
  6. User = 'testuser'
  7. Password = 'testpassword'
  9. ksdbh = KSDatabase.new("dbi:#{Vendor}:#{Database}:#{Host}",#{User},#{Password})
  10. ksdbh.map_all_tables

This example is very simple, but let's take it apart a piece at a time and analyze it.

Line 1
To use Kansas, the library must be required. There are no other libraries that must be required for Kansas to work, although Kansas itself requires dbi, and will not work without DBI being available.
Lines 3-7
Just some constants used to store the database connection information. These are merely examples. There are many different ways to manage database connection information and two different ways to supply Kansas with a database connection. This tutorial will cover both of those methods, but will not delve into details about managing database connection information.
Line 9
Create the Kansas object. It takes a DSN, username, password, and optional options hash for creating the internal DBI object.
Line 10
Finally, the Kansas object is told to go ahead and map all of the tables that DBI reports are accessible. After this point, all of the database's tables and data are accessible as objects.

This method of object initialization will usually work perfectly adequately for the programmers needs. Sometimes, though, one has a more complex relationship with a database happening within an application. It may be that a connection to the database is created elsewhere, and one would like to simply use that already existing connection, or it may be that a database connection pool is in use, and one wants Kansas to only use a connection delivered by the connection pool, or there may be something else going on. Regardless, though, there needs to be a way to make a Kansas object utilize a handle that already exists.

  1. ksdbh = KSDatabase.new(@dbh)
  2. ksdbh.map_all_tables

If new() is passed a database handle, the new object will happily use that handle for all of its database interactions. Nothing else is necessary.

Mapping Tables To A Kansas Object

As shown in the examples above, a method is provided to simply map all of the tables within a database. For small databases, this is often sufficient. For large databases where one may only need access to a subset of tables, or where one wants more control over the mapping process, Kansas provides a mechanism to map tables one at a time.

  1. ksdbh.table('Students','students')

The table() method is used to create a mapping between a database table and a Ruby class. The general form of the method invocation is: 

table(local_name, remote_name)

The local_name parameter specifies the class name to use for the table representation, while the remote_name specifies the name of the table within the database. The method will request from information on the fields within the table and will construct a class that encapsulates those fields. Both the remote names and the local names can be passed either as strings or as symbols. There's no reason not to use symbols, especially for the local names, as the local names will be refered to repeatedly,

The other half of manually handling table mapping is specifying the one-to-many and many-to-one relationships between the tables:

  1. ksdbh.table(:Students,:students)
  2. ksdbh.table(:Courses,:courses)
  3. ksdbh.table(:CoursesTaken,:courses_taken)
  4. KSDatabase::CoursesTaken.to_one('course', 'name', 'Courses')
  5. KSDatabase::CoursesTaken.to_one(:student, :student_number, KSDatabase::Students)
  6. KSDatabase::Students.to_many(:courses_taken, KSDatabase::CoursesTaken, "student_number")
  7. KSDatabase::Courses.to_many(:students_taking, :CoursesTaken, :name)
Lines 1-3
As in the prior example, this is just mapping the specified tables to Ruby classes. Note that in these lines all of the parameters were passed as symbols.
Lines 4-7
If you scan over these lines for a moment, you will notice that there is no consistency between when a string was used for a parameter and when a symbol was used for a parameter. Further, you'll notice that in several cases the constant name of the class representing a table was also used. This is simply to illustrate that the library will let one use whatever convention one is comfortable with, or that fits the coding style being used. As a general rule, though, this author prefers to use either the pattern in line 4 or in line 7, so that consistency is maintained in the code.
Line 4
The to_one() method is called on the class representing a table. It declares that the local table is linked to only one record from the foreign table. It takes three parameters: name, local field, and foreign table. The name parameter specifies the name to use to access this relationship. The local field is the field within the local table that is to be used as the key in the foreign table. And the foreign table is the table that is being linked to. So, in this example, if foo is an object of CoursesTaken, then: 
foo.course
will link to an object for the row in Courses where CoursesTaken.name matches the primary key in Courses.

Currently Kansas is limited to defining relationships that key off of the primary key of another table. This will be changing soon so that one-to-many and many-to-one relationships may key off of any field.
Line 6
The to_many() method is invoked like the to_one method. It declares that the local table may link to many records in the foreign table. It, also, expects three parameters. In this case, they are: name, foreign table, and foreign field. Name is, as with to_one, the method name that will be used to access the relationship. Also as with the prior example, the foreign table is the table that a link is being established with. The foreign field is the field in that table which the local table's primary key must match for a link to exist. So, if foo is an object of Students, then: 
foo.courses_taken
will return an array of Courses for that Student.

Okay, But How Do I Get At The Data?

  1. ksdbh.select(:Students).each do |student|
  2.   puts "Name: #{student.name}"
  3. end

In Kansas, the select() method is the channel through which one queries the database. It expects one parameter and will take an optional second parameter. The first parameter is the table to perform the select on. This can be passed either as a string or a symbol or as the full classname for the table (i.e. KSDatabase::Students). The optional second parameter is the SQL to use when performing the query. This is there primarily for flexibility; it's not often used.

In addition, the select() method can take a block. If only the table to operate on is passed, and there is no block, then all of the records in the table will be queried. If a SQL statement is passed, and there is no block, then the SQL statement will be executed. However, if a block is provided, the contents of the block will be evaluated to define to details of the query to execute. An example:

  1. ksdbh.select(:Students) {|s|
  2.   (s.student_number < 102) | (s.student_number > 104)}.each do |student|
  3.   puts "Student #{student.name}; Number: #{student.student_number}"
  4. end
Line 1
Select from Students...
Line 2
...every record where student_number is less than 102 or is greater than 104, and iterate through each record returned,...
Line 3
...printing the name and number for each record.

This mechanism provides a natural, rubyish way of defining the selection criteria. The rules about how to write ruby code to specify the details of the SQL statement are pretty simple. The block is providing zero or more modifiers to the SQL statement, such as an ORDER BY clause, a LIMIT clause, or a GROUP BY statement. Following these modifiers should be a boolean of some sort; this boolean will comprise the WHERE portion of the SQL statement. The other thing to remember is that each reference to a field in the table and each reference to a statement modifier or database function must be called on the block variable. Here's a more complex example to illustrate this:

  1. ksdbh.select(:Students) {|s| #s is the block variable
  2.   s.order_by(s.last_name, s.first_name => 'desc')
  3.   s.limit(3)
  4.   (s.student_number.between(103,106) & (s.student_number != 104))
Line 1
This is just like the other examples. The s variable is the block variable.
Line 2
The order_by() method takes for its arguments either the field to sort on, in which case it defaults to sorting in ascending order, or a hash containing fields to sort on as keys and either 'asc' or 'desc' as values, to indicate whether to sort in ascending or descending order.
Line 3
This line adds LIMIT(3) to the end of the generated SQL statement.
Line 4
Finally the block ends with a boolean comparison that will define what appears in the WHERE portion of the SQL statement. This Ruby code writes this SQL:
WHERE (Students.student_number BETWEEN 103 AND 106) AND (Students.student_number != 104)

The following is the currently defined set of boolean operators that can be used within a block to define the contents of the WHERE clause:

Ruby Operator SQL Operator
& AND
| OR
< <
> >
<= <=
>= >=
=~ LIKE
== =
<=> <=>

The following table lists the current set of other operators and functions currently available for use.

Method SQL Statement/Database Function
is_null() IS NULL
is_not_null() IS NOT NULL
in() IN
not_in() NOT IN()
between() BETWEEN
not between() NOT BETWEEN
greatest() GREATEST
least() LEAST
min() MIN
max() MAX

In addition, any method with an underscore as its first name is assumed to refer to a database function, and is simply inserted into the SQL as-is. One use for this was illustrated earlier, with last_insert_id(). 

s.student_number == s._last_insert_id()

And finally, here is the set of SQL statement modifiers that currently works:

Method SQL Keyword
order_by() ORDER BY
sort_by() ORDER BY
limit() LIMIT

Work is being done on the expression handling portions of Kansas to allow access to more database operations and functions. This may be obvious, but not all of the database functions and operators accessible via Kansas are actually implemented on all of the database backends that DBI supports. For database independent code, it is up to the developer to make careful decisions about the database functions that will be used and how they will be used.