HTML5 Project Note 5: Designing an Offline database using HTML5 webdatabase

HTML5-based Web database allows you to persist data storage management and queries in your browser, assuming that your offline application has storage capabilities that need to be normalized, then using Web database, Allows your application to save data on the client, either offline or online, or if the network is not smooth.

The following is a comparison of two different database in HTML5 database, excerpted from an article above http://www.html5rocks.com/en.

We use Websql to design and write the underlying services, the WEBDATABASE specification says the specification is no longer maintained, but almost all of them choose SQLite as a lightweight and easy-to-use client database:

Now let's encapsulate and extract the Websql common method.

First, we need to get the SQL data context that the SQLite database can manipulate and execute:

This way open or create a database by using the Opendatatbase method:

View Code

This way you can also set the name of the database dbname and database size, the default database name is Oflmail, is the name of our offline system, the default size is 2 megabytes.

We can also set the error handling method Sqlerrorhandler, the user handles the error capture after the operation failed

In this way, we get the data context db that operates SQLite, and through the context db, we can perform the corresponding curd operation.

in the first step, we write a method of creating a data table that puts this method in the SQLProvider method body,

View Code

Altogether contains three parameters tablename,fileds,callback, the table represents the name of the table you want to create, the corresponding field array, that is, the corresponding field of the table is stored in an array (the method also automatically creates a table name plus "_sec" field, he is an incremental identity, With the master key), callback as the name implies, callback function, this parameter can not pass. The existence of this callback function is important because the entire SQLite database-based operation method is called asynchronously, so it needs to be nested within the callback function, or some execution will be interrupted.

The advantage of putting this function in SQLProvider is that the function can be called directly in the dynamic instantiation of SQLProvider.

such as: var sqlprovider = new SQLProvider ();

Sqlprovider.createtable ("UserInfo", New Array ("UserName", "userpwd"));

This way, it is convenient for us to call in the page. This method of operation is equivalent to the dynamic class creation method inside C #, SQLProvider is the class name, CreateTable is the method in the class, instantiation of the call.

Next, the operation of our database, including the curd of data tables and data, will be written in this way:

Delete the data table (just pass in the table name and he will delete the corresponding data table):

View Code

Add data (contains four parameters: Table name, field array, array of values for the field, and a callback function)

The fields and values here represent the field array and the value array, which correspond to each:

such as Var fileds=new Array ("UserName", "userpwd");

var values=new array ("Ben", "123456"), then a data is added to the UserInfo table, which contains at least three valued fields, primary keys, username and userpwd, while values are the corresponding array of values.

The callback function has a returned parameter that returns the primary key for the row data you are adding.

View Code

Delete data (contains three parameters: Table name tablename, primary key sec, and a callback function callback)

This primary key SEC is the primary key of the data to be deleted in the Web database, we have an incremental identification field in front of the table, the name of the field is the table name plus "_sec", because of uniqueness, so we can delete the row data based on this primary key,

The code is as follows:

View Code

Modify the data (this side includes four parameters, table name tablename, field array fields, value array values, callback function callback)

The field array and the value array must correspond to one by one, and the first field must be the primary key, and the first value of the corresponding values must also be the value of the primary key, so that the corresponding data row can be queried based on the primary key of the field.

After the data row is detected, it can be modified according to the corresponding field in the following.

View Code

The invocation is similar to the following:

var fileds=new Array ("Userinfo_sec", "UserName", "userpwd");

var values=new Array ("5", "Ben", "123456");

Sqlprovider.updaterow ("UserInfo", Fileds, Values,function () {

Log.debug ("Modified successfully! ”);

});

This is to modify the data row of the primary key 5 in the UserInfo table and modify its username value to: "Ben",

The value of the userpwd that modifies it is: "123456"

Querying a single row of data based on the primary key (contains three parameter table names TableName, primary key sec, callback function callback):

Gets to the row data based on the table name and primary key name, and returns, noting that this is the result of returning the query through the Cllback callback function, which executes the SQL script through the data context TX and returns the result set, This way, we'll take him. The first data in the result set is also Result.rows.item (0), and in fact there is only one piece of data in the result collection.

View Code

Reads the specified data table (reads the corresponding data table according to the table name and returns the result set):

View Code

The collection of columns in result set results is represented by result.rows

The number of columns is represented by Result.rows.length.

The single data is represented by Result.rows.item (index), which refers to the index position of the column, starting with 0

Reads the specified data table (executed according to the table name TableName and Sqlsenten conditional statements and returns the result set) according to the SQL WHERE condition statement:

View Code

Similar to the above method, just one more sqlsenten conditional statement to filter the data

Use a field to check for the existence of the column (by the field name and the value of the field), which is used too much to query the data rows based on the primary key:

View Code

When the retrieved result collection contains a single piece of data, returns 1, representing the presence of the row, which is 0 when the row does not exist. This is actually not perfect. You can only display correctly in a field of a unique value, such as a primary key, and you can also verify that the row exists by using a WHERE condition statement. Don't say this side, try it yourself.

This completes the entire offline database curd operation, if there is not enough place, we can continue to modify the perfect, complete code as follows, at the end of the code we instantiated, We store the code independently in the Webdatabase.js file so that the script library can be called directly into the page that inherits the script file.

Now we apply the operations of these databases to our system,

Our User Information page (information.html), which is used to store the personal information of the logged-in User:

The following fields are included: Name, gender, entry time, work number, and department:

Check to see if there is data at the time of loading, with data showing the first

View Code

The code for our Save button is as follows:

View Code

Once saved, the data is stored in our offline database and is displayed as shown on the page when loaded:

We go to the database in the browser to view, we can see this piece of data,

Here is the form reset function, which deletes the code for the user's information

function resets () {

var userinfo_sec = $ ("#UserInfo_SEC"). Val ();

if (userinfo_sec! = "0") {

Sqlprovider.deleterow ("UserInfo", userinfo_sec, function () {

Window.location.reload (TRUE);

})

}

}

2010.11.18, the company announced that it will no longer focus on Web SQL databas, and no longer maintain its outdated specifications, browser vendors will no longer update and upgrade this piece in their new version of the browser, replaced by INDEXEDDB, The organization encourages and advocates the use of INDEXEDDB. Therefore, it is suggested that the learner should take a look at how indexeddb is used.

HTML5 Project Note 5: Designing an Offline database using HTML5 webdatabase