The qsql function Library provides a Generic Analytic Quasi Relational Local Database Manager. Allows the local storage and in menory analysis of quasi relational data. This may include normal relational data such as Numbers and Strings; but, may also include complex data such as Graphs, Vectors, Tables, and Lambda objects in each element of the relation (table).
Each relation is called a Table. Each element in a table is called a record. The index of a table record is called its row (rows begin with zero). Each element of a table record is an untyped Lisp Word and may contain ANY valid Lisp data type. The tables are stored in a Local database folder, and are loaded into memory for analysis.
QSQL supports multiple database spaces, like normal SQL. Each database space is stored as an AIS Object Repository in a subfolder of the application folder. The QSQL local database subfolder is named "../myqsqldata/". Each database space is given a name and is stored in an AIS Object Repository with the following path and file name: "../myqsqldata/databasename.db". Like normal SQL, QSQL stores all of its data in relations (tables). The QSQL database system stores each of its tables in the database repository indexed by the table name as a table object. Tables are loaded into memory, using the AIS fast object load service, prior to performing data anlysis, joins, sorts, or other data management operations.
QSQL Table (see the table object) provide the ability to view subsets of the information in the table by running filtering operations against the table. Tables may be joined together, as in normal SQL, to form new tables, sorted, imported, exported, or filtered. Filtering operations can be applied to the table's current view and result in a new current view. Filtering does not affect the underlying table data and the current view can be restored to match the underlying table data with the table.restore or table.reset functions. Use the table.saveView function to save the current view, by name, into the table's view directory. Use the table.restoreView function to make a saved view the current view. Note that the view directory is saved in the in memory table as part of the collection of table objects inside the table. Note that the current view is contained in the table's rowVector property and you will see reference to rowVector in this document. The bckVector contains the unfiltered content of the table's underlying table. The table.reset function mentioned above essentially create a rowVector that references all of the rows in bckVector in bckVector order to restore an unfiltered view of the table.
The QSQL database system allows the storage of the full range of Lisp data types including normal relational data such as Numbers and Strings; but, can also include quasi relational data such as Graphs, Vectors, Tables, and Lambda objects in each element of the relation (table). Furthermore, these more complex data types are active during data analysis, tables joins, sorting, and filtering. Furthermore, the QSQL tables are stored away from the normal corporate relational server. This allows the AIS application to engage in massive bulk table reads and writes without unsettling the transaction load balance of the normal relational server.
For normal relational database transactions, AIS provides an integrated connection to the MySQL database server system.
qsql.t is a persistent variable, within the qsql Lambda, which contains the Dictionary of currently open tables. It can be used in console shorthand for accessing open tables while working with the qsql library. For instance.
(qsql "load Sales;") ;; Load the sales table into memory.
(writeln "Sales tables has [" qsql.t.sales.recordCount "] records.")
will load the "sales" table into memory for analysis and display the number of records in the currently open sales table.
The qsql main function executes one or more javaScript QSQL commands. The javaScript commands are documented in the javaScript Reference Guide and also in this QSQL Reference Guide.
Type: Function
Syntax: (qsql command command ...)
| commands | One or more QSQL javaScript commands to be executed. |
| Returns: | The Word result of executing the QSQL commands. |
;; Filters the sales table to view the 10% highest salaries only.
(qsql "filter sales with top Salary 10%;")
The backup function clears all open tables and exports all repository tables, in the current database, to the myqsqldata/databaseName folder.
Type: Function
Syntax: (backup)
| none | There are no argments. |
;; clears all open tables and exports all repository tables, in the current database, to the myqsqldata/databaseName folder.
(qsql.backup)
The create function creates the specified named table and saves it into the QSQL repository and the QSQL system main memory.
Type: Function
Syntax: (create tableName)
| tableName | The name of the table to be created (with no column names) and saved it into the QSQL repository and the QSQL system main memory. |
;; Creates the "sales" table (with no column names) and saves it into the QSQL repository and the QSQL system main memory.
(qsql.create "sales")
Syntax: (create tableName columnVector)
| tableName | The name of the table to be created (with the specified column names) and saved it into the QSQL repository and the QSQL system main memory. |
| columnVector | The vector of column names for the newly created table. |
;; Creates the "sales" table (with the specified column names) and saves it into the QSQL repository and the QSQL system main memory.
(qsql.create "sales" #(obj| Employee Address Supervisor SalesAmount))
Syntax: (create tableName "FullPathAndFileName")
| tableName | The name of the table to be created (with the specified data imported) and saved it into the QSQL repository and the QSQL system main memory. |
| pathAndFileName | The full path and file name for importing the data for the newly created table. |
;; Creates the "sales" table (importing data from the specified file) and saves it into the QSQL repository and the QSQL system main memory.
(qsql.create "Sales" (append _path "/Sales.csv"))
The drop function deletes the specified named database or table from the QSQL system.
Type: Function
Syntax: (drop database: databaseName;)
| database | Mandatory keyword. |
| databaseName | The database to be deleted from the QSQL system. |
Syntax:(drop table: tableName;)
| table | Mandatory keyword. |
| tableName | The table to be deleted from the current QSQL database. |
;; Deletes the database "main" from the QSQL system.
(qsql.drop database "main")
;; Deletes the table "Sales" from the current QSQL database.
(qsql.drop table "sales")
The export function exports the specified named table to the specified data file.
Type: Function
Syntax: (export tableName "FullPathAndFileName")
| tableName | The name of the table to be exported. |
| pathAndFileName | The full path and file name for exporting the data for the specified table. |
;; Exports the "sales" table to the specified data file.
(qsql.export "sales" (append _path "/Sales.csv"))
The import function creates the specified named table (importing the specified data) and saves it into the QSQL repository and the QSQL system main memory.
Type: Function
Syntax: (import tableName "FullPathAndFileName")
| tableName | The name of the table to be created (with the specified data imported) and saved it into the QSQL repository and the QSQL system main memory. |
| pathAndFileName | The full path and file name for importing the data for the newly created table. |
;; Creates the "sales" table (importing data from the specified file) and saves it into the QSQL repository and the QSQL system main memory.
(qsql.import "sales" (append _path "/Sales.csv"))
The list function displays the names of the current tables from the QSQL repository onto the console.
Type: Function
Syntax: (list)
| none | There are no arguments to the list function. |
;; Displays the names of the current tables from the QSQL repository onto the console.
(qsql.list)
The load function loads the specified named table from the QSQL repository into the QSQL system main memory and restores the table.
Type: Function
Syntax: (load tableName)
| tableName | The name of the table (found in QSQL memory) to be loaded from the QSQL repository into the QSQL system main memory and restores the table. |
;; Loads the "sales" table from the QSQL repository into the QSQL system main memory and restores the table.
(qsql.load "sales")
The restore function imports all tables in the myqsqldata/databaseName folder into the current QSQL repository.
Type: Function
Syntax: (backup)
| none | There are no argments. |
;; imports all tables in the myqsqldata/databaseName folder into the current QSQL repository.
(qsql.restore)
The save function saves the specified named table or actual table in the QSQL system main memory AND in the QSQL current database repository.
Type: Function
Syntax: (save tableName)
| tableName | The name of the table (found in QSQL memory) to be saved to the QSQL current database repository. |
Syntax:(save tableLambda)
| tableLambda | The actual table to be saved to the QSQL system main memory and in the current database repository. |
;; Saves the "sales" table to the QSQL system repository.
(qsql.save "sales")
;; Saves the "sales" table to the QSQL system main memory and repository.
(setq salesTable (table "Sales"))
(qsql.save salesTable)
The select function creates a new table by conditionally selecting data from two other tables. The user specifies the names of the columns for the new table, the manner in which each new column's data is to be extracted from the two existing tables, and any conditions which are imposed before a new row is to be created.
Type: Function
Syntax: (select newColNames xTableName yTableName newTableName newRowLambda joinXName joinYName whereLambda)
| newColNames | The Vector of column names for the new table. |
| xTableName | The name of the first existing table (the x table). |
| yTableName | The name of the second existing table (the y table). |
| newTableName | The name of the new table which is to be created. |
| newRowLambda | The lambda for extracting the data for each new row from the selected rows of the two existing tables. |
| joinXName | The name of x table join column (void if there is no join requested). |
| joinYName | The name of y table join column (void if there is no join requested). |
| whereLambda | The condition lambda which must be true (void if no conditions are specified). |
;; Create a new table, VOL, from the existing SPX and VIX tables.
(qsql.select #(obj| Date Price Vol) SPX: VIX: VOL: (lambda(x y) (new Vector: ~ x.Date x.Close y.Close)) Date: Date: (lambda(x y) (y.Close > 20)) )
The show function displays the specified named table on the console.
Type: Function
Syntax: (show tableName startRow rowCount fieldWidth)
| tableName | The name of the table to be displayed on the console. |
| startRow | The starting row of the table to be displayed on the console. |
| rowCount | The number of rows to be displayed on the console. |
| fieldWidth | The width of each field to be displayed on the console. |
;; Display the "sales" table on the console.
(qsql.show "sales" 22 10 20)
The unload function deletes the specified named table from the QSQL system main memory but NOT from the QSQL repository.
Type: Function
Syntax:(unload tableName)
| tableName | The table to be deleted from the current QSQL main memory but NOT from the QSQL repository. |
;; Deletes the table "Sales" from the current QSQL database main memory.
(qsql.unload "sales")
The use function specifies a database name for the current focus. If the named database does not exist, it will be created. A use function must be issued before any further commands can be issued by the QSQL system.
Type: Function
Syntax: (use "databaseName ...")
| databaseName | The database name to receive the focus. |
| clear: | (Optional)If "clear" then the database is cleared of all existing tables. |
;; Sets the database name for the current focus.
(qsql.use "main")
;; Sets the database name for the current focus and clears all tables in the database.
(qsql.use "graphs" clear:)
The view function exports the specified named table and then executes Excel to view the exported table data.
Type: Function
Syntax: (view tableName)
| tableName | The name of the table to be exported and viewed in Excel. |
;; views the "sales" table in Excel.
(qsql.view "sales")