Limitations on SQL queries when using OurSQL to replicate your database

OurSQL has some limitation on SQL queries possible to use.

This is caused by requirements to control database integrity after possible update conflicts.

  1. No any limitations on READ access. It is possible to execute ANY SELECT query. In fact, such requests are done in your local copy of a DB.
  2. Only CREATE TABLE and DROP TABLE are allowed from all possible table management queries. No ALTER TABLE s allowed, no indexes operation (but this can be done locally by a DApp ).
  3. No any sort of stored procedures. At this time OurSQL can not replicate changes caused by stored procedures. It is possible to have stored procedure for READ access. But creation of this procedure can not be replicated.
  4. INSERT can do only single row adding, no queries like INSERT INTO mytable VALUES (‘1a’,’1b’),(‘2a’,’2b’),(‘3a’,’3b’)
  5. INSERT REQUIRES colmn names definition. IT works only in formats like INSERT INTO mytable (column1, column2) VALUES (‘1′,’2′),   or will work in format like INSERT INTO mytable SET column1=’1′, column2=’2’.
  6. Every table must have a PRIMARY KEY . It must be auto_increment OR some unique string but generated outside and present in an INSERT query (not generated with some trigger or so)
  7. UPDATE or DELETE can have only primary key single value in condition . Only single row can be affected by UPDATE or DELETE operations. No any bulk updates.
    For example, “UPDATE mytable SET col2=’x’ WHERE tk = 2” is fine query, but “UPDATE mytable SET col2=’x’ WHERE tk > 5” is bad and not supported.

If some query is not supported, OurSQL will return an error on attempt to execute it.

Additionally, when using OurSQL you have to remember, it is distributed ledger system. Total size of a DB can be be big. There is a blockchain, it grows all time. Every SQL update has a cost , space will be needed for it.

Try to follow golden rule – update a DB as rare as possible. Keep minimum data in a DB and it will live long

OurSQL Demo Database

If you are interested to see how OurSQL works, you can do it right now if you have some basic experience with docker usage. (https://www.docker.com/)

We created demo database called “OurSQL Demo DB”.

On of DB nodes is 109.251.62.4:8765 . For now network is not big, if there are more nodes later we will list them.

Run docker container and connect to mysql proxy.

docker run --name oursql -p 8765:8765 -p 8766:8766 -d -it oursql/oursql-server importandstart -port 8765 -nodeaddress 109.251.62.4:8765

This connects to existent decentralized database and starts new node. The node listens on the port 8765 and DB proxy server listens on the port 8766.

Connect to your local copy of this database. Database bane is BC, user credentials are blockchain/blockchain .

mysql -h 127.0.0.1 -P 8766 -u blockchain -pblockchain BC

You can see list of current DB tables. Ignore tables with the “bc_” prefix. This are special tables where blockchain info is stored. Work only with tables without this prefix.

Try to insert something in tables. Most probably, you will get an error like “No enough funds for requested transaction”.

In this database we set all SQL updates to be “paid” to prevent unlimited data inserts or so.

When you just started your node, you will get new created wallet which doesn’t have any funds (in internal cryptocurrency used in this database).

To continue your tests, you can ask us to send some funds for you. We are not stingy 🙂 . We will send you enough coins to do your tests of this demo database.

Firstly, you need to know your wallet address. Execute the command. There will be only 1 address if you didn’t create more yet.

docker exec -it oursql2 /node listaddresses

Email your address to oursql.project@gmail.com or to roman@gelembjuk.com and notice you would like to get funds to test the DB. After you get your funds, you will be able to execute SQL updates on this DB.

For example, add some message to the chat table.

mysql -h 127.0.0.1 -P 8766 -u blockchain -pblockchain BC

mysql> INSERT INTO chat SET username="visitor", message="Hi,All, i am here with you", addtime='2018-11-28 22:00';
Query OK, 1 row affected (0,12 sec)

After short period of time, you update will be visible on all other nodes.