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

Discussion in 'B4J Share Your Creations' started by JakeBullet70, Jul 15, 2016.

  1. JakeBullet70

    JakeBullet70 Well-Known Member Licensed User

    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.

    Code:
    CREATE TABLE `data` (
      `user` varchar(
    254NOT NULL,
      `key` varchar(
    254NOT 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;
    upload_2016-7-15_21-54-48.png




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

    upload_2016-7-15_21-53-31.png


    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!!!!
     

    Attached Files:

  2. schimanski

    schimanski Well-Known Member Licensed User

    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.
    Code:
    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:

    In my xampp-console it looks like:

    1.png

    Adding an item, I get the following message:

    Code:
    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:

    Code:
    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:

    Code:
    java.io.EOFException: Unexpected end of ZLIN input stream
    I think, that the table is not created correctly.

    Thanks for help.
     
  3. AscySoft

    AscySoft Active Member Licensed User

    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, OliverA and JakeBullet70 like this.
  4. schimanski

    schimanski Well-Known Member Licensed User

    You are right!

    Like JacketBullet70 wrote is the_time an bigint(20):confused:. 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.

    Code:
    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

    Code:
    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:

    2.png

    Edit: I checked it with the CloudKVS-Client for b4x. The id is an integer, but the same exception.
     
    Last edited: Feb 10, 2018
  5. OliverA

    OliverA Well-Known Member Licensed User

    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.

    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).
     
  6. schimanski

    schimanski Well-Known Member Licensed User

    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...
     
  7. OliverA

    OliverA Well-Known Member Licensed User

    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 likes this.
  8. schimanski

    schimanski Well-Known Member Licensed User

    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...
     
  9. schimanski

    schimanski Well-Known Member Licensed User

    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?
     
  10. OliverA

    OliverA Well-Known Member Licensed User

    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.
     
  11. schimanski

    schimanski Well-Known Member Licensed User

    Thanks for your reply!

    I now changed this on server-side:

    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))
    to this:

    Code:
    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:

    Code:
    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:
    Code:
    'Returns a map with the keys and values of the given user.
    Public Sub GetAll(user As StringAs 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...
     
  12. OliverA

    OliverA Well-Known Member Licensed User

    The GetUserItems sub in the CloudKVS code always returns Null. That's the issue.
    Code:
    Dim items As List
    items.Initialize
    should be before the try and instead of
    Code:
    Return Null
    it should be
    Code:
    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).
     
Loading...