CSC/ECE 517 Fall 2025 - E2560. Framework for Import and Export

From Expertiza_Wiki
Jump to navigation Jump to search

Important Note

The user db schema is being changed soon. It will be removing the field full_name and adding the field username. Since our implementation mirrors fields from the db, the following will need to be changed:

  • In app/models/user.rb, change line 5 to match this: mandatory_fields :name, :email, :password, :username

Introduction

The export/import functionality is one of the most valuable tools for instructors when setting up assignments in Expertiza. Instructors often have lists of students, teams, and other data from their learning management systems. The ability to seamlessly export and import this data into Expertiza significantly reduces setup time and effort.

Objective

The previous version of Expertiza offered multiple export and import features—for example, exporting students, teams, rubrics, topics, and more. These features typically retrieve data from the database and save it in a specified format. However, the same logic is often duplicated across different implementations for various data types.

The goal of this project is to design and implement a generic export/import framework that can handle various types of data based on input parameters. It will create a module that accepts database table names and column names to determine what data should be exported or imported. This module should be flexible and reusable across all export/import features in Expertiza.

Approach

The challenge of this project was making sure our functionality was generic. It needed to be able to run for any class that required it, with the necessary fields and other configuration details. To handle this challenge, we decided to create an overall service that would handle all cases of importing and exporting. To facilitate this, we created a mixin for classes that want to be able to import and export. This mixin provides a place to declare mandatory fields for import/export, and provides functions to facilitate it. The service uses the functions to generically handle any case.

In the case of duplicate data being entered into the system (e.g., two users with the same username), we choose to go with a strategy approach. We created actions for handling duplicates that can be added to each class. Users can't choose between these actions in the frontend, and the chosen action is then performed if a duplicate is found. This initially includes skipping duplicates, changing the offending field, or updating the record. We have left it so that more actions can be created if there are other cases.

Design Document

Class Diagram

How It Fits Together

When importing a file (through import_controller.rb), the request passes the file, the class being imported to, the fields in order, a bool telling whether to use the headers in the CSV or not, and a selected duplicate action. The import service (Import.rb) takes these values and passes them to the #try_import_record function included in the importable_exportable_helper.rb. This function goes through each row and calls #import_row. Importing each row takes the values in the current row, looks for any associated ExternalClasses that the values reference, and creates an object based on the values. It then tries to save this object. If the object is a duplicate, it is marked as such and not saved to the database yet. If it is not a duplicate, it then tries to create any External classes as specified in the CSV file. For example, the User class looks up the ExternalClass Role, and the Item class creates the ExternalClass QuestionAdvice. Once all the rows have been created in this way, the duplicate records are passed back out to the service to be dealt with. Each duplicate record has its existing counterpart found in the database, and is then dealt with by the duplicate action that the user selected when importing the file. Once those are dealt with, the result of the import is passed back through the controller to the user as a success.

When exporting a file (through export_controller.rb), the request passes the order of the headers and the class being imported. These are passed to the export service (Export.rb). The export service puts all the headers into a CSV, then picks out the values associated with the base class (ie. the fields of the User class), and puts them into an array. It then goes through each of those classes ExternalClasses, gets the values, and places them in the CSV. After the same has been done to every row, this CSV is passed back to the export_controller.rb, and given to the user successfully.

Architecture

The system is divided into two main layers:

  • Backend — Handles import/export logic, data validation, and duplicate record management.
  • Frontend — Provides user interfaces for triggering imports and exports, communicating with backend controllers.

Backend Components

FieldMapping

Represents the relationship between class fields and their corresponding data columns.

  • Attributes:
    • class: Class — The associated class for data mapping.
    • ordered_fields: Array[String] — Field order for import/export operations.
DuplicateAction (Mixin)

Defines actions to take when duplicate records are detected.

  • Attributes
    • name: String — Name of the duplicate action.
  • Methods:
    • on_duplicate_record() — Handles logic when a duplicate record is found.
ImportableExportable (Mixin)

Provides shared functionality for classes that can be imported or exported.

  • Attributes:
    • mandatory_fields: Array[String] — Fields required for processing.
    • optional_fields: Array[String] — Fields that can optionally be included.
    • available_duplicate_actions: Array[DuplicateAction] — List of supported duplicate handling strategies.
  • Methods:
    • register_detail(class, field: String)
    • try_insert_record(DuplicateAction)
    • is_duplicate_record()
    • get_detail_field(record, detail_class, detail_field: String)
    • to_hash()
ImportExportManager

Acts as the central interface for managing import and export workflows.

  • Methods:
    • export(class, mapping: FieldMapping, filename: String) — Exports class data based on mapping.
    • import(class, mapping: FieldMapping, filename: String, use_header: bool) — Imports data into the specified class.
    • default_mapping(class) — Retrieves the default mapping configuration.
    • suggest_filename(class, mode: String) — Suggests a filename based on class and mode.
ImportController

Handles import-related HTTP requests.

  • Endpoints:
    • index — Returns import configuration data.
    • import — Accepts incoming data and triggers import operations.
  • Interactions:
    • Sends mandatory and optional fields, as well as available duplicate actions, to the frontend.
    • Receives chosen ordered fields and duplicate action from the frontend.
ExportController

Handles export-related HTTP requests.

  • Endpoints:
    • index — Provides available export configurations.
    • export — Generates export files based on selected parameters.
  • Interactions:
    • Sends mandatory and optional fields to the frontend.
    • Receives chosen ordered fields for export.

Frontend Components

Import (.tsx)
  • Methods:
    • on_import — Initiates the import process.
  • Interactions:
    • Receives mandatory/optional field data and duplicate actions.
    • Sends selected ordered fields and chosen duplicate handling action back to the backend.
Export (.tsx)
  • Methods:
    • on_export — Initiates the export process.
  • Interactions:
    • Receives mandatory/optional field data from backend.
    • Sends selected ordered fields for export.

Data Flow

Import Sequence
  1. Frontend requests import configuration.
  2. Backend responds with mandatory/optional fields and duplicate actions.
  3. User selects ordered fields and a duplicate action.
  4. Frontend sends selections to backend for processing via ImportController.import.
Export Sequence
  1. Frontend requests export configuration.
  2. Backend sends available fields.
  3. User selects ordered fields to include.
  4. Frontend sends selections to backend via ExportController.export.

Use Case Diagram

Primary Actor

Instructor / Administrator

  • Initiates both import and export operations for any class (e.g., Users, Teams, Rubrics).
  • Goal is to efficiently transfer data into or out of the system without requiring separate tools for each entity.

Use Cases

Main Use Cases
Import data for any class from a CSV file
  1. The actor uploads a CSV file.
  2. The system reads available headers or prompts the actor to select them.
  3. The actor confirms header order and selects a duplicate-handling option.
  4. The system validates data and imports records according to the selected duplicate policy.
Export data for any class to a CSV file
  1. The actor selects which fields to include.
  2. The actor orders these fields as desired.
  3. The system generates a downloadable CSV file containing the chosen data.
Included Use Cases
Choose/Auto Read Headers Included in the File
  • When importing, the system attempts to automatically detect headers from the uploaded CSV.
  • If headers are missing or unclear, the actor manually specifies them.
Choose the Order of the Headers
  • Applies to both import and export operations.
  • The actor arranges the order of headers to ensure correct mapping between CSV columns and database fields.
Choose Headers that will be Included in the File
  • Applies primarily to export operations.
  • The actor selects specific headers to include in the output CSV file, ensuring only relevant data is exported.

Relationships

  • Both the Import and Export use cases include the sub-use cases for choosing headers and their order.
  • The actor directly initiates the primary use cases and indirectly interacts with the included ones through system prompts.

System Responsibilities

  • Provide the actor with available class fields and supported duplicate actions.
  • Manage CSV validation, field mapping, and record processing.
  • Generate clear feedback on import/export results (e.g., success, skipped, or error entries).

Design Notes

The design emphasizes modularity and extensibility because the same flow supports multiple data entities using a unified import/export framework.

  • Included use cases ensure flexibility for various file structures and actor preferences.
  • Error handling (e.g., missing headers, duplicate data) is encapsulated within the main import process.

Usage

Creating CSV for Importing

When creating a CSV that will be imported, there are a few guidelines that should be followed to ensure information is uploaded successfully.

Guidelines
  1. CSV should be made with headers that names align with the fields in the program. The names should be in Capital case (ie Name, User id, Txt, etc). These need to align with the program version of the fields (ie. name, user_id, txt) because they will be converted programmatically.
  2. CSV must include all mandatory fields and necessary external fields. Optional fields may be included as well. To find these, check the import page for the item you are trying to import.
  3. For Headers that refer to an external class, the class name should be appended to the front of the field. (ex. If a user is trying to reference a Role by its name field, the header should be "Role name", not "name").
  4. For CSVs that contain Duplicate Headers for External classes (Ex. Items and QuestionAdvice), make sure the external class appears in the CSV grouped together and in sequence.

Importing a File

The Import Modal is a reusable modal used to upload CSV files for different models (Items, Teams, Users, Assignments, etc.).

Opening the Import Modal
  • From the relevant page (e.g., Items index), click the Import button. This opens the Import <ModelName> modal.
  • As soon as the modal opens, the frontend automatically calls:
    GET /import/<modelClass> to fetch import metadata for that model. This metadata is what populates the lists at the top of the modal.
Field Requirements
  • At the top of the modal you’ll see three lists:
  1. Mandatory fields – columns that must be present in your import for it to succeed.
  2. Optional fields – extra columns you may include if you have that data.
  3. External fields – related fields that can be pulled from or linked to other records (e.g., questionnaire_name, question_advice_id, etc.)
  • These lists are read-only and meant to guide how you structure your CSV.

Selecting a CSV
  • Under CSV file:
  1. Click Choose File and select your .csv file.
  2. Decide whether your file’s first row is a header row:
    • First row contains headers (switch ON)
      • Use this if your first row has column names like txt, weight, seq.
      • The backend will match CSV headers to field names automatically.
    • First row contains headers (switch OFF)
      • Use this if your file has no header row (just data).

Manual Column Mapping
  • If “First row contains headers” is OFF, the modal:
    • Reads the first one or two lines from your CSV.
    • For each column, shows:
      • A dropdown to choose which field this column represents.
      • A “First Row Value” snippet so you can see an example value from that column.
  • You must:
    1. Go through each column.
    2. Use the dropdown to select the corresponding field name (e.g., map column 1 → txt, column 2 → weight, etc.).
    3. Ensure that all mandatory fields appear in at least one column.
    • If mandatory fields are missing from your selection, the modal will block the import and show a status message.

Handling Duplicates
  • In the Duplicate handling section:
    • If the backend has configured options, you’ll see one or more radio buttons (e.g., skip, overwrite).
    • Choose how the system should behave if it encounters duplicate records during the import.
  • If no duplicate options are available, you’ll see: “No duplicate options.”
Running the Import
  • When everything is set:
    1. Click "import"
    2. The frontend sends a POST /import/<modelClass> request with:
      • csv_file – the file you selected.
      • use_headers – true or false depending on the toggle.
      • ordered_fields – only included if header mode is off, listing the fields you selected for each column.
  • While the request is in progress, the modal shows a loading state.
  • When the response returns, the Status line at the bottom will show either:
    • A success message from the backend (e.g., “Import complete” or a custom message)
    • An error message if something went wrong (e.g., missing mandatory fields, invalid format)
  • You can then close the modal with cancel or by using the close icon.

Exporting a File

Opening the Export Modal
  • Click the Export button on the page for the model you want to export (e.g., Teams).
  • When the modal opens, it automatically makes a request to:
    `GET /export/<modelClass>`
  • to fetch the field metadata for the selected model.
Field Lists Displayed
  • The modal uses the metadata received from the backend to show three types of fields:
  1. Mandatory fields – These must always be included in the export and cannot be unchecked.
  2. Optional fields – These may be included in the export at the user’s discretion.
  3. External fields – Additional related fields made available for export (if applicable).
  • Users can hover over an info icon to view the full field list in a tooltip.
Choosing Columns to Export
  • The modal provides a checkbox list of all available fields.
  • Users may:
  1. Check or uncheck optional fields.
  2. View mandatory fields (locked and always selected).
  3. Reorder any fields using ↑ and ↓ arrows, which determines the ordering of columns in the exported CSV.
Column Ordering
  • Fields appear in the export file in the exact order shown in the modal.
  • The user can:
  1. Move any non-mandatory field up.
  2. Move any non-mandatory field down.
  • Mandatory fields are always included and their order can also be adjusted if they are part of the list returned by the backend.
Generating the CSV
  • When satisfied with selections:
    • Click export
  • The frontend gathers:
    • The selected fields
  • It then generates a CSV where:
    • The first row contains the field names (in the chosen order)
    • All following rows contain the corresponding values
    • A downloadable `.csv` file is automatically created and saved to the user’s system.
Status Feedback
  • The modal displays a status message during and after the export operation, for example:
  1. “Generating CSV…”
  2. “Export complete”
  • If no fields are selected, the modal will prevent exporting and display a warning.

Making a class Importable/Exportable

Backend

  1. Add the Import/Export mixin to the top of the class 'extend ImportableExportableHelper'
  2. Specify which fields are mandatory for import/export (in snake_case)
  3. Specify any external classes that will need to be looked up or created (ie. looking up the Role of a User by id)
  4. Specify the available duplicate actions. (By default, the offending fields are transformed.)

Example of adding helper to the User model

Note: If you are adding this helper to a class that has sub classes: Case 1: If you want the sub classes have the same fields, external classes, and duplicate actions as the super class:

  • Do the steps listed above in the super class
  • Additionally, do only the first step in the sub class.

Case 2: If you want to specify different fields, external classes, or duplicate actions for sub classes:

  • Do not do any of the steps for the super class
  • Do all steps for each individual sub class


Example of Case 1: Adding helper to the Item model, then QuizItem model

Frontend

  1. Create the showModal variable
  2. Create the handleCloseModal method
  3. Add the ImportModal or ExportModal to your frontend file. In the tag, add the show variable, onHide method, and the string of the class that is being imported/exported. (ie Model Class User => "User")

Testing

Fixtures

  • empty.csv
    A completely empty CSV file. This file is used to ensure that if no content is included, the import service will fail gracefully.
  • empty_with_headers.csv
    A CSV file that only contains the headers for the User class. This file is used to ensure a file with no records is handled gracefully.
  • multiple_users_no_headers.csv
    A CSV file with no headers for importing records for the User class. It contains multiple User records (John Doe, Jane Doe) to import.
  • multiple_users_with_headers.csv
    A CSV file with headers for importing records for the User class. It contains multiple User records (John Doe, Jane Doe) to import.
  • questionnaire_item_with_headers.csv
    A CSV file with headers for importing records for the Item class. These Items make up a questionnaire. It contains a single Item that needs to be imported.
  • single_user_no_headers.csv
    A CSV file with headers for importing records for the User class. It contains a single User (John Doe) who needs to be imported.
  • single_user_with_headers.csv
    A CSV file with headers for importing records for the User class. It contains a single User (John Doe) who needs to be imported.
  • users_duplicate_records.csv
    A CSV file containing multiple duplicate records that need to be handled.
  • single_user_email_invalid.csv
    A CSV file that contains a user with an invalid email. That is an Internal field that needs to be validated.
  • single_user_role_doe_not_exist.csv
    A CSV file that contains a user with a role that does not exist. That is a field from an External Class that needs to be looked up.

Unit Testing

ImportableExportableHelper - Importing

Create tests for each of the different importable classes
  1. Show a class with no headers can be imported (User)
    This test tries to import the 'single_user_no_headers.csv' file. This file doesn't have headers, so the test passes a list of headers to the import statement. This CSV file calls for the Role Name of the user, which is set to Student. This tests whether the import statement is able to look up a role by its name. It is able to do this because the name is a lookup field for the External Class Role, which is assigned to the User Class.
  2. Show a class with headers can be imported (User)
    This test tries to import the 'single_user_with_headers.csv' file. This file has headers, so the test passes nil to the header variable of the import statement. This CSV file calls for the Role ID of the user, which is set to 4 (Teaching Assistant). This tests whether the import statement is able to look up a role by its ID. It can do this even though ID is not the set lookup field, because the lookup function uses the primary key if the lookup field isn't in the file.
  3. Show a class with multiple records can be imported (User)
    This test tries to import the 'multiple_users_with_headers.csv' file. This file contains multiple user records that need to be imported (John Doe and Jane Doe). This tests that multiple records successfully get imported with the right information.
  4. Show a class with external create classes can take duplicate headers (Questionnaire w/ multiple Advice)
    This test tries to import the 'questionnaire_item_with_headers.csv' file that creates an external class. Not only that, but it can detect if there are multiple instances of this external class and save them individually.


Import Export Helper Tests: Test Case 1 Implementation

Import Export Helper Tests: Test Case 4 Implementation


Create Tests to test Errors/Edge Cases
  1. Import a class with external lookup class that doesn't exist
    Tries to import a csv with an invalid external field. The user's role doesn't exist, so looking up this field should fail and ultimately cause the import to fail.
  2. Import a class with an invalid field (User with ian nvalid email)
    Tries to import a csv with an invalid internal field. The user email is validated inside the User class, so make sure that validation raises the correct error.
  3. Import an empty CSV (With Headers)
    Tries to import an empty csv with no records inside. This is tested with the use_header option set to true.
  4. Import an empty CSV (Without Headers)
    Tries to import an empty csv with nothing inside. This is tested with the use_header option set to false.

Import Export Helper Tests: Error Test case 2 and 3 Implementation

ImportExportService - Importing

Create tests to make sure the Service works
  1. Test Get Import: 200 response
    Test that Import#Index recieves the response, and returns the correct values
  2. Test Post Import: 200 response
    Test that Import#Import recieves the response with data, and can return a response.


Import Service Tests: Test case 1 Implementation

Create Tests to test Errors/Edge Cases
  1. Test Post Import: 422 response
  2. Test Post Import: 500 response


ImportExportService - Exporting

Create tests for each of the different exportable classes
  • Export a class with the default headers
  • Export a class with only the mandatory headers
  • Export a class with only some optional headers
  • Export a class with a different order of headers
Create tests to test Errors/Edge Cases
  • Export a class with chosen headers that are empty


Frontend Testing

Import Modal

  • Shows loading state when isLoading is true
    Ensures that when `useAPI` reports `isLoading=true`, the modal hides all content and displays a “Loading…” message.
  • Renders field summary and duplicate options from metadata
    Confirms that metadata returned from the backend (mandatory, optional, external fields, and duplicate handling actions) are rendered correctly in the modal.
  • Shows status when importing with no file selected
    Validates that clicking “import” without choosing a CSV sets an error status message and prevents the import call.
  • Shows column mapping and first-row values when header mode is off
    After uploading a CSV and disabling the “First row contains headers” toggle, verifies that the “Column order” UI appears, column dropdowns are rendered, and the first-row preview text is displayed.
  • Calls sendImport when import is valid
    Uploads a valid CSV containing mandatory fields and clicks “import.” Checks that the `sendRequest` mock is called, proving that the modal sends a correctly prepared API request.
  • Calls onHide when cancel is clicked
    Simulates clicking the “cancel” button and confirms the modal fires the `onHide` callback to close itself.

Future work

  • Adding event-based entity registration.
  • Extending support for new duplicate resolution policies.
  • Integration with the engine demo for visualization and debugging.
  • More extensive testing for functionality and edge cases.
  • Add a space in the import modal that shows all the values that will be imported.
  • Add the helper method across the website wherever the import/export functionality is required.
  • The helper is currently in:
  • QuizItem
  • Item
  • QuestionAdvice
  • Team
  • User

Other Notes/Recommendations

  • For Items, limit the string values that question_type can be using built-in validation rules as well, or else importing will allow just any string.
  • For Questionnaires, break_before can't be false because of the validation rules. Maybe try this `validates :break_before, inclusion: { in: [true, false] }`
Retrieved from "https://wiki.expertiza.ncsu.edu/index.php?title=CSC/ECE_517_Fall_2025_-_E2560._Framework_for_Import_and_Export&oldid=167448"