Angular WebSql Service
Helps you generate websql simple queries and run them without writing any sql code. This version additionally also supports
the brodysoft/Cordova-SQLitePlugin. The code will auto-detect which
openDatabase()
call to use.
Setup
bower install angular-websql
- Include the
angular-websql.min.js
and angular itself. - Add
angular-websql
as a module dependency to your app.
TODOS
- merge
limitedOrderedSelect
andorderedSelect
intoselect
statement
Usage
1- Add $webSql
provider to a controller.
2- Open a database. See method.
3- Use returned database object's methods.
Methods
Open Database
$webSql.openDatabase(dbName, version, desc, size)
Example:
$scope.db = $webSql.openDatabase('mydb', '1.0', 'Test DB', 2 * 1024 * 1024);
1- Database name
2- Version number
3- Text description
4- Size of database
Returns
An object, containing database operation methods, is returned with openDatabase
method.
All methods has optional callback parameter which takes query result object as parameter.
These methods are:
- createTable()
- dropTable()
- update()
- delete()
- select()
- orderedSelect()
- limitedOrderedSelect()
- selectAll()
Database Methods
Create Table
createTable(string tableName, object fields, [callback])
Example:
createTable('user', {
"id":{
"type": "INTEGER",
"null": "NOT NULL",
"primary": true, // primary
"auto_increment": true // auto increment
},
"created":{
"type": "TIMESTAMP",
"null": "NOT NULL",
"default": "CURRENT_TIMESTAMP" // default value
},
"username":{
"type": "TEXT",
"null": "NOT NULL"
},
"password": {
"type": "TEXT",
"null": "NOT NULL"
},
"age": {
"type": "INTEGER",
"null": "NOT NULL"
}
})
Drop Table
dropTable(string tableName, [callback])
Insert
insert(string tableName, object fields, [callback])
Example:
$scope.db.insert('user', {"username": 'pc', "password": '1234', 'age': 22}, function(results) {
console.log(results.insertId);
})
INSERT INTO user (username, password, age) VALUES('pc', '1234', 22)
Update
update(string tableName, object fields, [callback])
Examples:
$scope.db.update("user", {"username": 'paulo.caldeira'}, {
'id': 1
})
UPDATE user SET username='paulo.caldeira' WHERE id=1
$scope.db.update("user", {"age": 23}, {
"username": {
"operator":'LIKE',
"value":'paulo.*'
"union":'AND' // condition suffix
},
"age": 22
})
UPDATE user SET age=23 WHERE username LIKE 'paulo.*' AND age=22
Delete
delete(string tableName, object where, [callback])
$scope.db.del("user", {"id": 1})
DELETE user WHERE id=1
Select
select(string tableName, object where, [callback])
$scope.db.select("user", {
"age": {
"value":'IS NULL',
"union":'AND'
},
"username":'IS NOT NULL'
}, function(results) {
$scope.users = [];
for(i=0; i < results.rows.length; i++){
$scope.users.push(results.rows.item(i));
}
$scope.$apply();
})
SELECT * FROM user WHERE age IS NULL AND username IS NOT NULL
Ordered Select
orderedSelect(string tableName, object where, orderByField, ascending, [callback])
$scope.db.orderedSelect("user", {
"age": {
"operator": '>=',
"value": '18'
}
}, 'age', true, function(results) {
$scope.users = [];
for(i=0; i < results.rows.length; i++){
$scope.users.push(results.rows.item(i));
}
$scope.$apply();
})
SELECT * FROM user WHERE age >= 18 ORDER BY age ASC
Limited Ordered Select
limitedOrderedSelect(string tableName, object where, orderByField, ascending, limit, [callback])
$scope.db.limitedOrderedSelect("user", {
"age": {
"operator": '>=',
"value": '18'
}
}, 'age', true, 5, function(results) {
$scope.users = [];
for(i=0; i < results.rows.length; i++){
$scope.users.push(results.rows.item(i));
}
$scope.$apply();
})
SELECT * FROM user WHERE age >= 18 ORDER BY age ASC LIMIT 5
Select All
selectAll(string tableName, [callback])
$scope.db.selectAll("user", function(results) {
$scope.users = [];
for(i=0; i < results.rows.length; i++){
$scope.users.push(results.rows.item(i));
}
$scope.$apply();
})
SELECT * FROM user
Operators
Your can use common operators like =
, >=
, <=
and LIKE
. You can use also NULL
and NOT NULL
as condition values.
Changelog
###0.0.8
- clean up if-clause monster
###0.0.7
- process SQLResult for successful inserts with a provided insertId
###0.0.6
- adapt update() logic to query + bindings logic
###0.0.5
- minor change: added $log for error logging
###0.0.4
- reverted stringify of console errors
###0.0.3
- fixed issue with "IN( ... )" using single quotes
###0.0.2
- added CREATE [UNIQUE] INDEX
- added
orderedSelect()
andlimitedOrderedSelect()
- added Cordova SQLiteplugin support