ソースコード （AirDB.v010.zip をダウンロード）
/**
* sqlite lib
* AIR sqlite 暫定ライブラリ
*
* @author mizugame
* @version 0.1.0 (2008-06-02)
* @license GPL
*/
var AirDB = (function(){
var regList = {
rmLastCamma : /,$/,
escWord : /\\(\w{1})/g
},
DB = function()
{
this.dbo = new air.SQLConnection();
this.reg = regList;
this.async = false;
this.sql = null;
this.result = null;
this.error = null;
},
pt = DB.prototype;
/**
* open database
* url : DBファイルパス
* [async : 非同期(true),同期(false)]
* [fn : callback function]
*/
pt.connect = function(uri, async, fn)
{
var self = this;
self.async = (async === true ? true : false),
path = air.File.applicationStorageDirectory.resolvePath(uri);
if (self.async === true) {
self.dbo.addEventListener(
air.SQLErrorEvent.ERROR, function(e){self.setError.call(self, statem, e)});
if (fn instanceof Function) self.dbo.addEventListener(air.SQLEvent.OPEN, fn);
}
self.dbo[self.async === true ? 'openAsync' : 'open'](path);
return self;
};
/**
* close database
*/
pt.close = function()
{
if (this.dbo != null) this.dbo.close();
};
/**
* escape paramater
* ※とりあえずのもの
* v : エスケープする文字列
*/
pt.escape = function(v)
{
return v.replace("\\", "\\\\")
.replace(this.reg.escWord, "\\\\$1")
.replace("'", "\'\'")
//.replace("\n", "\\n")
//.replace("\r", "\\r")
.replace("\x00", "\\x00")
.replace("\x1a", "\\x1a");
};
/**
* escape paramater(ワイルドカード[%,_]用)
* ※SQLStatement.textでWHERE句のバックスラッシュが
* 改行に変換されてしまうようなので、今のところ使い物にならない(一応残してるだけ)
* v : Parameter that wants to escape
*/
pt.escapePattern = function(v)
{
return v.replace("%", "\\%")
.replace("_", "\\_");
};
/**
* create SQLStatement
*/
pt.statement = function()
{
var self = this,
statem = new air.SQLStatement();
statem.sqlConnection = self.dbo;
statem.addEventListener(
air.SQLEvent.RESULT, function(){self.setResult.call(self, statem)});
if (self.async === true) statem.addEventListener(
air.SQLErrorEvent.ERROR, function(e){self.setError.call(self, statem, e)});
return statem;
};
/**
* execute SQLStatement
* sql : 実行するsql文
* [statem : SQLStatement]
* [fetchNum : prefetch number(結果がある場合、先読みする件数を整数で指定。-1はすべて)]
* [statem : reply object(応答関数)]
*/
pt.query = function(sql, statem, fetchNum, fn)
{
var self = this,
statem = statem || self.statement();
statem.text = sql;
statem.execute(fetchNum||-1, fn||null);
return statem;
};
/**
* create Table
* table : テーブル名
* cols : Object{column name : cols type}
*/
pt.create = function(table, cols)
{
var self = this,
col = new String();
for (i in cols) col += i + " " + cols[i] + ",";
return self.query(
"CREATE TABLE IF NOT EXISTS " +
table +
" (" + col.replace(self.reg.rmLastCamma, "") + ")"
);
};
/**
* execute select query
* table : テーブル名
* cols : String or Array[column name]
* [option : SELECT文用の絞込み文法など(Where句、Order句...)]
*/
pt.select = function(table, cols, option)
{
var self = this,
col = new String();
if (typeof cols == 'string') {
col = cols;
} else if (cols instanceof Array) {
for (var i=0; i<cols.length; i++) col += cols[i] + ",";
}
return self.query(
"SELECT" +
" " + col.replace(self.reg.rmLastCamma, "") + " " +
"FROM " +
table +
(typeof option == 'string' ? " " + option : "")
);
};
/**
* execute insert query
* table : テーブル名
* data : Object{column name : insert data}
*/
pt.insert = function(table, data)
{
var self = this,
field = new String(),
value = new String(),
statem = self.statement();
for (i in data) {
field += i + ",";
value += ":" + i + ",";
self.paramater(i, data[i], statem);
}
return self.query(
"INSERT INTO " +
table +
" (" + field.replace(self.reg.rmLastCamma, "") + ")" +
" VALUES " +
" (" + value.replace(self.reg.rmLastCamma, "") + ")",
statem
);
};
/**
* execute update query
* table : テーブル名
* data : Object{column name : update data}
* [option : UPDATE文用の絞込み文法など(Where句、Order句...)]
*/
pt.update = function(table, data, option)
{
var self = this,
set = new String(),
statem = self.statement();
for (i in data) {
set += i + "=:" + i + ",";
self.paramater(i, data[i], statem);
}
return self.query(
"UPDATE " +
table +
" SET " +
set.replace(self.reg.rmLastCamma, "") +
(typeof option == 'string' ? " " + option : ""),
statem
);
};
/**
* execute delete query
* table : テーブル名
* [option : DELETE文用の絞込み文法など(Where句、Order句...)]
*/
pt.Delete = function(table, option)
{
var self = this;
return self.query(
"DELETE FROM " +
table +
(typeof option == 'string' ? " " + option : "")
);
};
/**
* execute prepared query
* sql : 実行するsql文
* param : Object{param name : param value}
* [statem : SQLStatement Object]
*/
pt.prepared = function(sql, param, statem)
{
var self = this,
statem = statem || self.statement();
if (typeof param == 'object') {
for (i in param) self.paramater(i, param[i], statem);
statem = self.query(sql, statem);
}
return statem;
};
/**
* add paramater
* name : param name
* value : param value
* statem : SQLStatement Object
*/
pt.paramater = function(name, value, statem)
{
statem.parameters[":" + name] = value;
};
/**
* return DataBace Object
*/
pt.getDB = function()
{
return this.dbo;
};
/**
* return request(直近の実行結果)
*/
pt.getResult = function()
{
return this.result;
};
/**
* return a data
*/
pt.getOne = function()
{
return this.result != null ? this.result.data[0] || null : null;
};
/**
* return all data
*/
pt.getRows = function()
{
return this.result != null ? this.result.data || null : null;
};
/**
* return error message(直近のエラーメッセージ)
*/
pt.getError = function()
{
return this.error.error;
};
/**
* return sql query(直近の実行されたSQL文)
*/
pt.getSql = function()
{
return this.sql;
};
/**
* save error comment
*/
pt.setError = function(statem, e)
{
var self = this;
self.sql = statem.text;
self.error = e;
};
/**
* save result
*/
pt.setResult = function(statem)
{
var self = this;
self.sql = statem.text;
self.result = statem.getResult();
};
/**
* begin
*/
pt.begin = function()
{
this.dbo.begin();
};
/**
* commit
*/
pt.commit = function()
{
this.dbo.commit();
};
/**
* rollback
*/
pt.rollback = function()
{
this.dbo.rollback();
};
/**
* alias (短い名前の関数エイリアス。不要なら削除)
*/
pt.con = pt.connect;
pt.clo = pt.close;
pt.esc = pt.escape;
pt.escP = pt.escapePattern;
pt.state = pt.statement;
pt.q = pt.query;
pt.cre = pt.create;
pt.sel = pt.select;
pt.ins = pt.insert;
pt.upd = pt.update;
pt.del = pt.Delete;
pt.pre = pt.prepared;
pt.param = pt.paramater;
//return new DB;
return DB;
})();
記事に戻る