Crazy Idea: New opcodes for relational DB like data reads and writes

What

  • add new opcodes that map to create, read, update, and delete statements in SQL
  • needs only to be a “sensible” subset of the SQL99 standard, for example no need to support JOIN
  • should have the appropriate checks/ limits in place, e.g. LIMIT should always be specified, and have a hard cap

Why

  • without this, what you can build in a smart contract is severely restricted, due to the severe restrictions on how data may be accessed
  • most attempts model relational DB like operations using mapping and (ab)use of event logs with indices
  • reduce the necessity to build “hybrid” DApps, in particular where a centralised DB is used to supplement the storage present on smart contracts
  • this may present some interesting implications during implementation, for example storage cost reduction arising from segregation of executing code from data storage

Thamks!

Opcodes are a limited resources, and any new special one could be collide with another one in Ethereum, in the future. Maybe we could use precompiled smart contract

good point- precompiled smart contracts sound like a good idea.

I assume that precompiled in this case would mean that the “relational DB” code executed would be built into rskj itself.

also think more about this, what would the choice of DB be. something like sqlite3 would be more than enough to meet the needs, but it would need to be recompiled to add some additional logic to swap timestamps with something similar to an atomic clock, but based on block numbers (and perhaps transaction # within a block).

Well, as a “baby step” I would prefer don’t have any additional software, like a real sql engine. I hope I could write a proof of concept based only in blockchain storage,

Take into account that having a sql engine and storage (like sqllite), implies we should resolve the “fork problem”. It’s not only the state at block 12, but we should retrieve the state at block 12 hash H1, vs block 12 hash H2 or anything else.

Having the data in storage, we could solve that problem. Modified by smart contract, then iterate on performance (precompiled, in memory cache, etc)

Only after all that, I would add a query language, that could be preprocessed offchain, maybe, at that point

Thinking aloud :slight_smile:

Keen to take the baby steps approach on this as well.

Take into account that having a sql engine and storage (like sqllite), implies we should resolve the “fork problem”.

Yeah, aware of this - if integrating a separate storage does eventuate, it will likely have other consensus implications too.

Modified by smart contract, then iterate on performance (precompiled, in memory cache, etc)

Hmm. The smart contract starting point may not be possible using solidity though - as mentioned in the OP the constraints/ limits of mapping (and event) make it tough to impossible. For example while looking up an entity by its PK easy to do; looking up multiple entities using other attributes (equivalent of SELECT ... WHERE ...) requires workarounds, and can end up infeasible due to exceeding gas costs. Which means we’ll need to consider writing bytecode directly.

Hi! I just published https://angeljavalopez.medium.com/database-like-tables-in-blockchain-d75e4df4b7b6 explaining my tentative alternative implementation in Solidity

1 Like

Stumbled across this - SQLite, but distributed: https://github.com/canonical/dqlite

… unlikely to help while we’re pursuing the contract-based storage approach,
but putting this here anyway as a bookmark.