<?xml version="1.0" encoding="utf-8"?>
<mx:WindowedApplication xmlns:mx="http://www.adobe.com/2006/mxml" layout="absolute" initialize="init()">
	<mx:Script>
		<![CDATA[
			import mx.utils.ObjectUtil;
 
			private namespace safe;			
			private namespace normal;
 
			private function init():void
			{
				use namespace safe;
				//use namespace normal; //如果把 namespace 换成 normal 在 mac 上会造成 crash
 
				var dbFile:File = new File("app-storage:/test.db");
				if(dbFile.exists)
				{
					dbFile.deleteFile();
				}
				var connection:SQLConnection = new SQLConnection();
				connection.open(dbFile);
 
				connection.begin();
				executeSql(connection,"CREATE TABLE foo (id INTEGER PRIMARY KEY AUTOINCREMENT, bar0 OBJECT NOT NULL, bar1 OBJECT NOT NULL);");
				connection.commit();
 
				//喂点复杂的 object graph 给 sqlite...
				var obj0:Object = {prop0:"blah"};
				var obj1:Object = {prop0:obj0, prop1:"test"};
				var obj2:Object = {prop0:obj1, prop2: obj0};
 
				connection.begin();
				executeSql(connection,"INSERT INTO foo (bar0, bar1) VALUES(:bar0, :bar1);",{bar0:obj1,bar1:obj2});
 
				for(var i:int=0;i<1000;i++)
				{
					obj0.prop0=Math.random();
					executeSql(connection,"UPDATE foo SET bar0=:bar0, bar1=:bar1;",{bar0:obj1,bar1:obj2});
					executeSql(connection,"SELECT * FROM foo");
				}
 
				connection.commit();
 
			}
 
			normal function executeSql(connection:SQLConnection,str:String,param:Object=null,mapping:Class=null):SQLResult
			{
				var s:SQLStatement = new SQLStatement();
			    s.sqlConnection = connection;
			    s.text = str;			    
			    if(param!=null)
			    {
			    	for(var k:String in param)
			     	{
			     		var p:Object = param[k];
			      		s.parameters[":"+k] = p;
			       	}
			    }			    
			    if(mapping!=null)
			    {
			      	s.itemClass = mapping;
			    }
			    s.execute();			    
			    return s.getResult();
			}
 
			safe function executeSql(connection:SQLConnection,str:String,param:Object=null,mapping:Class=null):SQLResult
			{
 
				var s:SQLStatement = new SQLStatement();
			    s.sqlConnection = connection;
			    s.text = str;
 
			    if(param!=null)
			    {
			    	for(var k:String in param)
			     	{
			     		var p:Object = param[k];
			     		if(!isPlainType(p))
			     		{//将 复杂 Object 先 write 到 byteArray 里
			     			p = objectToByteArray(p);
			     		}
			      		s.parameters[":"+k] = p;
			       	}
			    }
 
			    if(mapping!=null)
			    {
			      	s.itemClass = mapping;
			    }
			    s.execute();
 
			    var result:SQLResult = s.getResult();
			    if(result.data!=null)
			    {
				    var c:int = result.data.length;
					for(var i:int=0;i<c;i++)
					{
						var row:Object = result.data[i];
						for(var j:String in row)
						{
							var val:Object = row[j];
							if(val is ByteArray)
							{// 还原 byteArray 里面的 object
								val = objectFromByteArray(val as ByteArray);
								row[j]=val;
							}
						}
					}
			    }
			    return result;
			}
 
			private function isPlainType(obj:Object):Boolean
			{
				var type:String = typeof(obj);
				switch (type)
				{
					case "number":
					case "string":
					case "boolean":
					{
						return true;
					}
					case "object":
					{
					    return (obj is Date);
					}
				}
				return false;
			}
 
			private function objectToByteArray(obj:Object):ByteArray
			{
				var bytes:ByteArray = new ByteArray();
				bytes.writeObject(obj);
				bytes.position=0;
				return bytes;
			}
 
			private function objectFromByteArray(bytes:ByteArray):Object
			{
				return bytes.readObject();
			}
		]]>
	</mx:Script>
</mx:WindowedApplication>