Questions on litesync

jhay006

I just have a question regarding on litesync ,

Does writing on database on primary nodes is queued ? or it writes multiple data at the same time ?
Does litesync have conflict resolution ?
Do you have plan to support indexedDB ? Qt can execute QSQlite in webassembly but in temporary , maybe we can use this to have sqlite for webassembly .

Bernardo

I think that the best word is serialized. One writer will lock the db and others will wait until the first finishes
Conflicts on insertions are avoided with the different row id numbering. There is no conflict resolution in place. The commands are processed in the order they reach the primary node(s)
Check the video for details: https://youtu.be/ed9HOUpckjg
No and No

jhay006

how about litesync via QT for android / ios , ?
i'm trying to use it on Qt for android
i downloaded the free binaries then download the .aar file from flutter instruction http://litesync.io/download/litesync.aar

but got an error , it can't connect db
V/litesync: => (sqlite3_log) 21 - misuse at line 174978 of [df5c253c0b]

Bernardo

Are you using an URI to open the database?
Are you using a proper path available to the mobile app?
Avoid using PRAGMA commands after the db is open.
You can share the code used to open and access the db. Only this part, not the whole app.

It is interesting that you were able to load the LiteSync library on your app. I don't know how it was done with Qt. Did you just renamed the library or used another trick? Because normally it would just load the native SQLite library instead. For Qt it would require something equivalent to System.loadLibrary("litesync"); used in Java / Android Studio.

jhay006

Bernardo

I made it working now, on android using that .aar but i changed to full c code , i noticed that the instruction or example is a bit outdated for C language ,

i didnt rename the library , i just follow the instruction for android studio , i downloaded the .aar from flutter instruction , then i just use the code from android studio to create a c++ wrapper

anyways i noticed when the database was auto created from server for example
file:app.db?node=primary&bind=tcp://0.0.0.0:1234

it was created successfully but the db is corrupted when i tried to open the db using sqlite3 then i run .tables the response is "the file is not a database"

i just need to recreate the app.db file using sqlite3 command , then create the table there then run my node js script , then it works .

for the autogenerated db from the wrapper without recreating , i can just stay the corrupted file but i needed to create 1 secondary database and attach it to the server , then i run the create table script from the secondary database and create another secondary node and attach it with the same server, the 2 secondary database are readable (not corrupted) only the server/primary is corrupted

a 1:1 is not recommended for litesync if the primary is autocreated using the wrapper because of the bugs
i simulated a 1:1 , 1 primary and 1 secondary ,

the primary is autocreated (tested in better sqlite3 node js , and litesync for python) but due to bugs it is corrupted

then a secondary connected to primary

regardless of primary being corrupted , i run several insert scripts assuming it that it can insert data and send it to the secondary . but it doesn't work . it doesn't sync but it was successfully run the insert script .

final thought :
to make a p2p works i need another secondary to sync so 1:2 ratio is needed for litesync

Bernardo

Why did you do all this? You could simply download a sample project for Android and start from there.

The database is not corrupted. It can only be opened by the sqlite3 that comes with LiteSync. It is not possible to open the database with a native unmodified SQLite library (or shell or app) because the format is different. You must use the sqlite3 shell that comes with LiteSync to see the content.

You can create the tables on the primary node, after the connection is opened and checking that the db is ready the app can use CREATE TABLE IF NOT EXISTS commands for that purpose, or check which tables do not exist and issue a CREATE TABLE command. Or it can be made on the secondary nodes instead, it does not matter. The table will be replicated.

I cannot understand most of what you wrote. Are you using more than one application opening the same local database? This is not supported. And this "ratio" you imagined is fake. LiteSync has many functional tests and all of them are passing.

jhay006

okay , just nvm my last comment,

one last thing do you have plan to add websocket support instead of TCP ?

I try to create a websocket tunneling to tcp , but at some point , after the first handshake it stop from receiving any data .

i just give up, hehehe i am planning to host litesync to our server. but our server allows only port 80/443 meaning only HTTP/S or WS/S are allowed no other ports .

so to make it work , i just need to tunnel the websocket to litesync tcp .

Bernardo

WebSocket is not supported. No plans to work on it.

I don't recommend these tricks like tunneling. There is no guarantee that they will work.

Check if it is possible to run LiteSync on port 80. If the server only has limit on port numbers but does not check protocol (content) then it may work.

But the best approach is to ask for the admin to open a port on the firewall dedicated to LiteSync, to avoid common crawler traffic on port 80 and 443.

If it is not possible, make sure to enable encryption on LiteSync (both primary and secondary nodes) to avoid problems. This is recommended in any way, but becomes a requirement if using port 80/443.

https://litesync.io/en/sqlite-encryption.html

jhay006

Bernardo
got it ! thanks 🙂

anyways i got it working on websocket yesterday,

btw , the one i mention using c, i don't know if it is a bit outdated or it is just different when in c++

#include <sqlite3.h>

char *uri = "file:app.db?node=secondary&connect=tcp://server:port";

int main() {
  sqlite3 *db;
  /* open the database */
  sqlite3_open(uri, &db);
  /* check if the db is ready */
  while(1){
    char *json_str = sqlite3_query_value_str(db, "PRAGMA sync_status");     
    BOOL db_is_ready = strstr(json_str, "\"db_is_ready\": true") > 0;
    sqlite3_free(json_str);
    if (db_is_ready) break;
    sleep_ms(250);
  }
  /* access the database */
  start_access(db);
}

The code inside while loop , i changed it to sqlite3_exec,

Bernardo

I updated the example code to include the implementation of sqlite3_query_value_str function

Thank you for informing!

Bernardo

There is now example for C++ using sqlite_modern_cpp as well

jhay006

Bernardo thank you 🙂

btw , is there any other ports beside the one we used for bind ?

file:app.db?bind=tcp://server:port = example , i used port 1234 here , then is there a hidden port tcp/udp that used for notification ?

this is related on the websocket connection , on localhost , it is fully working but when i add it to an upstream server after the handshake, it doesn't sync. heheheh

Bernardo

There is a UDP one but it is disabled by default, it may not affect.
But you really need websocket? Just bind to port 80 on the primary node:

file:app.db?node=primary&bind=tcp://0.0.0.0:80

Using encryption:

file:app.db?node=primary&bind=tcp://0.0.0.0:80&cipher=xrc4&key=testing

jhay006

Bernardo i see , will try this thanks

jhay006

btw ,
regarding on your below respone

Conflicts on insertions are avoided with the different row id numbering. There is no conflict resolution in place. The commands are processed in the order they reach the primary node(s)

does it mean , if client 1 and client 2 add a record on a table exactly same time but with different ID's
it will save both records ?

what about , they edit the same record example row ID number 1 with name and age , both changed it at the exact same time

client 1 changed age to 22
client 2 changed age to 23
? How will litesync treat it ?

does the last write wins ? i mean both changes successfully saved and sync at the same exact time , but i believe since its sqlite , the changes will be queued and whichever the last write will win ?

Bernardo

Yes.

Clients 1 and 2 write to the local database first, only then these changes are sent to the primary node(s) for synchronization.

If they are creating new records, then these records will have different row ids, and there is no conflict.

If they are modifying the same record using an UPDATE command, the last one to reach the primary nodes will overwrite the data send by the first one. And both nodes will end up having the content of the last node that sent the data to the primary node. So it does not matter the time in which they wrote to the record, what matters it the order in which the changes reach the primary node(s).

jhay006

okay thanks for that 🙂 ,

The last question is , how can i contribute ? since source code is only available for commercial license ,
but i want to contribute for this project , is there something i can do to contribute ?

Thanks

Bernardo

The wrappers for different programming languages are open source

You can check them here:

https://github.com/litesync
https://gitlab.com/litesync

jhay006

Bernardo

is there any update documentation for compiling the source code ? all of the wrappers included on the first line says
"First follow the instructions to compile and install LiteSync from the source code "

but this link is not found
https://gitlab.com/litesync/litesync-release

Bernardo

Only for those with a commercial license

It also says "or use the pre-compiled binaries"