以前スマホ向けアプリの案件で使った(といっても結局お蔵入りになった)、Web Sql Database をあつかうLibrary、Coffee Scriptです。

cfewebdb.coffee

###
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()

###