aNhjd4da

1# モデルがこれだけで作れたら素敵じゃないか
2# entry.coffee
3Database = require('libs/database')
4class Entry extends Database
5  initialize:()->
6    @property 'title', 'text'
7    @property 'body', 'text'
8    super
9module.exports = new Entry('entries')
10
11# モデルを扱う方法も簡単
12# hoge.coffee
13Entry = require('models/entry')
14entry = Entry.find_by_id(1)
15entries = Entry.all
16
17new_entry = Entry.create()
18new_entry.title = "I wish I was a fisherman"
19new_entry.body = "Whew!"
20new_entry.save()
21
22entry.title = 'I wish I was a brakeman'
23entry.update()
24
25for ent in entries
26  ent.destroy()
27
28# 裏ではこんなことをやっている
29# database.coffee
30
31class Database
32  constructor:(table_name, auto_increment)->
33    @table_name = table_name
34    @properties = []
35    @columns = []
36    @auto_increment = auto_increment
37    if auto_increment
38      @auto_increment = false
39    else
40      @auto_increment = true
41      @properties.push {name:'id', type:'INTEGER PRIMARY KEY AUTOINCREMENT'}
42      @columns.push 'id'
43    @properties.push {name:'created_at', type:'datetime'}
44    @properties.push {name:'updated_at', type:'datetime'}
45    @columns.push 'created_at'
46    @columns.push 'updated_at'
47    if Ti.Platform.osname == 'android'
48      @db = Ti.Database.install('/myapp.db', 'my_app')
49    else
50      @db = Ti.Database.install('myapp.db', 'my_app')
51    @initialize()
52
53  property:(name, type)->
54    if @auto_increment && name == 'id'
55      return
56    if name != 'updated_at' && name != 'created_at'
57      @properties.push {name:name, type:type}
58      @columns.push name
59
60  initialize:()->
61    # table properties are set in descendant class here
62    @init()
63
64  init:()->
65    sql = "CREATE TABLE IF NOT EXISTS " + @table_name + " ("
66    for data, i in @properties
67      table_name = data.name
68      table_type = data.type
69      add = table_name + " " + table_type
70      sql += add if i == 0
71      sql += "," + add if i > 0
72    sql += ");"
73
74    @db.execute sql
75
76  transaction:(fn)->
77    # 使えるのこれ？
78    if Ti.Platform.osname != 'android'
79      @db.execute "BEGIN"
80    res = fn()
81    if res && Ti.Platform.osname != 'android'
82      @db.execute "COMMIT"
83    else if Ti.Platform.osname != 'android'
84      @db.execute "ROLLBACK"
85
86  create:(data)->
87    self = new Object()
88    for name in @columns
89      self[name] = data[name]
90    self.save = ()=>
91      params = new Object()
92      for p in @columns
93        params[p] = self[p]
94      @save params
95    self
96
97  save:(params)->
98    params.created_at = '' if !params.created_at
99    params.updated_at = '' if !params.updated_at
100    
101    values = []
102    columns = []
103    data = []
104    for name, id in @columns
105      columns.push name
106      continue if @auto_increment && name == 'id'
107      if name == 'created_at' or name == 'updated_at'
108        values.push "datetime('now', 'localtime')"
109      else
110        values.push '?'
111        if params[name]
112          data.push params[name]
113        else
114          data.push null
115
116    sql = "INSERT INTO " + @table_name + "(" + columns.join(',') + ") VALUES (" + values.join(',') + ");"
117    return @db.execute(sql, data)
118
119  read:(sql, data)->
120    if Ti.Platform.osname != 'android'
121      @db.execute "BEGIN"
122
123    if data
124      resultSet = @db.execute sql, data
125    else
126      resultSet = @db.execute sql
127    results = []
128    while resultSet.isValidRow()
129      result = {}
130      for name in @columns
131        result[name] = resultSet.fieldByName name
132      result.save = @_update(result)
133      result.destroy = @_destroy(result)
134      results.push result
135      resultSet.next()
136    resultSet.close()
137    if Ti.Platform.osname != 'android'
138      @db.execute "COMMIT"
139    results
140    
141  find:(params)->
142    sql = "SELECT * FROM " + @table_name
143    keys = []
144    vals = []
145    orderby = ""
146    for key, value of params
147      if key == 'order'
148        orderby = " ORDER BY " + value
149      else
150        keys.push key
151        vals.push value
152
153    if keys.length > 0
154      sql += " WHERE "
155      wheres = []
156      for key in keys
157        wheres.push key + " = ?"
158      sql += wheres.join(',')
159    sql += orderby
160    return @read(sql, vals)
161
162  first:()->
163    sql = "SELECT * FROM " + @table_name + " ORDER BY id DESC LIMIT 1"
164    data = @read(sql)
165    return data[0]
166
167  last:()->
168    sql = "SELECT * FROM " + @table_name + " ORDER BY id ASC LIMIT 1"
169    data = @read(sql)
170    return data[0]
171
172  all:()->
173    sql = "SELECT * FROM " + @table_name + " ORDER BY id DESC"
174    return @read(sql)
175
176  find_by_id:(id)->
177    sql = "SELECT * FROM " + @table_name + " WHERE id = ?"
178    data = @read(sql, id)
179    return data[0]
180
181  create_if_not_exist:(params)->
182    data = @find_by_id(params.id)
183    if data
184      return true
185    else
186      return @save(params)
187
188  update:(params)->
189    params.updated_at = '' if !params.updated_at
190    sql = "UPDATE " + @table_name + " "
191    setter = []
192    values = []
193    for key, value of params
194      if key == 'id'
195        # don't never update your primary key
196        id = value
197        continue
198      else if key == 'updated_at'
199        setter.push key + " = datetime('now', 'localtime')"
200      else
201        setter.push key + " = ?"
202        values.push value
203    values.push id
204    sql += " SET " + setter.join(',')
205    sql += " WHERE id = ?"
206    return @db.execute(sql, values)
207
208  destroy:(params)->
209    sql = "DELETE FROM " + @table_name + " WHERE id = ?"
210    return @db.execute(sql, params.id)
211
212  _update:(data)->
213    self = data
214    return ()=>
215      params = new Object()
216      for p in @columns
217        params[p] = self[p]
218      @update params
219      return
220
221  _destroy:(data)->
222    self = data
223    return ()=>
224      @destroy(data)
225      return
226
227 module.exports = Database