Web SQL was introduced by Apple and is based on a free database SQLite. It will be discontinued very soon, and I intend to add it here only to show you how it could be used and how we can migrate to other advanced techniques without glitches. In the following code, we created a new shopping_cart_web_sql project as a copy of the project from the Web Storage section and added WebSQLStorageManager into the StorageManager class:
abstract class StorageManager {
factory StorageManager() {
if (WebSQLStorageManager.supported) {
return new WebSQLStorageManager();
} else if (WebStorageManager.supported) {
return new WebStorageManager();
} else {
return new CookieStorageManager();
}
}
Future<String> getItem(key);
Future setItem(key, value);
Future removeItem(key);
}First of all, check whether the web browser supports Web SQL and instantiate it if successful. You should specify the version and initial size of the database to be used. The web browser support for Web SQL can be quickly checked with the supported property SqlDatabase class. Web SQL needs a big preparation before it can be used. First of all, we need to open the database. After the database is open, we can create a table if it does not exist. Web SQL has a more complex API than a cookie and Web Storage; each method increases in size exponentially. It is vital that all methods must execute very specific SQL statements. WebSQL also supports read and write transactions. When a transaction begins, you need to specify the key that will be assigned to the SQL parameters. The instance of SqlResultSet keeps a track of the transactions. The rows.isEmpty property of SqlResultSet is an important property that tells us exactly how many rows were returned, as shown in the following code:
class WebSQLStorageManager implements StorageManager {
static const SHOPPING = "SHOPPING";
static const PRODUCT = "PRODUCT";
static const TRANS_MODE = "readwrite";
static final String VERSION = "1";
static const int SIZE = 1048576;
SqlDatabase _database;
static bool get supported => SqlDatabase.supported;
Future<SqlDatabase> _getDatabase(
String dbName, String storeName) {
if (_database == null) {
_database = window.openDatabase(dbName, VERSION,
dbName, SIZE);
var sql = 'CREATE TABLE IF NOT EXISTS ' +
storeName +
' (id NVARCHAR(32) UNIQUE PRIMARY KEY, value TEXT)';
var completer = new Completer();
_database.transaction((SqlTransaction tx) {
tx.executeSql(sql, [],
(SqlTransaction txn, SqlResultSet result) {
completer.complete(_database);
}, (SqlTransaction transaction, SqlError error) {
completer.completeError(error);
});
}, (error) => completer.completeError(error));
return completer.future;
} else {
return new Future.sync(() => _database);
}
}
Future<String> getItem(key) {
var sql = 'SELECT value FROM $PRODUCT WHERE id = ?';
var completer = new Completer();
_getDatabase(SHOPPING, PRODUCT).then((SqlDatabase database) {
database.readTransaction((SqlTransaction tx) {
tx.executeSql(sql, [key],
(SqlTransaction txn, SqlResultSet result) {
if (result.rows.isEmpty) {
return completer.complete(null);
} else {
Map row = result.rows.first;
return completer.complete(row['value']);
}
}, (SqlTransaction transaction, SqlError error) {
completer.completeError(error);
});
});
});
return completer.future;
}For the create and update operations, we use the following transaction and specify the special SQL statement, and we need to specify the SQL parameters key and value as well:
Future setItem(key, value) {
var sql = 'INSERT OR REPLACE INTO $PRODUCT (id, value) ' +
'VALUES (?, ?)';
var completer = new Completer();
_getDatabase(SHOPPING, PRODUCT).then((SqlDatabase database) {
database.transaction((SqlTransaction tx) {
tx.executeSql(sql, [key, value],
(SqlTransaction txn, SqlResultSet result) {
return completer.complete(value);
}, (SqlTransaction transaction, SqlError error) {
completer.completeError(error);
});
}, (error) => completer.completeError(error));
});
return completer.future;
}To remove an item, we use the following transaction and specify the key parameter that will be assigned to the SQL parameters:
Future removeItem(key) {
var sql = 'DELETE FROM $PRODUCT WHERE id = ?';
var completer = new Completer();
_getDatabase(SHOPPING, PRODUCT).then((SqlDatabase database) {
database.transaction((SqlTransaction tx) {
tx.executeSql(sql, [key],
(SqlTransaction txn, SqlResultSet result) {
return completer.complete();
}, (SqlTransaction transaction, SqlError error) {
completer.completeError(error);
});
}, (error) => completer.completeError(error));
});
return completer.future;
}
}Launch our application and change the number of products. Expand the Web SQL tree item from the Resources tab to see the Web SQL storage data. The following screenshot shows the SHOPPING database and the PRODUCT table with the stored data:
The Web SQL database API isn't actually a part of the HTML5 specification. Therefore, it's time to migrate to IndexedDB if you have a code that uses Web SQL.