"Translation" HTML5 development--Lightweight web database repository Html5sql.js

Mode 1:

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

Before reading, read a paragraph about web database:

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

Applications working Group does not intend to maintain it further.

means that the Web database specification is deadlocked.

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

Briefly

Html5sql is a lightweight JavaScript module that makes HTML5 Web database easier to use, and its basic function is to provide the structure of executing SQL statements sequentially in a single transaction. Although the WEB database does not stop moving forward, this module simply simplifies the process of interacting with databases. This module also contains many other small details so that developers can work more easily and naturally and easily.

Core Features

1. Provide the ability to execute SQL statements in different ways:
Single SQL statement
A set of SQL statements
A set of SQL statement objects (which you might want to use when you want to inject data into SQL or call a callback function after SQL execution)
From a file that has been split to include the SQL statement
2. Provide a framework to control the database version

Example

If you've used HTML5 Web database, you'll find it has a lot of pain, especially when you start building your watch. Well, now you'll find these are not problems. To get a clearer picture of what I mean and the capabilities of this module, look at the following example:
Suppose you are going to build a table soldier thousand insert a set of data into this table. If you use Html5sql, you only need to put all the statements into a separate file, in this case we are named Setup-tables.sql. The form of this file is similar to

In this example we are named Setup-tables.sql. The form of this file is similar to the following:
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, to execute these SQL statements sequentially (including creating a table), you just need to open the database and add the following piece of code.

$.get (' Setup-tables.sql ', function (sqlstatements) {

    Html5sql.process (
        The text data from the SQL file retrieved
        Sqlstatements,
        function () {
            After all statements is processed this function
            would be   called.
        },
        Function (Error) {
            Handle any errors here
        }
    );
});

With the help of the jquery get method, you have obtained SQL statements from separate files (' Setup-tables.sql ') and split execution in the order in which they appear.

Performance

The description above sounds good, but you might ask if the performance will be compromised when SQL is executed sequentially. The answer is that the impact is not obvious, at least as far as I'm concerned.
For example, I created a table with Google Chrome Desktop, and then inserted 10,000 records into the table sequentially, the entire execution time fluctuates, but the average is within 2-6 seconds, so I have reason to believe that Html5sql will perform well when dealing with large amounts of data.

Suggestions

The core of SQL is designed as a language for sequential execution, and some statements must be executed before other statements. For example, before inserting a record, you must first create a table. Conversely, Javascipt is an asynchronous, event-driven language. For developers, these asynchronous features add complexity to the specification and description of the HTML5 client database. When writing this library, the website has no longer maintained the Web SQL database specification. However, since WebKit has implemented an interface, this library is useful because of the large user base of the WebKit kernel browser, especially on mobile devices.

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

blindly simplifying it will only be self-defeating. In my experience, learning SQL is the King, the trend of Satan.

User Guide

There are 3 functions built into the Html5sql module:

Html5sql.opendatabase (DatabaseName, DisplayName, EstimatedSize)

Html5sql.opendatabase is a encapsulation of the native OpenDatabase method, which 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-The name of the database. Any valid name you like, usually "Com.yourcompany.yourapp" or something.
DisplayName-Database description information
EstimatedSize-database size. 5M = 5*1024*1024

If the reader is familiar with Web database native methods, it will find that the above package is missing the version information parameter. When you need to change the table structure of the database, the version information is a powerful identification tool,

This method of changing the version is encapsulated into the Html5sql changeversion method.
Now, let's create a common database connection:

Html5sql.opendatabase (

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

2.

Html5sql.process (SQL, Finalsuccesscallback, Errorcallback)

The Html5sql.process () method is the carrier of all functions, once you have created a database connection, you can pass the SQL statement, and the rest will be handed to html5sql.process (), and he will ensure that SQL is executed sequentially.

The first parameter of Html5sql.process () is the SQL statement, which is passed in a number of forms:
(1), string form-you can pass a simple string to the process method, such as:
"SELECT * from table;"
Or a group with a semicolon (";") ) A simple string of connections, such as:

"CREATE table (ID INTEGER PRIMARY KEY, Data TEXT);" +
INSERT into table Data VALUES (' one ');
INSERT into table Data VALUES (' both ');
INSERT into table Data VALUES (' three ');
"INSERT into table (data) VALUES (' Four ');" +
"INSERT into table (data) VALUES (' Five ');"

(2), SQL statement from a standalone file. An example of an SQL statement from a standalone file is the same as the example above, with no essential difference.
(3), a set of SQL statement strings, you can pass a set of SQL statements to html5sql.process (), such as:

[
     "CREATE table (ID INTEGER PRIMARY KEY, Data TEXT);",
     INSERT into table Data VALUES (' one ');
     INSERT into table Data VALUES (' both ');
     INSERT into table Data VALUES (' three '); ",
     "INSERT into table (data) VALUES (' Four ');",
     "INSERT into table (data) VALUES (' Five ');"
]

(4), a set of SQL statement objects. This is the most practical form of passing a set of SQL statement objects directly. The SQL statement object's domain-native ExecuteSQL method has the same parameters, with three parts:
sql[string]--the string containing the SQL statement, which can contain the substitution "?"
data[array]--a set of data that needs to be inserted into the SQL statement to replace the symbol, where the number of SQL statements must match the number of data in the
Success (function)-functions that execute a callback after the SQL statement can handle the results of the SQL statement execution. In addition, if the method returns an array, the returned array can also be executed as the data parameter of the next SQL statement. This allows you to invoke the results of the previous SQL statement in SQL execution, which is common in cases where foreign keys are used.
Perhaps the simplest definition and the way to use this object is to use only the object literal. The generic template is similar to the following form:

{
    "SQL": "",
    "Data": [],
    "Success": function (transaction, results) {
     Code to is processed when SQL statement have been processed
    }
}

Therefore, a simple example of a SQL object parameter 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
      },
    }
  ]

In the above object, the only difference from the native ExecuteSQL () is that there is no way to handle the error part because there is a generic error handling callback function to handle the case of error, thus avoiding error definitions for each statement. This generic error-handling function is the third parameter of Html5sql.process ().

Summary

Html5sql.process () A total of three parameters,
SQL-any of the forms described above can be
Finalsuccesscallback-A final, triggered after all SQL execution has completed successfully
Errorcallback-a common function that handles all errors in this procedure, when any error occurs, the current transaction is rolled back and the database version does not change.
To summarize, 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 the method that is required to create, migrate, and process a version of the database. This method detects whether the current version is consistent with the old version parameter (oldversion), executes the SQL statement in the parameter if it matches, and changes the database version to the value shown in the NewVersion parameter.
Oldversion-The version number of the database that needs to be modified, the default initial value is ""
NewVersion-The new value you assigned
SQL-The SQL statement you want to execute. Specific instructions see html5sql.process () section
Finalsuccesscallback-function called after successful execution
Errorcallback-Universal data processing function, as in html5sql.process (), when an error occurs, the entire transaction is rolled back, and the version number is not changed.

SOURCE Depth

The biggest problem with this JS library is that if you want to manipulate multiple databases at the same time, it can cause confusion, which the author does not seem to think much about. The other thing is that the error recovery process for this batch of SQL statements is still not perfect.

Add one:

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 a database reference is obtained.

Add two:
There are also two properties for debugging, Logerrors and Loginfo: false by default and set to True to see the process of each step. Because the console.log of the console is called, an error may be raised on some browsers.

Add Three:

For the process method, no matter what form of SQL parameter you take, it will eventually be converted to the form of a SQL object.
When the SQL statement contains only the select operation, the internal use of the Readtransaction method, note the difference between readtransaction and transaction.
This is the readtransaction, this is to ensure that the table is not written, this is a safe move, of course, can also be used transaction. However, the Readtransaction method has some compatibility problems, so it should be guaranteed to be correct before use.

Reprint please indicate from small Xishan "http://www.cnblogs.com/xesam/"
This address: http://www.cnblogs.com/xesam/archive/2012/03/10/2389365.html

Mode 2 "Sql.js":

For JS access to the SQLite database support, you can use: https://github.com/kripken/sql.js/
Small amount of data support is also OK, if the single-table data volume is too large or not recommended to use the JS operation directly.

Write a database to the disk

You need to convert the result of to db.export a buffer

Require ("FS");  // [...] (Create the database) db. export (); Buffer (data); FS. Writefilesync ("Filename.sqlite", buffer);         

See:https://github.com/kripken/sql.js/blob/master/test/test_node_file.js

"Translation" HTML5 development--Lightweight web database repository Html5sql.js