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.
Catalog
Named tables that queries can refer to. SQL run through a catalog can join its tables, and can create, change and delete them.
Threads: one catalog may be shared by any number of threads that query through it at once (Query, Prepare, Explain, Get, GetNames), including queries that name files, which each thread finds in the catalog's cache or, when the files changed, waits for one thread to read again. GetError gives each thread its own last error. Add, Remove, Load and CREATE TABLE may run while other threads query: a query sees a table as it was before or after the change. INSERT, UPDATE, DELETE, CREATE INDEX and DROP INDEX write a table, so while one runs no other thread may use that table (as for any Table). SetFileAccess and SetArchiveLimits apply to the queries that start after them; set them before sharing the catalog. A Statement from Prepare belongs to the thread that binds it.
Example
catalog := Catalog->New();
catalog->Load(CsvSource->New("runs.csv"));
catalog->Load(CsvSource->New("labels.csv"));
rs := catalog->Query("select r.id, r.score, l.label from runs r join labels l on r.id = l.id");Operations
- New
- Add
- AddNew
- Execute
- Explain
- Get
- GetError
- GetFileReads
- GetNames
- Has
- Load
- Prepare
- Query
- Remove
- Run
- SetArchiveLimits
- SetFileAccess
Add #
Adds a table under its name, replacing any table of that name
method : public : Add(table:Table) ~ NilParameters
| Name | Type | Description |
|---|---|---|
| table | Table | table |
AddNew #
Adds a table unless one of its name exists: the check and the add are one step, so of threads adding tables of one name at once, exactly one succeeds (CREATE TABLE uses it)
method : public : AddNew(table:Table) ~ BoolParameters
| Name | Type | Description |
|---|---|---|
| table | Table | table |
Return
| Type | Description |
|---|---|
| Bool | true if added, false if a table of that name exists |
Execute #
Runs an INSERT, UPDATE, DELETE, CREATE TABLE, CREATE INDEX or DROP INDEX (see Table->Execute). A statement that fails changes nothing.
method : public : Execute(statement:String) ~ IntParameters
| Name | Type | Description |
|---|---|---|
| statement | String | SQL statement |
Return
| Type | Description |
|---|---|
| Int | rows inserted, updated or deleted (0 for CREATE TABLE), -1 on error (see GetError) |
Example
catalog->Execute("create table runs (id int, score float, label string)");
catalog->Execute("insert into runs values (1, 0.5, 'cat'), (2, 0.9, 'dog')");Explain #
Tells how a statement reads its rows (see Table->Explain); a SELECT is run to find out
method : public : Explain(statement:String) ~ StringParameters
| Name | Type | Description |
|---|---|---|
| statement | String | SQL statement |
Return
| Type | Description |
|---|---|
| String | plan, Nil on error (see GetError) |
Example
catalog->Explain("select * from runs r join meta m on r.id = m.id where r.score > 0.9")->PrintLine();Get #
Gets a table by name
method : public : Get(name:String) ~ TableParameters
| Name | Type | Description |
|---|---|---|
| name | String | table name |
Return
| Type | Description |
|---|---|
| Table | table, Nil if there is none |
GetError #
Gets the reason the calling thread's last load, query or statement failed: several threads may share a catalog, and each reads its own error
method : public : GetError() ~ StringReturn
| Type | Description |
|---|---|
| String | error, Nil if none |
GetFileReads #
Gets how many times queries have read a file, for checking that unchanged files come from the cache
method : public : GetFileReads() ~ IntReturn
| Type | Description |
|---|---|
| Int | file reads |
GetNames #
Gets the table names
method : public : GetNames() ~ String[]Return
| Type | Description |
|---|---|
| String | table names |
Has #
Whether a table of that name exists
method : public : Has(name:String) ~ BoolParameters
| Name | Type | Description |
|---|---|---|
| name | String | table name |
Return
| Type | Description |
|---|---|
| Bool | true if it exists |
Load #
Loads every table of a source
method : public : Load(source:Source) ~ BoolParameters
| Name | Type | Description |
|---|---|---|
| source | Source | source |
Return
| Type | Description |
|---|---|
| Bool | true if loaded, false on error (see GetError) |
Load #
Loads a source's tables, passing what the caller needs
method : public : Load(source:Source, hint:ScanHint) ~ BoolParameters
| Name | Type | Description |
|---|---|---|
| source | Source | source |
| hint | ScanHint | columns and rows needed, Nil for everything |
Return
| Type | Description |
|---|---|
| Bool | true if loaded, false on error (see GetError) |
Prepare #
Parses a statement once, for running many times with different values
method : public : Prepare(statement:String) ~ StatementParameters
| Name | Type | Description |
|---|---|---|
| statement | String | SQL with '?' placeholders |
Return
| Type | Description |
|---|---|
| Statement | statement, Nil on a syntax error (see GetError) |
Query #
Runs a SELECT over the catalog's tables, which it can join, and over files named in it
method : public : Query(statement:String) ~ TableParameters
| Name | Type | Description |
|---|---|---|
| statement | String | SQL SELECT |
Return
| Type | Description |
|---|---|
| Table | result table, Nil on error (see GetError) |
Example
rs := catalog->Query("select dept, count(*) as n from emp group by dept having count(*) > 1");Remove #
Removes a table
method : public : Remove(name:String) ~ BoolParameters
| Name | Type | Description |
|---|---|---|
| name | String | table name |
Return
| Type | Description |
|---|---|
| Bool | true if removed |
Run # function
Runs a SELECT that reads files named in it, with no catalog of tables. Each call reads the files again; a program that repeats a query keeps a Catalog and uses its Query, which reads a file again only after it changes.
function : Run(statement:String) ~ TableParameters
| Name | Type | Description |
|---|---|---|
| statement | String | SQL SELECT |
Return
| Type | Description |
|---|---|
| Table | result table, Nil on error (a Catalog's Query and GetError say why) |
Example
rs := Catalog->Run("select name, avg(score) from 'results/run_*.csv' group by name");SetArchiveLimits #
Sets how much an archive or workbook named in a query may decompress, as ArchiveSource->SetLimits does: a member larger than 'member_bytes' is refused, and so is a read that takes the archive's total past 'total_bytes'. The defaults are 256 MB and 1 GB.
method : public : SetArchiveLimits(member_bytes:Int, total_bytes:Int) ~ NilParameters
| Name | Type | Description |
|---|---|---|
| member_bytes | Int | most bytes one member or part may hold uncompressed |
| total_bytes | Int | most bytes one archive or workbook may hold uncompressed in all |
SetFileAccess #
Whether queries may name files and URLs ('from 'runs.csv'', 'from 'https://...''), which they may by default. A catalog that answers queries from other people, as TableServer's does, turns this off: otherwise a query could read any file the process can, or make it fetch any URL. Archives and workbooks are files, so this refuses them too.
method : public : SetFileAccess(allowed:Bool) ~ NilParameters
| Name | Type | Description |
|---|---|---|
| allowed | Bool | false to refuse files and URLs in queries |