There's More Than select()

Updating Data

There's far more to common database interactions than just selection data from the database. To go back to the examples from the previous section, an application that is dealing with class schedules for students may very well need to be able to update its records for a class or for a student. What happens when a student decides to change her name from Jennifer to Julia, for example? How would Kansas make this easy?

# Assume that earlier code has retrieved a record for the student already.
student.first_name = 'Julia'

That's it. We are also assuming that the autocommit setting for Kansas, which defaults to true, was left at true. When this statement is executed, Kansas will change the value of the first_name attribute within the object. It will also push the change into it's rollback buffer, automatically. It then checks the autocommit setting, and if it is true, invoked the commit() method to write the change out to the record for the object within the database. You don't care, though. You just know that Jennifer is now Julia, and that any future queries of the database as well as any future use of the object for that student will reflect that she is Julia, and you didn't have to write one bit of SQL to make that happen.

Updating Using Transactions

That was very easy. However, there are often times when one is updating an object over a period of time, but one doesn't want the changes committed to the database until the last change to the object has been performed. Maybe the administration office has an application interface that lets them alter student information; the designer of the application doesn't want an data written to the database until the person using the application has double checked the changes and confirmed them. Once that happens, all of the changes should be written at once, and if the person instead decides to cancel the operation, they should instead be rolled back.

ksdbh.autocommit = false
# Get the Student object.
student.first_name = 'Julia'
# ...time passes...
student.address = '12345 Lone Tree Rd.'
# ...time passes...
student.city = 'Chugwater'
student.state = 'Wyoming'
# ...more time passes. Changes are confirmed.
ksdbh.commit

When autocommit gets set to false, an explicit commit() call is necessary before any changes are written to the database. This is more or less the same way that transactions with an actual database work. The effect is merely being extended out into the code. If the changes had not been confirmed, a:

ksdbh.rollback

could have been executed. This would have iterated back down through all of the state changes to the object that had occured since the last commit, and would have rolled them all back, restoring the student object to the condition it was in before the changes.

Kansas also offers a block oriented way to do this, as well.

ksdbh.transaction do
student.first_name = 'Julie'
student.address = '12345 Lone Tree Rd.'
student.city = 'Chugwater'
student.state = 'Wyoming'
end

When the block exits then the changes will be committed. If an exception occurs while the block is being executed, the changes will be rolled back. Going back to the original task that was being described, though, this block version seems to lack something. The original notion was that the user could cancel the operation, causing a rollback to be performed. Short of raising an exception that will then either have to be handled in the calling code, there doesn't seem to be a way around having the changes committed when the block exits. Kansas provides a way around this, too, however:

ksdbh.transaction do
student.first_name = 'Julie'
student.address = '12345 Lone Tree Rd.'
student.city = 'Chugwater'
student.state = 'Wyoming'
ksdbh.rollback
end

The rollback() method is smart. It knows when it's being invoked from inside of transaction and will both roll back the transaction and exit the calling block. With these tools, using transactions is pretty easy, and using transactions is usually good practice. It helps ensure that bad data or inconsistent data does not get written to the database and it is also more efficient to process multiple updates at the same time than to do each individually.

Inserting Data

So far everything that has been talked about has assumed that the needed data already resides within the database. Somewhere, somehow, at some time, though, data must get loaded into a database before the database is of any use. Sometimes the applications that one writes are read only, but most often applications need to be able to insert data into the database themselves.

newrow = ksdbh.new_object(TABLE)

new_object() is the method that one uses to do this. When invoked with a table identifier, newObject returns an empty object for a record in the given table. This call will return an empty (i.e. it has no field data) object for a row in the named table, and if the autocommit property is set to true, a corresponding empty row will be inserted into the database.

This usually is not very useful, though. If the table has fields which have not null constraints on them, expect an exception to be thrown. Also, without some sort of primary key information there is going to be no way for Kansas to know how to update the record that was just inserted. What one needs is to either have a way of initializing the object before it is written, or if the table has an auto_increment field as a primary key or something similar, then one wants to turn around and query the inserted record back out of the database before performing updates. The next three examples will demonstrate a few techniques for handling this.

ksdbh.autocommit = false
new_course = ksdbh.new_object('Courses')
new_course.name = 'POLS212'
ksdbh.commit

By turning the autocommit property off, once has the opportunity to populate the row object with field data before it is written to the database. It is only when commit() is called that the insert into the database is performed. There is a more concise method of populating the row object with field data before insertion, too.

initialization_hash = {name => 'POLS212'}
new_course = ksdbh.new_object('Courses',initialization_hash)

The new_object() method will accept an optional second argument. This arg should be a hash. The keys in the hash are interpreted as field names for the table that the record is being inserted into and the values are the initial values to insert into those fields. This initialization hash provides a way to provide the data for insertion even if autocommit it turned on. Sometimes, though, part of a record's data is provided by the database itself. Records that have a sequence as a key to the record, for instance, require that the database provide that key. An example of this is the MySQL auto_increment field. The only way to get that is to perform the insertion of the record, and then to query the record back out of the database. The following example is MySQL-centric, but the concept applies to other types of db-provided data, as well.

ksdbh.new_object('Students')
new_student = ksdbh.select_one('Students') {|s| s.student_number = s._last_insert_id()}.first

In this case, the object returned by new_object() isn't really useful since the act of inserting the row into the database changes at least one field, the student_number, in the row. So, the code doesn't bother to save this object. Instead, it follows the new_object() call with a query that will return the new record (since the student_number field of the new record will equal the value returned by the last_insert_id() database function.

Deleting Data

The last of the four main ways of interacting with data is the ability to delete records from a table. There are two seperate delete() methods available for this. The first is called on an object representing a row of data; it deletes that row. The second is called on the main Kansas object, with a syntax identical to that of a select statement. It is used to delete multiple rows of data at once.

The first case for handling data deletion is to delete a row that one already has an object for:

pete = ksdbh.select_one(:Students) {|s| s.first_name == 'Pete'}
pete.delete

Following the delete() call, the pete object will still exist. However, it will have nil for all of its values and any attempts to change its values will not stick. Assuming that autocommit was turned on, the row corresponding to pete will be gone from the database, as well.

If autocommit is not turned on, a delete() call on an object will work as expected. The object will be cleared of its values as in the above example. The database row, however, will not be deleted until commit is called, and if a rollback occurs, the object will be restored to its prior state.

pete = ksdbh.select_one(:Students) {|s| s.first_name == 'Pete'}
ksdbh.transaction do
pete.courses_taken.each {|c| c.delete}
pete.delete
puts "Pete's number is gone: #{pete.student_number}"
ksdbh.rollback
end
puts "Pete's number is still here: #{pete.student_number}"

Line 1: Retrieve a record for Pete.
Line 2: Start the transaction.
Line 3: Iterate through each of Pete's courses, deleting them.
Line 4: Delete Pete, now.
Line 5: pete.student_number would return nil here.
Line 6: Roll the changes back.
Line 7: End the transaction block.
Line 8: The object for Pete would be back to it's status prior to the transaction after the rollback completed.

The other method of performing a delete operation uses a method that looks a lot like the select() method. It is used to delete one or more rows of data without first having to acquire objects representing the rows.

ksdbh.delete(:CoursesTaken) {|ct| ct.name == 'XZY987'}

This line would delete all records from the courses_taken table where the course name was XZY987. If one wants to know what rows are being eliminated from the database, one can capture that data, too:

deleted_rows = ksdbh.delete(:CoursesTaken) {|ct| ct.name == 'XZY987'}

deleted_rows will contain an array of objects for each of the rows that was deleted. It is equivalent to:

deleted_rows = ksdbh.select(:CoursesTaken) {|ct| ct.name == 'XZY987'}
ksdbh.delete(:CoursesTaken) {|ct| ct.name == 'XZY987'}

If this use of delete() occurs inside of a transaction, the array returned will show the current set of rows that would be deleted if the deletion were executed at that moment. The actual delete operation, however, will not occur until the commit() is called.

What If I Need To Use The Database Handle Directly

A KSDatabase object can be used as a DBI::DatabaseHandle directly. i.e.

sth = ksdbh.prepare('select foo from bar')

Just use your ksdbh object like a typical dbh object if you want to write and handle some SQL code directly. If, for whatever reason, you need to gain direct access to the database handle that the KSDatabase object is using, there is also a dbh() accessor method that can be used.

mydbh = ksdbh.dbh

Client Login
User ID:
Password: