Relational Data in FullSync apps

In this article we will see how FullSync can handle relational data and how to build relational databases using FullSync NoSQL technology

Why relational data ?

In Many apps, you may want to create relation between documents in your FullSync databases. For example, let’s consider a Flight planning application. We would have ‘flight’ documents with ‘from’ and ‘to’ airports using a ‘plane’ model. This could be described as this jSON document:


{
	_id: "6ccbe0fe-63b3-4063-b73b-16e2cae112c4",
	flight: {
   		"from": "LFPN",
   		"to": "LFOZ",
   		"plane": "F-GXCF",
   		"comment": "test flight",
   		"type": "flight",
   		"date": "10/11/2017",
   		"time": "10:00"
	}
}

As you see, this document refers to a ‘LFPN’ ‘from’ airport and to a ‘LFOZ’ ‘to’ airport, using a ‘F-GXCF’ plane. All these are IDs to other documents that can be described this way


{  
	_id: "LFPN",
	airport: {
   		"type": [
       		"airport",
       		"medium_airport"
   		],
   		"airport_id": "4188",
   		"iaco": "LFPN",
   		"name": "Toussus-le-Noble Airport",
   		"latitude": "48.75189971923828",
   		"longitude": "2.1061899662017822",
   		"altitude": "538",
   		"zone": "EU",
   		"country": "FR",
   		"iso_region": "FR-J",
   		"city": "Toussus-le-Noble",
   		"scheduled_service": "no",
   		"gps_code": "LFPN",
   		"iata_faa": "TNF",
   		"local_code": "",
   		"Colonne2": "",
   		"Colonne3": "",
   		"Colonne1": "",
   		"id": "LFPN"
	}
}  

And for a plane document :


{  
	_id: "F-GXCF",
	plane: {
   		"type": "plane",
   		"ID": "3",
   		"TAG": "F-GXCF",
   		"TYPE": "AT-01",
   		"SPEED": "110",
   		"SEATS": "2",
   		"pid": "F-GXCF"
	}
}

The goal is to get in one query a flight with all the related data attached

Relational data with linked documents

This feature is known as ‘Linked documents’ in CouchDB. Convertigo FullSync supports linked documents on the server side and of course on the client side for all SDKs including the JS based SDKs used by Mobile Builder. More information on linked documents can be found on the official documentation for CouchDB : http://docs.couchdb.org/en/stable/ddocs/views/joins.html

Creating linked documents views is easy (when of course you know how …) CouchDB documentation says that emitting an object

{_id:<some other document id>}

on an index wil have CouchDB return the linked document if the query is done with ‘include_docs’ set to ‘true’. To illustrate this, here is how we can write a map function using this feature:


function (doc) {
	try {
		if (doc.flight) {
			emit(doc._id, doc.flight);
			emit(doc._id, {_id: doc.flight.from});
			emit(doc._id, {_id: doc.flight.to});
			emit(doc._id, {_id: doc.flight.plane});
		}
	} catch (err) {
		log(err.message);
	}
}

As we see here, we emit on the same index, the flight itself plus the 3 linked documents for the ‘from’ , ‘to’ airports and the ‘plane’

Query a Linked document view

We will query the view exactly as usual by specifying the ‘ddoc’ (Design document) and the ‘view’, but we will also set the ‘include_docs’ parameter to ‘true’. If you use Mobile Builder, be sure to set the “include_ docs” property to true in the QueryView Action.

IncludeDocs

Use results in your app

The Query result will be in this format:


{
    "total_rows": 4,
    "offset": 0,
    "rows": [
        {
            "key": "35f20ed0-a2c7-4943-b108-7803e94b9fc8",
            "id": "35f20ed0-a2c7-4943-b108-7803e94b9fc8",
            "value": {
                "_id": "F-GFZA"
            },
            "doc": {
                "plane": {
                    "type": "plane",
                    "ID": "4",
                    "TAG": "F-GFZA",
                    "TYPE": "PA-28-181",
                    "SPEED": "120",
                    "SEATS": "4",
                    "pid": "F-GFZA"
                },
                "_id": "F-GFZA",
                "_rev": "1-244618913889b27284f5e93c3080323e"
            }
        },
        {
            "key": "35f20ed0-a2c7-4943-b108-7803e94b9fc8",
            "id": "35f20ed0-a2c7-4943-b108-7803e94b9fc8",
            "value": {
                "_id": "LFAI"
            },
            "doc": {
                "airport": {
                    "type": [
                        "airport",
                        "small_airport"
                    ],
                    "airport_id": "4052",
                    "iaco": "LFAI",
                    "name": "Nangis-Les Loges Airport",
                    "latitude": "48.59619903564453",
                    "longitude": "3.0067899227142334",
                    "altitude": "428",
                    "zone": "EU",
                    "country": "FR",
                    "iso_region": "FR-J",
                    "city": "Nangis/Les Loges",
                    "scheduled_service": "no",
                    "gps_code": "LFAI",
                    "iata_faa": "",
                    "local_code": "",
                    "Colonne2": "",
                    "Colonne3": "",
                    "Colonne1": "",
                    "id": "LFAI"
                },
                "_id": "LFAI",
                "_rev": "1-cfdd54a3b80495dc22b5a6fcde71a62e"
            }
        },
        {
            "key": "35f20ed0-a2c7-4943-b108-7803e94b9fc8",
            "id": "35f20ed0-a2c7-4943-b108-7803e94b9fc8",
            "value": {
                "_id": "LFPN"
            },
            "doc": {
                "airport": {
                    "type": [
                        "airport",
                        "medium_airport"
                    ],
                    "airport_id": "4188",
                    "iaco": "LFPN",
                    "name": "Toussus-le-Noble Airport",
                    "latitude": "48.75189971923828",
                    "longitude": "2.1061899662017822",
                    "altitude": "538",
                    "zone": "EU",
                    "country": "FR",
                    "iso_region": "FR-J",
                    "city": "Toussus-le-Noble",
                    "scheduled_service": "no",
                    "gps_code": "LFPN",
                    "iata_faa": "TNF",
                    "local_code": "",
                    "Colonne2": "",
                    "Colonne3": "",
                    "Colonne1": "",
                    "id": "LFPN"
                },
                "_id": "LFPN",
                "_rev": "1-b77d4e3f144e235f1c296cefd56a79c4"
            }
        },
        {
            "key": "35f20ed0-a2c7-4943-b108-7803e94b9fc8",
            "id": "35f20ed0-a2c7-4943-b108-7803e94b9fc8",
            "value": {
                "from": "LFPN",
                "to": "LFAI",
                "plane": "F-GFZA",
                "occupiedSeats": 2,
                "date": "11/10/2018",
                "time": "10:00",
                "comment": "TEST"
            },
            "doc": {
                "flight": {
                    "from": "LFPN",
                    "to": "LFAI",
                    "plane": "F-GFZA",
                    "occupiedSeats": 2,
                    "date": "11/10/2018",
                    "time": "10:00",
                    "comment": "TEST"
                },
                "~c8oAcl": "olivierp@convertigo.com",
                "_id": "35f20ed0-a2c7-4943-b108-7803e94b9fc8",
                "_rev": "1-4f512abbf19c4667901c90e66136abac"
            }
        }
    ]
}

The query will return 4 rows of data for the same key. (One row by emit) And in the response you will have the flight itself plus all the linked documents for this flight

You will be able to use this data in your app easily as row[0] is ‘plane’ data, row[1] ‘from’ data, row[2] ‘to’ data and flight details are in row[3]

Leave a comment