Uses µ Query Language to build JSON query object for easy querying.
This service generates Q-Server compatible JSON query objects.
Q-Server expects quries in the form of a JSON object. That format is documented on Q-Server. But sometimes writing a query expression is simpler than writing a JSON object. Even better, if the query language is easy to learn and use!
You may ask: what was wrong with SQL? Nothing! Except, if you want to minimize the query writing effort in your client app, and need a consistent interface to the data, no matter who the data provider is or when the data provider is changed, you can use µ query language.
You feed the service a collection of µ queries, to get the entries from a collection or table or any source that Q-Server is connected to. µ Query service will provide you a JSON query object, ready for consumption for the Q-Server.
In brief, it takes away the burden of writing complex JSON query object.
I'm sure it will be helpful to your product, but until test cases are added, please don't.
Check this:
- Query to retrieve complete table / collection from data provider is:
employeeTable
- Query to retrieve first 5 entries from table / collection from data provider is:
5 * employeeTable
- Query to retrieve first 5 entries from table / collection from data provider with specified fields is:
5 * employeeTable [employee_id, employee_name]
Easy enough?
Before you proceed further to study grammar of the language, please note that this code uses ANTLR.
There are 2 types of queries that you can build:
-
'Select' statements: These provide column data of the entries. The EBNF format of the query is:
select: num table filters order_by fields;
- num [Optional]: It is optional. When missing, all records are fetched. If given, only those number of 'top' or 'limit'ed records are fetched. Expected value is a natural number.
num: (NUMBERS '*')?;
: The number has to follow asterik (*
) symbol.
- table [Required]: This represents the name of the table or ollection. Expected format is name of table/collection without space as string.
- filters [Optional]: This is a collection of expressions containing field names, numerics and strings. The collection is given in format of:
filters: filtersFrag?;
: Filters are completely optional.filtersFrag: ( filter (, filter)* );
: Filter expressions, separated by,
will beAND
ed.filter: filterFrag ( | filterFrag)*;
: Filter expressions can beOR
ed using|
.filterFrag: variable OP variable | variable OP? [ num_or_strings ] | filterFrag;
: ThefilterFrag
in this expression helps in writing nested filters. It is contained by(
and)
. Refer exmples below, for more clarity.
- order_by [Optional]: This is a collection of names of the fields / columns, by which you want the data to be sorted. The EBNF expression is
order_by: ( { ID (, ID)* } )?;
: It is optional. You provide the names of the fields separated by comma (,
). It is contained by{
and}
.ID: [a-zA-Z_] [a-zA-Z_0-9]+;
: This is what ID stands for. First letter can be a character in A-Z range (ignoring case) or an underscore (_
). The rest of the literals can also include a digit.
fields
[Optional]: Here you provide names of fields to fetch. The EBNF format is:fields: ( [ (ID (, ID)*)? ] )?;
: It is contained by[
and]
.
- num [Optional]: It is optional. When missing, all records are fetched. If given, only those number of 'top' or 'limit'ed records are fetched. Expected value is a natural number.
-
'Count' statements: These provide a number of entries matching the given criteria. The EBNF format of the query is:
count: '#' table filters;
- # [Required]: This hash/sharp symbol (
#
) must be provided to make this query return you a count, instead of entries. - table [Required]: Same as explained in
select
. - filters [Optional]: Same as explained in
select
.
- # [Required]: This hash/sharp symbol (
Refer select.g4 for complete reference.
null
represents a NULL value, and is a recognized keyword.
Some examples:
// ============================= SELECT EXAMPLES ================================
// Returns 'employee_id' and 'employee_name' from 'employeeTable', limited to first 5.
`5 * employeeTable [employee_id, employee_name]`
// Returns all fields from 'employeeTable' where 'employee_id' is in collection [100, 101, 102].
`employeeTable(employee_id [2001, 2002])`
// Returns all fields from 'employeeTable' where 'employee_id' is **not** in collection
// [100, 101, 102].
`employeeTable(employee_id ![100, 101, 102])`
// Returns all fields from 'employeeTable' where 'employee_name' starts with text 'Jane',
// limited to first entry.
`1 * employeeTable(employee_name = "Jane*")`
// Returns all fields from 'employeeTable' where 'employee_name' ends with text 'Doe', limited
// to first entry.
`1 * employeeTable(employee_name = "*Doe")`
// Returns all fields from 'employeeTable' where 'employee_name' contains text 'Mary', limited
// to first entry.
`1 * employeeTable(employee_name = "*Mary*")`
// Returns all fields from 'employeeTable' where 'employee_name' does **not** match string pattern
// (starts with 'abc').
`employeeTable(employee_name != "abc*")`
// Returns all fields from 'employeeTable' where 'employee_id' is greater than 100.
`employeeTable (employee_id > 100)`
// Returns all fields from 'employeeTable' where 'employee_id' is greater than or equal to 100.
`employeeTable (employee_id >= 100)`
// Returns 'employee_id' and 'employee_name' from 'employeeTable' where 'employee_id' is greater than
// or equal to 100, sorted by assesment_status, employee_id and employee_name, in the given order.
`employeeTable (employee_id >= 100) {assesment_status,employee_id,employee_name}[employee_id, employee_name]`
// Returns top 7 records, with 'employee_id', 'employee_name' and 'business_unit' fields from 'employeeTable'
// where 'employee_id' matches pattern *816517* (contains 816517), sorted by 'employee_id'.
// This is also an example to show than the query can be split across multiple lines.
`7 * employeeTable (
employee_id = '*816517*'
) {
employee_id
} [
employee_id,
employee_name,
business_unit
]`
// Returns all fields from 'employeeTable' where
// * 'business_unit' is not empty and NULL
// * 'employee_name' is not empty and NULL
// * 'joining_date' is not empty and NULL
// sorted by 'employee_id'.
`employeeTable
(
(business_unit != '', business_unit != null) | (employee_name != '', employee_name != null)
, joining_date != ''
, joining_date != null
)
{employee_id}`
// The corresponding SQL query would be:
// SELECT * FROM employeeTable
// WHERE (
// business_unit <> '' AND business_unit IS NOT NULL
// OR
// employee_name <> '' AND employee_name IS NOT NULL
// )
// AND joining_date <> ''
// AND joining_date IS NOT NULL
// ============================= SELECT EXAMPLES ================================
// Returns count of entries in 'employeeTable' where 'employee_id' is greater than or equal to 100.
`# employeeTable (employee_id >= 100)`
// Returns count of entries in 'employeeTable' where
// * 'business_unit' is not empty and NULL
// * 'employee_name' is not empty and NULL
// * 'joining_date' is not empty and NULL
// sorted by 'employee_id'.
`# employeeTable
(
(business_unit != '', business_unit != null) | (employee_name != '', employee_name != null)
, joining_date != ''
, joining_date != null
)`
// The corresponding T-SQL query would be:
// SELECT TOP 1 COUNT(1) FROM employeeTable
// WHERE (
// business_unit <> '' AND business_unit IS NOT NULL
// OR
// employee_name <> '' AND employee_name IS NOT NULL
// )
// AND joining_date <> ''
// AND joining_date IS NOT NULL
Essentially, it boils down to this format structure:
- Select : +ve NUMBER "collection or table" ( filters ) { order by } [ fields ]
- Count : # "collection or table" ( filters )
Here is a sample input and output received:
{
config: {
"server": "localhost",
"options": {
// "instanceName": "SQLEXPRESS",
"trustedConnection": true,
"encrypt": false
}
},
db: 'ABCD',
select: [
'2 * employeeTable',
'employeeTable (employee_id > 1000)'
],
count: [
`# employeeTable
(
(business_unit != '', business_unit != null) | (employee_name != '', employee_name != null)
, joining_date != ''
, joining_date != null
)`
]
};
then, this is how result looks like:
{
"config": {
"server": "localhost",
"options": {
"trustedConnection": true,
"encrypt": false
}
},
"db": "ABCD",
"select": [
{
"employeeTable": {
"limit": 2
}
},
{
"employeeTable": {
"filter": [
{
"employee_id": {
"gt": 1000
}
}
]
}
}
],
"count": [
{
"employeeTable": {
"filter": [
{
"all": [
{
"or": [
{
"all": [
{
"business_unit": {
"not_eq": "\"\""
}
},
{
"business_unit": {
"not_eq": "null"
}
}
]
},
{
"all": [
{
"employee_name": {
"not_eq": "\"\""
}
},
{
"employee_name": {
"not_eq": "null"
}
}
]
}
]
},
{
"joining_date": {
"not_eq": "\"\""
}
},
{
"joining_date": {
"not_eq": "null"
}
}
]
}
]
}
}
]
}
The supported operators are:
Operator | Description |
---|---|
= | Equality check. Also, if the RHS (right hand side) includes * in the text can be used for pattern matching. |
!= | Inequality check. Also, if the RHS (right hand side) includes * in the text can be used for a negated pattern matching. |
[value1, value2, ..values..] | This expression provides collection of possible values to find in. The collection is contained by [ and ] . |
! [value1, value2, ..values..] | Just like collection check, but negated (i.e. excluding the collection values). |
> | Greater than, for numbers. |
>= | Greater than or equal to, for numbers. |
< | Less than, for numbers. |
<= | Less than or euqal to, for numbers. |
The returned object format is already explained by documentation of Q-Server.
The Pre-requsites are:
- Download Antlr 4 (complete, jar). Keep it in
antlr
folder.- Make sure the environment is configured to run Antlr jar file (like, JRE 8+ is installed, and environment variables set, if needed.)
- Run antlr to generate the lexer and parser files, from command prompt:
cd antlr && java -jar antlr-4.7-complete.jar -Dlanguage=JavaScript select.g4 && cd..
: This is required very first time, or whenever you modify the select.g4 file. A script has already been added to the package.json. To use it, runnpm run gen
.
- Run
npm start
.
There are a few, which correspond to the ones in Q-Server. They are:
- Only select and count can be queried. No other supported yet.
- Except the above documented operators and expressions, others are not supported yet.