Installing SQLite in Font

Maker ECC200 in Font Installing SQLite

The first step to getting a database system up and running quickly is to install SQLite3 the latest version of SQLite. SQLite s download page at http://www.sqlite.org/ download.html contains binary downloads of the SQLite3 libraries for Windows (DLL) and Linux (shared library), as well as the source code for compilation on other systems. Mac OS X DarwinPorts users can install SQLite3 by typing sudo port install sqlite3 at the command prompt. Users of certain Linux distributions may be able to install SQLite3 using the respective package manager.

Note For Windows users there s a video screencast of the SQLite 3 installation process at http://

blip.tv/file/48664.

Once the SQLite3 libraries are installed, you can install the Ruby library that gives Ruby access to SQLite3 databases as a gem. The gem is called sqlite-ruby and can be installed on all systems with gem install sqlite3-ruby or sudo gem install sqlite3-ruby on Unix-related operating systems if you aren t running as a super-user. (For information about installing Ruby gems, refer to 7.) You can check that everything was installed okay with this code:

require 'rubygems' require 'sqlite3' puts "It's all okay!" if defined (SQLite3::Database)

It's all okay!

If the installation didn t progress smoothly, links to SQLite resources are available in Appendix C.

To manage databases with any of the various database systems at a basic level, knowledge of several SQL commands is required. In this section we re going to look at how to create tables, add data to them, retrieve data, delete data, and change data. Throughout this section, think entirely in terms of databases separately from Ruby. A demonstration of how Ruby can use SQL to manipulate a database is covered in detail in the later section Using SQLite with Ruby.

Note If you re already familiar with SQL, you can skip the next few sections and jump straight to the

Structured Query Language (SQL) is a special language, often known as a query language, used to interact with database systems. You can use SQL to create, retrieve, update, and delete data, as well as create and manipulate structures that hold those data. Its basic purpose is to support the interaction between a client and a database system. In this section I m going to give you a primer on SQL s syntax and how you can use it from Ruby. Be aware that this section is only a very basic introduction to SQL, as a full and deep explanation of SQL is beyond the scope of this book. If you wish to learn SQL in more depth, please refer to the resources mentioned in Appendix C. Note that the way different database systems use and implement SQL can vary wildly, which is why the following sections will only cover that which is reasonably standard and enables you to perform basic data operations.

Before you can add data into a database, it s necessary to create one or many tables to hold it. To create a table, you need to know what you want to store in it, what you want to call it, and what attributes you want to store. For your table people, you want to have name, job, gender, and age columns, as well as a unique id column for possible relationships with other tables. To create a table, you use a syntax like so:

CREATE TABLE table_name ( column_name data_type options, column_name data_type options, ..., ... )

Therefore, for your people table, you d use this syntax:

CREATE TABLE people ( id integer primary key, name varchar(50), job varchar(50), gender varchar(6), age integer)

This SQL command creates a people table and gives it five columns. The data types for the name, job, and gender columns are all VARCHARs, meaning they re variable-length character fields. In basic terms, it means they can contain strings. The number in brackets refers to the maximum length of that string, so the name column can hold a maximum of 50 characters.

Note SQLite is a reasonably pragmatic database, and it ignores most conventions relating to data types

in SQL. Almost any form of data will fit into any type of column. SQLite ignores the maximum lengths for these VARCHAR columns. This is one reason why SQLite is great for quick and easy development, but not so great for crucial systems!

The id column has the words primary key as its options. This means that the id column is the primary reference to each row and that the id must be unique for each row. In SQLite, this means SQLite will automatically assign a unique id to each row, so you don t need to specify one yourself each time you add a new row.