U E D R , A S I H C RSS

html5/web Sql Database

1. ฐœš”

  • SeeAlso) html5/web-storage
  • กœปฌ  €žฅ†Œ˜ €. html5/web-storage™ธ— ถ”€  œณต˜Š” ฒƒ.
  • ธŒšฐ € žฒด ฒฝŸ‰ DB
  • web storage™€ „ต
    • WebStorageŠ” „‹จํ•œ ฐดํ„  €žฅํ•˜ธฐ  ํ•ฉ.
    • WebSqlDatabaseŠ” ณด‹ค ตฌกฐ ดณ  ฒด„ํ™”œ €„ํ˜• ฐดํ„ฒ ดŠค Œ€Ÿ‰œกœ  €žฅ
  • •ˆ • ธ ฒฝŸ‰ €„ํ˜• žฃŒตฌกฐ €›, ํ‘œ€ SQLงˆ˜ €›
  • SQLite ธฐฐ˜œกœ ํ•˜ณ  žˆ‹ค.

1.1. ธŒšฐ € €›

  • ˜™ธกœ ํŒŒด–ดํญŠค—„œ €›ํ•˜€ •ŠŠ”‹ค.
    • ‹ฌ€–ด ‚ด€ ””„€ sqlite ž„—„ ถˆตฌํ•˜ณ .
  • •„ž˜˜ ฝ”“œ ดšฉํ•˜ฉด €› —ฌ€ ํ™•ธ ํ•  ˆ˜ žˆ‹ค.
    if(!!window.openDatabase) {
         alert("ํ˜„žฌ ธŒšฐ €Š” Web SQL Database €›ํ•ฉ‹ˆ‹ค")
    }
    else{
          alert("ํ˜„žฌ ธŒšฐ €Š” Web SQL Database €›ํ•˜€ •ŠŠต‹ˆ‹ค")
    }
    

1.2. Indexed Database

  • SeeAlso) html5/indexedDatabase
  • Indexed DatabaseŠ” ƒˆกœ “žฅํ•œ ˜‹คฅธ กœปฌ  €žฅ†Œ ŠคํŽ™ด‹ค
  • 2009…„ งŒ€ ‚ฌ–‘ … •ด „ํ–‰ค‘ด˜ Web SQL Database ˜ Œ€•ˆœกœ ํƒ„ƒํ–ˆ‹ค
  • ํ˜„žฌ Web SQL Database Š” ‚ฌ–‘ … •ด ค‘€œ ƒํƒœดฉฐ, IndexedDB Š” ƒˆกœšด ŠคํŽ™ด
Œ€•ˆœกœ – ˜คฅดณ  žˆ‹ค

2. ํŠง•

  • „™ธฐ ž‘—… ด€กœ UI— ตœ†Œํ•œ˜ ˜ํ–ฅ„ €‹ค.
  • „ฉ”ธ ‹จœ„กœ DB€ „ฆฌœ‹ค.
    • ํŠ • „ฉ”ธ—„œ ƒ„œ ””„Š” ‹คฅธ „ฉ”ธ—„œ  ‘ํ•  ˆ˜ —†‹ค.
  • ฐ˜ ธ DB ‚ฌšฉฒ• „Šทํ•˜‹ค. —ดณ , ˆ˜ํ–‰ํ•˜ณ , ‹ซณ .

3. ‚ฌšฉฒ•

  • „™ธฐ ‹ด‹ค.
  • transaction„ €›ํ•œ‹ค.
    • transaction(), readTransaction

3.1. €ˆ˜ „ –ธ

var html5rocks = {};
html5rocks.webdb = {};

3.2. —ดธฐ

  • ‚ฌšฉ „— ฐ˜“œ‹œ ฐดํ„ฒ ดŠค —ด–ด• ํ•œ‹ค.

html5rocks.webdb.db = null;

html5rocks.webdb.open = function() {
  var dbSize = 5 * 1024 * 1024; // 5MB
  html5rocks.webdb.db = openDatabase('Todo', '1.0', 'todo manager', dbSize);
}

html5rocks.webdb.onError = function(tx, e) {
  alert('Something unexpected happened: ' + e.message );
}

html5rocks.webdb.onSuccess = function(tx, r) {
  // re-render all the data
  html5rocks.webdb.getAllTodoItems(tx, r);
}

3.3. ํ…Œดธ” ƒ„

  • todo table. ID, todo, added_on

html5rocks.webdb.createTable = function() {
  html5rocks.webdb.db.transaction(function(tx) {
    tx.executeSql('CREATE TABLE IF NOT EXISTS ' + 
                  'todo(ID INTEGER PRIMARY KEY ASC, todo TEXT, added_on DATETIME)', []);
  });
}

3.4. ํ…Œดธ”— ฐดํ„ถ”€

  • dynamic sql

html5rocks.webdb.addTodo = function(todoText) {
  html5rocks.webdb.db.transaction(function(tx){
    var addedOn = new Date();
    tx.executeSql('INSERT INTO todo(todo, added_on) VALUES (?,?)', 
        [todoText, addedOn],
        html5rocks.webdb.onSuccess,
        html5rocks.webdb.onError);
    });
}

3.5. €ƒ‰ งˆ˜



html5rocks.webdb.getAllTodoItems = function(renderFunc) {
  html5rocks.webdb.db.transaction(function(tx) {
    tx.executeSql('SELECT * FROM todo', [], renderFunc, 
        html5rocks.webdb.onError);
  });
}

3.5.1. ฒฐ ํ‘œ‹œ

function loadTodoItems(tx, rs) {
  var rowOutput = "";
  for (var i=0; i < rs.rows.length; i++) {
    rowOutput += renderTodo(rs.rows.item(i));
  }
  var todoItems = document.getElementById('todoItems');
  todoItems.innerHTML = rowOutput;
}

function renderTodo(row) {
  return '<li>' + row.ID  +
         '[<a onclick="html5rocks.webdb.deleteTodo(' + row.ID + ');"'>X</a>]</li>';
}

3.6. ฐดํ„€šฐธฐ

html5rocks.webdb.deleteTodo = function(id) {
  html5rocks.webdb.db.transaction(function(tx) {
    tx.executeSql('DELETE FROM todo WHERE ID=?', [id],
        loadTodoItems, html5rocks.webdb.onError);
  });
}

3.7. hooking it all up

3.7.1. init

function init() {
  html5rocks.webdb.open();
  html5rocks.webdb.createTable();
  html5rocks.webdb.getAllTodoItems(loadTodoItems);
}
</script>

<body onload="init();">

3.7.2. adding

function addTodo() {
  var todo = document.getElementById('todo');

  html5rocks.webdb.addTodo(todo.value);
  todo.value = '';
}

3.8. transaction

var db = openDatabase('mydb', '1.0', 'my first database', 2 * 1024 * 1024);
db.transaction(function (tx) {
  tx.executeSql('CREATE TABLE IF NOT EXISTS foo (id unique, text)');
  tx.executeSql('INSERT INTO foo (id, text) VALUES (1, "synergies")');
});

Valid XHTML 1.0! Valid CSS! powered by MoniWiki
last modified 2021-02-07 05:31:41
Processing time 0.0267 sec