#Overview

Jeli Query Language or for short jQl is a part of jEliDb core written for the purpose of perform query using it's own query Syntax. JQL is easy to use, with or without SQL knowledge. Aside using JQL to perform query task, it can also be used for other actions such as like calling REST APIS provided by JELIDB Applications.

jQl accepts two paramaters

• {string} Command (required)

  Define the query you want to perform, using our query syntax

• {Object} Handlers (required)

  Define how to handle response from jQl method.

  • onSuccess

  • onError

• {Object} params (Optional)

  Pass arguments to your query, using the placeholder pattern. %argument%

DB
.jQl(QUERY_TO_PERFORM,{
onSuccess : function(){},
onError : function(){}
},
arguments
);

#Create

• Syntax : create -TABLE_NAME -[COLUMNS]

• Details

  COLUMNS command is an ARRAY OF OBJECTS with list of columns with their configuration. A column must contain a type of input to accept. For empty column pass an empty ARRAY : []

#Alter

• Syntax : alter -TABLE_NAME

• Add New column

  Syntax: -add -n -c -COLUMN_NAME -CONFIGURATION

  Details:

  Add a new column to an existing table. Configuration must be a javascript Object

  Command: ALTER -members -add -new -column -fullName -{type:"VARCHAR",defaultValue:"Foo Bar"}

• Add Primary Key

  Syntax: -add -p -key -column_name

  Details:

  Alter by adding setting an existing column as primary key.

  Command: ALTER -TBL_NAME -add -p -key -column_name

• Drop a column

  Syntax: -drop -column_name

  Details:

  Drop an existing column from a table, dropped column will be removed from table rows including data.

  Command: ALTER -TBL_NAME -drop -column_name

#Truncate

• Syntax : -truncate -tableName -flag(yes or no)

• Details:

  truncate -members -yes

  Truncating a table deletes every record in the table, flag must be set to yes before task is performed, else an error exception will be thrown.

#Drop

• Syntax : drop -t -TBL_NAME -flag(yes or no)

• Details:

  Dropping a table removes the table from the Database, flag must be set to yes before task is performed, else an error exception will be thrown.

#Insert

• Syntax : insert -data -table_name

• Details:

  -data parameter accepts ARRAY of OBJECTS or ARRAY of ARRAYS. Column name must be defined when using ARRAY of Objects, while for ARRAY of ARRAYS the data must follow according to their column position in the table.

  In Array of Objects AUTO_INCREMENT field can be omitted, while for ARRAY of Arrays cannot.

  insert -%data% -TBL_NAME data = [{fullName:"Foo Bar",email:"foo@bar.com"},{fullName:"Bar Foo",email:"bar@foo.com"}]

  Inserting Data with Array of Arrays into a Table

  insert -%data% -TBL_NAME data = [[1,"Foo Bar","foo@bar.com"],[2,"Bar Foo","bar@foo.com"]]

• As said above, the ID field cannot be omitted even if having the auto_incremement configuration when inserting data with ARRAY of ARRAYS. Data in each array will be Mapped to the columns

#Update

• Syntax : update -TBL_NAME -records (optional)-jQlWHERECLAUSE

• Details:

  records parameter accepts only 2 type of data (OBJECT and params )

  • OBJECT : {fullName:"Foo Boo",email:"foo@boo.com"}
  • PARAM : fullName="Foo Boo",email="foo@boo.com"

• If where clause is not defined, all rows in the table will be updated.

#Select

• Syntax : select -[fields] -[TABLE_NAME] -Clause[ -join -where( CLAUSE[Like|IN|NOTIN]]) -limit(start,end) -orderBy(ASC|DESC) -groupBy(COLUMN_NAME)

  fields parameter accepts below values.

  • * - To select all columns
  • -COLUMN_NAME: Select a particular column set from the table ( Seperate multiple columns with a comma)
  • -COLUMN_NAME as user-defined : select columns and return as user-defined Name (eg : -fullName as fName)
  • -JQLFIELDCLAUSE : use the Select Clause defined by JQL e.g COUNT() as total.

• Details:

  Using JQLSELECTCLAUSE in Select

  • WHERE() :

    List of WHERECLAUSE Functions

    • #IN()

      IN(@select(t2.column) @table(t2) OR @value([values]) @field(t1.column))

    • #NOTIN()

      NOTIN(@select(t2.column) @table(t2) OR @value([values]) @field(t1.column))

    • #LIKE()

      Like(@value(needle) @field(COLUMN))

    Operands and name used in JQL

    • $is Symbol ===
    • $isEqual Symbol = or ==
    • $gte Symbol >=
    • $gt Symbol >
    • $lte Symbol <=
    • $lt Symbol <
    • $not Symbol !=
    • $isNot Symbol !==
    • $inArray Symbol []
    • $inClause Symbol [=]
    • $noInArray Symbol ![]
    • $noInClause Symbol [!]
    • $lk Symbol ~
    • $! Symbol ! or !!
  • Join Clause :

    Syntax: select -t1.Column, t2.Column -TBL as t1, TBL2 as t2 -join(@table(t2) @clause(INNER) @on(t1.column=t2.column))

    Join Clause to use
    • INNER
    • LEFT
    • RIGHT
    • OUTER

• When performing join clause all required tables must exists else error exception will be thrown.

#Delete

• Syntax : delete -TABLE_NAME -jQlWHERECLAUSE

• Details:

  If where clause is not defined, all rows in the table will be deleted.

#InsertReplace

• Syntax : insert -%data% -TBL_NAME -replace -COLUMN_NAME

• Details:

  Please refer to api section for more details.

#Batch

• Syntax : batch -%data%

• Details:

  Please refer to api section for more details.

#Import

• Syntax : import -TABLE_NAME -[fileType[json|jql|csv|html]]

• Details:

  Importing record opens a dialog box to select the file you want to import to the table, accepted files are (.csv,.html,.json, .jql)

  if the table doesn't exist it will be created and data will be inserted into the table.

#Export

• Syntax : export -TABLE_NAME --[fileType[json|jql|csv|html]] -task[download|print] (optional) -fileName

• Details:

  Accepted types are (.csv,.html,.json, .jql), title is optional and can be left blank

#Sync

• Syntax : sync -TBL_NAME -force

• Details:

  Without definig a tableName, all tables in the database will be synchronized. Table Name must be in ARRAY format

• Read more on Sync API

#Drop Database

• Syntax : drop -d -DB_NAME -flag(yes or no)

• Details:

  Dropping a database removes every record and information about the database, flag must be set to yes before task is performed, else an error exception will be thrown.