| import graphviz |
| import json |
| from tempfile import NamedTemporaryFile |
| import os |
|
|
| def generate_entity_relationship_diagram(json_input: str, output_format: str) -> str: |
| """ |
| Generates an Entity Relationship (ER) diagram from JSON input. |
| |
| Args: |
| json_input (str): A JSON string describing the ER diagram structure. |
| It must follow the Expected JSON Format Example below. |
| |
| output_format (str): The output format for the generated diagram. |
| Supported formats: "png" or "svg" |
| |
| Expected JSON Format Example: |
| { |
| "entities": [ |
| { |
| "name": "Person", |
| "type": "strong", |
| "attributes": [ |
| { |
| "name": "person_id", |
| "type": "primary_key" |
| }, |
| { |
| "name": "first_name", |
| "type": "regular" |
| }, |
| { |
| "name": "last_name", |
| "type": "regular" |
| }, |
| { |
| "name": "birth_date", |
| "type": "regular" |
| }, |
| { |
| "name": "age", |
| "type": "derived" |
| }, |
| { |
| "name": "phone_numbers", |
| "type": "multivalued" |
| }, |
| { |
| "name": "full_address", |
| "type": "composite" |
| } |
| ] |
| }, |
| { |
| "name": "Student", |
| "type": "strong", |
| "attributes": [ |
| { |
| "name": "student_number", |
| "type": "regular" |
| }, |
| { |
| "name": "enrollment_date", |
| "type": "regular" |
| }, |
| { |
| "name": "gpa", |
| "type": "derived" |
| } |
| ] |
| }, |
| { |
| "name": "UndergraduateStudent", |
| "type": "strong", |
| "attributes": [ |
| { |
| "name": "major", |
| "type": "regular" |
| }, |
| { |
| "name": "expected_graduation", |
| "type": "regular" |
| }, |
| { |
| "name": "credits_completed", |
| "type": "regular" |
| } |
| ] |
| }, |
| { |
| "name": "GraduateStudent", |
| "type": "strong", |
| "attributes": [ |
| { |
| "name": "thesis_topic", |
| "type": "regular" |
| }, |
| { |
| "name": "advisor_id", |
| "type": "regular" |
| }, |
| { |
| "name": "degree_type", |
| "type": "regular" |
| } |
| ] |
| }, |
| { |
| "name": "Faculty", |
| "type": "strong", |
| "attributes": [ |
| { |
| "name": "employee_number", |
| "type": "regular" |
| }, |
| { |
| "name": "hire_date", |
| "type": "regular" |
| }, |
| { |
| "name": "office_number", |
| "type": "regular" |
| }, |
| { |
| "name": "years_of_service", |
| "type": "derived" |
| } |
| ] |
| }, |
| { |
| "name": "Professor", |
| "type": "strong", |
| "attributes": [ |
| { |
| "name": "rank", |
| "type": "regular" |
| }, |
| { |
| "name": "tenure_status", |
| "type": "regular" |
| }, |
| { |
| "name": "research_areas", |
| "type": "multivalued" |
| } |
| ] |
| }, |
| { |
| "name": "Lecturer", |
| "type": "strong", |
| "attributes": [ |
| { |
| "name": "contract_type", |
| "type": "regular" |
| }, |
| { |
| "name": "courses_per_semester", |
| "type": "regular" |
| } |
| ] |
| }, |
| { |
| "name": "Staff", |
| "type": "strong", |
| "attributes": [ |
| { |
| "name": "position_title", |
| "type": "regular" |
| }, |
| { |
| "name": "department_assigned", |
| "type": "regular" |
| }, |
| { |
| "name": "salary_grade", |
| "type": "regular" |
| } |
| ] |
| }, |
| { |
| "name": "AdministrativeStaff", |
| "type": "strong", |
| "attributes": [ |
| { |
| "name": "access_level", |
| "type": "regular" |
| }, |
| { |
| "name": "responsibilities", |
| "type": "multivalued" |
| } |
| ] |
| }, |
| { |
| "name": "TechnicalStaff", |
| "type": "strong", |
| "attributes": [ |
| { |
| "name": "certifications", |
| "type": "multivalued" |
| }, |
| { |
| "name": "equipment_assigned", |
| "type": "multivalued" |
| } |
| ] |
| }, |
| { |
| "name": "Vehicle", |
| "type": "strong", |
| "attributes": [ |
| { |
| "name": "vehicle_id", |
| "type": "primary_key" |
| }, |
| { |
| "name": "license_plate", |
| "type": "regular" |
| }, |
| { |
| "name": "year", |
| "type": "regular" |
| }, |
| { |
| "name": "current_value", |
| "type": "derived" |
| } |
| ] |
| }, |
| { |
| "name": "Car", |
| "type": "strong", |
| "attributes": [ |
| { |
| "name": "doors", |
| "type": "regular" |
| }, |
| { |
| "name": "fuel_type", |
| "type": "regular" |
| } |
| ] |
| }, |
| { |
| "name": "Bus", |
| "type": "strong", |
| "attributes": [ |
| { |
| "name": "capacity", |
| "type": "regular" |
| }, |
| { |
| "name": "route_assigned", |
| "type": "regular" |
| } |
| ] |
| }, |
| { |
| "name": "MaintenanceVehicle", |
| "type": "strong", |
| "attributes": [ |
| { |
| "name": "equipment_type", |
| "type": "regular" |
| }, |
| { |
| "name": "specialized_tools", |
| "type": "multivalued" |
| } |
| ] |
| }, |
| { |
| "name": "Course", |
| "type": "strong", |
| "attributes": [ |
| { |
| "name": "course_id", |
| "type": "primary_key" |
| }, |
| { |
| "name": "course_name", |
| "type": "regular" |
| }, |
| { |
| "name": "credits", |
| "type": "regular" |
| } |
| ] |
| }, |
| { |
| "name": "Department", |
| "type": "strong", |
| "attributes": [ |
| { |
| "name": "dept_id", |
| "type": "primary_key" |
| }, |
| { |
| "name": "dept_name", |
| "type": "regular" |
| }, |
| { |
| "name": "budget", |
| "type": "regular" |
| } |
| ] |
| } |
| ], |
| "relationships": [ |
| { |
| "name": "PersonISA", |
| "type": "isa", |
| "parent": "Person", |
| "children": ["Student", "Faculty", "Staff"] |
| }, |
| { |
| "name": "StudentISA", |
| "type": "isa", |
| "parent": "Student", |
| "children": ["UndergraduateStudent", "GraduateStudent"] |
| }, |
| { |
| "name": "FacultyISA", |
| "type": "isa", |
| "parent": "Faculty", |
| "children": ["Professor", "Lecturer"] |
| }, |
| { |
| "name": "StaffISA", |
| "type": "isa", |
| "parent": "Staff", |
| "children": ["AdministrativeStaff", "TechnicalStaff"] |
| }, |
| { |
| "name": "VehicleISA", |
| "type": "isa", |
| "parent": "Vehicle", |
| "children": ["Car", "Bus", "MaintenanceVehicle"] |
| }, |
| { |
| "name": "Enrolls", |
| "type": "regular", |
| "entities": ["Student", "Course"], |
| "cardinalities": { |
| "Student": "M", |
| "Course": "M" |
| }, |
| "attributes": [ |
| { |
| "name": "semester" |
| }, |
| { |
| "name": "year" |
| }, |
| { |
| "name": "grade" |
| } |
| ] |
| }, |
| { |
| "name": "Teaches", |
| "type": "regular", |
| "entities": ["Faculty", "Course"], |
| "cardinalities": { |
| "Faculty": "M", |
| "Course": "M" |
| }, |
| "attributes": [ |
| { |
| "name": "semester" |
| }, |
| { |
| "name": "classroom" |
| } |
| ] |
| }, |
| { |
| "name": "WorksIn", |
| "type": "regular", |
| "entities": ["Faculty", "Department"], |
| "cardinalities": { |
| "Faculty": "M", |
| "Department": "1" |
| }, |
| "attributes": [ |
| { |
| "name": "start_date" |
| } |
| ] |
| }, |
| { |
| "name": "Manages", |
| "type": "regular", |
| "entities": ["Staff", "Department"], |
| "cardinalities": { |
| "Staff": "M", |
| "Department": "M" |
| }, |
| "attributes": [ |
| { |
| "name": "role" |
| } |
| ] |
| }, |
| { |
| "name": "Uses", |
| "type": "regular", |
| "entities": ["Staff", "Vehicle"], |
| "cardinalities": { |
| "Staff": "M", |
| "Vehicle": "M" |
| }, |
| "attributes": [ |
| { |
| "name": "usage_date" |
| }, |
| { |
| "name": "purpose" |
| } |
| ] |
| } |
| ] |
| } |
| |
| Returns: |
| str: The filepath to the generated image file. |
| """ |
| try: |
| if not json_input.strip(): |
| return "Error: Empty input" |
| |
| data = json.loads(json_input) |
| |
| if 'entities' not in data: |
| raise ValueError("Missing required field: entities") |
|
|
| dot = graphviz.Graph(comment='ER Diagram', engine='neato') |
| dot.attr( |
| bgcolor='white', |
| pad='1.5', |
| overlap='false', |
| splines='true', |
| sep='+25', |
| esep='+15' |
| ) |
| dot.attr('node', fontname='Arial', fontsize='10', color='#404040') |
| dot.attr('edge', fontname='Arial', fontsize='9', color='#4a4a4a') |
| |
| entity_color = '#BEBEBE' |
| attribute_color = '#B8D4F1' |
| relationship_color = '#FFF9C4' |
| isa_color = '#A8E6CF' |
| |
| font_color = 'black' |
| |
| entities = data.get('entities', []) |
| relationships = data.get('relationships', []) |
| |
| for entity in entities: |
| entity_name = entity.get('name') |
| entity_type = entity.get('type', 'strong') |
| attributes = entity.get('attributes', []) |
| |
| if not entity_name: |
| continue |
| |
| entity_color = '#BEBEBE' |
| |
| if entity_type == 'weak': |
| dot.node( |
| entity_name, |
| entity_name, |
| shape='box', |
| style='filled,rounded', |
| fillcolor=entity_color, |
| fontcolor=font_color, |
| color='#404040', |
| penwidth='3', |
| width='1.8', |
| height='0.8', |
| fontsize='12' |
| ) |
| else: |
| dot.node( |
| entity_name, |
| entity_name, |
| shape='box', |
| style='filled,rounded', |
| fillcolor=entity_color, |
| fontcolor=font_color, |
| color='#404040', |
| penwidth='1', |
| width='1.8', |
| height='0.8', |
| fontsize='12' |
| ) |
| |
| for i, attr in enumerate(attributes): |
| attr_name = attr.get('name', '') |
| attr_type = attr.get('type', 'regular') |
| |
| attr_id = f"{entity_name}_attr_{i}" |
| attr_color = attribute_color |
| |
| if attr_type == 'primary_key': |
| dot.node( |
| attr_id, |
| f'{attr_name} (PK)', |
| shape='ellipse', |
| style='filled,rounded', |
| fillcolor=attr_color, |
| fontcolor=font_color, |
| color='#404040', |
| width='1.2', |
| height='0.6', |
| fontsize='10' |
| ) |
| elif attr_type == 'partial_key': |
| dot.node( |
| attr_id, |
| f'{attr_name} (Partial)', |
| shape='ellipse', |
| style='filled,rounded,dashed', |
| fillcolor=attr_color, |
| fontcolor=font_color, |
| color='#404040', |
| width='1.2', |
| height='0.6', |
| fontsize='10' |
| ) |
| elif attr_type == 'multivalued': |
| dot.node( |
| attr_id, |
| attr_name, |
| shape='ellipse', |
| style='filled,rounded', |
| fillcolor=attr_color, |
| fontcolor=font_color, |
| color='#404040', |
| penwidth='3', |
| width='1.2', |
| height='0.6', |
| fontsize='10' |
| ) |
| elif attr_type == 'derived': |
| dot.node( |
| attr_id, |
| f'/{attr_name}/', |
| shape='ellipse', |
| style='filled,rounded,dashed', |
| fillcolor=attr_color, |
| fontcolor=font_color, |
| color='#404040', |
| width='1.2', |
| height='0.6', |
| fontsize='10' |
| ) |
| elif attr_type == 'composite': |
| dot.node( |
| attr_id, |
| attr_name, |
| shape='ellipse', |
| style='filled,rounded', |
| fillcolor=attr_color, |
| fontcolor=font_color, |
| color='#404040', |
| width='1.2', |
| height='0.6', |
| fontsize='10' |
| ) |
| else: |
| dot.node( |
| attr_id, |
| attr_name, |
| shape='ellipse', |
| style='filled,rounded', |
| fillcolor=attr_color, |
| fontcolor=font_color, |
| color='#404040', |
| width='1.2', |
| height='0.6', |
| fontsize='10' |
| ) |
| |
| dot.edge(entity_name, attr_id, color='#4a4a4a', len='1.5') |
| |
| for relationship in relationships: |
| rel_name = relationship.get('name') |
| rel_type = relationship.get('type', 'regular') |
| entities_involved = relationship.get('entities', []) |
| cardinalities = relationship.get('cardinalities', {}) |
| rel_attributes = relationship.get('attributes', []) |
| |
| if not rel_name: |
| continue |
| |
| if rel_type == 'isa': |
| parent = relationship.get('parent') |
| children = relationship.get('children', []) |
| |
| if parent and children: |
| isa_id = f"isa_{rel_name}" |
| isa_color = isa_color |
| |
| dot.node( |
| isa_id, |
| 'ISA', |
| shape='triangle', |
| style='filled,rounded', |
| fillcolor=isa_color, |
| fontcolor=font_color, |
| color='#404040', |
| penwidth='2', |
| width='1.0', |
| height='0.8', |
| fontsize='10' |
| ) |
| |
| dot.edge(parent, isa_id, color='#4a4a4a', len='2.0') |
| |
| for child in children: |
| dot.edge(isa_id, child, color='#4a4a4a', len='2.0') |
| |
| elif len(entities_involved) >= 2: |
| rel_color = relationship_color |
| |
| if rel_type == 'identifying': |
| dot.node( |
| rel_name, |
| rel_name, |
| shape='diamond', |
| style='filled,rounded', |
| fillcolor=rel_color, |
| fontcolor=font_color, |
| color='#404040', |
| penwidth='3', |
| width='1.8', |
| height='1.0', |
| fontsize='11' |
| ) |
| else: |
| dot.node( |
| rel_name, |
| rel_name, |
| shape='diamond', |
| style='filled,rounded', |
| fillcolor=rel_color, |
| fontcolor=font_color, |
| color='#404040', |
| penwidth='1', |
| width='1.8', |
| height='1.0', |
| fontsize='11' |
| ) |
| |
| for j, attr in enumerate(rel_attributes): |
| attr_name = attr.get('name', '') |
| attr_id = f"{rel_name}_attr_{j}" |
| attr_color = attribute_color |
| |
| dot.node( |
| attr_id, |
| attr_name, |
| shape='ellipse', |
| style='filled,rounded', |
| fillcolor=attr_color, |
| fontcolor=font_color, |
| color='#404040', |
| width='1.0', |
| height='0.5', |
| fontsize='9' |
| ) |
| dot.edge(rel_name, attr_id, color='#4a4a4a', len='1.0') |
| |
| for entity in entities_involved: |
| cardinality = cardinalities.get(entity, '1') |
| dot.edge( |
| entity, |
| rel_name, |
| label=f' {cardinality} ', |
| color='#4a4a4a', |
| len='2.5', |
| fontcolor='#4a4a4a', |
| fontsize='10' |
| ) |
|
|
| with NamedTemporaryFile(delete=False, suffix=f'.{output_format}') as tmp: |
| dot.render(tmp.name, format=output_format, cleanup=True) |
| return f"{tmp.name}.{output_format}" |
|
|
| except json.JSONDecodeError: |
| return "Error: Invalid JSON format" |
| except Exception as e: |
| return f"Error: {str(e)}" |
