koobas.hobune.stream
Converting from SQLite Database to JSON
Good day,
I need help with a project I am working on, its a multiplayer virtual environment simulating a home (similar to The Sims game ) however it is currently saving data intoan SQLlite database . Is it possible for me to convert the SQL lite database into a json string?
below is a snippet of my code
public class DBInstanceInspector : ScriptableObject {
public bool DebugMode = false;
public string SQL_DB_LOCATION = "";
public string SQL_DB_NAME = "sensor_readings";
private IDbConnection dbConnection = null;
private IDbCommand dbCommand = null;
private IDataReader dbReader = null;
public void InitialiseSQLiteConnection()
{
/*
* Important to note:
* - during builds, this is located in the project root (where assets, obj and other folders located)
* - during runtime (Windows at least), this is located in the SAME directory as the executable
*
* Currently, this will always make it point to Assets/Databases/ directory.
*/
SQL_DB_LOCATION = "URI=file:" + Application.dataPath + "/Databases/" + SQL_DB_NAME + ".db";
if (DebugMode)
Debug.Log(SQL_DB_LOCATION);
Debug.Log("Opening SQLite Connection at " + SQL_DB_LOCATION);
dbConnection = new SqliteConnection(SQL_DB_LOCATION);
dbCommand = dbConnection.CreateCommand();
dbConnection.Open();
OptimiseDBForPerformance();
dbConnection.Close();
}
private void OptimiseDBForPerformance()
{
// WAL = write ahead logging, basically a speed increase (see https://sqlite.org/wal.html)
dbCommand.CommandText = "PRAGMA journal_mode = WAL;";
dbCommand.ExecuteNonQuery();
// Speed increase in commits (https://sqlite.org/pragma.html#pragma_synchronous)
dbCommand.CommandText = "PRAGMA synchronous = OFF";
dbCommand.ExecuteNonQuery();
}
public void CloseSQLiteConnection()
{
if (dbReader != null && !dbReader.IsClosed)
dbReader.Close();
dbReader = null;
if (dbCommand != null)
dbCommand.Dispose();
dbCommand = null;
if (dbConnection != null && dbConnection.State != ConnectionState.Closed)
dbConnection.Close();
dbConnection = null;
}
public bool DoesTableExistInDB(string tableName)
{
Debug.Log("Checking if table exists: " + tableName);
bool tableExists = true;
dbConnection.Open();
dbCommand.CommandText = "SELECT name FROM sqlite_master WHERE name='" + tableName + "'";
dbReader = dbCommand.ExecuteReader();
if (!dbReader.Read())
tableExists = false;
dbReader.Close();
dbConnection.Close();
return tableExists;
}
public void RemoveTableInDB(string tableName)
{
if (dbCommand != null)
{
Debug.Log("Dropping old SQLite table if exists: " + tableName);
dbCommand.CommandText = "DROP TABLE IF EXISTS " + tableName;
dbCommand.ExecuteNonQuery();
}
}
public int GetNumberOfRowsInTable(string tableName)
{
Debug.Log("Finding total number of rows in SQLite table if exists: " + tableName);
int numberOfRows = 0;
dbConnection.Open();
dbCommand.CommandText = "SELECT Count(*) FROM " + tableName;
dbReader = dbCommand.ExecuteReader();
while (dbReader.Read())
numberOfRows = dbReader.GetInt32(0);
dbReader.Close();
dbConnection.Close();
return numberOfRows;
}
Uhm, what kind of help do you need? There is no built-in conversion. You have to transfer the data yourself, However you haven't mentioned what tables you have / what columns those tables have and what json structure you want to use to represent your data.
So what's your concrete problem? Where are you stuck? This question can't be answered in it's current form.
Ok, as i said in the comment i'll assume for now that you just want to store the data from your sensor class(es) in a json file.
We can see that your "BaseSensorReading" is marked as serializable. Though since your actual sensor value is not a field it would not be serialized. So you would need a seperate class that represents a data record (like a row inside a database). As long as the class and all data that should be stored is serializable by Unity you could use the JsonUtility to save several datasets you have gathered into a file. First of all Unity's JsonUtility does only support an object as base element in a json file. Json itself allows an array as well but if we want to use the JsonUtility we need a wrapper class.
[System.Serializable]
public class SensorData
{
public string SensorType;
public string AreaName;
public string SensorName
public int Year, Month, Day;
public int Hour, Minutes, Seconds;
public float SensorValue;
public string SensorBookmarkName;
public string PlayerID;
public int SessionID;
}
[System.Serializable]
public class Data
{
public List<SensorData> data = new List<SensorData>();
}
Now you can just create an instance of the Data class and add new instances of the SensorData class whenever you generate a data record. So somewhere you would create a variable like this
Data myJsonData = new Data();
To add a new data record just do
SensorData record = new SensorData();
record.SensorType = yourType;
// [...] fill all the other fields
myJsonData.data.Add(record);
that way you can store your gathered records in that list. Once you're done you can save that information into a json file by simply doing:
System.IO.File.WriteAllText("C:\\YourPath\\To\\Your\\File", JsonUtility.ToJson(myJsonData));
Note by default ToJson will produce "compact" json. So without spaces or line breaks. If you want a human readable format you can pass "true" as optional parameter as you can read here
System.IO.File.WriteAllText("C:\\YourPath\\To\\Your\\File", JsonUtility.ToJson(myJsonData, true));
This will create a json file from the SensorData records you have put into the data list.
Thank you @Bunny83 thanks so much for this it works !!!!!!!!!!!!
HI @Bunny83, sorry about that I am still a beginner in programming so some of the concepts I am still learning. Basically, have 1 table of data with about 8 fields that are being collected from a class I have called Base sensor readings. Basically, in the virtual environment or game, I am building I have sensors that are being triggered during gameplay and I want the data from these sensors (which is that base sensor class) stored in a JSON file. in this case, the order of columns doesn't matter but as long as the fields are passed into the JSON file and stored. Below are the fields and the snippet of the class i want to get data from.
Blockquote
*0 SensorType (text)
* 1 AreaName (text)
* 2 SensorName (text)
* 3-5 Date - Year, Month, Day (integers)
* 6-8 Time - Hour, Minutes, Seconds (integers)
* 9 SensorValue (dependent on sensor reading type)
* 10 SensorBookmarkName (text)
* 11 PlayerID (text)
* 12 SessionID (integer)
Blockquote
[System.Serializable]
public class BaseSensorReading {
public Sensor.SensorType sensorType;
public string areaName;
public string sensorName;
public DateTimeValues sensorDateTime;
public virtual void SetSensorDateTime(int year, int month, int day, int hours, int minutes, int seconds)
{
if (sensorDateTime == null)
sensorDateTime = new DateTimeValues(year, month, day, hours, minutes, seconds);
else
{
sensorDateTime.SetDate(year, month, day);
sensorDateTime.SetTime(hours, minutes, seconds);
}
}
public virtual string GetSensorReadingString()
{
return sensorDateTime.GetDateString() + ";"
+ sensorDateTime.GetTimeString() + ";"
+ sensorType.ToString() + ";" + areaName + ";" + sensorName;
}
public virtual float GetSensorValue()
{
return 0f;
}
//public BaseSensorReading data = new BaseSensorReading();
}
I can't seem to understand how I can be able to get that data from that class of sensor readings into a JSON string.
Well, first of all you posted an answer and did not answer your own question. Why didn't you edit your question? Anything relevant to the question should be mentioned inside the question.
This post clears up some things but brings up even more questions. Here you seem to ask how to store data from a in-game class inside a json file while your original question was how to get the data from a sqlite database into a json file. Your first code snippets in your question was only about reading from the database file. So we have to assume the data is already inside the database. So if you collect data from your sensors is that data already stored in that DB? $$anonymous$$ore concrete: Do you have some code that does already storing your sensor data inside the database?
Now do you actually want to change the script ins$$anonymous$$d of writing to the database to write to a json file? Or do you just want to get the data from the database into a json file?
$$anonymous$$eep in $$anonymous$$d that Json is just a data exchange format and not a database. You can't simply add more data to the file as you go. The file needs to be written at once due to the json structure. Databases are specifically designed to allow efficient insert, delete and search operations on the data sets.
I'll just assume for a moment that all that sqlite stuff is actually obsolete and you just want to store the data gathered through your sensors in a json file. I'll post an answer soon.
thanks that was my bad , i was still getting accustomed to the interface on here
