Pages

Subscribe:

2013年9月4日 星期三

Web SQL資料庫

在支援HTML5的瀏覽器上，已經可以撰寫出給使用者端存取的小型資料庫了，這方面可應用像是建立使用者的最愛收藏、記事功能，讓我們設計的行動版網站或手機APP可以記錄各種需要長期儲存在使用者端的資料。這方面的資料庫技術，有三種主要規格，而基於一些理由，大家都是選擇採用Web SQL。
在開始學習Web SQL之前，還要瞭解一個工具，在Chrome瀏覽器的[工具]>[開發人員工具]所開啟的分割視窗中，[Resources]之下就可以查看[Web SQL]內容。我們在練習Web SQL程式時需要利用這個工具做觀察，才能知道是否有正確建立存取資料。(這裡有一個例子可以先開啟練習如何觀察Web SQL資料庫)
好的，來練習個中規中矩的例子吧，假設要用Web SQL讓使用者建立自己的聯絡人資料庫，重要語法就下列這三招：
  1. openDatabase( ) : This method creates the database object either using existing database or creating new one.
  2. transaction( ) : This method give us the ability to control a transaction and performing either commit or rollback based on the situation.
  3. executeSql( ) : This method is used to execute actual SQL query.
Opening Database:
The openDatabase method takes care of opening a database if it already exists, this method will create it if it already does not exist.
To create and open a database, use the following code:
var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
Above method took following five paramters:
  1. Database name
  2. Version number
  3. Text description
  4. Size of database
  5. Creation callback
The last and 5th argument, creation callback will be called if the database is being created. Without this feature, however, the databases are still being created on the fly and correctly versioned.

Executing queries:

To execute a query you use the database.transaction() function. This function needs a single argument, which is a function that takes care of actually executing the query as follows:
var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
db.transaction(function (tx) {  
   tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');
});
The above query will create a table called LOGS in 'mydb' database.

INERT Operation:

To create enteries into the table we add simple SQL query in the above example as follows:
var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
db.transaction(function (tx) {
   tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');
   tx.executeSql('INSERT INTO LOGS (id, log) VALUES (1, "foobar")');
   tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "logmsg")');
});
We can pass dynamic values while creating entering as follows:
var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
db.transaction(function (tx) {  
  tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');
  tx.executeSql('INSERT INTO LOGS 
                        (id,log) VALUES (?, ?'), [e_id, e_log];
});
Here e_id and e_log are external variables, and executeSql maps each item in the array argument to the "?"s.

READ Operation:

To read already existing records we use a callback to capture the results as follows:
var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
db.transaction(function (tx) {
   tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');
   tx.executeSql('INSERT INTO LOGS (id, log) VALUES (1, "foobar")');
   tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "logmsg")');
});
db.transaction(function (tx) {
   tx.executeSql('SELECT * FROM LOGS', [], function (tx, results) {
   var len = results.rows.length, i;
   msg = "<p>Found rows: " + len + "</p>";
   document.querySelector('#status').innerHTML +=  msg;
   for (i = 0; i < len; i++){
      alert(results.rows.item(i).log );
   }
 }, null);
});

Final Example:

So finally, let us keep this example in full fledged HTML5 document as follows and try to run it with Safari browser.
<!DOCTYPE HTML>
<html>
<head>
<script type="text/javascript">
var db = openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
var msg;
db.transaction(function (tx) {
  tx.executeSql('CREATE TABLE IF NOT EXISTS LOGS (id unique, log)');
  tx.executeSql('INSERT INTO LOGS (id, log) VALUES (1, "foobar")');
  tx.executeSql('INSERT INTO LOGS (id, log) VALUES (2, "logmsg")');
  msg = '<p>Log message created and row inserted.</p>';
  document.querySelector('#status').innerHTML =  msg;
});

db.transaction(function (tx) {
  tx.executeSql('SELECT * FROM LOGS', [], function (tx, results) {
   var len = results.rows.length, i;
   msg = "<p>Found rows: " + len + "</p>";
   document.querySelector('#status').innerHTML +=  msg;
   for (i = 0; i < len; i++){
     msg = "<p><b>" + results.rows.item(i).log + "</b></p>";
     document.querySelector('#status').innerHTML +=  msg;
   }
 }, null);
});
</script>
</head>
<body>
<div id="status" name="status">Status Message</div>
</body>
</html>
This would produce following result with latest version of either Safari or Opera:
Log message created and row inserted.

Found rows: 2

foobar

logmsg
To learn above concept - Do Online Practice using latest version of either Safari or Opera.


更多說明可看Using the JavaScript Database，只要你對SQL有概念就應該很容易瞭解。但是，如果完全沒有SQL的概念，建議先從w3schools的SQL教學範例開始學習，也可以參考W3C Working Group Note - Web SQL Database。
張貼者： 於

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)