Storage: Difference between revisions

From MemCP
Jump to navigation Jump to search
(Created page with "= Storage = The '''Storage''' module provides functions to manage databases, tables, columns, keys, partitions, and large data operations in SCM. ← Back to Full SCM API documentation == scan == Performs an unordered parallel filter-map-reduce on a table '''Allowed number of parameters:''' 6–10 '''Parameters:''' * '''schema''' (<code>string|nil</code>): database name * '''table''' (<code>string|list</code>): table name(s) * '''filterColumns''' (<code>list</c...")
 
No edit summary
Line 4: Line 4:


← Back to [[Full SCM API documentation]]
← Back to [[Full SCM API documentation]]
== table ==
resolves a schema+table name pair into a table handle
'''Allowed number of parameters:''' 2–2
<span id="parameters"></span>
=== Parameters ===
* '''schema''' (<code>string</code>):
* '''table''' (<code>string</code>):
<span id="returns"></span>
=== Returns ===
<code>table</code>
== scan_estimate ==
estimate output row count for a table scan
'''Allowed number of parameters:''' 1–1
<span id="parameters-1"></span>
=== Parameters ===
* '''table''' (<code>table</code>):
<span id="returns-1"></span>
=== Returns ===
<code>int</code>
== table_planner_statistics ==
return the immutable O(1) planner-statistics snapshot for a table
'''Allowed number of parameters:''' 1–1
<span id="parameters-2"></span>
=== Parameters ===
* '''table''' (<code>table</code>):
<span id="returns-2"></span>
=== Returns ===
<code>any</code>
== scan_selectivity_estimate ==
bounded estimate of visible rows matching a table filter; stops at max_rows and does not log scan telemetry
'''Allowed number of parameters:''' 5–5
<span id="parameters-3"></span>
=== Parameters ===
* '''tx''' (<code>any</code>): transaction context to use for visibility; usually ((context &quot;session&quot;) &quot;__memcp_tx&quot;)
* '''table''' (<code>table</code>):
* '''condition_cols''' (<code>list</code>):
* '''condition''' (<code>any</code>):
* '''max_rows''' (<code>int</code>):
<span id="returns-3"></span>
=== Returns ===
<code>list</code>
<span id="table_empty"></span>
== table_empty? ==
returns true if a table currently has no rows
'''Allowed number of parameters:''' 1–1
<span id="parameters-4"></span>
=== Parameters ===
* '''table''' (<code>table</code>):
<span id="returns-4"></span>
=== Returns ===
<code>bool</code>
== scan_recset ==
builds a query-local record-set handle from one table scan, or -- when given an existing recset instead of a table -- narrows that recset to the members which also satisfy filter, re-evaluating filter only over its existing membership. The latter is the cheap way to AND a further (possibly subscan-heavy) condition onto an already-narrowed recset without re-touching rows outside it (e.g. evaluating an expensive correlated check only over the rows a cheap selective filter already narrowed a table down to). The returned value is not persisted and can be scanned like a table
'''Allowed number of parameters:''' 4–4
<span id="parameters-5"></span>
=== Parameters ===
* '''tx''' (<code>any</code>): transaction context to use for visibility; usually ((context &quot;session&quot;) &quot;__memcp_tx&quot;)
* '''table''' (<code>any</code>): a table, or an existing recset to narrow further
* '''filterColumns''' (<code>list</code>):
* '''filter''' (<code>func(columns:any...) -&gt; bool</code>): lambda function that decides whether a row enters the recset
<span id="returns-5"></span>
=== Returns ===
<code>recset</code>
== recset_count ==
returns the number of currently stored recids in a query-local recset
'''Allowed number of parameters:''' 1–1
<span id="parameters-6"></span>
=== Parameters ===
* '''recset''' (<code>recset</code>):
<span id="returns-6"></span>
=== Returns ===
<code>int</code>
== recset_project_join ==
projects a source recset through key columns into a query-local target-table recset
'''Allowed number of parameters:''' 5–5
<span id="parameters-7"></span>
=== Parameters ===
* '''tx''' (<code>any</code>): transaction context to use for visibility; usually ((context &quot;session&quot;) &quot;__memcp_tx&quot;)
* '''source_recset''' (<code>recset</code>):
* '''source_key_columns''' (<code>list</code>):
* '''target_table''' (<code>table</code>):
* '''target_key_columns''' (<code>list</code>):
<span id="returns-7"></span>
=== Returns ===
<code>recset</code>
== recset_key_index ==
builds an immutable lookup function for key columns of the rows contained in a query-local recset
'''Allowed number of parameters:''' 3–3
<span id="parameters-8"></span>
=== Parameters ===
* '''tx''' (<code>any</code>): transaction context used while reading source keys
* '''source_recset''' (<code>recset</code>):
* '''source_key_columns''' (<code>list</code>):
<span id="returns-8"></span>
=== Returns ===
<code>func(key:any...) -&gt; bool</code>
== recset_union ==
combines query-local recsets from the same table and removes duplicate record IDs
'''Allowed number of parameters:''' 1–1
<span id="parameters-9"></span>
=== Parameters ===
* '''recsets''' (<code>list</code>):
<span id="returns-9"></span>
=== Returns ===
<code>recset</code>
== recset_intersect ==
intersects query-local recsets from the same table
'''Allowed number of parameters:''' 1–1
<span id="parameters-10"></span>
=== Parameters ===
* '''recsets''' (<code>list</code>):
<span id="returns-10"></span>
=== Returns ===
<code>recset</code>
== recset_difference ==
returns the records from the first query-local recset which occur in none of the following same-table recsets
'''Allowed number of parameters:''' 1–1
<span id="parameters-11"></span>
=== Parameters ===
* '''recsets''' (<code>list</code>):
<span id="returns-11"></span>
=== Returns ===
<code>recset</code>
== recset_not ==
returns the complement of a query-local recset relative to the currently visible rows of its base table
'''Allowed number of parameters:''' 1–1
<span id="parameters-12"></span>
=== Parameters ===
* '''recset''' (<code>recset</code>):
<span id="returns-12"></span>
=== Returns ===
<code>recset</code>
== scan_exists ==
returns true if a table contains at least one visible row matching the given filter; uses scan boundary analysis without map/reduce setup
'''Allowed number of parameters:''' 4–4
<span id="parameters-13"></span>
=== Parameters ===
* '''tx''' (<code>any</code>): transaction context to use for visibility; usually ((context &quot;session&quot;) &quot;__memcp_tx&quot;)
* '''table''' (<code>table|list|recset</code>):
* '''filterColumns''' (<code>list</code>):
* '''filter''' (<code>func(columns:any...) -&gt; bool</code>): lambda function that decides whether a row exists
<span id="returns-13"></span>
=== Returns ===
<code>bool</code>


== scan ==
== scan ==


Performs an unordered parallel filter-map-reduce on a table
does an unordered parallel filter-map-reduce pass on a single table and returns the reduced result


'''Allowed number of parameters:''' 6–10
'''Allowed number of parameters:''' 6–10


'''Parameters:'''
<span id="parameters-14"></span>
* '''schema''' (<code>string|nil</code>): database name
=== Parameters ===
* '''table''' (<code>string|list</code>): table name(s)
 
* '''filterColumns''' (<code>list</code>): filter columns
* '''tx''' (<code>any</code>): transaction context to use for visibility and mutations; usually ((context &quot;session&quot;) &quot;__memcp_tx&quot;)
* '''filter''' (<code>func</code>): lambda deciding dataset inclusion
* '''table''' (<code>table|list|recset</code>): table handle, query-local recset, or a list for temporary data
* '''mapColumns''' (<code>list</code>): map columns
* '''filterColumns''' (<code>list</code>): physical columns passed to filter before map/reduce; $recset_contains supplies a row-bound RecSet membership closure
* '''map''' (<code>func</code>): lambda extracting/processing data
* '''filter''' (<code>func(columns:any...) -&gt; bool</code>): lambda function that decides whether a dataset is passed to the map phase. You can use any column of that table as lambda parameter. You should structure your lambda with an (and) at the root element. Every equal? &lt; &gt; &lt;= &gt;= will possibly translated to an indexed scan
* '''reduce''' (<code>func</code>): optional aggregator
* '''mapColumns''' (<code>list</code>): physical columns passed to map after filtering; pseudo columns are $update (update/delete current row), $recset_contains (row-bound RecSet membership), $set:<column>, $increment:<column>, and $invalidate:<column> (computed-column maintenance), plus NEW.<column> in trigger plans
* '''neutral''' (<code>any</code>): neutral element
* '''map''' (<code>func(columns:any...) -&gt; any</code>): lambda function to extract data from the dataset. You can use any column of that table as lambda parameter. You can return a value you want to extract and pass to reduce, but you can also directly call insert, print or resultrow functions. If you declare a parameter named '<math display="inline">update', this variable will hold a function that you can use to delete or update a row. Call (</math>update) to delete the dataset, call ($update '(&quot;field1&quot; value1 &quot;field2&quot; value2)) to update certain columns.
* '''reduce2''' (<code>func</code>): optional second reducer
* '''reduce''' (<code>func(any, any)</code>): (optional) lambda function to aggregate the map results. It takes two parameters (a b) where a is the accumulator and b the new value. The accumulator for the first reduce call is the neutral element. The return value will be the accumulator input for the next reduce call. There are two reduce phases: shard-local and shard-collect. In the shard-local phase, a starts with neutral and b is fed with the return values of each map call. In the shard-collect phase, a starts with neutral and b is fed with the result of each shard-local pass. ''(optional)''
* '''isOuter''' (<code>bool</code>): outer join–like behavior
* '''neutral''' (<code>any</code>): (optional) neutral element for the reduce phase, otherwise nil is assumed ''(optional)''
* '''reduce2''' (<code>func(any, any)</code>): (optional) second stage reduce function that will apply a result of reduce to the neutral element/accumulator ''(optional)''
* '''isOuter''' (<code>bool</code>): (optional) if true, in case of no hits, call map once anyway with NULL values ''(optional)''
 
<span id="returns-14"></span>
=== Returns ===
 
<code>any</code>
 
== scan_batch ==
 
does an unordered parallel filter-map-reduce pass on a single table using batchdata-backed #N pseudo columns and returns the reduced result
 
'''Allowed number of parameters:''' 8–12
 
<span id="parameters-15"></span>
=== Parameters ===
 
* '''tx''' (<code>any</code>): transaction context to use for visibility and mutations; usually ((context &quot;session&quot;) &quot;__memcp_tx&quot;)
* '''table''' (<code>table|list|recset</code>): table handle, query-local recset, or a list for temporary data
* '''filterColumns''' (<code>list</code>): list of columns that are fed into filter; #0, #1, ... address batchdata slots
* '''filter''' (<code>func(columns:any...) -&gt; bool</code>): lambda function that decides whether a dataset is passed to the map phase
* '''mapColumns''' (<code>list</code>): list of columns that are fed into map; #0, #1, ... address batchdata slots
* '''map''' (<code>func(columns:any...) -&gt; any</code>): lambda function to extract data from the dataset
* '''stride''' (<code>int</code>): number of batchdata entries per batch row
* '''batchdata''' (<code>list</code>): flat batch buffer accessed via #N pseudo columns
* '''reduce''' (<code>func(any, any)</code>): (optional) lambda function to aggregate the map results ''(optional)''
* '''neutral''' (<code>any</code>): (optional) neutral element for the reduce phase, otherwise nil is assumed ''(optional)''
* '''reduce2''' (<code>func(any, any)</code>): (optional) second stage reduce function that will apply a result of reduce to the neutral element/accumulator ''(optional)''
* '''isOuter''' (<code>bool</code>): (optional) if true, in case of no hits, call map once anyway with NULL values ''(optional)''
 
<span id="returns-15"></span>
=== Returns ===
 
<code>any</code>
 
== scan_order_batch_accept ==
 
incrementally scans a table or existing RecSet in scan_order order and applies a RecSet batch filter before OFFSET/LIMIT and map/reduce. The first candidate RecSet contains offset+limit rows; if too few rows are accepted, subsequent disjoint batches contain twice as many candidates until the accepted limit is satisfied or the input is exhausted. batchFilter is called as (batchFilter input_recset) and must return an exact subset RecSet of the same base table and transaction. A simple batchFilter may call (scan_recset tx input_recset filterColumns realFilter); complex filters may project input_recset to another table, apply search/ACL scans and project the result back to the input table. The returned RecSet is used only as a membership mask against the already ordered candidate vector, so output order is preserved without scanning the unordered RecSet again. For non-unique ORDER BY values, include an explicit unique tie-breaker. sortcols/sortdirs may both be empty; that path greedily collects candidates without sorting. limitPartitionCols is present for scan_order signature compatibility and currently must be 0


'''Returns:''' <code>any</code>
'''Allowed number of parameters:''' 10–14
 
<span id="parameters-16"></span>
=== Parameters ===
 
* '''tx''' (<code>any</code>): transaction context used consistently by the candidate scan and every batch filter operation; usually ((context &quot;session&quot;) &quot;__memcp_tx&quot;)
* '''table_or_recset''' (<code>table|recset</code>): base table or complete existing query-local RecSet from which ordered candidate batches are drawn
* '''batchFilter''' (<code>func(input_recset:recset) -&gt; recset</code>): function (lambda (input_recset) accepted_recset). It may naively narrow input_recset with scan_recset, or run arbitrary RecSet projections/search/ACL operations and project back. It must return a same-table, same-transaction subset of input_recset
* '''sortcols''' (<code>list</code>): same as scan_order: columns or computed sort functions. Include a unique tie-breaker for a total repeatable order; use an empty list for greedy unsorted collection
* '''sortdirs''' (<code>list</code>): same as scan_order: one relation per sort column (&lt;, &gt; or collate relation); must also be empty when sortcols is empty
* '''limitPartitionCols''' (<code>number</code>): reserved for scan_order signature compatibility; currently must be 0
* '''offset''' (<code>number</code>): number of batch-filter-accepted rows to skip; it is not the number of driver candidates already examined
* '''limit''' (<code>number</code>): finite maximum number of accepted rows passed to map; the initial candidate batch size is offset+limit and doubles for every subsequent batch
* '''mapColumns''' (<code>list</code>): physical columns passed to map after filtering; pseudo columns are $update (update/delete current row), $recset_contains (row-bound RecSet membership), $set:<column>, $increment:<column>, and $invalidate:<column> (computed-column maintenance), plus NEW.<column> in trigger plans; $break is reserved for internal ORC convergence and must not implement SQL OFFSET/LIMIT, which belong in the native offset and limit arguments
* '''map''' (<code>func(columns:any...) -&gt; any</code>): same map callback contract as scan_order; accepted record IDs are passed to its shard mapper in batches
* '''reduce''' (<code>func(acc:any, val:any) -&gt; any</code>): optional serial reducer over mapped accepted rows, with the same accumulator contract as scan_order ''(optional)''
* '''neutral''' (<code>any</code>): optional neutral element for reduce; defaults to nil ''(optional)''
* '''isOuter''' (<code>bool</code>): optional scan_order-compatible outer behavior: map one NULL row when no accepted row reaches map ''(optional)''
* '''notFoundValue''' (<code>any</code>): optional result when no accepted row reaches map and isOuter is false; defaults to neutral ''(optional)''
 
<span id="returns-16"></span>
=== Returns ===
 
<code>any</code>


== scan_order ==
== scan_order ==


Performs an ordered parallel filter with serial map-reduce
does an ordered parallel filter and serial map-reduce pass on a single table and returns the reduced result
 
'''Allowed number of parameters:''' 11–17
 
<span id="parameters-17"></span>
=== Parameters ===
 
* '''tx''' (<code>any</code>): transaction context to use for visibility and mutations; usually ((context &quot;session&quot;) &quot;__memcp_tx&quot;)
* '''table''' (<code>table|list|recset</code>): table handle, query-local RecSet, or a list for temporary data
* '''filterColumns''' (<code>list</code>): physical columns passed to filter before map/reduce; $recset_contains supplies a row-bound RecSet membership closure
* '''filter''' (<code>func(columns:any...) -&gt; bool</code>): lambda function that decides whether a dataset is passed to the map phase. You can use any column of that table as lambda parameter. You should structure your lambda with an (and) at the root element. Every equal? &lt; &gt; &lt;= &gt;= will possibly translated to an indexed scan
* '''sortcols''' (<code>list</code>): list of columns to sort. Each column is either a string to point to an existing column or a func(cols...)-&gt;any to compute a sortable value
* '''sortdirs''' (<code>list</code>): list of column directions to sort. Must be same length as sortcols. &lt; means ascending, &gt; means descending, (collate ...) will add collations
* '''limitPartitionCols''' (<code>number</code>): number of leading sort columns that form the partition key for per-partition offset/limit. 0 (default) means global offset/limit.
* '''offset''' (<code>number</code>): number of globally ordered, filter-accepted items to skip before map; apply SQL OFFSET here rather than in map
* '''limit''' (<code>number</code>): maximum globally ordered, filter-accepted items passed to map; -1 means unlimited; apply SQL LIMIT here so shard-local Top-K and the global merge can brake early
* '''mapColumns''' (<code>list</code>): physical columns passed to map after filtering; pseudo columns are $update (update/delete current row), $recset_contains (row-bound RecSet membership), $set:<column>, $increment:<column>, and $invalidate:<column> (computed-column maintenance), plus NEW.<column> in trigger plans; $break is reserved for internal ORC convergence and must not implement SQL OFFSET/LIMIT, which belong in the native offset and limit arguments
* '''map''' (<code>func(columns:any...) -&gt; any</code>): lambda function to extract data from the dataset. You can use any column of that table as lambda parameter. You can return a value you want to extract and pass to reduce, but you can also directly call insert, print or resultrow functions. If you declare a parameter named '<math display="inline">update', this variable will hold a function that you can use to delete or update a row. Call (</math>update) to delete the dataset, call ($update '(&quot;field1&quot; value1 &quot;field2&quot; value2)) to update certain columns.
* '''reduce''' (<code>func(acc:any, val:any) -&gt; any</code>): (optional) lambda function to aggregate the map results. It takes two parameters (a b) where a is the accumulator and b the new value. The accumulator for the first reduce call is the neutral element. The return value will be the accumulator input for the next reduce call. There are two reduce phases: shard-local and shard-collect. In the shard-local phase, a starts with neutral and b is fed with the return values of each map call. In the shard-collect phase, a starts with neutral and b is fed with the result of each shard-local pass. ''(optional)''
* '''neutral''' (<code>any</code>): (optional) neutral element for the reduce phase, otherwise nil is assumed ''(optional)''
* '''isOuter''' (<code>bool</code>): (optional) if true, in case of no hits, call map once anyway with NULL values ''(optional)''
* '''notFoundValue''' (<code>any</code>): (optional) result for no hits when isOuter is false; defaults to neutral ''(optional)''
* '''postOrderFilterColumns''' (<code>list</code>): (optional) columns for a predicate evaluated in global order before OFFSET/LIMIT are counted; use for expensive acceptance checks that cannot participate in index boundaries ''(optional)''
* '''postOrderFilter''' (<code>func(columns:any...) -&gt; bool</code>): (optional) late acceptance predicate. Rejected rows do not count toward OFFSET/LIMIT and never reach map. SQL plans use this instead of callback-driven $break control flow ''(optional)''
 
<span id="returns-17"></span>
=== Returns ===
 
<code>any</code>
 
== scan_order_multi ==
 
does an ordered parallel filter and serial map-reduce pass across multiple tables simultaneously, merging results into a single sorted stream
 
'''Allowed number of parameters:''' 13–17
 
<span id="parameters-18"></span>
=== Parameters ===


'''Allowed number of parameters:''' 10–13
* '''tx''' (<code>any</code>): transaction context
* '''tables''' (<code>list</code>): list of table handles
* '''filterColumns''' (<code>list</code>): list of filter column lists, one per table
* '''filterFns''' (<code>list</code>): list of filter lambdas, one per table
* '''sortcols''' (<code>list</code>): list of sort column lists, one per table
* '''sortdirs''' (<code>list</code>): list of sort direction comparators (shared)
* '''perTableOffset''' (<code>list</code>): per-table offset (list of int; -1 disables)
* '''perTableLimit''' (<code>list</code>): per-table limit (list of int; -1 disables)
* '''limitPartitionCols''' (<code>number</code>): number of leading sort columns forming partition key
* '''offset''' (<code>number</code>): number of items to skip (global)
* '''limit''' (<code>number</code>): max number of items to read (global; -1 = unlimited)
* '''mapColumns''' (<code>list</code>): list of map column lists, one per table
* '''mapFns''' (<code>list</code>): list of map lambdas, one per table
* '''reduce''' (<code>func</code>): (optional) aggregation function ''(optional)''
* '''neutral''' (<code>any</code>): (optional) neutral element for reduce ''(optional)''
* '''isOuter''' (<code>bool</code>): (optional) if true, emit null row when no hits ''(optional)''
* '''notFoundValue''' (<code>any</code>): (optional) result for no hits when isOuter is false; defaults to neutral ''(optional)''


'''Parameters:'''
<span id="returns-18"></span>
* '''schema''' (<code>string</code>): database
=== Returns ===
* '''table''' (<code>string</code>): table name
* '''filterColumns''' (<code>list</code>): filter columns
* '''filter''' (<code>func</code>): filter lambda
* '''sortcols''' (<code>list</code>): sorting columns
* '''sortdirs''' (<code>list</code>): sorting directions
* '''offset''' (<code>number</code>): skip count
* '''limit''' (<code>number</code>): max items
* '''mapColumns''' (<code>list</code>): map columns
* '''map''' (<code>func</code>): map lambda
* '''reduce''' (<code>func</code>): optional reducer
* '''neutral''' (<code>any</code>): neutral element
* '''isOuter''' (<code>bool</code>): outer behavior


'''Returns:''' <code>any</code>
<code>any</code>


== createdatabase ==
== createdatabase ==


Creates a new database
creates a new database


'''Allowed number of parameters:''' 1–2
'''Allowed number of parameters:''' 1–2


'''Parameters:'''
<span id="parameters-19"></span>
* '''schema''' (<code>string</code>): database name
=== Parameters ===
* '''ignoreexists''' (<code>bool</code>): ignore if exists
 
* '''schema''' (<code>string</code>): name of the new database
* '''ignoreexists''' (<code>bool</code>): if true, return false instead of throwing an error ''(optional)''
 
<span id="returns-19"></span>
=== Returns ===


'''Returns:''' <code>bool</code>
<code>bool</code>


== dropdatabase ==
== dropdatabase ==


Drops a database
drops a database
 
'''Allowed number of parameters:''' 1–2
 
<span id="parameters-20"></span>
=== Parameters ===
 
* '''schema''' (<code>string</code>): name of the database
* '''ifexists''' (<code>bool</code>): if true, don't throw an error if it doesn't exist ''(optional)''
 
<span id="returns-20"></span>
=== Returns ===
 
<code>bool</code>
 
== checktablemaintenance ==
 
checks whether a user-initiated maintenance operation is allowed for a table
 
'''Allowed number of parameters:''' 3–3
 
<span id="parameters-21"></span>
=== Parameters ===
 
* '''schema''' (<code>string</code>):
* '''table''' (<code>string</code>):
* '''operation''' (<code>string</code>):
 
<span id="returns-21"></span>
=== Returns ===
 
<code>bool</code>
 
== maintenance_capabilities ==
 
returns the server-side maintenance capabilities for a database or table
 
'''Allowed number of parameters:''' 1–2
 
<span id="parameters-22"></span>
=== Parameters ===


'''Allowed number of parameters:''' 1–1
* '''schema''' (<code>string</code>):
* '''table''' (<code>string</code>): ''(optional)''


'''Parameters:'''
<span id="returns-22"></span>
* '''schema''' (<code>string</code>): database name
=== Returns ===


'''Returns:''' <code>bool</code>
<code>list</code>


== createtable ==
== createtable ==


Creates a new table
creates a table, runs its oninit option and registered after-create-table lifecycle triggers synchronously, and returns only after initialization completes; concurrent if-not-exists callers wait for that same completion


'''Allowed number of parameters:''' 4–5
'''Allowed number of parameters:''' 4–5


'''Parameters:'''
<span id="parameters-23"></span>
* '''schema''' (<code>string</code>): database
=== Parameters ===
* '''table''' (<code>string</code>): table name
 
* '''cols''' (<code>list</code>): column definitions
* '''schema''' (<code>string</code>): name of the existing database that will contain the table
* '''options''' (<code>list</code>): table options
* '''table''' (<code>string</code>): name of the table to create
* '''ifnotexists''' (<code>bool</code>): safe creation
* '''cols''' (<code>list</code>): column and constraint definitions: (&quot;column&quot; name type dimensions typeparams), (&quot;unique&quot; name columns), or (&quot;foreign&quot; name local_columns referenced_table referenced_columns update_mode delete_mode). dimensions is a list of integer type dimensions. typeparams is an alternating key/value list supporting primary (bool), unique (bool), auto_increment (bool), null (bool), default (any), default_expression (string), update (expression), comment (string), collate (string), temp (bool), filtercols (string list), filter (function), sortcols (string list), sortdirs (bool list), partitioncount (integer), mapcols (string list), mapfn (function), reducefn (function), and reduceinit (any). Column lists are string lists; foreign-key modes are restrict, cascade, or set null
* '''options''' (<code>list</code>): alternating key/value list; supported keys are engine (safe, logged, sloppy, memory, or cache), collation (string), charset (string), comment (string), auto_increment (non-negative integer), and oninit (closed zero-argument function run synchronously once per data generation; concurrent if-not-exists callers wait for it, and memory/cache tables persist the callback so the first idempotent createtable after restart repopulates their empty data)
* '''ifnotexists''' (<code>bool</code>): when true, return false instead of failing if the table exists; if another caller is still creating it, wait for that caller's after-create-table initialization before returning false ''(optional)''
 
<span id="returns-23"></span>
=== Returns ===


'''Returns:''' <code>bool</code>
<code>bool</code>


== createcolumn ==
== createcolumn ==


Creates a new column in a table
creates a new column in table
 
'''Allowed number of parameters:''' 5–7
 
<span id="parameters-24"></span>
=== Parameters ===


'''Allowed number of parameters:''' 6–8
* '''table''' (<code>table</code>):
* '''colname''' (<code>string</code>): name of the new column
* '''type''' (<code>string</code>): name of the basetype
* '''dimensions''' (<code>list</code>): dimensions of the type (e.g. for decimal)
* '''options''' (<code>list</code>): assoc list: primary, unique, auto_increment, null, comment, default, default_expression, collate; ORC: sortcols, sortdirs, partitioncount, mapcols, mapfn, reducefn, reduceinit
* '''computorCols''' (<code>list</code>): list of columns that is passed into params of computor ''(optional)''
* '''computor''' (<code>func(columns:any...) -&gt; any</code>): lambda expression that can take other column values and computes the value of that column ''(optional)''


'''Parameters:'''
<span id="returns-24"></span>
* '''schema''' (<code>string</code>): database
=== Returns ===
* '''table''' (<code>string</code>): table name
* '''colname''' (<code>string</code>): column name
* '''type''' (<code>string</code>): base type
* '''dimensions''' (<code>list</code>): type dimensions
* '''options''' (<code>list</code>): column options
* '''computorCols''' (<code>list</code>): columns for computed value
* '''computor''' (<code>func</code>): compute lambda


'''Returns:''' <code>bool</code>
<code>bool</code>


== createkey ==
== createkey ==


Creates a new key
creates a new key on a table
 
'''Allowed number of parameters:''' 4–4
 
<span id="parameters-25"></span>
=== Parameters ===


'''Allowed number of parameters:''' 5–5
* '''table''' (<code>table</code>):
* '''keyname''' (<code>string</code>): name of the new key
* '''unique''' (<code>bool</code>): whether the key is unique
* '''columns''' (<code>list</code>): list of columns to include


'''Parameters:'''
<span id="returns-25"></span>
* '''schema''' (<code>string</code>): database
=== Returns ===
* '''table''' (<code>string</code>): table
* '''keyname''' (<code>string</code>): key name
* '''unique''' (<code>bool</code>): uniqueness flag
* '''columns''' (<code>list</code>): key columns


'''Returns:''' <code>bool</code>
<code>bool</code>


== createforeignkey ==
== createforeignkey ==


Creates a new foreign key
creates a new foreign key on a table


'''Allowed number of parameters:''' 8–8
'''Allowed number of parameters:''' 7–7


'''Parameters:'''
<span id="parameters-26"></span>
* '''schema''' (<code>string</code>): database
=== Parameters ===
* '''keyname''' (<code>string</code>): key name
* '''table1''' (<code>string</code>): first table
* '''columns1''' (<code>list</code>): first columns
* '''table2''' (<code>string</code>): second table
* '''columns2''' (<code>list</code>): second columns
* '''updatemode''' (<code>string</code>): action on update
* '''deletemode''' (<code>string</code>): action on delete


'''Returns:''' <code>bool</code>
* '''table1''' (<code>table</code>):
* '''keyname''' (<code>string</code>): name of the new key
* '''columns1''' (<code>list</code>): list of columns to include
* '''table2''' (<code>table</code>):
* '''columns2''' (<code>list</code>): list of columns to include
* '''updatemode''' (<code>string</code>): restrict|cascade|set null
* '''deletemode''' (<code>string</code>): restrict|cascade|set null
 
<span id="returns-26"></span>
=== Returns ===
 
<code>bool</code>


== shardcolumn ==
== shardcolumn ==


Suggests partitions for a column
tells us how it would partition a column according to their values. Returns a list of pivot elements.
 
'''Allowed number of parameters:''' 2–3
 
<span id="parameters-27"></span>
=== Parameters ===


'''Allowed number of parameters:''' 3–4
* '''table''' (<code>table</code>):
* '''colname''' (<code>string</code>): name of the column
* '''numpartitions''' (<code>number</code>): number of partitions; optional. leave 0 if you want to detect the partiton number automatically or copy the partition schema of the table ''(optional)''


'''Parameters:'''
<span id="returns-27"></span>
* '''schema''' (<code>string</code>): database
=== Returns ===
* '''table''' (<code>string</code>): table
* '''colname''' (<code>string</code>): column
* '''numpartitions''' (<code>number</code>): partitions


'''Returns:''' <code>list</code>
<code>list</code>


== partitiontable ==
== partitiontable ==


Applies or adjusts a partition scheme
suggests a partition scheme for a table. If the table has no partition scheme yet, it will immediately apply that scheme and return true. If the table already has a partition scheme, it will alter the partitioning score such that the partitioning scheme is considered in the next repartitioning and return false.


'''Allowed number of parameters:''' 3–3
'''Allowed number of parameters:''' 2–2
 
<span id="parameters-28"></span>
=== Parameters ===
 
* '''table''' (<code>table</code>):
* '''columns''' (<code>list</code>): associative list of string -&gt; list representing column name -&gt; pivots. You can compute pivots by (shardcolumn ...)


'''Parameters:'''
<span id="returns-28"></span>
* '''schema''' (<code>string</code>): database
=== Returns ===
* '''table''' (<code>string</code>): table
* '''columns''' (<code>list</code>): column pivots


'''Returns:''' <code>bool</code>
<code>bool</code>


== altertable ==
== altertable ==


Alters a table
alters a table


'''Allowed number of parameters:''' 4–4
'''Allowed number of parameters:''' 3–3
 
<span id="parameters-29"></span>
=== Parameters ===
 
* '''table''' (<code>table</code>):
* '''operation''' (<code>string</code>): one of owner|drop|engine|collation|auto_increment
* '''parameter''' (<code>any</code>): name of the column to drop or value of the parameter


'''Parameters:'''
<span id="returns-29"></span>
* '''schema''' (<code>string</code>): database
=== Returns ===
* '''table''' (<code>string</code>): table
* '''operation''' (<code>string</code>): alteration
* '''parameter''' (<code>any</code>): parameter


'''Returns:''' <code>bool</code>
<code>bool</code>


== altercolumn ==
== altercolumn ==


Alters a column
alters a column
 
'''Allowed number of parameters:''' 4–4
 
<span id="parameters-30"></span>
=== Parameters ===


'''Allowed number of parameters:''' 5–5
* '''table''' (<code>table</code>):
* '''column''' (<code>string</code>): name of the column
* '''operation''' (<code>string</code>): one of drop|type|collation|auto_increment|comment
* '''parameter''' (<code>any</code>): name of the column to drop or value of the parameter


'''Parameters:'''
<span id="returns-30"></span>
* '''schema''' (<code>string</code>): database
=== Returns ===
* '''table''' (<code>string</code>): table
* '''column''' (<code>string</code>): column
* '''operation''' (<code>string</code>): alteration
* '''parameter''' (<code>any</code>): parameter


'''Returns:''' <code>bool</code>
<code>bool</code>


== droptable ==
== droptable ==


Removes a table
removes a table


'''Allowed number of parameters:''' 2–3
'''Allowed number of parameters:''' 2–3


'''Parameters:'''
<span id="parameters-31"></span>
* '''schema''' (<code>string</code>): database
=== Parameters ===
* '''table''' (<code>string</code>): table
 
* '''ifexists''' (<code>bool</code>): safe drop
* '''schema''' (<code>string</code>):
* '''table''' (<code>string</code>):
* '''ifexists''' (<code>bool</code>): if true, don't throw an error if it already exists ''(optional)''
 
<span id="returns-31"></span>
=== Returns ===
 
<code>bool</code>
 
== dropcolumn ==
 
drops a column from a table
 
'''Allowed number of parameters:''' 2–2
 
<span id="parameters-32"></span>
=== Parameters ===
 
* '''table''' (<code>table</code>):
* '''column''' (<code>string</code>): name of the column to drop
 
<span id="returns-32"></span>
=== Returns ===
 
<code>bool</code>
 
== migratedropcolumn ==
 
drops a legacy system column during startup migration
 
'''Allowed number of parameters:''' 2–2
 
<span id="parameters-33"></span>
=== Parameters ===
 
* '''table''' (<code>table</code>):
* '''column''' (<code>string</code>): legacy column name
 
<span id="returns-33"></span>
=== Returns ===
 
<code>bool</code>
 
== invalidatecolumn ==
 
marks all values of a computed column as stale
 
'''Allowed number of parameters:''' 2–2
 
<span id="parameters-34"></span>
=== Parameters ===
 
* '''table''' (<code>table</code>):
* '''column''' (<code>string</code>): name of the computed column
 
<span id="returns-34"></span>
=== Returns ===
 
<code>bool</code>
 
== invalidateorc ==
 
invalidates ORC column rows from a sort key onwards via validMask scan
 
'''Allowed number of parameters:''' 3–3
 
<span id="parameters-35"></span>
=== Parameters ===
 
* '''table''' (<code>table</code>):
* '''column''' (<code>string</code>): name of the ORC column
* '''sortkeys''' (<code>list</code>): composite sort key values from which to invalidate
 
<span id="returns-35"></span>
=== Returns ===
 
<code>bool</code>
 
== register_keytable_cleanup ==
 
registers triggers on a base table to maintain keytable entries (insert/delete group keys)
 
'''Allowed number of parameters:''' 4–4
 
<span id="parameters-36"></span>
=== Parameters ===
 
* '''base_table''' (<code>table</code>):
* '''kt_table''' (<code>table</code>):
* '''tblvar''' (<code>string</code>): table alias used in scan column prefixes
* '''key_pairs''' (<code>list</code>): list of (base_col kt_col) pairs
 
<span id="returns-36"></span>
=== Returns ===
 
<code>bool</code>
 
== initialize_cache_table ==
 
registers maintenance, locks source tables for a consistent snapshot, and runs a canonical planner-cache initializer exactly once
 
'''Allowed number of parameters:''' 5–6
 
<span id="parameters-37"></span>
=== Parameters ===
 
* '''transaction''' (<code>any</code>): explicit transaction context carrying query-session ownership
* '''table''' (<code>table</code>):
* '''source_tables''' (<code>list</code>):
* '''register_maintenance''' (<code>func</code>):
* '''initializer''' (<code>func</code>):
* '''finalizer''' (<code>func</code>): optional zero-argument finalizer run under the same source-table locks after initialization ''(optional)''
 
<span id="returns-37"></span>
=== Returns ===
 
<code>bool</code>
 
== touch_keytable ==
 
extends the lease on a keytable so CacheManager defers eviction
 
'''Allowed number of parameters:''' 1–1
 
<span id="parameters-38"></span>
=== Parameters ===
 
* '''table''' (<code>table</code>):
 
<span id="returns-38"></span>
=== Returns ===
 
<code>bool</code>
 
== locktables ==
 
acquires WRITE or READ user-level locks on a list of tables (LOCK TABLES); implicitly releases any previously held locks
 
'''Allowed number of parameters:''' 1–1
 
<span id="parameters-39"></span>
=== Parameters ===
 
* '''locks''' (<code>list</code>): flat list of schema, table, write? triples
 
<span id="returns-39"></span>
=== Returns ===
 
<code>bool</code>
 
== unlocktables ==
 
releases all user-level table locks held by this session
 
'''Allowed number of parameters:''' 0–0
 
<span id="parameters-40"></span>
=== Parameters ===
 
''This function has no parameters.''
 
<span id="returns-40"></span>
=== Returns ===
 
<code>bool</code>


'''Returns:''' <code>bool</code>
== get_fk_target ==
 
returns (ref_table ref_column) if a single-column FK exists for the given column, nil otherwise
 
'''Allowed number of parameters:''' 2–2
 
<span id="parameters-41"></span>
=== Parameters ===
 
* '''table''' (<code>table</code>):
* '''column''' (<code>string</code>): column name
 
<span id="returns-41"></span>
=== Returns ===
 
<code>any</code>
 
== renametable ==
 
renames a table
 
'''Allowed number of parameters:''' 3–3
 
<span id="parameters-42"></span>
=== Parameters ===
 
* '''schema''' (<code>string</code>): name of the database
* '''oldname''' (<code>string</code>): current name of the table
* '''newname''' (<code>string</code>): new name of the table
 
<span id="returns-42"></span>
=== Returns ===
 
<code>bool</code>


== insert ==
== insert ==


Inserts a dataset into a table
inserts a new dataset into table and returns the number of successful items
 
'''Allowed number of parameters:''' 3–7
 
<span id="parameters-43"></span>
=== Parameters ===


'''Allowed number of parameters:''' 4–7
* '''table''' (<code>table</code>):
* '''columns''' (<code>list</code>): list of column names, e.g. '(&quot;ID&quot;, &quot;value&quot;)
* '''datasets''' (<code>list</code>): list of list of column values, e.g. '('(1 10) '(2 15))
* '''onCollisionCols''' (<code>list</code>): list of columns of the old dataset that have to be passed to onCollision. Can also request $update, $set:<computed-column>, or NEW.<insert-column>. ''(optional)''
* '''onCollision''' (<code>func</code>): function called for each collision. Its positional parameters are the values requested by onCollisionCols, in the same order. If omitted, collisions raise an error. ''(optional)''
* '''mergeNull''' (<code>bool</code>): if true, it will handle NULL values as equal according to SQL 2003's definition of DISTINCT (https://en.wikipedia.org/wiki/Null_(SQL)#When_two_nulls_are_equal:_grouping,_sorting,_and_some_set_operations) ''(optional)''
* '''onInsertid''' (<code>func(id:number) -&gt; any</code>): (optional) callback (id)-&gt;any; called once with the first auto_increment id assigned for this INSERT ''(optional)''


'''Parameters:'''
<span id="returns-43"></span>
* '''schema''' (<code>string</code>): database
=== Returns ===
* '''table''' (<code>string</code>): table
* '''columns''' (<code>list</code>): column names
* '''datasets''' (<code>list</code>): row values
* '''onCollisionCols''' (<code>list</code>): collision columns
* '''onCollision''' (<code>func</code>): collision handler
* '''mergeNull''' (<code>bool</code>): handle null as equal


'''Returns:''' <code>number</code>
<code>number</code>


== stat ==
== stat ==


Returns memory statistics
return system statistics as assoc: mem_available, mem_total, process_memory, shard_memory, shard_budget, persisted_memory, persisted_budget, cache_entry_count, cache_entry_size. (stat schema) and (stat schema tbl) return a string with detailed memory usage.


'''Allowed number of parameters:''' 0–2
'''Allowed number of parameters:''' 0–2


'''Parameters:'''
<span id="parameters-44"></span>
* '''schema''' (<code>string</code>): database (optional)
=== Parameters ===
* '''table''' (<code>string</code>): table (optional)
 
* '''schema''' (<code>string</code>): (optional) database name for detailed string output ''(optional)''
* '''table''' (<code>string</code>): (optional) table name for detailed string output ''(optional)''
 
<span id="returns-44"></span>
=== Returns ===
 
<code>any</code>
 
== totalmem ==
 
Returns total physical memory in bytes (from /proc/meminfo)
 
'''Allowed number of parameters:''' 0–0
 
<span id="parameters-45"></span>
=== Parameters ===
 
''This function has no parameters.''
 
<span id="returns-45"></span>
=== Returns ===
 
<code>number</code>
 
== resolve_column_name ==
 
resolve a physical column name from immutable table metadata
 
'''Allowed number of parameters:''' 4–4
 
<span id="parameters-46"></span>
=== Parameters ===
 
* '''schema''' (<code>string</code>): database name
* '''table''' (<code>string</code>): table name
* '''column''' (<code>string</code>): column name
* '''ignorecase''' (<code>bool</code>): whether identifier case is ignored
 
<span id="returns-46"></span>
=== Returns ===


'''Returns:''' <code>string</code>
<code>string|nil</code>


== show ==
== show ==


Shows databases, tables, or columns
show databases/tables/columns/shards
 
(show) lists database names (show schema) lists table names (show table_handle) lists the memoized column defs (show table_handle true) returns table metadata (show table_handle &quot;statistics&quot;) returns index statistics (show schema true) lists tables with full info: [{name,engine,row_count,size_bytes,collation,comment},...] (show schema tbl) lists column defs (show schema tbl true) returns assoc {columns,meta,shards} (show schema tbl N) returns shard N overview assoc {shard,state,main_count,delta,deletions,size_bytes} (show schema tbl N true) returns shard N full assoc adding columns and indexes (show schema tbl &quot;statistics&quot;) returns index statistics (used by INFORMATION_SCHEMA)
 
'''Allowed number of parameters:''' 0–4
 
<span id="parameters-47"></span>
=== Parameters ===
 
* '''schema_or_table''' (<code>string|table|recset</code>): (optional) database name or resolved table/recset handle ''(optional)''
* '''table_or_property''' (<code>string|bool</code>): (optional) table name, true for full info, or &quot;statistics&quot; for a handle ''(optional)''
* '''property''' (<code>int|bool|string</code>): (optional) shard index (int), true for full table info, or &quot;statistics&quot; ''(optional)''
* '''full''' (<code>bool</code>): (optional) true to include columns and indexes in shard detail ''(optional)''
 
<span id="returns-47"></span>
=== Returns ===
 
<code>any</code>
 
== show_triggers ==
 
show triggers for a given table
 
'''Allowed number of parameters:''' 1–2
 
<span id="parameters-48"></span>
=== Parameters ===


'''Allowed number of parameters:''' 0–2
* '''schema''' (<code>string</code>): database name
* '''table''' (<code>string</code>): (optional) table name, if omitted shows all triggers in schema ''(optional)''


'''Parameters:'''
<span id="returns-48"></span>
* '''schema''' (<code>string</code>): optional database
=== Returns ===
* '''table''' (<code>string</code>): optional table


'''Returns:''' <code>any</code>
<code>any</code>


== rebuild ==
== rebuild ==


Rebuilds storages
rebuilds main storages and returns the amount of time it took; with a table handle, rebuilds only that table
 
'''Allowed number of parameters:''' 0–3
 
<span id="parameters-49"></span>
=== Parameters ===


'''Allowed number of parameters:''' 0–2
* '''table_or_all''' (<code>bool|table</code>): table handle for a table-local rebuild; otherwise whether to rebuild unchanged shards globally (default: false) ''(optional)''
* '''all_or_repartition''' (<code>bool</code>): with a table: whether to rebuild unchanged shards; globally: whether to repartition (default: true) ''(optional)''
* '''repartition''' (<code>bool</code>): with a table handle, whether to repartition that table (default: true) ''(optional)''


'''Parameters:'''
<span id="returns-49"></span>
* '''all''' (<code>bool</code>): rebuild all
=== Returns ===
* '''repartition''' (<code>bool</code>): repartition


'''Returns:''' <code>string</code>
<code>string</code>


<span id="loadcsv"></span>
== loadCSV ==
== loadCSV ==


Loads CSV into a table
loads a CSV stream into a table and returns the amount of time it took. The first line of the file must be the headlines. The headlines must match the table's columns exactly.


'''Allowed number of parameters:''' 3–5
'''Allowed number of parameters:''' 3–5


'''Parameters:'''
<span id="parameters-50"></span>
* '''schema''' (<code>string</code>): database
=== Parameters ===
* '''table''' (<code>string</code>): table
* '''stream''' (<code>stream</code>): CSV stream
* '''delimiter''' (<code>string</code>): delimiter
* '''firstline''' (<code>bool</code>): use header


'''Returns:''' <code>string</code>
* '''schema''' (<code>string</code>): name of the database
* '''table''' (<code>string</code>): name of the table
* '''stream''' (<code>stream</code>): CSV file, load with: (stream filename)
* '''delimiter''' (<code>string</code>): (optional) delimiter defaults to &quot;;&quot; ''(optional)''
* '''firstline''' (<code>bool</code>): (optional) if the first line contains the column names (otherwise, the tables column order is used) ''(optional)''


<span id="returns-50"></span>
=== Returns ===
<code>string</code>
<span id="loadjson"></span>
== loadJSON ==
== loadJSON ==


Loads JSONL into a database
loads a .jsonl file from stream into a database and returns the amount of time it took. JSONL is a linebreak separated file of JSON objects. Each JSON object is one dataset in the database. Before you add rows, you must declare the table in a line '#table <tablename>'. All other lines starting with # are comments. Columns are created dynamically as soon as they occur in a json object.


'''Allowed number of parameters:''' 2–2
'''Allowed number of parameters:''' 2–2


'''Parameters:'''
<span id="parameters-51"></span>
* '''schema''' (<code>string</code>): database
=== Parameters ===
* '''stream''' (<code>stream</code>): JSONL stream


'''Returns:''' <code>string</code>
* '''schema''' (<code>string</code>): name of the database where you want to put the tables in
* '''stream''' (<code>stream</code>): stream of the .jsonl file, read with: (stream filename)
 
<span id="returns-51"></span>
=== Returns ===
 
<code>string</code>


== settings ==
== settings ==


Reads or writes global settings
reads or writes a global settings value. This modifies your data/settings.json.


'''Allowed number of parameters:''' 1–2
'''Allowed number of parameters:''' 0–2
 
<span id="parameters-52"></span>
=== Parameters ===
 
* '''key''' (<code>string</code>): name of the key to set or get (for reference, rts) ''(optional)''
* '''value''' (<code>any</code>): new value of that setting ''(optional)''
 
<span id="returns-52"></span>
=== Returns ===
 
<code>any</code>
 
== createcreatetabletrigger ==
 
registers a lifecycle trigger that fires synchronously after a future createtable for the given schema/table succeeds
 
'''Allowed number of parameters:''' 6–6
 
<span id="parameters-53"></span>
=== Parameters ===
 
* '''schema''' (<code>string</code>): name of the database
* '''table''' (<code>string</code>): name of the table to watch for creation
* '''name''' (<code>string</code>): name of the trigger
* '''source_sql''' (<code>string</code>): original SQL body text (for diagnostics)
* '''body''' (<code>any</code>): trigger body (Scheme procedure or deferred trigger expression)
* '''visible''' (<code>bool</code>): true = user trigger, false = internal trigger
 
<span id="returns-53"></span>
=== Returns ===
 
<code>bool</code>
 
== dropcreatetabletrigger ==
 
removes a registered create-table lifecycle trigger
 
'''Allowed number of parameters:''' 4–4
 
<span id="parameters-54"></span>
=== Parameters ===
 
* '''schema''' (<code>string</code>): name of the database
* '''table''' (<code>string</code>): name of the table watched for creation
* '''name''' (<code>string</code>): name of the trigger
* '''ifexists''' (<code>bool</code>): don't throw error if trigger doesn't exist
 
<span id="returns-54"></span>
=== Returns ===
 
<code>bool</code>
 
== createtrigger ==
 
creates a new trigger on a table
 
'''Allowed number of parameters:''' 6–6
 
<span id="parameters-55"></span>
=== Parameters ===
 
* '''table''' (<code>table</code>):
* '''name''' (<code>string</code>): name of the trigger
* '''timing''' (<code>string</code>): one of: before_insert, after_insert, before_update, after_update, before_delete, after_delete
* '''source_sql''' (<code>string</code>): original SQL body text (for SHOW TRIGGERS)
* '''body''' (<code>any</code>): trigger body (parsed Scheme expression)
* '''visible''' (<code>bool</code>): true = user trigger (shown in SHOW TRIGGERS), false = internal trigger (hidden)
 
<span id="returns-55"></span>
=== Returns ===
 
<code>bool</code>
 
== droptrigger ==
 
removes a trigger from a table
 
'''Allowed number of parameters:''' 3–3
 
<span id="parameters-56"></span>
=== Parameters ===
 
* '''schema''' (<code>string</code>): name of the database
* '''name''' (<code>string</code>): name of the trigger
* '''ifexists''' (<code>bool</code>): don't throw error if trigger doesn't exist
 
<span id="returns-56"></span>
=== Returns ===
 
<code>bool</code>
 
== mysql_import ==
 
imports schema+data from a MySQL server into MemCP
 
'''Allowed number of parameters:''' 4–8
 
<span id="parameters-57"></span>
=== Parameters ===
 
* '''host''' (<code>string|nil</code>): MySQL host (nil =&gt; 127.0.0.1)
* '''port''' (<code>int|nil</code>): MySQL port (nil =&gt; 3306)
* '''username''' (<code>string</code>): MySQL username
* '''password''' (<code>string</code>): MySQL password
* '''sourcedb''' (<code>string|nil</code>): source database (omit/nil =&gt; all non-system dbs) ''(optional)''
* '''targetdb''' (<code>string|nil</code>): target database (omit/nil =&gt; sourcedb) ''(optional)''
* '''sourcetable''' (<code>string|nil</code>): source table (omit/nil =&gt; all tables in sourcedb) ''(optional)''
* '''targettable''' (<code>string|nil</code>): target table (omit/nil =&gt; sourcetable) ''(optional)''
 
<span id="returns-57"></span>
=== Returns ===
 
<code>bool</code>
 
== psql_import ==
 
imports schema+data from a PostgreSQL server into MemCP
 
'''Allowed number of parameters:''' 4–9
 
<span id="parameters-58"></span>
=== Parameters ===
 
* '''host''' (<code>string|nil</code>): PostgreSQL host (nil =&gt; 127.0.0.1)
* '''port''' (<code>int|nil</code>): PostgreSQL port (nil =&gt; 5432)
* '''username''' (<code>string</code>): PostgreSQL username
* '''password''' (<code>string</code>): PostgreSQL password
* '''sourcedb''' (<code>string|nil</code>): source database (omit/nil =&gt; all non-system dbs) ''(optional)''
* '''sourceschema''' (<code>string|nil</code>): source schema (omit/nil =&gt; all non-system schemas in sourcedb) ''(optional)''
* '''targetdb''' (<code>string|nil</code>): target database (omit/nil =&gt; sourcedb) ''(optional)''
* '''sourcetable''' (<code>string|nil</code>): source table (omit/nil =&gt; all tables in sourceschema) ''(optional)''
* '''targettable''' (<code>string|nil</code>): target table (omit/nil =&gt; sourcetable) ''(optional)''


'''Parameters:'''
<span id="returns-58"></span>
* '''key''' (<code>string</code>): setting key
=== Returns ===
* '''value''' (<code>any</code>): new value


'''Returns:''' <code>any</code>
<code>bool</code>

Revision as of 08:20, 27 August 2026

Storage

The Storage module provides functions to manage databases, tables, columns, keys, partitions, and large data operations in SCM.

← Back to Full SCM API documentation


table

resolves a schema+table name pair into a table handle

Allowed number of parameters: 2–2

Parameters

  • schema (string):
  • table (string):

Returns

table

scan_estimate

estimate output row count for a table scan

Allowed number of parameters: 1–1

Parameters

  • table (table):

Returns

int

table_planner_statistics

return the immutable O(1) planner-statistics snapshot for a table

Allowed number of parameters: 1–1

Parameters

  • table (table):

Returns

any

scan_selectivity_estimate

bounded estimate of visible rows matching a table filter; stops at max_rows and does not log scan telemetry

Allowed number of parameters: 5–5

Parameters

  • tx (any): transaction context to use for visibility; usually ((context "session") "__memcp_tx")
  • table (table):
  • condition_cols (list):
  • condition (any):
  • max_rows (int):

Returns

list

table_empty?

returns true if a table currently has no rows

Allowed number of parameters: 1–1

Parameters

  • table (table):

Returns

bool

scan_recset

builds a query-local record-set handle from one table scan, or -- when given an existing recset instead of a table -- narrows that recset to the members which also satisfy filter, re-evaluating filter only over its existing membership. The latter is the cheap way to AND a further (possibly subscan-heavy) condition onto an already-narrowed recset without re-touching rows outside it (e.g. evaluating an expensive correlated check only over the rows a cheap selective filter already narrowed a table down to). The returned value is not persisted and can be scanned like a table

Allowed number of parameters: 4–4

Parameters

  • tx (any): transaction context to use for visibility; usually ((context "session") "__memcp_tx")
  • table (any): a table, or an existing recset to narrow further
  • filterColumns (list):
  • filter (func(columns:any...) -> bool): lambda function that decides whether a row enters the recset

Returns

recset

recset_count

returns the number of currently stored recids in a query-local recset

Allowed number of parameters: 1–1

Parameters

  • recset (recset):

Returns

int

recset_project_join

projects a source recset through key columns into a query-local target-table recset

Allowed number of parameters: 5–5

Parameters

  • tx (any): transaction context to use for visibility; usually ((context "session") "__memcp_tx")
  • source_recset (recset):
  • source_key_columns (list):
  • target_table (table):
  • target_key_columns (list):

Returns

recset

recset_key_index

builds an immutable lookup function for key columns of the rows contained in a query-local recset

Allowed number of parameters: 3–3

Parameters

  • tx (any): transaction context used while reading source keys
  • source_recset (recset):
  • source_key_columns (list):

Returns

func(key:any...) -> bool

recset_union

combines query-local recsets from the same table and removes duplicate record IDs

Allowed number of parameters: 1–1

Parameters

  • recsets (list):

Returns

recset

recset_intersect

intersects query-local recsets from the same table

Allowed number of parameters: 1–1

Parameters

  • recsets (list):

Returns

recset

recset_difference

returns the records from the first query-local recset which occur in none of the following same-table recsets

Allowed number of parameters: 1–1

Parameters

  • recsets (list):

Returns

recset

recset_not

returns the complement of a query-local recset relative to the currently visible rows of its base table

Allowed number of parameters: 1–1

Parameters

  • recset (recset):

Returns

recset

scan_exists

returns true if a table contains at least one visible row matching the given filter; uses scan boundary analysis without map/reduce setup

Allowed number of parameters: 4–4

Parameters

  • tx (any): transaction context to use for visibility; usually ((context "session") "__memcp_tx")
  • table (table|list|recset):
  • filterColumns (list):
  • filter (func(columns:any...) -> bool): lambda function that decides whether a row exists

Returns

bool

scan

does an unordered parallel filter-map-reduce pass on a single table and returns the reduced result

Allowed number of parameters: 6–10

Parameters

  • tx (any): transaction context to use for visibility and mutations; usually ((context "session") "__memcp_tx")
  • table (table|list|recset): table handle, query-local recset, or a list for temporary data
  • filterColumns (list): physical columns passed to filter before map/reduce; $recset_contains supplies a row-bound RecSet membership closure
  • filter (func(columns:any...) -> bool): lambda function that decides whether a dataset is passed to the map phase. You can use any column of that table as lambda parameter. You should structure your lambda with an (and) at the root element. Every equal? < > <= >= will possibly translated to an indexed scan
  • mapColumns (list): physical columns passed to map after filtering; pseudo columns are $update (update/delete current row), $recset_contains (row-bound RecSet membership), $set:<column>, $increment:<column>, and $invalidate:<column> (computed-column maintenance), plus NEW.<column> in trigger plans
  • map (func(columns:any...) -> any): lambda function to extract data from the dataset. You can use any column of that table as lambda parameter. You can return a value you want to extract and pass to reduce, but you can also directly call insert, print or resultrow functions. If you declare a parameter named '<math display="inline">update', this variable will hold a function that you can use to delete or update a row. Call (</math>update) to delete the dataset, call ($update '("field1" value1 "field2" value2)) to update certain columns.
  • reduce (func(any, any)): (optional) lambda function to aggregate the map results. It takes two parameters (a b) where a is the accumulator and b the new value. The accumulator for the first reduce call is the neutral element. The return value will be the accumulator input for the next reduce call. There are two reduce phases: shard-local and shard-collect. In the shard-local phase, a starts with neutral and b is fed with the return values of each map call. In the shard-collect phase, a starts with neutral and b is fed with the result of each shard-local pass. (optional)
  • neutral (any): (optional) neutral element for the reduce phase, otherwise nil is assumed (optional)
  • reduce2 (func(any, any)): (optional) second stage reduce function that will apply a result of reduce to the neutral element/accumulator (optional)
  • isOuter (bool): (optional) if true, in case of no hits, call map once anyway with NULL values (optional)

Returns

any

scan_batch

does an unordered parallel filter-map-reduce pass on a single table using batchdata-backed #N pseudo columns and returns the reduced result

Allowed number of parameters: 8–12

Parameters

  • tx (any): transaction context to use for visibility and mutations; usually ((context "session") "__memcp_tx")
  • table (table|list|recset): table handle, query-local recset, or a list for temporary data
  • filterColumns (list): list of columns that are fed into filter; #0, #1, ... address batchdata slots
  • filter (func(columns:any...) -> bool): lambda function that decides whether a dataset is passed to the map phase
  • mapColumns (list): list of columns that are fed into map; #0, #1, ... address batchdata slots
  • map (func(columns:any...) -> any): lambda function to extract data from the dataset
  • stride (int): number of batchdata entries per batch row
  • batchdata (list): flat batch buffer accessed via #N pseudo columns
  • reduce (func(any, any)): (optional) lambda function to aggregate the map results (optional)
  • neutral (any): (optional) neutral element for the reduce phase, otherwise nil is assumed (optional)
  • reduce2 (func(any, any)): (optional) second stage reduce function that will apply a result of reduce to the neutral element/accumulator (optional)
  • isOuter (bool): (optional) if true, in case of no hits, call map once anyway with NULL values (optional)

Returns

any

scan_order_batch_accept

incrementally scans a table or existing RecSet in scan_order order and applies a RecSet batch filter before OFFSET/LIMIT and map/reduce. The first candidate RecSet contains offset+limit rows; if too few rows are accepted, subsequent disjoint batches contain twice as many candidates until the accepted limit is satisfied or the input is exhausted. batchFilter is called as (batchFilter input_recset) and must return an exact subset RecSet of the same base table and transaction. A simple batchFilter may call (scan_recset tx input_recset filterColumns realFilter); complex filters may project input_recset to another table, apply search/ACL scans and project the result back to the input table. The returned RecSet is used only as a membership mask against the already ordered candidate vector, so output order is preserved without scanning the unordered RecSet again. For non-unique ORDER BY values, include an explicit unique tie-breaker. sortcols/sortdirs may both be empty; that path greedily collects candidates without sorting. limitPartitionCols is present for scan_order signature compatibility and currently must be 0

Allowed number of parameters: 10–14

Parameters

  • tx (any): transaction context used consistently by the candidate scan and every batch filter operation; usually ((context "session") "__memcp_tx")
  • table_or_recset (table|recset): base table or complete existing query-local RecSet from which ordered candidate batches are drawn
  • batchFilter (func(input_recset:recset) -> recset): function (lambda (input_recset) accepted_recset). It may naively narrow input_recset with scan_recset, or run arbitrary RecSet projections/search/ACL operations and project back. It must return a same-table, same-transaction subset of input_recset
  • sortcols (list): same as scan_order: columns or computed sort functions. Include a unique tie-breaker for a total repeatable order; use an empty list for greedy unsorted collection
  • sortdirs (list): same as scan_order: one relation per sort column (<, > or collate relation); must also be empty when sortcols is empty
  • limitPartitionCols (number): reserved for scan_order signature compatibility; currently must be 0
  • offset (number): number of batch-filter-accepted rows to skip; it is not the number of driver candidates already examined
  • limit (number): finite maximum number of accepted rows passed to map; the initial candidate batch size is offset+limit and doubles for every subsequent batch
  • mapColumns (list): physical columns passed to map after filtering; pseudo columns are $update (update/delete current row), $recset_contains (row-bound RecSet membership), $set:<column>, $increment:<column>, and $invalidate:<column> (computed-column maintenance), plus NEW.<column> in trigger plans; $break is reserved for internal ORC convergence and must not implement SQL OFFSET/LIMIT, which belong in the native offset and limit arguments
  • map (func(columns:any...) -> any): same map callback contract as scan_order; accepted record IDs are passed to its shard mapper in batches
  • reduce (func(acc:any, val:any) -> any): optional serial reducer over mapped accepted rows, with the same accumulator contract as scan_order (optional)
  • neutral (any): optional neutral element for reduce; defaults to nil (optional)
  • isOuter (bool): optional scan_order-compatible outer behavior: map one NULL row when no accepted row reaches map (optional)
  • notFoundValue (any): optional result when no accepted row reaches map and isOuter is false; defaults to neutral (optional)

Returns

any

scan_order

does an ordered parallel filter and serial map-reduce pass on a single table and returns the reduced result

Allowed number of parameters: 11–17

Parameters

  • tx (any): transaction context to use for visibility and mutations; usually ((context "session") "__memcp_tx")
  • table (table|list|recset): table handle, query-local RecSet, or a list for temporary data
  • filterColumns (list): physical columns passed to filter before map/reduce; $recset_contains supplies a row-bound RecSet membership closure
  • filter (func(columns:any...) -> bool): lambda function that decides whether a dataset is passed to the map phase. You can use any column of that table as lambda parameter. You should structure your lambda with an (and) at the root element. Every equal? < > <= >= will possibly translated to an indexed scan
  • sortcols (list): list of columns to sort. Each column is either a string to point to an existing column or a func(cols...)->any to compute a sortable value
  • sortdirs (list): list of column directions to sort. Must be same length as sortcols. < means ascending, > means descending, (collate ...) will add collations
  • limitPartitionCols (number): number of leading sort columns that form the partition key for per-partition offset/limit. 0 (default) means global offset/limit.
  • offset (number): number of globally ordered, filter-accepted items to skip before map; apply SQL OFFSET here rather than in map
  • limit (number): maximum globally ordered, filter-accepted items passed to map; -1 means unlimited; apply SQL LIMIT here so shard-local Top-K and the global merge can brake early
  • mapColumns (list): physical columns passed to map after filtering; pseudo columns are $update (update/delete current row), $recset_contains (row-bound RecSet membership), $set:<column>, $increment:<column>, and $invalidate:<column> (computed-column maintenance), plus NEW.<column> in trigger plans; $break is reserved for internal ORC convergence and must not implement SQL OFFSET/LIMIT, which belong in the native offset and limit arguments
  • map (func(columns:any...) -> any): lambda function to extract data from the dataset. You can use any column of that table as lambda parameter. You can return a value you want to extract and pass to reduce, but you can also directly call insert, print or resultrow functions. If you declare a parameter named '<math display="inline">update', this variable will hold a function that you can use to delete or update a row. Call (</math>update) to delete the dataset, call ($update '("field1" value1 "field2" value2)) to update certain columns.
  • reduce (func(acc:any, val:any) -> any): (optional) lambda function to aggregate the map results. It takes two parameters (a b) where a is the accumulator and b the new value. The accumulator for the first reduce call is the neutral element. The return value will be the accumulator input for the next reduce call. There are two reduce phases: shard-local and shard-collect. In the shard-local phase, a starts with neutral and b is fed with the return values of each map call. In the shard-collect phase, a starts with neutral and b is fed with the result of each shard-local pass. (optional)
  • neutral (any): (optional) neutral element for the reduce phase, otherwise nil is assumed (optional)
  • isOuter (bool): (optional) if true, in case of no hits, call map once anyway with NULL values (optional)
  • notFoundValue (any): (optional) result for no hits when isOuter is false; defaults to neutral (optional)
  • postOrderFilterColumns (list): (optional) columns for a predicate evaluated in global order before OFFSET/LIMIT are counted; use for expensive acceptance checks that cannot participate in index boundaries (optional)
  • postOrderFilter (func(columns:any...) -> bool): (optional) late acceptance predicate. Rejected rows do not count toward OFFSET/LIMIT and never reach map. SQL plans use this instead of callback-driven $break control flow (optional)

Returns

any

scan_order_multi

does an ordered parallel filter and serial map-reduce pass across multiple tables simultaneously, merging results into a single sorted stream

Allowed number of parameters: 13–17

Parameters

  • tx (any): transaction context
  • tables (list): list of table handles
  • filterColumns (list): list of filter column lists, one per table
  • filterFns (list): list of filter lambdas, one per table
  • sortcols (list): list of sort column lists, one per table
  • sortdirs (list): list of sort direction comparators (shared)
  • perTableOffset (list): per-table offset (list of int; -1 disables)
  • perTableLimit (list): per-table limit (list of int; -1 disables)
  • limitPartitionCols (number): number of leading sort columns forming partition key
  • offset (number): number of items to skip (global)
  • limit (number): max number of items to read (global; -1 = unlimited)
  • mapColumns (list): list of map column lists, one per table
  • mapFns (list): list of map lambdas, one per table
  • reduce (func): (optional) aggregation function (optional)
  • neutral (any): (optional) neutral element for reduce (optional)
  • isOuter (bool): (optional) if true, emit null row when no hits (optional)
  • notFoundValue (any): (optional) result for no hits when isOuter is false; defaults to neutral (optional)

Returns

any

createdatabase

creates a new database

Allowed number of parameters: 1–2

Parameters

  • schema (string): name of the new database
  • ignoreexists (bool): if true, return false instead of throwing an error (optional)

Returns

bool

dropdatabase

drops a database

Allowed number of parameters: 1–2

Parameters

  • schema (string): name of the database
  • ifexists (bool): if true, don't throw an error if it doesn't exist (optional)

Returns

bool

checktablemaintenance

checks whether a user-initiated maintenance operation is allowed for a table

Allowed number of parameters: 3–3

Parameters

  • schema (string):
  • table (string):
  • operation (string):

Returns

bool

maintenance_capabilities

returns the server-side maintenance capabilities for a database or table

Allowed number of parameters: 1–2

Parameters

  • schema (string):
  • table (string): (optional)

Returns

list

createtable

creates a table, runs its oninit option and registered after-create-table lifecycle triggers synchronously, and returns only after initialization completes; concurrent if-not-exists callers wait for that same completion

Allowed number of parameters: 4–5

Parameters

  • schema (string): name of the existing database that will contain the table
  • table (string): name of the table to create
  • cols (list): column and constraint definitions: ("column" name type dimensions typeparams), ("unique" name columns), or ("foreign" name local_columns referenced_table referenced_columns update_mode delete_mode). dimensions is a list of integer type dimensions. typeparams is an alternating key/value list supporting primary (bool), unique (bool), auto_increment (bool), null (bool), default (any), default_expression (string), update (expression), comment (string), collate (string), temp (bool), filtercols (string list), filter (function), sortcols (string list), sortdirs (bool list), partitioncount (integer), mapcols (string list), mapfn (function), reducefn (function), and reduceinit (any). Column lists are string lists; foreign-key modes are restrict, cascade, or set null
  • options (list): alternating key/value list; supported keys are engine (safe, logged, sloppy, memory, or cache), collation (string), charset (string), comment (string), auto_increment (non-negative integer), and oninit (closed zero-argument function run synchronously once per data generation; concurrent if-not-exists callers wait for it, and memory/cache tables persist the callback so the first idempotent createtable after restart repopulates their empty data)
  • ifnotexists (bool): when true, return false instead of failing if the table exists; if another caller is still creating it, wait for that caller's after-create-table initialization before returning false (optional)

Returns

bool

createcolumn

creates a new column in table

Allowed number of parameters: 5–7

Parameters

  • table (table):
  • colname (string): name of the new column
  • type (string): name of the basetype
  • dimensions (list): dimensions of the type (e.g. for decimal)
  • options (list): assoc list: primary, unique, auto_increment, null, comment, default, default_expression, collate; ORC: sortcols, sortdirs, partitioncount, mapcols, mapfn, reducefn, reduceinit
  • computorCols (list): list of columns that is passed into params of computor (optional)
  • computor (func(columns:any...) -> any): lambda expression that can take other column values and computes the value of that column (optional)

Returns

bool

createkey

creates a new key on a table

Allowed number of parameters: 4–4

Parameters

  • table (table):
  • keyname (string): name of the new key
  • unique (bool): whether the key is unique
  • columns (list): list of columns to include

Returns

bool

createforeignkey

creates a new foreign key on a table

Allowed number of parameters: 7–7

Parameters

  • table1 (table):
  • keyname (string): name of the new key
  • columns1 (list): list of columns to include
  • table2 (table):
  • columns2 (list): list of columns to include
  • updatemode (string): restrict|cascade|set null
  • deletemode (string): restrict|cascade|set null

Returns

bool

shardcolumn

tells us how it would partition a column according to their values. Returns a list of pivot elements.

Allowed number of parameters: 2–3

Parameters

  • table (table):
  • colname (string): name of the column
  • numpartitions (number): number of partitions; optional. leave 0 if you want to detect the partiton number automatically or copy the partition schema of the table (optional)

Returns

list

partitiontable

suggests a partition scheme for a table. If the table has no partition scheme yet, it will immediately apply that scheme and return true. If the table already has a partition scheme, it will alter the partitioning score such that the partitioning scheme is considered in the next repartitioning and return false.

Allowed number of parameters: 2–2

Parameters

  • table (table):
  • columns (list): associative list of string -> list representing column name -> pivots. You can compute pivots by (shardcolumn ...)

Returns

bool

altertable

alters a table

Allowed number of parameters: 3–3

Parameters

  • table (table):
  • operation (string): one of owner|drop|engine|collation|auto_increment
  • parameter (any): name of the column to drop or value of the parameter

Returns

bool

altercolumn

alters a column

Allowed number of parameters: 4–4

Parameters

  • table (table):
  • column (string): name of the column
  • operation (string): one of drop|type|collation|auto_increment|comment
  • parameter (any): name of the column to drop or value of the parameter

Returns

bool

droptable

removes a table

Allowed number of parameters: 2–3

Parameters

  • schema (string):
  • table (string):
  • ifexists (bool): if true, don't throw an error if it already exists (optional)

Returns

bool

dropcolumn

drops a column from a table

Allowed number of parameters: 2–2

Parameters

  • table (table):
  • column (string): name of the column to drop

Returns

bool

migratedropcolumn

drops a legacy system column during startup migration

Allowed number of parameters: 2–2

Parameters

  • table (table):
  • column (string): legacy column name

Returns

bool

invalidatecolumn

marks all values of a computed column as stale

Allowed number of parameters: 2–2

Parameters

  • table (table):
  • column (string): name of the computed column

Returns

bool

invalidateorc

invalidates ORC column rows from a sort key onwards via validMask scan

Allowed number of parameters: 3–3

Parameters

  • table (table):
  • column (string): name of the ORC column
  • sortkeys (list): composite sort key values from which to invalidate

Returns

bool

register_keytable_cleanup

registers triggers on a base table to maintain keytable entries (insert/delete group keys)

Allowed number of parameters: 4–4

Parameters

  • base_table (table):
  • kt_table (table):
  • tblvar (string): table alias used in scan column prefixes
  • key_pairs (list): list of (base_col kt_col) pairs

Returns

bool

initialize_cache_table

registers maintenance, locks source tables for a consistent snapshot, and runs a canonical planner-cache initializer exactly once

Allowed number of parameters: 5–6

Parameters

  • transaction (any): explicit transaction context carrying query-session ownership
  • table (table):
  • source_tables (list):
  • register_maintenance (func):
  • initializer (func):
  • finalizer (func): optional zero-argument finalizer run under the same source-table locks after initialization (optional)

Returns

bool

touch_keytable

extends the lease on a keytable so CacheManager defers eviction

Allowed number of parameters: 1–1

Parameters

  • table (table):

Returns

bool

locktables

acquires WRITE or READ user-level locks on a list of tables (LOCK TABLES); implicitly releases any previously held locks

Allowed number of parameters: 1–1

Parameters

  • locks (list): flat list of schema, table, write? triples

Returns

bool

unlocktables

releases all user-level table locks held by this session

Allowed number of parameters: 0–0

Parameters

This function has no parameters.

Returns

bool

get_fk_target

returns (ref_table ref_column) if a single-column FK exists for the given column, nil otherwise

Allowed number of parameters: 2–2

Parameters

  • table (table):
  • column (string): column name

Returns

any

renametable

renames a table

Allowed number of parameters: 3–3

Parameters

  • schema (string): name of the database
  • oldname (string): current name of the table
  • newname (string): new name of the table

Returns

bool

insert

inserts a new dataset into table and returns the number of successful items

Allowed number of parameters: 3–7

Parameters

  • table (table):
  • columns (list): list of column names, e.g. '("ID", "value")
  • datasets (list): list of list of column values, e.g. '('(1 10) '(2 15))
  • onCollisionCols (list): list of columns of the old dataset that have to be passed to onCollision. Can also request $update, $set:<computed-column>, or NEW.<insert-column>. (optional)
  • onCollision (func): function called for each collision. Its positional parameters are the values requested by onCollisionCols, in the same order. If omitted, collisions raise an error. (optional)
  • mergeNull (bool): if true, it will handle NULL values as equal according to SQL 2003's definition of DISTINCT (https://en.wikipedia.org/wiki/Null_(SQL)#When_two_nulls_are_equal:_grouping,_sorting,_and_some_set_operations) (optional)
  • onInsertid (func(id:number) -> any): (optional) callback (id)->any; called once with the first auto_increment id assigned for this INSERT (optional)

Returns

number

stat

return system statistics as assoc: mem_available, mem_total, process_memory, shard_memory, shard_budget, persisted_memory, persisted_budget, cache_entry_count, cache_entry_size. (stat schema) and (stat schema tbl) return a string with detailed memory usage.

Allowed number of parameters: 0–2

Parameters

  • schema (string): (optional) database name for detailed string output (optional)
  • table (string): (optional) table name for detailed string output (optional)

Returns

any

totalmem

Returns total physical memory in bytes (from /proc/meminfo)

Allowed number of parameters: 0–0

Parameters

This function has no parameters.

Returns

number

resolve_column_name

resolve a physical column name from immutable table metadata

Allowed number of parameters: 4–4

Parameters

  • schema (string): database name
  • table (string): table name
  • column (string): column name
  • ignorecase (bool): whether identifier case is ignored

Returns

string|nil

show

show databases/tables/columns/shards

(show) lists database names (show schema) lists table names (show table_handle) lists the memoized column defs (show table_handle true) returns table metadata (show table_handle "statistics") returns index statistics (show schema true) lists tables with full info: [{name,engine,row_count,size_bytes,collation,comment},...] (show schema tbl) lists column defs (show schema tbl true) returns assoc {columns,meta,shards} (show schema tbl N) returns shard N overview assoc {shard,state,main_count,delta,deletions,size_bytes} (show schema tbl N true) returns shard N full assoc adding columns and indexes (show schema tbl "statistics") returns index statistics (used by INFORMATION_SCHEMA)

Allowed number of parameters: 0–4

Parameters

  • schema_or_table (string|table|recset): (optional) database name or resolved table/recset handle (optional)
  • table_or_property (string|bool): (optional) table name, true for full info, or "statistics" for a handle (optional)
  • property (int|bool|string): (optional) shard index (int), true for full table info, or "statistics" (optional)
  • full (bool): (optional) true to include columns and indexes in shard detail (optional)

Returns

any

show_triggers

show triggers for a given table

Allowed number of parameters: 1–2

Parameters

  • schema (string): database name
  • table (string): (optional) table name, if omitted shows all triggers in schema (optional)

Returns

any

rebuild

rebuilds main storages and returns the amount of time it took; with a table handle, rebuilds only that table

Allowed number of parameters: 0–3

Parameters

  • table_or_all (bool|table): table handle for a table-local rebuild; otherwise whether to rebuild unchanged shards globally (default: false) (optional)
  • all_or_repartition (bool): with a table: whether to rebuild unchanged shards; globally: whether to repartition (default: true) (optional)
  • repartition (bool): with a table handle, whether to repartition that table (default: true) (optional)

Returns

string

loadCSV

loads a CSV stream into a table and returns the amount of time it took. The first line of the file must be the headlines. The headlines must match the table's columns exactly.

Allowed number of parameters: 3–5

Parameters

  • schema (string): name of the database
  • table (string): name of the table
  • stream (stream): CSV file, load with: (stream filename)
  • delimiter (string): (optional) delimiter defaults to ";" (optional)
  • firstline (bool): (optional) if the first line contains the column names (otherwise, the tables column order is used) (optional)

Returns

string

loadJSON

loads a .jsonl file from stream into a database and returns the amount of time it took. JSONL is a linebreak separated file of JSON objects. Each JSON object is one dataset in the database. Before you add rows, you must declare the table in a line '#table <tablename>'. All other lines starting with # are comments. Columns are created dynamically as soon as they occur in a json object.

Allowed number of parameters: 2–2

Parameters

  • schema (string): name of the database where you want to put the tables in
  • stream (stream): stream of the .jsonl file, read with: (stream filename)

Returns

string

settings

reads or writes a global settings value. This modifies your data/settings.json.

Allowed number of parameters: 0–2

Parameters

  • key (string): name of the key to set or get (for reference, rts) (optional)
  • value (any): new value of that setting (optional)

Returns

any

createcreatetabletrigger

registers a lifecycle trigger that fires synchronously after a future createtable for the given schema/table succeeds

Allowed number of parameters: 6–6

Parameters

  • schema (string): name of the database
  • table (string): name of the table to watch for creation
  • name (string): name of the trigger
  • source_sql (string): original SQL body text (for diagnostics)
  • body (any): trigger body (Scheme procedure or deferred trigger expression)
  • visible (bool): true = user trigger, false = internal trigger

Returns

bool

dropcreatetabletrigger

removes a registered create-table lifecycle trigger

Allowed number of parameters: 4–4

Parameters

  • schema (string): name of the database
  • table (string): name of the table watched for creation
  • name (string): name of the trigger
  • ifexists (bool): don't throw error if trigger doesn't exist

Returns

bool

createtrigger

creates a new trigger on a table

Allowed number of parameters: 6–6

Parameters

  • table (table):
  • name (string): name of the trigger
  • timing (string): one of: before_insert, after_insert, before_update, after_update, before_delete, after_delete
  • source_sql (string): original SQL body text (for SHOW TRIGGERS)
  • body (any): trigger body (parsed Scheme expression)
  • visible (bool): true = user trigger (shown in SHOW TRIGGERS), false = internal trigger (hidden)

Returns

bool

droptrigger

removes a trigger from a table

Allowed number of parameters: 3–3

Parameters

  • schema (string): name of the database
  • name (string): name of the trigger
  • ifexists (bool): don't throw error if trigger doesn't exist

Returns

bool

mysql_import

imports schema+data from a MySQL server into MemCP

Allowed number of parameters: 4–8

Parameters

  • host (string|nil): MySQL host (nil => 127.0.0.1)
  • port (int|nil): MySQL port (nil => 3306)
  • username (string): MySQL username
  • password (string): MySQL password
  • sourcedb (string|nil): source database (omit/nil => all non-system dbs) (optional)
  • targetdb (string|nil): target database (omit/nil => sourcedb) (optional)
  • sourcetable (string|nil): source table (omit/nil => all tables in sourcedb) (optional)
  • targettable (string|nil): target table (omit/nil => sourcetable) (optional)

Returns

bool

psql_import

imports schema+data from a PostgreSQL server into MemCP

Allowed number of parameters: 4–9

Parameters

  • host (string|nil): PostgreSQL host (nil => 127.0.0.1)
  • port (int|nil): PostgreSQL port (nil => 5432)
  • username (string): PostgreSQL username
  • password (string): PostgreSQL password
  • sourcedb (string|nil): source database (omit/nil => all non-system dbs) (optional)
  • sourceschema (string|nil): source schema (omit/nil => all non-system schemas in sourcedb) (optional)
  • targetdb (string|nil): target database (omit/nil => sourcedb) (optional)
  • sourcetable (string|nil): source table (omit/nil => all tables in sourceschema) (optional)
  • targettable (string|nil): target table (omit/nil => sourcetable) (optional)

Returns

bool

Retrieved from "https://www.memcp.org/index.php?title=Storage&oldid=286"