Proof of SQL℠ tamperproof queries enable many new use-cases at the intersection of Blockchain and AI, however, each SQL operation requires a new ZK circuit, so the SQL syntax has some limitations today. This page is intended to help guide developers on writing tamperproof queries, demonstrate what is available now, what's coming soon, and how to work around certain limitations.

Let's Get Started!

To be able to validate your script, we'd ask that you generate a new file called Create_Tamperproof_Table.sh that you can commit to your branch of the SXT-Community/SXTAccreditation repo at the end of the activity.

Proof of SQL Syntax

Feature Availability

Below is Proof of SQL feature availability - what is currently available, what is coming soon. Each and every part of SQL syntax requires a custom ZK circuit to be built and optimized, keeping the Space and Time cryptography team busy! Understand there are limitations to Tamperproof queries today, but also that we expand the feature set weekly - check back often!

Currently Supported Syntax

Broadly, we support the following with more SQL features being added quickly:

• SELECT ... WHERE
• GROUP BY
• Comparison operations: =, >=, <=.
• Logical operations: AND, OR, NOT.
• Aggregations: SUM, COUNT
• Data Types: BIGINT, VARCHAR, DECIMAL75

For a detailed list of supported functions, see the SQL specification.

Roadmap

Proof of SQL is in active development. Here are some items that we are currently working on. We are happy to recieve feedback on additional features that the community requests.

• Expanded SQL support - in particular, multi-table queries (like JOIN) and subqueries
• Cluster scale proofs - this means faster proofs over larger tables!
• Solidity verifier - this would mean that verification can occur directly on chain.
• A novel commitment scheme - while we support a variety of commitment schemes, we are developing a commitment scheme specifically for database operations.

We are also currently undergoing robust security audits. Keep this in mind as you use this code.

📘

Check back frequently, this list will update often!

Tamperproof Table Requirements

Below walks through requirements when creating a tamperproof table. Tamperproof queries are only possible on tamperproof tables, as only tamperproof tables generate cryptographic commitments for all newly ingested data - a requirement for proof generation.

Creating a tamperproof table requires a few special settings, which are marked out in the example below:

CREATE TABLE MySchema.My_TpTable_v1 (
  MyPK_ID        bigint  not null  primary key
 ,some_textData  varchar not null  
 ,some_intData   bigint  not null  
 ,PROOF_ORDER    bigint  not null  /* <-- this is required */
)  
with "tamperproof=true, immutable=true, access_type=public_read, public_key=$RESOURCE_PUBLIC_KEY" /* <-- all required */

Tamperproof Requirements

• PROOF_ORDER - an ordered, unique identifier for the data
  • This exists for technical reasons, later versions of Proof of SQL will remove this requirement.
  • The order of this column must match the order of the data inserted, however the values need not be consecutive, only ordered.
  • The application performing the data insert will need to manage the correct ordering of values (i.e., there is no Auto-Number yet)
  • The column must be named PROOF_ORDER
  • It can optionally be the primary key for the table, but not required
• NOT NULL - any column you wish to use in proof generation cannot allow nulls
  • You can have nullable columns, however they cannot be used for proof generation (aka cannot be used in a tamperproof query)
• WITH Statement has two required entries:
  • tamperproof=true - table should allow ZK Proofs
  • immutable=true - table is append-only
  • public_key - is required for ALL tables, tamperproof or not
• Data Types have varying levels of restrictions
  • Check out this page for the latest mapping of data type / feature mapping

Create Your Own Tamperproof Table

Let's take the example above and create our own tamperproof table, similar to the "Create Your Own Table" activity, although but with fewer columns, as Proof of SQL doesn't support all column types yet.

CREATE_SQL=$(cat << EOM

  CREATE TABLE SXTTemp_TP.TestTP_<your UserID>_V1
  ( MyID              BIGINT
  , MyText            VARCHAR
  , PROOF_ORDER       BIGINT
  , primary key(MyID)
  )
with "tamperproof=true, immutable=true, access_type=public_read, public_key=$RESOURCE_PUBLIC_KEY" 

EOM
)

sxtcli sql \
  --url=$API_URL \
  --accessToken=$ACCESS_TOKEN \
  --sqlText=$CREATE_SQL \
  --biscuits=$ADMIN_BISCUIT

We can also insert new rows:

INSERT_SQL=$(cat << EOM

INSERT INTO SXTTemp_TP.TestTP_<your UserID>_V1
(MyID, MyText, PROOF_ORDER)  
VALUES  
  ( 1, 'Foo', 10)
, ( 2, 'Bar', 20)
, ( 3, 'Baz', 30) 
, ( 4, 'Qux', 40)
, ( 5, 'Regoob', 50)

EOM
)

sxtcli sql \
  --url=$API_URL \
  --accessToken=$ACCESS_TOKEN \
  --sqlText=$INSERT_SQL \
  --biscuits=$ADMIN_BISCUIT

That said, UPDATES and DELETES are completely disallowed, and will throw errors if attempted.

DELETE_SQL=$(cat << EOM

DELETE FROM SXTTemp_TP.TestTP_<your UserID>_V1
WHERE MyID = 1

EOM
)

sxtcli sql \
  --url=$API_URL \
  --accessToken=$ACCESS_TOKEN \
  --sqlText=$DELETE_SQL \
  --biscuits=$ADMIN_BISCUIT

sql failed
[400] Bad Request
Details: Invalid operation (DELETE) on immutable table SXTTEMP_TP.TESTTP_

Create Table Best Practices

Tamperproof tables are today immutable, meaning data can be added, but not updated nor deleted. It also means the tables themselves cannot be dropped (to prevent a table-recreation attack).

🚧

Plan ahead for the immutability of tamperproof tables.

If you create a tamperproof table, it cannot be dropped, and will consume schema.table name forever on the network! This keeps the data immutable, but also makes every data model mistake or column mis-type immutable as well, which can lead to table clutter inside schemas.

To help, here are a few best-practices to help reduce the number of unused tamperproof tables:

• Iterate on your data structure using non-tamperproof tables first, then drop/recreate as tamperproof table only as the last step. The process for calling tamperproof and non-tamperproof queries is nearly identical, so iterate on the mutable table, and create your tamperproof table only once.
• That said, time changes everything - version your tamperproof table names (MySchema.TPTable_v1, _v2, etc.), making it clear which is the "correct" version of the table.
• Isolate tamperproof tables to their own schema, so they are easily identifiable and don't clutter up your 'core' mutable data workspace (MySchema vs MySchema_TP).
• Create a view to query the current version of each tamperproof table, using a more business-friendly and non-versioned alias (table MySchema_TP.Table_v3 with view MySchema.Table).
  Note, tamperproof queries can only be run on the table directly, not a view - however, non-tamperproof queries will run on the view just fine.

All of these practices are optional, but worth considering when designing an intuitive, user-friendly data ecosystem for users.

Get Credit:

You'll get credit for this activity if you:

• Create a new tamperproof table named: SXTTemp_TP.TestTP_<your UserID>_V1
  If your UserID has special characters that aren't accepted, simply replace them with underscores ().
• Insert 5 rows into your new table
• Run a ZK-proven query against you new table
• Commit your work file to the SXT-Community/SXTAccreditation repo

How should I commit my branch changes?

When you're complete with the activity, simply commit your changes to your branch of work, then push to the SXT-Community/SXTAccreditation repo. Many IDEs have automated processes to manage this for you, but to do this manually by opening a temrinal / WSL window, and typing:

Line 1: Change to your work folder in your SXT-Community/SXTAccreditation directory Line 2: Make sure your branch is active Line 3: Add all changed files to your local stage Line 4: Commit your changes Line 5: Push your commited changes to github!

cd ./your/path/SXTAccreditation/<your_folder> git checkout <your_branch> git add . git commit -m "added my new working file" git push

If you get an error on line 5 saying, The current branch stephens_work has no upstream branch, this is because Github doesn't know about your branch yet. Simply run the command the git tool recommends - it will look something like:
git push --set-upstream origin <your_branch>

When you're done for the day, head over to SXT-Community/SXTAccreditation/Pulls page on Github, and (if you're logged in) you'll see a message like:

Click on the button above to create a Pull Request, give us some feedback in the description, and click "Create Pull Request". Done!