SqlLite Helper

SqlLite Helper

HTML5에서 제공되는 WEB DB인 SqlLite Helper Module을 아래 코드와 같이 작성해(간단히;;) 보았습니다.

1. // SqlLite DB Helper
2. var SqlLite = new (function () {
3.  
4.     var that = null;
5.     var SqlLite = function () {
6.  
7.         this.db = null;
8.         return this;
9.     }
10.  
11.     SqlLite.fn = SqlLite.prototype = {
12.         ready: function (name, callback, size, version, displayName) {
13.  
14.  
15.             if (!name) return false;
16.  
17.             callback = typeof callback === 'function' ? callback : function () { ; };
18.  
19.             // options
20.             size = size || 5 * (1024 * 1024);
21.             version = version || '1.0';
22.             displayName = displayName || 'sqlLiteDB';
23.  
24.             var that = this;
25.             if (window.openDatabase) {
26.                 this.db = window.openDatabase(String(name), version, displayName, size, function (db) {
27.                     callback.apply(that, Array.prototype.slice.call(arguments))
28.                 });
29.             }
30.  
31.             return this.db;
32.         },
33.         reader: function (sql, callback, errcallback) {
34.  
35.             if (!sql) return false;
36.  
37.             callback = typeof callback === 'function' ? callback : function () { ; };
38.             errcallback = typeof errcallback === 'function' ? errcallback : function () { ; };
39.  
40.             var that = this;
41.             this.db.readTransaction(function (ts) {
42.                 ts.executeSql(sql, [], function (ts, ds) {
43.                     callback.call(that, ds);
44.                     delete ts;
45.                 }, function (e) {
46.                     errcallback.apply(that, [e, e.message]);
47.                     delete ts;
48.                 });
49.             });
50.  
51.             return this;
52.         },
53.         execute: function (sql, datas, callback, errcallback) {
54.  
55.             if (!sql) return false;
56.  
57.             callback = typeof callback === 'function' ? callback : function () { ; };
58.             errcallback = typeof errcallback === 'function' ? errcallback : function () { ; };
59.  
60.             var that = this;
61.             this.db.transaction(function (ts) {
62.                 ts.executeSql(sql, datas, function (ts) {
63.                     callback.call(that);
64.                     delete ts;
65.                 }, function (e) {
66.                     errcallback.apply(that, [e, e.message]);
67.                     delete ts;
68.                 });
69.             });
70.  
71.             return this;
72.         }
73.     }
74.  
75.     return SqlLite;
76.  
77. } ())();

SqlLite.ready(name, callback, size, version, displayName) 함수

name: DB명

callback: 완료 시 handler method

size: DB Size

version: DB Version

displayName: displayName

1. SqlLite.ready('testDB', function () {
2.  
3.     var sql = [];
4.  
5.     sql.push(' create table if not exists comments');
6.     sql.push(' (');
7.     sql.push(' idx integer primary key autoincrement,');
8.     sql.push(' saboidx integer not null,');
9.     sql.push(' menuidx integer not null,');
10.     sql.push(' boardidx integer not null,');
11.     sql.push(' usn text integer null,');
12.     sql.push(' uid text not null,');
13.     sql.push(' uc text not null,');
14.     sql.push(' ucn text not null,');
15.     sql.push(' up text not null,');
16.     sql.push(' upn text not null,');
17.     sql.push(' comments text not null,');
18.     sql.push(' isdel integer not null,');
19.     sql.push(' regdate text not null');
20.     sql.push(' )');
21.  
22.     this.execute(sql.join(''));
23.  
24. });

SqlLite.reader(sql, callback, errcallback) 함수

sql: Sql쿼리문

callback: 완료 시 handler method

errcallback: 에러 시 handler method

1. SqlLite.reader('select * from comments order by idx desc limit (' + pageSize + ') offset ' + pageSize + ' * ' + (page - 1),
2. function(ds){
3.  
4.     for (var i = 0, length = ds.rows.length; i < length; i++) {
5.  
6.         var idx = ds.rows.item(i).idx
7.             , usn = ds.rows.item(i).usn
8.             , uid = ds.rows.item(i).uid
9.             , comments = ds.rows.item(i).comments
10.             , regdate = ds.rows.item(i).regdate;
11.  
12.         h.push('<tr>');
13.         h.push('<td>' + idx + '</td>');
14.         h.push('<td>' + usn + '</td>');
15.         h.push('<td>' + uid + '</td>');
16.         h.push('<td>' + comments + '</td>');
17.         h.push('<td>' + regdate + '</td>');
18.         h.push('<td><input type="button" onclick="return boardDelete(' + idx + ', ' + page + ')" value="삭제" /></td>');
19.         h.push('</tr>');
20.     }
21.  
22.     elem.innerHTML = h.join('');
23.  
24.     SqlLite.reader('select count(idx) as cnt from comments', function (ds) {
25.         paging(page, ds.rows.item(0).cnt);
26.     });
27. });

SqlLite.execute(sql, datas, callback, errcallback) 함수

sql: Sql쿼리문

datas: 파라메터 배열

callback: 완료 시 handler method

errcallback: 에러 시 handler method

1. SqlLite.execute('insert into comments (saboidx, menuidx, boardidx, usn, uid, uc, ucn, up, upn, comments, isdel, regdate) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)',
2. [SaboIdx, MenuIdx, BoardIdx, Usn, Uid, Uc, Ucn, Up, Upn, Comments, IsDel, Regdate],
3. function () {
4.     boardList(pageSize);
5. });

참고 사이트:

http://www.w3.org/TR/webdatabase/#databases