@ocvibe/fdp

FDP Schema

FDP Schema defines the structure of data stored in the database. The FDP engine will use these schema files to generate a Graphql API to federate reading across multiple databases.

Schemas and namespaces

Fdp schemas can be scoped to a single namespace. schemas can not reference objects across namespaces. This can be used to configure objects local to a specific domain, or to create .separate versions of a schema.

schemas within a namespace can reference objects in other schema definition files that are also in that namespace. Objects names within a namespace must be unique. The database mapping system can be used to stored objects in a single schema file to different instances of the that database type.

Schema Definitions

Each schema definition file contains a json object that has a top level object. This objects has the following has the following structure

Prop	description	type	Required
ConfigType	define which fdp config file this is	"Schema"	yes
Name	name of this schema	string	yes
Namespace	namespace this schema belongs to	string	yes
Objects	array of object definitions	Object array	yes

Each schema contains a list of objects. the objects have the following fields

Object Definition

Prop	description	type	Required
Names	unique name of this object	string	yes
Fields	list of field definitions	Field array	yes
PrimaryKey	field to use as primary key	String	yes

Embedded flag is for objects that only used embedded in other objects. FDp will generate CRUD operations for these objects. object embedding is defined at the relationship definition. The PrimaryKey is not required to a field that is exposed by this object. If it is not, it cannot be returned in responses.

Each object can have a list of field definitions

Field Definition

Prop	description	type	Required
Name	unique name of this object	string	yes
Type	field type [Int,String,Float,Boolean,Object,Array]	Field array	yes
Array	Options if field is of type Array	ArrayOptions	no
Object	Options if field is of type Object	ObjectOptions	no
Options	Field Options	FieldOptions	no

If the field is of type array or object, there are additional options to set. if field is of type Array - Array field is required. If field is of type Object the the Object field is required.

Field Options

Prop	description	type	Required
Nullable	set field to be allowed to be null (default: true)	boolean	no

Object Options / Reference

if a field is of type object, this represents a reference and the object it references must exists in the namespace. Objects are reference by their Name field

Prop	description	type	Required
Reference	Object Name that field is referencing	string	yes
Relationship	relationship type: [oneToOne,oneToMany,manyToMany,manyTomany]	string	yes
Embedded	embedded directly in parent object (mongodb only)	boolean	no

Array Options

Prop	description	type	Required
Type	array type [Int,String,Float,Boolean,Object,Array]	string	yes
Object	Options if field is of type Object	ObjectOptions	yes
Nullable	are objects in the array nullable (default: true)	boolean	no

an example schema

{
    "ConfigType": "Schema",
    "Namespace": "projectA",
    "Name": "acl",
    "Objects": [
      {
        "Name": "User",
        "PrimaryKey": "id",
        "Fields": [
          {
            "Name": "Name",
            "Type": "String",
            "Options": {
              "Nullable": false
            }
          },
          {
            "Name": "Active",
            "Type": "Boolean",
            "Options": {
              "Nullable": false
            }
          },
          {
            "Name": "Age",
            "Type": "Int",
            "Options": {
              "Nullable": false
            }
          },
          {
            "Name": "id",
            "Type": "Int"
          },
          {
            "Name": "Role",
            "Type": "Object",
            "Object": {
              "Reference": "Role",
              "Relation": "manyToOne"
            }
          }
        ]
      },
      {
        "Name": "Role",
        "PrimaryKey": "id",
        "Fields": [
          {
            "Name": "Name",
            "Type": "String",
            "Options": {
              "Nullable": false
            }
          },
          {
            "Name": "id",
            "Type": "Int"
          },
          {
            "Name": "Permissions",
            "Type": "Array",
            "Array": {
              "Type": "Object",
              "Object": {
                "Reference": "Permission",
                "Relation": "manyToMany"
              }
            }        
          },
          {
            "Name": "Users",
            "Type": "Array",
            "Array": {
              "Type": "Object",
              "Object": {
                "Reference": "User",
                "Relation": "oneToMany"
              }
            }
          }
        ]
      },
      {
        "Name": "Permission",
        "PrimaryKey": "id",
        "Fields": [
          {
            "Name": "Name",
            "Type": "String",
            "Options": {
              "Nullable": false
            }
          },
          {
            "Name": "id",
            "Type": "Int"
          },
          {
            "Name": "Roles",
            "Type": "Array",
            "Array": {
              "Type": "Object",
              "Object": {
                "Reference": "Role",
                "Relation": "manyToMany"
              }
            }
          }
        ]
      }
    ]
  }

Database mapping

Objects are mapped to database using the following key structure (global.namespace.schema.object). priority for which database an object is in, is based on this keys specificity. For example if global.namespace1 is set to mongodb1 and global.namespace1.schema1.object3 is set to mongodb2, then all objects in schema1 and namespace1 are set found in mongodb1, but object3 is location in mongodb2

Database schemas

The FdpQuery layer converts schema definition files to database queries. It expects databases to be structured a certain way. The following are the database schema structures for SQl and mongodb databases

MySql

Objects

MySQl objects are stored in their own table, named $${object_name(s)}

Below is a table representing the data type conversions between FDP and MySQL:

FDP Data Type	Representation	MongoDB Data Type
FdpType.INT	Represents integer values	INT
FdpType.STRING	Represents string values	VARCHAR
FdpType.FLOAT	Represents floating-point numbers	DOUBLE
FdpType.BOOLEAN	Represents boolean values	BOOLEAN or TINYINT
FdpType.OBJECT	Represents complex objects with multiple fields	Not directly supported. Typically stored as JSON or in a related table.
FdpType.ARRAY	Represents arrays of values	Not directly supported. Typically stored in a related table with a one-to-many relationship.
FdpType.JSON_OBJECT	Represents JSON objects	Not directly supported. Typically stored as JSON or in a related table.
FdpType.DATE	Represents date values	DATE
FdpType.DATETIME	Represents date and time values	DATETIME
FdpType.TIMESTAMP	Represents date and time values, stored as seconds since the Unix epoch	TIMESTAMP

Relationships

Relationship	ID Column	Foreign ID Column	Foreign Table Name
OneToOne	${field_name}_id	NA	${object_name}(s) - lowercase
ManyToOne	${field_name}_id	NA	${object_name}(s) - lowercase
OneToMany	${field_name}_id	NA	${reference_object_name}(s) - lowercase
ManyToMany	${object_name}_id	${reference_object_name}_id	${objectN_name}_${objectN_name} - alphabetical order

• OneToOne

  • Objects stored in one to one relationships will store a reference to the objects id field in the column ${field_name}_id} in the objects table
  • Objects stored in one-to-one relationships will store a reference to the object's id field in the column ${field_name}_id in the object's table.
  • For example, the User object has a one-to-one relationship with the Role object, as indicated by the User object's role_id field containing the _id of the related Role object.

• ManyToOne

  • Objects stored in one-to-one relationships will store a reference to the object's id field in the column ${field_name}_id in the object's table.
  • For example, the User object has a one-to-one relationship with the Role object, as indicated by the User object's role_id field containing the _id of the related Role object.

• oneToMany

  • Objects stored in one-to-many relationships will store references to the objects' ids in an array field called ${field_name}_id(s).
  • In MySQL, this is typically implemented by storing an array of _ids of related objects in a field called ${field_name}_id(s) within the object's table.
  • For example, the Role object has a one-to-many relationship with the User object, as indicated by the Role object's user_ids field containing an array of _ids of related User objects.

• ManyToMany

  • Objects stored in many-to-many relationships will store references to the objects' ids in a join table. Fdp query expects a join table called ${object1_name(s)}_${object2_name(s)} with columns ${object1}_id and ${object2}_id. The objects' names in the join table name will be pluralized and sorted alphabetically.
  • For example, if there's a many-to-many relationship between Role and Permission objects, the join table might be named permissions_roles, with columns permission_id and role_id.

MSSQL

Objects

MSSQL objects are stored in their own table, named ${object_name(s)}.

Below is a table representing the data type conversions between FDP and MSSQL:

FDP Data Type	Representation	MSSQL Data Type
FdpType.INT	Represents integer values	INT
FdpType.STRING	Represents string values	NVARCHAR
FdpType.FLOAT	Represents floating-point numbers	FLOAT
FdpType.BOOLEAN	Represents boolean values	BIT
FdpType.OBJECT	Represents complex objects with multiple fields	Not directly supported. Typically stored as JSON or in a related table.
FdpType.ARRAY	Represents arrays of values	Not directly supported. Typically stored in a related table with a one-to-many relationship.
FdpType.JSON_OBJECT	Represents JSON objects	Not directly supported. Typically stored as JSON or in a related table.
FdpType.DATE	Represents date values	DATE
FdpType.DATETIME	Represents date and time values	DATETIME
FdpType.TIMESTAMP	Represents date and time values, stored as seconds since the Unix epoch	DATETIME2

Relationships

Relationship	ID Column	Foreign ID Column	Foreign Table Name
OneToOne	${field_name}_id	NA	${object_name}(s) - lowercase
ManyToOne	${field_name}_id	NA	${object_name}(s) - lowercase
OneToMany	${field_name}_id	NA	${reference_object_name}(s) - lowercase
ManyToMany	${object_name}_id	${reference_object_name}_id	${objectN_name}_${objectN_name} - alphabetical order

Relationships

• OneToOne

  • Objects stored in one-to-one relationships will store a reference to the object's id field in the column ${field_name}_id in the object's table.
  • For example, the User object has a one-to-one relationship with the Role object, as indicated by the User object's Role field containing the id of the related Role object.

• ManyToOne

  • Objects stored in many-to-one relationships will store a reference to the object's id field in the field ${object_name}_id.
  • For example, the User object has a many-to-one relationship with the Role object, as indicated by the User object's Role field containing the id of the related Role object.

• OneToMany

  • Objects stored in one-to-many relationships will store references to the objects' ids in an array field called ${field_name}_id(s).
  • In MSSQL, this is typically implemented by using a foreign key constraint. For example, the Role object has a one-to-many relationship with the User object, as indicated by the Role object's Users field containing an array of ids of related User objects.

• ManyToMany

  • Objects stored in many-to-many relationships will store references to the objects' ids in a join table.
  • In MSSQL, this is typically implemented by creating a join table with foreign key constraints to both related tables. For example, the Role object has a many-to-many relationship with the Permission object, as indicated by a join table RolePermissions containing foreign keys to both Role and Permission tables.

Embedded objects

Embedded objects require an _id just like top-level objects. When using federated objects in MSSQL and MongoDB databases, the referenced schema must follow the origin object's database reference schema. Relationships are not automatically bidirectional. For example, if object A is stored in MSSQL and object B is stored in MongoDB, and object A has a one-to-one relationship with object B, object A will have a column in its table that holds the ID of object B. If object B wants to reference object A, it must also contain a reference field in its document schema that references object A's id.

MongoDB

Objects

MongoDB objects must contain a _id field that is unique to each object. This field is automatically generated by MongoDB if not provided explicitly. Each object can have multiple fields, each representing a different attribute or property of the object.

Below is a table representing the data type conversions between FDP and MongoDB:

FDP Data Type	Representation	MongoDB Data Type
FdpType.INT	Represents integer values	Int32
FdpType.STRING	Represents string values	String
FdpType.FLOAT	Represents floating-point numbers	Double
FdpType.BOOLEAN	Represents boolean values	Boolean
FdpType.OBJECT	Represents complex objects with multiple fields	Object
FdpType.ARRAY	Represents arrays of values	Array
FdpType.JSON_OBJECT	Represents JSON objects	Object
FdpType.DATE	Represents date values	Date
FdpType.DATETIME	Represents date and time values	Date
FdpType.TIMESTAMP	Represents date and time values, stored as seconds since the Unix epoch	Date

Relationships

Relationship	ID Column	Foreign ID Column	Foreign Table Name
FdpRelationship.ONE_TO_ONE	uses object id column, {field_name}_id	NA	uses object table
FdpRelationship.MANY_TO_ONE	uses object id column, {field_name}_id	NA	uses object table
FdpRelationship.ONE_TO_MANY	uses object id column, {field_name}_id	NA	uses object table
FdpRelationship.MANY_TO_MANY	uses object id column, {field_name}_id	NA	uses object table

• OneToOne

  • Objects stored in one to one relationships will store a reference to the object's id field in the column ${field_name}_id} in the object
  • For example, the User object has a one-to-one relationship with the Role object, as indicated by the User object's Role field containing the _id of the related Role object.

• ManyToOne

  • Objects stored in many to one relationships will store a reference to the object's id field in the field ${object_name}_id}
  • For example, the User object has a many-to-one relationship with the Role document, as indicated by the User object's Role field containing the _id of the related Role document.

• oneToMany

  • Objects stored in one to many relationships will store references to the objects' ids in an array field called ${field_name}_id(s)}
  • In MongoDB, this is typically implemented by storing an array of _ids of related objects in a field called ${field_name}_id(s)} within the object. For example, the Role object has a one-to-many relationship with the User object, as indicated by the Role object's Users field containing an array of _ids of related User object.

• ManyToMany

  • Objects stored in many to many relationships will store references to the objects' ids in an array field called ${field_name}_id(s)}
  • MongoDB does not directly support many-to-many relationships, but they can be implemented using arrays of _ids in objects. For example, the Role object has a many-to-many relationship with the Permission object, as indicated by the Role object's Permissions field containing an array of _ids of related Permission objects.

Embedded objects

embedded objects require a _id or PrimaryKey just like top level objects

Parquet Files

Objects

Parquet objects are stored in their own files.

Below is a table representing the data type conversions between FDP and Parquet:

FDP Data Type	Representation	Parquet Data Type
FdpType.INT	Represents integer values	INT32, INT64, INT96, INT_8, INT_16, INT_32, INT_64, UINT_8, UINT_16, UINT_32, or UINT_64
FdpType.STRING	Represents string values	UTF8
FdpType.FLOAT	Represents floating-point numbers	DOUBLE or FLOAT
FdpType.BOOLEAN	Represents boolean values	BOOLEAN
FdpType.OBJECT	Represents complex objects with multiple fields	Not currently supported.
FdpType.ARRAY	Represents arrays of values	Not currently supported.
FdpType.JSON_OBJECT	Represents JSON objects	Not currently supported.
FdpType.DATE	Represents date values	Not currently supported.
FdpType.DATETIME	Represents date and time values	Not currently supported.
FdpType.TIMESTAMP	Represents date and time values, stored as seconds since the Unix epoch	Not currently supported

Relationships

Relationships are not currently supported in Parquet Files.

Error Handling

| Error Type | Result | | Missing Column in file | The missing field will be returned as null. | | Extra Column in file | The extra column will not be returned in queries. The queries will behave normally. | | Mismatched type between file and FdpSchema | The data will be casted to the type in FdpSchema if possible. If that is not possible then an error will be thrown. | | Corrupt or Unreadable file | An error will be logged with the file path of the corrupt or unreadable file. |

Federated

objects in fdp can reference other objects in different databases, the reference schema must follow the origin objects db reference schema. Relationships are not automatically bi directional. For example object A is stored in my sql and object B is stored in Mongo. Object A has a one to one relationship with object b. Object A will have a column in its table that holds the ObjectID of object B. If object B wants to reference object A it must also contain a reference field in its document schema that references object A's id.

Cross-Database Support in FDP

FDP (Federated Data Platform) offers robust support for cross-database queries, allowing users to seamlessly query data across multiple database systems. By leveraging GraphQL as the query language and employing intelligent query translation mechanisms, FDP enables users to interact with disparate databases as if they were a single unified data source.

Database Compatibility

FDP supports cross-database queries between the following database systems:

• MongoDB
• MySQL
• MSSQL (Microsoft SQL Server)

This broad compatibility enables users to federate data stored in different database technologies and perform integrated queries across diverse datasets.

Query Translation

FDP translates GraphQL queries into database-specific queries tailored to each supported database system. This translation process ensures that queries are optimized for efficient execution on the target database, taking into account database-specific syntax, optimization techniques, and query execution strategies.

Data Mapping

FDP seamlessly maps FDP schema types to native database types for each supported database system. This includes mapping common data types such as integers, strings, dates, and boolean values, as well as complex data structures like arrays and JSON objects. By maintaining consistent mappings across databases, FDP ensures data integrity and interoperability.

Join Operations

FDP handles join operations for queries involving multiple databases by intelligently orchestrating data retrieval and joining results across database boundaries. This includes performing efficient cross-database joins and aggregations to combine data from different sources and present a unified view to the user.

One-to-One Relationship

Consider a scenario where an e-commerce platform stores customer information in a MySQL database (customer_db) and primary address information in a MongoDB database (address_db).

• MySQL Database (customer_db): Contains: Customer information such as customer id, name, and a reference to the primary address. Customer Table: Holds records for each customer, including a foreign key reference to the primary address.

• MongoDB Database (address_db): Contains: Addresses of customers, with each address having a unique id, street, city, and country information. Address Collection: Stores documents representing addresses, each uniquely identified by an address id.

The GraphQL query fetches the one-to-one relationship data through the field nested within the customers object, which is primaryAddress.

query {
  customers {
    id
    name
    primaryAddress {
      id
      street
      city
      country
    }
  }
}

This GraphQL query orchestrates the retrieval of customer information from the MySQL database (customer_db) and primary address information from the MongoDB database (address_db). The nested field primaryAddress ensures each customer's primary address is seamlessly joined with their data.

One-to-Many Relationship

Consider a scenario where each customer can have multiple orders. Customer information is stored in a MySQL database (customer_db), and order information is stored in a MongoDB database (order_db). Each order is associated with a customer through a foreign key reference.

• MySQL Database (customer_db): Contains: Customer information such as customer id, name, and a reference to orders. Customer Table: Holds records for each customer, including a foreign key reference to orders.

• MongoDB Database (order_db): Contains: Order information such as order id, date, and total amount. Order Collection: Stores documents representing orders, each uniquely identified by an order id.

The GraphQL query fetches customer details along with their associated orders.

query {
  customers {
    id
    name
    orders {
      id
      date
      totalAmount
    }
  }
}

This GraphQL query orchestrates the retrieval of customer information from the MySQL database (customer_db) and order information from the MongoDB database (order_db). The nested field orders ensures that all orders related to each customer are fetched in a single query.

Many-to-One Relationship

Consider a scenario where multiple orders can belong to a single customer. Customer information is stored in a MySQL database (customer_db), and order information is stored in a MongoDB database (order_db). Each order is associated with a customer through a foreign key reference.

• MySQL Database (customer_db): Contains: Customer information such as customer id, name. Customer Table: Holds records for each customer.

• MongoDB Database (order_db): Contains: Order information such as order id, date, total amount, and a foreign key reference to the customer. Order Collection: Stores documents representing orders, each uniquely identified by an order id. The GraphQL query fetches order details along with the associated customer information.

query {
  orders {
    id
    date
    totalAmount
    customer {
      id
      name
    }
  }
}

This GraphQL query orchestrates the retrieval of order information from the MongoDB database (order_db) and associated customer information from the MySQL database (customer_db). The nested field customer ensures that each order's corresponding customer details are seamlessly joined with the order data.

Many-to-Many Relationship

Consider a scenario where multiple customers can belong to multiple categories. Customer information is stored in a MySQL database (customer_db), and category information is stored in another MySQL database (category_db). The relationship between customers and categories is maintained through a junction table in the customer_db.

• MySQL Database (customer_db): Contains: Customer information such as customer id, name. Customer Table: Holds records for each customer. Customer Category Junction Table: Establishes the many-to-many relationship between customers and categories.

• MongoDB Database (category_db): Contains: Category information such as category id, name. Category Table: Stores records for each category.

The GraphQL query fetches customer details along with the associated categories.

query {
  customers {
    id
    name
    categories {
      id
      name
    }
  }
}

This GraphQL query orchestrates the retrieval of customer information from the MySQL database (customer_db) and associated category information from the MongoDB database (category_db). The nested field categories ensures that each customer's related categories are seamlessly joined with the customer data.

Schema Evolution

FDP provides robust support for schema evolution in a federated environment, allowing users to evolve schemas across different databases while maintaining compatibility and consistency. Schema changes are seamlessly propagated and synchronized across databases, ensuring that queries remain valid and data remains accessible even as schemas evolve over time.

Performance Optimization

To optimize query performance, FDP offers a range of optimization techniques and best practices. This includes indexing strategies, query optimization hints, caching mechanisms, and distributed query execution optimizations to minimize latency and maximize throughput for cross-database queries.

Filtering

FDP supports a generic filtering syntax that is converted into db specific filtering syntax for each object. This syntax allows you to build complex filters and only return documents which match the specified criteria.

FDP creates a get{Objects}(filter,options) query call for each object in the graphql schema.

Comparison Operators

Comparison operators return data based on value expressions. Multiple comparison operators in a single filter object are joined using AND.

Operator	Description
{value}	Matches values that are equal to a {value}.
$eq	Matches values that are equal to a specified value. [default operator]
$gt	Matches values that are greater than a specified value.
$gte	Matches values that are greater than or equal to a specified value.
$in	Matches any of the values specified in an array.
$lt	Matches values that are less than a specified value.
$lte	Matches values that are less than or equal to a specified value.
$ne	Matches all values that are not equal to a specified value.
$nin	Matches none of the values specified in an array.

Date comparisons will depend on the underlying database type and FDP schema type.

Logical Operators

Logical Operators allow you to combine multiple comparison operators into a single operation.

Operator	Description	Format
$and	Joins array of filter expressions with a logical AND.	$and: [filter, ...]
$not	Inverts the effect of a filter expression.	$not: {filter}
$nor	Joins array of filter expressions with a logical NOR.	$nor: [filter, ...]
$or	Joins filter expressions with a logical OR.	$or: [filter, ...]

Options

Additional operations can be applied to queries using the options field of the Get{Objects} query.

distinct

Distinct takes an array of fields and only returns unique records for those fields.

**Only distinct fields can be returned, all other fields requested will return null.

lookup

Lookup applies join operations to query, returning only rows that satisfy the join expression. Lookup operations do NOT modify the fields returned, only the rows to be returned.

Operator	Description
$inner	TODO inner
$outer	TODO outer
$left	TODO left
$right	TODO right

Lookup Parameters

The left side of the join is the object being requested. The right side of the join is defined by the lookup parameters. The records are joined using the left_field and right_field equality.

Field	Description
left_field	Field on object
right_schema	(SQL only) Schema name
right_table	Collection/table to join
right_field	Field on right table to match with object field (left_field)

Examples

type MobileOrder {
  profile_id: String
  meta_api: String
  average_purchase_amount: Float
  seat: Int
  total_mobile_count: Int
  id: Int
  order_type: String
  event_date: String
}

Get{Objects}() query with filter and option arguments for MobileOrders

query GetMobileOrders($filters: JSONObject, $options: JSONObject) {
  GetLthsMobileOrders(filters: $filters, options: $options) {
    profile_id
    meta_api
    average_purchase_amount
    seat
    total_mobile_count
    event_date
    order_type
    id
  }
}

field equality example

{
  "filters": {
    "event_date": "2023-05-01",
    "order_type": "food",
  
  },
  "options": {}
}

comparison operators

{
  "filters": {
    "event_date": {
      "$lt": "2023-05-01",
      "$gt": "2023-04-12"
    },
    "seat": {
      "$in": [ 100,200,330]
    },
    "average_purchase_amount": {
      "$ge": 25.99
    }
  },
  "options": {}
}

logical operators

{
  "filters": {
    "$or": [
      { 
        "event_date": {
          "$lt": "2023-05-01",
          "$gt": "2023-04-12"
        }
      },{
         "event_date": {
          "$lt": "2021-08-01",
          "$gt": "2020-01-12"
        }
      }
    ]   
  },
}

lookup

{
  "options": {
    "lookup": {
      "$inner": {
        "left_field": "vendor_id",
        "right_schema": "sales",
        "right_table": "vendor",
        "right_field": "id",
      }
    }
  }
}

distinct

{
  "options": {
    "distinct": ["order_type","event_date"]
  }
}