<!DOCTYPE html>
<html>
<head>
<style>
body {
color: #222;
font: 14px Arial;
}
body a {
color: #3D5C9D;
text-decoration: none;
}
</style>
<script>
var html5rocks = {};
html5rocks.webdb = {};
html5rocks.webdb.db = null;
html5rocks.webdb.open = function() {
var dbSize = 5 * 1024 * 1024; // 5MB
html5rocks.webdb.db = openDatabase("website", "1.0", "website manager", dbSize);
}
html5rocks.webdb.createTable = function() {
var db = html5rocks.webdb.db;
db.transaction(function(tx) {
tx.executeSql("CREATE TABLE IF NOT EXISTS website(ID INTEGER PRIMARY KEY ASC, name TEXT, site TEXT, added_on DATETIME)", []);
});
}
html5rocks.webdb.add = function(name, site) {
var db = html5rocks.webdb.db;
db.transaction(function(tx){
var addedOn = new Date();
tx.executeSql("INSERT INTO website(name,site, added_on) VALUES (?,?,?)",
[name, site, addedOn],
html5rocks.webdb.onSuccess,
html5rocks.webdb.onError);
});
}
html5rocks.webdb.onError = function(tx, e) {
alert("There has been an error: " + e.message);
}
html5rocks.webdb.onSuccess = function(tx, r) {
// re-render the data.
html5rocks.webdb.getAllWebsiteItems(loadItems);
}
html5rocks.webdb.getAllWebsiteItems = function(renderFunc) {
var db = html5rocks.webdb.db;
db.transaction(function(tx) {
tx.executeSql("SELECT * FROM website", [], renderFunc,
html5rocks.webdb.onError);
});
}
html5rocks.webdb.delete = function(id) {
var db = html5rocks.webdb.db;
db.transaction(function(tx){
tx.executeSql("DELETE FROM website WHERE ID=?", [id],
html5rocks.webdb.onSuccess,
html5rocks.webdb.onError);
});
}
function loadItems(tx, rs) {
var rowOutput = "";
var todoItems = document.getElementById("WebSiteItems");
for (var i=0; i < rs.rows.length; i++) {
rowOutput += renderWebSite(rs.rows.item(i));
}
todoItems.innerHTML = rowOutput;
}
function renderWebSite(row) {
return "<li>" + row.name + " "+ row.site +"[<a href='javascript:void(0);' onclick='deleteRow(" + row.ID +");'>Delete</a>][<a href='javascript:void(0);' onclick='updateRow(" + row.ID +");'>update</a>]</li>";
}
function updateRow( id) {
var name = document.getElementById("name");
var site = document.getElementById("site");
html5rocks.webdb.update(name.value, site.value, id);
name.value = "";
site.value = "";
init();
}
html5rocks.webdb.update = function(name,site , id) {
var db = html5rocks.webdb.db;
db.transaction(function(tx){
tx.executeSql("update website set name=?, site=? where id=?", [name, site, id],
html5rocks.webdb.onSuccess,
html5rocks.webdb.onError);
});
}
function deleteRow(rowId) {
html5rocks.webdb.delete(rowId);
}
function init() {
html5rocks.webdb.open();
html5rocks.webdb.createTable();
html5rocks.webdb.getAllWebsiteItems(loadItems);
}
function add() {
var name = document.getElementById("name");
var site = document.getElementById("site");
html5rocks.webdb.add(name.value, site.value);
name.value = "";
site.value = "";
}
function deleteALL() {
html5rocks.webdb.deleteALL();
init();
}
html5rocks.webdb.deleteALL = function() {
var db = html5rocks.webdb.db;
db.transaction(function(tx){
tx.executeSql("drop table website", [],
html5rocks.webdb.onSuccess,
html5rocks.webdb.onError);
});
}
</script>
</head>
<body onload="init();">
<ul id="WebSiteItems">
</ul>
<form type="post" onsubmit="add(); return false;">
<input type="text" id="name" name="name" placeholder="site name" style="width: 200px;" />
<input type="text" id="site" name="site" placeholder="site address" style="width: 200px;" />
<input type="submit" value="Add Item"/>
</form>
<form type="post" onsubmit="deleteALL(); return false;">
<input type="submit" value="delete all Item"/>
</form>
</body>
</html>
저의 예제는 todo테이블을 살짝 변형시켰습니다. text하나 추가하였습니다.
1. update는 input text 두개의 칸에 value값을 넣어주고 update를 눌러주면 해당 row가 input 값에 따라 update
아래 화면과 같이 input 텍스트칸에 데이터를 넣고 변경하려는 row의 update버튼을 눌러줍니다.
2. drop의 경우엔 sql 구문에서 truncate가 먹히지 않아서 drop으로 처리했으며, 테이블 drop 후 init()함수를 통하
여 다시 테이블을 생성하도록 했습니다.
delete all Item 버튼을 눌러주면 테이블이 drop됩니다.
크롬의 요소검사->resources->Databases 에서 테이블을 확인하시면 테이블 데이터가 없는것을 볼수 있습니다.
3. 나머지 select, insert, delete 구문은 어떻게 돌아가는지 실행해보시면 쉽게 아실수 있을겁니다.
