Before you begin using Sqlite for BGE, edit the following in the CM_Database class:
- [projectName] Variable used in the creation of your profile-based database folder.
- [databaseName] Variable use to create your database name and extension.
- [GetSqlStatements] Function that contains your SQL statements for database creation and population.
- [GetResetStatements] Function that contains your SQL statements for dropping tables and resetting the database.
A simple to use database access class that leverages the CM_Database class to provide multi-platform compliant access to a Sqlite database using the four simple functions described below. This class requires no modification, but you will need to modify the CM_Database.py class before you begin. See the CM_Database.py class for details.
- SqlScalar (Use this to retrieve a single value.)
- SqlNonQuery (Use this to execute insert, update, or delete statements.)
- SqlReader (Use this to retrieve a data set.)
- ResetDB (Drop tables and rebuild using [GetSqlStatements] from CM_Database)
Import the CM_Sqlite namespace into your python script:
from CM_Sqlite import CM_Sqlite
Instantiate an instance:
dba = CM_Sqlite()
Use the instance to call a method:
#Retrieve a single value
count = dba.SqlScalar("SELECT COUNT(ID) FROM savedata WHERE ID='1';")
#Execute commands such as INSERT, UPDATE, or DELETE
dba.SqlNonQuery("UPDATE savedata SET PosX='0', PosY='0', PosZ='1' WHERE ID='1';")
#INSERT statements return the last_insert_id
lastID = dba.SqlNonQuery("INSERT INTO savedata (PosX, PosY, PosZ) VALUES ('0', '0', '1');")
#Retrieve a list of data
myList = dba.SqlReader("SELECT PosX, PosY, PosZ FROM savedata WHERE ID='1';")
#Reset the database back to default settings
This class creates a platform compliant folder path in your profile. Edit the the following details before you begin using the system:
- [projectName] - This is the name of the folder that will be created in your profile to store the database.
- [databaseName] - This is the name of the database and file extension that will be store in your project folder.
- [GetSqlStatements] - Add your own SQL statements for database creation and population to this function.
- [GetResetStatements] - Add your own SQL table reset statements to this function.
Database creation path by platform:
- GetPath (Builds a platform compliant profile-based database path, creates the path if it doesn't exist, and returns the path.)
- GetSqlStatements (Returns your Sql create and insert statements as a list of queries)
- GetResetStatements (When doing a database reset with CM_Sqlite.ResetDB(), this command is called to provide the statements necessary to purge all tables from the database.)
Example usage -
This class is used behind the scenes by CM_Sqlite. Modify the variables [projectName] and [databaseName] to your preference, add your own SQL create and/or insert statements to the [GetSqlStatements] function, and add your own SQL drop table statements to the [GetResetStatements] function for game resets. No other modifications are necessary.
Example usage of the CM_Sqlite class to delete objects from the database and the scene, upon collision. Attach this script to the Player cube, add a Collision Sensor with Pulse Mode set to True, and link it to a Python Controller with this script. Add an Enemy Boolean property to the green ball enemy spawn object and set it's value to True. Upon Collision, the ID of this object will be retrieved so that the scene object and the corresponding database record can be deleted.
Example usage of the CM_Sqlite class to retrieve previously saved scene and spawn object positions from the database and update their positions. Attached to the camera using an Always Sensor with Pulse Mode set to True, and linked to a Python Controller with this script. It retrieves all saved object data from the database and updates the position of scene and spawned objects. Listens for a [H] key press.
This script loads the 'Overlay' scene that contains the instructional text visible in play mode [P].
Example usage of the CM_Sqlite class to load object position data from the database to an object that already exists in the scene, and has an ID integer property set at design-time to correlate with an existing database ID record. Attach this script to the player cube object in the scene, add an Always Sensor with Pulse Mode set to False, and link it to a Python Controller with this script. Add an ID Integer property and set it's value to correlate with an existing record in the database. Upon next play, this object will start in the last saved position.
Example usage of the CM_Sqlite class to save object position data to the database. Attach this script to an object using an Always Sensor with Pulse Mode
set to True, and link it to a Python Controller with this script. Add an ID Integer property and set it's value to correlate with an existing record in the database. Listens for a [SPACE] key press.
Example usage of the [ResetDB] function in the CM_Sqlite class to reset the database back to default. The CM_Sqlite class then call's the [GetResetStatements] function from the CM_Database class to learn the SQL statements required to perform the requested reset. Once reset, the database is restored to default using the same logic as the first initialization. Listens for an [R] key press.
Simple movement script to allow cube (player) position changes in the example mini-game, which can then be saved to the database. Listens for the following key presses:
[UPARROWKEY] or [W] = forward
[DOWNARROWKEY] or [S] = backward
[RIGHTARROWKEY] or [D] = left
[LEFTARROWKEY] or [A] = right
Example usage of the CM_Sqlite class to retrieve previously saved spawn objects from the database and position them in the scene with previously saved position data. It's attached to the camera using an Always Sensor with Pulse Mode set to False, and linked to a Python Controller with this script. It retrieves all saved spawn data from the database and spawns each dynamic object back into the scene in the previously saved position, and sets the spawn object's ID property to the corresponding database ID.
Example usage of the CM_Sqlite class to spawn new objects into the database, and and into the scene when the [O] key is pressed. This example is attached to the camera using an Always Sensor with Pulse Mode set to True, and linked to a Python Controller with this script. When a new object is spawned into the scene, it's spawn position is inserted into the database, which returns the inserted record's ID. This ID is then assigned to the spawned object's ID integer property to correspond with the record inserted into the database. Listens for an [O] key press to spawn a new object.
Main scene to demonstrate the Sqlite functions.
Contains instructional text visible at run-time.