Select

1. SQL query

SQL Select query:

SELECT j.id, j.Title AS JobTitle, j.Salary AS JobSalary, e.CompanyName
FROM Jobs j, Employers e
WHERE (j.EmployerId = e.Id) AND (j.Category = 'IT-Software') AND 
      (j.PostDate BETWEEN '2017-08-01 00:00:00' AND '2017-10-01 00:00:00')
ORDER BY j.Id DESC
LIMIT 2;

2. dbAPI syntax

dbAPI for CouchDB usage:

var dbAPI = require('./dbAPI-server').dbAPI;
                         
dbAPI(input, function (output) {
    //Handle output
});

with the input:

input = {
    "metadata": {
        "dbinfo": {
            "DBMS": "couchdb",
            "cFile": "../dbAPI-server/config/dbinfo.couchdb.json"
        }
    },
    "data": {
        "format": "text",
        "szSQL": "SELECT j.id, j.Title AS JobTitle, j.Salary AS JobSalary, e.CompanyName FROM Jobs j, Employers e WHERE (j.EmployerId = e.Id) AND (j.Category = 'IT-Software') AND (j.PostDate BETWEEN '2017-08-01 00:00:00' AND '2017-10-01 00:00:00') ORDER BY j.Id DESC LIMIT 2;"
    }
}

To execute the SQL create query in other DBMS, change the "DBMS" name from couchdb to neo4j, redis, mysql or sqlite. Then change the cFile to corresponding DBMS configuration file.

Support features:

• Neo4j:

  • Supported:

  • Operator: =, !=, <, <=,>, >=, IN, NOT IN, BETWEEN AND, LIKE, AND, OR

  • Subquery: 1 level subquery with 1 field in Select clause. Example: Select candidates with location is New York and they applied to at least 3 IT-Software employers.

    SELECT FirstName, LastName, Gender, Location
    FROM Candidates
    WHERE (Location = 'New York') AND 3 <= (SELECT COUNT(ec.EmployerId) AS empCount
                                FROM Employer_Candidate ec, Employers e 
                                WHERE (ec.EmployerId = e.Id) AND (Candidates.Id = ec.CandidateId)
                                AND e.Industry = 'IT-Software');

  • Not support:

    • Operator: EXISTS, NOT EXISTS, ANY, ALL

    • Subquery: Two or more fields in select clause of subquery.

• CouchDB:supported by pouchdb-find

  • Supported: =, !=, <, <=,>, >=, IN, NOT IN, BETWEEN AND, LIKE, AND, OR

  • Not support: Subquery, DISTINCT, JOINS, UNIONS, GROUP BY, mixed ORDER BY (asc and desc in the same time)

• Redis: supported by rediSQL module