The Web SQL database API isn’t actually part of the HTML5 specification, but it is part of the suite of specifications that allows us developers to build fully fledged web applications, so it’s about time we dig in and check it out.
What’s in the box?
If you haven’t guessed from the overly verbose specification title, Web SQL Databases is a spec that brings SQL to the client side. If you have a back-end developer’s background, then you’ll probably be familiar with SQL and happy as a pig in muck. If not, you might want to learn some SQL before you start hacking around, Google’s your friend here.
The specification is based around SQLite (3.1.19), but having come from MySQL myself, it’s all pretty much the same (sorry for the sweeping statement!).
For an example of Web SQL Databases working, have a look at the Twitter HTML5 chatter demo I put together. It uses SQL and the WHERE clause to narrow down the recent chat about HTML5 on Twitter (it will work in Safari, Chrome and Opera 10.50).
There are three core methods in the spec that I’m going to cover in this article:
-
openDatabase
-
transaction
-
executeSql
Support is a little patchy at the moment. Only Webkit (Safari, SafariMobile and Chrome) and Opera 10.50 (ATOW alpha on Mac) support web databases. Fellow Doctor Bruce Lawson has told me that Firefox are holding off as they feel there’s a better implementation than SQLite (though I hope it’s similar, whatever they pick). Either way, I’d definitely recommend checking out the SQLite documentation for the functions that are available.
Because of this patchy support and the simple fact that Webkit had implemented the database spec some time ago, the spec on the W3C is now slightly ahead of the implementations in Safari, while Webkit is still catching up. On the other hand, since Opera has only just added support, it’s closer to the spec (I’ll mention the differences as we go along).
Nonetheless, it’s fun to play with, so let’s get playing!
Creating and Opening Databases
If you try to open a database that doesn’t exist, the API will create it on the fly for you. You also don’t have to worry about closing databases.
To create and open a database, use the following code:
var db = openDatabase('mydb', '1.0', 'my first database', 2 * 1024 * 1024);
I’ve passed four arguments to the openDatabase
method. These are:
- Database name
- Version number
- Text description
- Estimated size of database
The missing feature of openDatabase
(I’m not sure when it was added) is the fifth argument:
- Creation callback
The 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.
The return value from openDatabase
contains the transaction methods, so we’ll need to capture this to be able to perform SQL queries.
Estimated database size
From the tests I’ve run, only Safari prompts the user if you try to create a database over the size of the default database size, 5MB. The prompt is shown the image below, asking whether you want to grant the database permission to scale up to the next size of database — 5, 10, 50, 100 and 500MB. Opera, on the other hand, builds the database without complaining, which I expect might change later as it’s still in alpha.
Versions
I could be wrong, but everything I’ve tested so far says that versioning in SQL databases is borked. The problem is this:
If you upgrade your database to version 2.0 (e.g., there are some important schema changes since version 1.0), how do you know which visitors are on version 1.0 and which are on version 2.0?
The version number is a required argument to openDatabase
, so you must know the version number before you try to open it. Otherwise, an exception is thrown.
Also, changeVersion
, the method to change the database version, is not fully supported in Webkit. It works in Chrome and Opera, but not in Safari or Webkit. Regardless, if I can’t determine which version of database the user is on, then I can’t upgrade the user.
A possible workaround is to maintain a state database, something like the ‘mysql’ database in MySQL. This way, you would only have one version of this state database, and within this you would record the current version of any databases that control your application. It’s a hack, but it works.
Transactions
Now that we’ve opened our database, we can create transactions. Why bother with transactions instead of just running our SQL? Transactions give us the ability to rollback. This means that if a transaction — which could contain one or more SQL statements — fails (either the SQL or the code in the transaction), the updates to the database are never committed — i.e. it’s as if the transaction never happened.
There are also error and success callbacks on the transaction, so you can manage errors, but it’s important to understand that transactions have the ability to rollback changes.
The transaction is simply a function that contains some code:
var db = openDatabase('mydb', '1.0', 'my first database', 2 * 1024 * 1024);
db.transaction(function (tx) {
// here be the transaction
// do SQL magic here using the tx object
});
I recently uploaded a demo to html5demos.com that demonstrates a transaction rollback in action: Web SQL database rollback demo
In the nightly builds of the browsers, we also have db.readTransaction
, which allows only read statements to run on the database. I assume there are performance benefits to using a read-only readTransaction
instead of a read/write transaction
, most probably to do with table locking.
Now that we’ve got our transaction object (named tx
in my example) we’re ready to run some SQL!
executeSql
This is the funnel of love for all your SQL goodness. executeSql
is used for both read and write statements, includes SQL injection projection, and provides a callback method to process the results of any queries you may have written.
Once we have a transaction object, we can call executeSql
:
var db = openDatabase('mydb', '1.0', 'my first database', 2 * 1024 * 1024);
db.transaction(function (tx) {
tx.executeSql('CREATE TABLE foo (id unique, text)');
});
This will now create a simple table called “foo” in our database called “mydb”. Note that if the database already exists the transaction will fail, so any successive SQL wouldn’t run. So we can either use another transaction, or we can only create the table if it doesn’t exist, which I’ll do now so I can insert a new row in the same 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")');
});
Now our table has a single row inside it. What if we want to capture the text from the user or some external source? We’d want to ensure it can’t compromise the security of our database (using something nasty like SQL injection). The second argument to executeSql
maps field data to the query, like so:
tx.executeSql('INSERT INTO foo (id, text) VALUES (?, ?)', [id, userValue]);
id
and userValue
are external variables, and executeSql
maps each item in the array argument to the “?”s.
Finally, if we want to select values from the table, we use a callback to capture the results:
tx.executeSql('SELECT * FROM foo', [], function (tx, results) {
var len = results.rows.length, i;
for (i = 0; i < len; i++) {
alert(results.rows.item(i).text);
}
});
(Notice that in this query, there are no fields being mapped, but in order to use the third argument, I need to pass in an empty array for the second argument.)
The callback receives the transaction object (again) and the results object. The results object contains a rows
object, which is array-like but isn’t an array. It has a length, but to get to the individual rows, you need to use results.rows.item(i)
, where i
is the index of the row. This will return an object representation of the row. For example, if your database has a name
and an age
field, the row will contain a name
and an age
property. The value of the age
field could be accessed using results.rows.item(i).age
.
That’s all you should need to get started with Web SQL Databases. I’m certain that mini JavaScript libraries are going to emerge to help support working with databases. If you want to find out more about SQL databases (shameless self promotion begins) I just finished the storage chapter for Introducing HTML5, which I’m writing with fellow Doc Bruce, so check that bad boy out too!
相关推荐
HTML5 Web SQL Database 与 Indexed Database 的 CRUD 操作
一个本地数据库(Web SQL Database)实现的通信录;介绍怎样创建打开数据库,创建表,添加数据,更新数据,删除数据,删除表 等等。
通过一个例子说明 Web SQL Database 的基本用法。它首先调用 openDatabase 创建了名为“fooDB”的数据库。然后使用 transaction 执行两条 SQL 语句。第一条 SQL 语句创建了名为“foo”的表,第二条 SQL 语句向表中...
html5 web sql database 的基本功能
利用HTML5 新特新 canvas 动态画图,同时,还实现了WEB SQL DATABASE 客户端嵌入式数据库增删改查的功能。(注意:完全支持的浏览器是chrome)
HTML5中ExtJS和Web SQL Database的使用.pdf
IOS+HTML5+web sql database
WebDatabase 规范中说这份规范不再维护了,原因是同质化(几乎实现者都选择了Sqlite), 且不说这些,单看在HTML5中如何实现离线数据的CRUD,最基本的用法(入门级别) 1,打开数据库 2,创建表 3,新增数据 4,更新数据 5,读取...
//openDatabase方法打开已经存在的数据库,如果不存在将会创建一个数据库,参数分别是 数据库的名称,版本,数据库的描述,和数据大小 var db = window.openDatabase(mydatabase, 1.0, 我的数据库描述, 20000);...
Chrome 4现已支持HTML 5 Web SQL Database API Flex开发者需要知道的10件事 CWE/SANS发布2010年25个最危险的编程错误 Google将不再支持老式浏览器 SOA设计关乎契约还是服务实现? Chrome中5大安全增强 Amazon ...
简易的一个后台管理,将数据保存在WEB SQL DATABASE 访问地址(部署tomcat): http://localhost:8080/arbor-v0.92/demos/halfviz/index.html http://localhost:8080/arbor-v0.92/demos/halfviz/admin.html
11.5.1 Web SQL Database 247 11.5.2 索引数据库API 250 11.6 进阶功能 251 11.6.1 JSON对象的存储 251 11.6.2 共享窗口 252 11.7 小结 253 第12章 构建离线Web应用 254 12.1 HTML5离线Web应用概述 254 12.2...
I wrote this book for anyone who needs to understand how to use XML to integrate applications or organizations that store data in a SQL Server database. The book covers XML-related functionality in ...
This book is an easy-to-understand guide to building Oracle8i (Oracle's "Internet database") Web applications using a variety of tools -- PL/SQL, HTML, XML, WebDB, and Oracle Application Server (OAS)....
(websql) Android 浏览器 2.1+ (websql), 4+ (indexeddb) Android 网页客户端、iOS 网页客户端 (websql) Opera 10+ (websql), Opera 15+ (indexeddb) 特征 IndexedDB、WebDatabase 和 WebStorage 存储机制上的统一...