Skip to content
forked from igorizr1/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.

Notifications You must be signed in to change notification settings

SmailHammour/wSQL

 
 

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

50 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

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);
        });

});

About

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.

Resources

Stars

Watchers

Forks

Packages

No packages published

Languages

  • JavaScript 100.0%