Starting with SQLite (aka REALSQLDatabase)

Posted by paul on Jan 9, 2009 in Uncategorized | Comments Off

This article originally appeared in REALbasic Developer Magazine 4.4 (Mar/Apr 2006)

REAL SQL Database (REALSQL) is the single-user database engine included with REALbasic. It is based on SQLite 3 (http://www.sqlite.org), a fast and powerful embedded open-source database engine.

REALSQL supports all the SQL commands that SQLite supports:

In order to start working with any of these exciting SQLite commands, you need to create a REALSQL database. This is not hard at all as we’ll see with some sample code. All access to REALSQL databases is coincidentally done through the REALSQLDatabase class. Once we have an instance of that class, we can assign it to a folder item and choose to create a new database. For example:

Dim testDb As New REALSQLDatabase
Dim testDbFile As New FolderItem("TestDb.rsd")
 
testDb.DatabaseFile = testDbFile
 
If testDb.CreateDatabaseFile Then
	// Database is now available for use
Else
	MessageBox("Unable to create the database.")
End If

We can now use testDb to execute SQLite commands. For example to create a simple table, we would use this command:

CREATE TABLE simple ( firstName TEXT, lastName TEXT, age INTEGER )

This REALbasic code will send the above command to our database:

Dim result As Integer
result = testDb.SQLExecute("CREATE TABLE simple ( firstName TEXT, lastName TEXT, age INTEGER );")

Now we are ready to start talking about the Table commands. The three table-related commands (ALTER TABLE, CREATE TABLE and DROP TABLE) do what their names suggest: create new tables, alter (or change) existing tables, and drop (delete) existing tables.

Creating Tables

As we briefly saw above, the CREATE TABLE command is the first one you would need to use after creating the database. After all, a database without any tables is not of much use! If you go to the official SQLite documentation page for CREATE TABLE (http://sqlite.org/lang_createtable.html), you’ll see great detail about what this command can do for you. We’ll try to focus on a few practical examples here.

All tables contain a unique primary key (_rowid), but you should really create your own unique key as well. Although these _rowid values are unique, they can change when rows are deleted. If you rely on them as your only key, you’ll be in big trouble if you try to use them as foreign keys! These primary keys are stored in 64-bit integers, so the sun will go cold before you would run out of key values. Use the type of INTEGER PRIMARY KEY to create your primary key.

Columns can be either NULL or NOT NULL. NOT NULL columns must have a value. Columns can also have defaults which are automatically assigned to the column when a row is created and no other value is specified.

Let’s create a table to store URLs for a fictional bookmark database (refer to the sidebar for information on SQLite data types):

CREATE TABLE bookmark (ID INTEGER PRIMARY KEY, URL TEXT NOT NULL, Description TEXT)

SQLite also has the ability to create temporary tables. As the name suggests, temporary tables are not permanent and will only be available for the while you are connected to the database; once the connection is closed, the table disappears.

Changing a Table

SQLite provides some basic ways to change a table after you have created it. Specifically, you are allowed to rename the table or to add new columns to the table. SQLite does not yet provide a way to remove a column from a table. Renaming a table is not something you will likely do often, but adding new columns is something that is quite useful.

Keep in mind that if you want to add a NOT NULL column to a table, then you’ll also need to provide a non-NULL default value.

// Rename the table

ALTER TABLE bookmark RENAME TO bookmarks

// Add a column to track how often the URL is visted, defaulting to 0

ALTER TABLE bookmarks ADD Visits INTEGER NOT NULL DEFAULT 0

Removing a Table

In SQL-speak, the word DROP is used as a synonymn for remove. The DROP TABLE command is not one you should use often or lightly. Dropping a table throws all the data in the table away. You will not be able to get the data back. The syntax is quite simple:

DROP TABLE bookmarks

Note that dropping a table does not actually reduce the size of your database file. SQLite retains the space so that it can be used later by other tables as they grow. Consider this a teaser for a future column: the VACUUM command can be used to reclaim unused space.

In our next column we’ll look at the commands for adding and manipulating data in tables.

A Note About Data Types

SQLite works with datatypes quite differently than you might expect. Enough so that it is worth taking a little time to explain it. To be brief, the datatypes in SQLite are really more like guidelines. The SQLite documentation refers to them as “affinities”. Regardless of the type that you specify, a column can store any type of information. If you have a column with an INTEGER type and you try to assign it ‘REALbasic’, SQLite will simply store ‘REALbasic’ in the column.

As far as I know, SQLite is the only database that works this way. None of this is a huge problem, it’s just a bit odd. This does mean you need to be careful when attempting to do math on columns that you think might contain numbers.

The types available to you are TEXT, NUMERIC, INTEGER, and BLOB. For more information, visit the SQLite web site.