万年素人からHackerへの道

万年素人がHackerになれるまで殴り書きするぜ。

  • ・寄付お願いします
    YENTEN:YYzNPzdsZWqr5THWAdMrKDj7GT8ietDc2W
    BitZenny:ZfpUbVya8MWQkjjGJMjA7P9pPkqaLnwPWH
    c0ban:8KG95GXdEquNpPW8xJAJf7nn5kbimQ5wj1

Indie版UnityでSQLite

iPhone向けにはSystem.Data.SQLiteを使った場合にPro版でないと
http://terasur.blog.fc2.com/blog-entry-265.html
のようにエラーになる。

iPhone向けで、無料版のUnityでSQLiteを使用するとき

コチラのクラスを用意し、
・DBAccess.cs

using System.Data;
using System.Collections.Generic;
using Mono.Data.Sqlite;// we import sqlite
 
class DBAccess
{
    // variables for basic query access
    private string connection;
    private IDbConnection dbcon;
    private IDbCommand dbcmd;
    private IDataReader reader;
 
    public void OpenDB (string p)
    {
        connection = "URI=file:" + p; // we set the connection to our database
        dbcon = new SqliteConnection (connection);
        dbcon.Open ();
    }
 
    public IDataReader BasicQuery (string q, bool r)
    {
        // run a baic Sqlite query
        dbcmd = dbcon.CreateCommand (); // create empty command
        dbcmd.CommandText = q; // fill the command
        reader = dbcmd.ExecuteReader (); // execute command which returns a reader
        if (r)
        { // if we want to return the reader
            return reader; // return the reader
        }

        return null;
    }
 
    // This returns a 2 dimensional ArrayList with all the
    //  data from the table requested
    public List<List<object>> ReadFullTable (string tableName)
    {
        string query;
        query = "SELECT * FROM " + tableName;
        dbcmd = dbcon.CreateCommand ();
        dbcmd.CommandText = query; 
        reader = dbcmd.ExecuteReader ();
        List<List<object>> readArray = new List<List<object>> ();
        while (reader.Read())
        { 
            List<object> lineArray = new List<object> ();
            for (int i = 0; i < reader.FieldCount; i++)
            {
                lineArray.Add (reader.GetValue (i));
            } // This reads the entries in a row
            readArray.Add (lineArray); // This makes an array of all the rows
        }
        return readArray; // return matches
    }
 
    // This function deletes all the data in the given table.  Forever.  WATCH OUT! Use sparingly, if at all
    public void DeleteTableContents (string tableName)
    {
        string query;
        query = "DELETE FROM " + tableName;
        dbcmd = dbcon.CreateCommand ();
        dbcmd.CommandText = query; 
        reader = dbcmd.ExecuteReader ();
    }
 
    public void CreateTable (string name, string[] col, string[] colType)
    { // Create a table, name, column array, column type array
        string query;
        query = "CREATE TABLE " + name + "(" + col [0] + " " + colType [0];
        for (int i=1; i<col.Length; i++)
        {
            query += ", " + col [i] + " " + colType [i];
        }
        query += ")";
        dbcmd = dbcon.CreateCommand (); // create empty command
        dbcmd.CommandText = query; // fill the command
        reader = dbcmd.ExecuteReader (); // execute command which returns a reader
 
    }
 
    public void InsertIntoSingle (string tableName, string colName, string value)
    { // single insert 
        string query;
        query = "INSERT INTO " + tableName + "(" + colName + ") " + "VALUES (" + value + ")";
        dbcmd = dbcon.CreateCommand (); // create empty command
        dbcmd.CommandText = query; // fill the command
        reader = dbcmd.ExecuteReader (); // execute command which returns a reader
    }
 
    public void InsertIntoSpecific (string tableName, string[] col, string[] values)
    { // Specific insert with col and values
        string query;
        query = "INSERT INTO " + tableName + "(" + col [0];
        for (int i=1; i<col.Length; i++)
        {
            query += ", " + col [i];
        }
        query += ") VALUES (" + values [0];
        for (int i=1; i<values.Length; i++)
        {
            query += ", " + values [i];
        }
        query += ")";
        dbcmd = dbcon.CreateCommand ();
        dbcmd.CommandText = query; 
        reader = dbcmd.ExecuteReader ();
    }
 
    public void InsertInto (string tableName, string[] values)
    {
        // basic Insert with just values
        string query;
        query = "INSERT INTO " + tableName + " VALUES (" + values [0];
        for (int i=1; i<values.Length; i++)
        {
            query += ", " + values [i];
        }
        query += ")";
        dbcmd = dbcon.CreateCommand ();
        dbcmd.CommandText = query; 
        reader = dbcmd.ExecuteReader (); 
    }
 
    // This function reads a single column
    //  wCol is the WHERE column, wPar is the operator you want to use to compare with, 
    //  and wValue is the value you want to compare against.
    //  Ex. - SingleSelectWhere("puppies", "breed", "earType", "=", "floppy")
    //  returns an array of matches from the command: SELECT breed FROM puppies WHERE earType = floppy;
    public List<object> SingleSelectWhere (string tableName, string itemToSelect, string wCol, string wPar, string wValue)
    {
        // Selects a single Item
        string query;
        query = "SELECT " + itemToSelect + " FROM " + tableName + " WHERE " + wCol + wPar + wValue; 
        dbcmd = dbcon.CreateCommand ();
        dbcmd.CommandText = query; 
        reader = dbcmd.ExecuteReader ();
        List<object> readArray = new List<object> ();
        while (reader.Read())
        { 
            readArray.Add (reader.GetString (0)); // Fill array with all matches
        }
        return readArray; // return matches
    }
 
    public void CloseDB ()
    {
        reader.Close (); // clean everything up
        reader = null; 
        dbcmd.Dispose (); 
        dbcmd = null; 
        dbcon.Close (); 
        dbcon = null; 
    }
}

・ScriptThatUsesTheDatabase.cs
のサンプルで動作する。

using UnityEngine;
using System;
using System.Collections;
using System.Collections.Generic;

public class ScriptThatUsesTheDatabase : MonoBehaviour
{
    public string  DatabaseName = "TestDB.sqdb";

    // This is the name of the table we want to use
    public string  TableName = "TestTable";
    private DBAccess db ;

    void Start ()
    {
        // Give ourselves a dbAccess object to work with, and open it
        db = new DBAccess ();
        db.OpenDB (DatabaseName);
        // Let's make sure we've got a table to work with as well!
        string tableName = TableName;
        string[] columnNames = new string[]{"firstName", "lastName"};
        string[] columnValues = new string[]{"text", "text"};
        try
        {
            db.CreateTable (tableName, columnNames, columnValues);
        }
        catch (Exception e)
        {
            // Do nothing - our table was already created
            //- we don't care about the error, we just don't want to see it
            Debug.LogError(e.ToString());
        }
    }

    // These variables just hold info to display in our GUI
    private string firstName = "First Name";
    private string  lastName = "Last Name";
    private int DatabaseEntryStringWidth = 100;
    private Vector2 scrollPosition ;
    private List<List<object>> databaseData = new List<List<object>> ();

    // This GUI provides us with a way to enter data into our database
    //  as well as a way to view it
    void OnGUI ()
    {
        GUI.Box (new Rect (25, 25, Screen.width - 50, Screen.height - 50), ""); 
        GUILayout.BeginArea (new Rect (50, 50, Screen.width - 100, Screen.height - 100));
        // This first block allows us to enter new entries into our table
        GUILayout.BeginHorizontal ();
        firstName = GUILayout.TextField (firstName, GUILayout.Width (DatabaseEntryStringWidth));
        lastName = GUILayout.TextField (lastName, GUILayout.Width (DatabaseEntryStringWidth));
        GUILayout.EndHorizontal ();
        
        if (GUILayout.Button ("Add to database"))
        {
            // Insert the data
            InsertRow (firstName, lastName);
            // And update the readout of the database
            databaseData = ReadFullTable ();
        }
        // This second block gives us a button that will display/refresh the contents of our database
        GUILayout.BeginHorizontal ();
        if (GUILayout.Button ("Read Database"))
        { 
            databaseData = ReadFullTable ();
        }
        if (GUILayout.Button ("Clear"))
        {
            databaseData.Clear ();
        }
        GUILayout.EndHorizontal ();
        
        GUILayout.Label ("Database Contents");
        scrollPosition = GUILayout.BeginScrollView (scrollPosition, GUILayout.Height (100));
        foreach (List<object> line in databaseData)
        {
            GUILayout.BeginHorizontal ();
            foreach (object s in line)
            {
                GUILayout.Label (s.ToString (), GUILayout.Width (DatabaseEntryStringWidth));
            }
            GUILayout.EndHorizontal ();
        }

        GUILayout.EndScrollView ();
        if (GUILayout.Button ("Delete All Data"))
        {
            DeleteTableContents ();
            databaseData = ReadFullTable ();
        }

        GUILayout.EndArea ();
    }

    // Wrapper function for inserting our specific entries into our specific database and table for this file
    private void InsertRow (string firstName, string lastName)
    {
        string[] values = new string[]{("'" + firstName + "'"), ("'" + lastName + "'")};
        db.InsertInto (TableName, values);
    }

    // Wrapper function, so we only mess with our table.
    private List<List<object>> ReadFullTable ()
    {
        return db.ReadFullTable (TableName);
    }

    // Another wrapper function...
    private void DeleteTableContents ()
    {
        db.DeleteTableContents (TableName);
    }
}

元ネタは
http://wiki.unity3d.com/index.php/SQLite
だが、UnityScriptという酷い言語選択な上、コードが間違ってる・・・。
このコードをC#に置き換えて綺麗にした。
C#だと動かないかもしれない。。。。。