Front-end storage of web SQL Database

Objective

In the indexeddb of the previous front-end storage, our project team is going to have a back-and-forth project that requires storage at the front end, and we first find INDEXEDDB, and we've been looking at the INDEXEDDB for a while, and we've found that it's not very suitable for our project, Reason article later will talk about, so we continue to find, so we found the Web SQL database, found that the front-end databases are more suitable for our project, and then decisively switch to the embrace of the Web SQL database, find storage tools and shoes a truth, don't care how cool, Appropriate is the king, if it is not appropriate to lead to the grinding foot is not long-term, since found the right shoes, not, the appropriate storage database, of course, the next is to find out its character temper Ah, to better for our use, so we have this article.

Why is Web Sql database sacred?

Web SQL Database, also known as the HTML5 Local database, is a lightweight database that runs on the browser side as the HTML5 specification joins. In HTML5, greatly enrich the content that the client can store locally, add a lot of functions to save the data that must be saved on the server to the client locally, thus greatly improve the performance of the Web application, reduce the burden on the server, so that the web era back to the "client-oriented, Server for the Light "era. As can be seen, its use is still very large, like some often need to retrieve the data, can be removed from the background, saved to the Web SQL database, the next time you use it, there is no need to retrieve the background, thus reducing the number of requests to the server, there is a Web SQL can be Database to do some off-line operations because the data is already stored in the Web SQL database.

The Web SQL database API is not actually part of the HTML5 specification, but rather a separate specification. It manipulates the client's database through a set of APIs. Web SQL Database is already supported by mainstream browsers such as Safari, Chrome, Firefox, and opera. When people who have studied the database use a Web SQL database, they will find it very interesting, because you will find that it has a storage style similar to that of our MySQL database, you can operate the local database like MySQL database, it is easy to get started. Although the Web-based SQL Database specification is no longer maintained by the official in November 2011, it is necessary to understand the API for Web development due to its wide implementation.

Web Sql Database VS IndexedDB

Wit's little partner may have seen, Web SQL database is a kind of technology that stores data in the browser, unlike INDEXEDDB, the Websql database is more like a relational database, and the classmates who read my previous article should know that INDEXEDDB more like a nosql database, using SQL query data, and our project, exactly a lot of places to do this association query, so websql database more suitable for us, in fact indexeddb is also very good, very powerful! After all, INDEXEDDB is now the recommend.

The three core methods of Web SQL database

Web SQL database is really very simple to use because of the three core methods defined in its specification:

1. OpenDatabase: This method creates a database object using an existing database or creating a new database.

var db= opendatabase (database name, version, database description, database size);

Eg:var db= OpenDatabase ("Mytestdb", "1.0", "My Test Database", 10*1024,[callback function (can not)]);

2. Transaction: To access the database, to borrow the transaction () method, this method allows us to control the transaction commit or rollback as appropriate.

3. ExecuteSQL: This method is used to execute a real SQL query. This method is asynchronous, and subsequent business logic can be processed in the callback function.

These three methods are not at a glance, the basic process is to open the database, get transactions, and then execute SQL, and we use the back end of those data a hair!

Basic operations for WEB SQL database

1. Open the Database

if (! window.opendatabase) {             console.log (' The browser does not support database ')             ; return false ;         }         // 1. Database name 2. Database version number 3. Display name 4. Database save data size in bytes byte 10M 5. Callback function (not required)  = window.opendatabase ("myautotest.db", "1.0", "Automated test Platform Database Database", 10 * 1024 * 1024);

Say OpenDatabase method, the first time will create a database, if it already exists, then open the database, the creation of the database is local, it should be in your user data under the Google directory, you can look for, my local database address is: C:\Users\dengyul\ Appdata\local\google\chrome\user data\default\databases.

2. Create a data table

You write a SQL statement that builds a table, the syntax is consistent with a database such as MySQL, and then executes the SQL statement using the ExecuteSQL method, explaining the four parameters of ExecuteSQL , the first one representing the query string, The second represents the string data where the question mark is inserted into the query, and the third is the callback function that executes when it succeeds. Returns two parameters: the result of TX and execution, and the fourth is a callback function that executes when it fails. Returns two parameters: TX and failed error message.

functionfunction(TX) {Tx.executesql ("CREATE table if not exists TEMPLATE (template_id INTEGER not NULL PRIMARY KEY autoincrement, template_name TEXT) ", [],function(tx,result) {alert (' Create Template table Success '); },function(TX, error) {alert (' Create Template table failed: ' + error.message ');});}

3. Add Data

or call ExecuteSQL to execute the inserted SQL statement

function () {database.transaction (function  (TX) {tx.executesql ("INSERT INTO TEMPLATE (template_ Name) VALUES (?) " , [' template 1 '], function {alert (' Add data Success ');},function (TX, error) {alert (' Add data failed: ' + error.message ');} );

4. Querying data

As we said earlier, the ExecuteSQL succeeds after successful execution of the callback function, and the callback function has a parameter of result, and this result is the data set that is queried. The data type is SqlResultSet, where the most important property of the-sqlresultsetrowlist type is rows, and rows has two properties: Length, item, and therefore, the value of a column in a row for the query result : Result.rows[i].item[fieldname].

function () {database.transaction (function  (TX) {tx.executesql ("select * from TEMPLATE", [],  function/ / Execute successful callback function // Here is the result of doing what you want to do ... },function  (TX, error) {alert (' query failed: ' + error.message ');});}

5. Updating data

Basically consistent with the above.

function (ID, name) {database.transaction (function  (TX) {tx.executesql ("Update TEMPLATE set Template_name =? where template_id =? " , [name, id], function (TX, result) {// post-update processing }, function (TX, error) {alert (' update failed: ' + error.message) ;});}

6. Delete data

In line with the above, the SQL is replaced by the deletion of the statement can not do more than repeat ...

Summarize

As can be seen from the crud operation above, the Web SQL database operation is still very friendly to the traditional developers, so it is easier to master, although the Web SQL database specification has been discarded, but learning is still very necessary. With these HTML5 Web SQL Database API interfaces, I believe there will be some very good applications built on top of these APIs.

Front-end storage of web SQL Database