Create a new text file in the project directory called history.lua and open it. Start by loading the needed library:

local sqlite3 = require "sqlite3"

This loads Corona support for working with databases and establishes the local name sqlite3 as your interface for using it. Now it's time to open and prepare the database as needed:

local sqlite = require "sqlite3"

local storagePath = system.pathForFile('BatSwat.history', system.DocumentsDirectory)

local db = sqlite3.open(storagePath)

Because the Resource directory is read-only on mobile platforms, we store the score database in the Documents directory. The sqlite3.open call attempts to load the given file location as a new database, and creates a blank database file if no file was found:

local db = sqlite3.open(storagePath)
Runtime:addEventListener('system',
  function(event)
        if( event.type == "applicationExit" ) then              
            db:close()
        end		
  end
)

We'll get more into dealing with closed and paused applications in the next chapter, but this just makes sure that the database will be flushed and closed properly if the application is closing.

We'll need a list in the database where we can store score values:

  end
)

db:exec[[
  CREATE TABLE IF NOT EXISTS history (
    happened PRIMARY KEY, 
    Score, 
    Count,
    Initials
  );
]]

This is our first taste of SQL, where we have the database run with the :exec method. It just makes sure that the database has a table to hold our high scores, including when the score was achieved, how much it was, how many creatures there were on the whole level, and who got the score in question. The time the score that was achieved is used as the table's primary key, meaning there can be only one score for any given moment of completion. As the game is single-player, this is fine. If the database already existed, this line will do nothing.

We don't need to keep more scores than we can display.

  );
]]

db:exec[[
  DELETE FROM history WHERE happened NOT IN (SELECT TOP 10 happened FROM history ORDER BY Score DESC)
]]

This is a maintenance line. We don't want the list of high scores to just keep getting bigger and bigger and taking up more of the user's device memory, so whenever we launch the game, we clean out any scores that have fallen off the bottom of the list. The SQL statement here basically says, "make a list of all the times that have scores in the top 10, and then delete every score whose time isn't on that list."

]]

local history = {}

return history

Here, we're just preparing the history module that will be returned when the file is loaded. The three functions that are the substance of the module will be added between these two new lines, since the return must go last.

To see if a new score qualifies as a high score, we'll see where it falls among the scores already gathered:

local history = {}

function history:find(score)
  for count in db:urows([[ SELECT count( ) FROM history WHERE Score >= ]] .. score) do
    return count + 1
  end
end

This function identifies where a proposed score would fit into the list. We'll use it to identify which new scores have a place in the top 10 and should ask for the player's initials. It works by counting the number of existing score entries that are larger than the score under consideration.

The sqlite3 library offers three different functions for scanning through the results of a SELECT query. The db:nrows method returns a table representing the row, with named fields matching the column names that hold their values for that record. The db:rows function gives back a table which simply holds the value of the first column at index 1, the value of the second column at index 2, and so forth. The db:urows function, used here, simply uses Lua's multiple returns to pass back all the values from the record without making a new table, in the same order they would appear in the table returned by db:rows.

When a new score is identified as being better than a previous high score, we need to record it:

end

function history:add(statistics)
  if statistics.HighScore <= 0 then return end

This function will submit a new score to the database. If for some reason a score of 0 was submitted, we won't bother storing it:

  if statistics.HighScore <= 0 then return end
  db:exec(
    string.format([[
      INSERT INTO history VALUES (
        datetime('now', 'localtime'),
        %d, %d, %q
      );  ]],
      statistics.HighScore,statistics.MobCount, statistics.Initials
    )
  )

Here, we use the string.format function (a close relative and derivative of the C printf) to fill in the specific information provided to us about the score into an otherwise preprogrammed SQL INSERT command. Executing the finished command adds the new row into the database.

To display the scores, we'll need to retrieve them from the database:

  )
end


function history:TopScore(index)

The last function we add will retrieve the score with a given index; 1 for the highest score, 2 for the second highest, and so on:

function history:TopScore(index)
  local query = [[SELECT * FROM history ORDER BY Score DESC LIMIT 1 OFFSET ]] .. (index - 1)

Here we prepare the query. The LIMIT 1 clause means we only want one value from the list, and the OFFSET clause indicates how far down the sorted list we want to find that value, basically like an index into an array:

  local query = [[SELECT * FROM history ORDER BY Score DESC LIMIT 1 OFFSET ]] .. (index - 1)
  for info in db:nrows(query) do

To make it easier for the code calling this function to use the result, we use the db:nrows iterator to get back a table structured like a record, with named fields for the column values.

  for info in db:nrows(query) do
    return info
  end

Like most database functions, the luasqlite3 iterators aren't really intended to be used with single values. We could save the obtained record in a variable local to the function and trust the loop to exit after one pass (since the query statement specified should never return more than one record), but just returning out of the loop on the first pass also works fine, since we have nothing else to do after finding the first record.

Since the function will always return from the first pass through the loop, there's no need for any other body, and we're done with the module:

    return info
  end
end

return history

Now that the score tracker is ready, we need to prepare the other modules to use it. First, we make a small change to the game scene file, to make it pass its final score back to the menu scene for consideration. The storyboard library has added the ability to hand parameters off to a scene when you load it which is perfect for this purpose:

function scene:Despawn(event)
  if not tonumber(self.Count) or self.Count <= 1 then
    self:dispatchEvent{name = 'Game'; action = 'stop'}
    storyboard.gotoScene("menu", {params = {Score = self.ScoreTotal, Count = self.StartingCount}})
  else
    self.Count = self.Count - 1
  end
end

This way, the menu's enterScene function will be able to access the score and count as fields of the event.params table. This is the only change we need to make to the game.lua scene file. Next, open the menu.lua file to add support for receiving this data. Start by loading the history module at the top of the file so that we will be able to check whether the received score is a new record:

local history = require "history"

local storyboard = require( "storyboard" )

Now, we need to add support for using that module to check for a new high score and record the initials that qualify. If we have to pop up a collection window, we want to hold off on running any animations until we've returned from that process, so replace the unconditional call to the scene:Cycle()function, which handles showing the high scores and credits, with a conditional statement:

function scene:enterScene( event )
  self.Banner.alpha = 0

  if event.params and event.params.Score then
    if history:find(event.params.Score) <= 10 then
      storyboard.showOverlay("enterInitials", {effect = "fromBottom", params = {Score = event.params.Score, Count = event.params.Count}, isModal = true})
    end
  else
    self:Cycle()
  end

end

First, we check whether we've received a score at all. Remember that this scene is also launched when the app starts up, in which case there will be no new score to forward.

Next, it uses the history module to ask whether the newly received score belongs in the top 10. If not, it won't be showing the initials entry screen and can go directly to running animations.

If this is a new high score, however, we need to display the enterInitials pop-up scene to collect user input. We use the storyboard library's showOverlay function to display the new scene over the current one, since we will be coming straight back to the splash screen when we are done. We pass scene and count to this function, just as we received them, so that the data entry screen can record them in the database. The isModal argument field prevents touches in the pop-up scene from drifting down into the menu screen while it is active.

Finally, we register the menu scene to notice when the score is recorded and the overlay is closed, so that it can start its animations. First, we specify that the Cycle function (which runs those animations) should be the scene's response to any overlays ending; then we make sure the scene knows that it is interested in its own overlayEnded events:

end

scene.overlayEnded = scene.Cycle
scene:addEventListener( "overlayEnded", scene)

function scene:exitScene( event )

Now that score processing is ready, we're going to add code to actually display the high scores. For the moment, we'll just lay them out in the designated space as soon as animations are visible. So, we'll add that call to the menu's scene:Cycle function:

function scene:Cycle()
  self.StopPulsing = visuals.PulseInOut(self.Banner)
  self.StopEffects = revealScores(self)
end

Because we're expecting this to be animated later, we're leaving open the option to have a transition that we might need to stop or change. Right now, we'll focus on just making the scores show up in the new revealScores function:

local function revealScores(scene)
  display.remove(scene.ScoresSlide)
  scene.ScoresSlide = display.newGroup()
  scene.ScoresWindow:insert(scene.ScoresSlide)
end

function scene:Cycle()

This adds a new group to store all our high score displays in, making it easy to animate or clear all of them at once. Before that, however, we remove any previous high-score displays to make room, since the high scores may have changed since they were last displayed:

  scene.ScoresWindow:insert(scene.ScoresSlide)
  for i = 1, 10 do
    local score = history:TopScore(i)

Next, we loop through the 10 highest scores in the history of the game. The score variable will actually be a table containing all the relevant fields:

    local score = history:TopScore(i)
    if not (score and score.Initials and score.Score) then break; end

If the game is new, the high score table might be mostly empty, so if we run out of scores, we finish the loop early:

    if not (score and score.Initials and score.Score) then break; end
    display.newText(scene.ScoresSlide, score.Initials, 0, 24 * (i - 1), native.systemFont, 16)
    local score = display.newText(scene.ScoresSlide, score.Score, 0, 24 * (i - 1), native.systemFont, 16)

We create the two text objects to hold the initials and the actual score. Creating two objects means that they can be aligned separately:

    local score = display.newText(scene.ScoresSlide, score.Score, 0, 24 * (i - 1), native.systemFont, 16)
    score:setReferencePoint(display.CenterRightReferencePoint)
    score.x = 100
  end
end

Finally, we align the score number on the right-hand side of the available space. The score reveal is now basically complete!