Using SQL helpers

Use the SQL helpers library to make it easier write SQL in Solidity.

---

When creating and writing to tables, Solidity can be a bit challenging to work with when it comes to forming SQL statements. The SQLHelpers library provides a series of helper methods to ease writing create table statements, insert, updates, and deletes, and it also has a couple of common helpers. This page describes these methods, their function signatures, and links to the actual implementation.

toNameFromId

Pass a table prefix and tableId, which returns the formatted table name as {prefix}_{chainId}_{tableId} (e.g., healthbot_1_1).

function toNameFromId(
    string memory prefix,
    uint256 tableId
) public view returns (string memory);

toCreateFromSchema

Form a CREATE TABLE statement by supplying the table schema and custom prefix, which then must be passed to the registry contract's create method.

function toCreateFromSchema(
    string memory schema,
    string memory prefix
) public view returns (string memory);

toInsert

Generate an INSERT statement with the specified columns and values for a specific table, which then can be passed to the registry's mutate method to mutate the table itself.

function toInsert(
    string memory prefix,
    uint256 tableId,
    string memory columns,
    string memory values
) public view returns (string memory);

toBatchInsert

Rather than a single set of values, you can also batch insert data. That is, you must define the set of columns to alter but then pass a string of comma-separated values.

function toBatchInsert(
    string memory prefix,
    uint256 tableId,
    string memory columns,
    string[] memory values
) public view returns (string memory);

toUpdate

Form an UPDATE statement where the setters establishes what values to SET existing data to, and filers allows for additional checks to add to the update. This response can then be passed to the mutate method.

function toUpdate(
    string memory prefix,
    uint256 tableId,
    string memory setters,
    string memory filters
) public view returns (string memory);

toDelete

Define what and how table data should be deleted with filters applied to the DELETE FROM statement—the returned string should then be passed to mutate for execution.

function toDelete(
    string memory prefix,
    uint256 tableId,
    string memory filters
) public view returns (string memory);

toQuote

All TEXT types must be inserted into a table wrapped in single quotes (e.g., 'my string'). In Solidity, this can bea bit cumbersome with doing the string concatenation on your own, so this helper will wrap the input string and return it with single quotes around it.

function quote(string memory input) public pure returns (string memory);