Office of Digital Humanities
So far we've seen how to do basic database access from a LiveCode stack. Now it's time to build a useful application that integrates with your online database. We are going to build what's known as a "front end" to your database—a graphical user interface that makes it easy for an end user to do useful things with the data stored in your database.
What sort of things might we want our database management stack to do? Here is a basic list; you can probably think of lots of others.
With a few basic features defined, let's get started.
This simple interface should be enough to allow us to build the display and editing tools mentioned in our list above.
Notice that we are using a type of control that you may be unfamiliar with; namely, a "data grid". The data grid is a relatively new addition to the LiveCode tool set. It is fundamentally different from other LiveCode controls in that it is a hybrid built of several other types of controls that are grouped into a non-editable group. Data grids are highly customizable and potentially quite complex, but using them to display tabular data, such as our database records, is very straightforward.
To create a data grid, just drag from the data grid icon on the tools palette onto your card, and resize to the desired size.
Open its property inspector and name it just as you would any object. The basic properties for data grid in the example layout above are set as shown here.
Data grids have another set of properties for setting up columns and column properties. Note that each column you create can have both a column name and a column label. In addition, each column width can be set to the precise width you want it to be. Columns may also be visible or hidden, a feature we'll use later on.
As LiveCode objects, data grids have properties just like fields and buttons do. Due to the hybrid nature of data grids, however, you have to refer to certain properties using the prefix "dg". The two properties we'll be using are the dgText and the dgHilitedLine. They are analogous to the text and the hilitedLine properties of fields.
Much more could be said about data grids, but this is enough for our needs. If you want to explore data grids more in depth, see the excellent on-line documentation for data grids.
With the basic interface built, we are ready to start scripting. We'll start by scripting button "showAll" to display all of the records in the database table that we want to manage in our stack.
The first step is to script creating the connection to the database. And you may recall, the revOpenDatabase() function requires several arguments to connect successfully to the database server. Rather than typing them directly into the script, it's convenient to have them stored somewhere in the stack where they are easily accessed. Custom properties of the stack are well suited for things like this.
Make your job easier with "wrapper" commands. Even with the database connection information stored neatly in custom properties, it's tedious and repetitious to type out the revOpenDatabase() function in full every time we need to make a connection. A good practice is to create a "wrapper" handler that does the work for you in the stack script (or even in a library stack.) The wrapper handler might look something like this:
# in the stack script
global gConnID
on connectToDB
put the dbhost of me & ":" & the dbport of me into tHost
put revOpenDatabase(the dbtype of me,tHost,the dbname of me,the dbUser of me, the dbPasswd of me) into tConxn
# it's a good idea, but not required, to check for a successful connection here
if tConxn is a number then
put tConxn into gConnID
return empty
else
put "Unable to connect to database '" & the dbName of me & "': " & tConxn into tErrMsg
answer error tErrMsg as sheet
put empty into gConnID
return tErrMsg
exit to top
end if
end connectToDB
Notice that we're saving the successful connection ID in a global variable, gConnID, so that it can also be accessed anywhere in the stack. Now, every time we need to make a connection to our database, we can just enter the command connectToDB in our handler. While we're at it, we can also simplify the process of disconnecting from the database with a wrapper handler in the stack script:
on closeDB pConnID
revCloseDatabase pConnID
# this ensures that you'll never have a left-over connection number in the global var
if pConnID = gConnID then
put empty into gConnID
end if
end closeDB
Terminating a database connection can now be done with a simple call in our handler, closeDB gConnID. Remember to declare the global variable gConnID. Our open-close database framework in any handler is pretty simple now:
global gConnID
on mouseUp
connectToDB
# Do DB access stuff here
closeDB gConnID
end mouseUp
With our wrapper commands in place for easy opening and closing of database connections, we're ready to get to work on handlers for displaying and editing records in our tables. The first thing we may want to do is simply show all the records in a table. Let's say we're working with a table called 'people'. As mentioned before, the revDataFromQuery() function is an easy way to grab data from a database. Remember the SQL query to display all fields for all records?
SELECT * FROM people
Just send this query to the database like this:
put "SELECT * FROM people" into tQuery put revDataFromQuery(,,gConnID,tQuery) into tRecords
It's always a good idea when working with database queries to make sure our query succeeded before proceeding. Many of the LiveCode database commands and functions, when they fail, return an error message that begins with the string revdberr. We can use that to do a quick error check before displaying the results of the query in the datagrid table:
if tRecords begins with "revdberr" then
answer error "There was a problem accessing the people database: " & tRecords
closeDB gConnID
exit to top
end if
Now all that's left is to display the records in the data grid, which we can do by setting the dgText property of the data grid. The mouseUp handler in button "showAll" should now look something like this:
global gConnID
on mouseUp
connectToDB
# get all of the records in your database
put "SELECT * FROM people" into tQuery
put revDataFromQuery(,,gConnID,tQuery) into tRecords
# this part's not required, but a good idea to check for errors
if tRecords begins with "revdberr" then
answer error "There was a problem accessing the people database: " & tRecords
closeDB gConnID
exit to top
end if
# end error check
closeDB gConnID
set the dgText of group "employeeList" to tRecords
end mouseUp
The first piece of our database editing stack should be working now. A click on the Show All Records button should give this result:
This is almost perfect, but we need to fix one thing. One of the requirements was to make records easier to read, and we still only have id numbers listed for the departments. Instead, we want to display the name of the department. There are two ways to solve this—the LiveCode approach and the SQL approach. We'll look at both of them. First, however, let's modify the data grid and add a column, so there will be a column for the department ID number and a column for the department name:
The LiveCode approach requires more LiveCode scripting but simpler SQL statements. Essentially, it involves:
We already have all the records for the people table, displayed in the data grid. Let's write a handler that will get all the records in the department table. We'll put the handler in the card script (since we might want to access it from more than just the "showAll" button.) You'll notice that it is almost identical to the handler that gets the people records:
# in the card script
global gConnID
on getDeptList
connectToDB
put "SELECT * FROM department" into tQuery
put revDataFromQuery(,,gConnID,tQuery) into tDeptList
# check for errors
if tDeptList begins with "revdberr" then
answer error "There was a problem accessing the people database: " & tDeptList
closeDB gConnID
exit to top
end if
# end error check
closeDB gConnID
# store dept records in a custom property:
set the deptData of me to tDeptList
end getDeptList
We will also need a function that looks up the department numbers in this list so they can be added to the display in the data grid. We'll add that function to the card script
# added to card script
function deptName pDeptId
set the itemDelimiter to tab
put the deptData of me into tDeptData
put lineOffset(pDeptId,tDeptData) into tLine
if tLine<> 0 then
return item 2 of line tLine of tDeptData
else
return empty
end if
end deptName
Finally, let's modify the script for button "showAll" to use these new handlers to add the department name to the people records. Notice that we're just calling the new handlers we added to the card script:
global gConnID
on mouseUp
connectToDB
# get all of the records in your database
put "SELECT * FROM people" into tQuery
put revDataFromQuery(,,gConnID,tQuery) into tRecords
# this part's not required, but a good idea to check for errors
if tRecords begins with "revdberr" then
answer error "There was a problem accessing the people database: " & tRecords
closeDB gConnID
exit to top
end if
# end error check
closeDB gConnID
# add department names
getDeptList
set the itemDelimiter to tab
repeat with i = 1 to the number of lines in tRecords
put tab & deptName(item -1 of line i of tRecords) after line i of tRecords
end repeat
set the dgText of group "employeeList" to tRecords
end mouseUp
Now the datagrid shows all of the people records, but with the proper department names added:
You might think that the LiveCode approach is a lot of trouble for a simple thing. You would be right, but it is a viable option for someone comfortable with LiveCode scripting but a novice at SQL.
The SQL approach requires simpler LiveCode scripting, but more complicated SQL statements. In a nutshell, we use a SQL technique called a join, which allows us to join data from separate tables in a single query. SQL joins are powerful and potentially complex, so what we use here only scratches the surface. Here is the SQL statement that will get us the joined data we want in one query:
SELECT people.*,department.name FROM people,department WHERE people.dept = department.dept_id
In this kind of SQL join, we list two tables in the FROM clause, instead of just one. Because we're referring to more than one table, we need to qualify each column name by preceeding it with the table name followed by a dot, in this format: tablename.columnname. So people.* means "all the columns in the people table", and department.name means "the name column in the department table". The WHERE clause is basically saying "only include the records where the 'dept' column in table 'people' corresponds to a record in table 'department' with an equivalent value in the 'dept_id' column."
To use this new join query, just use it in place of the simple query we used earlier in the "showAll" button:
global gConnID
on mouseUp
connectToDB
# get all of the records in your database
put "SELECT people.*,department.name FROM people,department " & \
"WHERE people.dept = department.dept_id" into tQuery
put revDataFromQuery(,,gConnID,tQuery) into tRecords
# this part's not required, but a good idea to check for errors
if tRecords begins with "revdberr" then
answer error "There was a problem accessing the people database: " & tRecords
closeDB gConnID
exit to top
end if
# end error check
closeDB gConnID
set the dgText of group "employeeList" to tRecords
end mouseUp
By using this new SQL statement we get the people record list shown above, including the department names, without the need for extra LiveCode scripting!
There is a minor cosmetic issue to fix. Even though the id numbers for the people records and the department records are important, they are meaningless to most users. Fortunately, data grid objects allow us to hide columns, while still keeping the underlying data intact. To do so, simply open the property inspector for the datagrid, choose the Columns panel, and uncheck the Visible checkbox for the columns you want to hide. Then adjust the width of the data grid and we end up with a much cleaner display:
Remember, the data in the hidden columns is still there; it is just not showing. If we look at the dgText of group "employeeList", we can see it is still there, with tab-separated items on each line:
1 Milton Chilton 801-123-2345 Room 101 1 Accounting 5 Mike Nelson 801-345-6780 Room 40 1 Accounting 8 Jerry Ball 322-5684 Room 220 1 Accounting 11 Fred Farkel 801-345-5678 Room 111 1 Accounting 4 Judy Johnson 801-656-8765 Room 130 2 Sales 3 Jane Doe 801-567-7890 Room 160 2 Sales 6 Bob Bower 345-0908 Room 203 3 Marketing (etc.)
Knowing that the id numbers are still available to us as part of the data will become very important to us as we build tools to edit the database.
Continue to Part 2: Editing your Database Records