In this Tutorial I will show how easy it is to manage SQLite databases with SQLiter. I will always try to explain examples how SQLite databases could be used in games. So our first attempt will be a simple hiscore table.
You will need Teamonkey's SQLite wrapper. Thanks to Assari who recompiled the SQLite wrapper for BlitzMax 1.18+. And you need the SQLiter module from here. The SQLite wrapper is contained in the archive, so you only need this one download. Except you are using pre BlitzMax 1.18. In this case you need the original SQLite wrapper from Teamonkey's webseite.
Assari did also wrote some nice SQLite tutorials . He explains how to work with the bare SQLite wrapper.
I also suggest to download SQLite Browser. A tool to browse the data of a SQLite Database.
SuperStrict
Import pyropixel.sqliter
'Create new SQLite Object
Global db:TSQLiter = New TSQLiter
'Open Database File
db.Open(":memory:")
'Create a database
db.Query( "CREATE TABLE hiscore(name TEXT, score INTEGER)")
'Insert some values
db.Query("INSERT INTO hiscore VALUES('Mike',20052)")
'Now lets see what is in the db
db.Query("SELECT * FROM hiscore")
db.PrintQuery(10) 'Close database
'Close database
db.Close()
End
Listing 1.1
Now let's examine Listing 1.1 step by step.
SuperStrict
Import pyropixel.sqliter
The first line sets BlitzMax to SuperStrict mode which means you have to declare and specify each variable.
The second line imports the SQLiter module.
Global db:TSQLiter = New TSQLiter
This line creates a new TSQLiter database object. All further database operations will be executed with this object.
db.Open(":memory:") Here we introduce the first method of the TSQLiter object: Open(). The method needs a string parameter with a path to a database file or, like in this example, ":memory:".
:memory: opens the database in, you guess, memory. A database opened in memory is always empty at the beginning. This is why we have to enter some data into the database in the next step.
db.Query( "CREATE TABLE hiscore(name TEXT, score INTEGER)")
At first we need to set up the structure of the database. A SQL database always consists of tables. Each table is subdivided into columns. And you have to define what kind of data a column can store, just like you tell BlitzMax if a variable stores a String or an Integer or a Float and so on...
So in the code above we tell the database to create a table called 'hiscore'. And we define a column called 'name' to store TEXT and a column called 'score' to store INTEGER values. The TEXT specifier means a String value with a maximum length of 255 characters. The INTEGER specifier is appropriate to BlitzMax's Int specifier.
Sending a command to database is usually called a query. Mostly the query contains SQL statements to perform database operations. SQLiter-Objects uses it's Query() method to send queries to database.
db.Query("INSERT INTO hiscore VALUES('Mike',20052)") Now it's time to insert real data. The code above inserts a new row of data into the table 'hiscore'. If we enter a value in each column of the table, we do not have to specify, which data belongs to which column. The data gets automatically stored into the columns, like we created the columns in the table. Because we created the column 'name' at first we will have to enter the name data at first after the VALUE paramater. And the score data comes at second place because we created the score column after the name column.
Of course it is possible to store only one column data per query. In this case we would have to specify which data gets into which column. I will cover this in another tutorial since this does not make sense in a hiscore table.
db.Query("SELECT * FROM hiscore")
db.PrintQuery(10) The first line sends a query to database. The SELECT statement queries data from the database. The asterisk is a wildcard for all columns in the table 'hiscore'. So what this query does is, simply output all data in the table 'hiscore'.
The second line uses the PrintQuery() method of TSQLiter objects. This is mostly used for testing and debug purposes, PrintQuery() prints the results of the last query to console column by column. You can send an integer parameter to PrintQuery() for a column width. So a PrintQuery(10) would print out the columns with a 10 characters width. Default is 30 characters.
db.Close()
The last TSQLiter method in this tutorial is Close(). This method simply closes the database connection and should be performed to avoid loss of data.
End is the BlitzMax command to close the application.
Now you see why SQLiter makes database operation much easier. No callback functions, no pointer variables. The ugly stuff is all done by SQLiter.
Let's see why a hiscore table in a database could be usefull:
SuperStrict
Import pyropixel.sqliter
'Create new SQLite Object
Global db:TSQLiter = New TSQLiter
'Open Database File
db.Open(":memory:")
'Create a database
db.Query( "CREATE TABLE hiscore(name TEXT, score INTEGER)")
'Insert some values
db.Query("INSERT INTO hiscore VALUES('Mike',20052)")
db.Query("INSERT INTO hiscore VALUES('Silja',78952)")
db.Query("INSERT INTO hiscore VALUES('Cathy',12052)")
'Now lets see what is in the db
db.Query("SELECT * FROM hiscore ORDER BY score DESC")
db.PrintQuery(10)
'Close database
db.Close()
End
Listing 1.2
Load listing 1.2 into BlitzMax IDE and execute it. You see: all the entries in the table prints out in the correct order. Let's examine what we did.
db.Query("INSERT INTO hiscore VALUES('Mike',20052)")
db.Query("INSERT INTO hiscore VALUES('Silja',78952)")
db.Query("INSERT INTO hiscore VALUES('Cathy',12052)") Here we entered some more player scores into the table 'hiscore'.
db.Query("SELECT * FROM hiscore ORDER BY score") This line queries the data in the table by sorting the rows by it's score value. Imagine a hiscore table with thousand entries. You don't need to write sorting algos or special inserting functions. Just add the new hiscore in table and query it's data with ORDER BY. The DESC indicates to order the result in a descending order. Change the DESC to an ASC an see what happen.
Now you're saying: Ok, but I don't want the query results in console but in my variables! Up to listing 1.3...
SuperStrict
Import pyropixel.sqliter
'Create new SQLite Object
Global db:TSQLiter = New TSQLiter
'Open Database File
db.Open(":memory:")
'Create a database
db.Query( "CREATE TABLE hiscore(name TEXT, score INTEGER)")
'Insert some values
db.Query("INSERT INTO hiscore VALUES('Mike',20052)")
db.Query("INSERT INTO hiscore VALUES('Silja',78952)")
db.Query("INSERT INTO hiscore VALUES('Cathy',12052)")
'Now lets see what is in the db
db.Query("SELECT * FROM hiscore ORDER BY score")
'Go through each query record of last query
While db.NextRecord()
Local name\$ = db.F("name")
Local score\$ = db.F("score")
Print position +". "+ name\$ +" - "+ score\$
Wend
'Close database
db.Close()
End
Listing 1.3
Run listing 1.3. You see that hiscore data gets printed and the data is sorted descending by score value. It's the same result as in listing 1.2. But this time we stored every data in a single variable and printed the variable out. Lets have a closer look.
While db.NextRecord()
Wend
Here we introduce a new method: NextRecord(). This method returns true as long there is a further record in the query. Since we queried all data in the table 'hiscore' by using the asterisk after the SELECT statement, we should get three records: one for Mike, one for Silja and for Cathy. Remember the results in console of listing 1.2. There were these three rows of names and score. One row in a table is also called a record. When you call NextRecord() you can check if there is a further record, and you set this further record, to the current record. From the current record you can read the data.
Now what this loop does is setting every result of the query to the current record, so that you can read the column fields of the record. If there a no more or none record at all, the loop will end because NextRecord() returns false.
Local name\$ = db.F("name")
Local score\$ = db.F("score") With method F(column_name\$) you can read the field of the column you like of the current record. You only have to give the column name as a parameter to the method like in the example.
The first line above reads the data from the current record in the column 'name' ans stores it's value in the BlitzMax variable name\$. In the same record we read the appropriate score with the second line and store it in the variable score\$. Remember that F() does always return a string value.
The last line in the loop simply prints the variables to console. But since you have the values now stored in variables, you could do what ever you want it to do.
This is the end of my first SQLiter Tutorial. I hope you were able to understand my english. If you find any mistakes or if you have suggestions please let me know.
Regards, René