ujjwalguptaofficial / sqlweb Goto Github PK

View Code? Open in Web Editor NEW

54.0 4.0 15.0 4.52 MB

SqlWeb is an extension of JsStore which allows to use sql query for performing database operation in IndexedDB.

Home Page: https://github.com/ujjwalguptaofficial/sqlweb/wiki

License: MIT License

JavaScript 84.49% TypeScript 2.60% HTML 1.23% PEG.js 11.68%

sql indexeddb jsstore storage syntax websql alternative-websql javascript-library typescript-library javascript

sqlweb's Introduction

SqlWeb

SqlWeb is an extension of JsStore which allows to use sql query for performing database operation in IndexedDB.

Website

https://github.com/ujjwalguptaofficial/sqlweb/wiki

Examples

import * as JsStore from 'jsstore';
import SqlWeb from "sqlweb";

// create jsstore connection
var connection = new JsStore.Instance('jsstore worker path');

// add SqlWeb 
connection.addPlugin(SqlWeb);

// run select query
connection.$sql.run("select * from Customers").then(function(result) {
    console.log(result);
});

For a complete example - check out below link.

sqlweb's People

Contributors

Stargazers

Watchers

Forkers

jenalgit esironal nenepadi henrichen apple666 dolly-chhabriya-altius zerocrise breezycloud pbblox o2alexanderfedin piterden devdoshi 4lolo vuthanh86

sqlweb's Issues

Wrong new-lines removing

Hello. Just read your code and...

sqlweb/src/util.ts

Line 24 in 42bb25f

query = query.replace(new RegExp('\n', 'g'), '').trim();

I believe you should replace it to the space. I will make PR for you.

Support for queries containing join

idbstudio, that uses sqlweb as I understood, seems not to recognise queries containing a join:

idbstudio --sql "select * FROM activity INNER JOIN place on activity.from_place = place.place_id ;"
{ message:
   'Expected One or more whitespaces, distinct, end of input, group, ignoreCase, limit, order, skip, or where but "I" found.',
  type: 'syntax_error' }

AND clause will not allow for boolean

First - this is an awesome project - thanks for making it.

I have a table called Customers where there is a boolean flag for IsActive.

When I say:
SELECT FROM Customers WHERE Type='new' && IsActive=true

I get:
Expected One or more whitespaces or column value but "t" found.

Any idea what might be happening?

How to Use upsert using sql web

How to Use upsert using sql web?

How to alias table names

can you please give me an example of how to alias table names in query

like below :

SELECT xx.workordertypeid, xx.createdon
FROM
msdyn_workordertype as xx

Is "return" option of INSERT clause working

Hello,
sqlweb is a nice plugin
Couple of weeks ago i had issue with getting return id of an inserted row. As far as i remember i used js from:
https://github.com/ujjwalguptaofficial/sqlweb/tree/master/examples/simple%20example/scripts
and add.js (i use pure js, no additional stuff like composer, etc.)

There is an example from the wiki where return keyword is to return rowId
INSERT INTO CUSTOMERS ("{ID:1,NAME:'Ramesh',AGE:32,ADDRESS:'Ahmedabad',SALARY:2000} return");
Is this "return" working, do i have an old version of the sqlweb from the aforementioned link? How is it supposed to work, can i link an "id" named column with the returned value?
The mentioned add.js use this for insert:
var query = new connection.$sql.Query("insert into Student values ({Name:'@name',Gender:'@gender',Country:'@country',City:'@city'})")
There is no "return" keyword there, if it were it would indicate to me that i was using it wrong. So i have no clue what should i do about the last inserted id.

And also there was issue with a syntax (i saw it using console.log) that the sqlweb didn't accept return keyword. It treated it as an error.

I also saw some differencies (just two maybe) between syntaxes in the wiki f.e.
https://github.com/ujjwalguptaofficial/sqlweb/wiki
there we have:
CREATE TABLE
CREATE DB
but here with the name in url Create-Database
https://github.com/ujjwalguptaofficial/sqlweb/wiki/Create-Database
we have what really works:
DEFINE dbname
DEFINE tablename
And it's fine, just it took some time to figure it out :) .

Also how do i create table name or database if it not exists, like in sqlite, quouting it from memory it would be:
CREATE TABLE IF NOT EXISTS

Thank you in advance for any information. Any update on documentation would also help others.

cannot select specific columns

i faced this issue

"{message: "column createdon exist in both table msdyn_workordertype & ntwapp_translation", type: "invalid_join_query"}"

the column existed on both tables so what can i do if i cannot select specific columns ?

How to write query for "DROP TABLE IF EXISTS TableName" or How to drop a table using sqlWeb()

Not working in ionic project

Hi ujjwalguptaofficial,

I am trying to use 'sqlweb' in my ionic 6 project but i am facing challenges like not able to create or initialize db, because db is not initialize so not able to perform any query. please give me suggestion how can i run this in ionic 6 and which dependencies should i install.

Plugin throws error when loading

I'm using the V3.13.6 versions of Jsstore/worker and sqlweb 1.6.

Uncaught TypeError: e.setup is not a function
at t.addPlugin (jsstore.min.js:6)

When I debug I see error here, and this is default.setup:
e.setup(this, t)

What am I doing wrong?

Bulk insert using sql web

Can I insert multiple records at once.

Module parse failed: Unexpected token (356:25)

I have error when I try to use it in my Vuejs project.
import * as SqlWeb from 'sqlweb';
ERROR in ./node_modules/sqlweb/dist/sqlweb.commonjs2.js
Module parse failed: Unexpected token (356:25)
You may need an appropriate loader to handle this file type.
| var columns = {};
| all.forEach(function(column){
| columns = {...columns,...column}
| });
| var versionData = ver==null?null:ver['version'];

To be compatible with sqlite, I loop through the query objects of the jsstore and concatenate them into sqlite syntax. Would it be easier to implement using sqlweb?

// 新增
connection.insert = async function(body) {
	// if(body['into']== 'Members') console.log('开始插入',body)
  // console.log('开始插入',body)
  if (!isOpen()) await openDb();
  //获取表格字段
  let tableItems = initTable.filter(el => {
    return el.name == body['into']
  })
  // console.log('tableItems',tableItems)
  const tableItem = {...tableItems[0]['columns']}
  Object.keys(tableItem).forEach(mkk => {
	// 如果是主键自增字段，删除数据，让他自增长
    if ('autoIncrement' in tableItem[mkk] && tableItem[mkk]['autoIncrement']) {
      delete tableItem[mkk];
	  return;
    }
	//如果设置了联合索引，删除字段不做处理
	if ('keyPath' in tableItem[mkk] ) {
		delete tableItem[mkk];
		return;
	}
  })
  
  let sqlStr = ``;
  Object.keys(body).forEach(key => {
    if (key == 'into') {
      if ('upsert' in body && body['upsert']) {
        // 如果存在旧数据与新数据冲突，删除旧数据插入新数据
        sqlStr += `REPLACE INTO ${body[key]} `
      } else {
        // 与主键冲突的数据会忽略插入
        sqlStr += `INSERT IGNORE INTO ${body[key]} `
      }
    }
    if (key == 'values') {
      let keys = "";
      let values = "";
      if (Array.isArray(body[key])) {
        Object.keys(tableItem).forEach(m => {
          keys += m + ',';
        })
        keys = keys.replace(/^(\s|,)+|(\s|,)+$/g, '');
		
        body[key].forEach(n => {
          values += `(`;
          Object.keys(tableItem).forEach(mk => {
            if (mk in n) {
              if (tableItem[mk]['dataType'] == 'number') {
                if (Number(n[mk]) == 0 || Number(n[mk])) {
                  values += `'${ Number(n[mk])}',`;
                } else {
                  values += '-1,';
                }
              } else {
                values += `'${ n[mk]? sqliteEscape(n[mk]) : ""}',`;
              }
            } else {
              if (tableItem[mk]['dataType'] == 'number') {
                values += '-1,';
              } else {
                values += '"",';
              }
            }
          })
          values = values.replace(/^(\s|,)+|(\s|,)+$/g, '');
          values += `),`;
        })
        // console.log('值',values)
        keys = keys.replace(/^(\s|,)+|(\s|,)+$/g, '');
        values = values.replace(/^(\s|,)+|(\s|,)+$/g, '');
        sqlStr += `(${keys}) VALUES ${values}`;
      } else {
        // console.log('进入222')
        Object.keys(body[key]).forEach(s => {
          keys += s + ',';
          // values+=body[key][s]+',';
          if (typeof body[key][s] == 'string') {
            values += `'${sqliteEscape(body[key][s]) }',`;
          } else {
            values += `${body[key][s]},`;
          }

        });
        keys = keys.replace(/^(\s|,)+|(\s|,)+$/g, '');
        values = values.replace(/^(\s|,)+|(\s|,)+$/g, '');
        sqlStr += `(${keys}) VALUES(${values})`;
      }

    }
  })
  // if(body['into']== 'Members') console.log('新增sql语句',sqlStr)
  // console.log('新增sql语句',sqlStr)
  return new Promise((resolve, reject) => {
    plus.sqlite.executeSql({
      name: DbName,
      sql: sqlStr,
      success: async (e) => {
        // console.log('插入成功...' + sqlStr)
        if (body['return']) {
          // console.log(body['into'])
          let rowId = await getLastId(body['into']);
          resolve(rowId);
        } else {
          resolve(e);
        }

      },
      fail(e) {
        console.log('e----', e)
        console.log('sqlStr', sqlStr)
        reject(e);
      }
    })
  })
}

this is not sql and wiki docs are wrong.

Better to archive this artifact.

Throws error when searching for empty string

I am using the latest versions of JsStore and SqlWeb.

To get around the can't search for null problem I put empty strings in those columns but when I try to select for them I get this: Expected One or more whitespaces or column value but "'" found."

If I put a value in the quotes it works.

select * from table where middle_name = ''

How do I search for an empty string?

"Table does not exist" error when query is executed after "Key already exists" error

I'm using this great library together with JsStore, in an iterative process of reading log records from many JSON files, processing and then storing into IndexedDB via SqlWeb + JsStore, and it works great.

However, like the title says, if I get an error of "key already exists", when the script moves to the next query (often on a different table), I get the error of "table does not exist", when it definitely exists.

I use connection.runSql(query).then(...).catch(...) but it still raises the table error for all following queries, until the whole process eventually stops.

column names alias

You can use "as" for aliasing a column name just like sql. Here is an example -

select Customers.CustomerName as name, Customers.ContactName as cName, Customers.CustomerID as cId from Orders join Customers 
on 
Orders.CustomerID=Customers.CustomerID

Originally posted by @ujjwalguptaofficial in #3 (comment)

aliasing is working fine but the returned query still contains all columns plus the alias column , what i need is how to select aliasing columns only without other columns

connection.addPlugin is not a function

I am testing one of the examples in jsstore and I am receiving the following error: formList.html:21 Uncaught TypeError: connection.addPlugin is not a function. I am testing it on a tablet. Everywhere I check for this error I can't find anything related. It would be nice to see real examples setting this up for using the jsstore. I want to have the sqlweb functionality in place to use sql on indexexDB stores. What am I missing? See code below taken from the wiki page at https://github.com/ujjwalguptaofficial/sqlweb/wiki.