JavaScriptSQL User Manual (versión 2.1)
Other manuals availables:- JSBD_Conversor library manual (spanish only for now)
- JSBD_Indexador library manual (spanish only for now)
- JSBD structure description (spanish only for now)
Index
- Initial Design
- Features
- Possible Applications
- Requirements and Installation
- Basic Example of Use
- JSSQL class reference
- Data Types
- SQL Sintax
- Use of indexes
- Document indexing
- Table partitioning
I. Initial Design
Below is the initial design diagram that describes the relationship between the various modules used for the operation of the JavaScript database.
The basic operation of the database for JavaScript is to generate, by converting the souece databases and indexing documents, the definition files, tables, indexes and other components of the JSBD database . Then the JSSQL engine can efficiently access the JSBD database using SQL queries.
Cada uno de estos componentes o subsistemas están conformados por una o más clases a ser usadas por los desarrolladores. El conversor y el indexador están desarrollados en PHP mientras que el motor JSSQL esta desarrollado íntegramente en JavaScript.
Once a JSBD database is generated , you can meke SQL querys to the JSSQL engine without any other database engine and without using any scripting language that works on the server side (PHP, ASP, etc..).
II. Features
The database JSSQL engine is a class developed entirely in JavaScript that allows SQL queries on the JSBD database generated for this purpose.
JSSQL is a database engine for read only, once generated the database can not add or modify records or tables.
The engine can interpret JavaScriptSQL SQL queries with the following structure:
SELECT
[DISTINCT]
* | select_expresion [ AS alias_field ] [, ...]
FROM table_name [table_name] [, ...]
[ WHERE where_condition ]
[ GROUP BY field_number [, ... ] [HAVING having_condition ] ]
[ ORDER BY field_number [ ASC | DESC ] [, ...] ]
[ LIMIT amount ]
where:
select_expresion:
It is an expression or set of functions applied to the fields from the tables listed in the FROM clause.
In these expressions can be used functions and operators from JavaScript or functions previously defined by the user or aggregate functions (COUNT, SUM, AVG) in case of group records by the GROUP BY clause. For example, "field1" or "COUNT (orders)" or "name.ToUpperCase ()".
where_condition
Es una expresión que devuelve únicamente los valores true o false, puede estar compuesta por campos de las tablas listadas en la cláusula FROM. Pueden utilizarse funciones y operadores propios de JavaScript y funciones previamente definidas por el usuario.
condición_having:
Is an expression that returns only the values true or false, may be composed of fields from the tables listed in the FROM clause.
Can use functions and operators from JavaScript and specific functions previously defined by the user.
field_number
The number of field in the SELECT list of fields, these numbers can not be repeated in the list defined in the ORDER BY clauses and GROUP BY clauses.
amount:
Is the maximum number of records to be returned by the query.
The current version has the following features:
- Allows offline data query without the intervention of Web servers or database servers.
- Allows flexible SQL syntax, accepting the use of tables and fields aliases with case-insensitive terms and llows the use of multiple spaces, so that the writing of SQL querys is simple and intuitive.
- Allows use of all JavaScript functions and operators so that is totally intuitive for JavaScript developers. Allows the use of functions externally defined by users.
- Allows the definition and use of indexes to speed up the queries.
- Permite la paginación de tablas de manera que puedan utilizarse tablas de gran cantidad de registros sin sobrecargar la memoria utilizada por el navegador.
- Incluye funcionalidades para la búsqueda de texto dentro de documentos indexados mediante el indexador JSBD_Indexador y asociados mediante campos del tipo link en las tablas de la base de datos.
- It allows table pagination so you can use tables with lot of records without overloading the memory used by the browser.
- Includes functionality for text search within documents indexed by the indexer JSBD_Indexador and associated with link data types defined at the database tables .
- Allows creation of JavaScript databases using the converter JSBD_Conversor from PostgreSQL and MySQL engines.
- Clearly reports syntax and runtime errors, and loading of the database and SQL queries.
- Provide debug mechanisms to help the development, maintenance and testing of the engine.
- Use a clear structure for the definition of database structures, data storing and indexes. Using JSON structures.
- Use and installation is simple, with low requirements and compatible with most popular browsers.
III. Possible Applications:
Any web application with data management where you need a more dynamic usage without server intervention. For example, a dynamic data table (or spreadsheet) that allows to filter the values presented, make arrangements for the different columns and auxiliary calculations (sums, subtotals, averages, etc.). for example:
- A dictionary or encyclopedia.
- A catalog.
- A document recopilation
- Any web application with data management where you need a more dynamic usage without server intervention. For example, a dynamic data table (or spreadsheet) that allows to filter the values presented, make arrangements for the different columns and auxiliary calculations (sums, subtotals, averages, etc.).
- A Business Intelligence (BI) aplication where data are grouped by different fields dynamically showing the various statistics. For example, a BI application that analyzes a company's sales, to see dynamically sales by country, vendor, customer type, product, etc. Without the need for repeated requests to the server.
IV. Requirements and Installation
The JSSQL engine works in most modern browsers with javascrit enabled, and was formally tested in the following browsers:
- Firefox 4.0
- Internet Explorer 6.0
- Google Crome 5.0
To use JSSQL simply need to copy the JSSQL.js file to the directory where you want install and copy the JSBD database in the 'bases' directory in the installation directory.
For example if the installation directory is 'basedir' and the database JSBD is called 'Neptuno' we have the following file structure
basedir\JSSQL.js
basedir\bases\neptuno\
The default storage directory of the JSBD is 'bases', but can be modified by the SetBaseDir (BASEDIR) method.
Inside the directory 'basedir \ bases \ neptune \' are all the database definition files.
It is recommended to create the database JSBD using the converter developed for this purpose ( JSBD_Conversor ) and index fields of type 'link' by the indexer developed for this purpose ( JSBD_Indexador ).
V. Basic Example of Use
Below is an example of how to use the class to query the JSBD database.
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>JSSQL use example</title>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<!-- ADDING CLASS JSSQL ------------------------------------------ --->
<script LANGUAGE="JavaScript1.2" type="text/javascript" SRC="JSSQL.js"></script>
<!-- ---------------------------------------------------------------------- --->
</head>
<body>
<script LANGUAGE="JavaScript1.2" type="text/javascript">
//JSSQL engine creation
myJSSQL= new JSSQL();
//neptuno database opening
if (!myJSSQL.Open('neptuno'))
{
alert(myJSSQL.GetError()); //if it fails displays an error message
}
//Consulta SQL
sql="select nombrecompania as comp, nombrecontacto as cont, cargocontacto as cargo
from clientes order by 3, 2 desc" ;
//Query execution
res=myJSSQL.Query(sql)
if(res===false)
{
//if it fails displays an error message
alert( myJSSQL.GetError() );
}
else
{
//printing results
for (i=0;i < res.length;i++){
document.write(res[i]["comp"]);
document.write(" | ");
document.write(res[i]["cont"]);
document.write(" | ");
document.write(res[i]["cargo"]);
document.write(" | <br>");
}
}
</script>
</body>
</html>
VI. JSSQL class reference
The following describes the methods and attributes of the JSSQL class
VI.i useDebug
Sets whether or not to use the debug information generation. Like response times, number of records returned, number of records read, structures used, and so on. The default is on. Disabling this option the engine will be a little faster.
VI.ii useIndex
Sets whether analyzes the WHERE conditions to use the indexes (if defined), should be enabled assumed that these conditions are expressed in a simple way to prevent inaccurate results (see Use of indexes for more information .) The default is disabled.
VI.iii docsMaxLen
Sets the maximum number of characters that can have a word in the content indexes. If you search for a larger word it is ignored in the full text search.
VI.iv docsMinLen
Sets the minimum number of characters that can have a word in the content indexes. If you search for a shorter word it is ignored in the full text search.
VI.v Open(JSBD)
Open the JSBD database, loading into memory data tables, indexes and definitions.
Parameters:
JSBD: Name of the JSBD database to open.
Returns:
true/false if can or not open the corresponding structures. Use GetError() for a description of the error.
VI.vi Query(sql)
Execute the SQL query in the database.
Parameters:
Sql: SQL query to execute.
Returns:
An array with all the resulting records, the array has the form:
[
{"field1":value, "field2":value, (...) } , // Record 1
(..)
{"field1":value, "field2":value, (...) } // Record n
]
It returns false in case of error. Use GetError() for a description of the error.
VI.vii GetError()
Returns the description of the last error generated.
VI.viii GetDebug()
Returns an array of debug data generated on the last query, the array has the following structure:
[
{"property_name_1":["description", value] } , // property 1
(..)
{"property_namen":["description", value] } // property n
]
VI.ix GetDef()
Returns an array with the definition of the tables in the opened database, the structure is the defined for the JSBD database.
VI.x SetBaseDir(BaseDir)
Sets the directory that stores the JSBD databases
Parameters:
Basedir: Directory that stores the JSBD databases.
Returns:
true/false if can or not open the corresponding structures. Use GetError() for a description of the error.
VII. Data Types
Possible types of JSBD database fields are as follows:
- numeric: integers, or floating point.
- string: string of characters.
- boolean: logical values (true/false).
- date: amount of time expressed in milliseconds since 01/01/1970.
- link: refers to a document whose contents have been indexed.
VIII. SQL Sintax
Las consultas que procesa SQL deben son de la forma:
SELECT
[DISTINCT]
* | select_expresion [ AS alias_field ] [, ...]
FROM table_name [table_name] [, ...]
[ WHERE where_condition ]
[ GROUP BY field_number [, ... ] [HAVING having_condition ] ]
[ ORDER BY field_number [ ASC | DESC ] [, ...] ]
[ LIMIT amount ]
In all cases the clauses, table names, field names and aliases are case-insensitive, meaning that the clause 'SELECT' and 'select' and 'Select' are valid and are interpreted in the same way.
The functions SUM, AVG, COUNT, CONTENT, ALL, ANY, are functions defined by the JSSQL engine and should be written in uppercase.
VIII.i. SELECT
Following the SELECT must include a list of expressions that conform result fields.
Referring to the fields in a table:
select nombre, apellidos from empleados
Using aliases for expressions:
select nombre as N, apellidos as A from empleados
It should be noted that it is wrong to repeate expressions alias, if it occurs only return the alias of the last expression.
WRONG:
select nombre as N, apellidos as N from empleados
Using the '*'
select * from empleados
select *, idempleado as ID2 from empleados
The expressions can use any operator or function defined outside the JSSQL class
Using operators
select idempleado *2 as IDx2 , nombre + ' ' + apellidos as NombreCompleto
from empleados
Using javascript functions and atrributes
Select nombre.length , nombre.match(/nancy/i), "CONSTANTE" as C, escape(pais) as P
from empleados
Converting a date inside the query
Select nombre, (new Date(fechanacimiento)).getFullYear() as AnioNacimiento from empleados
Using external functions. Assuming defined functions pad() and DateFormat ()
select pad(idempleado,4) as id, DateFormat(E.Fechanacimiento) as FechaNacimiento
from empleados E
VIII.ii. FROM
Following the FROM lists the tables to be included in the query.
In case of more than one table the engine runs through the tables as a Cartesian product.
Each table can be associated with a table alias
Select e.* from empleados e
The default alias is the name of the table
Select empleados.idempleado from empleados
Joining tables
Select j.Apellidos, 'Es jefe de' as Relacion, e.apellidos
from empleados e, empleados j
where j.idempleado == e.jefe
In case of unambiguous field names referenced in the SELECT expressions can be obviated the use of aliases
Select nombreproducto, nombrecategoria
from categorias, productos
VIII.iii.WHERE
Following the WHERE must be written a logical expression that returns true or false.
In this expression can use the operators, functions, methods and properties available in JavaScript and externally defined by users.
select nombre, apellidos from empleados
where apellidos.length>5 && (nombre=='Nancy' || nombre=='Andrew')
select nombre, apellidos from empleados
where nombre.match(/an/i) != null
Select nombre from empleados
where(new Date(fechanacimiento)).getFullYear() > 1960
This condition can also be referred to the table aliases
Select nombreproducto, nombrecategoria from categorias c , productos p
where c.idcategoria == p.idcategoria &&
p.idcategoria != 5 && nombrecategoria != 'Bebidas'
In case that useIndex is on must be careful with the conditions used, in some cases the results may be inaccurate. See Use or indexes for more information.
VIII.iv. GROUP BY
Following the GROUP BY expressions must be listed to be grouped. This list must contain only non-repeating numbers where each referencing a SELECT expression that is not an aggregate function (those expressions where there is no reference to the functions COUNT, AVG or SUM).
When using GROUP BY all SELECT expressions must be listed in the GROUP BY or be aggregated functions.
A GROUP BY without aggregation functions:
Select pais, ciudad from empleados group by 1,2
Below the definitions of the aggregation functions
SUM(FieldName)
Description:
Returns the sum of the values grouped FieldName field.
Parameters:
FieldName: the field name to add (must be a numeric)
Examples:
select idpedido, SUM(cantidad) from detalledepedido group by 1
select SUM(cargo) from pedidos
COUNT(FieldName)
Description:
Returns the number of the grouped values of field FieldName. In case the field name is '*' include all values, otherwise include all non-zero values
Parameters:
FieldName: the field name to count (must be of type numeric), to count all the records you can use the string '*'
Examples:
select idpedido, COUNT('*') from detalledepedido group by 1
select COUNT(jefe) from empleados
select COUNT('*') from empleados
AVG(FieldName)
Description:
Returns the average of the grouped values of the field.
Parameters:
FieldName: the field name to average (must be a numeric)
Examples:
select idpedido, AVG(cantidad) from detalledepedido group by 1
select AVG(cargo) from pedidos
VIII.v. HAVING
Following the HAVING written a logical condition that refers to expressions of SELECT.
This condition is evaluated after the data is grouped together
select idproveedor, AVG(idproducto) from productos where idproveedor>=5
group by 1
having AVG(idproducto)>58
The only restriction is that the expression should be cited as it is in the SELECT
WRONG:
select idproveedor, AVG(idproducto) from productos p group by 1 having AVG(p.idproducto) >58
Is wrong becouse 'AVG(idproducto)' is not equal to 'AVG(p.idproducto)'
VIII.vi. ORDER BY
Following the ORDER BY list of expression numbers for which are ordering the result.
All numbers listed must correspond to a number of SELECT expression, the numbers should not be repeated.
Simple ordering
Select pais, ciudad from empleados order by 1
Multiple ordering
Select pais, ciudad from empleados order by 1,2
Additionally you can add to each number of expression ASD or DESC to indicate ascending or descending order respectively, the default is ascending order.
Select pais, ciudad from empleados
order by 1 desc, 2 asc
VIII.vii. DISTINCT
Optionally after the SELECT the DISTINCT clause can be specified, this indicates that the records be returned without repeating groups of values.
Select distinct pais from empleados
Returns the country field values
Select distinct pais, ciudad from empleados
Returns all possible values of the pair (country, city) found in the Employees table.
VIII.viii. LIMIT
Optionally the end of the SQL statement can use the LIMIT clause to limit the number of resulting records, avoiding extensive consultation.
Following the LIMIT the maximum number of records to be returned.
Select nombre from empleados limit 5
The LIMIT clause terminates the query when reached the number of registers to be returned, so it may conflict with the GROUP BY clause returning inaccurate results in aggregation functions.
For Example:
Assuming the employee following cities:
| employeeId | City |
|---|---|
| 0 | Seattle |
| 1 | Tacoma |
| 2 | Kirkland |
| 3 | Redmond |
| 4 | Londres |
| 5 | Londres |
| 6 | Seattle |
| 7 | Londres |
| 8 | Londres |
Select ciudad, COUNT('*') from empleados
group by 1 limit 5
returns:
| Reg. | ciudad | COUNT |
|---|---|---|
| 0 | Seattle | 1 |
| 1 | Tacoma | 1 |
| 2 | Kirkland | 1 |
| 3 | Redmond | 1 |
| 4 | Londres | 1 |
Select ciudad, COUNT('*') from empleados
group by 1
| Reg. | ciudad | COUNT |
|---|---|---|
| 0 | Seattle | 2 |
| 1 | Tacoma | 1 |
| 2 | Kirkland | 1 |
| 3 | Redmond | 1 |
| 4 | Londres | 4 |
IX. Use of indexes
For the use of indexes JSSQL looks in the WHERE condition expressions like field == 'value', then if the field has an index it load the list of records of the associated table with that value.
If all operators used are && (AND) for all the indexed expressions of the same table is performed an intersection in the list of filtered records.
In case there is at least one operator | | (OR) the list of records to be filtered is the union of the lists of the required values.
As noted above, the WHERE condition is evaluated in a single step, allowing great flexibility in writing the SQL statement because it allows the use of JavaScript functions or function externally defined by the user.
By not limiting the use of functions can not perform a exhaustive parsing of the WHERE condition. This makes it impossible to confirm the accuracy of a result, using an index, in the entire universe of possible queries.
For example, assuming you want to evaluate the following WHERE condition
(name=='juan') || true
In this case the evaluation would always be true, but it would be misleading to use an index and restrict the search to those records where the field name is equal to 'juan'.
Conditions can be as complex as you want making it impossible to determine exactly at what point should use the indexes.
For this reason the engine JSSQL enables the use of indices to reduce the number of iterations and evaluations, but this functionality should be enabled by the useIndex property.
To conclude, some examples of valid and invalid cases on a testing database.
Table: Productos| Field | Tipo | Indexado |
|---|---|---|
| Improducto | numeric | NO |
| NombreProducto | string | NO |
| Idcategoria | numeric | SI |
| IdProveedor | numeric | SI |
Table: Proveedores
| Field | Tipo | Indexado |
|---|---|---|
| ImProveedor | numeric | NO |
| NombreCompania | string | NO |
| Pais | string | SI |
Be the query:
select A.idproducto, A.nombreproducto, B.idproveedor, B.nombrecompania
from productos A , proveedores B
where CONDITION
Valid conditions: (The indexed subconditions is highlighted)
A.IdProveedor == B.IdProveedor
A.IdProveedor == B.IdProveedor && A.IdProveedor==4
A.IdCategoria==2 && ( A.IdProveedor==1 || B.pais> "S")
A.IdCategoria==2 || A.IdProveedor==4
A.IdProveedor == B.IdProveedor && (A.IdCategoria==2 || A.IdProveedor==4)
Invalid Conditions
A.IdCategoria==2 || B.IdProveedor==4
A.IdCategoria==2 || B.pais> "S"
(A.IdCategoria==2 && A.IdProveedor==1 ) || B.pais> "S"
A.IdProveedor == B.IdProveedor && (A.IdCategoria==2 || B.Pais=='Francia')
X.Document indexing
In JSSQL you can use fields of type 'link', this implies that a record is associated to a document (whether text, pdf, etc.). All links must be indexed by the JSBD_Indexador . This allows you to search text within the content of the indexed documents.
To access the contents of those documents was implemented the following functions that be used inside a SQL query
CONTENT( FieldName )
Description:
Returns the contents of the document referred to in the value of the link field.
Parameters:
FieldName: the name of the field of 'link' type
Example:
select CONTENT(notas) from empleados
ALL(FieldName, ArrayOfWords)
Description:
Returns true if it finds all the words of the ArrayOfWords
in a document associated with the value of the field FieldName
Parameters:
FieldName: the name of the field of 'link' type
ArrayOfWords: Array of words to be searched
Examples:
select CONTENT(notas) from empleados where ALL(notas,['formación','boston'])
ANY(FieldName, ArrayOfWords)
Description:
Returns true if it finds any of the words of the ArrayOfWords
in a document associated with the value of the field FieldName
Parameters:
FieldName: the name of the field of 'link' type
ArrayOfWords: Array of words to be searched
Examples:
select CONTENT(notas) from empleados where ANY(notas,['licenciatura','formacion'])
EXACT(FieldName, Phrase [, Strict ] )
Description:
Returns true if it finds the phrase inside the document associated with the field FieldName
Parameters:
FieldName: the name of the field of 'link' type
Phrase: String of tect to search inside the document
Stric: A Boolean value that determines whether the sentence is searched taking into account case-sensitive and accents. It is optional and defaults to true
Examples:
select CONTENT(notas) from empleados where EXACT(notas,' ingresó en la empresa')
select CONTENT(notas) from empleados where EXACT(notas,'Ingreso en la empresa',false)
The functions ALL, ANY and EXACT uses the inverted index of words associated with the 'link' field, to optimize search times without reading the contents of all documents.
En estos índices las palabras se encuentran en mayúsculas y sin acentos, para reducir la cantidad de palabras a indexar.
In these indexes the words are in capital letters and without accents, to reduce the number of words indexed.
XI. Table partitioning
The JSBD database tables can be partitioned into small portions of data to avoid excessive memory use when using tables with many records.
The definition of the size of the partitions of the tables is done at the time of the creation of the JSBD (usually by JSBD_Conversor converter).
Using partitioned tables is completely transparent to the user at the time of the query execution in the JSSQL .