Difference between revisions of "Mojo Storage Database"
Templarian (talk | contribs) (table edit) |
Templarian (talk | contribs) |
||
Line 28: | Line 28: | ||
==Creating a Database and a Table== | ==Creating a Database and a Table== | ||
− | < | + | <syntax lang="javascript"> |
var name = "MyDB"; // required | var name = "MyDB"; // required | ||
var version = "0.1"; // required | var version = "0.1"; // required | ||
Line 52: | Line 52: | ||
}); | }); | ||
} | } | ||
− | </ | + | </syntax> |
If you try to look for **MyDB** on the filesystem, you won't find it. The **openDatabase** method creates an entry in the **Databases** table in **/var/usr/home/root/html5-databases/Databases.db** which points to the actual location of your database. Note that the details in the Databases table are what you specified in openDatabase except for version. Version is contained in the **@@__@@WebKitDatabaseInfoTable@@__@@** table in the actual database. | If you try to look for **MyDB** on the filesystem, you won't find it. The **openDatabase** method creates an entry in the **Databases** table in **/var/usr/home/root/html5-databases/Databases.db** which points to the actual location of your database. Note that the details in the Databases table are what you specified in openDatabase except for version. Version is contained in the **@@__@@WebKitDatabaseInfoTable@@__@@** table in the actual database. |
Revision as of 21:02, 18 July 2009
This page was created to help others by giving a basic example of creating a new database for your application and storing some data in it.
SQL Overview
It needs to be mentioned that SQLite (what HTML5 uses), is not as controlling as other databases. For one it's not going to give you an error when you insert text into an integer, or anything like that so regex all input for users or limit it in some way.
Data Type | Example |
INTEGER | '0' '123' '3939' |
REAL | '1.1' '10.0' |
TEXT | 'foo' 'bar' |
BLOB | [binary data / images] |
NULL | absolutely nothing |
Creating a Database and a Table
<syntax lang="javascript"> var name = "MyDB"; // required var version = "0.1"; // required var displayName = "My Mojo-Driven database"; // optional var size = 200000; // optional
var db = openDatabase(name, version, displayName, size);
if (!db) {
Mojo.Log.error("Could not open database");
} else {
var sql = "CREATE TABLE IF NOT EXISTS 'my_table' (id INTEGER PRIMARY KEY, num REAL, data TEXT)"; // check sqlite data types for other values db.transaction( function (transaction) { transaction.executeSql(sql, // SQL to execute [], // array of substitution values (if you were inserting, for example) function(transaction, results) { // success handler Mojo.Log.info("Successfully created table"); }, function(transaction, error) { // error handler Mojo.Log.error("Could not create table: " + error.message); } ); });
} </syntax>
If you try to look for **MyDB** on the filesystem, you won't find it. The **openDatabase** method creates an entry in the **Databases** table in **/var/usr/home/root/html5-databases/Databases.db** which points to the actual location of your database. Note that the details in the Databases table are what you specified in openDatabase except for version. Version is contained in the **@@__@@WebKitDatabaseInfoTable@@__@@** table in the actual database.
- Note**: I have been unable to use my own function as a success or an error handler. Some of the existing applications seem to be able to do so, but others write anonymous functions as in the example.
openDatabase
In the examples contained in this page, the database that is created has a maximum storage capacity of 1 MB. If you need a larger database, then append ext: to the beginning of the database name. For example:
<syntaxhighlight lang="javascript"> var db = openDatabase("ext:MyDB", "0.1"); </syntaxhighlight>
This will create the database in **/media/internal/.app-storage**.
Inserting a Row
<syntaxhighlight lang="javascript"> var myNum = 512.785; var test = "I'm test data!";
var db = openDatabase("MyDB", "0.1"); // this is all that is required to open an existing DB var sql = "INSERT INTO 'my_table' (num, data) VALUES (?, ?)";
db.transaction( function (transaction) {
transaction.executeSql(sql, [myNum, test], function(transaction, results) { // success handler Mojo.Log.info("Successfully inserted record"); }, function(transaction, error) { // error handler Mojo.Log.error("Could not insert record: " + error.message); } );
}); </syntaxhighlight>
Inserting Multiple Rows
With asynchronous database methods, you can really screw things up if you try to do too many transacations in rapid succession. Luckily, you can run as many executeSql methods inside a single transaction as you need.
<syntaxhighlight lang="javascript"> var dataArray = []; for(var i = 0; i < 100; i++) {
dataArray[i] = i;
}
var db = openDatabase("MyDB", "0.1");
db.transaction( function (transaction) {
for(var i=0; i < dataArray.legnth; i++) { var sql = "INSERT INTO 'my_table' (num, data) VALUES (?, ?)"; transaction.executeSql(sql, [dataArray[i], dataArray[i]], function(transaction, results) { // success handler Mojo.Log.info("Successfully inserted record"); }, function(transaction, error) { // error handler Mojo.Log.error("Could not insert record: " + error.message); } ); }
}); </syntaxhighlight>