2

First a little background; The GAE app was developed by a third party and provides an interface for some embedded hardware to save data to the cloud over a mobile network. The data is POSTed from the device as a JSON object over HTTP. The app on GAE is written in python, and I have full access to the source code (even though I don't fully understand it!)

The data field "points" is a fixed length array of items with a location, datestamp and two numbers.

The data model for a single point is thus (obfuscated for security):

class MeasurementPoint(EndpointsModel):
    Val1 = ndb.FloatProperty()
    Val2 = ndb.FloatProperty()
    timestamp = ndb.DateTimeProperty(auto_now_add=True)
    loc = ndb.GeoPtProperty()

and for the encapuslating class (again obfuscated):

class CompletedReading(EndpointsModel):  
    
    _message_fields_schema = ('id', 'inspection_id', 'timestamp', 
                              'points',
                              'Val1_average', 'Val2_average', 'Val1_min', 
                              'location_id',
                              'organisation_id', )

    
    timestamp = ndb.DateTimeProperty(auto_now_add=True)
    points = ndb.LocalStructuredProperty(Measurement, repeated=True, compressed=True) 
    
    Val1_average = ndb.FloatProperty()
    Val2_average = ndb.FloatProperty()
    Val1_min = ndb.FloatProperty()
        
    ... hopefully you get the idea.

The posted JSON is something like:

{
  "points": [
    {
      "Val1": 999,
      "Val2": 319160.3,
      "timestamp": "2020-03-03T15:56:01.000000",
      "location": {
        "lat": 52.75024,
        "lon": -1.919412
      }
    },
    {
      "Val1": 999,
      "Val2": 319160.3,
      "timestamp": "2020-03-03T15:56:02.000000",
      "location": {
        "lat": 52.75024,
        "lon": -1.919412
      }
    },
    ..... n sets of "points" ( always a fixed size array )
  ],
  "organisation_id": "5634161670881280",
  "location_id": "5638358357245952",
  "timestamp": "2020-03-03T15:55:58.000000"
}

So... I now have this data stored in the database and I am writing a .net app (in VB, but it could easily be C#) and I am using the Google.Cloud.Datastore.V1 API to access it. So far this is all working and I can list the CompletedReading entity using the following simple code:

Imports Google.Cloud.Datastore.V1
Imports Google.Protobuf

Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim ProjectId As String = "xxxxxxxxxx" ' Obfuscated for security
        Dim db As DatastoreDb
        Dim kind As String = "CopmletedReading" 
        Dim q As Query
        Dim Entry As Entity

        ' In order for this to work you have to set the environment variable 
        ' GOOGLE_APPLICATION_CREDENTIALS= <Path to zzzzzzzzzzzz.json> (Obfuscated for security)
        

        Environment.SetEnvironmentVariable("GOOGLE_APPLICATION_CREDENTIALS", "zzzzzzzzzzzz.json")

        db = DatastoreDb.Create(ProjectId)

        q = New Query(kind)

        Label1.Text = ""

        For Each Entry In db.RunQueryLazily(q)
            Label1.Text = Label1.Text & vbCrLf & vbCrLf & vbCrLf & vbCrLf &
                 "ToString:" & vbCrLf & Entry.ToString & vbCrLf & vbCrLf &
                 "Key:" & vbCrLf & Entry.Key.ToString & vbCrLf & vbCrLf &
                 "Properties:" & vbCrLf & Entry.Properties().ToString & vbCrLf & vbCrLf &
                 "name:" & vbCrLf & Entry.Item("points").ToString
        Next
    End Sub
End Class

results in the following output for each entity ....


ToString:
{ "key": { "partitionId": { "projectId": "xxxxxxxxxx" }, "path": [ { "kind": "CompletedReading", "id": "6320665133056000" } ] }, "properties": { "Val1_average": { "doubleValue": 422 }, "Val2_average": { "doubleValue": 278 }, "organisation_ref": { "keyValue": { "partitionId": { "projectId": "xxxxxxxxxx" }, "path": [ { "kind": "Organisation", "id": "5629499534213120" } ] } }, "location_id": { "keyValue": { "partitionId": { "projectId": "xxxxxxxxxx" }, "path": [ { "kind": location", "id": "4922041111150592" } ] } }, "points": { "arrayValue": { "values": [ { "meaning": 22, "blobValue": "eJwrkpXiy8gvyqzKzytJzInPKa1QYNDiVGQAgQ8VDkUqHJxSHDn5yYklmfl5QCkRbcPVjjNu713g5WB5d9oJ1vQHEgd0iqQ42KU4SzJzU4sLEnMLQCZwLNu+ZcuOzxeYiqSleMpSi0oyk9GNX1DoAAD8CChx", "excludeFromIndexes": true }, { "meaning": 22, "blobValue": "eJwrkpXiy8gvyqzKzytJzInPKa1QYNDiVGQAgYIqhyIVDk4pjpz85MSSzPw8oJSItuFqxxm39y7wcrB8HMzQk/5A4oBOkRQHuxRnSWZuanFBYm4ByASOXdu3bNnx+QJTkbQUT1lqUUlmMrrxDwocAK8uJ8g=", "excludeFromIndexes": true }, { "meaning": 22, "blobValue": "eJwrkpXiy8gvyqzKzytJzInPKa1QYNDiVGQAgQPVDkUqHJxSHDn5yYklmfl5QCkRbcM3Bmp/9y7wcrB8KWAhlPFA4oBOkRQHuxRnSWZuanFBYm4ByASOXdu3bNnx+QJTkbQUT1lqUUlmMrrxCwodAKVhJzw=", "excludeFromIndexes": true } ] } }, "timestamp": { "timestampValue": "2016-12-14T10:08:47Z" }, "Val1_min": { "doubleValue": 399 } } }

Key:
{ "partitionId": { "projectId": "xxxxxxxxxx" }, "path": [ { "kind": "CompletedReading", "id": "6320665133056000" } ] }

Properties:
{ "Val1_average": { "doubleValue": 422 }, "Val2_average": { "doubleValue": 278 }, "organisation_ref": { "keyValue": { "partitionId": { "projectId": "xxxxxxxxxx" }, "path": [ { "kind": "Organisation", "id": "5629499534213120" } ] } }, "location_id": { "keyValue": { "partitionId": { "projectId": "xxxxxxxxxx" }, "path": [ { "kind": "location", "id": "4922041111150592" } ] } }, "points": { "arrayValue": { "values": [ { "meaning": 22, "blobValue": "eJwrkpXiy8gvyqzKzytJzInPKa1QYNDiVGQAgQ8VDkUqHJxSHDn5yYklmfl5QCkRbcPVjjNu713g5WB5d9oJ1vQHEgd0iqQ42KU4SzJzU4sLEnMLQCZwLNu+ZcuOzxeYiqSleMpSi0oyk9GNX1DoAAD8CChx", "excludeFromIndexes": true }, { "meaning": 22, "blobValue": "eJwrkpXiy8gvyqzKzytJzInPKa1QYNDiVGQAgYIqhyIVDk4pjpz85MSSzPw8oJSItuFqxxm39y7wcrB8HMzQk/5A4oBOkRQHuxRnSWZuanFBYm4ByASOXdu3bNnx+QJTkbQUT1lqUUlmMrrxDwocAK8uJ8g=", "excludeFromIndexes": true }, { "meaning": 22, "blobValue": "eJwrkpXiy8gvyqzKzytJzInPKa1QYNDiVGQAgQPVDkUqHJxSHDn5yYklmfl5QCkRbcM3Bmp/9y7wcrB8KWAhlPFA4oBOkRQHuxRnSWZuanFBYm4ByASOXdu3bNnx+QJTkbQUT1lqUUlmMrrxCwodAKVhJzw=", "excludeFromIndexes": true } ] } }, "timestamp": { "timestampValue": "2016-12-14T10:08:47Z" }, "Val1_min": { "doubleValue": 399 } }

name:
{ "arrayValue": { "values": [ { "meaning": 22, "blobValue": "eJwrkpXiy8gvyqzKzytJzInPKa1QYNDiVGQAgQ8VDkUqHJxSHDn5yYklmfl5QCkRbcPVjjNu713g5WB5d9oJ1vQHEgd0iqQ42KU4SzJzU4sLEnMLQCZwLNu+ZcuOzxeYiqSleMpSi0oyk9GNX1DoAAD8CChx", "excludeFromIndexes": true }, { "meaning": 22, "blobValue": "eJwrkpXiy8gvyqzKzytJzInPKa1QYNDiVGQAgYIqhyIVDk4pjpz85MSSzPw8oJSItuFqxxm39y7wcrB8HMzQk/5A4oBOkRQHuxRnSWZuanFBYm4ByASOXdu3bNnx+QJTkbQUT1lqUUlmMrrxDwocAK8uJ8g=", "excludeFromIndexes": true }, { "meaning": 22, "blobValue": "eJwrkpXiy8gvyqzKzytJzInPKa1QYNDiVGQAgQPVDkUqHJxSHDn5yYklmfl5QCkRbcM3Bmp/9y7wcrB8KWAhlPFA4oBOkRQHuxRnSWZuanFBYm4ByASOXdu3bNnx+QJTkbQUT1lqUUlmMrrxCwodAKVhJzw=", "excludeFromIndexes": true } ] } }

This is the point at which I'm stuck. I'm not familiar with GAE/Datastore/Python or the Google API .... I have tried unsuccesfully to try to deserialise the blob into a VB class. I can't find any references in the API to a LocalStructuredProperty so I can't deal with it directly.

My "last ditch" thought was to attempt to write some python on the web server to read the datastore back into a suitable object then fire the data back at my VB app as JSON, but I'd really prefer to be able to access the object in the datastore from VB and to get my array of points back.

Can anyone suggest where I go from here?? Thanks for looking.

Paul Smith
  • 21
  • 5
  • More research indicates that the blob may be a compressed (ZLIB) array of segments. Apparently this is what "Meaning: 22" indicates. – Paul Smith Jul 02 '20 at 10:27

1 Answers1

0

This is currently only a partial answer....

Indeed the field was stored as a ZLIB compressed array of characters. There's porbably an easier way to achieve what I did, but this works up to a point;

Convert the Google ByteString to a VB Byte Array. From there convert it to a MemoryStream and decompress it. Write a Class with an Unpack() method to fiddle with the resultant uncompressed stream.

I had to reverse engineer quite a bit of stuff and the only bit that doesn't quite work is the Datestamp field which (for now) I can't figure out how it's stored.

Here's how I did it. Not pretty as it's very much still work in progress, but this has gotten me further along the route;

First, the class

 ' Class to encapsulate a POINT object stored in the database.
    Private Class OnePoint
        Dim Val1 As Double
        Dim Val2 As Double
        Dim timestamp As DateTime
        Dim lat As Double
        Dim lon As Double

        Public Sub Unpack(bs As Stream)
            Dim b As Byte
            Dim b1 As Byte
            Dim i As Integer
            Dim bArry As Byte()
            Dim d As Double

            ReDim bArry(20)  

            ' 3 Currently unknown bytes.
            bs.ReadByte()
            bs.ReadByte()
            bs.ReadByte()
            b = bs.ReadByte
            Debug.Assert(b = &H4)  ' Length of the string "Val1"
            For i = 0 To b - 1
                b1 = bs.ReadByte()
                bArry(i) = b1
            Next
            'debug.assert (check the string is what we expect)
            b = bs.ReadByte()
            Debug.Assert(b = &H20)  ' Always a space
            b = bs.ReadByte()
            Debug.Assert(b = &H0)  ' Always a NULL
            b = bs.ReadByte()
            Debug.Assert(b = &H2A)  ' Always 0x2A
            b = bs.ReadByte()
            Debug.Assert(b = &H9)  ' Always 0x09
            b = bs.ReadByte()
            Debug.Assert(b = &H21)  ' Always 0x21

            ' Now read the "Val1" value as an 8 byte number LSB..MSB
            For i = 0 To 7
                b1 = bs.ReadByte()
                bArry(i) = b1
            Next
            If (BitConverter.IsLittleEndian = False) Then
                ' This computer byte order is not as per the data we have, so reverse the array.
                Array.Reverse(bArry)
            End If
            hLux = BitConverter.ToDouble(bArry, 0)

            'Next bit is the location....
            b = bs.ReadByte()
            Debug.Assert(b = &H72)  ' Always 0x72
            b = bs.ReadByte()
            Debug.Assert(b = &H24)  ' Always 0x24
            b = bs.ReadByte()
            Debug.Assert(b = &H8)  ' Always 0x08
            b = bs.ReadByte()
            Debug.Assert(b = &H9)  ' Always 0x09
            b = bs.ReadByte()
            Debug.Assert(b = &H1A)  ' Always 0x1A
            b = bs.ReadByte
            Debug.Assert(b = &H8)  ' Length of the string "location"
            For i = 0 To b - 1
                b1 = bs.ReadByte()
                bArry(i) = b1
            Next
            'debug.assert (check the string is what we expect)
            b = bs.ReadByte()
            Debug.Assert(b = &H20)  ' Always a space
            b = bs.ReadByte()
            Debug.Assert(b = &H0)  ' Always a NULL
            b = bs.ReadByte()
            Debug.Assert(b = &H2A)  ' Always 0x2A
            b = bs.ReadByte()
            Debug.Assert(b = &H14)  ' Always 0x14
            b = bs.ReadByte()
            Debug.Assert(b = &H2B)  ' Always 0x2B
            b = bs.ReadByte()
            Debug.Assert(b = &H31)  ' Always 0x31

            ' Now read the latitude value as an 8 byte number LSB..MSB
            For i = 0 To 7
                b1 = bs.ReadByte()
                bArry(i) = b1
            Next
            If (BitConverter.IsLittleEndian = False) Then
                ' This computer byte order is not as per the data we have, so reverse the array.
                Array.Reverse(bArry)
            End If
            lat = BitConverter.ToDouble(bArry, 0)

            b = bs.ReadByte()
            Debug.Assert(b = &H39)  ' Always 0x39

            ' Now read the longitude value as an 8 byte number LSB..MSB
            For i = 0 To 7
                b1 = bs.ReadByte()
                bArry(i) = b1
            Next
            If (BitConverter.IsLittleEndian = False) Then
                ' This computer byte order is not as per the data we have, so reverse the array.
                Array.Reverse(bArry)
            End If
            lon = BitConverter.ToDouble(bArry, 0)

            b = bs.ReadByte()
            Debug.Assert(b = &H2C)  ' Always 0x2C
            b = bs.ReadByte()
            Debug.Assert(b = &H72)  ' Always 0x72
            b = bs.ReadByte()
            Debug.Assert(b = &H1A)  ' Always 0x1A
            b = bs.ReadByte()
            Debug.Assert(b = &H8)  ' Always 0x08
            b = bs.ReadByte()
            Debug.Assert(b = &H7)  ' Always 0x07
            b = bs.ReadByte()
            Debug.Assert(b = &H1A)  ' Always 0x1A

            b = bs.ReadByte()
            Debug.Assert(b = &H9)  ' Length of the string  "timestamp" 
            For i = 0 To b - 1
                b1 = bs.ReadByte()
                bArry(i) = b1
            Next
            'debug.assert (check the string is what we expect)

            ' FOR NOW, SKIP THIS FIELD
            For i = 0 To 15
                b1 = bs.ReadByte()
            Next

            b = bs.ReadByte()
            Debug.Assert(b = &H4)  ' Length of the string "Val2" 
            For i = 0 To b - 1
                b1 = bs.ReadByte()
                bArry(i) = b1
            Next
            'debug.assert (check the string is what we expect)

            b = bs.ReadByte()
            Debug.Assert(b = &H20)  ' Always a space
            b = bs.ReadByte()
            Debug.Assert(b = &H0)  ' Always a NULL
            b = bs.ReadByte()
            Debug.Assert(b = &H2A)  ' Always 0x2A
            b = bs.ReadByte()
            Debug.Assert(b = &H9)  ' Always 0x09
            b = bs.ReadByte()
            Debug.Assert(b = &H21)  ' Always 0x21

            ' Now read the "Val2"value as an 8 byte number LSB..MSB
            For i = 0 To 7
                b1 = bs.ReadByte()
                bArry(i) = b1
            Next
            If (BitConverter.IsLittleEndian = False) Then
                ' This computer byte order is not as per the data we have, so reverse the array.
                Array.Reverse(bArry)
            End If
            vLux = BitConverter.ToDouble(bArry, 0)

        ' Make sure the number of bytes read matches what we expeted....
            Debug.Assert(bs.Position = 126)
        End Sub
    End Class

and then the way I unpacked it...

    Dim o As Object = Entry.Properties("points").ArrayValue.Values      ' Google.Protobuf.Collections.RepeatedField(Of Google.Cloud.Datastore.V1.Value)
    Dim i As Integer
    Dim b As Google.Protobuf.ByteString
    Dim x As Byte()
    Dim Finalresult As Byte()

    ' o points to the object which is currently a list of 15 compressed inspection points.
    For i = 0 To o.count - 1
        b = o(i).BlobValue      ' The compressed array as a bytestring

        ReDim x(b.Length)       ' Make some space in an array of bytes....
        b.CopyTo(x, 0)          ' Copy from Google.Protobuf.ByteString to VB byte array

        ' Now x is an array of bytes the size of the BLOB...
        ' ... but it will be a compressed ZLIB format....
        ' If this is indeed the case then the first two bytes will be 0x78 0x9C
        ' ... and by debuginng through we can see that they are !!
        '
        ' Unfortunately "Deflate" requires a stream for input, not a byte array.
        ' So convert the byte array to a stream so that we can pass that to Deflate....
        '
        ' There may be a mode direct way to convert from a Google.Protobuf.ByteString to a stream,
        ' but this works.
        '
        ' When dealing with memorystreams you have to use the decorator 'Using' to ensure correct object disposal when we're done...
        Using myms As New MemoryStream(x)
            Using DecompressedMemoryStream As New MemoryStream()
'#Const EITHER = 1
#If EITHER Then
                ' Either of these methods works....this one relies on NOT having the header
                myms.ReadByte()   ' Throw away the header
                myms.ReadByte()   ' Throw away the header

                Using DecompressionStream As New DeflateStream(myms, CompressionMode.Decompress)
#Else
                ' Either of these methods works....this one relies on having the header
                Using DecompressionStream As New GZipStream(myms, CompressionMode.Decompress)
#End If
                    DecompressionStream.CopyTo(DecompressedMemoryStream)
                    Finalresult = DecompressedMemoryStream.ToArray()
                End Using
            End Using
        End Using
        Debug.Print(Finalresult.Length)
    
        If (FinalResult.Length == 126)
            Dim ms As New MemoryStream(Finalresult)
            p.Unpack(ms)
        End If
    Next

..If I figure out what any of the unknown bytes are or if I can decode the timestamp I will post a follow-up answer, but for now I can do what I need to do.

Thanks for looking!

Paul Smith
  • 21
  • 5