Created
August 18, 2013 11:56
-
-
Save uzulla/6261282 to your computer and use it in GitHub Desktop.
以前スマホ向けアプリの案件で使った(といっても結局お蔵入りになった)、Web Sql Database をあつかうLibrary、Coffee Scriptです。
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
### | |
CFEWebDb | |
idがPkey | |
updated_at | |
created_atは必須 | |
トランザクションには非対応 | |
Ver 1.0 20111206 | |
とりあえずできた。 | |
### | |
### | |
//schema 作成 sample | |
class DB_SETTING | |
DB_NAME: 'testdatabase' | |
DB_VERSION: '1' | |
DB_SIZE: 1*1024*1024; | |
@DB_SETTING = DB_SETTING # END OF CLASS | |
class Post extends CFEWebDb | |
TABLE_NAME: 'post' | |
SCHEMA: [ | |
'id INTEGER PRIMARY KEY' | |
'title TEXT NOT NULL' | |
'text TEXT NOT NULL' | |
'updated_at DATETIME NOT NULL' | |
'created_at DATETIME NOT NULL' | |
] | |
@test = new Post | |
### | |
class CFEWebDb | |
DB_NAME: DB_SETTING::DB_NAME | |
DB_VERSION: DB_SETTING::DB_VERSION | |
DB_SIZE: DB_SETTING::DB_SIZE | |
#TABLE_NAME: ''# please over ride | |
#SCHEMA: [ # please over ride | |
#] | |
COLS: null#[] | |
schema_text: '' | |
cols_text: '' | |
db: null | |
do_queue: null#[] | |
ready:false | |
dump: (limit=0)-> | |
@ | |
.get() | |
.order('id', 'DESC') | |
.success( (rows, tx, rs)-> | |
console.log rows, tx, rs | |
) | |
if limit > 0 | |
@.limit(limit) | |
@.exec() | |
constructor: ()-> | |
@COLS = [] | |
@do_queue= [] | |
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 @ | |
sql: (sql, params, mode='SELECT') -> | |
@do_queue.push({ | |
sql: sql | |
params: params | |
mode: mode | |
}) | |
return @ | |
setRows: (rows) -> | |
for row in rows | |
id = row.id ? null | |
delete row.id if row.id? | |
@set(id, row) | |
return @ | |
set: (id, values) -> | |
cols = [] | |
vals = [] | |
pvals = [] | |
if id? | |
delete values.id if values.id? | |
values.updated_at = new moment().format('YYYY/MM/DD HH:mm:ss') if !(values.updated_at?) | |
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 | |
values.created_at = new moment().format('YYYY/MM/DD HH:mm:ss') | |
values.updated_at = new moment().format('YYYY/MM/DD HH:mm:ss') | |
for k,v of values | |
cols.push("'#{k}'") | |
vals.push(v) | |
pvals.push("?") | |
cols_str = cols.join(',') | |
pvals_str = pvals.join(',') | |
next_sql = "INSERT INTO #{@TABLE_NAME} ( #{cols_str} ) VALUES ( #{pvals_str} )" | |
params = vals | |
@do_queue.push({ | |
sql: next_sql | |
params: params | |
mode: 'INSERT' | |
}) | |
return @ | |
order: (col, order="") -> | |
@do_queue[@do_queue.length-1].order = " ORDER BY #{col} #{order} " | |
return @ | |
limit: (limit_num)-> | |
@do_queue[@do_queue.length-1].limit = " LIMIT #{limit_num} " | |
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 "CFEDWebDb Exec this:",do_one | |
sql = "#{do_one.sql} " | |
sql += "#{do_one.order} " if do_one.order? | |
sql += "#{do_one.limit} " if do_one.limit? | |
tx.executeSql( | |
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) -> | |
if do_one.fail? | |
do_one.fail(error) | |
else | |
console.log 'SQL EXEC ERROR' | |
console.log _this | |
console.log error | |
_this.clear() | |
do (_this) -> | |
() -> | |
do_one.allways?() | |
) | |
@clear() | |
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 'GOOD db check ok' | |
#for i in [0...rs.rows.length] | |
# row = rs.rows.item(i) | |
# console.log row | |
) | |
do (_this) -> | |
(error) -> | |
console.log 'NG db select fail' | |
_this.initTable() | |
() -> | |
console.log '==db check end==' | |
@ready = true | |
) | |
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 IF EXISTS #{_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' | |
) | |
@CFEWebDb = CFEWebDb # END OF CLASS | |
### | |
test.initDB() | |
INSERTのやり方(setの第一引数をnullに) | |
test | |
.set(null, {title:'post title', text:'blah blah blah' } ) | |
.success( function(rows, tx, rs){ console.log(rows, tx, rs) } ) | |
.exec() | |
UPDATEのやり方(setの第一引数を指定する) | |
test | |
.clear() | |
.set(1, {title:'new title', text:'hoge hoge hoge', 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() | |
.order('id', 'DESC') | |
.success( function(rows, tx, rs){ console.log(rows, tx, rs) } ) | |
.exec() | |
SELECT なにかで | |
test | |
.clear() | |
.get('title', 'new title') | |
.success( function(rows, tx, rs){ console.log(rows, tx, rs) } ) | |
.exec() | |
マルチインサート | |
rows = [ | |
{ name:'title 1', text:'ほげほげ1' }, | |
{ name:'title 2', text:'ほげほげ2' }, | |
{ name:'title 3', text:'ほげほげ3' }, | |
{ name:'title 4', text:'ほげほげ4' }, | |
{ name:'title 5', text:'ほげほげ5' }, | |
] | |
test | |
.setRows( rows ) | |
.allways( function(){ console.log('complete') } ) | |
.exec() | |
SQL直接 SELECT | |
test | |
.sql('SELECT id,title FROM post WHERE id>=? AND id<=?', [1,3]) | |
.success( function(rows){ console.log (rows) } ) | |
.exec() | |
SQL直接 ORDER LIMIT | |
test | |
.sql('SELECT id,title FROM post WHERE id>=? AND id<=?', [1,3]) | |
.success( function(rows){ console.log (rows) } ) | |
.order('id', "DESC") | |
.limit(1) | |
.sql('SELECT id,title FROM post WHERE id>=? AND id<=?', [1,3]) | |
.success( function(rows){ console.log (rows) } ) | |
.order('id') | |
.limit(2) | |
.exec() | |
SQL直接 UPDATE | |
test | |
.sql('UPDATE post SET title=? WHERE id=?', ['updating!', 9], "UPDATE") | |
.success( function(){ console.log ('complete') } ) | |
.exec() | |
SQL直接 INSERT | |
test | |
.sql('INSERT INTO list ("title", "text","updated_at","created_at") VALUES (?, ?, datetime(\'now\'),datetime(\'now\'))', ['inserting!', 'great post'], 'INSERT') | |
.success( function(id){ console.log ('complete, last id is:'+id) } ) | |
.exec() | |
### |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment