万年素人からHackerへの道

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

  • ・資産運用おすすめ
    10万円は1000円くらい利益
    資産運用ブログ アセマネ
    • ・寄付お願いします
      YENTEN:YYzNPzdsZWqr5THWAdMrKDj7GT8ietDc2W
      BitZenny:ZfpUbVya8MWQkjjGJMjA7P9pPkqaLnwPWH
      c0ban:8KG95GXdEquNpPW8xJAJf7nn5kbimQ5wj1
      Skycoin:KMqcn7x8REwwzMHPi9fV9fbNwdofYAWKRo

    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#だと動かないかもしれない。。。。。