wSQL

wSQL - angularJS active record module for WebSQLite database - database in the browser. U can persist your data for your PhoneGap HTML5 applications build with ionic or angular. Good solution for mobile applications with offline mode.

install from bower

bower install angular-wsql

A sample of config file(u need to set your DB schema there) can be found in this repo at src/wSQL.db.config.js (just cope it in your project)

methods:

• wSQL.select()
• wSQL.insert(table_name, data)
• wSQL.batch_insert(table_name, data)
• wSQL.update(table_name, data)
• wSQL.delete(table_name)

optionally u can perform query directly with passing SQL

• wSQL.query(custom_sql)

optionally u can perform queries upon tables

• wSQL.create_table(table_name, table_fields)
• wSQL.drop_table(table_name)

methods to query request and generate results (all return promises):

• query - get all results
• row - get one row
• col - get one column

    ...
    .query().then(function(result){
        // query returns promise
        console.log(result);
    }, function(error){
        // error result
        console.log(error);
    })

list of query modifiers:

• where
• where_in
• and
• and_in
• or
• or_in
• join
• left_join
• having
• set
• group_by
• order_by
• limit //limit also does offset as a second argument

no full DOC yet but samples below -- pretty simple

also examples can be found in examples/example1

angular.module('sampleApp', [
    "wSQL.db"
])
.controller('SampleController', function($scope, wSQL) {

    // SELECT id, category_id FROM table1 WHERE id=? LIMIT 1
    wSQL.select("id, category_id")
        .from("table1")
        .where("id", 3)
        .col()
        .then(function(d){
            console.log(d);
        });

    // SELECT * FROM table1 WHERE id>? AND category_id=? OR category_id=? LIMIT 1
    wSQL.select("*")
        .from("table1")
        .where("id", 3, ">")
        .and("category_id", 15)
        .or("category_id", 14)
        .row()
        .then(function(d){
            console.log(d);
        });

    // SELECT * FROM table1 WHERE id IN (?, ?, ?)
    wSQL.select()
        .from("table1")
        .where_in("id", [3,4,6])
        .query()
        .then(function(d){
            console.log(d);
        });

    // SELECT id, category_id, count(*) as amount FROM table1 GROUP BY category_id
    wSQL.select("id, category_id, count(*) as amount")
        .from("table1")
        .group_by("category_id")
        .query()
        .then(function(d){
            console.log(d);
        });

    // SELECT id, category_id, count(*) as amount FROM table1 GROUP BY category_id HAVING category_id=? OR category_id=?
    wSQL.select("id, category_id, count(*) as amount")
        .from("table1")
        .group_by("category_id")
        .having("category_id", 15)
        .or("category_id", 10)
        .query()
        .then(function(d){
            console.log(d);
        });


    // SELECT id, category_id, count(*) as amount FROM table1 WHERE id=57 GROUP BY category_id HAVING category_id=10
    wSQL.select("id, category_id, count(*) as amount")
        .from("table1")
        .where("id", 57)
        .group_by("category_id")
        .having("category_id", 10)
        .or("category_id", 15)
        .query()
        .then(function(d){
            console.log(d);
        });

    // INSERT INTO table1 (category_id) VALUES (?)
    wSQL.insert("table1", {
        category_id: 14
    }).then(function(insert){
        console.log("insert_id");
        console.log(insert.insertId);
    });

    // INSERT OR IGNORE INTO table1 (category_id,category_name,test_field) SELECT ? as category_id,? as category_name,? as test_field UNION SELECT ?,?,? UNION SELECT ?,?,? UNION SELECT ?,?,?
    wSQL.batch_insert_or_ignore("table1", [
        {
            category_id: rand(),
            category_name: "ttdasdas1",
            test_field: "test_field1"
        },
        {
            category_id: rand(),
            category_name: "ttdasdas2",
            test_field: "test_field2"
        },
        {
            category_id: rand(),
            category_name: "ttdasdas3",
            test_field: "test_field3"
        },
        {
            category_id: rand(),
            category_name: "ttdasdas4",
            test_field: "test_field4"
        }
    ]).then(function(insert){
        console.log("insert_id");
        console.log(insert.insertId);
    });
    
    // INSERT INTO table1 (category_id,category_name,test_field) SELECT ? as category_id,? as category_name,? as test_field UNION SELECT ?,?,? UNION SELECT ?,?,? UNION SELECT ?,?,?
    wSQL.batch_insert("table1", [
        {
            category_id: 1,
            category_name: "ttdasdas1",
            test_field: "test_field1"
        },
        {
            category_id: 2,
            category_name: "ttdasdas2",
            test_field: "test_field2"
        },
        {
            category_id: 3,
            category_name: "ttdasdas3",
            test_field: "test_field3"
        },
        {
            category_id: 10,
            category_name: "ttdasdas4",
            test_field: "test_field4"
        }
    ]).then(function(insert){
        console.log("insert_id");
        console.log(insert.insertId);
    });

    // INSERT OR IGNORE INTO table1 (category_id,category_name,test_field) SELECT ? as category_id,? as category_name,? as test_field UNION SELECT ?,?,? UNION SELECT ?,?,? UNION SELECT ?,?,?
    wSQL.batch_insert("table1", [
        {
            category_id: rand(),
            category_name: "ttdasdas1",
            test_field: "test_field1"
        },
        {
            category_id: rand(),
            category_name: "ttdasdas2",
            test_field: "test_field2"
        },
        {
            category_id: rand(),
            category_name: "ttdasdas3",
            test_field: "test_field3"
        },
        {
            category_id: rand(),
            category_name: "ttdasdas4",
            test_field: "test_field4"
        }
    ],true).then(function(insert){
        console.log("insert_id");
        console.log(insert.insertId);
    });


    // UPDATE table1 SET category_id=? WHERE id=?
    wSQL.update("table1", {category_id: 6})
        .where("id", 19)
        .query()
        .then(function(result){
            console.log(result);
        });

    // DELETE FROM table1 WHERE id = ?
    wSQL.delete("table1")
        .where("id", 18)
        .query()
        .then(function(data){
            console.log(data);
        });

    /**
     * Direct Queries
     * (better to use ORM stuff)
      */
    // Simple direct query
    wSQL.query("SELECT * FROM table1").then(function(result){
        console.log(result);
    });

    // Direct Query with params in SQL
    wSQL.query("SELECT * FROM table1 WHERE id IN (1,5)").then(function(result){
        console.log(result);
    });

    // Pass query params in second argument to avoid escape errors
    wSQL.query("SELECT * FROM table1 WHERE category_name=?", ["'♥,♥,|'♥,♥,♥'"]).then(function(result){
        console.log(result);
    });

    // Or u can get error
    wSQL.query("SELECT * FROM table1 WHERE category_name= '♥,♥,|'♥,♥,♥'").then(function(result){
        console.log(result);
    }, function(error){
        console.log("___error");
        console.log(error);
    });

    /**
     * Like Queries
     */

    wSQL.select()
        .from("table1")
        .like("category_name", "my_query")
        .query()
        .then(function(d){
            console.log(d);
        });

    wSQL.select()
        .from("table1")
        .like("category_name", "query", "before")
        .query()
        .then(function(d){
            console.log(d);
        });

    wSQL.select()
        .from("table1")
        .like("category_name", "my_", "after")
        .query()
        .then(function(d){
            console.log(d);
        });

    wSQL.select()
        .from("table1")
        .like("category_name", "y_quer", "both")
        .query()
        .then(function(d){
            console.log(d);
        });

    wSQL.select()
        .from("table1")
        .like("category_name", "%query", false)
        .query()
        .then(function(d){
            console.log(d);
        });

    wSQL.select()
        .from("table1")
        .like("category_name", "s")
        .and_like("test_field", "field4", "before")
        .query()
        .then(function(d){
            console.log(d);
        });

    wSQL.select()
        .from("table1")
        .like("category_name", "s")
        .like("test_field", "field4", "before")
        .query()
        .then(function(d){
            console.log(d);
        });

    wSQL.select()
        .from("table1")
        .like("category_name", "s")
        .or_like("test_field", "field4", "before")
        .query()
        .then(function(d){
            console.log(d);
        });

    wSQL.select()
        .from("table1")
        .where("test_field", "test_field4")
        .like("category_name", "s")
        .or_like("test_field", "field4", "before")
        .query()
        .then(function(d){
            console.log(d);
        });

});