To create a database and tables from within REALbasic you may as well forget about the rubbish and buggy SQLite ‘browser’ that is built into REALbasic and instead use code. If writing a commercial application you need to use code anyway as presumably you want the database to be created on the users machine either as part of a startup method or following a specific ‘Create Database’ instruction from the user.

Lets assume you are creating a Contacts Manager program. Your user can create a new Contacts database by selecting ‘New Database’ and they are presented with a window similar to the one shown below:




The code behind the Select button looks like this:

Dim f as FolderItem
f=SelectFolder
If f<> Nil Then
edtDBPath.Text = f.URLPath
End If
edtDBPath.Text = edtDBPath.Text + "ContactsDB"
pbProceed.Enabled = True
pbProceed.Default = True

The code behind the Proceed button, which is now enabled (pbProceed.Enabled = True) , looks like this:

dim booResult as Boolean = mSQLite.fCreateDBIfNotExist(edtDBPath.Text)
if booResult = True then
dim booInsert as Boolean = mSQLite.fCreateDefaultRecords
if booInsert = True then
dim booConnect as Boolean = mSQLite.fConnectDB
wMain.SETpDirtyWindow(True)
end if
end if
Self.Close

A couple of explanations are in order here I think. mSQLite is a Module which contains all the code that interacts directly with the database. wMain.SETpDirtyWindow is a method on my main application window, wMain, this method sets the Property pDirtyWindow on wMain to True. In the Activate event for the window wMain the Property is checked and if True the window reloads its data. This is necessary because you have just created or selected a new database.

So the code above attempts to create the selected database if it does not exist. If this is successful (booResult = True) it then attempts to populate this database with default data. If this is successfull (booInsert = True) then a connection is made to the database.

Next we’ll look at the code for the three functions in the mSQLite module that do the actual work.

mSQLite.fCreateDBIfNotExist
This method is passed the pathname to the database you have just created / selected (edtDBPath.Text). The code looks like this:

Protected Function fCreateDBIfNotExists(strFileName as String) As Boolean
Dim f as FolderItem
f = GetFolderItem(strFileName,2)
if f.exists Then
Return True
Else
'Create Database
dim folTarget as FolderItem
dim booResult as Boolean

db = New REALSQLDatabase
folTarget = GetFolderItem(strFileName,2)

db.DatabaseFile = folTarget
booResult = db.CreateDatabaseFile

'Contacts Table
dim strCreateContactsTable as String = "CREATE TABLE contacts (con_pk INTEGER PRIMARY KEY,"
strCreateContactsTable = strCreateContactsTable + "con_company_name VARCHAR(50),"
strCreateContactsTable = strCreateContactsTable + "con_title VARCHAR(20),"
strCreateContactsTable = strCreateContactsTable + "con_first_name VARCHAR(50),"
strCreateContactsTable = strCreateContactsTable + "con_last_name VARCHAR(50),"
strCreateContactsTable = strCreateContactsTable + "con_phone_number VARCHAR(20),"
strCreateContactsTable = strCreateContactsTable + "con_email VARCHAR(20),"
strCreateContactsTable = strCreateContactsTable + "con_free_text VARCHAR(1000),"
strCreateContactsTable = strCreateContactsTable + "con_addr1 VARCHAR(50),"
strCreateContactsTable = strCreateContactsTable + "con_addr2 VARCHAR(50),"
strCreateContactsTable = strCreateContactsTable + "con_town VARCHAR(50),"
strCreateContactsTable = strCreateContactsTable + "con_county VARCHAR(50),"
strCreateContactsTable = strCreateContactsTable + "con_postcode VARCHAR(50),"
strCreateContactsTable = strCreateContactsTable + "con_country VARCHAR(50))"
dim strCreateContactsIndex1 as String = "CREATE INDEX con_pk ON contacts (con_pk)"
dim strCreateContactsIndex2 as String = "CREATE INDEX con_company_name ON contacts (con_company_name)"
dim strCreateContactsIndex3 as String = "CREATE INDEX con_last_name ON contacts (con_last_name)"

'Country Table
dim strCreateCountryTable as String = "CREATE TABLE countries (cry_pk INTEGER PRIMARY KEY,"
strCreateCountryTable = strCreateCountryTable + "cry_code VARCHAR(20) NOT NULL,"
strCreateCountryTable = strCreateCountryTable + "cry_name VARCHAR(50) NOT NULL)"
dim strCreateCountryIndex1 as String = "CREATE INDEX cry_pk ON countries (cry_pk)"
dim strCreateCountryIndex2 as String = "CREATE INDEX cry_code ON countries (cry_code)"

if booResult = True then
'Create Schemas
db.SQLExecute(strCreateContactsTable)
db.SQLExecute(strCreateContactsIndex1)
db.SQLExecute(strCreateContactsIndex2)
db.SQLExecute(strCreateContactsIndex3)
db.SQLExecute(strCreateCountryTable)
db.SQLExecute(strCreateCountryIndex1)
db.SQLExecute(strCreateCountryIndex2)

db.Commit
db.Close
Return True
else
‘ Error Message Shown Here
Return False
end if
End if
End Function

The code above attempts to open the database file at the location you selected via your SQLite window earlier. If the file exists already then True is returned and nothing further is executed within this method. If however the file does not exist a new REALSQLDatabase is created.

The SQL code necessary to create the required tables is created by building up a string for each table and index we require. Here we are creating a Contacts Table and a Country table (to use for showing the Countries in a PopupMenu when the address is being entered). In this tutorial I’m not going to attempt to teach SQL, there are many sources for SQL introductory texts, Google is your friend

Once we have created the SQL strings necessary to create our tables and views we peform an Execute against ‘db’ (our new database) passing the SQL strings as a parameter.

The db.Commit is not actually required as the above will automatically commit as we are making changes to the database structure. All being well we return the value True and the next mSQLite function is called.

This post has gone on too long so I’ll cover creating the default database records and connecting to the database in the next post.

Any feedback, good, bad, indifferent and also any suggestions for what you would like to see covered - leave a comment or drop me an email to stevecholerton@mac.com

RSS Feed: http://feeds2.feedburner.com/Lonelyhacker

Tags: REALbasic, Tutorial