HTML5 development-lightweight web database repository html5sql. js

Document directory

• Example
• Suggestions
• User Guide
• Summary
• In-depth source code

Before reading this article, let's take a look at W3C's section about web database:

Beware. This specification is no longer in active maintenance and the Web

 

Applications Working Group does not intend to maintain it further.

This means that the web database specification is deadlocked.

 

Html5sql Official Website: http://html5sql.com/

Brief Introduction

Html5sql is a lightweight JavaScript module that makes HTML5 web database easier to use. Its basic function is to provide a structure for sequential execution of SQL statements in a transaction. Although the WEB database does not stop moving forward, this module can only simplify the interaction process with the database. This module also contains many other small details so that developers can work more easily and conveniently.

Core features

1. Provide the ability to execute SQL statements in different order:
Single SQL statement
A group of SQL statements
A group of SQL statement objects (this form may be required when you want to inject data in SQL or call a callback function after SQL Execution)
From a split file that contains SQL statements
2. provides a framework for controlling database versions

Example

If you have used HTML5 Web database, you will find it quite painful, especially when you start to create your table. Now you will find that these are not problems. To clearly show what I mean and the capabilities of this module, let's look at the following example:
Suppose you want to create a table and insert a group of data into it. If you use html5sql, you just need to put all the statements into a separate file, in this example we get the name Setup-Tables. SQL. The format of this file is similar

In this example, we named the Setup-Tables. SQL. The format of this file is similar:
Create Table example (ID integer primary key, data text );
Insert into example (data) values ('first ');
Insert into example (data) values ('second ');
Insert into example (data) values ('third ');

Create Table example2 (ID integer primary key, data text );
Insert into example2 (data) values ('first ');
Insert into example2 (data) values ('second ');
Insert into example2 (data) values ('third ');

With html5sql, you only need to open the database and add the following code to execute these SQL statements (including creating tables) in sequence.

$. Get ('setup-tables. SQL ', function (sqlstatements ){

    html5sql.process(
//This is the text data from the SQL file you retrieved
        sqlStatements,
function(){
// After all statements are processed this function
            //   will be called.
        },
function(error){
// Handle any errors here
        }
    );
});

With the help of jquery's get method, you have obtained SQL statements from a separate file ('setup-tables. SQL ') and executed them separately in the order of appearance.

Performance

The above description sounds good, but you may ask if the performance will be impaired during SQL sequential execution. The answer is that the impact is not obvious, at least in my opinion.
For example, I used Google Chrome desktop to create a table and insert 10,000 records into the table in sequence. The entire execution process may fluctuate, but the average value is within the range of 2 to 6 seconds, therefore, I have reason to believe that html5sql is doing well when processing a large amount of data.

 

Suggestions

The core of SQL is designed as a language for sequential execution. Some statements must be executed before other statements. For example, you must create a table before inserting a record. On the contrary, javascipt is an asynchronous and event-driven language. For developers, these asynchronous features increase the complexity of HTML5 client database specifications and descriptions. When writing this database, W3C no longer maintains the web SQL database specification. Despite this, WebKit has implemented an interface, and this library is still useful because of the large user base of WebKit kernel browsers, especially on mobile devices.

Although this module reduces the complexity of HTML5 SQL database, it does not simplify the SQL itself, which is intended. SQL is a powerful language,

Blindly simplifying it will only be self-defeating. In my experience, learning SQL is the king, and the general trend is rampant.

 

User Guide

The html5sql module has three built-in functions:

Html5sql. opendatabase (databasename, displayname, estimatedsize)

Html5sql. opendatabase is an encapsulation of the native opendatabase method. This method opens a database connection and returns a reference to the connection.
This is the first step before all other operations.
This method has three parameters:
Databasename-database name. Any valid name you like can usually be "com. yourcompany. yourapp"
Displayname-database description
Estimatedsize-database size. 5 m = 5*1024*1024

If you are familiar with the native web database method, you will find that the parameter version information is missing in the preceding encapsulation. When you need to change the table structure of the database, version information is a powerful identification tool,

This method of version change is encapsulated into the changeversion method of html5sql.
Now, we create a common database connection:

Html5sql. opendatabase (

     "com.mycompany.appdb",
     "The App Database"
     3*1024*1024);

2,

Html5sql. Process (SQL, finalsuccesscallback, errorcallback)

Html5sql. the process () method is the carrier of all functions. Once you create a database connection, you can pass SQL statements, and the rest will be handed over to html5sql. process (), which ensures SQL Execution in sequence.

The first parameter of html5sql. Process () is an SQL statement, which is transmitted in many ways:
(1), string form-you can pass a simple string to the process method, such:
"Select * from table ;"
Or a group of simple strings connected with semicolons (;), such:

"CREATE table (id INTEGER PRIMARY KEY, data TEXT);" +
"INSERT INTO table (data) VALUES ('One');" +
"INSERT INTO table (data) VALUES ('Two');" +
"INSERT INTO table (data) VALUES ('Three');" +
"INSERT INTO table (data) VALUES ('Four');" +
"INSERT INTO table (data) VALUES ('Five');"

(2) SQL statements from independent files. The examples of SQL statements from independent files are the same as the examples above, and there is no essential difference.
(3) A set of SQL statement strings. You can pass a set of SQL statements to html5sql. Process (), such:

 [
     "CREATE table (id INTEGER PRIMARY KEY, data TEXT);",
     "INSERT INTO table (data) VALUES ('One');",
     "INSERT INTO table (data) VALUES ('Two');",
     "INSERT INTO table (data) VALUES ('Three');",
     "INSERT INTO table (data) VALUES ('Four');",
     "INSERT INTO table (data) VALUES ('Five');"
 ]

(4) A group of SQL statement objects. This is the most practical form of directly passing a set of SQL statement objects. The parameters of the native executesql method in the structure field of the SQL statement object are the same. There are three parts:
SQL [String] -- a string containing an SQL statement, which can contain the replacement character "?"
Data [array] -- a group needs to be inserted into SQL statements to replace? Symbol data, in which SQL statements? Must be consistent with the number of data in the data.
Success (function)-the callback function after the SQL statement is executed, which can process the execution result of the SQL statement. In addition, if this method returns an array, the returned array can also be executed as the data parameter of the next SQL statement. In this way, you can call the result of the previous SQL statement during SQL Execution. This is common when a foreign key is used.
Perhaps the simplest way to define and use this object is to use only the object literal. A general template is similar to the following:

 {
    "sql": "",
    "data": [],
    "success": function(transaction, results){
//Code to be processed when sql statement has been processed
    }
 }

Therefore, a simple SQL object parameter example is as follows:

  [
    {
      "sql": "INSERT INTO contacts (name, phone) VALUES (?, ?)",
      "data": ["Joe Bob", "555-555-5555"],
      "success": function(transaction, results){
//Just Added Bob to contacts table
      },
    },
    {
      "sql": "INSERT INTO contacts (name, phone) VALUES (?, ?)",
      "data": ["Mary Bob", "555-555-5555"],
      "success": function(){
//Just Added Mary to contacts table
      },
    }
  ]

The only difference between the above object and native executesql () is that there is no error processing method, because there is a common error processing callback function to handle the error, this prevents errors in each statement. This common error handler is the third parameter of html5sql. Process.

Summary

Html5sql. Process () has three parameters in total,
SQL-any form described above can be
Finalsuccesscallback-a final one that is triggered after successful execution of all SQL statements
Errorcallback-common functions that process all errors in this process. When any errors occur, the current transaction will be rolled back and the database version will not change.
To sum up, a complete example of this method is as follows:

 html5sql.process(
     [
         "DROP TABLE table1",
         "DROP TABLE table2",
         "DROP TABLE table3",
         "DROP TABLE table4"
     ],
function(){
         console.log("Success Dropping Tables");
     },
function(error, statement){
         console.error("Error: " + error.message + " when processing " + statement);
     }
 );

3,

html5sql.changeVersion("oldVersion","newVersion",SQL,successCallback,errorCallback)

Html5sql. changeversion () is required to create, migrate, and process a database version. This method checks whether the current version is consistent with the old version parameter (oldversion). If it is consistent, the SQL statement in the parameter is executed, and change the value shown in the newversion parameter for the database version.
Oldversion-version number of the database to be modified. The default value is ""
Newversion-new value you assigned
SQL-the SQL statement you want to execute. For more information, see html5sql. Process ().
Finalsuccesscallback-function called after successful execution
Errorcallback-common data processing functions, which are the same as those in html5sql. Process (). When an error occurs, the entire transaction is rolled back without changing the version number.

 

In-depth source code

 

The biggest problem with this JS library is that if you want to manipulate multiple databases at the same time, it will lead to confusion. The author does not seem to think much about it. In addition, it still does not feel perfect about the batch execution of SQL statements.

Supplement 1:

Html5sql. opendatabase () actually has four parameters

html5sql.openDatabase = function (name, displayname, size, whenOpen) {
                     html5sql.database = openDatabase(name, "", displayname, size);
                     readTransactionAvailable = typeof html5sql.database.readTransaction === 'function';
if (whenOpen) {
                         whenOpen();
                     }
                 };

The final whenopen is triggered when the database reference is obtained.

Supplement 2:
There are also two attributes for debugging: logerrors and loginfo: The default value is false. When set to true, you can see the operation process in each step. Because the console. log of the console is called, errors may occur in some browsers.

Supplement 3:

For the process method, no matter what form of SQL parameter you use, it will eventually be converted into the form of SQL object.
When the SQL statement only contains select operations, the readtransaction method is used internally. Note the difference between readtransaction and transaction.
Here, readtransaction is used to ensure that no write operation is performed on the table. This is a safe measure, and transaction can also be used. However, the readtransaction method has certain compatibility issues. Therefore, the check should be correct before use.

For more information, see http://www.cnblogs.com/xesam /]
Address: http://www.cnblogs.com/xesam/archive/2012/03/10/2389365.html