圣叹@游戏开发

提问：下面的写法有漏洞么？
[codes=java]
//method:

//execute a SQL SELECT command
//"SELECT bid, title FROM draft ... "
//or "SELECT  bid, title,  deleteAble, editAble FROM draft ... "
var result:SQLResult = execute(query, data, prefetch);
if(!result || !result.data)
{
  return null;
}
var dataArray:Vector. = new Vector.();    
for each(var obj:Object in result.data)
{
  var _data:BlogDraftVO = new BlogDraftVO();
  _data.bid= obj["bid"];
  _data.title= obj["title"];
  if(obj["deleteAble"])
    _data.deleteAble = obj["deleteAble"];
  if(obj["editAble"])
    _data.editAble= obj["editAble"];
  //...    
  dataArray.push(_data);
}
//...

//V-O struct example
class BlogDraftVO
{
public var deleteAble:Boolean = true;
public var editAble:Boolean = true;
public var bid:int;
public var title:String;
//...
}
[/codes]
错误很不明显。

类别：Program | Tags: as3 , object , hasownproperty , sql , select 评论(7) 阅读(1909)

Flex SDK 3.2.0包含了对AIR 1.5和Flash Player10的支持，但是SDK 3.2.0中的组件使用泛型(Vector)时会出错。如下例：

Array类型显示正常，Vector是调用toString后的结果。使用了泛型后，显示到Flex组件里时必须转换成数组，而且针对每一个Vector都要写这样一个转换函数。因为，假设这样写：

编译器将会直接报类型错误，于是不得不改为

这...值得么？所以，Vector是好药，但无法包治百病，得善用。

  整个Myspace China目前只有我一个Flash Develoer，除了承担繁重的产品开发项目之外，受到以善变著称的营销团队的骚扰也不可避免，但更痛苦。这个Team往往连最终需求都还没确定下来，就定下了上线日期——有可能是3天，也有可能是1个星期。总之是十分不靠谱的需求，变化莫测——因为拍板权在客户手里。
  在被骚扰的不得了之后，这些没啥技术含量的小玩意在产品可扩展性、可维护性上做了大文章。除去基础的软件工程里的那些古董，这次索爱的项目俺做了一个大胆的尝试：使用配置文件统一配置所有市场活动类项目。
  当然，假如是纯粹的HTML，这样做无任何意义，将一些参数写进SWF Object里，或者干脆hard code，作为常量定义在源文件里，以后有变动直接改代码，重新编译即可。但当你和后台代码联系起来，你就发现这是一个十分愚蠢的想法，尤其是.NET程序。因为每当前端HTML发生变动，都要通知.NET Team的人修改对应代码，然后测试，然后提交代码，然后部署...OMG，抓狂了。
  于是便有了针对每个项目的配置文件。但是这一次，我把所有的配置文件统一到了一起，不然太多太乱。比如：

这里是针对索爱漂流瓶活动的配置，针对活动的接口地址、一些效果的参数配置。如果以后有新的活动，增加新的结点即可。除去方便统一管理配置文件外，所有项目都调用这一个接口也会降低服务器负担。

使用数据访问对象(Data Access Object DAO)设计模式可以将低级别的数据访问逻辑与高级别的业务逻辑分离。实现 DAO 模式涉及比编写数据访问代码更多的内容。在核心J2EE模式中是这样介绍DAO模式的：为了建立一个健壮的J2EE应用，应该将所有对数据源的访问操作抽象封装在一个公共API中。用程序设计的语言来说，就是建立一个接口，接口中定义了此应用程序中将会用到的所有事务方法。在这个应用程序中，当需要和数据源进行交互的时候则使用这个接口，并且编写一个单独的类来实现这个接口在逻辑上对应这个特定的数据存储。


DAO pattern

一个典型的 DAO 实现有以下组件：
1、一个 DAO 工厂类
2、一个 DAO 接口
3、一个实现了 DAO 接口的具体类
4、数据传输对象(有时称为值对象)

示例：
[codes=java]
/**
* Abstract class DAO Factory
* Author: Qizhi Zhang, http://www.moorwind.com
*/
package reader.dao
{
  import reader.ReaderError;
  
  public class DAOFactory
  {
    //////////////////////////////////////////////////////////////////////
    //Public properties
    
    // List of DAO types supported by the factory
    public static const SQLITE:int = 1;
    
    // There will be a method for each DAO that can be created. The concrete factories will have to implement these methods.
    
    public function get accountDAO():IAccountDAO
    {
      throw new ReaderError(ReaderError.ABSTRACT_ERROR);
    }
    
    public function get blogDAO():IBlogDAO
    {
      throw new ReaderError(ReaderError.ABSTRACT_ERROR);
    }
    //////////////////////////////////////////////////////////////////////
    //methods
    
    public static function getDAOFactory(whichFactory:int):DAOFactory
    {
      switch (whichFactory)
      {
        case SQLITE:
          return new SqliteDAOFactory();
        default :
          return null;
      }
    }

  }
}

/**
*Sqlite concrete DAO Factory implementation
* Author: Qizhi Zhang, http://www.moorwind.com
*/
package reader.dao
{
  import flash.data.SQLConnection;
  import flash.filesystem.File;
  
  public class SqliteDAOFactory extends DAOFactory
  {
    //////////////////////////////////////////////////////////////////////
    //public properties
    
    public static const BD_NAME:String = "DataCache.db";
    
    public var connection:SQLConnection;
    
    //////////////////////////////////////////////////////////////////////
    //Constructor
    public function SqliteDAOFactory()
    {
      super();
      this.connection = creatConnection();
    }
    
    //////////////////////////////////////////////////////////////////////
    //methods
    
    public static function creatConnection():SQLConnection
    {
      var file:File = File.applicationDirectory.resolvePath(BD_NAME);
      if(file.exists)
      {
        var conn:SQLConnection = new SQLConnection();
        conn.open(file);
        return conn;
      }
      return null;
    }
    
    override public function get accountDAO():IAccountDAO
    {
      return new AccountDAO(connection);
    }
    
    override public function get blogDAO():IBlogDAO
    {
      return new BlogDAO(connection);
    }
  }
}

/**
* Interface that all AccountDAOs must support
* Author: Qizhi Zhang, http://www.moorwind.com
*/
package reader.dao
{
  import reader.model.vo.UserVO;
  
  public interface IAccountDAO
  {
    function insertUser(user:UserVO):int;
    function updateUser(user:UserVO):Boolean;
    function deleteUser(user:UserVO):Boolean;
    function selectUser(userid:int):UserVO;
  }
}

/**
*AccountDAO implementation of the IAccountDAO interface. This class can contain all
*SQLite specific code and SQL statements.
* The client is thus shielded from knowing these implementation details.
* Author: Qizhi Zhang, http://www.moorwind.com
*/
package reader.dao
{
  import flash.data.SQLConnection;
  import flash.data.SQLResult;
  
  import reader.model.vo.UserVO;
  
  public class AccountDAO extends BaseDAO implements IAccountDAO
  {
    //////////////////////////////////////////////////////////////////////
    //SQLStatement queries
    
    public static const INSERT_USER:String = "INSERT INTO user (userName, email) VALUES(:userName, :email)";
    public static const UPDATE_USER:String = "UPDATE user SET email=:email WHERE userId=:userId";
    public static const DELETE_USER:String = "DELETE FROM user WHERE userId=:userId";
    public static const SELETE_USER:String = "SELECT * FROM user WHERE userId=:userId";
        
    //////////////////////////////////////////////////////////////////////
    //constructor
    public function AccountDAO(connection:SQLConnection)
    {
      super(connection);
    }
    
    //////////////////////////////////////////////////////////////////////
    //methods
    
    public function insertUser(user:UserVO):int
    {
      // TODO: Implement insertUser here.
    }
    
    public function updateUser(user:UserVO):Boolean
    {
      // TODO: Implement updateUser here.
    }
    
    public function deleteUser(user:UserVO):Boolean
    {
      // TODO: Implement deleteUser here.
    }
    
    public function selectUser(userid:int):UserVO
    {
      // TODO: Implement selectUser here.
    }
    
  }
}

/**
*User Transfer Object
* Author: Qizhi Zhang, http://www.moorwind.com
*/
package reader.model.vo
{
  [Bindable]
  public class UserVO
  {
    public var userId:int = -1;    
    public var userName:String;
    public var password:String;
    public var email:String;    
    // getter and setter methods...
  }
}
[/codes]

关于 DAO 要记住的重要一点是它们是事务性对象。由 DAO 所执行的每一个操作 -- 如创建、更新或者删除数据 -- 都与一个事务相关联。在 DAO 中有两种主要的界定事务的策略。一种方式是让 DAO 负责界定事务，另一种将事务界定交给调用这个 DAO 方法的对象处理。
比如下面将事务代码嵌入到 DAO 中：

如果将事务界定交给调用这个 DAO 方法的对象处理，这种事务界定策略对于需要在一个事务中访问多个 DAO 的应用程序特别有用：


实际在操作SQLite 的 transaction 时，每一条SQLStatement语句必须独立创建，否则会抛异常。

1、程序总是报错“Operation cannot be performed while SQLStatement.executing is true”，没错，当使用asynchronous方式打开SQLLite时，抱这个错太多太流氓到让人抓狂的地步。
2、使用asynchronous链接SQLLite使用INSERT语句，插入到一个表a时要先插入另一个表b，将该数据的b.id一类的属性插入到表a中，asynchronous方式表示“很无奈，第一步你要XX，第二步你才能OO”。
无奈之下Google之，果然有大牛在开发中遇到了这俩common situations，写了篇长篇大论，然后跑出了解决方案：ConnectionPool和StatementPool。

下面是文章中比较有用的几段话[Allurent Desktop Connection's database pool]：

Lastly, only one database operation can be executing on a SQLConnection at a time. If both long-running and short-running database operations will occur that are independent of each other, it is possible to have multiple connections open to the same database. In order to take advantage of multiple connections, you will need to use  asynchronous connections. One connection can handle the long-running database operations while another connection handles the short database operations. However, it is not recommended to use multiple connections if both read and write database operations will be used simultaneously. Attempting to update a database on one connection while reading on another will generate an exception. How the Allurent Desktop Connection [注：Allurent Desktop Connection 为这家公司的产品名，作者Daniel Rinehart是Allurent 的架构师] used this multiple connection approach is described in the next section.

The Allurent Desktop Connection bundled a large database with the application to support client-side keyword and color searching. Additionally this database included detailed information on all products, including ones not shown in the primary navigation. A key aspect of the application was to provide as rich and engaging a user experience as possible while providing access to all of the information in the bundled database.

The use of panning and zooming as the primary navigation mechanism meant that using a synchronous connection would have impacted the user experience too much. When a user selects a product on the main strip, the application zooms in on the product before rendering detailed information about it. In the background while the zoom effect is playing, the application issues a database operation to load the full product information from the database. The effect that accomplishes the zooming was tuned to take slightly longer than it took to load the data from the database, so the user is visually occupied while the data is loading.

To minimize the burden of working with the asynchronous APIs we developed two utility classes. The first is the ConnectionPool class. This class handles opening one or more connections to the same database and executing database operations whenever a connection is available. In our application we found that we frequently had a long-running database operation executing at the same time a short operation needed to execute. Instead of queueing all database operations on the same connection, by using multiple connections we enabled the system threads to execute both queries at the same time. The ConnectionPool class handles the bookkeeping of which connections are in use and which are free, and assigns pending database operations to the next available connection.

On top of the ConnectionPool we wanted to leverage the use of prepared statements for the performance gains over needing to parse the SQL query each time. Since a SQLStatement is bound to a particular connection, managing which SQLStatement to use based on the connection returned from the ConnectionPool was cumbersome. The StatementPool was developed to encapsulate the logic of caching and reusing a SQLStatement based on the connection it was associated with.

These two utility classes provided a simplified view to the rest of the application. A typical data load operation was reduced to specifying the query to run, the parameters for the query, and the function the SQLResult should be passed to. This technique is used by the PoolExample class in the sample application. All the performance benefits of multiple connections and cached prepared SQLStatements were hidden from the rest of the application.

原文：http://www.adobe.com/devnet/air/flex/articles/air_sql_operations.html
示例：http://download.macromedia.com/pub/developer/air/SQLite_operations.zip