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

Leave a Reply

Your email address will not be published. Required fields are marked *