At work we are trying to develop some ways to visualize data. Since game engines are great at making complicated math look cool, we are trying to use them to visualize data-sets. We wanted to test how many “dimensions” we could represent (x, y, z..size, direction, color, etc). So I made a little proof of concept with Unity3d, SQLite, and AIR. (The AIR part of will not be covered in this tutorial; but I basically used it as a simple form to input data-points, nothing crazy.)
Unity3d uses Mono which has an SQLite Library which I then modified to javascript (for some reason it worked better in javascript than C#).
In this tutorial, I will assume you already have a basic understanding of databases, scripting, and even Unity.
So the first thing we’ll do is create a new project in Unity. Then create an Empty Game Object, place it at (0,0,0), and name it 3DGraph. To create an axis, simply create a Cube, make it a child of 3DGraph by dragging it onto 3DGraph in the Hierarchy view. Lets name this X-Axis and place it at (0,0,0) and scale it (100,0.5,0.5). This should give us a nice long bar on the x axis. Repeat these steps for Y-Axis and Z-Axis, scaling the respective axis by 100 and the other by 0.5.
Once that is complete, we should have a nice X,Y,Z graph. Feel free to get fancy and add materials and colors, and add a point light or some other light source, but I will not cover how to do that. With that done, lets take a moment to go over our database.
I used SQLite Database Browser to create and manipulate the SQLite DB. Create a new table called points and save the SQLite DB file in the same directory as the Unity project, name the DB file “SqliteTest.db” or something. Here is the SQLite CREATE statement for tabel points.
CREATE TABLE points (
id INTEGER PRIMARY KEY,
name VARCHAR,
x NUMERIC,
y NUMERIC,
z NUMERIC,
scale NUMERIC,
r NUMERIC,
g NUMERIC,
b NUMERIC,
timeMod date
)
The name is whatever name the point has, x,y,z are the coordinates, scale is how much to scale the point (size), r,g,b are the color values, and timeMod is the date it was last modified. These are the 7 dimensions that we will visualize in this tutorial. Since it is SQLite, a TRIGGER is needed to keep the timeMod up to date when data is updated or inserted. Here is the TRIGGER statement.
CREATE TRIGGER insert_point_timeMod AFTER INSERT ON points
BEGIN
UPDATE points SET timeMod = DATETIME('NOW') WHERE id = new.id;
END;
CREATE TRIGGER update_point_timeMod AFTER UPDATE ON points
BEGIN
UPDATE points SET timeMod = DATETIME('NOW') WHERE id = new.id;
END;
Now that the DB is all setup perfectly, quit the SQLite Database Browser (or else you will get an error when Unity tries to access the DB). Now we need to create the script in Unity to access the DB (Project view…Create JavaSctipt). You will have to rename it to something useful, like “MySqlite” or “SQLiteUpdater”. Here is the code.
import System;
import Mono.Data.Sqlite;
var pointObject : Transform;
private var childArray : Array = new Array();
private var graphClone : Object;
function Start()
{
var connStrn : String = "URI=file:SqliteTest.db";
var dbcon : SqliteConnection = new SqliteConnection(connStrn);
dbcon.Open();
GetTable(dbcon);
//GetLatest(dbcon);
dbcon.Close();
dbcon = null;
}
function Update ()
{
var connStrn : String = "URI=file:SqliteTest.db";
var dbcon : SqliteConnection = new SqliteConnection(connStrn);
dbcon.Open();
var rowCount : int = GetRowCount(dbcon);
//DestroyPoints();
if((rowCount != transform.childCount - 3) || NeedUpdate(dbcon))
{
DestroyPoints();
//graphClone = Instantiate(transform,Vector3.zero,Quaternion.identity);
GetTable(dbcon);
}
//Debug.Log("children: " + transform.childCount);
dbcon.Close();
dbcon = null;
}
function GetRowCount(dbcon) : int
{
var dbcmd : Object = dbcon.CreateCommand();
dbcmd.CommandText = "SELECT count(*) as count FROM points";
var reader : Object;
try
{
reader = dbcmd.ExecuteReader();
}catch(err)
{
Debug.LogError("cmd failed:" + err.Message);
}
reader.Read();
var rowCount : int = reader.GetInt32(0);
reader.Close();
reader = null;
//dbcmd.Close();
//dbcmd = null;
return rowCount;
}
private var lastUpdated : DateTime;
//If no update needed, don't redraw everything
//Format 2008-01-30 15:38:41
// 0123,56,89,1112,1415,1718
//DateTime(Int32, Int32, Int32, Int32, Int32, Int32) constructor
function NeedUpdate(dbcon) : Boolean
{
var latest = false;
var dbcmd : Object = dbcon.CreateCommand();
dbcmd.CommandText = "SELECT timeMod FROM points ORDER BY timeMod DESC LIMIT 1;";
var reader : Object = dbcmd.ExecuteReader();
reader.Read();
var timeMod = reader.GetString(0);
//Debug.Log("timeMod: " + timeMod);
var tempYear = parseInt(timeMod[0].ToString()+timeMod[1].ToString()+timeMod[2].ToString()+timeMod[3].ToString());
var tempMonth = parseInt(timeMod[5].ToString()+timeMod[6].ToString());
var tempDay = parseInt(timeMod[8].ToString()+timeMod[9].ToString());
var tempHour = parseInt(timeMod[11].ToString()+timeMod[12].ToString());
var tempMinute = parseInt(timeMod[14].ToString()+timeMod[15].ToString());
var tempSecond = parseInt(timeMod[17].ToString()+timeMod[18].ToString());
var sqlUpdate = new DateTime(tempYear,tempMonth,tempDay,tempHour,tempMinute,tempSecond);
if(DateTime.Compare(sqlUpdate,lastUpdated) > 0) //sqlUpdate > lastUpdated
{
lastUpdated = sqlUpdate; //new DateTime(tempYear,tempMonth,tempDay,tempHour,tempMinute,tempSecond);
latest = true;
}
//var timeInt : int = parseInt(timeMod);
//var timeDivided : Array = timeMod.Substring(" ");
//var timeDate : Array = timeDivided[0].split("-");
//var timeTime : Array = timeDivided[1].split(":");
//Debug.Log(timeDivided.ToString());// + timeDate.ToString() + timeTime.ToString());
//pareInt
reader.Close();
reader = null;
//dbcmd.Close();
//dbcmd = null;
return latest;
}
function GetTable(dbcon) : void
{
var dbcmd : Object = dbcon.CreateCommand();
dbcmd.CommandText = "SELECT * FROM points;";
var reader : Object = dbcmd.ExecuteReader();
while(reader.Read())
{
var pos : Vector3 = new Vector3(reader.GetFloat(2),reader.GetFloat(3),reader.GetFloat(4));
var scale : Vector3 = new Vector3(reader.GetFloat(5),reader.GetFloat(5),reader.GetFloat(5));
var colour : Color = new Color(reader.GetFloat(6),reader.GetFloat(7),reader.GetFloat(8),1);
var newChild : Transform = Instantiate(pointObject,Vector3.zero,Quaternion.identity);
newChild.parent = transform;
newChild.localPosition = pos;
newChild.localScale = scale;
newChild.renderer.material.color = colour;
childArray.Push(newChild);
//result += reader.GetInt32(0) + " " + reader.GetString(1) + " " + reader.GetString(2) + ", ";
//http://msdn2.microsoft.com/en-us/library/system.data.idatareader_methods.aspx
}
reader.Close();
reader = null;
//dbcmd.Close();
//dbcmd = null;
}
//Erase them all so we can draw all the new ones and we won't get duplicates
function DestroyPoints() : void
{
//Destroy(graphClone);
//Can't remove Transform because MeshFilter, SphereCollider, MeshRenderer depend on it!
//var points = GetComponentsInChildren(typeof(pointObject));
while(childArray.length > 0)
//for(var child : Object in points)
{
var child = childArray.Pop();
var meshFilters = child.GetComponentsInChildren (MeshFilter);
for (var meshF : MeshFilter in meshFilters) {
DestroyImmediate(meshF);
}
var sphereColliders = child.GetComponentsInChildren (SphereCollider);
for (var sphere : SphereCollider in sphereColliders) {
DestroyImmediate(sphere);
}
var meshRenderers = child.GetComponentsInChildren (MeshRenderer);
for (var meshR : MeshRenderer in meshRenderers) {
DestroyImmediate(meshR);
}
DestroyImmediate(child);
//Debug.Log("Children after destroy: " + transform.childCount);
}
}
Change the URI to point to your SQLite DB.
var connStrn : String = "URI=file:SqliteTest.db";
Drag this script from the Project view onto the 3DGraph game object. All that is left is to create a point object. Lets just use a sphere, so create a new sphere object, call it GraphPoint, click on 3DGraph and drag GraphPoint onto the Point Object under the script settings. You should disable the source point object so its not confused with the ones created by the script. That should be all you need. The graph should plot the points in 3D space! Yay!
To make it more appealing, we added some controls, like zooming and flying the camera around with an XBOX360 controller. Maybe I will do that in Part 2, after I get back from Hawaii!!
