sql-query-identifier
A SQL query identifier
Last updated 4 years ago by maxcnunes .
MIT · Original npm · Tarball · package.json
$ cnpm install sql-query-identifier 
SYNC missed versions from official npm registry.

sql-query-identifier

Build Status npm version view demo

Identifies the types of each statement in a SQL query (also provide the start, end and the query text).

This uses AST and parser techniques to identify the SQL query type. Although it will not validate the whole query as a fully implemented AST+parser would do. Instead, it validates only the required tokens to identify the SQL query type. In summary it identifies the type by:

  1. Scanning the tokens:
  • Comments
  • The initial keywords that identify the query type (e.g. INSERT, DELETE)
  • White spaces
  • String
  • Semicolon
  1. Parsing the tokens to identify the query:
  • Comments are ignored since they could include words that will give false positive to identify the type
  • Keywords are expected to be at the beginning of the query statement
  • White spaces are only required to identify keywords with multiple words (e.g. CREATE TABLE)
  • String values are ignored since they also could include false positive words
  • Semicolon identifies the end of the statement

So the best approach using this module is only applying it after executing the query over the SQL client. This way you have sure is a valid query before trying to identify the types.

Current Available Types

  • INSERT
  • UPDATE
  • DELETE
  • SELECT
  • TRUNCATE
  • CREATE_TABLE
  • CREATE_DATABASE
  • DROP_TABLE
  • DROP_DATABASE
  • UNKNOWN (only available if strict mode is disabled)

Execution types

Execution types allow to know what is the query behavior

  • LISTING: is when the query list the data
  • MODIFICATION: is when the query modificate the database somehow (structure or data)
  • INFORMATION: is show some data information such as a profile data
  • UNKNOWN: (only available if strict mode is disabled)

Installation

Install via npm:

$ npm install sql-query-identifier

Usage

import { identify } from 'sql-query-identifier';

const statements = identify(`
  INSERT INTO Persons (PersonID, Name) VALUES (1, 'Jack');
  SELECT * FROM Persons;
`);

console.log(statements);
[ 
  { 
    start: 9,
    end: 64,
    text: 'INSERT INTO Persons (PersonID, Name) VALUES (1, \'Jack\');',
    type: 'INSERT',
    executionType: 'MODIFICATION'
  },
  { 
    start: 74,
    end: 95,
    text: 'SELECT * FROM Persons;',
    type: 'SELECT',
    executionType: 'LISTING'
  } 
]

API

identify arguments:

  1. input (string): the whole SQL script text to be processed
  2. options (object): allow to set different configurations
  3. strict (bool): allow disable strict mode which will ignore unknown types (default=true)

Contributing

It is required to use editorconfig and please write and run specs before pushing any changes:

npm test

License

Copyright (c) 2016 Max Claus Nunes. This software is licensed under the MIT License.

Current Tags

  • 1.1.0                                ...           latest (4 years ago)

7 Versions

  • 1.1.0                                ...           4 years ago
  • 1.0.0                                ...           4 years ago
  • 0.4.0                                ...           4 years ago
  • 0.3.1                                ...           4 years ago
  • 0.3.0                                ...           4 years ago
  • 0.2.0                                ...           4 years ago
  • 0.1.0                                ...           4 years ago
Maintainers (1)
Downloads
Today 0
This Week 2
This Month 10
Last Day 0
Last Week 1
Last Month 12
Dependencies (0)
None
Dev Dependencies (9)

Copyright 2014 - 2016 © taobao.org |