In-memory query engine over typed columns. Tables are filled row by row or loaded from sources (CSV files, folders, the program's own objects) and queried with SQL-like statements; aggregates, Describe() and Finder (file search) are included. Compile with -lib query.
View
Rows of a table, chosen and ordered without copying: the table, the columns to show and the row slots in order. Chaining Where, OrderBy, Limit and Select only makes new lists of slots. A view made before its table changes refuses to read it afterwards (see IsCurrent).
Example
view := table->Where(Col->Of("score")->Gt(0.5))->OrderBy("score", Order->DESC)->Limit(10);
each(i : view->Count()) {
view->Get(i)->ToString()->PrintLine();
};Operations
- New
- Average
- Count
- Distinct
- Get
- GetAll
- GetColumnNames
- GetError
- GetPlan
- GetRows
- GetTable
- IsCurrent
- Limit
- Max
- Min
- Offset
- OrderBy
- Select
- SetPlan
- Size
- Sum
- ToCsv
- ToJson
- ToMatrix
- ToString
- ToTable
- Where
- WriteCsv
- WriteJson
Average #
Averages a column's numeric cells over the view's rows
method : public : Average(name:String) ~ FloatParameters
| Name | Type | Description |
|---|---|---|
| name | String | column name |
Return
| Type | Description |
|---|---|
| Float | average, 0.0 if there are none |
Count #
Counts a column's numeric cells over the view's rows
method : public : Count(name:String) ~ IntParameters
| Name | Type | Description |
|---|---|---|
| name | String | column name |
Return
| Type | Description |
|---|---|
| Int | count |
Distinct #
The first row holding each distinct value of a column; empty cells are left out
method : public : Distinct(name:String) ~ ViewParameters
| Name | Type | Description |
|---|---|---|
| name | String | column name |
Return
| Type | Description |
|---|---|
| View | new view, Nil if the column is unknown (see GetError) |
Get #
Gets a row by its position in the view
method : public : Get(index:Int) ~ RowParameters
| Name | Type | Description |
|---|---|---|
| index | Int | position, 0 based |
Return
| Type | Description |
|---|---|
| Row | row, Nil if out of range or the view is stale |
GetAll #
Gets every row, in view order
method : public : GetAll() ~ Vector<Row>Return
| Type | Description |
|---|---|
| Vector<Row> | rows, empty if the view is stale |
GetColumnNames #
Gets the names of the columns shown
method : public : GetColumnNames() ~ String[]Return
| Type | Description |
|---|---|
| String | column names |
GetError #
Gets the reason the last operation failed
method : public : GetError() ~ StringReturn
| Type | Description |
|---|---|
| String | error, Nil if none |
GetPlan #
Tells how the view's rows were found: for each table, whether its condition scanned it or read it through an index (naming the index), and which orderings came from a sorted index. Steps are separated by '; '.
method : public : GetPlan() ~ StringReturn
| Type | Description |
|---|---|
| String | plan, Nil for a view made without a condition or a query |
Example
rs := table->Where(Col->Of("grp")->Eq("g7"));
rs->GetPlan()->PrintLine(); # where runs: HASH index runs_grp on grp (=)GetRows #
Gets the row slots, in view order
method : public : GetRows() ~ Int[]Return
| Type | Description |
|---|---|
| Int | row slots |
GetTable #
Gets the table the view reads
method : public : GetTable() ~ TableReturn
| Type | Description |
|---|---|
| Table | table |
IsCurrent #
Whether the table is unchanged since the view was made
method : public : IsCurrent() ~ BoolReturn
| Type | Description |
|---|---|
| Bool | true if the view can be read |
Limit #
At most the first n rows
method : public : Limit(n:Int) ~ ViewParameters
| Name | Type | Description |
|---|---|---|
| n | Int | row limit |
Return
| Type | Description |
|---|---|
| View | new view |
Max #
Gets the largest numeric cell of a column over the view's rows
method : public : Max(name:String) ~ FloatParameters
| Name | Type | Description |
|---|---|---|
| name | String | column name |
Return
| Type | Description |
|---|---|
| Float | maximum, 0.0 if there are none |
Min #
Gets the smallest numeric cell of a column over the view's rows
method : public : Min(name:String) ~ FloatParameters
| Name | Type | Description |
|---|---|---|
| name | String | column name |
Return
| Type | Description |
|---|---|
| Float | minimum, 0.0 if there are none |
New # constructor
Constructor. Use Table->AsView, Where, Select or OrderBy rather than this.
New(table:Table, columns:Int[], rows:Int[])Parameters
| Name | Type | Description |
|---|---|---|
| table | Table | table |
| columns | Int | column indexes shown, 'pk' excluded |
| rows | Int | row slots, in order |
New # constructor
Constructor for a view whose columns may include names the table does not have; those read as empty cells. Use Table->Prepare rather than this.
New(table:Table, columns:Int[], names:String[], rows:Int[])Parameters
| Name | Type | Description |
|---|---|---|
| table | Table | table |
| columns | Int | column indexes shown, -1 for an unknown name |
| names | String | column names shown |
| rows | Int | row slots, in order |
Offset #
The rows after the first n
method : public : Offset(n:Int) ~ ViewParameters
| Name | Type | Description |
|---|---|---|
| n | Int | rows to skip |
Return
| Type | Description |
|---|---|
| View | new view |
OrderBy #
The rows sorted by a column, ascending; empty cells last
method : public : OrderBy(name:String) ~ ViewParameters
| Name | Type | Description |
|---|---|---|
| name | String | column name |
Return
| Type | Description |
|---|---|
| View | new view, Nil if the column is unknown (see GetError) |
OrderBy #
The rows sorted by a column; empty cells last in either direction. Rows with equal values keep their order.
method : public : OrderBy(name:String, order:Order) ~ ViewParameters
| Name | Type | Description |
|---|---|---|
| name | String | column name |
| order | Order | ASC or DESC |
Return
| Type | Description |
|---|---|
| View | new view, Nil if the column is unknown (see GetError) |
Select #
The same rows showing only some columns
method : public : Select(names:String[]) ~ ViewParameters
| Name | Type | Description |
|---|---|---|
| names | String | column names |
Return
| Type | Description |
|---|---|
| View | new view, Nil if a name is unknown (see GetError) |
SetPlan #
Sets the plan GetPlan reports. Used by the query engine.
method : public : SetPlan(plan:String) ~ NilParameters
| Name | Type | Description |
|---|---|---|
| plan | String | plan text |
Sum #
Sums a column's numeric cells over the view's rows
method : public : Sum(name:String) ~ FloatParameters
| Name | Type | Description |
|---|---|---|
| name | String | column name |
Return
| Type | Description |
|---|---|
| Float | sum, 0.0 if there are none |
ToCsv #
Writes the view as CSV: a header line of column names, then one line per row, quoted only where RFC 4180 needs it. Empty cells are empty fields; numbers are written exactly (a Float as the shortest text that reads back as the same value); dates as '#MM/dd/yyyy hh:mm:ss#', the form CsvSource reads back as dates; Bools as true and false.
method : public : ToCsv() ~ StringReturn
| Type | Description |
|---|---|
| String | CSV text, Nil if the view is stale |
Example
results := table->Query("select id, score from runs where score > 0.5");
results->ToCsv()->PrintLine();ToCsv #
Writes the view as delimited text (see ToCsv())
method : public : ToCsv(delimiter:Char) ~ StringParameters
| Name | Type | Description |
|---|---|---|
| delimiter | Char | field delimiter, such as '\t' |
Return
| Type | Description |
|---|---|
| String | delimited text, Nil if the view is stale |
ToJson #
Writes the view as JSON: an array with one object per row, members in column order. Numbers are written exactly; an empty cell is null, as is a Float that is not a finite number (JSON has none); a date is its ISO 8601 text, 'yyyy-MM-ddTHH:mm:ss', with a trailing 'Z' for a GMT date.
method : public : ToJson() ~ StringReturn
| Type | Description |
|---|---|
| String | JSON text, Nil if the view is stale |
Example
results := table->Query("select id, label from runs limit 3");
results->ToJson()->PrintLine(); # [{"id":1,"label":"cat"},...]ToMatrix #
Copies numeric columns into a matrix for System.ML, one row per view row, one column per name, in the order named. INT, FLOAT and BOOL cells are read as numbers (a Bool as 1 or 0), and so are numbers in an OBJECT column. An empty cell, or one that is not a number, is an error: no value is invented without being asked for (see ToMatrix(names, fill)).
method : public : ToMatrix(names:String[]) ~ Float[,]Parameters
| Name | Type | Description |
|---|---|---|
| names | String | columns to copy |
Return
| Type | Description |
|---|---|
| Float | matrix, Nil on error (see GetError) |
Example
features := table->AsView()->ToMatrix(["height", "weight"]);ToMatrix #
Copies numeric columns into a matrix, putting a fill value where a cell is empty or not a number (see ToMatrix(names))
method : public : ToMatrix(names:String[], fill:Float) ~ Float[,]Parameters
| Name | Type | Description |
|---|---|---|
| names | String | columns to copy |
| fill | Float | value for a missing cell, such as 0.0 or a column mean |
Return
| Type | Description |
|---|---|
| Float | matrix, Nil on error (see GetError) |
ToString #
String representation of the view's rows
method : public : ToString() ~ StringReturn
| Type | Description |
|---|---|
| String | rows as a string |
ToTable #
Copies the view into a new, independent table
method : public : ToTable(name:String) ~ TableParameters
| Name | Type | Description |
|---|---|---|
| name | String | new table's name |
Return
| Type | Description |
|---|---|
| Table | table, Nil if the view is stale |
Where #
The rows that also meet a condition
method : public : Where(condition:Predicate) ~ ViewParameters
| Name | Type | Description |
|---|---|---|
| condition | Predicate | condition |
Return
| Type | Description |
|---|---|
| View | new view, Nil if the condition names an unknown column (see GetError) |
WriteCsv #
Writes the view as CSV to a stream, a line at a time, so a large result never has to be held as one string (see ToCsv())
method : public : WriteCsv(out:System.IO.OutputStream) ~ BoolParameters
| Name | Type | Description |
|---|---|---|
| out | OutputStream | stream, such as a FileWriter or an HTTP response body |
Return
| Type | Description |
|---|---|
| Bool | true if every line was written, false on error (see GetError) |
WriteCsv #
Writes the view as delimited text to a stream (see ToCsv())
method : public : WriteCsv(out:System.IO.OutputStream, delimiter:Char) ~ BoolParameters
| Name | Type | Description |
|---|---|---|
| out | OutputStream | stream |
| delimiter | Char | field delimiter |
Return
| Type | Description |
|---|---|
| Bool | true if every line was written, false on error (see GetError) |
WriteJson #
Writes the view as JSON to a stream, an object at a time (see ToJson())
method : public : WriteJson(out:System.IO.OutputStream) ~ BoolParameters
| Name | Type | Description |
|---|---|---|
| out | OutputStream | stream |
Return
| Type | Description |
|---|---|
| Bool | true if everything was written, false on error (see GetError) |