Gp3MfARL

1using Mono.Data.Sqlite;
2using System;
3using System.Collections;
4using System.Collections.Generic;
5using System.Data;
6using UnityEngine;
7
8public class DatabaseHandler : MonoBehaviour
9{
10    public string databasePath = "TestDB.db"; // Path to the DB. If it doesn't exist, it will be created. NOTE: Doesn't currently work unless the file is in the root folder!
11
12    private Dictionary <uint, string> sessionPlayerList = new Dictionary <uint, string>(); // Dictionary for storing player network IDs for the session and their associated persistent player IDs.
13
14    private string connection;
15    private IDbConnection dbcon;
16    private IDbCommand dbcmd;
17    private IDataReader reader;
18    private string query;
19
20    private string GetPlayerID (uint networkID) // Allows the persistent ID of a player to be retrieved using their session-specific network ID.
21    {
22        Debug.Log ("dbHandler: Retrieving player ID for player with network ID '" + networkID + "'.");
23
24        string playerID = null;
25        sessionPlayerList.TryGetValue (networkID, out playerID); // Attempt to get the player's ID and store it in the string 'playerID'.
26
27        if (playerID == null)
28        {
29            Debug.Log ("dbHandler: Error when retrieving player ID from session list: There is no player associated with this network ID!");
30        }
31
32        return playerID; // WARNING: Will be null if no playerID was found for the given network ID.
33    }
34
35    public void AddNewEntryToSessionPlayerList (uint networkID, string playerID)
36    {
37        Debug.Log ("dbHandler: Creating a new entry in the session player list for player with network ID '" + networkID + "', player ID '" + playerID + "'.");
38
39        if (sessionPlayerList.ContainsKey (networkID)) // Check that there isn't already an entry for the given network ID (and return early if there is).
40        {
41            Debug.Log ("dbHandler: Error when adding a new entry to session player list; this network ID is already associated with a player!");
42            return; // Do not add a new entry to the session player list.
43        }
44
45        if (sessionPlayerList.ContainsValue (playerID)) // Check that this player isn't already using another network ID.
46        {
47            Debug.Log("dbHandler: Error when adding a new entry to session player list; this player is already connected with another network ID!");
48            return;
49        }
50
51        sessionPlayerList.Add (networkID, playerID);
52    }
53
54    public void RemoveEntryFromSessionPlayerList (uint networkID)
55    {
56        Debug.Log("dbHandler: Removing entry from the session player list for player with network ID '" + networkID + "'.");
57
58        if (!sessionPlayerList.ContainsKey (networkID)) // Check that an entry currently exists for the given network ID (and return early if it doesn't).
59        {
60            Debug.Log ("dbHandler: Error when removing an entry from the player session list; there is no player associated with this network ID!");
61            return; // Do not remove an entry from the session player list.
62        }
63
64        sessionPlayerList.Remove (networkID);
65    }
66
67    public void Start ()
68    {
69        // Open the database (or create it if it doesn't exist yet).
70        connection = "URI=file:" + databasePath;
71        dbcon = new SqliteConnection (connection);
72        dbcon.Open();
73
74        // Create the PlayerData table if it doesn't exist yet.
75        CreatePlayerDataTable();
76    }
77
78    private void CreatePlayerDataTable ()
79    {
80        Debug.Log ("dbHandler: Creating PlayerData table (if it doesn't yet exist).");
81
82        // Construct the necessary query for creation of the table (checking first that it doesn't already exist).
83        query = "CREATE TABLE IF NOT EXISTS PlayerData (";
84        query += "id TEXT PRIMARY_KEY, characterName TEXT, familyName TEXT, posX FLOAT, posY FLOAT, posZ FLOAT, rotX FLOAT, rotY FLOAT, rotZ FLOAT, rotW FLOAT";
85        query += ")";
86
87        // Actually create the PlayerData table.
88        dbcmd = dbcon.CreateCommand(); // Create empty command.
89        dbcmd.CommandText = query; // Fill the command.
90        reader = dbcmd.ExecuteReader(); // Execute the command (returns a reader).
91    }
92
93    // Add an entry for a new player to the database.
94    public void RegisterNewPlayer (string playerID, string characterName, string familyName, Vector3 pos, Quaternion rot)
95    {
96        Debug.Log ("dbHandler: Registering a new player with ID '" + playerID + "'.");
97
98        // Construct the necessary query for addition of a new player record.
99        query = "INSERT INTO PlayerData VALUES (";
100        query += "'" + playerID + "', '" + characterName + "', '" + familyName + "', " + pos.x + ", " + pos.y + ", " + pos.z + ", " + rot.x + ", " + rot.y + ", " + rot.z + ", " + rot.w;
101        query += ")";
102
103        // Actually create the new record.
104        dbcmd = dbcon.CreateCommand(); // Create empty command.
105        dbcmd.CommandText = query; // Fill the command.
106        reader = dbcmd.ExecuteReader(); // Execute the command (returns a reader).
107    }
108
109    // Retrieve a complete player record from the database via their network ID (and return it as a comma-delimited string).
110    public string GetCompletePlayerRecord (uint networkID)
111    {
112        string playerRecord = ""; // Stores the retrieved data as a comma-delimited list of values.
113
114        string playerID = GetPlayerID (networkID); // Get the player's ID from their network ID using the sessionPlayerData dictionary.
115
116        Debug.Log ("dbHandler: Retrieving complete player data for player ID '" + playerID + "'.");
117
118        // Construct the necessary query for retrieval of a complete player record.
119        query = "SELECT * FROM PlayerData WHERE id = ";
120        query += "'" + playerID + "'";
121
122        // Actually retrieve the player record.
123        dbcmd = dbcon.CreateCommand(); // Create empty command.
124        dbcmd.CommandText = query; // Fill the command.
125        reader = dbcmd.ExecuteReader(); // Execute the command (returns a reader).
126
127        while (reader.Read())
128        {
129            playerRecord = reader["id"] + ", " + reader["characterName"] + ", " + reader["familyName"] + ", " + reader["posX"] + ", " + reader["posY"] + ", " + reader["posZ"];
130            playerRecord += ", " + reader["rotX"] + ", " + reader["rotY"] + ", " + reader["rotZ"] + ", " + reader["rotW"];
131        }
132
133        return playerRecord;
134    }
135
136    // Update the location stored in the database for a player given their position and rotation.
137    public void UpdatePlayerPosition (uint playerNetworkID, Vector3 pos, Quaternion rot)
138    {
139        Debug.Log ("dbHandler: Updating position in the database for player with network ID '" + playerNetworkID + "'.");
140
141        // Construct the necessary query for updating a player's position data.
142        query = "UPDATE PlayerData SET ";
143        query += "posX = " + pos.x + ", posY = " + pos.y + ", posZ = " + pos.z + ", rotX = "+ rot.x + ", rotY = " + rot.y + ", rotZ = " + rot.z + ", rotW = " + rot.w + " ";
144        query += "WHERE id = '" + GetPlayerID (playerNetworkID) + "'";
145
146        // Actually update the position data.
147        dbcmd = dbcon.CreateCommand(); // Create empty command.
148        dbcmd.CommandText = query; // Fill the command.
149        reader = dbcmd.ExecuteReader(); // Execute the command (returns a reader).
150    }
151}