search

hexresearch / hschain

Other
4 stars 0 forks source link

SQL interface prototype #494

Open thesz opened 4 years ago

thesz commented 4 years ago

It would be good to have something that is recognizable as SQL by most day-to-day developers for developing blockchain solutions. Having minimal ODBC driver support would help immensely - just by issuing standard connection/command requests we can provide support for blockchain interoperation for different languages simultaneously.

This issue will provide a plan to get there, a documentation of limitations we impose and why.

SQLite ODBC driver for ODBC/unixODBC: https://github.com/Distrotech/sqliteodbc

thesz commented 4 years ago

The case for multiple "currencies" is postponed.

thesz commented 4 years ago

There are more than 200 functions in ODBC driver. We need to cut that off significantly.

thesz commented 4 years ago

We need a way to specify database schema with actual tables and views over them. Views provide a way to control access and add additional constraints on data (withdraw shell not result in negative amount)..

thesz commented 4 years ago

Development branch: https://github.com/hexresearch/hschain/tree/sql-interface-development

Shimuuar commented 4 years ago

I think that arbitrary precision numerics and arbitrary length strings are obvious vector for DoS attacks: make nodes perform calculations with long fractions, waste node storage space by creating long strings...

thesz commented 4 years ago

https://docs.google.com/document/d/e/2PACX-1vSF78DXfHRdhJ0YP9Mxxs77p9w7Td843C0sGH4_FGO2b0r47kmUwD2nbmTQUqRKSmx8Ogt2CcdllfLR/pub

Proposal document

thesz commented 4 years ago

I think that arbitrary precision numerics and arbitrary length strings are obvious vector for DoS attacks: make nodes perform calculations with long fractions, waste node storage space by creating long strings...

The idea is to have a list of curated requests. We may design them so that DoS as you suggest in the comment is, at least, problematic if even possible.

thesz commented 4 years ago

Link about unixODBC config files: http://www.unixodbc.org/odbcinst.html

Relevant quote:

[PostgreSQL] Description = Test to Postgres Driver = /usr/local/lib/libodbcpsql.so Trace = Yes TraceFile = sql.log

That means we can specify path to driver we custom-built for our nefarious purposes.

thesz commented 4 years ago 
$ python
Python 2.7.15+ (default, Oct  7 2019, 17:39:04) 
[GCC 7.4.0] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc
>>> cnxn = pyodbc.connect("Driver=SQLite3 ODBC Driver;Database=sqlite.db")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'SQLite3 ODBC Driver' : file not found (0) (SQLDriverConnect)")
>>> cnxn = pyodbc.connect("Driver=/home/sz/usr/lib/libsqlite3odbc.so;Database=sqlite.db")
>>> cursor = cnxn.cursor()
>>> cursor.execute("CREATE TABLE bubu(i integer")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
pyodbc.Error: ('HY000', '[HY000] [SQLite]incomplete input (1) (1) (SQLExecDirectW)')
>>> cursor.execute("CREATE TABLE bubu(i integer)")
<pyodbc.Cursor object at 0x7f1d0189ef30>
>>> cnxn.commit()
>>> cursor.execute("INSERT INTO bubu (i) VALUES (1234)")
<pyodbc.Cursor object at 0x7f1d0189ef30>
>>> cnxn.commit()
>>> cursor.execute("SELECT * FROM bubu")
<pyodbc.Cursor object at 0x7f1d0189ef30>
>>> cursor.fetchall()
[(1234, )]

It works, it seems.

thesz commented 4 years ago

Next week (up to Nov 1st):

thesz commented 4 years ago

The order must be: state change code (we have to emulate some SQL here, luckily we have SQL parser ready to use), consensus (it is tedious) and then handle with python code test.

thesz commented 4 years ago

http://hackage.haskell.org/package/simple-sql-parser - SQL parser for Haskell.

thesz commented 4 years ago

I think I have to add guard selects for some requests.

For example, only superuser can add users. But addition of users is an INSERT operation which does not have WHERE guards.

Thus, before issuing an INSERT we should issue a SELECT with something like 'SELECT COUNT(*) FROM rights WHERE user_id = :user_id AND right = 'ADD_USER';`

If the resulting value (scalar one, BTW) of aforementioned SELECT is 0, we will not proceed to INSERT.

I postpone it as an idea to implement. Right now I am deep in debugging in four or five languages.

thesz commented 4 years ago

https://sqlite.org/lang_savepoint.html - how to emulate nested transactions to check validity of user transactions.

thesz commented 4 years ago

https://stackoverflow.com/questions/11938401/difference-between-text-and-string-datatype-in-sqlite - about types used in SQLite.

May help in debugging the selects.

thesz commented 4 years ago

Transaction gets posted into mockup blockchain, wire transfer works.

Documenting the thing.

thesz commented 4 years ago

https://github.com/orlp/ed25519/tree/master/src - a portable library that implements ED25519.

Have to check compatibility with ours (most probably it is compatible - see reference to SUPERCOP ref10 in the readme at the link above).

thesz commented 4 years ago

https://github.com/hexresearch/hschain/tree/sql-consensus-node-development - new developments are there.

thesz commented 4 years ago

Current state of affairs:

Done: