Enhancement Three: Databases

In this category, I planned to:

(1) Incorporate a database to store statistics about the game

(2) create a scoring system based on gameplay

(3) potentially customize the naming of the high scores

The database component required a working knowledge of SQL to implement, as well as understanding of database structures in order to create one that suited the needs of this program. Both of these were demonstrated as I had to plan out what data was to be stored, along with the CRUD methods that would be used to create, retrieve, update, and delete that data.

The database was successfully implemented, and now players not only have a score at the end, but they can see their statistics compared to previous games, with the best of their scores being saved until they are surpassed. The previous randomization combined with the new scoring system gives players a reason to replay, to improve upon past performance and see the results of their efforts reflected in the scoreboard.

Runtime initializes database if not found, reads and writes if already existing.

The 3rd course outcome was addressed because I used algorithmic principles to design a solution (database) while managing trade-offs between different approaches and data structures. The 4th outcome was also fulfilled because I had to use innovative techniques to implement computer solutions that deliver value. The 5th outcome was addressed by having a security mindset, where I mitigated design flaws (fixing a game breaking-bug with adversarial potential) and ensured that the database was only open when in active use and closed to potential tampering outside of that intended use. 

The process was similar to my previous “sprint” where I identified what needed to be done and outlined what I would like to accomplish within the timeframe I had. I added notes onto the original outline as I worked to keep my thoughts together.

I decided on using SQLite JDBC library for Java: https://github.com/xerial/sqlite-jdbc

I read the documentation for how to use connections and statements and adapted the SQLite queries to the needs of my program. Some useful examples were found on https://www.tutorialspoint.com/sqlite/sqlite_java.htm

This video covered the installation process: https://www.youtube.com/watch?v=0beocykXUag&ab_channel=LogicLambda

In eclipse, right clicking on the project>properties>Java Build Path>Libraries>Classpath>Add External JARs allows the sqlite jdbc driver to be integrated into the program.

I also found a database browser to visually verify the results of operations after runtime:

https://sqlitebrowser.org/about/

During the process, I ran into a number of problems and errors, some of which was due to program code, and some of which was due to the SQL queries.

In one instance, I forgot to put commas between column names, ended up with one super-column and got “incorrect syntax near 1” error.

While building out the database CRUD methods, I got errors saying the database was locked, or closed. In these cases, I forgot to close connections or database objects, or I accidentally closed them before they could be used.

I decided on a scoring system based on ItemsCollected divided by PlayerActions multiplied by 1000 if all parts were collected, or by 100 for not finding all parts. However, I found that calculation was resulting in 0 score consistently. This is because in division, I needed to typecast integers as floats or some decimal enabled variable, as otherwise every resulting fraction would be 0 multiplied by something which is always zero.

I tried multiple approaches to initializing and updating the database. At first, I tried copying the data from one entry and applying it to the next in a loop, but these were inefficient and left unwanted duplicates or dangling data beyond the desired bounds.

Trying to shift rows down by one, to make room for the newest, tried looping and shifting, ended up with duplicates of all but 1, and overall determined it was not efficient to sort within the database.

I then started simply incrementing the ordering column variable and inserting at the beginning, after cutting off the end. This created a First in-Last out Queue structure.