Configure database connections for a single-threaded application

You probably want a singleton or global database handle. Something like:

    module Highcrest
    module Persistence
        def dbh()
            $dbh ||= DBI.connect 'DBI:OCI8:', 'scott', 'tiger'
        end
    end
    end

Configure database connections for a multi-threaded application

Associate the connection with the current thread. Use a thread-local variable or associate the handle with another accessible object such as Apache::Request.

    module Apache
    class Request
        def dbh()
            @dbh ||= DBI.connect 'DBI:OCI8:', 'scott', 'tiger'
        end

        def cleanup()
            @dbh.disconnect if @dbh
        end
    end
    end

    module Highcrest
    module Persistence
        def dbh()
            Apache::Request.dbh
        end
    end
    end

Unless you use auto-commit, use a request completion event to trigger commits, and the request error-handler to trigger rollback.

You probably also want to establish a connection pool, rather than reconnecting in every request that uses the database.

Navigate to a related object

FIXME

Include a dependent object

To include an object that is automatically persisted along with this one, and which is automatically deleted if this one is deleted:

FIXME

Use optimistic locking in a three-tier environment

Assuming that we send the old values to the client-tier, and it returns back both old values and updated values in the middle tier:

1. Re-read the object from the database (in case it contains attributes that were not sent to the client).
2. Populate the object from the old values, and then reset the optimistic cache from those values.
3. Populate the object from the updated values.
4. Try and save the object.

Something like:

    object = Object.read old_key_value
    populate object, all_old_values
    object.reset_optimistic_cache!
    populate object, all_new_values
    object.replace!

For this to work, you would have to make the reset_optimistic_cache! method public. More likely you would mix in a public populate_old_values method that called it automatically.