//Create User_Table
public void Create_UnlockedCharacterTable()
{
dbManager.BeginTransaction();
string sql = "CREATE TABLE IF NOT EXISTS \"User_Table\" " +
"(\"UNLOCKED_CHAR_ID\" INTEGER, " +
"\"UNLOCKED_CHAR_NAME\" varchar(40))";
dbManager.Execute(sql);
dbManager.Commit();
dbManager.Close();
}
/// <summary>
/// Creates the table using a SQL statement
/// </summary>
public void CreateTable_Query()
{
string sql;
// Start a transaction to batch the commands together
dbManager.BeginTransaction();
// Create the table
sql = "CREATE TABLE \"StarShip\" " +
"(\"StarShipID\" INTEGER PRIMARY KEY NOT NULL, " +
"\"StarShipName\" varchar(60) NOT NULL, " +
"\"HomePlanet\" varchar(100) DEFAULT Earth, " +
"\"Range\" FLOAT NOT NULL, " +
"\"Armor\" FLOAT DEFAULT 120, " +
"\"Firepower\" FLOAT)";
dbManager.Execute(sql);
// Create an index on the starship name
sql = "CREATE INDEX \"StarShip_StarShipName\" on \"StarShip\"(\"StarShipName\")";
dbManager.Execute(sql);
// Commit the transaction and run all the commands
dbManager.Commit();
Results("Create Table Query Success!");
}
void Start()
{
dbManager.Execute("DROP TABLE IF EXISTS posts");
dbManager.Execute("CREATE VIRTUAL TABLE posts"
+ " USING FTS5 ("
+ "title"
+ ",body"
+ ")"
);
dbManager.Execute("INSERT INTO posts "
+ "("
+ "title"
+ ",body"
+ ")"
+ " VALUES ("
+ "'Learn SQlite FTS5'"
+ ",'This tutorial teaches you how to perform full-text search in SQLite using FTS5'"
+ ")"
+ ",("
+ "'Advanced SQlite Full-text Search'"
+ ",'Show you some advanced techniques in SQLite full-text searching'"
+ ")"
+ ",("
+ "'SQLite Tutorial'"
+ ",'Help you learn SQLite quickly and effectively'"
+ ")"
);
var matchResults = dbManager.Query <Post>("SELECT * FROM posts WHERE posts MATCH 'learn NOT text'");
outputText.text = "Posts matching 'learn NOT text'\n\n";
foreach (var result in matchResults)
{
outputText.text += "<color=#1abc9c>Title</color>: '" + result.title + "' " +
"<color=#1abc9c>Body</color>: '" + result.body + "'\n";
}
}
void Start()
{
outputText.text = "";
// get the final path in the working directory by combining the persistentDataPath with the new file name
var attachDatabaseFilePath = Path.Combine(Application.persistentDataPath, attachDatabaseNewFileName);
// extract the attach database file from the Unity project into the working directory
if (!ExtractDatabase(attachDatabaseFilePath))
{
// failed, exit
return;
}
// attach to the scifi database
dbManager.Execute("ATTACH DATABASE '" + attachDatabaseFilePath + "' AS SciFi");
// query the players from the newly attached database and output them to the screen
outputText.text += "Players:\n";
var players = dbManager.Query <PlayerStats>("SELECT * FROM PlayerStats");
foreach (var player in players)
{
outputText.text += player.PlayerName + "\n";
}
outputText.text += "\n\n";
// query the weapons from the original datbase and output them to the screen
outputText.text += "Weapons:\n";
var weapons = dbManager.Query <Weapon>("SELECT * FROM Weapon");
foreach (var weapon in weapons)
{
outputText.text += weapon.WeaponName + "\n";
}
}
/// <summary>
/// Updates the player stats by executing a SQL statement. Note that no data is returned, this only modifies the table
/// </summary>
/// <param name='playerID'>
/// The ID of the player to update
/// </param>
/// <param name='playerName'>
/// Player name.
/// </param>
/// <param name='totalKills'>
/// Total kills.
/// </param>
/// <param name='points'>
/// Points.
/// </param>
private void UpdatePlayerStats_Query(int playerID, string playerName, int totalKills, int points)
{
// Call our SQL statement using ? to bind our variables
dbManager.Execute("UPDATE PlayerStats SET PlayerName = ?, TotalKills = ?, Points = ? WHERE PlayerID = ?", playerName, totalKills, points, playerID);
}
/// <summary>
/// Saves the player stats by executing a SQL statement. Note that no data is returned, this only modifies the table
/// </summary>
/// <param name='playerName'>
/// Player name.
/// </param>
/// <param name='totalKills'>
/// Total kills.
/// </param>
/// <param name='points'>
/// Points.
/// </param>
private void SavePlayerStats_Query(string playerName, int totalKills, int points)
{
// Call our SQL statement using ? to bind our variables
dbManager.Execute("INSERT INTO PlayerStats (PlayerName, TotalKills, Points) VALUES (?, ?, ?)", playerName, totalKills, points);
}
/// <summary>
/// Deletes the first player stat by executing a SQL statement. Note that no data is returned, this only modifies the table
/// </summary>
/// <param name='playerID'>
/// The ID of the player to update
/// </param>
private void DeletePlayerStats_Query(int playerID)
{
// Call our SQL statement using ? to bind our variables
dbManager.Execute("DELETE FROM PlayerStats WHERE PlayerID = ?", playerID);
}