| Structured Query Language is the language you use to communicate with the database. Using SQL you can create and modify tables, add, edit and retrieve data, do searches and more. Although SQL can become quite complex, especially when handling large amounts of data, it can also be surprisingly easy to use when dealing with smaller databases. The main SQL commands you'll be using are CREATE, INSERT, and SELECT. Note By convention, SQL keywords like SELECT are written using all capital letters. This makes the SQL easy to spot in a lengthy query.
Creating a Table Tables are the containers for the data in your database. A database file can contain any number of different tables, and those tables can be related through different key fields, or pieces of data. This is called a relational database and is beyond the scope of this book. For our training log, a single table will suffice to hold all of the exercise data you'll ever enter. A table in a database is organized into a series of columns (fields), and rows (records). Each row in the table holds one single record, although that record can contain many individual pieces of data. The individual pieces of data are stored in the fields, or columns, of the record. A restaurant menu is a perfect example of a database table. Have a look at the following three menu items from a local Thai restaurant. Pad Thai | Rice noodles, peanuts, green onions and more make this a customer favorite. | $13.95 | Volcano Chicken | Spicy and flavorful Thai peppers give this dish a special flair. | $17.95 | Jasmine Rice | A hint of jasmine and dark soy sauce make this rice truly unique. Available with tofu, chicken, beef. | $13.95 |
The column names for this table might be: item, description, and price. Simple, right? If you've ever created tables for Web pages, database tables will be quite familiar. To create a table with SQL, you use the CREATE TABLE command, along with the name of the table to create and the column names. The following SQL statement could be used to create a table for the menu: CREATE TABLE menu (item, description, price)
One thing that should be mentioned is that in many database engines, the data type for each column also needs to be specified. Arca, because it is built on SQLite, is typeless. This makes it a little easier to work with, because you don't need to specify what kind of information will be held in each column. However, in some cases, such as when your storing numbers, you will want to specify the data type or the number will be stored as a string. With this information in mind, let's create the data table for the exercise database. 1. | Double-click the Main Script, in the scripts cast, to open it for editing. Add the following five lines of Lingo to the makeDB method. Replace the create table comment with the new code.
err = _global.myDB.executeSQL("CREATE TABLE data(m integer, d integer,¬ y integer, type, location, distance float, time, notes)") errCode = err.errorMsg if errCode <> 0 then showError(errCode) end if
Aside from the call to Arca's executeSQL method, all the remainder of the code does is alert you if any error occurs. You use the executeSQL method of the Xtra to, as you may have guessed, execute any SQL statements on the database. Here you use the standard CREATE TABLE command to create a table named data within the exercise database. Note that when creating columns to store numbers like month, day and year (m, d, y) and also distance, the data type has been specified. This prevents the numbers from being stored as strings within the table. The distance field has been specified as float so that fractional distances can be stored.
The empty table, after creation, would look something like this:
Table: data
m | d | y | type | location | distance | time | notes |
| 2. | Rewind and play the movie, then stop it.
All you did here was to execute the startMovie handler. It tried to open the database file, failed, and so created it, along with the table. If you look in your project_two folder, you should see that the exercise file is no longer 0K in size, but approximately 3 K. This is because of the addition of the table to the file. It's now time to actually add some data to the file. To add data to a table, you use SQL's INSERT INTO command.
| 3. | Save the movie before continuing.
|
Inserting Data Now that the database file and data table have been created, you can start taking data from the input dialog and storing it in the table. SQL's INSERT INTO command lets you insert data into the columns of a specified table. Recall the getURL handler that currently intercepts the Close message, sent from the Flash sprite. You'll need to add Lingo to the getURL handler so that it also intercepts the save message. When a save is sent you'll need to pull the information from the input dialog and feed it to the INSERT INTO command. 1. | Open the Main Script from the scripts cast, then add the if statement and associated code that will run when Save is pressed. Add the code right after the current if statement that checks for Close.
if data = "save" then m = _global.theMonth d = _global.theDay y = _global.theYear if sprite("dialog").biking.selected = true then saveType = "B" if sprite("dialog").running.selected = true then saveType = "R" if sprite("dialog").walking.selected = true then saveType = "W" saveLoc = sprite("dialog").location.text saveMiles = sprite("dialog").miles.text saveTime = sprite("dialog").time.text saveNotes = sprite("dialog").notes.text err = _global.myDB.executeSQL("INSERT INTO dataVALUES(?,?,?,?,?,?,?,?)", ¬ [m,d,y, saveType, saveLoc, saveMiles, saveTime, saveNotes]) errCode = err.errorMsg if errCode <> 0 then showError(errCode) else displayMonth() end if end if
Now when Save is pressed in the input dialog, the new code within the handler will be executed. First, three local variables (m, d,y) are used to store the date values from the globals. This is done to make the SQL line a bit shorter. Next, the saveType variable is set to either B, R, or W, depending on which of the check boxes is selected in the dialog. After the remainder of the data is extracted from the fields in the dialog, the executeSQL method of the Xtra is called.
INSERT INTO tableName VALUES(?,?,?,?,?,?,?,?), [list_of_values]
This form of the INSERT INTO is unique to Arca, and makes it much more straightforward to do the insert. Although you could actually use the standard SQL form, there's no reason you'd want to. The reason being is the use of quotes. First, note that the entire SQL command needs to be sent to the executeSQL method as a string. Second, any string values need to have single quotes surrounding them. As an example, look at the following:
"INSERT INTO data values('B', 'JM', 10, '59:15', 'Felt Great')"
Note the need for single quotes around string values. Now, being that the INSERT command needs to be dynamic, the values need to be replaced by the variable names. Can you imagine the mess?
"INSERT INTO data values('" & type & "','" & location & "','" & etc.
As you can see, this would make a line of code that is long, hard to read, and error prone. By allowing you to use question marks and a list of values, Arca makes the process much easier. Any question marks within the VALUES portion will be replaced with the values given in the list of values. Note the order of the data being stored. Recall when you created the table, the field order went: theDate, type, location, distance, time, notes. The order of data in the INSERT command needs to be the same.
After the INSERT is complete the returned error list is checked to make sure no error occurred. If none has, then the displayMonth method is called, which redraws the calendar. This is done so that the newly entered data appears as soon as Save is pressed. Don't worry; you'll add the code to display the data next. But before doing that, let's add some data to the database so that there's something to display.
| 2. | Rewind and play the movie, then click any valid day of the month to open the input dialog. Fill in the fields, and press the Save button.
Visually, nothing should happen, but the data is now saved in the data table inside the exercise database. The next step is to retrieve and display the data within the calendar view.
|
Retrieving Data Finally, you've reached the good part. In this section, you'll use the SELECT FROM command to retrieve data from the database. SELECT lets you select as much, or little, information FROM a table as needed. In addition, you can employ the keyword WHERE to do conditional selections. Let's take a look at a couple of examples before adding the Lingo to the training log. "SELECT * FROM data"
As you can see, you can even use what's known as a wildcard character. In this manner all of the data from the data table will be selected and returned. You can also select data from individual columns, as shown here. "SELECT distance FROM data"
This would select just the data from the distance column, within the data table. You might use this to display the mileage total for all data ever entered. If you wanted to calculate the mileage for just the year 2004 you could use the WHERE command like so: "SELECT distance FROM data WHERE y = 2004"
Or if you wanted the mileage from September 2004: "SELECT distance FROM data WHERE m = 9 AND y = 2004"
When using variables, you can also use the question mark, list format within Arca, as described earlier, and shown here being used to select the data on July 3, 2004: "SELECT * FROM data WHERE m = ? AND d = ? AND y = ? ", [m,d,y]
as opposed to the much more cluttered: "SELECT * FROM data WHERE m = " & m & "d = " & d & "y = " & y
In Arca, the data from the SELECT will be returned in a property list, along with a host of other information, such as the error message, column names, and more. Within the property list the data is located in the #rows property and can be accessed using dot notation, in the same manner as retrieving the error code. 1. | Open the Main Script from the scripts cast, and scroll to the repeat loop within the displayMonth method. Add the following three lines of Lingo, immediately before the repeat loop.
exerciseChannel = _global.exerciseSprites + _global.firstDayOfWeek m = _global.theMonth y = _global.theYear
Because you'll be displaying the user-entered data, you'll need to know where the exercise indicator sprites begin. Again, a couple of local variables are used to store the month and year values from the globals, in order to save room within the SQL statement that follows.
Next, you'll use SQL's SELECT command to query the database for every day of the month. As the repeat loop iterates through the days of the month, you will try to select data for each day. If data is present you will display it using the data and exercise sprites to do so.
| 2. | Add the following chunk of Lingo inside of the repeat loop. Replace the comment line that says --SQL SELECT goes here with the new code.
err = _global.myDB.executeSQL("SELECT * FROM data WHERE m = ? AND ¬ d = ? and y = ?",[m, thisDay, y]) errCode = err.errorMsg if errCode = 0 then theData = err.rows if theData.count <> 0 then theData = theData[1] theType = theData[4] theLoc = theData[5] theDist = theData[6] theTime = theData[7] theNotes = theData[8] sprite(exerciseChannel).member.text = theType sprite(dataChannel).member.text = theLoc & return & ¬ theDist & " " & theTime & return & theNotes end if end if exerciseChannel = exerciseChannel + 1
What happens is that as the repeat loop progresses, and iterates through each day of the month, a SELECT is performed to retrieve all the data on that particular day. The remainder of the code is there to place the returned data, if any, into the text sprites on the Stage. Let's take a look at how Arca returns data when using the SELECT command. Examine this output from the Message window:
SQL = "SELECT * FROM data WHERE m = 7 AND d = 8 AND y = 2004" err = _global.myDB.executeSQL(SQL) trace(err) -- [#rowschanged: 0, #columns: ["m", "d", "y", "type", "location", ¬ "distance", "time", "notes"], #columntype: ["integer", "integer", ¬ "integer", <Void>, <Void>, "float", <Void>, <Void>], #rows: [[7, 8, ¬ 2004, "B", "Emma Carlin", 13, "1:15:22", "Trails in great shape"]], ¬ #errorMsg: 0]
As you can see, a good deal of information is returned in a property list, including the column names, their types, the actual data, and any error message. To get at the data, you access the #row property as shown here:
trace(err.rows) -- [[7, 8, 2004, "B", "Emma Carlin", 13, "1:15:22", "Trails in great shape"]]
Be sure to note the double square brackets surrounding the data. As you know, the brackets indicate a list, and this notation indicates a list within a list. Because doing a SELECT could result in many rows of data, each row is returned in its own list, within the main one. Because you're selecting data for an individual day, just one row of data should ever be returned. With this in mind, let's return to examining the new Lingo you just added.
After the SELECT is performed, the usual check is done to make sure there was no error. If no error is present, then the local variable theData is set equal to the # rows property of the list. Next, another check is performed to see if any data was actually returned:
if theData.count <> 0 then
This check uses the count property of lists, which simply returns the number of items in a list. If no data was returned, the count of the list will return zero. So if the count is not zero, there is data present on that day, and it should be displayed.
Because each row is returned in its own list, the next line of code gets the actual data into its own single list:
theData = theData[1]
If this doesn't quite make sense, examine the following:
testList = [[1, 2, 3]] trace(testList) -- [[1, 2, 3]] testList = testList[1] trace(testList) -- [ 1, 2, 3] trace(testList[1]) -- 1
Once you have the actual row of data contained in the theData variable, you can extract the individual items, by using the index of each item in the list.
theType = theData[4] theLoc = theData[5] theDist = theData[6] theTime = theData[7] theNotes = theData[8]
Note that the reason you start at 4 and not 1, is because the m, d, and y fields in the database occupy positions 1, 2, and 3 in the returned list.
Once the data has been pulled from the list, the exercise sprite gets set to theType variable, resulting in a B, R, or W appearing in the calendar.
The next line places the remainder of the data into the data sprite:
sprite(dataChannel).member.text = theLoc & return & theDist & " " & ¬ theTime & return & theNotes
By concatenating the variables with the keyword return you are able to force line breaks to appear in the text.
Once the data has been displayed for the day, the exerciseChannel variable is incremented in order to remain synced to the repeat loop, just as is done with the dataChannel and dayChannel variables.
| 3. | Save the movie, then rewind and play it.
The data you previously entered will now appear in the calendar view, as shown here:
The calendar is really shaping up now, and is mostly working. However, when you click a day of the month containing data, that data needs to appear in the input dialog, ready for editing. It also needs to appear in the dialog for the notes text, because not all of the notes text will fit in the calendar view.
|
Updating Data To fill in the fields of the input dialog when a day with data is clicked, you'll need a SELECT statement and accompanying code that are nearly identical to what you used in the previous section. You need to retrieve the information for a given day, and then insert it into the fields within the Flash sprite. If the data is changed, and the Save button pressed, the changes need to be placed back into the database. Currently, when Save is pressed, the INSERT INTO command is used to insert the data from the dialog into the data table. However, the INSERT command will create a new row of data in the table, which isn't what you want if data is already present for the day. While there is an SQL command called UPDATE, there's a simpler way to do it. First, add the code that will fill in the input dialog when a day with data present is clicked on. 1. | Stop the movie if it's still playing, and then select the set date script within the scripts cast.Open the script for editing by clicking the Cast Member Script button in the cast panel.
The Set Date script is attached to all of the data sprites in the calendar and sets the title field within the input dialog, when a data sprite is clicked. In addition, you want to perform a SELECT and use the returned data to fill in the fields within the dialog.
| 2. | Replace the comment line, --SQL SELECT goes here, within the mouseUp handler, with the following Lingo.
err = _global.myDB.executeSQL("SELECT * FROM data WHERE m = ? AND d = ?¬ AND y = ?", [_global.theMonth, myDate, _global.theYear]) if err.errorMsg = 0 then theData = err.rows if theData.count <> 0 then theData = theData[1] sprite("dialog").biking.selected = false sprite("dialog").running.selected = false sprite("dialog").walking.selected = false theType = theData[4] if theType = "B" then sprite("dialog").biking.selected = true if theType = "R" then sprite("dialog").running.selected = true if theType = "W" then sprite("dialog").walking.selected = true sprite("dialog").location.text = theData[5] sprite("dialog").miles.text = theData[6] sprite("dialog").time.text = theData[7] sprite("dialog").notes.text = theData[8] end if else showError(err.errorMsg) end if
First, an SQL SELECT is done to select the data on the day of the month that was clicked on. If no error occurred, and there was data returned then the Flash sprite is updated with the data. As you did before, the local variable theData is set to the first item in itself, because it is a list of lists.
Next, the selected property of the three check boxes is set to false, effectively turning them off. This prevents there being multiple checks turned on, as the next three lines of code set the selected property to true, depending on the type of exercise stored in the database.
After that is done, the next four lines set the location, mileage, time, and notes field from the returned data.
With this code in place, the input dialog will now be filled in when a day of the month containing exercise data is clicked on.
The final thing you need to do before this lesson is complete is update the data in the database, when a day already containing data is edited.
| 3. | Open the Main Script in the scripts cast and add the following Lingo to the current getURL method. Add the new code directly after the three lines that set up the m, d, and y variables.
err = _global.myDB.executeSQL("DELETE FROM data WHERE m = ? AND d = ? ¬ AND y = ?", [m, d, y]) errCode = err.errorMsg if errCode <> 0 then showError(errCode) end if
This code needs to go after the lines that set m, d, and y because the DELETE command uses them to delete the data for the particular day. Can you see how this works?
When the Save button is pressed any data in the table for the given day is first erased. Then, exactly as it was, data is pulled from the input dialog and inserted into the table using the INSERT INTO command.
| 4. | Rewind and play the movie. Click the day you entered data into and edit it. Press the Save button to update the database. When you're through stop the movie and save it.
You're now ready to add a couple of finishing touches and hook up the two total fields in the bottom right corner of the interface. The one on top will be used to display the total mileage for the month. The bottom one will display the running total for the current year.
|
Finishing Touches Before moving on to the next section, the two mileage total fields should be hooked up. For this you'll use what are known in SQL as aggregate functions. Aggregate functions such as sum and avg are computed across all rows of the selected data. For example, to get the mileage total for the month of July 2004 you could use the following SELECT command: "SELECT SUM(distance) FROM data WHERE m = 7 AND y = 2004"
This provides a property list, with a #rows property that contains the total for the column: -- [#rowschanged: 0, #columns: ["sum(distance)"], #columntype: ¬ ["NUMERIC"], #rows: [[480]], #errorMsg: 0]
Knowing this it should be simple for you to add the necessary code to the displayMonth method. 1. | Open the Main Script and add the following Lingo to the displayMonth method. Add the new code at the end of the method, immediately after the end repeat line.
err = _global.myDB.executeSQL("SELECT sum(distance) FROM data WHERE m = ¬ ? AND y = ?", [m, y]) errCode = err.errorMsg if errCode = 0 then theData = err.rows theData = theData[1] member("tot_mon").text = string(theData[1]) else showError(errCode) end if
Once the SELECT command retrieves the sum of the distance column for the specified month and year, an error check is done to make sure no error occurred. If none did, the returned sum is extracted from the #rows property and placed into the tot_mon text member. Note that you didn't need to check the count of the rows list because even if no data was found, the sum function will still return a 0.
| 2. | Rewind and play the movie and notice that the mileage total for the month is now displayed. If you change the month, the total will update accordingly.
Because the code for displaying the mileage total for the entire year is nearly identical to displaying it for the month, I will leave it to you to complete that.
| 3. | Be sure and add some random data to the current month so that you have something to work with in the remaining lessons.
Having some data in the database will be necessary if you want to see the graph you'll be building do anything. All you need to enter is data for four or five days.
| 4. | Stop and save the movie before continuing.
Another thing you may want to consider adding is a way to delete data for a given day. To do this, you'd just need to add a button to the input dialog, and send a delete message to be intercepted by the getURL handler. An SQL DELETE FROM command just like what's used when Save is pressed, would be all you would need.
This lesson is now complete, and you can move on to using imaging Lingo to create a dynamic graph that will show distance and average speed information.
|
|
