100 | Chapter 4: Database
SQLite
SQLite is a minimalist database that is excellent for small projects. Although it does
not support many fancy features, it is a great choice for projects that will not grow
very large. It supports ACID transactions*out of the box. SQLite is a library that is
linked into your program; there is no server process to speak of. The library code
residing in your application’s process space accesses a database file.
SQLite provides no concurrency, as there is no server process to enforce the ACID
properties. Therefore, it uses file-level locking: the entire database file is locked at the
filesystem level during a transaction. Still, for many small applications, it fits the bill
perfectly. It is a good replacement for data that may have been stored in flat files, as
it supports most of the SQL-92 standard and would be easy to migrate to a more tra-
ditional DBMS as needs grow.
Microsoft SQL Server
Though Rails grew up in the Linux/Unix world, it has developed great community
support for the Windows platform as well. Not only are Microsoft SQL Server data-
base connections supported in Rails, there are also provisions for connecting to SQL
Server from a Linux-based systems as well, using the FreeTDS library.†
From a Windows client, the standard approach is to use Ruby-DBI (a Ruby database-
independent adapter) with ADO. The configuration looks like this:
development:
adapter: sqlserver
host: server_name
database: my_db
username: user
password: passYour configuration may vary, depending on the version of SQL Server and the ADO
libraries you have installed. Once the database configuration is in place, the stan-
dard ActiveRecord API methods can be used to manipulate data.
Oracle
Rails supports Oracle versions 8i, 9i, and 10g through the ruby-oci8 library,‡which
supports the OCI8 API. Windows, Linux, and OS X are supported as clients. The
connection configuration is fairly standard, usingocias the connection adapter name.
- ACID stands for Atomic, Consistent, Isolated, and Durable, which are necessary properties for transactional
integrity within a database. Seehttp://en.wikipedia.org/wiki/ACID for a full definition and explanation.
† Instructions are athttp://wiki.rubyonrails.org/rails/pages/HowtoConnectToMicrosoftSQLServerFromRailsOnLinux;
FreeTDS is available fromhttp://www.freetds.org/.
‡http://rubyforge.org/projects/ruby-oci8/