Web SQL DatabaseのWrapper的なもの書いてる
題名の通り、１日かかって、なんとなくうごくようになってきたので、作製中コードをちょっとのっけてみる。
こんなの
はてぶろはコードコピペにあんまりやさしくないよね…
class CFEChain
func_queue: []
index: -1
chain: (func) ->
@func_queue.push(func)
return @
chainExec: () ->
@index++
#console.log 'chainExec'
#console.log @index, @func_queue[@index]
@func_queue[@index]?.apply(this, arguments);
class DB_SETTING
DB_NAME: 'test_database'
DB_VERSION: '1'
DB_SIZE: 1*1024*1024;
#ここから本体
class List2
DB_NAME: DB_SETTING::DB_NAME
DB_VERSION: DB_SETTING::DB_VERSION
DB_SIZE: DB_SETTING::DB_SIZE
TABLE_NAME: 'list'
SCHEMA: [
'id INTEGER PRIMARY KEY'
'name TEXT NOT NULL'
'site_num INTEGER NOT NULL'
'updated_at DATETIME NOT NULL'
'created_at DATETIME NOT NULL'
]
COLS: []
schema_text: ''
cols_text: ''
db: null
do_queue: []
constructor: ()->
for col in @SCHEMA
@COLS.push( col.split(' ')[0] )
@schema_text = "(" + @SCHEMA.join(',') + ")"
@cols_text = "(" + @COLS.join(',') + ")"
@openDB()
@checkTable()
clear: ()->
@do_queue = []
return @
get: () ->
if arguments.length == 0 #all
@do_queue.push({
sql: "SELECT * FROM #{@TABLE_NAME}"
params: []
mode: 'SELECT'
})
else if arguments.length == 1 #get by id
@do_queue.push({
sql: "SELECT * FROM #{@TABLE_NAME} WHERE id = ?"
params: [arguments[0]]
mode: 'SELECT'
})
else if arguments.length == 2
@do_queue.push({
sql: "SELECT * FROM #{@TABLE_NAME} WHERE `#{arguments[0]}` = ?"
params: [arguments[1]]
mode: 'SELECT'
})
else
return #fail...
return @
set: (id, values) ->
cols = []
vals = []
pvals = []
if id?
for k,v of values
cols.push("'#{k}'=?")
vals.push(v)
vals.push(id)
cols_str = cols.join(',')
next_sql = "UPDATE #{@TABLE_NAME} set #{cols_str} WHERE id=?"
params = vals
@do_queue.push({
sql: next_sql
params: params
mode: 'UPDATE'
})
else
for k,v of values
cols.push("'#{k}'")
vals.push(v)
pvals.push("?")
console.log cols
cols_str = cols.join(',')
pvals_str = pvals.join(',')
next_sql = "INSERT INTO #{@TABLE_NAME} ( #{cols_str} ) VALUES ( #{pvals_str} )"
params = vals
console.log next_sql
console.log params
@do_queue.push({
sql: next_sql
params: params
mode: 'INSERT'
})
return @
success: (func) ->
@do_queue[@do_queue.length-1].success = func
return @
fail: (func) ->
@do_queue[@do_queue.length-1].fail = func
return @
allways: (func) ->
@do_queue[@do_queue.length-1].allways = func
return @
exec: () ->
_this = this
for do_one in @do_queue
@db.transaction(
do (_this, do_one) ->
return (tx) ->
console.log do_one
tx.executeSql(
do_one.sql
do_one.params
(tx, rs)->
#do_one.success?(tx, rs)
if do_one.mode is 'INSERT'
id = rs.insertId;
do_one.success(id, tx, rs)
else if do_one.mode is 'UPDATE'
do_one.success(tx, rs)
else
rows = []
for row,i in rs.rows
rows.push(rs.rows.item(i));
do_one.success(rows, tx, rs)
)
do (_this) ->
(error) ->
do_one.fail?(error)
do (_this) ->
() ->
do_one.allways?()
)
openDB: ->
@db = window.__db ? openDatabase(@DB_NAME, @DB_VERSION, "", @DB_SIZE)
window.__db ?= @db
checkTable: ->
_this = this
@db.transaction(
do (_this) ->
return (tx) ->
console.log 'db check start'
sql = "SELECT * FROM #{_this.TABLE_NAME}"
tx.executeSql(
sql
[]
(tx, rs)->
console.log 'db check ok'
for i in [0...rs.rows.length]
row = rs.rows.item(i)
console.log row
)
do (_this) ->
(error) ->
console.log 'db select fail'
_this.initTable()
() ->
console.log 'db check end'
)
initTable: ->
console.log 'db init start'
_this = this
@db.transaction(
do (_this)->
(tx) ->
c = new CFEChain
c.chain(
(c, _this, tx) ->
console.log sql1 = "DROP TABLE #{_this.TABLE_NAME};"
tx.executeSql( sql1, [], (tx, rs)->
console.log "SUCCESS: #{sql1}"
c.chainExec(c,_this, tx)
)
).chain(
(c, _this, tx) ->
console.log sql2 = "CREATE TABLE IF NOT EXISTS #{_this.TABLE_NAME} #{_this.schema_text};"
tx.executeSql( sql2, [], (tx, rs)->
console.log "SUCCESS: #{sql2}"
)
).chainExec(c, _this, tx)
(error) ->
console.log error
console.log 'db init fail'
->
console.log 'db init end'
)
@test = new List2
こんなふうに使うかも
以下使う時のサンプル
DB初期化（テーブル初期化、ただし、テーブルが存在していない場合には勝手に行われます）
test.initDB()
INSERTのやり方（setの第一引数をnullに）
test
.clear()
.set(null, {name:'sitename', site_num:1, updated_at:'2011/01/01 01:02:03', created_at:'2011/01/01 01:02:03' } )
.success( function(rows, tx, rs){ console.log(rows, tx, rs) } )
.exec()UPDATEのやり方（setの第一引数を指定する）
test
.clear()
.set(1, {name:'sitename2', site_num:1, updated_at:'2011/01/01 01:02:03', created_at:'2011/01/01 01:02:03' } )
.success( function(rows, tx, rs){ console.log(rows, tx, rs) } )
.exec()SELECT by pkey
test
.clear()
.get(1)
.success( function(rows, tx, rs){ console.log(rows, tx, rs) } )
.exec()SELECT all
test
.clear()
.get()
.success( function(rows, tx, rs){ console.log(rows, tx, rs) } )
.exec()SELECT なにかで
test
.clear()
.get('name', 'sitename2')
.success( function(rows, tx, rs){ console.log(rows, tx, rs) } )
.exec()
