HTML5-based Websql local device database

HTML5 's Web SQL Database uses local and session storage to implement simple object persistence and to process tedious relational data.

In the Web SQL database specification ( see Introduction), the Web SQL database introduces a set of APIs that use SQL to manipulate client databases (Client-side database), which are asynchronous (asynchronous), so the author will find anonymous functions useful when using this set of APIs.

The three core methods defined in the Web SQL Database specification:

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

2. Transaction: This method allows us to control the transaction commit or rollback, depending on the situation.

3, ExecuteSQL: This method is used to execute the real SQL query

　　

Open Database

The OpenDatabase method can open a database that already exists and is created if it does not exist:

var db = OpenDatabase (' MyDatabase ', ' 1.0 ', my db ', 2 * 1024);

The five parameters in Opendatabasek are: database name, version number, description, database size, create callback. Creating a callback does not create a database either.

• Below is a useful way to create a database in a Web-based package

functionInitdb () {varMyDB =NULL; Try {         if(!window.opendatabase) {//Current browser does not have database supportAlert (' DB not supported '); } Else {             varShortName = ' TestDB '; varVersion = ' 1.0 '; varDisplayName = ' Test offline database '; varMaxSize = 65536;//bytesMyDB =OpenDatabase (shortname, version, DisplayName, maxSize); }     } Catch(e) {//this starts the exception handling.        if(E = =Invalid_state_err) {             //database version exception.Alert ("Invalid database version.")); } Else{alert ("Unknown Error" +e+ "."); }     }     //return to the created database instance    returnMyDB; }

Execute DATABASE statement　　

The execution of all SQL statements is nested within a single transaction, guaranteeing the integrity and consistency of the data. The results of executing the SQL statement and the error handling need to be specified in the last two parameters of the ExecuteSQL function, which can be omitted from the two callback method parameters.

　　

varSQL1 = ' CREATE TABLE IF not EXISTS User (name TEXT, age INTEGER); 'varSQL2 = ' INSERT into User values (?,?) '; varSQL3 = ' SELECT * from User WHERE name=? '; varSql4 = ' DELETE from User where name=? '; varSQL5 = ' UPDATE User SET age=? WHERE name=? '; Mydb.transaction (function(TX) {tx.executesql (sql,[' Parameters '],function(tx,results) {varLen =results.rows.length; varresult = Results.rows.item (0). Name},function(Tx,error) {}); });

Persistence.js

All SQL operations require result processing and error handling in the specified function, and traditional SQL statements are cumbersome to write, and we all miss the ORM tool in the Java language.

Fortunately, there is no shortage of creative developers in the development community, and many open source Web SQL ORM tools have emerged.

Persistence.js is a JavaScript framework that was originally designed to make OR Mapping HTML Web sql to facilitate fast WEB SQL access by client developers.

Later, with the development of the framework, it began to gradually peel off its reliance on the database and become a framework that could support a variety of databases.

(1) Introduction of Persistence.js

Persistence.js divides its core modules into smaller JavaScript files, effectively guaranteeing high-performance loading, and users only need to introduce the appropriate JavaScript files when they use a function module. Here we use Persistence.js,persistence.store.sql.js and persistence.store.websql.js, these three files are required to use WEB SQL. Persistence.store.memory.js can also be introduced if the user wants to temporarily save the data in memory as an alternative to the Web SQL API that the browser does not support.

<script type= "Text/javascript" src= "Js/persistence.js" ></script>

<script type= "Text/javascript" src= "Js/persistence.store.sql.js" ></script>

<script type= "Text/javascript" src= "Js/persistence.store.websql.js" ></script>

<script type= "Text/javascript" src= "Js/persistence.store.memory.js" ></script>

　　files : Https://github.com/coresmart/persistencejs

(2) inserting and deleting data

Inserting a record into the user table is simply saving a user object and calling the flush () method to persist.

Persistence.js Inserting data

var mark = new User ({name: "Mark", age:60}); 　　 Persistence.add (Mark); Persistence.flush ();

Deleting a record deletes a User object directly, as shown in Listing 10.

Persistence.js Deleting data

Persistence.remove (Mark);  Persistence.flush ();

(3) Support for association between tables

Traditional databases support a one-to-many, many-to-many, multi-pair Table association, which is well supported in many Java ORM tools, and similar implementations can be found in persistence.js. For example, the user table in the previous example, we need to add a new address table to record the addresses of the user object, each user object may have multiple addresses. We can easily implement the association of tables.

 var  address = persistence.define (' Address '  "text" " text " });  User.hasmany ("Addresses", Address, "user");  Persistence.schemasync ();  var  addr1 = new   Address ({detail: "Addr1", ZipCode: "Code1"});  var  addr2 = new   Address ({detail: "ADDR2", ZipCode: "Code2"});  Persistence.add (ADDR1);  Persistence.add (ADDR2);  Mark.addresses.add (ADDR1);  Mark.addresses.add (ADDR2); Persistence.flush ();  

The Hasmany () method is a built-in method for each object defined by Persistence.define, and by performing the Hasmany () method described above, the User table will add an attribute addresses as an association to the Address table, and Addre SSEs can be associated to multiple Address objects. The Address object also adds a user property that associates an address object to a user object through the user property.

Similarly, a user can call the HasOne () method of a Table object to define that the Address object is only associated to a user object.

(4) Query data

Persistence.js provides simple and powerful query syntax, and querying data is only a fairly straightforward one or two API call. The query portal for a table is actually starting with the object defined by the Persistence.define. For example, the User defined in the above article.

User.all () will return directly to a querycollection that will contain all the User objects.

User.load () can load a user object based on the username passed in by the users.

User.findby () can query a collection of user objects that meet the criteria and return them based on the users ' passed-in object properties and the values of the properties.

 User.all (). One (function   (User) { Console.log (user);  });  //  query out the first User object   User.load ( "6c22fd66801c41728ae5a6bce0a8ee54", function   (user) {Console.log (user.name); });  //  query out ID " 6c22fd66801c41728ae5a6bce0a8ee54 "User object   User.findby (" name "," Mark ", function  　　  //  query out name is Mark's User object  

The querycollection,persistence.js returned for the query provides rich extensibility capabilities to organize the data in the collection, such as Filter,or,and,order,limit, and so on, where interested readers can Persistence.js's official web site to find the relevant introduction.

　　However!!!

　　

It's a little awkward for him to meow.

　　The websql was abandoned by the group on November 18, 2010. But it does not affect the use of its current functions, is still very strong, a few users.

HTML5-based Websql local device database