Data models

For each one of the data sets that you want to include in the project you will need to describe the data model. This description should include its relations or associations with any other model. The description should be placed in a json file following the json specs for this purpose. You will need to store all these json files in a single folder. Another limitation is that each model should have a unique name independently of its type. From now on, in this document, we will assume that all json files for each one of your data models will be stored in the directory /your-path/json-files

Table of contents

1. JSON Specs
2. Supported Data Types
3. Associations Spec
   1. Foreign keys
      1. single-end foreign keys
      2. paired-end foreign keys
   2. Many to many association through table
   3. generic associations
   4. Differences between backend and Frontend (GUI)
   5. About the association types
4. The code generator at work
   1. The model itself
   2. For all associations
   3. Association type x_to_many
   4. Association type x_to_one
   5. Association type many_to_many through sql_cross_table implementation
      1. Cross Table Resolver / Model
5. Authorization-checks and record limits
6. Pagination types
7. Data Loader

JSON Specs

Each json file describes one and only one model. However, one model can reference another model using the associations mechanism described below.

For a complete description of each model we need to specify the following fields in the json file:

Supported Data Types

The following types are allowed for the attributes field.

For more info about Date, Time, and DateTime types, please see this info.

Example:

• Date: A date string, such as 2007-12-03.
• Time: A time string at UTC, such as 10:15:30Z.
• DateTime: A date-time string at UTC, such as 2007-12-03T10:15:30Z

Associations Spec

We will consider four types of associations according to the relation between associated records of the two models:

1. one_to_one
2. many_to_one
3. one_to_many
4. many_to_many

For all types of association, the necessary arguments would be:

Foreign keys

It’s important to notice that when a model involves a foreign key for the association, this key should be explicitly written into the attributes field of the given local model. Although, foreign keys will be available for the user only as readable attributes, for editing this attributes we offer the possibility as part of the API, please see this section for more info. To store to-many associations (many-to-many or one-to-many) via foreign keys Zendro offers to store the foreign keys in arrays. In this case the model will have an array attribute which will store ids from the associated records.

single-end foreign keys

Storing the foreign keys on a single end of the association means that only one of the two associated data-models holds the foreign-key attribute. Storing the keys in that way guarantees fast write actions and avoids error prone operations of writing multiple records to update any association. It also requires less storage space, but can become slow to read and search, especially in a distributed context, where the associated records could be distributed over multiple servers.

To define single-end foreign key associations the following arguments need to be added:

Examples:

{
  "model" : "book",
  "storageType" : "sql",
  "attributes" : {
    "title" : {"type":"String", "description": "The book's title"},
    "publisher_id": "Int"
  },
  "associations":{
      "publisher" : {
        "type" : "many_to_one", // association type
        "implementation": "foreignkeys",
        "reverseAssociation": "books",
        "target" : "publisher", // the target model name is `publisher`
        "targetKey" : "publisher_id", // foreign key for this association
        "keysIn": "book", // FK to `publisher` will be stored in the `book` model
        "targetStorageType" : "sql"
        }
  }
}

{
  "model" : "publisher",
  "storageType" : "sql",
  "attributes" : {
    "publisher_id": "Int",
    "publisher_name": "String"
  },
  "associations":{
      "books" : {
        "type" : "one_to_many", // association type
        "implementation": "foreignkeys",
        "reverseAssociation": "publisher",
        "target" : "book", // the target model name is `book`
        "targetKey" : "publisher_id", // foreign key for this association
        "keysIn": "book", // FK to `book` will be stored in the `book` model
        "targetStorageType" : "sql"
        }
  }
}

paired-end foreign keys

Storing the association via paired-end foreign keys means that both associated data-models contain a reference (foreign key) to the associated records. Storing the keys in that way guarantees read and search efficiency, especially in a distributed context, at the cost of time and storage-space when handling write actions. Since the keys are stored at both ends the information needs to be updated at both ends as well, which is slower and more prone to errors.

Many-to-many associations can be stored via paired-end associations. In this case both models will hold an array attribute which will store ids from the associated records. These two attributes will be described in the association as sourceKey and targetKey. Also, for indicating that the association is a many-to-many association via arrays as foreign key, we need to specify in the association info the implementation field as foreignkeys.

To define paired-end foreign key associations the following arguments need to be added:

Examples:

Assume we have an many_to_many association between two models book and author and an one_to_many association between author and card. The model definitions should be as below:

{
    "model" : "author",
    "storageType" : "sql",
    "database": "default-sql",
    "attributes" : {
        "id": "String",
        "name": "String",
        "lastname": "String",
        "email": "String",
        "book_ids": "[ String ]",
        "card_ids": "[ String ]"
    },

    "associations":{
      "books":{
        "type": "many_to_many", // association type
        "implementation": "foreignkeys",
        "reverseAssociation": "authors",
        "target": "book", // target model name
        "targetKey": "author_ids", // foreign key array stored in target model
        "sourceKey": "book_ids", // foreign key array stored in source model
        "keysIn": "author", // source model name
        "targetStorageType": "sql"
      },
      "cards":{
        "type": "one_to_many", // association type
        "implementation": "foreignkeys",
        "reverseAssociation": "author",
        "target": "card", // target model name
        "targetKey": "author_id", // foreign key stored in target model
        "sourceKey": "card_ids", // foreign key array stored in source model
        "keysIn": "author", // source model name
        "targetStorageType": "sql"
      }
    },

    "internalId": "id"
  }

{
    "model" : "book",
    "storageType" : "sql",
    "database": "default-sql",
    "attributes" : {
        "id": "String",
        "title": "String",
        "genre": "String",
        "ISBN": "String",
        "author_ids": "[ String]"
    },

    "associations":{
      "authors":{
        "type": "many_to_many", // association type
        "implementation": "foreignkeys",
        "reverseAssociation": "books",
        "target": "author", // target model name
        "targetKey": "book_ids", // foreign key array stored in target model
        "sourceKey": "author_ids", // foreign key array stored in source model
        "keysIn": "book", // source model name
        "targetStorageType": "sql"
      }
    },

    "internalId": "id"
  }

{
    "model" : "card",
    "storageType" : "sql",
    "database": "default-sql",
    "attributes" : {
        "card_id": "String",
        "genre": "String",
        "author_id": "String"
    },

    "associations":{
      "author":{
        "type": "many_to_one", // association type
        "implementation": "foreignkeys",
        "reverseAssociation": "cards",
        "target": "author", // target model name
        "targetKey": "card_ids", // foreign key array stored in target model
        "sourceKey": "author_id", // foreign key stored in source model
        "keysIn": "card", // source model name
        "targetStorageType": "sql"
      }
    },

    "internalId": "id"
  }

Many to many association through table

When the association is of type many_to_many and it refers to a more particular type of association many_to_many, stored in a cross table, the implementation argument should be set as to_many_through_sql_cross_table and it’s only available for sql stored models.

Example:

//User model
{
  "model" : "User",
  "storageType" : "SQL",
  "attributes" : {
    "email" : "String",
    "password" : "String"
  },
  "associations" :{
    "roles" : {
      "type" : "many_to_many", // association type
      "implementation": "sql_cross_table",
      "reverseAssociation": "users",
      "target" : "Role", // target model name
      "targetKey" : "role_Id", // foreign key stored in target model
      "sourceKey" : "user_Id", // foreign key stored in source model
      "keysIn" : "role_to_user", // source model name
      "targetStorageType" : "sql",
      "label": "name"
    }
  }

}

//Role model
{
  "model" : "Role",
  "storageType" : "SQL",
  "attributes" : {
    "name" : "String",
    "description" : "String"
  },
  "associations" : {
    "users" : {
      "type" : "many_to_many", // association type
      "implementation": "sql_cross_table",
      "reverseAssociation": "roles",
      "target" : "User", // target model name
      "targetKey" : "user_Id", // foreign key stored in target model
      "sourceKey" : "role_Id", // foreign key stored in source model
      "keysIn" : "role_to_user", // source model name
      "targetStorageType" : "sql",
      "label": "email"
    }
  }
}

//role_to_user model
{
  "model" : "role_to_user",
  "storageType" : "SQL",
  "attributes" : {
    "user_Id" : "Int",
    "role_Id" : "Int"
  }
}

generic associations

To generate a generic association the generic implementation type can be used. This will genereate code stubs in the models for the user specific implementation of resolving the management of the association.

Differences between backend and Frontend (GUI)

The same data model description files (.json) can be used for generating both the backend and frontend. Fields such as label and sublabel in the model specification that are only needed for GUI generator are ignored by the backend generator.

The field reverseAssociation is only mandatory for generating the queries used in the single-page-application to communicate with the graphql-server. Generating the graphql-server code without setting this field will give an appropriate warning.

About the association types

In Zendro, there are four association types, namely * : 1 (many-to-one), 1 : * (one-to-many), * : * (many-to-many) and 1:1 (one-to-one).

As usually, in any association type the foreign-key shall be placed inside one of the two associated tables. However both table models need to be notified about that link. This association information shall be placed in each model definition JSON files. Below we consider the models for two tables A and B, that are defined in the files A.json and B.json correspondingly.

Within the body of any model JSON file, the model that is described in this file is considered as a source model, and any other model is considered as a target. Therefore, within the JSON source it is required to specify how the current model is associated with all its targets.

Let’s use some examples to explain these four types of association:

1. Many-to-One This case happens when more than one element of the table A can reference the same element of table B. In this case, the foreign-key has to be created in the table A.

   Example: Table A contains a list of employees and each of them can work in a single department only (catalog B).

   Data Model Definition for table A:

    {
      "model": "employee",
      "storageType": "mongodb",
      "attributes": {
          "employee_id": "String",
          "name": "String",
          "department_id": "String"
      },
      "associations": {
          "department": {
              "type": "many_to_one",
              "implementation": "foreignkeys",
              "reverseAssociation": "employees",
              "target": "department",
              "targetKey": "department_id",
              "keysIn": "employee",
              "targetStorageType": "mongodb"
          }
      },
      "internalId": "employee_id",
      "id": {
          "name": "employee_id",
          "type": "String"
      },
      "useDataLoader": true
    }
   

   (table A keeps a not unique foreignkey_B)

2. One-to-Many This association type is the reverse type of Many-to-One association type. Hence, we can use the same example to explain. And the below data model definition is for table B:

   Data Model Definition for table B:

    {
        "model": "department",
        "storageType": "mongodb",
        "attributes": {
            "department_id": "String",
            "department_name": "String"
        },
        "associations": {
            "employees": {
                "type": "one_to_many",
                "implementation": "foreignkeys",
                "reverseAssociation": "department",
                "target": "employee",
                "targetKey": "department_id",
                "keysIn": "employee",
                "targetStorageType": "mongodb"
            }
        },
        "internalId": "department_id",
        "id": {
            "name": "department_id",
            "type": "String"
        },
        "useDataLoader": false
    }
   

3. Many-to-Many

   Based on the previous example, this type of relationship underlines the fact, that an employee can belong to more than one department as well as the department can incorporate more than one employee. In this case, a new relation table has to be created that would hold the foreign-key pairs that define employee-to-department associations.

   Data Model Definition for table A:

    {
      "model": "employee",
      "storageType": "mongodb",
      "attributes": {
          "employee_id": "String",
          "name": "String",
          "department_ids": "[String]"
      },
      "associations": {
          "departments": {
              "type": "many_to_many",
              "implementation": "foreignkeys",
              "reverseAssociation": "employees",
              "target": "department",
              "targetKey": "employee_ids",
              "sourceKey": "department_ids",
              "keysIn": "employee",
              "targetStorageType": "mongodb"
          }
      },
      "internalId": "employee_id",
      "id": {
          "name": "employee_id",
          "type": "String"
      },
      "useDataLoader": true
    }
   

   Data Model Definition for table B:

    {
        "model": "department",
        "storageType": "mongodb",
        "attributes": {
            "department_id": "String",
            "department_name": "String",
            "employee_ids": "[String]"
        },
        "associations": {
            "employees": {
                "type": "many_to_many",
                "implementation": "foreignkeys",
                "reverseAssociation": "departments",
                "target": "employee",
                "targetKey": "department_ids",
                "sourceKey": "employee_ids",
                "keysIn": "department",
                "targetStorageType": "mongodb"
            }
        },
        "internalId": "department_id",
        "id": {
            "name": "department_id",
            "type": "String"
        },
        "useDataLoader": false
    }
   

   (AB relation table with foreignkey_A and foreignkey_B is created automatically)

4. One-to-One

   This relation type contains restriction: an absence of the possibility to relate more than one pair of the elements. In this case it is a subject of intuition which table shall hold the foreign-key. However, the foreign-keys can’t repeat and shall be unique.

   For example, the A table is a catalog of well studied species that have strict registration number, whereas table B contains all discovered species, some of which are not cataloged yet.

   Data Model Definition for table B:

    {
        "model": "discovered_specie",
        "storageType": "mongodb",
        "attributes": {
          "discovered_specie_id": "String",
          "studied_specie_id": "String"
        },
        "associations": {
          "unique_studied_specie": {
            "type": "one_to_one",
            "implementation": "foreignkeys",
            "reverseAssociation": "unique_discovered_specie",
            "target": "studied_specie",
            "targetKey": "studied_specie_id",
            "keysIn": "discovered_specie",
            "targetStorageType": "mongodb"
          }
        },
        "internalId": "discovered_specie_id",
        "id": {
            "name": "discovered_specie_id",
            "type": "String"
        }
    }
   

   (table B keeps a unique foreignkey_A)

The code generator at work

The code generator receives the specification as described above and generates resolvers and models from it. In this section we take a look at the code that is generated from the different attributes that can be given by the JSON specification.

The model contains a constant named definition that contains the full JSON specification. The following placeholders will be used from now on (references to the JSON file included):

• <model>: The respective main record type (model)
• <models>: The pluralized version of <model>
• <NAME>: The name of the association as given in the JSON file (respective key in the object associations)
• <assoc>: The name of the associated record (<NAME> with capitalization and plural forms properly applied)
• <assocID>: The association ID (associations -> <NAME> -> targetKey)
• <ModelIDAttribute>: The name of the ID attribute (internalId if present, otherwise “id”)
• <assocIDValue>: The actual value of <assocID>
• <CrossTable>: The name of the SQL cross table (associations -> <NAME> -> keysIn)

The only storage type that is examined here is sql (storageType for the main record, associations -> <NAME> -> targetStorageType for the association).

The model itself

First we observe the methods that are created for handling the model itself with no regard yet to the associations.

The following methods are created in the resolver:

The following methods are created in the model:

For all associations

In the resolver the following entries are created:

In the model a method static associate(models) is created, which is filled for each association type as outlined below.

Association type x_to_many

For this association the following methods are created in the resolver:

In the model file, an entry is added to the method associate(models) in the form <model>.hasMany(models.<assoc>, {as: <assoc>, foreignKey: <assocID>}). The model methods that are called from the resolver are in the associated model (which holds the association key).

Association type x_to_one

For this association the following methods are created in the resolver:

In the model the following entries are created:

Association type many_to_many through sql_cross_table implementation

In this case an additional record type is introduced that contains the SQL cross table, so there are 3 models and 3 resolvers to consider. Unlike the former two cases, this one is fully symmetric. Both records involved define this type of connection with the name of the cross table as keysIn. The source key for both types is the ID of the own record, and the target key is the ID of the other record. Because of the symmetry, only 4 files must be considered.

In each record resolver, the following entries are created:

In each record model, the following entries are created:

Cross Table Resolver / Model

Normal resolver / model files are created, where the respective model type has no associations of its own so that these files contain only the methods from the model itself.

Authorization-checks and record limits

Not every access to Zendro is permitted. In many cases (see above), the user must be authorized to perform a certain action. Possible authorizations for a given table include read, create, delete, update. These authorizations with respect to tables are connected to roles that users can have and are stored within the database.

Additionally, reading actions are refused if they access too many records. GraphQL is a very powerful data query and manipulation language that gives the user the control about what to query the server, but this makes it possible (by accident or malice) to make such a large query that the server cannot handle it. To prevent this, the server has a set limit of records that can be accessed by a single query and the user is required to provide pagination arguments in case of a readMany query.

Pagination types

As seen above, Zendro provides two different types of pagination: Limit-offset based and cursor based. Limit-offset based pagination is the better known one, where the user provides an offset (how many entries to skip) and a limit (how many entries to display on one page).

Limit-offset based pagination is not possible for distributed data models, i.e. models where records of a single table are spread over different servers. This is because the client (who makes the request) does not know how the entries are distributed, so the offset for the different servers cannot be provided. Since this data model is especially useful for Big Data, it is not feasible to request the entire table and paginate the data on the client side.

Instead, a different model has to be used. If the different servers are told which was the last entry “above” the requested page (a cursor), they can deliver the content that would follow up to it and the client can now get the received data in order. This type of pagination works for any kind of data, although it is more complex (the entries for the table are found under edges[] -> node).

Data Loader

When reading a record by its id, by default Zendro uses a data loader to improve read performance. It does so by bundling IDs to be fetched and request those in one composite query.

Here is an example for fetching multiple records within one request:

{
  n0: readOneAccession(accession_id: "a-instance1") {
    accession_id
    collectors_name
    location(search:null){
      locationId
    }
  }
  n1: readOneAccession(accession_id: "b-instance1") {
    accession_id
    collectors_name
    location(search:null){
      locationId
    }
  }
  n2: readOneAccession(accession_id: "c-instance1") {
    accession_id
    collectors_name
    location(search:null){
      locationId
    }
  }
}

In the above example querying the associated location for each readOneAccession query invokes the location root resolver to search for associated records. By using the data loader we can collect all readById requests to the accession and the location model and optimize the queries to fetch those Ids together. This reduces the amount of executed queries from six to two.

Table of contents

• Resolver and Model Layers
• Data validation
• Cassandra storageType