Quick SQLite question for Unity Game Database

Questions & Answers
1

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! :slight_smile:

2

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

  1. Generate an array with length COUNT, containing simply the indices 0…[Count-1]

  2. Randomize the array using repeated shuffle.

  3. 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;*

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[] 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;
}
}
}