koobas.hobune.stream
Quick SQLite question for Unity Game Database
Hi guys, quick sqlite3 question. Can anyone help? I need random selection but no repeats.
Currently I have:
string sqlQuery = "SELECT * FROM english ORDER BY RANDOM () LIMIT 1;";
any ideas?
Thanks a lot for any help! :)
Usually you can get away with determining the row index to select using code, then retrieving that row using SQL... unless you have a really large table. Here is what you do
Generate an array with length COUNT, containing simply the indices 0...[Count-1]
Randomize the array using repeated shuffle.
Now simply walk through the array, and use the value as the row index, i.e.
string sqlQuery = String.Format("SELECT * from english LIMIT 1 OFFSET %d", rowIndex);
Once you reach the end of the array, you've visited all rows exactly once and you can either start over or generate a new array.
Example code
using UnityEngine;
using System.Data;
using Mono.Data.Sqlite;
using System;
public class QuizStart : MonoBehaviour {
private IDbConnection connection;
private int[] rowIndices;
private int questionIndex;
void Start () {
// Initialization
string connectionString = "URI=file:" + Application.dataPath + "/quizz.db";
connection = (IDbConnection)new SqliteConnection(connectionString);
connection.Open();
InitializeQuestions();
// Execute GetNextQuestion when you need to access the next question
GetNextQuestion();
}
void OnDestroy() {
connection.Close();
}
private void InitializeQuestions()
{
// Retrieve amount of questions
IDbCommand cmd = connection.CreateCommand();
cmd.CommandText = "SELECT COUNT(*) FROM english";
var count = (Int64) cmd.ExecuteScalar();
// Generate a shuffled array of question indices.
rowIndices = new int[count];
for (int i = 0; i < count; i++)
rowIndices[i] = i;
Shuffle(rowIndices);
}
public void GetNextQuestion()
{
// Read question from database. Do note that you'll have to use your own columns here.
IDbCommand dbcmd = connection.CreateCommand();
string sqlQuery = string.Format("SELECT * " + "FROM english LIMIT 1 OFFSET {0}", rowIndices[questionIndex]);
dbcmd.CommandText = sqlQuery;
using (IDataReader reader = dbcmd.ExecuteReader())
{
reader.Read();
string question = reader.GetString(1);
Debug.Log("question: " + question);
reader.Close();
}
questionIndex++;
if (questionIndex > rowIndices.Length)
questionIndex = 0;
}
private static void Shuffle<T>(T[] array)
{
var rnd = new System.Random();
int n = array.Length;
while (n > 1)
{
int k = rnd.Next(n--);
T temp = array[n];
array[n] = array[k];
array[k] = temp;
}
}
}
Wow, this looks much more complex than I thought! :-) It will be a big table once I finish, with hundreds of entries (for a trivia game)... Thank you so much for explaining the process in detail. I really appreciate it! Now I'm off to try and implement it! ;-)
The good news is "hundreds of entries" will work perfectly fine with this approach, as you only need to create a single array of integers and enumerate it once more to swap the entries.If you run into any problems in the implementation make sure to post back and I'll extend the answer.
Hi again! So, this is what I have at present. No errors showing but not working. I'm sure I did something wrong. I'm still learning C#:
public void GetData()
{
using (IDbConnection dbConnection = new SqliteConnection(connectionString))
{
dbConnection.Open();
using (IDbCommand dbCmd = dbConnection.CreateCommand())
{
string sqlQuery = String.Format("SELECT * from english LI$$anonymous$$IT 1 OFFSET 20", 0,19);
dbCmd.CommandText = sqlQuery;
using (IDataReader reader = dbCmd.ExecuteReader())
{
while (reader.Read())
{
questionText.text = reader.GetString(1);
for (int i = 0; i < answerButtons.Length; i++)
{
answerButtons[i].GetComponentInChildren<Text>().text = reader.GetString (2 + i);
}
correctAnswerText = reader.GetString(7);
Debug.Log(correctAnswerText);
correctAnswerNum = reader.GetInt32(8);
Debug.Log(correctAnswerNum);
Debug.Log (reader.GetString(1) + reader.GetString(2) + reader.GetString(3) + reader.GetString(4) + reader.GetString(5) + reader.GetString(6));
}
dbConnection.Close();
reader.Close();
}
}
}
}
int[] array = Enumerable.Range(0, 19).ToArray();
public static void Shuffle<T>(T[] array)
{
var rnd = new System.Random();
int n = array.Length;
while (n > 1)
{
int k = rnd.Next(n--);
T temp = array[n];
array[n] = array[k];
array[k] = temp;
}
}
}
Any ideas? :)
I've added working code that illustrates the approach. Hope that helps! :)
