CRUD For Attributes & Changeover Matrix: A Comprehensive Guide

by Alex Johnson 63 views

In this comprehensive guide, we will explore the implementation of CRUD (Create, Read, Update, Delete) operations for attributes and changeover matrices within a system. This functionality is crucial for managing configurable elements, such as attributes (e.g., Color, Thickness) and their corresponding changeover times between different groups (e.g., Laser Cutting, Powder Coating). This guide will provide a detailed walkthrough of the process, covering everything from defining the GraphQL schema to implementing resolvers and ensuring the proper handling of mutations and queries.

Understanding the Scope: Entities and Their Relationships

Before diving into the technical implementation, it's important to understand the entities involved and their relationships. We will be working with five key database entities, each playing a vital role in the attribute and changeover management system.

  1. Attributes: This entity represents the characteristics or properties that can be configured, such as color, material, or thickness. Each attribute has a unique ID and a name (e.g., "Color", "Thickness").
  2. Attribute Parameters: These are the specific values or options for each attribute. For example, the "Color" attribute might have parameters like "Red", "Green", and "Blue". Each parameter has an ID, a value (e.g., "Red"), an optional note, and a reference to its parent attribute.
  3. Changeover Groups: These represent categories or groups of operations, processes, or machines between which changeovers occur. Examples include "Laser Cutting", "Powder Coating", or "Assembly". Each group has an ID and a name.
  4. Changeover Times: This entity stores the base changeover times for a specific combination of a changeover group and an attribute. It represents the time required to switch between different attributes within a particular group. Each record includes an ID, the changeover time (in a suitable unit like minutes), a reference to the changeover group, and a reference to the attribute.
  5. Changeover Data: This entity provides detailed changeover times between specific attribute parameters within a changeover group. It represents a matrix of changeover times, where each cell indicates the time required to switch from one parameter to another. Each record includes an ID, the setup time, references to the changeover group and attribute, and references to the "from" and "to" attribute parameters.

Understanding these entities and their relationships is crucial for designing the GraphQL schema and implementing the resolvers.

Step-by-Step Implementation Guide

Now, let's dive into the technical details of implementing the CRUD operations for these entities. We'll start by updating the GraphQL schema, then move on to implementing the resolvers, and finally discuss the acceptance criteria for the implementation.

1. Updating the GraphQL Schema

The first step is to define the GraphQL schema, which acts as a contract between the client and the server. The schema specifies the types, queries, and mutations that are available. We need to extend the existing schema with definitions for the five entities mentioned above.

Defining Types

The GraphQL types represent the structure of the data that will be exchanged between the client and the server. For each entity, we define a corresponding type with fields that match the database schema.

type Attribute {
  id: Int!
  name: String!
  parameters: [AttributeParameter!]!
}

type AttributeParameter {
  id: Int!
  attributeValue: String!
  attributeNote: String
  attributeId: Int!
  attribute: Attribute!
}

type ChangeoverGroup {
  id: Int!
  name: String!
}

type ChangeoverTime {
  id: Int!
  changeoverTime: Float!
  changeoverGroupId: Int!
  attributeId: Int!
  attribute: Attribute
  changeoverGroup: ChangeoverGroup
}

type ChangeoverData {
  id: Int!
  setupTime: Float!
  changeoverGroupId: Int!
  attributeId: Int!
  fromAttrParamId: Int!
  toAttrParamId: Int!
  
  # Expanded relations for UI display
  fromAttributeParameter: AttributeParameter
  toAttributeParameter: AttributeParameter
}
  • Attribute: Represents an attribute with an ID, name, and a list of associated parameters.
  • AttributeParameter: Represents a parameter for an attribute, including its ID, value, optional note, and the ID of the parent attribute.
  • ChangeoverGroup: Represents a changeover group with an ID and a name.
  • ChangeoverTime: Represents the base changeover time for a group-attribute combination, including the time, group ID, and attribute ID.
  • ChangeoverData: Represents the detailed changeover time between specific attribute parameters, including the setup time and IDs for the group, attribute, and "from" and "to" parameters.

Defining Queries

Queries are used to fetch data from the server. We need to define queries for retrieving all attributes, a single attribute by ID, attribute parameters for a specific attribute, all changeover groups, a single changeover group by ID, changeover times for a specific group, and changeover data for a specific group and attribute.

type Query {
  # ... existing queries ...

  # --- Attributes & Parameters ---
  getAttributes: [Attribute!]!
  getAttribute(id: Int!): Attribute
  getAttributeParameters(attributeId: Int!): [AttributeParameter!]!

  # --- Changeover Groups ---
  getChangeoverGroups: [ChangeoverGroup!]!
  getChangeoverGroup(id: Int!): ChangeoverGroup

  # --- Changeover Matrices ---
  """
  Fetches base times for a group (Matrix 1: Attribute vs Group)
  """
  getChangeoverTimes(changeoverGroupId: Int!): [ChangeoverTime!]!
  
  """
  Fetches detailed from-to times (Matrix 2: Param vs Param)
  """
  getChangeoverData(changeoverGroupId: Int!, attributeId: Int!): [ChangeoverData!]!
}
  • getAttributes: Retrieves all attributes.
  • getAttribute: Retrieves a specific attribute by its ID.
  • getAttributeParameters: Retrieves all parameters for a given attribute ID.
  • getChangeoverGroups: Retrieves all changeover groups.
  • getChangeoverGroup: Retrieves a specific changeover group by its ID.
  • getChangeoverTimes: Retrieves all changeover times for a given changeover group ID.
  • getChangeoverData: Retrieves all changeover data for a given changeover group ID and attribute ID.

Defining Mutations

Mutations are used to modify data on the server. We need to define mutations for creating, updating, and deleting attributes and changeover groups, as well as for setting changeover times and changeover data.

type Mutation {
  # ... existing mutations ...

  # --- Attributes ---
  createAttribute(name: String!): Attribute!
  updateAttribute(id: Int!, name: String!): Attribute!
  deleteAttribute(id: Int!): SuccessMessage!

  # --- Attribute Parameters ---
  createAttributeParameter(input: CreateAttributeParamInput!): AttributeParameter!
  updateAttributeParameter(input: UpdateAttributeParamInput!): AttributeParameter!
  deleteAttributeParameter(id: Int!): SuccessMessage!

  # --- Changeover Groups ---
  createChangeoverGroup(name: String!): ChangeoverGroup!
  updateChangeoverGroup(id: Int!, name: String!): ChangeoverGroup!
  deleteChangeoverGroup(id: Int!): SuccessMessage!

  # --- Changeover Matrix Operations ---
  """
  Upsert logic: Creates a new record or updates existing one based on Group + Attribute ID
  """
  setChangeoverTime(input: SetChangeoverTimeInput!): ChangeoverTime!

  """
  Upsert logic: Creates or updates based on Group + Attribute + FromParam + ToParam
  """
  setChangeoverData(input: SetChangeoverDataInput!): ChangeoverData!
}
  • createAttribute: Creates a new attribute with a given name.
  • updateAttribute: Updates an existing attribute with a given ID and name.
  • deleteAttribute: Deletes an attribute with a given ID.
  • createAttributeParameter: Creates a new attribute parameter.
  • updateAttributeParameter: Updates an existing attribute parameter.
  • deleteAttributeParameter: Deletes an attribute parameter.
  • createChangeoverGroup: Creates a new changeover group with a given name.
  • updateChangeoverGroup: Updates an existing changeover group with a given ID and name.
  • deleteChangeoverGroup: Deletes a changeover group with a given ID.
  • setChangeoverTime: Creates or updates a changeover time.
  • setChangeoverData: Creates or updates changeover data.

Defining Inputs

Inputs are used to pass complex data structures to mutations. We need to define inputs for creating and updating attribute parameters, as well as for setting changeover times and changeover data.

input CreateAttributeParamInput {
  attributeId: Int!
  attributeValue: String!
  attributeNote: String
}

input UpdateAttributeParamInput {
  id: Int!
  attributeValue: String
  attributeNote: String
}

input SetChangeoverTimeInput {
  changeoverGroupId: Int!
  attributeId: Int!
  time: Float!
}

input SetChangeoverDataInput {
  changeoverGroupId: Int!
  attributeId: Int!
  fromAttrParamId: Int!
  toAttrParamId: Int!
  setupTime: Float!
}
  • CreateAttributeParamInput: Input for creating a new attribute parameter, including the attribute ID, value, and optional note.
  • UpdateAttributeParamInput: Input for updating an existing attribute parameter, including its ID, value, and optional note.
  • SetChangeoverTimeInput: Input for setting a changeover time, including the changeover group ID, attribute ID, and time.
  • SetChangeoverDataInput: Input for setting changeover data, including the changeover group ID, attribute ID, "from" parameter ID, "to" parameter ID, and setup time.

2. Implementing Resolvers

Resolvers are functions that fetch data for the fields in the GraphQL schema. We need to implement resolvers for all the queries and mutations defined in the schema. To maintain a clean project structure, it is recommended to organize the resolvers into separate files based on their functionality.

Query Resolvers

We'll create two files for query resolvers: app/graphql/resolvers/query/attributes.js for attribute-related queries and app/graphql/resolvers/query/changeover.js for changeover-related queries.

app/graphql/resolvers/query/attributes.js
import { prisma } from "../../../prismaClient.js";

export const getAttributes = async () => {
  return await prisma.attribute.findMany({
    include: { attributeParameters: true }
  });
};

export const getAttribute = async (id) => {
  return await prisma.attribute.findUnique({
    where: { id },
    include: { attributeParameters: true }
  });
};

export const getAttributeParameters = async (attributeId) => {
    return await prisma.attrParam.findMany({ where: { attributeId } });
};
  • getAttributes: Fetches all attributes from the database, including their associated parameters.
  • getAttribute: Fetches a specific attribute by its ID, including its associated parameters.
  • getAttributeParameters: Fetches all parameters for a given attribute ID.
app/graphql/resolvers/query/changeover.js
import { prisma } from "../../../prismaClient.js";

export const getChangeoverGroups = async () => await prisma.changeoverGroup.findMany();
export const getChangeoverGroup = async (id) => await prisma.changeoverGroup.findUnique({ where: { id } });

export const getChangeoverTimes = async (changeoverGroupId) => {
    return await prisma.changeoverTime.findMany({ where: { changeoverGroupId } });
};

export const getChangeoverData = async (changeoverGroupId, attributeId) => {
    return await prisma.changeoverData.findMany({ 
        where: { changeoverGroupId, attributeId },
        include: { fromAttributeParameter: true, toAttributeParameter: true }
    });
};
  • getChangeoverGroups: Fetches all changeover groups.
  • getChangeoverGroup: Fetches a specific changeover group by its ID.
  • getChangeoverTimes: Fetches all changeover times for a given changeover group ID.
  • getChangeoverData: Fetches all changeover data for a given changeover group ID and attribute ID, including the associated "from" and "to" attribute parameters.

Mutation Resolvers

We'll create two files for mutation resolvers: app/graphql/resolvers/mutation/attributes.js for attribute-related mutations and app/graphql/resolvers/mutation/changeover.js for changeover-related mutations.

app/graphql/resolvers/mutation/attributes.js
import { prisma } from "../../../prismaClient.js";

export const createAttribute = async ({ name }) => {
  return await prisma.attribute.create({ data: { name } });
};

export const updateAttribute = async ({ id, name }) => {
  return await prisma.attribute.update({ where: { id }, data: { name } });
};

export const deleteAttribute = async ({ id }) => {
  await prisma.attribute.delete({ where: { id } });
  return { message: "Attribute deleted successfully" };
};

export const createAttributeParameter = async ({ input }) => {
  return await prisma.attrParam.create({ data: input });
};

export const updateAttributeParameter = async ({ input }) => {
  const { id, ...data } = input;
  return await prisma.attrParam.update({ where: { id }, data });
};

export const deleteAttributeParameter = async ({ id }) => {
  await prisma.attrParam.delete({ where: { id } });
  return { message: "Attribute Parameter deleted successfully" };
};
  • createAttribute: Creates a new attribute with the given name.
  • updateAttribute: Updates an existing attribute with the given ID and name.
  • deleteAttribute: Deletes an attribute with the given ID.
  • createAttributeParameter: Creates a new attribute parameter with the given input.
  • updateAttributeParameter: Updates an existing attribute parameter with the given input.
  • deleteAttributeParameter: Deletes an attribute parameter with the given ID.
app/graphql/resolvers/mutation/changeover.js
import { prisma } from "../../../prismaClient.js";

export const createChangeoverGroup = async ({ name }) => {
  return await prisma.changeoverGroup.create({ data: { name } });
};

export const updateChangeoverGroup = async ({ id, name }) => {
  return await prisma.changeoverGroup.update({ where: { id }, data: { name } });
};

export const deleteChangeoverGroup = async ({ id }) => {
  await prisma.changeoverGroup.delete({ where: { id } });
  return { message: "Changeover Group deleted successfully" };
};

export const setChangeoverTime = async ({ input }) => {
  const { changeoverGroupId, attributeId, time } = input;

  // Check if record exists
  const existing = await prisma.changeoverTime.findFirst({
    where: {
      changeoverGroupId,
      attributeId,
    }
  });

  if (existing) {
    // Update
    return await prisma.changeoverTime.update({
      where: { id: existing.id },
      data: { changeoverTime: time }
    });
  } else {
    // Create
    return await prisma.changeoverTime.create({
      data: {
        changeoverGroupId,
        attributeId,
        changeoverTime: time
      }
    });
  }
};

export const setChangeoverData = async ({ input }) => {
  const { changeoverGroupId, attributeId, fromAttrParamId, toAttrParamId, setupTime } = input;

  // Check if record exists
  const existing = await prisma.changeoverData.findFirst({
    where: {
      changeoverGroupId,
      attributeId,
      fromAttrParamId,
      toAttrParamId
    }
  });

  if (existing) {
    // Update
    return await prisma.changeoverData.update({
      where: { id: existing.id },
      data: { setupTime }
    });
  } else {
    // Create
    return await prisma.changeoverData.create({
      data: {
        changeoverGroupId,
        attributeId,
        fromAttrParamId,
        toAttrParamId,
        setupTime
      }
    });
  }
};
  • createChangeoverGroup: Creates a new changeover group with the given name.
  • updateChangeoverGroup: Updates an existing changeover group with the given ID and name.
  • deleteChangeoverGroup: Deletes a changeover group with the given ID.
  • setChangeoverTime: Creates or updates a changeover time using an upsert logic based on the group and attribute IDs.
  • setChangeoverData: Creates or updates changeover data using an upsert logic based on the group, attribute, and "from" and "to" parameter IDs.

3. Registering Resolvers

After implementing the resolvers, we need to register them with the GraphQL server. This is typically done in a resolvers.js file, where we import all the individual resolver functions and combine them into a single object.

// ... existing imports
import { 
  getAttributes, 
  getAttribute, 
  getAttributeParameters 
} from "./query/attributes.js";

import { 
  getChangeoverGroups,
  getChangeoverGroup,
  getChangeoverTimes,
  getChangeoverData
} from "./query/changeover.js";

import {
  createAttribute, 
  updateAttribute, 
  deleteAttribute,
  createAttributeParameter, 
  updateAttributeParameter, 
  deleteAttributeParameter
} from "./mutation/attributes.js";

import {
  createChangeoverGroup, 
  updateChangeoverGroup, 
  deleteChangeoverGroup,
  setChangeoverTime, 
  setChangeoverData
} from "./mutation/changeover.js";

export const resolvers = {
  Query: {
    // ... existing queries
    getAttributes: () => getAttributes(),
    getAttribute: (_, { id }) => getAttribute(id),
    getAttributeParameters: (_, { attributeId }) => getAttributeParameters(attributeId),
    
    getChangeoverGroups: () => getChangeoverGroups(),
    getChangeoverGroup: (_, { id }) => getChangeoverGroup(id),
    getChangeoverTimes: (_, { changeoverGroupId }) => getChangeoverTimes(changeoverGroupId),
    getChangeoverData: (_, { changeoverGroupId, attributeId }) => getChangeoverData(changeoverGroupId, attributeId),
  },
  Mutation: {
    // ... existing mutations
    createAttribute: (_, { name }) => createAttribute({ name }),
    updateAttribute: (_, { id, name }) => updateAttribute({ id, name }),
    deleteAttribute: (_, { id }) => deleteAttribute({ id }),

    createAttributeParameter: (_, { input }) => createAttributeParameter({ input }),
    updateAttributeParameter: (_, { input }) => updateAttributeParameter({ input }),
    deleteAttributeParameter: (_, { id }) => deleteAttributeParameter({ id }),

    createChangeoverGroup: (_, { name }) => createChangeoverGroup({ name }),
    updateChangeoverGroup: (_, { id, name }) => updateChangeoverGroup({ id, name }),
    deleteChangeoverGroup: (_, { id }) => deleteChangeoverGroup({ id }),

    setChangeoverTime: (_, { input }) => setChangeoverTime({ input }),
    setChangeoverData: (_, { input }) => setChangeoverData({ input }),
  },
  // Add Type Resolvers if relations need manual fetching
  Attribute: {
    parameters: (parent) => parent.attributeParameters || [] 
  }
};

This code imports all the resolver functions and organizes them into a resolvers object. The Query and Mutation fields of this object map the GraphQL queries and mutations to their corresponding resolver functions. Additionally, a type resolver is added for the Attribute type to handle the parameters field, which might require manual fetching of the related attribute parameters.

Acceptance Criteria

To ensure the successful implementation of the CRUD operations, we need to define clear acceptance criteria. These criteria serve as a checklist to verify that all requirements have been met.

  • [ ] The schema.graphql file is updated with all new types and operations, including the types for Attribute, AttributeParameter, ChangeoverGroup, ChangeoverTime, and ChangeoverData, as well as the queries and mutations for managing these entities.
  • [ ] Queries successfully retrieve data for Attributes, Parameters, and Changeover Groups. This includes verifying that the getAttributes, getAttribute, getAttributeParameters, getChangeoverGroups, getChangeoverGroup, getChangeoverTimes, and getChangeoverData queries return the correct data.
  • [ ] Mutations allow creating, updating, and deleting Attributes and Groups. This includes verifying that the createAttribute, updateAttribute, deleteAttribute, createChangeoverGroup, updateChangeoverGroup, and deleteChangeoverGroup mutations function as expected.
  • [ ] The setChangeoverTime mutation correctly updates the time if a record exists, or creates it if it doesn't. This ensures that the upsert logic for changeover times is working correctly.
  • [ ] The setChangeoverData mutation correctly handles the matrix cell updates (from-to values). This ensures that the upsert logic for changeover data is working correctly and that the matrix of changeover times between attribute parameters can be managed effectively.
  • [ ] The code is modularized into query/ and mutation/ directories. This ensures that the codebase is well-organized and maintainable.

Conclusion

Implementing CRUD operations for attributes and changeover matrices is crucial for managing configurable elements within a system. By following this comprehensive guide, you can effectively define the GraphQL schema, implement the resolvers, and ensure the proper handling of mutations and queries. This will enable you to create a robust and flexible system for managing attributes and their associated changeover times.

For more information on GraphQL and its implementation, you can visit the official GraphQL website. This resource provides a wealth of information, including documentation, tutorials, and best practices for working with GraphQL.