Share My Creation Erels [B4X] CloudKVS - synchronized key / value store - MySQL

Well, It's Erels... [B4X] CloudKVS - synchronized key / value store...
with the b4j server using a MySQL back-end.
This is a moderate programmer project. Not really for a new-bee.

Thanks Erel!!!!!
https://www.b4x.com/android/forum/threads/b4x-cloudkvs-synchronized-key-value-store.63536/

Not a lot of testing has been done but for my small app it is working fine.

Remember this is the SERVER app. It is b4j.
OK, a little more work to setup (Well a lot more) but if you want MySQL power? here you go.

So... Step by step

0. Make sure you have mysql-connector-java-5.1.39-bin.jar in your Additional libs folder.
1. Create a database called KeyStore on your MySQL server.
2. Set users, permissions, you know the drill. Open firewall ports if needed if you are on a VPS
3. Time for the table , indexes --- create them in your KeyStore database.

CREATE TABLE `data` (
  `user` varchar(254) NOT NULL,
  `key` varchar(254) NOT NULL,
  `value` blob,
  `id` int(11) DEFAULT NULL,
  `the_time` bigint(20) DEFAULT NULL,
  UNIQUE KEY `pri_index` (`user`,`key`),
  KEY `id_index` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

4. Load the attached project. Do not forget to change your database setting in the DB module.

Make sure you have a good database connection going or else not much will matter.

This should be compatible with all of Erel's b4x clients he posted as the only thing changed was in the DB module.

Well, have fun. As always
(c)Every living being. If you are breathing... Use and hack!!!

Source to use, abuse, edit, add and delete as you see fit.

Please post bug fixes (don't tell me you have a bug.. Fix it! or if you need help then post here!!!) And any ideas...

I hope soon to add MQTT to do pushes to the clients so they do not have to keep querying for new data.

Have fun!!!!

 

[schimanski]

Thanks a lot for that solution. This is my first experience with MySql, but I now set up an xampp-SQL-Server on my localhost to try your solution.

This is my init-sub. For testing i don't set a password.

Public Sub Init
    
    Dim user As String = "root"
    Dim PW As String =  ""
    Dim myDB As String = "keystore"
    Dim serverPort As String = "localhost:3306"
    
    sql.Initialize("com.mysql.jdbc.Driver",  _
             $"jdbc:mysql://${serverPort}/${myDB}?"$ & _
             $"user=${user}&password=${PW}"$)
    
    CreateDatabase
End Sub

Now i'm able to connect my CloudKVS-Server to the xampp-server, but it seems, that I did something wrong with creating the table, because I couldn't find all the parameter:

'--- Create a database named keystore
'--- create a table named data
' CREATE TABLE `data` (
' id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
' user VARCHAR(254) Not Null,
' `key` VARCHAR(254) Not Null,
' value BLOB,
' time INT(6), <===== must it be the_time???
' catalog_key VARCHAR(254) );

In my xampp-console it looks like:

Adding an item, I get the following message:

Task: additem, User: u1, Key: number, IP: 192.168.1.127
DELETE FROM data WHERE  `key` = "number" and `user` = "u1"
1518203989933
INSERT INTO data ( `user`, `key`, `value`, `id`,`the_time`)
            VALUES ("u1","number","[B@7c9fdc7c",1,1518203989933)
AddItem:  com.mysql.jdbc.MysqlDataTruncation: Data truncation: Out of range value for column 'the_time' at row 1

Getting an item, there is this exception:

Task: getuser_u1, User: u1, Key: -1, IP: 192.168.1.127
GetUserItems:  com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'key, value, id, the_time FROM data WHERE user = 'u1' AND id > -1' at line 1
Fehler in Zeile: 16 (Action)  <====     Dim bytes() As Byte = serializator.ConvertObjectToBytes(items)
java.lang.RuntimeException: Object should first be initialized (List).

And the B4J-Client says:

java.io.EOFException: Unexpected end of ZLIN input stream

I think, that the table is not created correctly.

Thanks for help.

 

[AscySoft]

Out of range value for column 'the_time' at row 1

it seems that the value inserted is "larger" that the db column type is supported, INT support a min value of -2147483648 and a max of 2147483647
See this https://dev.mysql.com/doc/refman/5.7/en/integer-types.html Try to change it to BIGINT to store a unix time value as integer.
There may be other errors as well

 

[schimanski]

You are right!

Like JacketBullet70 wrote is the_time an bigint(20). Now it is possible to write the data to the mysql.-cloudkvs without problems. The only thing, which doesn't work is reading out of the database.

Task: getuser_u1, User: u1, Key: -1, IP: 192.168.1.127
GetUserItems:  com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'key, value, id, the_time FROM data WHERE user = 'u1' AND id > -1' at line 1

id is always -1. The B4J-Client-code is

Private Sub HandleQueue
    If SendingJob = True Then
        Return
    End If
    Dim rs As ResultSet = sql.ExecQuery("SELECT qid, task, taskname FROM queue ORDER BY qid")
    If rs.NextRow Then
        Dim queue_id As Long = rs.GetLong("qid")
        Dim Job As HttpJob
        Job.Initialize("job", Me)
        Job.PostBytes(url,rs.GetBlob("task"))
        Job.Tag = CreateMap("queue_id": queue_id, "taskname": rs.GetString("taskname"))
        SendingJob = True
    End If
   
    rs.Close
End Sub

But even if i set the id manually to a number, which is available in the database, the exception is the same.

My table looks like that. I hope, it is correct:

Edit: I checked it with the CloudKVS-Client for b4x. The id is an integer, but the same exception.

 

[OliverA]

GetUserItems: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'key, value, id, the_time FROM data WHERE user = 'u1' AND id > -1' at line 1

Where is the SELECT? Looks like that may have been deleted out part of the SQL statement. Check the SQL statement in the GetUserItems method in the DB Class of the Server code.

id is always -1

Any new items created in the client get a -1. You need more than one key per user to have an id greater than that (only the server increments this number, not the client).

 

[schimanski]

Where is the SELECT? Looks like that may have been deleted out part of the SQL statement. Check the SQL statement in the GetUserItems method in the DB Class of the Server code.

Public Sub GetUserItems (user As String, lastId As Int) As List
    Dim subName As String = "GetUserItems"
    Try         
        Dim items As List
        items.Initialize
        Dim rs As ResultSet = sql.ExecQuery2("SELECT key, value, id, the_time FROM data WHERE user = ? AND id > ?", Array(user, lastId))
        Do While rs.NextRow
            Dim item As Item
            item.Initialize
            item.UserField = user
            item.KeyField = rs.GetString("key")
            item.ValueField = rs.GetBlob("value")
            item.idField = rs.GetLong("id")
            item.TimeField = rs.GetLong("the_time")
            items.Add(item)
        Loop
        rs.Close
        Return items
        
    Catch
        Log($"${subName}:  ${LastException}"$)
    End Try
    Return Null
End Sub

I did'n changed the sub from the first thread...

 

[OliverA]

Idk. But, I think you need to delete your table and use the SQL that is posted in post #1 to create the table (I don't think your primary key is set up correctly). Use the SQL code instead of trying to build the table manually (this would have also avoided the bigint issue). Once you have a fresh (and hopefully correct) table, try it again. I would also zap anything on the client and start from fresh.

 

[schimanski]

Oh, i did not know, that it is possible to build the table with the code...sorry. I'm searching for that feature and call back...

 

[schimanski]

I have created the table with the code above, but always the same problem. It is possible to write in the database but not to read. I checked it with the b4j and b4i-client.

If i try to create the table manually, xampp tolds, that 'key' is a reserved word under sql. Could this be a problem?

 

[OliverA]

Could this be a problem?

Yes, but the solution is to quote the column names in the SELECT statement just like the SQL statement provided in post #1. Do that in any SQL found in the server code.

 

[schimanski]

Thanks for your reply!

I now changed this on server-side:

Public Sub GetUserItems (user As String, lastId As Int) As List
    Dim subName As String = "GetUserItems"
    Try        
        Dim items As List
        items.Initialize
        Dim rs As ResultSet = sql.ExecQuery2("SELECT key, value, id, the_time FROM data WHERE user = ? AND id > ?", Array(user, lastId))

to this:

Dim rs As ResultSet = sql.ExecQuery2("SELECT `key`, `value`, `id`, `the_time` FROM data WHERE `user` = ? AND `id` > ?", Array(user, lastId))

and it seems, that it is possible to read the data out of the database. But now I get another exception on client-Side after refreshing a unser:

Waiting for debugger to connect...
Program started.
 **** User: u1 ****
Fehler in Zeile: 262 (ClientKVS)
java.util.zip.ZipException: incorrect header check
   at java.util.zip.InflaterInputStream.read(InflaterInputStream.java:164)
   at java.util.zip.InflaterInputStream.read(InflaterInputStream.java:122)
   at java.io.DataInputStream.readByte(DataInputStream.java:265)
   at anywheresoftware.b4a.randomaccessfile.B4XSerializator.readByte(B4XSerializator.java:133)
   at anywheresoftware.b4a.randomaccessfile.B4XSerializator.readObject(B4XSerializator.java:301)
   at anywheresoftware.b4a.randomaccessfile.B4XSerializator.ReadObject(B4XSerializator.java:112)
   at anywheresoftware.b4a.randomaccessfile.B4XSerializator.ConvertBytesToObject(B4XSerializator.java:82)
   at b4j.example.clientkvs._getall(clientkvs.java:497)
   at b4j.example.clientkvs._utilprintdata(clientkvs.java:177)
   at b4j.example.main._ckvs_newdata(main.java:168)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:498)
   at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:613)
   at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:231)
   at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:159)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:498)
   at anywheresoftware.b4a.BA.raiseEvent2(BA.java:90)
   at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:93)
   at anywheresoftware.b4a.keywords.Common.CallSub4(Common.java:482)
   at anywheresoftware.b4a.keywords.Common.CallSubNew(Common.java:429)
   at b4j.example.clientkvs._getuser_nonquerycomplete(clientkvs.java:608)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:498)
   at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:613)
   at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:231)
   at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:159)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:498)
   at anywheresoftware.b4a.BA.raiseEvent2(BA.java:90)
   at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:93)
   at anywheresoftware.b4a.BA$3.run(BA.java:246)
   at com.sun.javafx.application.PlatformImpl.lambda$null$172(PlatformImpl.java:295)
   at java.security.AccessController.doPrivileged(Native Method)
   at com.sun.javafx.application.PlatformImpl.lambda$runLater$173(PlatformImpl.java:294)
   at com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:95)
   at com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
   at com.sun.glass.ui.win.WinApplication.lambda$null$147(WinApplication.java:177)
   at java.lang.Thread.run(Thread.java:748)

In the sub:

'Returns a map with the keys and values of the given user.
Public Sub GetAll(user As String) As Map
   Dim res As Map
   res.Initialize
   Dim ser As B4XSerializator
   Dim rs As ResultSet = sql.ExecQuery2("SELECT key, value FROM data WHERE user = ? AND value IS NOT NULL", Array As String(user))
   Do While rs.NextRow
       res.Put(rs.GetString("key"), ser.ConvertBytesToObject(rs.GetBlob("value")))    <==== Exception
   Loop
   rs.Close
   Return res
End Sub

I deleted the hole database from the sql-server and creates a new one...seems to be only a littleness...

 

[OliverA]

The GetUserItems sub in the CloudKVS code always returns Null. That's the issue.

Dim items As List
items.Initialize

should be before the try and instead of

Return Null

it should be

Return items

Now if items is empty, it is either because nothing was found or an error was thrown on the server (but at least it does not bomb out the client).

 

[janderkan]

Hi
I had some issues with this and now I have spent a day or two to get it working.
Added a new event to the KVS and fixed an error when saving an array to a blob.
Erels clients are not compatible.
The most important is to use your favorite MySQL workbench to create the database.
Copy the SQL from DB module and execute it in the workbench.